应用程序慢如牛,原因有很多,可能是网络的原因、可能是系统架构的原因,还有可能是数据库的原因。
那么如何提高数据库SQL语句执行速度呢?本文总结了一些SQL优化的技巧。

1、比较运算符能用 “=”就不用“<>”

“=”增加了索引的使用几率。

2、将大的DELETE,UPDATE or INSERT 查询变成多个小查询

能写一个几十行、几百行的SQL语句是不是显得逼格很高?然而,为了达到更好的性能以及更好的数据控制,你可以将他们变成多个小查询。

3、如果查询结果只有一条,那请使用 “LIMIT 1”

当你只想要返回一条数据时,加上 LIMIT 1 可以增加性能。MySQL数据库引擎会在找到一条数据后停止搜索,而不是继续往后查少下一条符合记录的数据。

4、为列选择合适的数据类型

能用TINYINT就不用SMALLINT,能用SMALLINT就不用INT,道理你懂的,磁盘和内存消耗越小越好嘛。

mysql的基本数据类型里几个int如下:

类型 大小 范围(有符号) 范围(无符号) 用途
TINYINT 1字节 (-128,127) (0,255) 小整数值
SMALLINT 2 字节 (-32 768,32 767) (0,65 535) 大整数值
MEDIUMINT 3 字节 (-8 388 608,8 388 607) (0,16 777 215) 大整数值
INT或INTEGER 4 字节 (-2 147 483 648,2 147 483 647) (0,4 294 967 295) 大整数值
BIGINT 8 字节 (-9 233 372 036 854 775 808,9 223 372 036 854 775 807) (0,18 446 744 073 709 551 615) 极大整数值

5、使用UNION ALL 代替 UNION,如果结果集允许重复的话

因为 UNION ALL 不去重,效率高于 UNION。

6、为获得相同结果集的多次执行,请保持SQL语句前后一致

这样做的目的是为了充分利用查询缓冲。
比如根据地域和产品id查询产品价格,第一次使用了:

SELECT price FROM order WHERE id = 666 and region = "BEIJING";

那么第二次同样的查询,请保持以上语句的一致性,比如不要将where语句里面的id和region位置调换顺序。

7、尽量避免使用 “SELECT *”

SELECT * 会增加很多不必要的消耗(cpu、io、内存、网络带宽),因为它会进行全表扫描,不能有效利用索引,增大了数据库服务器的负担,以及它与应用程序客户端之间的网络IO开销。

8、WHERE 子句里面的列尽量被索引

只是“尽量”哦,并不是说所有的列。因地制宜,根据实际情况进行调整,因为有时索引太多也会降低性能

9、JOIN 子句里面的列尽量被索引

同样只是“尽量”哦,并不是说所有的列。

10、ORDER BY 的列尽量被索引

ORDER BY的列如果被索引,性能也会更好。

11、使用 LIMIT 实现分页逻辑

不仅提高了性能,同时减少了不必要的数据库和应用间的网络传输。

12、使用 EXPLAIN 关键字去查看执行计划

善用 explain 查看SQL执行计划

1、type列,连接类型(从最好到最差的连接类型为 const、eq_reg、ref、range、index和ALL,最好达到range以上的级别)
2、possible_keys,显示可能应用在这张表中的索引。如果为空,没有可能的索引。
3、key列,实际使用的索引。如果为NULL,则没有使用索引。
4、key_len列,使用的索引的长度。在不损失精确性的情况下,长度越短越好。
5、ref列,显示索引的哪一列被使用了,如果可能的话,是一个常数。
6、rows列,扫描行数。该值是个预估值。
7、extra列,详细说明。注意常见的不太友好的值有:Using filesort, Using temporary。

13、SQL语句中IN包含的值不应过多

MySQL对于IN做了相应的优化,即将IN中的常量全部存储在一个数组里面,而且这个数组是排好序的。但是如果数值较多,产生的消耗也是比较大的。再例如:select id from table_name where num in(1,2,3) 对于连续的数值,能用 between 就不要用 in 了;再或者使用连接来替换。

14、避免在 WHERE 子句里面进行 null 的判断

对于null的判断会导致引擎放弃使用索引而进行全表扫描。建议在创建字段时设置默认值。

15、不建议使用 % 前缀 模糊查询

例如LIKE “%name”或者LIKE “%name%”,这种查询会导致索引失效而进行全表扫描。但是可以使用LIKE “name%”。

17、避免在WHERE子句中对字段进行表达式操作

select user_id from table_name where age*2=36;

中对字段就行了算术运算,这会造成引擎放弃使用索引,建议改成

select user_id from table_name where age=36/2;

18、使用合理的分页方式以提高分页的效率

select id,name from table_name limit 66666, 20

使用上述sql语句做分页的时候,可能有人会发现,随着表数据量的增加,直接使用limit分页查询会越来越慢。

优化的方法如下:可以取前一页的最大行数的id,然后根据这个最大的id来限制下一页的起点。比如此列中,上一页最大的id是866612。sql可以采用如下的写法。

select id,name from table_name where id> 66666 limit 20

后续会持续更新…