MySQL性能优化之sql优化-优快云博客

本文探讨了SQL查询优化的多种策略，包括使用Exists替代OR、IN与INNER JOIN的选择、JOIN语法及性能考量，以及如何避免不必要的数据请求，优化查询方式，分解大查询，优化MIN()、MAX()、COUNT()和LIMIT分页，以及处理子查询和UNION查询的方法。

2019独角兽企业重金招聘Python工程师标准>>>

一、使用OR还是Exists

在写SQL语句的时候，经常会使用OR语句。OR语句的性能不是很好，经常让表的索引失去作用。

最近调试一个语句，里面有一个搜索条件是A=1 or B=1 or C=1。其中的A、B、C是同一个表里面的字段名。因为使用了OR语句，结果语句的性能不是很快。

原来的语句：

SELECT * FROM Users 
Where isActive=1 AND (A=1 OR B=1 OR C=1)

Exists语句有时候对提高语句的效率有很大的帮助，所以这次我尝试使用Exists，结果效果不错。但是语句写起来有些累赘。

修改后的语句。

SELECT * FROM Users u
Where isActive=1 
AND 
(
    (EXISTS(Select UserId FROM Users WHERE UserId = u.UserId AND A=1) 
OR 
    (EXISTS(Select UserId FROM Users WHERE UserId = u.UserId AND B=1) 
OR
    (EXISTS(Select UserId FROM Users WHERE UserId = u.UserId AND C=1) 
)

其实，第二种写法，让MSSQL的优化器能更好地使用索引。而第一种的索引，使用的不是很好，从而导致性能上的区别。

二、使用IN和INNER JOIN

IN在MSSQL语句里是表示在一组数据里寻找。如果那一组数据的量很大，IN的性能就会很差。另外，如果要比较的数据量很多，IN的性能也不是很好。当IN的性能出现问题的时候，而相对的语句能用INNER JOIN来实现，可以尝试一下还提高语句的性能。

原来的SQL语句：

SELECT * FROM Invoices WHERE UserId IN (SELECT UserId FROM Users)

可以改写成为

SELECT Invoices.* FROM Invoices 
INNER JOIN Users ON Users.UserId = Invoices.UserId

当然，如果SELECT UserId FROM Users只有少量的数据，那影响就不会很大。

三、JOIN语法以及性能优化

内外联结的区别是内联结将去除所有不符合条件的记录，而外联结则保留其中部分。外左联结与外右联结的区别在于如果用A左联结B则A中所有记录都会保留在结果中，此时B中只有符合联结条件的记录，而右联结相反，这样也就不会混淆了。

1．Join语法概述

join 用于多表中字段之间的联系，语法如下：

代码如下:

FROM table1 INNER|LEFT|RIGHT JOIN table2 ON conditiona

table1:左表；table2:右表。

JOIN 功能分类

INNER JOIN（内连接,或等值连接）：取得两个表中存在连接匹配关系的记录。

LEFT JOIN（左连接）：取得左表（table1）完全记录，即是右表（table2）并无对应匹配记录。

RIGHT JOIN（右连接）：与 LEFT JOIN 相反，取得右表（table2）完全记录，即是左表（table1）并无匹配对应记录。

注意：mysql不支持Full join,不过可以通过UNION 关键字来合并 LEFT JOIN 与 RIGHT JOIN来模拟FULL join.

接下来给出一个列子用于解释下面几种分类。如下两个表(A,B)

代码如下:

mysql> select A.id,A.name,B.name from A,B where A.id=B.id;
+----+-----------+-------------+
| id | name | name |
+----+-----------+-------------+
| 1 | Pirate | Rutabaga |
| 2 | Monkey | Pirate |
| 3 | Ninja | Darth Vader |
| 4 | Spaghetti | Ninja |
+----+-----------+-------------+
4 rows in set (0.00 sec)

2.Inner join

内连接，也叫等值连接，inner join产生同时符合A和B的一组数据。

代码如下:

mysql> select * from A inner join B on A.name = B.name;
+----+--------+----+--------+
| id | name | id | name |
+----+--------+----+--------+
| 1 | Pirate | 2 | Pirate |
| 3 | Ninja | 4 | Ninja |
+----+--------+----+--------+

3.Left join

代码如下:

mysql> select * from A left join B on A.name = B.name;
#或者：select * from A left outer join B on A.name = B.name;
+----+-----------+------+--------+
| id | name | id | name |
+----+-----------+------+--------+
| 1 | Pirate | 2 | Pirate |
| 2 | Monkey | NULL | NULL |
| 3 | Ninja | 4 | Ninja |
| 4 | Spaghetti | NULL | NULL |
+----+-----------+------+--------+
4 rows in set (0.00 sec)

left join,（或left outer join:在Mysql中两者等价，推荐使用left join.）左连接从左表(A)产生一套完整的记录,与匹配的记录(右表(B)) .如果没有匹配,右侧将包含null。

如果想只从左表(A)中产生一套记录，但不包含右表(B)的记录，可以通过设置where语句来执行，如下：

代码如下:

mysql> select * from A left join B on A.name=B.name where A.id is null or B.id is null;
+----+-----------+------+------+
| id | name | id | name |
+----+-----------+------+------+
| 2 | Monkey | NULL | NULL |
| 4 | Spaghetti | NULL | NULL |
+----+-----------+------+------+
2 rows in set (0.00 sec)

同理，还可以模拟inner join. 如下：

代码如下:

mysql> select * from A left join B on A.name=B.name where A.id is not null and B.id is not null;
+----+--------+------+--------+
| id | name | id | name |
+----+--------+------+--------+
| 1 | Pirate | 2 | Pirate |
| 3 | Ninja | 4 | Ninja |
+----+--------+------+--------+
2 rows in set (0.00 sec)

求差集：

根据上面的例子可以求差集，如下：

代码如下:

SELECT * FROM A LEFT JOIN B ON A.name = B.name
WHERE B.id IS NULL
union
SELECT * FROM A right JOIN B ON A.name = B.name
WHERE A.id IS NULL;
# 结果
+------+-----------+------+-------------+
| id | name | id | name |
+------+-----------+------+-------------+
| 2 | Monkey | NULL | NULL |
| 4 | Spaghetti | NULL | NULL |
| NULL | NULL | 1 | Rutabaga |
| NULL | NULL | 3 | Darth Vader |
+------+-----------+------+-------------+

4.Right join

代码如下:

mysql> select * from A right join B on A.name = B.name;
+------+--------+----+-------------+
| id | name | id | name |
+------+--------+----+-------------+
| NULL | NULL | 1 | Rutabaga |
| 1 | Pirate | 2 | Pirate |
| NULL | NULL | 3 | Darth Vader |
| 3 | Ninja | 4 | Ninja |
+------+--------+----+-------------+
4 rows in set (0.00 sec)

同left join。

5.Cross join

cross join：交叉连接，得到的结果是两个表的乘积，即笛卡尔积

笛卡尔（Descartes）乘积又叫直积。假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1), (b,2)}。可以扩展到多个集合的情况。类似的例子有，如果A表示某学校学生的集合，B表示该学校所有课程的集合，则A与B的笛卡尔积表示所有可能的选课情况。

代码如下:

mysql> select * from A cross join B;
+----+-----------+----+-------------+
| id | name | id | name |
+----+-----------+----+-------------+
| 1 | Pirate | 1 | Rutabaga |
| 2 | Monkey | 1 | Rutabaga |
| 3 | Ninja | 1 | Rutabaga |
| 4 | Spaghetti | 1 | Rutabaga |
| 1 | Pirate | 2 | Pirate |
| 2 | Monkey | 2 | Pirate |
| 3 | Ninja | 2 | Pirate |
| 4 | Spaghetti | 2 | Pirate |
| 1 | Pirate | 3 | Darth Vader |
| 2 | Monkey | 3 | Darth Vader |
| 3 | Ninja | 3 | Darth Vader |
| 4 | Spaghetti | 3 | Darth Vader |
| 1 | Pirate | 4 | Ninja |
| 2 | Monkey | 4 | Ninja |
| 3 | Ninja | 4 | Ninja |
| 4 | Spaghetti | 4 | Ninja |
+----+-----------+----+-------------+
16 rows in set (0.00 sec)

#再执行：mysql> select * from A inner join B; 试一试

#在执行mysql> select * from A cross join B on A.name = B.name; 试一试

实际上，在 MySQL 中（仅限于 MySQL） CROSS JOIN 与 INNER JOIN 的表现是一样的，在不指定 ON 条件得到的结果都是笛卡尔积，反之取得两个表完全匹配的结果。 INNER JOIN 与 CROSS JOIN 可以省略 INNER 或 CROSS 关键字，因此下面的 SQL 效果是一样的：

代码如下:

... FROM table1 INNER JOIN table2
... FROM table1 CROSS JOIN table2
... FROM table1 JOIN table2

6.Full join

代码如下:

mysql> select * from A left join B on B.name = A.name 
-> union 
-> select * from A right join B on B.name = A.name;
+------+-----------+------+-------------+
| id | name | id | name |
+------+-----------+------+-------------+
| 1 | Pirate | 2 | Pirate |
| 2 | Monkey | NULL | NULL |
| 3 | Ninja | 4 | Ninja |
| 4 | Spaghetti | NULL | NULL |
| NULL | NULL | 1 | Rutabaga |
| NULL | NULL | 3 | Darth Vader |
+------+-----------+------+-------------+
6 rows in set (0.00 sec)

全连接产生的所有记录（双方匹配记录）在表A和表B。如果没有匹配,则对面将包含null。

7.性能优化

7.1.显示(explicit) inner join VS 隐式(implicit) inner join

如：

代码如下:

select * from
table a inner join table b
on a.id = b.id;

代码如下:

select a.*, b.*
from table a, table b
where a.id = b.id;

我在数据库中比较(10w数据)得之，它们用时几乎相同，第一个是显示的inner join，后一个是隐式的inner join。

7.2.left join/right join VS inner join

尽量用inner join.避免 LEFT JOIN 和 NULL.

在使用left join（或right join）时，应该清楚的知道以下几点：

7.2.1 on与 where的执行顺序

ON 条件（“A LEFT JOIN B ON 条件表达式”中的ON）用来决定如何从 B 表中检索数据行。如果 B 表中没有任何一行数据匹配 ON 的条件,将会额外生成一行所有列为 NULL 的数据,在匹配阶段 WHERE 子句的条件都不会被使用。仅在匹配阶段完成以后，WHERE 子句条件才会被使用。ON将从匹配阶段产生的数据中检索过滤。

所以我们要注意：在使用Left (right) join的时候，一定要在先给出尽可能多的匹配满足条件，减少Where的执行。如：

PASS

代码如下:

select * from A
inner join B on B.name = A.name
left join C on C.name = B.name
left join D on D.id = C.id
where C.status>1 and D.status=1;

Great

代码如下:

select * from A
inner join B on B.name = A.name
left join C on C.name = B.name and C.status>1
left join D on D.id = C.id and D.status=1

从上面例子可以看出，尽可能满足ON的条件，而少用Where的条件。从执行性能来看第二个显然更加省时。

7.2.2 注意ON 子句和 WHERE 子句的不同

如作者举了一个列子：

代码如下:

mysql> SELECT * FROM product LEFT JOIN product_details
ON (product.id = product_details.id)
AND product_details.id=2;
+----+--------+------+--------+-------+
| id | amount | id | weight | exist |
+----+--------+------+--------+-------+
| 1 | 100 | NULL | NULL | NULL |
| 2 | 200 | 2 | 22 | 0 |
| 3 | 300 | NULL | NULL | NULL |
| 4 | 400 | NULL | NULL | NULL |
+----+--------+------+--------+-------+
4 rows in set (0.00 sec)
 

mysql> SELECT * FROM product LEFT JOIN product_details
ON (product.id = product_details.id)
WHERE product_details.id=2;
+----+--------+----+--------+-------+
| id | amount | id | weight | exist |
+----+--------+----+--------+-------+
| 2 | 200 | 2 | 22 | 0 |
+----+--------+----+--------+-------+
1 row in set (0.01 sec)

从上可知，第一条查询使用 ON 条件决定了从 LEFT JOIN的 product_details表中检索符合的所有数据行。第二条查询做了简单的LEFT JOIN，然后使用 WHERE 子句从 LEFT JOIN的数据中过滤掉不符合条件的数据行。

7.2.3 尽量避免子查询，而用join

往往性能这玩意儿，更多时候体现在数据量比较大的时候，此时，我们应该避免复杂的子查询。如下：

PASS

代码如下:

insert into t1(a1) select b1 from t2 where not exists(select 1 from t1 where t1.id = t2.r_id);

Great

代码如下:

insert into t1(a1) 
select b1 from t2 
left join (select distinct t1.id from t1 ) t1 on t1.id = t2.r_id 
where t1.id is null;

8.测试题(多表连接join查询)

8.1. 题目

　　现有如下2个保存学校班级比赛的表,一个表保存班级id和班级名,另外一个表保存主场班级id,客场班级id和比赛场数,现在需要连接查询生成主场班级名,客场班级名,比赛场数的表,求具体实现的sql语句.

8.1.1 班级表

mysql> select * from class;
+------+--------+
| c_id | c_name |
+------+--------+
|    1 | 1班    |
|    2 | 2班    |
|    3 | 3班    |
|    4 | 4班    |
+------+--------+
4 rows in set (0.00 sec)

8.1.2 比赛表

mysql> select * from team;
+------+------+------+
| h_id | g_id | num  |
+------+------+------+
|    1 |    2 |   34 |
|    2 |    4 |   37 |
+------+------+------+
2 rows in set (0.00 sec)

8.2. 详解

8.2.1 分析

　　发现要生成2个班级名的表,而一次join连接可以生成一个,则需要2次join连接,由于class表和team表连接内容必须要匹配,所以采用内连接.

8.2.2 结果

mysql> select ct.h_name,class.c_name as g_name,ct.num from (select class.c_name as h_name,team.* from class join team on class.c_id=team.h_id) as ct join class on ct.g_id=class.c_id;
+--------+--------+------+
| h_name | g_name | num  |
+--------+--------+------+
| 1班    | 2班    |   34 |
| 2班    | 4班    |   37 |
+--------+--------+------+
2 rows in set (0.00 sec)

三、总结

避免向数据库请求不需要的数据

在访问数据库时，应该只请求需要的行和列。请求多余的行和列会消耗MySql服务器的CPU和内存资源，并增加网络开销。
例如在处理分页时，应该使用LIMIT限制MySql只返回一页的数据，而不是向应用程序返回全部数据后，再由应用程序过滤不需要的行。
当一行数据被多次使用时可以考虑将数据行缓存起来，避免每次使用都要到MySql查询。
避免使用SELECT *这种方式进行查询，应该只返回需要的列。

查询数据的方式

查询数据的方式有全表扫描、索引扫描、范围扫描、唯一索引查询、常数引用等。这些查询方式，速度从慢到快，扫描的行数也是从多到少。可以通过EXPLAIN语句中的type列反应查询采用的是哪种方式。
通常可以通过添加合适的索引改善查询数据的方式，使其尽可能减少扫描的数据行，加快查询速度。
例如，当发现查询需要扫描大量的数据行但只返回少数的行，那么可以考虑使用覆盖索引，即把所有需要用到的列都放到索引中。这样存储引擎无须回表获取对应行就可以返回结果了。

分解大的查询

可以将一个大查询切分成多个小查询执行，每个小查询只完成整个查询任务的一小部分，每次只返回一小部分结果
删除旧的数据是一个很好的例子。如果只用一条语句一次性执行一个大的删除操作，则可能需要一次锁住很多数据，占满整个事务日志，耗尽系统资源、阻塞很多小的但重要的查询。将一个大的删除操作分解成多个较小的删除操作可以将服务器上原本一次性的压力分散到多次操作上，尽可能小地影响MySql性能，减少删除时锁的等待时间。同时也减少了MySql主从复制的延迟。
另一个例子是分解关联查询，即对每个要关联的表进行单表查询，然后将结果在应用程序中进行关联。下面的这个查询：

SELECT * FROM tag
    JOIN tag_post ON tag_post.tag_id=tag.id
    JOIN post ON tag_post.post_id=post.id
WHERE tag.tag = 'mysql';

可以分解成下面这些查询来代替：

SELECT * FROM tag WHERE tag = 'mysql';
SELECT * FROM tag_post WHERE tag_id = 1234;
SELECT * FROM post WHERE post.id in (123,456,567,9098,8904);

将一个关联查询拆解成多个单表查询有如下有点：

让缓存的效率更高。如果缓存的是关联查询的结果，那么其中的一个表发生变化，整个缓存就失效了。而拆分后，如果只是某个表很少的改动，并不会破坏所有的缓存。
可以减少锁的竞争
更容易对数据库进行拆分，更容易做到高性能和可扩展。
查询本身的效率也有可能会有所提升。例如上面用IN()代替关联查询比随机的关联更加高效。

优化MIN()和MAX()

添加索引可以优化MIN()和MAX()表达式。例如，要找到某一列的最小值，只需要查询对应B-Tree索引的最左端的记录即可。类似的，如果要查询列中的最大值，也只需要读取B-Tree索引的最后一条记录。对于这种查询，EXPLAIN中可以看到"Select tables optimized away",表示优化器已经从执行计划中移除了该表，并以一个常数取而代之。

用IN()取代OR

在MySql中，IN()先将自己列表中的数据进行排序，然后通过二分查找的方式确定列的值是否在IN()的列表中，这个时间复杂度是O(logn)。如果换成OR操作，则时间复杂度是O(n)。所以，对于IN()的列表中有大量取值的时候，用IN()替换OR操作将会更快。

优化关联查询

在MySql中，任何一个查询都可以看成是一个关联查询，即使只有一个表的查询也是如此。
MySql对任何关联都执行嵌套循环的关联操作，例如对于下面的SQL语句：

SELECT tbl1.col1,tbl2.col2
FROM tbl1 INNER JOIN tbl2 USING(col3)
WHERE tbl1.col1 IN(5,6);

下面的伪代码表示MySql将如何执行这个查询：

//先从第一个表中取出符合条件的所有行
out_iter = iterator over tbl1 where col1 IN(5,6)
outer_row = out_iter.next
//在while循环中遍历第一个表结果集的每一行
while outer_row
    //对于第一个表结果集中的每一行，在第二个表中找出符合条件的所有行
    inner_iter = iterator over tbl2 where col3 = outer_row.col3
    inner_row = inner_iter.next
    while inner_row
        //将第一个表的结果列和第二个表的结果列拼装在一起作为结果输出
        output[outer_row.col1, inner_row.col2]
        inner_row = inner_iter.next
    end
    //回溯，再根据第一个表结果集的下一行，继续上面的过程
    outer_row = outer_iter.next
end

对于单表查询，那么只需要完成上面外层的基本操作。
优化关联查询，要确保ON或者USING子句中的列上有索引，并且在建立索引时需要考虑到关联的顺序。通常来说，只需要在关联顺序中的第二个表的相应列上创建索引。例如，当表A和表B用列c关联的时候，假设关联的顺序是B、A，那么就不需要在B表的c列上建立索引。没有用到的索引只会带来额外的负担。
此外，确保任何的GROUP BY和ORDER BY中的表达式只涉及到一个表中的列，这样才能使用索引来优化这个过程。

临时表的概念

上面提到在MySql中，任何一个查询实质上都是一个关联查询。那么对于子查询或UNION查询是如何实现关联操作的呢。
对于UNION查询，MySql先将每一个单表查询结果放到一个临时表中，然后再重新读出临时表数据来完成UNION查询。MySql读取结果临时表和普通表一样，也是采用的关联方式。
当遇到子查询时，先执行子查询并将结果放到一个临时表中，然后再将这个临时表当做一个普通表对待。
MySql的临时表是没有任何索引的，在编写复杂的子查询和关联查询的时候需要注意这一点。
临时表也叫派生表。

排序优化

应该尽量让MySql使用索引进行排序。当不能使用索引生成排序结果的时候，MySql需要自己进行排序。如果数据量小于“排序缓冲区”的大小，则MySql使用内存进行“快速排序”操作。如果数据量太大超过“排序缓冲区”的大小，那么MySql只能采用文件排序，而文件排序的算法非常复杂，会消耗很多资源。
无论如何排序都是一个成本很高的操作，所以从性能角度考虑，应尽可能避免排序。所以让MySql根据索引构造排序结果非常的重要。

子查询优化

MySql的子查询实现的非常糟糕。最糟糕的一类查询是WHERE条件中包含IN()的子查询语句。
应该尽可能用关联替换子查询，可以提高查询效率。

优化COUNT()查询

COUNT()有两个不同的作用：

统计某个列值的数量，即统计某列值不为NULL的个数。
统计行数。

当使用COUNT(*)时，统计的是行数，它会忽略所有的列而直接统计所有的行数。而在括号中指定了一个列的话，则统计的是这个列上值不为NULL的个数。
可以考虑使用索引覆盖扫描或增加汇总表对COUNT()进行优化。

优化LIMIT分页

处理分页会使用到LIMIT，当翻页到非常靠后的页面的时候，偏移量会非常大，这时LIMIT的效率会非常差。例如对于LIMIT 10000，20这样的查询，MySql需要查询10020条记录，将前面10000条记录抛弃，只返回最后的20条。这样的代价非常高，如果所有的页面被访问的频率都相同，那么这样的查询平均需要访问半个表的数据。
优化此类分页查询的一个最简单的办法就是尽可能地使用索引覆盖扫描，而不是查询所有的列。然后根据需要与原表做一次关联操作返回所需的列。对于偏移量很大的时候，这样的效率会提升非常大。考虑下面的查询：

SELECT film_id, description FROM sakila.film ORDER BY title LIMIT 50, 5;

如果这个表非常大，那么这个查询最好改写成下面的这样子：

SELECT film.film_id, film.description FROM sakila.film
INNER JOIN 
(SELECT film_id FROM sakila.film ORDER BY title LIMIT 50,5) AS lim
USING(film_id);

注意优化中关联的子查询，因为只查询film_id一个列，数据量小，使得一个内存页可以容纳更多的数据，这让MySQL扫描尽可能少的页面。在获取到所需要的所有行之后再与原表进行关联以获得需要的全部列。
LIMIT的优化问题，其实是OFFSET的问题，它会导致MySql扫描大量不需要的行然后再抛弃掉。可以借助书签的思想记录上次取数据的位置，那么下次就可以直接从该书签记录的位置开始扫描，这样就避免了使用OFFSET。可以把主键当做书签使用，例如下面的查询：

SELECT * FROM sakila.rental ORDER BY rental_id DESC LIMIT 20;

假设上面的查询返回的是主键为16049到16030的租借记录，那么下一页查询就可以直接从16030这个点开始：

SELECT * FROM sakila.rental WHERE rental_id < 16030
ORDER BY rental_id DESC LIMIT 20;

该技术的好处是无论翻页到多么后面，其性能都会很好。
此外，也可以用关联到一个冗余表的方式提高LIMIT的性能，冗余表只包含主键列和需要做排序的数据列。

优化UNION查询

除非确实需要服务器消除重复的行，否则一定要使用UNION ALL。如果没有ALL关键字，MySql会给临时表加上DISTINCT选项，这会导致对整个临时表的数据做唯一性检查。这样做的代价非常高。

转载于:https://my.oschina.net/xsh1208/blog/1924440