Mysql8中优化思路

一.分页常用的LIMIT关键字

　　

1.
SELECT *
FROM   user
WHERE  type = 'SQLStats' 
       AND name = 'SlowLog' 
ORDER  BY create_time
LIMIT  1000, 10;

常用的办法是在type, name, create_time字段上加组合索引

如果数量巨大比如，十万个找10条，每次翻页，
2.
SELECT *
FROM   user
WHERE  type = 'SQLStats' 
       AND name = 'SlowLog' 
ORDER  BY create_time
LIMIT  100000, 10;
100w每次翻页10条，还是会很慢

3.
SELECT *
FROM   user
WHERE  type = 'SQLStats' 
       AND name = 'SlowLog' 
ORDER  BY create_time
LIMIT  1000000, 10;



4.还有一种方式就是在加完索引后，按照某个条件进行分页，这里使用create_time

SELECT   *
FROM     operation
WHERE    type = 'SQLStats' 
AND      name = 'SlowLog' 
AND      create_time > '2017-03-16 14:00:00' 
ORDER BY create_time limit 10;

5.如果有id，还可以根据ID，把每次查询结果的最大ID取出来单独作为分页条件，比如这样、

SELECT   *
FROM     operation
WHERE    type = 'SQLStats' 
AND      name = 'SlowLog' 
AND      user_id > '每次分页后的最大/最小值' 
ORDER BY user_id ASC/DESC limit 10;

经过4和5后，性能就能得到极大的提升

　　

二. 隐式转换

　　

SQL语句中查询变量和字段定义类型不匹配是另一个常见的错误　　

　

 explain extended SELECT *  FROM user b  WHERE b.bpn = 14000000123 
show warnings;
| Warning | 1739 | Cannot use ref access on index 'bpn' due to type or collation conversion on field 'bpn'


其中字段bpn的定义为varchar(20)，MySQL的策略是将字符串转换为数字之后再比较。函数作用于表字段，索引失效。

上述情况可能是应用程序框架自动填入的参数，而不是程序员的原意。类型是varchar,参数输入却是数值类型，就可能导致索引不能命中。


所以日常开发过程要注意字段类型和书写习惯
SELECT *  FROM user b  WHERE b.bpn = '14000000123' 

　　

