详细了解索引规约

索引规约

在大厂中数据量非常庞大，也有很多高并发场景，因此在大厂中使用索引规约主要是为了规范索引的创建、使用及管理，确保数据库性能的高效与稳定，避免因随意或不合理创建索引带来诸如占用过多存储资源、影响数据更新效率等问题，同时让开发团队成员遵循统一标准，便于协作与维护，提升整体业务系统对海量数据处理的效率。

尽量单表索引查找或操作数据（速度特快），更多的操作放在web应用中处理，数据库的扩容，操作麻烦，性能低

\1. 【强制】业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。

说明：不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的；另外，即使在应用层做了非常完善的校验控制，只要没有唯一索引，根据墨菲定律，必然有脏数据产生

提高数据准确性：在应用层虽然可以做完善的校验控制，但由于各种复杂情况（如并发操作、代码逻辑漏洞、人为失误等），很难完全杜绝脏数据的产生。而唯一索引能够从数据库层面保证具有唯一特性的字段不会出现重复值，确保数据的完整性和准确性，符合业务规则要求。
提升查找速度：数据库在执行查询操作时，对于设置了唯一索引的字段，能够利用索引结构快速定位到目标记录。例如，在用户表中用户名是唯一的，通过为用户名建立唯一索引，当根据用户名查找用户信息时（如 SELECT * FROM users WHERE username = '张三'），数据库可以直接通过索引快速找到对应的用户记录，而无需进行全表扫描，大大提高了查询效率，尤其在数据量较大的表中，这种查询速度的提升效果更为显著。
可忽略的插入速度损耗：尽管在插入数据时，数据库需要额外检查唯一索引以确保插入的值不与已有值重复，这在一定程度上会增加插入操作的时间开销，但实际上这个损耗相对较小，相比于其带来的查询性能提升以及数据准确性保障，是可以接受的权衡。

\2. 【强制】超过三个表禁止 join。需要 join 的字段，数据类型保持绝对一致；多表关联查询时，保证被关联的字段需要有索引。

说明：即使双表 join 也要注意表索引、SQL 性能。

控制查询复杂度与性能风险：当连接的表数量过多时，SQL 查询的逻辑会变得极为复杂，数据库在执行查询计划、进行表连接操作以及数据匹配时，需要消耗大量的系统资源，容易导致性能急剧下降。限制 join 表的数量在三个以内，有助于保持查询语句的可维护性和执行性能，避免出现因复杂的表连接导致的长时间查询等待情况，保障系统的响应速度。
确保数据匹配准确性：要求 join 字段的数据类型保持绝对一致，可以避免因数据类型隐式转换带来的问题。如果数据类型不一致，数据库在进行连接操作时可能需要进行额外的转换操作，这不仅可能导致索引失效，还可能出现数据匹配错误的情况，保证数据类型一致能使表连接按照预期准确执行，得到正确的查询结果。
提升连接查询效率：为被关联的字段建立索引，能够让数据库在执行多表关联查询时，快速定位到符合连接条件的数据行，减少全表扫描或者大量数据遍历的情况，显著提高查询性能。例如，在进行订单表、用户表、商品表的关联查询时，为订单表中的用户 ID 字段（关联用户表）和商品 ID 字段（关联商品表）分别建立索引，数据库就能基于这些索引快速找到相关记录进行连接操作，加快查询速度。

\3. 【强制】在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度。

说明：索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为 20 的索引，区分度会高达 90%以上，可以使用 count(distinct left(列名, 索引长度))/count(*)的区分度来确定。

节省磁盘空间：不指定索引长度对全 varchar 字段建立索引会占用较多的磁盘空间，因为索引本身需要存储相应的数据副本用于快速查找。而根据实际文本区分度合理指定索引长度，只对字段中具有较高区分度的部分建立索引，可以大大减少索引所占用的磁盘存储空间，提高磁盘利用率，尤其在处理大量文本数据的表中，这种空间节省效果更为明显。
优化查询性能：索引长度并非越长越好，过长的索引可能导致查询效率下降，因为在索引查找过程中需要比较的数据量增多。通过分析文本区分度确定合适的索引长度，能够聚焦于最能区分不同记录的部分字段内容，在保证较高查询命中率的同时，加快索引查找速度，让数据库更快地定位到目标记录，提升查询性能。例如，对于一个文章标题字段（varchar 类型），若经过分析发现前 20 个字符的区分度能达到 90% 以上，那么只对这 20 个字符建立索引，既能满足大部分查询需求，又能提高查询效率。

