mysql聚集索引 mysql如何查看某个表的聚集索引

2021-12-24 20:14:42

转自：https://www.5axxw.com/wenku/ox/1100175x.html

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

【测试查看】

创建表innodb_table_monitor

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

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

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

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

TABLE: name test/t, id 23, flags 1, columns 5, indexes 2, appr.rows 0
  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; 
  INDEX: name PRIMARY, id 25, fields 1/4, uniq 1, type 3
   root page 3, appr.key vals 0, leaf pages 1, size pages 1
   FIELDS:  id DB_TRX_ID DB_ROLL_PTR name
  INDEX: name idx_name, id 26, fields 1/2, uniq 2, type 0
   root page 4, appr.key vals 0, leaf pages 1, size pages 1
   FIELDS:  name id

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

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

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

查看error.log

TABLE: name test/t1, id 25, flags 1, columns 5, indexes 1, appr.rows 0
  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; 
  INDEX: name idx_name, id 29, fields 1/4, uniq 1, type 3
   root page 3, appr.key vals 0, leaf pages 1, size pages 1
   FIELDS:  name DB_TRX_ID DB_ROLL_PTR id

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

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

mysql> create table t2(id int ,name varchar(10),key idx_name(name));
Query OK, 0 rows affected (0.02 sec)

TABLE: name test/t2, id 26, flags 1, columns 5, indexes 2, appr.rows 0
  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; 
  INDEX: name GEN_CLUST_INDEX, id 30, fields 0/5, uniq 1, type 1
   root page 3, appr.key vals 0, leaf pages 1, size pages 1
   FIELDS:  DB_ROW_ID DB_TRX_ID DB_ROLL_PTR id name
  INDEX: name idx_name, id 31, fields 1/2, uniq 2, type 0
   root page 4, appr.key vals 0, leaf pages 1, size pages 1
   FIELDS:  name DB_ROW_ID

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

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

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));
Query OK, 0 rows affected (0.02 sec)

TABLE: name test/t3, id 27, flags 1, columns 6, indexes 3, appr.rows 0
  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; 
  INDEX: name PRIMARY, id 32, fields 1/5, uniq 1, type 3
   root page 3, appr.key vals 0, leaf pages 1, size pages 1
   FIELDS:  id DB_TRX_ID DB_ROLL_PTR name1 name2
  INDEX: name idx_name1, id 33, fields 1/2, uniq 1, type 2
   root page 4, appr.key vals 0, leaf pages 1, size pages 1
   FIELDS:  name1 id
  INDEX: name idx_name2, id 34, fields 1/2, uniq 2, type 0
   root page 5, appr.key vals 0, leaf pages 1, size pages 1
   FIELDS:  name2 id

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

5.7查看聚集索引的方法

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

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));
Query OK, 0 rows affected (0.02 sec)

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';
+----------+-----------+----------+------+----------+---------+-------+-----------------+
| INDEX_ID | NAME      | TABLE_ID | TYPE | N_FIELDS | PAGE_NO | SPACE | MERGE_THRESHOLD |
+----------+-----------+----------+------+----------+---------+-------+-----------------+
|      104 | PRIMARY   |       64 |    3 |        1 |       3 |    64 |              50 |
|      105 | idx_name1 |       64 |    2 |        1 |       4 |    64 |              50 |
|      106 | idx_name2 |       64 |    0 |        1 |       5 |    64 |              50 |
+----------+-----------+----------+------+----------+---------+-------+-----------------+
3 rows in set (0.00 sec)

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

总结：

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

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

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

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

码农公寓