分區(qū)表場景下的 SQL 優(yōu)化
導(dǎo)讀
有個(gè)表做了分區(qū)，每天一個(gè)分區(qū)。
該表上有個(gè)查詢，經(jīng)常只查詢表中某一天數(shù)據(jù)，但每次都幾乎要掃描整個(gè)分區(qū)的所有數(shù)據(jù)，有什么辦法進(jìn)行優(yōu)化嗎？
待優(yōu)化場景
有一個(gè)大表，每天產(chǎn)生的數(shù)據(jù)量約100萬，所以就采用表分區(qū)方案，每天一個(gè)分區(qū)。
下面是該表的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,
該表上經(jīng)常發(fā)生下面的慢查詢：
SELECT ... FROM `t1` WHERE `date` = '2017-04-01' AND `icnt` > 300 AND `id` = '801301';
SQL優(yōu)化之路
SQL優(yōu)化思路
想要優(yōu)化一個(gè)SQL，一般來說就是先看執(zhí)行計(jì)劃，觀察是否盡可能用到索引，同時(shí)要關(guān)注預(yù)計(jì)掃描的行數(shù)，以及是否產(chǎn)生了臨時(shí)表（Using temporary） 或者 是否需要進(jìn)行排序（Using filesort），想辦法消除這些情況。
更進(jìn)一步的優(yōu)化策略則可能需要調(diào)整程序代碼邏輯，甚至技術(shù)架構(gòu)或者業(yè)務(wù)需求，這個(gè)動作比較大，一般非核心系統(tǒng)上的核心問題，不會這么大動干戈，絕大多數(shù)情況，還是需要靠DBA盡可能發(fā)揮聰明才智來解決。
SQL性能瓶頸定位
現(xiàn)在，我們來看下這個(gè)SQL的執(zhí)行計(jì)劃：
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
這個(gè)執(zhí)行計(jì)劃看起來還好，有索引可用，也沒臨時(shí)表，也沒filesort。不過，我們也注意到，預(yù)計(jì)要掃描的行數(shù)還是挺多的 rows: 9384602，而且要掃描zheng整個(gè)分區(qū)的所有數(shù)據(jù)，難怪效率不高，總是SLOW QUERY。
優(yōu)化思考
我們注意到這個(gè)SQL總是要查詢某一天的數(shù)據(jù)，這個(gè)表已經(jīng)做了按天分區(qū)，那是不是可以忽略 WHERE 子句中的 時(shí)間條件呢？
還有，既然去掉了 date 條件，反觀表DDL，剩下的條件貌似就沒有合適的索引了吧？
所以，我們嘗試新建一個(gè)索引：
yejr@imysql.com[myDB]> ALTER TABLE t1 ADD INDEX iid (iid, icnt);
然后，把SQL改造成下面這樣，再看下執(zhí)行計(jì)劃：
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 這優(yōu)化效果，杠杠滴。 事實(shí)上，如果不強(qiáng)制指定分區(qū)的話，也是可以達(dá)到優(yōu)化效果的： 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
后記
絕大多數(shù)的SQL通過添加索引、適當(dāng)調(diào)整SQL代碼（例如調(diào)整驅(qū)動表順序）等簡單手法來完成。
多說幾句，遇到SQL優(yōu)化性能瓶頸問題想要在技術(shù)群里請教時(shí)，麻煩先提供幾個(gè)必要的信息：
- 表DDL
- 表常規(guī)統(tǒng)計(jì)信息，可執(zhí)行 SHOW TABLE STATUS LIKE ‘t1' 查看
- 表索引分布信息，可執(zhí)行 SHOW INDEX FROM t1 查看
- 有問題的SQL及相應(yīng)的執(zhí)行計(jì)劃 沒有這些信息的話，就別去麻煩別人了吧。
以上就是分區(qū)表場景下的 SQL 優(yōu)化的詳細(xì)內(nèi)容，更多關(guān)于sql分區(qū)表優(yōu)化的資料請關(guān)注本站其它相關(guān)文章！
版權(quán)聲明：本站文章來源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請保持原文完整并注明來源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非www.sddonglingsh.com所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來，僅供學(xué)習(xí)參考，不代表本站立場，如有內(nèi)容涉嫌侵權(quán)，請聯(lián)系alex-e#qq.com處理。
關(guān)注官方微信