\4. 【强制】页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。

说明：索引文件具有 B-Tree 的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。

充分利用索引特性：数据库中的索引文件大多具有 B-Tree 的最左前缀匹配特性，这意味着只有当查询条件从索引字段的最左边开始确定时，才能有效地利用索引进行快速查找。左模糊或全模糊查询（如 WHERE column LIKE '%abc' 或 WHERE column LIKE '%abc%'）不符合最左前缀匹配原则，会导致数据库无法利用索引，只能进行全表扫描，查询性能极差。遵守此规约可以引导开发人员采用符合索引特性的查询方式，保障查询效率。
借助专业搜索引擎优势：专业的搜索引擎（如 Elasticsearch 等）专门针对文本的模糊搜索进行了优化，具备强大的全文检索功能、倒排索引等技术，能够高效地处理各种模糊搜索需求。当页面搜索有模糊搜索要求时，将其转移到专业搜索引擎上处理，可以在满足业务需求的同时，避免对数据库查询性能造成严重影响，提升用户的搜索体验。

\5. 【推荐】如果有 order by 的场景，请注意利用索引的有序性。order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现 file_sort 的情况，影响查询性能。

正例：where a=? and b=? order by c; 索引：a_b_c

反例：索引如果存在范围查询，那么索引有序性无法利用，如：WHERE a>10 ORDER BY b; 索引 a_b 无法排序。

避免额外排序操作：数据库在执行查询并涉及 ORDER BY 操作时，如果能够按照索引的有序性来获取数据，就可以直接利用索引已经排好序的特点，无需再进行额外的排序操作（即避免出现 file_sort 情况）。例如，对于查询语句 WHERE a=? and b=? ORDER BY c，如果建立了 a_b_c 这样的组合索引，数据库可以基于该索引按照 c 字段的顺序直接获取有序的数据，大大提高查询效率，减少查询执行时间。
提升查询性能：当出现 file_sort 时，数据库需要在内存或磁盘临时空间中对查询结果进行排序，这会消耗大量的系统资源，尤其是在处理大量数据时，会使查询变得非常缓慢。遵循此规约，合理构建和利用索引，能让数据库利用索引自身的有序性来满足排序需求，优化查询性能，提升系统的响应速度，改善用户体验

\6. 【推荐】利用覆盖索引来进行查询操作，避免回表。

说明：如果一本书需要知道第 11 章是什么标题，会翻开第 11 章对应的那一页吗？目录浏览一下就好，这个目录就是起到覆盖索引的作用。

正例：能够建立索引的种类分为主键索引、唯一索引、普通索引三种，而覆盖索引只是一种查询的一种效果，用 explain 的结果，extra 列会出现：using index。

减少数据读取量：覆盖索引是指查询所需要的数据列都包含在索引中，数据库在执行查询时只需通过索引就能获取到全部所需信息，无需再根据索引中的指针 “回表” 去查找对应的完整数据行。这相当于只查看 “目录”（索引）就能得到想要的答案，避免了额外的数据读取操作，减少了磁盘 I/O 次数以及数据传输量，尤其在查询结果集较大或者表数据量庞大的情况下，能显著提升查询效率。
提高查询速度：由于不需要回表操作，查询可以更快速地执行完成，缩短了查询的响应时间。例如，在一个包含用户信息（如用户名、年龄、地址等多个字段）的表中，如果经常只查询用户名和年龄这两个字段，为这两个字段建立覆盖索引后，当执行相应的查询语句时，数据库直接从索引中获取数据，无需再去查找完整的用户记录，查询速度会明显加快，提升了系统的整体性能。

\7. 【推荐】利用延迟关联或者子查询优化超多分页场景。

说明：MySQL 并不是跳过 offset 行，而是取 offset+N 行，然后返回放弃前 offset 行，返回 N 行，那当offset 特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行 SQL改写。

正例：先快速定位需要获取的 id 段，然后再关联：

SELECT t1.* FROM 表 1 as t1, (select id from 表 1 where 条件 LIMIT 100000,20 ) as t2 where t1.id=t2.id

