SQL优化 慢SQL 高性能索引 全表扫描
小结:
1、
Limit深分页问题的本质原因就是:偏移量(offset)越大,mysql就会扫描越多的行,然后再抛弃掉,这样就导致查询性能的下降。所以我们可以采用标签记录法
千万级数据深分页查询SQL性能优化实践
一、系统介绍和问题描述
如何在Mysql中实现上亿数据的遍历查询?先来介绍一下系统主角:关注系统,主要是维护京东用户和业务对象之前的关注关系;并对外提供各种关系查询,比如查询用户的关注商品或店铺列表,查询用户是否关注了某个商品或店铺等。但是最近接到了一个新需求,要求提供查询关注对象的粉丝列表接口功能。该功能的难点就是关注对象的粉丝数量过多,不少店铺的粉丝数量都是千万级别,并且有些大V粉丝数量能够达到上亿级别。而这些粉丝列表数据目前全都存储在Mysql库中,然后通过业务对象ID进行分库分表,所有的粉丝列表数据分布在16个分片的256张表中。同时为了方便查询粉丝列表,同一个业务对象的所有粉丝都会路由到同一张表中,每个表的数据量都能够达到 2 亿+。
二、解决问题的思路和方法
数据库表结构示例如下:
CREATE TABLE follow_fans_[0-255]
(
id bigint(11) NOT NULL AUTO_INCREMENT COMMENT '自增id',
biz_content VARCHAR(50) DEFAULT NULL COMMENT '业务对象ID',
source VARCHAR(50) DEFAULT NULL COMMENT '来源',
pin VARCHAR(50) DEFAULT NULL COMMENT '用户pin',
ext VARCHAR(5000) DEFAULT NULL COMMENT '扩展信息',
status TINYINT(2) DEFAULT 1 COMMENT '状态,0是失效,1是正常',
created_time DATETIME DEFAULT NULL COMMENT '创建时间',
modified_time DATETIME DEFAULT NULL COMMENT '修改时间',
PRIMARY KEY(id),
UNIQUE INDEX uniq_biz_content_pin (biz_content, pin)
)
ENGINE = InnoDB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8 COMMENT = '关注粉丝表';
【 Limit实现 】
由于同一个业务对象的所有粉丝都保存到一张数据库表中,对于分页查询列表接口,首先想到的就是用limit实现,对于粉丝数量很少的关注对象,查询接口性能还不错。但是随着关注对象的粉丝数量越来越多,接口查询性能就会越来越慢。后来经过接口压测,当业务对象粉丝列表数量达到几十万级别的时候,查询页码数量越大,查询耗时越多。limit深分页为什么会变慢?这就和sql的执行计划有关了,limit语句会先扫描offset+n行,然后再丢弃掉前offset行,返回后n行数据。也就是说limit 100000,10,就会扫描100010行,而limit 0,10,只扫描10行。查询 sql 示例如下:
select id,biz_content,pin FROM follow_fans_1 where biz_content = #{bizContent} order by id desc limit 10, 10;
-
方案优点:实现简单,支持跳页查询。
-
方案缺点:数据量变大时,随着查询页码的深入,查询性能越来越差。
【 标签记录法 】
Limit深分页问题的本质原因就是:偏移量(offset)越大,mysql就会扫描越多的行,然后再抛弃掉,这样就导致查询性能的下降。所以我们可以采用标签记录法,就是标记一下上次查询到哪一条了,下次再来查的时候,从该条开始往下扫描。具体做法方式是,查询粉丝列表中按照自增主键ID倒序查询,查询结果中返回主键ID,然后查询入参中增加maxId参数,该参数需要透传上一次请求粉丝列表中最后一条记录主键ID,第一次查询时可以为空,但是需要查询下一页时就必传。最后根据查询时返回的行数是否等于 10 来判断整个查询是否可以结束。优化后的查询sql参考如下:
select id,biz_content,pin FROM follow_fans_1 where biz_content = #{bizContent} and id < #{lastId} order by id desc limit 10;
- 方案优点:避免了数据量变大时,页码查询深入的性能下降问题;经过接口压测,千万级数据量时,前 N-1页查询耗时可以控制在几十毫秒内。
- 方案缺点:只能支持按照页码顺序查询,不支持跳页,而且仅能保证前 N-1 页的查询性能;如果最后一页的表中行数量不满 10 条时,引擎不知道何时终止查询,只能遍历全表,所以当表中数据量很大时,还是会出现超时情况。
【 区间限制法 】
标签记录法最后一页查询超时就是因为不知道何时终止查询,所以我们可以提供一个区间限制范围来告诉引擎查询到此结束。
查询sql再次优化后参考如下:
select id,biz_content,pin FROM follow_fans_1 where biz_content = #{bizContent} and id < #{lastId} and id >={minId} order by id desc limit 10;
由于查询时需要带上 minId 参数,所以在执行查询粉丝列表之前,我们就需要先把 minId 查询出来,查询 sql 参考如下:
select min(id) from follow_fans_1 where biz_content = #{bizContent}
由于表中数据量太大,每个表中总数据量都是上亿级别,导致第一步查询 minId就直接超时了,根本没有机会去执行第二步。但是考虑到上一个查询方案只有最后一页才会查询超时,前N-1页查询根本用不到 minId 作为区间限制。所以当表中数据量很大时,通常从第一页到最后一页查询之间会存在一定的时间差。我们就可以正好去利用这个时间差去异步查询minId,然后将查询出来的minId存储到缓存中,考虑到这个 minId 可能会被删除,可以设置一定的过期时间。最后优化后的查询流程如下:
- 调用查询粉丝列表方法时首先查询缓存minId;
- 如果缓存minId 为空,则创建异步任务去执行select min(id) 查询表中的 minId,然后回写缓存,该异步任务执行时间可能会很长,可以单独设置超时时间。
- 如果缓存minId不为空,则在查询sql中拼接查询条件id >={minId},从而保证查询最后一页时不会超时。
但是在上述方案中,如果表中的数据量达到上亿级别时,第二步的异步获取minId任务还是会存在超时的风险,从而导致查询最后一页粉丝列表出现超时。所以我们又引入了离线数据计算任务,通过在大数据平台离线计算获取每个biz_content下的minId,然后将计算结果minId推送到缓存中。为了保证minId能够及时更新,我们可以自由设置该离线任务的执行周期,比如每周执行一次。通过大数据平台的离线计算minId,从而大大减少了在查询粉丝列表时执行 select min(id)的业务数据库压力。只有当缓存没有命中的时候才去执行 select min(id),通常这些缓存没有命中的 minId 也都是一些被离线任务遗漏的少量数据,不会影响接口的整体查询性能。
- 方案优点:避免了数据量变大时,页码查询深入的性能下降问题;经过接口压测,千万级数据量时,从第一页到最后一页都控制在几十毫秒内。
- 方案缺点:只能支持按照页码顺序和主键ID倒序查询,不支持跳页查询,并且还需要依赖大数据平台离线计算和额外的缓存来存储 minId。
三、对SQL优化治理的思考
通过对以上三种方案的探索实践,发现每一种方案都有自己的优缺点和它的适用场景,我们不能脱离实际业务场景去谈方案的好坏。所以我们要结合实际的业务环境以及表中数据量的大小去综合考虑、权衡利弊,然后找到更适合的技术方案。以下是总结的几条SQL优化建议:
【 查询条件一定要有索引 】
索引主要分为两大类,聚簇索引和非聚簇索引,可以通过 explain 查看 sql 执行计划判断查询是否使用了索引。
聚簇索引 (clustered index):聚簇索引的叶子节点存储行记录,InnoDB必须要有且只有一个聚簇索引:
-
如果表定义了主键,则主键索引就是聚簇索引;
-
如果没有定义主键,则第一个非空的唯一索引列是聚簇索引;
-
如果没有唯一索引,则创建一个隐藏的row-id列作为聚簇索引。主键索引查询非常快,可以直接定位行记录。
非聚簇索引 (secondary index):InnoDB非聚簇索引的叶子节点存储的是行记录的主键值,而MyISAM叶子节点存储的是行指针。通常情况下,需要先遍历非聚簇索引获得聚簇索引的主键ID,然后在遍历聚簇索引获取对应行记录。
【 正确使用索引,防止索引失效 】
可以参考以下几点索引原则:
-
最左前缀匹配原则,mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配,比如 a=1 and b=2 and c>3 and d=4 ,如果建立了(a,b,c,d)顺序的索引,d是用不到索引的,如果建立(a,b,d,c)的索引则都可以用到,a、b、d的顺序可以任意调整。
-
=和in可以乱序,比如 a=1 and b=2 and c=3 建立(a,b,c)索引可以任意顺序,mysql的查询优化器会帮助优化成索引可以识别的形式。
-
尽量选择区分度高德列作为索引,区分度公式count(distinct col)/count(*),表示字段不重复的比例。
-
索引列不能使用函数或参与计算,不能进行类型转换,否则索引会失效。
-
尽量扩展索引,不要新建索引。
【 减少查询字段,避免回表查询 】
回表查询就是先定位主键值,在根据主键值定位行记录,需要扫描两遍索引。解决方案:只需要在一棵索引树上能够获取SQL所需要的所有列数据,则无需回表查询,速度更快。可以将要查询的字段,建立到联合索引里去,这就是索引覆盖。查询sql在进行explain解析时,Extra字段为Using Index时,则触发索引覆盖。没有触发索引覆盖,发生了回表查询时,Extra字段为Using Index condition。
- END -
如何知道是否全表扫描
MySQL :: MySQL 8.0 Reference Manual :: 8.2.1.23 Avoiding Full Table Scans https://dev.mysql.com/doc/refman/8.0/en/table-scan-avoidance.html
explain 语句
The output from EXPLAIN
shows ALL
in the type
column when MySQL uses a full table scan to resolve a query.
慢 SQL 分析与优化 https://mp.weixin.qq.com/s/CaSVhAJgycjjbCxAkII2ZA
从系统设计角度看,一个系统从设计搭建到数据逐步增长,SQL 执行效率可能会出现劣化,为继续支撑业务发展,我们需要对慢 SQL 进行分析和优化,严峻的情况下甚至需要对整个系统进行重构。所以我们往往需要在系统设计前对业务进行充分调研、遵守系统设计规范,在系统运行时定期结合当前业务发展情况进行系统瓶颈的分析。
从数据库角度看,每个 SQL 执行都需要消耗一定 I/O 资源,SQL 执行的快慢,决定了资源被占用时间的长短。假如有一条慢 SQL 占用了 30%的资源共计 1 分钟。那么在这 1 分钟时间内,其他 SQL 能够分配的资源总量就是 70%,如此循环,当资源分配完的时候,所有新的 SQL 执行将会排队等待。所以往往一条慢 SQL 会影响到整个业务。
本文仅讨论 MySQL-InnoDB 的情况。
优化方式
SQL 语句执行效率的主要因素
- 数据量
- SQL 执行后返回给客户端的数据量的大小;
- 数据量越大需要扫描的 I/O 次数越多,数据库服务器的 IO 更容易成为瓶颈。
- 取数据的方式
- 数据在缓存中还是在磁盘上;
- 是否能够通过全局索引快速寻址;
- 是否结合谓词条件命中全局索引加速扫描。
- 数据加工的方式
- 排序、子查询、聚合、关联等,一般需要先把数据取到临时表中,再对数据进行加工;
- 对于数据量比较多的计算,会消耗大量计算节点的 CPU 资源,让数据加工变得更加缓慢;
- 是否选择了合适的 join 方式
优化思路
- 减少数据扫描(减少磁盘访问)
- 尽量在查询中加入一些可以提前过滤数据的谓词条件,比如按照时间过滤数据等,可以减少数据的扫描量,对查询更友好;
- 在扫描大表数据时是否可以命中索引,减少回表代价,避免全表扫描。
- 返回更少数据(减少网络传输或磁盘访问)
- 减少交互次数(减少网络传输)
- 将数据存放在更快的地方
- 某条查询涉及到大表,无法进一步优化,如果返回的数据量不大且变化频率不高但访问频率很高,此时应该考虑将返回的数据放在应用端的缓存当中或者 Redis 这样的缓存当中,以提高存取速度。
- 减少服务器 CPU 开销(减少 CPU 及内存开销)
- 避免大事务操作
- 利用更多资源(增加资源)
优化案例
数据分页优化
select * from table_demo where type = ? limit ?,?;
优化方式一:偏移 id
lastId = 0 or min(id)
do {
select * from table_demo where type = ? and id >{#lastId} limit ?;
lastId = max(id)
} while (isNotEmpty)
优化方式二:分段查询
该方式较方式一的优点在于可并行查询,每个分段查询互不依赖;较方式一的缺点在于较依赖数据的连续性,若数据过于分散,代价较高。
minId = min(id) maxId = max(id)
for(int i = minId; i<= maxId; i+=pageSize){
select * from table_demo where type = ? and id between i and i+ pageSize;
}
优化 GROUP BY
提高 GROUP BY 语句的效率, 可以通过将不需要的记录在 GROUP BY 之前过滤掉.下面两个查询返回相同结果但第二个明显就快了许多。
低效:
select job , avg(sal) from table_demo group by job having job = ‘manager'
高效:
select job , avg(sal) from table_demo where job = ‘manager' group by job
范围查询
联合索引中如果有某个列存在范围(大于小于)查询,其右边的列是否还有意义?
explain select count(1) from statement where org_code='1012' and trade_date_time >= '2019-05-01 00:00:00' and trade_date_time<='2020-05-01 00:00:00'
explain select * from statement where org_code='1012' and trade_date_time >= '2019-05-01 00:00:00' and trade_date_time<='2020-05-01 00:00:00' limit 0, 100
explain select * from statement where org_code='1012' and trade_date_time >= '2019-05-01 00:00:00' and trade_date_time<='2020-05-01 00:00:00'
-
使用单键索引 trade_date_time 的情况下
- 从索引里找到所有 trade_date_time 在'2019-05-01' 到'2020-05-01' 区间的主键 id。假设有 100 万个。
- 对这些 id 进行排序(为的是在下面一步回表操作中优化 I/O 操作,因为很多挨得近的主键可能一次磁盘 I/O 就都取到了)
- 回表,查出 100 万行记录,然后逐个扫描,筛选出 org_code='1020'的行记录
-
使用联合索引 trade_date_time, org_code -联合索引 trade_date_time, org_code 底层结构推导如下:
以查找 trade_date_time >='2019-05-01' and trade_date_time <='2020-05-01' and org_code='1020'为例:
- 在范围查找的时候,直接找到最大,最小的值,然后进行链表遍历,故仅能用到 trade_date_time 的索引,无法使用到 org_code 索引
- 基于 MySQL5.6+的索引下推特性,虽然 org_code 字段无法使用到索引树,但是可以用于过滤回表的主键 id 数。
小结:对于该 case, 索引效果[org_code,trade_date_time] > [trade_date_time, org_code]>[trade_date_time]。实际业务场景中,检索条件中 trade_date_time 基本上肯定会出现,但 org_code 却不一定,故索引的设计还需要结合实际业务需求。
优化 Order by
索引:
KEY `idx_account_trade_date_time` (`account_number`,`trade_date_time`),
KEY `idx_trade_date_times` (`trade_date_time`)
KEY `idx_createtime` (`create_time`),
慢 SQL:
SELECT id,....,creator,modifier,create_time,update_time FROM statement
WHERE (account_number = 'XXX' AND create_time >= '2022-04-24 06:03:44' AND create_time <= '2022-04-24 08:03:44' AND dc_flag = 'C') ORDER BY trade_date_time DESC,id DESC LIMIT 0,1000;
优化前:SQL 执行超时被 kill 了
SELECT id,....,creator,modifier,create_time,update_time FROM statement
WHERE (account_number = 'XXX' AND create_time >= '2022-04-24 06:03:44' AND create_time <= '2022-04-24 08:03:44' AND dc_flag = 'C') ORDER BY create_time DESC,id DESC LIMIT 0,1000;
优化后:执行总行数为:6 行,耗时 34ms。
MySQL使不使用索引与所查列无关,只与索引本身,where条件,order by 字段,group by 字段有关。索引的作用一个是查找,一个是排序。
业务拆分
select * from order where status='S' and update_time < now-5min limit 500
拆分优化:
随着业务数据的增长 status='S'的数据基本占据数据的 90%以上,此时该条件无法走索引。我们可以结合业务特征,对数据获取按日期进行拆分。
date = now; minDate = now - 10 days
while(date > minDate) {
select * from order where order_date={#date} and status='S' and update_time < now-5min limit 500
date = data + 1
}
数据库结构优化
- 范式优化:表的设计合理化(符合 3NF),比如消除冗余(节省空间);
- 反范式优化:比如适当加冗余等(减少 join)
- 拆分表:分区将数据在物理上分隔开,不同分区的数据可以制定保存在处于不同磁盘上的数据文件里。这样,当对这个表进行查询时,只需要在表分区中进行扫描,而不必进行全表扫描,明显缩短了查询时间,另外处于不同磁盘的分区也将对这个表的数据传输分散在不同的磁盘 I/O,一个精心设置的分区可以将数据传输对磁盘 I/O 竞争均匀地分散开。对数据量大的表可采取此方法,可按月建表分区。
SQL 语句优化
SQL 检查状态及分数计算逻辑
- 尽量避免使用子查询
- 用 IN 来替换 OR
- 读取适当的记录 LIMIT M,N,而不要读多余的记录
- 禁止不必要的 Order By 排序
- 总和查询可以禁止排重用 union all
- 避免随机取记录
- 将多次插入换成批量 Insert 插入
- 只返回必要的列,用具体的字段列表代替 select * 语句
- 区分 in 和 exists
- 优化 Group By 语句
- 尽量使用数字型字段
- 优化 Join 语句
大表优化
- 分库分表(水平、垂直)
- 读写分离
- 数据定期归档
原理剖析
MySQL 逻辑架构图:
索引的优缺点
优点
- 提高查询语句的执行效率,减少 IO 操作的次数
- 创建唯一性索引,可以保证数据库表中每一行数据的唯一性
- 加了索引的列会进行排序,在使用分组和排序子句进行查询时,可以显著减少查询中分组和排序的时间
缺点
- 索引需要占物理空间
- 创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加
- 当对表中的数据进行增删改查时,索引也要动态的维护,这样就降低了数据的更新效率
索引的数据结构
主键索引
普通索引
组合索引
索引页结构
索引页由七部分组成,其中 Infimum 和 Supremum 也属于记录,只不过是虚拟记录,这里为了与用户记录区分开,还是决定将两者拆开。
数据行格式:
MySQL 有 4 种存储格式:
- Compact
- Redundant (5.0 版本以前用,已废弃)
- Dynamic (MySQL5.7 默认格式)
- Compressed
Dynamic 行存储格式下,对于处理行溢出(当一个字段存储长度过大时,会发生行溢出)时,仅存放溢出页内存地址。
索引的设计原则
哪些情况适合建索引
- 数据又数值有唯一性的限制
- 频繁作为 where 条件的字段
- 经常使用 group by 和 order by 的字段,既有 group by 又有 order by 的字段时,建议建联合索引
- 经常作为 update 或 delete 条件的字段
- 经常需要 distinct 的字段
- 多表连接时的字段建议创建索引,也有注意事项
- 连接表数量最好不要超过 3 张,每增加一张表就相当于增加了一次嵌套循环,数量级增长会非常快
- 对多表查询时的 where 条件创建索引
- 对连接字段创建索引,并且数据类型保持一致
- 在确定数据范围的情况下尽量使用数据类型较小的,因为索引会也会占用空间
- 对字符串创建索引时建议使用字符串的前缀作为索引
- 这样做的好处是:
- 能节省索引的空间,
- 虽然不能精确定位,但是能够定位到相同的前缀,然后通过主键查询完整的字符串,这样既能节省空间,又减少了字符串的比较时间,还能解决排序问题。
- 区分度高(散列性高)的字段适合作为索引。
- 在多个字段需要创建索引的情况下,联合索引优先于单值索引。使用最频繁的列作为索引的最左侧 。
哪些情况下不需要使用索引
- 在 where 条件中用不到的字段不需要。
- 数据量小的不需要建索引,比如数据少于 1000 条。
- 由大量重复数据的列上不要建索引,比如性别字段中只有男和女时。
- 避免在经常更新的表或字段中创建过多的索引。
- 不建议主键使用无序的值作为索引,比如 uuid。
- 不要定义冗余或重复的索引
- 例如:已经创建了联合索引 key(id,name)后就不需要再单独建一个 key(id)的索引
索引优化之 MRR
例如有一张表 user,主键 id,普通字段 age,为 age 创建非聚集索引,有一条查询语句 select* user from table where age > 18;(注意查询语句中的结果是*)
在 MySQL5.5 以及之前的版本中如何查询呢?先通过非聚集索引查询到 age>18 的第一条数据,获取到了主键 id;然后根据非聚集索引中的叶子节点存储的主键 id 去聚集索引中查询行数据;根据 age>18 的数据条数每次查询聚集索引,这个过程叫做回表。
上述的步骤有什么缺点呢?如何 age>18 的数据非常多,那么每次回表都需要经过 3 次 IO(假设 B+树的高度是 3),那么会导致查询效率过低。
在 MySQL5.6 时针对上述问题进行了优化,优化器先查询到 age>3 的所有数据的主键 id,对所有主键的 id 进行排序,排序的结果缓存到 read_rnd_buffer,然后通过排好序的主键在聚簇索引中进行查询。
如果两个主键的范围相近,在同一个数据页中就可以之间按照顺序获取,那么磁盘 io 的过程将会大大降低。这个优化的过程就叫做 Multi Range Read(MRR) 多返回查询。
索引下推
假设有索引(name, age), 执行 SQL: select * from tuser where name like '张%' and age=10;
MySQL 5.6 以后, 存储引擎根据(name,age)联合索引,找到,由于联合索引中包含列,所以存储引擎直接在联合索引里按照age=10
过滤。按照过滤后的数据再一一进行回表扫描。
索引下推使用条件
- 只能用于
range
、ref
、eq_ref
、ref_or_null
访问方法; - 只能用于
InnoDB
和MyISAM
存储引擎及其分区表; - 对存储引擎来说,索引下推只适用于二级索引(也叫辅助索引);
索引下推的目的是为了减少回表次数,也就是要减少 IO 操作。对于的聚簇索引来说,数据和索引是在一起的,不存在回表这一说。
- 引用了子查询的条件不能下推;
- 引用了存储函数的条件不能下推,因为存储引擎无法调用存储函数。
思考:
- MySQL 一张表到底能存多少数据?
- 为什么要控制单行数据大小?
- 优化案例 4 中优化前的 SQL 为什么走不到索引?
总结
抛开数据库硬件层面,数据库表设计、索引设计、业务代码逻辑、分库分表策略、数据归档策略都对 SQL 执行效率有影响,我们只有在整个设计、开发、运维阶段保持高度敏感、追求极致,才能让我们系统的可用性、伸缩性不会随着业务增长而劣化。
参考资料
- https://help.aliyun.com/document_detail/311122.html
- https://blog.csdn.net/qq_32099833/article/details/123150701
- https://www.cnblogs.com/tufujie/p/9413852.html
MySQL慢SQL探究 https://mp.weixin.qq.com/s/n-YRgv9uDSvcD4122hJs8g
前言
我们在日常开发中,一定遇见过某些SQL
执行较慢的情况,我们俗称“慢SQL
”,如果你对系统的接口性能要求较高的话,一定不会放过这种SQL
,肯定会想办法进行解决,那么,导致慢 SQL 出现的原因,究竟可能都有哪些呢?这是一道经典的面试题,就此我们来研究一番,下面,我们就来好好看一下,原因可能出在哪里。本篇我们将从如下几个方面进行讨论:
1.慢SQL
捕获
2.执行计划分析
3.引擎参数配置分析
让我们就此开启本次慢SQL
分析之旅,Let's go!
ps: 本篇文章的讨论,主要基于MySQL8.0数据库,Oracle等其他数据库不在本篇讨论范围之列。
慢SQL捕获
追查应用服务的慢SQL
,首先需要追踪哪些SQL
可能是慢SQL
,对于Java
服务,很多数据库中间件提供了慢SQL
的追踪能力,例如Alibaba Druid
,会将服务运行过程中的慢SQL
打印到日志文件,方便开发运维人员追查。MySQL
当然也提供了捕获慢查询的监控能力,记录在MySQL
中执行时间超过指定时间的SQL
语句。默认情况下,MySQL
并没有开启慢日志,可以通过修改slow_query_log
参数来打开慢日志。与慢日志相关的参数介绍如下:
- slow_query_log:是否启用慢查询日志,1表示开启,0表示关闭,默认为关闭。
- slow_query_log_file:指定慢查询日志位置及名称,默认值为
host_name-slow.log
,可指定绝对路径。 - long_query_time:慢查询执行时间阈值,超过此时间会记录,默认为10,取值范围0~31536000,单位为秒。
- min_examined_row_limit:对于查询扫描行数小于此参数的
SQL
,将不会记录到慢查询日志中,默认为0,最大值(bit-64)为18446744073709551615。 - log_output:慢查询日志输出目标,默认为
FILE
,即输出到文件,取值为TABLE
、FILE
、NONE
。 - log_timestamps:主要是控制
error log
、slow log
、genera log
日志文件中的显示时区,默认使用UTC
时区,取值为UTC
、SYSTEM
,建议改为SYSTEM
系统时区。 - log_queries_not_using_indexes:是否记录所有未使用索引的查询语句,默认为
OFF
。 - log_slow_admin_statements:慢速管理语句是否写入慢日志中,管理语句包含
ALTER TABLE
,ANALYZE TABLE
,CHECK TABLE
,CREATE INDEX
,DROP INDEX
,OPTIMIZE TABLE
,REPAIR TABLE
,默认为OFF
即不写入。
一般情况下,我们只需开启慢日志记录,配置下阈值时间,其余参数可按默认配置。对于阈值时间,可灵活调整,比如说可以设置为 1s 或 3s 。
慢查询追踪配置方式
MySQL
提供了两种配置慢查询参数的方式,提供给开发者使用,下面我们依次来看一下。第一种,将慢查询参数配置写入MySQL
配置文件,永久生效:
# 慢查询日志相关配置,可根据实际情况修改
vim /etc/my.cnf
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/sql-slow.log
long_query_time = 1
log_timestamps = SYSTEM
log_output = FILE
第二种,MySQL Server
中临时开启慢查询功能,当MySQL Server
重启时,配置修改则全部失效并恢复原状:
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/sql-slow.log';
SET GLOBAL log_queries_not_using_indexes = 'ON';
SET SESSION long_query_time = 1;
SET SESSION min_examined_row_limit = 100;
如何查看下慢查询日志是否开启,以及慢查询日志文件的位置:
mysql> show variables like '%slow_query_log%';
+---------------------+-----------------------------------------+
| Variable_name | Value |
+---------------------+-----------------------------------------+
| slow_query_log | ON |
| slow_query_log_file | /var/lib/mysql/VM-16-14-centos-slow.log |
+---------------------+-----------------------------------------+
2 rows in set (0.00 sec)
下面我们具体看下,慢日志会记录哪些内容?我们执行一条较慢的查询SQL
,来看下在慢日志中的体现。
# Time: 2022-11-02T09:23:37.004885Z
# User@Host: wtopps[wtopps] @ localhost [] Id: 10831
# Query_time: 1.609214 Lock_time: 0.003828 Rows_sent: 2050008 Rows_examined: 2150010
SET timestamp=1667381015;
SELECT A.* FROM `user` A LEFT JOIN grade B ON A.`id` = B.`user_id`;
对于每一组慢SQL
,日志内容格式如下:第一行记录的是该条SQL
执行的时刻(如果log_timestamps
参数为UTC
,则改时间会显示UTC
时区时间)第二行记录的是执行该语句的用户和IP
以及链接id
第三行的几个字段含义如下:
- Query_time:语句执行时间,以秒为单位。
- Lock_time:获取锁的时间(以秒为单位)。
- Rows_sent:发送给
Client
端的行数。 - Rows_examined:服务器层检查的行数(不计算存储引擎内部的任何处理)
在8.0.14及以上版本可以打开log_slow_extra
系统参数,收集更多信息。
mysql>set global log_slow_extra=on
# Time: 2023-02-10T13:07:50.617272Z
# User@Host: wtopps[wtopps] @ [111.197.236.164] Id: 19187
# Query_time: 91.511261 Lock_time: 0.000124 Rows_sent: 2050008 Rows_examined: 2150010 Thread_id: 19187 Errno: 0 Killed: 0 Bytes_received: 0 Bytes_sent: 58061725 Read_first: 2 Read_last: 0 Read_key: 2 Read_next: 0 Read_prev: 0 Read_rnd: 0 Read_rnd_next: 2150012 Sort_merge_passes: 0 Sort_range_count: 0 Sort_rows: 0 Sort_scan_count: 0 Created_tmp_disk_tables: 0 Created_tmp_tables: 0 Start: 2023-02-10T13:06:19.106011Z End: 2023-02-10T13:07:50.617272Z
SET timestamp=1676034379;
SELECT A.* FROM `user` A LEFT JOIN grade B ON A.`id` = B.`user_id`;
可以看到,当开启log_slow_extra
参数后,慢查询日志中出现了大量的额外信息,其含义如下:
Thread_id:连接的标识;
Errno:SQL错误号,0表示没有错误;
Killed:语句终止的错误号,0表示正常终止;
Bytes_received/sent:收到和发送的字节数;
Read_first:Handler_read_first的值,代表读取索引中第一个条目的次数。反映查询全索引扫描的次数。
Read_last:读取索引最后一个key的次数;
Read_key:基于key读取行的请求数,较大说明使用正确的索引
Read_next:按顺序取下一行数据的次数,索引范围查找和索引扫描时该值会增大;
Read_prev:按顺序读取上一行的请求数,order by desc查询较优时该值较大;
Read_rnd:按固定位置读取行的请求数,大量的回表、没有索引的连接和对结果集排序时会增加;
Read_rnd_next:读取数据文件下一行的次数,大量表扫描、未创建或合理使用索引时会增加;
Sort_range_count:使用范围完成的排序次数;
Sort_rows:排序的行数;
Sort_scan_count:通过扫描表完成的排序次数;
Sort_merge_passes:排序算法合并的次数,如该值较大考虑增加sort_buffer_size的值
Created_tmp_disk_tables:创建内部磁盘临时表的数量;
Created_tmp_tables:创建内部临时表的数量;
Start/End:语句开始和结束时间
Tips:在MariaDB中,可以开启log_slow_verbosity参数,可以更加详尽的打印出慢SQL的执行细节,该参数在MySQL8.0版本中并未支持,读者感兴趣可以自行查阅相关信息。
通过慢查询日志,我们可以捕获到具体的慢SQL
,接下来,则要具体分析慢SQL
产生的可能原因。
情况分析
为什么查询会慢?
在尝试编写快速的查询之前,需要清楚一点,真正重要是响应时间。如果把查询看作是一个任务,那么它由一系列子任务组成,每个子任务都会消耗一定的时间。如果要优化查询,实际上要优化其子任务,要么消除其中一些子任务,要么减少子任务的执行次数,要么让子任务运行得更快。MySQL
在执行查询的时候有哪些子任务,哪些子任务运行的速度很慢?这里很难给出完整的列表,通常来说,查询的生命周期大致可以按照顺序来看:从客户端,到服务器,然后在服务器上进行解析,生成执行计划,执行,并返回结果给客户端。其中“执行”可以认为是整个生命周期中最重要的阶段,这其中包括了大量为了检索数据到存储引擎的调用以及调用后的数据处理,包括排序、分组等。mysql执行过程在完成这些任务的时候,查询需要在不同的地方花费时间,包括网络,CPU
计算,生成统计信息和执行计划、锁等待(互斥等待)等操作,尤其是向底层存储引擎检索数据的调用操作,这些调用需要在内存操作、CPU
操作和内存不足时导致的I/O
操作上消耗时间。根据存储引擎不同,可能还会产生大量的上下文切换以及系统调用。在每一个消耗大量时间的查询案例中,大概率会出现一些不必要的额外操作、某些操作被额外地重复了很多次、某些操作执行得太慢等。优化查询的目的就是减少和消除这些操作所花费的时间。再次申明一点,对于一个查询的全部生命周期,上面列的并不完整。这里我们只是想说明:了解查询的生命周期、清楚查询的时间消耗情况对于优化查询有很大的意义。有了这些概念,我们再一起来看看如何优化查询。捕获具体的慢查询SQL
后,我们需要对可能导致慢查询的原因进行分析,我们可以从如下几个角度,对问题进行拆解:
SQL
执行计划分析- 引擎参数配置分析
- 引擎参数配置分析(网络,物理机配置,内存,机器负载
I/O
)
SQL执行计划分析
查询性能低下最基本的原因是访问的数据太多。某些查询可能不可避免地需要筛选大量数据,但在实际业务场景中,这并不常见。大部分性能低下的查询都可以通过减少访问的数据量的方式进行优化。对于低效的查询,我们发现通过下面两个步骤来分析总是很有效:1.确认应用程序是否在检索大量超过需要的数据。这通常意味着访问了太多的行,但有时候也可能是访问了太多的列。2.确认MySQL
服务器层是否在分析大量超过需要的数据行。有些查询会请求超过实际需要的数据,然后这些多余的数据会被应用程序丢弃。这会给MySQL
服务器带来额外的负担,并增加网络开销,另外也会消耗应用服务器的CPU
和内存资源。因此,合理的使用索引的重要性就凸显出来,如果查询中的查询条件未命中索引字段,MySQL
引擎则只能对全量的数据进行检索,再根据查询条件进行过滤,筛选出目标的数据集,这个过程是非常耗时且低效的。接下来,我们将逐步对SQL
执行的过程进行分析拆解,通过工具手段剖析慢查询的具体原因。SQL分析三步走
explain执行计划分析
对于SQL
执行过程分析,最先登场的毫无疑问就是explain
语句了,explain
是我们在日常开发最常使用的分析命令。其使用方式,这里不再赘述,一般来说,95% 的慢查询问题只需要explain
就可以解决了。对于explain
执行计划的分析,我们需要关注最简单的衡量查询开销的两个点:
- 扫描的行数
- 返回的行数
通过explain
执行计划可以获得SQL
在执行时预估的扫描行数以及返回行数的大概比例,这在一定程度上能够说明该查询找到需要的数据的效率高不高。但这个指标也并不是绝对的,因为并不是所有的行的访问代价都是相同的。较短的行的访问速度更快,内存中的行也比磁盘中的行的访问速度要,快得多。理想情况下扫描的行数和返回的行数应该是相同的。但实际情况中这种情况并不多见。例如在做一个多表的join
关联查询时,服务器必须要扫描多行才能生成结果集中的一行。扫描的行数对返回的行数的比率通常很小,一般在1:1和10:1之间,不过有时候这个值也可能非常非常大。比值越大则意味着查询效率越低,因为引擎执行扫描出的大部分数据行都会被丢弃,这也意味着需要执行更多的I/O操作,因此尽可能降低扫描的行数对返回的行数的比率,是我们对慢查询优化的关键所在。一般MySQL
能够使用如下三种方式应用where
条件,从好到坏依次为:
- 在索引中使用
where
条件来过滤不匹配的记录。这是在存储引擎层完成的。 - 使用索引覆盖扫描(在
Extra
列中出现了Using index
)来返回记录,直接从索引中过滤不需要的记录并返回命中的结果。这是在MySQL
服务器层完成的,但无须再回表查询记录。 - 从数据表中返回数据,然后过滤不满足条件的记录(在
Extra
列中出现Using Where
)。这在MySQL
服务器层完成,MySQL
需要先从数据表读出记录然后过滤。
Extra中Using Index与Using Where,MySQL官方文档的解释如下:Using IndexThe column information is retrieved from the table using only information in the index tree without having to do an additional seek to read the actual row. This strategy can be used when the query uses only columns that are part of a single index.Using WhereA WHERE clause is used to restrict which rows to match against the next table or send to the client. Unless you specifically intend to fetch or examine all rows from the table, you may have something wrong in your query if the Extra value is not Using where and the table join type is ALL or index.
使用explain
判断导致查询慢的原因,判断依据可以根据如下几点:
where
查询条件中的字段,是否是索引字段,索引字段是否满足了最左匹配原则where
查询条件中是否对索引字段使用了函数处理
- 对索引字段使用函数操作,会使得索引失效
where
查询条件中是否存在like %字段%
情况
like%%
的全模糊匹配,会使得索引失效,如需使用like
,请使用like字段%
- 对于
select *
的查询,该表的字段数量为多少
- 对于巨型宽表,例如上百个字段的大表,
select *
是效率低下的选择,实际业务中很少有情况会需要全部字段的情况,根据需要查询特定的字段是非常必要的
where
查询条件中是否使用or
,如果使用了,or
的字段是否是主键或者索引字段
- 对于主键或索引字段,
or
与in
不存在性能差距,对于非索引字段,or
的性能会低于in
In many database servers, IN() is just a synonym for multiple OR clauses,
because the two are logically equivalent. Not so in MySQL,
which sorts the values in the IN() list and uses a fast binary search to see whether a value is in the list.
This is O(Log n) in the size of the list, whereas an equivalent series of OR clauses is O(n) in the size of the list (i.e., much slower for large lists)
- 查询是否使用了多个表(大于3张) 的
join
操作,join
表的数据量级如何,是否使用了索引字段进行查询
- 对于多表
join
的复杂联合查询,是可能产生慢SQL
的重灾区,join
子表的顺序决定了扫描结果集会有多大,需要结合explain
进行分析判断 - 实际业务场景中,也尽可能的避免多表join操作,需要在表设计阶段就做好冗余字段的考虑
where
查询条件是否使用了分页查询,分页深度是多大
limit10
,offset100000
,MySQL
在实际执行时,会查询出100010条记录,然后丢弃前100000条,性能会极为的糟糕- 对于深分页查询优化,当执行深度分页时,可以带入主键ID作为查询条件,执行下一页的查询时,将上一页最大的主键ID作为条件,
id > last_page_max_id
综上我们总结,SQL
执行时其扫描的行数决定了执行的效率,而决定扫描行数的关键,则是索引的命中情况与索引的质量。
Tips:关于索引的一些小建议
1、唯一索引命名uk字段,普通索引命名idx字段,过长时可用首字母替代
2、尽量避免三张表以上的join,对于多表join的情况,可以视情况考虑将一个大查询拆分成多个子查询,对结果集在业务层进行聚合处理。如必须要多表join的场景,特别注意多连表查询的扫描行数问题以及索引的命中情况。
3、varchar长字段建立索引,需要指定索引长度,根据文本区分度来决定长度。
4、避免左模糊,全模糊匹配。
5、order by 字段放在索引最后列,避免filesort
6、考虑利用覆盖索引来进行查询操作,避免回表
7、性能优化目标,需要为range级别以上,最好是ref级别,或者const最好。
8、区分度高的列在索引最左边。
9、避免字段类型不同造成的隐式转换,导致索引失效。例如:varchar和数字类型
10、根据大多数SQL来创建索引。
11、对于运行较久的大表,需要关注索引字段的区分度问题,当索引值出现了严重倾斜时,需要考虑优化拆分索引值。
PROFILE分析
通过使用explain
分析SQL
的执行计划,我们可以看到SQL
执行过程中是否使用索引,使用了哪些索引,索引扫描的行数等,但MySQL
的慢查询,并不一定慢在有没有索引,SQL
的执行环节中任意一环出了问题都会表现为查询变慢,所以即使执行过程命中了索引,explain
的结果也很完美,但是还是慢,怎么办?这时候,就需要profile
工具来帮忙了,这个命令可以详细的列出SQL
语句在每一个步骤消耗的时间,前提(缺点)是先执行一遍语句。PROFILE
默认是关闭的,所以需要在client
端先打开,操作如下:
set session profiling = 1;
在实际的生产环境中,可能会需要加大profile
的队列,保证想要查看的profile
结果还保存着,因此可以用如下操作来增加profile
的队列大小:
set session profiling_history_size = 50;
让我们一起来看一下profile
分析如何使用,我们先执行下面的SQL
:
mysql> explain select * from user where name = '小六' and code = 300000003;
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| 1 | SIMPLE | user | NULL | ALL | NULL | NULL | NULL | NULL | 2043040 | 1.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
这个一个典型的效果较差的查询,type=ALL
,没有命中索引,执行了全表扫描,我们使用profile
分析一下各阶段的执行时间:
mysql> show profiles;
+----------+------------+---------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+---------------------------------------------------------------+
| 1 | 0.55695825 | select * from user where name = '小六' and code = 300000003 |
+----------+------------+---------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)
找到我们需要分析的语句,查询执行耗时详情:
mysql> show profile block io,cpu,memory,source for query 1;
profile执行耗时详情从profile
执行结果中,我们可以清晰的看到一条SQL在每个执行阶段的耗时、CPU
使用率、IO
等指标,帮助我们定位到慢查询具体执行耗时的阶段,对于该条SQL
,执行过程中最耗时的部分是executing
部分,executing
阶段包括了执行线程正在为SELECT
读取和处理数据行,并将数据发送到客户端。因为在这个状态下发生的操作往往执行大量的磁盘读取,所以它往往是在整个查询的生命周期中运行时间最长的一个阶段。经过了对SQL
语句进行explain
与profile
分析之后,一个SQL
为什么慢,慢在哪里基本上可以定位出来了,那么最后的手段主要是解决什么问题呢?我们将使用最终的分析工具,OPTIMIZER_TRACE
。
OPTIMIZER_TRACE分析
OPTIMIZER_TRACE
是MySQL 5.6
添加的新功能,这个功能可以看到内部查询计划的TRACE
信息,也就是MySQL
在执行过程中的具体决策细节,从而可以知道MySQL
是如何在众多索引中的挑选最合适的索引。如果我们通过执行计划,发现MySQL
选择的索引并不符合我们的预期,就需要根据OPTIMIZER_TRACE
的信息来判断为什么会选择它,是MySQL
的配置原因,还是SQL
某些地方写的不好导致MySQL
误判。开启这个功能的方式如下:
set session optimizer_trace='enabled=on';
在客户端执行一个EXPLAIN
语句,生成一个执行计划,然后在information_chema.optimizer_trace
的表里面查找这一条语句对应的信息:
mysql> select * from information_schema.optimizer_trace;
| explain select * from user where age = 21 | {
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `user`.`id` AS `id`,`user`.`name` AS `name`,`user`.`age` AS `age`,`user`.`code` AS `code` from `user` where (`user`.`age` = 21)"
}
]
}
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "(`user`.`age` = 21)",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "multiple equal(21, `user`.`age`)"
},
{
"transformation": "constant_propagation",
"resulting_condition": "multiple equal(21, `user`.`age`)"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "multiple equal(21, `user`.`age`)"
}
]
}
},
{
"substitute_generated_columns": {
}
},
{
"table_dependencies": [
{
"table": "`user`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
]
}
]
},
{
"ref_optimizer_key_uses": [
{
"table": "`user`",
"field": "age",
"equals": "21",
"null_rejecting": true
}
]
},
{
"rows_estimation": [
{
"table": "`user`",
"range_analysis": {
"table_scan": {
"rows": 2043040,
"cost": 205676
},
"potential_range_indexes": [
{
"index": "PRIMARY",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_age",
"usable": true,
"key_parts": [
"age",
"id"
]
}
],
"setup_range_conditions": [
],
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
},
"skip_scan_range": {
"potential_skip_scan_indexes": [
{
"index": "idx_age",
"usable": false,
"cause": "query_references_nonkey_column"
}
]
},
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "idx_age",
"ranges": [
"21 <= age <= 21"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": true,
"using_mrr": false,
"index_only": false,
"in_memory": 0.788627,
"rows": 1,
"cost": 0.61,
"chosen": true
}
],
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
}
},
"chosen_range_access_summary": {
"range_access_plan": {
"type": "range_scan",
"index": "idx_age",
"rows": 1,
"ranges": [
"21 <= age <= 21"
]
},
"rows_for_plan": 1,
"cost_for_plan": 0.61,
"chosen": true
}
}
}
]
},
{
"considered_execution_plans": [
{
"plan_prefix": [
],
"table": "`user`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "idx_age",
"rows": 1,
"cost": 0.35,
"chosen": true
},
{
"access_type": "range",
"range_details": {
"used_index": "idx_age"
},
"chosen": false,
"cause": "heuristic_index_cheaper"
}
]
},
"condition_filtering_pct": 100,
"rows_for_plan": 1,
"cost_for_plan": 0.35,
"chosen": true
}
]
},
{
"attaching_conditions_to_tables": {
"original_condition": "(`user`.`age` = 21)",
"attached_conditions_computation": [
],
"attached_conditions_summary": [
{
"table": "`user`",
"attached": "(`user`.`age` = 21)"
}
]
}
},
{
"finalizing_table_conditions": [
{
"table": "`user`",
"original_table_condition": "(`user`.`age` = 21)",
"final_table_condition ": null
}
]
},
{
"refine_plan": [
{
"table": "`user`"
}
]
}
]
}
},
{
"join_explain": {
"select#": 1,
"steps": [
]
}
}
]
}
1 row in set (0.02 sec)
内容是非常长的 JSON
格式,所以推荐把结果转存到其他地方,然后用JSON
的转换工具来辅助查看,如果要看索引的选择情况,就重点关注这个JSON
的ref_optimizer_key_uses
,rows_estimation
及之后的部分,这里会展示索引选择相关的信息,截取一部分结果作为示例:
{
"ref_optimizer_key_uses": [
{
"table": "`user`",
"field": "age",
"equals": "21",
"null_rejecting": true
}
]
}
......
"chosen_range_access_summary": {
"range_access_plan": {
"type": "range_scan",
"index": "idx_age",
"rows": 1,
"ranges": [
"21 <= age <= 21"
]
},
"rows_for_plan": 1,
"cost_for_plan": 0.61,
"chosen": true
}
chosen_range_access_summary
部分的含义是在前一个步骤中分析了各类索引使用的方法及代价,得出了一定的中间结果之后,在summary
阶段汇总前一阶段的中间结果确认最后的方案。
- range_access_plan:
range
扫描最终选择的执行计划。在该结构体中会给出执行计划的type
,使用的索引以及扫描行数。如果range_access_plan.type
是index_roworder_intersect
(即index merge
)的话,在该结构体下还会列intersect_of
结构体给出index merge
的具体信息。 - rows_for_plan:该执行计划的扫描行数
- cost_for_plan:该执行计划的执行代价
- chosen:是否选择该执行计划
OPTIMIZER_TRACE
的核心是在跟踪记录TRACE
的JSON
树,通过这棵树中的内容可以具体去分析优化器究竟做了什么事情,进行了哪些选择,是基于什么原因做的选择,选择的结果及依据。这一系列都可以辅助验证我们的一些观点及优化,更好的帮助我们对我们的数据库的实例进行调整。由于OPTIMIZER_TRACE
的内容非常复杂,本文由于篇幅的关系,无法在此对于每一个字段进行详细的解读,感兴趣的读者,可以参考MySQL
官方文档对于OPTIMIZER_TRACE
的解读。
引擎参数配置分析
上面的篇幅中,我们针对SQL
层面进行了可能导致慢查询的原因分析,MySQL
的数据最终都会存储在磁盘上,因此操作系统的I/O
情况也会影响MySQL
的运行性能,这一章节我们将从底层入手,从操作系统I/O
层面分析MySQL
执行性能问题。
I/O性能分析
Linux
系统查看 系统I/O
情况,可以使用iostat
命令:
[root@VM-16-14-centos ~]# iostat -x 1 -m
Linux 3.10.0-1160.11.1.el7.x86_64 (VM-16-14-centos) 12/21/2022 _x86_64_ (1 CPU)
avg-cpu: %user %nice %system %iowait %steal %idle
0.82 0.00 0.67 0.07 0.00 98.44
Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await r_await w_await svctm %util
vda 0.00 1.67 0.04 2.49 0.00 0.02 17.55 0.01 2.34 3.74 2.31 0.29 0.07
scd0 0.00 0.00 0.00 0.00 0.00 0.00 7.10 0.00 0.89 0.89 0.00 0.88 0.00
avg-cpu
自然就是CPU
相关的指标,判断IO
问题时可以关注%iowait
,其他指标的意义如下:
- r/s和w/s:合并过后的读请求和写请求的每秒请求数,可以当做
IOPS
来理解。 - rMB/s和wMB/s:磁盘的读写吞吐量。
- rrqm/s和wrqm/s:每秒合并的读请求和写请求数量。
- r_await和w_await:读请求和写请求的平均响应时间,包含真正的处理时间和队列中的等待时间(
ms
)。 - avgrq-sz:平均每次设备
I/O
操作的数据大小 (扇区)。 - avgqu-sz:平均
I/O
队列长度。 - await:平均每次设备
I/O
操作的等待时间 (毫秒)。 - scvtm:计算出来的平均
IO
响应时间,目前已经不准确,不用再关注。 - %util:如果使用了
RAID
或者SSD
,则忽略这个指标,仅在单块机械盘上准确。
一般来说,评价一块IO
设备(忽略机械盘的情况,没有评价的意义)是否达到了高负载情况,可以看这几个指标:r/s
,w/s
,rMB/s
,wMB/s
,r_await
,w_await
,avgqu-sz
。
MySQL I/O参数
MySQL
涉及到IO
相关的参数会比较多,因此这里仅一部分经常用到的参数:参数默认设置备注
innodb_io_capacity | 200 | 定义了后台任务可用的 IOPS 量,InnoDB后台任务可用的每秒I/O操作数(IOPS),例如从缓冲池冲刷页面和从变更缓冲区合并数据。 |
innodb_io_capacity_max | 动态 | 定义了后台任务可用的最大 IOPS 量 |
innodb_flush_log_at_trx_commit | 1 | 控制事务的提交策略 0:日志缓存区将每隔一秒写到日志文件中,并且将日志文件的数据刷新到磁盘上。该模式下在事务提交时不会主动触发写入磁盘的操作。 1:每次事务提交时RDS for MySQL都会把日志缓存区的数据写入日志文件中,并且刷新到磁盘中,该模式为系统默认。 2:每次事务提交时RDS for MySQL都会把日志缓存区的数据写入日志文件中,但是并不会同时刷新到磁盘上。该模式下,MySQL会每秒执行一次刷新磁盘操作。 |
sync_binlog | 1 | 控制 binlog 落盘的频率 0 :存储引擎不进行binlog的刷新到磁盘,而由操作系统的文件系统控制缓存刷新。 1:每提交一次事务,存储引擎调用文件系统的sync操作进行一次缓存的刷新,这种方式最安全,但性能较低。 n:在每N次binlog日志文件写入后与磁盘同步,存储引擎调用文件系统的sync操作进行一次缓存的刷新。 |
innodb_io_capacity
和innodb_io_capacity_max
是最直接限制IOPS
的指标,大多数时候,SSD
可以设置成16000或者更高的数值,如果是云主机或者其他的共享存储设备,则需要了解一下详细的IOPS
上限再具体调整。trx_commit
和 sync_binlog
这两个参数也放进来的原因是不同的参数组合对 IO
的压力也会有区别。 通常的用法是双 1或者20(二零),参考官方文档的描述,双 1 在每次提交事务的时候都会刷盘,对IO
的压力要高不少;20 则是滞后刷盘,对IO
的压力会较小,因此写入QPS
会高一些。另外,可以关注到一个细节,innodb_io_capacity
的描述对象是:后台任务。这代表着 MySQL
后台的 flush
,purge
操作会受到这个参数设置的限制。吞吐量和IOPS
,一般情况下衡量IO
系统性能最直观的指标,并没有特别的提及,主要原因还是判断起来很简单:如果iostat
的指标已经达到或者接近了实际硬件的指标(比如达到了 75%),那么根据业务量增长的情况及早规划硬件升级或者其他的手段来分散读写压力。常规的手段,可以简单的遵循以下场景来酌情使用:读多写少读写分离,写多读少拆库拆表加缓存。
其他原因分析
网络抖动
目前的互联网应用服务,数据库的部署与应用服务的部署,不会在一台服务器上,那么应用服务器与数据库服务器之间就需要通过网络通信,一般情况来讲,都会在同机房或同一个可用区进行部署,以保证网络通信的质量。但是难免可能会出现网络抖动的情况,这种情况下,对应用服务来讲,可能会出现一条SQL
执行较慢的情况,但不是由于数据库原因导致的,而是由于网络原因导致的。发现偶现的SQL
执行慢,且SQL
本身执行计划没有问题,可以从网络的角度分析问题,在服务所在的机器ping
数据库服务器,查看响应时间,特别针对数据库服务器与业务服务器跨城市的情况,如果没有拉通专线访问,会很容易出现网络慢导致的问题。
单表数据量过大
上面我们提到了InnoDB
的数据存储方式是面向主键索引进行数据存储的。InnoDB
的数据表数量级超过几千万后,性能会出现下降,核心是由于B+Tree
的数据结构导致的。因此,当你的数据表超过几千万量级的时候,SQL
执行即使全部命中的索引,也有可能出现执行慢的情况。这个时候,建议考虑采用拆表。目前开源的优秀的分库分表中间件有很多,例如sharing-JDBC
、MyCat
等,可以根据业务的实际情况进行选择,这里就不过多的进行赘述。
总结
本篇,我们围绕着一个问题:一条SQL
执行较慢可能有哪些原因导致的?进行了研究,总结一下,大部分的慢查询其实均由SQL
书写不当导致的,通过explain
命令结合实际业务场景分析,可以解决绝大多数的慢查询问题,对于一些疑难杂症SQL
,使用MySQL
强大的SQL
分析工具,也可以找出真正的问题原因。另外,系统层面的分析也是必不可少的,作为开发人员,我们也需要掌握一些DBA
的分析手段,检查MySQL
运行性能情况,保证数据库服务的稳定运行。
本篇参考
1.MySQL官方文档The Slow Query Log:
https://dev.mysql.com/doc/refman/5.7/en/slow-query-log.htmlhttps://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_long_query_timehttps://dev.mysql.com/doc/refman/8.0/en/explain-output.html#explain-extra-information2.MySQL or/in性能对比:https://www.cnblogs.com/chihirotan/p/7457204.html3.MySQL explain结果Extra中"Using Index"与"Using where; Using index"区别探究:https://www.cnblogs.com/AcAc-t/p/mysql_explain_difference_between_using_index_and_using_where.html4.MySQL General Thread States:https://dev.mysql.com/doc/refman/8.0/en/general-thread-states.html5.innodb_flush_log_at_trx_commit和sync_binlog参数详解:https://support.huaweicloud.com/bestpractice-rds/rds_02_0010.html6.认识OPTIMIZER_TRACE:https://www.cnblogs.com/zhoujinyi/p/13889831.html
小结:
1、
高性能的索引有哪些:
1.独立的列:索引列不能是表达式的一部分;
2.选择区分度高的列作为索引;
3.选择合适的索引列顺序:将选择性高的索引列放在最前列;
4.覆盖索引:查询的列均在索引中,不需要回查聚簇索引;
5.使用索引扫描来做排序;
6.在遵守最左前缀的原则下,尽量扩展索引,而不是创建索引。
Java开发手册(黄山版).pdf
https://sr.kaikeba.com/Java开发手册(黄山版).pdf
(二) 索引规约
1.【强制】业务上具有唯一特性的字段,即使是组合字段,也必须建成唯一索引。 说明:不要以为唯一索引影响了 insert 速度,这个速度损耗可以忽略,但提高查找速度是明显的;另外,即使在应用层 做了非常完善的校验控制,只要没有唯一索引,根据墨菲定律,必然有脏数据产生。
2.【强制】超过三个表禁止 join。需要 join 的字段,数据类型保持绝对一致;多表关联查询时,保证被关联 的字段需要有索引。 说明:即使双表 join 也要注意表索引、SQL 性能。
3.【强制】在 varchar 字段上建立索引时,必须指定索引长度,没必要对全字段建立索引,根据实际文本区 分度决定索引长度。 说明:索引的长度与区分度是一对矛盾体,一般对字符串类型数据,长度为 20 的索引,区分度会高达 90%以上,可以使 用 count(distinct left(列名,索引长度)) / count(*) 的区分度来确定。
4.【强制】页面搜索严禁左模糊或者全模糊,如果需要请走搜索引擎来解决。 说明:索引文件具有 B-Tree 的最左前缀匹配特性,如果左边的值未确定,那么无法使用此索引。
5.【推荐】如果有 order by 的场景,请注意利用索引的有序性。order by 最后的字段是组合索引的一部 分,并且放在索引组合顺序的最后,避免出现 filesort 的情况,影响查询性能。 正例:where a = ? and b = ? order by c;索引:a_b_c 反例:索引如果存在范围查询,那么索引有序性无法利用,如:WHERE a > 10 ORDER BY b;索引 a_b 无法排序。
6.【推荐】利用覆盖索引来进行查询操作,避免回表。 说明:如果一本书需要知道第 11 章是什么标题,会翻开第 11 章对应的那一页吗?目录浏览一下就好,这个目录就是起 到覆盖索引的作用。 正例:能够建立索引的种类分为主键索引、唯一索引、普通索引三种,而覆盖索引只是一种查询的一种效果,用 explain 的结果,extra 列会出现:using index。
7.【推荐】利用延迟关联或者子查询优化超多分页场景。 说明:MySQL 并不是跳过 offset 行,而是取 offset+N 行,然后返回放弃前 offset 行,返回 N 行,那当 offset 特别大 的时候,效率就非常的低下,要么控制返回的总页数,要么对超过特定阈值的页数进行 SQL 改写。 正例:先快速定位需要获取的 id 段,然后再关联: SELECT t1.* FROM 表 1 as t1 , (select id from 表 1 where 条件 LIMIT 100000 , 20) as t2 where t1.id = t2.id
8.【推荐】SQL 性能优化的目标:至少要达到 range 级别,要求是 ref 级别,如果可以是 const 最好。 说明: 1)consts 单表中最多只有一个匹配行(主键或者唯一索引),在优化阶段即可读取到数据。 Java 开发手册(黄山版) 34/51 2)ref 指的是使用普通的索引(normal index)。 3)range 对索引进行范围检索。 反例:explain 表的结果,type = index,索引物理文件全扫描,速度非常慢,这个 index 级别比较 range 还低,与全 表扫描是小巫见大巫。
9.【推荐】建组合索引的时候,区分度最高的在最左边。 正例:如果 where a = ? and b = ?,a 列的几乎接近于唯一值,那么只需要单建 idx_a 索引即可。 说明:存在非等号和等号混合判断条件时,在建索引时,请把等号条件的列前置。如:where c > ? and d = ? 那么即使 c 的区分度更高,也必须把 d 放在索引的最前列,即建立组合索引 idx_d_c。
10.【推荐】防止因字段类型不同造成的隐式转换,导致索引失效。
11.【参考】创建索引时避免有如下极端误解:
1)索引宁滥勿缺。认为一个查询就需要建一个索引。
2)吝啬索引的创建。认为索引会消耗空间、严重拖慢记录的更新以及行的新增速度。
3)抵制唯一索引。认为唯一索引一律需要在应用层通过“先查后插”方式解决。
(三) SQL 语句
1.【强制】不要使用 count(列名) 或 count(常量) 来替代 count(*),count(*) 是 SQL92 定义的标准统计行 数的语法,跟数据库无关,跟 NULL 和非 NULL 无关。 说明:count(*) 会统计值为 NULL 的行,而 count(列名) 不会统计此列为 NULL 值的行。
2.【强制】count(distinct col) 计算该列除 NULL 之外的不重复行数,注意 count(distinct col1 , col2) 如 果其中一列全为 NULL,那么即使另一列有不同的值,也返回为 0。
3.【强制】当某一列的值全是 NULL 时,count(col) 的返回结果为 0;但 sum(col) 的返回结果为 NULL,因 此使用 sum() 时需注意 NPE 问题。
正例:可以使用如下方式来避免 sum 的 NPE 问题:SELECT IFNULL(SUM(column) , 0) FROM table;
4.【强制】使用 ISNULL() 来判断是否为 NULL 值。 说明:NULL 与任何值的直接比较都为 NULL。
1)NULL<>NULL 的返回结果是 NULL,而不是 false。
2)NULL=NULL 的返回结果是 NULL,而不是 true。
3)NULL<>1 的返回结果是 NULL,而不是 true。 反例:在 SQL 语句中,如果在 null 前换行,影响可读性。 select * from table where column1 is null and column3 is not null;而 ISNULL(column) 是一个整体,简洁易懂。 从性能数据上分析,ISNULL(column) 执行效率更快一些。
5.【强制】代码中写分页查询逻辑时,若 count 为 0 应直接返回,避免执行后面的分页语句。
6.【强制】不得使用外键与级联,一切外键概念必须在应用层解决。 说明:(概念解释)学生表中的 student_id 是主键,那么成绩表中的 student_id 则为外键。如果更新学生表中的 student_id,同时触发成绩表中的 student_id 更新,即为级联更新。外键与级联更新适用于单机低并发,不适合分布式、 高并发集群;级联更新是强阻塞,存在数据库更新风暴的风险;外键影响数据库的插入速度。
7.【强制】禁止使用存储过程,存储过程难以调试和扩展,更没有移植性。
8.【强制】数据订正(特别是删除或修改记录操作)时,要先 select,避免出现误删除的情况,确认无误才 能执行更新语句。
9.【强制】对于数据库中表记录的查询和变更,只要涉及多个表,都需要在列名前加表的别名(或表名)进 行限定。
Java 开发手册(黄山版) 35/51 说明:对多表进行查询记录、更新记录、删除记录时,如果对操作列没有限定表的别名(或表名),并且操作列在多个 表中存在时,就会抛异常。
正例:select t1.name from first_table as t1 , second_table as t2 where t1.id = t2.id;
反例:在某业务中,由于多表关联查询语句没有加表的别名(或表名)的限制,正常运行两年后,最近在某个表中增加 一个同名字段,在预发布环境做数据库变更后,线上查询语句出现出 1052 异常: Column 'name' infield list is ambiguous。
10.【推荐】SQL 语句中表的别名前加 as,并且以 t1、t2、t3、...的顺序依次命名。 说明:
1)别名可以是表的简称,或者是依照表在 SQL 语句中出现的顺序,以 t1、t2、t3 的方式命名。
2)别名前加 as 使别名更容易识别。 正例:select t1.name from first_table as t1 , second_table as t2 where t1.id = t2.id;
11.【推荐】in 操作能避免则避免,若实在避免不了,需要仔细评估 in 后边的集合元素数量,控制在 1000 个之内。
12.【参考】因国际化需要,所有的字符存储与表示,均采用 utf8 字符集,那么字符计数方法需要注意。 说明: SELECT LENGTH("轻松工作");--返回为 12 SELECT CHARACTER_LENGTH("轻松工作");--返回为 4 如果需要存储表情,那么选择 utf8mb4 来进行存储,注意它与 utf8 编码的区别。
13.【参考】TRUNCATE TABLE 比 DELETE 速度快,且使用的系统和事务日志资源少,但 TRUNCATE 无事务且不触发 trigger,有可能造成事故,故不建议在开发代码中使用此语句。 说明:TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同。
慢sql治理经典案例分享 https://mp.weixin.qq.com/s/ofiJ6M6kUdnO9a1km2yEkA
慢sql治理经典案例分享
作者 | 如期
菜鸟供应链金融慢sql治理已经有一段时间,自己负责的应用持续很长时间没有慢sql告警,现阶段在推进组内其他成员治理应用慢sql。这里把治理过程中的一些实践拿出来分享下。
一 全表扫描
1 案例
SELECT count(*) AS tmp_count FROM (
SELECT * FROM `XXX_rules` WHERE 1 = 1 ORDER BY gmt_create DESC ) a
2 溯源
在分页查询治理的文章里已经介绍过我们系统旧的分页查询逻辑,上面的查询sql明显就是分页查询获取总记录数,通过XXX_rules表的分页查询接口溯源,找到发起调用的页面是我们小二后台的一个操作商家准入的页面,页面打开后直接调用分页查询接口,除了分页参数,不传入其他任何查询参数,导致扫描全表。
3 分析
灵魂拷问:为什么要扫描全表?全表数据展示到页面,花里胡哨的数据有用吗?
调研:和经常使用这个页面的运营聊后了解到,打开页面查询出的全表数据对运营是没有用的,他们根本不看这些数据。运营的操作习惯是拿到商家id,在页面查询框中输入商家id,查到商家数据后进行操作。
4 解决方案
由此优化方案就很明朗了:打开页面时不直接查询全量数据,等运营输入商家id后,将商家id作为参数进行查询。XXX_rules表中,商家id这一常用查询条件设置为索引,再结合分页查询优化,全表扫描慢sql得以解决。
优化后的小二后台页面如下:
打开页面时未查询任何数据,查询条件商家账户为必填项。
优化后的sql为:
SELECT count(*) AS tmp_count FROM (
SELECT * FROM `xxx_rules` WHERE 1 = 1 AND `rule_value` = '2928597xxx' ) a
执行EXPLAIN得到结果如下:
可以看到命中了索引,扫描行数为3,查询速度明显提高。
5 思考
扫描全表治理简单来说就是加入查询条件,命中索引,去除全表扫描查询,虽然有些粗暴,但并不是没有道理。实际业务场景中,很少有要扫描全表获取全部数据的情况,限制调用上游必须传入查询条件,且该查询条件能命中索引,能很大程度上避免慢sql。
另外,再引申下,XXX_rules初始的用意是准入表,记录金融货主维度的准入情况,最多也就几千条数据,但是很多同事将这张表理解为规则表,写入很多业务相关规则,导致这个表膨胀到一百多万条数据,表不clean了。这就涉及到数据表的设计使用,明确表的使用规范,不乱写入数据,能给后期维护带来很大的便利。
二 索引混乱
1 示例
2 分析
除了时间、操作人字段,XXX_rules表就rule_name、rule_value、status、product_code四个字段,表的索引对这四个字段做各种排列组合。存在如下问题:
1.rule_name离散度不高,放在索引首位不合适;
2.前三个索引重合度很高;
显然是对索引的命中规则不够了解。XXX_rules表很多业务有定时任务对其写入删除,索引多、混乱,对性能有很大的影响。
高性能的索引有哪些,再来回顾下:
1.独立的列:索引列不能是表达式的一部分;
2.选择区分度高的列作为索引;
3.选择合适的索引列顺序:将选择性高的索引列放在最前列;
4.覆盖索引:查询的列均在索引中,不需要回查聚簇索引;
5.使用索引扫描来做排序;
6.在遵守最左前缀的原则下,尽量扩展索引,而不是创建索引。
但凡记得第3和6规则,也不至于把索引建成这样。
3 治理
对索引进行整合如下:
系统中有很多任务拉取整个产品下的准入记录,然后进行处理,所以将区分度较高的product_code放在索引首位,然后添加rule_name、status字段到索引里,进一步过滤数据,减少扫描行数,避免慢sql。针对常用的rule_value查询条件,可以命中UK,因此不用单独建立索引。
三 非必要排序
1 问题描述
很多业务逻辑中,需要拉取满足某个条件的记录列表,查询的sql语句带有order by,记录比较多的情况,排序代价往往很大,但是查询出来的记录是否有序对业务逻辑没有影响,比如分页治理里讨论的count语句,只需要统计条数,order by对条数没有影响,再比如查出记录列表后,不依赖记录的顺序遍历列表处理数据,这时候order by多此一举。
2 解决方案
查询sql无limit语句,且业务处理逻辑不依赖于order by后列表记录的顺序,则去除查询sql中的order by语句。
四 粗粒度查询
1 问题描述
业务中有很多定时任务,扫描某个表中某个产品下所有数据,对数据进行处理,比如:
SELECT * FROM XXX_rules
WHERE rule_name = 'apf_distributors'
AND status = '00'
AND product_code = 'ADVANCE'
三个查询条件都是区分度不高的列,查出的数据有27W条,加索引意义也不大。
2 分析
实际业务量没那么大,顶多几千条数据,表里的数据是从上游同步过来的,最好的办法是让上游精简数据,但是由于业务太久远,找上游的人维护难度太大,因此只能想其他的办法。
这个定时任务目的是拉出XXX_rules表的某些产品下的数据,和另一张表数据对比,更新有差异的数据。每天凌晨处理,对时效性没有很高的要求,因此,能不能转移任务处理的地方,不在本应用机器上实时处理那么多条数据?
3 解决方案
数据是离线任务odps同步过来的,首先想到的就是dataWork数据处理平台。
建立数据对比任务,将定时任务做的数据对比逻辑放到dataWork上用sql实现,每天差异数据最多几百条,且结果集含有区分度很高的列,将差异数据写入odps表,再将数据回流到idb。
新建定时任务,通过回流回来的差异数据中区分度高的列作为查询条件查询XXX_rules,更新XXX_rules,解决了慢sql问题。
这个方法的前提是对数据实效性要求不高,且离线产出的结果集很小。
五 OR导致索引失效
1 案例
SELECT count(*)
FROM XXX_level_report
WHERE 1 = 1
AND EXISTS (
SELECT 1
FROM XXX_white_list t
WHERE (t.biz_id = customer_id
OR customer_id LIKE CONCAT(t.biz_id, '@%'))
AND t.status = 1
AND (t.start_time <= CURRENT_TIME
OR t.start_time IS NULL)
AND (t.end_time >= CURRENT_TIME
OR t.end_time IS NULL)
AND t.biz_type = 'GOODS_CONTROL_BLACKLIST'
)
2 分析
explain上述查询语句,得到结果如下:
XXX_white_list表有将biz_id作为索引,这里查询XXX_white_list表有传入biz_id作为查询条件,为啥explain结果里type为ALL,即扫描全表?索引失效了?索引失效有哪些情况?
索引失效场景
1.OR查询左右有未命中索引的;
2.复合索引不满足最左匹配原则;
3.Like以%开头;
4.需要类型转换;
5.where中索引列有运算;
6.where中索引列使用了函数;
7.如果mysql觉得全表扫描更快时(数据少时)
上述查询语句第8行,customer_id为XXX_level_report表字段,未命中XXX_white_list表索引,导致索引失效。
3 解决方案
这个语句用condition、枚举、join花里胡哨的代码拼接起来的,改起来好麻烦,而且看起来“OR customer_id LIKE CONCAT(t.biz_id, '@%')”这句不能直接删掉。最后重构了该部分的查询语句,去除or查询,解决了慢sql。
那些年我们一起优化的SQL https://mp.weixin.qq.com/s/sPO-6ULwIfUexLY3V4acBg
那些年我们一起优化的SQL
一、背景
随着业务不断迭代,系统中出现了较多的SQL慢查。慢查虽不致命,但会让商家感知到系统较慢,影响使用体验。在进行慢查优化过程中,我们积累了一些经验。本文将基于我们的实战经历,讲解工作中比较常见的慢查原因,以及如何去优化。
本文讲解基于MySQL 5.7。
二、慢查优化
本节主要针对常见的慢查进行分类,讲解怎么去优化。
2.1 建立索引的正确姿势
数据量较大的时候,如果没有索引,那么数据库只能全表一行一行的遍历判断数据,因此优化SQL的时候,第一步要做的就是确定有没有合适的可用的索引。在业务本身比较复杂的情况下,一个表会涉及各种各样的查询方式,因此我们需要建立各种各样的索引去提高查询。然而索引数量过多又会影响增删改的效率,并且也会占用更多额外的空间去存储索引,因此我们要懂得怎么去正确的建立索引,避免索引滥用。
2.1.1 索引不要包含选择性过低字段
选择性过低,即通过该字段只能过滤掉少部分的数据,是没必要建立索引的,因为如果该数据只是占小部分,即使没有索引直接查询数据表也不用过多的遍历即可找到目标数据,没有必要基于索引查询。
SQL:
select * from my_table where col_a=1 and col_b=1
索引:
index (col_a,col_b)
col_b为逻辑删除字段,包含0未删除和1已删除,数据库中的值只有很少量部分是逻辑删除的。但是在业务中我们一般都只查未删除的,那么这种情况col_b是完全不必要在索引中的,可以把col_b从组合索引中去掉。
2.1.2 选择性高的字段前置或者单独建立索引
SQL:
select * from my_table where col_a=1 and col_b=1 and col_c=1
索引:
index(col_a,col_b,col_c)
假设选择性col_c>col_b>col_a,抛开业务本身需要,组合索引建立的顺序尽可能建为index(col_c,col_b,col_a)。
原因是组合索引底层的存储先按照第一个进行排序,第一个字段相同再按照第二字段排序,如果选择性低的字段放在前面,因此选择性高的字段放前面相对而言IO的次数可能会减少一些。比如基于第一个字段过滤完会有10000条数据,基于第二个字段过滤完只有100条数据,如果先查第一个字段则需要在10000条数据的基础上再进行过滤查询,而基于第二字段过滤完只需要基于100条数据进行过滤查询即可。
而如果col_c选择性特别高,经过col_c过滤后只剩下极少的数据甚至一条数据,单独建立col_c索引就可以。
需要注意的是这个规则特别提到了抛开业务本身需要,比如如果col_a选择性比col_b高一点相差不大,但是col_b在业务场景中通用性更强,那么col_b放在前面更合适,可以减少创建的索引。
2.1.3 尽量使用覆盖索引
SQL:
SELECT sum(col_c) FROM my_table where col_a=1 and col_b=1
索引:
index(col_a,col_b)
如果col_a和col_b过滤完后还有大量数据,那么建议建一个index(col_a,col_b,col_c)索引,否则MySQL需要通过大量回表去查询col_c的数据再去求和。
2.1.4 小结
1、选择性低的字段不用建立索引。
2、具有唯一性或者高选择性的字段无需与其他字段建立组合索引。
3、除了业务需求上的考虑,尽量将选择性高的索引字段前置。
4、在经过索引过滤后数据量依旧很大的情况下可以考虑通过覆盖索引优化。
2.2 使用索引的正确姿势
除了SQL本身没有适用的索引,有了相关的索引但是对应的索引没有生效是比较常见的情况,以下列举一些常见的失效场景,在日常的开发中,我们要尽量避免。
需要注意的是,索引失效这里指的是没有利用到索引的二分查询进行数据过滤。因为存在ICP,所以会存在触发了失效场景执行计划还是显示使用了索引的情况。
2.2.1 最左匹配截断
SQL:
select * from my_table where col_b=1
select * from my_table order by col_b
索引:
index(col_a,col_b)
组合索引的匹配规则是从左往右匹配,无论是作为过滤条件还是排序条件都要遵循这个原则。如果要使用col_b字段走索引,查询条件则必须要携带col_a字段。
补充说明:
1、col_b作为排序字段如果要走索引,只要保证组合索引中col_b前面的字段都可以包含在过滤条件或者排序条件中即可,也不需要保证col_b作为组合索引中的最后一个字段。
比如:
select * from my_table order by col_a,col_b
col_a和col_b都可以走索引。
2、如果col_b是作为过滤条件,则col_b前面的字段都应该在过滤条件中。
比如:
select * from my_table where col_b=1 order by col_a
col_a和col_b都走不了索引,因为col_a在组合索引左边,但是col_a不在查询条件中。
2.2.2 隐式转换
字段类型:
col_a(VARCHAR)
col_b(DATETIME)
索引:
index1(col_a)
index2(col_b)
SQL:
select * from my_table where col_a=1
select * from my_table where col_b=1603296000000
失效原因
字段类型和查询数据的值类型不一致,会导致字段上的索引失效。
-
col_a是字符类型,使用了数字类型进行查询。
-
col_b是datetime类型,针对datetime/date/time类型,MySQL增删查改都要基于字符串形式日期去处理,否则MySQL就需要额外进行转换。(虽然底层储存的是数字类型,但是并不是存储时间戳,底层是处理是统一将外部传入的字符串进行转换,比如是date类型通过将 “2021-12-01” 字符串转数字 20211201 这种形式去存储)。
2.2.3 in + order by 导致排序失效
索引:
index(col_a,col_b)
SQL:
select * from my_table where col_a in (1,2) order by col_b
解决方式:
-
如果col_a的过滤性不高,在组合索引中可以通过将col_b字段前置,将col_a移动到组合索引后面,只用于避免或减少回表。
-
如果col_a的过滤性高,过滤后的数据相对较少,则维持当前的索引即可,剩余不多的数据通过filesort进行排序。
-
如果存在大量数据,并且经过col_b过滤后还是存在大量数据,建议基于别的数据存储实现,比如Elasticsearch。
另外SQL建议调整为只查询id(或者其他已经在索引中的字段),再根据id去查对应的数据。可以促使SQL走覆盖索引进一步优化、也可以促使MySQL底层在进行filesort使用更优的排序算法。
2.2.4 范围查询阻断组合索引
索引:
index(col_a,col_b)
SQL:
select * from table where col_a >'2021-12-01' and col_b=10
解决方式:
可以调整下索引顺序,col_a放在最后面。index(col_b,col_a)
2.2.5 后缀匹配不能走索引
索引:
index(col_a,col_b)
SQL:
select * from table where col_a=1 and col_b like '%name%'
以上SQL会导致索引失效。前缀匹配比如name%是可以走索引的,但是后缀匹配比如%name会导致没办法基于索引树进行二分查找。如果需要进行后缀匹配,数据量较大建议基于Elasticsearch实现。
2.2.6 or查询导致失效
索引:
index(col_a,col_b)
SQL:
select * from table where col_a=1 or col_b=''
or查询会导致索引失效,可以将col_a和col_b分别建立索引,利用Mysql的index merge(索引合并)进行优化。本质上是分别两个字段分别走各自索引查出对应的数据,再将数据进行合并。
2.2.7 使用函数查询或运算
索引:
index(col_a,col_b)
SQL:
select * from table where col_a=1 and DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(col_b);
select * from table where col_a=1 and col_b+1=10
2.2.8 不等于、不包含(只用到ICP)
索引:
index(col_a,col_b,col_c)
SQL:
select * from table where col_a=1 and col_b not in (1,2)
select * from table where col_a=1 and col_b != 1
2.2.9 选择性过低,直接走全表
选择性过低会导致索引失效。由于通过二级索引查询后还有回表查询的开销,如果通过该字段只能过滤少量的数据,整体上还不如直接查询数据表的性能,则MySQL会放弃这个索引,直接使用全表扫描。底层会根据表大小、IO块大小、行数等信息进行评估决定。
索引:
index(col_a)
SQL:
select * from table where col_a>'2017-10-22'
2.2.10 asc和desc混用
索引:
index(col_a,col_b,col_c)
SQL:
select * from my_table where col_a=1 order by col_b desc,col_c asc
desc 和asc混用时会导致索引失效,不建议混用。
2.2.11 小结
根据以上例子,总结几个索引失效的场景:
-
组合索引左匹配原则
-
发生隐式转换
-
组合索引,in + order by in会阻断排序用索引
-
范围查询会阻断组合索引,索引涉及到范围查询的索引字段要放在组合索引的最后面。
-
前模糊匹配导致索引失效
-
or查询,查询条件部分有索引,部分无索引,导致索引失效。
-
查询条件使用了函数运算、四则运算等。
-
使用了!=、not in
-
选择性过低
-
asc和desc混用
2.3 编写SQL的正确姿势
懂得怎么建立索引,也懂得了怎么避免索引失效,但是有些场景即使索引走对了,也会慢查,这时我们要考虑优化我们SQL写法。
2.3.1 深分页
索引:
index(col_c)
SQL:
select * from my_table where col_c=1 limit 1000,10
为什么深分页会效率变差,首先我们要了解一下分页的原理。
MySQL limit不会传递到引擎层,只是在服务层进行数据过滤。查询数据时,先由引擎层通过索引过滤出一批数据(索引过滤),然后服务层进行二次过滤(非索引过滤)。
引擎层过滤后会将获取的数据暂存,服务层一条一条数据获取,获取时引擎层回表获得完成数据交给服务层,服务层判断是否匹配查询条件(非索引过滤),如果匹配会继续判断是否满足limit限制的数据范围,符合并且范围内的数据都查完了才返回。
所以如果深分页,会导致大量的无效回表(前1000条进行了回表,实际上只需要1000-1010的数据),因此优化的方式就是避免深分页带来的额外回表。
解决方式:
# 避免深分页
select * from my_table where id>上次查询的数据id值 limit 100
# 延迟关联 避免大量回表
SELECT * FROM my_table t1,(select id from my_table where col_c=1 limit 1000,100) t2 where t1.id=t2.id
避免深分页: 我们可以改成id过滤,每次都只查询大于上次查询的数据id。这样每次只查询100条,回表也只需要回表100条。
覆盖索引: 如果业务需要的字段比较少,可以通过保证SQL查询的字段和查询条件都在索引上,避免回表。
延迟关联: 通过延迟关联,通过编写完全基于索引查询数据的SQL,再根据id查询详细的字段数据。
2.3.2 order by id
索引:
index(col_a)
SQL:
select * from table where col_a=1 and col_b=2 order by id
MySQL INNODB二级索引最终叶子结点引用的都是主键id,因此我们可以利用这个点去使用id排序。
但是在本场景中,col_b截断了索引,导致SQL没法利用id进行索引排序。而主键索引的权重会比较高,可能会导致MySQL没有正确选择索引,从而选择了可能性能更差的主键索引去做排序,查询条件通过遍历扫描数据。
因此在不能保证id排序可以走索引的情况下,建议改用其他字段排序。如果查询结果集确定会比较少排序字段可以不在索引上,如果结果集较大还是要把排序字段加到索引中。
三、慢查分析
在掌握了SQL优化的理论知识之后,我们怎么验证编写的SQL是否有按照预期使用了比较合适的索引?这就需要学会分析SQL的执行情况。
执行计划:我们可以通过explain关键字查看SQL的执行计划,通过执行计划分析SQL的执行情况。
执行计划字段描述:
extra字段常见值:
using index condition补充说明:
using index condition表示使用了ICP(Index Condition Pushdown索引下推),ICP是MySQL 5.6之后新增的特性,部分字段因为某些情况无法走索引查询,则会展示using where(在server层进行数据过滤),ICP是在存储引擎层进行数据过滤,而不是在服务层过滤,利用索引现有的数据过滤调一部分数据。
using where 和 using index condition的区别:
为什么需要ICP机制:
索引:
index(col_a,col_b)
SQL:
select * from my_table where col_a="value" and col_b like "%value%"
如果没有using index condtion,col_a会走索引查询,匹配到对应的数据后,回表查出剩余字段信息,再去匹配col_b。假设col_a过滤后还有10000条数据,而通过col_b过滤后只会剩余1000条数据,则有9000条的数据是没必要的回表。
本质上索引树上是包含col_b字段的,只是col_b不能利用索引树二分查找特性(因为使用了前模糊匹配),但是可以利用索引上现有的数据进行遍历,减少无效回表。有了ICP后,基于索引就可以过滤col_a和col_b字段,过滤后只会剩下1000条数据,减少了大量的回表操作。
小结:
通过执行计划我们可以分析出SQL最终使用了什么索引,对索引的使用是处于什么情况,进而可以得出还有没有优化空间。
四、总结
我们要有质量意识,做好预防而不是做补救,SQL优化在开发阶段就要考虑清楚,而不是等上线后出现慢查了才去优化。
做好SQL优化可以记住一个口诀,有用高。SQL要有索引(建立正确的索引),索引要可用(避免索引失效),最后要考虑高效(覆盖索引、索引的选择性)。