人麻了,刚面试入职就遇到MySQL亿级大表调优...

📢📢📢📣📣📣
哈喽!大家好,我是【IT邦德】,江湖人称jeames007,10余年DBA及大数据工作经验
一位上进心十足的【大数据领域博主】!😜😜😜
中国DBA联盟(ACDU)成员,目前服务于工业互联网
擅长主流Oracle、MySQL、PG、高斯及Greenplum运维开发,备份恢复,安装迁移,性能优化、故障应急处理等。
✨ 如果有对【数据库】感兴趣的【小可爱】,欢迎关注【IT邦德】💞💞💞
❤️❤️❤️感谢各位大可爱小可爱!❤️❤️❤️

文章目录

    • 📣 1.背景
    • 📣 2.方案
    • 📣 3.大表转分区
      • ✨ 3.1 数据库参数调整
      • ✨ 3.2 分别导出表结构和数据
      • ✨ 3.3 备份原表
      • ✨ 3.4 新建原表
      • 3.5 在线分区
    • 📣 4.SQL调优
      • ✨ 4.1 慢查询开启
      • ✨ 4.2 DB参数优化
      • ✨ 4.3 索引创建原则
      • ✨ 4.4 查询建议
    • 📣 5.总结


想起以前刚面试入职的第一家公司,也是自己真正入行DBA,就遇到了MySQL 亿级大表调优这个事儿!

📣 1.背景

想起以前刚面试入职的第一家公司,也是自己真正入行DBA,就遇到了MySQL亿级大表调优这个事儿!** 实例(主从复制架构)*** 告警中每天凌晨在Zabbix报警,从报警来看存在一定的主从延迟。** 实例的慢查询数量在慢查询记录中很多,系统数据写入很大,大多是都是一些历史数据,** 应用那方每天在做手动删除一个月前数据的任务,应用每天都在抱怨,备份蛮烦,日常运维太闹心了,那接下来我们就开始做大表转分区及慢查询调优的工作

📣 2.方案

对于业务繁忙的数据库来说,在运行了一定时间后,往往会产生一些数据量较大的表,特别是对于每天新增数据较多的日志表或者流水表,大表对于日常的运维非常的不方便,特别是数据的清理、迁移,表的访问性能也会随着数据量的增大而受到影响,因此,对于大表我们需要进行优化拆分,通常拆分的方案有

📣 3.大表转分区

✨ 3.1 数据库参数调整

–导出时设置
set global wait_timeout=28800000;
set global net_read_timeout=28800;
set global net_write_timeout=28800;
set global max_allowed_packet=1G;

导入时设置
#关闭二进制日志
set sql_log_bin=0;
##默认为1时代表每一次事务提交都直接将日志写入硬盘
将其修改为2时代表不直接写入硬盘而是写入系统缓存,等待定时flush到硬盘
set global innodb_flush_log_at_trx_commit = 2;
##当每进行20000次事务提交之后,MySQL将进行一次fsync之类的
磁盘同步指令来将binlog_cache中的数据强制写入磁盘。
set global sync_binlog = 20000;
set global max_allowed_packet=110241024*1024;
set global net_buffer_length=100000;
set global interactive_timeout=28800000;
set global wait_timeout=28800000;

✨ 3.2 分别导出表结构和数据

目的是导出表结构重新建表,并将导出的数据导入分布表

--导出表结构
mysqldump -uroot -proot -h192.168.6.10 -P3306 --databases XXX \
--tables XXX --single-transaction \
--hex-blob --no-data --routines --events --triggers --master-data=2 --set-gtid-purged=OFF \
--default-character-set=utf8 | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' \
-e 's/DEFINER[ ]*=.*FUNCTION/FUNCTION/' \
-e 's/DEFINER[ ]*=.*PROCEDURE/PROCEDURE/' \
-e 's/DEFINER[ ]*=.*TRIGGER/TRIGGER/' \
-e 's/DEFINER[ ]*=.*EVENT/EVENT/' \
-e 's/DEFINER[ ]*=.*SQL SECURITY DEFINER/SQL SECURITY DEFINER/' \
> /home/mysql/backup/XXX_ddl.sql

--导出数据,可以带条件  --where="column1=1"
mysqldump -uroot -proot -h192.168.6.10 -P3306 --databases XXX \
--tables XXX \
--single-transaction --hex-blob --no-create-info \
--skip-triggers --master-data=2 \
--default-character-set=utf8 > /home/mysql/backup/XXX_data.sql

✨ 3.3 备份原表

RENAME TABLE ### TO XXXX;
在进行表重命名时,需要注意以下几点:
1.确保新表名不与现有表名冲突:
在重命名表时,需要确保新表名在当前数据库中是唯一的,
以避免与现有表名发生冲突。
2.检查外键关联:如果表存在外键关联,
那么在重命名表时需要确保外键关联的表名也相应地进行了更新。

