首页 > 数据库 >MYSQL新能优化策略

MYSQL新能优化策略

时间:2024-08-27 17:05:02浏览次数:12  
标签:01 优化 使用 查询 索引 MYSQL 新能 WHERE SELECT

一.一般语句优化

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百万的数据速度要慢很多。

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

标签:01,优化,使用,查询,索引,MYSQL,新能,WHERE,SELECT
From: https://www.cnblogs.com/chenlei210162701002/p/18383138

相关文章

  • 10W级并发Mysql优化
    批量插入(BatchInsert)批量处理:将多条数据合并成一个INSERT语句,一次性批量插入。这样可以显著减少数据库的交互次数,提高性能。INSERTINTOyour_table(column1,column2,...)VALUES(value1,value2,...),(value3,value4,...)批量大小:通常建议每批次的大小在几百到几......
  • mysql磁盘碎片整理
    背景数据结转过程中经常进行delete操作,产生空白空间,如果进行新的插入操作,MySQL将尝试利用这些留空的区域,但仍然无法将其彻底占用,于是造成了数据的存储位置不连续,以及物理存储顺序与理论上的排序顺序不同,久而久之就产生了碎片。碎片治理思路根据线上处理经验总结比对4种处理......
  • mysql8.0.39采用克隆方式快速搭建主从同步
    mysql8.0.39采用克隆方式快速搭建主从同步备注:基于物理文件拷贝,数据量越大,越能体现出这种优势。8.0.17以上都可以使用 一、环境192.168.0.101主库192.168.0.102从库Serverversion:8.0.39 二、查看是否已经安装克隆插件#如果没有同步账号,可以新建一个dropus......
  • Mysql特性总结
    1.发展史:瑞典几个开发者研发,2000年开源源代码,后面发展为Mysql,再后来更新mariaDB,2.功能比较具备所有数据库管理系统所需要的相关功能。数据存储引擎Innodb PostGreSQL支持四种隔离级别每种数据库管理系统都有自身的优势,也有其自身的限制。产品定位不同。mysql安装包......
  • WAMP(Windows + Apache + MySQL + PHP)是在 Windows 系统上搭建本地 Web 开发环境的一种
    WAMP(Windows+Apache+MySQL+PHP)是在Windows系统上搭建本地Web开发环境的一种方式。下面是WAMP中一些常见的问题及其解决方法:启动问题启动后WAMP托盘图标显示橙色描述:WAMP启动后托盘图标显示为橙色,表示某些服务没有正常启动。解决方法:检查Apache或MySQL......
  • XAMPP 是一个非常流行的本地开发环境,用于搭建 PHP、MySQL、Apache 等服务
    XAMPP是一个非常流行的本地开发环境,用于搭建PHP、MySQL、Apache等服务。在使用XAMPP的过程中,可能会遇到各种问题。这里总结了一些常见的问题及其解决方法:安装与配置问题安装失败描述:安装过程中出现错误或安装完成后无法正常启动。解决方法:确保安装过程中没有中断。......
  • MATLAB智能优化算法-学习笔记(1)——遗传算法求解0-1背包问题【过程+代码】
    一、问题描述(1)数学模型(2)模型总结目标函数:最大化背包中的总价值Z。约束条件:确保背包中的物品总重量不超过容量W。决策变量:每个物品是否放入背包,用0或1表示。这个数学模型是一个典型的0-1整数线性规划问题。由于其NP完全性,当问题规模较大时,求解此问题通常需要使用启发......
  • 【java计算机毕设】网上商城MySQL springcloud vue HTML maven项目设计源码带项目报告
    目录1项目功能2项目介绍3项目地址 1项目功能【java计算机毕设】网上商城MySQLspringcloudvueHTMLmaven项目设计源码带项目报告PPT前后端可分离也可不分离 2项目介绍系统功能:网上商城包括管理员、用户两种角色。管理员功能包括个人中心模块用于修改个人......
  • 电动汽车整车控制器 UDS诊断功能开发调试优化方法
    摘要:通过某电动汽车VCU开发过程中,UDS诊断功能的调试,发现一些测试中存在的问题,比如覆盖度不够、效率低下等,提出了相应的优化方法,并经过测试验证其可行性,此方法亦可推广应用至BMS、HCM等其他控制器的UDS诊断开发调试过程中。希望能给相关技术人员带来参考和帮助。一、UDS诊断功......
  • ThinkPHP 6 + PHP7.4.3nts +nginx 使用mysql和oracle数据库
    ThinkPHP6+PHP7.4.3nts+nginx使用mysql和oracle数据库.前言业务需求,之前使用的php7.3.4nts,mysql自己写的代码,需要对接第三方系统,第三方使用的oracle数据库。之前也是各种的网查,稀里糊涂的成功了。上周五又需要对接,这次用的是php7.4.3nts,各种试了两三天不行,昨晚就突然可以......