溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
如何優化SQL數據庫中的分區表？相信很多沒有經驗的人對此束手無策，為此本文總結了問題出現的原因和解決方法，通過這篇文章希望你能解決這個問題。
導讀
有個表做了分區，每天一個分區。
該表上有個查詢，經常只查詢表中某一天數據，但每次都幾乎要掃描整個分區的所有數據，有什么辦法進行優化嗎？
待優化場景
有一個大表，每天產生的數據量約100萬，所以就采用表分區方案，每天一個分區。
下面是該表的DDL：
CREATE TABLE `t1` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`date` date NOT NULL,
`kid` int(11) DEFAULT '0',
`uid` int(11) NOT NULL,
`iid` int(11) DEFAULT '0',
`icnt` int(8) DEFAULT '0',
`tst` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`countp` smallint(11) DEFAULT '1',
`isr` int(2) NOT NULL DEFAULT '0',
`clv` int(5) NOT NULL DEFAULT '1'，
PRIMARY KEY (`id`,`date`),
UNIQUE KEY `date` (`date`,`uid`,`iid`),
KEY `date_2` (`date`,`kid`)
) ENGINE=InnoDB AUTO_INCREMENT=3180686682 DEFAULT CHARSET=utf8mb4
/*!50500 PARTITION BY RANGE COLUMNS(`date`)
(PARTITION p20161201 VALUES LESS THAN ('2016-12-02') ENGINE = InnoDB,
PARTITION p20161202 VALUES LESS THAN ('2016-12-03') ENGINE = InnoDB,
PARTITION p20161203 VALUES LESS THAN ('2016-12-04') ENGINE = InnoDB,該表上經常發生下面的慢查詢：
SELECT ... FROM `t1` WHERE `date` = '2017-04-01' AND `icnt` > 300 AND `id` = '801301';
SQL優化之路
SQL優化思路
想要優化一個SQL，一般來說就是先看執行計劃，觀察是否盡可能用到索引，同時要關注預計掃描的行數，以及是否產生了臨時表（Using temporary） 或者 是否需要進行排序（Using filesort），想辦法消除這些情況。
更進一步的優化策略則可能需要調整程序代碼邏輯，甚至技術架構或者業務需求，這個動作比較大，一般非核心系統上的核心問題，不會這么大動干戈，絕大多數情況，還是需要靠DBA盡可能發揮聰明才智來解決。
SQL性能瓶頸定位
現在，我們來看下這個SQL的執行計劃：
yejr@imysql.com[myDB]> EXPLAIN PARTITIONS SELECT ... FROM `t1` WHERE `date` = '2017-03-02' AND `icnt` > 100 AND `iid` = '502302'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 partitions: p20170302 type: range possible_keys: date,date_2 key: date key_len: 3 ref: const rows: 9384602 Extra: Using where
這個執行計劃看起來還好，有索引可用，也沒臨時表，也沒filesort。不過，我們也注意到，預計要掃描的行數還是挺多的 rows: 9384602，而且要掃描zheng整個分區的所有數據，難怪效率不高，總是SLOW QUERY。
優化思考
我們注意到這個SQL總是要查詢某一天的數據，這個表已經做了按天分區，那是不是可以忽略 WHERE 子句中的 時間條件呢？
還有，既然去掉了 date 條件，反觀表DDL，剩下的條件貌似就沒有合適的索引了吧？
所以，我們嘗試新建一個索引：
yejr@imysql.com[myDB]> ALTER TABLE t1 ADD INDEX iid (iid, icnt);
然后，把SQL改造成下面這樣，再看下執行計劃：
yejr@imysql.com[myDB]> EXPLAIN PARTITIONS SELECT ... FROM `t1` partition(p2017030) WHERE `icnt` > 100 AND `iid` = '502302'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 partitions: p20170302 type: ref possible_keys: date,date_2,iid key: iid key_len: 10 ref: const rows: 7800 Extra: Using where 這優化效果，杠杠滴。 事實上，如果不強制指定分區的話，也是可以達到優化效果的： yejr@imysql.com[myDB]> EXPLAIN PARTITIONS SELECT ... FROM `t1` WHERE `date` = '2017-03-02' AND `icnt` > 100 AND `iid` = '502302'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 partitions: p20170302 type: ref possible_keys: date,date_2,iid key: iid key_len: 10 ref: NULL rows: 7800 Extra: Using where
后記
絕大多數的SQL通過添加索引、適當調整SQL代碼（例如調整驅動表順序）等簡單手法來完成。
多說幾句，遇到SQL優化性能瓶頸問題想要在技術群里請教時，麻煩先提供幾個必要的信息：
看完上述內容，你們掌握如何優化SQL數據庫中的分區表的方法了嗎？如果還想學到更多技能或想了解更多相關內容，歡迎關注億速云行業資訊頻道，感謝各位的閱讀！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
