Mysql语句优化基本原则(学习笔记)

SQL优化

1.1 使用explain分析查询语句

1. EXPLAIN SELECT * from emp where ename = 'jack';  #非索引字段

2. EXPLAIN SELECT * from emp where eid = 1;		#索引字段

效果分别如下：

分析：

Type：连接类型；执行效率(const>eq_ref>ref>range>index>all )

• All：表示全表扫描，若表中数据有上百上千万，必须要优化，否则性能堪忧。
• Index：full index scan 。index 与All 的区别为 index只遍历索引树，这通常比All快，因为索引文件通常比数据文件小。All，index虽然都是全表读取，但是index是读取索引树立的全表，All是直接读取硬盘中的全表。
• range：只检索给定范围的行，使用一个索引来选择这些范围里的行数据。这个索引是在key列显示出来的。range一般出现在有between、>、<等的情况下。
• ref：非唯一性索引扫描。返回与某个单独值所匹配的的所有行。
• eq_ref：唯一性索引扫描。对于每个索引键，表中只有一条记录与之匹配。一般是主键索引或者唯一索引
• const： 表示全表只有一行数据与查询结果匹配；
• system：表示只有一行记录；const的特例；
• possible_keys：可能用到的索引；
• key：真正用到的索引；
• ken_len：索引长度，越短越好；
• rows：查询行数；

1.2 sql优化基本原则：

已知，如果给字段添加索引，字段会被维护到B+树上；

对于字段的数据类型有以下要求：

1. 尽可能使用较小以及简单类型；
2. 尽可能设置合理长度；固定长度；
3. 尽可能添加not null，非空约束；

对于sql语句有以下要求：

1. 尽量不用*
2. 尽量减少嵌套
3. 可使用limit 限制结果；

避免索引失效：

1. 对索引列使用函数以及模糊查询都会导致索引失效；

2. 索引列避免用于计算；加减乘除等；否则索引失效

3. 避免对索引列使用 !=,<>；否则索引失效；

   ... where id !=10; 可以换成：
   ... where id >10 union ... where id <10; 	
   

4. in 和 exists;
   in ： 内部查询结果返回给外部使用；把内表与外表进行hash连接；
   exists： 对外表作循环，依次将结果交给内部执行；把外表作loop，再交给内表；
   如果内表与外表数据量差距不大，两者使用起来性能差距不大；
   如果内表数据量小用in，外表小用exists；