1. 索引的本质与底层存储结构聊MySQL索引之前我先把一个最常见的误区说清楚很多人把索引当成一种加快查询的神秘设置以为加了索引数据库就万事大吉。这是不对的。索引本质上是一种数据结构它解决的核心问题只有一个——减少磁盘IO次数。MySQL默认的存储引擎InnoDB索引底层用的是B树。为什么不选哈希表、二叉树或者红黑树这里我展开聊一下理解了底层原理后面很多优化就都顺理成章了。1.1 为什么是B树而不是红黑树哈希表做等值查询确实快O(1)就能定位但数据库的查询场景远不止等值查询——范围查询、排序、模糊匹配都是家常便饭哈希表在这种场景下几乎没法用。红黑树是平衡二叉树单次查询O(logn)听着还行但问题在于树高一个千万量级的表红黑树大概需要20到30层每下一层都是一次磁盘IO这个代价太致命了。B树的核心设计就是矮胖。一个InnoDB的页默认16KB假设主键是bigint8字节每个节点大概能存储上千个键值三层B树就能撑起千万级别的数据量。也就是说一次普通查询走主键索引最多3次磁盘IO就能定位到数据页这个效率是红黑树完全比不上的。1.2 B树索引的两种类型聚簇与非聚簇InnoDB的索引分成两大类聚簇索引clustered index和二级索引secondary index。这两者最大的区别是叶子节点存什么。聚簇索引的叶子节点直接存放完整的行数据表里的数据本身就是按主键排序的——这也是为什么InnoDB表必须要有主键。如果你建表时不指定主键InnoDB会找第一个非空的唯一索引实在没有它就会偷偷生成一个隐藏的rowid做主键。这个行为很多新手完全没察觉但等到了数据量上去之后隐藏主键往往是性能劣化的元凶。二级索引的叶子节点存的是索引列的值加上主键值。注意是索引列主键不是整行数据。所以走二级索引查询时通常要经历两步先在二级索引的B树上找到主键值再到聚簇索引的B树上去查完整数据。这个过程叫回表。提示回表不是百害无利的。如果查询的列恰好都包含在索引里MySQL就只查二级索引本身就够了不用回表这叫覆盖索引。设计索引时尽量让查询列被索引覆盖到是减少回表开销最直接的手段。2. 索引分类与创建实操MySQL里的索引种类不少但真正日常用得多的就是下面这几种。很多面试题喜欢问主键索引和唯一索引的区别我看网上答案都说得挺绕其实用自己的话讲清楚就行主键索引是聚簇索引一个表只能有一个且不允许为NULL唯一索引是二级索引一个表可以有多个列的NULL值可以存在多个MySQL的InnoDB里唯一索引允许多个NULL值因为NULL和NULL不算冲突。2.1 六种索引类型对照速查索引类型关键字是否允许重复是否允许NULL一个表可以建几个备注主键索引PRIMARY KEY否否1个聚簇索引叶子节点存整行数据唯一索引UNIQUE否允许多个多个二级索引叶子存索引列主键普通索引INDEX允许允许多个最常用的加速查询索引组合索引INDEX(多列)允许允许多个多列联合遵循最左前缀原则全文索引FULLTEXT——多个主要用于大文本的LIKE %xxx%搜索InnoDB从5.6开始支持空间索引SPATIAL——多个主要针对geometry类型一般业务用不到这里单独说下全文索引。很多人以为MySQL的全文索引能替代ES做全文搜索实际体验下来差别还是很大的。MySQL全文索引适合数据量不大、对中文分词要求不高的场景比如几万条文章标题的模糊搜索。一旦到了百万级文本内容、需要复杂中文分词和相关性排序还是老老实实上专业搜索组件吧。2.2 创建索引的几种方式与场景选择创建索引的时机我大致分成三种第一种是建表时直接定义CREATE TABLE user_info ( id BIGINT AUTO_INCREMENT PRIMARY KEY, user_no VARCHAR(64) NOT NULL COMMENT 用户编号, user_name VARCHAR(32) NOT NULL COMMENT 用户姓名, mobile VARCHAR(20) DEFAULT NULL, status TINYINT DEFAULT 1, create_time DATETIME DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uk_user_no (user_no), KEY idx_mobile (mobile), KEY idx_status_time (status, create_time) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;第二种是表已存在用ALTER TABLE来加ALTER TABLE user_info ADD INDEX idx_user_name (user_name); ALTER TABLE user_info ADD UNIQUE KEY uk_user_no (user_no);第三种是用CREATE INDEX命令CREATE INDEX idx_user_name ON user_info (user_name);这三种方式效果等价选哪种纯粹看个人习惯。我习惯在开发阶段用第二种因为建表语句已经写了字段直接在表结构上加索引改动最小。删除索引用DROP INDEX idx_name ON table_name或者ALTER TABLE table_name DROP INDEX idx_name。另外提醒一句索引不是越多越好索引本身要占磁盘空间每次写入时还要额外维护B树的更新。一个写多读少的表加一排索引纯粹是给自己找麻烦。2.3 字段类型对索引的影响这一块容易被忽略直接决定索引能不能用上。首先是字符集。如果两张表join的字段用了不同的字符集比如一张utf8、一张utf8mb4MySQL很可能无法直接用索引做关联因为底层比较的时候要做隐式转换。开发规范里一般要求所有表统一使用utf8mb4不仅是emoji存储的问题关联查询性能也有关系。其次是字段长度。索引列是varchar类型时建索引一般不需要把整个字段都索引进去。比如用户姓名规范一点的做法是只索引前几个字符KEY idx_user_name (user_name(10))。这样可以大幅减小索引的体积一个页能装下更多索引值B树的层高更低IO次数也更少。再次是隐式类型转换。最典型的就是varchar字段拿数字去查WHERE mobile 13800138000。如果mobile是varchar类型MySQL会把查询值转成字符串再匹配吗不会它会先把字段转成数字再比较——结果就是索引上用不了LIKE那种匹配必须扫描全表。这个坑我踩过不只一次排查慢SQL时看到type是ALL先检查是不是字段类型配错了。3. 组合索引与最左前缀原则网上关于where后面有a and b怎么建索引的讨论非常多热搜词里就有这个。这个问题的本质就是组合索引的设计。组合索引和单列索引最大的区别在于组合索引是有方向性的它先把第一列排序再在相同第一列值内对第二列排序依此类推。3.1 最左前缀原则的完整解读所谓最左前缀指的是查询条件里必须从组合索引的最左列开始连续匹配索引才会生效。我拿一个实际场景来说明有一张订单表常用查询条件是WHERE status 1 AND create_time 2024-01-01。这时候建索引的正确姿势是KEY idx_status_time (status, create_time)。那么下面几条SQL分别能不能用到这个索引-- 能用status走索引create_time走索引范围扫描 SELECT * FROM order_info WHERE status 1 AND create_time 2024-01-01; -- 能用create_time显然用不上但status这个等值条件能走索引 SELECT * FROM order_info WHERE status 1; -- 不能用跳过了第一列status直接查create_time索引从第二列开始无法匹配 SELECT * FROM order_info WHERE create_time 2024-01-01;第三类查询是最容易犯的错误。很多人建了组合索引之后以为这个索引覆盖了所有列第二列随便怎么查都用得上——实际上MySQL的B树在组合索引里是先按第一列排的第一列都没出现在条件里后面排得再整齐也没办法定位。补充一个细节最左前缀连续匹配的意思是从第一列开始连续用不是说查询条件里必须全部列都在。WHERE status 1这种只有第一列的查询当然能用索引。还有一类情况要特别注意——第一个条件是范围查询时的优化器行为。比如WHERE status 1 AND create_time 2024-01-01这条SQL能用组合索引但只有status能利用索引做范围扫描如果条件匹配的结果集太大create_time的过滤效率就会下降。所以设计组合索引时一般把等值条件的列放在前面把范围条件的列放在后面。实操建议写SQL时把组合索引里靠前的列放在WHERE的最前面即使MySQL优化器实际会自动调整顺序自己写清楚也能降低排查时的认知负担。3.2 索引下推ICP带来的变化MySQL 5.6版本之后引入了一个重要的优化叫索引下推Index Condition Pushdown。在没有ICP之前组合索引KEY idx_status_time (status, create_time)遇到WHERE status 1 AND create_time 2024-01-01这种条件时MySQL会先用status定位到一批索引记录然后每条都回表去读整行数据再用create_time去过滤。有了ICP之后MySQL会在索引层面就把create_time的条件判断做掉只有满足create_time条件的记录才回表。这显著减少了回表次数对组合索引的使用效果提升很大。日常排查慢SQL时看执行计划里Extra列如果出现Using index condition说明索引下推正在生效。3.3 组合索引列顺序选择的三条经验给自己做太多约束没啥意义我总结三条实用经验第一区分度高的列优先放前面。比如性别字段取值只有男、女区分度太低相比之下用户的手机号几乎每条都不同。把区分度高的列放前面索引树的剪枝效率更高。第二等值条件优先、范围条件靠后。WHERE status 1 AND create_time ...这种场景status放前create_time放后。第三高频查询场景优先。如果一个查询是每周跑一次报表用的另一个查询是每秒几十次的接口查询优先给后者配最顺手的索引组合。4. ExplaIN执行计划索引用得怎么样一眼看清索引建得好不好、SQL写得对不对不能靠猜要看MySQL给出的执行计划。用EXPLAIN SELECT ...查看执行计划是我排查SQL的第一动作比什么工具都直接。我挑几个关键列出来讲这几个看懂基本就够日常使用了。4.1 type列访问类型从好到差执行计划里type列反映了MySQL访问数据的方式从性能高到低大概是type含义说明system表只有一行基本是系统表的特例const主键或唯一索引等值查询命中一行最快之一eq_ref关联查询时按主键或唯一索引逐行匹配join场景中很理想ref普通索引等值匹配常见WHERE普通索引列常量range索引范围扫描常见BETWEEN、、、IN等index全索引扫描比ALL好点但没有过滤掉太多行ALL全表扫描最差数据量大时千万别出现我给自己定了一个规矩业务SQL的type最好在ref及以上。如果看到range可以接受但观察一下扫描行数是否和预期一致如果看到ALL除非是明确要全表扫的小表否则一定得整改。4.2 keys列与rows列的判断技巧keys列显示实际用到的索引名rows列显示MySQL预估要扫描的行数。这两个要结合起来看。举个例子SELECT * FROM order_info WHERE status 1如果status列有个单列索引keys显示idx_statusrows是几万type是ref——这是正常的。但如果这个表有1000万数据status1的记录占了500万走索引反而可能比全表扫描还慢因为回表500万次的成本非常高。这时候优化器可能会选择全表扫描type变成ALLrows变成1000万。这恰恰说明索引不是万能药区分度太低时MySQL优化器会主动放弃索引。还有一个细节值得关注EXPLAIN的rows是预估值不是精确值。数据量大、统计信息不准确时预估和实际可能差很多。如果怀疑优化器预估错了可以用ANALYZE TABLE table_name更新统计信息让优化器重新评估。4.3 Extra列里几种值得警惕的提示Extra列是执行计划里最有信息量的一列Using filesortMySQL在排序时没有利用索引顺序要额外做一次排序操作。如果排序的数据量大这就是个性能炸弹。解决思路是把ORDER BY的字段加到索引中去让索引天然有序。Using temporary使用了临时表常见于GROUP BY和DISTINCT的复杂场景也是性能恶化的信号SQL要多做优化。Using index覆盖索引不回表这是理想状态。Using index condition索引下推生效减少了回表次数可以接受。Using where在存储引擎层返回数据后又进行了WHERE过滤需要结合type来判断整体效率。5. 索引失效场景实战排查这个部分我必须单开一章因为网上搜索引失效的实在太多了。我结合自己的实战经验把最常见的失效场景整理出来每一个都是踩过坑换来的。5.1 函数操作导致索引失效对索引列使用了函数索引基本就废了。这是最经典的失效场景之一-- 失效对create_time使用了DATE()函数 SELECT * FROM order_info WHERE DATE(create_time) 2024-06-01; -- 正确做法改为范围查询 SELECT * FROM order_info WHERE create_time 2024-06-01 AND create_time 2024-06-02;有人会问MySQL不是有函数索引吗确实5.7版本开始支持函数索引但在常规开发里大量函数索引会加大SQL的复杂度而且很多老项目的MySQL版本还没到这个功能。所以我的建议是能改SQL就别建函数索引SQL侧的写法是成本最低的方案。WHERE name CONCAT(张, 三)这种虽然是函数但作用在常量上对索引列本身没有影响不会失效。核心判断标准是索引列是否被包裹在函数里。5.2 隐式转换导致索引失效刚才提过的隐式类型转换放到失效场景里再强调一遍。两条SQL-- mobile字段是varchar类型 SELECT * FROM user_info WHERE mobile 13800138000; -- 失效 SELECT * FROM user_info WHERE mobile 13800138000; -- 生效MySQL对数字和字符串比较的处理逻辑是把字符串转换成数字再比较。也就是说索引列mobile被强制转换成了数值类型索引自然失效。应对方案一是代码层面确保参数类型和字段类型一致二是统一使用字符串传参最简单也最稳。同样的道理字符集不同导致关联查询时无法用索引也是隐式转换的一种表现。5.3 模糊查询的%前缀通配导致索引失效LIKE %关键字这种左侧带百分号的模糊查询索引一定是失效的因为B树按列值的顺序存储无法从通配符开头的值做定位。比较实用的是区分场景-- 失效以通配符开头 SELECT * FROM user_info WHERE user_name LIKE %三%; SELECT * FROM user_info WHERE user_name LIKE %三; -- 生效通配符在尾部 SELECT * FROM user_info WHERE user_name LIKE 张%;左侧模糊查询的解决方案没有银弹最常用的三条路一是如果数据量可控直接接受全表扫描二是数据量大的场景引入专门的全文检索组件三是如果只是前缀匹配场景可以用普通索引。MySQL的全文索引从5.6开始支持中文但如果数据量上了几十万中文分词质量还是差强人意。真实项目里如果需要大量模糊搜索建议考虑用专门的搜索引擎组件如ES不要为难MySQL。5.4 不等于、NOT IN和NULL判断WHERE status ! 1MySQL的优化器一般不会用status索引做这类查询因为不确定不等于的分布情况但它可能把!转换成range扫描。实际测试下来大多数情况下type会变成ALL或走了别的索引。WHERE mobile NOT IN (...)同理不走索引的概率极高。WHERE mobile IS NULL这里要看索引列是否允许NULL。允许NULL时MySQL的二级索引会存储NULL值IS NULL查询在某些条件下是可能走索引的。但如果业务设计里这个列大量为NULL那区分度就极差索引优化器大概率放弃。需要注意的一点是尽量避免在索引列上使用OR连接不同条件。比如WHERE status 1 OR user_name 张三如果status有索引、user_name也有索引MySQL早期版本经常把OR查询搞成全表扫。现在优化器的确能做index merge但仍然是比较重的操作。建议能拆成两条SQL就拆或者改写成UNION。5.5 排序和分组场景下的索引失效ORDER BY无法用索引的情况很常见。比如SELECT * FROM order_info WHERE status 1 ORDER BY create_time如果只有status的单独索引而create_time不在索引里MySQL必须把结果集取出来再排序Extra列会显示Using filesort。这个场景下的正确优化是建立组合索引(status, create_time)。由于组合索引在相同status内已经对create_time排好序直接按索引顺序读取就是结果顺序连排序都省了。同样GROUP BY容易出现Using temporary临时表的问题。好的索引设计可以让分组操作直接基于索引列的顺序扫描完成不用额外建临时表。排查思路和ORDER BY是一样的。6. 高频SQL优化的实用技巧与工具选择索引设计到最后拼的就是一套系统性的分析方法。我这里把日常排查慢SQL的方法论整理一下。6.1 从慢查询日志入手定位问题MySQL提供了慢查询日志功能通过配置可以记录执行时间超过阈值的SQL-- 查看慢查询日志状态 SHOW VARIABLES LIKE slow_query_log; SHOW VARIABLES LIKE long_query_time; -- 开启慢查询日志需要持久化到配置文件 SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1;我一般在开发环境把long_query_time设成0.5秒方便抓出更多潜在问题。生产环境一般设1秒以上日志文件要单独存放定期分析。实际操作中光看慢日志还不够日志里记录的SQL不一定用了什么索引必须再配合EXPLAIN分析。我总结的排查流程是先看慢日志找到耗时SQL再对SQL做EXPLAIN根据执行计划的type、keys、rows、Extra列判断问题最后通过调整索引或改写SQL解决问题再回归压测验证效果。6.2 大表加索引的正确姿势给大表加索引最怕的是锁表时间过长导致线上业务中断。MySQL 8.0之前ALTER TABLE加索引操作在InnoDB上虽然不是全表锁但实际操作仍然比较重。几个经验第一尽量选择线上业务低峰期操作。凌晨三点加索引是常规操作听起来不酷但真的很实用。第二MySQL 8.0的在线DDL支持INPLACE算法很多加索引操作不会阻塞DML了。但5.7版本中部分加索引操作依然可能引入锁问题尽量用ALGORITHMINPLACE显式指定ALTER TABLE big_table ADD INDEX idx_user_name (user_name), ALGORITHMINPLACE;第三数据量极大时可以考虑通过新建一张相同结构的表先在新表上加好索引再分批把数据导过去最后改表名切换。这种方法虽然运维成本高但能把对业务的影响降到最低。6.3 索引选择时的一些容易忽略的点索引合并Index Merge是个容易被忽视的场景。举个例子WHERE status 1 AND user_name 张三如果user_id和user_name各自有单列索引MySQL可能分别走两个索引再取交集。但这不见得比组合索引高效——组合索引只需要一次B树搜索索引合并需要两次搜索加结果集操作。设计上优先考虑组合索引而不是把希望寄托在索引合并上。另外就是索引统计信息的及时性。时间久了、数据量变化大优化器的判断会偏差。定期对核心表做ANALYZE TABLE是低投入高回报的维护操作。再提一个冷门但实用的点覆盖索引的价值在查询列少时尤其明显。比如业务只需要查user_name和mobile两个字段索引(user_name, mobile)就能覆盖这个查询回表完全消失。7. 主键设计、事务与索引的联动关系索引设计和主键设计是绑在一起的。很多选手建表时根本没有认真思考主键策略等到数据量上来了再考虑改主键那真是一场灾难。7.1 主键为什么推荐自增而不是UUIDInnoDB聚簇索引的数据是物理按主键排序存放的。如果主键是自增的每次插入新记录都追加到当前B树的最右叶子节点节点满了就分裂顺序写性能非常高。而UUID做主键时插入顺序是随机的B树要频繁进行节点分裂、移动数据产生的随机IO会让写入性能下降好几个量级。还有一个现实痛点UUID占36个字符如果别的表都要用它做外键二级索引的体积会被撑得很大内存和磁盘的浪费非常明显。所以从MySQL 8.0开始官方甚至直接建议使用AUTO_INCREMENT或者全局唯一ID生成器而不是UUID。7.2 事务隔离级别和索引的关系事务隔离级别与索引使用之间没有直接因果关系但有个小细节需要留意在RR可重复读隔离级别下普通SELECT走的是快照读MVCC版本链是挂在索引记录上的。如果查询能正好命中聚簇索引或二级索引MVCC版本链的遍历效率也会高一些如果走了全表扫描每条记录都要去回溯版本链开销就会明显变大。另一个点是当前读比如UPDATE、DELETE、SELECT ... FOR UPDATE会加锁InnoDB对走索引的记录加的是记录锁如果没走索引那就是全表锁实际是范围锁但表现上接近全表锁定。所以线上会出现一个现象我只是更新了一条没走索引的数据结果整个表都被锁住了。这就是典型的因索引缺失导致的锁膨胀问题这种问题排查起来特别隐蔽但一旦发生影响就是灾难级的。7.3 自增主键的页分裂问题自增主键就百分百完美吗也不是。在高并发插入场景下自增主键集中在B树最右节点如果这个节点还没满但插入量突然变大会发生页分裂。更关键的是如果表里已存在大量删除操作最右叶子节点可能不是空的自增插入时依然会触发分裂。处理办法之一是定期整理表碎片ALTER TABLE table_name ENGINEInnoDB或者OPTIMIZE TABLE table_name。这个操作会重建表和索引让B树重新归整。但注意大表做这个操作会锁表必须在低峰期执行。8. 常见问题速查表与避坑技巧这部分我按实际问题来整理每一条都是我或团队同事在项目中验收过的结果可以直接对照排查。问题现象可能原因排查建议推荐方案查询很慢EXPLAIN显示ALL没有可用索引或索引失效看keys、rows列加合适索引检查列类型Extra列显示Using filesortORDER BY列不在索引里查看排序字段把排序列加进组合索引WHERE条件里有函数查询慢索引列应用了函数改写SQL去除函数包裹改成范围查询或函数索引LIKE %xx%慢通配符开头无法用索引确认业务可接受程度全文索引/搜索引擎或接受全表扫varchar字段和数字比较慢隐式类型转换检查字段类型和参数类型统一为字符串传参更新数据导致锁表UPDATE没走索引EXPLAIN看type确保WHERE条件命中索引关联查询慢且无法用索引字符集不一致检查两表字段字符集统一utf8mb4ORDER BY limit 深度分页慢大量扫描后丢弃看EXPLAIN扫描行数用游标/覆盖索引优化8.1 一个实际案例复盘之前有个业务反馈订单列表接口订单量到了800万之后查询从原来的100毫秒涨到了4秒。我EXPLAIN了一下发现type是rangekeys是idx_statusrows预估是120万。这个SQL是WHERE status 1 ORDER BY create_time DESC LIMIT 20 OFFSET 3000走的是status单列索引。因为status1的订单非常多MySQL先把所有符合条件的记录从索引里抓出来再额外排序Using filesort然后丢掉了前面3000条才返回最后的20条——整个过程代价极高。优化方案很简单把索引改成组合索引(status, create_time)。优化后type还是range但Extra里的Using filesort消失了排序直接利用索引顺序完成。深度分页带来的偏移量消耗依然存在但整体查询时间降到了600毫秒以内。再进一步优化可以改成游标分页把OFFSET 3000换成WHERE status 1 AND create_time 上一页最大时间限定条件彻底绕开深度分页的毛病。8.2 基于索引的常见面试题实战答案这个区域给准备面试的朋友。面试官问索引相关问题时其实围绕的就是下面这几类第一题主键索引和唯一索引的区别。 回答框架主键索引是聚簇索引、叶子存整行数据、表只能一个、不允许NULL唯一索引是二级索引、叶子存索引列主键、允许NULL了不会报错、一个表可以有多个。第二题哪些场景会导致索引失效。 回答框架函数操作、隐式类型转换、LIKE左模糊、WHERE条件中OR连接非索引列部分场景、反向查询!、NOT IN、对索引列做运算如id 1 5、联合索引不满足最左前缀原则。第三题SQL优化的思路。 回答框架先定位慢SQLEXPLAIN分析执行计划检查type是否ALL、key是否为null、rows预估、Extra是否有filesort和temporary然后针对性地加索引、调整索引顺序或改写SQL。回答的时候多举自己实际的优化案例会加分很多。8.3 三个容易忽略的细节技巧细节一使用IN而非连续OR。WHERE status IN (1, 2, 3)通常能走索引范围扫描而WHERE status 1 OR status 2 OR status 3不一定建议改写为IN。细节二分页排序时把排序列放入索引。ORDER BY create_time LIMIT 0, 10如果表里只有普通索引没有(create_time)的索引大概率会造成Using filesort。加个KEY idx_create_time (create_time)就能直接按索引顺序取前10条。细节三count查询优先走覆盖索引。COUNT(*)或COUNT(1)在InnoDB里需要扫描行数如果表里有个小体积的二级索引优化器会优先走它因为二级索引体积比聚簇索引小IO开销低。所以建一个小体积的普通索引对高频的count查询会有意外收益。关于索引这个事我先聊到这儿。我实际操作下来最大的感受是索引设计做得好SQL性能就有六成以上把握但索引设计又是一个不断演进的过程数据量和查询模式变了原来的索引可能就不合适了。所以别指望一次性设计终身完美定期看慢查询日志、定期分析执行计划、定期调整索引这个习惯比任何一条具体的优化技巧都重要。