導讀
有個表做了分區(qū)，每天一個分區(qū)。
該表上有個查詢，經(jīng)常只查詢表中某一天數(shù)據(jù)，但每次都幾乎要掃描整個分區(qū)的所有數(shù)據(jù)，有什么辦法進行優(yōu)化嗎？
待優(yōu)化場景
有一個大表，每天產(chǎn)生的數(shù)據(jù)量約100萬，所以就采用表分區(qū)方案，每天一個分區(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)化一個SQL，一般來說就是先看執(zhí)行計劃，觀察是否盡可能用到索引，同時要關(guān)注預計掃描的行數(shù)，以及是否產(chǎn)生了臨時表（Using temporary） 或者 是否需要進行排序（Using filesort），想辦法消除這些情況。
更進一步的優(yōu)化策略則可能需要調(diào)整程序代碼邏輯，甚至技術(shù)架構(gòu)或者業(yè)務(wù)需求，這個動作比較大，一般非核心系統(tǒng)上的核心問題，不會這么大動干戈，絕大多數(shù)情況，還是需要靠DBA盡可能發(fā)揮聰明才智來解決。
SQL性能瓶頸定位
現(xiàn)在，我們來看下這個SQL的執(zhí)行計劃：
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
這個執(zhí)行計劃看起來還好，有索引可用，也沒臨時表，也沒filesort。不過，我們也注意到，預計要掃描的行數(shù)還是挺多的 rows: 9384602，而且要掃描zheng整個分區(qū)的所有數(shù)據(jù)，難怪效率不高，總是SLOW QUERY。
優(yōu)化思考
我們注意到這個SQL總是要查詢某一天的數(shù)據(jù)，這個表已經(jīng)做了按天分區(qū)，那是不是可以忽略 WHERE 子句中的 時間條件呢？
還有，既然去掉了 date 條件，反觀表DDL，剩下的條件貌似就沒有合適的索引了吧？
所以，我們嘗試新建一個索引：
yejr@imysql.com[myDB]> ALTER TABLE t1 ADD INDEX iid (iid, icnt);
然后，把SQL改造成下面這樣，再看下執(zhí)行計劃：
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)化效果，杠杠滴。
事實上，如果不強制指定分區(qū)的話，也是可以達到優(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通過添加索引、適當調(diào)整SQL代碼（例如調(diào)整驅(qū)動表順序）等簡單手法來完成。
多說幾句，遇到SQL優(yōu)化性能瓶頸問題想要在技術(shù)群里請教時，麻煩先提供幾個必要的信息：
- 表DDL
- 表常規(guī)統(tǒng)計信息，可執(zhí)行 SHOW TABLE STATUS LIKE ‘t1' 查看
- 表索引分布信息，可執(zhí)行 SHOW INDEX FROM t1 查看
- 有問題的SQL及相應(yīng)的執(zhí)行計劃 沒有這些信息的話，就別去麻煩別人了吧。
以上就是分區(qū)表場景下的 SQL 優(yōu)化的詳細內(nèi)容，更多關(guān)于sql分區(qū)表優(yōu)化的資料請關(guān)注腳本之家其它相關(guān)文章！
您可能感興趣的文章:- MySQL優(yōu)化之分區(qū)表
- 詳解MySQL分區(qū)表
- MySQL最佳實踐之分區(qū)表基本類型
- MySQL分區(qū)表的正確使用方法
- MySQL分區(qū)表的局限和限制詳解
- PostgreSQL分區(qū)表（partitioning）應(yīng)用實例詳解
- Mysql分區(qū)表的管理與維護
- PostgreSQL教程(三)：表的繼承和分區(qū)表詳解
- Rails中使用MySQL分區(qū)表一個提升性能的方法
- mysql使用教程之分區(qū)表的使用方法(刪除分區(qū)表)
