MySql语句优化

最新推荐文章于 2022-07-12 17:12:45 发布

哎呦，帅小伙哦

最新推荐文章于 2022-07-12 17:12:45 发布

阅读量151

点赞数 1

分类专栏：数据库文章标签： MySql

本文链接：https://blog.csdn.net/xiaoan08133192/article/details/103730172

版权

数据库专栏收录该内容

14 篇文章 0 订阅

订阅专栏

1、Mysql提供了一个EXPLAIN命令，它可以对SELECT语句进行分析，并输出SELECT执行的详细信息，以供开发人员针对性优化。EXPLAIN命令用法十分简单，在SELECT语句前加Explain就可以了，例如：EXPLAIN SELECT * from user_info WHERE id < 300;

2、Explain命令的输出格式如下：

id：SELECT查询的标识符。每个SELECT都会自动分配一个唯一的标识符

select_type：SELECT查询的类型

table：查询的是哪个表

partitions:匹配的分区

type：join类型

possible_keys：此次查询中可能选用的索引

key：此次查询中确切使用到的索引

key_len:此次查询中确切使用到的索引长度

ref：哪个字段或常数与key一起被使用

rows：显示此次查询一共扫描了多少行。这个是一个估计值

filterd：表示此查询条件所过滤的数据的百分比

extra：额外的信息

注意加粗的五项是开发人员需要重点关注的

3、对type的所有可能的类型进行进一步的剖析

通过这个值可以判断是全表扫描还是索引扫描

1）表中只有一条数据的时候，这个值是const；

2）eq_ref通常出现在多表的join查询，表示对于前表的每一个结果都只能匹配到后表的一行结果。并且查询的比较操作通

常是=，查询效率较高。

3） ref：此类型通常出现在多表的join查询，针对于非唯一或者非主键索引，或者是使用了最左前缀规则索引的查询。

4）range：表示使用索引范围查询，通过索引字段范围获取表中部分数据记录。这个类型通常出现在=，<>,>,>=,<,<=,IS NULL,<=>,BETWEEN, IN操作中。当type是range时，那么EXPLAIN输出的ref字段为NULL，并且key_len字段是此次查询中使用到的索引的最长的那个。

5）index：表示全索引扫描，和ALL类型类似，只不过ALL类型是全表扫描，而index类型则仅仅扫描所有的索引，而不扫描数据。index类型通常出现在：所要查询的数据直接在索引树中就可以获取到，而不是需要扫描数据。当是这种情况时，Extra字段会显示Using index。

6）ALL：表示全表扫描，这个类型是查询是性能最差的查询之一。通常来说，我们的查询不应该出现ALL类型的查询，因为这样的查询在数据量大的情况下，读数据库的性能是巨大的灾难。如果一个查询是ALL类型的查询，那么一般来说可以对响应的字段添加索引来避免。

4、如果排序字段没有用到索引，就尽量少排序

5、如果限制条件中其他字段没有索引，尽量少用or

or两边的字段中，如果有一个不是索引字段，而其他条件也不是索引字段，会造成该查询不走索引的情况。很多时候使用 union all 或者是union(必要的时候)的方式来代替“or”会得到更好的效果

6、区分in和exists， not in和not exists

select * from 表A where id in (select id from 表B)

上面sql语句相当于

select * from 表A where exists(select * from 表B where 表B.id=表A.id)

区分in和exists主要是造成了驱动顺序的改变（这是性能变化的关键），如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询。所以IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。
关于not in和not exists，推荐使用not exists，不仅仅是效率问题，not in可能存在逻辑问题。如何高效的写出一个替代not exists的sql语句？

原sql语句

select colname …  from A表 where a.id not in (select b.id from B表)

高效的sql语句

select colname …  from A表 Left join B表 on where a.id = b.id where b.id is null

7、避免在 where 子句中对字段进行 null 值判断

对于null的判断会导致引擎放弃使用索引而进行全表扫描

8、避免在where子句中对字段进行表达式操作

比如

select user_id,user_project from user_base where age*2=36;

中对字段就行了算术运算，这会造成引擎放弃使用索引，建议改成

select user_id,user_project from user_base where age=36/2;

9、对于联合索引来说，要遵守最左前缀法则

举列来说索引含有字段id,name,school，可以直接用id字段，也可以id,name这样的顺序，但是name;school都无法使用这个索引。所以在创建联合索引的时候一定要注意索引字段顺序，常用的查询字段放在最前面

10、关于JOIN优化

LEFT JOIN A表为驱动表
INNER JOIN MySQL会自动找出那个数据少的表作用驱动表
RIGHT JOIN B表为驱动表

注意：MySQL中没有full join，可以用以下方式来解决

select * from A left join B on B.name = A.name 
where B.name is null
 union all
select * from B;

尽量使用inner join，避免left join

参与联合查询的表至少为2张表，一般都存在大小之分。如果连接方式是inner join，在没有其他过滤条件的情况下MySQL会自动选择小表作为驱动表，但是left join在驱动表的选择上遵循的是左边驱动右边的原则，即left join左边的表名为驱动表。

合理利用索引

被驱动表的索引字段作为on的限制字段。

利用小表去驱动大表

从原理图能够直观的看出如果能够减少驱动表的话，减少嵌套循环中的循环次数，以减少 IO总量及CPU运算的次数。

巧用STRAIGHT_JOIN

inner join是由mysql选择驱动表，但是有些特殊情况需要选择另个表作为驱动表，比如有group by、order by等「Using filesort」、「Using temporary」时。STRAIGHT_JOIN来强制连接顺序，在STRAIGHT_JOIN左边的表名就是驱动表，右边则是被驱动表。在使用STRAIGHT_JOIN有个前提条件是该查询是内连接，也就是inner join。其他链接不推荐使用STRAIGHT_JOIN，否则可能造成查询结果不准确。