MYSQL性能优化策略

1|0一.一般语句优化

1|11.选择合适的数据类型以及字符集:

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

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

CREATE TABLE users ( is_active TINYINT(1) );

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

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

1|22.避免使用SELECT*:

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

例如:避免select*,改用具体的列

SELECT id, name, email FROM users;

1|33.合理使用JOIN,避免子查询

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

子查询会生成单独的表,占用内存,优先使用连表查询,连表查询底层是笛卡尔积。

例如:优化连接条件,确保连接上有索引

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

1|44.避免使用%开头的LIKE查询

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

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

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

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

1|55.使用批量插入,优化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;

1|66.使用查询缓存

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

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

1|77.避免使用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;

2|0二.配置参数调优

  • 调整innodb_buffer_pool_size:增大InnoDB缓冲池大小,提高缓存命中率。
  • 调整query_cache_size:指定查询缓存的大小(MYSQL8.0及以上版本已经被移除,可忽略)。
  • 调整thread_cache_size:增大线程缓存大小,减少线程创建开销。
  • 调整table_open_cache:增大表缓存大小,减少表打开的开销。

3|0三.索引

3|11.在常用查询条件和连接条件的列上创建索引(where,group by,和外键):

只要发现查询较慢,优先检查where条件后面是否创建了索引。

3|22.避免在索引列上进行函数计算:

例子:避免where year(date)= 2024,改用范围查询。

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

3|33.避免重复索引:

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

3|44.更新,修改频繁的列慎用索引:

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

CREATE INDEX idx_update_col ON table_name (update_col);

4|0四.其他避坑

4|11.避免使用SELECT DISTINCT:

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

SELECT DISTINCT name FROM users WHERE status = 'active';

4|22.使用LIMIT 1优化查询

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

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

4|33.合理使用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|44.避免在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|0五.冷热数据备份

简单来讲,什么是目前业务进场需要的数据,比如5,8年前的数据 是否业务不再进行访问,或者对数据按照(时间,某一业务)维度拆分,把数据一拆为多,减轻当前表的压力。总之,访问5千万的数据量要比访问5百万的数据速度要慢很多。

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

__EOF__

本文作者xxxx
本文链接https://www.cnblogs.com/chenlei210162701002/p/18383138.html
关于博主:评论和私信会在第一时间回复。或者直接私信我。
版权声明:本博客所有文章除特别声明外,均采用 BY-NC-SA 许可协议。转载请注明出处!
声援博主:如果您觉得文章对您有帮助,可以点击文章右下角推荐一下。您的鼓励是博主的最大动力!
posted @   哒喇  阅读(14)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 在鹅厂做java开发是什么体验
· 百万级群聊的设计实践
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
· 永远不要相信用户的输入:从 SQL 注入攻防看输入验证的重要性
· 浏览器原生「磁吸」效果!Anchor Positioning 锚点定位神器解析
点击右上角即可分享
微信分享提示