パーティションテーブルシーンの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を最適化するには、一般的に実行計画を見て、できるだけインデックスを使っているかどうかを観察しながら、予測スキャンの行数に注目し、また、仮テーブルが発生しているかどうか、または順序付けが必要かどうかを確認し、これらを解消する方法を考えます。
更に最適化戦略はプログラムコードの論理を調整しなければならないかもしれません。技術構造や業務需要まで、この動作は比較的大きいです。一般的に非核心システム上の核心問題はそんなに大きな問題ではないです。
SQL性能ボトルネック位置決め
今、私達はこのSQLの実行計画を見に来ました。

[email protected][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もありません。しかし、私達も気になります。スキャンする行数はやはり多いローソン：9384602で、しかもzheng全体のパーティションのすべてのデータをスキャンします。
思考を最適化する
私達はこのSQLがいつもある日のデータを調べなければならないことに気づきました。この表はすでに天の分区によって作られました。WHERE子文の時間条件を無視できますか？
また、date条件がなくなった以上、逆観表DDLは、残りの条件に該当するインデックスがないようですね？
したがって、索引を新規作成してみます。

[email protected][myDB]> ALTER TABLE t1 ADD INDEX iid (iid, icnt);

そして、SQLを次のように改造して、実行計画を見てください。

[email protected][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
     ，   。

   ，           ，           ：

[email protected][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最適化性能ボトルネック問題が発生したら、技術グループで教えてもらいたい時、まず必要な情報を提供してください。

表DDL

テーブルの一般的な統計情報は、SHOW TABLE STATUS LIKE‘t 1’を実行して

を見ることができます。

表インデックス分布情報は、SHOW＿INDEX＿FROM t 1を実行して

を見ることができます。

問題があるSQL及びそれに対応する実行計画にこれらの情報がないなら、他の人を煩わすことはないでしょう。

以上はパーティションテーブルのシーンのSQL最適化の詳細です。sqlパーティションの最適化に関する資料は他の関連記事に注目してください。