SQL入門：インデックス
パフォーマンス
テーブルステータスの表示
SHOW TABLE STATUS WHERE name='city';
出力結果
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+ | city | InnoDB | 10 | Dynamic | 4046 | 101 | 409600 | 0 | 114688 | 0 | 4082 | 2020-10-27 10:22:24 | NULL | NULL | utf8mb4_0900_ai_ci | NULL | | | +------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+ 1 row in set (0.00 sec)
インデックス情報
SHOW INDEX FROM city;
出力結果
mysql> SHOW INDEX FROM city \G
*************************** 1. row ***************************
Table: city
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: ID
Collation: A
Cardinality: 4046
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
*************************** 2. row ***************************
Table: city
Non_unique: 1
Key_name: CountryCode
Seq_in_index: 1
Column_name: CountryCode
Collation: A
Cardinality: 232
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
2 rows in set (0.01 sec)
統計情報の手動取得
ANALYZE TABLE city;
出力結果
+------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +------------+---------+----------+----------+ | world.city | analyze | status | OK | +------------+---------+----------+----------+ 1 row in set (0.01 sec)
実行計画
フルスキャン
EXPLAIN SELECT * FROM city;
出力結果
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | SIMPLE | city | NULL | ALL | NULL | NULL | NULL | NULL | 4046 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ 1 row in set, 1 warning (0.01 sec)
レンジスキャン
EXPLAIN SELECT * FROM city WHERE id BETWEEN 1001 AND 1100;
出力結果
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | 1 | SIMPLE | city | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 100 | 100.00 | Using where | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
EXPLAIN SELECT COUNT(*) FROM city GROUP BY CountryCode;
出力結果
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+ | 1 | SIMPLE | city | NULL | index | CountryCode | CountryCode | 12 | NULL | 4046 | 100.00 | Using index | +----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
EXPLAIN SELECT district,COUNT(*) FROM city GROUP BY district;
出力結果
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+ | 1 | SIMPLE | city | NULL | ALL | NULL | NULL | NULL | NULL | 4046 | 100.00 | Using temporary | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+ 1 row in set, 1 warning (0.00 sec)
インデックスの作成
CREATE INDEX idx_district ON city(District);
出力結果
Query OK, 0 rows affected (0.10 sec) Records: 0 Duplicates: 0 Warnings: 0
インデックスの確認
SHOW INDEX FROM city \G
出力結果
*************************** 1. row ***************************
Table: city
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: ID
Collation: A
Cardinality: 4046
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
*************************** 2. row ***************************
Table: city
Non_unique: 1
Key_name: CountryCode
Seq_in_index: 1
Column_name: CountryCode
Collation: A
Cardinality: 232
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
*************************** 3. row ***************************
Table: city
Non_unique: 1
Key_name: idx_district
Seq_in_index: 1
Column_name: District
Collation: A
Cardinality: 1367
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
3 rows in set (0.01 sec)
インデクス検索
EXPLAIN SELECT * FROM city WHERE district='kyoto';
出力結果
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-----------------------+ | 1 | SIMPLE | city | NULL | ref | idx_district | idx_district | 80 | const | 4 | 100.00 | Using index condition | +----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec)
インデックスの削除
DROP INDEX idx_district ON city;
出力結果
Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0
