SQL语句优化
本文系统介绍了MySQL数据库优化的核心方法，重点聚焦索引优化策略。文章首先阐述了数据库优化的关键因素，包括表结构设计、索引创建、存储引擎选择等。然后详细讲解了执行计划（EXPLAIN）的各项指标及其优化意义，特别是type列的性能等级划分和Extra列的关键信息。针对索引优化，文章深入分析了索引合并、索引下推、复合索引的最左匹配原则等高级特性，并列举了11种常见的索引失效场景。最后提供了全面的索
系列文章目录
MySQL存储过程和触发器_mysql 存储过程-CSDN博客
目录
前言
本文系统介绍了MySQL数据库优化的核心方法，重点聚焦索引优化策略。文章首先阐述了数据库优化的关键因素，包括表结构设计、索引创建、存储引擎选择等。然后详细讲解了执行计划（EXPLAIN）的各项指标及其优化意义，特别是type列的性能等级划分和Extra列的关键信息。针对索引优化，文章深入分析了索引合并、索引下推、复合索引的最左匹配原则等高级特性，并列举了11种常见的索引失效场景。最后提供了全面的索引使用原则和16个常见优化问题的解答方案，涵盖从基础概念到实战技巧的完整知识体系。全文通过大量SQL示例和优化建议，为数据库性能调优提供了实用指导。
一、数据库优化概述
数据库软件级别影响性能有几个重要因素，例如表结构、查询语句和数据库配置。优化数据库性能时，首先要考虑软件级别的规则，再考虑系统层面的优化；
数据库优化的重要因素：
- 表结构是否正确：比如列是否指定了正确的数据类型；
- 表的类型是否正确：对于更新频繁的应用程序，通常有很多表，表中有少量的列；对于数据分析的应用程序，通常有少量的表，表中有很多列；
- 是否为频繁查询的列建立索引来提高查询效率；
- 是否为每个表选择了适当的存储引擎，并利用了不同存储引擎的优点；
- 每个表是否选择了适当的行格式，比如归档数据选用压缩格式，减少 IO 次数；
- 用于缓存的内存大小是否合适等；
本文主要讨论索引优化的方法。
创建索引的语句：
-- 增加唯一索引
alter table 表名 add unique 索引名 (列名);
-- 创建普通索引
create idnex 索引名 on 表名 (列名);
-- 创建复合索引
create index 索引名 on 表名 (列名1, 列名2, ...);二、压力测试工具
MySQL 自带的压力测试工具可以模拟多个客户端同时查询，用于模拟高并发的应用场景；
压测工具最终会返回执行 sql 语句的最大耗时，最小耗时和平均耗时；
可以在系统的命令行中输入：
// 使用 mysql 自带的压力测试工具进行压力测试
// 如果电脑上有多个 mysql 版本，要注意通过端口号来启动要使用的版本
// --concurrency=100 --iterations=100
// 客户端的数量为 100，每个客户端发送 100 个请求
// --number-of-queries=10000
// 最多不超过 10000 个请求
mysqlslap -uroot -p123456 -P 3308 --concurrency=100 --iterations=100 --create-schema="数据库名" --engine="innodb" --number-of-queries=10000 --query "具体的 sql 查询语句"注意：如果电脑上有多个 MySQL 版本，要设置不同的端口号，使用压测工具的时候，要注意写上端口号，用于测试指定版本的 MySQL 服务器中的数据库；
三、执行计划
1. 查看执行计划
执行计划的语法：
-- 将返回的结果按照执行计划字段分成多行显示
explain 具体的 sql 语句\G
-- 将返回的结果放在表格中显示
explain 具体的 sql 语句;在执行 sql 语句之前都可以使用执行计划分析 sql 语句的执行情况，以便优化 sql 语句。
执行计划并不会真正执行 sql，只是对 sql 进行分析，最终返回分析结果；
2. 执行计划字段说明
执行计划的各个字段：
id 列：select 标识符，查询语句中 select 的序号。如果整条语句中包含子查询和合并查询，则每个查询的序号依次递增；
select_type 列：
| select_type 值 | 说明 |
| SIMPLE | 简单的 select（不适用子查询或者合并查询） |
| PRIMARY | 外层查询 |
| UNION | UNION 中的第二个及之后的 select 语句 |
| UNION RESULT | UNION 的结果 |
| SUBQUERY | 子查询 |
| INSERT | insert 语句 |
| UPDATE | update 语句 |
| delete | delete 语句 |
table 列：查询时数据行所在表的名称，如果数据行所在的是临时表，显示查询临时表的 select 语句的 id 列的值；
partition 列：数据行所在的分区，非分区表值为 NULL；
possible_keys 列：sql 语句中可能用到的索引，如果值为 NULL 表示没有可用的索引，这时候需要检查 where 语句中用到的列，考虑是否创建索引提高查询性能；
key 列：表示 sql 语句中实际用到的索引，如果为 NULL，表示 sql 语句没有使用索引；
key_len 列：表示 sql 语句中使用索引的长度（字节为单位），如果 key 列值为 NULL， key_len 也为 NULL；
ref 列：显示查询过程中，哪些列或者常量和 key 中指定的索引进行比较。如果值是 func，则使用的值是某个函数的结果；
rows 列：表示 sql 语句执行时需要检查的行数，对于 InnoDB 表，这是一个估计值。数据越小，表示效率越高；
filtered 列：表示按照条件筛选的的数据行的百分比。值越大表示过滤的效率越高；如果是 100%，表示查询出来的结果集都是符合要求的不用过滤；如果是 50%，表示查询出来的结果集，只有 50% 的数据是符合要求的；
3. type 列详解
执行计划的字段中还有 type 列。
type 列描述了 sql 语句的查询性能，从高到低依次是：
system，const，eq_ref，ref，fulltext，ref_or_null，index_merge，unique_subquery，index_subquery，range，index，All；
system：一般在 MyISAM 引擎中，通常数据只有一行，不需要扫描，性能极高；
const：当查询中的条件通过主键索引或者唯一索引与常量进行比较时，结果最多有一个匹配的数据行，类型显示为 const；性能极高，且只会返回一行数据；
eq_ref：除 system 和 const 之外性能最高的连接类型。用于多表连接场景，表关联的条件是主键索引或者唯一非空索引使用 ‘=’ 进行索引列的比较，每行只匹配一条记录；
ref：sql 语句使用了普通索引，该索引列非空但不唯一，返回的结果可能是多行数据组成的结果集；
ref_or_null：类型与 ref 类似，索引列必须是可以为空的列，不唯一，包括对 null 值的检索；
index_merge：查询中使用多个索引，or 两边必须是单独索引，最终通过不同索引检索数据，然后对结果集进行合并，key_len 显示最长索引的长度；
unique_subquery：子查询中返回的是外层表的主键索引或唯一索引；
index_subquery：子查询中返回的是外层表的普通索引；
range：使用索引列进行范围查询，当使用 <>，>，>=，<，<=，isNull，<=>，between，like，in操作符，索引列与常量值比较时为 range；
index：扫描整棵索引树，而不全表扫描；
All：全表扫描，全表扫描是应极力避免的，通常要在查询的列上加索引；
4. Extra 详解
Extra 列中如果出现 Using filesort 和 Using temporary 将会对查询效率有比较严重的影响；
Using filesort 是使用文件排序，Using temporary 是使用临时表排序；
临时表是内存中的一片区域，内存占满了之后就会使用临时文件排序，此时就会发生磁盘的 IO，出现效率极低的情况；
对非索引，无序的列进行排序时，都会创建临时表，在临时表中排序；
索引数是一棵 B+ 树，所有的数据都保存在叶子节点上，叶子节点之间通过双向链表进行连接，本身数据就是有序的；因此使用索引列进行排序，不会创建临时表，效率会比较高；
Using temporary：使用临时表排序；
Using filesort：使用临时文件排序；
Using where：使用非索引列检索数据，表示进行全表扫描；当使用索引列检索数据，进行范围查找时，表示扫描索引树；
Using index：发生索引覆盖时，显示 Using index，表示这是一个高效查询；
回表查询和索引覆盖：
主键索引树的叶子节点里面保存的是表中的所有数据；普通索引树的叶子节点里面保存的是该列对应的值以及主键值；
使用普通索引查询时，如果要查询的列不在普通索引树的叶子节点中，就会拿到索引记录中主键的值，再去主表中查询所有的数据，找到要查询的列的值，最终返回结果集，这就是回表查询；
如果要查询的列，在索引树中可以找到，那么直接从索引树中返回结果，不需要再进行回表查询，这就是索引覆盖；
回表查询的效率小于不如索引覆盖的效率；
四、select 优化
select 优化是 MySQL 内部针对程序员的 sql 语句进行的优化；
1. where 子句优化
where 子句的优化适用于 select，update，delete 语句中的 where 子句；
1. 删除不必要的括号
-- 优化前
((a and b) and c or (((a and b) and (c and d))))
-- 优化后
(a and b and c) or (a and b and c and d)2. 常量合并
-- 优化前
(a < b and b = c) and a = 5
-- 优化后
b > 5 and b = c and a = 53. 常量条件去除
-- 优化前
(b >= 5 and b = 5) or (b = 6 and 5 = 5) or (b = 7 and 5 = 6)
-- 优化后
b >= 5 or b = 64. 删除无效或超出范围的值
-- 表结构如下，有一个 TINYINT 类型的列
create table t (c tinyint unsighed not null);
-- 优化前
select * from t where c < 256;
-- 优化后
select * from t where 1;5. 优先查询常量表
在 MySQL 中常量表分为两种类型：
System 表：只包含一行数据的表。通常用于优化查询，因为其数据是固定的，对查询性能影响极小；
Const 表：经过 where 语句中的限制条件筛选后只包含一行数据的表。这个条件通常是某列等于一个常量值，该列上通常有索引，可能是主键或唯一键。MySQL 可以通过索引快速定位该行。
以下操作都输入常量表操作：
-- 单表中用主键查询
select * from t where primary_key = 1;
-- 多表中用主键查询
select * from t1, t2 where t1.primary_key = 1 and t2.primary_key = t1.id;在查询中，会先通过常量比较，过滤出一个比较小的范围再进行其它的条件处理；
6. 其他优化
提前检测无效的常量表达式并返回空结果集；
如果不使用 group by 或者聚合函数，having 将与 where 合并；
没有 where 的单个表的 count(*) 直接从 MyISAM 和 MEMORY 表的表信息中检索；
7. 高效查询示例
-- 统计所有行数
select count(*) from tbl_name;
-- 聚合函数中使用索引列
select min(key_part1), max(key_part1) from tbl_name;
-- 复合索引时，where 条件使用索引列 1，聚合函数中使用其它索引列
select max(key_part2) from tbl_name where key_part1 = constant;
-- 排序时使用索引列，复合索引时，按照索引列的顺序排序
select key_part1, key_part2, ... from tbl_name order by key_part1, key_part2, ... limit 10;
-- 排序时使用索引列，复合索引时，按照索引列的顺序排序，可以为不同的列指定排序规则
select key_part1, key_part2, ... from tbl_name order by key_part1 desc, key_part2 dest,... limit 10;2. 范围优化
范围优化是非常常见的查询方式，在范围查询的列上建立索引可以有效的提高查询效率。
范围并不一定是一个区间，例如 id in (5, 10, 15, 20) 也表示一个范围；
1. 单部索引范围访问
单部索引的范围定义：
对于 B+ 树和 hash 索引使用 =，<=>， in() 操作符时，索引部分与常量值的比较是一个范围条件；
对于 B+ 树索引使用 >，<，>=，<=，between，<> 操作符时，索引部分与常量值的比较是一个范围条件；
like 的参数是一个不以通配符开头的常量字符串也是一个范围条件；
对于所有索引类型，多个范围条件用 or 或者 and 组合形成一个范围条件；
-- key_col 是表 t1 中定义的索引列
-- key_col 与常量区间进行比较
select * from t1 where key_col > 1 and key_col < 10;
-- key_col 与常量进行比较并且在一个范围中
select * from t1 where key_col = 1 or key_col in (15, 20, 25);
-- key_col 与字符串常量比较并且在开头不是通配符的字符串范围中
select * from t1 where key_col like 'ab%' or key_col between 'bar' and 'foo';优化器执行：
MySQL 尝试从 where 子句中为每个可能的索引提取范围条件。
提取过程中，不能用于构造范围的条件将被删除，产生重叠范围的条件将被合并，产生空范围的条件将被删除；
被删除的条件将用 true 或者 false 替换；
-- 优化前
select * from t1 where
(key1 < 'abc' and (key1 like 'abcde' or key1 like '%b')) or
(key1 < 'bar' and nonkey = 4) or
(key1 < 'uux' and key1 > 'z');
-- 优化中
select * from t1 where
(key1 < 'abc' and true) or
(key1 < 'bar' and true) or
(false);
-- 优化后
select * from t1 where key1 < 'bar';
2. 多部索引范围访问
多部索引使用复合索引的多个索引列进行范围查询；
B+ 树索引可以使用 =，<=>，is null，>，<，>=，<=，!=，<>，between，like 确定一个范围；
key_part1 = 'foo' and key_part2 >= 10 and key_part3 > 10当第一个条件比较使用 =，<=>，is null 时，优化器使用索引确定单个区间；
当某个条件使用 >，<，>=，<=，!=，<>，between，like 进行范围检索时，当前条件使用索引，后续条件全部不使用索引；
进行 !=，<> 运算时，优化器根据分析结果决定是否使用索引，如果全表扫描的效率更高，则不使用索引；
以上的条件表达式由于 key_part2 使用 >=，key_part3 不再使用索引，可以表示为：
('foo', 10, -inf) < (key_part1, key_part2, key_part3) < ('foo', +inf, +inf)在条件表达式中使用 or，如果覆盖的区间不同，将使用两个区间的并集；
-- 条件表达式
(key_part1 = 1 and key_part2 < 2) or (key_part1 > 5)
-- 条件区间
(1, -inf) < (key_part1, key_part2) < (1, 2)
(5, -inf) < (key_part1, key_part2) < (inf, inf) 3. 索引合并优化
多条件的查询语句中 where 通常会包含多个条件判断，以 and 或者 or 操作进行连接。在对一个表进行查询时，最多只能利用该表上的一个索引，其它条件需要在回表查询时进行判断；
当回表的记录很多，就需要进行大量的随机 IO，导致查询性能下降；
索引合并是一个表通过多个索引进行条件扫描，并将满足条件的多个主键集合取交集或者并集后，再进行回表，可以提升查询效率；
索引合并可以检索多个范围扫描的数据行，并将结果合并为 1 个；
索引合并仅合并单个表中的索引扫描，而不能跨表扫描；
合并的结果是多个索引扫描的并集或交集；
4. 索引下推优化
当使用索引进行范围查询或者 不以 % 开头的模糊查询时，先过滤出一个范围，如果没有索引下推，则根据主键回表后，再判断其它的 where 条件。
如果使用复合索引，且其它的条件可以使用复合索引中的列判断，则在存储引擎层面就进行过滤，而不用回表查询后再过滤，最终减少回表的次数，提升效率；
5. 外连接优化
外连接包括 left join 和 right join，MySQL 不支持全外连接；
对于左外连接，A left join B join_specification：
表 B 依赖表 A，以及表 A 依赖的所有表；
表 A 依赖在 left join 中使用的所有表，表 B 除外；
left join 条件用于决定如何从表 B 中检索数据行；
执行所有标准连接优化，先读取依赖的表，再读取当前表，如果存在循环依赖，则报错；
执行所有标准的 where 优化；
如果表 A 中有一行匹配 where 条件，表 B 中没有，则生成一个额外的 B 行，所有列都设置为 null；
right join 的实现类似于 left join，只是表的顺序不同，在执行过程中，右连接被转换为等效的左连接；
对于外连接来说，表的连接顺序不能随意交换，约束条件也不能随意下推。如果可以将外连接转换为内连接，那么就可以简化查询优化过程。如果可以通过约束条件把外连接中值为 NULL 数据行过滤掉，那么外连接就可以转化为内连接；
6. is null 优化
如果 where 子句对非空列使用 is null 作为条件查询，则表达式会被直接删除；
因此建议在建表的时候为非空的列，指定 not null 约束；
7. order by 优化
在对查询结果进行 order by 排序时，可能会使用到索引。由于索引是有序的，使用索引可以避免额外的排序；
使用复合索引进行排序时，只要排序的列按照创建索引的顺序，就会使用索引；
以下情况 MySQL 不使用索引处理 order by，但可以使用索引作为 where 条件：
对不同的索引使用 order by：
-- key1 和 key2 是两个不同的普通索引
select * from t1 order by key1, key2;
不符合最左原则：
select * from t1 order by key_part1, key_part3;where 中的索引与 order by 不一致：
select * from t1 where key2 = constant order by key1;order by 中对索引列进行转换和运算：
select * from t1 order by ABS(key);
select * from t1 order by -key;如果 Order by 不能使用索引避免额外的排序，MySQL 将执行 filesort 文件排序操作；
文件排序会用到额外的内存空间，可以通过 sort_buffer_size 来设置排序内存的大小，默认为 256 kb，可以通过选项文件修改；
-- 查看 sort_buffer_size
show variables like 'sort_buffer_size';
sort_buffer_size = 16M;如果结果集太大，超出 sort_buffer_size 内存限制，则文件排序会根据需要使用临时磁盘文件，这样会严重影响查询效率；
如果 explain 输出的 Extra 列包含 Using filesort，说明查询语句执行了文件排序，必须要优化；
8. group by 优化
在执行 group by 分组查询时，会把符合 where 条件的结果保存在一个新创建的临时表中，临时表每个分组字段的所有行都是连续的，然后再分离每个组应用聚合函数；由于索引的叶子节点中保存的数据是使用双向链表连接的，因此数据本身就是连续的，使用索引可以避免创建临时表；
-- 新建表并创建索引
create table t2 (
c1 varchar(10),
c2 varchar(10),
c3 varchar(10),
index idx_c1_c2 (c1, c2)
);以下分组查询，可以使用索引：
分组时使用索引列，并遵循最左原则：
select c1, c2 from t2 group by c1, c2;对同一个索引列使用 min() 或者 max()：
select c1, min(c2), max(c2) from t2 group by c1;如果使用其它聚合函数，必须加 distinct：
select count(distinct c1), sum(distinct c1) from t2;
select count(distinct c1, c2), count(distinct c2, c1) from t2;查询列表中的列都在 group by 中的列之前，之后的索引列在 where 中引用常量：
select c1, c2 from t2 where c1 < const group by c1, c2;
select max(c3), min(c3), c1, c2 from t2 where c2 > const group by c1, c2;
select c2 from t2 where c1 < const group by c1, c2;
select c1, c2 from t2 where c3 = const group by c1, c2;以下分组不使用索引：
使用了除 min()，max() 之外的聚合函数：
select c1, sum(c2) from t2 group by c1;group by 子句的列没有遵循最左原则：
select c2 from t2 group by c2, c3;9. distinct 优化
在大多数情况下，distinct 被视为 group by 的特殊情况；
10. 函数调用优化
MySQL 函数分为确定性函数和非确定性函数；
多次调用一个函数，并传入相同的参数，如果返回不同的结果，那么这个函数就是非确定性函数，比如 rand() 和 UUID()；
当查询中使用了非确定性函数，则无法使用索引；
当将非确定性函数的值保存在变量中，再在查询语句中使用，则可使用索引；
-- pow() 是确定性函数，可使用索引
select * from t where id = pow(1, 2);
-- floor 是一个不确定性函数，查询不使用索引
select * from t where id = floor(1 + rand() * 49);
-- 用变量接收不确定性函数的值，可以使用索引
set @keyval = floor(1 + rand() * 49);
select * from t where id = @keyval;五、索引失效
复合索引没遵循最左原则；
-- 复合索引 idx_mail_age_classId
explain select * from index_demo where age = 20\Gwhere 中有 or 条件，且条件列没有索引；
explain select * from index_demo where id = 1020000 or age = 20\G复合索引中第一个使用范围查询的条件或不以 % 开头的模糊查询之后的列不使用索引；
explain select * from index_demo where mail like 'user%' and age = 20\Glike 查询以 % 开头；
-- name 为索引列
explain select * from index_demo where name like '%00'\G类型隐式转换；
-- sn 为 varchar 类型的列，并创建了索引
explain select * from index_demo where sn = 1020000\Gwhere 子句中有表达式，或者函数；
explain select * from index_demo where id + 1 = 1020000\G
explain select * from index_demo where length(name) = 11\G使用 <>，not in：
explain select * from index_demo where id <> 1020000\G
explain select * from index_demo where id not in (10, 20, 30)\GMySQL 判断全表扫描比索引快时，不使用索引；
数据量极少不使用索引，或者过滤条件不佳时不使用索引；
六、索引的使用原则
1. 每张表必须有主键，推荐 bigint 类型且自增；
2. distinct，order by，group by，join 条件，where 条件的列加索引；
3. 对频繁进行数据操作的表，不要建立太多索引，因为维护索引也需要很大的成本；
4. 使用复合索引时，遵循最左原则；
5. 多表 join 有确定条件时，可以分成多个单表查询，在内存中合并结果集；
6. 避免在重复值太多的列上建立索引，比如大部分都是相同的值，查了索引之后还会回表，还不如全表扫描；
7. 使用指定的索引：
use index(index_name)：建议使用索引；
force index(index_name)：强制使用索引；
8. 创建索引时可以指定 asc，desc；
9. 创建一个复合索引 （a，b，c），相当于创建了 （a），（a， b），（a，bc）；
10. 创建索引之前，确保当前数据库实例没有未提交的大事务，防止数据锁死；
11. 对不常用的索引进行清理；
12. 删除无用的索引，避免对执行计划造成负面影响；
七、常见问题汇总
1. 关于数据库优化需要考虑哪几个层面的因素？
数据库软件级别的优化：配置内存，缓冲区大小，是否开启日志等；
使用级别的优化：
考虑数据表的设计：表的结构是否指定了正确的数据类型，表的类型是否大量的表少量的列，或者少量的表，表中有大量的列，是否增加数据冗余列或者遵守范式；
考虑如何创建索引，利用索引高效查询；
硬件层面：
考虑服务器的配置，内存，磁盘，网络带宽等；
2. 介绍一下什么是索引？索引的作用是什么？
索引类似书的目录，是一种高速检索数据的数据结构；使用索引进行查询，能够极大提高查询效率；
3. 索引用到了哪些数据结构？
索引用到了 B+ 树，所有的数据都保存在叶子节点中，叶子节点使用了双向链表，首位相连，便于范围查询；
哈希索引使用了哈希表的结构，进行精准匹配性能很高，但是不能进行范围查询或者模糊查询；
4. 索引是如何提升查询效率的？
创建索引后，数据库会生成一棵索引树，索引树的数据结构是 B+ 树，本身是一棵 N 叉搜索树，查找元素的时间复杂度是 logN；
B+ 树的优点：B+ 树将所有的数据都保存在叶子节点中，叶子节点中使用双向链表连接，相比于哈希表，非常适合范围查询；
B+ 树相比于 AVL树和红黑树来说，树的高度明显降低，大大减少了磁盘 IO 的次数，极大提升了效率；
B+ 树相比于 B- 树，所有的数据都保存在叶子节点中，进行范围查询或者模糊查询的时候，不需要进行回溯，减少了 IO 次数，因此比 B- 树效率更高；
5. 在哪些列上创建索引？
数据量很少的时候，不需要创建索引，数据量多了的时候需要在频繁查询的列上创建索引；
创建主键会默认生成主键索引；
建议在使用 distinct，order by，group by，join 条件和 where 条件的列上增加索引；
6. 索引越多越好吗？为什么？
索引不是越多越好。如果数据表经常需要插入数据，更新数据，删除数据，索引过多会明显降低数据插入，更新和删除的效率。因为每创建一个索引，就会生成一棵索引树，插入，更新和删除数据的时候也需要维护索引树，索引数量过多会给数据库带来极大的负担；
7. 如何查看索引是否生效？
可以使用执行计划，explain 语句进行查看；如果查询到的执行计划的 key 列的值为某个索引，说明索引生效了，如果查询到的是 NULL，说明索引没生效；
8. 知道执行计划吗？它的作用是什么？
执行计划使用 explain 关键字加上要执行的 sql 语句，用于分析要执行的 sql 语句的效率；根据返回的执行计划，便于针对 sql 语句进行优化；
9. 执行计划中各列的含义了解吗？
id 列表示 select 语句的序号；
select_type 列表示当前 sql 语句的类型；
table 列表示查询的数据表；
type 列表示 sql 语句的执行效率；
possible_keys 列表示 sql 中可能用到的索引；
key 列表示查询用到的索引；
key_len 列表示当前使用索引的长度；
ref 列表示查询过程中哪些列或者常量与索引列比较；
rows 列表示执行查询要检索的行数；
filtered 列表示检索出来的数据行中有效数据行数的占比；
Extra 列表示附加信息；
10. 执行计划的 type 列的含义是什么？包含哪些内容？
System：在 MyISAM 存储引擎中，如果数据表只有一行数据，那么执行计划的 type 列显示System，表示查询效率极高，这是最高的查询效率；
const 表示常量级别的查询，效率极高；
eq_ref 表示一对一关系的两个数据表，使用主键索引或者唯一索引进行比较使用等号进行比较的查询的效率；
ref 表示使用普通索引，返回的结果可能是多行数据组成的结果集；
ref_or_null 表示使用不唯一，可以为空的普通索引查询，返回的结果集中包含对 NULL 值的检索；
index_merge：表示索引合并，使用两个索引查询出来的主键进行合并，再进行回表查询；
unique_subquery：使用唯一键进行子查询；
index_subquery：使用普通索引进行子查询；
range 表示使用索引进行范围查询；
index：表示扫描整棵索引树，效率不高；
All 表示全表扫描，查询效率极低，必须要优化；
11. 如果 Extra 列中显示 Using index 意味着什么？
意味着当前发生了索引覆盖，是一个高效的查询。如果使用索引查询出来的数据包含了要查询的列，就会发生索引覆盖，不需要再进行回表查询；
常见的复合索引，查询的数据的列覆盖了要查询的列，就会索引覆盖；
如果没有覆盖到要查询的列，就会根据索引，把主键的值查出来，在使用主键去主表中再查询一次，这就是回表查询；
12. 如何使用 explain 命令来分析查询计划，并举例说明如何根据执行计划进行优化
explain 关键字加上要分析的 sql 语句，返回执行计划的内容。查看 type 列的值，确认查询效率是否高，查看 key 列的值是否使用了索引，如果没有使用索引，查看 possible_keys 列是否有 sql 语句中涉及到的可以使用的索引，如果有要使用索引查询，如果没有考虑给 where 条件或者 group by，order by，join 条件，distinct 条件中中使用的列创建索引；
13. 如何避免全表扫描？
同12；
14. 知道索引合并吗？
当使用两个不同的索引进行查询，会根据普通索引查询到主键的值，再把主键的值取交集或者并集，之后再进行回表查询，这就是索引合并；
15. 什么是索引下推？
当使用索引进行范围查询或者不易通配符开头的模糊查询时，先过滤出一个范围，再判断其它的 where 条件。如果使用复合索引，且其它条件可以使用复合索引的列判断，就可以在存储引擎层面进行过滤，不需要再回表查询之后进行过滤，减少过滤次数，提升效率；
16. 说一说索引失效的场景，以及最左匹配原则
使用复合索引没有遵循最左匹配原则；
最左匹配原则是使用复合索引要按照创建索引的顺序进行使用，如果没有使用索引的第一个列，先使用第二个列，就违反了最左匹配原则，索引失效；
where 中使用了 or 连接多个条件，其中有条件没有使用索引；
使用了复合索引，当前面的列使用了范围查询或者不以通配符开头的模糊查询，后面的索引不再生效；
like 查询以 % 开头；
发生隐式转换；
where 子句中表达式或者函数；
使用 <> 或者 not in 导致查询范围过大，MySQL 判断使用全表扫描效率更高，就会全表扫描，也会使索引失效；
17. 说一说 select count(*) 和 select count(1) 的区别
select count(*) 查询表中的记录行数是复合 SQL 语言的标准的；
InnoDB 存储引擎中 select count(*) 是通过扫描聚集索引实现查找记录行数的；
select count(*) 和 select count(1) 再查询性能上是没有区别的；
但是在 MyISAM 存储引擎中，在表中的一个字段中维护了记录的行数，使用 select count(*) 的查询效率是极高的；
有“AI”的1024 = 2048，欢迎大家加入2048 AI社区
更多推荐
19
0- 0
已为社区贡献2条内容
所有评论(0)