1. 这一天的内容定位为什么是数据库索引而不是Python语法学Python学到第43天按常规路线早该撸Flask、写爬虫、做数据分析玩了。但我那套“100天计划”把Day 43专门留给MySQL索引原因很简单我见过太多用Python写业务的人CRUD溜得飞起一看数据库慢查询日志直接傻眼。你写的代码再优雅一条不带索引的SQL能让接口从20毫秒变成20秒到时候Python的任何优化技巧都救不了你。这一天不是让你背索引语法而是要你在脑子里建立起“数据在磁盘上到底怎么存储、MySQL怎么找到你想要的那条记录”这一整套逻辑。只有搞懂索引的底层组织方式和失效条件你才能避免瞎建索引——尤其是那种在一个表上堆了十几个单列索引、结果写入慢、查询也没变快的经典翻车操作。这篇内容适合三类人第一类是Python基础已经学完、开始写真实业务接口的开发者第二类是负责维护老项目、经常被慢SQL折磨的运维或后端开发者第三类是纯粹想搞懂“为什么加个索引查询就快了”的求知型选手。不需要你有DBA背景只要你写过SELECT、JOIN、WHERE这三种语句这天的内容就完全能跟上。我更想强调的是这里讨论的不是一个孤立的技术点。索引优化的思路——用空间换时间、用预处理换查询效率、用合理的结构减少无效计算——这种思维方式在你以后设计缓存策略、拆分服务、优化算法时完全通用。所以别只把Day 43当成一个“索引语法速查”它其实是你从“写代码”转变成“设计系统”的一道分水岭。2. 从“全表扫描”说起索引到底解决了什么问题2.1 没有索引时MySQL是怎么找数据的先想个最朴素的问题一张表里存了一万条用户记录你要找到手机号是138xxxx8888的那个人MySQL在没有索引的情况下是怎么干的答案是从头开始一条一条地读比对手机号字段直到找出所有匹配的记录。这个操作在MySQL里叫全表扫描Full Table Scan。全表扫描本身没毛病关键看表有多大。一万条还能忍一百万条呢一千万条呢每次查询都要把整张表的全部数据页从磁盘捞出来过一遍哪怕你只需要一条记录。我做过一次不算严谨的测试在一张600万行数据的表上做无索引等值查询冷启动状态下耗时接近4秒。同样的查询加了合适的索引之后耗时降到30毫秒以内。这个数量级的差距完全不是Python层面你能通过什么代码技巧弥补回来的。所以问题的本质是MySQL需要一个类似“目录”的机制不用翻完整本书就能定位到目标记录所在的页码。这个机制就是索引。这里涉及一个关键认知索引不是MySQL执行查询时的可选项而是数据读取路径的底层加速器。每一条SQL执行前优化器都会根据统计信息和索引情况决定“走哪条路”去取数据。没有索引就只有全表扫描一条路有索引优化器才可能有别的选择。2.2 索引的底层组织方式B树到底比顺序扫描强在哪MySQL InnoDB引擎里主键索引和辅助索引最常用的底层数据结构是B树。很多人一听B树就觉得复杂其实你可以把它想象成一个“多层目录”最顶层是根节点往下是内部节点最底层是叶子节点。关键特性有两个叶子节点之间通过链表串联且叶子节点直接存储整行数据聚簇索引或者索引字段加主键值辅助索引。B树的查找过程是典型的二分查找逐层收窄。以查找id500000为例假设树的高度是3层MySQL先从根节点读入一大块索引条目判断目标范围落在哪一层再往下走一层最后抵达叶子节点在链表里定位到具体位置。整个过程读入磁盘的数据页数量是常数级别的——通常3到4次I/O就能定位任意一行数据而不是从头扫描几百万条。这里有个特别重要的设计点B树的叶子节点是有序的而且它们之间是双向链表。这意味着范围查询比如WHERE age BETWEEN 20 AND 30只需要找到起点叶节点然后顺着链表往后读就行了。相比随机访问顺序读取在磁盘和内存层面都更友好。我在Day 43会专门强调这一点因为很多人的范围查询慢往往不是索引没建而是索引设计让范围扫描没法充分利用B树的链表特性。另外还有一点容易被忽略B树为了保持平衡插入、删除时可能会触发页分裂和页合并。换句话说索引不是建完就一劳永逸的频繁写入会让索引页不断变化碎片增多查询性能会逐渐退化。这也是为什么即使有索引也要定期做优化维护——后面我会专门讲。2.3 聚簇索引和辅助索引为什么说“回表”是性能杀手InnoDB里每张表都有一个聚簇索引也就是主键索引。聚簇索引的叶子节点直接存这一行的完整数据。也就是说当你用主键查询时MySQL在B树里找到叶子节点就等于找到了整行数据不需要再去别的地方取。这是最高效的查询路径。当你给某个非主键字段比如user_name创建索引时这个索引属于辅助索引也叫二级索引。辅助索引的叶子节点存储的是“索引字段的值 主键值”。也就是说如果你用user_name索引查询MySQL先在辅助索引的B树里找到匹配的叶子节点拿到主键值然后再拿着主键去聚簇索引的B树里再查一遍才能拿到完整行数据。这个过程就叫回表。回表不是必然的坏事一共就多一两次磁盘I/O。但如果你需要查出大量数据比如一个辅助索引匹配了10万行那就要回表10万次性能急剧下降。这就是我经常说的“辅助索引不是万能的要查全字段时得想想怎么避免回表”。最常见的解决方案是覆盖索引让辅助索引的叶子节点直接包含你要查询的所有字段。比如用户表里有id、user_name、phone三个字段你建立了(user_name, phone)联合索引那么SELECT phone FROM table WHERE user_name xxx根本不需要回表因为索引叶子节点里已经有你要的phone了。这个技巧MySQL术语叫Using index是优化查询的一个非常高效的方案。回表这个问题Python开发者经常会忽略因为ORM框架帮你生成的SQL往往都是SELECT *。我在后面第4小节会说不要盲目相信ORM默认行为必要时要手动指定返回列不仅是为了省流量更关键的是帮MySQL创造覆盖索引的机会。3. 索引设计实操创建索引前要回答的四个关键问题3.1 第一个问题你到底要优化哪一条SQL很多人一上来就是“给这个字段加个索引吧”完全没搞清这条SQL长什么样、执行路径是怎样的。这是典型的“没看病情先开药”。我在Day 43里反复强调一个原则索引不是给表建的是给SQL建的。同样的表WHERE条件不同、排序方式不同、关联字段不同需要索引的字段组合完全不同。举个例子。一张订单表orders字段有id, user_id, status, created_at, amount。如果你有一个高频查询是“查某个用户最近30天的已完成订单”那么SQL大概是SELECT * FROM orders WHERE user_id 1001 AND status completed AND created_at 2024-01-01 ORDER BY created_at DESC;这时候直接在user_id上建单列索引效果有限——因为MySQL还得去过滤status和created_at。更好的方案是建立一个联合索引(user_id, status, created_at)这样B树先按user_id定位用户再按status过滤最后直接利用索引的有序性完成排序连filesort都省了。这就是所谓“索引匹配最左前缀 索引排序”。所以我的建议是动手创建索引之前先把项目里最慢的20条SQL翻出来逐个分析它们的WHERE条件、ORDER BY字段、JOIN用的关联字段。然后根据高频SQL的模式来设计联合索引而不是凭空猜。没有慢SQL日志的话可以从MySQL的performance_schema或慢查询日志开启记录这方面的配置我第5节会提到。提示把慢查询日志打开long_query_time先设为1秒跑个业务高峰期再回头分析。这是所有索引优化工作的起点“数据驱动”不仅适用于产品决策也适用于索引设计。3.2 第二个问题联合索引的字段顺序怎么排联合索引的字段顺序是整个索引设计中最容易翻车的点。联合索引(a, b, c)本质上是先按a排序a相同再按b排序b相同再按c排序。这决定了它有两个重要特性最左前缀原则和索引排序能力。最左前缀原则的意思是如果查询条件里不包含最左边的字段那么这个联合索引基本不会被高效使用。例如(user_id, status, created_at)这个联合索引可以高效支持WHERE user_id ?、WHERE user_id ? AND status ?、WHERE user_id ? AND status ? AND created_at ?但如果你直接写WHERE status completedMySQL优化器极大概率会放弃这个联合索引或者只能扫索引但效率很低。字段顺序怎么排我的经验是三句话等值条件的字段放前面范围条件的字段放后面选择性高的字段放前面最常用于排序的字段放在最后或者紧随最后一个等值条件之后。以user_id 1001 AND status completed AND created_at 2024-01-01 ORDER BY created_at DESC来说user_id和status是等值条件放前面created_at既有范围又有排序放最后。这样的排列既满足了过滤条件又让索引天然支持排序。需要特别说明的是选择性区分度也很重要。如果一个字段只有“男/女”两种值区分度极低放在联合索引第一位意义不大——因为它无法快速收窄范围。假如是is_deleted这种标志字段更是直接别考虑放索引首位。当然如果查询全是等值条件区分度低的字段只要放在前面不会影响后续字段的过滤但这需要衡量确实无法绕开。3.3 第三个问题唯一索引、普通索引、前缀索引怎么选这一节我想集中把三类常见索引的特点说透因为它们各自的适用场景完全不同。唯一索引有两个作用一是约束——保证字段值不重复防止脏数据二是加速——它天然是等值查询的利器。如果你的业务本身要求某个字段必须唯一比如手机号、订单号那直接建唯一索引不要先建普通索引再加应用层校验。因为唯一索引是数据库层面的兜底靠Python代码校验总有漏网之鱼。普通索引就是没有任何约束的辅助索引适合不需要纯唯一性的场景。它的查询性能跟唯一索引差别不大但允许重复值因此插入时不强制检查唯一性写入成本略低一点。很多开发者说“这个字段业务上不会重复啊”但我通常建议不要依赖这种“自定义假设”该建唯一索引就建否则后面数据从多个接口汇入时很容易出现意想不到的重复。前缀索引则是个容易被忽视的利器。如果字段是长文本比如用户邮箱userexample.com、或者日志消息你不可能把整列都塞进索引里——浪费空间、降低I/O效率。这时可以只对前几个字符建索引比如ALTER TABLE users ADD INDEX idx_email_prefix (email(10))。但要注意前缀索引也是有代价的它无法做覆盖索引扫描因为在索引里存不了完整字段值查询时仍然需要回表拿真实数据。另外定前缀长度时要测试区分度选取一个能覆盖绝大多数数据的前缀长度否则优化效果会大打折扣。3.4 第四个问题一个表建多少索引才合理说一个我踩过的大坑早期维护某个后台管理系统的用户表陆陆续续为每个字段都建了索引加索引的时候感觉自己是“性能工程师”结果等业务起来后用户表写入直接变慢单条INSERT从1毫秒变成了十几毫秒并发一高更是明显变慢。原因很简单每个索引都是B树每次INSERT、UPDATE、DELETE不仅要写数据本身还要同步维护所有索引树。索引越多写放大越严重。那么一个表建多少索引合理这个没有绝对标准但我的经验是单表索引数量控制在5到6个以内联合索引尽量覆盖高频查询组合OLTP在线事务处理系统的表要精打细算能不建的索引坚决不建OLAP分析型系统则相反可以适当多建因为写入频率低、查询模式复杂。另外MySQL 8.0中索引是支持不可见索引invisible index的你可以把一个索引设置为不可见观察查询计划是否变化用来安全地评估删掉它会不会影响业务。这个特性我强烈推荐用起来比直接删索引安全得多。还有一个实用技巧用SHOW INDEX FROM table_name查看某个表已经存在的索引信息用SELECT * FROM sys.schema_unused_indexes直接列出从未被使用过的索引。我每次接手新项目都会先跑这条SQL一堆冗余索引立刻暴露。删掉这些吃灰索引写入性能往往会有明显提升。4. Python视角的索引优化从ORM到原生SQL的实操闭环4.1 你写的ORM代码可能正在悄悄坑你用Python操作MySQL多数人走的是SQLAlchemy或Django ORM。ORM的好处不用多说但它最大的问题在于你很难直观看到生成的SQL到底长什么样更谈不上针对索引特点去调整。我见过太多人写filter(user_id1001)觉得这就够了结果发现ORM生成的SQL里SELECT了所有字段或者加了一些MySQL无法用索引的写法。举个例子。Django ORM里如果你做这样的链式调用users User.objects.filter(user_name__startswith张).order_by(-created_at)ORM生成的SQL可能是WHERE user_name LIKE 张% ORDER BY created_at DESC。如果user_name上有前缀索引碰巧覆盖了这个左前缀条件那查询能用上索引但如果ORDER BY created_at不在索引里MySQL还得做一次额外的排序操作。这就是为什么即使是写ORM代码你也必须能预判底层SQL的形态。我的建议是开发阶段打开SQL日志让框架打印出每一条执行的SQL。SQLAlchemy可以通过echoTrue开启日志Django可以通过配置LOGGING打印django.db.backends的SQL语句。看到真实SQL后用EXPLAIN分析执行计划再判断索引是否需要调整。整个过程完全不玄学就是“看SQL查计划调索引”的循环。4.2 用EXPLAIN读懂MySQL的执行计划这是整个Day 43里最值钱的技能。拿到一条SQL不要急着猜直接在前面加EXPLAIN关键字跑一遍MySQL会告诉你它的执行计划。重点看以下几列type访问类型。从好到差大致是const、eq_ref、ref、range、index、ALL。看到ALL基本等于全表扫描必须警惕。key实际用到的索引名。如果为NULL说明这条SQL没用索引。rows预估扫描的行数。这个数字越小越好。Extra各种附加信息。如果出现Using filesort或Using temporary说明排序或分组没用到索引是优化重点如果出现Using index说明索引覆盖了查询不再回表这是理想状态。我在教学过程中最喜欢拿一个例子来说事。假设有这样一个查询EXPLAIN SELECT user_name, phone FROM users WHERE user_name 张三 AND status 1;假设表上有idx_user_name(user_name)和idx_status(status)两个单列索引。MySQL优化器只会选择一个索引来用另一个就被浪费了。你会发现key只显示idx_user_namerows可能是一个比较大的值因为还得靠status 1逐条过滤。这种情况下正确的做法是建联合索引(user_name, status)让B树直接同时过滤两个条件rows会大幅下降。需要留意的是EXPLAIN显示的rows是估算值不一定精确到每一行但它足以给你一个相对可靠的判断方向。注意EXPLAIN不执行查询只输出执行计划。真正线上排查时如果SQL本身很重请先EXPLAIN确认没有问题后再去执行真实查询避免一次慢查询拖垮数据库。4.3 覆盖索引实战把回表次数降为零前面我已经两次提到覆盖索引这里专门给一个Python场景的实操闭环。假设你有一个文章表articles(id, author_id, category, title, created_at, content)前端列表页最常见的SQL是SELECT id, author_id, title, created_at FROM articles WHERE category python ORDER BY created_at DESC LIMIT 20 OFFSET 0;如果直接在category上建单列索引MySQL靠索引定位到categorypython的所有主键然后每条都要回表取title、created_at最后再排序。如果这个分类下有几千篇文章回表几千次性能堪忧。更优的方案是建联合索引(category, created_at, id, author_id, title)——注意不只是过滤字段连SELECT的字段也一起放进去。这样MySQL可以在索引叶子节点直接取到全部所需字段不需要任何回表操作。EXPLAIN的Extra会显示Using index效率极高。当然这种“把SELECT字段全部塞进索引”的做法不适合所有查询。比如列表页要展示正文content这个超大字段你把整个content塞进索引里索引体积会膨胀到难以接受还不如老老实实回表。所以我的建议是覆盖索引用于高频列表页、统计页这类查询字段固定的场景不要为了“全覆盖”而把核心大字段塞进索引。4.4 Python脚本批量验证索引效果的完整流程光说不练假把式。我在Day 43的实操部分会让学生做一个完整的测试闭环用Python脚本对比加索引前后的查询耗时。这个方法也推荐你在自己的项目里做整个流程分四步第一步准备数据。用Python脚本往测试表里灌入至少50万到100万行数据模拟真实业务量级。建议使用INSERT ... ON DUPLICATE KEY UPDATE或者批量插入否则插入本身就会耗费很久。第二步关闭查询缓存并使用SQL_NO_CACHE关键字执行查询确保每次查询都是真实走磁盘/InnoDB缓冲池而不是缓存命中。Python代码里用time.perf_counter()计时重复执行10次取平均值降低偶然误差。第三步不加索引先跑一遍记录基准时间然后创建索引再跑一遍同样的查询记录优化时间。同时用EXPLAIN对比两次的执行计划把type、rows、Extra的变化记录下来。第四步也是容易被忽略的一步随机改变查询条件多测几组不同值验证索引在全范围内的稳定性。因为如果索引选择性差可能某些值快、某些值慢这不利于上线。我贴一段简化版的测试脚本片段用的是PyMySQL思路可以直接迁移到任何ORMimport time import pymysql conn pymysql.connect(host127.0.0.1, userroot, password***, databasetest_db) cursor conn.cursor() sql SELECT SQL_NO_CACHE id, user_name, phone FROM users WHERE user_name user_100 # 不加索引时先执行一次预热 cursor.execute(sql) start time.perf_counter() for _ in range(10): cursor.execute(sql) cursor.fetchall() print(无索引平均耗时:, (time.perf_counter() - start) / 10) # 创建索引 cursor.execute(ALTER TABLE users ADD INDEX idx_user_name (user_name)) conn.commit() # 再加索引后再执行一次 cursor.execute(sql) start time.perf_counter() for _ in range(10): cursor.execute(sql) cursor.fetchall() print(有索引平均耗时:, (time.perf_counter() - start) / 10)这里有一个容易踩的坑MySQL有查询缓存时第二次查询直接命中缓存耗时记录为0很多新手看到0就误以为索引无敌。所以一定要用SQL_NO_CACHE或者把MySQL的query_cache_type配置确认清楚。MySQL 8.0已经彻底移除了查询缓存反而省了这份操心。以上内容基于实测经验总结加索引前后耗时发生数量级变化时你会真正从心底信任这个机制。5. 索引失效的隐藏雷区与慢查询排查技巧实录5.1 那些“加了索引却不生效”的经典操作索引失效的高频原因我总结下来基本逃不开这几类每一条都是我在真实项目里反复碰过或带新人时常提的问题。第一类在索引字段上做计算或函数操作。比如WHERE YEAR(created_at) 2024哪怕created_at上有索引MySQL也无法直接利用因为它需要对每一行的created_at先执行函数计算再比较。正确写法是WHERE created_at 2024-01-01 AND created_at 2025-01-01。还有一种常见写法是WHERE DATE_FORMAT(created_at, %Y-%m-%d) 2024-06-01同样会让索引失效。函数操作是索引失效最普遍的原因之一。第二类隐式类型转换。字段是字符串类型但查询传了整型或者反过来。比如user_phone是VARCHAR你写WHERE user_phone 13812345678MySQL会先把字段转成数字类型做比较索引就废了。正确的做法是传字符串13812345678。这个问题在Python代码里尤其容易发生因为Pandas处理数据时数字类型非常容易被当成整型传进去。第三类LIKE左模糊查询。WHERE user_name LIKE %张三%由于最左前缀原则MySQL没法从前缀开始定位B树优势无从发挥。如果业务确实需要这种模糊搜索建议引入全文索引MySQL FULLTEXT或者用专门的搜索引擎组件而不是指望普通索引。第四类联合索引违反最左前缀。前面已详细说过这里只补充一个常见场景ORM生成的查询条件顺序往往跟你的联合索引顺序不一致。但注意SQL优化器会调整WHERE条件的顺序所以跟SQL书写顺序无关关键是“查询里有没有包含联合索引最左边的字段”。只要包含优化器基本能自适应。第五类也是容易被忽略的对索引列进行了隐式字符集转换。当两个表JOIN时字段类型相同但字符集不同MySQL会隐式转换导致索引失效。比如一个表用utf8mb4另一个表用latin1关联字段varchar类型时就会出现这种问题。建表时统一字符集是很多老项目翻车后不得不回头补的功课。5.2 慢查询日志与性能诊断的联动用法我很推荐把慢查询日志当作日常工具而不是出了事故才打开。配置也很简单在MySQL配置文件里设置slow_query_log ON slow_query_log_file /var/log/mysql/slow.log long_query_time 1 log_queries_not_using_indexes ONlong_query_time 1表示超过1秒的SQL会被记录。log_queries_not_using_indexes这个参数尤其有用哪怕SQL执行很快只要没使用索引也会被记录到慢日志里。这样我就可以定期扫一眼找出“没走索引但暂时还不慢”的潜在隐患在数据量变大之前提前优化。拿到慢日志文件之后可以用mysqldumpslow命令做汇总分析它会帮你把相似的SQL聚合在一起显示执行次数、平均耗时、总耗时。这样你就能快速定位“哪个SQL模板最值得优化”而不是盯着几十条散乱的日志看半天。另外在Python生态里你还可以把慢查询日志接入到日志采集系统按小时或按天统计TOP N慢SQL配合告警规则。我见过不少团队将这套数据对接到内部监控看板用趋势图判断索引优化是否有效。不过这一步作为Day 43的延伸就足够了核心还是先把单条SQL的执行计划读懂。5.3 常见问题速查表我把这天的内容浓缩成一张问题速查表方便你在实际工作中对照排查更直观一些现象可能原因解决思路查询很慢EXPLAIN显示typeALL没有可用索引或索引被函数/类型转换破坏检查WHERE条件字段是否有索引重写SQL避免函数操作走了索引但rows很大索引选择性差比如低区分度字段放前面调整联合索引顺序优先放等值高区分度字段出现Using filesort排序字段不在索引范围内尝试把ORDER BY字段加入联合索引或调整联合索引字段顺序出现Using temporaryGROUP BY或DISTINCT未能走索引检查分组字段是否满足联合索引最左前缀插入和更新变慢冗余索引太多导致写放大用sys.schema_unused_indexes找没用的索引并删除SQL里LIKE %xx% 走不了索引左模糊导致B树无法定位改用全文索引或外部搜索引擎这张表看起来简单但我每次接手新项目都会把它打印出来贴显示器旁边。排查慢SQL时先对号入座效率远高于靠感觉瞎改。5.4 一个真实场景的排查过程回放这里分享一个曾经排查过的问题细节略做脱敏处理但整体思路完全来自实际经历。当时某个后台管理的列表页一到月底就卡顿所有接口都变慢。翻慢日志发现基本都是同一条SQL模板对订单表按商户编号和创建时间过滤后做分页排序。EXPLAIN一眼看下来typeALL全表扫描几百秒。表上明明有“商户编号”的单列索引但优化器就是没用。原因很微妙商户编号的选择性太差——头部几个大商户的订单量占到全表的八成。优化器经过统计信息评估后发现利用这个单列索引扫描还需要回表大量数据成本甚至比全表扫描还高于是干脆选择放弃索引。后来我们把联合索引调整为(merchant_id, created_at, status)让大商户的查询能直接在索引里完成排序和过滤不再大量回表。执行计划从ALL变成range接口响应时间从原来的秒级降到百毫秒级。这个例子想说明的很重要的一点索引是否生效并不只看“有没有建”而是优化器评估“用这个索引是否划算”。因此统计数据过期时ANALYZE TABLE刷新一下统计信息有时能带来立竿见影的效果。我见过有人加索引没用时第一反应是删了重建其实可能只是统计信息没更新。MySQL还有一个贴心的能力8.0引入了EXPLAIN ANALYZE可以真实执行查询并输出每个步骤的实际耗时和行数。相比普通EXPLAIN的估算值这个工具在排查“优化器判断有误”的场景时更有说服力。6. 索引优化的边界什么时候该放弃索引引入别的方案6.1 分页深了和排序多了之后索引也扛不住索引不是银弹。特别是分页查询当页码不断加深时MySQL的常见实现是LIMIT 100000, 20——它会扫描前100020条数据然后丢掉前100000条只返回最后20条。就算有索引这个扫描量也会让性能直线下降。我曾见过一条本来30毫秒的分页查询翻到第5000页时直接变成4秒。处理深分页有几个常规方案一是延迟关联。先用覆盖索引快速查到目标行的主键再通过主键关联原表取完整数据SELECT t.* FROM orders t INNER JOIN ( SELECT id FROM orders WHERE status completed ORDER BY created_at DESC LIMIT 100000, 20 ) tmp ON t.id tmp.id;子查询里只要id和排序字段索引基本能覆盖扫描量比直接取全字段小得多。二是游标分页keyset pagination。不用OFFSET而是记住上一页最后一条记录的排序字段值下一页查询直接用WHERE created_at 上次的值定位。这种方式完全跳过了“扫描并丢弃”的开销数据量再大也稳定。Python后端做API时非常推荐这种方案只是前端需要配合改造一下分页交互逻辑。三是走物化视图或定时汇总表。如果列表页只是需要统计总数或者汇总数据不要直接查大表而是把中间结果定时同步到一张小表里应用层查小表。这就是拿空间换时间用一个小代价换取稳定的查询性能。6.2 当索引空间膨胀到难以接受时考虑什么索引的本质是冗余存储。如果一个表的某些索引体积已经接近甚至超过数据本身那就要警惕了。出现这种情况通常是因为索引字段过长或冗余索引太多。除了删除无用索引、改前缀索引之外还可以考虑压缩存储或者把超长文本字段挪到单独的扩展表只在主表保留短ID关联。MySQL 8.0支持索引页压缩但压缩和解压会消耗CPU不一定适合所有场景。我更推荐从源头控制索引体积能用数字主键关联就不要用UUID字符串能用小整型枚举字段就不要用VARCHAR存状态能缩短前缀长度就缩短。索引越小单次I/O能加载的索引条目越多整体查询效率越高。这个道理和“缓存友好性”是一样的。6.3 从索引优化延伸到缓存层Python开发者的进阶思路当你发现索引优化已经到了瓶颈比如单表数据量实在太大、索引膨胀又太厉害这时候就该考虑引入缓存层了。Python生态里最常见的方案是Redis缓存热点数据。比如用户信息的查询频率极高完全可以在第一次从MySQL查出后写入Redis并设置过期时间后续请求直接打Redis给MySQL减负。缓存层用得好的前提是“知道哪些数据是真正的高频热点”这又回到第一节说的数据驱动思路。不要凭空猜哪个接口热点高去翻监控、看访问量排行。我常常看到有人一上来就把所有接口全加缓存结果缓存命中率不到10%反而引入了数据一致性的复杂度。正确的姿势是先用索引优化把SQL本身做好再用缓存把顶级热点的数据库读压力降下来。顺序不能反——如果SQL本身是全表扫描缓存也只是把问题往后推而且缓存击穿时数据库照样会被打穿。这里顺带提一句在Python中操作Redis、设计缓存过期策略这些内容我在后面的Day里会系统展开。但索引这部分至少你应该建立起“MySQL负责持久化与一致性Redis负责扛读压力”的分工认知。注意缓存不是用来掩盖糟糕SQL设计的。先把自己能控制的数据库查询做到最优再谈缓存这是性能优化里最基本的职业素养。7. 这一天的学习路径怎么落实给自己的项目做一个索引优化小实验7.1 三步设计实验亲眼看索引的效果学到这里如果你只是读了没动手那再过两周基本只剩一个模糊印象。我建议你当天就做一个实验闭环整个流程控制在两小时以内。第一步选一张你项目里真实的业务大表或者创建一个至少50万行的测试表。如果能在真实业务表上做测试效果更直观——但要记得在测试库操作不要动生产环境。第二步列出这张表最高频的三条查询SQL分别用EXPLAIN分析执行计划记录type、rows、Extra。如果条件允许用Python脚本记录每条SQL的平均执行时间。第三步根据SQL模式设计联合索引建好后重新跑EXPLAIN和计时脚本对比优化前后的数据。做完之后把整张表所有索引梳理一遍尝试用sys.schema_unused_indexes找出无用索引并删除再对比一次写入性能的差异。这个实验做完你对索引的理解会从“死记硬背”变成“刻在骨子里”。我见过太多人问我“联合索引到底有什么用”只要带他做一遍这个实验他自己就能回答。7.2 常用管理SQL汇总为了方便我把Day 43涉及的常用SQL整理一下都是运维排查和索引管理中的高频操作。建议保存到本地笔记里随查随用-- 查看某张表的索引信息 SHOW INDEX FROM users; -- 查看数据库中从未被使用过的索引 SELECT * FROM sys.schema_unused_indexes; -- 分析索引效率刷新统计信息 ANALYZE TABLE users; -- 分析一条SQL的执行计划 EXPLAIN SELECT * FROM users WHERE user_name 张三; -- 真实执行并分析每个步骤的实际耗时MySQL 8.0 EXPLAIN ANALYZE SELECT * FROM users WHERE user_name 张三; -- 创建联合索引 ALTER TABLE users ADD INDEX idx_user_name_status (user_name, status); -- 删除冗余索引 ALTER TABLE users DROP INDEX idx_user_name;有些版本的MySQL默认没开启performance_schema或者sys库如果sys.schema_unused_indexes查不到可能需要在配置文件中开启performance_schema ON。不过大部分MySQL 5.7以上版本默认就已经开了直接查询基本没问题。7.3 常见误区索引建了终身有效最后想纠正一个非常普遍的思维误区索引不是建完就完事了。随着数据量增长、查询模式变化、字段值分布变化原有索引效果会逐渐衰减。我见过一个系统上线时加了索引跑了两年后查询又开始变慢结果一查新业务的SQL模式完全变了原来的联合索引已经无法覆盖现在的查询条件。这说明运维层面也应有定期索引健康巡检的机制。最简单的巡检方式每个月利用慢查询日志和sys.unused_indexes两个来源清理掉不再使用的索引同时为新增的慢SQL设计新索引。这个过程不需要DBA后端开发者完全能自己搞定。只要把它沉淀为固定动作你管理的数据库会一直保持健康而不是等报警了才救火。另外方案评审阶段也要留个心眼每次新上功能必须先确认核心SQL的执行计划再决定要不要建索引。如果功能还没上线时SQL就出现全表扫描别指望上线后用户量大了会自动变快那只会更糟。写到最后聊聊我对索引这件事的一点体会学Python到第43天把时间花在MySQL索引上乍看像是一门“偏科”的课。但说实话我后来写业务接口时感受最深的一点是性能问题的瓶颈从来不在Python语法层面而在数据存储和查询路径层面。你把列表推导用出花来也不如给数据库加一条合适的索引带来的提升明显。我最初学索引时犯过的最大错误就是“这个字段总是出现在WHERE里那就加个索引吧”结果索引越建越多反而拖慢了写入。后来才慢慢理解索引的优雅之处不在“多”而在“准”——每一棵B树都应该有它明确服务的SQL模式多余的结构本身就是负担。这个道理放到系统设计里同样成立任何冗余的加速手段都该有清晰的收益目标和退出机制。另外我建议你把今天学到的这些手段沉淀成一套自己的排查清单拿到慢SQL先EXPLAIN看type和rows再查联合索引匹配情况和回表成本最后才动手加索引或改SQL。这个过程做多了会变成肌肉记忆遇到任何数据库性能问题都不慌。Day 43的索引只是整个性能优化大厦的地基往后的缓存、分区、读写分离全都建立在这套底层认知之上。地基打牢了后面的东西自然顺理成章。 
长河君 · 2026/10/16 10:40:18 · 阅读 1.2k MySQL索引优化实战：从B+树到覆盖索引，彻底告别慢查询
1. 这一天的内容定位为什么是数据库索引而不是Python语法学Python学到第43天按常规路线早该撸Flask、写爬虫、做数据分析玩了。但我那套“100天计划”把Day 43专门留给MySQL索引原因很简单我见过太多用Python写业务的人CRUD溜得飞起一看数据库慢查询日志直接傻眼。你写的代码再优雅一条不带索引的SQL能让接口从20毫秒变成20秒到时候Python的任何优化技巧都救不了你。这一天不是让你背索引语法而是要你在脑子里建立起“数据在磁盘上到底怎么存储、MySQL怎么找到你想要的那条记录”这一整套逻辑。只有搞懂索引的底层组织方式和失效条件你才能避免瞎建索引——尤其是那种在一个表上堆了十几个单列索引、结果写入慢、查询也没变快的经典翻车操作。这篇内容适合三类人第一类是Python基础已经学完、开始写真实业务接口的开发者第二类是负责维护老项目、经常被慢SQL折磨的运维或后端开发者第三类是纯粹想搞懂“为什么加个索引查询就快了”的求知型选手。不需要你有DBA背景只要你写过SELECT、JOIN、WHERE这三种语句这天的内容就完全能跟上。我更想强调的是这里讨论的不是一个孤立的技术点。索引优化的思路——用空间换时间、用预处理换查询效率、用合理的结构减少无效计算——这种思维方式在你以后设计缓存策略、拆分服务、优化算法时完全通用。所以别只把Day 43当成一个“索引语法速查”它其实是你从“写代码”转变成“设计系统”的一道分水岭。2. 从“全表扫描”说起索引到底解决了什么问题2.1 没有索引时MySQL是怎么找数据的先想个最朴素的问题一张表里存了一万条用户记录你要找到手机号是138xxxx8888的那个人MySQL在没有索引的情况下是怎么干的答案是从头开始一条一条地读比对手机号字段直到找出所有匹配的记录。这个操作在MySQL里叫全表扫描Full Table Scan。全表扫描本身没毛病关键看表有多大。一万条还能忍一百万条呢一千万条呢每次查询都要把整张表的全部数据页从磁盘捞出来过一遍哪怕你只需要一条记录。我做过一次不算严谨的测试在一张600万行数据的表上做无索引等值查询冷启动状态下耗时接近4秒。同样的查询加了合适的索引之后耗时降到30毫秒以内。这个数量级的差距完全不是Python层面你能通过什么代码技巧弥补回来的。所以问题的本质是MySQL需要一个类似“目录”的机制不用翻完整本书就能定位到目标记录所在的页码。这个机制就是索引。这里涉及一个关键认知索引不是MySQL执行查询时的可选项而是数据读取路径的底层加速器。每一条SQL执行前优化器都会根据统计信息和索引情况决定“走哪条路”去取数据。没有索引就只有全表扫描一条路有索引优化器才可能有别的选择。2.2 索引的底层组织方式B树到底比顺序扫描强在哪MySQL InnoDB引擎里主键索引和辅助索引最常用的底层数据结构是B树。很多人一听B树就觉得复杂其实你可以把它想象成一个“多层目录”最顶层是根节点往下是内部节点最底层是叶子节点。关键特性有两个叶子节点之间通过链表串联且叶子节点直接存储整行数据聚簇索引或者索引字段加主键值辅助索引。B树的查找过程是典型的二分查找逐层收窄。以查找id500000为例假设树的高度是3层MySQL先从根节点读入一大块索引条目判断目标范围落在哪一层再往下走一层最后抵达叶子节点在链表里定位到具体位置。整个过程读入磁盘的数据页数量是常数级别的——通常3到4次I/O就能定位任意一行数据而不是从头扫描几百万条。这里有个特别重要的设计点B树的叶子节点是有序的而且它们之间是双向链表。这意味着范围查询比如WHERE age BETWEEN 20 AND 30只需要找到起点叶节点然后顺着链表往后读就行了。相比随机访问顺序读取在磁盘和内存层面都更友好。我在Day 43会专门强调这一点因为很多人的范围查询慢往往不是索引没建而是索引设计让范围扫描没法充分利用B树的链表特性。另外还有一点容易被忽略B树为了保持平衡插入、删除时可能会触发页分裂和页合并。换句话说索引不是建完就一劳永逸的频繁写入会让索引页不断变化碎片增多查询性能会逐渐退化。这也是为什么即使有索引也要定期做优化维护——后面我会专门讲。2.3 聚簇索引和辅助索引为什么说“回表”是性能杀手InnoDB里每张表都有一个聚簇索引也就是主键索引。聚簇索引的叶子节点直接存这一行的完整数据。也就是说当你用主键查询时MySQL在B树里找到叶子节点就等于找到了整行数据不需要再去别的地方取。这是最高效的查询路径。当你给某个非主键字段比如user_name创建索引时这个索引属于辅助索引也叫二级索引。辅助索引的叶子节点存储的是“索引字段的值 主键值”。也就是说如果你用user_name索引查询MySQL先在辅助索引的B树里找到匹配的叶子节点拿到主键值然后再拿着主键去聚簇索引的B树里再查一遍才能拿到完整行数据。这个过程就叫回表。回表不是必然的坏事一共就多一两次磁盘I/O。但如果你需要查出大量数据比如一个辅助索引匹配了10万行那就要回表10万次性能急剧下降。这就是我经常说的“辅助索引不是万能的要查全字段时得想想怎么避免回表”。最常见的解决方案是覆盖索引让辅助索引的叶子节点直接包含你要查询的所有字段。比如用户表里有id、user_name、phone三个字段你建立了(user_name, phone)联合索引那么SELECT phone FROM table WHERE user_name xxx根本不需要回表因为索引叶子节点里已经有你要的phone了。这个技巧MySQL术语叫Using index是优化查询的一个非常高效的方案。回表这个问题Python开发者经常会忽略因为ORM框架帮你生成的SQL往往都是SELECT *。我在后面第4小节会说不要盲目相信ORM默认行为必要时要手动指定返回列不仅是为了省流量更关键的是帮MySQL创造覆盖索引的机会。3. 索引设计实操创建索引前要回答的四个关键问题3.1 第一个问题你到底要优化哪一条SQL很多人一上来就是“给这个字段加个索引吧”完全没搞清这条SQL长什么样、执行路径是怎样的。这是典型的“没看病情先开药”。我在Day 43里反复强调一个原则索引不是给表建的是给SQL建的。同样的表WHERE条件不同、排序方式不同、关联字段不同需要索引的字段组合完全不同。举个例子。一张订单表orders字段有id, user_id, status, created_at, amount。如果你有一个高频查询是“查某个用户最近30天的已完成订单”那么SQL大概是SELECT * FROM orders WHERE user_id 1001 AND status completed AND created_at 2024-01-01 ORDER BY created_at DESC;这时候直接在user_id上建单列索引效果有限——因为MySQL还得去过滤status和created_at。更好的方案是建立一个联合索引(user_id, status, created_at)这样B树先按user_id定位用户再按status过滤最后直接利用索引的有序性完成排序连filesort都省了。这就是所谓“索引匹配最左前缀 索引排序”。所以我的建议是动手创建索引之前先把项目里最慢的20条SQL翻出来逐个分析它们的WHERE条件、ORDER BY字段、JOIN用的关联字段。然后根据高频SQL的模式来设计联合索引而不是凭空猜。没有慢SQL日志的话可以从MySQL的performance_schema或慢查询日志开启记录这方面的配置我第5节会提到。提示把慢查询日志打开long_query_time先设为1秒跑个业务高峰期再回头分析。这是所有索引优化工作的起点“数据驱动”不仅适用于产品决策也适用于索引设计。3.2 第二个问题联合索引的字段顺序怎么排联合索引的字段顺序是整个索引设计中最容易翻车的点。联合索引(a, b, c)本质上是先按a排序a相同再按b排序b相同再按c排序。这决定了它有两个重要特性最左前缀原则和索引排序能力。最左前缀原则的意思是如果查询条件里不包含最左边的字段那么这个联合索引基本不会被高效使用。例如(user_id, status, created_at)这个联合索引可以高效支持WHERE user_id ?、WHERE user_id ? AND status ?、WHERE user_id ? AND status ? AND created_at ?但如果你直接写WHERE status completedMySQL优化器极大概率会放弃这个联合索引或者只能扫索引但效率很低。字段顺序怎么排我的经验是三句话等值条件的字段放前面范围条件的字段放后面选择性高的字段放前面最常用于排序的字段放在最后或者紧随最后一个等值条件之后。以user_id 1001 AND status completed AND created_at 2024-01-01 ORDER BY created_at DESC来说user_id和status是等值条件放前面created_at既有范围又有排序放最后。这样的排列既满足了过滤条件又让索引天然支持排序。需要特别说明的是选择性区分度也很重要。如果一个字段只有“男/女”两种值区分度极低放在联合索引第一位意义不大——因为它无法快速收窄范围。假如是is_deleted这种标志字段更是直接别考虑放索引首位。当然如果查询全是等值条件区分度低的字段只要放在前面不会影响后续字段的过滤但这需要衡量确实无法绕开。3.3 第三个问题唯一索引、普通索引、前缀索引怎么选这一节我想集中把三类常见索引的特点说透因为它们各自的适用场景完全不同。唯一索引有两个作用一是约束——保证字段值不重复防止脏数据二是加速——它天然是等值查询的利器。如果你的业务本身要求某个字段必须唯一比如手机号、订单号那直接建唯一索引不要先建普通索引再加应用层校验。因为唯一索引是数据库层面的兜底靠Python代码校验总有漏网之鱼。普通索引就是没有任何约束的辅助索引适合不需要纯唯一性的场景。它的查询性能跟唯一索引差别不大但允许重复值因此插入时不强制检查唯一性写入成本略低一点。很多开发者说“这个字段业务上不会重复啊”但我通常建议不要依赖这种“自定义假设”该建唯一索引就建否则后面数据从多个接口汇入时很容易出现意想不到的重复。前缀索引则是个容易被忽视的利器。如果字段是长文本比如用户邮箱userexample.com、或者日志消息你不可能把整列都塞进索引里——浪费空间、降低I/O效率。这时可以只对前几个字符建索引比如ALTER TABLE users ADD INDEX idx_email_prefix (email(10))。但要注意前缀索引也是有代价的它无法做覆盖索引扫描因为在索引里存不了完整字段值查询时仍然需要回表拿真实数据。另外定前缀长度时要测试区分度选取一个能覆盖绝大多数数据的前缀长度否则优化效果会大打折扣。3.4 第四个问题一个表建多少索引才合理说一个我踩过的大坑早期维护某个后台管理系统的用户表陆陆续续为每个字段都建了索引加索引的时候感觉自己是“性能工程师”结果等业务起来后用户表写入直接变慢单条INSERT从1毫秒变成了十几毫秒并发一高更是明显变慢。原因很简单每个索引都是B树每次INSERT、UPDATE、DELETE不仅要写数据本身还要同步维护所有索引树。索引越多写放大越严重。那么一个表建多少索引合理这个没有绝对标准但我的经验是单表索引数量控制在5到6个以内联合索引尽量覆盖高频查询组合OLTP在线事务处理系统的表要精打细算能不建的索引坚决不建OLAP分析型系统则相反可以适当多建因为写入频率低、查询模式复杂。另外MySQL 8.0中索引是支持不可见索引invisible index的你可以把一个索引设置为不可见观察查询计划是否变化用来安全地评估删掉它会不会影响业务。这个特性我强烈推荐用起来比直接删索引安全得多。还有一个实用技巧用SHOW INDEX FROM table_name查看某个表已经存在的索引信息用SELECT * FROM sys.schema_unused_indexes直接列出从未被使用过的索引。我每次接手新项目都会先跑这条SQL一堆冗余索引立刻暴露。删掉这些吃灰索引写入性能往往会有明显提升。4. Python视角的索引优化从ORM到原生SQL的实操闭环4.1 你写的ORM代码可能正在悄悄坑你用Python操作MySQL多数人走的是SQLAlchemy或Django ORM。ORM的好处不用多说但它最大的问题在于你很难直观看到生成的SQL到底长什么样更谈不上针对索引特点去调整。我见过太多人写filter(user_id1001)觉得这就够了结果发现ORM生成的SQL里SELECT了所有字段或者加了一些MySQL无法用索引的写法。举个例子。Django ORM里如果你做这样的链式调用users User.objects.filter(user_name__startswith张).order_by(-created_at)ORM生成的SQL可能是WHERE user_name LIKE 张% ORDER BY created_at DESC。如果user_name上有前缀索引碰巧覆盖了这个左前缀条件那查询能用上索引但如果ORDER BY created_at不在索引里MySQL还得做一次额外的排序操作。这就是为什么即使是写ORM代码你也必须能预判底层SQL的形态。我的建议是开发阶段打开SQL日志让框架打印出每一条执行的SQL。SQLAlchemy可以通过echoTrue开启日志Django可以通过配置LOGGING打印django.db.backends的SQL语句。看到真实SQL后用EXPLAIN分析执行计划再判断索引是否需要调整。整个过程完全不玄学就是“看SQL查计划调索引”的循环。4.2 用EXPLAIN读懂MySQL的执行计划这是整个Day 43里最值钱的技能。拿到一条SQL不要急着猜直接在前面加EXPLAIN关键字跑一遍MySQL会告诉你它的执行计划。重点看以下几列type访问类型。从好到差大致是const、eq_ref、ref、range、index、ALL。看到ALL基本等于全表扫描必须警惕。key实际用到的索引名。如果为NULL说明这条SQL没用索引。rows预估扫描的行数。这个数字越小越好。Extra各种附加信息。如果出现Using filesort或Using temporary说明排序或分组没用到索引是优化重点如果出现Using index说明索引覆盖了查询不再回表这是理想状态。我在教学过程中最喜欢拿一个例子来说事。假设有这样一个查询EXPLAIN SELECT user_name, phone FROM users WHERE user_name 张三 AND status 1;假设表上有idx_user_name(user_name)和idx_status(status)两个单列索引。MySQL优化器只会选择一个索引来用另一个就被浪费了。你会发现key只显示idx_user_namerows可能是一个比较大的值因为还得靠status 1逐条过滤。这种情况下正确的做法是建联合索引(user_name, status)让B树直接同时过滤两个条件rows会大幅下降。需要留意的是EXPLAIN显示的rows是估算值不一定精确到每一行但它足以给你一个相对可靠的判断方向。注意EXPLAIN不执行查询只输出执行计划。真正线上排查时如果SQL本身很重请先EXPLAIN确认没有问题后再去执行真实查询避免一次慢查询拖垮数据库。4.3 覆盖索引实战把回表次数降为零前面我已经两次提到覆盖索引这里专门给一个Python场景的实操闭环。假设你有一个文章表articles(id, author_id, category, title, created_at, content)前端列表页最常见的SQL是SELECT id, author_id, title, created_at FROM articles WHERE category python ORDER BY created_at DESC LIMIT 20 OFFSET 0;如果直接在category上建单列索引MySQL靠索引定位到categorypython的所有主键然后每条都要回表取title、created_at最后再排序。如果这个分类下有几千篇文章回表几千次性能堪忧。更优的方案是建联合索引(category, created_at, id, author_id, title)——注意不只是过滤字段连SELECT的字段也一起放进去。这样MySQL可以在索引叶子节点直接取到全部所需字段不需要任何回表操作。EXPLAIN的Extra会显示Using index效率极高。当然这种“把SELECT字段全部塞进索引”的做法不适合所有查询。比如列表页要展示正文content这个超大字段你把整个content塞进索引里索引体积会膨胀到难以接受还不如老老实实回表。所以我的建议是覆盖索引用于高频列表页、统计页这类查询字段固定的场景不要为了“全覆盖”而把核心大字段塞进索引。4.4 Python脚本批量验证索引效果的完整流程光说不练假把式。我在Day 43的实操部分会让学生做一个完整的测试闭环用Python脚本对比加索引前后的查询耗时。这个方法也推荐你在自己的项目里做整个流程分四步第一步准备数据。用Python脚本往测试表里灌入至少50万到100万行数据模拟真实业务量级。建议使用INSERT ... ON DUPLICATE KEY UPDATE或者批量插入否则插入本身就会耗费很久。第二步关闭查询缓存并使用SQL_NO_CACHE关键字执行查询确保每次查询都是真实走磁盘/InnoDB缓冲池而不是缓存命中。Python代码里用time.perf_counter()计时重复执行10次取平均值降低偶然误差。第三步不加索引先跑一遍记录基准时间然后创建索引再跑一遍同样的查询记录优化时间。同时用EXPLAIN对比两次的执行计划把type、rows、Extra的变化记录下来。第四步也是容易被忽略的一步随机改变查询条件多测几组不同值验证索引在全范围内的稳定性。因为如果索引选择性差可能某些值快、某些值慢这不利于上线。我贴一段简化版的测试脚本片段用的是PyMySQL思路可以直接迁移到任何ORMimport time import pymysql conn pymysql.connect(host127.0.0.1, userroot, password***, databasetest_db) cursor conn.cursor() sql SELECT SQL_NO_CACHE id, user_name, phone FROM users WHERE user_name user_100 # 不加索引时先执行一次预热 cursor.execute(sql) start time.perf_counter() for _ in range(10): cursor.execute(sql) cursor.fetchall() print(无索引平均耗时:, (time.perf_counter() - start) / 10) # 创建索引 cursor.execute(ALTER TABLE users ADD INDEX idx_user_name (user_name)) conn.commit() # 再加索引后再执行一次 cursor.execute(sql) start time.perf_counter() for _ in range(10): cursor.execute(sql) cursor.fetchall() print(有索引平均耗时:, (time.perf_counter() - start) / 10)这里有一个容易踩的坑MySQL有查询缓存时第二次查询直接命中缓存耗时记录为0很多新手看到0就误以为索引无敌。所以一定要用SQL_NO_CACHE或者把MySQL的query_cache_type配置确认清楚。MySQL 8.0已经彻底移除了查询缓存反而省了这份操心。以上内容基于实测经验总结加索引前后耗时发生数量级变化时你会真正从心底信任这个机制。5. 索引失效的隐藏雷区与慢查询排查技巧实录5.1 那些“加了索引却不生效”的经典操作索引失效的高频原因我总结下来基本逃不开这几类每一条都是我在真实项目里反复碰过或带新人时常提的问题。第一类在索引字段上做计算或函数操作。比如WHERE YEAR(created_at) 2024哪怕created_at上有索引MySQL也无法直接利用因为它需要对每一行的created_at先执行函数计算再比较。正确写法是WHERE created_at 2024-01-01 AND created_at 2025-01-01。还有一种常见写法是WHERE DATE_FORMAT(created_at, %Y-%m-%d) 2024-06-01同样会让索引失效。函数操作是索引失效最普遍的原因之一。第二类隐式类型转换。字段是字符串类型但查询传了整型或者反过来。比如user_phone是VARCHAR你写WHERE user_phone 13812345678MySQL会先把字段转成数字类型做比较索引就废了。正确的做法是传字符串13812345678。这个问题在Python代码里尤其容易发生因为Pandas处理数据时数字类型非常容易被当成整型传进去。第三类LIKE左模糊查询。WHERE user_name LIKE %张三%由于最左前缀原则MySQL没法从前缀开始定位B树优势无从发挥。如果业务确实需要这种模糊搜索建议引入全文索引MySQL FULLTEXT或者用专门的搜索引擎组件而不是指望普通索引。第四类联合索引违反最左前缀。前面已详细说过这里只补充一个常见场景ORM生成的查询条件顺序往往跟你的联合索引顺序不一致。但注意SQL优化器会调整WHERE条件的顺序所以跟SQL书写顺序无关关键是“查询里有没有包含联合索引最左边的字段”。只要包含优化器基本能自适应。第五类也是容易被忽略的对索引列进行了隐式字符集转换。当两个表JOIN时字段类型相同但字符集不同MySQL会隐式转换导致索引失效。比如一个表用utf8mb4另一个表用latin1关联字段varchar类型时就会出现这种问题。建表时统一字符集是很多老项目翻车后不得不回头补的功课。5.2 慢查询日志与性能诊断的联动用法我很推荐把慢查询日志当作日常工具而不是出了事故才打开。配置也很简单在MySQL配置文件里设置slow_query_log ON slow_query_log_file /var/log/mysql/slow.log long_query_time 1 log_queries_not_using_indexes ONlong_query_time 1表示超过1秒的SQL会被记录。log_queries_not_using_indexes这个参数尤其有用哪怕SQL执行很快只要没使用索引也会被记录到慢日志里。这样我就可以定期扫一眼找出“没走索引但暂时还不慢”的潜在隐患在数据量变大之前提前优化。拿到慢日志文件之后可以用mysqldumpslow命令做汇总分析它会帮你把相似的SQL聚合在一起显示执行次数、平均耗时、总耗时。这样你就能快速定位“哪个SQL模板最值得优化”而不是盯着几十条散乱的日志看半天。另外在Python生态里你还可以把慢查询日志接入到日志采集系统按小时或按天统计TOP N慢SQL配合告警规则。我见过不少团队将这套数据对接到内部监控看板用趋势图判断索引优化是否有效。不过这一步作为Day 43的延伸就足够了核心还是先把单条SQL的执行计划读懂。5.3 常见问题速查表我把这天的内容浓缩成一张问题速查表方便你在实际工作中对照排查更直观一些现象可能原因解决思路查询很慢EXPLAIN显示typeALL没有可用索引或索引被函数/类型转换破坏检查WHERE条件字段是否有索引重写SQL避免函数操作走了索引但rows很大索引选择性差比如低区分度字段放前面调整联合索引顺序优先放等值高区分度字段出现Using filesort排序字段不在索引范围内尝试把ORDER BY字段加入联合索引或调整联合索引字段顺序出现Using temporaryGROUP BY或DISTINCT未能走索引检查分组字段是否满足联合索引最左前缀插入和更新变慢冗余索引太多导致写放大用sys.schema_unused_indexes找没用的索引并删除SQL里LIKE %xx% 走不了索引左模糊导致B树无法定位改用全文索引或外部搜索引擎这张表看起来简单但我每次接手新项目都会把它打印出来贴显示器旁边。排查慢SQL时先对号入座效率远高于靠感觉瞎改。5.4 一个真实场景的排查过程回放这里分享一个曾经排查过的问题细节略做脱敏处理但整体思路完全来自实际经历。当时某个后台管理的列表页一到月底就卡顿所有接口都变慢。翻慢日志发现基本都是同一条SQL模板对订单表按商户编号和创建时间过滤后做分页排序。EXPLAIN一眼看下来typeALL全表扫描几百秒。表上明明有“商户编号”的单列索引但优化器就是没用。原因很微妙商户编号的选择性太差——头部几个大商户的订单量占到全表的八成。优化器经过统计信息评估后发现利用这个单列索引扫描还需要回表大量数据成本甚至比全表扫描还高于是干脆选择放弃索引。后来我们把联合索引调整为(merchant_id, created_at, status)让大商户的查询能直接在索引里完成排序和过滤不再大量回表。执行计划从ALL变成range接口响应时间从原来的秒级降到百毫秒级。这个例子想说明的很重要的一点索引是否生效并不只看“有没有建”而是优化器评估“用这个索引是否划算”。因此统计数据过期时ANALYZE TABLE刷新一下统计信息有时能带来立竿见影的效果。我见过有人加索引没用时第一反应是删了重建其实可能只是统计信息没更新。MySQL还有一个贴心的能力8.0引入了EXPLAIN ANALYZE可以真实执行查询并输出每个步骤的实际耗时和行数。相比普通EXPLAIN的估算值这个工具在排查“优化器判断有误”的场景时更有说服力。6. 索引优化的边界什么时候该放弃索引引入别的方案6.1 分页深了和排序多了之后索引也扛不住索引不是银弹。特别是分页查询当页码不断加深时MySQL的常见实现是LIMIT 100000, 20——它会扫描前100020条数据然后丢掉前100000条只返回最后20条。就算有索引这个扫描量也会让性能直线下降。我曾见过一条本来30毫秒的分页查询翻到第5000页时直接变成4秒。处理深分页有几个常规方案一是延迟关联。先用覆盖索引快速查到目标行的主键再通过主键关联原表取完整数据SELECT t.* FROM orders t INNER JOIN ( SELECT id FROM orders WHERE status completed ORDER BY created_at DESC LIMIT 100000, 20 ) tmp ON t.id tmp.id;子查询里只要id和排序字段索引基本能覆盖扫描量比直接取全字段小得多。二是游标分页keyset pagination。不用OFFSET而是记住上一页最后一条记录的排序字段值下一页查询直接用WHERE created_at 上次的值定位。这种方式完全跳过了“扫描并丢弃”的开销数据量再大也稳定。Python后端做API时非常推荐这种方案只是前端需要配合改造一下分页交互逻辑。三是走物化视图或定时汇总表。如果列表页只是需要统计总数或者汇总数据不要直接查大表而是把中间结果定时同步到一张小表里应用层查小表。这就是拿空间换时间用一个小代价换取稳定的查询性能。6.2 当索引空间膨胀到难以接受时考虑什么索引的本质是冗余存储。如果一个表的某些索引体积已经接近甚至超过数据本身那就要警惕了。出现这种情况通常是因为索引字段过长或冗余索引太多。除了删除无用索引、改前缀索引之外还可以考虑压缩存储或者把超长文本字段挪到单独的扩展表只在主表保留短ID关联。MySQL 8.0支持索引页压缩但压缩和解压会消耗CPU不一定适合所有场景。我更推荐从源头控制索引体积能用数字主键关联就不要用UUID字符串能用小整型枚举字段就不要用VARCHAR存状态能缩短前缀长度就缩短。索引越小单次I/O能加载的索引条目越多整体查询效率越高。这个道理和“缓存友好性”是一样的。6.3 从索引优化延伸到缓存层Python开发者的进阶思路当你发现索引优化已经到了瓶颈比如单表数据量实在太大、索引膨胀又太厉害这时候就该考虑引入缓存层了。Python生态里最常见的方案是Redis缓存热点数据。比如用户信息的查询频率极高完全可以在第一次从MySQL查出后写入Redis并设置过期时间后续请求直接打Redis给MySQL减负。缓存层用得好的前提是“知道哪些数据是真正的高频热点”这又回到第一节说的数据驱动思路。不要凭空猜哪个接口热点高去翻监控、看访问量排行。我常常看到有人一上来就把所有接口全加缓存结果缓存命中率不到10%反而引入了数据一致性的复杂度。正确的姿势是先用索引优化把SQL本身做好再用缓存把顶级热点的数据库读压力降下来。顺序不能反——如果SQL本身是全表扫描缓存也只是把问题往后推而且缓存击穿时数据库照样会被打穿。这里顺带提一句在Python中操作Redis、设计缓存过期策略这些内容我在后面的Day里会系统展开。但索引这部分至少你应该建立起“MySQL负责持久化与一致性Redis负责扛读压力”的分工认知。注意缓存不是用来掩盖糟糕SQL设计的。先把自己能控制的数据库查询做到最优再谈缓存这是性能优化里最基本的职业素养。7. 这一天的学习路径怎么落实给自己的项目做一个索引优化小实验7.1 三步设计实验亲眼看索引的效果学到这里如果你只是读了没动手那再过两周基本只剩一个模糊印象。我建议你当天就做一个实验闭环整个流程控制在两小时以内。第一步选一张你项目里真实的业务大表或者创建一个至少50万行的测试表。如果能在真实业务表上做测试效果更直观——但要记得在测试库操作不要动生产环境。第二步列出这张表最高频的三条查询SQL分别用EXPLAIN分析执行计划记录type、rows、Extra。如果条件允许用Python脚本记录每条SQL的平均执行时间。第三步根据SQL模式设计联合索引建好后重新跑EXPLAIN和计时脚本对比优化前后的数据。做完之后把整张表所有索引梳理一遍尝试用sys.schema_unused_indexes找出无用索引并删除再对比一次写入性能的差异。这个实验做完你对索引的理解会从“死记硬背”变成“刻在骨子里”。我见过太多人问我“联合索引到底有什么用”只要带他做一遍这个实验他自己就能回答。7.2 常用管理SQL汇总为了方便我把Day 43涉及的常用SQL整理一下都是运维排查和索引管理中的高频操作。建议保存到本地笔记里随查随用-- 查看某张表的索引信息 SHOW INDEX FROM users; -- 查看数据库中从未被使用过的索引 SELECT * FROM sys.schema_unused_indexes; -- 分析索引效率刷新统计信息 ANALYZE TABLE users; -- 分析一条SQL的执行计划 EXPLAIN SELECT * FROM users WHERE user_name 张三; -- 真实执行并分析每个步骤的实际耗时MySQL 8.0 EXPLAIN ANALYZE SELECT * FROM users WHERE user_name 张三; -- 创建联合索引 ALTER TABLE users ADD INDEX idx_user_name_status (user_name, status); -- 删除冗余索引 ALTER TABLE users DROP INDEX idx_user_name;有些版本的MySQL默认没开启performance_schema或者sys库如果sys.schema_unused_indexes查不到可能需要在配置文件中开启performance_schema ON。不过大部分MySQL 5.7以上版本默认就已经开了直接查询基本没问题。7.3 常见误区索引建了终身有效最后想纠正一个非常普遍的思维误区索引不是建完就完事了。随着数据量增长、查询模式变化、字段值分布变化原有索引效果会逐渐衰减。我见过一个系统上线时加了索引跑了两年后查询又开始变慢结果一查新业务的SQL模式完全变了原来的联合索引已经无法覆盖现在的查询条件。这说明运维层面也应有定期索引健康巡检的机制。最简单的巡检方式每个月利用慢查询日志和sys.unused_indexes两个来源清理掉不再使用的索引同时为新增的慢SQL设计新索引。这个过程不需要DBA后端开发者完全能自己搞定。只要把它沉淀为固定动作你管理的数据库会一直保持健康而不是等报警了才救火。另外方案评审阶段也要留个心眼每次新上功能必须先确认核心SQL的执行计划再决定要不要建索引。如果功能还没上线时SQL就出现全表扫描别指望上线后用户量大了会自动变快那只会更糟。写到最后聊聊我对索引这件事的一点体会学Python到第43天把时间花在MySQL索引上乍看像是一门“偏科”的课。但说实话我后来写业务接口时感受最深的一点是性能问题的瓶颈从来不在Python语法层面而在数据存储和查询路径层面。你把列表推导用出花来也不如给数据库加一条合适的索引带来的提升明显。我最初学索引时犯过的最大错误就是“这个字段总是出现在WHERE里那就加个索引吧”结果索引越建越多反而拖慢了写入。后来才慢慢理解索引的优雅之处不在“多”而在“准”——每一棵B树都应该有它明确服务的SQL模式多余的结构本身就是负担。这个道理放到系统设计里同样成立任何冗余的加速手段都该有清晰的收益目标和退出机制。另外我建议你把今天学到的这些手段沉淀成一套自己的排查清单拿到慢SQL先EXPLAIN看type和rows再查联合索引匹配情况和回表成本最后才动手加索引或改SQL。这个过程做多了会变成肌肉记忆遇到任何数据库性能问题都不慌。Day 43的索引只是整个性能优化大厦的地基往后的缓存、分区、读写分离全都建立在这套底层认知之上。地基打牢了后面的东西自然顺理成章。