基于索引的SQL调优

最新推荐文章于 2025-05-10 10:20:51 发布

split_second

最新推荐文章于 2025-05-10 10:20:51 发布

阅读量275

点赞数

CC 4.0 BY-SA版权

分类专栏：数据库文章标签：数据库 mysql

本文链接：https://blog.youkuaiyun.com/split_second/article/details/128453237

数据库专栏收录该内容

9 篇文章

订阅专栏

本文介绍了基于索引的SQL调优方法，包括重要索引类型如主键、二级索引、联合索引和Hash索引，以及如何设置和使用索引以提升查询效率。强调了避免在索引上进行操作、最大化利用索引和业务层面的查询优化策略，例如避免函数、类型转换和使用LIKE操作符时注意前缀匹配。同时，提倡根据主键顺序插入和利用Redis缓存计数，以优化分页查询。

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

基于索引的SQL调优

关系型数据库MySQL是有一套明确的规则去执行各类SQL语句的。基于这套规则，开发人员可以在数据库层面上，提高SQL的执行效率。

官方的优化文档给了很详细的优化建议，本文结合自身理解，摘一些可执行性高，且比较好分类的进行介绍。开始前，我们需要先讲一下索引的类型。

重要的索引类型

主键(Clusterd Index)
在这里插入图片描述
主键索引会附带该行所有的数据信息，“打包”成一个数据页挂载在B+树的叶子节点

二级索引
在这里插入图片描述

二级索引只会挂载对应的主键ID

联合索引
在这里插入图片描述

联合索引是把多个列整合，并以第一个位置上的列为B+树的节点做排序和平衡性；在节点数据的最后，同样挂载对应的主键ID

Hash索引

Hash索引是MySQL针对热点数据，自动创建的索引类型。针对Hash碰撞问题，MySQL会根据长度在节点后加链表或红黑树

如何设置索引？

从表格数据推算离散性

SELECT COUNT(DISTINCT name) / COUNT(*) FROM table1

把所有列都计算一遍离散值，越接近1则越适合做索引

占用空间小，前缀索引

优先选择字节数小的数据类型；对于TEXT，BLOB这类长字段则需要使用前缀作为索引

SELECT COUNT(DISTINCT LEFT(article, 10)) / COUNT(*) FROM table1

计算不同长度前缀的离散值

三星索引评价标准：

SELECT song FROM albums WHERE musician = 'Maroon5' AND album_name='Songs About Jane' ORDER BY song

一个SQL语句，在经过索引的过滤后，剩下的索引宽度越窄越好——剩下的数据量越小越好；
由于B+树本身会对索引排序，因此在用ORDER BY命令时能直接利用索引的排序是更好的；
如果SQL语句中需要的所有列都可以被一个索引包涵，是更好的

在这里插入图片描述

如何使用索引？

以下面这个表为例，重点介绍几个优化点

CREATE TABLE `order_exp` (
  `id` bigint(22) NOT NULL AUTO_INCREMENT,
  `order_no` varchar(50) NOT NULL,
  `order_note` varchar(100) NOT NULL,
  `insert_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `expire_duration` bigint(22) NOT NULL,
  `expire_time` datetime NOT NULL,
  `order_status` smallint(6) NOT NULL DEFAULT '0',

  PRIMARY KEY (`id`)
  UNIQUE KEY `u_idx_day_status` (`insert_time`,`order_status`,`expire_time`),
  KEY `idx_order_no` (`order_no`),
  KEY `idx_expire_time` (`expire_time`)
)

不要在索引上进行操作

避免加减；函数；类型转换；使用null值

EXPLAIN SELECT * FROM order_exp WHERE id + 1 = 17;

type	key	key_len	Extra
ALL	NULL	NULL	Using where

可以看到，在主键用了+1操作后，执行计划中的key就变为NULL，进而导致查询效率变低。

最大化利用索引

左前缀匹配；全字匹配；范围要放在最后；

EXPLAIN Select * from s1 where order_status=1 and expire_time='2021-03-22 18:35:14';

type	key	key_len	Extra
ALL	NULL	NULL	Using where

对于索引UNIQUE KEY u_idx_day_status (insert_time,order_status,expire_time)

联合索引是以最左侧的列建立索引的，所以要使用该索引，必须要有insert_time这一列

MySQL是按照声明的顺序安排列的前后关系，因此为了更大化利用索引——让key_len足够长，需要把范围条件尽量推到后面顺序的列上

EXPLAIN select * from order_exp where insert_time>'2021-03-22 18:23:42' and insert_time<'2021-03-22 18:35:00';

type	key	key_len	Extra
range	u_idx_day_status	6	Using index condition

EXPLAIN select * from order_exp
where insert_time='2021-03-22 18:34:55' and order_status=0 and expire_time>'2021-03-22
18:23:57' and expire_time<'2021-03-22 18:35:00' ;

type	key	key_len	Extra
range	u_idx_day_status	13	Using index condition

第二个查询语句的key_len明显更长一些。

like使用前缀；

explain SELECT * FROM order_exp WHERE order_no like '%_6S';

type	key	key_len	Extra
ALL	NULL	NULL	Using where

MySQL是只支持前缀做索引的，如果想要用后缀，最好自己根据后缀新建一个索引列。

OR尽量为同一个字段；排序时按固定顺序，且排序字段同属于一个索引；

explain SELECT * FROM order_exp WHERE expire_time= '2021-03-22 18:35:09' OR order_note = 'abc';

type	key	key_len	Extra
ALL	NULL	NULL	Using where

MySQL在优化查询时，一个语句只会用一个索引，因此对于OR，可以用两个语句分别查询，再拼接结果。

业务优化查询

按照主键顺序插入；count查询可通过redis完成；limit分页

有的功能需要用到分页功能，比如从满足条件的第100行开始，取10条数据返回

select * from order_exp limit 100,10;

type	key	key_len	Extra
ALL	NULL	NULL

这样的语句会直接扫描前100行数据，然后才能返回后10条。为了提高查询效率，我们可以直接在后端开发上做改进，进而优化SQL。

比如直接指定ID

select * from order_exp where id > 120 order by id limit 10;

结语

其实相比于SQL的优化来说，服务端架构的优化难度更小，且效果更好。因此在设计一个功能时，先考虑清楚架构问题；再根据需要对SQL进行调优。

https://dev.mysql.com/doc/refman/8.0/en/optimization.html

https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

https://book.douban.com/subject/26419771/