1. 本节目标了解使用主键查询与不使用主键查询在性能上的区别掌握如何查看EXPLAIN执行计划掌握执行计划结果中各字段的含义掌握执行计划结果中type列所各指标的含义掌握执行计划结果中Extra列所各指标的含义掌握如何通过执行计划的结果进行 SQL 调优掌握发生索引覆盖的原因及对性能的影响掌握发生回表查询的原因及对性能的影响了解 MySQL 内部对不同SELECT查询场景进行优化的方式包括WHERE子句优化范围查询优化索引合并优化索引下推优化IS NULL优化ORDER BY优化GROUP BY优化DISTINCT优化函数调用优化掌握索引失效的场景掌握在实际数据操作在使用索引的原则2. 本节我们可以解决的问题说一下你了解的关于数据库优化要考虑哪几个层面的因素介绍一下什么是索引索引的作用是什么索引用到了哪些数据结构索引是如何升查询效率的什么时候应该创建索引在哪些列上创建索引索引越多越好吗为什么如何查看索引是否生效知道执行计划 (Explain) 吗它的作用是什么执行计划结果中各列的含义了解吗执行计划的type列的含义是什么包含哪些内容说说你熟悉的如果type开中显示const意味什么如果Extra列中显示using index意味什么谈谈如何使用EXPLAIN命令来分析查询执行计划并举例说明如何根据执行计划进行优化什么是索引覆盖什么是回表查询如何避免全表扫描知道索引合并 (优化) 吗什么是索引下推说一下索引失效的场景什么是最左匹配原则select count(*)与select count(1)的区别3. 概述SQL 调优只是完整数据库调优体系中的一个分支完整数据库调优包含多个优化维度服务器操作系统层面调优MySQL软件服务层面调优各类服务配置参数优化例如内存分配、IO 相关配置等SQL语句层面调优。日常开发工作中我们几乎每天都要编写SQL语句。拿到业务需求后写出执行高效的SQL是开发的核心能力同时我们需要一套标准化指标用来判断SQL执行效率、定位性能瓶颈这也是本篇核心讲解的内容。学习完章全部内容后我相信大家会对SQL调优建立完整、体系化的认知。关于数据库级别的优化一般有几个重要的因素表结构是否正确比如列是否指定了正确的数据类型。表的类型是否正确比如对于频繁更新的应用程序通常有很多表表中有少量的列对于数据分析的应用程序通常有少量的表表中有很多列。是否为适应的列建立索引来提升查询效率。是否为每个表选择了适当的存储引擎并利用了不同存储引擎的优点。每个表是否选择了适当的行格式比如归档数据可以选用压缩格式来减少空间提升 I/O 效率用于缓存的内存大小是否合适等等等等……本章节我们主要讨论索引优化的方法。4. 优化索引关于索引的基础概念可以查看MySQL 索引 [ 重点 ]_mysql 唯一 索引-CSDN博客https://rosetea.blog.csdn.net/article/details/149966414索引的底层数据结构是B树InnoDB存储引擎默认采用的索引结构就是B树。而索引最核心的作用就是能够有效提升SQL语句的查询效率。在日常开发中我们会为业务中频繁查询的列建立索引以此优化查询性能。但随之而来就衍生出一系列核心问题我们该遵循什么原则利用索引编写高效的查询语句索引会在哪些场景下失效如何判断一条SQL是否成功使用了索引索引生效后如何评判查询效率、判断是否还有优化空间以上所有问题都属于索引优化的核心范畴。4.1 构建测试数据在正式讲解索引优化规则之前我们需要有前置操作构建百万级测试数据。-- 修改SQL结束符 delimiter // -- 创建存储过程 CREATE PROCEDURE p_init_index_data () BEGIN -- 生成学号和主键 DECLARE id BIGINT DEFAULT 100000; -- 年龄 DECLARE age TINYINT DEFAULT 18; -- 性别 DECLARE gender BIGINT DEFAULT 1; -- 班级编号 DECLARE class_id BIGINT DEFAULT 1; -- 循环计算 DECLARE count INT DEFAULT 0; -- 创建表 DROP TABLE IF EXISTS index_demo; CREATE TABLE index_demo ( id bigint auto_increment, sn varchar(10) NOT NULL, name varchar(20) NOT NULL, mail VARCHAR(20), age TINYINT(1), gender TINYINT(1), password VARCHAR(36) NOT NULL, class_id bigint NOT NULL, create_time DATETIME NOT NULL, update_time DATETIME NOT NULL, PRIMARY KEY (id), index (class_id) ); -- 插入一条测试数据 INSERT INTO index_demo VALUES (100000, 100000, testUser, 100000qq.com, 18, 1, UUID(), 1, NOW(), NOW()); -- 循环构建数据 WHILE count 1000000 DO -- ID和学号 SET id : id 1; -- 年龄 IF count % 10 0 THEN SET age : age 1; END IF; IF age 50 THEN SET age : 16; END IF; -- 性别 IF count % 3 0 THEN SET gender : 0; ELSE SET gender : 1; END IF; -- 班级编号 SET class_id : class_id 1; IF class_id 10 THEN SET class_id : 1; END IF; -- 写入数据 INSERT INTO index_demo VALUES (id, id, CONCAT(user_,id), CONCAT(id,qq.com), age, gender, UUID(), class_id, NOW(), NOW()); -- 更新count SET count : count 1; END WHILE; END // -- 还原SQL结束符 delimiter ; -- 调用存储过程开始构建数据大约20 - 100分钟左右 CALL p_init_index_data();这段脚本执行耗时较长大家应该已经提前本地运行完成。接下来我们详细拆解这段存储过程的底层逻辑。首先脚本中定义了一系列初始变量对应数据表中各个字段的初始值包含自增ID、年龄、性别、班级编号等同时定义了计数器变量用于循环逻辑判断。该存储过程会自动创建一张测试数据表也是我们后续所有索引优化练习的专用表。这张表会批量生成100万条测试数据足够我们直观对比走索引和不走索引场景下的查询性能差异。这张测试表包含核心字段如下自增主键id、学号sn、姓名、邮箱、年龄、性别、密码password、班级编号class_id以及创建时间create_time、更新时间update_time。索引配置规则为主键id设置主键索引为class_id字段设置普通索引其余字段均无任何索引表结构简单清晰适配我们的索引对比测试需求。为了保证测试数据规整、可对照脚本设置了统一的数据拼装规则所有数据均为程序自动生成sn学号与主键id数值完全一致仅数据类型为字符串方便精准对照查询姓名固定前缀test_user_后缀拼接当前数据的id值保证姓名唯一邮箱前缀拼接id值后缀统一为qq.com规则统一年龄取值范围固定在16~50之间每写入10条数据年龄自增1达到50后重置为16循环往复性别仅包含0、1两个值每写入3条数据切换一次性别模拟真实男女数据分布password密码通过UUID()生成随机字符串保证每条数据密码唯一class_id班级编号取值范围1~10数值自增超过10后重置为1模拟多班级数据场景时间字段创建时间、更新时间均取数据写入时的系统当前时间。整体数据写入逻辑脚本手动维护自增id所有字段数值均依托id和循环计数器生成确保100万条数据规整、不重复完美适配性能测试场景。mysql select count(*) from index_demo; ---------- | count(*) | ---------- | 1000001 | ---------- 1 row in set (0.62 sec)数据初始化完成后我们基于这张百万级数据表直观对比主键索引查询和无索引普通列查询的性能差距。这里我们使用命令行客户端执行查询能精准展示SQL执行耗时。4.2 使用主键查询首先登录MySQL客户端切换至测试数据库topic01查询表中指定主键ID的数据。执行基于主键id的精准查询后可以看到在100万条数据的大表中查询耗时显示0.00 sec。这里的0.00秒并非无耗时而是代表耗时在10毫秒以内这个查询性能在大数据量表中是非常优秀的这就是主键索引的性能优势。使用主键查询一条记录观察耗时mysql select id, sn, name, mail, age, gender, class_id from index_demo where id 1020000; ------------------------------------------------------------------------ | id | sn | name | mail | age | gender | class_id | ------------------------------------------------------------------------ | 1020000 | 1020000 | user_1020000 | 1020000qq.com | 38 | 1 | 1 | ------------------------------------------------------------------------ 1 row in set (0.00 sec)4.3 使用非索引列查询接下来我们测试无索引列的查询效果。本次选用sn字段测试该字段既不是主键也没有建立任何普通索引属于纯普通字段。我们保持查询条件完全一致仅将where条件从主键id替换为字符串类型的sn执行相同数值的精准查询。使用非索引字段查询一条记录比如使用sn观察耗时mysql select id, sn, name, mail, age, gender, class_id from index_demo where sn 1020000; ------------------------------------------------------------------------ | id | sn | name | mail | age | gender | class_id | ------------------------------------------------------------------------ | 1020000 | 1020000 | user_1020000 | 1020000qq.com | 38 | 1 | 1 | ------------------------------------------------------------------------ 1 row in set (1.40 sec)执行后可以明显看到查询卡顿最终耗时达到1.40 sec。仅仅单条查询就消耗了1.40秒以上的时间。大家可以试想如果线上服务器面临上万、十万级别的用户并发访问每一条查询都消耗1秒以上服务器负载会直接拉满完全无法支撑业务运行。由此可见无索引列的查询效率极低是线上业务必须优化的场景。对应的核心优化方案也很明确如果某个字段在业务中频繁作为查询条件、频繁出现在where子句中就必须为该字段建立索引这是最基础、最高效的优化手段。可以看到使用非索引字段查询同样一条记录的耗时是使用主键列的 60 倍左右4.4 压测工具单条查询的性能差距已经非常明显接下来我们通过并发压测模拟线上多用户同时访问的场景进一步放大索引与无索引的性能差距。这里使用mysqlslap工具完成压测。mysqlslap是MySQL自带的压测工具无需额外下载安装随MySQL服务安装包自带。核心功能是模拟多个客户端并发访问数据库、批量执行指定SQL语句并自动统计平均耗时、最大耗时、最小耗时等性能数据非常适合做SQL性能对比测试。核心参数详解-u / -p数据库用户名、密码和常规MySQL登录参数一致--concurrency并发客户端数模拟同时访问数据库的客户端数量--iterations单客户端查询次数每个模拟客户端执行SQL的总次数--create-schema指定需要测试的目标数据库名称--engine指定数据库存储引擎常规填写InnoDB即可--number-of-queries全局最大查询次数限制总查询数并发数×单客户端次数超出该值时会被强制限制为该阈值--query核心参数双引号内填写需要压测的目标SQL语句。⚠️ 前置要求使用该工具必须提前配置MySQL环境变量确保命令行可以直接识别并运行mysql、mysqlslap指令。主键索引并发压测100并发、单客户端100次压测配置模拟100个并发客户端每个客户端执行100次主键查询总查询次数10000次。C:\Users\A1983mysqlslap -uroot -p123456 --concurrency100 --iterations100 --create-schematopic01 --queryselect id,sn,name,mail,age,gender,class_id from topic01.index_demo where id 1020000; mysqlslap: [Warning] Using a password on the command line interface can be insecure. Benchmark Average number of seconds to run all queries: 0.194 seconds Minimum number of seconds to run all queries: 0.062 seconds Maximum number of seconds to run all queries: 1.110 seconds Number of clients running queries: 100 Average number of queries per client: 1压测结果基于InnoDB引擎执行10000次查询整体性能优异平均耗时0.194秒最小耗时0.062秒最大耗时1.110秒。100个并发客户端全部执行完成查询效率完全满足线上业务需求。非索引列并发压测30并发、单客户端3次由于单条无索引查询耗时就达到1.59秒高并发场景耗时会指数级增长因此我们降低压测配置模拟30个并发客户端每个客户端仅执行3次普通列查询总查询次数90次。C:\Users\A1983mysqlslap -uroot -p123456 --concurrency30 --iterations3 --create-schematopic01 --queryselect id,sn,name,mail,age,gender,class_id from topic01.index_demo where sn 1020000; mysqlslap: [Warning] Using a password on the command line interface can be insecure. Benchmark Average number of seconds to run all queries: 9.328 seconds Minimum number of seconds to run all queries: 8.640 seconds Maximum number of seconds to run all queries: 9.766 seconds Number of clients running queries: 30 Average number of queries per client: 1压测过程中可通过show processlist;命令查看数据库实时线程能看到大量客户端同时执行sn字段的查询语句证明并发压测正常运行。C:\Users\A1983mysql -uroot -p123456 -e SHOW PROCESSLIST; mysql: [Warning] Using a password on the command line interface can be insecure. ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | User | Host | db | Command | Time | State | Info | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 5 | event_scheduler | localhost | NULL | Daemon | 191827 | Waiting on empty queue | NULL | | 41573 | root | localhost:29700 | NULL | Sleep | 1 | | NULL | | 41574 | root | localhost:29702 | topic01 | Query | 1 | executing | select id,sn,name,mail,age,gender,class_id from topic01.index_demo where sn 1020000 | | 41575 | root | localhost:29703 | topic01 | Query | 1 | executing | select id,sn,name,mail,age,gender,class_id from topic01.index_demo where sn 1020000 | | 41576 | root | localhost:29704 | topic01 | Query | 1 | executing | select id,sn,name,mail,age,gender,class_id from topic01.index_demo where sn 1020000 | | 41577 | root | localhost:29701 | topic01 | Query | 1 | executing | select id,sn,name,mail,age,gender,class_id from topic01.index_demo where sn 1020000 | | 41578 | root | localhost:29705 | topic01 | Query | 1 | executing | select id,sn,name,mail,age,gender,class_id from topic01.index_demo where sn 1020000 | | 41579 | root | localhost:29706 | topic01 | Query | 1 | executing | select id,sn,name,mail,age,gender,class_id from topic01.index_demo where sn 1020000 | | 41580 | root | localhost:29708 | topic01 | Query | 1 | executing ........压测结果仅90次查询平均耗时就达到9.328秒最大耗时9.766秒、最小耗时8.640秒性能极差完全无法适配线上并发场景。通过两组对照压测我们清晰看到索引对SQL性能的决定性影响。但随之而来出现了核心问题我们该如何精准优化低效SQL优化完成后如何判断优化是否成功评判优化效果的核心指标是什么有什么工具可以辅助我们分析SQL性能、定位瓶颈以上所有问题就是我们接下来正式进入SQL索引优化核心阶段要逐一讲解的内容4.5 执行计划EXPLAIN我们写完一条SQL语句后想在正式执行前判断这条语句是否能走索引、能不能有效利用索引该用什么方式确认 总不能直接把低效 SQL 丢到数据库里执行再长时间等待结果这种排查方式并不科学。 在MySQL中官方提供了专门的分析手段 ——执行计划。 通过执行计划我们可以完整分析当前SQL对索引的使用情况、整体运行逻辑。这里有一个关键知识点执行计划仅做语句逻辑分析不会真正执行这条 SQL只会输出分析后的报告结果。我们依靠这份报告就能针对性完成 SQL 优化。报告中会清晰展示这条语句使用了哪一个索引、索引对应字段如果完全没有使用索引也会明确标识出来。在执行SELECTDELETEINSERTREPLACE和UPDATE之前都可以用执行计划分析 SQL 语句的执行情况以便优化 SQL 语句。4.5.1 查看执行计划EXPLAIN使用方式十分简单直接在完整SQL语句最前方增加EXPLAIN关键字原查询语句一字不变仅前置新增关键字即可。我们结合之前两组测试案例实操演示主键索引查询、无索引普通列查询分别加上EXPLAIN查看返回报告。打开命令行客户端登录MySQL切换至测试库topic01主键查询语句末尾加上\G可以让结果按列分行展示可读性更高在语句开头添加EXPLAIN后复制到客户端执行得到第一份执行计划报告后续所有 SQL 优化都以这份报告内的字段数值作为判断依据报告能直观告诉我们语句是否走索引、执行效率高低字段含义后面逐一拆解。先执行主键索引查询的执行计划再修改WHERE条件为无索引字段sn再次执行EXPLAIN拿到第二份报告将两份报告并排对比直观区分二者差异。使用主键查询的执行计划-- 在查询语句着加入EXPLAIN关键字查看当前语句的执行计划 mysql EXPLAIN select id, sn, name, mail, age, gender, class_id from index_demo where id 1020000\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: index_demo partitions: NULL type: const possible_keys: PRIMARY key: PRIMARY key_len: 8 ref: const rows: 1 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.01 sec)使用非索引列查询的执行计划-- 在查询语句着加入EXPLAIN关键字查看当前语句的执行计划 mysql EXPLAIN select id, sn, name, mail, age, gender, class_id from index_demo where sn 1020000\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: index_demo partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 894343 filtered: 10.00 Extra: Using where 1 row in set, 1 warning (0.00 sec)两份报告前四列id、select_type、table、partitions完全一致从第五列type开始possible_keys、key、key_len、ref、rows、filtered、Extra全部出现明显区别这就是走索引和全表扫描两种场景的核心区分点。主键查询报告中possible_keys与key字段显示PRIMARY代表成功使用主键索引sn无索引查询报告中possible_keys与key均为NULL代表完全没有可用索引。下面我们逐行拆解执行计划返回表中每一列的含义先建立整体认知后续结合案例讲解如何依靠这些字段做优化。4.5.2 执行计划字段说明列名说明idSELECT标识符select_typeSELECT类型table查询的表partitions查询的分区typeJOIN类型possible_keys可能选择的索引key实际选择的索引key_len索引长度ref与索引比较的列rows估算要检查的行数filtered按条件筛选行的百分比Extra附加信息1.id列查询标识符id是SELECT语句的序号标识代表当前分析语句内包含多少条独立查询单条简单查询仅存在 1 个id1包含子查询、UNION联合查询语句会拆分为多条独立查询id数值按解析顺序依次递增。实操演示子查询场景构造嵌套子查询示例外层查询学生表WHERE条件匹配内层子查询查出的id一条语句拆分为两层独立查询。 执行EXPLAIN后报告生成两行记录EXPLAIN SELECT * FROM student WHERE id IN (SELECT id FROM student WHERE class_id 102);第一行id1外层主查询命中主键索引第二行id2内层子查询识别为子查询类型。 执行顺序优先执行内层子查询再执行外层查询id仅做编号区分不代表执行先后。mysql EXPLAIN - SELECT * FROM student - WHERE id IN (SELECT id FROM student WHERE class_id 102); ------------------------------------------------------------------------------------------------------------------------------------ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | ------------------------------------------------------------------------------------------------------------------------------------ | 1 | SIMPLE | student | NULL | ref | PRIMARY,class_id | class_id | 9 | const | 1 | 100.00 | Using index | | 1 | SIMPLE | student | NULL | eq_ref | PRIMARY | PRIMARY | 8 | topic01.student.id | 1 | 100.00 | NULL | ------------------------------------------------------------------------------------------------------------------------------------ 2 rows in set, 1 warning (0.02 sec)实操演示UNION联合查询场景构造student表与student1表UNION合并查询执行EXPLAIN后报告生成三行记录EXPLAIN SELECT id,name FROM student UNION SELECT id,name FROM student1;id1第一张表的基础查询id2第二张表的基础查询第三行table字段为union1,2代表将id1、id2两条查询的结果集做合并视为一次独立合并操作。 三条记录对应三次独立查询逻辑由此就能理解id列代表语句内独立查询的总次数。2.select_type列查询类型标识当前这条独立查询的分类高频取值如下SIMPLE简单查询无UNION、无嵌套子查询的单条SELECTPRIMARY最外层主查询搭配子查询、UNION出现SUBQUERYWHERE中嵌套的内层子查询UNIONUNION关键字后第二条及之后的查询UNION RESULTUNION合并后的临时结果集增删改语句INSERT/UPDATE/DELETE会匹配对应专属类型。SQL 优化核心聚焦各类查询语句因此以上查询相关类型需要重点掌握。3.table列查询数据表显示当前查询读取数据的表名特殊场景标识规则普通单表查询直接展示表名UNION合并查询union a,ba和b代表对应查询的id值代表合并ida与idb的结果集子查询派生表subquery NN为内层子查询的id编号。4.partitions列查询分区代表命中的表分区名称未做分区的普通数据表该字段固定为NULL。 企业生产环境中我们一般使用中间件做数据分片很少依赖MySQL原生分区功能因此本小节不展开讲解。5.type列访问类型优化核心关键字段这是判断 SQL 执行效率、指导优化方向最重要的字段字段取值代表数据读取方式性能好坏完全依靠该字段判断后续会单独开辟小节详细讲解所有取值。6.possible_keys列候选索引集合列出当前查询条件下数据库理论上能够选用的所有索引一张表可同时存在单列索引、复合索引若WHERE字段同时命中多类索引全部会展示在此列字段值为NULL代表当前查询没有任何可用索引需要评估是否为WHERE条件字段新增索引注意本列仅为候选集合列出的索引不代表最终一定会使用。7.key列实际使用索引代表语句真实执行时选中的索引是优化时重点观察字段值为NULL完全未使用任何索引若成功走索引possible_keys候选集合内必然包含本列展示的索引名possible_keys是候选全集key是最终选中的子集优化时优先查看key判断索引是否生效。8.key_len列索引占用字节长度代表本次查询使用索引的字节长度数值由索引字段的数据类型决定主键id为BIGINT类型固定占用 8 字节对应key_len8VARCHAR字符串类型长度由建表时指定的字符长度、字符集共同决定若key为NULL本列同步为NULL。9.ref列索引匹配对比值记录和索引字段做等值匹配的常量 / 列标识索引的匹配来源常量匹配id1020000字段显示const匹配常量时查询效率极高关联其他数据表字段展示关联表字段名函数生成值显示func代表匹配值由内置函数计算得出 如需查看具体调用的函数执行完EXPLAIN后运行SHOW WARNINGS;查看警告日志。10.rows列预估扫描行数数据库预估执行本条语句需要遍历的数据行数数值越小性能越好主键精准查询预估扫描行数为 1仅匹配一条数据效率极高无索引全表扫描预估扫描行数 98 万 需要遍历整张表所有数据性能极差 该数值是优化核心参考指标优化目标就是尽可能降低rows数值。11.filtered列过滤数据百分比代表预估扫描行数中经过WHERE条件过滤后保留数据的占比取值范围 0~100数值越大过滤效率越高100 代表扫描到的数据全部符合条件无需过滤数值越小过滤损耗越大例如无索引查询示例中filtered10.00代表 98 万扫描行仅 10% 满足条件90% 数据会直接丢弃大量无效扫描损耗性能 计算公式有效行数 rows×filtered÷ 100 优化时可以结合rows与filtered两个字段综合评估过滤损耗。12.Extra列附加执行信息补充展示额外执行行为例如无索引查询示例中显示Using where代表执行阶段需要通过WHERE过滤全表数据该字段与type列为两大核心优化字段后续单独小节完整讲解。以上就是执行计划返回结果中全部字段的基础定义下一小节我们会构造更多测试案例演示子查询、联合查询等不同场景下type、Extra列的各类取值夯实基础后正式落地实操 SQL 索引优化。