3. 关联更新、删除

　　

  
		扫描两遍以上
  
		扫描两遍以上
	UPDATE t_user_audit
            SET audit_userid = #{auditUserId},
                audit_username = #{auditUsername}
            WHERE
                    id IN (
                    SELECT
                        t.id
                    FROM
                        ( SELECT id FROM t_user_audit
                        WHERE plan_id = #{planId}
                        AND plan_type = 1
                       ORDER BY id ASC LIMIT #{limitNum} OFFSET #{startNum} ) t
        );
				
					
				
				
				
				
	扫描两遍以上
				
UPDATE operation o
SET    status = 'applying' 
WHERE  o.id IN (SELECT id 
                FROM   (SELECT o.id,
                               o.status
                        FROM   operation o
                        WHERE  o.group = 123 
                               AND o.status NOT IN ( 'done' )
                        ORDER  BY o.parent,
                                  o.id
                        LIMIT  1) t);
看下operation 执行计划就呵呵了
+----+--------------------+-------+-------+---------------+---------+---------+-------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+-------+---------------+---------+---------+-------+------+-----------------------------------------------------+
| 1  | PRIMARY | o | index |               | PRIMARY | 8       | | 24   | Using where; Using temporary |
| 2 | DEPENDENT SUBQUERY | |       | |         | |       | | Impossible WHERE noticed after reading const tables |
| 3  | DERIVED | o | ref | idx_2,idx_5 | idx_5 | 8       | const | 1    | Using where; Using filesort |
+----+--------------------+-------+-------+---------------+---------+---------+-------+------+-----------------------------------------------------+
和两个样例数据量大，每次执行都得扫描多久，自己想吧~~

优化后

UPDATE t_user_audit t
JOIN ( 
 SELECT id 
 FROM t_user_audit 
 WHERE plan_id = #{planId}
	  AND plan_type = 1 
	  ORDER BY 
	  id ASC 
	LIMIT #{limitNum} OFFSET #{startNum} ) 
)  a
ON t.id=a.id
SET audit_userid = #{auditUserId}, audit_username = #{auditUsername}
				


UPDATE operation o
       JOIN  (SELECT o.id,
                            o.status
                     FROM   operation o
                     WHERE  o.group = 123 
                            AND o.status NOT IN ( 'done' )
                     ORDER  BY o.parent,
                               o.id
                     LIMIT  1) t
         ON o.id = t.id
SET    status = 'applying'

这样出去索引因素不考虑，性能也会得到极大的提升

再看下operation 执行计划，回表，索引的情况冗余度大大降低
+----+-------------+-------+------+---------------+-------+---------+-------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+-------+---------+-------+------+-----------------------------------------------------+
| 1  | PRIMARY |       | |               | |         | |      | Impossible WHERE noticed after reading const tables |
| 2 | DERIVED | o | ref | idx_2,idx_5 | idx_5 | 8 | const | 1 | Using where; Using filesort |
+----+-------------+-------+------+---------------+-------+---------+-------+------+-----------------------------------------------------+

　　

　　

4. 混合排序

　　

MySQL不能利用索引进行混合排序。但在某些场景，还是有机会使用特殊方法提升性能的。
SELECT *
FROM   my_order o
       INNER JOIN my_appraise a ON a.orderid = o.id
ORDER  BY a.is_reply ASC,
          a.appraise_time DESC 
LIMIT  0, 20;

执行计划显示为全表扫描：
+----+-------------+-------+--------+-------------+---------+---------+---------------+---------+-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
+----+-------------+-------+--------+-------------+---------+---------+---------------+---------+-+
| 1 | SIMPLE | a | ALL | idx_orderid | NULL | NULL | NULL | 1967647 | Using filesort |
|  1 | SIMPLE | o | eq_ref | PRIMARY | PRIMARY | 122     | a.orderid |       1 | NULL |
+----+-------------+-------+--------+---------+---------+---------+-----------------+---------+-+


由于is_reply只有0和1两种状态，我们按照下面的方法重写后，执行时间从1.58秒降低到2毫秒。
SELECT *
FROM   ((SELECT *
         FROM   my_order o
                INNER JOIN my_appraise a
                        ON a.orderid = o.id
                           AND is_reply = 0 
         ORDER  BY appraise_time DESC 
         LIMIT  0, 20)
        UNION ALL
        (SELECT *
         FROM   my_order o
                INNER JOIN my_appraise a
                        ON a.orderid = o.id
                           AND is_reply = 1 
         ORDER  BY appraise_time DESC 
         LIMIT  0, 20)) t
ORDER  BY  is_reply ASC,
          appraisetime DESC 
LIMIT  20;

　　

5. EXISTS语句

　　

MySQL对待EXISTS子句时，仍然采用嵌套子查询的执行方式。如下面的SQL语句：
SELECT *
FROM   my_neighbor n
       LEFT JOIN my_neighbor_apply sra
              ON n.id = sra.neighbor_id
                 AND sra.user_id = 'xxx' 
WHERE  n.topic_status < 4 
       AND EXISTS(SELECT 1 
                  FROM   message_info m
                  WHERE  n.id = m.neighbor_id
                         AND m.inuser = 'xxx')
       AND n.topic_type <> 5


执行计划为：
+----+--------------------+-------+------+-----+------------------------------------------+---------+-------+---------+ -----+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+------+ -----+------------------------------------------+---------+-------+---------+ -----+
|  1 | PRIMARY | n | ALL |  | NULL | NULL | NULL | 1086041 | Using where |
| 1 | PRIMARY | sra | ref | | idx_user_id | 123 | const | 1 | Using where |
|  2 | DEPENDENT SUBQUERY | m | ref |  | idx_message_info | 122     | const |       1 | Using index condition; Using where |
+----+--------------------+-------+------+ -----+------------------------------------------+---------+-------+---------+ -----+



去掉exists更改为join，能够避免嵌套子查询，将执行时间从1.93秒降低为1毫秒。
SELECT *
FROM   my_neighbor n
       INNER JOIN message_info m
               ON n.id = m.neighbor_id
                  AND m.inuser = 'xxx' 
       LEFT JOIN my_neighbor_apply sra
              ON n.id = sra.neighbor_id
                 AND sra.user_id = 'xxx' 
WHERE  n.topic_status < 4 
       AND n.topic_type <> 5

新的执行计划：
+----+-------------+-------+--------+ -----+------------------------------------------+---------+ -----+------+ -----+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+ -----+------------------------------------------+---------+ -----+------+ -----+
|  1 | SIMPLE | m | ref | | idx_message_info | 122     | const |    1 | Using index condition |
| 1 | SIMPLE | n | eq_ref | | PRIMARY | 122 | ighbor_id | 1 | Using where |
|  1 | SIMPLE | sra | ref | | idx_user_id | 123     | const |    1 | Using where |
+----+-------------+-------+--------+ -----+------------------------------------------

　　

　　

　　

6. 条件下推

　　

外部查询条件不能够下推到复杂的视图或子查询的情况有：

聚合子查询；
含有LIMIT的子查询；
UNION 或UNION ALL子查询；
输出字段中的子查询；

如下面的语句，从执行计划可以看出其条件作用于聚合子查询之后：
SELECT *
FROM   (SELECT target,
               Count(*)
        FROM   operation
        GROUP  BY target) t
WHERE  target = 'rm-xxxx'
+----+-------------+------------+-------+---------------+-------------+---------+-------+------+-------------+
| id | select_type | table      | type  | possible_keys | key         | key_len | ref   | rows | Extra |
+----+-------------+------------+-------+---------------+-------------+---------+-------+------+-------------+
| 1 | PRIMARY | <derived2> | ref   | <auto_key0> | <auto_key0> | 514     | const | 2 | Using where |
| 2 | DERIVED | operation | index | idx_4 | idx_4 | 519     | NULL  | 20 | Using index |
+----+-------------+------------+-------+---------------+-------------+---------+-------+------+-------------+

确定从语义上查询条件可以直接下推后，重写如下：

SELECT target,
       Count(*)
FROM   operation
WHERE  target = 'rm-xxxx' 
GROUP  BY target

执行计划变为：
+----+-------------+-----------+------+---------------+-------+---------+-------+------+--------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+-------+---------+-------+------+--------------------+
| 1 | SIMPLE | operation | ref | idx_4 | idx_4 | 514 | const | 1 | Using where; Using index |
+----+-------------+-----------+------+---------------+-------+---------+-------+------+--------------------+

　　

　　

7. 提前缩小范围

　　

先上初始SQL语句：
SELECT *
FROM   my_order o
       LEFT JOIN my_userinfo u
              ON o.uid = u.uid
       LEFT JOIN my_productinfo p
              ON o.pid = p.pid
WHERE  ( o.display = 0 )
       AND ( o.ostaus = 1 )
ORDER  BY o.selltime DESC 
LIMIT  0, 15


该SQL语句原意是：先做一系列的左连接，然后排序取前15条记录。从执行计划也可以看出，最后一步估算排序记录数为90万，时间消耗为12秒。
+----+-------------+-------+--------+---------------+---------+---------+-----------------+--------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+-----------------+--------+----------------------------------------------------+
|  1 | SIMPLE | o | ALL | NULL | NULL | NULL | NULL | 909119 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | u | eq_ref | PRIMARY | PRIMARY | 4 | o.uid | 1 | NULL |
|  1 | SIMPLE | p | ALL | PRIMARY | NULL | NULL | NULL |      6 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+--------+---------------+---------+---------+-----------------+--------+----------------------------------------------------+

由于最后WHERE条件以及排序均针对最左主表，因此可以先对my_order排序提前缩小数据量再做左连接。SQL重写后如下，执行时间缩小为1毫秒左右。
SELECT *
FROM (
SELECT *
FROM   my_order o
WHERE  ( o.display = 0 )
       AND ( o.ostaus = 1 )
ORDER  BY o.selltime DESC 
LIMIT  0, 15
) o
     LEFT JOIN my_userinfo u
              ON o.uid = u.uid
     LEFT JOIN my_productinfo p
              ON o.pid = p.pid
ORDER BY  o.selltime DESC
limit 0, 15

再检查执行计划：子查询物化后（select_type=DERIVED)参与JOIN。虽然估算行扫描仍然为90万，但是利用了索引以及LIMIT 子句后，实际执行时间变得很小。
+----+-------------+------------+--------+---------------+---------+---------+-------+--------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+---------------+---------+---------+-------+--------+----------------------------------------------------+
|  1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL |     15 | Using temporary; Using filesort |
| 1 | PRIMARY | u | eq_ref | PRIMARY | PRIMARY | 4 | o.uid | 1 | NULL |
|  1 | PRIMARY | p | ALL | PRIMARY | NULL | NULL | NULL |      6 | Using where; Using join buffer (Block Nested Loop) |
| 2 | DERIVED | o | index | NULL | idx_1 | 5 | NULL | 909112 | Using where |
+----+-------------+------------+--------+---------------+---------+---------

　　

　　

