SQL案例分析
SQL:
SELECT *
FROM b_task
WHERE 1= 1
AND task_type= '14'
and status in('04', '03', '01', '06')
AND is_current= '00'
ORDER BY status,
case when status in('01', '03') then create_time end,
case when status in('04', '06') then finish_time end desc
LIMIT 0,
这个sql语句可以这份到两次sql查询来实现:
SELECT `buss_no`
FROM b_task
WHERE 1= 1
AND task_type= '14'
and status in('03', '01')
AND is_current= '00'
ORDER BY status, `create_time`
limit 0,10
和
SELECT `buss_no`
FROM b_task
WHERE 1= 1
AND task_type= '14'
and status in('04', '06')
AND is_current= '00'
ORDER BY status, `finish_time` desc
limit 0,10
实际情况来看,满足: task_type= ’14’ and status in(’03’, ’01’) 的记录数不过100条,所以order by的压力特别小,sql语句执行时间为2ms;
满足: task_type= ’14’ and status in(’04’, ’06’) 的记录数有30万条,所以order by的压力很大,sql语句执行时间 600+ms; 如果能把finish_time添加到索引里面,则可以避免排序带来的压力,应该也会是2ms的响应级别
解决办法:
添加索引: task_type,status,finish_time
由于返回结果是非索引覆盖的,而且条件中含有非索引覆盖的字段,所以,这种联合索引是用不上的
根本的解决办法,还是需要先得到一个结果集,再对结果集进行排序;
进一步分析,可以优化sql语句如下:
select * from b_task where id in(select * from (SELECT `id`
FROM b_task
WHERE 1= 1
AND task_type= '14'
and status in('04', '06')
ORDER BY status, `finish_time` desc
limit 0,10) as tmp)
(其实,这个SQL只能和两部分中的其中一部分等价,将两部分合并成一个SQL还需要进一步分析)
进一步优化:
基本思路:
- 先将两个子查询通过使用不同的索引产生两个结果集(结果集只包含id)
- 将两个结果集进行union
- 再limit指定的id
- 再根据少量的id进行相关全量信息的查询
select SQL_NO_CACHE id
from b_task
where id in (
select tmp.id
from((
SELECT `id`
FROM b_task
-- force index(idx_task_TYPE_STATUS_FINISH_TIME)
WHERE 1= 1
AND task_type= '14'
and status in ('04', '06')
ORDER BY status, `finish_time` desc )
union(
SELECT `id`
FROM b_task
-- force index(idx_task_TYPE_STATUS_CREATE_TIME)
WHERE 1= 1
AND task_type= '14'
and status in ('01', '03')
ORDER BY status, `create_time` desc )
limit 0, 10) as tmp)
问题: 两个子查询并没有使用期望的索引,可能使用索引的成本比较大,强制使用索引虽然也可以,但是效果并不好,没有任何改善;如果给每个子查询添加limit,则也能触发mysql使用索引,结果就是,如果limit的结果集比较小,查询就快,如果子查询中limit的结果集很大,则查询依然很慢;
所以,这里的问题是,union不知道外面其实只需要很少的数据,做了很多的无用功,假如能根据外面的limit计算一个靠谱的union子查询内部的limit值,效果应该会好很多。
如何根据外面的limit n,10 来计算内部连个子查询的limit呢?
由于不知道原始的SQL语句是如何分别对两类不同的状态分别进行排序、合并的,所以,这里如果非要根据外部的limit n,10 来计算内部的limit的话,似乎内部的limit只能是 0,n+10 ;或许这样已经不错了,因为很少有人翻很多页的,一般看前几页就够了
注意: 上面包含case语法的sql,混合了两种排序方式,即使 task_type,status,create_time 和 task_type,status,finish_time 都有联合索引,也都用不上,如果分开写就好了,会快很多
附录:
这里的case语句用在了order by中,期望对于不同的状态采用不同的排序方式,能实现吗?
eg:
对于名字是a开头的,价格升序排列;对于名字是b开头的,价格降序排列:
select *
from book
where name like 'a%'
or name like 'b%'
order by case when name like 'a%' then price end,
case when name like 'b%' then price end desc;
等价的sql语句: