SQL语句优化

sql语句优化

1.查询语句尽量不要使用 * 而是需要查询的具体字段

反例：SELECT * FROM student
 
正例：SELECT id,NAME FROM student
 
理由：
   字段多时，表中数据过于庞大 select * 进行查询时，很可能不会用到索引，就会造成全表扫描，扫描了表中所有的数据，查取速度慢，浪费资源，查询具体字段节省资源、减少网络开销

2.尽量避免在where子句中使用 or 来连接条件

反例：select * from student where id = 1 or salary = 3000;

正例：  #分成两条语句分别执行
select * from student where id = 1;
select * from student where salary = 3000;

理由：
  使用or可能会使索引失效，从而造成全表扫描，例如，如果id属性有索引，而salary属性没有索引，在查询时id属性用了索引查询，or后面的salary却只能用全表扫描，如果刚开始就用全表扫描，则省去了之前对id字段的索引查询。

3.使用varchar代替var

理由：
  varchar：变长字符串，最多容纳4000个字符，按内容所占空间实际存储，查询慢，但是节省空间，例如：varchar（10）存储abc，只占3;
  char:固定长度字符串，最多容纳2000个字符，数据存储完之后的剩余空间用空格补齐，查询速度快，但是浪费空间;

4.尽量使用数值类型代替字符串类型

理由：
  数值类型比字符串类型的数据更好检索，查询速度快;
  一个汉字占3位，而一个数字只占1位，这样大大节省了存储空间;
  tinyint 1字节;smallint 2字节;mediumint 3字节;int 4字节;bigint 8字节;
  数值类型存储数据的长度都是固定的,其后面数组表示存储数据的宽度，并非长度;
  例如：tinyint(1)存储2时数据为2，int(1)存储2时数据为0002,剩余的存储空间会用零补齐，所以一般推荐使用tinyint，比较节省空间，但是存储范围为 0~255 比int小很多;
  primary key优先使用int,tinyint类型;
  数据库没有布尔类型，MySQL推荐使用tinyint;

5.查询尽量避免返回大量数据（采用分页分批向用户展示数据)

理由：
  当返回数据量过大时，查询时间也会过长，网络传输时间也会过长，查询速度也会变慢，出于效率考虑应该使用分页;

6.使用explain分析SQL执行计划（观察SQL性能）

explain  #解释、观察SQL性能，explain主要看SQL是否使用了索引
select * from dept where dname = 'maomao';

7.in 和 not in 也分情况用，否则会导致全表扫描

反例：select id from dept where num in(1,2,3) ;

对于连续的数值,能用between就不用in
正例：select id from dept where num between 1 and 3 ;

8.优化like语句

反例：
EXPLAIN 
SELECT id,NAME FROM student WHERE NAME LIKE '%1';
 
EXPLAIN 
SELECT id,NAME FROM student WHERE NAME LIKE '%1%';

正例：
EXPLAIN 
SELECT id,NAME FROM student WHERE NAME LIKE '1%'

理由：查询以1结尾或者含有1的记录会使索引失效,降低了查询效率;

9.索引不宜太多，最多控制在 5 个以内

理由：
  索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率;   
因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。    

10.避免在where中对字段进行 表达式 操作

反例：
EXPLAIN 
SELECT * FROM student WHERE id+1-1=+1;

正例：
EXPLAIN
SELECT * FROM student WHERE id=+1-1+1;
 
EXPLAIN
SELECT * FROM student WHERE id=1;

理由：
  表达式会导致引擎放弃使用索引而进行全表扫描;

11.避免在where子句中使用!=或<>操作符

反例：
explain select * from emp where salary <> 30000;
explain select * from emp where salary != 30000;
理由：
  表达式会导致引擎放弃使用索引而进行全表扫描;

12.where中使用默认值(0)代替null

#修改表，增加age字段，类型int，非空，默认值0
ALTER TABLE student ADD age INT NOT NULL DEFAULT 0;

13.提高 group by 语句的效率（先过滤，后分组）

反例：先分组,后过滤
select job，avg（salary） from employee
group by job  #先分组
having job ='president' or job = 'managent';  #后过滤

正例：先过滤,后分组
select job，avg（salary） from employee
where job ='president' or job = 'managent'  #where语句过滤
group by job;  #后分组

理由：
  where 语句先过滤时,过滤字段一般会添加索引，根据索引快速的过滤掉一部分数据，进而分组时只剩需要的少量数据，提高了SQL的查询效率;

14.排序字段创建索引

需要创建索引的字段：
  原则就是where和order by中常出现的字段就创建索引;