MYSQL性能优化策略
一.一般语句优化
1.选择合适的数据类型以及字符集：
使用合适的数据类型可以减少存储空间和提高查询速度。这个可不能小看，数据量到达一个量级，这个就能看出明显差异。
例子：对于布尔值使用 TINYINT(1) 而不是 CHAR(1) 比如你有一个字段是表示业务状态或者是类型。
CREATE TABLE users (
is_active TINYINT(1)
);
对于仅存储英文的表，使用 latin1 而不是 utf8mb4。
CREATE TABLE messages ( content VARCHAR(255) CHARACTER SET latin1);
2.避免使用SELECT*：
仅选择必要的列，减少数据传输量。
例如：避免select*，改用具体的列
SELECT id, name, email FROM users;
3.合理使用JOIN,避免子查询
避免过多的join操作，尽量减少数据集的大小。
子查询会生成单独的表，占用内存，优先使用连表查询，连表查询底层是笛卡尔积。
例如：优化连接条件，确保连接上有索引
SELECT * FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active';
4.避免使用%开头的LIKE查询
避免使用%开头的LIKE查询，因为不能使用索引。
例如：使用全文搜索代替LIKE '%keyword%'。也就是让%在最后面
SELECT * FROM products WHERE description LIKE 'keyword%';
这个尤其重要，相信各位在各大平台网站上。很多搜索只有输入前面的字才能有结果，你输入中间的字，会查询不到，其实就是这个原理。
5.使用批量插入，优化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;
6.使用查询缓存
使用查询缓存，减少重复查询的开销。
SET GLOBAL query_cache_size = 1048576;
SET GLOBAL query_cache_type = ON;
7.避免使用having，用where代替
在可能的情况下，使用where代替having进行过滤。
where实在分组之前进行过滤，having【实在分组之后进行过滤。
SELECT user_id, COUNT(*) FROM orders
WHERE order_date > '2020-01-01'
GROUP BY user_id
HAVING COUNT(*) > 1;
二.配置参数调优
- 调整innodb_buffer_pool_size:增大InnoDB缓冲池大小，提高缓存命中率。
- 调整query_cache_size:指定查询缓存的大小（MYSQL8.0及以上版本已经被移除，可忽略）。
- 调整thread_cache_size:增大线程缓存大小，减少线程创建开销。
- 调整table_open_cache:增大表缓存大小，减少表打开的开销。
三.索引
1.在常用查询条件和连接条件的列上创建索引（where，group by，和外键）：
只要发现查询较慢，优先检查where条件后面是否创建了索引。
2.避免在索引列上进行函数计算：
例子：避免where year（date）= 2024，改用范围查询。
SELECT * FROM orders WHERE date BETWEEN '2024-06-01' AND '2024-06-30';
3.避免重复索引：
检查并删除重复的索引，减少维护开销。了解mysql底层的都知道，创建索引，就会增加一个页，重复索引无疑是给数据库增加负担。
4.更新，修改频繁的列慎用索引：
对于更新，修改频繁的列，索引会增加写操作的开销，需要慎重使用。
CREATE INDEX idx_update_col ON table_name (update_col);
四.其他避坑
1.避免使用SELECT DISTINCT:
在没有必要的情况下避免使用SELECT DISTINCT,因为它会导致额外的排序操作，增加查询的开销。
SELECT DISTINCT name FROM users WHERE status = 'active';
2.使用LIMIT 1优化查询
在只需要一条结果的查询中使用LIMIT 1可以提高性能。
SELECT * FROM users WHERE email = 'user@example.com' LIMIT 1;
3.合理使用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;
4.避免在WHERE子句中使用函数：
避免在WHERE子句中使用函数，因为会导致索引失效。
*-- 避免*
SELECT * FROM users WHERE YEAR(created_at) = 2023;
*-- 改为*
SELECT * FROM users WHERE created_at BETWEEN '2024-06-01' AND '2024-06-01';
五.冷热数据备份
简单来讲，什么是目前业务进场需要的数据，比如5，8年前的数据 是否业务不再进行访问，或者对数据按照（时间，某一业务）维度拆分，把数据一拆为多，减轻当前表的压力。总之，访问5千万的数据量要比访问5百万的数据速度要慢很多。
浙公网安备 33010602011771号