explainのkey_len計算
33093 ワード
通常、SQLクエリーを最適化するときにexplainを使用してSQL実行計画を分析します.通常、組み合わせインデックスを使用する場合、インデックスが完全に使用されているとどのように判断しますか?もちろん、上級者は表の構造とSQL文を見て、いったいいくつかのフィールドを使ったことを知っています.よく知らない学生には?私たちはやはりkeyを見ることができます.lenの長さ、もちろんこの計算は少し複雑ですが、私のこのブログを見た後、あなたはきっと計算すると信じています.これは頭のいいあなたではありません.
くだらないことはあまり言わないで、私たちは直接例に行きます.表構造は以下の通りです.^^;
上の表の構造はとても簡単で、プライマリ・キー・インデックス、つまりidフィールド、もう一つの補助インデックス、つまりnameフィールドがあります.次に、SQLを実行し、実行計画を分析して、key_を見てみましょう.lenはどのように計算しますか.表には3つのレコードがあります.
次はexplainでkey_を表示します.lenの長さ(ここではkey_lenの計算のみ説明しますが、他の選択肢の意味は私の前のブログを参照してください)
key_が見えますlenの長さは60ですが、この60はどのように計算されますか.もちろん、単列インデックスであれば計算する必要はありません.意味がないので、組合せインデックスであれば、ここの長さが非常に意味があることを知っています.まず、この単列インデックスのkey_を簡単に見てみましょう.lenは60に等しく、どのように計算されますか.前のテーブル構造のnameフィールドの定義を覚えていますか?
`name`char(20)NOT NULL DEFAULT',char(20)を定義し,空ではない.
では、まず私の表で使うutf 8文字セットを計算してみましょう.では、utf 8文字セットが3バイトを占めていることはよく知られています.では、char(20)を定義します.結果を知っていますか.頭のいい君はきっと知っているに違いない.
key_len=20*3=60
計算は簡単でしょう.この状況は確かに簡単です.複雑な状況もありますね.へへ.
次のSQLを見てみましょう.nameというフィールドのインデックスを削除し、連合インデックスを追加します.key(name,name 1)
次のクエリを行います.
1番目のクエリーと2番目のクエリーの実行計画に何か違いがありますか?そう、key_lenとref列が違います.why?そしてなぜ2番目のSQL文のkey_lenは121ですが、これはどのように計算されますか?へへへ、上の計算方法で計算できないに違いない.教えてあげましょう.name 1フィールドの定義を覚えていますか? `name1` char(20) DEFAULT NULL,
name 1フィールドの定義はDEFAULT NULLであり、その他は変化していないことがわかります.だからMySQLはNULLを識別するために1バイトが必要です.
だから2番目のSQLのkey_len=20*3+(20*3+1)=121で、計算により、2つのフィールドのインデックスが完全に使用されていることがわかります.
次に、他の状況を見て、テーブルにフィールドを追加し、インデックスを追加し、範囲のクエリーを行います.
現在のテーブル構造はこうです.
SQLを見て、くだらないことはあまり言わない.
私が作成した連合インデックスidx_が表示されます.key_add_time_name 3、でも本当に全部使ったの?前は範囲クエリーなので、後のフィールドのインデックスは使えません.order by nullでなければ、Using filesortも見えます.でも私はやはりkeyに言いたいです.lenがどのように計算されているのか、timestampが4バイトを占めていることはよく知られているでしょう.答えは明らかだkeylenは4で、説明は連合インデックスidx_にのみ使用されます.key_add_time_name 3のadd_timeフィールド.
もう1つのケースを見てみましょう.charフィールドとvarcharフィールドからなる連合インデックスです.
SQLは次のとおりです.
key_が見えるlenの長さは123です.では、インデックスは完全に使われていますか?もちろんインデックスの常識はすべて完全に使ったことを知っています.みんなに伝えるためにここにいるkeylenはいったいどのように計算したのか.name3` varchar(20) NOT NULL DEFAULT ''
`name1` char(20) DEFAULT NULL,
上は2つのフィールドの定義で、1つはNULLを許可して、1つのNOT NULL、1つのchar、1つのvarchar
だからkey_len=(20*3 + 1)+(20 * 3 + 2)= 123
これにより,この組合せインデックスが完全に使用されていると判断する.ある同窓会が聞いたと信じています.+1は何ですか.+2は何ですか.これは、+1はMySQLがNULLを1バイト識別する必要があるためであり、+2はname 3フィールドがvarcharであり、長くなるフィールドが+2であるためであることを示しています.
ここまで書くと、皆さんは基本的な認識を持っていると思います.はい、多くは言わないで、公式はみんなに出して、自分で公式を使って、何度もテストをして鳥を理解します.
key_lenの長さ計算式:
varchr(10)が長くなるフィールドで、NULL=10*(character set:utf 8=3、gbk=2、latin 1=1)+1(NULL)+2(長くなるフィールド)varchr(10)が長くなるフィールドで、NULL=10*(character set:utf 8=3、gbk=2、latin 1=1)+2(長くなるフィールド)char(10)固定フィールドで、NULL=10*(character set:utf 8=3、gbk=2、latin 1=1=1)+1(NULL)char(10)固定フィールドで、NULL=10*(character set:utf 8=3、gbk=2、latin 1=1)+1(NULL)char(NULL)cha( 10)固定フィールドでNULL=10*(character set:utf 8=3、gbk=2、latin 1=1)を許可
くだらないことはあまり言わないで、私たちは直接例に行きます.表構造は以下の通りです.^^;
mysql [localhost] {msandbox} (yayun) > show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` char(20) NOT NULL DEFAULT '',
`name1` char(20) DEFAULT NULL,
`name3` varchar(20) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql [localhost] {msandbox} (yayun) > 上の表の構造はとても簡単で、プライマリ・キー・インデックス、つまりidフィールド、もう一つの補助インデックス、つまりnameフィールドがあります.次に、SQLを実行し、実行計画を分析して、key_を見てみましょう.lenはどのように計算しますか.表には3つのレコードがあります.
mysql [localhost] {msandbox} (yayun) > select * from t1;
+----+-------+-------+-----------+
| id | name | name1 | name3 |
+----+-------+-------+-----------+
| 1 | atlas | yayun | dengyayun |
| 2 | alex | talex | jalex |
| 3 | je | jetom | tomje |
+----+-------+-------+-----------+
3 rows in set (0.00 sec)
mysql [localhost] {msandbox} (yayun) > 次はexplainでkey_を表示します.lenの長さ(ここではkey_lenの計算のみ説明しますが、他の選択肢の意味は私の前のブログを参照してください)
mysql [localhost] {msandbox} (yayun) > explain select * from t1 where name='atlas';
+----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+
| 1 | SIMPLE | t1 | ref | name | name | 60 | const | 1 | Using index condition |
+----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+
1 row in set (0.03 sec)
mysql [localhost] {msandbox} (yayun) > key_が見えますlenの長さは60ですが、この60はどのように計算されますか.もちろん、単列インデックスであれば計算する必要はありません.意味がないので、組合せインデックスであれば、ここの長さが非常に意味があることを知っています.まず、この単列インデックスのkey_を簡単に見てみましょう.lenは60に等しく、どのように計算されますか.前のテーブル構造のnameフィールドの定義を覚えていますか?
`name`char(20)NOT NULL DEFAULT',char(20)を定義し,空ではない.
では、まず私の表で使うutf 8文字セットを計算してみましょう.では、utf 8文字セットが3バイトを占めていることはよく知られています.では、char(20)を定義します.結果を知っていますか.頭のいい君はきっと知っているに違いない.
key_len=20*3=60
計算は簡単でしょう.この状況は確かに簡単です.複雑な状況もありますね.へへ.
次のSQLを見てみましょう.nameというフィールドのインデックスを削除し、連合インデックスを追加します.key(name,name 1)
mysql [localhost] {msandbox} (yayun) > alter table t1 drop key name;
Query OK, 0 rows affected (0.15 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql [localhost] {msandbox} (yayun) > alter table t1 add key idx_key_name_name1 (name,name1);
Query OK, 0 rows affected (0.29 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql [localhost] {msandbox} (yayun) > 次のクエリを行います.
mysql [localhost] {msandbox} (yayun) > explain select * from t1 where name='atlas';
+----+-------------+-------+------+--------------------+--------------------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+--------------------+--------------------+---------+-------+------+-----------------------+
| 1 | SIMPLE | t1 | ref | idx_key_name_name1 | idx_key_name_name1 | 60 | const | 1 | Using index condition |
+----+-------------+-------+------+--------------------+--------------------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)
mysql [localhost] {msandbox} (yayun) > explain select * from t1 where name='atlas' and name1='yayun';
+----+-------------+-------+------+--------------------+--------------------+---------+-------------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+--------------------+--------------------+---------+-------------+------+-----------------------+
| 1 | SIMPLE | t1 | ref | idx_key_name_name1 | idx_key_name_name1 | 121 | const,const | 1 | Using index condition |
+----+-------------+-------+------+--------------------+--------------------+---------+-------------+------+-----------------------+
1 row in set (0.04 sec)
mysql [localhost] {msandbox} (yayun) > 1番目のクエリーと2番目のクエリーの実行計画に何か違いがありますか?そう、key_lenとref列が違います.why?そしてなぜ2番目のSQL文のkey_lenは121ですが、これはどのように計算されますか?へへへ、上の計算方法で計算できないに違いない.教えてあげましょう.name 1フィールドの定義を覚えていますか? `name1` char(20) DEFAULT NULL,
name 1フィールドの定義はDEFAULT NULLであり、その他は変化していないことがわかります.だからMySQLはNULLを識別するために1バイトが必要です.
だから2番目のSQLのkey_len=20*3+(20*3+1)=121で、計算により、2つのフィールドのインデックスが完全に使用されていることがわかります.
次に、他の状況を見て、テーブルにフィールドを追加し、インデックスを追加し、範囲のクエリーを行います.
mysql [localhost] {msandbox} (yayun) > alter table t1 add add_time timestamp;
Query OK, 0 rows affected (1.44 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql [localhost] {msandbox} (yayun) > alter table t1 add key idx_key_add_time_name3 (add_time,name3);
Query OK, 0 rows affected (0.19 sec)
Records: 0 Duplicates: 0 Warnings: 0現在のテーブル構造はこうです.
mysql [localhost] {msandbox} (yayun) > show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` char(20) NOT NULL DEFAULT '',
`name1` char(20) DEFAULT NULL,
`name3` varchar(20) NOT NULL DEFAULT '',
`add_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `idx_key_name_name1` (`name`,`name1`),
KEY `idx_key_add_time_name3` (`add_time`,`name3`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
1 row in set (0.01 sec)
mysql [localhost] {msandbox} (yayun) > SQLを見て、くだらないことはあまり言わない.
mysql [localhost] {msandbox} (yayun) > explain select * from t1 where add_time >='2014-09-10 02:36:46' and add_time <='2014-09-11 02:36:46' group by name3 order by null;
+----+-------------+-------+-------+------------------------+------------------------+---------+------+------+----------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+------------------------+------------------------+---------+------+------+----------------------------------------+
| 1 | SIMPLE | t1 | range | idx_key_add_time_name3 | idx_key_add_time_name3 | 4 | NULL | 2 | Using index condition; Using temporary |
+----+-------------+-------+-------+------------------------+------------------------+---------+------+------+----------------------------------------+
1 row in set (0.00 sec)
mysql [localhost] {msandbox} (yayun) > 私が作成した連合インデックスidx_が表示されます.key_add_time_name 3、でも本当に全部使ったの?前は範囲クエリーなので、後のフィールドのインデックスは使えません.order by nullでなければ、Using filesortも見えます.でも私はやはりkeyに言いたいです.lenがどのように計算されているのか、timestampが4バイトを占めていることはよく知られているでしょう.答えは明らかだkeylenは4で、説明は連合インデックスidx_にのみ使用されます.key_add_time_name 3のadd_timeフィールド.
もう1つのケースを見てみましょう.charフィールドとvarcharフィールドからなる連合インデックスです.
mysql [localhost] {msandbox} (yayun) > alter table t1 add key idx_key_name1_name3 (name1,name3);
Query OK, 0 rows affected (0.27 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql [localhost] {msandbox} (yayun) > SQLは次のとおりです.
mysql [localhost] {msandbox} (yayun) > explain select * from t1 where name1='yayun' and name3='dengyayun';
+----+-------------+-------+------+---------------------+---------------------+---------+-------------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------------+---------------------+---------+-------------+------+-----------------------+
| 1 | SIMPLE | t1 | ref | idx_key_name1_name3 | idx_key_name1_name3 | 123 | const,const | 1 | Using index condition |
+----+-------------+-------+------+---------------------+---------------------+---------+-------------+------+-----------------------+
1 row in set (0.00 sec)
mysql [localhost] {msandbox} (yayun) > key_が見えるlenの長さは123です.では、インデックスは完全に使われていますか?もちろんインデックスの常識はすべて完全に使ったことを知っています.みんなに伝えるためにここにいるkeylenはいったいどのように計算したのか.name3` varchar(20) NOT NULL DEFAULT ''
`name1` char(20) DEFAULT NULL,
上は2つのフィールドの定義で、1つはNULLを許可して、1つのNOT NULL、1つのchar、1つのvarchar
だからkey_len=(20*3 + 1)+(20 * 3 + 2)= 123
これにより,この組合せインデックスが完全に使用されていると判断する.ある同窓会が聞いたと信じています.+1は何ですか.+2は何ですか.これは、+1はMySQLがNULLを1バイト識別する必要があるためであり、+2はname 3フィールドがvarcharであり、長くなるフィールドが+2であるためであることを示しています.
ここまで書くと、皆さんは基本的な認識を持っていると思います.はい、多くは言わないで、公式はみんなに出して、自分で公式を使って、何度もテストをして鳥を理解します.
key_lenの長さ計算式:
varchr(10)が長くなるフィールドで、NULL=10*(character set:utf 8=3、gbk=2、latin 1=1)+1(NULL)+2(長くなるフィールド)varchr(10)が長くなるフィールドで、NULL=10*(character set:utf 8=3、gbk=2、latin 1=1)+2(長くなるフィールド)char(10)固定フィールドで、NULL=10*(character set:utf 8=3、gbk=2、latin 1=1=1)+1(NULL)char(10)固定フィールドで、NULL=10*(character set:utf 8=3、gbk=2、latin 1=1)+1(NULL)char(NULL)cha( 10)固定フィールドでNULL=10*(character set:utf 8=3、gbk=2、latin 1=1)を許可