MySQL的聚簇索引、非聚簇索引及其查询解析方法

1.聚簇索引和非聚簇索引

索引是用来提高数据库性能的，用于快速找出某个列中有一特定值的行，如果不使用索引，MySQL必须从第1条记录开始读完整个表直到找出相关的行，表越大，花费的时间越多，如果表中查询的列有一个索引，MySQL能快速到达一个位置去搜索数据文件，没有必要看全部数据，这对性能将有极大的提升。

再用一道数据题来理解：如果表中的一条记录在磁盘上占用 1000字节的话，我们对其中10字节的一个字段建立索引，那么该记录对应的索引块的大小只有10字节。我们知道，MySQL的最小空间分配单元是“页（Page）”，一个页在磁盘上默认占用16K空间，那么这一个页可以存储上述记录16条，但可以存储索引1600条。现在我们要从一个有16000条记录的表中检索符合某个条件的记录，如果没有索引的话，我们可能需要遍历16000条×1000字节/16K字节=1000个页面才能够找到结果。如果在检索字段上有上述索引的话，那么我们可以在16000条×10字节/16K字节=10个页面中就检索到满足条件的索引块，然后根据索引块上的指针逐一找到结果数据块，这样IO访问量要少的多，可见索引对性能的提升作用。

索引可分为聚簇索引和非聚簇索引。

聚簇索引中键值的逻辑顺序决定了表数据行的物理顺序，只要索引是相邻的，对应的数据一定也是相邻地存放在磁盘上，因此每张表只能建立一个聚簇索引。比如英语课本中查找某个单词在第8页，翻到第6页再往后翻，翻到第10页再往前翻，这就类似于聚簇索引，表存储数据的顺序就是跟着聚簇索引来存储的。

InnoDB表中聚簇索引首选主键，其次选择不含null值的唯一索引，如果两者都无，则会内置rowid作为隐含的聚集索引。

官方文档关于这个知识点描述如下：

• When you define a PRIMARY KEY on your table, InnoDB uses it as theclustered index.Define a primary key for each table that you create. If there is no logicalunique and non-null column or set of columns, add a new auto-increment column, whose values are filled in automatically. 首选主键

• If you do not define a PRIMARY KEY for your table, MySQL locates thefirst UNIQUE index where all the key columns are NOT NULL and InnoDB uses it asthe clustered index. 没有主键则选非空唯一索引

• If the table has no PRIMARY KEY or suitable UNIQUE index, InnoDBinternally generates a hidden clustered index on a synthetic column containingrow ID values. The rows are ordered by the ID that InnoDB assigns to the rowsin such a table. The row ID is a 6-byte field that increases monotonically asnew rows are inserted. Thus, the rows ordered by the row ID are physically ininsertion order.都没有的话就内置rowid生成隐含的聚集索引

非聚簇索引中键值的逻辑顺序与数据行的物理存储顺序不同，假设要找某个单词，需要翻到英语书后面的单词目录，找到对应的字母位置，看其所在第几课，然后再找到对应课中的单词。这个附录就类似非聚簇索引。一个表可以有不止一个非聚簇索引，非聚簇索引需要大量的硬盘空间和内存，虽然非聚簇索引可以提高从表中读取数据的速度，但也会降低向表中插入和更新数据的速度，因为只要你更新一个建立了非聚簇索引的表中的数据时，必须要同时更新索引。

一般缺省情况下建立的索引是非聚簇索引，但并一定是最佳的，因为很多情况下，聚簇索引比非聚簇索引查询效率高很多，聚簇索引的叶子节点就是数据节点，而非聚簇索引的叶子节点仍然是索引节点，只不过有一个指针指向对应的数据块。而且如果需要频繁地更新表数据就不要建太多非聚簇索引，另外，如果硬盘和内存空间有限，也应该限制非聚簇索引的数量。总之，真正合理的索引设计要建立在对各种查询的分析和预测上。

官方文档说明如下：

Every InnoDB table has aspecial index called the clustered index where the data for the rows is stored. 
Typically, the clustered indexis synonymous with the primary key. 
To get the best performance from queries, inserts, and other databaseoperations, you must understand how InnoDB uses the clustered 
index to optimizethe most common lookup and DML operations for each table.

2.如何查询表的索引基本情况？

表的索引情况记录在information_schema库中的statistics表中，该表专提供表索引的信息，查询某库某表索引信息语句如下：

SELECT * FROM INFORMATION_SCHEMA.STATISTICS
WHERE table_name = 'tbl_name'
AND table_schema = 'db_name'

或者

SHOW INDEX FROM tbl_name FROM db_name

指定查询某库某表索引情况的语句：

mysql> select * from statistics whereTABLE_SCHEMA='cloudXXX' and TABLE_NAME='opmXXXX'\G

查询第一行数据进行逐个字段解析：

mysql> select * fromstatistics limit 0,1\G

