一.一般语句优化
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', '[email protected]'),
('Bob', '[email protected]');
在批量插入时,关闭唯一性检查和索引更新,插入完成后再开启。
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 = '[email protected]' 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百万的数据速度要慢很多。