mysql查询优化

1 针对大数据量的分页优化

1.1 优化sql案例

优化前:

select  * from trade_info where status = 0 and create_time >= '2020-10-01 00:00:00' and create_time <= '2020-10-07 23:59:59' order by id desc limit 102120, 20;

优化后:

select * from trade_info a ,

(select  id from trade_info where status = 0 and create_time >= '2020-10-01 00:00:00' and create_time <= '2020-10-07 23:59:59' order by id desc limit 102120, 20) as b   //这一步走的是索引覆盖扫描，不需要回表
 where a.id = b.id;

1.2  优化原理

对于典型的分页limit m, n来说，越往后翻页越慢，也就是m越大会越慢，因为要定位m位置需要扫描的数据越来越多，导致IO开销比较大，这里可以利用辅助索引的覆盖扫描来进行优化，先获取id，这一步就是索引覆盖扫描，不需要回表，然后通过id跟原表trade_info进行关联

2 针对大数据量的更新优化

2.1 优化前

-- 需要更新的数据量500w
update coupons set status = 1 where status =0 and create_time >= '2020-10-01 00:00:00' and create_time <= '2020-10-07 23:59:59';

2.2 优化后

1 先获取要更新的数据范围内的最小id和最大id（表没有物理delete，所以id是连续的

select min(id) min_id, max(id) max_id from coupons where status =0 and create_time >= '2020-10-01 00:00:00' and create_time <= '2020-10-07 23:59:59';

2   通过Id 每1000条进行批量更新

update
table
set status=2
where
id between 1 and 10000

这两个案例告诉我们，要充分利用辅助索引包含主键id的特性，先通过索引获取主键id走覆盖索引扫描，不需要回表，然后再通过id去关联操作是高效的，同时根据MySQL的特性使用分而治之的思想既能高效完成操作，又能避免主从复制延迟产生的业务数据混乱。

3 复合索引设计原则

3.1  范围查询的索引放在后面

例如:通过创建时间和状态进行查询,把创建时间放在后面

索引: idx_status_create_time

3.2 等值查询的索引放在前面

例如: 通过用户Id 和状态查询,把用户Id 放在前面

索引: idx_user_id_status