当前位置：首页 > news >正文

Mysql sql技巧与优化

news 来源：原创 2024/9/20 12:06:37

1、解决mysql同时更新、查询问题

在这里插入图片描述

2、控制查询优化 hint

在这里插入图片描述

3、优化特定类型的查

优化 COUNT() 查询

在这里插入图片描述
使用近似值
业务能接受近似值的话，使用explain拿到近似值

在这里插入图片描述

优化关联查询

在这里插入图片描述

优化子查询

在这里插入图片描述

4、优化group by和distinct

在这里插入图片描述

优化GROUP BY WITH ROLLUP

在这里插入图片描述

5、优化 limit分页

在这里插入图片描述

其他优化法办包括还使用预先计算的汇总表，或关联者到一个冗余表，冗余表只包含主键列和需要做排序数据。

6、优化SQL_CALC_FOUND_ROWS

在这里插入图片描述
说明

SQL_CALC_FOUND_ROWS 会告诉 MySQL 在执行 SELECT 查询时计算总行数，而不管 LIMIT 子句。
FOUND_ROWS() 函数会返回上一个 SELECT
SQL_CALC_FOUND_ROWS 查询的总行数。

注意事项

SQL_CALC_FOUND_ROWS 在大表或复杂查询中可能会导致性能问题，因为它会强制 MySQL 计算总行数。
使用 SQL_CALC_FOUND_ROWS 和 FOUND_ROWS() 的组合可能会被替代方案所取代，例如先执行 COUNT(*) 查询，然后再执行带 LIMIT 的查询，这样可能会更高效。

7、优化 UNION 查询

在这里插入图片描述

8、静态查询分析

在这里插入图片描述

9、如何判断sql较优 explain

在这里插入图片描述
如上图所示，用户可以通过Explain判断语句是否已最优，其中Type与Extra的主要类型与含义如下：

Type

1）ALL：Full Table Scan 全表扫描；
2）index: Full Index Scan，索引扫描；
3）range:索引范围扫描；
4）ref: 表示非唯一索引连接匹配条件；
5）eq_ref: 类似ref，区别就在使用的索引是唯一索引；

用法：当一个表有一个唯一索引，并且该索引中的所有字段都被用于连接条件时，会使用eq_ref。

SELECT * FROM orders
JOIN customers ON orders.customer_id = customers.customer_id;

在这个例子中，如果customers.customer_id是一个唯一键或主键，并且在orders.customer_id上有索引，那么可能会使用eq_ref访问方法

6）const: 常量查询，比如pk等值；
用法：当表中有一个主键或唯一索引，并且查询条件使用了常量值时，会使用const。

SELECT * FROM customers WHERE customer_id = 1;

在这个例子中，如果customer_id是主键或唯一索引，那么可能会使用const访问方法。

7）system是Const类型的特例；当查询的表只有一行的情况下，使用system。

从性能角度来看，从上往下性能越来越高，一般要求是到Range范围扫描。

Extra

1）Using filesort 排序；
表示查询需要进行文件排序。通常在 ORDER BY 子句中未使用索引时会出现。这通常是一个性能问题的信号，因为文件排序可能比较耗时。

2）Using index 使用索引可以返回请求列；
3）Using index condition 通过索引初步过滤；回表再过滤其它条件；
4）Using temporary 临时表；
表示查询需要使用临时表来存储中间结果。通常在涉及 GROUP BY、ORDER BY 或者某些复杂的查询时会出现。

5）Using where 单独出现时；一般代表表上出现全表扫描过滤；
6）Using index & Using where 使用索引返回数据；同时通过索引过滤。

Extra反映了执行计划的真实执行情况。

在这里插入图片描述

结合上图执行计划分析，C表是外部驱动表，索引方式为idx_pk，Type是Range，Extra有Using index condition、Using where以及Using MRR，
表示进行全表扫描，通过索引初步过滤，回表B再过滤其他条件。B表是从外表取数据做内循环，索引方式为i_text，扫描的列为c.b.KeyNo，这种情况说明这个执行计划相对完善。

10、sql优化建议

禁止项

1）select *，返回无用数据，过多IO消耗，以及Schema 变更问题；
2）Insert语句指定具体字段名称，不要写成insert into t1 values(…)，道理同上；
3）禁止不带WHERE，导致全表扫描以及误操作；
4）Where条件里等号左右字段类型必须一致，否则可能会产生隐式转换，无法利用索引；
5）索引列不要使用函数或表达式，否则无法利用索引。
如where length(name)=‘Admin’或where user_id+2=5；
6）Replace into，会导致主备不一致；
7）业务语句中带有DDL操作，特别是Truncate。

建议项

1）减小三表以上Join；
2）用Union all 替代Union；
3）使用Join 替代子查询；
4）不要使用 like ‘%abc%’，可以使用 like ‘abc%’；
5）Order by /distinct /group by 都可以利用索引有序性；
6）减少使用event/存储过程，通过业务逻辑实现；
7）减小where in() 条件数据量；
8）减少过于复杂的查询和拼串写法。