MySQL超全优化清单执行指南-51CTO.COM

一般语句优化

先从一般的语句优化开始，其实对于很多规范大家并不陌生，可就是在用的时候，无法遵从，希望今天大家再过一遍，可以养成一种良好的数据库编码习惯。

选择合适的数据类型及字符集

使用合适的数据类型可以减少存储空间和提高查询速度。这个可不能小看，数据量到达一个量级，这个就能看出明显差异。

例子：对于布尔值使用 TINYINT(1) 而不是 CHAR(1) 比如你有一个字段是表示业务状态或者是类型。

CREATE TABLE users (
    is_active TINYINT(1)
);

对于仅存储英文的表，使用 latin1 而不是 utf8mb4。

CREATE TABLE messages (
    content VARCHAR(255) CHARACTER SET latin1
);

避免使用SELECT *

仅选择必要的列，减少数据传输量。

例子：避免 SELECT *，改用具体列名。

SELECT id, name, email FROM users;

合理使用JOIN、避免子查询

避免过多的 JOIN 操作，尽量减少数据集的大小。

例子：优化连接条件，确保连接列上有索引。

SELECT * FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active';

尽量使用 JOIN 或者 EXISTS 代替子查询。

例子：避免使用子查询，改用 JOIN。

SELECT u.name, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id;

使用UNION代替OR、优化ORDER BY和GROUP BY

确保 ORDER BY 和 GROUP BY 的列上有索引。

例子：在排序和分组列上添加索引。

CREATE INDEX idx_order_date ON orders (order_date);
SELECT * FROM orders ORDER BY order_date;

在业务允许的情况下，使用 UNION 代替 OR 条件。

例子：用两个查询的 UNION 代替一个带 OR 的查询。

SELECT id, name FROM users WHERE status = 'active'
UNION
SELECT id, name FROM users WHERE status = 'pending';

避免使用%开头的LIKE查询

避免使用 % 开头的 LIKE 查询，因为不能使用索引。

例子：使用全文本搜索代替 LIKE '%keyword%'。也就是让%在最后面

SELECT * FROM products WHERE description LIKE 'keyword%';

这个尤其重要，相信各位在各大平台网站上。很多搜索只有输入前面的字才能有结果，你输入中间的字，会查询不到，其实就是这个原理。

使用批量插入、优化INSERT操作

使用批量插入减少插入操作的开销。

INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com'), 
('Bob', 'bob@example.com');

在批量插入时，关闭唯一性检查和索引更新，插入完成后再开启（此种情况大家可根据业务来，比如当查询很频繁的时候，这样操作会影响查询效率）。

SET autocommit=0;
SET unique_checks=0;
SET foreign_key_checks=0;

-- 批量插入操作

SET unique_checks=1;
SET foreign_key_checks=1;
COMMIT;

使用查询缓存

使用查询缓存，减少重复查询的开销。

SET GLOBAL query_cache_size = 1048576;
SET GLOBAL query_cache_type = ON;

避免使用HAVING代替WHERE

在可能的情况下，使用 WHERE 代替 HAVING 进行过滤。

例子：避免使用 HAVING 过滤。

SELECT user_id, COUNT(*) FROM orders
WHERE order_date > '2020-01-01'
GROUP BY user_id
HAVING COUNT(*) > 1;

配置参数调优

该部分主要针对Mysql的配置做一些操作，这块还是相当重要的，虽然是运维领域，但熟悉Mysql的配置是我们研发的不可不会的领域。

调整innodb_buffer_pool_size

innodb_buffer_pool_size 是 InnoDB 存储引擎最重要的配置参数之一，用于指定 InnoDB 缓冲池的大小。缓冲池用于缓存数据页、索引页和 InnoDB 表的其它信息。合理设置这个参数对数据库性能有很大影响。

增大 InnoDB 缓冲池大小，提高缓存命中率。

SET GLOBAL innodb_buffer_pool_size = 2G;

但是这里要注意该值并不是越大越好。innodb_buffer_pool_size 应该设置要尽可能大，但要确保为操作系统和其他应用程序留出足够的内存。

一般建议在数据库专用服务器上设置为物理内存的 60% 到 80%。通过监控数据库性能和内存使用情况，可以进一步调整这个参数以优化数据库性能。

调整query_cache_size

query_cache_size 是用于指定查询缓存的大小。查询缓存可以缓存 SELECT 查询的结果，避免重复执行相同的查询，从而提高性能。

