数据库（四）：MySQL数据库索引优化

1. 索引类型

1. B-tree索引

• B-tree索引是以B+数的结构存储数据（顺序存储的）
• B-tree索引可以加快数据的查询速度
• B-tree索引更适合进行范围查找

1. B树索引适合场景

• 全值匹配查询

  order_sn = `9876432143340`

• 匹配最左前缀的查询
• 匹配列前缀查询

order_sn like '9876%'

• 匹配范围值得查询

  order_sn > `9876432143300`
  and order_sn < `9876432143399`

• 精确匹配左前列并范围匹配另外一列

• 只访问索引的查询

2. B树索引的使用限制

• 如果不是按照索引最左列开始查找，则无法使用索引
• 使用索引时不能跳过索引中的列
• not in 和 <>操作无法使用索引
• 如果查询中有某个列的范围查询，则其右边所有列都无法使用索引

2. Hash索引

Hash索引是基于Hash表来实现的，只有查询条件精确匹配Hash索引中的所有列时，才能使用hash索引。
对于Hash索引中的所有列，存储引擎都会为每一行计算一个Hash码，Hash索引中存储的就是Hash码。

Hash索引的限制

• Hash索引必须进行二次查找
• Hash索引无法排序
• Hash索引不支持部分查找也不支持范围查找
• Hash索引中Hash码的计算可能存在Hash冲突

3. 为什么使用索引

• 索引大大减少了存储引擎需要扫描的数据量
• 索引可以帮助我们进行排序以避免使用临时表
• 索引可以把随机IO变成顺序IO

缺点：

• 索引会增加写操作成本
• 太多的索引会增加查询优化器的选择时间

4. 索引优化策略

1. 索引列上不能使用表达式或函数

2. 前缀索引和索引列的选择性

create index index_name on table(col_name(n));

索引的选择性是不重复的索引值和表记录数的比值。

3. 联合索引
如何选择索引列的顺序

• 经常被用到的列优先
• 选择性高的列优先
• 宽度小的列优先

4. 覆盖索引
优点：

• 可以优化缓存，减少磁盘IO操作
• 可以减少随机IO，变随机IO操作变为顺序IO操作
• 可以避免对InnoDB主键索引的二次查询
• 可以避免MyISAM表进行系统调用

缺点：
无法使用覆盖索引的情况

• 存储引擎不支持覆盖索引
• 查询中使用了太多的列
• 使用了双%号的like查询

  5. 使用索引来优化查询

  1. 使用索引扫描来优化排序
• 索引的列顺序和Order By子句顺序完全一致
• 索引中所有的列的方向（升序，降序）和Order By子句完全一致
• Order by中的字段全部在关联表中的第一张表中

2. 模拟Hash索引优化查询

• 只能处理键值的全值匹配查找
• 所使用的Hash函数决定着索引键的大小

3. 利用索引优化锁

• 索引可以减少锁定的行数
• 索引可以加快处理速度，同时也加快了锁的释放

6. 索引的维护和优化

• 删除重复和冗余的索引
  
  
• 查找重复和冗余的索引
  pt-duplicate-key-checker 工具使用方法
• 查找未被使用的索引
  
• 更新索引统计信息及减少索引碎片

  analyze table table_name