如何巧用索引优化SQL语句性能?

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
云数据库 Redis 版,社区版 2GB
推荐场景:
搭建游戏排行榜
简介: 在 MySQL 中,添加合适的索引可以显著提升慢查询的速度,因为索引加快了数据检索。要优化 SQL 性能,首先需定位慢查询,可通过查看执行时间和执行计划。`EXPLAIN` 命令用于查看执行计划,分析如`type`(全表扫描最慢,索引扫描较快)、`key`(未使用索引为NULL)等字段。例如,全表扫描的查询可考虑为慢查询,并创建相应索引进行优化。此外,注意聚簇索引、索引覆盖和最左前缀原则等索引使用技巧,以提高查询效率。启用慢查询日志并设置阈值,有助于识别已运行的慢查询。

为什么在 MySQL数据库中,一条慢查询只要添加上合适的索引,查询速度就能提升一个档次?对于 MySQL,如何巧用索引优化SQL语句性能?需要注意什么问题?

解决问题之前最重要且最难的事情是定位问题,因此,我们需要先定位出慢 SQL,这样才能对症下药进行优化,那么,如何定位慢 SQL呢?

如何判断慢 SQL?

判断慢 SQL的方法有很多种,这里介绍最常用的两种方式:查看执行时间 和 查看执行计划。

查看执行计划

日常开发中,我们一般会使用“EXPLAIN”命令来查看 SQL语句的执行计划,从而判断 SQL是否存在慢SQL的风向,能否投入生产。

为了更好的解释“EXPLAIN”命令,我们通过一个真实示例来演示,场景:根据 name字段从拥有百万条数据的 user表中来查询记录,EXPLAIN执行计划如下图:

EXPLAIN输出的每个字段解释:

  • id: 标识查询中每个SELECT子句的顺序。通常,id值越大表示优先级越高,越先被执行。
  • select_type: 描述查询的类型。常见值包括:
  • SIMPLE:简单SELECT查询,不包含子查询或UNION。
  • PRIMARY:最外层的SELECT。
  • UNION:UNION中的第二个或后续的SELECT语句
  • DEPENDENT UNION:UNION中的第二个或后续的SELECT语句,取决于外部查询
  • SUBQUERY:子查询中的第一个SELECT
  • DEPENDENT SUBQUERY:子查询中的第一个SELECT,取决于外部查询
  • table: 查询涉及的表名
  • partitions: 显示查询访问的分区(如果表是分区表)
  • type: 连接类型,表示查询使用的访问方法。常见类型从好到差依次为:
  • system:表仅有一行(系统表)
  • const:表最多有一个匹配行(常量表)
  • eq_ref:对于每个来自前一个表的行,最多有一个匹配行
  • ref:对于每个来自前一个表的行,有多个匹配行
  • range:使用索引范围扫描
  • index:全索引扫描
  • ALL:全表扫描
  • possible_keys: 查询中可能使用的索引列表
  • key: 实际使用的索引。如果没有使用索引,则显示 NULL
  • key_len: 使用的索引的长度(字节数)
  • ref: 显示索引的哪一列被使用了,如果可能的话,是一个常量
  • rows: 估计需要读取的行数。这是一个估算值,越小越好
  • filtered: 表示返回的行的百分比。该值是一个估算值,表示在应用表条件后,返回的行数占读取行数的百分比 Extra: 其他的额外信息。常见的值包括:
  • Using index:只使用索引覆盖扫描(覆盖索引),不需要访问表数据
  • Using where:使用了 WHERE子句进行过滤
  • Using temporary:使用临时表保存中间结果
  • Using filesort:使用文件排序,通常意味着需要优化

上述示例截图中执行计划的结果分析如下:

  • id:1,表示这是最外层的查询
  • select_type:SIMPLE,表示这是一个简单查询
  • table:user,表示查询的表是 user表
  • partitions:NULL,表示没有使用分区
  • type:ALL,表示进行了全表扫描
  • possible_keys:NULL,表示没有使用索引
  • key:NULL,表示没有使用索引
  • key_len:NULL,表示没有使用索引,所以索引长度为NULL
  • ref:NULL,表示索引列与常量进行比较。
  • rows:1,表示预计读取 936000行数据
  • filtered:10.00,表示在扫描了user表的所有行之后,只有大约 10%的行满足查询条件并被返回
  • Extra:Using where,表示使用了WHERE子句进行过滤

