SQL 调优 [ 2 ]

type列详解EXPLAIN输出的type列描述了表是如何连接的性能从最好到最差的排序如下systemconsteq_refreffulltextref_or_nullindex_mergeunique_subqueryindex_subqueryrangeindexALL接下来我们对type列做详细讲解。我们都知道想要评估一条SQL语句的性能可以在 SQL 语句前面加上EXPLAIN生成执行计划做分析。 在执行计划返回的结果中优先关注type列展示的值它可以非常直观地告诉我们这条 SQL 的性能等级。上面已经列出type列全部可选值。type的性能等级从上到下依次递减从system一直到最后的ALL。最上方system性能极高最末尾ALL代表性能极差。我们做 SQL 优化的核心目标尽可能把type的值往性能更高的方向提升。只要不是ALL性能就已经得到改善当然越往上效果越好。重点关注几个高频取值system特殊场景才会出现InnoDB存储引擎基本见不到const常量精准匹配性能极高eq_ref、ref表关联、普通索引查询常用index扫描完整索引树尽量避免ALL全表扫描必须极力规避。之前演示案例已经见过部分类型使用主键id做等值查询type为const常量级精准匹配直接定位单行执行效率很高使用无索引字段查询即使传入常量条件数据库不知道数据存储位置触发全表扫描type为ALL性能很差。下面逐个讲解每一种type的触发场景同时配套演示案例。system仅当表里面只有一行数据时触发不需要扫描直接返回结果。⚠️注意该类型几乎只出现在MyISAM存储引擎InnoDB不会出现system。演示创建MyISAM引擎表写入 1 条数据执行EXPLAINtype显示system如果再插入第二条数据再次执行执行计划就会变成ALL换成InnoDB引擎哪怕表里只有 1 行原始数据直接查询type也不会是system如果给id做等值常量查询where id 1此时type变为const。小结system不用重点掌握InnoDB环境我们重点看const。表中只有一行数据不用任何扫描性能极高是const类型的特殊情况CREATE TABLE test_system_myisam ( id INT PRIMARY KEY, name VARCHAR(255) ) ENGINE MyISAM; INSERT INTO test_system_myisam VALUES (1, name1); -- MyISAM表中只有一行记录时typesystem explain select * from test_system_myisam; CREATE TABLE test_system_innodb ( id INT PRIMARY KEY, name VARCHAR(255) ) ENGINE innodb; INSERT INTO test_system_innodb VALUES (1, name1); -- 观察Innodb表并不会显示system explain select * from test_system_innodb;const触发条件通过主键索引 或者 唯一索引和常量做等值比较最多匹配一行数据。当查询中的条件通过主键索引或唯一索引与常量进行比较时结果最多有一个匹配的行类型显示为const这种类型查询性能极高且只会返回一行数据。主键本身就是唯一索引唯一索引允许字段为NULL但非空值全局唯一。当查询条件为确定常量最多匹配一行就得到const。这也是建表一定要设置主键的重要原因主键不仅标记行、校验重复查询时还可以拿到const级别的高性能。实操演示原有案例where id 1020000主键 常量等值typeconst。再看普通字段sn不给sn建索引执行查询耗时 1.67 秒typeALL全表扫描给sn建立唯一索引alter table index_demo add unique index uk_sn(sn);大表建立索引会消耗时间创建完成后再次EXPLAIN select ... where sn1020000type变为constkey显示使用了刚刚创建的唯一索引。优化思路业务上具备唯一性的查询字段优先创建唯一索引争取达到const级别不具备唯一性创建普通索引也比无索引的全表扫描好。查看表全部索引show index from index_demo;可以看到主键索引、刚刚创建的uk_sn、建表时创建的class_id索引。演示产出-- 使用主键查询 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 -- 类型为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.00 sec)为sn列创建唯一索引-- 为sn列创建唯一索引 mysql alter table index_demo add unique un_sn (sn) ; Query OK, 0 rows affected (1.98 sec) Records: 0 Duplicates: 0 Warnings: 0 -- 查看索引 mysql show index from index_demo\G *************************** 1. row *************************** Table: index_demo Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: id Collation: A Cardinality: 982651 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: Visible: YES Expression: NULL *************************** 2. row *************************** Table: index_demo Non_unique: 0 Key_name: un_sn Seq_in_index: 1 Column_name: sn Collation: A Cardinality: 980746 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: Visible: YES Expression: NULL *************************** 3. row *************************** Table: index_demo Non_unique: 1 Key_name: class_id Seq_in_index: 1 Column_name: class_id Collation: A Cardinality: 9 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: Visible: YES Expression: NULL 3 rows in set (0.00 sec)在WHERE子句中使用sn作为条件查询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: const -- 类型为const possible_keys: un_sn key: un_sn key_len: 42 ref: const rows: 1 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec) 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 (0.00 sec) -- 耗时明显改善eq_refeq_ref是除了system类型和const类型之外最高效的连接类型。应用于多表连接的场景表关联条件是主键索引或唯一非空索引时使用等号 () 进行索引列的比较每行只匹配一条记录。除system、const之外性能最高的连接类型多用于多表关联查询。触发条件表连接的关联字段使用主键索引 或者 非空唯一索引表 A 的一行记录在关联表 B 中有且仅有一行与之匹配属于一对一关系。举业务场景 拆分用户业务为两张表student保存学生基础信息account保存账号登录信息用户名、密码。 两张表一对一关联通过主键 / 唯一键完成关联。实操演示创建account测试表写入配套测试数据。 两种关联方式student.id account.id主键与主键关联student.id account.student_id主键与唯一外键字段关联。执行关联查询EXPLAIN观察输出关联表的type为eq_ref。原理通过唯一索引树寻址一对一匹配查找路径很短查询效率很高。注意MySQL 内部会有自主优化如果数据集很小数据库评估全表扫描比索引更快会放弃索引属于正常现象。 如果关联字段不是唯一索引就不会出现eq_ref会降级到ref。-- 创建用户表与student表是一对一关系 drop table if exists account; create table account ( id bigint primary key auto_increment, username varchar(20) not null, password varchar(32) not null, student_id bigint not null, UNIQUE (student_id) ); -- 写入数据 insert into account (username, password, student_id) values (user_001, pwd_123456, 1); insert into account (username, password, student_id) values (user_002, pwd_123456, 2); insert into account (username, password, student_id) values (user_003, pwd_123456, 3); insert into account (username, password, student_id) values (user_004, pwd_123456, 4); insert into account (username, password, student_id) values (user_005, pwd_123456, 5); insert into account (username, password, student_id) values (user_006, pwd_123456, 6); insert into account (username, password, student_id) values (user_007, pwd_123456, 7); insert into account (username, password, student_id) values (user_008, pwd_123456, 8); -- 执行计划 mysql explain select * from student s, account a where s.id a.id\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: a partitions: NULL type: ALL possible_keys: PRIMARY key: NULL key_len: NULL ref: NULL rows: 6 filtered: 100.00 Extra: NULL *************************** 2. row *************************** id: 1 select_type: SIMPLE table: s partitions: NULL type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 8 ref: topic01.a.id rows: 1 filtered: 100.00 Extra: NULL 2 rows in set, 1 warning (0.00 sec)eq‑ref 完整查找过程InnoDBB 树JOIN 执行逻辑MySQL 的 Nested‑Loop Join嵌套循环连接 分为驱动表、被驱动表驱动表先扫描的那张表取出一批行被驱动表拿驱动表每一行的关联值去被驱动表索引树上查找eq‑ref 出现在【被驱动表】以语句student s JOIN account a ON s.id a.student_id假设student作为驱动表account作为被驱动表步骤 1读取驱动表 studentMySQL 扫描驱动表 student依次取出每一行拿到当前行的s.id值。 示例拿到第一条s.id 1。步骤 2拿驱动表字段值去被驱动表 account 的唯一索引 B 树做等值查找account.student_id是UNIQUE唯一索引B 树。 使用s.id 1作为 key在 B 索引树做等值查询从根节点开始逐层向下走索引页唯一索引约束同一个 key 最多只能有 1 条记录找到叶子节点直接定位唯一一条满足student_id1的索引记录通过索引上保存的主键 id回表拿到 account 完整行数据balance 等。步骤 3拼接结果行将 student 行和 account 行拼接输出。步骤 4循环往复取出驱动表 student 下一行s.id2重复步骤 2‑3直到驱动表全部行处理完毕。为什么是eq_ref而不是 refeq_ref主键 / UNIQUE 唯一索引等值匹配每次查找最多返回 1 行。索引的唯一性保证只要找到不会再有其他行。ref普通二级索引可以返回0、1、多条记录允许多条匹配。typeeq_ref 的核心特征每次索引查找最多命中一条记录B 树路径只走一次不会扫描多条索引叶子。refref表示 SQL 语句中使用了普通索引返回的结果可能是多行组成的结果集。触发条件 索引列为非空但不唯一普通索引允许多条重复值查询会返回一个结果集而不是单行。业务场景举例学生表class_id同一个班级多个学生多条记录共用同一个class_id用户名字段name允许重名多条记录名字相同。实操演示class_id是普通索引执行where class_id 常量执行计划typeref。rows预估扫描行数 20 多万filtered100%代表扫描出来的数据全部满足条件不需要过滤。ref同样会出现在多表关联多对一、一对多的表关系关联字段是普通非唯一索引关联查询的type就是ref。 性能等级低于eq_ref。继续演示name字段 不给name建索引按名字查询执行速度很慢 创建普通索引create index idx_name on index_demo(name);索引构建完成后再次查询typeref查询耗时降低到 10ms 以内。小结ref代表普通非空索引生效返回多条匹配结果集是开发中非常常见的优化目标。-- 以class_id 为条件查询 mysql explain select * from index_demo where class_id 1\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: index_demo partitions: NULL type: ref possible_keys: class_id key: class_id key_len: 8 ref: const rows: 209914 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec)为name列创建普通索引-- 创建索引 mysql create index idx_name on index_demo(name); Query OK, 0 rows affected (2.22 sec) Records: 0 Duplicates: 0 Warnings: 0 -- 查看索引 mysql show index from index_demo\G ### 省略 *************************** 4. row *************************** Table: index_demo Non_unique: 1 Key_name: idx_name Seq_in_index: 1 Column_name: name Collation: A Cardinality: 982666 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: Visible: YES Expression: NULL 4 rows in set (0.00 sec)以name为条件查询并查看执行计划-- 执行计划 mysql explain select * from index_demo where name user_1020021\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: index_demo partitions: NULL type: ref -- 类型为ref possible_keys: idx_name key: idx_name key_len: 82 ref: const rows: 1 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec) mysql select id, sn, name, mail, age, gender, password, class_id from index_demo where name user_1020021\G *************************** 1. row *************************** id: 1020021 sn: 1020021 name: user_1020021 mail: 1020021qq.com age: 41 gender: 1 password: 6d29ccfa-7b10-11ef-aece-207bd2c8a157 class_id: 1 row in set (0.00 sec)fulltext使用全文索引时显示为FULLTEXT这里不做过多讨论ref_or_null类型与ref类似但包括对null值的检索索引列必须是可以为空的列和ref大体类似区别索引列允许为 NULL查询条件包含is null检索。触发条件索引字段没有非空约束可以存储NULL同时查询条件包含is null。实操演示修改表结构把name字段改为允许为空更新某行把name设置为NULL执行查询where namexxx or name is nullEXPLAIN查看此时typeref_or_null。如果去掉or name is null不再查询空值type变回ref。重点区分不带is null→ref查询同时包含普通值 NULL→ref_or_null演示产出先把name列修改为可以为空mysql alter table index_demo modify name varchar(20) null; Query OK, 0 rows affected (7.24 sec) Records: 0 Duplicates: 0 Warnings: 0修改某行数据的name为空mysql update index_demo set name null where id 100000; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0以name列为条件进行查询同时查找为null的列mysql explain select * from index_demo where name user_1020021 or name is null\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: index_demo partitions: NULL type: ref_or_null -- 类型为ref_or_null possible_keys: idx_name key: idx_name key_len: 83 ref: const rows: 2 filtered: 100.00 Extra: Using index condition 1 row in set, 1 warning (0.00 sec)index_merge在查询中使用了多个索引OR两边必须是单独索引最终通过不同索引检索数据然后对结果集进行合并Key_len显示最长的索引长度。触发条件一条SQL使用多个独立单列索引条件之间用OR连接MySQL 分别使用各个索引检索数据最后把多份结果做合并。注意必须是多个独立单列索引不是复合索引。演示案例条件where namexxx or id123。id是主键索引name是普通单列索引possible_keys同时列出两个索引key字段同时显示两个索引type为index_merge。执行逻辑两次索引检索拿到两份结果集再做合并。演示产出where条件中使用name和id同时进行查询mysql explain select * from index_demo where name user_1020021 or id 1030300\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: index_demo partitions: NULL type: index_merge -- 类型为index_merge possible_keys: PRIMARY,idx_name key: idx_name,PRIMARY key_len: 83,8 ref: NULL rows: 2 filtered: 100.00 Extra: Using union(idx_name,PRIMARY); Using where 1 row in set, 1 warning (0.00 sec)unique_subquery用eq_ref替换子查询中的IN表达式如下所示-- 子查询中返回的是外层表的主键索引或唯一索引 value IN (SELECT primary_key FROM single_table WHERE some_expr)index_subquery类似于unique_subquery只不过子查询中返回的是普通索引列表达式如下所示-- 子查询中返回的是外层表的普通索引 value IN (SELECT key_column FROM single_table WHERE some_expr)unique_subquery/index_subquery这两类子查询场景日常开发使用不多。unique_subquery子查询返回外层表主键 / 唯一索引eq_ref优化替换in子查询index_subquery子查询返回普通索引字段。理解前面eq_ref、ref之后这两个类型就很容易看懂这里不做过多演示。range使用索引列进行范围查询当使用、、、、、is NULL、、BETWEEN、LIKE或IN()操作符索引列与常量进行比较时为range:触发条件索引列与常量做范围比对。 支持运算符、、、、between、in、前缀固定的like模糊查询。几种典型场景区间范围id 10000 and id 20000in常量列表id in (1001,1002,1003)前缀固定模糊匹配name like user%前面字符串固定后面通配符。⚠️重要坑点like %xxx通配符写在最前面没有确定的范围起点索引失效。 此时不会走range会降级为index或者ALL。优化目标范围查询尽量优化到typerange保证索引生效。演示产出-- id在一个范围之内 mysql explain select * from index_demo where id 110000 and id 200000\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: index_demo partitions: NULL type: range possible_keys: PRIMARY key: PRIMARY key_len: 8 ref: NULL rows: 173552 filtered: 100.00 Extra: Using where 1 row in set, 1 warning (0.00 sec) -- 条件中使用IN mysql explain select * from index_demo where id in (100000, 110000, 120000)\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: index_demo partitions: NULL type: range possible_keys: PRIMARY key: PRIMARY key_len: 8 ref: NULL rows: 3 filtered: 100.00 Extra: Using where 1 row in set, 1 warning (0.00 sec) -- 条件中使用Like mysql explain select id from index_demo where name like user_10000%\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: index_demo partitions: NULL type: range possible_keys: idx_name key: idx_name key_len: 83 ref: NULL rows: 496178 filtered: 100.00 Extra: Using where; Using index 1 row in set, 1 warning (0.00 sec)index两种触发场景模糊查询通配符前置like %xxx索引无法定位起点完整扫描整个索引树仅使用索引字段做order by排序没有查询过滤条件扫描整棵索引树完成排序。注意 InnoDB 每创建一条索引就会生成一棵独立的 B 索引树。普通二级索引叶子节点存储索引列的值 主键值主键聚簇索引叶子保存整行全部数据。 索引不是越多越好索引越多增删改的时候需要维护多棵索引树会降低写入性能。演示order by sn仅排序无 where 条件typeindex扫描完整索引树。MySQL 会自主评估代价如果limit 10取少量数据扫描索引树代价小会使用索引typeindex如果取出的数据量很大数据库评估全表扫描更快就会放弃索引降级为ALL全表扫描。index是扫描索引树比ALL全表扫描略好但依然是完整遍历业务上尽量避免。mysql explain select * from index_demo order by sn limit 10\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: index_demo partitions: NULL type: index -- 类型为index possible_keys: NULL key: un_sn key_len: 42 ref: NULL rows: 10 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.01 sec)ALL最差的情况表示MySQL必须对全表进行逐行扫描才可以找到匹配行遇到这种情况通常建议在查询的列上加索引来避免全表扫描。MySQL 逐行扫描聚簇索引完整数据遍历整张表性能最差。常见触发场景查询条件的字段完全没有建立索引索引失效无法使用任何索引。补充不是 where 里出现无索引字段就一定是ALL。 如果前面已经通过索引过滤出来一个小的结果集再在这个结果集内对无索引字段过滤此时type不会是ALL只是filtered过滤效率变低。示例where age 18age无索引 →typeALL全表扫描where id between 100000 and 200000 and age 18主键先圈定范围再过滤 agetyperange不会全表扫描。查看优化后真实执行语句执行完EXPLAIN之后执行show warnings;可以看到 MySQL 优化器改写之后真正交给存储引擎执行的 SQL。演示产出mysql explain select * from index_demo where age 18\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: index_demo partitions: NULL type: ALL -- 类型为ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 992356 filtered: 10.00 Extra: Using where 1 row in set, 1 warning (0.00 sec)案例分析案例 1主键常量查询EXPLAIN select * from index_demo where id 1020000;id:1简单查询SIMPLEtable:index_demo无分区type:const常量级别possible_keys:PRIMARYkey:PRIMARY使用主键索引key_len:8BIGINT占 8 字节ref:const和常量做比对rows:1预估扫描 1 行filtered:100.00全部数据有效。 ✅ 高性能推荐这种写法。案例 2无索引字段查询EXPLAIN select * from index_demo where age 18;type:ALL全表扫描possible_keys:NULLkey:NULL无索引rows:982666预估扫描 90 多万行filtered:10.00过滤效率很低Extra:Using where。 ❌ 极差开发中尽量规避。拿到执行计划优先看type列 性能从高到低systemconsteq_refrefref_or_nullrangeindex_mergeindex_subquery/unique_subqueryindexALL业务尽量往const/eq_ref/ref/range靠拢index扫描完整索引树能改业务就尽量规避ALL全表扫描一定要极力避免。对于开始的两条SQL语句对照执行计划字段说明可以得到以下结果使用主键查询的语句查询标识符为 1查询的是index_demo表中的数据JOIN类型是常量级别可能用到的索引是主键索引实际用到的索引是主键索引索引长度为 8 字节索引比较的列是常量估算要检查的行数为 1 行按条件筛选率是 100%。使用非索引列查询的语句查询标识符为 1查询的是index_demo表中的数据JOIN类型是全表扫描没有用到索引估算要检查的行数为 982666 行按条件筛选率仅为 10%。可以看到一个用到了索引一个没有用到索引在真实执行相应查询语句的时候从结果耗时也可以看出用到索引的效率明显要高于没有用到索引的查询所以在编写SQL语句时尽量使用索引。