天天看點
傳回

為DISTINCT、GROUP BY和ORDER BY效率問題提提速

廢話不多說，直擊問題處理過程...

問題描述

原SQL，多表關聯，order表4w，order_trace表24w資料，按照正常邏輯，就算關聯7表，查詢其他表得字段很多，查詢分頁得到資料速度平均在1.300s，肯定存在問題。

SELECT DISTINCT
    (a.order_no),
    a.`name` order_name,
    a.eid,
    a.uid,
    a.device_type_id,
    a.num,
    a.`desc`,
    a.attach,
    a.total_amount amount,
    a.ctime,
    a.publish_time,
    a.finish_time,
    a.`status`,
    a.ent_name,
    a.ent_user_name,
    a.ent_user_phone,
    a.cust_id,
    a.cust_name,
    a.start_work_time,
    a.expire_time,

IF (
    (
        NOW() > a.expire_time
        AND a.`status` = 4
    )
    OR (
        a.cancel_time > a.expire_time
        AND a.`status` = 5
    )
    OR b.`type` = 2,
    '1',
    '2'
) 'is_expire',
 d.parent_id,
 e.`name` parent_name,
 d.`name` device_type_name,
 b.eid AS `user.eid`,
 b.uid AS `user.uid`,
 b.ctime AS `user.ctime`,
 c.`name` AS `orderAddress.name`,
 c.phone AS `orderAddress.phone`,
 c.province_id AS `orderAddress.province_id`,
 c.province_name AS `orderAddress.province_name`,
 c.city_id AS `orderAddress.city_id`,
 c.city_name AS `orderAddress.city_name`,
 c.county_id AS `orderAddress.county_id`,
 c.county_name AS `orderAddress.county_name`,
 c.detail_address AS `orderAddress.detail_address`,
 c.`desc` AS `orderAddress.desc`,
 c.lng AS `orderAddress.lng`,
 c.lat AS `orderAddress.lat`,
 b.`type` AS `userOrder.type`,
 b.`cntr_id` AS `userOrder.cntr_id`,
 b.`name` AS `userOrder.name`,
 b.`phone` AS `userOrder.phone`,
 b.dispatch_uid AS `userOrder.dispatch_uid`,
 b.dispatch_time AS `userOrder.dispatch_time`,
 b.type AS `user_order_type`,
 g.attn_name AS `order_attr.attn_name`,
 g.attn_phone AS `order_attr.attn_phone`,
 a.settle_type
FROM
    `order` a
LEFT JOIN user_order b ON a.order_no = b.order_no
AND b.`status` = 1
LEFT JOIN order_address c ON a.order_no = c.order_no
LEFT JOIN category d ON a.device_type_id = d.id
LEFT JOIN category e ON d.parent_id = e.id
LEFT JOIN order_item f ON f.order_no = a.order_no
LEFT JOIN order_attr g ON g.order_no = a.order_no
WHERE
    1 = 1
AND (
    a.`status` IN (4, 5, 6, 7, 12)
    AND a.payway IS NOT NULL
    OR a.`status` = 14
    OR a.`status` = 10
)
AND (
    a.`status` != 4
    OR a.audit_time IS NOT NULL
)
ORDER BY
    a.order_no DESC
LIMIT 12 OFFSET 0;

問題排查

EXPLAIN一下

