Sql优化

主题说明
常用 SQL 优化原则
减少表的连接数
临时表的使用
减少子查询的使用
常用 SQL 编写注意事项
避免 Select *
尽量避免进行全盘扫描
用 UNION 来代替 OR
用 exists 代替 in
谨慎使用 in 和 not in
为什么要避免使用游标
设置合理的字段属性
字段设置为 NOT NULL
尽量使用 JOIN 代替子查询
ORDER BY NULL 禁止不需要的排序
使用索引时应该注意什么
总结
主题说明

在传统的系统应用程序中，我们通常都会和数据库建立连接进行数据的读写操作，为了减少连接数据库造成的资源消耗于是有了数据库连接缓冲池。在此基础上，SQL 语句的优化对于研发人员也是非常重要的，高效的 SQL 语句经常会给使一个业务逻辑的接口响应速度变得非常快。

所以本篇 Chat 将主要从 SQL 语句的优化给出一些建议以及如何使用 SQL 语句里面的关键字等才能使 SQL 的执行效率相对提升，希望给研发人员在编写 SQL 语句时能有一些帮助。

常用 SQL 优化原则

减少表的连接数

在使用联表查询时，表的连接数越多查询的性能越差。在允许的情况下进行拆分执行，不仅能够降低 SQL 的复杂程度，也可以对执行效率进行优化。联表查询时应该尽量使用 INNER JOIN 进行查询，这样效率对高一些。一般情况下，在进行数据库设计时应该避免多表之间的联合查询，避免由于数据库设计缺陷造成的多个表之间复杂联合查询。

临时表的使用

如果不可避免，可以考虑使用临时表或表变量存放中间结果。

mysql> CREATE TEMPORARY TABLE test(
-> product_name VARCHAR(50) NOT NULL
-> , total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00
-> , avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00
-> , total_units_sold INT UNSIGNED NOT NULL DEFAULT 0
);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO test
-> (product_name, total_sales, avg_unit_price, total_units_sold)
-> VALUES
-> (‘cucumber’, 100.25, 90, 2);

mysql> SELECT * FROM test;
±-------------±------------±---------------±-----------------+
| product_name | total_sales | avg_unit_price | total_units_sold |
±-------------±------------±---------------±-----------------+
| cucumber | 100.25 | 90.00 | 2 |
±-------------±------------±---------------±-----------------+
1 row in set (0.00 sec)
所谓临时表肯定就是临时存在的，我们会发现在我们平时创建表的 SQL 中使用添加了 TEMPORARY 这个关键字。在退出数据库操作以后临时表也会跟着销毁，再使用查询语句时结果也是不会存在的。

减少子查询的使用

子查询即嵌套查询，就是把一个查询的结果在另一个查询中使用就叫做子查询。一般情况下子查询不宜超过两个。不仅会增加 SQL 的复杂程度，而且也影响了性能。所以在条件允许的情况下应该尽量减少子查询的使用。

select s.s_id , s_name , c.c_id , c_name , score
from t_grade g join t_student s on g.s_id = s.s_id
join t_course c on g.c_id = c.c_id
having c.c_id != 2000202 and score > all (
select score
from t_grade
where c_id = 2000202
)
group by c.c_id , s.s_id , s.s_name , c_name ,score
常用 SQL 编写注意事项

避免 Select *

SELECT
*
FROM
user
WHERE
name = “zhangsan”;
使用星号 * 可能会返回不使用的列的数据。 它在 MySQL 数据库服务器和应用程序之间产生不必要的 I/O 磁盘和网络流量。而且就算是要查询所有的列时也不建议使用，因为明确的写出结果字段也更利于查询结果集的管理。

尽量避免进行全盘扫描

避免全盘扫描主要是为了在数据库表中存在索引时，可能存在由于我们使用不当的 SQL 关键字会导致索引字段的失效从而会降低 SQL 的执行效率。一般情况下，这种关键字主要是 where 语句相关联的一些关键字，比如 or、!=、in、not in 的使用等等。在 SQL 中，如果是两个字段其中一个有索引，而另一个没有索引时就会执行全盘扫描不进行索引的使用。所以，在日常的 SQL 编程中应该尽量避免没有必要的全盘扫描。

