优化查询效率的六条SQL语法

1、关于limit分页优化的问题

SELECT * FROM message_1 LIMIT 10000,10
这条语句执行速度很快，当我们把语句改成下面语句的时候再看看用了多久？

SELECT * FROM message_1 LIMIT 1000000,10
运行结果如下图：
解决方法：

我们加“order by id”改造后的sql为：

SELECT * FROM message_1 order by id LIMIT 1000000,10
执行图如下：
只用了1.1秒，可以说稍微改动一下，效率提升了17倍。这是因为这个sql语句使用了主键id来作为索引，所以速度很快。

2、任何情况下都禁止使用 select * from table

看一下最新《阿里java开发手册（泰山版）》中 MySQL 部分描述：

4 - 1. **【强制】**在表查询中，一律不要使用 * 作为查询的字段列表，需要哪些字段必须明确写明。说明：

• 增加查询分析器解析成本。
• 增减字段容易与 resultMap 配置不一致。
• 无用字段增加网络 消耗，尤其是 text 类型的字段。

3、优化批量插入

INSERT INTO message_1(id,content) values(1,'内容1')INSERT INTO message_1(id,content) values(2,'内容2')INSERT INTO message_1(id,content) values(3,'内容3')INSERT INTO message_1(id,content) values(4,'内容4')INSERT INTO message_1(id,content) values(5,'内容5')

应改为

INSERT INTO message_1(id,content) values(1,'内容1'),(2,'内容2'),(3,'内容3'),(4,'内容4'),(5,'内容5')

4、不要在where条件中出现函数、算数运算或其他表达式运算

SELECT content FROM message_1 WHERE datediff(day,createTime,'2020-05-25')=0

DATEDIFF() 函数返回两个日期之间的时间。
语法:

DATEDIFF(datepart,startdate,enddate)

改为

SELECT content FROM message_1 WHERE createTime>='2020-05-01' and createTime<'2020-05-25'

这句话告诉我们不要在where条件中出现函数、算数运算或其他表达式运算，否则也会使索引失效。

5、union all替换union

union和union all的区别在于前者需要合并两个以上的结果集，然后在进行唯一性过滤操作，这样做肯定会涉及到数据的排序，增大cpu的运算与资源消耗和延迟。因此，当在确定没有重复数据或者不关心重复数据的情况下，要使用union all。

6、索引的数量控制在5个以内

因为不是索引越多就越好。索引尽管提高了查询效率，但是也是降低修改和新增的效率。表上有过度地插入、修改和删除操作时，索引页被分成多块,因此会出现索引碎片，降低表的查询性能.所以一张表的索引数最好不要超过五个，如果超过五个那么就得针对这张表做优化处理了

参考博客

1. https://blog.csdn.net/xingkongtianma01/article/details/80658086
2. https://blog.csdn.net/h8y0bdjvukwe1lbozle/article/details/107373386