✨ 3.4 新建原表

mysql -uroot -proot
-h192.168.6.10 -P3306 数据库名
-f --default-character-set=utf8 <XXX_ddl.sql
说明:通过dump导入的方式,就可以新建原表

3.5 在线分区

alter table account_history partition by range(to_days(create_time))
(
 PARTITION create_time_202401 
 VALUES LESS THAN (to_days('2024-02-01')),
 PARTITION create_time_202402 
 VALUES LESS THAN (to_days('2024-03-01')),
 PARTITION create_time_DEFAULTE 
 VALUES LESS THAN MAXVALUE 
); 

结果报错:
Error Code: 1503. A UNIQUE INDEX must
include all columns in the table's partitioning function'
主键必须包含分区字段才可以
ALTER TABLE account_history DROP PRIMARY KEY, 
ADD PRIMARY KEY(`id`,`create_time`);

如何将分区表转换回普通表
ALTER TABLE account_history remove partitioning;

分区的过程是将一个表或索引分解为多个更小、更可管理的部分。
对于开发者而言,分区后的表使用方式和不分区基本上还是一模一样,
只不过在物理存储上,
原本该表只有一个数据文件,现在变成了多个,
每个分区都是独立的对象,可以独自处理,
也可以作为一个更大对象的一部分进行处理。
为此落实在数据库端的历史检索SQL响应时间就缩短到1-5秒时间范围

📣 4.SQL调优

✨ 4.1 慢查询开启

慢查询日志(slow_query_log)主要用来记录执行时间超过设置的某个时长的SQL语句,
能够帮助数据库维护人员找出执行时间比较长、
执行效率比较低的SQL语句,并对这些SQL语句进行针对性优化。
• 慢查询日志可以帮助 DBA 找出执行效率缓慢的 SQ语句,
为数据库优化工作提供帮助。
• 慢查询日志默认是不开启的,
建议开启慢查询日志。
• 当需要进行采样分析时手工开启。
除了在文件中配置开启慢查询日志外,
也可以在MySQL命令行中修改参数开启慢查询日志
mysql> SET GLOBAL slow_query_log = 1;
mysql> SET GLOBAL slow_query_log_file = ‘/data/mysql/log/query_log/slow_statement.log’;
mysql> SET GLOBAL long_query_time = 10;
mysql> SET GLOBAL log_output = ‘FILE’;

✨ 4.2 DB参数优化

在这里我列出了官方推荐以及我实践中的稳定性良好的可靠的参数,
以 InnoDB 为主。
--Connections
# 保持在缓存中的可用连接线程
# default = -1(无)
thread_cache_size = 16
# 最大的连接线程数(关系型数据库)
# default = 151
max_connections = 1000
# 最大的连接线程数(文档型/KV型)
# default = 100
#mysqlx_max_connections = 700

--缓冲区 Buffer
# 缓冲区单位大小;default = 128M
innodb_buffer_pool_size = 128M
# 缓冲区总大小,内存的70%,单位大小的倍数
# default = 128M
innodb_buffer_pool_size = 6G
# 以上两个参数的设定,MySQL会自动改变 innodb_buffer_pool_instances 的值

--I/O 线程数
# 异步I/O子系统
# default = NO
innodb_use_native_aio = NO
# 读数据线程数
# default = 4
innodb_read_io_threads = 32
# 写入数据线程数
# default = 4
innodb_write_io_threads = 32

--Open cache
# default = 5000
open_files_limit = 10000
# 计算公式:MAX((open_files_limit-10-max_connections)/2,400)
# default = 4000
table_open_cache = 4495
# 超过16核的硬件,肯定要增加,以发挥出最大性能
# default = 16
table_open_cache_instances = 32

✨ 4.3 索引创建原则

过多查询的表,过少写入的表。
数据量过大导致的查询效率慢。
经常作为条件查询的列。
批量的重复值,不适合创建索引;比如<业务状态>列
值过少重复的列,适合创建索引;比如、列

尽量能够覆盖常用字段
字段值区分度高
字段长度小(合适的长度,不是越小越好,至少能足够区分每个值)
相对低频的写入操作,以及高频的查询操作的表和字段上建立索引
通过非聚集索引检索记录的时候,需要2次操作,先在非聚集索引中检索出主键,然后再到聚集索引中检索出主键对应的记录,这个过程叫做回表,比聚集索引多了一次操作。

✨ 4.4 查询建议