+----+-------------+-------+--------+-------------------------------------------------------------+--------------+---------+------------------------+-------+----------------------------------------------+
| id | select_type | table | type   | possible_keys                                               | key          | key_len | ref                    | rows  | Extra                                        |
+----+-------------+-------+--------+-------------------------------------------------------------+--------------+---------+------------------------+-------+----------------------------------------------+
|  1 | SIMPLE      | a     | ALL    | NULL                                                        | NULL         | NULL    | NULL                   | 41708 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | b     | ref    | idx_order_no,idx_order_no_status,idx_order_no_complete_time | idx_order_no | 9       | order.a.order_no       |     1 | Using where                                  |
|  1 | SIMPLE      | c     | eq_ref | PRIMARY,idx_order_no_city_id,idx_order_no_county_id         | PRIMARY      | 8       | order.a.order_no       |     1 | NULL                                         |
|  1 | SIMPLE      | d     | eq_ref | PRIMARY                                                     | PRIMARY      | 4       | order.a.device_type_id |     1 | NULL                                         |
|  1 | SIMPLE      | e     | eq_ref | PRIMARY                                                     | PRIMARY      | 4       | order.d.parent_id      |     1 | NULL                                         |
|  1 | SIMPLE      | f     | ref    | idx_order_no                                                | idx_order_no | 9       | order.a.order_no       |     1 | Using index                                  |
|  1 | SIMPLE      | g     | eq_ref | PRIMARY                                                     | PRIMARY      | 8       | order.a.order_no       |     1 | Using where                                  |
+----+-------------+-------+--------+-------------------------------------------------------------+--------------+---------+------------------------+-------+----------------------------------------------+
7 rows in set

發現問題一：

　　type=ALL表示索引沒用到，rows=41708行，證明使用了全表掃描，Extra包含了Using filesort表示排序的字段上出了問題，是以索引了ORDER BY。

　　解決方式：

　　order_no生成順序是根據時間生成的，是以，ORDER BY order_no也是可行的，就沒必要給ctime建立索引。

　　結果：

　　處理完成之後發現，執行的查詢結果時間幾乎看不出來差距，此時得知問題并不完全出現在ORDER BY上，繼續排查。。。

EXPLAIN一下

+----+-------------+-------+--------+-------------------------------------------------------------+--------------+---------+------------------------+------+------------------------------+
| id | select_type | table | type   | possible_keys                                               | key          | key_len | ref                    | rows | Extra                        |
+----+-------------+-------+--------+-------------------------------------------------------------+--------------+---------+------------------------+------+------------------------------+
|  1 | SIMPLE      | a     | index  | NULL                                                        | PRIMARY      | 8       | NULL                   |   12 | Using where; Using temporary |
|  1 | SIMPLE      | b     | ref    | idx_order_no,idx_order_no_status,idx_order_no_complete_time | idx_order_no | 9       | order.a.order_no       |    1 | Using where                  |
|  1 | SIMPLE      | c     | eq_ref | PRIMARY,idx_order_no_city_id,idx_order_no_county_id         | PRIMARY      | 8       | order.a.order_no       |    1 | NULL                         |
|  1 | SIMPLE      | d     | eq_ref | PRIMARY                                                     | PRIMARY      | 4       | order.a.device_type_id |    1 | NULL                         |
|  1 | SIMPLE      | e     | eq_ref | PRIMARY                                                     | PRIMARY      | 4       | order.d.parent_id      |    1 | NULL                         |
|  1 | SIMPLE      | f     | ref    | idx_order_no                                                | idx_order_no | 9       | order.a.order_no       |    1 | Using index                  |
|  1 | SIMPLE      | g     | eq_ref | PRIMARY                                                     | PRIMARY      | 8       | order.a.order_no       |    1 | Using where                  |
+----+-------------+-------+--------+-------------------------------------------------------------+--------------+---------+------------------------+------+------------------------------+

發現問題二：

Extra包含了Using temporary表示用上了臨時表，這是效率慢的一大因素。而DISTINCT是一個SQL優化能不用就不用的關鍵字，但是因為業務和資料結構的問題的問題，user_order和order_item表是一對多關系，是以DISTINCT是一個需要處理的點。DISTINCT和GROUP BY的原理基本一緻，但有一些地方還不一緻，是以仍然執行分組操作，意外收獲，不使用了臨時表，并且效率提升了13倍，查詢時間控制在0.100s左右，完全滿足查詢效率問題

