4.建索引的注意事项

字段有唯一性限制的，比如商品编码；
经常用于 WHERE 查询条件的字段，这样能够提高整个表的查询速度，如果查询条件不是一个字段，可以建立联合索引。
经常用于 GROUP BY 和 ORDER BY 的字段，这样在查询的时候就不需要再去做一次排序了，因为我们都已经知道了建立索引之后在 B+Tree 中的记录都是排序好的。

什么时候不需要创建索引？

WHERE 条件，GROUP BY，ORDER BY 里用不到的字段，索引的价值是快速定位，如果起不到定位的字段通常是不需要创建索引的，因为索引是会占用物理空间的。
字段中存在大量重复数据，不需要创建索引，比如性别字段，只有男女，如果数据库表中，男女的记录分布均匀，那么无论搜索哪个值都可能得到一半的数据。在这些情况下，还不如不要索引，因为 MySQL 还有一个查询优化器，查询优化器发现某个值出现在表的数据行中的百分比很高的时候，它一般会忽略索引，进行全表扫描。
表数据太少的时候，不需要创建索引；
经常更新的字段不用创建索引，比如不要对电商项目的用户余额建立索引，因为索引字段频繁修改，由于要维护 B+Tree的有序性，那么就需要频繁的重建索引，这个过程是会影响数据库性能的。

拓展:MySQL 优化这个问题要怎么回答？

引言

MySQL 优化我觉得主要从三个层面去说，分别是软件层面、硬件层面以及架构层面。

软件层面

首先最直接的、优化效果的就是软件层面，所谓软件层面，就是指 MySQL 自身的优化。

MySQL 升级

最简单的方式就是 MySQL 的升级，从 5.7 到 8.0 ，按照 MySQL 官方的描述，8.0 的性能是 5.7 的 2 倍，这个我当时看了官方文档，在 MySQL 8.0 官方针对 InndDB 存储引擎进行了优化，主要是索引方面，MySQL 8 已经支持对索引进行拆分了，简单来说就是一个大索引可以拆分成多个小索引，这个和 JDK 1.7 版本 CurrentHashMap 1.7 里面分段锁的思想是一致的，其主要目的就是通过将一个大的方式进行分段，插入的时候则分段插入，最大程度的减少锁的冲突，因为在我们插入或者修改数据的时候，其实会对索引进行一个更新操作，拆分完索引之后可以从一定程度上减少锁的粒度。这个主要是官方的说法，但是我也没有进行详细的尝试，当时我在优化的时候，主要就是针对 MySQL 的版本进行了一个升级，所以我觉得对于这个说法还是要以实际压测的数据为主。

建立合适索引

第二个关键的点就是建立合适的索引，因为我们知道，建立索引太少的话，可能效果并不是很明显，建立索引太多了，在维护的时候有需要一定的成本，而且不一定有用，所以从一定程度上来说，索引的好坏决定了查询的快慢。MySQL 的索引底层用的是 B+ 树，平衡二叉树的升级版，并且按照 MySQL 默认页大小 16k，我们假设单条数据 1k 的情况，那么三层的 B+ 树就可以存储 2000W 的数据，然后我们一般情况下 B+ 树的层数维持在 3-4 层左右，也就是说如果我们使用了索引，最多 3-4 次 IO 就可以找到对应的数据。不过索引多了，缺点也明显，因为我们索引多了，我们再插入的时候如果更新了索引字段也同时会去更新索引插入的性能也就不高。

因此，我们在建立索引的时候，可以针对一些经常查询、排序、分组的字段去建立对应的索引，然后在建立索引的时候，可以针对一些区分度比较高的字段去建立索引，比如一张个人信息表，就可以针对身份证号码、手机号这种标识度比较高的字段去建立索引，然后我们当时由于日志这一块，日志记录的 ID 基本是唯一的，所以当时为了加快查询，加上后面分库分表，就直接将日志记录的 ID 作为主键索引，然后就是我们在查询的时候，有时候会针对日志的级别是进行对应的分组，我们也有建立对应的索引，排序这块我们日志优化的时候主要是针对时间段的，然后基本插入都是递增的形式，所以当时就没有针对这块去做对应的优化。另外一个点，就是我们可以针对一些比较长的字段去建立前缀索引，因为索引字段长度过长的话，索引占据的空间就越大，IO 的效率也就越低，比如我之前做过的一个需求，就是针对 UUID 去建立对应的前缀索引，然后当时日志系统优化的时候，其实我们有想过针对日志中的服务来源去做前缀索引，不过后面想着这个没有多大的必要，就没有去实现，而针对 UUID 去建立前缀索引的原因其实很简单，因为 UUID 其实类似于 Git 的版本号，前面几个字符就可以识别一条记录，然后具体取多少位的前缀，这里我记得当时是有一个索引选择性的点，只要保证选择的位数可以使得索引选择性为 1，或者接近为 1 差不多就可以了。

