常见手段
1. limit分页优化
当偏移量特别大时，limit效率会非常低。
比如：
SELECT id FROM A LIMIT 1000,10 很快
SELECT id FROM A LIMIT 90000,10 很慢
方案一：结合order by使用。
1 | select id from A order by id limit 90000,10; |
其原理在于order by会调用索引，所以速度会快的多。
方案二：结合between...and...，缩小检索范围。
1 | select id from A order by id between 90000 and 90010; |
2.利用limit 1 （MySQL）、top 1（SQL Server） 取得一行
有些业务逻辑进行查询操作时(特别是在根据某一字段DESC,取最大一笔)。可以使用limit 1 或者 top 1 来终止[数据库索引]继续扫描整个表或索引。
反例
1 | SELECT id FROM A LIKE 'abc%' |
正例：limit
1 | SELECT id FROM A LIKE 'abc%' limit 1 |
3. 任何情况都不要用 select * from table ，用具体的字段列表替换”*”，不要返回用不到的字段,避免全盘扫描！
反例
1 | SELECT * FROM A |
正例
1 | SELECT id FROM A |
但是值得注意的是：MyISAM引擎在计算COUNT(*)全表时会很快。
4. 批量插入优化
反例
1 | INSERT into person(name,age) values('A',24) |
正例
1 | INSERT into person(name,age) values('A',24),('B',24),('C',24), |
sql语句的优化主要在于对索引的正确使用，而我们在开发中经常犯的错误便是对表进行全盘扫描，一来影响性能，而来耗费时间!
5.like语句的优化
反例
1 | SELECT id FROM A WHERE name like '%abc%' |
由于abc前面用了“%”，因此该查询必然走全表查询,除非必要(模糊查询需要包含abc)，否则不要在关键词前加%
正例
1 | SELECT id FROM A WHERE name like 'abc%' |
实例
mysql版本：5.7.26
1 | select nick_name from member where nick_name like '%小明%' |
like’%小明%’ 并未使用索引。
1 | select nick_name from member where nick_name like '小明%' |
like’小明%’ 成功使用索引。
6.where子句使用or的优化
通常使用 union all 或 union 的方式替换“or”会得到更好的效果。where子句中使用了or关键字,索引将被放弃使用。
反例
1 | SELECT id FROM A WHERE num = 10 or num = 20 |
正例
1 | SELECT id FROM A WHERE num = 10 union all SELECT id FROM A WHERE num=20 |
7.where子句中使用 IS NULL 或 IS NOT NULL 的优化
反例
1 | SELECT id FROM A WHERE num IS NULL |
在where子句中使用 IS NULL 或 IS NOT NULL 判断，索引将被放弃使用，会进行全表查询。
正例
优化成num上设置默认值0，确保表中num没有null值, IS NULL 的用法在实际业务场景下SQL使用率极高，我们应注意避免全表扫描
1 | SELECT id FROM A WHERE num=0 |
8.where子句中对字段进行表达式操作的优化
不要在where子句中的“=”左边进行函数、算数运算或其他表达式运算，否则系统将可能无法正确使用索引。
- 1
1 | SELECT id FROM A WHERE datediff(day,createdate,'2019-11-30')=0 |
优化为
1 | SELECT id FROM A WHERE createdate>='2019-11-30' and createdate<'2019-12-1' |
- 2
1 | SELECT id FROM A WHERE year(addate) <2020 |
优化为
1 | SELECT id FROM A where addate<'2020-01-01' |
9. 排序的索引问题
mysql查询只是用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引。因此数据库默认排序可以符合要求情况下不要使用排序操作；
尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。
10. 尽量用 union all 替换 union
union和union all的差异主要是前者需要将两个（或者多个）结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的cpu运算，加大资源消耗及延迟。所以当我们可以确认不可能出现重复结果集或者不在乎重复结果集的时候，尽量使用union all而不是union
11.Inner join 和 left join、right join、子查询
- 第一：inner join内连接也叫等值连接是，left/rightjoin是外连接。
1 | SELECT A.id,A.name,B.id,B.name FROM A LEFT JOIN B ON A.id =B.id; |
经过来之多方面的证实 inner join性能比较快，因为inner join是等值连接，或许返回的行数比较少。但是我们要记得有些语句隐形的用到了等值连接，如：
SELECT A.id,A.name,B.id,B.name FROM A,B WHERE A.id = B.id;
推荐：能用inner join连接尽量使用inner join连接
- 第二：子查询的性能又比外连接性能慢，尽量用外连接来替换子查询。
反例
mysql是先对外表A执行全表查询，然后根据uuid逐次执行子查询，如果外层表是一个很大的表，我们可以想象查询性能会表现比这个更加糟糕。
1 | Select* from A where exists (select * from B where id>=3000 and A.uuid=B.uuid); |
执行时间：2s左右
正例
1 | Select* from A inner join B ON A.uuid=B.uuid where b.uuid>=3000; 这个语句执行测试不到一秒； |
执行时间：1s不到
- 第三：使用JOIN时候，应该用小的结果驱动大的结果
left join 左边表结果尽量小，如果有条件应该放到左边先处理，right join同理反向。如：
反例
1 | Select * from A left join B A.id=B.ref_id where A.id>10 |
正例
1 | select * from (select * from A wehre id >10) T1 left join B on T1.id=B.ref_id; |
12.exist & in 优化
1 | SELECT * from A WHERE id in ( SELECT id from B ) |
查询的拆分
设计查询的时候一个需要考虑的重要问题是，是否需要将一个复杂的查询分成多个简单的查询。在传统实现中，总是强调需要数据库层完成尽可能多的工作，这样做的逻辑在于以前总是认为网络通信、查询解析和优化是一件代价很高的事情。
但是这样的想法对于MySQL并不适用，对于MySQL，可以适当的拆分（比如需要使用联合查询与子查询的地方）。
其主要原因有以下几点：
- MySQL从设计上让连接和断开连接都很轻量级，在返回一个小的查询结果方面很高效。
- 减少锁的持有时间，比如一个大型的任务，拆分后方便其他任务有时间执行。
- 可以有效地利用缓存。许多应用程序可以方便地缓存单表查询对应的结果对象。
- 可以减少冗余记录的查询。在应用层做关联查询，意味着对于某条记录应用只需要查询一次，而在数据库中做关联查询，则可能需要重复地访问一部分数据。从这点看，这样的重构还可能会减少网络和内存的消耗。
比如：
1 | SELECT * FROM tag |
应该改为：
1 | SELECT * FROM tag post WHERE tag_id=1’; |