MySQL数据库——聚集索引，非聚集索引，自适应哈希索引，全文索引，索引优化

最新推荐文章于 2024-08-27 16:57:03 发布

讲不出再见

最新推荐文章于 2024-08-27 16:57:03 发布

阅读量640

点赞数

分类专栏： MySQL数据库文章标签：自适应哈希索引全文索引倒排索引 Cardinality 索引优化

本文链接：https://blog.csdn.net/dgh112233/article/details/106811277

版权

1、聚集索引和非聚集索引（辅助索引）

1.1、索引覆盖

1.2、优化器选择索引

1.3、Multi-Range Read 优化

1.4、Index Condition Pushdown （ICP）优化

2、自适应哈希索引

3、倒排索引

4、全文检索

4.1、全文搜索 NATURAL LANGUAGE 模式

1、聚集索引和非聚集索引（辅助索引）

聚集索引：每张表只能有一个聚集索引，即以主键作为索引值所创建的结构，本质上是基于 B+树的，非叶子节点只用于存储索引值，叶子节点既存储索引值，也存储索引值对应的该行记录，因此，一张表的所有数据都存储在该 B+ 树的叶子节点上，而且叶子节点之间通过双向链表连接起来，更加适合范围查询。

非聚集索引：没张表可以有多个非聚集索引，也是 B+ 树，非叶子节点仍然只存储索引值，叶子节点既存储索引值，也存储一个指针，这个指针指向对应的聚集索引中的主键，因此，如果根据非聚集索引去查询数据的话，要经过2次查询，第一次是在非聚集索引上查询，找到主键，再拿着主键到聚集索引上找到数据。

1.1、索引覆盖

索引覆盖是一种查询思想，如果我们要查询主键数据，一般我们把 where 条件包含主键，这样会按照聚集索引去查询，直到聚集索引的叶子节点为止。换一种思想（索引覆盖），非聚集索引的叶子节点不是包含了主键吗？如果我们让 where 条件包含非聚集索引的列去查询，就会按照非聚集索引去查询，直到叶子节点，再取出主键，不是更加方便吗？聚集索引包含了数据，查询时，会有更多的磁盘IO，但是非聚集索引不包含数据，主包含主键，所以磁盘IO少，更加快速。

但是需要注意，使用这种思想时，非聚集索引应该与主键唯一对应，比如（键1，主键1），（键1，主键2），这样的话，根据非聚集所以查出来就有2个主键，不知道你要哪个。

优化器会对SQL进行分析，选择一个优化器认为好的一种方案去执行SQL，比如查询，它是按照索引覆盖的思想去查呢？还是按照聚集索引去查。

还需注意的是：非聚集索引的存储时离散的，聚集索引的存储时顺序的，这也是优化器考虑的一个方面。

1.2、优化器选择索引

优化器的功能就是对SQL语句进行分析，得出一个它认为最优的执行方案去执行，比如一条查询语句，用这个索引可以完成查询，用另一个索引也能完成查询，但是究竟用哪个呢？这就是优化器要分析的。在大多数时候，优化器都能有最优的分析，但是偶尔也会有分析不对的时候，如果我们看到慢查询日志里有因为优化器的错误分析而导致查询很慢的，可以自己定义用哪个索引查询，强制优化器去选择我们定义的方案。

1、建议优化器用哪个索引 use index(索引名)

比如 select * from student use index(id) where name = 'dan'; 只是建议，优化器在考虑选择的时候，多了一个方案而且，如果优化器认为你建议的方案不好，也是不会采纳的。

2、强制优化器用哪个索引 force index(索引名)

比如 select * from student force index(id) where name = 'dan'; 强制让优化器执行这个方案。

1.3、Multi-Range Read 优化

范围读取优化，这种优化是在非聚集索引上查询的情况下，如果在聚集索引上查询，就没有这一说了。如果我们查询一个范围内的数据，查询出来的是按照非聚集索引的顺序的，对应的主键未必有序（乱序的），非聚集索引上的顺序和主键的顺序不一样，因此，根据主键去聚集索引查询数据时，没有顺序可言，一会儿在这个节点里面，一会儿又在另一个节点里面，一会儿又跳回这个节点里，导致数据页频繁地被换入换出，磁盘IO增多，性能低。

在获取到乱序的主键后，Multi-Rang Read 会将主键存到一个缓冲里，排好序，再按照顺序去根据主键查询聚集索引，聚集索引里的数据存储顺序和主键的顺序一致，所以按照顺序来，一页一页的读取，降低磁盘IO。

optimizer_switch 参数可以设置此优化，里面有很多个详细的参数。默认开启。