1.避免使用*,以避免回表查询。
2.不常用的查询列或text类型的列,尽量以单独的扩展表存放。
3.条件避免使用函数。
4.条件避免过多的or,建议使用in()/union代替,
in中的数据不可以极端海量,至少个数小于1000比较稳妥。
5.避免子查询,子查询的结果集是临时表不支持索引、或结果集过大、或重复扫描子表;
以join代替子查询,尽量以inner join代替最为妥当。
6.避免使用’%Sol%'查询,或以’Sol%'代替。

📣 5.总结

MySQL是一款广泛使用的关系型数据库管理系统。
随着数据量的增加和应用需求的变化,数据库性能调优变得越来越重要。
本文介绍的MySQL调优的经验,并通过实例分析,
帮助您更好地理解如何提高数据库性能。

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:/a/362284.html

如若内容造成侵权/违法违规/事实不符,请联系我们进行投诉反馈qq邮箱809451989@qq.com,一经查实,立即删除!

相关文章

基于微服务的高考志愿智能辅助决策系统(附源码)

目录 一.引言 1、编写目的 2、系统功能概述 二.功能分析 三.微服务模块 1、微服务用户相关模块 &#xff08;1&#xff09;用户注册 &#xff08;2&#xff09;用户登录 &#xff08;3&#xff09;用户信息管理 &#xff08;4&#xff09;用户操作 2、微服务文件云存…

TensorFlow2实战-系列教程13:Resnet实战1

&#x1f9e1;&#x1f49b;&#x1f49a;TensorFlow2实战-系列教程 总目录 有任何问题欢迎在下面留言 本篇文章的代码运行界面均在Jupyter Notebook中进行 本篇文章配套的代码资源已经上传 Resnet实战1 Resnet实战2 Resnet实战3 1、残差连接 深度学习中出现了随着网络的堆叠…

SpringCloud Gateway(4.1.0) 返回503:原因分析与解决方案

文章目录 一、环境版本二、原因分析三、解决方案 一、环境版本 Versionspring-cloud-dependencies2023.0.0spring-cloud-starter-gateway4.1.0Nacosv2.3.0 二、原因分析 在 Spring Cloud Gateway 的早期版本中&#xff0c;Ribbon 被用作默认的负载均衡器。随着Spring Cloud的…

jsonpath相关---JSONPath - 用于 JSON 的 XPath

一.简介 XML 的一个经常强调的优点是提供了大量工具来分析、转换和有选择地从 XML 文档中提取数据。XPath 就是这些强大的工具之一。 现在是时候想知道&#xff0c;是否需要像 XPath4JSON 这样的东西&#xff0c;以及它可以解决哪些问题。 无需特殊脚本&#xff0c;即可以交…

眼未来,萨科微半导体将持续发挥自身在技术研发和产品创新方面的优势

金航标kinghelm萨科微slkor宋仕强说&#xff0c;着眼未来,萨科微半导体将持续发挥自身在技术研发和产品创新方面的优势,以优质高效的半导体解决方案满足全球各地市场的需求。目前,萨科微的产品线已经囊括了二极管、三极管、功率器件、电源管理芯片等多个系列,并在霍尔传感器、A…

【MySQL】学习并使用聚合函数和DQL进行分组查询

&#x1f308;个人主页: Aileen_0v0 &#x1f525;热门专栏: 华为鸿蒙系统学习|计算机网络|数据结构与算法 ​&#x1f4ab;个人格言:“没有罗马,那就自己创造罗马~” #mermaid-svg-t8K8tl6eNwqdFmcD {font-family:"trebuchet ms",verdana,arial,sans-serif;font-siz…

【开源】SpringBoot框架开发天然气工程运维系统

目录 一、摘要1.1 项目介绍1.2 项目录屏 二、功能模块2.1 系统角色分类2.2 核心功能2.2.1 流程 12.2.2 流程 22.3 各角色功能2.3.1 系统管理员功能2.3.2 用户服务部功能2.3.3 分公司&#xff08;施工单位&#xff09;功能2.3.3.1 技术员角色功能2.3.3.2 材料员角色功能 2.3.4 安…

Python算法题集_轮转数组

本文为Python算法题集之一的代码示例 题目189 题目&#xff1a;轮转数组 说明&#xff1a;给定一个整数数组 nums&#xff0c;将数组中的元素向右轮转 k 个位置&#xff0c;其中 k 是非负数。 示例 1: 输入: nums [1,2,3,4,5,6,7], k 3 输出: [5,6,7,1,2,3,4] 解释: 向右…

Hadoop3.x基础(2)- HDFS

来源&#xff1a;B站尚硅谷 目录 HDFS概述HDFS产出背景及定义HDFS优缺点HDFS组成架构HDFS文件块大小&#xff08;面试重点&#xff09; HDFS的Shell操作&#xff08;开发重点&#xff09;基本语法命令大全常用命令实操准备工作上传下载HDFS直接操作 HDFS的API操作HDFS的API案例…

