MySQL in 查询优化 mysql查询性能优化

前言；一般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（全连接）四种方式，它们之间其实并没有太大区别，仅仅是查询出来的结果有所不同。

例如我们有两张表：

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

执行结果

此种连接方式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

执行结果

可以看到，左表（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

执行结果

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

执行结果

查询结果是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;

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