然而，在 MySQL 8.0 及更高版本中，查询缓存已经被完全移除。如果你使用的是 MySQL 8.0 及以上版本，可以忽略 query_cache_size 参数。

调整thread_cache_size

增大线程缓存大小，减少线程创建开销。

SET GLOBAL thread_cache_size = 100;

调整table_open_cache

增大表缓存大小，减少表打开的开销。

SET GLOBAL table_open_cache = 4000;

调整tmp_table_size和max_heap_table_size

增大临时表和堆表的最大大小，减少磁盘 I/O。

SET GLOBAL tmp_table_size = 64M;
SET GLOBAL max_heap_table_size = 64M;

调整innodb_flush_log_at_trx_commit

根据需求调整日志刷新策略，权衡性能和数据安全性。

SET GLOBAL innodb_flush_log_at_trx_commit = 2;

调整innodb_log_file_size

增大日志文件大小，减少日志文件切换的开销。

SET GLOBAL innodb_log_file_size = 256M;

调整innodb_log_buffer_size

增大日志缓冲区大小，提高写入性能。

SET GLOBAL innodb_log_buffer_size = 16M;

调整innodb_io_capacity

根据磁盘 I/O 性能调整 InnoDB I/O 容量。

SET GLOBAL innodb_io_capacity = 2000;

调整max_connections

增大最大连接数，支持更多并发连接。

SET GLOBAL max_connections = 500;

调整sort_buffer_size

增大排序缓冲区大小，提高排序操作的性能。

SET GLOBAL sort_buffer_size = 4M;

调整read_buffer_size

增大读缓冲区大小，提高顺序扫描性能。

SET GLOBAL read_buffer_size = 2M;

正确使用索引

这块是最重要的，因为假如使用不当，那么创建索引不但没有效果，反而还会成为负担。

在常用查询条件和连接条件的列上建立索引

这块很清楚，反正只要发现查询较慢，优先检查where条件后面，有没有被创建索引。

遵循最左前缀原则

这个是针对复合索引时的要求，遵循最左前缀原则。

例子：对于索引 (a, b, c)，可以用于 (a)，(a, b)，(a, b, c) 的查询。

CREATE INDEX idx_abc ON table_name (a, b, c);
SELECT * FROM table_name WHERE a = 1 AND b = 2;

避免在索引列上进行计算

例子：避免 WHERE YEAR(date) = 2020，改用范围查询。

SELECT * FROM orders WHERE date BETWEEN '2024-06-01' AND '2024-06-30';

避免重复索引

检查并删除重复的索引，减少维护开销。了解mysql底层的都知道，创建索引，就会增加一个页，重复索引无疑是给增加负担。

更新频繁的列慎用索引

对于更新频繁的列，索引会增加写操作的开销，需要慎重使用。

CREATE INDEX idx_update_col ON table_name (update_col);  
-- 如果 update_col 更新频繁，需慎用

避免过多的列使用复合索引

复合索引的列数不要太多，列数过多会增加索引的维护开销，并且可能导致索引文件过大。对此可以拆分为较少复合索引和单个索引

CREATE INDEX idx_columns ON table_name (col1, col2, col3, col4, col5);  
-- 列数太多

使用覆盖索引

这个什么意思呢，如果查询的所有列都在索引中，那么可以避免回表，提高性能。

CREATE INDEX idx_covering ON orders (order_id, order_date, customer_id);

-- 查询只涉及索引中的列
SELECT order_id, order_date, customer_id FROM orders WHERE customer_id = 123;

其他避坑

避免使用SELECT DISTINCT

在没有必要的情况下避免使用 SELECT DISTINCT，因为它会导致额外的排序操作，增加查询的开销。

-- 如果可以确定结果集不会有重复值，避免使用 DISTINCT
SELECT DISTINCT name FROM users WHERE status = 'active';

使用LIMIT 1优化查询

在只需要一条结果的查询中使用 LIMIT 1 可以提高性能。

SELECT * FROM users WHERE email = 'user@example.com' LIMIT 1;

合理使用HAVING

在可能的情况下，使用 WHERE 代替 HAVING 进行过滤，因为 HAVING 是在聚合之后进行过滤，性能较差。

SELECT user_id, COUNT(*) FROM orders
WHERE order_date > '2020-01-01'
GROUP BY user_id
HAVING COUNT(*) > 1;

