mysql中优化类型不等于2的sql语句_MySQL性能优化之常用SQL语句优化

SQL性能优化的目标：至少要达到range级别，要求是ref级别，consts最高。【阿里巴巴JAVA开发手册】

说明：

1)、consts单表中最多只有一个匹配行(主键/唯一索引)，在优化阶段即可读到数据。

2)、ref指的是使用普通的索引(normal index)。

3)、range 对索引进行范围检索

反例：explain表的结果，type=index，索引物理文件全扫描，速度非常慢，这个index级别比较range还低，与全表扫描是小巫见大巫。

最优到最差顺序：system > const > eq_ref > ref > range > index > ALL

语句优化

1.简单查询语句优化

1.1 select 字段部分

a、返回具体的字段以代替*

1)、用不到的字段会消耗CPU和IO、消耗网络带宽

2)、若可以使用索引的情况下，使用*会导致回表查询，效率更低

3)、数据暴露更不安全。

b、count(*)会统计null值的行，count(列名)不会统计null值的行

select count(user_id) from dw_user where `order` is null limit 10; 结果：5875

select count(0) from dw_user where `order` is null limit 10; 结果：5875

select count(`order`) from dw_user where `order` is null limit 10; 结果：0

5.6版本：默认会选择辅助索引技术，没有的话才会走主键聚集索引技术

1.select count(1) from employees; 使用辅助联合索引计数

2.select count(id) from employees; 使用辅助索引技术

3.select count(name) from employees; 使用辅助索引技术

4.select count() from employees; 使用辅助索引技术

5.select count(id) from employees force index(PRIMARY) 强制使用主键索引

从效率角度分析：count(1) ==count()>count(name)>count(id)

因为主键索引的id对应的叶子节点中存放data数据，每个节点只能放16k的页数据，所以扫描范围不是非常广，而且比较占内存的耗时。

count(name)统计的时候，叶子节点中存放主键id，每次扫描范围更广，效率更高。

推荐使用count(*)

在MySQL 5.7 COUNT(*)默认会选择聚集索引技术，进行一次内部handler函数调用，即可快速获得该表总数。

如果聚集索引较大(或者说表数据量较大)，没有完全加载到buffer pool中的话，MySQL 5.7的查询方式有可能反而会更慢，还不如用原先的方式(MySQL 5.6)

聚集索引：就是主键id索引

非聚集索引：就是自定义的其他字段索引

c、函数统计，尽量放到内存中执行

select sum(a)+sum(b) as c

d、tinyint在使用时容易变为布尔型，代码中记得转换，用case时等于更优

case status when 0 then do1 when 1 do2 end

===》

case when status=0 then do1 when status=1 then do2 end

1.2 条件部分

a、数据类型必须一致，否则索引失效，还会增加转换的开销

SELECT * FROM t WHERE id = '19';

----->

SELECT * FROM t WHERE id = 19;

b、少用≥，直接使用＞，可提升查询效率

select * from dw_user where user_id>=101; ----—多一次等于的判断

select * from dw_user where user_id>100;

1.3 分组和排序

禁止使用order by rand()

SELECT * FROM t1 WHERE 1=1 ORDER BY RAND() LIMIT 4;

---->

SELECT * FROM t1 WHERE id >= CEIL(RAND()*1000) LIMIT 4;

分组查询时，默认分组后，还会排序，可能会降低速度

在group by 后面增加 order by null 就可以防止排序.

explain select * from emp group by deptno order by null;

1.4 索引不起效果的写法

a、使用group by、not in、not like不使用索引

2.查询语句-临时表场景

2.1 必须创建临时表的情况

如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；

如果数据量不大，为了缓和系统表的资源，应先 create table，然后 insert。

2.2 临时表的显示删除

先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。

3.插入语句

//批量插入使用/*+append*/

insert into p_nbsc_ho1 select * from p_nbsc_ho (消耗时间73分钟)

insert /*+append*/ into p_nbsc_ho2 select * from p_nbsc_ho (消耗时间7分钟)

4.更新语句

如果只更改 1、2 个字段，不要 Update 全部字段，否则频繁调用会引起明显的性能消耗，同时带来大量日志。

5.开发代码中少用truncate table

truncate table和delete功能相同，但速度更快，使用的系统和事务日志资源较少，它无事务且不触发trigger，容易造成事故。【阿里巴巴JAVA开发手册】