在 InnoDB 存储引擎中，表都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表（index organized table），或者叫聚集索引（clustered index）。

索引组织表存在如下特性：

  • 每张表都必须有一个主键。
  • 根据主键的值构造一棵 B+Tree。
  • 这棵 B+Tree 的叶子节点（leaf page）存放所有的记录（Row）。
  • 非叶子节点（Non-leaf page）存放的主键和指针（若干个 {主键，指针} 组成一个非页节点）。
    • 这里的指针其实就是 PageNumber（这里不需要 SpaceID，因为 SpaceID 对应的是 ibd 文件，我们现在是在 ibd 文件内部查找数据）。

主键

如果创建表的时候，没有显示指定主键，则 InnoDB 会按照如下方式选择或创建主键：

  1. 判断表中是否有非空的唯一索引，如果有，该列即为主键。
    1. 如果存在多个非空唯一索引，以创建表时以第一个定义的非空唯一索引为准，而不是（columns）定义的顺序。
  2. 如果上述条件都不符合，则 InnoDB 会自动创建一个6字节大小的主键（Int 类型，全局唯一，且是隐藏列）。 ```sql mysql> create table test_key_3( -> a int, -> b int, -> c int); Query OK, 0 rows affected (0.11 sec)

mysql> insert into test_key_3 values(1,2,3),(4,5,6),(7,8,9); Query OK, 3 rows affected (0.02 sec) Records: 3 Duplicates: 0 Warnings: 0

mysql> select *,_rowid from test_key_3; ERROR 1054 (42S22): Unknown column ‘_rowid’ in ‘field list’ — 这里无法用_rowid查看，因为系统rowid对用户是透明的

  1. 假设有 a 和 b 两张表都使用了系统定义的主键，则系统定义的主键的 ID 不是在表内进行单独递增的，而是全局递增。
  3. 该系统的 rowid 是定义在 ibdata1.ibd 中的 sys_rowid 中，全局自增6个字节表示的数据量为 2^48 ，通常意义上是够用的。
  5. 强烈建议自己显示定义主键。
  7. **如果存在多个非空的唯一索引，如何知道是哪个索引作为主键了？**
  8. ```sql
  9. mysql> create table test_key (
  10.     -> a int,
  11.     -> b int not null,
  12.     -> c int not null,
  13.     -> unique key(a),
  14.     -> unique key(c),
  15.     -> unique key(b)
  16.     -> );
  17. Query OK, 0 rows affected (0.16 sec)
  19. mysql> insert into test_key values(1,2,3),(4,5,6),(7,8,9);
  20. Query OK, 3 rows affected (0.04 sec)
  21. Records: 3 Duplicates: 0 Warnings: 0
  23. mysql> select * from test_key;
  24. +------+---+---+
  25. | a | b | c |
  26. +------+---+---+
  27. | 1 | 2 | 3 |
  28. | 4 | 5 | 6 |
  29. | 7 | 8 | 9 |
  30. +------+---+---+
  31. 3 rows in set (0.00 sec)
  33. mysql> select *, _rowid from test_key; -- _rowid是主键值
  34. +------+---+---+--------+
  35. | a | b | c | _rowid |
  36. +------+---+---+--------+
  37. | 1 | 2 | 3 | 3 | -- 可以发现，这里的主键是c
  38. | 4 | 5 | 6 | 6 |
  39. | 7 | 8 | 9 | 9 |
  40. +------+---+---+--------+
  41. 3 rows in set (0.00 sec)
  43. mysql> create table test_key_2 (
  44.  -> a varchar(8), -- 使用varchar类型
  45.  -> b varchar(8) not null,
  46.  -> c varchar(8) not null,
  47.  -> unique key(a),
  48.  -> unique key(c),
  49.  -> unique key(b)
  50.  -> );
  51. Query OK, 0 rows affected (0.15 sec)
  53. mysql> insert into test_key_2 values('a','b','c'),('d','e','f'),('g','h','i');
  54. Query OK, 3 rows affected (0.02 sec)
  55. Records: 3 Duplicates: 0 Warnings: 0
  57. mysql> select * from test_key_2; 
  58. +------+---+---+
  59. | a | b | c |
  60. +------+---+---+
  61. | a | b | c |
  62. | d | e | f |
  63. | g | h | i |
  64. +------+---+---+
  65. 3 rows in set (0.00 sec)
  67. mysql> select *, _rowid from test_key_2;
  68. ERROR 1054 (42S22): Unknown column '_rowid' in 'field list' -- 报错了
  69. -- _rowid只能是在key的类型为整型时才有效
  71. -- 方法一
  72. mysql> select * from information_schema.columns where table_name="test_key_2" and column_key="PRI"\G
  73. *************************** 1. row ***************************
  74.  TABLE_CATALOG: def
  75.  TABLE_SCHEMA: burn_test
  76.  TABLE_NAME: test_key_2
  77.  COLUMN_NAME: c -- 该列的列名是 c
  78.  ORDINAL_POSITION: 3
  79.  COLUMN_DEFAULT: NULL
  80.  IS_NULLABLE: NO
  81.  DATA_TYPE: varchar
  82. CHARACTER_MAXIMUM_LENGTH: 8
  83.  CHARACTER_OCTET_LENGTH: 32
  84.  NUMERIC_PRECISION: NULL
  85.  NUMERIC_SCALE: NULL
  86.  DATETIME_PRECISION: NULL
  87.  CHARACTER_SET_NAME: utf8mb4
  88.  COLLATION_NAME: utf8mb4_general_ci
  89.  COLUMN_TYPE: varchar(8)
  90.  COLUMN_KEY: PRI -- 该列是主键
  91.  EXTRA: 
  92. PRIVILEGES: select,insert,update,references
  93.  COLUMN_COMMENT: 
  94.  GENERATION_EXPRESSION: 
  95. 1 row in set (0.00 sec)
  97. -- 方法二
  98. mysql> desc test_key_2;
  99. +-------+------------+------+-----+---------+-------+
  100. | Field | Type | Null | Key | Default | Extra |
  101. +-------+------------+------+-----+---------+-------+
  102. | a | varchar(8) | YES | UNI | NULL | |
  103. | b | varchar(8) | NO | UNI | NULL | |
  104. | c | varchar(8) | NO | PRI | NULL | | -- key 是PRI ，就可以知道 c 列是主键
  105. +-------+------------+------+-----+---------+-------+
  106. 3 rows in set (0.00 sec)