SQL 语句优化

说完索引建立之后，接下来就经典的数据库 SQL 语句优化了，SQL 语句之所以执行慢，目前我遇到的情况，主要有三个方面：

SQL 语句没有走索引，走全表扫描
回表次数太多，导致随机 IO 过多
单行数据量较大，导致系统磁盘 IO 太久

主要就是一个点，就是避免写一些全表扫描的 SQL，首先一个方面就是尽量使用字段去查询，不要使用 select * 去进行查询，另外一个方面就是尽可能地避免索引失效的情况。

索引失效情况

（1）首先最常见的就是模糊匹配，即 like 以 % 开头导致索引失效，针对这个情况，如果一定要进行前后的模糊查询匹配的话，可以存 2 个字段，即一个字段正向存储，一个字段方向存储，比如 abcd 和 cdba，或者用倒排索引还有全文检索的方式，然后这里有一个点，就是虽然 MySQL 现在可以全文检索了，但是有个缺点，就是会把最小两个字段作为倒排索引，这个还是比较致命的，所以全文检索的话还是推荐使用 ES 会好一点。

（2）第二个点就是我之前做过测试的，就是在我们 in 一些特别大的数据量的时候，in 可能会导致索引失效，当时我测试的应该是大概 20%~30% 左右的时候，就会导致索引失效了，然后如果没有失效的话，一般会走一个 range 的索引，性能还是可以的。

（3）第三个点就是在条件匹配，即条件查询也就是 where 的时候使用一些函数，比如使用不等于符号，匹配的时候范围查询，查询条件包含 or 并且字段列不包含索引，这个会直接导致索引失效。

（4）第四个点就是我之前经历过的一个关联查询的场景，即关联条件进行了隐式的类型转换，比如 A 表是 int 类型，B 表的关联字段是 varchar 类型，我以前做过的一个真实场景就是因为关联字段数据不一致导致的，当时有一张合同表和一张审批表，合同不一定有审批，所以合同表左关联，审批表去分页查询，这个时候就发现两边差不多只有 5w 条数据的时候，查询 10 条数据却要 6-7s，当时第一时间排查到就是这两张表对应的关联字段不一致，就合同表的 id 是 int 类型，但是审批表存储的那个确实 varchar，然后当时有一个问题，就是审批表还在被其他模块使用，所以没有办法去修改审批表的字段类型，这里我们针对审批表的 SQL 去进行了一个优化，因为我这个场景是左关联的，先把那 10 条数据去查询出来，然后把这个子查询作为临时表去关联审批表，这样就算是全表扫描，优化之后的查询时间就差不多 300ms 就可以查询出来，不过这里最好的优化方式还是去修改字段类型，然后这里当时接触的还有一个点，就是如果表的字符集，比如一张表是 utf8 ，另一张表是 utf8mb4 的时候也可能导致索引失效。

（5）最后一个点就是在我们使用一些 is null 的语句的时候，也会导致索引失效。

SQL 监控

只说 SQL 语句注意的点其实不太够，因为有时候你以为他是走了索引，但是却还是可能没走索引，比如 in 的这个情况，你很难去判断数据量，所以还是得使用一些 SQL 监控的工具来定位哪些 SQL 执行比较慢，有些公司会有 SQL 监控工具，那个除外，我这里说一下普遍用的，就 MySQL 自带的慢查询日志，首先我们可以通过在数据库中配置 show_query_log = 1 来打开慢查询日志，或者配置 log_queries_not_using_index = 1 来监控哪些 SQL 语句没有走索引，然后配置一下慢 SQL 的阈值 long_query_time，默认的时间是 10s，然后一般的话会配置 2~4s，超过这段时间就标记为慢 SQL。

