MySQL优化总结 (索引 + 存储结构 + 事务 + 锁机制)

MySQL优化

1. 分层

1.1 连接层:

• 提供与客户端连接的服务

1.2 服务层:

• 提供各种用户使用接口 (CRUD)
• Sql优化器
  • 会自动优化语句,写的语句A,执行的是优化B

1.3 引擎层:

常见引擎：MyISAM，InnoDB

区别：

1. 是否⽀持⾏级锁 : MyISAM 只有表级锁(table-level locking)，⽽InnoDB ⽀持⾏级锁(rowlevel locking)和表级锁,默认为⾏级锁。
2. 是否⽀持事务和崩溃后的安全恢复： MyISAM 强调的是性能，每次查询具有原⼦性,其执⾏速度
   ⽐InnoDB类型更快，但是不提供事务⽀持。但是InnoDB 提供事务⽀持事务，外部键等⾼级数据
   库功能。 具有事务(commit)、回滚(rollback)和崩溃修复能⼒(crash recovery capabilities)
   的事务安全(transaction-safe (ACID compliant))型表。
3. 是否⽀持事务和崩溃后的安全恢复： MyISAM 强调的是性能，每次查询具有原⼦性,其执⾏速度
   ⽐InnoDB类型更快，但是不提供事务⽀持。但是InnoDB 提供事务⽀持事务，外部键等⾼级数据
   库功能。 具有事务(commit)、回滚(rollback)和崩溃修复能⼒(crash recovery capabilities)
   的事务安全(transaction-safe (ACID compliant))型表。
4. 是否⽀持外键： MyISAM不⽀持，⽽InnoDB⽀持。
5. 索引：

MyISAM: B+Tree叶节点的data域存放的是数据记录的地址。在索引检索的时候，⾸先按照B+Tree
搜索算法搜索索引，如果指定的Key存在，则取出其 data 域的值，然后以 data 域的值为地址
读取相应的数据记录。这被称为“⾮聚簇索引”。
InnoDB: 其数据⽂件本身就是索引⽂件。相⽐MyISAM，索引⽂件和数据⽂件是分离的，其表数据
⽂件本身就是按B+Tree组织的⼀个索引结构，树的叶节点data域保存了完整的数据记录。这个索
引的key是数据表的主键，因此InnoDB表数据⽂件本身就是主索引。这被称为“聚簇索引（或聚集
索引）”。
其余的索引都作为辅助索引，辅助索引的data域存储相应记录主键的值⽽不是地
址，这也是和MyISAM不同的地⽅。在根据主索引搜索时，直接找到key所在的节点即可取出数
据；在根据辅助索引查找时，则需要先取出主键的值，再⾛⼀遍主索引。 因此，在设计表的时
候，不建议使⽤过⻓的字段作为主键，也不建议使⽤⾮单调的字段作为主键，这样会造成主索引
频繁分裂。

1.4 存储层:

• 使用 B+ 树进行存储数据

  • mysql直接把树的根节点加载到内存中，只需要进行两次io操作

• 存储索引:

  • 页:
    • 数据都是存放到页中的,每个页中都有多行数据,一行只能存储16384个字节
    • 多余的字节,存放到其他页中,行溢出
  • 目录页:
    • 保存着多个页目录的地址值
    • key: 页目录的最小的索引
    • value: 页号
  • Page Directory: 页面目录
    • 对多行数据进行分组,方便查找
  • Dynamic格式: 可变字段长度列表,null标志位,记录头信息,列1数据,列2数据
    • 可变字段长度列表: 记录每个可变长度字段真实存储的长度
    • null标志位: 将为null的列统一管理起来,标记他们的位置
      • 如果没有null值,null标志位也就不存在
    • key: 索引
    • value: 真实数据
  • 其他:
    • 索引字段:
      • 主键 > 唯一 > row_id
    • 缓存表:
      • MySQL会先创建一张空表,在进行创建B+ 树时
      • 会把第一张表进行copy,然后把第一张表当做目录页

• 读取数据:

  • 将数据读到内存中时,会使用局部性原理,将相邻的数据(一页/4kb)也读到内存中去
  • 加快下一次的检索速度

1.5 常见问题

1. 为什么要设置主键 ？
毫无疑问的是，当对Mysql中的表设置了InnoDB存储引擎的时候，那么就需要对一列数据建立索引，方便快速查找，如果不设置主键的，Mysql会在表中逐列对比寻找无重复项的列作为逐渐，如果没有查找到，那么Mysql会在表中添加默认列作为主键。如果不设置主键列，那么Mysql会消耗大量的资源去寻找索引列或者是创建新的索引列，如果设置了索引列那么将极大的降低Mysql的资源消耗。

2. 为什么要采用整型的数据 ？
Mysql底层对数据查找的时候，会涉及到大量的对比操作，如果采用UUID的字符串模式，那么Mysql在对比过程中会消耗资源用于字符串中每个字符的对比，这样做将极大的消耗系统的性能。

