mysql的查询优化方法

约定不等于承诺〃 2022-12-21 10:48 153阅读 0赞

前言；一般mysql的性能优化包括 索引优化，查询优化，查询缓存，服务器设置优化，操作系统和硬件优化，应用层面优化（web服务器，缓存）等。本文主要是查询语句上面的优化，其它层面的优化技巧在此不做记录。内容主要是自己整理、总结以及网上获取的。

**查询的开销指标：**

执行时间 检查的行数 返回的行数

# 查询优化 #

## 1、索引优化 ##

**建立索引来优化查询**

如何选择合适的列建立索引？

1.在where、group by 、order by 、on从句出现的列  
2.索引字段越小越好  
3.离散度大的列放到联合索引的前面  
4.不要把主键建成索引  
注意：索引的正确建立 对于数据查询的速度影响很大，要根据开发的实际情况建立索引  
如何某些字段经常用在一起作为查询条件 那么就对这些字段建立多列索引。  
建索引 尽量要对重复率低的数据建立索引 这样子索引的效果就会比较明显  
对于在where、group by 、order by 、on从句出现的列，要建立单列索引，或者多列索引。

## 子查询优化 ##

通常把子查询优化为join查询，但是在优化时 要考虑是否存在一对多的关系。  
例如 子查询如下  
查询t的id值在t1表中相同的字段值

select * 
    from t 
    where t.id in 
    (select t1.tid from t1);

将其转换成连接查询

select t.id 
    from t 
    join t1 on t.id = t1.tid;

去重

select distinct t.id
    from t 
    join t1 on t.id = t1.tid;

## 下面说一下连接查询 ##

**sql中的连接查询有inner join(内连接）、left join(左连接)、right join（右连接）、full join（全连接）四种方式，它们之间其实并没有太大区别，仅仅是查询出来的结果有所不同。**

例如：我们要将客户基本信息表中没有任何订单的客户删除掉，就可以利用子查询先从销售信息表中将所有发出订单的客户id取出来，然后将结果传递给主查询，如下图所示：

delete from customerinfo where customerId not in(select customerid  from salesinfo)

如果使用连接(JOIN)来完成这个工作，速度将会快很多，尤其是当salesinfo表中对CustomerID建有索引的话，性能将会更好，查询如下：

select * from customerinfo left join salesinfo on customerinfo.customerid = salesinfo.customerid
      where salesinfo.customerid is null;

**推荐：能用inner join连接诶就用inner join连接。**

sql中的连接查询有inner join(内连接）、left join(左连接)、right join（右连接）、full join（全连接）四种方式，它们之间其实并没有太大区别，仅仅是查询出来的结果有所不同。

**例如我们有两张表：**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5MjEzOTY5_size_16_color_FFFFFF_t_70_pic_center]

## inner join（内连接） ##

在两张表进行连接查询时，只保留两张表中完全匹配的结果集。  
我们使用inner join对两张表进行连接查询，sql如下：

SELECT p.LastName, p.FirstName, o.OrderNo
    FROM Persons p
    INNER JOIN Orders o
    ON p.Id_P=o.Id_P and 1=1　　--用and连接多个条件
    ORDER BY p.LastName

执行结果  
![在这里插入图片描述][20201116015654818.jpg_pic_center]

此种连接方式Orders表中Id\_P字段在Persons表中找不到匹配的，则不会列出来。

注意：单纯的select \* from a,b是笛卡尔乘积。比如a表有5条数据，b表有3条数据，那么最后的结果有5\*3=15条数据。

但是如果对两个表进行关联:select \* from a,b where a.id = b.id 意思就变了，此时就等价于：

select \* from a inner join b on a.id = b.id。即就是内连接。

但是这种写法并不符合规范，可能只对某些数据库管用，如sqlserver。推荐最好不要这样写。最好写成inner join的写法。

内连接查询 (select \* from a join b on a.id = b.id) 与 关联查询 (select \* from a , b where a.id = b.id)的区别

## left join ##

在两张表进行连接查询时，会返回左表所有的行，即使在右表中没有匹配的记录。  
我们使用left join对两张表进行连接查询，sql如下：

SELECT p.LastName, p.FirstName, o.OrderNo
    FROM Persons p
    LEFT JOIN Orders o
    ON p.Id_P=o.Id_P
    ORDER BY p.LastName

执行结果  
![在这里插入图片描述][20201116015719768.jpg_pic_center]

可以看到，左表（Persons表）中LastName为Bush的行的Id\_P字段在右表（Orders表）中没有匹配，但查询结果仍然保留该行。

## right join ##

在两张表进行连接查询时，会返回右表所有的行，即使在左表中没有匹配的记录。  
我们使用right join对两张表进行连接查询，sql如下：

SELECT p.LastName, p.FirstName, o.OrderNo
    FROM Persons p
    RIGHT JOIN Orders o
    ON p.Id_P=o.Id_P
    ORDER BY p.LastName

执行结果  
![在这里插入图片描述][20201116015741806.jpg_pic_center]

Orders表中最后一条记录Id\_P字段值为65，在左表中没有记录与之匹配，但依然保留。

## full join ##

在两张表进行连接查询时，返回左表和右表中所有没有匹配的行。  
我们使用full join对两张表进行连接查询，sql如下：

SELECT p.LastName, p.FirstName, o.OrderNo
    FROM Persons p
    FULL JOIN Orders o
    ON p.Id_P=o.Id_P
    ORDER BY p.LastName

执行结果  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5MjEzOTY5_size_16_color_FFFFFF_t_70_pic_center 1]