然后我们拿到慢 SQL 之后，一般可以通过 explain 计划排查慢 SQL。查看执行计划，执行计划主要看 type，性能从好到差分别是 system>const>eq_reg_ref>index_merge>range>index>ALL，一般我们对于一条 SQL 语句的要求起码要达到 range 才可以。我们进行优化的时候，也是通过各种方式去优化 type 的级别。我记得当时我在拓尔思实习的时候，我们有一个出库的场景，就数据库里面存了一条条的存储明细，然后出库之后要把对应的某种商品出库的库存状态更新为出库，当我们做批量出库的时候，一下子出库了很多数据，大概 1W 条的时候，写的是一个 in 操作，就是查询这批出库的 ID，然后 update 状态 in select 这些 ID，走的这个索引类型是 range，大概 8s，后来我们集合我们数据自身的情况，结合这个自增主键，查询出 1w 条里面最大的那个值，然后直接更新比这个值小的数据就可以了，然后我 explain 了一下，走的是 index merge，就索引合并，优化之后大概耗时是 3 秒多，提升了很多。

MySQL 回表

回表次数太多，即我们查询的时候索引并没有走所有的字段，这个时候就会多了一些不必要的查询，即随机 IO，从而导致效率不高，这个主要和 MySQL 的索引有关，MySQL 的索引分为聚集索引以及非聚集索引，非聚集索引就是普通索引，如果命中了非聚集索引，就可能走一个二次回表的操作，你查询的数据越多，回表次数也就越多，所以针对这个情况，可以对一些比较固定查询的字段监控你联合索引，这样查询的时候就可以走索引覆盖，避免二次回表，这个也是一种优化方式。

数据库优化

数据库设计

这个合理的数据库设计包含索引、字段、表结构几个方面，字段设计的话最主要就是的就是主键最好选择单调递增的数字，主要有两个方面，一个方面是单调底层可以保证新插入的数据在最后面，避免索引重建，另外一个面就是数字主键查询的时候可以直接比较大小，比字符串的逐字比较性能会好很多。然后一些字段如果是空的可以设置空字符串或者 0，这样查询的时候也可以走索引，另外就是表结构的设计，表结构的设计要尽可能满足三大方式，但是如果完全按照第二范式和第三范式的话，我们在数据量大的时候，可能会增加很多的 join 操作，join 操作的底层就是一个 for 循环，多次 join 就代表多次嵌套，会导致性能比较差。所有有的时候我们会采取反范式设计，即适当地进行数据冗余，比如一张学生表、一张班级表，一张学生班级关系表，如果要查询哪些班有哪些学生，就必须要关联三张表。但是如果把学生名字、班级名称这些字段冗余到学生班级关系表里面，就可以不用多次 join，从而提高性能。

数据库的三大范式:

第一范式：原子性，每个字段的值不能再分。

第二范式：唯一性，表内每行数据必须描述同一业务属性的数据。

第三范式：独立性，表中每个非主键字段之间不能存在依赖性。

合理分表

慢 SQL 的一个重要原因就是单行查询的数据量过大，从而导致磁盘 IO 较大，针对这个的话，我们一般会去采取分表的策略，分表的话一般有垂直分表和水平分表两种方式，垂直分表其实就是为了减少单条数据的大小，从而让聚簇索引能够容纳更多的数据，减少单表数据的高度，这个的话和我想到了之前一个 BI 分析的项目，当时主要就是给提示词给大模型，然后大模型按照模板生成对应的 Echart 代码，然后一开始的时候就是 Echart 代码直接存储到 MySQL 的一个数据列中，后面想到的就是如果单行数据列过大可能导致整体查询效率过低，就用垂直分表去解决，后面的话但是我是继续拓展，使用 MongoDB 进行存储，另外一个方式就是水平分表，水平分表还有两种方式，一种是按照某一个字段进行哈希分表，分表数量是 2 的倍数，这种分表的分表原则就是针对表进行压测评估，看看多少容量级会有明显的延迟，一般是按照未来 3 年的数据量来预估要分多少张表。还有一种方式就是按照时间去进行拆分，形成归档表以及冷热表，这个的话我当时东华教育 CRM 以及框架项目分表、还有我实习公司当时活动后台的日志数据查询，都是按照这个逻辑，即按照时间线去进行分表，形成归档表以及冷热表。比如按照月份一个月一张表进行分，或者按照年去进行分表。实际工作中主要是这两种方式结合去做，比如我当时东华教育项目中用到的，首先我们订单表根据用户去进行分表，然后我们同时还观察到用户可能只关心最近的数据，所以我们把 3 个月以内的数据定义为热数据，3-6 个月的数据定义为温数据，把 6 个月以上的数据定义为冷数据，然后使用像 DataX、Kettle ETL 工具定期将数据同步到另外的一些表里面，比如饿了么就只能查询一年以内的数据。

配置参数

