【每日八股】MySQL篇（四）：索引（下）

使用索引会有哪些缺陷？

用一句话概括：索引会增加存储开销、降低写性能、提升维护成本，并可能引发查询优化器误判以及无效索引问题。

存储开销增加

• 实际上建立一个索引也就是建立了一张表，每个索引也需要独立的存储空间（B+ 树结构）；
• 大表索引可能占用与数据表相当的存储量，比如 10 GB 的数据表可能需要 8 GB 的索引空间；

写操作性能损耗

• INSERT/UPDATE/DELETE 在修改数据表中内容的同时，需要同时维护索引结构；
• 事务提交时要保证数据与索引的一致性；

优化器决策成本

• 多索引时查询计划分析时间增加；
• 可能产生次优执行计划（因为索引误选）；

维护复杂性提升

• 版本升级带来的索引兼容性风险；

特殊场景限制

• 前导通配符查询无法使用索引；
• 低区分度字段索引效率差；

资源竞争

• 高频写入时索引页锁争用。

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

需要创建索引的典型场景

1. WHERE 子句高频过滤列（如用户 ID、订单日期）；
2. JOIN 操作常用连接字段（外键列）；
3. 频繁排序/分组的列（如按创建时间排序）；
4. 组合查询场景（创建复合索引）；
5. 数据量超过 10 万行且查询性能下降时；
6. 需要强制唯一性约束的列（唯一索引）；

不需要创建索引的情况

1. 数据量小的列（小于 1 万行）；
2. 写操作频率远高于读操作的表；
3. 低选择性列（如性别、标志状态等，以性别为例，假设性别只有男/女作为值，且两种值在表中的概率分布接近 50%，那么此时为性别这一列建立索引对提升查表性能没有帮助）；
4. TEXT/BLOB 大字段（应使用前缀索引）；
5. 频繁批量更新的列；
6. 已有更优复合索引前缀的列（避免冗余）；

拓展：在建表时，是否必须设置主键？主键就是唯一索引吗？数据表一定要有主键或唯一索引吗？

数据库不强制要求主键或唯一索引，但为了数据完整性、查询性能和可维护性，建议每个表都应显式定义主键，关键业务字段添加唯一索引。

一. 是否必须设置主键？

不一定，但是强烈建议设置主键。在技术层面上，MySQL 的 InnoDB 引擎会为没有主键的表自动生成隐式的ROW_ID，作为聚簇索引。其它关系型数据库无此机制，但允许无主键表的存在。显式主键（如自增ID）比隐式ROW_ID更加可控，可避免潜在的性能问题。

二. 主键与唯一索引的关系？

主键是特殊的唯一索引，但唯一索引未必是主键，其核心区别如下：

索引的优化（使用索引的注意事项）？

like 语句的前导模糊查询不能使用索引

select * from doc where title like '%XX';   --不能使用索引
select * from doc where title like 'XX%';   --非前导模糊查询，可以使用索引

union、in、or 都能够命中索引，建议使用 in

因为 in 的综合效率最高。

负向条件查询不能使用索引

负向条件包括：!=、<>、not in、not exists、not like等，优化案例：

select * from doc where status != 1 and status != 2;  --优化前
select * from doc where status in (0,3,4);            --优化后

联合索引的最左前缀原则

如果在(a,b,c)三个字段上建立联合索引，那么他会自动建立 a | (a,b) | (a,b,c) 组索引。

• 建立联合索引的时候，区分度最高的字段在最左边；
• 存在非等号和等号混合判断条件时，在建立索引时，把等号条件的列前置，因为非等号的范围判断条件将直接使联合查询中其右侧的条件失效（以非等号判断条件作为起始条件的联合查询是非法的）；
• 最左前缀查询时， SQL 语句的 where 顺序未必要和联合索引一致；

不能使用索引中范围条件右边的列(范围列可以用到索引)，范围列之后列的索引全失效