查询结果是left join和right join的并集。

## count的优化 ##

比如：计算id大于5的城市 `a. select count(*) from world.city where id > 5; b. select (select count(*) from world.city) – count(*) from world.city where id <= 5;` a语句当行数超过11行的时候需要扫描的行数比b语句要多， b语句扫描了6行，此种情况下，b语句比a语句更有效率。当没有where语句的时候直接`select count(*) from world.city`这样会更快，因为mysql总是知道表的行数。

## sum的优化 ##

如果表的数据量是百万级或者千万级的，以下该查询语句的查询时间较长。

SELECT STATUS, SUM(COUNT) FROM t GROUP BY STATUS ;

在mysql优化的方法中，有这么两条：  
经常同时存取多列，且每列都含有重复值可考虑建立组合索引；  
组合索引要尽量使关键查询形成索引覆盖，其前导列一定是使用最频繁的列。  
所以你可以试试下面方法：在STATUS和COUNT两字段上建立组合索引，以STATUS为前导列（如果该字段值很长，可以指定长度）

ALTER TABLE t ADD KEY(STATUS(10),COUNT);

总结：使用恰当的索引使sql的效率倍增，类似sum(),min(),max()这些都需要在字段上建索引。

## in语句的优化 ##

原则 如果能够使用between 语句的情况下 就不要使用in语句 因为会导致全表扫描  
影响效率

**原始语句**

SELECT * FROM t WHERE name IN ( SELECT name FROM t1 WHERE name != "" )

在数据行数比较大的情况下 这个查询估计会耗时 好几十秒

下面对其进行优化

**优化1**  
仍使用in子查询，多查询一次

SELECT * FROM t WHERE name IN ( SELECT name FROM ( SELECT name FROM t1 WHERE name != "" ) AS h )

上面的语句只是对in子查询又做了一次查询, 查询速度非常快，毫无延迟

**优化2**  
使用LEFT JOIN查询

SELECT * FROM ( SELECT DISTINCT name FROM t1 WHERE name != "" ) AS h LEFT JOIN  t ON  = h.name

查询速度也非常快，毫无延迟

mysql会对sql语句做优化， in 后面的条件不超过一定数量仍然会使用索引。  
mysql 会根据索引长度和in后面条件数量判断是否使用索引。

**注意：如果是in后面是子查询，则不会使用索引。**

例子  
**执行一个很普通的查询：**

Select * FROM `article` Where article_category=11 orDER BY article_id DESC LIMIT 5

//执行时间大约要5秒左右

解决方案：  
**建一个索引：**

create index idx_u on article (article_category,article_id);
    
    Select * FROM `article` Where article_category=11 orDER BY article_id DESC LIMIT 5

减少到0.0027秒

继续问题：进入 **in 语句**

Select * FROM `article` Where article_category IN (2,3) orDER BY article_id DESC LIMIT 5

执行时间要11.2850秒。  
**使用OR:**

select * from article where article_category=2 or article_category=3 order by article_id desc limit 5

执行时间：11.0777s  
优化建议：**避免使用in 或者 or (or会导致扫表)，使用union all**

**使用UNION ALL：** 代替 in语句 查询

(select * from article where article_category=2 order by article_id desc limit 5)
    UNION ALL (select * from article where article_category=3 order by article_id desc limit 5)
    orDER BY article_id desc limit 5

但是会受到 in范围的限制  
执行时间：0.0261s

**范例**  
in在应用程序中使用子查询后，SQL语句的查询性能变得非常糟糕。例如：

SELECT driver_id FROM driver where driver_id in (SELECT driver_id FROM driver where _create_date > '2020-07-25 00:00:00');

SELECT count(driver_id) FROM driver where driver_id in (SELECT uid FROM user);

**优化语句**

SELECT count(driver_id) FROM driver where exists (SELECT uid FROM (SELECT uid from user) as b where b.uid = driver.driver_id);

同样的还是相关子查询，但是减少了内部遍历查询的操作。所以可以通过预查询来减少遍历操作，而提高效率。

**其实在实际编程中，很多开发人员选择不使用连接表查询，而是自己先把数据从一张表中取出，再到另一张表中执行WHEREIN操作，这原理和上面SQL语句实现的是一样的。（有索引）**

## group by语句优化 ##

默认情况下，MySQL排序所有GROUP BY col1, col2, …，查询的方法如同在查询中指定ORDER BY col1, col2, …。  
如果显式包括一个包含相同的列的ORDER BY子句，MySQL可以毫不减速地对它进行优化，尽管仍然进行排序。  
如果查询包括GROUP BY但你想要避免排序结果的消耗，你可以指定ORDER BY NULL禁止排序。  
**例如：**

SELECT a, COUNT(*) FROM bar GROUP BY a ORDER BY NULL;

注意：最直接的优化方案就是给分组字段建索引

> https://blog.csdn.net/szwangdf/article/details/80789279

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5MjEzOTY5_size_16_color_FFFFFF_t_70_pic_center]: /images/20221120/bbaafc01c4d845ff8f5f1d6a095fc6e4.png
[20201116015654818.jpg_pic_center]: /images/20221120/16dd7c0892ce492b907341b949e93990.png
[20201116015719768.jpg_pic_center]: /images/20221120/00328f678f83410485508fb45f7245a9.png
[20201116015741806.jpg_pic_center]: /images/20221120/1f3f790a7aa0452faff6cbc6913b330f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5MjEzOTY5_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20221120/993d502a6d5f4e5fbc20a25b2a56195d.png