覆盖索引VS非覆盖索引

最新推荐文章于 2025-03-10 13:10:04 发布

kf_panda

最新推荐文章于 2025-03-10 13:10:04 发布

阅读量1.2k

点赞数

分类专栏： MYSQL索引&SQL优化&服务器优化

本文链接：https://blog.csdn.net/gua___gua/article/details/47980073

版权

MYSQL索引&SQL优化&服务器优化专栏收录该内容

26 篇文章

订阅专栏

本文探讨了覆盖索引与非覆盖索引的概念。覆盖索引是指查询所需的全部字段都在索引中，而不需要回表到原始数据行获取信息。文章通过在tokudb引擎上的测试，强调了优化查询性能的重要性，并提供了测试方法，包括如何清空系统缓存和重启MySQL服务。此外，还提到了选择更合适的非覆盖索引以提升查询效率。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

覆盖索引：指的是查询SQL涉及的所有字段都包含在索引中。

CREATE TABLE `sjkk_gcjl` (
  `jlbh` varchar(50) NOT NULL COMMENT '记录编号',
  .......
  KEY `index09` (`csys`,`jgsj`,`jlbh`,`hphm`,`clsd`),
  CLUSTERING KEY `index08` (`jgsj`,`jlbh`),
  KEY `index10` (`csys`,`jgsj`,`jlbh`)
) ENGINE=TokuDB DEFAULT CHARSET=utf8 ROW_FORMAT=TOKUDB_QUICKLZ;

测试覆盖索引与非覆盖索引速度差异

说明：虽然使用的引擎时tokudb但是这块和innodb是类似的

测试方法

每次测试前执行下面语句（确保每次测试前清空os和Mysql缓存，并且保证表的相关文件是打开的）

sync;echo 1>/proc/sys/vm/drop_caches;/etc/init.d/mysql3307 restart; mysql -uroot -h 192.168.60.159 -P3307 changzhou_5000

select * from sjkk_gcjl limit 0;

覆盖索引查询

select `csys`,`jgsj`,`jlbh`,`hphm`,`clsd` 
from sjkk_gcjl force index(index09) 
where csys='Z' and jgsj>='2014-01-06 22:15:40' 
      and jgsj<='2015-07-06 22:15:40' 
	  and hphm like '%7Y59%' 
	  order by jgsj desc,jlbh desc limit 100;
.....
100 rows in set (10.85 sec)
+----+-------------+-----------+-------+---------------+---------+---------+------+----------+--------------------------+
| id | select_type | table     | type  | possible_keys | key     | key_len | ref  | rows     | Extra                    |
+----+-------------+-----------+-------+---------------+---------+---------+------+----------+--------------------------+
|  1 | SIMPLE      | sjkk_gcjl | range | index09       | index09 | 25      | NULL | 46344961 | Using where; Using index |
+----+-------------+-----------+-------+---------------+---------+---------+------+----------+--------------------------+

非覆盖索引查询（添加一个查询字段，让其索引无法覆盖）

select `csys`,`jgsj`,`jlbh`,`hphm`,`clsd`,`clnk`
from sjkk_gcjl force index(index09) 
where csys='Z' and jgsj>='2014-01-06 22:15:40' 
      and jgsj<='2015-07-06 22:15:40' 
	  and hphm like '%7Y59%' 
	  order by jgsj desc,jlbh desc limit 100;

100 rows in set (2 min 3.99 sec)
+----+-------------+-----------+-------+---------------+---------+---------+------+----------+-------------+
| id | select_type | table     | type  | possible_keys | key     | key_len | ref  | rows     | Extra       |
+----+-------------+-----------+-------+---------------+---------+---------+------+----------+-------------+
|  1 | SIMPLE      | sjkk_gcjl | range | index09       | index09 | 25      | NULL | 46344961 | Using where |
+----+-------------+-----------+-------+---------------+---------+---------+------+----------+-------------+

使用更合适的非覆盖索引

select `csys`,`jgsj`,`jlbh`,`hphm`,`clsd`
from sjkk_gcjl force index(index10) 
where csys='Z' and jgsj>='2014-01-06 22:15:40' 
      and jgsj<='2015-07-06 22:15:40' 
	  and hphm like '%7Y59%' 
	  order by jgsj desc,jlbh desc limit 100;

100 rows in set (2 min 2.55 sec)
+----+-------------+-----------+-------+---------------+---------+---------+------+----------+-------------+
| id | select_type | table     | type  | possible_keys | key     | key_len | ref  | rows     | Extra       |
+----+-------------+-----------+-------+---------------+---------+---------+------+----------+-------------+
|  1 | SIMPLE      | sjkk_gcjl | range | index10       | index10 | 25      | NULL | 46948386 | Using where |
+----+-------------+-----------+-------+---------------+---------+---------+------+----------+-------------+