【MySQL】 索引、联合索引以及大数据量分批查询问题总结

MySQL 索引、联合索引以及大数据量分批查询问题总结1. 索引到底是干什么的很多人理解索引加索引以后查询就一定快。这个理解是不准确的。索引真正的作用帮助数据库快速定位数据减少需要扫描的数据量。例如一张表用户表 1000万条数据查询SELECT*FROMuserWHEREuser_id10086;如果没有索引数据库只能第1条 第2条 第3条 ... 第1000万条一条一条判断。这种叫全表扫描Full Table Scan如果建立索引CREATEINDEXidx_user_idONuser(user_id);查询SELECT*FROMuserWHEREuser_id10086;数据库可以索引 ↓ 快速定位 user_id10086 ↓ 找到对应数据不用扫描全部数据。所以索引的本质是减少扫描的数据量。2. 索引是不是用了就一定快不是。很多人的误区使用索引 一定快实际上使用索引 ↓ 扫描数据少 ↓ 才会快如果使用索引以后仍然需要扫描大量数据也可能很慢。例如表订单表 1亿条数据建立CREATEINDEXidx_create_timeONorder_table(create_time);查询SELECT*FROMorder_tableWHEREcreate_time2026-01-01;执行计划type: range表示走了索引范围查询但是如果符合条件的数据5000万条那么数据库仍然需要通过索引找到开始位置 ↓ 扫描5000万条数据所以虽然走索引但是扫描范围太大仍然可能慢。3. range 实际上是走了索引对吧对。例如SELECT*FROMorder_tableWHEREcreate_time2026-07-01;如果 create_time 有索引执行计划type: range表示索引范围扫描例如索引2026-01 2026-02 2026-03 ... 2026-07数据库找到2026-07的位置 ↓ 继续向后扫描所以range 走索引。但是range 不代表一定快。还要看扫描的数据量。4. 普通索引和联合索引有什么区别4.1 普通索引例如CREATEINDEXidx_nameONuser(name);表示给一个字段建立索引。查询SELECT*FROMuserWHEREname张三;可以使用这个索引。4.2 联合索引例如CREATEINDEXidx_name_ageONuser(name,age);表示多个字段组成一个索引。例如数据张三 18 张三 20 李四 25 王五 30索引结构类似name | -- age联合索引遵循最左匹配原则索引(name,age)可以支持WHEREname张三也可以支持WHEREname张三ANDage18但是WHEREage18通常不能有效使用。原因联合索引首先按照name排序。没有 name数据库不知道从哪里开始查 age。5. 为什么增加 id 0 后扫描量变多假设表业务数据表 100万条数据其中id 是主键数据id 1 2 3 4 ... 1000000查询SELECT*FROMAWHEREstatus完成ORDERBYidLIMIT1000;可能执行根据status索引找到符合数据 ↓ 排序id ↓ 返回1000条现在增加ANDid0变成SELECT*FROMAWHEREstatus完成ANDid0ORDERBYidLIMIT1000;问题id 0对于主键来说基本等于所有数据过滤效果很低。但是同时存在ORDERBYidLIMIT1000而主键天然按照id排序所以 MySQL 可能认为直接扫描主键 不用额外排序 找到1000条停止于是执行PRIMARY KEY扫描 id1 判断条件 id2 判断条件 id3 判断条件 ... 找到1000条6. 为什么主键扫描反而慢例如总数据100万条符合条件5000条如果走业务索引先找到符合条件的数据 扫描5000条如果走主键id1 不符合 id2 不符合 id3 不符合 ... 扫描20万条 才找到1000条虽然使用了索引但是扫描更多数据所以仍然慢。7. LIMIT 到底是什么执行逻辑例如表1000条数据符合条件200条SQLSELECT*FROMAWHERE条件LIMIT100;是不是先查询100条 然后过滤不是。逻辑顺序FROM ↓ WHERE过滤 ↓ ORDER BY排序 ↓ LIMIT限制实际逻辑1000条数据 ↓ 过滤 ↓ 剩余200条 ↓ 取前100条但是实际执行时数据库可能提前停止。例如扫描数据 找到第1条符合 找到第2条符合 ... 找到第100条符合 停止因为已经满足 LIMIT。8. 大数据量为什么需要分批查询假设5000万条数据一次查询SELECT*FROMA;问题内存压力大查询时间长数据处理慢所以需要每次处理1000条9. 为什么使用 id 上一次ID不要使用LIMIT100000,1000因为页数越大扫描越多。例如第一页LIMIT0,1000扫描1000条第10000页LIMIT10000000,1000数据库需要扫描10001000条 丢弃前10000000条 返回1000条推荐游标分页第一次SELECT*FROMAWHERE条件ORDERBYidLIMIT1000;得到最后id5000第二次SELECT*FROMAWHERE条件ANDid5000ORDERBYidLIMIT1000;继续。10. 那 id 0 这种方式有什么问题分页思想没有问题。问题是第一次id0范围太大。同时ORDERBYid容易让 MySQL 选择主键扫描而不是业务条件索引过滤正确方式后续id上一次最大id是合理的。11. 联合索引如何帮助分页查询假设SQLSELECT*FROMAWHEREstatus完成ANDtype运输ANDid?ORDERBYidLIMIT1000;如果只有status索引数据库可能先过滤status ↓ 再过滤type ↓ 再排序id效率一般。可以建立CREATEINDEXidx_status_type_idONA(status,type,id);这样索引顺序status ↓ type ↓ id数据库可以先过滤status ↓ 过滤type ↓ 按照id范围扫描 ↓ 返回1000条12. 如何判断是不是索引选择错误使用EXPLAINSELECT...重点看key实际使用的索引。例如key: PRIMARY表示走主键。可能导致扫描大量数据。rows预计扫描行数。例如rows: 500000表示需要扫描很多数据。如果rows: 5000通常更合理。Extra例如Using filesort表示额外排序。但是不要简单认为没有filesort一定更快因为为了避免排序走主键有可能扫描更多数据。13. 总结核心理解1. 索引作用减少扫描的数据量不是用了索引一定快2. range表示索引范围查询但是范围太大一样慢3. 联合索引作用多个字段一起建立索引遵循最左匹配原则4. 大数据分页推荐idlastIdORDERBYidLIMIT10005. SQL 优化重点不要只看有没有走索引重点看扫描多少数据 使用什么索引 执行计划是否合理最终目标让数据库尽可能少扫描数据。