上个月帮同事排查一个线上慢查询订单表 200 万行按用户查最近 10 条订单要跑 800ms。业务方催得急同事试了好几个索引都没明显改善。我拿到 SQL 看了一眼问题根本不在有没有索引而在索引按什么顺序建、查询条件到底能不能命中规则。后来把索引改成复合索引SQL 从 800ms 掉到 3ms。这件事让我觉得很多开发不是不会写 CREATE INDEX而是对索引的语法和使用规则缺一套系统认知。这篇就把这件事说透MySQL 索引语法怎么用、使用规则有哪些、复合索引到底按什么顺序建才不白建。适合三类人看写过 SQL 但没系统性研究过索引的开发者、正在为慢查询头疼的后端或运维、准备面试想一次理清索引知识点的朋友。1. 索引底层逻辑不明白这层语法只是死记硬背1.1 索引的本质从全表扫描到目录查找数据库表里的数据在磁盘上是分散存放的。没有索引时MySQL 查一条记录只能把整张表的每一个数据页都读一遍逐行比对。还是拿订单表举例200 万行数据假设一行 1KB、一个页 16KB一页大约装 16 行一次全表扫描要读十几万个数据页。这和在一本没有目录的厚书里找一句话完全一样不是找不到是太慢。索引的本质是给数据建一棵有序的 B 树。树的叶子节点按索引键有序排列内层节点只存索引键 指针占用空间很小。查询时从根节点出发沿值的大小走对应分支几次磁盘 IO 就能定位目标数据。跟查字典的感觉很像不需要把整本字典翻一遍按字母范围逐层逼近就行。有个概念必须放在最前面索引不是给表建的是给查询路径建的。你把哪几列放进索引相当于给这几列建了一条有序目录。后续所有 SQL 能不能走这条路取决于 SQL 的条件、排序、分组是否能和这个目录的顺序对上。1.2 聚簇索引与二级索引回表到底在说什么InnoDB 表上的索引分两大类。聚簇索引表数据本身按主键构建的索引。叶子节点直接存放整行记录。InnoDB 表必须有主键没有明确主键时它会用一个隐藏的 rowid 来建。这也是为什么我一直建议给表加一个自增主键目的就是让聚簇索引干净、稳定、有序。二级索引普通索引、唯一索引都算。叶子节点存的是索引列的值 主键值不存完整行记录。理解两类索引后回表就顺理成章了。通过二级索引查数据先定位到主键值再回到聚簇索引取整行这个过程叫回表CREATE TABLE user ( id INT PRIMARY KEY, name VARCHAR(50), age INT ); CREATE INDEX idx_age ON user(age); -- 这条 SQL 会回表 SELECT * FROM user WHERE age 28;过程是先在 idx_age 里找到 age28 对应的主键 id再回聚簇索引取出完整行。如果命中的行数很少回表无所谓如果命中几千行回表几千次每次都伴随一次随机磁盘 IO性能立刻打回原形。所以能不能不回表成了索引设计里非常重要的判断标准。查询需要的字段如果全部在索引里就完全不需要回表Extra 会显示 Using index这种索引又叫覆盖索引后面细讲。1.3 为什么 B 树这么能打一个 InnoDB 数据页默认 16KB。内层节点只存键和指针假如主键是 bigint8 字节加指针约 6 字节一个页大约能装 1142 个键。叶子节点每页存放数据行一行 1KB 的话能放十几行。推算三层 B 树大约能支撑千万行级别的数据。也就是说常规业务表的查询只需要 3~4 次磁盘 IO 就能定位目标。这也是哈希索引不如 B 树适合做通用索引的原因哈希索引单点查找极快但不支持范围查询、不能排序、没有最左前缀的概念。而 B 树天然有序范围、排序、分组都能蹭上这个有序的红利。记住这句话索引的底层就一件事把无序数据变成有序结构让每次查找变成找位置而不是翻所有页。所有语法和规则都从这个本质推导出来。2. MySQL 索引语法创建、查看、删除全部写清楚2.1 创建索引的五种姿势继续用订单表当例子先把建表语句摆出来CREATE TABLE orders ( id bigint(20) NOT NULL AUTO_INCREMENT, order_no varchar(32) NOT NULL COMMENT 订单号, user_id bigint(20) NOT NULL COMMENT 用户ID, status tinyint(4) NOT NULL DEFAULT 0 COMMENT 0待支付 1已支付 2已发货 3已完成 4已取消, amount decimal(10,2) NOT NULL COMMENT 金额, create_time datetime NOT NULL COMMENT 创建时间, PRIMARY KEY (id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;第一种建表时内联定义索引。适合表结构一开始就想清楚查询场景的情况CREATE TABLE orders ( id bigint NOT NULL AUTO_INCREMENT, order_no varchar(32) NOT NULL, user_id bigint NOT NULL, status tinyint NOT NULL, PRIMARY KEY (id), KEY idx_user (user_id), UNIQUE KEY uk_order_no (order_no) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;第二种用 CREATE INDEX 单独创建适合给已存在的表补索引CREATE INDEX idx_user_status ON orders(user_id, status);第三种用 ALTER TABLE 追加效果和 CREATE INDEX 等价ALTER TABLE orders ADD INDEX idx_create_time (create_time);第四种创建唯一索引利用数据库约束防重复CREATE UNIQUE INDEX uk_order_no ON orders(order_no);第五种全文索引和空间索引。全文索引用在文章、评论这类文本搜索上MySQL 自带的全文检索能力有限复杂场景通常交给搜索引擎。空间索引用到专业的地理数据场景平时开发很少碰。ALTER TABLE articles ADD FULLTEXT INDEX ft_content (content);MySQL 8.0 还有一个值得提的新特性降序索引。8.0 之前索引里所有列都是升序存储8.0 开始可以显式指定某个列降序ALTER TABLE orders ADD INDEX idx_user_time (user_id, create_time DESC);这对按用户查、按时间倒序的查询非常友好排序能直接复用索引避免文件排序。2.2 查看索引SHOW INDEX 的字段你看懂几个想确认一张表现在有哪些索引直接SHOW INDEX FROM orders\G输出里重点看四个字段字段含义怎么用Key_name索引名一个索引名下可能有多行代表联合索引的多列Seq_in_index列在索引中的顺序从 1 开始判断联合索引顺序是否和你设计的一致Column_name索引列名确认覆盖面Cardinality区分度估算值值越大列取值越分散索引效果越好Non_unique0 表示唯一索引排查重复索引很有用Cardinality 值得多说一句。它是优化器决定是否走索引的重要参考如果某列就三个取值Cardinality 会非常小优化器可能觉得走全表扫描更划算这也是明明建了索引却不走的重要原因之一。2.3 删除索引DROP INDEX idx_user_status ON orders; -- 等价写法 ALTER TABLE orders DROP INDEX idx_user_status;语法本身没有任何难度。我想说的是删除索引之前必须先确认它是不是真的没被使用别凭感觉删。后面常见问题部分会讲怎么查未被使用的索引。2.4 索引命名规范很多人写惯了代码会把规范丢在数据库这层。建议统一命名普通索引idx_表名缩写_列名比如 idx_orders_user唯一索引uk_表名缩写_列名比如 uk_orders_order_no联合索引idx_orders_user_status列名按索引顺序用下划线连接为什么强调这个因为 SHOW INDEX 输出一大堆索引名不清不楚时根本分不清哪个是哪个维护成本极高。我见过生产环境里十几个 idx1、idx2 这种命名的索引后续做索引治理时想死的心都有。3. 索引使用规则命中还是失效高频场景一次讲清3.1 最左前缀原则复合索引的命根子最左前缀原则是复合索引里最重要、也最容易被忽略的一条规则。联合索引 idx(a, b, c) 不是把三个列各建一个独立索引而是建立一棵先按 a 排a 相同再按 b 排b 也相同再按 c 排的复合树。这意味着真正能用到完整索引的组合只有三种aa, ba, b, c查询条件里没有最左列 a 时这个索引通常完全失效。-- 假设只有联合索引 idx_user_status(user_id, status) SELECT * FROM orders WHERE user_id 1001 AND status 1; -- 命中 SELECT * FROM orders WHERE user_id 1001; -- 命中 SELECT * FROM orders WHERE status 1; -- 不命中为什么第三句不命中B 树中 status 只是第二个排序键单独拿 status 去查时MySQL 不知道应该从哪一棵子树进入只能放弃索引走全表扫描。还有一种情况是部分命中。where a and c没有 b。这种情况下 a 能走索引定位但 c 只能在 a 命中的记录里逐条过滤不会像 b 那样在索引上做精确定位。所以用 EXPLAIN 看时 type 可能从 ref 变成 range 或者 indexrows 也偏大。3.2 索引失效高频清单实践中最常见的索引失效场景整理成一张速查表场景示例结果违反最左前缀WHERE status 1status 不是最左列索引不生效索引列套函数WHERE DATE(create_time) 2025-01-01索引不生效隐式类型转换WHERE order_no 1001001order_no 是 varchar可能不生效LIKE 前导模糊WHERE order_no LIKE %AB%索引不生效OR 连接非索引列WHERE user_id 1 OR amount 100可能不生效索引列参与运算WHERE user_id 1 1002索引不生效否定条件WHERE status ! 1可能不生效范围查询后面的等值WHERE user_id 1 AND create_time 2025-01-01 AND status 1status 通常不生效逐个解释。函数包裹列是最典型的反模式。WHERE DATE(create_time) 2025-01-01MySQL 必须对每一行的 create_time 执行一次 DATE 函数才能和常量比较B 树的有序性完全无从谈起。解决办法是改成范围查询SELECT * FROM orders WHERE create_time 2025-01-01 AND create_time 2025-01-02;隐式类型转换非常隐蔽。order_no 是 varchar(32)查询条件写成数字 1001001MySQL 会把索引列转成数字再比较等于在索引列上偷偷加了一层 CAST。同样的道理字符集不一致的 JOIN 也可能导致连接列无法命中索引。LIKE 前导模糊 %AB%前缀被通配符替代后无法在有序索引里二分定位。反过来AB% 这种后缀模糊是可以走索引的因为知道开头就能定位范围。OR 条件稍微复杂。WHERE user_id 1 OR amount 100如果 amount 没有索引优化器可能选择了全表扫描因为用索引找 user_id1 的结果后还得再全表找 amount100 的结果把两个结果合并反而更慢。如果有把握两个列都有索引有时会用 index_merge 优化但这依赖优化器决策不可控。否定条件未必一定失效。status ! 1如果 status 只有五个取值大部分行都满足优化器算出走全表反而划算就会放弃索引。这也再次说明索引会不会被用上优化器会基于成本判断不是条件满足就一定走。3.3 范围查询后面的字段为什么不生效这个值得单独讲。索引是 (user_id, create_time, status)查询SELECT * FROM orders WHERE user_id 1001 AND create_time 2025-01-01 AND status 1;user_id 能精准定位create_time 能做范围定位。但 create_time 之后B 树内部无法再对 status 做精确定位了。原因是create_time 是一个范围范围内所有记录在 status 列上并不保持有序。比如同一秒内的多条订单status 可能是 1、2、3 交错排列无法通过索引直接跳到 status1 的位置。所以设计联合索引时要把等值条件放在前面范围条件放最后。排序字段也类似排完序后面的列在索引里同样不能继续精确定位。3.4 覆盖索引与索引下推两个被低估的优化覆盖索引解决的是回表问题。查询需要的字段全部包含在索引里时直接扫描索引就能拿到结果Extra 显示 Using index。-- 假如存在联合索引 idx_user_status(user_id, status) SELECT user_id, status FROM orders WHERE user_id 1001;索引里已经包含 user_id 和 status查询不需要回聚簇索引磁盘 IO 少一大截。如果查询频繁出现SELECT 只取几列做一个刚好够用的联合索引收益非常明显。索引下推Index Condition PushdownICP是 MySQL 5.6 引入的优化。没有 ICP 时二级索引查到一批记录后必须回表再过滤剩余条件。有 ICP 后存储引擎可以在二级索引内部先完成部分条件判断把不符合的记录直接过滤掉再回表。还是用订单表举例。索引是 idx_user_status(user_id, status)查询SELECT * FROM orders WHERE user_id 1000 AND user_id 2000 AND status 1;没有 ICP先把 user_id 范围内的所有记录取回回表再过滤 status1。开启 ICP在二级索引遍历时先判断 status1满足条件的才回表。回表次数大幅减少。如果 EXPLAIN 的 Extra 里看到 Using index condition说明这个优化正在生效。4. 复合索引实战where a and b 到底怎么建索引4.1 区分度优先索引设计的第一指标现在进入最实际的问题一条 SQL 里同时出现 where a and b复合索引怎么建先记住核心原则等值场景下区分度高的列放前面。区分度用这个 SQL 估算SELECT COUNT(DISTINCT user_id) / COUNT(*) AS user_sel, COUNT(DISTINCT status) / COUNT(*) AS status_sel FROM orders;值越高说明列取值越分散、过滤效果越好。订单表里 user_id 的区分度接近 1status 只有 0、1、2、3、4 五个值区分度大约 0.0000025。所以 where user_id ? and status ? 这种查询应该建 (user_id, status)不要建 (status, user_id)。为什么区分度高的放前面因为联合索引的第一排序键决定根节点往下分出的第一条分支。user_id 接近唯一一个值几乎对应一条主路径status 只有五个取值第一层只能分出五个分支每个分支下挂几十万条记录后续还得继续在索引里过滤。两者性能差距非常大数据量越多样板越明显。4.2 等值在前范围和排序放后还有一条设计铁律等值查询字段放前面范围查询字段和排序字段放后面。订单表最常见的查询是查某个用户某段时间内的订单SELECT * FROM orders WHERE user_id 1001 AND create_time 2025-01-01 ORDER BY create_time ASC;等值 user_id 放最前面范围 create_time 放后面。建索引ALTER TABLE orders ADD INDEX idx_user_time (user_id, create_time);这样 WHERE 阶段就能在索引里完成大部分工作而且 ORDER BY create_time 也能直接利用索引有序性避免文件排序。如果把 create_time 放前面user_id 放后面最左前缀直接违反索引完全失效如果把 user_id 和 create_time 都列进索引但中间夹一个 status范围断裂问题又会冒出来。4.3 排序、分组、Join 场景的索引设计ORDER BY 走索引是很多人忽略的优化点。B 树叶子节点天然有序如果 ORDER BY 的顺序和索引顺序完全一致包括 ASC/DESC8.0 之后连方向都能匹配MySQL 直接按索引顺序读就行不需要 filesort。-- 查询用户最近订单 SELECT * FROM orders WHERE user_id 1001 ORDER BY create_time DESC LIMIT 10;如果只有 idx_user_status(user_id, status)这个 SQL 会先按 user_id 定位数据再对 create_time 做文件排序。200 万行订单里按用户过滤后的数据量可能不大但文件排序依然要付出额外内存和时间。更合适的设计是建 (user_id, create_time)WHERE 定位 user_idORDER BY 直接依赖索引天然倒序读取一路顺序读下 10 条就结束了。GROUP BY 同理它本质是把相同值归到一起和索引的有序性天然契合。GROUP BY 的字段顺序和索引顺序一致可以避免临时表。重要程度和 ORDER BY 差不多很多人只记得 where 条件要建索引忘了 group by 也是索引的消费大户。JOIN 场景是业务系统里最常见的性能灾难。左边驱动表循环去右边找匹配行右边连接字段如果没有索引每行都要扫一遍全表这是典型的 N1 灾难。所以不管是什么 JOIN被驱动表的连接字段必须有索引。这个动作应该直接写进建表设计的 check list不要等出了慢查询再补救。4.4 一个可复现的完整优化案例把前面所有规则串起来走一遍完整案例。业务诉求查询某个用户、某个状态下最近 20 条订单并按创建时间倒序展示。原始 SQLSELECT id, order_no, amount, create_time FROM orders WHERE user_id 1001 AND status 1 ORDER BY create_time DESC LIMIT 20;第一步EXPLAIN 看现状EXPLAIN SELECT id, order_no, amount, create_time FROM orders WHERE user_id 1001 AND status 1 ORDER BY create_time DESC LIMIT 20;大概率看到 typeALL 或 refrows 接近全表Extra 出现 Using filesort。这已经能说明问题查询条件没被索引充分消化排序又在额外排序。第二步设计索引。等值字段是 user_id 和 status排序字段是 create_time。按等值在前、排序在后的原则建联合索引ALTER TABLE orders ADD INDEX idx_user_st_time (user_id, status, create_time);第三步再次 EXPLAINEXPLAIN SELECT id, order_no, amount, create_time FROM orders WHERE user_id 1001 AND status 1 ORDER BY create_time DESC LIMIT 20;这次 type 会变成 refkey 指向 idx_user_st_timerows 大幅下降Extra 里 Using filesort 消失。因为 B 树已经能按 (user_id, status) 定位到目标集合并且叶子节点在这组键下天然按 create_time 有序LIMIT 20 只需要顺序读一小段。第四步考虑要不要做覆盖索引。SELECT 只取 id、order_no、amount、create_time 四列如果这个查询是超高频查询可以把 amount 也放进索引让 Extra 变成 Using index连回表都省掉。代价是索引体积变大、写入变慢所以一般只对最高频的查询做这种极端优化。线上实测结果200 万行订单表优化前 800ms优化后 3~5ms。差距就是这么夸张。核心不是某个神秘配置而是让 B 树尽可能多地承担 where 过滤和排序工作。5. 常见问题排查与技术沉淀5.1 怎么确认索引真的生效遇到慢查询第一动作永远是 EXPLAIN不要靠猜。EXPLAIN SELECT * FROM orders WHERE user_id 1001 AND status 1;重点看四列列含义理想状态type访问类型至少是 range理想是 ref/constkey实际使用的索引不为 NULLrows预估扫描行数越小越好Extra附加信息不出现 Using filesorttype 从好到差大致是system const eq_ref ref range index ALL。看到 ALL 说明全表扫描索引没起作用看到 index 说明扫了整棵索引树比全表好一点但也不理想ref 和 eq_ref 是常见的理想状态。Extra 里出现 Using filesort、Using temporary 都需要警惕说明排序或分组没有复用索引MySQL 在额外内存或临时表里干活数据量大时非常伤。5.2 经常踩的坑速查现象可能原因解决思路明明建了索引却不走违反最左前缀或索引列上有函数/类型转换改写 SQL调整索引列顺序EXPLAIN 出来的 rows 很大区分度不够优化器认为全表扫描更划算把过滤性更强的列放进索引Extra 出现 Using filesort排序字段和索引顺序不一致把排序字段加入联合索引8.0 可配 DESC写入越来越慢索引过多每次 DML 都要维护多棵 B 树精简索引删除重复和低频索引加了索引后效果不稳定统计信息滞后数据分布变了执行 ANALYZE TABLE 刷新统计信息两列各自建了索引但查询还是慢缺少联合索引单列索引无法同时高效过滤两列根据最左前缀重建联合索引关于为什么不走索引我的排查习惯是把 SQL 里的 where、order by、group by 列全圈出来对照最左前缀、函数包裹、范围断裂、区分度四条规则逐条排除基本能在几分钟内定位问题远比乱试多个索引高效。5.3 几条实践心得第一条别急着把所有可能查的列都加索引。一个 200 万行的表五个索引的写入维护开销和一个索引完全不是一个量级。索引是给高频查询设计的不是给万一以后要查准备的。每次加索引前先问自己这个索引服务哪条 SQL那条 SQL 多久执行一次第二条删索引前先看它到底有没有被用过。MySQL 8.0 的 sys schema 里有一张 schema_unused_indexes 视图可以直接查出长期未使用的索引确认后再删比我看什么线上截图都靠谱。第三条EXPLAIN ANALYZE 是排查性能的放大器。MySQL 8.0.18 起支持能直接给出真实执行时间和耗时分布EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id 1001 AND status 1;它比普通 EXPLAIN 更接近真实情况调优时我一般以它为准。第四条索引不是数据库调优的全部。SQL 写法、表设计、数据分布、连接池配置都会影响最终效果。索引是在 SQL 和表结构已经合理的情况下最后那一层放大器。SQL 写得稀烂加一百个索引也救不回来。关于索引这件事我在实际工作里最深的体会是它不是独立存在的魔法而是数据和查询之间的桥梁。建索引前先把 SQL 想明白把 where、order by、group by 里的列圈出来按顺序排进索引。平时多看几眼 EXPLAIN比记任何口诀都管用。语法说到底不过一行 CREATE INDEX真正值钱的是想清楚它到底在加速哪条路。
MySQL索引语法与使用规则：从慢查询到3ms的调优实践
上个月帮同事排查一个线上慢查询订单表 200 万行按用户查最近 10 条订单要跑 800ms。业务方催得急同事试了好几个索引都没明显改善。我拿到 SQL 看了一眼问题根本不在有没有索引而在索引按什么顺序建、查询条件到底能不能命中规则。后来把索引改成复合索引SQL 从 800ms 掉到 3ms。这件事让我觉得很多开发不是不会写 CREATE INDEX而是对索引的语法和使用规则缺一套系统认知。这篇就把这件事说透MySQL 索引语法怎么用、使用规则有哪些、复合索引到底按什么顺序建才不白建。适合三类人看写过 SQL 但没系统性研究过索引的开发者、正在为慢查询头疼的后端或运维、准备面试想一次理清索引知识点的朋友。1. 索引底层逻辑不明白这层语法只是死记硬背1.1 索引的本质从全表扫描到目录查找数据库表里的数据在磁盘上是分散存放的。没有索引时MySQL 查一条记录只能把整张表的每一个数据页都读一遍逐行比对。还是拿订单表举例200 万行数据假设一行 1KB、一个页 16KB一页大约装 16 行一次全表扫描要读十几万个数据页。这和在一本没有目录的厚书里找一句话完全一样不是找不到是太慢。索引的本质是给数据建一棵有序的 B 树。树的叶子节点按索引键有序排列内层节点只存索引键 指针占用空间很小。查询时从根节点出发沿值的大小走对应分支几次磁盘 IO 就能定位目标数据。跟查字典的感觉很像不需要把整本字典翻一遍按字母范围逐层逼近就行。有个概念必须放在最前面索引不是给表建的是给查询路径建的。你把哪几列放进索引相当于给这几列建了一条有序目录。后续所有 SQL 能不能走这条路取决于 SQL 的条件、排序、分组是否能和这个目录的顺序对上。1.2 聚簇索引与二级索引回表到底在说什么InnoDB 表上的索引分两大类。聚簇索引表数据本身按主键构建的索引。叶子节点直接存放整行记录。InnoDB 表必须有主键没有明确主键时它会用一个隐藏的 rowid 来建。这也是为什么我一直建议给表加一个自增主键目的就是让聚簇索引干净、稳定、有序。二级索引普通索引、唯一索引都算。叶子节点存的是索引列的值 主键值不存完整行记录。理解两类索引后回表就顺理成章了。通过二级索引查数据先定位到主键值再回到聚簇索引取整行这个过程叫回表CREATE TABLE user ( id INT PRIMARY KEY, name VARCHAR(50), age INT ); CREATE INDEX idx_age ON user(age); -- 这条 SQL 会回表 SELECT * FROM user WHERE age 28;过程是先在 idx_age 里找到 age28 对应的主键 id再回聚簇索引取出完整行。如果命中的行数很少回表无所谓如果命中几千行回表几千次每次都伴随一次随机磁盘 IO性能立刻打回原形。所以能不能不回表成了索引设计里非常重要的判断标准。查询需要的字段如果全部在索引里就完全不需要回表Extra 会显示 Using index这种索引又叫覆盖索引后面细讲。1.3 为什么 B 树这么能打一个 InnoDB 数据页默认 16KB。内层节点只存键和指针假如主键是 bigint8 字节加指针约 6 字节一个页大约能装 1142 个键。叶子节点每页存放数据行一行 1KB 的话能放十几行。推算三层 B 树大约能支撑千万行级别的数据。也就是说常规业务表的查询只需要 3~4 次磁盘 IO 就能定位目标。这也是哈希索引不如 B 树适合做通用索引的原因哈希索引单点查找极快但不支持范围查询、不能排序、没有最左前缀的概念。而 B 树天然有序范围、排序、分组都能蹭上这个有序的红利。记住这句话索引的底层就一件事把无序数据变成有序结构让每次查找变成找位置而不是翻所有页。所有语法和规则都从这个本质推导出来。2. MySQL 索引语法创建、查看、删除全部写清楚2.1 创建索引的五种姿势继续用订单表当例子先把建表语句摆出来CREATE TABLE orders ( id bigint(20) NOT NULL AUTO_INCREMENT, order_no varchar(32) NOT NULL COMMENT 订单号, user_id bigint(20) NOT NULL COMMENT 用户ID, status tinyint(4) NOT NULL DEFAULT 0 COMMENT 0待支付 1已支付 2已发货 3已完成 4已取消, amount decimal(10,2) NOT NULL COMMENT 金额, create_time datetime NOT NULL COMMENT 创建时间, PRIMARY KEY (id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;第一种建表时内联定义索引。适合表结构一开始就想清楚查询场景的情况CREATE TABLE orders ( id bigint NOT NULL AUTO_INCREMENT, order_no varchar(32) NOT NULL, user_id bigint NOT NULL, status tinyint NOT NULL, PRIMARY KEY (id), KEY idx_user (user_id), UNIQUE KEY uk_order_no (order_no) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;第二种用 CREATE INDEX 单独创建适合给已存在的表补索引CREATE INDEX idx_user_status ON orders(user_id, status);第三种用 ALTER TABLE 追加效果和 CREATE INDEX 等价ALTER TABLE orders ADD INDEX idx_create_time (create_time);第四种创建唯一索引利用数据库约束防重复CREATE UNIQUE INDEX uk_order_no ON orders(order_no);第五种全文索引和空间索引。全文索引用在文章、评论这类文本搜索上MySQL 自带的全文检索能力有限复杂场景通常交给搜索引擎。空间索引用到专业的地理数据场景平时开发很少碰。ALTER TABLE articles ADD FULLTEXT INDEX ft_content (content);MySQL 8.0 还有一个值得提的新特性降序索引。8.0 之前索引里所有列都是升序存储8.0 开始可以显式指定某个列降序ALTER TABLE orders ADD INDEX idx_user_time (user_id, create_time DESC);这对按用户查、按时间倒序的查询非常友好排序能直接复用索引避免文件排序。2.2 查看索引SHOW INDEX 的字段你看懂几个想确认一张表现在有哪些索引直接SHOW INDEX FROM orders\G输出里重点看四个字段字段含义怎么用Key_name索引名一个索引名下可能有多行代表联合索引的多列Seq_in_index列在索引中的顺序从 1 开始判断联合索引顺序是否和你设计的一致Column_name索引列名确认覆盖面Cardinality区分度估算值值越大列取值越分散索引效果越好Non_unique0 表示唯一索引排查重复索引很有用Cardinality 值得多说一句。它是优化器决定是否走索引的重要参考如果某列就三个取值Cardinality 会非常小优化器可能觉得走全表扫描更划算这也是明明建了索引却不走的重要原因之一。2.3 删除索引DROP INDEX idx_user_status ON orders; -- 等价写法 ALTER TABLE orders DROP INDEX idx_user_status;语法本身没有任何难度。我想说的是删除索引之前必须先确认它是不是真的没被使用别凭感觉删。后面常见问题部分会讲怎么查未被使用的索引。2.4 索引命名规范很多人写惯了代码会把规范丢在数据库这层。建议统一命名普通索引idx_表名缩写_列名比如 idx_orders_user唯一索引uk_表名缩写_列名比如 uk_orders_order_no联合索引idx_orders_user_status列名按索引顺序用下划线连接为什么强调这个因为 SHOW INDEX 输出一大堆索引名不清不楚时根本分不清哪个是哪个维护成本极高。我见过生产环境里十几个 idx1、idx2 这种命名的索引后续做索引治理时想死的心都有。3. 索引使用规则命中还是失效高频场景一次讲清3.1 最左前缀原则复合索引的命根子最左前缀原则是复合索引里最重要、也最容易被忽略的一条规则。联合索引 idx(a, b, c) 不是把三个列各建一个独立索引而是建立一棵先按 a 排a 相同再按 b 排b 也相同再按 c 排的复合树。这意味着真正能用到完整索引的组合只有三种aa, ba, b, c查询条件里没有最左列 a 时这个索引通常完全失效。-- 假设只有联合索引 idx_user_status(user_id, status) SELECT * FROM orders WHERE user_id 1001 AND status 1; -- 命中 SELECT * FROM orders WHERE user_id 1001; -- 命中 SELECT * FROM orders WHERE status 1; -- 不命中为什么第三句不命中B 树中 status 只是第二个排序键单独拿 status 去查时MySQL 不知道应该从哪一棵子树进入只能放弃索引走全表扫描。还有一种情况是部分命中。where a and c没有 b。这种情况下 a 能走索引定位但 c 只能在 a 命中的记录里逐条过滤不会像 b 那样在索引上做精确定位。所以用 EXPLAIN 看时 type 可能从 ref 变成 range 或者 indexrows 也偏大。3.2 索引失效高频清单实践中最常见的索引失效场景整理成一张速查表场景示例结果违反最左前缀WHERE status 1status 不是最左列索引不生效索引列套函数WHERE DATE(create_time) 2025-01-01索引不生效隐式类型转换WHERE order_no 1001001order_no 是 varchar可能不生效LIKE 前导模糊WHERE order_no LIKE %AB%索引不生效OR 连接非索引列WHERE user_id 1 OR amount 100可能不生效索引列参与运算WHERE user_id 1 1002索引不生效否定条件WHERE status ! 1可能不生效范围查询后面的等值WHERE user_id 1 AND create_time 2025-01-01 AND status 1status 通常不生效逐个解释。函数包裹列是最典型的反模式。WHERE DATE(create_time) 2025-01-01MySQL 必须对每一行的 create_time 执行一次 DATE 函数才能和常量比较B 树的有序性完全无从谈起。解决办法是改成范围查询SELECT * FROM orders WHERE create_time 2025-01-01 AND create_time 2025-01-02;隐式类型转换非常隐蔽。order_no 是 varchar(32)查询条件写成数字 1001001MySQL 会把索引列转成数字再比较等于在索引列上偷偷加了一层 CAST。同样的道理字符集不一致的 JOIN 也可能导致连接列无法命中索引。LIKE 前导模糊 %AB%前缀被通配符替代后无法在有序索引里二分定位。反过来AB% 这种后缀模糊是可以走索引的因为知道开头就能定位范围。OR 条件稍微复杂。WHERE user_id 1 OR amount 100如果 amount 没有索引优化器可能选择了全表扫描因为用索引找 user_id1 的结果后还得再全表找 amount100 的结果把两个结果合并反而更慢。如果有把握两个列都有索引有时会用 index_merge 优化但这依赖优化器决策不可控。否定条件未必一定失效。status ! 1如果 status 只有五个取值大部分行都满足优化器算出走全表反而划算就会放弃索引。这也再次说明索引会不会被用上优化器会基于成本判断不是条件满足就一定走。3.3 范围查询后面的字段为什么不生效这个值得单独讲。索引是 (user_id, create_time, status)查询SELECT * FROM orders WHERE user_id 1001 AND create_time 2025-01-01 AND status 1;user_id 能精准定位create_time 能做范围定位。但 create_time 之后B 树内部无法再对 status 做精确定位了。原因是create_time 是一个范围范围内所有记录在 status 列上并不保持有序。比如同一秒内的多条订单status 可能是 1、2、3 交错排列无法通过索引直接跳到 status1 的位置。所以设计联合索引时要把等值条件放在前面范围条件放最后。排序字段也类似排完序后面的列在索引里同样不能继续精确定位。3.4 覆盖索引与索引下推两个被低估的优化覆盖索引解决的是回表问题。查询需要的字段全部包含在索引里时直接扫描索引就能拿到结果Extra 显示 Using index。-- 假如存在联合索引 idx_user_status(user_id, status) SELECT user_id, status FROM orders WHERE user_id 1001;索引里已经包含 user_id 和 status查询不需要回聚簇索引磁盘 IO 少一大截。如果查询频繁出现SELECT 只取几列做一个刚好够用的联合索引收益非常明显。索引下推Index Condition PushdownICP是 MySQL 5.6 引入的优化。没有 ICP 时二级索引查到一批记录后必须回表再过滤剩余条件。有 ICP 后存储引擎可以在二级索引内部先完成部分条件判断把不符合的记录直接过滤掉再回表。还是用订单表举例。索引是 idx_user_status(user_id, status)查询SELECT * FROM orders WHERE user_id 1000 AND user_id 2000 AND status 1;没有 ICP先把 user_id 范围内的所有记录取回回表再过滤 status1。开启 ICP在二级索引遍历时先判断 status1满足条件的才回表。回表次数大幅减少。如果 EXPLAIN 的 Extra 里看到 Using index condition说明这个优化正在生效。4. 复合索引实战where a and b 到底怎么建索引4.1 区分度优先索引设计的第一指标现在进入最实际的问题一条 SQL 里同时出现 where a and b复合索引怎么建先记住核心原则等值场景下区分度高的列放前面。区分度用这个 SQL 估算SELECT COUNT(DISTINCT user_id) / COUNT(*) AS user_sel, COUNT(DISTINCT status) / COUNT(*) AS status_sel FROM orders;值越高说明列取值越分散、过滤效果越好。订单表里 user_id 的区分度接近 1status 只有 0、1、2、3、4 五个值区分度大约 0.0000025。所以 where user_id ? and status ? 这种查询应该建 (user_id, status)不要建 (status, user_id)。为什么区分度高的放前面因为联合索引的第一排序键决定根节点往下分出的第一条分支。user_id 接近唯一一个值几乎对应一条主路径status 只有五个取值第一层只能分出五个分支每个分支下挂几十万条记录后续还得继续在索引里过滤。两者性能差距非常大数据量越多样板越明显。4.2 等值在前范围和排序放后还有一条设计铁律等值查询字段放前面范围查询字段和排序字段放后面。订单表最常见的查询是查某个用户某段时间内的订单SELECT * FROM orders WHERE user_id 1001 AND create_time 2025-01-01 ORDER BY create_time ASC;等值 user_id 放最前面范围 create_time 放后面。建索引ALTER TABLE orders ADD INDEX idx_user_time (user_id, create_time);这样 WHERE 阶段就能在索引里完成大部分工作而且 ORDER BY create_time 也能直接利用索引有序性避免文件排序。如果把 create_time 放前面user_id 放后面最左前缀直接违反索引完全失效如果把 user_id 和 create_time 都列进索引但中间夹一个 status范围断裂问题又会冒出来。4.3 排序、分组、Join 场景的索引设计ORDER BY 走索引是很多人忽略的优化点。B 树叶子节点天然有序如果 ORDER BY 的顺序和索引顺序完全一致包括 ASC/DESC8.0 之后连方向都能匹配MySQL 直接按索引顺序读就行不需要 filesort。-- 查询用户最近订单 SELECT * FROM orders WHERE user_id 1001 ORDER BY create_time DESC LIMIT 10;如果只有 idx_user_status(user_id, status)这个 SQL 会先按 user_id 定位数据再对 create_time 做文件排序。200 万行订单里按用户过滤后的数据量可能不大但文件排序依然要付出额外内存和时间。更合适的设计是建 (user_id, create_time)WHERE 定位 user_idORDER BY 直接依赖索引天然倒序读取一路顺序读下 10 条就结束了。GROUP BY 同理它本质是把相同值归到一起和索引的有序性天然契合。GROUP BY 的字段顺序和索引顺序一致可以避免临时表。重要程度和 ORDER BY 差不多很多人只记得 where 条件要建索引忘了 group by 也是索引的消费大户。JOIN 场景是业务系统里最常见的性能灾难。左边驱动表循环去右边找匹配行右边连接字段如果没有索引每行都要扫一遍全表这是典型的 N1 灾难。所以不管是什么 JOIN被驱动表的连接字段必须有索引。这个动作应该直接写进建表设计的 check list不要等出了慢查询再补救。4.4 一个可复现的完整优化案例把前面所有规则串起来走一遍完整案例。业务诉求查询某个用户、某个状态下最近 20 条订单并按创建时间倒序展示。原始 SQLSELECT id, order_no, amount, create_time FROM orders WHERE user_id 1001 AND status 1 ORDER BY create_time DESC LIMIT 20;第一步EXPLAIN 看现状EXPLAIN SELECT id, order_no, amount, create_time FROM orders WHERE user_id 1001 AND status 1 ORDER BY create_time DESC LIMIT 20;大概率看到 typeALL 或 refrows 接近全表Extra 出现 Using filesort。这已经能说明问题查询条件没被索引充分消化排序又在额外排序。第二步设计索引。等值字段是 user_id 和 status排序字段是 create_time。按等值在前、排序在后的原则建联合索引ALTER TABLE orders ADD INDEX idx_user_st_time (user_id, status, create_time);第三步再次 EXPLAINEXPLAIN SELECT id, order_no, amount, create_time FROM orders WHERE user_id 1001 AND status 1 ORDER BY create_time DESC LIMIT 20;这次 type 会变成 refkey 指向 idx_user_st_timerows 大幅下降Extra 里 Using filesort 消失。因为 B 树已经能按 (user_id, status) 定位到目标集合并且叶子节点在这组键下天然按 create_time 有序LIMIT 20 只需要顺序读一小段。第四步考虑要不要做覆盖索引。SELECT 只取 id、order_no、amount、create_time 四列如果这个查询是超高频查询可以把 amount 也放进索引让 Extra 变成 Using index连回表都省掉。代价是索引体积变大、写入变慢所以一般只对最高频的查询做这种极端优化。线上实测结果200 万行订单表优化前 800ms优化后 3~5ms。差距就是这么夸张。核心不是某个神秘配置而是让 B 树尽可能多地承担 where 过滤和排序工作。5. 常见问题排查与技术沉淀5.1 怎么确认索引真的生效遇到慢查询第一动作永远是 EXPLAIN不要靠猜。EXPLAIN SELECT * FROM orders WHERE user_id 1001 AND status 1;重点看四列列含义理想状态type访问类型至少是 range理想是 ref/constkey实际使用的索引不为 NULLrows预估扫描行数越小越好Extra附加信息不出现 Using filesorttype 从好到差大致是system const eq_ref ref range index ALL。看到 ALL 说明全表扫描索引没起作用看到 index 说明扫了整棵索引树比全表好一点但也不理想ref 和 eq_ref 是常见的理想状态。Extra 里出现 Using filesort、Using temporary 都需要警惕说明排序或分组没有复用索引MySQL 在额外内存或临时表里干活数据量大时非常伤。5.2 经常踩的坑速查现象可能原因解决思路明明建了索引却不走违反最左前缀或索引列上有函数/类型转换改写 SQL调整索引列顺序EXPLAIN 出来的 rows 很大区分度不够优化器认为全表扫描更划算把过滤性更强的列放进索引Extra 出现 Using filesort排序字段和索引顺序不一致把排序字段加入联合索引8.0 可配 DESC写入越来越慢索引过多每次 DML 都要维护多棵 B 树精简索引删除重复和低频索引加了索引后效果不稳定统计信息滞后数据分布变了执行 ANALYZE TABLE 刷新统计信息两列各自建了索引但查询还是慢缺少联合索引单列索引无法同时高效过滤两列根据最左前缀重建联合索引关于为什么不走索引我的排查习惯是把 SQL 里的 where、order by、group by 列全圈出来对照最左前缀、函数包裹、范围断裂、区分度四条规则逐条排除基本能在几分钟内定位问题远比乱试多个索引高效。5.3 几条实践心得第一条别急着把所有可能查的列都加索引。一个 200 万行的表五个索引的写入维护开销和一个索引完全不是一个量级。索引是给高频查询设计的不是给万一以后要查准备的。每次加索引前先问自己这个索引服务哪条 SQL那条 SQL 多久执行一次第二条删索引前先看它到底有没有被用过。MySQL 8.0 的 sys schema 里有一张 schema_unused_indexes 视图可以直接查出长期未使用的索引确认后再删比我看什么线上截图都靠谱。第三条EXPLAIN ANALYZE 是排查性能的放大器。MySQL 8.0.18 起支持能直接给出真实执行时间和耗时分布EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id 1001 AND status 1;它比普通 EXPLAIN 更接近真实情况调优时我一般以它为准。第四条索引不是数据库调优的全部。SQL 写法、表设计、数据分布、连接池配置都会影响最终效果。索引是在 SQL 和表结构已经合理的情况下最后那一层放大器。SQL 写得稀烂加一百个索引也救不回来。关于索引这件事我在实际工作里最深的体会是它不是独立存在的魔法而是数据和查询之间的桥梁。建索引前先把 SQL 想明白把 where、order by、group by 里的列圈出来按顺序排进索引。平时多看几眼 EXPLAIN比记任何口诀都管用。语法说到底不过一行 CREATE INDEX真正值钱的是想清楚它到底在加速哪条路。