通过示例分析可以知道:该查询进行了全表扫描且未使用任何索引,实际耗时是 240毫秒。因此,我们可以判断这条 SQL为慢 SQL(耗时大于 100ms),可以考虑给name创建一个索引来优化:

给 name字段增加一个“index-name”索引,信息如下:

从执行计划可以看出:查询使用了“index_name”索引,实际查询的行数是 1,执行时间从 240ms 降低到 10ms,速度提升了 24倍。

查看执行时间

对于已经投入生产使用的 SQL查询语句,我们一般会通过查看 SQL执行日志,通过 SQL执行时间来判断是否存在慢 SQL,在 MySQL中,可以使用下面的指令来开启慢查询日志和设置慢SQL时间阈值:

sql

复制代码

SET GLOBAL slow_query_log = 'ON'; -- 开启慢 SQL日志
SET GLOBAL long_query_time = 0.1; -- 设置慢查询阈值为 100毫秒

然后查看日志目录,指令如下:

sql

复制代码

SHOW VARIABLES LIKE 'slow_query_log_file';

索引优化

在使用索引的时候,需要注意的一些事项和使用技巧:

聚簇索引

首先需要判断 MySQL的引擎是不是 Innodb,它采用的聚簇索引(主键索引),B+树的非叶子节点(内部节点)存放的是索引值和指向子节点的指针,叶子节点上存放的是索引值和数据。

非聚簇索引,B+树的非叶子节点存储索引值和指向子节点的指针,叶子节点存放的是索引值和聚簇索引值。因此非聚簇索引需要先遍历非聚簇索引B+树定位到聚簇索引的值,再到聚簇索引上回表获取数据。 聚簇索引的优点:可以避免每棵索引树上都存放数据,使得在相同的内存空间下存放的更多的索引节点,减少磁盘IO。

聚簇索引示意图如下:

非聚簇索引示意图如下:

聚簇索引和非聚簇索引

聚簇索引:将数据存储与索引放到了一块,找到索引也就找到了数据。

索引覆盖

在当前索引树上能直接查找所需结果,不需要回表,这就是索引覆盖。

比如上面的案例: select id from user where age = 30 and sex = '男'; 因为id已经在当前索引的叶子节点,所以不需要到聚簇索引上回表,因此这就是一个索引覆盖的场景。由于覆盖索引可以减少树的搜索次数,显著提升查询性能,所以使用覆盖索引是一个常用的性能优化手段。

联合索引

联合索引是指将表中多个字段联合组合成一个索引,比如:index(age, sex)

那么联合索引是如何用B+树实现的呢?

场景:查询用户表中年龄为30岁的男性 表结构:

sql

复制代码

mysql> create table user(
id int primary key,
name varchar(16),
age int not null,
sex varchar(4) not null,
index(age, sex)) engine=InnoDB;

联合索引在 B+树索引模型示意图如下:

查询分析:

ini

复制代码

首先,从根节点根据组合索引里面的所有字段进行精确匹配查到到age=30 and sex='男'的记录有两条;

然后,获取id2和id3两个节点中指向子节点的指针,定位到子节点,再定位到叶子节点,从叶子节点中拿到聚簇索引的值 id2和id3;

最后,到聚簇索引上遍历id2和id3,直到叶子节点上获取目标数据;

最左前缀原则

在日常的工作中,我们发现 查询条件比较多,比如上面的用户表,有根据age和sex查询,有根据name和age查询,也有根据name和sex查询,各种查询组合,那是不是都要为它们创建一个索引呢? 答案是不一定。B+树 可以利用索引的“最左前缀”来定位记录。 最左前缀可以是联合索引的最左 N 个字段,也可以是字符串索引的最左 M 个字符

css

复制代码

比如:联合索引index(a, b, c)
查询条件 where a = ?
where a = ? and b = ?
where a = ? and b = ? and c = ?
where 条件中的字段都可以匹配索引,但是 where a = ?and c = ?   where条件中的a,c只有a 可以匹配 联合索引的a字段。

示例: 场景:查询用户表中姓刘的男性 联合索引:index(name, sex) B+树索引模型示意图如下:

查询分析:

sql

复制代码

