MySQL查询优化方案

一个搬砖的农民工

已于 2023-03-30 23:39:46 修改

阅读量1.1k

点赞数 1

分类专栏：关系型数据库文章标签： mysql 数据库 sql

于 2023-03-28 20:55:32 首次发布

本文链接：https://blog.csdn.net/weixin_44183847/article/details/129824855

版权

关系型数据库专栏收录该内容

24 篇文章 2 订阅

订阅专栏

一、优化方案

使用索引：索引可以大大提高查询的效率，可通过 EXPLAIN 命令来查看 SQL 语句是否使用了索引。
避免 SELECT *：应该尽量避免使用 SELECT *，只查询需要的数据字段，这样可以减少数据传输和查询开销。
能使用 INNER JOIN 的时候尽量不使用 IN 和 EXISTS（join只匹配一次， IN 和 EXISTS都需要每行数据进行匹配），否则尽量使用 EXISTS ，特殊情况如已知数据且数据量不大可以使用 IN(1,2,3)
在一个sql查询中不要使用过多JOIN，产生临时表会占用空间，且数据过大的时候会影响查询效率
采用合适的存储引擎：不同的存储引擎适合不同的应用场景，如 InnoDB 支持事务和行级锁定，适合高并发和写入的应用；MyISAM 不支持事务和行级锁定，但性能较好，适合大量读操作的应用。
采用分库分表：如果数据量非常大，可以采用分库分表的方式来分散查询压力，提高查询效率。
调整 MySQL 优化器的配置：通过调整查询缓存的大小、调整查询缓存的失效策略等方式，进一步提高查询性能。
优化 SQL 语句：对于复杂的 SQL 语句，可以通过分解大的查询、升级 MySQL 版本或者使用更好的算法等方式，进一步优化查询性能。
如果多个较大的表用join关联可能会较慢，因为每次join会生成临时表。可以尝试如下进行优化

SELECT b.name as name from a LEFT JOIN b on b.id = a.id  
改为
SELECT (SELECT b.name from b WHERE b.id = a.id limit 1) as name from a;

好处：加上 limit 1 后在查询到一条数据就会返回，
坏处：join 只会匹配一次，子查询会去b表匹配多次
总结：根据实际情况测试，自己亲测过 limit 1 的子查询会快点

二、其他总结

1、BETWEEN AND 和 <= 、 >=效率是相同的

例：id BETWEEN 1 AND 2 ，MySQL 会将 BETWEEN 转换为 >= 和 <= 的组合条件，因此这个查询的效率与 id >= 1 AND id <= 2 是相同的

2、IN 与 OR 是相同的

id IN (1,2) 在实现上，MySQL 会将 IN 子句转换为多个 OR 条件的形式，因此这个查询的效率与 id = 1 OR id = 2 是相同的。

在某些情况下，IN 子句的效率可能会更高。例如，当查询条件中包含多个值时，使用 IN 子句可以减少 SQL 语句的长度，从而减少网络传输和执行时间。此外，使用 IN 子句还可以更加清晰地表达查询的意图，使 SQL 语句更易于理解和维护。

3、exists 与 in 区别

exists语法：
SELECT ...
FROM table1
WHERE EXISTS (SELECT ...
              FROM table2
              WHERE ...);

in语法
SELECT ...
FROM table1
WHERE column1 IN (SELECT ...
                  FROM table2
                  WHERE ...);

当使用EXISTS时，数据库会先执行子查询，如果子查询返回至少一行记录，则会立即返回 True，不会继续查询，否则返回False。在这个过程中，外层查询的数据只会被检索，而不会被放到内存中。因此，使用EXISTS可以避免将大量数据加载到内存中，从而提高查询效率。

当使用IN时，则是先执行子查询，将子查询结果集缓存到内存中，然后对外层查询的每一行进行匹配，最终返回结果集。如果子查询里数据较大，则内存的消耗也较大。

总结：当外表小里表大的时候，尽量使用exists，如果里表小比如in(1,2,3)则可以使用in

4、JOIN 产生临时表

在 MySQL 中，JOIN 操作生成的临时表是由 MySQL 系统自动管理的。临时表的存储方式可以在 MySQL 配置文件中进行配置，具体有以下两种方式：

默认情况下，MySQL 将临时表存储在内存中，使用的是基于内存的临时表（Memory Storage Engine）。这种方式可以提高查询性能，因为内存访问速度较快。但是，内存大小是有限的，如果临时表的数据量过大，就会导致内存不足的问题。

如果内存不足或临时表的数据量过大，MySQL 会自动将临时表存储在磁盘上，使用的是基于磁盘的临时表（MyISAM Storage Engine）。这种方式可以避免内存不足的问题，但是访问磁盘的速度较慢，可能会影响查询性能。

可以通过 MySQL 配置文件中的 tmp_table_size 和 max_heap_table_size 参数来控制内存中临时表的大小，默认值都是 16MB。如果临时表的大小超过了这个值，MySQL 就会将临时表存储在磁盘上。需要注意的是，这两个参数的值必须小于等于 MySQL 配置文件中的 tmpdir 参数指定的目录所在分区的可用空间大小。

因此，MySQL 中 JOIN 操作生成的临时表默认是基于内存的临时表，但是如果内存不足或临时表的数据量过大，MySQL 会自动将临时表存储在磁盘上。可以通过配置 tmp_table_size 和 max_heap_table_size 参数来控制内存中临时表的大小。