*************************** 1. row ***************************

TABLE_CATALOG: def 数据表登记目录

TABLE_SCHEMA: monitor_XXXX 索引所属表的数据库名

TABLE_NAME: alarm_XXXX 索引所属的数据表名

NON_UNIQUE: 0 字段不唯一的标识，索引不能包括重复词则为0，否则为1

INDEX_SCHEMA: monitor_XXXX 索引所属的数据库名

INDEX_NAME: PRIMARY 索引名称，primary一般是聚簇索引

SEQ_IN_INDEX: 1 索引中的序列号，从1开始

COLUMN_NAME: id 索引列的列名

COLLATION: A 校对，列值全显示为A

CARDINALITY: 58 基数，同该表的数据行数

SUB_PART: NULL 列只是部分编入索引，则显示编入索引的字符的数据，如整列被编入索引，则为NULL

PACKED: NULL 关键字是否包装过，没有压缩包装则默认为NULL

NULLABLE: 是否为空

INDEX_TYPE: BTREE 索引的类型，列值全显示为BTREE，InnoDB的存储引擎默认创建的是BTREE（举例：BTREE,FULLTEXT, HASH, RTREE）

COMMENT: 索引注释、备注

INDEX_COMMENT:

如果查询某库某表索引信息没有任何输出，则说明该表未建索引。

3.如何查询表的索引类型情况？

InnoDB表的索引类型情况在information_schema库中的INNODB_SYS_TABLES和INNODB_SYS_INDEXES里。

先在INNODB_SYS_TABLES表里找到自己需要查询的表对应的TABLE_ID：

mysql> select TABLE_ID,NAME from INNODB_SYS_TABLESwhere NAME like 'monitor %';

输出举例：

|       76 |monitor_XXXXXXXX/os_perf_biz_vpn                          |
|       77 |monitor_ XXXXXXXX /os_perf_biz_vpn_day                      |
|       78 |monitor_ XXXXXXXX /os_perf_biz_vpn_hour                     |
|       79 |monitor_ XXXXXXXX /os_perf_biz_vpn_month                    |

再用这个TABLE_ID去INNODB_SYS_INDEXES表里去查该表的索引类型：

mysql> select * from INNODB_SYS_INDEXES whereTABLE_ID between '76' and '199';

输出举例：

+----------+-------------------+----------+------+----------+---------+-------+
| INDEX_ID | NAME              | TABLE_ID | TYPE | N_FIELDS |PAGE_NO | SPACE |
+----------+-------------------+----------+------+----------+---------+-------+
|       92 | GEN_CLUST_INDEX   |      76 |    1 |        0 |       3 |   65 |
|      351 | create_time_index |       76 |   0 |        1 |       4 |   65 |
|       93 | GEN_CLUST_INDEX   |      77 |    1|        0 |       3 |   66 |
|      352 | create_time_index |       77 |   0|        1 |       4 |   66

这个输出关键看TYPE字段，TYPE字段官网解析如下：

A numeric identifiersignifying the kind of index. 
0 = Secondary Index, 
1 =Clustered Index, 
2 = Unique Index, 
3 = PrimaryIndex, 
32 = Full-text Index, 
64 = Spatial Index, 
128 = A secondary indexthat includes a generated virtual column

TYPE为0便是辅助索引，也是非聚簇索引，1是聚簇索引，3是主键索引。上文输出可以看出这些表建的就是非聚簇索引，索引名字为create_time_index。

除此之外，还有个GEN_CLUST_INDEX，其官网解析如下：

If the name isGEN_CLUST_INDEX, the index is the clustered index thatis created automatically if the table definition doesn't include a primary keyor non-NULL unique index.

即使一个既无主键也无索引的表，在statistics中没有任何记录，在这里也可以查询如下，这也应证了上文所述的InnoDB表隐含的聚集索引：

mysql>select TABLE_ID,NAME from INNODB_SYS_TABLES where NAME='monitor_XXXXXX/os_XXXXXX';
+----------+---------------------------------------------------+
|TABLE_ID | NAME                                              |
+----------+---------------------------------------------------+
|      203 | monitor_XXXXX/os_XXXXXXXXXXX |
+----------+---------------------------------------------------+
1row in set (0.00 sec)
 
mysql>select * from INNODB_SYS_INDEXES where TABLE_ID='203';
+----------+-----------------+----------+------+----------+---------+-------+
|INDEX_ID | NAME            | TABLE_ID | TYPE| N_FIELDS | PAGE_NO | SPACE |
+----------+-----------------+----------+------+----------+---------+-------+
|      218 | GEN_CLUST_INDEX|      203 |    1 |       0 |       3 |   192 |
+----------+-----------------+----------+------+----------+---------+-------+
1row in set (0.00 sec)

以上就是MySQL的聚簇索引、非聚簇索引及其查询解析方法的内容，更多相关内容请关注PHP中文网（m.sbmmt.com）！