mysql5.6 查看索引_MySQL5.6新引入的索引优化详解

最新推荐文章于 2023-02-22 16:08:01 发布

地下蝉

最新推荐文章于 2023-02-22 16:08:01 发布

阅读量317

点赞数

文章标签： mysql5.6 查看索引

本文链接：https://blog.csdn.net/weixin_42110533/article/details/113225879

版权

覆盖索引

覆盖索引又称索引覆盖，是指如果查询的信息在辅助索引中就可以查到，就不去聚集索引中查找。因为辅助索引中只包含记录的部分信息，所以查询可以减少大量的IO操作。实际执行时，InnoDB存储引擎永远只会选代价尽可能小，速度尽可能快的索引方式，所以优化器会在进行覆盖索引操作。

例如，在联合索引(a,b)中，在查询子句只包含b的时候，一般不能使用该联合索引，但是sql语句是统计操作select

count(*)，并且可以利用到覆盖索引的信息，就会选择该联合索引。

优化器最终通过全表扫描聚集索引，而不去选择索引，即不使用索引的情况，常发生在范围查找，Join链接等情况下。原因在于用户要选取的是整行的信息，而索引不能覆盖到要查的信息。因此如果使用了目标索引，还需要一次书签访问来查找整行数据，变为磁盘上的离散读操作，因此不如直接全表扫描。但如果此时访问的数据量很少(小于全表数据的20%)，优化器还是会选择辅助索引。因为顺序读要远远快于离散读。

对于不能进行索引覆盖的情况，优化器选择辅助索引的依据是，查找的数据是少量的。如果用户使用的是固态硬盘，随机读速度非常快，可以用FORCE

INDEX关键字来强制使用某个索引。但一般情况下，优化器自己选择的总是最好的。

如果某SQL可以选择的索引特别多，优化器选择执行计划的开销可能大于SQL本身。例如，优化器分析范围查询就比较耗时，这时DBA可以分析选择最优的索引选择，强制不执行路径的成本分析，直接查询。USE

INDEX是告诉优化器可以选择该索引，最可靠的是FORCE

INDEX。

MMR(Multi-Range Read)优化

MMR优化是将随机访问转化为较为顺序的访问，减少磁盘的随机访问。在查询辅助索引是，首先根据辅助索引的查询结果，按照主键排序，变成有序的之后，再进行书签查找。好处是减少缓冲池中页被替换的次数，批量处理对键值的查询操作。

MMR还可以将某些范围查询，拆分成键值对，在拆分过程中，直接过滤掉不符合查询条件的数据，然后再进行批量的数据查询。例如联合索引，之前是取出所有数据后再过滤，现在取出辅助索引后，就可以过滤。减少无用数据被取出的情况。

MMR优化器会将查询条件进行拆分，然后用拆分后的小条件那些查询。

ICP(Index Condition

Pushdown)优化

将where的部分过滤操作放在了存储引擎层，首先根据索引来过滤记录。提高数据库的整体性能。从而让辅助索引支持like‘�ads%’的操作。

查找缓存页的哈希算法

哈希表由直接寻址表改进而来。直接寻址表的缺点是地址长度决定了计算机的可用容量。数据库中使用冲突链接法，来解决两个关键字映射到同一个槽上的问题。一般来说，使用取余的哈希算法，m的取值要略大于2倍的缓冲池页数量。如果缓冲池页数为640，则启动时，分配1399个槽的哈希表。然后根据space_id，来计算hash值。

自适应哈希索引

数据库自建的不可人工干预的自适应哈希索引，对字典类型的查找非常快速。哈希索引只能用来搜索等值的查询，即a=3。

全文索引

电子商务网站往往需要在商品的详细介绍中查找，使用like

语句。全文索引将文本拆成单词。全文索引通常使用倒排索引来实现。在辅助表中存储了两种关联数组：倒排文章索引｛单词，单词所在文档ID｝，满倒排索引｛单词，(单词所在文档id，具体位置)｝

全文检索时，会查询关联数组，然后再去文档中查找。满倒排索引(full

inverted index)占用更多空间，但能够更好定位数据，并且扩充其他搜索特性。

在InnoDB存储引擎中，为了提高全文检索的并行性能，共有6张辅助表，根据单词编码进行分区。辅助表是持久表，存于磁盘。与全文检索索引缓存共同提高全文检索的性能。

全文检索索引缓存(FTS Index

Cache)是红黑树结构，根据(word，ilist)排序，数据对应了表。更新会写入该缓存，再延时批量写入辅助表。删除也是。从而提高性能。在事务提交是，InnoDB存储引擎会将分词写入FTS

Index Cache。数据库关闭不会破坏FTS Index

Cache，但宕机会重新读取辅助表。