第五个就是配置合理的参数，这里的值有点多，我说一下几个比较重要的值，比如 buffer_pool，独立部署的话，缓冲池大小一般推荐是服务器物理内存的 50%-80%，还有一些其他的参数比如 join_buffer_size 是用于 MySQL 中 join 时分配的缓冲区大小，sort_buffer_size 是用于排序时候的缓存，如果太小的话 MySQL 可能会在硬盘上进行排序，从而导致性能不高。read_buffer_size 是控制单个 session 读取数据的缓冲池大小，增大也可以提高性能，不过这些参数的设置具体还是得根据实际压测的效果来给出。

软件层面大概就这么多了，然后接下来就是硬件层面的内容。

硬件层面

硬件层面的话一般是到了万不得已的时候采取的方式，因为 MySQL 其实没有我们想得那么脆弱，软件层面的优化成本是最低的，比如有限更换硬盘的类型，更换硬盘的成本其实是最低的，比如机械硬盘可以换成 SSD 方式存储，因为 SSD 的随机读写能力比机械硬盘要好一点，以前我利用实验室的电脑去压测过，如果是 insert 语句，插入一个大数据量的字符串，本地的 SSD 硬盘可以达到 1000 TPS，TPS 即每秒钟执行的数据量，我当时是直接插入，所以相当于每条 SQL 语句就是一次事务提交，然后机械硬盘会少一点，只有 600 多-700 的样子。

然后就是扩大硬件规格，因为 MySQL 是一个 IO 密集型的中间件，最推荐的就是加内存，因为内存越大，上面所说的哪些缓冲池参数可以设置更大，这样 MySQL 可以将更多的数据放到内存中去，提高的效率也就越多。

架构层面

然后最后就是架构层面，首先对于这个方面，我感觉可以部署 MySQL 一主一从集群，因为做了这个集群以后，理论上就可天然地将读的性能提高一倍差不多，不过了解到还有一主二从那些，不过那些主要还是看项目需求。或者可以采用分区表的方式，不过分区表理论上还是会收到单机性能来的影响，所以可以引入分布式数据库，分布式数据库当时我之前项目中冷热数据分离这块有用到过，当时主要是日志数据，我们将冷数据归档到 HBase 中，在结合中间表以及索引等机制提高冷数据查询效率，然后分布式数据库也顶不住之后，就可以考虑使用分库分表，不过一般是到了没有办法才使用的，因为分库分表需要考虑的点很多，比如全局 ID 选择、分库分表字段等，这些如果经验不是很够的话很难去进行选择，而且分库分表还会带来跨表查询、跨表事务等问题，然后针对分库分表方案，这里可以考虑引入 MyCat 或者 Sharding 等框架。

还有一个点忘记说了，就是你可以考虑加缓存，这可以理论上来说已经不算是数据库的优化了，但是可以减轻数据库的压力，即把一些查询频繁的，然后更新不怎么频繁的数据放到缓存中去，从而减少对于数据库的访问，然后缓存的话我们可以考虑使用多级缓存，比如 Java 里面的 Caffeine、guava、ehcache，Go 里面我之前用到的一个缓存驻留的库 singleflight，然后二级缓存比较常用的就是 redis、Memcached，以上就是我感觉比较可以的点了，以上就是我想到的关于数据库优化的大概方案，然后我之前还有过一个相关的优化案例，需要我说一下吗？

SQL 优化案例

SQL 优化案例补充：4s -> 500ms

当时遇到一个 SQL 语句，那个是一个外包的后台系统好像，然后当时我们实验室主要就是负责这个系统的一些运维，然后那个 SQL 一开始在生产环境查询的时候，全表差不多 22w 条数据，查询时间是用了 3.8s-4.5s 的样子，然后后面就让我们优化，本地测试的时候时间差不多就是 1.8s 左右，然后用的 MySQL 版本是 8.0，然后一开始我们对接的时候说这个时间还是可以的，后面那边说不太可以，因为这条 SQL 需要执行差不多两次，一次是查询结果，然后一次是 count 语句，算下来时间就差不多 7 秒左右，最后优化的结果是返回 3000 多条记录，然后执行时间差不多 500ms 左右，我记得当时最快的一次是达到了 300 ms.

执行 explain 执行计划，查看是否命中索引了，当时发现已经命中索引了，虽然有一些走的是合并索引，然后我这里简单说一下合并索引这个点，这个是 5.1 之后引入的技术，对同一个表可以使用多个索引分别进行条件扫描，然后将结果进行合并处理，然后最后进行回表查询。

这样做的好处就是可以同时利用两个索引去过滤掉一些 ID 值，从而节省一些回表操作，不过索引合并还是会产生一定的性能损耗，一般来说用联合索引会更加好一些。

