Mysql的索引讲解（覆盖索引，聚簇索引，辅助索引）

小灰灰 2022-10-02 11:54 262阅读 0赞

在MySQL中，like ‘string%’可以用到索引，但是like ‘%string%’却会全表扫描。这个是常识，但真的就没有解决方法了吗？有的，要使用到索引覆盖。  
**什么叫覆盖索引？**  
解释一： 就是**select的数据列只用从索引中就能够取得**，不必从数据表中读取，换句话说查询列要被所使用的索引覆盖。

解释二： 索引是高效找到行的一个方法，当能通过检索索引就可以读取想要的数据，那就**不需要再到数据表中读取行**了。如果一个索引包含了（或覆盖了）满足查询语句中字段与条件的数据就叫做覆盖索引。

解释三：是**非聚集组合索引**的一种形式，它包括在查询里的Select、Join和Where子句用到的所有列（即建立索引的字段正好是覆盖查询语句\[select子句\]与查询条件\[Where子句\]中所涉及的字段，也即，索引包含了查询正在查找的所有数据）。

总之，不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引的列，而哈希索引、空间索引和全文索引等都不存储索引列的值，所以MySQL只能使用B-Tree索引做覆盖索引。

**小试牛刀**

CREATE TABLE a1
    (
        id INT AUTO_INCREMENT PRIMARY KEY,
        column_name VARCHAR(20),
        column_type VARCHAR(20)
    );
    CREATE INDEX idx_a1_column_name ON a1(column_name);

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0NOWllZSA_size_16_color_FFFFFF_t_70]

语句1：它没有使用到索引（Extra：using where），意味着全表扫描，理论如此，

语句2：它使用了索引范围查找（type=range）（key=idx\_a1\_column\_name）,但是它使用索引方式为二级检索（Extra：Using index condition）还是会有一定的性能消耗的，也有解决办法：针对select的列创建联合索引

语句3：它使用了索引覆盖（Extra：Using index）

结论：

*  using index ：使用覆盖索引的时候就会出现
 *  using where：在查找使用索引的情况下，需要回表去查询所需的数据
 *  using index condition：查找使用了索引，但是需要回表查询数据
 *  using index & using where：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据

有实验证明using index & using where要优于using index condition。

**小知识点（原理）**

1.啥是回表？

上面讲过如果索引的列在select子句中就不需要回表，索引列也是表，如果索引中包括你需要的列，查询结果就找到了，如果没有你需要的列，**索引列中有指针指向表记录的行位置，从表中查询列值**。

2.二级索引篇

聚簇索引：每个InnoDB表具有一个特殊的索引称为聚集索引。如果您的表上定义有主键，该主键索引是聚集索引。如果你不定义为您的表的主键 时，MySQL取第一个唯一索引（unique）而且只含非空列（NOT NULL）作为主键，InnoDB使用它作为聚集索引。如果没有这样的列，InnoDB就自己产生一个这样的ID值，它有六个字节，而且是隐藏的，使其作为聚簇索引。

二级索引：又称辅助索引、非聚集索引(no-clustered index)。b＋tree树结构，然而二级索引的叶子节点不保存记录中的所有列，其叶子节点保存的是<健值，(记录)地址>，非叶子节点存放的记录格式为<键值，主键值，地址>。而聚集索引叶子节点保存保存记录中的所有列，非叶子节点保存的是下一层节点地址。

记录的地址一般可以保存两种形式：

1.记录的物理地址，页号：槽号：偏移量  
2.记录的主键值

innodb引擎是索引组织表，所有记录都放在聚集索引里，因此其辅助索引中的记录地址存放的主键的键值。

**读操作：**

通过二级索引查询记录仅能得到主键值，要查询完整的记录还需要再通过一次聚集索引查询,这种查询方式为书签查找（bookmark lookup）。

**写操作：**

仅当主键发生改变时，才会更新二级索引。

原文地址：[https://blog.csdn.net/alex\_xfboy/article/details/82789942][https_blog.csdn.net_alex_xfboy_article_details_82789942]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0NOWllZSA_size_16_color_FFFFFF_t_70]: /images/20220112/afb153605d2b43aea309adfa2c727cac.png
[https_blog.csdn.net_alex_xfboy_article_details_82789942]: https://blog.csdn.net/alex_xfboy/article/details/82789942