上一节了解了B+树索引的基本概念，以及MySQL中怎么对B+树索引进行基本的管理，为了进一步了解MySQL中B+树索引的具体使用，这一节聊一聊MySQL InnoDB存储引擎的索引结构。
InnoDB存储引擎是MySQL数据库中使用最为广泛的引擎，在海量并发的OLTP业务中，InnoDB必须。它在数据存储方面有一个非常大的特点就是：索引组织表（Index Organized Table）
数据存储有堆表和索引组织表两种方式
堆表
堆表中的数据无序存放，数据的排序完全依赖于索引（Oracle、Microsoft SQL Server、PostgreSQL早期默认支持的数据存储都是堆表结构）。
堆表
从图中可以看到，堆表的组织结构中，数据和索引分开存储。索引时排序后的数据，二堆表中的数据是无序的，索引的叶子节点存放了数据在堆表中的地址，当堆表的数据发生改变，且位置发生了变更，所有索引中的地址都要更新，这非常影响性能，尤其是对于OLTP业务。
索引组织表
索引组织表，数据根据主键排序存放在索引中，主键索引也叫聚集索引（Clustered Index）。在索引组织表中，数据即索引，索引即数据。
MySQL InnoDB存储引擎就是这样的数据组织方式，Oracle、Microsoft SQL Server后期也推出了支持索引组织表的存储方式。但是PostgreSQL数据库因为只支持堆表存储，不适合OLTP的访问特性，虽然它后期对堆表有一定的优化，但本质是通过空间换时间，对海量并发的OLTP业务支持依然存在局限性。
B+树索引
回顾上一节中的users表，其就是索引组织表的方式，主键为id，所以表中的数据根据id排序存储，叶子节点存放了表中完整的记录，可以看到表中的数据存放在索引中，即表就是索引，索引就是表。
二级索引
InnoDB存储引擎的数据是根据主键索引排序存储的，除了主键索引外，其他的索引都称之为二级索引（Secondeary Index）或称非聚集索引（None Clustered Index）。
二级索引也是一颗B+树索引，但它和主键索引不同的是叶子节点存放的是索引键值、主键值。对于上一节创建的表users，假设在列name上创建了索引idx_name，该索引就是二级索引：
CREATE TABLE users (
id BIGINT AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
sex CHAR(6) NOT NULL,
register_date DATETIME NOT NULL,
...
PRIMARY KEY(id), -- 主键索引
KEY idx_name(name) -- 二级索引
);
如果用户通过列name进行查询，比如下面的SQL：
SELECT * FROM users WHERE name='David';
通过二级索引idx_name只能定位到主键值，需要额外的再通过主键索引进行查询，才能得到最终的结果。这种”二级索引通过主键索引进行再一次查询的操作“叫做回表，可以通过下图理解二级索引的查询：
回表
索引组织表这样的二级索引设计有一个非常大的好处：若记录发生了变化其他索引无须进行维护，除非记录的主键发生了修改。
与堆表的索引实现对比来看，会发现索引组织表在存在大量变更的场景下，性能优势会非常明显，因为大部分情况下都不需要维护其他二级索引。
前面强调的”索引组织表，数据即索引，索引即数据“。那么为了便于理解二级索引，你可以将二级索引按照一张表进行理解，比如索引idx_name可以理解乘一张表，如下所示：
CREATE TABLE idx_name (
name VARCHAR(255) NOT NULL,
id BIGINT NOT NULL,
PRIMARY KEY(name,id)
);
根据name进行查询的SQL可以理解为拆分成了两个步骤：
SELECT id FROM idx_name WHERE name = ?;
SELECT * FROM users WHERE id = _id; -- 回表
当插入数据时，你可以理解为对主键索引表、二级索引表进行一个事务操作，要么都成功，要么都不成功：
START TRANSATION;
INSERT INTO users VALUES (...) -- 主键索引
INSERT INTO idx_name VALUES (...) -- 二级索引
COMMIT;
当然，对于索引，还可以加入唯一的约束，具有唯一约束的索引称之为唯一索引，也是二级索引。对于表users，列name应该具有唯一约束，因为通常用户注册要求昵称唯一，所以表users定义更新为：
CREATE TABLE users (
id BIGINT AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
sex CHAR(6) NOT NULL,
register_date DATETIME NOT NULL,
...
PRIMARY KEY (id),
UNIQUE KEY idx_name(name)
);
那么对于唯一索引又该如何理解为表呢？其实我们可以将约束理解成一张表或一个索引，故唯一索引idx_name应该理解为：
CREATE TABLE idx_name(
name VARCHAR(255) NOT NULL,
id BIGINT NOT NULL,
PRIMARY KEY (name,id)
) -- 二级索引
CREATE TABLE check_idx_name(
name VARCHAR(255),
PRIMARY KEY(name)
) -- 唯一约束
现在，你应该理解了吧！在索引组织表中，万物皆索引，索引就是数据，数据就是索引。
最后，为了加深对于索引组织表的理解，我们来回顾以下堆表的实现。
堆表中的索引都是二级索引，哪怕是主键索引也是二级索引，也就是说它没有聚集索引，每次索引查询都要回表。同时堆表中记录都存放在数据文件中，并且无须存放，这对海量并发互联网的OLTP业务来说，堆表的实现确实”过时“了。
二级索引的性能评估
主键在设计时可以选择比较顺序的方式，比如自增整型，有序的UUID等。所以主键索引的排序效率和出入性能相对较高。二级索引就不一样了，它可能是比较顺序出入，也可能是完全随机的插入，具体如何呢，来看下比较接近业务的表users：
CREATE TABLE users (
id BINARY(16) NOT NULL,
name VARCHAR(255) NOT NULL,
sex CHAR(1) NOT NULL,
password VARCHAR(1024) NOT NULL,
money BIGINT NOT NULL DEFAULT 0,
register_date DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
last_modify_date DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
uuid CHAR(36) AS (BIN_TO_UUID(id)),
CHECK (sex = 'M' OR sex = 'F'),
CHECK (IS_UUID(UUID)),
PRIMARY KEY(id),
UNIQUE KEY idx_name(name),
KEY idx_register_date(register_date),
KEY idx_last_modify_date(last_modify_date)
);
表users有3个二级索引idx_name、idx_register_date、idx_last_modify_date。
通常业务时无法要求用户注册的昵称时顺序的，所以索引idx_name的插入是随机的，性能开销相对较大；另外昵称通常可更新，但业务为了性能考虑，可以限制单个用户每天、甚至是每年昵称更新的次数。
而用户注册时间是比较顺序的，所以索引idx_register_date的性能开销相对较小，另外用户注册时间一旦插入之后不会更新。
而关于idx_last_modify_date,插入时是比较顺序的，但是该列会存在比较频繁的更新操作，这会导致二级索引的更新。
由于每个二级索引都包含了主键值，查询通过主键值进行回表，所以在表结构设计让逐渐尽可能的紧凑，为的就是能提升二级索引的性能。
除此之外，在实际核心业务中，程序员同学还有很大可能会设计带有业务属性的主键，但请牢记以下两点设计规则：
- 要比较顺序，对聚集索引性能友好
- 尽可能紧凑，对二级索引的性能和存储友好（紧凑是指一个页能存放的记录数尽可能多）
函数索引
从 MySQL5.7版本开始，MySQL支持创建函数索引（即索引键是一个函数表达式），函数索引有两大用处：
-
优化业务SQL性能
-
配合虚拟列（Generated Column）
优化业务SQL性能
比如对于users表，要查询2021年1月注册的用户，有些同学会错误地写成如下SQL:
SELECT * FROM users WHERE DATE_FORMAT(register_date,'%Y-%m') = '2021-01'
获取开发同学认为在register_date列创建了索引，所以所有的SQL都可以使用该索引。但索引地本质是排序，索引idx_register_date只对register_date地数据排序，又没有对DATE_FORMAT(register_date)排序，所以上述SQL无法使用二级索引idx_register_date.
数据库规范要求查询条件中函数写在等式右边，而不能写在左边，就是这个原因。
通过命令EXPLAIN查看上述SQL的执行计划，会更为直观的发现索引idx_register_date没有被使用到：
mysql> EXPLAIN SELECT * FROM users WHERE DATE_FORMAT(register_date,'%Y-%m')='2021-01';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | users | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
正确的SQL写法应该是：
mysql> EXPLAIN SELECT * FROM users WHERE register_date > '2021-01-01' and register_date < '2021-02-01';
+----+-------------+-------+------------+-------+-------------------+-------------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+-------------------+-------------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | users | NULL | range | idx_register_date | idx_register_date | 8 | NULL | 1 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------+-------------------+-------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
如果线上业务真的没有按正确的SQL编写，那么可能造成数据库存在很多慢查询，导致业务缓慢甚至发生雪崩的场景。要尽快解决这个问题，可以使用函数索引，创建一个DATE_FORMAT(register_date)的索引：
ALTER TABLE users ADD INDEX idx_func_register_date((DATE_FORMAT(register_date,'%Y-%m')));
接着用命令 EXPLAIN 查看执行计划，就会发现 SQL 可以使用到新建的索引idx_func_register_date：
mysql> EXPLAIN SELECT * FROM users WHERE DATE_FORMAT(register_date,'%Y-%m')='2021-01';
+----+-------------+-------+------------+------+-------------------------------+-------------------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+-------------------------------+-------------------------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | users | NULL | ref | idx_funidx_func_register_date | idx_funidx_func_register_date | 10 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+-------------------------------+-------------------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
上述创建的函数索引可以解决业务线上的燃眉之急，但强烈建议业务开发同学在下一个版本中优化SQL，否则会导致对同一份数据做了两份索引，索引需要排序，排序多了就会影响性能。
结合虚拟列使用
在前面JSON小节中，我们已经创建了表UserLogin：
CREATE TABLE UserLogin (
userId BIGINT,
loginInfo JOSN,
cellphone VARCHAR(255) AS (loginInfo->>"$.cellphone"),
PRIMARY KEY (userId),
UNIQUE KEY idx_cellphone(cellphone)
);
其中的列cellphone就是一个虚拟列，它由后面的函数表达式计算而得，本身这个列不占用任何的存储空间，而索引idx_cellphone实质是一个函数索引，这样做的好处实在写SQL时，可以直接使用这个虚拟列，而不用写冗长的函数。对于爬虫类的业务，我们会从网上先爬取很多数据，其中有些是我们关心的数据，有些是不关心的数据。通过虚拟列技术，可以展示我们想要的那部分数据，再通过虚拟列上创建索引，就是对爬取的数据进行快速的访问和搜索。
总结
- 索引组织表主键是聚集索引，索引的叶子节点存放表中一整行完整记录；
- 除主键索引外的索引都是二级索引，索引的叶子节点存放的是（索引键值，主键值）
- 二级索引不存放完整记录，因此需要通过主键值再进行一次回表才能定位到完整数据
- 索引组织表对比堆表，在海量并发的OLTP业务中能又更好的性能表现
- 每种不同数据，对二级索引的性能开销影响是不一样的
- 有时通过函数索引可以快速解决线上SQL的性能问题
- 虚拟列不占用实际存储空间，在虚拟列上创建索引本质就是函数索引
网友评论