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还需要进一步分析）

进一步优化：

基本思路：

1. 先将两个子查询通过使用不同的索引产生两个结果集（结果集只包含id）
2. 将两个结果集进行union
3. 再limit指定的id
4. 再根据少量的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语句：