MySQL技术专题（11）🌲优化专区-Sql优化

一.SQL优化

1.Sql优化就是指语句在执行的时候效率不是那么乐观所以提高Sql优化就是指在数据库的执行速度的。

可通过配置搜索引擎，

加索引，

分库分表，

对Sql的各列进行精确取其范围值，

尽量避免全局扫描

2.Sql优化的规则：

不要有超过5个以上的表连接（JOIN）

考虑使用临时表或表变量存放中间结果。

少用子查询

视图嵌套不要过深,一般视图嵌套不要超过2个为宜。

能用inner join连接尽量使用inner join等值连接，LEFT JOIN RIGHT JOIN是 取的某个表的满足条件的全量记录

单一表的索引数严禁超过6个

3.Sql查询缓慢的原因:

数据量过大

表设计不合理

sql语句写得不好

没有合理使用索引

4.针对SQL语句的优化

4.1 查询语句中不要使用 *

 4.2 尽量减少子查询，使用关联查询（left join,right join,inner  join）替代

 4.3 减少使用IN或者NOT IN ,使用exists，not exists或者关联查询语句替代

 4.4 or的查询尽量用 union或者union all 代替(在确认没有重复数据或者不用剔除重复数据时，union all会更好)

 4.5 合理的增加冗余的字段（减少表的联接查询） 

 4.6增加中间表进行优化（这个主要是在统计报表的场景，后台开定时任务将数据先统计好，尽量不要在查询的时候去统计） 

 4.7建表的时候能使用数字类型的字段就使用数字类型（type,status...），数字类型的字段作为条件查询比字符串的快

 4.8 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的最末尾

 4.9 合理使用like模糊查询

eg：关键词 %姜%，由于姜前面用到了“%”，因此该查询必然走全表扫描，除非必要，否则不要在关键词前加%

select * from student where name like '%姜%' --会造成全表扫描

   select * from student where name like '姜%' --不会造成全表扫描

4.10where子句使用 ！= 或 <> 操作符优化

在where子句中使用 ！= 或 <>操作符，索引将被放弃使用，会进行全表查询。

 eg：SELECT id FROM A WHERE ID != 5             --会造成全表扫描

          SELECT id FROM A WHERE ID>5 OR ID<5    --不会造成全局表描

4.11  where子句中使用 IS NULL 或 IS NOT NULL 的优化

在where子句中使用 IS NULL 或 IS NOT NULL 判断，索引将被放弃使用，会进行全表查询。

eg：SELECT id FROM A WHERE num IS NULL --会造全表扫描

         SELECT id FROM A WHERE num=0 --优化成num上设置默认值0，确保表中num没有null值

 4.12 where子句使用or的优化

很多时候使用union all 或 nuin(必要的时候)的方式替换“or”会得到更好的效果。where子句中使用了or,索引将被放弃使用。

eg：SELECT id FROM A WHERE num =10 or num = 20--索引失效

SELECT id FROM A WHERE num = 10 union all SELECT id FROM A WHERE num=20 --优化后

 4.13where子句使用IN 或 NOT IN的优化 in和not in 也要慎用，否则也会导致全表扫描。

         方案一：between替换in

SELECT id FROM A WHERE num in(1,2,3) --会造成全表扫描

SELECT id FROM A WHERE num between 1 and 3 --不会造成全表扫描

       方案二：exist替换in

SELECT id FROM A WHERE num in(select num from b ) --会造成全表扫描

SELECT num FROM A WHERE num exists(select 1 from B where B.num = A.num) --不会造成全表扫描

    方案三：left join替换in

SELECT id FROM A WHERE num in(select num from B) --会造成全表扫描

SELECT id FROM A LEFT JOIN B ON A.num = B.num --不会造成全表扫描

4.14  where子句中对字段进行表达式操作的优化  不要在where子句中的“=”左边进行函数、算数运算或其他表达式运算，否则

       系统将可能无法正确使用索引。

        eg:   

SELECT id FROM A WHERE num/2 = 100 --会造成索引失效

SELECT id FROM A WHERE num = 100*2 --优化后

SELECT id FROM A WHERE substring(name,1,3) = 'abc' --会造成索引失效

SELECT id FROM A WHERE LIKE 'abc%' --优化后

SELECT id FROM A WHERE datediff(day,createdate,'2016-11-30')=0--会造成索引失效

SELECT id FROM A WHERE createdate>='2016-11-30' and createdate<'2016-12-1'--不会造成索引失效

SELECT id FROM A WHERE year(addate) <2016--会造成索引失效

SELECT id FROM A where addate<'2016-01-01'--不会造成索引失效

4.15 任何地方都不要用 select * from table ，用具体的字段列表替换"*"，不要返回用不到的字段

   4.16 使用“临时表”暂存中间结果

采用临时表暂存中间结果好处：

（1）避免程序中多次扫描主表，减少程序执行“共享锁”阻塞“更新锁”，减少了阻塞，提高了并发性能。

          （2）尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。

          （3）避免频繁创建和删除临时表，以减少系统资源的浪费。

          （4）尽量避免向客户端返回大数据量，若数据量过大，应考虑相应需求是否合理。

   4.17 limit分页优化 　当偏移量特别大时，limit效率会非常低