用 UNION 来代替 OR

因为 SQL 中 or 关键字在使用时会导致索引失效，所以在允许的情况下，并且满足 UNION 的使用时应该使用 UNION 关键字来进行代替。

SQL 原型：

select id, job, from test where job=‘work01’ or job=‘work02’;
SQL 优化：

select id, job, from test where job=‘work01’;

union all

select id, job, from test where job=‘work02’;
用 exists 代替 in

not in 是最低效的，因为要对子查询的表进行全表扫描。可以考虑使用外链接或 not exists。

SQL 原型：

SELECT *
FROM EMP
WHERE EMPNO > 0 AND DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE LOC = ‘MELB’)
SQL 优化：

SELECT *
FROM EMP
WHERE
EMPNO > 0
AND EXISTS (SELECT ‘X’ FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = ‘MELB’)
谨慎使用 in 和 not in

同样 in 和 not in 会导致进行全盘扫描，如果在数据量几百万条时这时候进行全盘扫描肯定是效率比较低下的。

原型 SQL ：

select * from table1 where name not in (select name from table2);
优化 SQL

select * from table1
where not EXISTS (select name from table2 where table1.name = table2.phone);
所以在开发时测试数据库表的数据量不是很大，更应该尽量避免这种操作。

为什么要避免使用游标

游标一般用于把通过脚本得到的结果集的内容在用于其它的 SQL 语句中。但是游标执行会影响脚本执行速度，所以使用时请慎重。一般情况下，当需要在存储过程中遍历所查得的结果集时才会使用游标来进行操作。

设置合理的字段属性

设计字段属性主要是数据库设计时需要考虑注意，在设置字段属性值类型内存大小等时应该尽量考虑实际业务逻辑情况合理优化设计。比如，我们需要定义一个长整型的时间戳来作为一个数据库表中的 id 的使用，如果我们根据所需要的业务逻辑知道这个 id 的长度是 11 位，那我们设置的属性肯定应该是 id bigint（11），而不是使用默认的情况或是其他。这样设计在数据库执行效率上也是有很大提升的。

字段设置为 NOT NULL

在数据库设计时，字段应该有必须的属性不允许设置成 NULL。如果字段属性设置成 NULL 也会造成不需要的全盘扫描从而影响 SQL 的执行效率。

索引失效：

SELECT * FROM test WHERE name IS NOT NULL;
尽量使用 JOIN 代替子查询

因为子查询在嵌套时会创建数据库临时表，影响 SQL 执行效率。使用 JOIN 关键字时会减少子查询的使用。

原型 SQL：

DELETE FROM test01
WHERE id NOT IN (SELECT id FROM test02 );
优化 SQL：

SELECT * FROM test01
LEFT JOIN test02 ON test01.id=test02.id
WHERE test01.id IS NULL
ORDER BY NULL 禁止不需要的排序

默认情况下，SQL 查询结果都是经过排序的。在语句不需要排序时，使用 ORDER BY NULL 可以禁止排序提高执行效率。

使用索引时应该注意什么

如果条件中有 or，or 条件中的每个列都加上索引，索引才生效.可考虑用 UNION 替换。
like 查询是以 % 开头，否则也会导致索引失效。
如果 MySQL 估计使用全表扫描要比使用索引快，则不使用索引。
数据唯一性差的字段不要使用索引，一般使用主键或是具有唯一性的字段作为索引字段。
如果数据量不是很大，一般不建议使用索引。
索引不能包含字段属性为 NULL 的列。
索引字段使用了 SQL 中的函数表达式时，索引会失效。
总结

本篇 Chat 主要还是对 SQL 优化给出的一些常见的建议或是笔者的理解。在 SQL 性能优化的过程中肯定是不存在有完美的 SQL 编写的，所以在日常的开发中，我们在满足业务逻辑允许的前提下对其尽可能的优化以提升执行效率，最后感谢大家的阅读！