思考几个问题

1. 表结构建索引的依据是什么，有哪些注意事项？什么情况下会考虑分库分表？

2. “聚集索引”和“非聚集索引”的区别，能以集团实际使用场景举例吗？

3. 什么情况下索引会失效，有办法不失效吗？

4. 1. 右模糊匹配(xxx%)、左模糊匹配(%xxx)、全模糊匹配(%xxx%)

   2. 没传查询条件

5. MySQL索引底层数据存储格式为什么是B+树，而不是B树、红黑树、哈希表？集团使用的XDB也是一样吗？

6. 随着SSD、ESSD等存储介质和技术的问世，数据库当前的数据存储结构会过时吗？

7. 数据库的存储计算分离怎么理解？存储引擎属于计算还是存储？

来看个例子

一个14秒的SQL拖垮线上环境

业务影响：

1. BU预算追加统计页面数据加载超时，影响资源管理员、财务评估需求合理性

2. 预算审批流程获取审批人信息超时，影响审批流程正常往下走

3. 首页“我的任务”数据加载超时

系统表现：

1. JVM的Eden区、Old区全满，不停在做FGC，集群重启后并无好转

2. 网络重传率飙升，在用连接数骤减

数据库连接超时报错：

org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.PersistenceException:

### Error querying database. Cause: ERR-CODE: [TDDL-4614][ERR_EXECUTE_ON_MYSQL] Error occurs when execute on GROUP 'HCRM_ECO_BUDGET_GROUP' ATOM 'xxx': Communications link failure

[TDDL] all of current dbkeys weight is 0, maybe db error happened, dbKeys:[x x x,xxx], weight:[10, 0], tddl version: 5.2.10-1

数据库表现：

慢SQL分析：

14秒的慢SQL定位（没有传入实际业务语义的查询条件，全表扫描）：

xxxxxxxxxx
select * from user_entity where tenant_id = ? and is_deleted = ?

建表语句：