8. 中间结果集下推

　　

再来看下面这个已经初步优化过的例子(左连接中的主表优先作用查询条件)：
SELECT    a.*,
          c.allocated
FROM      (
              SELECT   resourceid
              FROM     my_distribute d
                   WHERE    isdelete = 0 
                   AND      cusmanagercode = '1234567' 
                   ORDER BY salecode limit 20) a
LEFT JOIN 
          (
              SELECT   resourcesid， sum(ifnull(allocation, 0) * 12345) allocated
              FROM     my_resources
                   GROUP BY resourcesid) c
ON        a.resourceid = c.resourcesid

那么该语句还存在其它问题吗？不难看出子查询 c 是全表聚合查询，在表数量特别大的情况下会导致整个语句的性能下降。

其实对于子查询 c，左连接最后结果集只关心能和主表resourceid能匹配的数据。因此我们可以重写语句如下，执行时间从原来的2秒下降到2毫秒。
SELECT    a.*,
          c.allocated
FROM      (
                   SELECT   resourceid
                   FROM     my_distribute d
                   WHERE    isdelete = 0 
                   AND      cusmanagercode = '1234567' 
                   ORDER BY salecode limit 20) a
LEFT JOIN 
          (
                   SELECT   resourcesid， sum(ifnull(allocation, 0) * 12345) allocated
                   FROM     my_resources r,
                            (
                                     SELECT   resourceid
                                     FROM     my_distribute d
                                     WHERE    isdelete = 0 
                                     AND      cusmanagercode = '1234567' 
                                     ORDER BY salecode limit 20) a
                   WHERE    r.resourcesid = a.resourcesid
                   GROUP BY resourcesid) c
ON        a.resourceid = c.resourcesid

但是子查询 a 在我们的SQL语句中出现了多次。这种写法不仅存在额外的开销，还使得整个语句显的繁杂。使用WITH语句再次重写：
WITH a AS
(
         SELECT   resourceid
         FROM     my_distribute d
         WHERE    isdelete = 0 
         AND      cusmanagercode = '1234567' 
         ORDER BY salecode limit 20)
SELECT    a.*,
          c.allocated
FROM      a
LEFT JOIN 
          (
                   SELECT   resourcesid， sum(ifnull(allocation, 0) * 12345) allocated
                   FROM     my_resources r,
                            a
                   WHERE    r.resourcesid = a.resourcesid
                   GROUP BY resourcesid) c
ON        a.resourceid = c.resourcesid