那些年我们一起优化的SQL
Hi,我是王知无,一个大数据领域的原创作者。
放心关注我,获取更多行业的一手消息。
一、前言
在日常开发中,我们经常遇到一些数据库相关的问题,比方说:
SQL已经走了索引了,为什么还是会超时? 索引越建越多了,但是好像都是合理的,因为需求就是需要各种查询,但是索引过多又会降低写入的效率,怎么更加合理的建立索引? 为某个业务场景建立了某个索引,想当然的会生效,搞不清楚为啥没有完全覆盖? 索引包含了排序字段,为什么还是fileSort? 刷数据,批量处理大量数据如何优化
二、分析SQL的手段
2.1 基于SQL执行计划
通过explain可以获取SQL的执行计划
执行返回结果
常见字段
查询语句常出现的Extra类型
MySQL系统架构图
using where 和 using index condition的区别
比如:
索引:
index(field1,field2)
SQL:
select * from table where field1=value1 and field2 like '%value2%'
如果没有using index condtion,field1会走索引查询,匹配到对应的数据后,回表查出剩余字段信息,再去匹配。
如果通过field1匹配出了大量数据,会导致大量回表匹配。
于是就有了using index condtion,本质上索引树上是包含field2字段的,只是field2不能走索引搜索(因为使用了前模糊匹配),但是可以利用索引上现有的数据进行遍历,减少无效回表。
using index condtion就是使用了ICP(索引下推),在存储引擎层进行数据过滤,而不是在服务层过滤,利用索引现有的数据减少回表的数据。
比方说field1过滤后剩下1000条数据,需要回表1000条,使用field2在索引过滤后剩下100条,那么即使select * 也只是回表100条。
2.2 基于追踪优化器分析
该功能可以查看优化器生成执行计划的整个过程
sql语句的优化转换 表的依赖关系 全表扫描、索引的开销计算,及最终选择 filesort排序算法的选择
怎么使用
执行信息截取:
三、案例分析
案例数据表:
CREATE TABLE `reserve` (
`id` BIGINT UNSIGNED AUTO_INCREMENT NOT NULL COMMENT '数据库自增id',
`reserve_no` VARCHAR(32) NOT NULL COMMENT '预约号 全局唯一',
`shop_id` BIGINT UNSIGNED NOT NULL COMMENT '店铺id',
`uid` BIGINT UNSIGNED NOT NULL COMMENT '买家id',
`technician_id` BIGINT UNSIGNED NOT NULL DEFAULT '0' COMMENT '技师id',
`arrange_at` DATETIME NOT NULL COMMENT '预约开始时间',
`is_delete` TINYINT UNSIGNED NOT NULL DEFAULT '0' COMMENT '逻辑删除字段 0-未删除 1-已删除',
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
`reserve_status` INT NOT NULL DEFAULT '5' COMMENT '预约状态',
`guest_name` VARCHAR(30) NOT NULL DEFAULT '' COMMENT '预约人姓名/昵称',
`guest_mobile` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '预约人手机号',
`real_pay` BIGINT UNSIGNED NOT NULL DEFAULT '0' COMMENT '预约总价格',
PRIMARY KEY (`id`),
KEY `idx_reserve_no` (`shop_id`,`reserve_no`) COMMENT '预约号',
KEY `idx_uid_kdt` (`shop_id`,`uid`) COMMENT '用户 ID',
KEY `idx_shop_guest_name` (`shop_id`,`guest_name`) COMMENT '预约人姓名',
KEY `idx_status_arrange` (`shop_id`,`reserve_status`,`arrange_at`) COMMENT '状态、到店时间',
KEY `idx_status_arrange` (`shop_id`,`reserve_status`,`uid`) COMMENT '状态、买家id',
KEY `idx_shop_create_status` (`shop_id`,`created_at`,`reserve_status`) COMMENT '分店创建时间',
KEY `idx_shop_del` (`shop_id`,`is_delete`) COMMENT '逻辑删除',
KEY `idx_shop_dept_arrange` (`shop_id`,`arrange_at`,`created_at`),
KEY `idx_guest_name` (`guest_name`) COMMENT '预约人姓名',
) ENGINE=InnoDB CHARSET=utf8mb4 COMMENT='预约记录表'
注意:
ICP只能减少回表,但是在索引上的搜索依旧没有利用到查找树的特性去提高搜索效率,所以这种场景该字段在本章中也会认定索引失效。
比方说表table 存在索引 index(field1,field2)
select * from table where field1=value1 and field2 like '%value2%'
field2会基于从index上过滤后的数据进行遍历搜索。
那么出现using index condition 就说明索引还不是最优吗?
并不一定, 当索引使用类型type为range时,都会出现using index condition。
比方说:
select * from table where field1 > value1
3.1 索引失效场景
3.1.1 最左匹配截断
索引:
KEY
idx_reserve_no
(shop_id
,reserve_no
) COMMENT '预约号'
SQL:
select * from reserve where reserve_no=''
查询匹配从左往右匹配,要使用reserve_no走索引,必须查询条件携带shop_id
3.1.2 隐式转换
索引:
KEY
idx_shop_guest_name
(shop_id
,guest_name
) COMMENT '预约人姓名' KEYidx_shop_create_status
(shop_id
,created_at
,reserve_status
) COMMENT '分店创建时间'
SQL:
select * from reserve where shop_id=1 and guest_name=1
select * from reserve where created_at=1603296000000
guest_name是字符类型,使用了数字,以及date_time类型应该使用字符串匹配。否则mysql会用到隐式替换,导致索引失效。
3.1.3 in + order by 导致排序失效
索引:
KEY
idx_status_arrange
(shop_id
,reserve_status
,arrange_at
) COMMENT '状态时间'
SQL:
select * from reserve where shop_id=1 and reserve_status in (1,2) order by created_at
使用了in+order by会导致order by不能走索引,可以通过将order by字段前置,将in过滤的字段移动到组合索引后端,只用于避免回表。
一半建议减少排序的字段,所以可以调整下SQL只查id,再根据id去查对应的数据。(可以让sql走覆盖索引、非索引排序的情况下也可以促使Mysql使用更优的排序算法)
select id from reserve where shop_id=1 and reserve_status in (1,2) order by created_at
可以调整索引为
KEY
idx_status_arrange
(shop_id
,arrange_at
,reserve_status
) COMMENT '状态时间'
让排序走索引,并且利用reserve_status在索引上进行数据过滤,避免回表
同时存在 reserve_status、created_at索引和 created_at、reserve_status索引时 ,则要注意mysql在进行索引选择时,针对in是有特殊的代价计算规则的。
in代价计算
in查询在Mysql底层是通过n*m的方式去搜索,类似union,但是效率比union高。
in查询在进行cost代价计算时(代价 = 元组数 * IO平均值),是通过将in包含的数值,一条条去查询获取元组数的,因此这个计算过程会比较的慢,所以Mysql设置了个临界值,5.6之后超过这个临界值后该列的cost就不参与计算了。因此会导致执行计划选择不准确。
eq_range_index_dive_limit
默认是200,即in条件超过了200个数据,会导致in的代价计算存在问题,可能会导致Mysql选择的索引不准确。
3.1.4 范围查询阻断组合索引
索引:
KEY
idx_shop_create_status
(shop_id
,created_at
,reserve_status
) COMMENT '分店创建时间',
SQL:
select * from reserve where shop_id=1 and created_at>'2020-10-22' and reserve_status=10
注意:>和< 判断会阻断组合索引,created_at用了>查询,因此reserve_status不能走索引。
可以调整下索引顺序,created_at放在最后面。
KEY
idx_shop_create
(shop_id
,reserve_status
,created_at
) COMMENT '分店创建时间排序'
注意:>=和<=虽然也会阻断后续字段,但是后续字段可能会通过icp基于索引获取数据,所以explain可能可以看到key_len是包含后续字段的。
3.1.5 前模糊匹配不能走索引
组合索引时,在非最左字段的情况下,可以用到ICP
索引:
KEY
idx_shop_guest_name
(shop_id
,guest_name
) COMMENT '预约人姓名',
SQL:
select * from reserve where shop_id=1 and guest_name like '%name%'
3.1.6 or查询导致失效
索引:
KEY
idx_guest_name
(guest_name
) COMMENT '预约人姓名' KEYuid
(uid
) COMMENT '买家id'
SQL:
select * from reserve where uid=1 or guest_name=''
or查询会导致索引失效,可以将uid和guest_name分别建立索引,利用Mysql的索引合并进行优化。
3.1.7 使用函数查询
包括使用查询字段进行四则运算 field+1 > value
索引:
KEY
idx_shop_create_status
(shop_id
,created_at
,reserve_status
) COMMENT '分店创建时间' KEYidx_shop_real_pay
(shop_id
,real_pay
) USING BTREE,
SQL:
select * from reserve where shop_id=1 and DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(created_at);
select * from reserve where shop_id=1 and real_pay+1=10
在列上进行使用函数查询和使用查询字段进行四则运算,会导致不能走索引
3.1.8 不等于、不包含只能用到ICP
索引:
KEY
idx_status_arrange
(shop_id
,reserve_status
,arrange_at
) COMMENT '状态时间'
SQL:
select * from reserve where shop_id=1 and reserve_status not in (1,2)
select * from reserve where shop_id=1 and reserve_status != 1
3.1.9 选择性过低,直接走全表
选择性过低,曾经以选择性是否低于30%为界限决定是否走索引,现在还会根据表大小、IO块大小、行数等决定。
索引:
KEY
idx_shop_create_status
(shop_id
,created_at
,reserve_status
) COMMENT '分店创建时间'
SQL:
select * from reserve where shop_id=1 and created_at>'2017-10-22'
3.1.10 索引碎片化
索引碎片化是较为特殊的场景,查询字段均符合索引,但是索引执行计划不符合预期。考虑如果是大量执行DML的表,有可能是索引碎片化严重,导致索引失效。
(11) 排序使用了索引
排序的字段不一定是要在最后,但是一定不能前一个字段不在查询条件中
索引:
KEY
idx_shop_create_status
(shop_id
,created_at
,reserve_status
) COMMENT '分店创建时间'
SQL:
select * from reserve where shop_id=1 and created_at>'2017-10-22' order by
小结
根据以上例子,总结几个索引失效的场景
组合索引左匹配原则 发生隐式转换 组合索引,in + order by in会阻断排序用索引 范围查询会阻断组合索引,索引涉及到范围查询的索引字段要放在组合索引的最后面。 前模糊匹配导致索引失效 or查询,查询条件部分有索引,部分无索引,导致索引失效。 查询条件使用了函数运算、四则运算等。 使用了!=、not in 选择性过低
3.2 索引可优化场景
3.2.1 索引包含选择性过低字段
SQL:
select * from reserve where shop_id=1 and is_delete=0
索引:
KEY
idx_shop_del
(shop_id
,is_delete
) COMMENT '逻辑删除',
is_delete 表示逻辑删除,包含0未删除和1已删除,数据库中的值只有很少量部分是逻辑删除的,但是在业务中我们一般都只查未删除的,那么这种情况is_delete是完全不必要在索引中的。可以把is_delete在组合索引中去掉
3.2.2 唯一性索引
SQL:
select * from reserve where shop_id=1 and reserve_no=''
索引:
KEY
idx_reserve_no
(shop_id
,reserve_no
) COMMENT '预约号',
具有唯一性的字段不用跟别的字段建立组合索引,可以只保留reserve_no。
3.2.3 字段前置
SQL:
select * from reserve where shop_id=1 and uid=1 and reserve_status=1
索引:
KEY
idx_status_arrange
(shop_id
,reserve_status
,uid
) COMMENT '状态、买家id' uid的区分度明显比reserve_status更高,单条sql来看,将uid和reserve_status的位置交换会更加合适
3.2.4 覆盖索引
SQL:
SELECT sum(real_pay) FROM reserve where shop_id=1 and reserve_status=1
索引:
KEY
idx_status_arrange
(shop_id
,reserve_status
,uid
) COMMENT '状态、买家id',
看起来查询条件已经走索引了,但是如果数据量很大的情况下,会造成大量回表,也会导致慢查。
可以通过建一个索引解决
KEY
idx_status_real_pay
(shop_id
,reserve_status
,real_pay
) COMMENT '状态-预约实付金额',
小结
建立索引要选择性低的字段不用建立索引 具有唯一识别的字段无需与其他字段建立组合索引 除了业务需求上的考虑,尽量选择性高的索引字段前置 不止需要考虑查询条件是否走索引,在经过索引过滤后,数据量依旧很大的情况下要考虑使用覆盖索引。
3.3 sql写法可优化场景
3.3.1 深分页
在刷数据经常涉及到深分页,而深分页的时候查询效率会很慢。
比如以下sql:
select * from reserve where field=1 limit 1000,10
由于limit不会传递到引擎层,所以如果查1000行数据,如果where有条件不走索引,会导致需要回表1000行数据。
Mysql查询数据时,先由引擎层通过索引过滤出一批数据(索引过滤),然后服务层进行二次过滤(非索引过滤)。引擎层过滤后会将获取的数据暂存,服务层一条一条数据获取,获取时引擎层回表获得完成数据交给服务层,服务层判断是否匹配查询条件(非索引过滤),如果匹配会继续判断是否满足limit限制的条数。
所以如果深分页,会导致大量的无效回表,因此优化的方式就是避免深分页带来的无效回表。
我们可以改成id过滤,每次都只查询大于上次查询的数据id。这样每次只查询100条,回表也只需要回表100条
# 避免深分页
select * from reserve where id>上次查询的数据id值 limit 100
# 延迟关联 避免大量回表
SELECT * FROM shop t1,(select id from reserve limit 1000,100) t2 where t1.id=t2.id
另外,涉及到大数据量的处理,可以按照时间分割查询,每个线程负责一个时间段的数据处理,提高整体的效率。
3.3.2 asc和desc混用
索引:
KEY
ide_shop_dept_arrage
(sho_id
,arrage_at
,created_at
)
SQL:
select * from t1 where shop_id=1 order by arrage_at desc,created_at asc
desc 和asc混用时会导致索引失效
另外排序建议只select必须的字段,通过覆盖索引提高效率。排序字段一定要走索引,不走索引数据量大的情况即使select的字段很少也会很慢。
select比较少的字段不仅可以利用到覆盖索引提高速度。在排序没有命中索引时,还涉及到Mysql如果选择排序策略的问题,本文不扩展说明。
如果这个文章对你有帮助,不要忘记 「在看」 「点赞」 「收藏」 三连啊喂!
2022年全网首发|大数据专家级技能模型与学习指南(胜天半子篇)