show-index-from
概要
show index from t_xxx 语句的解析。
创建环境
创建验证环境
create database tempdb;
use tempdb;
create table t_person(
id bigint unsigned not null primary key auto_increment,
name varchar(48) not null default '',
age int not null default 0,
index idx_name(name),
index idx_age(age));
insert into t_person(name, age) values("tom", 16), ("jerry", 17), ("bob", 18);
select * from t_person;
+----+-------+-----+
| id | name | age |
+----+-------+-----+
| 1 | tom | 16 |
| 2 | jerry | 17 |
| 3 | bob | 18 |
+----+-------+-----+
show-index
show index from 语句用于查看表的索引信息。
mysql> show index from t_person;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| t_person | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |
| t_person | 1 | idx_name | 1 | name | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |
| t_person | 1 | idx_age | 1 | age | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0.01 sec)
- Table 表名
- Non_unique 是否不唯一， 0 表示唯一， 1 表示不唯一
- Key_name 索引名，如果是主键索引那么它的名字会被设定成 PRIMARY
- Seq_in_index 当前列出现在索引中的次序
- Column_name 列名
- Collation 当前列在索引的的排序方式 A:ascending D:descending NULL:not sorted
- Cardinality 根据统计信息估计出当前列在表中有多少个唯一的值，也就是说这个值越接近总行数，说明这个一列的区分度越好。
- 7.1 更新 Cardinality 的值，当我们发现这个值有可能不准确的情况下，可以用 analyze table 来更新统计信息
show index from t_person;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| t_person | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |
| t_person | 1 | idx_name | 1 | name | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |
| t_person | 1 | idx_age | 1 | age | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0.01 sec)
analyze table t_person;
+-----------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-----------------+---------+----------+----------+
| tempdb.t_person | analyze | status | OK |
+-----------------+---------+----------+----------+
1 row in set (0.00 sec)
show index from t_person;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| t_person | 0 | PRIMARY | 1 | id | A | 3 | NULL | NULL | | BTREE | | | YES | NULL |
| t_person | 1 | idx_name | 1 | name | A | 3 | NULL | NULL | | BTREE | | | YES | NULL |
| t_person | 1 | idx_age | 1 | age | A | 3 | NULL | NULL | | BTREE | | | YES | NULL |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0.00 sec)
- Sub_part 如果是前缀索引的话，这个值会对应前缀索引的字符数量
- 8.1 构造一个前缀索引
-- 建表个 8 字符的前缀索引
create index idx_name_perfix on t_person(name(8));
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
- 8.2 和期望的一个现在可以看到 Sub_part 是 8 了
show index from t_person;
+----------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| t_person | 0 | PRIMARY | 1 | id | A | 3 | NULL | NULL | | BTREE | | | YES | NULL |
| t_person | 1 | idx_name | 1 | name | A | 3 | NULL | NULL | | BTREE | | | YES | NULL |
| t_person | 1 | idx_age | 1 | age | A | 3 | NULL | NULL | | BTREE | | | YES | NULL |
| t_person | 1 | idx_name_perfix | 1 | name | A | 3 | 8 | NULL | | BTREE | | | YES | NULL |
+----------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
4 rows in set (0.00 sec)
- Packed 这个还真不知道怎么翻译了，来段英文原文吧，
Indicates how the key is packed. NULL if it is not.；好在我们通常看到的都是 NULL - Null 如果是 Yes 说明索引里面包含有 NULL 值
- Index_type 索引类型 (BTREE, FULLTEXT, HASH, RTREE)
- Visible 索引是否可见
- Comment | Index_comment 都可以看成是说明。
show-extendex-index
准确的来说 show extendex index 是用于查看索引树的，可以近似的看成它可以看到一些隐藏列。
show extended index from t_person;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| t_person | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |
| t_person | 0 | PRIMARY | 2 | DB_TRX_ID | A | NULL | NULL | NULL | | BTREE | | | YES | NULL |
| t_person | 0 | PRIMARY | 3 | DB_ROLL_PTR | A | NULL | NULL | NULL | | BTREE | | | YES | NULL |
| t_person | 0 | PRIMARY | 4 | name | A | NULL | NULL | NULL | | BTREE | | | YES | NULL |
| t_person | 0 | PRIMARY | 5 | age | A | NULL | NULL | NULL | | BTREE | | | YES | NULL |
| t_person | 1 | idx_name | 1 | name | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |
| t_person | 1 | idx_name | 2 | id | A | NULL | NULL | NULL | | BTREE | | | YES | NULL |
| t_person | 1 | idx_age | 1 | age | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |
| t_person | 1 | idx_age | 2 | id | A | NULL | NULL | NULL | | BTREE | | | YES | NULL |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
9 rows in set (0.01 sec)
- 对于主键索引来说，它能看到所有列，外加 DB_TRX_ID, DB_ROLL_PTR
- 对于普通的二级索引来说它能看到主键列