SQL优化21连击 + 思维导图

一、查询SQL尽量不要使用select *，而是具体字段

1、反例

SELECT * FROM user

2、正例

SELECT id,username,tel FROM user

3、理由

1. 节省资源、减少网络开销。
2. 可能用到覆盖索引，减少回表，提高查询效率。

注意：为节省时间，下面的样例字段都用*代替了。

二、避免在where子句中使用 or 来连接条件

1、反例

SELECT * FROM user WHERE id=1 OR salary=5000

2、正例

（1）使用union all

SELECT * FROM user WHERE id=1 
UNION ALL
SELECT * FROM user WHERE salary=5000

（2）分开两条sql写

SELECT * FROM user WHERE id=1
SELECT * FROM user WHERE salary=5000

3、理由

1. 使用 or 可能会使索引失效，从而全表扫描；
2. 对于 or 没有索引的 salary 这种情况，假设它走了 id 的索引，但是走到 salary 查询条件时，它还得全表扫描；
3. 也就是说整个过程需要三步：全表扫描+索引扫描+合并。如果它一开始就走全表扫描，直接一遍扫描就搞定；
4. 虽然 mysql 是有优化器的，出于效率与成本考虑，遇到 or 条件，索引还是可能失效的；

三、尽量使用数值替代字符串类型

1、正例

1. 主键（id）： primary key 优先使用数值类型 int ， tinyint
2. 性别（sex）：0代表女，1代表男；数据库没有布尔类型， mysql 推荐使用 tinyint

2、理由

1. 因为引擎在处理查询和连接时会逐个比较字符串中每一个字符；
2. 而对于数字型而言只需要比较一次就够了；
3. 字符会降低查询和连接的性能，并会增加存储开销；

四、使用varchar代替char

1、反例

`address` char(100) DEFAULT NULL COMMENT '地址'

2、正例

`address` varchar(100) DEFAULT NULL COMMENT '地址'

3、理由

1. varchar 变长字段按数据内容实际长度存储，存储空间小，可以节省存储空间；
2. char 按声明大小存储，不足补空格；
3. 其次对于查询来说，在一个相对较小的字段内搜索，效率更高；

五、技术延伸，char与varchar2的区别？

1、 char 的长度是固定的，而 varchar2 的长度是可以变化的。

比如，存储字符串 “101” ，对于 char(10) ，表示你存储的字符将占10个字节（包括7个空字符），在数据库中它是以空格占位的，而同样的 varchar2(10) 则只占用3个字节的长度，10只是最大值，当你存储的字符小于10时，按实际长度存储。

2、 char 的效率比 varchar2 的效率稍高。

3、何时用 char ，何时用 varchar2 ?

char 和 varchar2 是一对矛盾的统一体，两者是互补的关系， varchar2 比 char 节省空间，在效率上比 char 会稍微差一点，既想获取效率，就必须牺牲一点空间，这就是我们在数据库设计上常说的“以空间换效率”。

varchar2 虽然比 char 节省空间，但是假如一个 varchar2 列经常被修改，而且每次被修改的数据的长度不同，这会引起“行迁移”现象，而这造成多余的I/O，是数据库设计中要尽力避免的，这种情况下用 char 代替 varchar2 会更好一些。 char 中还会自动补齐空格，因为你 insert 到一个 char 字段自动补充了空格的,但是 select 后空格没有删除，因此 char 类型查询的时候一定要记得使用 trim ，这是写本文章的原因。

如果开发人员细化使用 rpad() 技巧将绑定变量转换为某种能与 char 字段相比较的类型（当然，与截断 trim 数据库列相比，填充绑定变量的做法更好一些，因为对列应用函数 trim 很容易导致无法使用该列上现有的索引），可能必须考虑到经过一段时间后列长度的变化。如果字段的大小有变化，应用就会受到影响，因为它必须修改字段宽度。

正是因为以上原因，定宽的存储空间可能导致表和相关索引比平常大出许多，还伴随着绑定变量问题，所以无论什么场合都要避免使用char类型。

六、where中使用默认值代替null

1、反例

SELECT * FROM user WHERE age IS NOT NULL

2、正例

SELECT * FROM user WHERE age>0

