DBAの道6_MySQL_索引(下)および実行計画
15247 ワード
1.1索引
1.2インデックスの操作管理
2.実行計画
2.1作用
2.2計画取得の実行
2.3重要情報の紹介
type:ref
レベル
Index:フルインデックススキャン
range:インデックス範囲スキャン
ref:補助インデックス等値クエリー
eq_ref:マルチテーブル接続クエリーの場合、onの条件列は一意のインデックスまたはプライマリ・キーです.
const,system:プライマリ・キーまたはユニーク・キーの等値クエリー
Extra:Null追加情報
3.Explain(desc)使用シーン(面接問題)
1.MySQLでパフォーマンスの問題が発生し、2つの状況をまとめた.
4.インデックス適用仕様
業務1.製品の機能2.ユーザーの動作「ホット」クエリー文--->遅い--->slowlog「ホットデータ」
4.1インデックスの確立の原則(DBA運行メンテナンス規範)
4.1.0説明
4.1.1(必要な)表を作る時に必ず主キーがあって、普通は関係ない列である
略振り返ると、集約インデックス構造.
4.1.2一意性インデックスの選択
4.1.3
4.10*********連合インデックスについて********************
MUL
PRI （ ）
UNI
1.2インデックスの操作管理
mysql> alter table t100w add index idx_k2(k2);
Query OK, 0 rows affected (6.11 sec)
mysql> show index from t100w;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t100w | 1 | idx_k2 | 1 | k2 | A | 3826 | NULL | NULL | YES | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
mysql> desc t100w;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+-------------------+-----------------------------+
| id | int(11) | YES | | NULL | |
| num | int(11) | YES | | NULL | |
| k1 | char(2) | YES | | NULL | |
| k2 | char(4) | YES | MUL | NULL | |
| dt | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+
5 rows in set (0.00 sec)
mysql>alter table t100w add unique index index_k1(k1);
mysql> alter table city add index idx_name(name(3));
Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table city drop index idx_name;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
；
mysql> alter table city add index idx_n_p(countrycode,population);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from city
-> ;
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| city | 0 | PRIMARY | 1 | ID | A | 4188 | NULL | NULL | | BTREE | | |
| city | 1 | CountryCode | 1 | CountryCode | A | 232 | NULL | NULL | | BTREE | | |
| city | 1 | idx_n_p | 1 | CountryCode | A | 232 | NULL | NULL | | BTREE | | |
| city | 1 | idx_n_p | 2 | Population | A | 4052 | NULL | NULL | | BTREE | | |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)
2.実行計画
2.1作用
， （ ）
2.2計画取得の実行
desc explain （ ），
mysql> desc select * from city where name='ShangHai';
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| 1 | SIMPLE | city | NULL | ref | idx_name | idx_name | 35 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
：
table： ， ，
type: （ ）
possible_key
key
key_len （ ， ）
rows （ ， ）
Extra
2.3重要情報の紹介
type:ref
レベル
1>ALL： ， ，
ALL ：（1）
（2）
2>Index：
3>range：
4>ref:
5>eq_ref: ，on
6>const,system:
： ，
ALL： ， ，
ALL ：（1）
（2）
：
mysql> desc select * from t100w where k2 != 'aaa'; != all
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | t100w | NULL | ALL | idx_k2 | NULL | NULL | NULL | 888567 | 100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> desc select * from t100w where k2 like '%xy%';
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | t100w | NULL | ALL | NULL | NULL | NULL | NULL | 888567 | 11.11 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> desc select k1 from t100w;
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------+
| 1 | SIMPLE | t100w | NULL | ALL | NULL | NULL | NULL | NULL | 888567 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> desc select * from t100w;
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------+
| 1 | SIMPLE | t100w | NULL | ALL | NULL | NULL | NULL | NULL | 888567 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------+
1 row in set, 1 warning (0.00 sec)
:（1） select * from
（2）select from
（3）!= all range
（4）%a ALL， range
Index:フルインデックススキャン
mysql> desc select k2 from t100w;
+----+-------------+-------+------------+-------+---------------+--------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | t100w | NULL | index | NULL | idx_k2 | 17 | NULL | 888567 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
range:インデックス範囲スキャン
:< > <= >= like
in or
：!= range
mysql> desc select * from world.city where countrycode like 'C%'
mysql> desc select * from world.city where id!=3000;
mysql> desc select * from world.city where id>3000;
mysql> desc select * from world.city where countrycode in ('CHN','USA');
：
desc
select * from world.city where countrycode='CHN'
union all
select * from world.city where countrycode='USA';
ref:補助インデックス等値クエリー
mysql> desc select * from city where countrycode='CHN';
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | city | NULL | ref | CountryCode | CountryCode | 3 | const | 363 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
eq_ref:マルチテーブル接続クエリーの場合、onの条件列は一意のインデックスまたはプライマリ・キーです.
mysql> desc select a.name,b.name ,b.surfacearea
-> from city as a
-> join country as b
-> on a.countrycode=b.code
-> where a.population <100;
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------------+------+----------+-------------+
| 1 | SIMPLE | a | NULL | ALL | CountryCode | NULL | NULL | NULL | 4188 | 33.33 | Using where |
| 1 | SIMPLE | b | NULL | eq_ref | PRIMARY | PRIMARY | 3 | world.a.CountryCode | 1 | 100.00 | NULL |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
const,system:プライマリ・キーまたはユニーク・キーの等値クエリー
mysql> desc select * from city where id='10';
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | city | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
Extra:Null追加情報
+---------------------------------------+
| Extra |
+---------------------------------------+
| Using index condition; Using filesort |
+---------------------------------------+
Using filesort ：
mysql> desc select * from city where countrycode='CHN' order by population;
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+---------------------------------------+
| 1 | SIMPLE | city | NULL | ref | CountryCode | CountryCode | 3 | const | 363 | 100.00 | Using index condition; Using filesort |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+---------------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> alter table city add index idx_pop_cou(countrycode,population);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc select * from city where countrycode='CHN' order by population;
+----+-------------+-------+------------+------+-------------------------+-------------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+-------------------------+-------------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | city | NULL | ref | CountryCode,idx_pop_cou | idx_pop_cou | 3 | const | 363 | 100.00 | Using index condition |
+----+-------------+-------+------------+------+-------------------------+-------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
3.Explain(desc)使用シーン(面接問題)
1.MySQLでパフォーマンスの問題が発生し、2つの状況をまとめた.
（1） ，
： （ ， ）
：
1.show proesslist;
2.explain SQL ， ，
3. ，
（2） （ ）
1. ，slowlog， slowlog
2.explain， SQL ， ，
3. ，
4.インデックス適用仕様
業務1.製品の機能2.ユーザーの動作「ホット」クエリー文--->遅い--->slowlog「ホットデータ」
4.1インデックスの確立の原則(DBA運行メンテナンス規範)
4.1.0説明
， ， 。 ?
4.1.1(必要な)表を作る時に必ず主キーがあって、普通は関係ない列である
略振り返ると、集約インデックス構造.
4.1.2一意性インデックスの選択
， 。
4.1.3
where group by order by ，
4.10*********連合インデックスについて********************
(1）where A, group by B. order by c ---------->(A,,B,C)
(2) where A B C
1> （5.5 ， ），
，
2> ，
select where A= and B> and C=
ACB/CAB， ACB/CAB,AC