MySQL查询优化方案

╰半橙微兮° 2024-03-24 10:18 81阅读 0赞

## 一、优化方案 ##

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