MySQL查询语言中的部分优化方法

最新推荐文章于 2024-06-04 22:03:36 发布

一米多的李同学

最新推荐文章于 2024-06-04 22:03:36 发布

阅读量142

点赞数 1

分类专栏： MySQL 文章标签： MySQL SQL优化

本文链接：https://blog.csdn.net/qq_31329893/article/details/89816492

版权

MySQL 专栏收录该内容

2 篇文章 1 订阅

订阅专栏

在实际开发与面试中，经常会遇到如何对MySQL数据查询脚本进行优化的问题。

首先我们需要了解数据查询类语言DQL（Data QueryLanguage）的主要组成结构。

A、一段最基本的查询：select x from table；

B、常用的查询结构有：select ? from table where ? group by ? having ? order by ?;

可以看出 select 和 from 是一段SQL中必须的内容，其他关键字是不是必要的，只是做了约束与过滤作用。

注：关键字的执行顺序与SQL中的书写顺序并不是一样的，简述一下B中关键字的执行顺序。

a、from ：从数据表检索数据；

b、where ：约束声明，对查询条件进行约束；

c、group by ：将约束后检索出的数据进行分组；

d、having ：过滤声明，将上面的结果进行一次筛选；

e、select ：查看结果集中数据结果；

f、order by ：通过指定的顺序返回数据结果。

下面说说具体的优化方案

一、基于SQL本身结构，进行优化：

1、尽量避免使用 select * from table，应该用具体的字段替代 “*”，解决不必要字段的返回。

2、在没有必要业务需求时，杜绝使用 group by 和 order by（或例子外的其他约束或过滤等条件）。

3、嵌套子查询可以修改为连接查询，如：

SELECT
    d.id,d.age,d.name,d.gender
FROM
    (
        SELECT
            ur.id,ur.age,ur.name,ur.gender
        FROM
            user ur
        WHERE
            ur.id = 0
        AND ur.name = '张三'
    ) u
INNER JOIN dept d ON d.u_id = u.id

可以修改为：

SELECT
  d.id,d.age,d.NAME,d.gender 
FROM
  dept d
  INNER JOIN USER ur ON d.u_id = u.id 
WHERE
  ur.id = 0 
  AND ur.NAME = '张三';

二、杜绝或尽量避免全表扫描：

1、从建表开始，每一个表建立主键，并考虑在 where 和 order by 等操作的字段上建立索引。

2、尽量避免在 where 子句中使用 like ，如：

select id from user where name like '%张三%';

3、慎重使用 in 和 not in，如：

select id from user where age in(12,21,35);

对于条件为连续的数值区间时，可以采用：

select id from user where age between 12 and 35;

三、杜绝或尽量避免索引失效的操作（面试中经常被问到：有哪些操作会导致索引失效）：

1、尽量避免在 where 子句中对字段进行函数、算术等表达式操作，如：

select id from user where age/2=20;
可以修改为：
select id from user where age=20*2;

2、尽量避免在 where 子句中使用!=或<>操作符。

3、尽量避免在 where 子句中使用 or 条件来进行数据过滤，如：

select id from user where name='张三' or name='李四'；

可以采用 union all 进行结果连接:

select id from user where name='张三'
union all  
select id from user where name='李四'；

4、尽量避免在 where 子句中对字段进行 null 值判断，可将需要查询的字段进行固定值设置，如设置为-1：

select id from user where age is null;
可以采用：
select id from user where age = -1;

5、另外在一些复杂的SQL中，数据库引擎会通过自身的算法导致索引失效，如一段SQL中，利用了主键和其他的复合索引，引擎在执行脚本后，自身会采用效率最高的执行，即可能直接采取主键索引，导致复合索引失效。

四、查看执行计划

在表数据过大或一段SQL脚本中嵌套了很多子查询后，通过查看执行计划（查询计划）后可以很好的进行SQL优化。

①、首先我执行了2个SQL查询脚本，第1个脚本进行了全表扫描，第2个脚本中通过dev_id 进行了数据过滤，且 dev_id 为主键。

select dev_id,cat_id,dev_code,bms_id from t_ibms_info_dev;

select dev_id,cat_id,dev_code,bms_id from t_ibms_info_dev where dev_id = '40288220525cb04201525dc24388005b';

②、查询分别计划如下：

1）、
在这里插入图片描述
2）、

③、可以在查询计划中明确的看出第1个SQL进行了全表扫描（其中的TYPE列为ALL），而第2个执行计划中为 const （当MySQL对某查询某部分进行优化，并转为一个常量时，使用这些访问类型。如果将主键置于where列表中，MySQL就能将该查询转化为一个常量。）

④、其他列补充为：

possible_keys：显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句。
key：实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MySQL会选择优化不足的索引。这种情况下，可以在SELECT语句中使用USE INDEX（indexname）来强制使用一个索引或者用IGNORE INDEX（indexname）来强制MySQL忽略索引。
key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好。
ref：显示索引的哪一列被使用了，如果可能的话，是一个常数。

在数据到达一定程度时，可以明显的体验到全表扫描与索引扫描的区别，当数据量足够庞大时，有索引与无索引进行独立的一条数据查询时，两者效率也是极不相同的。而在复杂SQL中，就可以通过查看查询计划，通过分析是哪一部分的查询导致效率较低，从而对SQL整体进行改造。

至此，在开发或面试中，都会有不小的帮助。

*** 文中只是例举了部分优化方法，如有错误，烦请指出。