SQL优化 20 连击

一、查询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,'c#编程');

INSERT INTO user (id,username) VALUES(2,'妲己');

2、批量提交

INSERT INTO user (id,username) VALUES(1,'c#编程'),(2,'妲己');

3、理由

默认新增SQL有事务控制，导致每条都需要事务开启和事务提交，而批量处理是一次事务开启和提交，效率提升明显，达到一定量级，效果显著，平时看不出来。

十四、表连接不宜太多，索引不宜太多，一般5个以内

1、表连接不宜太多，一般5个以内

1. 关联的表个数越多，编译的时间和开销也就越大

2. 每次关联内存中都生成一个临时表

3. 应该把连接表拆开成较小的几个执行，可读性更高

4. 如果一定需要连接很多表才能得到数据，那么意味着这是个糟糕的设计了

5. 阿里规范中，建议多表联查三张表以下

2、索引不宜太多，一般5个以内

1. 索引并不是越多越好，虽其提高了查询的效率，但却会降低插入和更新的效率；

2. 索引可以理解为一个就是一张表，其可以存储数据，其数据就要占空间；

3. 索引表的数据是排序的，排序也是要花时间的；

4. insert或update时有可能会重建索引，如果数据量巨大，重建将进行记录的重新排序，所以建索引需要慎重考虑，视具体情况来定；

5. 一个表的索引数最好不要超过5个，若太多需要考虑一些索引是否有存在的必要；

十五、避免在索引列上使用内置函数

1、反例

SELECT * FROM user WHERE DATE_ADD(birthday,INTERVAL 7 DAY) >