MySQL数据库索引优化

一，Btree索引和Hash索引

1,MySQL支持的索引类型（在存储引擎层实现的）

2.Btree索引的特点：Btree索引以B+树的结构存储数据，加快存储引擎查找效率，更适合进行范围查找

在什么情况下可以用到B树索引：全值匹配信息，匹配最左前缀查询(联合索引)，匹配列前缀查询：order_sn like '12345%'，匹配范围值的查询，精确匹配左前列并且范围匹配另外一列，只访问索引的查询

Btree索引的使用限制：如果不是按照索引最左列开始查找，则无法使用索引（联合索引）。使用索引时不能跳过索引中左边的列（三个列组成的联合索引中如id,name,phone，如果只查了id和phone,那么索引只对id有效）。Not in 和<>操作无法使用索引。如果查询中有某个列的范围查询，则其右边所有列都无法使用索引。

3.Hash索引的特点：Hash索引是基于Hash表实现的，只有查询条件精确匹配Hash索引中的所有列时才能够使用到Hash索引（只能用到等值查询中，不能适用于范围查询或者模糊查询）对于Hash索引中的所有列，存储引擎都会为每一行计算一个Hash码，Hash索引中存储的就是Hash码，这也证明只能等值查询了。

Hash索引的限制：Hash索引必须进行二次查找（使用Hash索引必须先找到对应的行，然后进行读取），Hash索引无法用于排序（Hash索引按照hash码的顺序存储而不是按照键值），Hash索引不支持部分索引查找也不支持范围查找，Hash索引中Hash码的计算可能存在Hash冲突（Hash索引不适合选择性很差的列上（性别））

4.为什么要使用索引：索引大大减少了存储引擎需要扫描的数据量，索引帮助排序以避免使用临时表，索引可以把随机IO变为顺序IO

5,索引是不是越多越好：索引会增加写操作的成本，太多的索引会增加查询优化器的选择时间

二，索引优化策略

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

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

CREATE INDEX index_name ON table(col_name(n))

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

2.联合索引：

如何选择索引列的顺序：经常被使用到的列优先，但选择性很差的列不适合建索引。选择性高的列优先。宽度小的列优先。

3.覆盖索引（包含查询字段所有列的值）

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

无法使用覆盖索引的情况：存储引擎不支持覆盖索引，查询中使用了太多的列，使用了双%号的like查询

三，使用索引优化查询

1.使用索引扫描来优化排序：通过排序操作，按照索引顺序扫描数据。

使用索引扫描来优化排序：

索引的列顺序和order by子句的顺序完全一致

索引中所有列的方向（升序，降序）和order by子句完全一致

order by 中的字段全部在关联表中的第一张表中

2.模拟Hash索引来优化查询：只能处理键值的全局匹配查找，所使用的Hash长度决定着索引键的大小

3.利用索引优化锁：索引可以减少锁定的行数，索引可以加快处理速度，同时也加快锁的释放。

4.索引的维护和优化：

删除重复和冗余的索引