数据库调优-SQL语句优化

2. SQL语句优化

sql

复制代码

# 请问这两条SQL语句有什么区别呢？你来猜一猜那条SQL语句执行查询效果更好！ select id from sys_goods where goods_name='华为 HUAWEI 麦芒7 魅海蓝 6G+64G 全网通'; ​ select id from sys_goods where goods_id=1496732598527979521;

2.1. 查看SQL执行计划【explain】

MySQL 提供了一个 EXPLAIN 命令, 它可以对 SELECT 语句的执行计划进行分析, 并输出 SELECT 执行的 详细信息, 以供开发人员针对性优化。使用explain这个命令来查看一个这些SQL语句的执行计划，查看 该SQL语句有没有使用上了索引，有没有做全表扫描，这都可以通过explain命令来查看。

可以通过explain命令深入了解MySQL的基于开销的优化器，还可以获得很多可能被优化器考虑到的访 问策略的细节，以及当运行SQL语句时哪种策略预计会被优化器采用。

EXPLAIN 命令用法十分简单, 在 SELECT 语句前加上 explain 就可以了, 例如:

在MySQL中可以使用explain 查看SQL执行计划，用法：

sql

复制代码

EXPLAIN SELECT * FROM t_user;

1. id：SELECT识别符，这是SELECT查询序列号。
2. select_type（重要）：表示单位查询的查询类型，比如：普通查询、联合查询(union、union all)、子查询等复杂查询。
3. table：表示查询的表。
4. partitions：使用的哪些分区（对于非分区表值为null）。
5. type（重要）表示表的连接类型。
6. possible_keys：此次查询中可能选用的索引，一个或多个
7. key：查询真正使用到的索引
8. key_len：显示MySQL决定使用的索引长度。
9. ref：哪个字段或常数与 key 一起被使用
10. rows：显示此查询一共扫描了多少行，这个是一个估计值。不精确值
11. filtered: 表示此查询条件所过滤的数据的百分比
12. Extra（重要）：额外信息

哈哈，看到这么多的参数，可能，大部分人都直接放弃了！！！

xdm，别怕，重点的参数，我已经标出来了，也就3个，那我们就针对这3个参数进行详细说明吧。

select_type：查询类型

type：连接类型

Extra：额外信息

哈哈，开干！！！

2.2. 关键结果说明

2.2.1 select_type

单位查询的查询类型，比如：普通查询、联合查询(union、union all)、子查询等复杂查询。

有以下几种值：

• simple： 普通查询，表示不需要union操作或者不包含子查询的简单select查询。有连接查询时，外层的查询为simple。

• primary： 查询的主要部分， 一个需要union操作或者含有子查询的select，位于最外层的单位查询的select_type即为primary。

• union：连接查询 ，union连接的两个select查询，第一个查询是dervied派生表，除了第一个表外，第二个以后的表select_type都是union

  • derived在FROM列表中包含的子查询被标记为DERIVED（衍生），MySQL会递归执行这些子查询，把结果放在临时表中
  • union若第二个SELECT出现在UNION之后，则被标记为UNION：若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED
  • union result 从UNION表获取结果的SELECT

• dependent union： 依赖连接查询 ，与union一样，出现在union 或union all语句中，但是这个 查询要受到外部查询的影响
• subquery： 子查询 ，除了from字句中包含的子查询外，其他地方出现的子查询都可能是subquery
• dependent subquery： 依赖子查询， 与dependent union类似，表示这个subquery的查询要受到外部表查询的影响
• derived：派生表， from字句中出现的子查询，也叫做派生表，其他数据库中可能叫做内联视图或嵌套select

01-simple简单查询

csharp

复制代码

-- simple： 表示不需要union操作或者不包含子查询的简单select查询。有连接查询时，外层的查询为simple。 ​ explain select * from t_user;

02-union连接查询

sql

复制代码

-- union： union连接的两个select查询，第一个查询是dervied派生表，除了第一个表外，第二个以后的表select_type都是union -- 语句 1 explain select * from t_user a union select * from t_user b; ​ -- UNION 若第二个SELECT出现在UNION之后，则被标记为UNION： -- 若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED -- UNION RESULT 从UNION表获取结果的SELECT -- 语句 2 explain select * from ( select * from t_user a union select * from t_user b) c; -- DERIVED 在FROM列表中包含的子查询被标记为DERIVED（衍生），MySQL会递归执行这些子查询，把结果放在临时表中

sql

复制代码

-- dependent union：与union一样，出现在union 或union all语句中，但是这个查询要受到外部查询的影响 ​ explain select * from t_user a where a.id in (select id from t_user b union select id from t_user c);

03-subquery子查询

csharp

复制代码

-- subquery 除了from字句中包含的子查询外，其他地方出现的子查询都可能是subquery ​ explain select (select id from t_user where id=1) from t_user;

css

复制代码

-- dependent subquery 与dependent union类似，表示这个subquery的查询要受到外部表查询的影响 ​ explain select (select id from t_user a where a.id=b.id) from t_user b;

2.2.2 type

显示的是单位查询的 查询类型 或者理解为 访问类型 ，访问性能依次从好到差：

sql

复制代码

system const eq_ref ref fulltext ref_or_null unique_subquery index_subquery range index_merge index ALL：查询效率很低，而且耗CPU，在数据量大的情况下才会出现

• system ：表中 只有一行数据或者是空表 。等于系统表，这是const类型的特列，平时不会出现，可以忽略不计

• const（重要） ：使用 唯一索引或者主键 ，返回记录一定是 1 行记录的等值where条件时，通常type是const。其他数据库也叫做唯一索引扫描。

• eq_ref（重要） ： 唯一性索 引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描

• ref（重要） ： 非唯一性索 引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。

  • 组合索引
  • 非唯一索引