微信小程序(二十八)网络请求数据进行列表渲染

注释很详细&#xff0c;直接上代码 上一篇 新增内容&#xff1a; 1.GET请求的规范 2.数据赋值的方法 源码&#xff1a; index.wxml <!-- 列表渲染基础写法&#xff0c;不明白的看上一篇 --> <view class"students"><view class"item">&…

yolov8训练自己的关键点检测模型

参考&#xff1a; https://blog.csdn.net/weixin_38807927/article/details/135036450 标注数据集 安装labelme pip install labelme -i https://pypi.tuna.tsinghua.edu.cn/simple如果报错 $ labelme 2024-01-31 03:16:20,636 [INFO ] __init__:get_config:67- Loading …

MIMIC-IV数据库, 如何提取哪些肺栓塞病人进行了溶栓手术治疗?

溶栓手术是通过药物或者手术的方式&#xff0c;使闭塞的血管再通的一种手术。 溶栓手术主要是通过药物或者手术的方式&#xff0c;使闭塞的血管再通的一种手术。常用的药物有尿激酶、链激酶等&#xff0c;这些药物可以激活纤溶酶原&#xff0c;使纤溶酶原转化为纤溶酶&#xff…

Shell的字符处理和expect

一、Here Document免交互 1.1Here Document概述 使用I/O重定向的方式将命令列表提供给交互式程序&#xff0c;标准输入的一种替代品 格式: 命令 <<标记 输入内容 标记 1.2Here Document使用注意事项 标记可以使用任意合法字符结尾的标记一定要顶格写&#xff0c;前面…

DEV-C++ ege.h库 绘图教程(九)

一、Getting Start 前情回顾&#xff1a; DEV-C ege.h库 绘图教程 今天我们将来讲一讲一些关于杂项的函数。 二、控制台函数 1.initconsole 初始化并显示控制台窗口。 &#xff08;但因为Dev C默认就是显示窗口的&#xff0c;所以这个函数一点也没用&#xff09; 但如果想…

基于C++的面向对象程序设计:类与对象的深入剖析

面向对象程序设计的基本特点 面向对象程序设计的基本特点包括&#xff1a;抽象、封装、继承、多态。 抽象 抽象是指对具体问题或对象进行概括&#xff0c;抽出其公共性质并加以描述的过程。一般情况抽象分为数据抽象和行为抽象&#xff0c;其中数据抽象是指一个对象区别于另…

二叉树顺序结构堆实现

目录 Test.c测试代码 test1 test2 test3 &#x1f387;Test.c总代码 Heap.h头文件&函数声明 头文件 函数声明 &#x1f387;Heap.h总代码 Heap.c函数实现 ☁HeapInit初始化 ☁HeapDestroy销毁 ☁HeapPush插入数据 【1】插入数据 【2】向上调整Adjustup❗ …

关于谷歌新版调试用具(Chrome Dev Tool ),网络选项(chrome-network)默认开启下拉模式的设置

今天在使用谷歌浏览器进行调试的时候&#xff0c;打开调试工具网络选项发现过滤不同模式的选项卡不见了&#xff0c;转而变成一个下拉式选项&#xff0c;如下图 这样一来使得切换不同类型查看的时候变得非常不方便&#xff0c;然后网上查了一下发现这个功能谷歌在很早版本就已…

Mysql 主从库的重新配置

1.从库和主库的数据差异实在太大&#xff0c;反复处理数据耗时耗力&#xff0c;不如重做。 2.备份主数据库(命令备份的) usr/local/mysql/bin/mysqldump -h 100.1.4.42 -P 5566 -u root -p 备份数据库 > /mysql/db/备份的名称.sql 3.停止从库复制 登录到MySQL从库&#x…

腾讯云邀请你参与【腾讯2024技术答人挑战赛】 赢取丰厚的礼品

腾讯云邀请你参与【腾讯2024技术答人挑战赛】 赢取丰厚的礼品 2024年 腾讯礼品大派送 保持技术好奇心是程序员构建护城河的重要一环&#xff0c;快来测测你现在的技术知识面在中国程序员中排第几&#xff1f; 参与答题更有iPad、Pico VR游戏机、Switch等、腾讯云官方认证证书好…

Prometheus+grafana配置监控系统

使用docker compose安装 方便拓展, 配置信息都放在在 /docker/prometheus 目录下 1.目录结构如下 . ├── conf │ └── prometheus.yml ├── grafana_data ├── prometheus_data └── prometheus_grafana.yaml2.创建目录文件 mkdir /docker/prometheus &&am…