八股文教给我,你们专心刷题和面试

那些年我们一起优化的SQL_mysql


Hi,我是王知无,一个大数据领域的原创作者。
放心关注我,获取更多行业的一手消息。


一、前言

在日常开发中,我们经常遇到一些数据库相关的问题,比方说:

  • SQL已经走了索引了,为什么还是会超时?
  • 索引越建越多了,但是好像都是合理的,因为需求就是需要各种查询,但是索引过多又会降低写入的效率,怎么更加合理的建立索引?
  • 为某个业务场景建立了某个索引,想当然的会生效,搞不清楚为啥没有完全覆盖?
  • 索引包含了排序字段,为什么还是fileSort?
  • 刷数据,批量处理大量数据如何优化

二、分析SQL的手段

2.1 基于SQL执行计划


通过explain可以获取SQL的执行计划


执行返回结果

那些年我们一起优化的SQL_python_02

常见字段

那些年我们一起优化的SQL_数据库_03

查询语句常出现的Extra类型

那些年我们一起优化的SQL_数据库_04

MySQL系统架构图

那些年我们一起优化的SQL_java_05

using where 和 using index condition的区别

那些年我们一起优化的SQL_数据库_06

比如:

索引:


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排序算法的选择

怎么使用

那些年我们一起优化的SQL_大数据_07那些年我们一起优化的SQL_大数据_08

执行信息截取:

那些年我们一起优化的SQL_数据库_09

三、案例分析

案例数据表:

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 '预约人姓名' KEY ​​idx_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

那些年我们一起优化的SQL_mysql_10

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 '预约人姓名'  KEY ​​uid​​​ (​​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 '分店创建时间' KEY ​​idx_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

小结

根据以上例子,总结几个索引失效的场景

  1. 组合索引左匹配原则
  2. 发生隐式转换
  3. 组合索引,in + order by in会阻断排序用索引
  4. 范围查询会阻断组合索引,索引涉及到范围查询的索引字段要放在组合索引的最后面。
  5. 前模糊匹配导致索引失效
  6. or查询,查询条件部分有索引,部分无索引,导致索引失效。
  7. 查询条件使用了函数运算、四则运算等。
  8. 使用了!=、not in
  9. 选择性过低

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 '状态-预约实付金额',


小结

  1. 建立索引要选择性低的字段不用建立索引
  2. 具有唯一识别的字段无需与其他字段建立组合索引
  3. 除了业务需求上的考虑,尽量选择性高的索引字段前置
  4. 不止需要考虑查询条件是否走索引,在经过索引过滤后,数据量依旧很大的情况下要考虑使用覆盖索引。

3.3 sql写法可优化场景

3.3.1 深分页

在刷数据经常涉及到深分页,而深分页的时候查询效率会很慢。

比如以下sql:

select * from reserve where field=1 limit 1000,10

由于limit不会传递到引擎层,所以如果查1000行数据,如果where有条件不走索引,会导致需要回表1000行数据。

Mysql查询数据时,先由引擎层通过索引过滤出一批数据(索引过滤),然后服务层进行二次过滤(非索引过滤)。引擎层过滤后会将获取的数据暂存,服务层一条一条数据获取,获取时引擎层回表获得完成数据交给服务层,服务层判断是否匹配查询条件(非索引过滤),如果匹配会继续判断是否满足limit限制的条数。

那些年我们一起优化的SQL_mysql_11

所以如果深分页,会导致大量的无效回表,因此优化的方式就是避免深分页带来的无效回表。

我们可以改成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如果选择排序策略的问题,本文不扩展说明。

如果这个文章对你有帮助,不要忘记 「点赞」 「收藏」 三连啊喂!