提高分页查询效率：在传统的分页查询方式中，当偏移量（offset）特别大时，数据库需要先获取从起始位置（offset 行）开始的大量数据（offset + N 行），然后再舍弃前面的 offset 行，只返回后面的 N 行，这个过程会消耗大量的时间和系统资源，查询效率极低。而采用延迟关联或子查询的方式，先通过条件快速定位到需要获取的 ID 段（例如先查询出符合条件的部分 ID），再通过关联操作获取对应的数据行，避免了大量无用数据的读取和处理，能够显著提高超多分页场景下的查询效率，缩短查询响应时间。
优化用户体验：在一些需要展示大量分页数据的应用场景（如电商平台的商品列表、论坛的帖子列表等）中，快速的分页查询响应能够让用户更流畅地浏览不同页面的内容，避免长时间等待页面加载，提升用户体验，增强应用的可用性和竞争力。

\8. 【推荐】SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，如果可以是 consts最好。

说明：

1） consts 单表中最多只有一个匹配行（主键或者唯一索引），在优化阶段即可读取到数据。

2） ref 指的是使用普通的索引（normal index）。

3） range 对索引进行范围检索。

反例：explain 表的结果，type=index，索引物理文件全扫描，速度非常慢，这个 index 级别比较 range还低，与全表扫描是小巫见大巫。

不同级别性能优势递增：
- consts 级别：表示在单表中最多只有一个匹配行（通常基于主键或者唯一索引），数据库在优化阶段就能直接读取到数据，查询速度极快，几乎不需要额外的查找操作，是性能最优的情况。例如，通过主键查询一条特定的用户记录（SELECT * FROM users WHERE user_id = 123，假设 user_id 是主键），就能达到 consts 级别，能瞬间获取到结果。
- ref 级别：使用普通的索引（normal index）进行查询，数据库可以根据索引快速定位到多条符合条件的数据行，相比于全表扫描或者低效率的索引使用方式，查询效率有明显提升。例如，在用户表中根据用户名（已建立普通索引）查询多个同名用户的记录（SELECT * FROM users WHERE username = '张三'），能利用索引达到 ref 级别，快速找到相关用户信息。
- range 级别：对索引进行范围检索，虽然相对前两者效率稍低一些，但也比全表扫描或者低效的索引物理文件全扫描（type = index）要好得多。比如，查询年龄在某个区间内的用户记录（SELECT * FROM users WHERE age BETWEEN 20 AND 30，假设 age 有索引），可以通过索引进行范围查找，缩小查询范围，提高查询速度。
整体性能提升目标：明确这些性能优化目标级别，有助于开发人员在编写 SQL 查询语句和设计索引时，朝着更高效的方向努力，通过合理的索引构建和查询优化，尽可能提升查询性能，使数据库系统在处理各种业务查询时能够快速响应，满足用户的使用需求，提升系统的整体性能和效率。

\9. 【推荐】建组合索引的时候，区分度最高的在最左边。

正例：如果 where a=? and b=?，a 列的几乎接近于唯一值，那么只需要单建 idx_a 索引即可。

说明：存在非等号和等号混合判断条件时，在建索引时，请把等号条件的列前置。如：where c>? and d=?

那么即使 c 的区分度更高，也必须把 d 放在索引的最前列，即建立组合索引 idx_d_c。

提高索引筛选效率：将区分度最高的字段放在组合索引的最左边，能够在查询时首先基于这个字段快速缩小查找范围。因为区分度高意味着该字段不同值的数量占总记录数的比例大，能更精准地区分不同的数据行，数据库通过这个字段可以快速过滤掉大量不符合条件的数据，后续再结合组合索引中的其他字段进一步筛选，提高整个查询的效率。例如，在订单表中，如果 customer_id 字段的区分度很高，当执行查询语句 WHERE customer_id =? and order_date =? 时，把 customer_id 放在组合索引的最左边（如建立 idx_customer_id_order_date 索引），数据库就能先依据 customer_id 的值快速定位到一部分订单记录，然后再根据 order_date 进一步精确查找，加快查询速度。
适应复杂查询条件优化：在存在非等号（如 >、<、BETWEEN 等范围条件）和等号混合判断条件时，把等号条件的列前置建立组合索引，是为了更好地利用索引特性。由于数据库在使用索引进行查找时，对于等号条件能够更有效地定位数据，先基于等号条件缩小范围，再处理范围条件，这样可以使索引发挥更大的作用，避免因范围条件在前导致索引无法充分利用的情况，提升复杂查询条件下的查询性能。例如，对于查询语句 WHERE c >? and d =?，建立 idx_d_c 这样的组合索引，能先通过 d 的值确定一个较小的数据集，再在这个基础上根据 c 的范围条件进行筛选，优化查询操作。

