vlambda博客
学习文章列表

一、前言

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

• 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 '预约人姓名' 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

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限制的条数。

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

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