# Mysql

# 数据库三范式

• 第一范式
  • 属性的原子性，不可再分
• 第二范式
  • 记录的唯一性，记录有唯一标识，不存在部分依赖
• 第三范式
  • 字段的冗余性，要求任何字段不能由其他字段派生，要求字段没有冗余，不存在传递依赖

# 索引

索引的目的在于提高查询效率

• 唯一索引
• 组合索引
  • 最左前缀原则
    • 组合键的第一个字段必须出现，出可以乱序，否则不会命中索引
    • MySQL 会一直向右匹配，知道遇到（>,<,between,like）就会停止匹配。比如 a = 1 and b = 2 and c > 3 and d = 4, 如果建立（a,b,c,d）顺序的联合索引，d 是用不到的索引，如果建立（a,b,d,c）的索引则都可以用到，且 a,b,d 的顺序可以任意调整。所以当使用组合键作为条件时一定要将比较运算符放在最后。
• 主键索引
• 全文索引

# 提高 SQL 查询效率需要注意的点

• 列字段有 null 值，索引会失效，列最好加 not null 和 default value

• not in,<>, != 索引会失效

• 查询条件中有 or 如果有的列没加索引，不会命中索引

  如: select * from orders where id = 100 or user_id = 123,当 id 有主键索引，而 user_id 没有索引时，不会命中索引。如果想命中索引，要给 where 条件的每个列都加上索引。

• like 的模糊查询以 % 开头，索引失效

• 如果字段类型是字符串，查询条件中药将数据使用引号引用起来，否则索引失效

  如： select * from users where mobile = '18888888888' , mobile 字段在查询时需要加引号，否则不会命中索引

• where 条件子句中使用了函数，无法使用索引

• 如果 MySQL 预计使用全表扫描要比使用索引快，则不使用索引

# 分析慢查询 Explain 的使用

# explain 后比较重要的几个字段

• select_type

  表示查询类型，常用的取值有：

  • SIMPLE : 表示此查询不包含 UNION 查询或子查询
  • PRIMARY : 表示此查询是最外层的查询
  • UNION : 表示此查询是 UNION 的第二查询

• table

  表示查询涉及的表或衍生表

• type

  通过 type 字段可以判断此次是全表扫描还是索引扫描

  • system： 表中只有一条数据
  • const：
  • eq_ref：此类型通常出现在多表的 join 查询
  • ref：通常出现在多表的 join 查询，针对非唯一或非主键原则，护着使用了最左前缀原则
  • range：表示使用索引范围查询，通过索引字段范围获取表中部署数据记录
  • index：表示全索引扫描，和 ALL 类似，这个仅仅扫描全部索引
  • ALL：表示全表扫描，这个类型的查询时性能最差的查询之一

  type 类型的性能对比：ALL < index < range < ref < eq_ref < const < system

• possible_keys

  表示在查询时, 能够使用到的索引. 注意, 即使有些索引在 possible_keys 中出现, 但是并不表示此索引会真正地被 MySQL 使用到。 MySQL 在查询时具体使用了哪些索引, 由 key 字段决定.

• key

  此字段是 MySQL 在当前查询时所真正使用到的索引.

• rows

  估算 SQL 要查找到结果集需要扫描读取的数据行数。这个值非常直观显示 SQL 的效率好坏, 原则上 rows 越少越好

# MySQL 索引及 B+树

• 为什么加索引能优化查询？

索引是一种优化查询的数据结构，MySQL中的索引是使用B+树的数据结构实现的，B+树可以优化查询速度。所以在MySQL中可以使用索引优化查询速度。

• 为什么MySQL使用B+树

  • B+树的非叶子结点只存储索引值和指针，不存储数据

    • 在磁盘中每页容量大小固定,如果存储数据就会导致存储的键（索引值）少，导致树高，树的高度会影响I/O开销，也就影响索引的查找效率。

  • MySQL 索引使用 B+树的数据结构存储索引数据

• MyISAM 和 InnoDB 索引存储的方式

  • MyISAM
    • MyISAM 叶子节点存储的是表的一行数据对应的地址
  • InnoDB
    • 主键索引叶子节点存的是当前行的数据
    • 非主键索引叶子节点存的是当前行的主键索引

# Mysql 分表

# 为什么要分表

当一张表的数据达到千万级别时，查询一次所花的时间会变多，如果有联合查询的话，SQL执行速度回很慢。分表的目的就在于减小数据库的负担，缩短查询时间。

• 数据量太大，读写性能会下降，即使有索引，索引也会变得很大，性能同样会下降
• 数据库文件会变得很大，数据库备份和恢复需要耗时很长

# 切分策略

• 水平切分 当一个应用难以再以更细粒度的垂直切分或者切分后数据量行数依然巨大，存在单库读写存储性能瓶颈，这时候需要进行水平切分。 水平切分分为库内分表和分库分表，库内分表只解决单一表数据量过大的问题，没有解决高并发下对同一数据库实例的查询压力
  • 优点
    • 不存在单表数据量过大，高并发的性能瓶颈，提升系统稳定性和负载能力
    • 应用端改造小，不需要拆分业务模块
  • 缺点
    • 夸分片的事务一致性难以保证
    • 夸库的join关联查询性能差
• 垂直切分 垂直切分是基于数据库的列进行，某个表字段较多，可以新建一张扩展表，将不经常用的字段或字段长度较大的字段拆到扩展表。通过大表拆小表，可以在数据以行为单位加载到内存中的时候加载到更多的数据，命中率更高，减少磁盘IO，从而提升数据库性能。
  • 优点
    • 解决业务系统层面的耦合，业务清晰
    • 提升IO、数据库连接数
  • 缺点
    • 部分表无法join，只能通过接口聚合方式解决，提升了开发的复杂度
    • 依然存在单表数据量过大的问题

# 方案

• range
  • 0 - 100000 , 100001 - 200000
• id取模
• 地理区域
• 时间

# 分库分表带来的问题

• join 操作

• count 操作

• order by 操作

# Mysql 锁

# 事务

# 事务的ACID属性

• 原子性（Atomicity）
• 一致性（Consistent）
• 隔离性（Isolation）
• 持久性（Durable）

# 并发事务处理会带来的问题

• 更新丢失 两个事务同时操作相同数据，后提交的事务会覆盖先提交的事务处理结果，通过乐观锁可解决。
• 脏读 事务A读取到了事务B已经修改尚未提交的数据，如果B事务回滚，A读去的数据无效，不符合一致性
• 不可重复读 事务A读取到事务B已经提交的修改数据，不符合隔离性
• 幻读 事务A读去到了事务B提交的新数据，不符合隔离性

# 事务隔离级别

• Read uncommitted(读未提交)
  • 不可避免脏读
  • 不可避免重复读
  • 不可避免幻读
• Read committed(读已提交)
  • 可避免脏读
  • 不可避免重复读
  • 不可避免幻读
• Repeatable read(可重复读)
  • 可避免脏读
  • 可避免重复读
  • 不可避免幻读
• Serializable(可串行化)
  • 可避免脏读
  • 可避免重复读
  • 可避免幻读