xxxxxxxxxx
CREATE TABLE `user_entity` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `gmt_create` datetime NOT NULL COMMENT '创建时间',
  `gmt_modified` datetime NOT NULL COMMENT '修改时间',
  `creator` varchar(256) NOT NULL COMMENT '创建人',
  `modifier` varchar(256) NOT NULL COMMENT '修改人',
  `is_deleted` tinyint(3) unsigned NOT NULL COMMENT '是否删除',
  `tenant_id` varchar(64) NOT NULL COMMENT '租户 ID',
  `user_id` varchar(256) NOT NULL COMMENT '用户 ID',
  `entity_id` varchar(64) NOT NULL COMMENT '部门 ID',
  `entity_name` varchar(64) NOT NULL COMMENT '部门名称',
  `entity_full_name` text NOT NULL COMMENT '部门全名',
  `entity_en_name` text NOT NULL COMMENT '部门英文名',
  `entity_path` varchar(256) NOT NULL COMMENT '部门路径',
  `entity_type` varchar(32) NOT NULL COMMENT '部门类型',
  `sign` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '标记',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_userid` (`user_id`(32),`tenant_id`(32),`sign`),
  KEY `idx_gmtmodified_isdel` (`gmt_modified`,`is_deleted`)
) ENGINE=InnoDB AUTO_INCREMENT=60530544 DEFAULT CHARSET=utf8mb4 COMMENT='用户和部门的关联关系'

代码分析：

1. 查询用户信息时，userId入参为null退化为全表扫描，返回数据量过大，触发FGC

右侧是bug fix补充上的代码：

索引基础

什么是索引（what）

百度百科：在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。

数据与索引的关系，一般我们都会类比成字典与目录的关系。字典里面的字词解释对应着数据库的数据，目录对应着数据库的索引，索引需要额外占用数据存储空间。另一方面，字典可以按照拼音去查询字词解释条目，也可以按边旁部首去查询条目。

索引按照不同维度可以分为不同类型，如下图所示，按照数据存储方式分为聚集索引和非聚集索引。

• 聚集索引（Clustered index），就是按照数据表设置的主键列以B+树数据结构存储数据的一种方式，其中，只有叶子节点存储记录数据条目，而非叶子结点主要用于记录主键列的不同区间范围。

• 非聚集索引也称之为二级索引（Secondary Index），和聚集索引的区别是叶子节点存储的是主键列的值，当使用非聚簇索引定位全部字段数据时，需要先定位到主键值，再根据主键值查找聚簇索引，最终定位到具体的字段值。

• 每张表当中只允许存在一个聚集索引，但可以同时存在多个非聚集索引。

索引的作用（why）

索引是按照指定列和规则建立的一种数据排列组合的结构，当遇到数据表数据量比较大，检索消息成为系统瓶颈的时候，第一时间想到的就是给数据表创建索引。但索引对于业务而言并非是必须的，尤其是对于数据量比较小的数据表或者修改频率比较大的数据表而言，索引的创建不仅不会加快查询速度，反而会降低查询速度，因为需要额外的索引维护。索引的作用根据不同的类型划分，主要包括：提高数据检索效率、保证数据唯一性，以及提供排序分组效率。

优点：

• 大大减小服务器需要扫描的数据量，从而加快数据的检索速度，这也是创建索引的最主要原因

• 帮助服务器避免排序和创建临时表

• 将随机IO变成顺序IO

• 对于InnoDB（对索引支持行级锁）非常重要，因为它可以让查询锁更少的元组，提高了表访问并发性

• 关于InnoDB、索引和锁：InnoDB在二级索引上使用共享锁（读锁），但访问主键索引需要排他锁（写锁）

• 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性

• 在使用分组和排序子句进行数据检索时，显著减少查询中分组和排序的时间

缺点：

• 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加

• 索引需要占物理空间，如果需要建立聚集索引，那么需要占用的空间会更大

• 对表中的数据进行增、删、改的时候，索引也要动态进行调整维护

• 如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果（区分度不显著）

• 对于非常小的表，大部分情况下简单的全表扫描更高效

什么时候需要索引（when）

• 系统反应时间长，识别出有慢SQL，排查是否存在索引、存在的索引是否合理、是否适合当前业务场景

• 业务需要保证数据唯一性时，对数据表单个字段或者组合字段添加唯一索引

• 在考虑使用索引的时候，需要结合具体的业务场景。因为索引的使用也是需要占用物理空间的，虽然提高检索效率采取的策略是用空间换取时间，但需要尽量追求二者的平衡。数据表的增删改操作都会触发索引结构的更新，所以索引并不是越多越好。

如何创建索引（how）

新建、删除、修改（先删再重建），到DMS操作

索引进阶

SQL语句的执行轨迹

从整体而言，MySQL分为Server层（无状态）和存储引擎层（有状态）。Server层主要包括：连接器、查询缓存、解析器、优化器和执行器，所有跨存储引擎的功能都在这一层实现，比如：存储过程、触发器、视图、经常使用的聚合函数等。存储引擎层主要是数据的实际存储，提供api供上层Server层查询数据。

• 连接器：想要执行一条SQL语句，首先需要连接到指定数据库上，这时候涉及到的就是连接器，连接器首先会建立连接->验证权限->对建立的链接进行管理，如果账号错误，也会进行提示。

• 查询缓存：客户端和数据库建立连接后，就可以执行查询语句了，执行查询语句首先就会看查询缓存中是否存在之前的查询结果。之前的查询结果直接在内容中以key-value的格式存储，其中key是查询语句，value是查询结果，如果存在直接返回客户端value中存储的查询结果。如果在查询缓存中没有查询到历史执行结果，就会继续执行后面的步骤。（默认情况下缓存是关闭的）。

• 解析器：解析器主要负责对编写的查询语句做正确性判断，首先会根据输入的语句做识别，确认属于什么类型的语句，语句是否符合MySQL规范、字段、表明是否存在等作分析，如果有任何的问题，都会直接返回客户端错误信息，比如：大家最常见的unknown column错误。

• 优化器：MySQL通过解析器已经知道你想要做什么，然后优化器会对SQL语句做优化，比如：是否使用索引、多个索引同时存在的情况下，具体使用哪个索引、多个数据表关联查询的时候，表的先后关系等。

• 执行器：MySQL通过解析器已经知道你想要做什么，通过优化器知道了怎么做，执行器开始执行具体语句，通过存储引擎提供的API接口，依次获取满足条件的数据行记录，全部数据获取完成后，返回给客户端。

• 存储引擎：负责数据的实际存储功能，存储引擎是插件式的嵌入到MySQL当中，从MySQL 5.5版本后InnoDB作为默认的存储引擎。

索引底层数据存储格式为B+树

数据库中常见的支持数据动态修改和快速查询的数据结构，主要有散列表（哈希表）、平衡二叉查找树（AVL）和红黑树。其中，

• 散列表，具有很好的查询、修改性能，时间复杂度是O(1)，但对于范围查询只能全表遍历。

• 平衡二叉查找树，查询的时间复杂度是O(logN)，但对于范围查询也只能全表遍历。另外，由于AVL树要求所有节点的左右子树高度差的绝对值不超过1，是严格的平衡二叉树，在执行完插入或者删除操作后只要不满足平衡条件，就要通过旋转来保持平衡非常耗时，因此较适用于插入与删除次数比较少但查找多的场景。

• 红黑树，查询的时间复杂度是O(logN)，对于范围查询也只能全表遍历。通过对任何一条从根到叶子的路径上各个节点着色的方式的限制，红黑树确保没有一条路径会比其它路径长出两倍，相对AVL树而言红黑树是一种弱平衡二叉树，旋转次数相对较少，所以对于搜索，插入，删除操作较多的情况下，我们就用红黑树。

为了同时支持等值查询和范围查询，同时减少每次查询的访问次数，MySQL采用了B+树作为索引的存储结构。B+树的非叶子节点并不存储数据而是存储索引关系，只有叶子节点才真正存储数据，而且叶子节点使用双向链表相连，链表上的数据按照从小到大的顺序排列，从而支持基于区间的数据查询。使用B+树还有另一个原因是为了减少磁盘的访问次数从而提高数据查询的效率（与树的高度有关）。为了节约内存，索引通常是存储在硬盘上的，B+树每个节点代表一次磁盘IO，树的高度就是实际磁盘IO的次数，所以B+树采用了增加叉数降低树的高度，从而减少了磁盘IO的次数。

B+树存储结构

另一方面，操作系统在读取数据的时候都是按页为单位（4KB）读取的，而并非访问多少数据就读多少数据。如果每次访问磁盘的一页数据刚好全部是B+树的非叶子节点的话(字段类型以int举例，约1k多个子节点)，三层的1k叉树最多能放多少条数据（1k1k1k=10亿）。一般来说根节点MySQL都会缓存在内存中，因此查询到数据只需要访问两次的磁盘页，即只要访问两次磁盘即可。

上面介绍的是聚集索引的存储，非聚集索引的存储是类似的，只是叶子节点存储的不是数据记录本身，而是指向聚集索引的键值，这也是为什么非聚集索引也被称为二级索引的原因。由于B+树特有的存储结构，可以看出基于在有索引字段的等值和范围查询是较快的，通过节点的定义更能直观的看到。

执行性能分析

对sql语句进行性能分析最常用的方式是通过explain查看分析结果，explain执行计划看到的是一个预估的值，而非实际准确的值，只能查看select查询语句，而且不会考虑使用查询缓存。语法格式为：explain <sql语句>，下面对explain输出结果的具体的字段含义进行下说明。

-------------------------------------------------------------- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

--------------------------------------------------------------

• id：SQL语句执行顺序，按照id从大到小的执行，当id相同时表示是同一组

• select_type：表示select语句的类型

• • SIMPLE，最简单的SELECT语句（不使用UNION或子查询等）

  • PRIMARY，最外层的语句

  • UNION，当语句中存在UNION时，UNION中的第二个或后面的SELECT语句

  • DEPENDENT UNION，UNION中的第二个或后面的SELECT语句，取决于外面的查询结果

  • SUBQUERY，子查询中的第一个SELECT，其结果不依赖于外面的查询结果

  • DEPENDENT SUBQUERY，子查询中的第一个SELECT，依赖于外面的查询结果

  • DERIVED，当从一个中间结果进行FROM时，指的是派生表的SELECT(FROM子句的子查询)

• table：指定行数据是从哪张表获取，临时表也在这个范围内

• type：表访问方式是否使用了索引，常用的类型有： ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好）

• • ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行

  • index：Full Index Scan，index与ALL区别为index类型只遍历索引树

  • range：只检索给定范围的行，使用一个索引来选择行

  • ref：表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

  • eq_ref：类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件

  • const、system：当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用system

  • NULL：MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成

• possible_keys：MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用（该查询可以利用的索引，如果没有任何索引显示 null）

• key：MySQL实际决定使用的键（索引），必然包含在possible_keys中

• key_len：索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）

• ref：列与索引的比较，表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

• rows：估算找到所需的记录所需要读取的行数，核心指标

• Extra：该列包含MySQL解决查询的详细信息,有以下几种情况

• • Using index condition：列数据仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的时候

  • Using where：不用读取表中所有信息，仅通过索引就可以获取所需数据，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤

  • Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询，常见 group by 、order by

  • Using filesort：查询语句包含order by，且无法利用索引完成排序，执行过程中使用临时文件进行排序

  • Using join buffer：改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果，如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能

  • Impossible where：通过where语句会导致没有符合条件的行

  • Select tables optimized away：只通过使用索引，优化器可能仅从聚合函数结果中返回一行

  • No tables used：Query语句中使用from dual 或不含任何from子句

索引失效的场景

索引虽然具有提高查询效率的能力，但是过分使用索引会给系统性能带来副作用，不适合创建索引的场景

• 数据表数据量较少，使用全表扫描效率可以接受的场景

• 更新操作频繁的数据字段，修改性能和检索性能是相互矛盾的

• 区分度不高的字段，比如true和flase

• 很少使用到的字段

• 现有索引前缀已经包括要创建索引涉及字段

• 字段类型为text、image等占用存储空间太大的类型

• 经常需要使用聚合函数计算的字段

• 经常需要使用模糊查询的字段，主要是指左模糊匹配(%xxx)以及全模糊匹配(%xxx%)

• 经常需要通过负向组合的查询语句，如：not、not in、not like、!=等

• 字段类型不一致，比如字段类型为varchar但输入的where条件是一个int

索引使用原则

• 最左前缀匹配原则，是指使用联合索引时会从左至右依次匹配，不能跳跃式的使用。对于联合索引，B+树首先会按照第一个字段组织数据，然后第二个字段……第n个字段组织数据，单纯看第n个字段在B+树中是无序的，然而在前面n-1个字段固定的情况下，第n个字段也是有序排列的，所以查询语句中如果前n个字段都走索引的情况下，第n的字段也是有序的，自然可以走索引，举例说明：假设有三个int类型字段a、b、c，然后建了一个a_b_c的组合索引

• • where a=1 and b=2 and c>3 -- 此时检索效率最高（c充分利用了索引的有序性，顺序IO）

  • where a>1 and b=2 and c=3 -- 此时检索效率最低（b、c可能分散在不同区间，最终导致乱序IO）

• sql书写顺序和索引顺序无关，优化器会自动优化SQL语句，使其适配需要走的索引格式

• 索引列应该尽量选择区分度高的列作为索引

• 建立索引的字段不要使用聚合函数，使用聚合函数后默认不能走相关的索引

• 在遵守最左前缀的原则下，尽量的扩展索引，而不是新建索引

• 如果SQL可能命中多个索引，MySQL执行计划是根据某条SQL判定最终使用哪个索引，不会动态调整索引的选择，如果业务侧明确具体某个索引在大部分情况下是更优的，那么可以强行指定索引 force index

• • 比如：SELECT * FROM core_demand_item WHERE gmt_modified > xxx and account_id = xxx，如果gmt_modified 和account_id都建了索引，那么MySQL在一段时间内都会选择其中的某一条索引进行执行，如果选到了gmt_modified，那么它不管在什么时候都不会走到account_id这个索引了

慢SQL优化

慢SQL是稳定性建设中的重要治理对象，一般指查询时间超过1s或扫描行数百万的SQL操作，其发现方式主要是：CloudDBA慢SQL日志。

危害：

• 响应时效：结果反馈时间长，容易发生超时，用户体验差

• 资源消耗：慢SQL往往是扫描很多行但是真正使用的只有其中的小部分，这造成了大量无用的资源消耗，比如：CPU资源、IO资源、内存空间、网络带宽、锁，一般问题集中在前两个

• 服务不可用：当大量慢SQL并发的出现，可能把当前数据库的连接池打满或者把数据库服务器CPU打满，导致其他依赖这个库/表的服务不可用

大部分慢SQL是可以通过添加索引、分库分表的方式来优化解决，还有些优化技巧同样值得关注。

分页优化

很多接口都会提供分页查询的能力，但当对于一些遍历结果的定时任务来说可能会扫描到最后一页（深度分页）。这意味着当筛选出的结果很多的时候，可能出现 SELECT ... OFFSET 49990 LIMIT 10 类似这样的SQL语句，它会扫描5w行记录然后抛弃前面49990行返回最后10行，5w行就算全部命中索引，有时候耗时都会高达2s。耗时原因主要在于：

1. 抛弃前面49990行是在数据库Server层执行的，而实际获取数据是在存储引擎层，两层之间是分开无感知的

2. 数据获取会先在非聚集索引里按顺序拿到记录的主键id，再去聚集索引（主键）中找到完整记录数据，但是这个操作不是顺序的是随机的，也就是说进行了5w次的随机IO

分页边改边查

假设数据表budget有1000w行数据，现在有个定时任务遍历FY2022的预算条目，捞出来以后将状态从草稿置为已审批，原SQL示意如下

xxxxxxxxxx
SELECT *
  FROM budget
 WHERE is_deleted = 0
   AND fyear = 'FY2022'
   AND status = 'draft'
 LIMIT 10000, 100

优化后，每次记录上一次扫描结束的记录主键id作为下一次扫描的参数，这里还会产生索引合并（index merge），主键索引和二级索引的结果做交集，降低扫描的范围，当分页到很后面的时候优化效果会更加明显。

xxxxxxxxxx
SELECT *
  FROM budget
 WHERE id > 7506888
   AND is_deleted = 0
   AND fyear = 'FY2022'
   AND status = 'draft'
 ORDER BY id
 LIMIT 100

延迟关联子查询优化

一个典型二级索引查询，例如：

xxxxxxxxxx
SELECT * FROM core_demand_item 
WHERE commodity_type = 'pouch' 
LIMIT 30000,500

大致分三步：

1. 首先在二级索引(idx_commodity_type)筛选出符合条件的记录的主键id

2. 如果没有索引覆盖则带着主键id到一级索引里查完整行记录

3. 完整行记录返回给server层，根据limit对行记录进行过滤

这里可以发现由于要查询select很多列所以会把一级索引里所有的行记录取出来再使用limit筛选，这一步显的很重，因为我们会对很多不需要的行做读取IO，比如 LIMIT 1000,50 那无用的行记录IO就是950。子查询的思路就是把limit的操作提前到二级索引筛选的时候，在到一级索引中搜索结果输出，相当于把查询拆成两步把limit筛选提前。这个过程中大致分为：

1. 首先在二级索引(idx_commodity_type)筛选出符合条件的主键id

2. 在id的结果中根据limit选出符合要求的主键id

3. 通过主键做表连接，返回符合要求的完整行记录

优化后的SQL写法：

xxxxxxxxxx
SELECT *
FROM core_demand_item p1 join(
    SELECT id FROM core_demand_item WHERE commodity_type = 'pouch' 
  LIMIT 30000, 500) as p2 
ON p1.`id`= p2.`id`;

count优化

很多接口调用需要获取符合条件的记录总量，这就需要用到count()（count()和count(1)效率相同），计数当遇到索引使用不到位、筛选条件不合理会产生大量行扫描。但是count的优化方式不多，要么取大概值要么离线算好专门维护一个数量值，要么分表，直接优化方式最好的就是索引覆盖，避免回表查询后在server层进行筛选然后计数。

exist替代全表count(*)

有的业务需要根据条件判断表中是否有符合条件的记录，这种不需要count计算全表记录数，只需要最快命中符合条件的记录即可，exist有短路机制，因此效率更高。对于这种业务需求可以直接将sql语句改为如下的形式：

xxxxxxxxxx
-- 1是有，0是无
SELECT count(*) FROM (SELECT 1 FROM table1 WHERE ... LIMIT 1);
 
-- 1是有，0是无
SELECT EXISTS (SELECT * FROM table1 WHERE ...);

额外计数存储

创建另一个表或者直接用一个tair计数器，汇总每天的记录数，获取更精准的数量可以通过sql查询今天的总数再加上昨天的计数。

索引优化

通过explain查看数据访问类型type，SQL性能优化的目标：至少要达到 range 级别，要求是ref级别，如果可以是const最好。一般来说所有类型效率从高到低：（常见几种的是const > ref > range > index > all）

低效索引

在慢SQL治理中可以适当修改或删除低效的索引，减少不必要的资源开销。

• 选择性不高的索引。其性能可能没有提升多少跟全表扫描一样，还会浪费/损耗资源，比如性别之类的，可以删除或根据日常查询需要增加其他列组成联合索引以增加其选择性

• 体量过大的索引。这里体量大指占用空间大或者无用空间大的索引，例如把文本类的列作为索引（非全文索引），一页数据页按16K算，更大的占用空间代表一个页存储的记录量很少，从而造成更多的IO；再例如对于一个区域表，里面存储着用户的身份证，在给身份证建索引的时候会发现所有记录前几位基本都是一样的（省市区），这意味着这前几位基本没有区分度可言，没有节约时间反而浪费空间。对于这些情况可以考虑使用前缀索引CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));（身份证之类的可以反转再取前缀），去除无用信息减少索引体量

• 使用率不高的索引。使用率的问题只能结合业务来看，是不是能命中或者场景覆盖得全。

三星索引

索引在建立的时候可以参考三星索引的原则：

• 一星索引，根据where后面等值的条件，或者范围的条件来构建索引。一星索引的核心就是利用索引来尽可能的过滤不必要的数据，减少数据处理的规模，选择性高的列放前面。

• 二星索引，利用索引的有序性，消除ordby或者group by等需要排序的操作。避免排序消耗CPU资源，以及因为sort buffer不够而内外存导致换进换出的情况。

• 三星索引，索引的叶子节点就能够读到查询SQL所需要的所有信息，而不需要回原表去查询了。这样能大概率的避免，至少是减少物理IO。

往往现实中复杂的查询情况难以做到三星索引，这就可以根据筛选能力决定选择哪几个星了。

JSON虚拟列索引

MySQL 5.7后提供生成列（Generated Columns）功能，增加一个可以由其他列计算而成的列（比如加减价算、JSON解析等）。当遇到一些列的内容是JSON格式或者其他需要解析的列不好做索引，可以用生成列的虚拟列（Virtual Columns）功能，它会把规则保存在元数据里而不产生新数据，每次读取的时候根据规则计算，然后再给虚拟列加索引就可以加快Json格式列的查询速度。

xxxxxxxxxx
CREATE TABLE `sigma_label` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `gmt_create` datetime NOT NULL COMMENT '创建时间',
  `gmt_modified` datetime NOT NULL COMMENT '修改时间',
  `owner_type` varchar(16) NOT NULL COMMENT '主体类型',
  `owner_id` varchar(16) NOT NULL COMMENT '主体id',
  `owner_name` varchar(128) NOT NULL COMMENT '主体名称',
  `dimension` json NOT NULL COMMENT '查询维度，当前是四元组：应用分组、机房、单元、环境',
  `label_key_id` bigint(20) unsigned NOT NULL COMMENT '标签名id',
  `label_value` longtext NOT NULL COMMENT '标签值',
  `creator` varchar(32) NOT NULL COMMENT '提交方（调用系统）',
  `modifier` varchar(32) NOT NULL COMMENT '修改方（调用系统）',
  `emp_id` varchar(20) NOT NULL COMMENT '员工工号',
  `remark` varchar(255) DEFAULT NULL COMMENT '备注',
  `is_deleted` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '已删除',
  `group_name` varchar(255) GENERATED ALWAYS AS (json_extract(`dimension`,'$.groupName')) VIRTUAL COMMENT '分组名',
  `idc` varchar(255) GENERATED ALWAYS AS (json_extract(`dimension`,'$.idc')) VIRTUAL COMMENT '机房',
  `unit` varchar(255) GENERATED ALWAYS AS (json_extract(`dimension`,'$.unit')) VIRTUAL COMMENT '单元',
  `env` varchar(255) GENERATED ALWAYS AS (json_extract(`dimension`,'$.env')) VIRTUAL COMMENT '环境',
  `sign` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '用于建唯一索引',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_owner_params` (`owner_id`,`owner_type`,`idc`(16),`unit`(32),`group_name`(64),`env`(16),`label_key_id`,`is_deleted`,`sign`),
  KEY `idx_ownername_isdeleted` (`owner_name`,`is_deleted`) COMMENT 'create by cloudDBA-86fe0e7c-f070-4d51-8b6a-1d5c6c532573',
  KEY `idx_gmt_modified` (`gmt_modified`)
) ENGINE=InnoDB AUTO_INCREMENT=1354979 DEFAULT CHARSET=utf8mb4 COMMENT='sigma标签信息'