2. 为什么要采用自增主键 ？
了解B + 树的节点存储机制的话，就一定会知道当某个节点的索引数量达到阈值的时候会分裂节点重新调整树的平衡。这样做对系统性能的消耗是相当不友好，在B + 树的叶子节点中，通过双向指针维护了一根将索引按照升序排列的双向链表，采用自增主键，新的索引将会被添加在链表的尾部，避免了B + 树的分裂所带来的系统消耗。

2. 索引

• 帮助MySQL高效获取数据的数据结构(默认: B+树)

• 优势:

  • 提高查询的效率(降低IO使用率)
  • 降低CPU的使用率(B树,本身有序,在使用排序时,直接中序遍历)

• 弊端:

  • 本身需要存储空间,不适合少量的数据,频繁更新的字段
  • 可以增加查询的效率,但是会降低增删改的效率

• 分类:

  • 主键索引: 不能为null

  • 唯一索引: 不能重复 (id) ,可以为null

    create unique index 索引名 on 表(字段)
    alter table 表 add unique index 索引名 (字段)
    

  • 单值索引: 单列,一个表可以有多个单值索引

    create index 索引名 on 表(字段)
    alter table 表 add index 索引名(字段)
    

  • 复合索引: 多个列构成的 (相当于二=多级目录)

    • 页中只会存放一份数据,如果是根据辅助索引查询,数据(key:符合索引,value:主键索引)

    • 然后用主键索引(: row_id),去B+树中查询

      create index 索引名 on 表(字段1,字段2....)
      alter table 表 add index 索引名 (字段1,字段2)
      

• 删除索引:

  drop index 索引名 on 表名
  

• 查询索引:

  - show index from 表名
  - show index from 表名 \g;
  

4. SQL优化

• 原因:
  • 性能低,执行时间太长,等待时间太长,sql语句欠佳,索引失效等
• 分析sql的执行计划: explain
  • 可以让我们看到sql真正执行的过程,因为sql优化器会干扰执行
• 优化方式: 主要是优化索引
  • 对经常查询,但是很少做修改的字段可以考虑添加索引,值唯一
  • 避免使用 select * ,应当展示 索引字段
  • 避免使用or 来连接条件,ont in 也应该少用,否则会进行全盘扫描
  • 避免where子句对字段进行计算/函数操作
  • 多条件查询时,减少使用单列索引,尽量使用符合索引
  • 最左前缀法则: 使用索引字段作为条件时,如果该索引为符合索引,则必须使用该索引中的第一个字段作为条件才能保证使用到索引
  • 对经常排序的字段可以考虑添加索引
    • 但是要注意用 where 进行索引筛选,否则sql优化器不经过索引查询

5. 事务

• 事务的四大特性:
  • 原子性:
    • 每个事物都是最小的不可分割单位,要么全部成功,要么全部失败
  • 一致性:
    • 数据操作前后一致
  • 隔离性:
    • 每个线程的操作相互隔离,互不影响
  • 永久性:
    • 操作一旦成功,数据永久保存到数据库中
• 隔离级别:
  • 执行效率:
    • 串行化 < 可重复读 < 读已提交 < 读未提交
  • 并发安全:
    • 串行化 > 可重复读 > 读已提交 > 读未提交
• 并发产生的问题:
  • 脏读:
    • 一个事务读到了另一个事务未提交的数据
  • 不可重复读:
    • 一个事务读到了另一个事务已提交的数据,通常由 update / delete引起的
  • 幻读:
    • 一个事务读到了另一个事务已提交的数据,通常由 insert 引起的

隔离级别 \ 并发问题	脏读	不可重复读	幻读
串行化	无	无	无
可重复读	无	无	有
读已提交	无	有	有
读未提交	有	有	有

5. 锁

• 锁机制用于管理对共享资源的并发访问

5.1 Mysql中的锁类型

锁类型	描述
表锁	加锁快,不会出现死锁,并发度最低
行锁	加锁慢,会出现死锁,并发度最高
页锁	速度介于表锁和行锁之间,会出现死锁,并发度一般

5.2 InnoDB中的锁

锁类型	描述	写法
读锁: 共享锁,S 锁	对X锁互斥,对S锁不互斥	select … lock in share mode
写锁: 排它锁,X 锁	对X,S锁都互斥	select … for update

• 读操作:
  • 对于普通的select 语句,InnoDB 不会加任何锁
• 对于DDL操作;
  • 会默认加上写锁
• 自定义加锁:
  • 只会对我查出出来的数据进行加锁
• 事务结束,默认释放锁

5.3 死锁

• 出现原因:
  • 当两个线程争夺锁资源时,造成两个线程都无法进行下一步操作,出现死锁
• 解决方案:
  • 设置超时时间,当一个线程获取锁达到超时时间时,自动回滚事务