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

explain分析你SQL执行计划

explain select * from information_schema.processlist

type：

index 索引全扫描
range 索引范围扫描，常用语<，<=，>=，between等操作
ref 使用非唯一索引扫描或唯一索引前缀扫描，返回单条记录，常出现在关联查询中
eq_ref 类似ref，区别在于使用的是唯一索引，使用主键的关联查询
const 当查询是对主键或者唯一键进行精确查询，系统会把匹配行中的其他列作为常数处理
null MySQL不访问任何表或索引，直接返回结果
System 表只有一条记录(实际中基本不存在这个情况)

性能排行：
System > const > eq_ref > ref > range > index > ALL

possible_keys：

显示可能应用在这张表中的索引

key: 表示实际使用的索引

key列显示MySQL实际决定使用的键（索引），必然包含在possible_keys中

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，但是like很可能让你的索引失效。

反例：

select userId,name from user where userId like ‘%123’;

正例：

select userId,name from user where userId like ‘123%’;

也可以使用以下方法代替

方法一：LOCATE(‘模糊的字’, ‘字段名’) > 0
方法二： POSITION(‘模糊的字’ IN ‘字段名’)
方法三：INSTR(‘模糊的字’, ‘字段名’)
方法四：FIND-IN-SET(‘模糊的字’, ‘字段名’)

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

19、避免在where子句中使用or来连接条件

假设现在需要查询userid为1或者年龄为18岁的用户，很容易有以下sql

反例:

select id,name from user where userId = 1 or age = 18;

正例：

select id,name from user where userId = 1
union all
select id,name from user where age = 18;

理由：

  • 使用or可能会使索引失效，从而全表扫描，而且采用 UNION 语句，返回的结果同上面的一样，但是速度要快些。

  • 对于or+没有索引的age这种情况，假设它走了userId的索引，但是走到age查询条件时，它还得全表扫描，也就是需要三步过程： 全表扫描+索引扫描+合并 如果它一开始就走全表扫描，直接一遍扫描就完事。 mysql是有优化器的，处于效率与成本考虑，遇到or条件，索引可能失效，看起来也合情合理。

20、尽可能使用varchar/nvarchar 代替 char/nchar

反例：
deptName char(100) DEFAULT NULL COMMENT ‘部门名称’

正例：
deptName varchar(100) DEFAULT NULL COMMENT ‘部门名称’

理由：

  • 因为首先变长字段存储空间小，可以节省存储空间。
  • 其次对于查询来说，在一个相对较小的字段内搜索，效率更高。
  • 尽量使用数值替代字符串类型。如性别、支付状态等

21、字段类型是字符串，where时一定用引号括起来，否则索引失效

反例：

select * from user where userid = 123;

正例：

select * from user where userid ='123';

理由：

  • 为什么第一条语句未加单引号就不走索引了呢？ 这是因为不加单引号时，是字符串跟数字的比较，它们类型不匹配，MySQL会做隐式的类型转换，把它们转换为浮点数再做比较。

22、索引不宜太多，一般5个以内

  • 1、索引并不是越多越好，索引虽然提高了查询的效率，但是也降低了插入和更新的效率。
  • 2、insert或update时有可能会重建索引，所以建索引需要慎重考虑，视具体情况来定。
  • 3、一个表的索引数最好不要超过5个，若太多需要考虑一些索引是否没有存在的必要。

23、索引不适合建在有大量重复数据的字段上

  • 因为SQL优化器是根据表中数据量来进行查询优化的，如果索引列有大量重复数据，Mysql查询优化器推算发现不走索引的成本更低，很可能就放弃索引了。

24、避免在索引列上使用mysql的内置函数

业务需求：查询最近七天内登陆过的用户(假设loginTime加了索引)

反例：

select userId,loginTime from loginuser where Date_ADD(loginTime,Interval 7 DAY) >=now();

正例：

explain select userId,loginTime from loginuser where loginTime >= Date_ADD(NOW(),INTERVAL - 7 DAY);

理由：

索引列上使用mysql的内置函数，索引失效。
如果索引列不加内置函数，索引还是会走的。

25、尽量使用union all替代union

如果检索结果中不会有重复的记录，推荐union all 替换 union。

反例：

select from user where userid = 1
union
select from user where age = 10

正例：

select from user where userid=1
union all
select from user where age = 10

理由：

  • union 和 union all的区别是，union会自动去掉多个结果集合中的重复结果，而union all则将所有的结果全部显示出来，不管是不是重复。

后续会持续更新…