这篇文章主要讲解一下23个问题以及答案1.说⼀下你了解的关于数据库优化要考虑哪⼏个层⾯的因素2.介绍⼀下什么是索引3.索引的作⽤是什么4.索引⽤到了哪些数据结构5.索引是如何的升查询效率的6.什么时候应该创建索引7.在哪些列上创建索引8.索引越多越好吗为什么9.如何查看索引是否⽣效10.知道执⾏计划(Explain)吗它的作⽤是什么11.执⾏计划结果中各列的含义了解吗12.执⾏计划的type列的含义是什么包含哪些内容说说你熟悉的13.如果type开中显⽰const意味什么14.如果Extra列中显⽰using index 意味什么15.谈谈如何使⽤ EXPLAIN 命令来分析查询执⾏计划并举例说明如何根据执⾏计划进⾏优化16.什么是索引覆盖17.什么是回表查询18.如何避免全表扫描19.知道索引合并(优化)吗20.什么是索引下推21.说⼀下索引失效的场景22.什么是最左匹配原则23.select count(*) 与 select count(1) 的区别前言很多小伙伴面试数据库索引相关问题时只会背干巴巴概念面试官一追问底层、线上真实场景就卡壳。今天把面试最常连环提问的 23 道题全部整理出来全部用日常开发的大白话讲解不堆砌晦涩定义结合平时写 SQL 踩过的坑、线上遇到的慢 SQL 案例看完既能应付面试写业务代码也能直接用上。1. 说一下你了解的关于数据库优化要考虑哪几个层面的因素如果线上数据库卡顿、接口超时我们优化是分先后顺序的优先做低成本、见效快的最后才动硬件、改架构 第一层先改 SQL 和建合适索引。这个成本最低不用改服务器、不用拆分服务90% 的慢查询在这里就能解决比如乱写 SQL 导致索引失效、没做覆盖索引频繁回表改完接口毫秒级恢复。 第二层优化表本身设计。比如主键别用字符串、大文本单独拆分、字段尽量设非空减少索引存储压力。 第三层调 MySQL 内存参数。比如缓冲池开大点让热点索引和数据存在内存里少读磁盘。 第四层架构层面改造。数据量大、并发高了单库扛不住就做读写分离、分库分表热点数据丢 Redis 缓存模糊查询交给 ES。 第五层升级硬件。换更快的 SSD、加服务器内存这是最后没办法才选的方案成本最高。2. 介绍一下什么是索引拿书本举例子一本书后面的目录就是索引。 数据表就是一整本厚厚的书没有索引的时候想找某一条数据只能一页一页从头翻到尾也就是全表扫描。 索引就是单独建一份有序的 “目录”记录字段对应数据在哪查询的时候先查目录直接定位数据位置不用逐行遍历。 InnoDB 里索引分两种主键自带聚集索引自己建的普通、复合索引都叫二级索引。3. 索引的作用是什么好处先说 第一查询变快不用全表扫少量 IO 就能找到数据 第二B 树索引本身是排好序的平时分页排序、分组统计不用额外排序 第三设计好覆盖索引的话不用来回查表大幅减少磁盘读写。 但索引不是白给的有代价 只要新增、修改、删除数据表里所有索引都要同步更新写操作会变慢而且索引单独存文件会占用磁盘空间线上随便新增索引低版本 MySQL 还会锁表影响线上业务。4. 索引用到了哪些数据结构平时开发接触最多的是 B 树InnoDB 所有普通索引、主键索引全是这个结构适配范围查询、排序、等值查询业务场景全能打。 然后是哈希索引只有 Memory 引擎用InnoDB 有自适应哈希但几乎不用。哈希只能精准等值匹配大于小于、排序全都不支持局限性很大。 还有两种特殊索引全文索引、空间索引做搜索、地理坐标才会用到日常开发基本碰不到。5. 索引是如何提升查询效率的没有索引等于从头遍历整张表每一行都要读磁盘速度巨慢。 B 树的设计很巧妙只有最底层叶子节点存真实数据上层节点只存索引关键字树的高度很低可能 3-4 次磁盘读取就能定位到目标数据区间。 另外叶子节点用链表串在一起想查一段时间、一段范围的数据直接顺着链表读不用来回跳转。 如果设计成覆盖索引只走索引就能拿到所有需要的数据不用再去查主表完整数据少一次磁盘读取速度提升特别明显。6. 什么时候应该创建索引看平时业务 SQL 高频怎么写经常用 where 条件过滤的字段比如根据手机号、用户 ID 查数据两张表关联查询的连接字段不建索引会产生巨量笛卡尔积列表页经常排序、分组、去重的字段后台分页查询数据量大每次都要筛选的字段。 只要这个字段每天高频被查询就值得建索引。7. 在哪些列上创建索引适合建索引的区分度高的字段像手机号、订单编号、用户 ID每条数据值基本都不一样过滤后能筛掉绝大多数数据。 不建议单独建索引的 一是区分度极低的字段比如性别只有 0 和 1、删除状态 0/1就算建索引查完还是一大半数据数据库干脆直接放弃索引全表扫描 二是频繁修改的字段每次更新都要改索引拖慢写入 三是很长的文本字段索引会特别大占用大量磁盘。8. 索引越多越好吗为什么绝对不是越多越好很多新手喜欢每个字段单独建索引线上直接出问题。 每多一个索引新增、更新、删除数据的时候数据库都要同步维护这棵 B 树写接口会明显变慢高并发写入场景甚至直接卡死。 而且每个索引都会占用磁盘空间表数据量大之后存储成本翻倍。 线上新增索引还会锁表影响业务读写。 规范上普通业务表索引控制 5 个以内日志、流水这种大量写入的表控制 3 个以内长期不用的冗余索引直接删掉。9. 如何查看索引是否生效最靠谱的工具就是 Explain把查询 SQL 前面加 Explain 执行不用真的跑业务数据就能看索引有没有用上。 看两个关键字段key 字段有值代表用到索引key 是 null说明没走索引要么没建要么索引失效。 同时看 type 字段如果是 ALL代表全表扫描索引完全没生效。 也可以辅助看慢查询日志同一条 SQL 扫描行数几十万大概率没走索引。10. 知道执行计划 (Explain) 吗它的作用是什么Explain 就是 MySQL 给我们用来分析 SQL 的工具不会真实执行 SQL不会改动表里数据只会模拟执行流程把数据库的执行逻辑展示出来。 平时用来排查慢 SQL看这条 SQL 选了哪个索引、预估要扫多少行、多表关联顺序、有没有文件排序、有没有生成临时表。 上线新 SQL 之前必须先用 Explain 过一遍避免把全表扫描的 SQL 发布到线上造成数据库卡顿。11. 执行计划结果中各列的含义了解吗一共 12 个字段挑日常重点的说 id区分主查询、子查询、union 合并语句id 越大越先执行 select_type简单查询、子查询、联合查询区分 table当前操作哪张表 type最核心代表访问数据的方式直接判断 SQL 好坏 possible_keys理论上能用的索引只是候选不一定真的选 key实际最终使用的索引 key_len索引用到多少字节判断复合索引用到几列 rows预估要扫描多少行数值越大越慢 filtered过滤后剩下有效数据比例 Extra附加信息性能坑全在这里比如文件排序、临时表、覆盖索引标识。12. 执行计划的 type 列的含义是什么包含哪些内容说说你熟悉的type 代表数据库读取数据的方式性能从最好到最差排序system const eq_ref ref range index ALL 日常开发最常用的几个 const最优主键 / 唯一索引精准匹配只查 1 行 eq_ref两张表关联用主键一对一匹配联表最优 ref普通索引等值查询能匹配多条数据日常列表查询最常见 range范围查询大于小于、in、前缀模糊匹配 index遍历整棵索引树比全表扫描好一点但还是要优化 ALL全表扫描线上红线必须整改。13. 如果 type 中显示 const 意味什么这是性能天花板的级别只有两种情况能触发用主键等值查询或者唯一索引等值查询并且最多只能匹配一条数据。 数据库拿到条件后直接把这条数据当成常量处理不用去磁盘多次读取百万数据表查询也是毫秒级响应。 比如根据用户主键 id 查详情执行计划 type 基本都是 const。14. 如果 Extra 列中显示 using index 意味什么大白话讲这条查询不用回表只靠索引就能拿到全部需要的数据也就是覆盖索引。 InnoDB 二级索引只存索引字段 主键如果我们 select 查的所有字段刚好都在索引里面数据库不用再拿着主键去主键索引查完整行少一次磁盘随机读取性能提升巨大。15. 谈谈如何使用 EXPLAIN 命令来分析查询执行计划并举例说明如何根据执行计划进行优化使用很简单两种写法推荐竖排展示看得更清楚 EXPLAIN SELECT * FROM user WHERE phone 13800001111\G 拿到执行计划先看 type、key、Extra 三个地方。 举个真实踩坑案例 原 SQLSELECT * FROM user WHERE phone13800001111 ORDER BY age; 执行计划结果key 正常走 phone 索引但是 Extra 出现 Using filesort。 问题在哪索引只有 phone排序字段 age 不在索引里查完数据还要单独排序数据量大就很慢。 优化方案建复合索引 idx_phone_age (phone,age)同时不要写 select *只查业务需要的字段。 优化后再 explainExtra 出现 Using index没有文件排序完全不用回表速度提升很多。16. 什么是索引覆盖我们建的复合索引里面包含了 where 过滤用到的字段也包含了 select 需要查询返回的所有字段数据库只扫描这一棵二级索引树就能拿到所有数据不用再去主键索引查表这种情况就是索引覆盖执行计划会标 Using index。 举个例子索引是 (phone,age)查询只查 phone、age条件是 phonexxx直接走覆盖索引。17. 什么是回表查询InnoDB 二级索引只存索引列 主键不存完整一行数据。 如果我们查询的字段不在索引里面数据库只能先通过二级索引找到主键 id再拿着这个 id 去主键聚集索引里面读取完整的一行数据第二次查表这个动作就叫回表。 回表会产生大量随机磁盘 IO高并发列表查询会特别卡能避免一定要避免。18. 如何避免全表扫描全表扫描对应 typeALL开发日常写 SQL 多注意几点就能规避高频筛选、关联、排序字段建好复合索引别让索引字段参与运算、套函数字符串查询必须加引号防止隐式转换导致索引失效like 模糊查询不要把 % 放最前面or 两边的字段都要有索引一边没索引直接全表扫复合索引查询带上最左边的字段遵循最左匹配查询尽量缩小范围配合 limit 减少扫描行数上线前统一用 explain 校验杜绝 typeALL 上线。19. 知道索引合并 (优化) 吗一张表建了好几个单独的单列索引SQL 里用 or、and 连接不同索引字段数据库会同时扫描多个索引把查到的主键合并到一起这种机制就是索引合并执行计划 type 会显示 index_merge。 分三种情况and 交集、or 等值并集、or 范围排序合并。 但这个只是兜底优化性能不如直接建一个复合索引业务里尽量把多个单列索引合并成复合索引。20. 什么是索引下推MySQL5.6 之后默认开启用来减少回表次数。 没有索引下推的时候数据库只拿索引前半段过滤剩下的条件要等全部回表之后在服务层过滤很多无效数据白白回表 开启索引下推后存储引擎直接利用复合索引里所有字段提前过滤只把完全符合条件的数据回表少大量无效 IO。 执行计划 Extra 出现 Using index condition 就是索引下推生效了。21. 说一下索引失效的场景都是平时写代码经常踩的坑很好理解复合索引查询跳过最左边第一个字段不满足最左匹配整个索引废掉or 连接的两个字段其中一个没有索引复合索引里出现范围条件范围后面的索引列全部失效like 查询 % 写在最前面无法匹配 B 树有序前缀字符串字段传数字不加引号发生隐式类型转换where 条件里索引字段做加减运算、套 length、substr 这类函数使用、、not in 大范围排除数据库觉得索引扫描不如全表快索引字段区分度极低数据库主动放弃索引走全表扫描。22. 什么是最左匹配原则复合索引是按创建顺序排序存储的举个例子索引 idx (a,b,c)必须先匹配最左边的 a 字段才能继续使用后面 b、c 索引。 如果查询条件不带 a直接查 b、c索引完全用不上。 能用索引where a1where a1 and b2where a1 order by b 不能用索引where b2where b2 and c3 建索引也遵循这个逻辑等值查询字段放最前面范围条件放最后防止截断索引。23. select count (*) 与 select count (1) 的区别分版本说现在项目基本都是 5.7.18 以上这个版本两者性能没有任何区别底层优化逻辑一模一样行业规范推荐写 count ()可读性更好符合 SQL 标准。老版本 5.7.18 之前count (1) 会稍微快一点count () 会校验整行不为空多一步操作。 补充一个知识点MyISAM 没有 where 条件时 count (*) 速度极快引擎直接存了总行数InnoDB 不会缓存总行数不管哪种写法都要扫描索引统计。博客结尾总结上面 23 道题覆盖了面试从基础到深挖的全部考点没有堆砌生硬理论全是结合平时写 SQL、线上排查慢 SQL 的真实场景。 数据库优化核心思路就是提前规避索引失效、合理设计复合覆盖索引上线前用 Explain 校验执行计划从源头杜绝全表扫描、文件排序、频繁回表这类性能问题。不管是面试刷题还是日常开发写业务 SQL这套内容都能直接落地使用。