10.【推荐】防止因字段类型不同造成的隐式转换，导致索引失效。

确保索引正常使用：数据库在执行查询时，如果涉及到字段类型不一致，可能会自动进行隐式转换来使查询条件能够匹配数据。然而，这种隐式转换往往会导致索引失效，使得数据库无法利用原本建立好的索引进行快速查找，只能进行全表扫描，严重影响查询性能。例如，在一个表中 age 字段定义为 INT 类型，而查询语句写成 WHERE age = '20'（这里传入了字符串类型的值），数据库可能会进行隐式转换，但这样就不能使用 age 字段上的索引了。通过保证字段类型一致，能避免这种情况发生，确保索引在查询中正常发挥作用，维持查询的高效性。
获得准确查询结果：除了性能问题，隐式转换还可能引发数据匹配错误，导致查询结果不符合预期。保证字段类型相同，能让查询按照正确的逻辑进行数据匹配，得到准确的查询结果，符合业务需求，避免因数据类型问题带来的业务逻辑错误和数据不一致情况。

11.【参考】创建索引时避免有如下极端误解：

1）索引宁滥勿缺。认为一个查询就需要建一个索引。

2）吝啬索引的创建。认为索引会消耗空间、严重拖慢记录的更新以及行的新增速度。

3）抵制惟一索引。认为惟一索引一律需要在应用层通过“先查后插”方式解决。

避免 “索引宁滥勿缺” 误解：
- 节省资源：如果认为一个查询就需要建一个索引，会导致数据库中索引数量过多。过多的索引不仅会占用大量的磁盘空间，还会在数据插入、更新和删除操作时，增加数据库维护索引结构的开销，使这些操作变得缓慢，影响系统的整体性能。例如，在一个频繁更新数据的表中创建了大量不必要的索引，每次数据更新时，数据库都要对众多索引进行相应的调整，会严重拖慢系统的响应速度，浪费系统资源。
- 提升可维护性：合理控制索引数量，只针对确实有高频查询需求的字段创建索引，能使数据库的索引体系更加简洁、清晰，便于后续的管理、维护以及性能优化工作。开发人员和运维人员在查看和分析数据库结构时，能够更容易理解每个索引的作用和必要性，避免因索引过多造成的混乱和潜在的问题。
避免 “吝啬索引的创建” 误解：
- 优化查询性能：虽然索引会消耗一定的磁盘空间且在数据更新时需要额外维护，但合理创建索引对于提升查询性能的作用是不可忽视的。对于经常用于查询条件的字段，创建索引能够大大加快查询速度，减少用户等待时间，提升用户体验。例如，在一个电商系统中，经常根据商品名称、分类等字段进行商品查询，如果因为担心影响更新速度而不创建相应索引，那么商品查询操作可能会变得非常缓慢，影响整个系统的使用效率。
- 平衡性能考量：正确的做法是权衡查询频率和数据变更频率，在对查询性能有较大提升需求且数据变更不会因索引带来严重性能问题的情况下，合理创建索引，实现查询性能优化和数据操作性能之间的平衡，保障数据库系统在不同业务场景下都能高效运行。
避免 “抵制惟一索引” 误解：
- 保证数据准确性：认为唯一索引一律需要在应用层通过 “先查后插” 方式解决是不合理的。在应用层进行校验虽然可以起到一定的控制作用，但很难完全避免并发等情况下的重复数据插入问题。而唯一索引能够从数据库底层保障具有唯一特性的数据不会重复，确保数据的完整性和准确性，避免脏数据的产生，这是应用层校验无法完全替代的。
- 提升查询效率：唯一索引在查询操作中同样能发挥快速定位数据的优势，尤其是在根据唯一标识字段查找特定记录时，能够利用索引结构迅速找到目标记录，提高查询速度，优化数据库的整体性能，与通过应用层复杂的 “先查后插” 方式相比，效率更高且更可靠。