前言：我们知道InnoDB是索引组织表，每个表都有一个聚集索引，那么我们怎么能看出一个表的聚集索引是什么呢？在MySQL5.6版本我们可以利用innodb_table_monitor来观察每个表创建时期选择的聚集索引，今天我们就利用这个黑科技来观察下MySQL是如何来选择每个表的聚集索引的。

创建表innodb_table_monitor

  1. mysql> create table innodb_table_monitor(id int);
  2. Query OK, 0 rows affected, 1 warning (0.07 sec)

创建测试表1(显示指定主键)

  1. mysql> create table t(id int auto_increment,name varchar(10),primary key(id));
  2. Query OK, 0 rows affected (0.02 sec)

过大概10几秒，通过查看error.log可以观察到

  1. TABLE: name test/t, id 23, flags 1, columns 5, indexes 2, appr.rows 0
  2.   COLUMNS: id: DATA_INT DATA_BINARY_TYPE DATA_NOT_NULL len 4; name: DATA_VARMYSQL len 30; DB_ROW_ID: DATA_SYS prtype 256 len 6; DB_TRX_ID: DATA_SYS prtype 257 len 6; DB_ROLL_PTR: DATA_SYS prtype 258 len 7; 
  3.   INDEX: name PRIMARY, id 25, fields 1/4, uniq 1, type 3
  4.    root page 3, appr.key vals 0, leaf pages 1, size pages 1
  5.    FIELDS:  id DB_TRX_ID DB_ROLL_PTR name
  6.   INDEX: name idx_name, id 26, fields 1/2, uniq 2, type 0
  7.    root page 4, appr.key vals 0, leaf pages 1, size pages 1
  8.    FIELDS:  name id

从上面可以看到InnoDB选择了主键id作为唯一索引，而索引idx_name为普通索引

创建测试表2(不指定主键且指定一个非空唯一索引)

  1. mysql> create table t1(id int ,name varchar(10) not null,unique key idx_name(name));
  2. Query OK, 0 rows affected (0.01 sec)

查看error.log

  1. TABLE: name test/t1, id 25, flags 1, columns 5, indexes 1, appr.rows 0
  2.   COLUMNS: id: DATA_INT DATA_BINARY_TYPE len 4; name: DATA_VARMYSQL DATA_NOT_NULL len 30; DB_ROW_ID: DATA_SYS prtype 256 len 6; DB_TRX_ID: DATA_SYS prtype 257 len 6; DB_ROLL_PTR: DATA_SYS prtype 258 len 7; 
  3.   INDEX: name idx_name, id 29, fields 1/4, uniq 1, type 3
  4.    root page 3, appr.key vals 0, leaf pages 1, size pages 1
  5.    FIELDS:  name DB_TRX_ID DB_ROLL_PTR id

从日志看到，聚集索引选择的是idx_name

创建测试表3(不指定主键也无非空唯一索引)

  1. mysql> create table t2(id int ,name varchar(10),key idx_name(name));
  2. Query OK, 0 rows affected (0.02 sec)
  1. TABLE: name test/t2, id 26, flags 1, columns 5, indexes 2, appr.rows 0
  2.   COLUMNS: id: DATA_INT DATA_BINARY_TYPE len 4; name: DATA_VARMYSQL len 30; DB_ROW_ID: DATA_SYS prtype 256 len 6; DB_TRX_ID: DATA_SYS prtype 257 len 6; DB_ROLL_PTR: DATA_SYS prtype 258 len 7; 
  3.   INDEX: name GEN_CLUST_INDEX, id 30, fields 0/5, uniq 1, type 1
  4.    root page 3, appr.key vals 0, leaf pages 1, size pages 1
  5.    FIELDS:  DB_ROW_ID DB_TRX_ID DB_ROLL_PTR id name
  6.   INDEX: name idx_name, id 31, fields 1/2, uniq 2, type 0
  7.    root page 4, appr.key vals 0, leaf pages 1, size pages 1
  8.    FIELDS:  name DB_ROW_ID

从日志可以看出，InnoDB选择的是rowid作为聚集索引

创建测试表4(包含以上三种索引)

  1. mysql> create table t3(id int auto_increment,name1 varchar(10) not null,name2 varchar(10),primary key(id),unique key idx_name1(name1),key idx_name2(name2));
  2. Query OK, 0 rows affected (0.02 sec)
  1. TABLE: name test/t3, id 27, flags 1, columns 6, indexes 3, appr.rows 0
  2.   COLUMNS: id: DATA_INT DATA_BINARY_TYPE DATA_NOT_NULL len 4; name1: DATA_VARMYSQL DATA_NOT_NULL len 30; name2: DATA_VARMYSQL len 30; DB_ROW_ID: DATA_SYS prtype 256 len 6; DB_TRX_ID: DATA_SYS prtype 257 len 6; DB_ROLL_PTR: DATA_SYS prtype 258 len 7; 
  3.   INDEX: name PRIMARY, id 32, fields 1/5, uniq 1, type 3
  4.    root page 3, appr.key vals 0, leaf pages 1, size pages 1
  5.    FIELDS:  id DB_TRX_ID DB_ROLL_PTR name1 name2
  6.   INDEX: name idx_name1, id 33, fields 1/2, uniq 1, type 2
  7.    root page 4, appr.key vals 0, leaf pages 1, size pages 1
  8.    FIELDS:  name1 id
  9.   INDEX: name idx_name2, id 34, fields 1/2, uniq 2, type 0
  10.    root page 5, appr.key vals 0, leaf pages 1, size pages 1
  11.    FIELDS:  name2 id

从日志看出，InnoDB还是选择的是主键作为聚集索引

5.7查看聚集索引的方法

因为5.7已经把innodb_table_monitor的功能移除，我们只能通过视图来观察，观察方法如下：

  1. root@127.0.0.1:3306  [information_schema]>create table xucl.tttt(id int,name1 varchar(10) not null,name2 varchar(10),unique key idx_name1(name1),key idx_name2(name2),primary key(id));
  2. Query OK, 0 rows affected (0.02 sec)
  4. root@127.0.0.1:3306  [information_schema]>select i.* from INNODB_SYS_INDEXes i join INNODB_SYS_TABLES t on i.table_id=t.table_id where [图片]t.name='xucl/tttt';
  5. +----------+-----------+----------+------+----------+---------+-------+-----------------+
  6. | INDEX_ID | NAME      | TABLE_ID | TYPE | N_FIELDS | PAGE_NO | SPACE | MERGE_THRESHOLD |
  7. +----------+-----------+----------+------+----------+---------+-------+-----------------+
  8. |      104 | PRIMARY   |       64 |    3 |        1 |       3 |    64 |              50 |
  9. |      105 | idx_name1 |       64 |    2 |        1 |       4 |    64 |              50 |
  10. |      106 | idx_name2 |       64 |    0 |        1 |       5 |    64 |              50 |
  11. +----------+-----------+----------+------+----------+---------+-------+-----------------+
  12. 3 rows in set (0.00 sec)

通过视图看到每个表的第一个索引即聚集索引

总结：

InnoDB在选择聚集索引的优先级上符合以下顺序

  • 如果InnoDB表显示地指定了主键，那么会选择主键作为聚集索引

  • 如果InnoDB表没有显示指定主键，那么会优先选择第一个非空唯一索引作为聚集索引

  • 如果以上条件都不满足，那么InnoDB会选择一个隐式的6字节rowid作为聚集索引