MySQL技能完整学习列表6、查询优化——1、EXPLAIN命令的使用——2、索引优化

​

目录

查询缓存

1. 启用查询缓存

2. 查询缓存的工作原理

3. 查询缓存的限制和注意事项

4. 示例：使用查询缓存提高性能

SQL优化技巧

1. 选择最有利的字段顺序

2. 使用索引

3. 避免使用SELECT *

4. 使用连接（JOIN）代替子查询

5. 使用LIMIT分页

6. 避免使用LIKE操作符的前导通配符

7. 定期优化表和修复索引

查询缓存

MySQL的查询缓存是一种用于存储SELECT语句结果集的机制。当相同的SELECT语句再次被执行时，MySQL可以直接从查询缓存中获取结果，而不需要再次执行查询。这可以显著提高查询性能，减少数据库负载。

以下是关于MySQL查询缓存的详细说明和示例：

1. 启用查询缓存

在使用查询缓存之前，需要确保MySQL的查询缓存功能已启用。可以通过设置query_cache_type系统变量来启用查询缓存。将该变量的值设置为1将启用查询缓存，设置为0将禁用查询缓存。

例如，可以在MySQL配置文件（通常是my.cnf或my.ini）中添加以下行来启用查询缓存：

[mysqld]
query_cache_type = 1

重新启动MySQL服务器后，查询缓存功能将生效。

2. 查询缓存的工作原理

当执行一个SELECT语句时，MySQL首先检查查询缓存中是否存在与该语句完全相同的缓存结果。如果存在匹配的结果，MySQL将直接从缓存中获取结果集并返回给客户端，而不再执行实际的查询。如果缓存中没有匹配的结果，MySQL将执行查询并将结果存储在查询缓存中，以便后续的相同查询可以直接使用。

需要注意的是，查询缓存是基于语句的文本进行比较的，所以即使两个查询在逻辑上是等价的，但如果它们的文本表示不同（例如，空格、注释或大小写不同），它们将被视为不同的查询。

3. 查询缓存的限制和注意事项

• 查询缓存对于写密集型的数据库环境可能不太适用，因为每次数据修改（INSERT、UPDATE、DELETE）都会导致相关的缓存结果失效。
• 查询缓存在处理大量数据时可能会占用较多的内存空间，因此需要合理设置查询缓存的大小。
• 查询缓存在某些情况下可能会导致性能下降，例如在高并发环境下，多个线程频繁地访问和更新缓存可能会导致竞争和锁争用。

4. 示例：使用查询缓存提高性能

假设有一个名为users的表，其中包含以下结构：

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT
);

插入一些示例数据：

INSERT INTO users (id, name, age) VALUES (1, 'Alice', 25), (2, 'Bob', 30), (3, 'Charlie', 20);

现在执行以下SELECT语句：

SELECT * FROM users WHERE age = 25;

假设该查询被频繁执行，而且数据不经常变化。启用查询缓存后，MySQL将存储该查询的结果集在查询缓存中。当相同的查询再次执行时，MySQL可以直接从缓存中获取结果，而不需要再次执行实际的查询。这可以显著提高性能，特别是对于读取密集型的应用场景。

SQL优化技巧

MySQL的SQL优化是一个关键的任务，可以显著提高数据库的性能。下面是一些常用的SQL优化技巧，以及具体的示例：

1. 选择最有利的字段顺序

在查询中，将最有限制性的条件放在前面，可以更快地过滤数据。

示例:

-- 优化前
SELECT * FROM users WHERE age > 20 AND country = 'China';

-- 优化后
SELECT * FROM users WHERE country = 'China' AND age > 20;

2. 使用索引

为经常查询的字段和WHERE子句中的条件字段建立索引。

示例:

-- 创建索引
CREATE INDEX idx_name ON users(name);

-- 优化查询
SELECT * FROM users WHERE name = 'John Doe';

3. 避免使用SELECT *

只选择需要的字段，而不是所有字段。

示例:

-- 优化前
SELECT * FROM users WHERE id = 1;

-- 优化后
SELECT id, name FROM users WHERE id = 1;

4. 使用连接（JOIN）代替子查询

连接通常比子查询更高效。

示例:

-- 优化前（子查询）
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 100);

-- 优化后（连接）
SELECT users.* FROM users JOIN orders ON users.id = orders.user_id WHERE orders.amount > 100;

5. 使用LIMIT分页

对于大量数据的查询，使用LIMIT分页可以提高性能。

示例:

SELECT * FROM users LIMIT 10 OFFSET 30; -- 获取第4页的数据，每页10条记录

6. 避免使用LIKE操作符的前导通配符

使用LIKE操作符时，避免在前导位置使用通配符，这样可以利用索引。

示例:

-- 优化前（使用前导通配符）
SELECT * FROM users WHERE name LIKE '%John%';

-- 优化后（不使用前导通配符）
SELECT * FROM users WHERE name LIKE 'John%';

7. 定期优化表和修复索引

使用OPTIMIZE TABLE命令来定期优化表和修复索引。这有助于提高查询性能。

示例:

OPTIMIZE TABLE users; -- 优化表
REPAIR TABLE users; -- 修复表

​