【MySQL】索引的使用与调优技巧

为什么MySQL的MyISAM和InnoDB存储引擎索引底层选择B+树，而不是B树？

• 对于B树： 索引+数据内容分散在不同的节点上，离根节点近，搜索就快，离根节点远，搜索就慢。 花费的磁盘IO次数不平均，每一行数据搜索花费的时间也不平均。 每一个非叶子节点上，不仅仅要存储索引（key）还要存储索引值所在那一行的data数据。一个节点所能存放的索引key值的个数，比只存储索引key值的个数要少很多。 B树不方便做范围搜索，整表遍历也不方便。
• 对于B+树： 每一个非叶子节点，只存放key，不存放data，好处就是一个节点可以存放更多的key值，在理论上来说，层数会更低，搜索效率会更高。 叶子节点上存储了所有的索引值和数据data，搜索每一个索引对应的值data，都需要到达叶子节点上，这样每一行数据搜索花费的时间非常平均。 叶子节点被串在一个链表当中，形成了一个有序链表，如果要进行索引树的搜索或者整表搜索或者范围搜索，可直接遍历有序链表，效率大大提升。

哈希索引：

基于哈希表数据结构实现，时间复杂度是O(1)。对于memory内存的存储引擎操作比较适合，不适合磁盘IO操作。哈希索引没办法处理磁盘上的数据，加载到内存上构建高效的搜索数据结构，因为它没有办法减少磁盘IO次数。 由于哈希表中的元素没有顺序，哈希索引只适合等值搜索比较，不适合范围搜索，前缀搜索，ORDER BY排序等。

在InnoDB存储引擎下，对于频繁的使用二级索引会被自动优化–自适应哈希索引，即它会根据这个二级索引，在内存上根据二级索引树（B+树）上的二级索引值，在内存上构建一个哈希索引，以加快搜索。

自适应哈希索引本身的数据维护也是要耗费性能的，并不是说自适应哈希索引在任何情况下都会提升二级索引的查询性能。应该按照参数指标，来具体分析是否打开或关闭自适应哈希索引。

show engine innodb status\G;

主要可以看到： 1. 出现RW-latch，等待的线程数量（自适应哈希索引默认分配了8个分区）同一个分区等待的线程数量过多 2. 0.00 hash searches/s, 0.00 non-hash searches/s 可以看到自适应哈希索引搜索的使用频率和二级索引树搜索的频率。当自适应哈希索引搜索的使用频率低时，要考虑关闭自适应哈希索引。

下面是官方文档介绍： In MySQL 5.7, the adaptive hash index search system is partitioned. Each index is bound to a specific partition, and each partition is protected by a separate latch. Partitioning is controlled by the innodb_adaptive_hash_index_parts configuration option. In earlier releases, the adaptive hash index search system was protected by a single latch which could become a point of contention under heavy workloads. The innodb_adaptive_hash_index_parts option is set to 8 by default. The maximum setting is 512. The hash index is always built based on an existing B-tree index on the table. InnoDB can build a hash index on a prefix of any length of the key defined for the B-tree, depending on the pattern of searches that InnoDB observes for the B-tree index. A hash index can be partial, covering only those pages of the index that are often accessed. You can monitor the use of the adaptive hash index and the contention for its use in the SEMAPHORES section of the output of the SHOW ENGINE INNODB STATUS command. If you see many threads waiting on an RW-latch created in btr0sea.c, then it might be useful to disable adaptive hash indexing.

具体项目实践步骤：

1.通过慢查询日志 可设置合理的，业务可以接收的慢查询时间 2.压测执行各种业务 3.查看慢查询日志，找出所有的执行耗时的sql语句 4.用explain分析这些耗时的sql 5.举例子，解决问题

可通过

show variables like 'profiling';

查看profiling的运行状态

set profiling = on;

show profiles； 查看sql语句具体详细的耗费时间。