InnoDB存储引擎是MySQL数据库中使用最为广泛的引擎。在海量大并发的 OLTP 业务中，InnoDB 必选。它在数据存储方面有一个非常大的特点：索引组织表（Index Organized Table）。

一、什么是索引组织表？

数据存储有堆表和索引组织表两种方式。

堆表中的数据无序存放， 数据的排序完全依赖于索引（Oracle、Microsoft SQL Server、PostgreSQL 早期默认支持的数据存储都是堆表结构）。

堆表的组织结构中，数据和索引分开存储。索引是排序后的数据，而堆表中的数据是无序的，索引的叶子节点存放了数据在堆表中的地址，当堆表的数据发生改变，且位置发生了变更，所有索引中的地址都要更新，这非常影响性能，特别是对于 OLTP 业务。

而索引组织表，数据根据主键排序存放在索引中，主键索引也叫聚集索引（Clustered Index） 。在索引组织表中，数据即索引，索引即数据。

MySQL InnoDB 存储引擎就是这样的数据组织方式；Oracle、Microsoft SQL Server 后期也推出了支持索引组织表的存储方式。

但是，PostgreSQL 数据库因为只支持堆表存储，不适合 OLTP 的访问特性，虽然它后期对堆表有一定的优化，但本质是通过空间换时间，对海量并发的 OLTP 业务支持依然存在局限性。

二、二级索引

InnoDB 存储引擎的数据是根据主键索引排序存储的，除了主键索引外，其他的索引都称之为二级索引（Secondeary Index） ， 或非聚集索引（None Clustered Index） 。

二级索引也是一颗 B+ 树索引，但它和主键索引不同的是叶子节点存放的是索引键值、主键值。比如我们在log表中加入一个二级索引，表结构如下：

create table sys_log(
id bigint auto_increament,
address varchar(20) not null,
create_time datetime,
update_time datetime

primary key(id),//主键索引
key index_address(address),//二级索引
)

如果我们此时需要通过address查询日志信息，SQL如下：

select * from sys_log where address="浙江省";

通过二级索引 index_address 只能定位主键值，需要额外再通过主键索引进行查询，才能得到最终的结果。这种“二级索引通过主键索引进行再一次查询”的操作叫作“回表”.

索引组织表这样的二级索引设计有一个非常大的好处：若记录发生了修改，则其他索引无须进行维护，除非记录的主键发生了修改。

三、二级索引的性能如何？

主键在设计时可以选择比较顺序的方式，比如自增整型，自增的 UUID 等，所以主键索引的排序效率和插入性能相对较高。二级索引就不一样了，它可能是比较顺序插入，也可能是完全随机的插入，具体如何呢？我们来看一下以下的表结构：

CREATE TABLE User (

    id  BINARY(16) NOT NULL,

    name VARCHAR(255) NOT NULL,

    sex CHAR(1) NOT NULL,

    password VARCHAR(1024) NOT NULL,

    money BIG INT 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)

);

以上表中有三个二级索引，分别是：idx_name，idx_register_date，idx_last_modify_date。

通常业务是无法要求用户注册的昵称是顺序的，所以索引 idx_name 的插入是随机的， 性能开销相对较大；另外用户昵称通常可更新，但业务为了性能考虑，可以限制单个用户每天、甚至是每年昵称更新的次数，比如每天更新一次，每年更新三次。

而用户注册时间是比较顺序的，所以索引 idx_register_date 的性能开销相对较小， 另外用户注册时间一旦插入后也不会更新，只是用于标识一个注册时间。

而idx_last_modify_date，在真实业务的表结构设计中，需要对每个核心业务表创建一个列 last_modify_date，标识每条记录的修改时间。

这时索引 idx_last_modify_date 的插入和 idx_register_date 类似，是比较顺序的，但不同的是，索引 idx_last_modify_date 会存在比较频繁的更新操作，比如用户消费导致余额修改、money 字段更新，这会导致二级索引的更新。

由于每个二级索引都包含了主键值，查询通过主键值进行回表，所以在设计表结构时让主键值尽可能的紧凑，为的就是能提升二级索引的性能.

