得物效率前端微應用推進過程與思考
一、背景效率工程隨著業務的發展，組織規模的擴大，越來越多的企業開始意識到協作效率對于企業團隊的重要性，甚至是決定其在某個行業競爭中突圍的關鍵，是企業長久生存的根本。得物
得益于摩爾定律，計算機性能已大幅提升，加上數據庫的進步以及微服務所倡導的各種反模式設計。因此，我們現在編寫復雜SQL查詢的機會越來越少。業界(是的，甚至包括谷歌)已經開始提倡不要進行專門的SQL優化，因為節省下來的資源并不足以抵消員工的工資成本。但是，作為工程師，我們應該在技術上努力追求卓越，成為本領域的頂尖科學家。
在這里，將介紹7個常見的SQL慢查詢語句，并解釋如何優化它們的性能。希望這對你有所幫助。
由DALLE-3生成
分頁是最常用的方案之一，但也容易出現問題。例如，對于以下簡單的語句，DBA通常建議的解決方案是添加一個包含type、name和create_time字段的復合索引。這樣，條件和排序就可以有效利用索引，從而顯著提高性能。
SELECT *FROM operationWHERE type = 'SQLStats' AND name = 'SlowLog'ORDER BY create_timeLIMIT 1000, 10;這可能會解決90%以上DBA的問題。但是，當LIMIT子句變成“LIMIT 1000000, 10”時，程序員仍會抱怨“為什么在只查詢10條記錄的時候，速度還這么慢?” 要知道，數據庫不知道第1000000條記錄從何處開始，所以即使有索引，它仍需要從頭開始計算。在大多數情況下，這個性能問題是由于懶惰編程造成的。
在前端數據瀏覽或批量導出大量數據的場景中，可以使用上一頁的最大值作為查詢參數。SQL可以重新設計如下:
SELECT *FROM operationWHERE type = 'SQLStats'AND name = 'SlowLog'AND create_time > '2017-03-16 14:00:00'ORDER BY create_timeLIMIT 10;采用這種新設計后，查詢時間保持不變，不會隨著數據量的增加而變化。
SQL語句中另一個常見的錯誤是查詢變量和字段定義的類型不匹配。以下面的語句為例：
mysql> explain extended SELECT * > FROM my_balance b > WHERE b.bpn = 14000000123 > AND b.isverified IS NULL ;mysql> show warnings;| Warning | 1739 | Cannot use ref access on index 'bpn' due to type or collation conversion on field 'bpn'在這種情況下，字段bpn被定義為varchar(20)，而MySQL的策略是在比較之前將字符串轉換為數字。這會導致函數被應用到表字段上，從而使索引失效。
這種情況可能是由應用程序框架自動填充參數造成的，而不是程序員的本意。如今，應用程序框架通常都很復雜，雖然它們提供了便利，但也可能帶來隱患。
盡管MySQL 5.6引入了物化，但它只優化了SELECT語句。對于UPDATE或DELETE語句，需要使用JOIN手動重寫。
例如，請看下面的UPDATE語句。MySQL實際上執行了一個循環/嵌套子查詢(DEPENDENT SUBQUERY)，執行時間可想而知。
UPDATE operation oSET 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);執行計劃如下:
+----+--------------------+-------+-------+---------------+---------+---------+-------+------+-----------------------------------------------------+| 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 |+----+--------------------+-------+-------+---------------+---------+---------+-------+------+-----------------------------------------------------+將其重寫為JOIN后，子查詢的選擇類型從DEPENDENT SUBQUERY變為DERIVED，執行時間顯著得從7秒縮短到2毫秒。
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.idSET status = 'applying';簡化后的執行計劃如下:
+----+-------------+-------+------+---------------+-------+---------+-------+------+-----------------------------------------------------+| 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 |+----+-------------+-------+------+---------------+-------+---------+-------+------+-----------------------------------------------------+MySQL無法利用索引進行混合排序。但是，在某些場景下，仍然可以使用特殊方法來提高性能。
SELECT *FROM my_order o INNER JOIN my_appraise a ON a.orderid = o.idORDER BY a.is_reply ASC, a.appraise_time DESCLIMIT 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)) tORDER BY is_reply ASC, appraisetime DESCLIMIT 20;在處理EXISTS子句時，MySQL仍然使用嵌套子查詢進行執行。以下面的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 |+----+-------------+-------+--------+ -----+------------------------------------------+---------+ -----+------+ -----+在某些情況下，外部查詢條件無法下推到復雜的視圖或子查詢中:
請看下面的語句，其中的條件會影響聚合子查詢：
SELECT *FROM (SELECT target, Count(*) FROM operation GROUP BY target) tWHERE target = 'rm-xxxx';+----+-------------+------------+-------+---------------+-------------+---------+-------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+------------+-------+---------------+-------------+---------+-------+------+-------------+| 1 | PRIMARY | n | ALL | NULL | NULL | NULL | NULL | 1086041 | Using where || 1 | PRIMARY | sra | ref | NULL | idx_user_id | 123 | const | 1 | Using where || 2 | DEPENDENT SUBQUERY | m | ref | NULL | 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 |+----+-------------+-------+--------+ -----+------------------------------------------+---------+ -----+------+ -----+讓我們看看以下經過部分優化的示例（左連接中的主表作為主查詢條件）：
SELECT a.*, c.allocatedFROM ( SELECT resourceid FROM my_distribute d WHERE isdelete = 0 AND cusmanagercode = '1234567' ORDER BY salecode limit 20) aLEFT JOIN ( SELECT resourcesid， sum(ifnull(allocation, 0) * 12345) allocated FROM my_resources GROUP BY resourcesid) cON a.resourceid = c.resourcesid;這條語句是否還存在其他問題？很明顯，子查詢c是對整個表進行聚合查詢，在處理大量表時可能會導致性能下降。
事實上，對于子查詢c，左連接的結果集只關心可以與主表的resourceid匹配的數據。因此，我們可以將語句重寫如下，將執行時間從2秒減少到2毫秒：
SELECT a.*, c.allocatedFROM ( SELECT resourceid FROM my_distribute d WHERE isdelete = 0 AND cusmanagercode = '1234567' ORDER BY salecode limit 20) aLEFT 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) cON 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.allocatedFROM aLEFT JOIN ( SELECT resourcesid， sum(ifnull(allocation, 0) * 12345) allocated FROM my_resources r, a WHERE r.resourcesid = a.resourcesid GROUP BY resourcesid) cON a.resourceid = c.resourcesid;數據庫編譯器生成的執行計劃決定了SQL語句的實際執行方式。但是，編譯器只能盡力提供服務，沒有一個數據庫編譯器是完美的。上述情況在其他數據庫中也同樣存在。了解了數據庫編譯器的特性，我們就能繞過它的限制，編寫出高性能的SQL語句。
在設計數據模型和編寫SQL語句時，將算法思維或算法意識引入到這個過程非常重要。在編寫復雜的SQL語句時，養成使用WITH語句的習慣可以簡化語句，減輕數據庫的負擔。
最后，下面是SQL語句的執行順序：
FROMON JOINWHEREGROUP BYHAVINGSELECTDISTINCTORDER BYLIMIT本文鏈接：http://www.tebozhan.com/showinfo-26-90655-0.html七個常見的SQL慢查詢問題，及其解決方法
聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯系，我們將在第一時間刪除處理。郵件：2376512515@qq.com
下一篇： 水下數據中心的殺手：聲波攻擊
Copyright ? 2016-2023 天津谷騏科技有限公司 版權所有 sitemap.xml
違法及侵權請聯系：2376512515@qq.com 津ICP備18001702號
津公網安備 12010102000574號