3、理由

1. 并不是说使用了 is null 或者 is not null 就会不走索引了，这个跟 mysql 版本以及查询成本都有关；
2. 如果 mysql 优化器发现，走索引比不走索引成本还要高，就会放弃索引，这些条件 !=，<>，is null，is not null 经常被认为让索引失效；
3. 其实是因为一般情况下，查询的成本高，优化器自动放弃索引的；
4. 如果把 null 值，换成默认值，很多时候让走索引成为可能，同时，表达意思也相对清晰一点；

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

1、反例

SELECT * FROM user WHERE salary!=5000
SELECT * FROM user WHERE salary<>5000

2、理由

1. 使用 != 和 <> 很可能会让索引失效
2. 应尽量避免在 where 子句中使用 != 或 <> 操作符，否则引擎将放弃使用索引而进行全表扫描
3. 实现业务优先，实在没办法，就只能使用，并不是不能使用

八、inner join 、left join、right join，优先使用inner join

三种连接如果结果相同，优先使用inner join，如果使用left join左边表尽量小。

• inner join 内连接，只保留两张表中完全匹配的结果集；
• left join会返回左表所有的行，即使在右表中没有匹配的记录；
• right join会返回右表所有的行，即使在左表中没有匹配的记录；

为什么？

• 如果inner join是等值连接，返回的行数比较少，所以性能相对会好一点；
• 使用了左连接，左边表数据结果尽量小，条件尽量放到左边处理，意味着返回的行数可能比较少；
• 这是mysql优化原则，就是小表驱动大表，小的数据集驱动大的数据集，从而让性能更优；

九、提高group by语句的效率

1、反例

先分组，再过滤

select job, avg（salary） from employee 
group by job
having job ='develop' or job = 'test';

2、正例

先过滤，后分组

select job，avg（salary） from employee 
where job ='develop' or job = 'test' 
group by job;

3、理由

可以在执行到该语句前，把不需要的记录过滤掉

十、清空表时优先使用truncate

truncate table 在功能上与不带 where 子句的 delete 语句相同：二者均删除表中的全部行。但 truncate table 比 delete 速度快，且使用的系统和事务日志资源少。

delete 语句每次删除一行，并在事务日志中为所删除的每行记录一项。 truncate table 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。

truncate table 删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用 DELETE。如果要删除表定义及其数据，请使用 drop table 语句。

对于由 foreign key 约束引用的表，不能使用 truncate table ，而应使用不带 where 子句的 DELETE 语句。由于 truncate table 不记录在日志中，所以它不能激活触发器。

truncate table 不能用于参与了索引视图的表。

十一、操作delete或者update语句，加个limit或者循环分批次删除

1、降低写错SQL的代价

清空表数据可不是小事情，一个手抖全没了，删库跑路？如果加limit，删错也只是丢失部分数据，可以通过binlog日志快速恢复的。

2、SQL效率很可能更高

SQL中加了 limit 1 ，如果第一条就命中目标 return ， 没有 limit 的话，还会继续执行扫描表。

3、避免长事务

delete 执行时,如果 age 加了索引，MySQL会将所有相关的行加写锁和间隙锁，所有执行相关行会被锁住，如果删除数量大，会直接影响相关业务无法使用。

4、数据量大的话，容易把CPU打满

如果你删除数据量很大时，不加 limit限制一下记录数，容易把 cpu 打满，导致越删越慢。

5、锁表

一次性删除太多数据，可能造成锁表，会有lock wait timeout exceed的错误，所以建议分批操作。

十二、UNION操作符

UNION 在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。 实际大部分应用中是不会产生重复的记录，最常见的是过程表与历史表 UNION 。如：

select username,tel from user
union
select departmentname from department

这个SQL在运行时先取出两个表的结果，再用排序空间进行排序删除重复的记录，最后返回结果集，如果表数据量大的话可能会导致用磁盘进行排序。 推荐方案：采用 UNION ALL 操作符替代 UNION ，因为 UNION ALL 操作只是简单的将两个结果合并后就返回。

十三、批量插入性能提升

1、多条提交

INSERT INTO user (id,username) VALUES(1,'哪吒编程');