数据库索引优化(笔记）

主要介绍Btree索引和Hash索引、以及索引优化策略

<!--more-->

原文请访问：http://fanqieto.top/2017/11/26/%E6%95%B0%E6%8D%AE%E5%BA%93%E7%B4%A2%E5%BC%95%E4%BC%98%E5%8C%96/

B-tree索引

B-tree索引的特点

• B-tree索引以B+树的结构存储数据

• B-tree索引能够加快数据的查询速度
• B-tree 索引更适合进行范围查找

什么情况下可以使用B数索引

• 全值匹配的查询
  • order_sn = "987654321"
• 匹配最左前缀的查询
• 匹配列前缀查询
  • order_sn like "9876"
• 匹配范围值的查询
  • order_sn > '987654321' and order_sn < '98765444111'
• 精确匹配左前列并范围匹配另外一列
• 只访问索引的查询

B-tree索引的使用限制

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

Hash索引

hash索引的特点

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

hash索引的限制

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

为什么要使用索引

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

索引是不是越多越好

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

索引优化策略

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

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

• create index index_name on table(col_name(n));索引的选择性是不重复的索引值和表的记录数的比值

联合索引

• 如何使用索引列的顺序
  • 经常会被使用到的列优先
  • 选择性高的列优先
  • 宽度小的列优先

覆盖索引

通常开发人员会根据查询的where条件来创建合适的索引，但是优秀的索引设计应该考虑到整个查询。其实mysql可以使用索引来直接获取列的数据。如果索引的叶子节点包含了要查询的数据，那么就不用回表查询了，也就是说这种索引包含（亦称覆盖）所有需要查询的字段的值，我们称这种索引为覆盖索引。

优点

• 可以优化缓存，减少磁盘i/o操作
• 可以减少随机i/o,变随机io操作为顺序io操作
• 可以避免对Innodb主键索引的二次查询
• 可以避免myisam表进行系统调用

无法使用覆盖索引的情况

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

使用索引来优化查询

使用索引扫描来优化排序

• 索引的列顺序和order by 字句的顺序完全一致
• 索引中所有列的方向（升序、降序）和order by 字句完全一致
• order by中的字段全部在关联表中的第一章表中

模拟hash索引优化查询

增加一列，将text字段的值用hash函数处理

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

利用索引优化锁

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

注意

select * from table_name where item = xxx;

如果没有添加对item添加索引，mysql会将查询的所有结果放入内存，然后进行where过滤，如果是事务操作的话，会进行全表锁定

索引的维护和优化

• 删除重复和冗余的索引

primary key(id),unique key(id),index(in) [重复索引]
index(a), index(a,b)
primary key(id),index(a,id) [冗余索引]

• 查找未被使用过的索引

• 更新索引统计信息及减少索引碎片
  • analyze table table_name
  • optimize table table_name:使用不当会导致锁表