-- 改为使用 WHERE
SELECT user_id, COUNT(*) AS order_count FROM orders
WHERE order_date > '2020-01-01'
GROUP BY user_id
WHERE order_count > 1;

避免在WHERE子句中使用函数

避免在 WHERE 子句中使用函数，因为会导致索引失效（这个刚才讲索引的时候提到了）。

-- 避免
SELECT * FROM users WHERE YEAR(created_at) = 2023;

-- 改为
SELECT * FROM users WHERE created_at BETWEEN '2024-06-01' AND '2024-06-01';

合理使用UNION ALL

在可能的情况下，使用 UNION ALL 代替 UNION，因为 UNION 会去重，增加开销。

SELECT name FROM employees WHERE department = 'Sales'
UNION ALL
SELECT name FROM contractors WHERE department = 'Sales';

避免在索引列上使用IS NULL或IS NOT NULL

尽量避免在索引列上使用 IS NULL 或 IS NOT NULL，因为有些存储引擎对这类查询不使用索引。

-- 避免
SELECT * FROM users WHERE email IS NULL;

-- 如果业务允许，考虑使用默认值替代 NULL
SELECT * FROM users WHERE email = '';

避免使用负条件

避免使用 NOT IN、!=、<> 等负条件，因为这些条件不能有效使用索引。

-- 避免
SELECT * FROM orders WHERE status != 'completed';

-- 改为使用正条件
SELECT * FROM orders WHERE status IN ('pending', 'processing');

合理使用分页

在大数据集分页时，避免使用 OFFSET 大量偏移，而是使用更高效的方式，如基于唯一键的范围查询。

-- 避免
SELECT * FROM orders ORDER BY order_id LIMIT 1000000, 10;

-- 改为使用范围查询
SELECT * FROM orders WHERE order_id > (SELECT order_id FROM orders ORDER BY order_id LIMIT 999999, 1) LIMIT 10;

使用适当的锁

在需要锁定的情况下，合理选择锁的类型（行锁、表锁）以避免性能问题和死锁（死锁、行锁、表锁等问题会开专栏讨论，今天不讨论）。

-- 行级锁
SELECT * FROM orders WHERE order_id = 1 FOR UPDATE;

-- 表级锁
LOCK TABLES orders WRITE;

冷热数据备份

这个什么意思呢，简单来讲，什么是目前业务经常需要的数据，比如5、8年前的数据是否业务不再进行访问，或者对数据按照（时间、某一业务）维度拆分，把数据一拆为多，减轻当表的压力。总之一个原则，访问5千万的数据量要比访问5百万的数据速度要慢很多。那就拆。

注意：这个和分库分表还不是一个概念，这个是把冷数据给清理出去，把最新的热数据放进来。

详解Explain

最后说一下这个，应该有有一部分人，对这个还不是很熟悉。

当一条查询语句在经过MySQL查询优化器的各种基于成本和规则的优化会后生成一个所谓的执行计划，这个执行计划展示了接下来具体执行查询的方式，比如多表连接的顺序是什么，对于每个表采用什么访问方法来具体执行查询等等。设计MySQL的大叔贴心的为我们提供了EXPLAIN语句来帮助我们查看某个查询语句的具体执行计划。

我们只用这个为我们服务一个点，那就是看有没有走索引，比如你加上索引了可是没有效果，那就看看执行计划，把你的sql执行前面加一个Explain。

编写查询语句

首先，编写你想要优化的查询语句。例如：

SELECT * FROM orders WHERE customer_id = 123 AND order_date > '2023-01-01';

使用 EXPLAIN

在查询语句前加上 EXPLAIN 关键字：

EXPLAIN SELECT * FROM orders WHERE customer_id = 123 AND order_date > '2023-01-01';

执行上述 EXPLAIN 语句，查看输出结果。MySQL 会返回一个包含查询执行计划的表格（例如下图）。

+----+-------------+-------+--------+---------------+---------+---------+-------------------+------+--------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref               | rows | Extra                    |
+----+-------------+-------+--------+---------------+---------+---------+-------------------+------+--------------------------+
|  1 | SIMPLE      | d     | const  | PRIMARY       | PRIMARY | 4       | const             |    1 |                          |
|  1 | SIMPLE      | e     | ref    | department_id | department_id | 4 | const             |   10 | Using where              |
+----+-------------+-------+--------+---------------+---------+---------+-------------------+------+--------------------------+

具体解释：

