BTree索引/Hash索引

BTree索引

• 使用B+树存储数据
• B+树索引能够加快数据的查询的速度
• 更加适合进行范围查找
• 什么情况下用到BTree索引？

1. 全值匹配的查询
2. 匹配左前缀的查询
3. 匹配列前缀查询
4. 范围查找
5. 匹配左前列并范围匹配另外一列
6. 只访问索引的查询

BTree索引的限制

1. 如果不是按照索引左列开始查找，那么无法使用索引。
2. 使用索引时不能跳过索引中的列。
3. not in、<>、!=操作无法使用索引。
4. 如果查询中有某个列的范围查询，则其右边的所有列都无法使用索引。

Hash索引

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

Hash索引的限制

• Hash索引中包含的只是Hash码与行指针，因此必须进行二次查找。
• Hash索引的建立是由Hash码构成的，因此Hash索引无法用于排序。
• Hash索引不支持部分索引查找也不适合范围查找。
• Hash索引中Hash码的计算可能存在Hash冲突。

为什么使用索引

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

索引的性能成本

• 由于在写入数据时也要维护索引，因此索引会增加写操作的成本。
• 太多的索引会导致查询优化器的时间，因为查询优化器要在很多索引中选择出合适的索引。

索引优化策略

• 索引列上不能使用表达式或者是函数。
• 对于InnoDB来说，索引列大小限制767Byte，对于MyISAM来说是1000Byte。
• 前缀索引或索引列的选择性，索引的选择性是不重复的索引值和表的记录数的比值。
• 建立联合索引如何选择索引列的顺序？

1. 经常会被使用到的列优先。
2. 选择性高的列优先。
3. 宽度小的列优先使用。

1. 可以优化缓存，减少磁盘IO
2. 可以减少随机IO，变成顺序IO
3. 可以避免对InnoDB主键索引的二次查询
4. 可以减少MyISAM表进行系统调用

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

1. 索引的列顺序和order by子句的顺序完全一致。
2. 索引红所有列的升序降序和order by子句完全一致。
3. order by中字段全部在关联表中的张表中。

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

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

1. primary key(id), unique key(id), index(id)
2. index(a), index(a,b)
3. primary key(id), index(a,id)
4. 使用工具pt-duplicate-key-checker h=127.0.0.1来检查

更新索引统计信息及减少索引碎片

1. analyze table 表名，InnoDB存储引擎执行该命令不会锁表只是粗略估算值。
2. optimize table 表名，使用不当会导致锁表。