首先,从根节点查到第一个'刘'开头的记录是id2,然后向后遍历,直到不满足条件为止,最后结果id2,id3两条;

然后,获取指向子节点的指针,定位到子节点,一直到叶子节点,接着比较第2个字段 sex='男',定位到 id2;

最后,根据id2到聚簇索引上遍历,直到叶子节点上获取目标数据;
从上面的查询分析可以看到:索引前缀原则,查询条件 name like '刘%' and sex = '男',只用到了联合索引中的name字段,那么set条件没有用到索引会怎么处理呢?  这个就是MySQL5.6引入的索引下推机制,name字段定位了一批数据减少了全表扫描,在符合name like '刘%'的数据集中再筛选sex='男',这样减少了回表的次数,降低了磁盘IO。

问题3:一个三层的B+树可以存放多少行数据呢?

ini

复制代码

在Innodb存储引擎里面,最小的存储单元是页(page),一个页的大小是16KB,
也就是一个节点的大小。根据上文,非叶子节点保存的是索引值和指针,
假设索引id是long类型,占8个byte,指针占6 byte, 所以,
根节点可以存放 16KB / (8 + 6) = 1170 个索引值,因此就有1170个指针,
假设一条数据的大小是1K,因此叶子节点可以存放 16Kb/1K = 16条数据,
所以3层的B+树可以存放 1170 * 1170 * 16 = 21902400行记录

总结

本文从索引角度来分析如何优化SQL语句性能,主要是思路是:

  • 先确认慢SQL,可从SQL执行日志,也可以通过 EXPLAIN执行计划
  • 通过 EXPLAIN执行计划来确认是否为慢SQL,以及该给哪些字段增加索引
  • 最后,在使用索引时,我们提供了一些注意点以及使用技巧


转载来源:https://juejin.cn/post/7372765277459759114

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
2天前
|
SQL 存储 关系型数据库
MySQL数据库进阶第二篇(索引,SQL性能分析,使用规则)
MySQL数据库进阶第二篇(索引,SQL性能分析,使用规则)
|
2天前
|
SQL 存储 数据库
SQL 撤销索引、撤销表以及撤销数据库
SQL 撤销索引、撤销表以及撤销数据库
15 4
|
4天前
|
SQL 数据处理 数据库
如何进行SQL查询优化?
【6月更文挑战第16天】如何进行SQL查询优化?
16 3
|
4天前
|
SQL 关系型数据库 MySQL
SQL优化方法有哪些?
【6月更文挑战第16天】SQL优化方法有哪些?
20 5
|
7天前
|
SQL 存储 关系型数据库
慢查询优化方案-SQL篇【JavaPub版】
慢查询优化方案-SQL篇【JavaPub版】
14 0
|
14天前
|
SQL 存储 关系型数据库
PolarDB产品使用合集之有的sql里面有自定义存储函数 如果想走列存有什么优化建议吗
PolarDB是阿里云推出的一种云原生数据库服务,专为云设计,提供兼容MySQL、PostgreSQL的高性能、低成本、弹性可扩展的数据库解决方案,可以有效地管理和优化PolarDB实例,确保数据库服务的稳定、高效运行。以下是使用PolarDB产品的一些建议和最佳实践合集。
|
14天前
|
SQL 关系型数据库 MySQL
MySQL数据库——SQL优化(3/3)-limit 优化、count 优化、update 优化、SQL优化 小结
MySQL数据库——SQL优化(3/3)-limit 优化、count 优化、update 优化、SQL优化 小结
24 0
|
14天前
|
SQL 关系型数据库 MySQL
MySQL数据库——SQL优化(2/3)-order by 优化、group by 优化
MySQL数据库——SQL优化(2/3)-order by 优化、group by 优化
17 0
|
14天前
|
存储 SQL 关系型数据库
MySQL数据库——SQL优化(1/3)-介绍、插入数据、主键优化
MySQL数据库——SQL优化(1/3)-介绍、插入数据、主键优化
27 1
|
14天前
|
SQL 关系型数据库 MySQL
MySQL数据库——索引(5)-索引使用(上),验证索引效率、最左前缀法则、范围查询、索引失效情况、SQL提示
MySQL数据库——索引(5)-索引使用(上),验证索引效率、最左前缀法则、范围查询、索引失效情况、SQL提示
17 0