Sql优化总结

1，sql优化概要:

　　Sql优化就是指语句在执行的时候效率不是那么乐观所以提高Sql优化就是指在数据库的执行速度的，可通过配置搜索引擎，加索引，分库分表等等，对Sql的各列进行精确取其范围值，然后尽量避免全局扫描等等，Sql优化有很多，下面的简单的总结一下吧。

1.1，sql优化的规则:

不要有超过5个以上的表连接（JOIN）
考虑使用临时表或表变量存放中间结果。
少用子查询
视图嵌套不要过深,一般视图嵌套不要超过2个为宜。

解释:
- 连接的表越多，其编译的时间和连接的开销也越大，性能越不好控制。
- 最好是把连接拆开成较小的几个部分逐个顺序执行。
- 优先执行那些能够大量减少结果的连接。
- 拆分的好处不仅仅是减少SQL Server优化的时间，更使得SQL语句能够以你可以预测的方式和顺序执行。
- 如果一定需要连接很多表才能得到数据，那么很可能意味着设计上的缺陷。
- 连接是outer join，非常不好。因为outer join意味着必须对左表或右表查询所有行。
- 如果表很大而没有相应的where语句，那么outer join很容易导致table scan或index scan。
- 要尽量使用inner join避免scan整个表。

1.2，sql查询缓慢的原因:

原因:
- 数据量过大。
- 表设计不合理。
- sql语句写得不好。
- 没有合理使用索引。
sql语句优化:
- 查询语句中不要使用 ,任何地方都不要用 select * from table ，用具体的字段列表替换""，不要返回用不到的字段
- 尽量减少子查询，使用关联查询（left join,right join,inner join）替代。
- 减少使用IN或者NOT IN ,使用exists，not exists或者关联查询语句替代。
- or 的查询尽量用 union或者union all 代替(在确认没有重复数据或者不用剔除重复数据时，union all会更好)。
- 合理的增加冗余的字段（减少表的联接查询）。
- 增加中间表进行优化（这个主要是在统计报表的场景，后台开定时任务将数据先统计好，尽量不要在查询的时候去统计）。
- 建表的时候能使用数字类型的字段就使用数字类型（type,status...），数字类型的字段作为条件查询比字符串的快。
- 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的最末尾。
- 使用"临时表"暂存中间结果的好处:
  - 避免程序中多次扫描主表，减少程序执行“共享锁”阻塞“更新锁”，减少了阻塞，提高了并发性能。
  - 尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。
  - 避免频繁创建和删除临时表，以减少系统资源的浪费。
  - 尽量避免向客户端返回大数据量，若数据量过大，应考虑相应需求是否合理。
索引优化：
- 如果针对sql语句已经没啥可以优化的，那我们就要考虑加索引了(下面有对索引的详细介绍，以及Mysql底层为什么选用b+树的介绍)

1.3，sql语句优化:

1）合理使用like模糊查询：
关键词 %姜小鱼%，由于姜小鱼前面用到了“%”，因此该查询必然走全表扫描，除非必要，否则不要在关键词前加%　

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

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

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

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

SELECT id FROM A WHERE num IS NULL --会造全表扫描
SELECT id FROM A WHERE num=0 --优化成num上设置默认值0，确保表中num没有null值

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

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    --优化后

5）where子句使用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    --不会造成全表扫描

6)where子句中对字段进行表达式操作的优化:
不要在where子句中的“=”左边进行函数、算数运算或其他表达式运算，否则系统将可能无法正确使用索引。

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'　　--不会造成索引失效

7)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;

8)批量插入优化

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)

9)利用limit 1 、top 1 取得一行:
有时要查询一张表时，你知道只需要看一条记录，你可能去查询一条特殊的记录。可以使用limit 1 或者 top 1 来终止数据库索引继续扫描整个表或索引。

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