• 范围条件有：<、<=、>、>=、between等；
• 索引最多用于一个范围列，如果查询条件中有两个范围列则无法全用到索引；

不要在索引列上面做任何操作（计算、函数），否则会导致索引失效而转向全表扫描

select * from doc where YEAR(create_time) <= '2016'; --优化前
select * from doc where create_time <= '2016-01-01'; --优化后

强制类型转换会全表扫描

字符串类型不加单引号会导致索引失效。

例子：
加入phone字段是varchar类型，则下述的 SQL 不能命中索引：

select * from user where phone=13800001234;

优化后：

select * from user where phone='13800001234';

更新十分频繁、数据区分度不高的列不宜建立索引

• 更新会变更 B+ 树，更新频繁的字段建立索引会大大降低数据库性能；
• "性别"这种区分度不大的属性，建立索引是没有什么意义的，不能有效过滤数据，性能与全表扫描类似；
• 一般区分度在80%以上的时候就可以建立索引，区分度可以使用 count(distinct(列名))/count(*) 来计算；

利用覆盖索引来进行查询操作，避免回表，减少select * 的使用

• 覆盖索引：查询的列和所建立的索引的列个数相同，字段相同；
• 被查询的列，数据能从索引中取得，而不用通过行定位符 row-locator 再到 row 上获取，即“被查询列要被所建的索引覆盖”，这能够加速查询速度；

索引不会包含有NULL值的列，IS NULL，IS NOT NULL无法使用索引

• 只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以在数据库设计时，尽量使用NOT NULL约束以及默认值

如果有 order by、group by 的场景，利用索引的有序性

order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现 file_sort 的情况，影响查询性能。

使用短索引（前缀索引）

• 对列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个 CHAR(255) 的列，如果该列在前 10 个或 20 个字符内，可以做到既使得前缀索引的区分度接近全列索引，那么就不要对整个列进行索引。因为短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作，减少索引文件的维护开销。可以使用 count(distinct leftIndex(列名, 索引长度))/count(*) 来计算前缀索引的区分度；
• 短索引的缺点是不能用于 ORDER BY 和 GROUP BY 操作，也不能用于覆盖索引；
• 很多时候没必要对全字段建立索引，根据实际文本区分度决定索引长度即可。

利用延迟关联或者子查询优化超多分页场景

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

如果明确知道只有一条结果返回，limit 1 能够提高效率

如：

select * from user where login_name=?;

可以优化为：

select * from user where login_name=? limit 1

超过三个表最好不要 join

• 需要 join 的字段，数据类型必须一致，多表关联查询时，保证被关联的字段需要有索引；
• 例如：left join 是由左边决定的，左边的数据一定都有，所以右边是我们的关键点，建立索引要建右边的。当然如果索引在左边，可以用 right join。

单表索引建议控制在 5 个以内

单表索引建议控制在 5 个以内。

SQL 性能优化 explain 中的 type：至少要达到 range 级别，要求是 ref 级别，如果可以是 consts 最好

• consts：单表中最多只有一个匹配行（主键或者唯一索引），在优化阶段即可读取到数据。
• ref：使用普通的索引（Normal Index）。
• range：对索引进行范围检索。
• 当 type=index 时，索引物理文件全扫，速度非常慢。

业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引

不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的。

WHERE语句索引使用的注意事项？

• where 子句使用的所有字段，都必须建立索引；
• 如果数据量太少，MySQL 制定执行计划时发现全表扫描比索引查找更快，会不使用索引。

索引什么时候会失效？

• 查询条件中带有or，除非所有的查询条件都建有索引；
• like查询以%开头，索引会失效；
• 如果列类型是字符串，那在查询条件中需要将数据用引号引用起来（使数据类型匹配），否则索引失效；
• 索引列上使用函数或参与计算，索引失效；
• 违背最左匹配原则，索引失效；
• 如果MySQL估计全表扫描要比使用索引要快，索引失效。