MySQL之常见SQL优化及案例

• 1.使用函数
• 2.隐式转换
• 3.分页
• 4.外连接
• 5.子查询
• 6.模糊查询
• 7.多表join
• 8.从业务使用逻辑去优化

1.使用函数

常见的是在表达式的左边对某些字段使用函数运算，导致字段上有索引但是还是全表扫描:

select id, skuThumbnailUrl from order_detail where DATE(createTime) = ‘2015-06-05’;

原因：因运输符的左边使用了函数，导致MySQL优化器无法走索引二全表扫描。

优化：把函数放到运算符的右侧。

2.隐式转换

常见的是数据类型发生了转换，例如varchar 转换成int，下面是个隐式转换的例子：

mysql> desc SELECT * FROM user LEFT JOIN user_ticket ON user.mobile=user_ticket.mobile LEFT JOIN carinfo ON user.defaultcar=carinfo.id WHERE user.mobile=99966633391;

+----+-------------+-------------+--------+---------------+---------+---------+------------------------+---------+-------------+

| id | select_type | table       | type   | possible_keys | key     | key_len | ref                    | rows    | Extra       |

+----+-------------+-------------+--------+---------------+---------+---------+------------------------+---------+-------------+

|  1 | SIMPLE      | user        | ALL    | mobile        | NULL    | NULL    | NULL                   | 3660313 | Using where |

|  1 | SIMPLE      | user_ticket | ref    | mobile        | mobile  | 768     | pinche.user.mobile     |       1 |             |

|  1 | SIMPLE      | carinfo     | eq_ref | PRIMARY       | PRIMARY | 8       | pinche.user.defaultcar |       1 |             |

+----+-------------+-------------+--------+---------------+---------+---------+------------------------+---------+——————+

原因：

优化：WHERE user.mobile='99966633391',    或者  定义mobile 列 int，查询使用user.mobile=99966633391就不会发生隐式转换不走索引而是全表扫描。

3.分页

优化原则：大表offset(也即是m) 很大时，暴露性能问题，从代码逻辑去优化，实在没办法才考虑在sql本身去优化。

常见有： ... order by xxx limit m,n;

select t.tobankid onlyCol,t.companydataid dataid,t.*

from recon_companyxxx t

WHERE t.bankshopperid = '8002003' and t.transdate >= ‘2014-12-17’and t.transdate < '2014-12-18'

order by t.tobankid,t.transtype,t.amount

limit 136000,2000;

优化：先拿到主键再join，减少回表。

select n.tobankid onlyCol,n.companydataid dataid,n.*

from recon_companyxxx n inner join(select companydataid from recon_companydata t

WHERE t.bankshopperid = '8002003' and t.transdate >= '2014-12-17' and t.transdate < '2014-12-18'

order by t.tobankid,t.transtype,t.amount limit 136000,2000) t2 USING (companydataid);

更好的优化方式：

--程序端实现逻辑

     实例一：

     #第一页

     select * from blog order by updatetime desc, id desc limit 10;

     #记录第一页的min(updatetime )

     #第二页  

       select * from blog where updatime <=minvalue_updatetime

    and (id < minvalue_id or updatetime < minvalue_updatetime) limit 10;

    依次类推。

4.外连接

优化原则：能用join替换的不用外连接，外连接没有join性能好。

SELECT * FROM a JOIN b LEFT JOIN c ON (c.key=a.key)

LEFT JOIN d ON (d.key=a.key)

WHERE b.key=d.key;

 

SELECT * FROM t1 LEFT JOIN t2 ON (column1) WHERE t2.column2=5;

上面sql有问题吗？

优化后如下：

SELECT * FROM b JOIN a LEFT JOIN c ON (c.key=a.key)

LEFT JOIN d ON (d.key=a.key)

WHERE b.key=d.key;

 

SELECT * FROM t1,t2 WHERE t1.col1=t2.col2 and t2.col2=5;

外连接的原则：
1.where条件中加入过滤条件，把驱动表变小表再驱动大表
2.右表尽量使用过滤条件

left join可以转化成join的情况？

5.子查询

优化原则：子查询改写成join。

看下面例子：

select a,b,c from `cash_coupon`

where orderId in(select id from order where create_time between 'xxx' and 'xxx');

优化后：

select a,b,c from cash_coupon as cc,`order` as o where cc.orderId=o.id;

6.模糊查询

优化原则：尽量确定前缀，没有前缀考虑使用搜索插件(slor、ES等)。

如下sql：

select customer_id,store_id,first_name ,last_name from customer where last_name like 'j%' and first_name like '%xiao%' order by first_name asc,last_name asc;

7.多表join

优化原则:

        a.对where条件建立合适的索引；

        b.小表驱大表(这里的小表和大表不是原表的大小，是指where过滤后数据行的数量)，基于cost(IO+cpu)的估算；     

        c.谓词要内推

        d.使用hint：STRAIGHT_JOIN

下面是一个优化后小表驱动大表例子：

select count(*) from mpay_order mpayOrder inner join mrecharge_order_info orderinfo on mpayOrder.order_num = orderinfo.order_num inner join mpay_trade mpayTrade on mpayOrder.order_num = mpayTrade.order_num where TRUE and left(mpayTrade.trade_num,2) = '10' and TIMESTAMPDIFF(SECOND,mpayOrder.create_time,'2014-12-19 00:00:00') <= 0 and TIMESTAMPDIFF(SECOND,mpayOrder.create_time,'2014-12-19 12:40:32') >= 0;

 

执行计划：

+----+-------------+-----------+-------+---------------+---------------+---------+--------------------------+--------+-------------+

 

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

 

+----+-------------+-----------+-------+---------------+---------------+---------+--------------------------+--------+-------------+

 