然后看见索引都命中之后，接下来主要看 filtered 以及 rows 的条数，因为知道走索引了，接下来主要判断其走回表的次数，然后当时先去看一张 join 的 cfg 表，那个 join 的 cfg 表走了 5w 多条数据，由于 join 的主键是非聚簇索引，代表 join 的时候走了 5w 次回表，然后针对这个，我们当时是针对这个 cfg 表先建一个联合索引，然后尝试执行一下，马上效率就到 1.9s 了。
然后第二个优化的点还是一个 join 的子表，那个表的问题主要就是出在一个索引合并上，那个索引合并的表只走了前面的索引，即第一个索引，后面的索引并没有走，然后当时我们先查询了其中一个字段，发现只有 5000 多条数据走了其中一个索引，然后我们当时是根据条件查询的字段加上命中的字段建立一个联合索引，后面发现有点神奇，他走的还是一开始那个索引，并没有走我们后面建立的索引，不过他的时间下降了，这里当时我第一反应就是发生索引失效了，后面排查的时候发现主要是走的数据变少了，差不多只有 3000 多条，也就说回表的次数减少到了 3000 多，时间差不多就到 1.6~1.8 左右。
然后回到上面的索引失效问题，我发现我当时的 SQL 语句也没有导致索引失效的场景，比如在 where 子句上面去进行函数计算操作、模糊匹配、隐式转换也都没有，后面突然想到一件事，就是可能 MySQL 自己的查询优化器的缘故，就是我建立的联合索引效率不一定比我之前那个索引的效率高，所以只能继续优化
继续优化的时候我想着，既然我建立的索引回表次数少了，则说明可能是索引的排序出现问题，因为我只建立了对应的联合索引，原先的字段并没有改变，那我就以原先索引的字段作为第一个字段，然后将建立的联合索引字段排在这个原先索引前面就 ok 了，这样就不用走二次回表了，应该可以快一点，后面发现还是没有效果，他还是走了原先的索引，然后我说一下我建立的两个索引的区别，主要就是第一个字段，一个是以 type 作为第一个字段，一个是以 id 作为第一个字段，走 id 那个会扫描 3000 多条数据，走 type 差不多扫描就是 1000 多条数据
后面我的想法就是让优化器去强制走我们建立type 的索引，即在我们后面建立索引的基础上，去加一个 force idx,即强制索引，后面发现差不多就可以达到 500 ms 了

深分页优化

深度分页问题是指在数据库查询中，当你尝试访问通过分页查询返回的结果集的后面部分（即深层页码）时遇到的性能问题。

假设你有一个包含数百万条记录的表，你想通过分页的方式来展示这些数据。当用户请求第10000页数据时，假设pageSize为10，那么最终就是LIMIT 99990,10 ，数据库必须先扫描过前99990条记录，才能返回第10000页的数据，这会导致显著的性能下降。

99991是起始ID = (页数 - 1) * 每页项目数 + 1

对于第1页，起始ID将是1，结束ID将是10。对于第2页，起始ID将是11，结束ID将是20，以此类推。

对于第10000页：

起始ID = (10000 - 1) * 10 + 1 = 99991

使用子查询和 JOIN 优化

假如我们这样一条SQL：

SELECT c1, c2, cn...
FROM table 
WHERE name = "test" LIMIT 1000000,10

我们可以基于子查询进行优化，如以下SQL：

SELECT c1, c2, cn...
FROM table
INNER JOIN (
  SELECT id
  FROM table
  WHERE name = "test"
  ORDER BY id
  LIMIT 1000000, 10
) AS subquery ON table.id = subquery.id

首先，使用子查询获取限定条件下的一部分主键 id，这部分id对应于我们分页的目标区域，使用这些 id 在主查询中获取完整的行数据。

以上SQL，在name有索引的情况下，子查询中查询id是不需要回表的。而当我们查询出我们想要的10个ID之后，基于ID查询不仅快，而且要查的数据量也很少。

使用子查询和 ID 过滤优化

SELECT c1, c2, cn...
FROM table
WHERE name = "linqi"
  AND id >= (SELECT id FROM table WHERE name = "Hollis" ORDER BY id LIMIT 1000000, 1)
ORDER BY id
LIMIT 10

这个方法代替了join的方式，使用了一个子查询来获取从哪里开始分页的参考点，基于ID做范围查询。但是这个方案有个弊端，那就是要求ID一定要是自增的。

和上面的方案同理，他也可以减少回表的次数。