这篇文章不错，这里转载一下：https://juejin.cn/post/7545035321433079862

一、最左前缀原则：索引字段的顺序比数量更重要

        这是最基础也最容易踩的坑 —— 很多人知道复合索引能加速多条件查询，却搞不懂字段顺序的门道。

业务场景

        电商订单查询：where status=1 and create_time>'2024-01-01' and amount>100（订单状态为 “已支付”、创建时间在 2024 年后、金额超 100 元）。

错误案例

        建了索引(amount, status, create_time)，结果查询还是全表扫描。
        为什么错：查询条件里status是第一个过滤条件（=1，范围小），但索引里amount在最前面（范围大，amount>100 会匹配很多行），导致索引无法被有效利用。

正确做法

        复合索引字段顺序：从 “过滤性最强” 到 “过滤性最弱”，与查询条件顺序一致。
这里status=1（枚举值，过滤后只剩 1/5 数据）→create_time>（范围查询，进一步过滤）→amount>（最后过滤），所以索引应为：

-- 正确索引：按查询条件顺序+过滤强度排序
create index idx_status_create_amount on `order`(status, create_time, amount);

原理拆解

        复合索引像一本 “多级目录”：先按第一个字段排序，第一个字段相同再按第二个，以此类推。查询时，只有从最左字段开始匹配，才能用到索引。
        比如查status=1 and create_time>'2024-01-01'能用到索引，但查create_time>'2024-01-01' and amount>100（跳过 status）就会失效。

二、索引选择性原则：别给 “性别” 这种字段建索引

        有些字段天生不适合建索引，强行建了反而拖慢性能。

业务场景

        用户表查询：where gender=1（查所有男性用户）。

错误案例

        给gender字段建了单列索引，结果查询速度比全表扫描还慢。
        为什么错：性别只有 3 个可能值（男 / 女 / 未知），1000 万行的表，每个值平均 300 多万行。索引查询需要先定位索引页，再回表查数据，还不如直接扫全表快。

正确做法

        只给 “选择性高” 的字段建索引（选择性 = 不同值的数量 / 总记录数，越接近 1 越好）。

• 适合建索引：手机号（几乎唯一，选择性≈1）、订单号（唯一，选择性 = 1）；
• 不适合建索引：性别（选择性≈0.000003）、状态（如果状态值少）、逻辑删除字段（只有 0 和 1）。

实战代码

-- 不推荐：选择性太低的字段
create index idx_gender on user(gender); -- 别这么干！

-- 推荐：选择性高的字段
create index idx_phone on user(phone); -- 手机号几乎唯一
create index idx_order_no on `order`(order_no); -- 订单号唯一

三、索引失效原则：这些操作会让索引 “罢工”

        明明建了索引，查询却走全表扫描 ——90% 是因为触发了索引失效的操作。

常见失效场景及避坑代码

失效操作	错误 SQL 示例	正确 SQL 示例	原理
字段用函数 / 运算	where substr(phone,1,3)='138'	where phone like '138%'	索引按原始值排序，函数会破坏顺序
隐式类型转换	where phone=13800138000（phone 是 varchar）	where phone='13800138000'	转换后的值与索引值不匹配
!=/<>/not in	where status!=1	改用union拆分（status=0 union status=2）	范围过大，优化器放弃索引
like前导模糊查询	where nickname like '%三'	where nickname like '三%'	前导 % 无法利用索引左前缀
or连接无索引的字段	where status=1 or amount>100（amount 无索引）	拆成两个查询用union	有一个字段无索引就全表扫

实战案例：修复索引失效

-- 原始SQL：用了函数，索引失效
select id from user where date(create_time) = '2024-01-01';

-- 优化后：索引字段不做处理，改条件
select id from user where create_time >= '2024-01-01' and create_time < '2024-01-02';
-- 配合索引：
create index idx_create_time on user(create_time);

四、覆盖索引原则：查什么就索引什么，避免回表

        这是优化查询速度的 “核武器”—— 让查询需要的所有字段都在索引里，不用回表查数据。