在线转离线

一些历史的数据可能查询频率不高，可以定期将线上数据库的历史数据转移到一些离线的数据库产品，比如ODPS、Lindorm。例如保障每天定时会将6个月前的历史数据转到Lindorm，如果在线库搜索不到则可以切换参数查询Lindorm。

分库分表

如果加了索引且达到了最优效果，由于数据量过大还是会存在性能问题，这时候可以考虑分表、分库分表。水平拆分一般以买家id作为分表键数据散列相对均匀，比如订单表、保单库，再通过TDDL中间件进行路由。数据库的水平拆分让容量不再成为瓶颈，但同时在部分性能方面作出了牺牲（分布式事务， 跨库关联等）。非万不得已，不要动这个念头……

其他存储

如果存储没有对事务有很大的依赖或者有文本检索需求又或者存在没法解决的热点用户，可以考虑将MySQL替换为OpenSearch（或者Tair）一类的存储以提升查询性能和多样性。

以Opensearch为例

优势：高扩展不需要分库分表天然支持分布式、数据量大压缩能力好、数据结构灵活

劣势：不支持事务、没有外键之类的数据约束，成本高

为数据关系约束考虑，存储最好还是以MySQL等关系型数据库为主要存储，Tair或OpenSearch等作为辅助存储，将MySQL数据同步到辅助存储以满足高效查询、文本检索等能力。

参考

• MySQL体系构架、存储引擎和索引结构，链接

• MySQL基础（连接池, SQL接口, 查询解析器, 查询优化器, 存储引擎接口, 执行器），链接

• 一文搞懂MySQL索引（清晰明了），链接

• 为什么Mysql用B+树做索引而不用B-树或红黑树，链接

• 二叉树，链接

• Data Structure Visualizations，链接