遇到过不少朋友数据库里明明建了索引一条慢查询还是跑成了蜗牛。这里说的 MySQL 索引其实不像很多人想的那样——建上就万事大吉它更像图书馆里的一套检索卡片用错了规则照样翻车。这篇把我这些年排查慢 SQL 的经验整理成“图书馆用法”一次讲透主键索引、唯一索引、普通索引、组合索引、全文索引这 5 种索引类型说明白它们分别在什么场景下用、怎么用不会失效、出了问题怎么一步步排查。如果你经常和 MySQL 打交道或者正在准备数据库相关的面试建议把这篇文章当作一份“索引使用手册”。里面不会堆砌复杂的源码分析但每一段看完都能直接用在工作里。尤其是那些“建了索引还慢”的 SQL我会直接带你用 EXPLAIN、慢查询日志把它揪出来。1. 索引的本质数据库的“图书馆”是这么工作的1.1 没有索引时数据库在“翻整本书”想象你去一个没有目录、也没有索书号的大书库找一本书。你不知道它在哪一排、哪一层只能从第一本开始一本一本看封面。运气好翻几本找到运气差得把整个书库翻个底朝天。MySQL 里没有索引的查询干的就是这件事。InnoDB 存储引擎会把表数据拆成一个个“页”放到磁盘上每页默认 16KB。查询时如果不走索引引擎只能从第一页开始把数据页读进内存逐行判断 WHERE 条件是否满足这个过程叫全表扫描。表里有一百万行你可能就要读一万多个数据页做上万次磁盘 I/O。磁盘读一页虽然有各种缓存优化但量一大慢就慢在这里。我遇到过最典型的例子一张用户订单表有三四百万行开发同学跑一条SELECT * FROM orders WHERE user_id 123要十几秒。查一下执行计划type 是 ALL说明就是全表扫描。后面给 user_id 建了个普通索引同样查询降到几十毫秒。这不是玄学是从“翻整本书”变成了“先查目录”。1.2 B树索引一本矮胖的“目录册”索引为什么能让查询这么快核心在于它改变了数据组织方式。MySQL 最常用的索引结构是 B 树你可以简单理解成一棵可以二分查找的“目录树”。B 树的叶子节点维护了关键字到数据位置的映射非叶子节点只存关键字和指针所以整棵树非常“矮胖”。一个百万行级别的 InnoDB 表索引树高度通常只有 3 层左右。也就是说走聚簇索引查询一条记录大概只需要 3 次磁盘 I/O就能从根节点一路定位到叶子节点。对比全表扫描动辄上百次的 I/O差距就是这么拉开的。另外 B 树的叶子节点之间还有链表连接天然支持范围查询。这就是为什么索引不只是对等值查询有效对、、BETWEEN这种范围条件也有明显加速效果。像哈希索引也能做到快速等值查询但做不了范围查询对排序支持也差所以 InnoDB 的普通索引默认几乎都是 B 树不会随便选哈希。1.3 索引不是建的越多越好很多人的误区是“多建索引万无一失”。实际上索引也有成本第一每次INSERT、UPDATE、DELETE都要额外维护索引树索引建多了写入性能会明显下降第二索引本身要占磁盘空间第三优化器在选择执行计划时面对一堆无效索引反而更纠结甚至可能因为统计信息不及时而选错。所以真正合理的思路不是“有查询就建索引”而是先分析真实的慢查询、数据分布和查询模式再决定该建哪种索引。不要上来给每个字段都建一个单列索引那样结果往往事与愿违。2. 5种索引的“图书馆用法”以及怎么建2.1 主键索引每一本书唯一的索书号主键索引是 InnoDB 里最特殊的一种索引。它默认就是聚簇索引也就是说整个表的数据本质上就是一棵以主键为顺序的 B 树叶子节点存储了一行的所有字段数据。你建表时指定的主键决定了数据在磁盘上物理排列的大致顺序。主键索引的核心要求是“唯一且非空”就像图书馆给每本书分配一个唯一索书号。设计主键时有几个建议尽量用自增整型做主键不要用 UUID。UUID 是随机字符串顺序不固定插入时容易导致索引页分裂产生大量碎片写入性能会受影响。主键长度不宜过长。二级索引的叶子节点会存放主键值你主键越长所有二级索引的体积就跟着膨胀磁盘占用和 I/O 成本都会上升。不要用业务字段做主键比如身份证号、手机号。业务字段一旦变更主键变更会牵动整个数据组织的物理位置代价很大。建表时指定主键是最常见操作CREATE TABLE user ( id INT NOT NULL AUTO_INCREMENT, username VARCHAR(64) NOT NULL, PRIMARY KEY (id) );2.2 唯一索引防止同一本书上架两次唯一索引的作用是保证一列或多列的组合值不允许重复但它和主键有个关键区别唯一索引允许NULL值并且 MySQL 会允许多个NULL同时存在。我经常把它比喻成图书馆的“同书号纠纷规则”内容一样的书不允许出现两个相同登记号但没登记号的书可以先放着后面再补。实际业务里用户手机号、邮箱、身份证这类有唯一性要求的字段适合建唯一索引一方面能约束脏数据另一方面也能作为查询索引使用。常见 SQLALTER TABLE user ADD UNIQUE INDEX uk_mobile (mobile);这里要提醒一句如果你只想防重复不想承担索引成本那可以通过业务逻辑或触发器校验但通常来说唯一索引是性价比最高的方案。因为它既满足唯一约束又在查询手机号时能直接走索引加速一举两得。2.3 普通索引最普适的检索卡片普通索引是最常见的索引类型不加任何唯一性限制纯粹为了加速查询。它的定位就像图书馆里一张关键词卡片同一关键词可以对应很多本书多本书记录在卡片上无所谓反正只要帮我们快速定位到书的位置就好。什么时候该建普通索引我一般会看两类条件频繁出现在WHERE条件中的列比如user_id、status。经常用于JOIN连接的列比如订单表中的customer_id。但要注意不是所有频繁查询的列都适合建索引。如果某个列的选择性太低比如性别字段只有“男”“女”两个值通过索引找到的结果占全表比例很大优化器可能宁愿全表扫描也不走索引。这个道理类似图书馆卡片上“工程技术”这一分类指向了半个书库那你不如直接逛书架。普通索引的创建方式CREATE INDEX idx_order_user_id ON orders (user_id);2.4 组合索引按顺序排布的“多级目录”组合索引也叫复合索引是把多个列组合到一棵索引树里。它的用法是面试和实际优化里的重头戏核心规则就是“最左前缀原则”。可以这么理解组合索引像一本带层级目录的工具书先按第一层分类再按第二层、第三层细分。如果你直接查第二层分类目录帮不上忙因为整本书的目录顺序是先按第一层排的。比如联合索引(a, b, c)它能支持查询条件包含a、a,b、a,b,c从左到右连续的列组合但查询条件里只有b或c时索引大概率用不上。实际设计组合索引时我会按这个顺序考虑先列等值查询的列比如dept_no ?。再放范围查询的列比如hire_date ?。范围列之后的其他列对索引用处有限除非使用 MySQL 5.6 引入的索引下推来做二次过滤但排序和索引选择上依然受限。最后考虑排序字段如果查询里需要ORDER BY把排序字段放进索引有机会避免 filesort。一个典型例子部门查询场景WHERE dept_no D05 AND hire_date 2022-06-01可以建索引(dept_no, hire_date)。这样优化器既能定位到 D05 部门又能在索引内部缩小到指定入职日期。如果只建(hire_date, dept_no)由于查询条件没限制 hire_date 的等值最左前缀就不是 dept_no索引利用率会大打折扣。创建组合索引ALTER TABLE employee ADD INDEX idx_dept_date (dept_no, hire_date);2.5 全文索引没有目录也能“关键词”搜书前面四种索引都是针对结构化字段的精确匹配或范围查询全文索引则面向文本内容的模糊搜索。你可以把它想象成给整本书做了一套关键词检索系统不再依赖目录结构而是可以搜索正文里的每一个词。MySQL 里做模糊搜索最朴素的做法是LIKE %关键词%。这种写法由于前置百分号的存在B 树索引完全失效只能全表扫描。对几百行的小表无所谓但几百万行的文章表就会很痛苦。全文索引通过分词、倒排表等方式把关键词和文档ID映射起来查询时直接查文档ID效率高很多。在 MySQL 中全文索引主要支持这几种用法ALTER TABLE article ADD FULLTEXT INDEX ft_title_content (title, content);查询时用MATCH ... AGAINSTSELECT * FROM article WHERE MATCH(title, content) AGAINST(数据库索引);需要提醒的是默认全文索引对中文分词支持并不好它依赖空格和常用符号分词。如果业务要做中文全文检索建议使用 MySQL 的 ngram 分词插件或者干脆把搜索需求交给 Elasticsearch 这类专门搜索引擎。不要把全文索引当成万能药。2.6 五种索引横向对比索引类型唯一性是否允许 NULL典型场景一句话总结主键索引唯一且非空否表的主键数据组织核心每本书唯一的索书号唯一索引唯一是防重复业务字段不允许上架两本同号的书普通索引不限制是加速 WHERE / JOIN按关键词快速定位组合索引可组合约束是多条件查询、排序多级目录按最左前缀用全文索引不限制是文本内容搜索正文关键词搜书3. 建了索引还慢多半是“用书”的姿势不对3.1 索引失效的常见原因很多时候索引建了但没生效问题出在 SQL 写法上。我总结了几个最常踩的坑。第一违反最左前缀原则。前面提过组合索引要遵循最左前缀。写了WHERE b 1但索引是(a,b,c)索引通常无法走。解决办法是调整 SQL 条件顺序或者重新设计索引列顺序。第二对索引列使用函数或计算。例如WHERE YEAR(hire_date) 2024因为对 hire_date 套了YEAR()函数索引上的有序排列就失去意义优化器只能逐行计算再判断。正确做法是改成范围条件WHERE hire_date 2024-01-01 AND hire_date 2025-01-01。同样WHERE salary 1000 5000这种写法也要避免改成salary 4000就好。第三隐式类型转换。典型的例子是手机号字段是varchar类型查询时却传了数字WHERE mobile 13612345678。MySQL 会把索引列转换成数字再比较导致索引失效。解决办法是保证字段类型和查询参数类型一致或者用明确字符串参数。第四LIKE前置百分号。LIKE %abc或LIKE %abc%都会让 B 树无法利用有序前缀只能全表扫描。只有LIKE abc%这种后置百分号才能走索引。第五OR 连接非索引列。比如WHERE name 张三 OR age 20如果age上没有索引优化器可能对整个条件做全表扫描而不是只用 name 索引。解决办法是把 OR 转成两个索引列的 UNION或者给age也建索引。第六统计信息过期或操作符号不合适。索引列上有大量重复值时优化器认为索引选择性太差会选择全表扫描。这不算索引失效但也是“建了索引还慢”的重要原因。下面是一份快速对照表现象常见原因建议组合索引部分条件不走索引违反最左前缀调整索引列顺序或 SQL 写法索引列参与函数/计算破坏了索引有序性改写为范围或等值条件隐藏类型转换字段和参数类型不一致统一类型LIKE %关键词%前置通配符全文索引或改为后置匹配OR 条件包含无索引列优化器选择全表扫描建索引或拆分 UNION走索引仍慢回表过多、统计信息旧覆盖索引、更新统计信息3.2 EXPLAIN把查询计划打开看看排查慢 SQL 的第一步不应该是乱加索引而是用EXPLAIN看执行计划。我很喜欢说的一句话是EXPLAIN 就是数据库在告诉你面对这条 SQL它打算怎么在“图书馆”里找书。基本用法EXPLAIN SELECT * FROM employee WHERE dept_no D05;输出里重点看几列type访问类型。从好到差大致是const、eq_ref、ref、range、index、ALL。看到ALL就说明是全表扫描大概率有问题。key实际用到的索引。如果值是NULL就说明没有可用索引。rows优化器预估需要扫描的行数。数字越大越危险。Extra额外信息。常见的有Using where、Using index、Using temporary、Using filesort。其中Using index是好事表示覆盖索引Using filesort表示排序没有用到索引需要额外排一次序数据量大会很慢。看执行计划时我习惯把type和rows两个字段一起看。比如出现typeALL, rows500000基本可以断定这条 SQL 在暴力翻书必须优化。如果typeref说明已经用了索引但可能的匹配行数依然很大这时又要考虑是不是索引选择性差或者需要覆盖索引避免回表。3.3 慢查询日志快速抓住“蜗牛SQL”你可能会问生产上那么多 SQL怎么知道哪条慢慢查询日志就是用来干这个的。它会把执行时间超过阈值的语句记录下来方便事后分析。在 MySQL 里临时开启SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL log_queries_not_using_indexes ON;然后使用mysqldumpslow工具对日志做汇总mysqldumpslow -s at -t 10 /var/lib/mysql/*-slow.log这里-s at表示按平均查询时间排序-t 10表示取前十名。通过它你能快速看到哪类 SQL 是“蜗牛大户”。不过要提醒一句生产环境长期开启慢查询日志会有一定性能开销和磁盘占用建议在排查期间临时开启或者使用性能监控平台采集。开启后记得确认long_query_time设置合适默认 10 秒太长一般业务库我会设为 1 秒敏感场景甚至可以设为 0.5 秒。4. 实操从一条慢 SQL 到索引优化的完整流程4.1 准备一张员工表并造数据理论讲再多不如亲手跑一遍。下面我用一个员工表来演示。CREATE TABLE employee ( id INT NOT NULL AUTO_INCREMENT, emp_no VARCHAR(20) NOT NULL, name VARCHAR(50) NOT NULL, dept_no VARCHAR(10) NOT NULL, salary DECIMAL(10,2) NOT NULL, hire_date DATE NOT NULL, PRIMARY KEY (id) ) ENGINEInnoDB;现在往里造数据。我经常用递归 CTE 快速插入几十万行比如 MySQL 8.0INSERT INTO employee (emp_no, name, dept_no, salary, hire_date) WITH RECURSIVE seq AS ( SELECT 1 AS n UNION ALL SELECT n 1 FROM seq WHERE n 500000 ) SELECT CONCAT(EMP, LPAD(n, 6, 0)), CONCAT(USER, n), CONCAT(D, LPAD(1 (n % 10), 2, 0)), 5000 (n % 20000), DATE_ADD(2015-01-01, INTERVAL (n % 3000) DAY) FROM seq;用SELECT COUNT(*) FROM employee验证一下数据量大概有五十万行。这是后续复现慢查询的基础。4.2 开启慢查询日志并捕获目标 SQL为了展示我先执行一个没有索引的查询SELECT * FROM employee WHERE dept_no D05 AND hire_date 2022-06-01;在只有主键索引的情况下这个查询需要全表扫描。打开慢查询日志SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 0;long_query_time临时设成 0确保这条查询被记录。这里稍微解释一下生产环境不要直接把阈值设成 0否则日志量会非常爆炸这里只是为了教学演示。执行完查询后在日志目录下能看到记录。日志里会显示查询时间和 SQL 语句明确告诉我们这条 SQL 就是蜗牛。4.3 用 EXPLAIN 看清问题接下来用 EXPLAIN 查看执行计划EXPLAIN SELECT * FROM employee WHERE dept_no D05 AND hire_date 2022-06-01;我实测的典型结果大致是typekeyrowsExtraALLNULL500000Using where看到typeALL、keyNULL、rows500000说明它把整个表翻了一遍。这里还只是单表范围查询如果再把这种查询放到 JOIN 里按五十万行嵌套查性能会更惨。4.4 创建组合索引并验证效果根据查询条件最直接的优化是创建一个按dept_no等值、hire_date范围的组合索引ALTER TABLE employee ADD INDEX idx_dept_date (dept_no, hire_date);再跑一次 EXPLAINEXPLAIN SELECT * FROM employee WHERE dept_no D05 AND hire_date 2022-06-01;这时基本会看到typekeyrowsExtrarefidx_dept_date大约几万Using index conditiontype从全表的ALL变成ref预估扫描行数从五十万降到几万查询时间通常能快 5 到 10 倍。这个案例里没有在 SELECT 中把列收窄所以还需要回表读完整行数据出现了Using index condition这是 MySQL 5.6 的索引下推优化表示查询条件可以在索引层先做一次过滤减少回表次数。如果想继续压榨性能还有一招把查询字段收窄成索引能完全覆盖的列避免回表。比如只查emp_no,name,dept_no并把这三个列加进索引。不过实际使用时不要盲目把所有列塞进索引索引太宽存储和写入成本会上升。4.5 别忘了后续索引维护索引建完不是终点。MySQL 的优化器依赖统计信息决定走不走索引如果统计信息过期明明有索引也可能出现全表扫描的糟糕选择。我的实践习惯是在数据量发生较大变化后执行一次ANALYZE TABLE employee;它会重新收集表的统计信息让优化器做出更合理的判断。另外线上环境可以定期检查是否存在长期未使用的索引SELECT * FROM sys.schema_unused_indexes;查到长期没用的索引可以和业务确认后考虑删除。释放的空间和写入性能收益有时候比你想的还明显。5. 常见索引“翻车”问题与排查技巧实录5.1 几个高频疑问速查问题排查思路索引一直在但 EXPLAIN 显示没走索引先看统计信息ANALYZE TABLE再看 SQL 有没有函数、隐式转换、最左前缀破坏走了索引但 rows 还是很大索引选择性太差可以考虑换列或改成覆盖索引查询里有 ORDER BY排序还是很慢看 Extra 是否出现Using filesort把排序列放进索引符合最左前缀条件里既有等值又有范围索引怎么建等值列放前面范围列放后面排序字段按需调整位置用 OR 连接多个条件后不走索引OR 条件中所有列都要有索引或者改写为 UNION5.2 我发现新手最常忽略的两个细节第一个是隐式类型转换。有一次排查用户表按手机号查询慢索引明明建在mobile字段上EXPLAIN 却显示全表扫描。后来发现查询参数是数字类型而不是字符串。单独这一个坑就能让索引完全失效。遇到这类问题第一时间检查字段定义和 SQL 入参类型。第二个是组合索引“看似命中实则没有完整利用”。例如索引(a,b,c)SQL 是WHERE a 1 AND c 2。优化器可以用到a来定位但c无法进一步缩小范围只能对a1的所有记录过滤。如果你发现明明走了索引却还是慢可能不是没走索引而是只用到了一部分前缀列。这时可以调整查询条件顺序再 EXPLAIN或者考虑是否需要新的索引覆盖a,c场景。5.3 面试题角度为什么建了索引还是慢这道题几乎每次面试都会遇到。我的回答思路分三层。第一层先确认 SQL 有没有真正用到索引。通过EXPLAIN看type和key。索引失效的常见原因就是前面说的那几类。第二层如果确实走了索引但还是很慢要考虑回表开销、扫描行数、排序或临时表。索引能快速定位到少量数据但如果最终要回表读取大量行几十万次随机 I/O 依然不便宜。覆盖索引就是解决这类问题的常用手段。第三层还要关注优化器决策。索引选择性差、数据量小、统计信息过期都可能导致优化器放弃索引并选择全表扫描。这种场景下不是“加索引”能解决的而是要让优化器拿到更准确的统计信息或者改写 SQL 让条件匹配现有索引。我在实际工作中总结了一个习惯任何一条慢 SQL都在优化前先保存它原来的 EXPLAIN 结果优化后再保存一份新的。两相对比既能看到效果也能在新问题出现时快速回退或调整。索引优化不是一次性的任务业务查询模式变了、数据分布变了索引策略也要跟着重新评估。最后再分享一个小技巧MySQL 8.0 以上可以使用EXPLAIN ANALYZE它不只是给预估计划还会真正执行 SQL 并输出每个步骤的行数和耗时。排查复杂慢查询时我经常先跑一遍EXPLAIN ANALYZE比单纯看EXPLAIN更能定位瓶颈发生在索引扫描、回表还是排序阶段。索引这块坑确实多但把最基本的图书馆目录逻辑吃透90% 的“建了索引还慢”问题都能自己解决了。