业务场景

        订单列表页：select order_no, amount, create_time from order where status=1（只查这三个字段）。

错误案例

        只建了idx_status索引，查询时用索引找到符合条件的订单 ID 后，还要回表查order_no、amount、create_time，速度慢。

正确做法

        建 “覆盖索引”，包含查询所需的所有字段：

-- 索引包含where条件+select字段，无需回表
create index idx_status_cover on `order`(status, order_no, amount, create_time);

原理拆解

        InnoDB 的索引分 “聚簇索引”（主键索引，叶子节点存完整数据）和 “二级索引”（非主键索引，叶子节点存主键 ID）。
        普通索引查询流程：二级索引找主键→聚簇索引查数据（回表）；
        覆盖索引查询流程：二级索引直接取数据（无回表），速度提升 5-10 倍。

五、索引维护原则：别让索引变成 “累赘”

        索引不是 “一建永逸” 的，维护不好会拖慢写入性能。

业务场景

        支付流水表（日均新增 100 万行），建了 5 个复合索引，结果高峰期插入耗时从 10ms 涨到 500ms。

问题分析

        每次插入 / 更新 / 删除操作，MySQL 都要同步更新所有索引（B + 树调整）。索引越多，写入开销越大。

维护技巧

1. 定期删除无用索引：用sys.schema_unused_indexes（MySQL 8.0+）查长期未使用的索引：

   select * from sys.schema_unused_indexes where table_schema='your_db';

2. 控制索引数量：一张表索引不超过 5 个，优先保留 “查询频繁 + 过滤性强” 的索引；

3. 大表加索引用 online 方式：避免锁表（MySQL 8.0 支持ALGORITHM=INPLACE, LOCK=NONE）：

   alter table `order` add index idx_status_amount(status, amount) ALGORITHM=INPLACE LOCK=NONE; -- 在线加索引，不阻塞读写

4. 批量插入前临时禁用索引：

   alter table user disable keys; -- 禁用非主键索引
   
   -- 执行批量插入... 
   alter table user enable keys; -- 重新构建索引（比逐条插入快10倍）

六、主键索引原则：自增 ID 是 “最优解”

        主键索引是特殊的索引（聚簇索引），设计不好会引发连锁问题。

业务场景

        用 UUID 作为用户表主键，结果插入性能越来越差，查询也变慢。

问题分析

        UUID 是无序的，每次插入会打乱聚簇索引的 B + 树结构（需要频繁分裂节点），导致页分裂和碎片。

正确做法

优先用自增 ID 作为主键：

-- 推荐：自增主键，有序插入，B+树结构稳定

create table user(

id bigint primary key auto_increment, -- 自增ID

username varchar(50),

phone varchar(20)

);

特殊场景处理

• 分库分表：用雪花算法生成全局唯一 ID（有序，避免页分裂）；
• 历史数据迁移：如果必须用业务字段当主键（如订单号），确保字段有序且唯一。

八年开发总结：索引原则的 “一句话精髓”

1. 最左前缀：索引字段顺序 = 查询条件顺序，从过滤强到弱；

2. 选择性：性别 / 状态这种字段别建索引，浪费空间；

3. 防失效：别对索引字段做函数 / 运算，like 别用 % 开头；

4. 覆盖索引：查什么就索引什么，省掉回表时间；

5. 维护：索引不是越多越好，定期清理没用的；

6. 主键：自增 ID 最省心，UUID 慎用作主键。

        其实索引原则就像 “交通规则”—— 不懂规则乱建索引，就像开车闯红灯，早晚出事故；懂规则并灵活运用，才能让数据库 “跑” 得又快又稳。

        最后送大家一个习惯：写复杂查询前先用explain看看执行计划，重点看type（ref/range是好的，ALL是全表扫）和Extra（Using index是覆盖索引，Using filesort是排序，Using temporary是临时表）。

参考

https://juejin.cn/post/7545035321433079862

https://juejin.cn/post/6903873756117073928

https://juejin.cn/post/7546939299217883145