基于索引的SQL调优

关注

最新推荐文章于 2026-03-28 12:22:18 发布 · 562 阅读

原创 ·

本内容遵循 CC 4.0 BY-SA 版权协议

索引调优 —— OceanBase SQL 性能调优实践分享（1）当发现某一条 SQL 存在性能问题时，我们可以通过很多方式对这条 SQL 进行优化，其中最常见的是索引调优。本文介绍 OceanBase 中索引的一些基础知识，以及创建合适的索引的方法。阅读详情

基于索引的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/

收录于

数据库

标签

#数据库 #mysql

GaussDB SQL调优：建立合适的索引所有调优手段都是围绕资源使用开展的。比如做典型点查询的时候，可以用seqscan+filter(即读取每一条元组和点查询条件进行匹配)实现，也可以通过indexscan实现，显然indexscan可以以更小的代价实现相同的效果。拥有云上高可用，高可靠，高安全，弹性伸缩，一键部署，快速备份恢复，监控告警等关键能力，能为企业提供功能全面，稳定可靠，扩展性强，性能优越的企业级数据库服务。很明显，执行计划中存在SubPlan，并且SubPlan中的运算相当重，即此SubPlan是一个明确的性能瓶颈点。阅读详情

基于索引的SQL调优

基于索引的SQL调优

重要的索引类型

如何设置索引？

如何使用索引？

不要在索引上进行操作

最大化利用索引

业务优化查询

结语

相关推荐