導(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è)必要的信息：
以上就是分區(qū)表場景下的 SQL 優(yōu)化的詳細(xì)內(nèi)容，更多關(guān)于sql分區(qū)表優(yōu)化的資料請關(guān)注腳本之家其它相關(guān)文章！
標(biāo)簽：武威 濟(jì)寧 廣東 汕頭 安徽 臺州 泰安 濟(jì)源
巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《分區(qū)表場景下的 SQL 優(yōu)化》，本文關(guān)鍵詞 分區(qū)表,場景,下,的,SQL,優(yōu)化,；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題，煩請?zhí)峁┫嚓P(guān)信息告之我們，我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無關(guān)。
