MySQL索引优化

1.MySQL支持的索引类型

　1.1. B-tree索引:

　　特点：
　　（1）以B+树的结构存储数据；
　　（2）能够加快数据的查询速度；

　　使用场景:
　　（1）全值匹配的查询；
　　（2）匹配最左前缀的查询；
　　（3）匹配列前缀查询；
　　（4）匹配范围值的查询；
　　（5）精确匹配左前列并范围匹配另外一列；
　　（6）只访问索引的查询。
　　
　　使用限制：
　　（1）如果不是按照索引最左列开始查找，则无法使用索引；
　　（2）使用索引时不能跳过索引中的列；
　　（3）Not in 和 <>(不等于)操作无法使用索引；
　　（4）如果查询中有某个列的范围查询，则其右边所有列都无法使用索引。
　　

　1.2. Hash索引：

　　特点：
　　（1）Hash索引是基于Hash表实现的，只有查询条件精确匹配Hash索引中的所有列时，才能够使用到hash索引。
　　（2）对于Hash索引中的所有列，存储引擎都会为每一行计算一个Hash码，Hash索引中存储的就是Hash码。
　　　使用场景：用于精确匹配的数据。不重复的数据列，比如身份证号码，手机号码等不唯一的数据。
　　　
　 使用限制：
　　（1）Hash索引必须进行二次查找；
　　（2）Hash索引无法用于排序；
　　（3）Hash索引不支持部分索引查找也不支持范围查找；
　　（4）Hash索引中的Hash码的计算可能存在Hash冲突。
　　　

　1.3. 使用索引的优缺点　　

　　优点：
　　（1）大大减少了存储引擎需要扫描的数据量；
　　（2）索引可以帮助我们进行排序以避免使用临时表；
　　（3）索引可以把随机I/O变为顺序I/O。
　　 缺点：
　　（1）索引会增加写操作的成本；
　　（2）太多的索引会增加查询优化器的选择时间。

2.索引优化策略

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

　2.2 前缀索引和索引列的选择性(CREATE INDEX index_name ON table(col_name(n)));

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

　2.3 联合索引

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

　2.4 覆盖索引

　优点：
　　（1）可以优化缓存，减少磁盘IO操作；
　　（2）可以减少随机IO，变随机IO操作变为顺序IO操作；
　　（3）可以避免对Innodb主键索引的二次查询；
　　（4）可以避免MyISAM表进行系统调用。
　　
　无法使用覆盖索引的情况：
　　（1）存储引擎不支持覆盖索引；
　　（2）查询中使用了太多的列；
　　（3）使用了双%号的like查询（这种情况无法使用索引）。
　　

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

　　（1）索引的列顺序和order by子句的顺序完全一致；
　　（2）索引中所有列的方向（升序，降序）和order by子句完全一致；
　　（3）order by中的字段全部在关联表中的第一张表中。
　

　2.6 模拟Hash索引优化查询

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

　2.7 利用索引优化锁

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

　2.8 删除重复和冗余的索引

pt-duplicate-key-checker h=127.0.0.1;　#可以使用该软件来帮忙检测数据表的重复和冗余

　2.9 删除未被使用过的索引

　查找命令：

SELECT object_schema,object_name,index_name,b.`TABLE_ROWAS` 
FROM performance_schema.table_io_waits_summary_by_index_usage a 
JOIN information_schema.tables b ON a.`OBJECT_SCHEMA` = b.`TABLE_SCHEMA` AND a.`OBJECT_NAME` = b.`TABLE_NAME` 
WHERE index_name IS NOT NULL AND count_star = 0 ORDER BY object_schema,object_name;

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

analyze table table_name;　#更新索引统计来使mysql优化器选择使用次数更多的索引，MyISAM会导致锁表，Innodb会统计可能不太准确。

optimize table table_name;　#维护表和索引的碎片（使用不当会导致锁表）