sql优化汇总

SQL优化常用的优化方式汇总，希望对小伙伴们有帮助。

1、select查询过程中尽量避免使用select * from table，务必指明字段名称；

2、对于表数据的参看，尽量使用limit，限制向客户端传送的数据量；

3、尽量避免在where子句中对字段进行null值判断；在数据保存过程中数字字段尽量默认值0，确保字段列不存在null值；

4、尽量避免在where子句中使用!=或<>操作；

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

6、在where子句中慎用使用in和not in，避免全表扫描；

7、避免使用like查询；

8、避免在where子句中对字段进行表达式操作，特别是左侧；

9、尽量避免在where子句中对字段进行函数操作，特别是左侧；

10、当in或者not in匹配的数据量较大时，可使用exists/not exists替代；

11、建表时字段类型尽量使用数字类型，用varchar代替char；

12、尽量避免频繁创建和删除临时表，减少系统表资源的消耗；

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

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

15、尽量避免使用游标；

16、count(*)\count(1)\count(column)统计数据一致时，使用count(*)；

17、在where子句中首先进行数据索引的筛选，之后跟可能过滤的数据量进行依次由大到小进行数据过滤；

18、尽量用union all代替union；

19、不使用ORDER BY RAND();

20、尽量避免隐式类型转换；

21、JOIN优化，尽量遵循小表驱动大表，LEFT JOIN A表为驱动表，INNER JOIN MySQL会自动找出那个数据少的表作用驱动表，RIGHT JOIN B表为驱动表；

22、order by 条件尽量与where中条件一致；

23、sql语句避免过多套用，最多不要超过3层，可拆分为小sql输出或者使用开窗函数提升执行效率；

24、避免使用where 1=1；

25、使用truncate代替delete，删表前先使用truncate清理数据，再进行drop，数据删除一定要慎重，三思而行！

26、复杂sql使用explain进行执行过程分析，优化sql，提升线上sql执行效率；

explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。 

使用方法，在select语句前加上explain就可以了，如：explain select * from table；

分析结果包含以下字段：

id|select_type|table |  type | possible_keys | key | key_len | ref|rows | Extr 

id：表示查询中执行select子句或者操作表的顺序，id的值越大，代表优先级越高，越先执行。 id相同可以理解成这三个表为一组，具有同样的优先级，执行顺序由上而下，具体顺序由优化器决定。如果我们的 SQL 中存在子查询，那么 id的序号会递增，id值越大优先级越高，越先被执行 。当三个表依次嵌套，发现最里层的子查询 id最大，最先执行。

select_type：表示 select 查询的类型，主要是用于区分各种复杂的查询，例如：普通查询、联合查询、子查询等

1)、SIMPLE：表示最简单的 select 查询语句，也就是在查询中不包含子查询或者 union交并差集等操作。

2)、PRIMARY:当查询语句中包含任何复杂的子部分，最外层查询则被标记为PRIMARY；

3)、SUBQUERY:当 select 或 where 列表中包含了子查询，该子查询被标记为：SUBQUERY ；

4)、DERIVED:表示包含在from子句中的子查询的select，在我们的 from 列表中包含的子查询会被标记为derived ；

5)、UNION:如果union后边又出现的select 语句，则会被标记为union；若 union 包含在 from 子句的子查询中，外层 select 将被标记为 derived；

6)、UNION RESULT:代表从union的临时表中读取数据，而table列的<union1,4>表示用第一个和第四个select的结果进行union操作；

table：显示这一行的数据是关于哪张表的

type：连接使用了何种类型，它在 SQL优化中是一个非常重要的指标，从最好到最差的连接类型为是：

system  > const > eq_ref > ref  > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

1)、system:表仅有一行(=系统表)。这是const联接类型的一个特例,往往不需要进行磁盘IO，速度非常快;

2)、const(命中主键索引(PRIMARY KEY或UNIQUE)): 表最多有一个匹配行，它将在查询开始时被读取。因为仅有一行，在这行的列值可被优化器剩余部分认为是常数;

3)、eq_ref:查询命中是UNIQUE或PRIMARY KEY索引

4)、ref： 命中普通key索引，会找到很多个符合条件的行。

5)、ref_or_null:Or Is null  ，类似于ref，区别在于 MySQL会额外搜索包含NULL值的行。

6)、unique_subquery：子查询返回不重复的集合

7)、index_subquery ：区别于unique_subquery，用于非唯一索引，可以返回重复值

8)、range:使用索引选择行，仅检索给定范围内的行

9)、index：Index 与ALL 其实都是读全表，区别在于index是遍历索引树读取( 覆盖索引)，而ALL是从硬盘中读取

10)、ALL：将遍历全表以找到匹配的行，性能最差

possible_keys：显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句 

key：实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MYSQL会选择优化不足的索引 。这种情况下，可以在SELECT语句中使用USEINDEX（indexname）来强制使用一个索引或者用IGNORE INDEX（indexname）来强制MYSQL忽略索引 

key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好

ref：显示索引的哪一列被使用了，如果可能的话，是一个常数 

rows：MYSQL认为必须检查的用来返回请求数据的行数 (扫描行的数量)

Extra：该列包含MySQL解决查询的详细信息