• fulltext ：全文索引检索，要注意，全文索引的优先级很高，若全文索引和普通索引同时存在时，mysql不管代价，优先选择使用全文索引

• ref_or_null ：与ref方法类似，只是增加了null值的比较。实际用的不多。

• unique_subquery ：用于where中的in形式子查询，子查询返回不重复值唯一值

• index_subquery ：用于in形式子查询使用到了辅助索引或者in常数列表，子查询可能返回重复值，可以使用索引将子查询去重。

• range（重要） ： 索引范围扫描 ，常见于使用>,<,is null,between ,in ,like等运算符的查询中。

• index_merge ：表示查询使用了两个以上的索引，最后取交集或者并集，常见and ，or的条件使用了不同的索引，官方排序这个在ref_or_null之后，但是实际上由于要读取所个索引，性能可能大部分时间都不如range

• index（重要） ：select结果列中使用到了索引，type会显示为index。 全部索引扫描 ，把索引从头到尾扫一遍，常见于使用索引列就可以处理不需要读取数据文件的查询、可以使用索引排序或者分组的查询。

• all（重要） ：这个就是全表扫描数据文件，然后再 在server层进行过滤 返回符合要求的记录。

• 除了all之外，其他的type都可以使用到索引
• 除了index_merge之外，其他的type只可以用到一个索引
• 最少要使用到range级别

详解：

01-const：

• 使用 唯一索引或者主键 ，返回记录一定是 1 行记录的等值where条件时，通常type是const。其他数据库也叫做唯一索引扫描。

csharp

复制代码

explain select * from t_user where id=1;

02-eq_ref：

• 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描

css

复制代码

explain select * from t_user a left join t_user b on a.id=b.id;

03-ref：

• 非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。

组合索引

csharp

复制代码

-- ref 组合索引 explain select * from t_user where username="llsydn"; ​ explain select * from t_user a left join t_user b on a.username=b.username;

非唯一索引

csharp

复制代码

-- ref 非唯一索引 explain select * from t_user where account='llsydn';

04-range：

• 索引范围扫描 ，常见于使用>,<,is null,between ,in ,like等运算符的查询中。

sql

复制代码

explain select * from t_user where account like 'lls%';

05-index：

• select结果列中使用到了索引，type会显示为index。 全部索引扫描 ，把索引从头到尾扫一遍，常见于使用索引列就可以处理不需要读取数据文件的查询、可以使用索引排序或者分组的查询。

csharp

复制代码

explain select account from t_user;

06-all：

• 这个就是全表扫描数据文件，然后再 在server层进行过滤 返回符合要求的记录。

csharp

复制代码

1 explain select * from t_user;

2.2.3 Extra

这个列包含不适合在其他列中显示的，但十分重要的额外的信息，这个列可以显示的信息非常多，有几十种。解释几个经常遇到的

01-Using filesort

• 使用了文件排序，说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”。这种操作需要优化sql。

sql

复制代码

-- 无索引，按照文件排序 explain select sort_number from t_menu order by sort_number;

sql

复制代码

-- 有索引，不按照文件排序 explain select sort_number from t_menu order by sort_number;

02-using index

表示相应的SELECT查询中使用到了索引，避免访问表的数据行，这种查询的效率很高！

• 如果同时出现Using Where ，索引在where之后，用作查询条件
• 如果没有同时出现Using Where ，索引在where之前，用作查询结果读取

vbnet

复制代码

-- 使用where，索引在where之后，用作查询条件 explain select sort_number from t_menu where id>0 order by sort_number;

vbnet

复制代码

-- 没有使用where，索引在where之前，用作查询结果读取 explain select sort_number from t_menu where id>0 order by sort_number;

03-using where

• 表示Mysql将对storage engine提取的结果进行过滤，过滤条件字段无索引；

csharp

复制代码

-- 只有where explain select * from t_menu where id>0;

04-Using join buffer

• 表明使用了连接缓存，比如说在查询的时候，多表join的次数非常多，那么将配置文件中的缓冲区的join buffer调大一些。

2.3. 为什么使用索引就能加快查询速度呢？

二叉树数据结构，与搜索算法时间复杂度：

B+Tree

面试常问题目：有没有做过数据库优化呀？我来问你一个特别简单的问题，查询的是有没有加过索引？

为什么要加索引？加上了索引之后速度就会变快吗？请您简单描述一下原因吧？

• 什么是索引：索引就是事先排好顺序，然后在查询的时候使用二分法等高校的查询算法来进行高效查询。
• 除了索引查询，肯定一般查询：这两者的差异是数量级的差异。
• 二分法或索引查询的时间复杂度O(log2N)，使用一般查询时间的复杂度是O(n)

举例来说：100w条数据，一般查询，平均 50 万条，比较 50 万次。如果用二分法，只需要不超过 20 次就可以找到数据！

二分法这种查找方式，效率是一般查询的2.5万倍！

哈哈，这里还是直接mark下吧！！！

 下面是配套资料，对于做【软件测试】的朋友来说应该是最全面最完整的备战仓库，这个仓库也陪伴我走过了最艰难的路程，希望也能帮助到你！

​

软件测试面试小程序
被百万人刷爆的软件测试题库！！！谁用谁知道！！！全网最全面试刷题小程序，手机就可以刷题，地铁上公交上，卷起来！

涵盖以下这些面试题板块：

1、软件测试基础理论 ，2、web,app，接口功能测试 ，3、网络 ，4、数据库 ，5、linux 6、web,app，接口自动化 ，7、性能测试 ，8、编程基础，9、hr面试题 10、开放性测试题，11、安全测试，12、计算机基础

​编辑资料获取方式 ：xiaobei_upup，添加时备注“csdn alex”