| 1 | SIMPLE | orderinfo | index | order_num_idx | order_num_idx | 93 | NULL | 184192 | Using index |

 

| 1 | SIMPLE | mpayTrade | ref | order_num_idx | order_num_idx | 93 | mpay.orderinfo.order_num | 1 | Using where |

 

| 1 | SIMPLE | mpayOrder | ref | order_num_idx | order_num_idx | 93 | mpay.mpayTrade.order_num | 1 | Using where |

 

+----+-------------+-----------+-------+---------------+---------------+---------+--------------------------+--------+-------------+

执行时间：1 row in set (2.69 sec)

 

 

===>建议优化为，改成成如下：

select count(*) from mpay_order mpayOrder inner join mrecharge_order_info orderinfo on mpayOrder.order_num = orderinfo.order_num inner join mpay_trade mpayTrade on mpayOrder.order_num = mpayTrade.order_num where TRUE and left(mpayTrade.trade_num,2) = '10' and mpayOrder.create_time>='2014-12-19 00:00:00' and mpayOrder.create_time<='2014-12-19 12:40:32';

 

执行计划：

+----+-------------+-----------+-------+-------------------------------+-----------------+---------+--------------------------+------+--------------------------+

 

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

 

+----+-------------+-----------+-------+-------------------------------+-----------------+---------+--------------------------+------+--------------------------+

 

| 1 | SIMPLE | mpayOrder | range | order_num_idx,create_time_idx | create_time_idx | 9 | NULL | 1484 | Using where |

 

| 1 | SIMPLE | orderinfo | ref | order_num_idx | order_num_idx | 93 | mpay.mpayOrder.order_num | 1 | Using where; Using index |

 

| 1 | SIMPLE | mpayTrade | ref | order_num_idx | order_num_idx | 93 | mpay.mpayOrder.order_num | 1 | Using where |

 

+----+-------------+-----------+-------+-------------------------------+-----------------+---------+--------------------------+------+--------------------------+

执行时间:1 row in set (0.03 sec)

下面是一个驱动表选错的例子：

select  count(*) from comment a,comment_antispam b where a.created_at>'2016-03-01 00:00:00' and a.status=2 and a.id=b.comment_id;

执行计划：

优化成:

select count(*) from comment a straight_join comment_antispam b where a.created_at>'2016-03-01 00:00:00' and a.status=2 and a.id=b.comment_id;

 

8.从业务使用逻辑去优化

8.1 下例是卖家查未读信息条数：

select count(*) from user_msg where receiver='sun098' and status='UNREAD' and title is not null;

此sql 的列reciver、status 都有加索引，但是存在问题是多个大卖家并发查询，导致cpu、load 负载都很高，此sql本身已经没有优化的空间。了解业务逻辑不需要精确条数，超过100显示100+，如此sql可以优化成：

select count(*) from (select id from user_msg where receiver='sun098' and status='UNREAD' and title is not null limit 101) a;

 

8.2 查看历史订单留言记录，未读留言的放在前面，已读的放在后面，并且按时间递减排序

select * from(

select ID,GMT_CREATE,GMT_MODIFIED,SENDER_ALI_ID,RECEIVER_ALI_ID,UNREAD_COUNT,STATUS,LAST_MESSAGE_ID,RELATION_ID,SELLER_ADMIN_SEQ,IS_READ

from message_relation_sender

WHERE SENDER_ALI_ID = 1344455 and UNREAD_COUNT > 0

order by LAST_MESSAGE_ID desc) m

union all

select * from(

select ID,GMT_CREATE,GMT_MODIFIED,SENDER_ALI_ID,RECEIVER_ALI_ID,UNREAD_COUNT,STATUS,LAST_MESSAGE_ID,RELATION_ID,SELLER_ADMIN_SEQ,IS_READ 

from message_relation_sender

WHERE SENDER_ALI_ID = 1344455 and UNREAD_COUNT = 0

order by LAST_MESSAGE_ID desc) n 

limit 5000,15;

分析问题：

a.union all直接合并结果集，2次索引扫描

b.union all 还会产生临时表，执行代价比较大

c.limit m,n 问题

优化方案一：

        这里看到unread_count实际值对这个查询没有实际意义，我们只需要区分已读和未读即可。由于sql本身已经没有优化余地，考虑对表结构进行修改，加一个字段is_read，表示已读和未读。is_read=2表示未读；is_read=1表示已读。通过组合索引(SENDER_ALI_ID,is_read, LAST_MESSAGE_ID),既可以完成过滤，还可以完成排序。优化sql如下：

select ID,GMT_CREATE,GMT_MODIFIED,SENDER_ALI_ID,RECEIVER_ALI_ID,UNREAD_COUNT,STATUS,LAST_MESSAGE_ID,RELATION_ID,SELLER_ADMIN_SEQ,IS_READ from message_relation_sender

where SENDER_ALI_ID = 1344455  order by is_read desc, LAST_MESSAGE_ID desc limit 5000,15

优化方案二：
由于索引不包含所有的返回字段，因此需要回表，而mysql对于limit 5000,15的查询却需要返回5015次，即回表5015次，这种无效的返回很影响查询效率。
分页的优化写法：

select a.ID,a.GMT_CREATE,a.GMT_MODIFIED,a.SENDER_ALI_ID,a.RECEIVER_ALI_ID,a.UNREAD_COUNT,STATUS,a.LAST_MESSAGE_ID,a.RELATION_ID,a.SELLER_ADMIN_SEQ,a.IS_READ

FROM message_relation_sender as a,(select ID from message_relation_sender where SENDER_ALI_ID = 1344455 order by is_read desc, LAST_MESSAGE_ID desc limit 5000,15)as b

where a.ID=b.ID;