域名預(yù)訂/競(jìng)價(jià)，好“米”不錯(cuò)過(guò)

這篇文章主要介紹了分區(qū)表場(chǎng)景下的 SQL 優(yōu)化，幫助大家更好的理解和學(xué)習(xí)SQL，感興趣的朋友可以了解下

導(dǎo)讀

有個(gè)表做了分區(qū)，每天一個(gè)分區(qū)。

該表上有個(gè)查詢(xún)，經(jīng)常只查詢(xún)表中某一天數(shù)據(jù)，但每次都幾乎要掃描整個(gè)分區(qū)的所有數(shù)據(jù)，有什么辦法進(jìn)行優(yōu)化嗎？

待優(yōu)化場(chǎng)景

有一個(gè)大表，每天產(chǎn)生的數(shù)據(jù)量約100萬(wàn)，所以就采用表分區(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ā)生下面的慢查詢(xún)：

SELECT ... FROM `t1` WHERE `date` = '2017-04-01' AND `icnt` > 300 AND `id` = '801301';

SQL優(yōu)化之路

SQL優(yōu)化思路

想要優(yōu)化一個(gè)SQL，一般來(lái)說(shuō)就是先看執(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è)動(dòng)作比較大，一般非核心系統(tǒng)上的核心問(wèn)題，不會(huì)這么大動(dòng)干戈，絕大多數(shù)情況，還是需要靠DBA盡可能發(fā)揮聰明才智來(lái)解決。

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

現(xiàn)在，我們來(lái)看下這個(gè)SQL的執(zhí)行計(jì)劃：

這個(gè)執(zhí)行計(jì)劃看起來(lái)還好，有索引可用，也沒(méi)臨時(shí)表，也沒(méi)filesort。不過(guò)，我們也注意到，預(yù)計(jì)要掃描的行數(shù)還是挺多的 rows: 9384602，而且要掃描zheng整個(gè)分區(qū)的所有數(shù)據(jù)，難怪效率不高，總是SLOW QUERY。

優(yōu)化思考

我們注意到這個(gè)SQL總是要查詢(xún)某一天的數(shù)據(jù)，這個(gè)表已經(jīng)做了按天分區(qū)，那是不是可以忽略 WHERE 子句中的 時(shí)間條件呢？

還有，既然去掉了 date 條件，反觀表DDL，剩下的條件貌似就沒(méi)有合適的索引了吧？

所以，我們嘗試新建一個(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通過(guò)添加索引、適當(dāng)調(diào)整SQL代碼（例如調(diào)整驅(qū)動(dòng)表順序）等簡(jiǎn)單手法來(lái)完成。

多說(shuō)幾句，遇到SQL優(yōu)化性能瓶頸問(wèn)題想要在技術(shù)群里請(qǐng)教時(shí)，麻煩先提供幾個(gè)必要的信息：

表DDL

表常規(guī)統(tǒng)計(jì)信息，可執(zhí)行 SHOW TABLE STATUS LIKE ‘t1' 查看

表索引分布信息，可執(zhí)行 SHOW INDEX FROM t1 查看

有問(wèn)題的SQL及相應(yīng)的執(zhí)行計(jì)劃 沒(méi)有這些信息的話，就別去麻煩別人了吧。

以上就是分區(qū)表場(chǎng)景下的 SQL 優(yōu)化的詳細(xì)內(nèi)容，更多關(guān)于sql分區(qū)表優(yōu)化的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

來(lái)源：腳本之家

鏈接：https://www.jb51.net/article/193531.htm

申請(qǐng)創(chuàng)業(yè)報(bào)道，分享創(chuàng)業(yè)好點(diǎn)子。點(diǎn)擊此處，共同探討創(chuàng)業(yè)新機(jī)遇！