索引和深分页优化案例

一、初始状态没加索引

总数据100w左右
浅分页 查询10条需要1.5s左右

select *  from timer_task where app = 'hzhXtimer' order by run_timer 
limit 0,10

深分页查询10条需要1.7s左右

select *  from timer_task where app = 'hzhXtimer' order by run_timer 
limit 100000,10

看执行计划，是去扫描了全表，且有Using filesort 给 run_timer 字段做了额外排序，所以得考虑建索引

二、建立索引：

1）只建单索引字段 app，那么系统还需要给run_timer做额外排序，还是较慢的

create index timer_task_index on timer_task (app)

浅分页219ms

深分页 302ms

2）所以需要建立联合索引（app,run_timer）
为什么建立联合索引后，就不需要对run_timer进行额外排序？
因为在B+树中，当第一个字段app的值确定之后（对应where app = 某值），第2个字段run_timer的值就会进行排序之后再存储，因为这个存储规则，所以才避免了额外排序

create index timer_task_index on timer_task (app,run_timer)

在建了app，run_timer 联合索引后，浅分页3ms,这个已经没问题

select *  from timer_task where app = 'hzhXtimer' order by run_timer 
limit 0,10

而深分页 151ms,还需要优化

select *  from timer_task where app = 'hzhXtimer' order by run_timer 
limit 100000,10

三、在已建立合适索引下，优化深分页sql写法

1）先理解查询过程
1、在联合索引树中查找100010条满足where条件 app=某值 的数据
2、通过id回表查询其他字段信息，回表 100010次
3、扔掉100000条数据，取最后10条返回
定位深分页慢的原因：无效的回表次数太多

select task_id ，Using index 是覆盖索引的查询，不需要回表

select * 没有Using index 所以需要回表

2）解决方案：通过改sql写法减少回表次数
通过子查询先把10条数据的主键id task_id 获取到，然后在外循环再去回表取10条数据，最终只回表10次，只需30ms
因为子查询内部是select task_id 所以不会先回表，如果是select 多个字段，而这多个字段在二级索引中又没有的话，就会先回表查

--写法一：子查询 + join写法
select * from timer_task join (
		select task_id  from timer_task where app = 'hzhXtimer' order by run_timer 
		limit 100000,10
) temp where timer_task.task_id = temp.task_id

--写法二：子查询 + where task_id in 写法
select * from timer_task where task_id in(
	select task_id from (
		select task_id  from timer_task where app = 'hzhXtimer' order by run_timer 
		limit 100000,10
	)as temp
)

深分页 运行截图：
子查询 + join写法

子查询 + where task_id in 写法

执行计划截图：

浅分页还是2-3ms，没什么影响

强制走某个不合理的索引会变慢 force index (index_app)

select * from timer_task join (
		select task_id  from timer_task  force index (index_app)
		where app = 'hzhXtimer' order by run_timer 
		limit 100000,10
) temp where timer_task.task_id = temp.task_id