MySQL中常用的SQL优化方法

SQL优化的方法有很多种，针对平时的情况总结一下几种：

以下用到的表和数据，可以通过这篇文章中的语句添加：

MySQL执行计划explain的详解_蓝星部队的博客-CSDN博客一、如何查看SQL执行计划：在MySQL中生成执行计划的方法很简单，在SQL语句前面加explain既可。explain可以作用于SELECT/INSERT/UPDATE/DELETE和REPLACE语句。二、执行计划输出列解读：当使用explain时，输出中包含的列为：id，select_type，table，type，possible_keys，key，key_len，ref，rows，filtered， Extra1、ID包含一组数字，表示select查询的序列号，标识执行的顺https://blog.csdn.net/lanxingbudui/article/details/116104884?spm=1001.2014.3001.5501

一、有索引而未被用到：

1、Like的参数以通配符%开头时，数据库引擎会放弃使用索引而进行全表扫描。

以通配符开头的sql语句，是全表扫描，没有使用到索引，不建议使用：

explain select * from teacher where tname like '%10';

不以通配符开头的sql语句，使用到了索引，是有范围的查找：

explain select * from teacher where tname like '10%';

 2、where条件不符合最左原则：

假设有这样一个索引——(a,b,c)，必须用到第一个字段a，才会走索引。否则如select * from table where b='b' and c='c'，就不会使用到索引。

关于详细的最左匹配原则可以查看这篇文章：MySQL查询不走索引的原因--最左匹配原则_蓝星部队的博客-CSDN博客查询条件最左以通配符%开始，例如where a like '%xx'查询条件使用IS NULL 或 IS NOT NULL查询条件在字段上进行数据运算或函数运算（包括隐式数据转换）索引列不能参与计算，保持列“干净”查询条件使用负向查询条件：NOT,!=,<>,!<,!>,NOT IN, NOT LIKE 等。https://blog.csdn.net/lanxingbudui/article/details/117063956?spm=1001.2014.3001.5501

3、索引列使用 != 或 <> 操作符时，数据库引擎会放弃使用索引而进行全表扫描。使用>或<会比较高效。

4、在 where 子句中对索引列进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。

 5、在where子句中对索引列进行null值判断，引擎放弃使用索引而进行全表扫描。

如： 低效：select * from teacher where note is null ;

可以在note 上设置默认值0，确保表中note 列没有null值，然后这样查询：

高效：select * from teacher where note= 0;

6、在where子句中使用or来连接条件，导致引擎放弃使用索引而进行全表扫描。

如： 低效：select * from teacher where note = 12 or note = 122;

可以用下面这样的查询代替上面的 or 查询：

高效：select * from teacher where note = 12 union all select * from teacher where note = 122;

或者：select * from teacher where note in (12, 122);

二、避免select *

        在解析的过程中，会将 ' * ' 依次转换成表的所有列名，这个工作是通过查询数据字典完成的，这意味着将耗费更多的时间。所以，应该养成一个需要什么就取什么的好习惯。

三、order by 语句优化

        任何在Order by语句的非索引项或者有计算表达式都将降低查询速度。

方法：

        1.重写order by语句以使用索引；

         2.为order by所使用的列建立另外一个索引;

        3.绝对避免在order by子句中使用表达式。

四、GROUP BY语句优化

提高GROUP BY 语句的效率, 可以通过将不需要的记录在GROUP BY 之前过滤掉。

--低效：
select c_id, sum(score) from sc where group by c_id having score > 100;

--高效：
select c_id, sum(score) from sc where score > 100 group by c_id ;

五、使用 varchar/nvarchar 代替 char/nchar

尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

六、能用DISTINCT的就不用GROUP BY

select c_id from sc where score > 100 group by c_id

可改为：

select distinct c_id from sc where score > 100

七、能用UNION ALL就不要用UNION

UNION ALL不执行SELECT DISTINCT函数，这样就会减少很多不必要的资源。

八、在Join表的时候使用相当类型的例，并将其索引

        如果应用程序有很多JOIN 查询，你应该确认两个表中Join的字段是被建过索引的。这样，MySQL内部会启动为你优化Join的SQL语句的机制。而且，这些被用来Join的字段，应该是相同的类型的。例如：如果你要把 DECIMAL 字段和一个 INT 字段Join在一起，MySQL就无法使用它们的索引。对于那些STRING类型，还需要有相同的字符集才行。（两个表的字符集有可能不一样）