EXPLAIN 输出表格包含多个列，每列提供不同的查询计划信息。常见列包括：

id：查询的标识符，表示查询的执行顺序。
select_type：查询类型，如 SIMPLE（简单查询），PRIMARY（主查询），UNION（联合查询的一部分），SUBQUERY（子查询）。
table：查询涉及的表。
type：连接类型，表示MySQL如何查找行。常见类型按效率从高到低排列为：

system：表只有一行（常见于系统表）。

const：表最多有一个匹配行（索引为主键或唯一索引）。

eq_ref：对于每个来自前一个表的行，表中最多有一个匹配行。

ref：对于每个来自前一个表的行，表中可能有多个匹配行。

range：使用索引查找给定范围的行。

index：全表扫描索引。

ALL：全表扫描。

possible_keys：查询中可能使用的索引。
key：实际使用的索引。
key_len：使用的索引键长度。
ref：使用的列或常量，与索引比较。
rows： MySQL 估计的要读取的行数。
filtered：经过表条件过滤后的行百分比。
Extra：额外的信息，如 Using index（覆盖索引），Using where（使用 WHERE 子句过滤），Using filesort（文件排序），Using temporary（使用临时表）。

优化查询路径

根据 EXPLAIN 输出，采取以下措施优化查询路径：

确保使用索引

如果 type 列显示为 ALL 或 index，说明表进行了全表扫描。可以通过创建适当的索引来优化查询。例如：

CREATE INDEX idx_customer_date ON orders (customer_id, order_date);

优化查询条件

避免在索引列上使用函数或进行计算。改写查询条件以利用索引。例如：

-- 避免
SELECT * FROM orders WHERE YEAR(order_date) = 2023;

-- 改为
SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';

使用覆盖索引

如果查询只涉及索引中的列，可以避免回表，提高性能。例如：

CREATE INDEX idx_covering ON orders (customer_id, order_date, order_id);

-- 查询只涉及索引中的列
SELECT customer_id, order_date, order_id FROM orders WHERE customer_id = 123;

分解复杂查询

将复杂查询分解为多个简单查询，可以提高性能。例如：

-- 复杂查询
SELECT * FROM orders o JOIN customers c ON o.customer_id = c.id WHERE c.name = 'John Doe';

-- 分解为两个简单查询
SELECT id FROM customers WHERE name = 'John Doe';
-- 假设查询结果为 123
SELECT * FROM orders WHERE customer_id = 123;

实际示例

假设有一个 employees 表和一个 departments 表：

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department_id INT,
    hire_date DATE,
    INDEX (department_id),
    INDEX (hire_date)
);

CREATE TABLE departments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50)
);

查询所有在某个日期后加入某部门的员工：

EXPLAIN
SELECT e.id, e.first_name, e.last_name
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE d.name = 'Sales' AND e.hire_date > '2023-01-01';

示例 EXPLAIN 输出：

+----+-------------+-------+--------+---------------+---------+---------+-------------------+------+--------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref               | rows | Extra                    |
+----+-------------+-------+--------+---------------+---------+---------+-------------------+------+--------------------------+
|  1 | SIMPLE      | d     | const  | PRIMARY       | PRIMARY | 4       | const             |    1 |                          |
|  1 | SIMPLE      | e     | ref    | department_id | department_id | 4 | const             |   10 | Using where              |
+----+-------------+-------+--------+---------------+---------+---------+-------------------+------+--------------------------+

从 EXPLAIN 输出可以看出：

d 表使用了 PRIMARY 索引，类型为 const，表示是一个常量查找。
e 表使用了 department_id 索引，类型为 ref，表示引用查找。

进一步优化：

如果查询频繁，可以为 departments.name 创建索引。
确保 hire_date 上有索引。

优化后的索引创建：

CREATE INDEX idx_department_name ON departments (name);

再次执行 EXPLAIN：

EXPLAIN
SELECT e.id, e.first_name, e.last_name
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE d.name = 'Sales' AND e.hire_date > '2023-01-01';

优化后的输出可能显示更好的执行计划，减少查询时间。

总结

通过以下步骤，可以有效使用 EXPLAIN 查看查询执行计划并优化查询路径：

编写并执行 EXPLAIN 查询。
分析 EXPLAIN 输出，关注 type、possible_keys、key 和 Extra 列。
根据输出信息优化索引、查询条件和表结构。
重新执行 EXPLAIN，验证优化效果。