另外，在实际核心业务中，可能大家还会设计带业务属性的主键，但这里需要记住两点：1、要比较顺序，对聚集索引性能友好。2、要尽可能紧凑，对二级索引的性能和存储友好。

四、函数索引

前面我们介绍的主键索引、二级索引等，都是基于列的基础来设计的，还有一个索引我们也需要了解：函数索引（索引键是一个函数表达式）。它有两大好处：

• 1、优化业务SQL性能；
• 2、配合虚拟列（Generated Column）。

如何理解上述的两个好处呢？

1、好处1：优化业务 SQL 性能

我们知道，不是每个开发人员都能比较深入地了解索引的原理，有时他们的表结构设计和编写 SQL 语句会存在“错误”，比如对于上面的表 User，要查询 2021 年1 月注册的用户，有些开发同学会错误地写成如下所示的 SQL：

SELECT * FROM User 

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没有被使用到：

EXPLAIN SELECT * FROM User

WHERE DATE_FORMAT(register_date,'%Y-%m') = '2021-01'

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

           id: 1

  select_type: SIMPLE

        table: User

   partitions: NULL

         type: ALL

possible_keys: NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 1

     filtered: 100.00

        Extra: Using where

上述需求正确的 SQL 写法应该是，其中变化在第 2 行，主要将函数 DATE_FORMAT 插接为了一个范围查询：

EXPLAIN SELECT * FROM User

WHERE register_date > '2021-01-01' 

AND register_date < '2021-02-01'

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

           id: 1

  select_type: SIMPLE

        table: User

   partitions: NULL

         type: range

possible_keys: idx_register_date

          key: idx_register_date

      key_len: 8

          ref: NULL

         rows: 1

     filtered: 100.00

        Extra: Using index condition

如果线上业务真的没有按正确的 SQL 编写，那么可能造成数据库存在很多慢查询 SQL，导致业务缓慢甚至发生雪崩的场景。要尽快解决这个问题，可以使用函数索引， 创建一个DATE_FORMAT(register_date) 的索引，这样就能利用排序数据快速定位了：

ALTER TABLE User 

ADD INDEX 

idx_func_register_date((DATE_FORMAT(register_date,'%Y-%m')));

接着用命令 EXPLAIN 查看执行计划，就会发现 SQL 可以使用到新建的索引idx_func_register_date：

EXPLAIN SELECT * FROM User 

WHERE DATE_FORMAT(register_date,'%Y-%m') = '2021-01'

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

           id: 1

  select_type: SIMPLE

        table: User

   partitions: NULL

         type: ref

possible_keys: idx_func_register_date

          key: idx_func_register_date

      key_len: 31

          ref: const

         rows: 1

     filtered: 100.00

        Extra: NULL

2、好处2：结合虚拟列使用

比如我们有如下表结构：

CREATE TABLE UserLogin (

    userId BIGINT,

    loginInfo JSON,

    cellphone VARCHAR(255) AS (loginInfo->>"$.cellphone"),

    PRIMARY KEY(userId),

    UNIQUE KEY idx_cellphone(cellphone)

);

其中的列 cellphone 就是一个虚拟列，它是由后面的函数表达式计算而成，本身这个列不占用任何的存储空间，而索引 idx_cellphone 实质是一个函数索引。这样做得好处是在写 SQL 时可以直接使用这个虚拟列，而不用写冗长的函数：

-- 不用虚拟列

SELECT  *  FROM UserLogin

WHERE loginInfo->>"$.cellphone" = '13918888888'



-- 使用虚拟列

SELECT  *  FROM UserLogin 

WHERE cellphone = '13918888888'

对于爬虫类的业务，我们会从网上先爬取很多数据，其中有些是我们关心的数据，有些是不关心的数据。通过虚拟列技术，可以展示我们想要的那部分数据，再通过虚拟列上创建索引，就是对爬取的数据进行快速的访问和搜索。

文章将持续更新，欢迎关注公众号：服务端技术精选。欢迎点赞、关注、转发。