　　解決方式：

SELECT
    a.order_no,
    a.`name` order_name,
    a.eid,
    a.uid,
    a.device_type_id,
    a.num,
    a.`desc`,
    a.attach,
    a.total_amount amount,
    a.ctime,
    a.publish_time,
    a.finish_time,
    a.`status`,
    a.ent_name,
    a.ent_user_name,
    a.ent_user_phone,
    a.cust_id,
    a.cust_name,
    a.start_work_time,
    a.expire_time,

IF (
    (
        NOW() > a.expire_time
        AND a.`status` = 4
    )
    OR (
        a.cancel_time > a.expire_time
        AND a.`status` = 5
    )
    OR b.`type` = 2,
    '1',
    '2'
) 'is_expire',
 d.parent_id,
 e.`name` parent_name,
 d.`name` device_type_name,
 b.eid AS `user.eid`,
 b.uid AS `user.uid`,
 b.ctime AS `user.ctime`,
 c.`name` AS `orderAddress.name`,
 c.phone AS `orderAddress.phone`,
 c.province_id AS `orderAddress.province_id`,
 c.province_name AS `orderAddress.province_name`,
 c.city_id AS `orderAddress.city_id`,
 c.city_name AS `orderAddress.city_name`,
 c.county_id AS `orderAddress.county_id`,
 c.county_name AS `orderAddress.county_name`,
 c.detail_address AS `orderAddress.detail_address`,
 c.`desc` AS `orderAddress.desc`,
 c.lng AS `orderAddress.lng`,
 c.lat AS `orderAddress.lat`,
 b.`type` AS `userOrder.type`,
 b.`cntr_id` AS `userOrder.cntr_id`,
 b.`name` AS `userOrder.name`,
 b.`phone` AS `userOrder.phone`,
 b.dispatch_uid AS `userOrder.dispatch_uid`,
 b.dispatch_time AS `userOrder.dispatch_time`,
 b.type AS `user_order_type`,
 g.attn_name AS `order_attr.attn_name`,
 g.attn_phone AS `order_attr.attn_phone`,
 a.settle_type
FROM
    `order` a
LEFT JOIN user_order b ON a.order_no = b.order_no
AND b.`status` = 1
LEFT JOIN order_address c ON a.order_no = c.order_no
LEFT JOIN category d ON a.device_type_id = d.id
LEFT JOIN category e ON d.parent_id = e.id
LEFT JOIN order_item f ON f.order_no = a.order_no
LEFT JOIN order_attr g ON g.order_no = a.order_no
WHERE
    1 = 1
AND (
    a.`status` IN (4, 5, 6, 7, 12)
    AND a.payway IS NOT NULL
    OR a.`status` = 14
    OR a.`status` = 10
)
AND (
    a.`status` != 4
    OR a.audit_time IS NOT NULL
)
GROUP BY
    a.order_no
ORDER BY
    a.order_no DESC
LIMIT 12 OFFSET 0;

　　原理：

　　如果GROUP BY 的列沒有索引，産生臨時表。

　　MYSQL5.6中解釋：The most general way to satisfy a GROUP BY clause is to scan the whole table and create a new temporary table where all rows from each group are consecutive, and then use this temporary table to discover groups and apply aggregate functions (if any). In some cases, MySQL is able to do much better than that and avoid creation of temporary tables by using index access.通過使用索引通路避免建立臨時表。

　　而DISTINCT與ORDER BY 使用同一字段并且索引，仍生産臨時表，是以不建議使用DISTINCT

總結

執行效率的排查，EXPLAIN是非常重要的，首先排查是否使用索引，其次排查是否使用了臨時表，如果效率還不行，就是得深入研究，或者考慮NOSQL進行緩存了

mysql
上一篇: 關于MySQL,Oracle和SQLServer的特點以及之間差別
下一篇: 每日一問：講講 JVM 的類加載機制

繼續閱讀