SQL语句优化汇总

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

反例

select * from test

正例

select id,name,age,remark from test

理由

节省资源、减少网络开销。

可能用到覆盖索引，减少回表，提高查询效率。

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

反例

select  * from test where  id=1 or age>20

正例

使用union all

select  * from test where  id=1 union allselect  * from test where age>20

理由

使用or可能会使索引失效，从而全表扫描；

虽然mysql是有优化器的，出于效率与成本考虑，遇到or条件，索引还是可能失效的；

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

正例

理由

因为引擎在处理查询和连接时会逐个比较字符串中每一个字符；

而对于数字型而言只需要比较一次就够了；

字符会降低查询和连接的性能，并会增加存储开销；

使用代替char

理由

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

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

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

比char节省空间，在效率上比char会稍微差一点，既想获取效率收获不止sql优化pdf，就必须牺牲一点空间，这就是我们在数据库设计上常说的“以空间换效率”。

where中使用默认值代替null

反例

select * from test where age is not null

正例

select * from test where age > 0

理由

where语句中索引列使用了负向查询，可能会导致索引失效

负向查询包括：NOT、!=、、!、NOT IN、NOT LIKE等

负向查询应尽量避免出现。否则引擎将放弃使用索引而进行全表扫描

避免在where子句中使用!=或操作符

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

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

理由

清空表时优先使用

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

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

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

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

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

like通配符可能会导致索引失效MySQL优化器的最终选择，不走索引

即使完全符合索引生效的场景，考虑到实际数据量等原因，最终是否使用索引还要看MySQL优化器的判断。当然你也可以在sql语句中写明强制走某个索引。

使用分析你SQL执行计划

type

Extra常用关键字

总结其它优化方式

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

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

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

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

5、where后面的字段，留意其数据类型的隐式转换。（字符串和整数 “100” 100）

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

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

7、 伪删除设计

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

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

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

9、 count(*) from table；

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

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

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

（2）字段干净无表达式收获不止sql优化pdf，索引生效；

11、 关于临时表

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

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

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

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

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

13、 去重过滤字段要少

14、 尽量避免大事务操作，提高系统并发能力

15、 所有表必须使用存储引擎

本文到此结束，希望对大家有所帮助。