mysql覆盖索引例子 mysql覆盖索引详解

转载

mob64ca14193248 2023-09-24 18:18:33

文章标签 mysql覆盖索引例子 mysql 数据库 database 数据库架构 文章分类 MySQL 数据库

什么是覆盖索引？

举例一

举例二

覆盖索引的利弊

好处

弊端

什么是覆盖索引？

理解方式一：索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了他们索引的数据；当能够通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了满足查询结果的数据就叫做覆盖索引。

理解方式二：非聚簇复合索引的一种形式，它包括在查询的select join 和where子句用到的所有列（即建索引的字段正好是覆盖查询条中所涉及的字段）。

简单说就是索引+主键包含select 到from之间查询的列

举例一

-- 删除之前在student上创建的索引
DROP INDEX idx_age_name ON student;

-- 查看student上的索引
SHOW INDEX FROM student;

-- 创建索引
CREATE INDEX idx_age_name ON student(age,NAME);

EXPLAIN SELECT * FROM student WHERE age<>20;

mysql覆盖索引例子 mysql覆盖索引详解_mysql

由之前我们讲过的当创建了索引的时候，如果条件中使用了不等于(<> 或者!=)的时候索引会失效可以解释上面语句不会使用我们创建的索引。

EXPLAIN SELECT id,age,NAME FROM student WHERE age<>20;

mysql覆盖索引例子 mysql覆盖索引详解_mysql_02

上面这个语句使用到了我们创建的索引，这又是为什么呢？

当我们使用select * 的时候优化器不知道我么的语句的列是什么不如直接扫描全表进行分析，而我们第二条语句明确了使用列，我们可以看到select 中的列 age和name是存在与索引中的，而id其实也是在我们根据索引排好序的叶子节点中的，这就不需要我们进行回表查询了，如果select中出现了除id以及索引字段的其他列，那么索引也不会被使用，比如下面这个。

EXPLAIN SELECT id,age,stuno ,NAME FROM student WHERE age<>20;

mysql覆盖索引例子 mysql覆盖索引详解_mysql覆盖索引例子_03

举例二

EXPLAIN SELECT * FROM student WHERE NAME LIKE '%abc';

mysql覆盖索引例子 mysql覆盖索引详解_database_04

EXPLAIN SELECT id,age,NAME FROM student WHERE NAME LIKE '%abc';

mysql覆盖索引例子 mysql覆盖索引详解_mysql_05

上面的执行结果也可用我们举例一中的解释

覆盖索引的利弊

好处

①避免innodb表进行索引的二次查询（回表）

innodb是以聚簇索引的顺序来存储的，对于innodb来说，二级索引在叶子节点中所保存的时行的主键信息，如果用二级索引查询数据，在查找到相应的键值后，还需要通过主键二次查询才能获取我们真实所需要的数据。在覆盖索引中，二级索引的键值中可以获取所需要的数据，避免了对主键的二次查询，减少了IO操作，提升了查询效率。

②可以把随机的IO变成顺序IO加快查询效率

由于覆盖索引是按键值顺序存储的，对于IO密集型的范围查找来说，对比随机从磁盘读取每一行的数据IO要少的多，因此利用覆盖索引在访问时也可以把磁盘的随机读取IO转变成索引查找的顺序IO。

由于覆盖索引减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。