SELECT id FROM A LIMIT 1000,10 --超级快

SELECT id FROM A LIMIT 90000,10 --特别慢

        优化方法:

          方法一：select id from A order by id limit 90000,10; 很快，0.04秒就OK。因为用了id主键做索引当然快

          方法二：select id,title from A where id>=(select id from collect order by id limit 90000,1) limit 10

方法三：select id from A order by id between 10000000 and 10000010;

  4.18 批量插入优化

         INSERT into person(name,age) values('A',14)

         INSERT into person(name,age) values('B',14)

         INSERT into person(name,age) values('C',14)

         可优化为：

INSERT into person(name,age) values('A',14),('B',14),('C',14)

4.19 利用limit 1 、top 1 取得一行

    有时要查询一张表时，你知道只需要看一条记录，你可能去查询一条特殊的记录。可以使用limit 1 或者 top 1 来终止数据库

   索引继续扫描整个表或索引。

SELECT id FROM A LIKE 'abc%' --优化之前

SELECT id FROM A LIKE 'abc%' limit 1 --优化之后

4.20 尽量不要使用 BY RAND()命令

BY RAND()是随机显示结果，这个函数可能会为表中每一个独立的行执行BY RAND()命令，这个会消耗处理器的处理能力。

SELECT * FROM A order by rand() limit 10

SELECT * FROM A WHERE id >= ((SELECT MAX(id) FROM A-(SELECT MIN(id) FROM A)) * RAND() + (SELECT MIN(id) FROM A) LIMIT 10--优化之后

4.21 排序的索引问题

    Mysql查询只是用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据

库默认排序可以符合要求情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

4.22 尽量用 union all 替换 union

union和union all的差异主要是前者需要将两个（或者多个）结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量

的cpu运算，加大资源消耗及延迟。所以当我们可以确认不可能出现重复结果集或者不在乎重复结果集的时候，尽量使用union all

而不是union

4.23 避免类型转换

   这里所说的“类型转换”是指where子句中出现column字段的类型和传入的参数类型不一致的时候发生的类型转换。人为的上

通过转换函数进行转换，直接导致mysql无法使用索引。如果非要转型，应该在传入参数上进行转换。

例如utime 是datetime类型，传入的参数是“2016-07-23”，在比较大小时通常是 date（utime）>"2016-07-23",可以优化为

utime>"2016-07-23 00：00：00"

4.24 尽可能使用更小的字段

MySQL从磁盘读取数据后是存储到内存中的，然后使用cpu周期和磁盘I/O读取它，这意味着越小的数据类型占用的空间越

小，从磁盘读或打包到内存的效率都更好，但也不要太过执着减小数据类型，要是以后应用程序发生什么变化就没有空间了。

修改表将需要重构，间接地可能引起代码的改变，这是很头疼的问题，因此需要找到一个平衡点。

4.25 Inner join 和 left join、right join、子查询

第一：inner join内连接也叫等值连接是，left/rightjoin是外连接。

SELECT A.id,A.name,B.id,B.name FROM A LEFT JOIN B ON A.id =B.id;

SELECT A.id,A.name,B.id,B.name FROM A RIGHT JOIN ON B A.id= B.id;

SELECT A.id,A.name,B.id,B.name FROM A INNER JOIN 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连接

第二：子查询的性能又比外连接性能慢，尽量用外连接来替换子查询。

Select * from A where exists (select * from B where id>=3000 and A.uuid=B.uuid);

A表的数据为十万级表，B表为百万级表，在本机执行差不多用2秒左右，我们可以通过explain可以查看到子查询是一个相关子查

询(DEPENDENCE SUBQUERY);Mysql是先对外表A执行全表查询，然后根据uuid逐次执行子查询，如果外层表是一个很大的

表，我们可以想象查询性能会表现比这个更加糟糕。一种简单的优化就是用innerjoin的方法来代替子查询，查询语句改为：

Select * from A inner join B ON A.uuid=B.uuid using(uuid) where b.uuid>=3000; 这个语句执行测试不到一秒；

第三：使用JOIN时候，应该用小的结果驱动打的结果（left join 左边表结果尽量小，如果有条件应该放到左边先处理，right join

同理反向），同时尽量把牵涉到多表联合的查询拆分多个query (多个表查询效率低，容易锁表和阻塞)。如：

Select * from A left join B A.id=B.ref_id where A.id>10;

可以优化为：

select * from (select * from A where id >10) T1 left join B on T1.id=B.ref_id;

4.26 exist 代替 in

SELECT * from A WHERE idin (SELECT id from B)

SELECT * from A WHERE id EXISTS (SELECT 1 from A.id= B.id)

in 是在内存中遍历比较exist 需要查询数据库，所以当B的数据量比较大时，exists效率优于in.　in()只执行一次，把B表中的所有

id字段缓存起来，之后检查A表的id是否与B表中的id相等，如果id相等则将A表的记录加入到结果集中，直到遍历完A表的所有记

录。in()适合B表比A表数据小的情况，exists()适合B表比A表数据大的情况。

针对mysql，其条件执行顺序是 从左往右，自上而下；

针对orcale，其条件执行顺序是从右往左，自下而上。