SQL优化——如何写出高效率SQL

一.在查询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、理由

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

十、表连接不宜太多，索引不宜太多，一般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) >=NOW();

2、正例

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

3、理由

使用索引列上内置函数，索引失效。

十二、创建联合索引

当查询条件有多个且固定时，应创建联合索引。对于符合条件查询创建联合索引的效率要大于创建多个单独索引。
注：

1.当查询时如果查询字段+条件字段<=索引字段（即覆盖索引），那么此次查询就只需要在索引中进行查询，不需要回表
2.当查询字段>索引字段时，即使条件字段全部覆盖在索引中，索引也成功生效。但因为索引中并未存在某些字段的值，也需要根据索引查到主键id，再回表去表中取得字段的值。
3.使用联合索引时应满足最左原则，如：创建id,name,age索引，查询条件为name,age或age则索引失效，为id,name或id索引生效

十三、多列进行order排序

排序时应按照组合索引中各列的顺序进行排序，即使索引中只有一个列是要排序的，否则排序性能会比较差。

create index IDX_USERNAME_TEL on user(deptid,position,createtime);
select username,tel from user where deptid= 1 and position = 'java开发' order by deptid,position,createtime desc; 

实际上只是查询出符合deptid= 1 and position = 'java开发'条件的记录并按createtime降序排序，但写成order by createtime desc性能较差。
注：

1.当采用order排序时，会把查出的所有数据先放到sort_buffer缓冲区中（为了避免多个线程对同一块内存进行操作带来锁竞争的问题，每个线程有自己的缓冲区）。当全部读入缓冲区后才会对结果进行排序。
2.sort_buffer缓冲区有最大限制（ InnoDB 存储引擎中，这个值是默认是256K），当数据量大于限制时MySQL会采用归并排序的思想，把要排序的数据分成若干份，每一份数据在内存中排序后会放入一个个磁盘的临时文件中最终对这些已经排序好的临时文件的数据再做一次合并排序，即文件排序。
3.如果不想走文件排序，可以通过建立联合索引，在索引中包含所有需要排序和查询的字段，这样在索引中存储的列就已经是排序好的结果，不需要回表只查询索引，就可以完美的解决这个问题了。

十四、优化like语句

模糊查询，程序员最喜欢的就是使用like，但是like很可能让你的索引失效。

1、反例

select * from citys where name like '%大连' (不使用索引)

select * from citys where name like '%大连%' (不使用索引)

2、正例

select * from citys where name like '大连%' (使用索引) 。

3、理由

• 首先尽量避免模糊查询，如果必须使用，不采用全模糊查询，也应尽量采用右模糊查询， 即like ‘…%’，是会使用索引的；

• 左模糊like ‘%...’无法直接使用索引，但可以利用reverse + function index的形式，变化成like ‘…%’；

• 全模糊查询是无法优化的，一定要使用的话建议使用搜索引擎。

十五、使用explain分析你SQL执行计划

1、type

1. system：表仅有一行，基本用不到；

2. const：表最多一行数据配合，主键查询时触发较多；

3. eq_ref：对于每个来自于前面的表的行组合，从该表中读取一行。这可能是最好的联接类型，除了const类型；

4. ref：对于每个来自于前面的表的行组合，所有有匹配索引值的行将从这张表中读取；

5. range：只检索给定范围的行，使用一个索引来选择行。当使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符，用常量比较关键字列时，可以使用range；

6. index：该联接类型与ALL相同，除了只有索引树被扫描。这通常比ALL快，因为索引文件通常比数据文件小；

7. all：全表扫描；

8. 性能排名：system > const > eq_ref > ref > range > index > all。

9. 实际sql优化中，最后达到ref或range级别。

2、Extra常用关键字

• Using index：只从索引树中获取信息，而不需要回表查询；

• Using where：WHERE子句用于限制哪一个行匹配下一个表或发送到客户。除非你专门从表中索取或检查所有行，如果Extra值不为Using where并且表联接类型为ALL或index，查询可能会有一些错误。需要回表查询。

• Using temporary：mysql常建一个临时表来容纳结果，典型情况如查询包含可以按不同情况列出列的GROUP BY和ORDER BY子句时；

六、索引失效的几种情况

1.使用or

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

当id有索引而salary没索引，如果走索引的话在id走完索引后salary再走全表扫描，这样效率反而更低，mysql帮我们优化了这种情况，使其直接全表扫描。可以通过union避免。

2.使用!=，<>，is null，is not null

使用上述查询条件可能会使得mysql觉得使用索引的效率比不使用更低，从而放弃索引。可以通过设置默认值为空值避免。

3.在查询条件的索引列上使用函数或进行计算
4.组合索引没有满足最左原则
5.使用like语句

使用like语句时只能在后面拼接模糊查询符号如:  like '张%'

6.连表查询字符集和字段类型不同，会导致需要进行转换，可能会导致索引失效

二十、一些其它优化方式

1、设计表的时候，所有表和字段都添加相应的注释。

2、SQL书写格式，关键字大小保持一致，使用缩进。

3、修改或删除重要数据前，要先备份。

4、很多时候用 exists 代替 in 是一个好的选择

5、where后面的字段，留意其数据类型的隐式转换。

未使用索引

SELECT * FROM user WHERE NAME=110

（1） 因为不加单引号时，是字符串跟数字的比较，它们类型不匹配； 

（2）MySQL会做隐式的类型转换，把它们转换为数值类型再做比较；

6、尽量把所有列定义为NOT NULL

NOT NULL列更节省空间，NULL列需要一个额外字节作为判断是否为NULL的标志位。NULL列需要注意空指针问题，NULL列在计算和比较的时候，需要注意空指针问题。

7、伪删除设计

8、数据库和表的字符集尽量统一使用UTF8

（1）可以避免乱码问题；

（2）可以避免，不同字符集比较转换，导致的索引失效问题；

9、select count(*) from table；

这样不带任何条件的count会引起全表扫描，并且没有任何业务意义，是一定要杜绝的。

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

（1）SQL解析时，如果字段相关的是表达式就进行全表扫描 ；

（2）字段干净无表达式，索引生效；

11、关于临时表

（1）避免频繁创建和删除临时表，以减少系统表资源的消耗；

（2）在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log；

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

（4）如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除。先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定；

12、索引不适合建在有大量重复数据的字段上，比如性别，排序字段应创建索引

13、去重distinct过滤字段要少

1. 带distinct的语句占用cpu时间高于不带distinct的语句

2. 当查询很多字段时，如果使用distinct，数据库引擎就会对数据进行比较，过滤掉重复数据

3. 然而这个比较、过滤的过程会占用系统资源，如cpu时间