10)尽量不要使用 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 --优化之后

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 wehre id >10) T1 left join B on T1.id=B.ref_id;`

11)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表数据大的情况。

12)其他优化方面:

排序的索引问题　
- Mysql查询只是用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求情况下不要使用排序操作，尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。
尽量用 union all 替换 union
- union和union all的差异主要是前者需要将两个（或者多个）结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的cpu运算，加大资源消耗及延迟。所以当我们可以确认不可能出现重复结果集或者不在乎重复结果集的时候，尽量使用union all而不是union
避免类型转换
- 这里所说的“类型转换”是指where子句中出现column字段的类型和传入的参数类型不一致的时候发生的类型转换。人为的上通过转换函数进行转换，直接导致mysql无法使用索引。如果非要转型，应该在传入参数上进行转换，例如utime 是datetime类型，传入的参数是“2016-07-23”，在比较大小时通常是 date（utime）>"2016-07-23",可以优化为utime>"2016-07-23 00：00：00"
尽可能使用更小的字段　　　　　　　　
- MySQL从磁盘读取数据后是存储到内存中的，然后使用cpu周期和磁盘I/O读取它，这意味着越小的数据类型占用的空间越小，从磁盘读或打包到内存的效率都更好，但也不要太过执着减小数据类型，要是以后应用程序发生什么变化就没有空间了，修改表将需要重构，间接地可能引起代码的改变，这是很头疼的问题，因此需要找到一个平衡点。

2，索引的概要:

　　索引对查询的速度有着至关重要的影响，理解索引也是进行数据库性能调优的起点。考虑如下情况，假设数据库中一个表有10^6条记录，DBMS的页面大小为4K，并存储100条记录。如果没有索引，查询将对整个表进行扫描，最坏的情况下，如果所有数据页都不在内存，需要读取10^4个页面，如果这10^4个页面在磁盘上随机分布，需要进行10^4次I/O，假设磁盘每次I/O时间为10ms(忽略数据传输时间)，则总共需要100s(但实际上要好很多很多)。如果对之建立B-Tree索引，则只需要进行log100(10^6)=3次页面读取，最坏情况下耗时30ms。这就是索引带来的效果，很多时候，当你的应用程序进行SQL查询速度很慢时，应该想想是否可以建索引。

2.1，索引的分类:

　　索引是在存储引擎中实现的，也就是说不同的存储引擎，会使用不同的索引。MyISAM和InnoDB存储引擎：只支持BTREE索引，也就是说默认使用BTREE，不能够更换，MEMORY/HEAP存储引擎，支持HASH和BTREE索引。

索引我们分为四类来讲单列索引(普通索引，唯一索引，主键索引)、组合索引、全文索引、空间索引、
- 单列索引:一个索引只包含单个列，但一个表中可以有多个单列索引。这里不要搞混淆了。
- 普通索引:MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点。
- 唯一索引:索引列中的值必须是唯一的，但是允许为空值，
- 主键索引:是一种特殊的唯一索引，不允许有空值。（主键约束，就是一个主键索引）
- 组合索引：在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。例如，这里由id、name和age3个字段构成的索引，索引行中就按id/name/age的顺序存放，索引可以索引下面字段组合(id，name，age)、(id，name)或者(id)。如果要查询的字段不构成索引最左面的前缀，那么就不会是用索引，比如，age或者（name，age）组合就不会使用索引查询
- 全文索引：全文索引，只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引，介绍了要求，说说什么是全文索引，就是在一堆文字中，通过其中的某个关键字等，就能找到该字段所属的记录行，比如有"你是个大煞笔，二货 ..." 通过大煞笔，可能就可以找到该条记录。这里说的是可能，因为全文索引的使用涉及了很多细节，我们只需要知道这个大概意思。
- 空间索引：空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有四种，GEOMETRY、POINT、LINESTRING、POLYGON。在创建空间索引时，使用SPATIAL关键字。要求，引擎为MyISAM，创建空间索引的列，必须将其声明为NOT NULL。可能跟游戏开发有关。

2.2，Mysql索引为什么使用B+树实现:

从上图你能看到，一个内结点x若含有n[x]个关键字，那么x将含有n[x]+1个子女

　　为什么说B+-tree比B 树更适合实际应用中操作系统的文件索引和数据库索引？
　　　　树的磁盘读写代价更低：
　　　　　　B+树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对于B树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数　　　　　　　　　　　　　　　　　　
　　　　　　也就降低了。
　　　　B+-tree的查询效率更加稳定:
　　　　　　由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

2.3，MySQL索引实现:

　　MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶结点data域保存了完整的数据记录。

2.4，有可能造成索引失效的几种情况[索引以最左前缀原则使用的]:

使用like关键字模糊查询时，% 放在前面索引不起作用，只有“%”不在第一个位置，索引才会生效（like '%文'--索引不起作用）
使用联合索引时，只有查询条件中使用了这些字段中的第一个字段，索引才会生效
使用OR关键字的查询，查询语句的查询条件中只有OR关键字，且OR前后的两个条件中的列都是索引时，索引才会生效，否则索引不生效。
尽量避免在where子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描。
对查询进行优化，应尽量避免全表扫描，首先应考虑在where以及order by涉及的列上建立索引。
应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：
　 select id from t where num/2=100
　应改为:
　 select id from t where num=100*2
尽量避免在where子句中对字段进行函数操作,将导致引擎放弃使用索引而进行全表扫描。
不要在 where 子句中的“=”左边进行函数. 算术运算或其他表达式运算，否则系统将可能无法正确使用索引。
并不是所有的索引对查询都有效，sql是根据表中的数据来进行查询优化的，当索引列有大量数据重复时，sql查询不会去利用索引，如一表中有字段sex，male,female几乎个一半，那么即使在sex上建立了索引也对查询效率起不了作用。
索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。
尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销,这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
mysql查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的,因此数据库默认排序可以符合要求的情况下不要使用排序操作，尽量不要包含多个列的排序，如果需要最好给这些列建复合索引。
order by 索引，不起作用的问题（除了主键索引之外）：
1)如果select 只查询索引字段，order by 索引字段会用到索引，要不然就是全表排列；
2)如果有where 条件，比如where vtype=1 order by vtype asc . 这样order by 也会用到索引！

posted @ 2019-06-27 14:00 姜煜阅读(3001) 评论(0) 编辑收藏举报

刷新页面返回顶部

屢敗屢戰