vlambda博客
学习文章列表

MySQL查询如何优化?

查询优化

EXPLAIN

在 MySQL 中可以使用 EXPLAIN 查看 SQL 执行计划,语法为:

EXPLAIN select * from table

使用 EXPLAIN 查看 SQL 的执行计划可以得知数据库运行该 SQL 时具体的执行顺序,使用了哪些表、索引,以及扫描了表数据的具体行数。

EXPLAIN 结果说明

在这里为大家整理了一张表格用来对 explain 执行计划的查询结果的解释说明,觉得有用可以收藏一下。

字段 说明
id select 查询语句的执行序号,包含一组数字,表示查询中执行 select 语句或操作表的执行顺序。不同的 id 值由大到小执行,相等的 id 值由上至下执行。
select_type select 语句的类型,可以有以下任意一种:
SIMPLE:简单的 select 查询,不使用关联查询或者子查询。
PRIMARY:查询语句中包含复杂的子部分,其外层查询就被标记为:PRIMARY。
UNION:连接查询的第 2 个或后面的查询语句。
DEPENDENT UNION:表示在 UNION 查询中的第二个或后面的 SELECT 语句,其取决于外面的查询。
UNION RESULT:表示做为连接查询的结果。
SUBQUERY:所有子查询的第 1 个 SELECT 语句。
DEPENDENT SUBQUERY:所有子查询的第 1 个 SELECT 语句,其取决于外面的查询。
DERIVEDSELECT( FROM 语句的子查询部分)
table 输出行数据所引用的具体表
type 表的连接类型。以下按照“从最佳类型到最差类型排序”:
system:全表仅有一行数据。
const:全表只匹配到一行数据,查询速度很快,常用于主键或者唯一索引的查询,是最优的查询方式。
eq_ref:对于每个来自于前面的表的行组合,从该表中读取一行。这可能是最好的联接类型,除了 const 类型。它用在主键或唯一索引的所有部分被联接使用。eq_ref 可以用于使用 = 比较带索引的列。
ref:查询条件索引既不是主键也不是唯一索引的情况。可使用 = 或 < 或 > 操作符与索引列关联。
ref_or_null:同 ref,但是添加了可以专门搜索包含 NULL 值的行。在解决子查询中经常使用该联接类型的优化。
上面这五种情况都是很理想的索引使用情况。
index_merge:该类型表示使用了索引合并的优化方法。在这种情况下,执行计划的 key 列包含了使用的索引清单,key_len 列包含了使用的索引的最长关键元素。
unique_subquery:该类型替换了下面形式的 IN 子查询的 ref: value in ( select primary_key from single_table where some_expr )。unique_subquery 是一个索引查找函数,可以完全替换子查询,效率更高。
index_subquery:该类型有点类似于 unique_subquery。可以代替 IN 查询,但只适合下列形式的子查询中的非唯一索引: value IN (select column from table where expr )。
range:使用一个索引检索一定范围的行数据。
index:索引树被扫描,因为索引文件通常比数据文件小,所以速度比 ALL 快一点。
ALL:进行完整的表扫描(性能最差)。
possible_keys 用来指明 MySQL 能使用的索引。如果为 NULL,说明没有使用索引,可以创建索引提高性能。
key 显示 MySQL 实际使用的索引,如果没有使用索引则显示 NULL。
可以强制使用所以或者忽略索引:
强制使用索引:USE INDEX(列名)
忽略使用索引:IGNORE INDEX(列名)
key_len 显示 MySQL 使用的索引长度。如果没有使用索引则显示长度也为 NULL。key_len 的值是确定了 MySQL 讲时间使用的索引长度。
ref 显示使用了哪些列或者常数,与 key 一起从表中选择行数据。
rows 显示 MySQL 执行语句时扫描的行数。多个查询语句之间显示的行数相乘,可以估算整个语句扫描的行数。值越小扫描行数越少,性能越高。
Extra 显示执行语句的详细信息:
Distinct:MySQL 在发现第 1 个匹配行后,停止为当前的行搜索更多的行。
Not exists:MySQL 能够对查询语句进行 LEFT JOIN 优化,在发现 1 个匹配 LEFT JOIN 标准的行后,不再为前面的的行组合在该表内检查更多的行。
range checked for each record(index map: #):MySQL 没有发现可用的索引,因此对从前面表中来的每一行组合,检查使用哪个索引,从表中返回行。这个效率比较低,但是比不使用索引好一点。
Using filesort:MySQL 需要额外的一次传递,来确定如何按照排序顺序检索行。
Using index:只使用索引中的信息,不需要通过索引回表就能得到期望的数据。
Using temporary:创建一个临时表来容纳数据结果。
Using where:表示使用索引后还需要通过索引回表查询数据。
Using sort_union:使用两个或者两个以上的索引提取数据,但无法确保每个索引会提取到一个自然排好序的结果,所以为了排除多余的数据,需要额外的处理。
Using union:使用两个或者两个以上的索引提取数据,分别取得已排序的结果,通过合并就可以获得正确结果。
Using intersect:使用两个或者两个以上的索引提取数据,分别取得已排序的结果,通过求交就可以获得正确结果。
Using index for group-by:表示使用一个索引,查询 GROUP BY 或 DISTINCT 的所有列。
重要字段说明

1. id 说明

从 id 字段看执行顺序。图一中 id 相同,执行顺序由上至下即先查询 sys_user u 表,再查询 sys_user_role ur 表,最后查询 sys_role r 表。

MySQL查询如何优化?

当 id 存在不相同的时候,如下图所示,先执行 id 值大的语句,id 值相等时依旧由上至下执行,实际执行顺序已用红色标注。

MySQL查询如何优化?

2. select_type 说明

表示查询中每个 select 子句的类型。有以下几种值:

  • SIMPLE:查询语句中不含子查询或者 UNION 查询

  • PRIMARY:查询语句中包含复杂的子部分,其外层查询就被标记为:PRIMARY。

MySQL查询如何优化?

  • UNION:连接查询的第 2 个或后面的查询语句。

MySQL查询如何优化?

  • DEPENDENT UNION:表示在 UNION 查询中的第二个或后面的 SELECT 语句,其取决于外面的查询。

  • UNION RESULT:表示做为连接查询的结果。

  • SUBQUERY:所有子查询的第 1 个 SELECT 语句。

MySQL查询如何优化?

  • DEPENDENT SUBQUERY:所有子查询的第 1 个 SELECT 语句,其取决于外面的查询。

  • DERIVED:SELECT(FROM 语句的子查询部分)

3. type 说明(重点)

  • system 类型:全表仅有一行数据。

  • const 类型:全表只匹配到一行数据,查询速度很快,常用于主键或者唯一索引的查询,是最优的查询方式。

MySQL查询如何优化?

  • eq_ref 类型:对于每个来自于前面的表的行组合,从该表中读取一行。这可能是最好的联接类型,除了 const 类型。它用在主键或唯一索引的所有部分被联接使用。eq_ref 可以用于使用 = 比较带索引的列。

MySQL查询如何优化?

  • ref 类型:查询条件索引既不是主键也不是唯一索引的情况。可使用 = 或 < 或 > 操作符与索引列关联。

MySQL查询如何优化?

  • ref_or_null 类型:同 ref,但是添加了可以专门搜索包含 NULL 值的行。在解决子查询中经常使用该联接类型的优化。

上面这五种情况都是很理想的索引使用情况。

  • index_merge 类型:该类型表示使用了索引合并的优化方法。在这种情况下,执行计划的 key 列包含了使用的索引清单,key_len 列包含了使用的索引的最长关键元素。

  • unique_subquery 类型:该类型替换了下面形式的 IN 子查询的 ref:value in ( select primary_key from single_table where some_expr )。unique_subquery 是一个索引查找函数,可以完全替换子查询,效率更高。

  • index_subquery 类型:该类型有点类似于 unique_subquery。可以代替 IN 查询,但只适合下列形式的子查询中的非唯一索引:value IN (select column from table where expr )

  • range 类型:使用一个索引检索一定范围的行数据。

MySQL查询如何优化?

  • index 类型:索引树被扫描,因为索引文件通常比数据文件小,所以速度比 ALL 快一点。

  • ALL 类型:进行完整的表扫描(性能最差)。

使用索引查询需要注意事项

索引可以提高查询的速度,但有些情况下是不生效的,需要注意!

数据库的索引是数据库管理系统中一个排序的数据结构(实际上也是一张表记录的数据),用来快速查询和更新数据库表中数据。通过索引事先排好顺序,再通过高效的查询算法如二分查找 O(log2n),就能加快定位数据。当然,一些特殊情况下索引设置不当也有可能影响查询效率。

为表设置索引是要付出代价的:一方面增加了存储空间,另一方面在写数据时要花时间修改索引表。这也是为什么索引不宜过多的原因。

设计索引也是有一些技巧的:

  • 在关键字段上设置主键或者唯一索引用于查询

  • 为经常排序、分组和查询的字段设计索引

  • 组合索引要将最常用的字段放在最左侧

  • 不建议在值重复率高的字段上建索引,往往不起效果

  • 控制索引数量,同一张表不要建过多的索引,一般 5 个索引以内即可

  • 根据业务发展设计索引

使用 LIKE 关键字的查询

对索引字段使用 LIKE 关键字进行查询时存在以下几种情况:

  • like "%xxx" 当以 % 开头时,索引失效。

  • like "%xxx%" 与上面情况一下,当以 % 开头时,索引失效。

  • like "xxx%" 不是以 % 开头时,索引生效。因为索引也是需要由第一个字符开始扫描的,所以索引长度也是影响索引效率的一个因素。

MySQL查询如何优化?

MySQL查询如何优化?

使用 OR 关键字的查询

查询语句的条件中只有 OR 关键字,且 OR 关键字前后的两个查询字段都是索引时,索引才会生效,否则,索引不生效。

  • id 是有索引的

  • mobile 是有索引的

  • name 是没有索引的

MySQL查询如何优化?

MySQL查询如何优化?

MySQL查询如何优化?

使用联合索引的查询

联合索引就是为多个字段创建一个索引,其排列顺序会影响索引是否生效。对于联合索引,只有查询条件中使用了这些字段中第一个字段时,索引才会生效。

MySQL查询如何优化?

MySQL查询如何优化?

子查询优化

什么叫子查询?子查询就是将一个 select 语句的结果作为另一个 select 语句的条件。这种写法很符合人的思维方式,先查一个值,再用这个值查询下一个值。但是很多时候执行效率并不高。

为什么它效率不高?MySQL 需要为子查询的结果创建临时表,查询完毕后再删除这些临时表。另外如果查询都是全表扫描,IO 次数最大能达到主表行数与子表行数的笛卡尔积。

优化方式:

  • 使用 JOIN 代替子查询,连接查询不用建立临时表。

  • 优化查询 SQL 的条件或者角度,减少扫描表的次数或者行数。

  • 拆分 SQL,通过多次查询组装结果。一次耗时过长的查询可能会让其他命令处在等待状态。

    关注我,获取更多干货