MySQL中使用的SQL优化手段

原创

小哥 3年前 (2022-10-29) 阅读数 124 #mysql

文章标签 sql

SQL有很多方法可以进行优化，对于通常的情况可以概括为几种：

可以通过本文中的语句添加以下表格和数据：

MySQL执行计划explain的详解_蓝星部队博客-CSDN博客 1.如何查看SQL执行计划：在MySQL在中生成执行计划的方法很简单。SQL该语句位于语句之前explain既可。explain可以对其采取行动SELECT/INSERT/UPDATE/DELETE和REPLACE声明。2.执行计划输出栏目解释：使用时。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.有索引且未使用：

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 语句或计算表达式的非索引项将减慢查询速度。

方法：

重写 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类型，您还需要具有相同的字符集。(两个表格的字符集可能不同)