β

SQL案例分析

PHPor 的Blog 3 阅读

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语句:

作者:PHPor 的Blog
PHP+Mysql+Apache+Linux
原文地址:SQL案例分析, 感谢原作者分享。

发表评论