聚集索引一行记录存在以下系统列：

  1. rowid：B+Tree 索引键值，只能查整型的主键值，作用不大。
  2. trx id：6字节，事务 id 列。
  3. roll pointer：7字节，回滚指针列。

索引组织表 VS 堆表

堆表

image.png
堆表将索引和数据分开（如 MyISAM），索引中叶子节点存放的是数据的位置，而不是数据本身。

索引组织表

image.png
索引组织表将索引和数据放在了一起，索引的叶子节点（leaf page）存放了所有完整的记录（Row），索引即数据，数据即索引。

注意：

  1. 非叶子节点（Non-leaf page）中不会存放所有的数据（Row）的 {主键, PageNumber}，而是从叶子节点（leaf page）中选出一个数据的主键，将这个主键和该页的 PageNumber 填入到非叶节点（Non-leaf page）中。
  2. 从逻辑上看，是一棵 B+Tree 树，但是从物理上看都是每个页（非叶子节点和叶子节点）通过指针串在一起，使得逻辑有序。

二级索引

image.png
二级索引中的叶子节点不存放数据本身，而是存放主键。

查询数据对比

堆表查询：
image.png
索引组织表以及二级索引查询：
image.png

Page 的空间申请

  • 叶子节点（leaf page）由 leaf page segment 进行申请空间。
  • 非叶子节点（Non-leaf page）由 Non-leaf page segment 进行申请空间。

所以索引由两个段组成：

  • leaf page segment
  • Non-leaf page segment

段（segment）是由区（extent）组成，申请空间就按照区（extent）进行申请（一般情况下一次申请4个区）。

作者：殷建卫 链接：https://www.yuque.com/yinjianwei/vyrvkf/wpbl5s 来源：殷建卫 - 架构笔记 著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。