当前位置： 首页 > article >正文

mysql性能提升方法大汇总

article 2025/9/29 15:27:17

前言

最近在开发自己的小程序的时候，由于业务功能对系统性能的要求很高，系统性能损耗又主要在mysql上，而业务功能的数据表很多，单表数据量也很大，又涉及到很多场景的数据查询，所以我针对mysql调用做了优化，成功地把原本一次复杂请求的时间从3到5秒加速到0.5秒以内，顺便总结了一些mysql性能优化的方法

一 mysql配置调整

1.1 内存相关

1.1.1 innodb_buffer_pool_size

mysql数据页索引页缓冲区的大小： mysql中命中率高的数据页会长期驻留，读取数据时如果在buffer pool中，就无需访问磁盘，InnoDB的数据页和索引页都会缓存在这里，

对于专用数据库服务器，可以设为物理内存的60%~80%，但是要注意，如果设置得太高，会导致操作系统内存不足而swap，系统整体性能下降，而且内存竞争影响其他服务，可以通过以下语句监控buffer pool得命中率

SHOW ENGINE INNODB STATUS

如果status中的buffer pool hit rate命中率小于99%就说明太小了

1.1.2 innodb_log_buffer_size

redo log缓冲区的大小，事务执行过程中，修改操作写入内存中的log buffer，提交时flush到redo log 文件，redo log缓冲区用于存放事务提交前的redo日志，在事务提交时刷写到磁盘。

一般设置为 8MB~64MB（默认 16MB），如果事务频繁、单个事务很大，可以设置更大一点，减少磁盘写入次数，如果innodb_log_buffer_size设置得太小，会导致大事务频繁触发flush，性能下降，可以监控Innodb_log_waits，值大于 0 表示内存不够，

SHOW GLOBAL STATUS LIKE 'Innodb_log_waits';

 

1.2 连接和并发
 

1.2.1 max_connections
 

服务器连接得最大并发连接数，超过该值的新连接会被拒绝。每个连接占用一定资源（内存+线程）
 

此参数是系统连接保护阈值。Web应用一般设置在 200~1000，高并发系统可适当调高，搭配连接池使用，设置太小时，高峰期连接被拒，出现 “Too many connections” 错误，太大时每个连接占用资源，连接过多会耗尽内存，崩溃风险增加，可以查看 Max_used_connections 是否接近 max_connections的值
 

 
SHOW GLOBAL STATUS LIKE 'Max_used_connections';
 

 

 

1.2.2 thread_cache_size
 

MySQL会重用已结束的线程，减少频繁创建线程的开销。线程结束后保留在线程缓存池中，下一个连接复用已有线程。
 

数据库服务一般设置为16~100，根据连接频繁程度和CPU核心数决定，设置太小时会导致Threads_created 值很高，频繁创建线程，影响性能，设置太大时占用内存资源而且提升不明显
 

 
SHOW GLOBAL STATUS LIKE 'threads_created';
 

 

 

 

1.2.3 table_open_cache
 

打开的表文件的缓存数量，避免频繁打开/关闭表文件，MySQL 每次访问表都会打开表文件，保存在 cache 中，复用效率更高。注意它缓存的是表文件的句柄和元数据结构，而不是表的数据变身
 

一般小型系统设置为512~2048，大表多或高并发可设为 8192，太小时会导致Opened_tables 值高，频繁打开表影响性能，太多则会占用过多内存，可以通过下面语句获取系统一共打开过多少次表文件
 

 
SHOW GLOBAL STATUS LIKE 'opened_tables';
 

 

你需要间隔一段时间（例如 1 分钟、5 分钟）对比两次结果，观察 opened_tables 的增长量。如果 5分钟内增长了几百甚至上千个，那就是异常的。正常情况下opened_tables 每分钟增长小于10 ，如果每分钟增长50到100之间，说明 table_open_cache 太小或有短连接频繁打开关闭表
 

也可以使用下面语句拿到获取表时命中缓存和没命中缓存的case数量，自行计算
 

 
SHOW GLOBAL STATUS LIKE 'Table_open_cache_hits';
 SHOW GLOBAL STATUS LIKE 'Table_open_cache_misses';
  
 

 


 命中率 = Table_open_cache_hits / (Table_open_cache_hits + Table_open_cache_misses)，通常命中率会大于95%，如果命中率小于90%，就应该考虑增加 table_open_cache
 

 

1.3 日志和事务
 

1.3.1 innodb_flush_log_at_trx_commit
 

这个配置控制事务redo日志何时写入磁盘
 

  •  
    1：每次提交事务都同步写磁盘（最安全）
     
  •  
    2：写OS缓存，根据系统自己的策略定时刷盘（折中）
     
  •  
    0：仅写内存，崩溃时候redo日志全部丢失
     
 

高可靠性场景：设为1，对性能要求高并且允许少量数据丢失时设为2，数据丢失几乎不造成损失时可以考虑设置为0
 

 

1.3.2 sync_binlog
 

控制bin log刷盘频率，影响主从一致性。
 

  •  
    1：每次事务都刷盘，最安全
     
  •  
    0：交给操作系统定期刷盘，性能较高，但可能丢 binlog
     
  •  
    N：每 N 次事务刷一次
     
 

高可靠性场景下设为1，性能优先：设为 100 或更高
 


  
 

1.4 临时文件与排序
 

1.4.1 tmp_table_size和max_heap_table_size              
 

这两个配置控制临时表的最大内存使用，超出后写磁盘，前者控制MySQL 创建内部临时表（用于 GROUP BY、ORDER BY、DISTINCT 等操作）时，可使用的最大内存空间，后者控制用户或系统创建的MEMORY引擎表的最大大小，当 MySQL创建一个内存临时表时，会以 tmp_table_size 和 max_heap_table_size 中的较小值为准，作为临时表在内存中的最大可用空间
 

默认16MB太小，建议提升到 64MB~256MB,太小会导致临时表频繁写磁盘，性能下降
 

 
SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';
 

 

 

1.4.2 sort_buffer_size
 

每个线程排序操作使用的缓冲区大小。ORDER BY、GROUP BY 会使用此 buffer，如果不足会写磁盘
 

这个是单线程的变量，建议设置为2MB~8MB，太小时排序频繁落盘，影响性能，不过高并发场景不要设太大，因为每个连接消耗内存大，有服务器内存撑爆风险
 

 
SHOW GLOBAL STATUS LIKE 'Sort_merge_passes'; 
 

 

 

 

二 索引设计和sql优化
 

2.1 索引设计
 

2.1.1 合理的索引
 

覆盖索引：如果查询使用的索引语句包含了所有查询需要的字段，mysql就不需要回表查询表数据，大大提升性能
 

 

前缀索引：当字段是长字符串（如 VARCHAR 或 TEXT），为了节省索引空间和提升性能，可以只索引字段的前几位（前缀）。这样可以减少索引大小，但仍保持较好的查询效率。比如               
 

 
CREATE INDEX idx_email_prefix ON users(email(10));
 

 

表示只索引 email 字段的前 10 个字符。
 

 

组合索引：当查询条件涉及多个列时，可以将多个列联合建立一个索引。MySQL会将多个字段组合在一起作为一个整体进行索引，大大提升多字段查询性能。比如下面索引
 

 
CREATE INDEX idx_user_status ON users(user_id, status);
 

 

对于同时又user_id和status的查询就能够快速定位到。
 

 

2.1.1 避免不合理的索引设计
 

索引过多：索引不仅会占用磁盘空间，还是增加写操作的开销，建议不超过每表 5-6 个。不超过总字段的三分之一
 

避免长文本索引：普通索引会存储值的所有内容，对于很长的字段会占用很大的空间，对于前面部分的区分度就比较高的长字符可以使用前缀索引
 

 

2.2 SQL 语句优化
 

  •  
    避免 SELECT *，只查需要的列
     
  •  
    保证查询能使用到索引，对于组合索引和字符模糊匹配要注意最左匹配原则，尽可能利用覆盖索引
     
  •  
    使用 EXPLAIN 分析执行计划，关注 type、rows、Extra 字段
     
  •  
    大表避免 OFFSET 深分页，推荐“游标式分页”（如 WHERE id > ?）
     
  •  
    避免 IN 过多项（>1000），或考虑改用临时表
     
  •  
    联表限制：尽量不超过 3 张表JOIN，JOIN 字段必须加索引
     
  •  
    尽量避免使用not in，or和union（尽可能用union all代替union）
     
  •  
    避免索引列参与函数计算：WHERE DATE(create_time) = '2023-01-01' 会导致索引失效
     
 

 

 

三 应用系统
 

 

3.1 减少不必要的请求
 

  •  
    缓存策略
     
    •  
      使用 Redis/Memcached 缓存热点数据。
       
    •  
      对于不变数据（如省市、用户等级），做本地缓存或CDN缓存。
       
     
  •  
    连接池
     
    •  
      应用侧应使用数据库连接池，如 Druid、HikariCP，控制最大并发连接。
       
     
  •  
    接口聚合
     
    •  
      尽量减少多次小查询，改为批量查询或数据合并。
       
     
 

3.2 数据库访问控制
 

  •  
    限流与降级策略：高峰期临时关闭非核心查询。
     
  •  
    使用读写分离：读请求走从库，写请求走主库。
     
  •  
    使用中间层封装数据库访问（如 DAO 层），方面中间层做统一的优化管理，避免业务层直接操作 SQL。
     
 

3.3 持续监控与预警
 

  •  
    使用工具如：
     
    •  
      慢查询日志 + pt-query-digest 分析慢 SQL。
       
    •  
      Prometheus + Grafana 或 Percona Toolkit 做实时性能监控。
       
    •  
      MySQL Enterprise Monitor 商业监控套件。
       
     
 

 

3.4 表设计
 

主键：优选整数型、自增主键（如 BIGINT AUTO_INCREMENT），避免使用 UUID 作为主键，因为UUID随机性太大，插入时会频繁触发页分裂
 

字段类型：明确字段类型，选择最合适的数据类型，字段长度固定的场景下使用定长字段而不是变长字段，使用最小足够类型，避免使用 TEXT/BLOB 除非确实要存大文本；这类字段性能差且不易索引
 

NULL值：非必要字段不要设置为 NULL，使用默认值（如 0、''）可减少 NULL 判断。
 

冗余字段：对于查询多写入少的情况可以适当添加一些冗余字段避免join操作影响性能
 

 

 

 

 

 

 

 

 

 















相关文章：
















mysql性能提升方法大汇总




前言 
最近在开发自己的小程序的时候，由于业务功能对系统性能的要求很高，系统性能损耗又主要在mysql上，而业务功能的数据表很多，单表数据量也很大，又涉及到很多场景的数据查询，所以我针对mysql调用做了优化…...






编程日记
2025/6/5 18:14:31



















C++标准流详解：cin/cout的绑定机制与cerr/clog的缓冲差异




在C中，标准错误流（cerr）、标准日志流（clog）与标准输入输出流（cin/cout）的行为差异主要体现在缓冲机制和绑定关系上。以下是详细解释，并结合cin和cout的关联性进行对比分析&#xff1…...






编程日记
2025/6/13 7:27:01



















BlockMesh Ai项目 监控节点部署教程




项目介绍 
BlockMesh 是一个创新、开放且安全的网络，允许用户轻松地将多余的带宽货币化。 它为用户提供了被动获利并参与人工智能数据层、在线隐私、开源和区块链行业前沿的绝佳机会。 
此教程为Linux系统教程 
教程开始 
首先到这里注册账号，注册后保存…...






编程日记
2025/9/29 9:13:39



















【Bluedroid】蓝牙 HID DEVICE 初始化流程源码解析




本文深入剖析Android蓝牙协议栈中HID设备（BT-HD）服务的初始化与启用流程，从接口初始化、服务掩码管理、服务请求路由到属性回调通知，完整展现蓝牙HID服务激活的技术路径。通过代码逻辑梳理，揭示服务启用的核心机制&…...






编程日记
2025/9/29 9:13:41



















iOS创建Certificate证书、制作p12证书流程




一、创建Certificates 
1、第一步得先在苹果电脑上创建一个.certSigningRequest的文件。首先打开钥匙串，使用快捷键【command空格】——输入【钥匙串】回车（找不到就搜一下钥匙串访问使用手册）  2、然后在苹果电脑的左上角菜单栏选择【钥匙串…...






编程日记
2025/9/29 9:13:40



















curl发送数据不为null，但是后端接收到为null




curl -X POST http://localhost:8080/xiaozhi/test --header "Content-Type: application/json" -d "{\"age\":123}"经过检查发现注解导入错误  正确的应该是 
import org.springframework.web.bind.annotation.RequestBody;...






编程日记
2025/9/29 9:13:40



















blazor与硬件通信实现案例




在网页接入硬件交互通信方案这篇博客中，曾经提到了网页中接入各种硬件操作的方法，即通过Windows Service作为指令的中转，并建立websocket通信连接，进而实现接入硬件的各种操作。这篇博客就以实际的案例来讲解具体怎么实现。 
一、建立Windows Service项目 
比如我就建立了一…...






编程日记
2025/9/29 9:13:47



















Linux下mysql的安装与远程链接




linux安装mysql 
01下载依赖： 找到网址/download下：   最下面MySQL Community（mysql社区版）  选择MySQL Community Server 选择对应的mysql版本 操作系统版本选择   根据操作系统的版本选择具体版本号  下载离线版本 安装包详情 0…...






编程日记
2025/9/29 9:13:48



















esp32硬件支持AT指令




步骤1：下载AT固件 
从乐鑫官网或Git鑫GitHub仓库（https://github.com/espressif/esp-at）获取对应ESP32型号的AT固件（如ESP32-AT.bin）。 
步骤2：安装烧录工具 
使用 esptool.py（命令行工具&#…...






编程日记
2025/9/19 19:16:15



















【HT周赛】T3.二维平面 题解（分块：矩形chkmax，求矩形和）




题意 
需要维护  n  n n \times n nn 平面上的整点，每个点  ( x , y ) (x, y) (x,y) 有权值  V ( x , y ) V(x, y) V(x,y)，初始都为  0 0 0。 
同时给定  n n n 次修改操作，每次修改给出  x 1 , x 2 , y 1 , y 2 , v x_1, x_2, y_1, y_2, v x…...






编程日记
2025/9/17 22:37:42



















C++中的虚表和虚表指针的原理和示例




一、基本概念 
1. 什么是虚函数（virtual function）？ 
虚函数是用 virtual 关键字修饰的成员函数，支持运行时多态（dynamic polymorphism）。通过基类指针或引用调用派生类重写的函数。 
class Base {
public:…...






编程日记
2025/6/29 3:02:19



















qemu热迁移后内存占用突增问题




1.问题描述 
虚拟机配置了memoryBackingmemfd的情况下，热迁移虚拟机后，在目的节点 qemu-kvm 进程占用 rss 会突增很多。 
如果去掉这个配置没这个现象。 
<memoryBacking><source typememfd/>
</memoryBacking>2.问题现象 
2.1 不配置…...






编程日记
2025/9/29 9:13:55



















鸿蒙 Core File Kit（文件基础服务）之简单使用文件




查看常用的沙箱目录 应用沙箱文件访问关系图 应用文件目录结构图 查看常用的沙箱目录 
Entry
Component
struct Index {build() {Button(查看常用的沙箱目录).onClick(_>{let ctx  getContext() // UI下只能使用这个方法&#xff0c;不能 this.contextconsole.log(--应用缓存…...






编程日记
2025/9/29 9:14:36



















AI 检测原创论文：技术迷思与教育本质的悖论思考




当高校将 AI 写作检测工具作为学术诚信的 "电子判官"&#xff0c;一场由技术理性引发的教育异化正在悄然上演。GPT-4 检测工具将人类创作的论文误判为 AI 生成的概率高达 23%&#xff08;斯坦福大学 2024 年研究数据&#xff09;&#xff0c;这种 "以 AI 制 AI&…...






编程日记
2025/7/22 7:55:22



















基于Qt的app开发第七天




写在前面 笔者是大一下计科生&#xff0c;标题这个项目是笔者这个学期的课设&#xff0c;与学长共创&#xff0c;我负责客户端部分&#xff0c;现在已经实现了待办板块的新建、修改。          这个项目目前已经走上正轨了&#xff0c;博主也实现了主要功能的从无到有&#xff…...






编程日记
2025/9/29 9:14:07



















目标检测任务常用脚本1——将YOLO格式的数据集转换成VOC格式的数据集




在目标检测任务中&#xff0c;不同框架使用的标注格式各不相同。常见的框架中&#xff0c;YOLO 使用 .txt 文件进行标注&#xff0c;而 PASCAL VOC 则使用 .xml 文件。如果你需要将一个 YOLO 格式的数据集转换为 VOC 格式以便适配其他模型&#xff0c;本文提供了一个结构清晰、…...






编程日记
2025/9/29 9:14:56



















NLTK库: 数据集3-分类与标注语料（Categorized and Tagged Corpora）




NLTK库: 数据集3-分类与标注语料&#xff08;Categorized and Tagged Corpora&#xff09; 
1.二分类语料 
主要是电影语料&#xff0c;和情绪(积极消极、主观客观)有关&#xff0c;有以下2个语料&#xff1a; 
1.1 movie_reviews: IMDb 影评 
IMDb&#xff08;Internet Movie …...






编程日记
2025/9/15 12:25:03



















uni-app学习笔记五-vue3响应式基础




一.使用ref定义响应式变量 
在组合式 API 中&#xff0c;推荐使用 ref() 函数来声明响应式状态&#xff0c;ref() 接收参数&#xff0c;并将其包裹在一个带有 .value 属性的 ref 对象中返回 
示例代码&#xff1a; <template>	<view>{{ num1 }}</view><vi…...






编程日记
2025/9/21 23:22:43



















ElasticSeach快速上手笔记-入门篇




由来 
Elasticsearch 是一个基于 Apache Lucene 构建的分布式、高扩展、近实时的搜索与数据分析引擎&#xff0c;能够高效处理结构化和非结构化数据的全文检索及复杂分析 
搜索&#xff0c;即用户在平台如百度进行输入关键词&#xff0c;由后端给出搜索结果数据进行返回&#x…...






编程日记
2025/9/20 19:17:49



















eward hacking 问题 强化学习钻空子




Reward Hacking的本质是目标对齐&#xff08;Goal Alignment&#xff09;失败 “Reward hacking”&#xff08;奖励黑客&#xff09;是强化学习或AI系统中常见的问题&#xff0c;通俗地说就是&#xff1a; AI模型“钻空子”&#xff0c;用投机取巧的方式来拿高分&#xff0c;而…...






编程日记
2025/6/19 10:53:30



















uniapp开发4--实现耗时操作的加载动画效果




下面是使用 Vue 组件的方式&#xff0c;在 uni-app 中封装耗时操作的加载动画效果及全屏遮罩层的组件的示例。 
组件结构&#xff1a; 
components/loading.vue: 组件文件&#xff0c;包含 HTML 结构、样式和 JS 逻辑。 
代码&#xff1a;    
<template><view class&…...






编程日记
2025/9/18 9:32:50



















《ffplay 读线程与解码线程分析：从初始化到 seek 操作，对比视频与音频解码的差异》




1 read-thread 1.1 初始化部分 
1.分配. avformat_alloc_context 创建上下⽂ 
ic  avformat_alloc_context();if (!ic) {av_log(NULL, AV_LOG_FATAL, "Could not allocate context.\n");ret  AVERROR(ENOMEM);goto fail;}2 ic->interrupt_callback.callback  deco…...






编程日记
2025/9/18 23:28:11



















MySQL推荐书单：从入门到精通




给大家介绍一些 MySQL 从入门到精通的经典书单&#xff0c;可以基于不同学习阶段的需求进行选择。 
入门 
MySQL必知必会 这本书继承了《SQL必知必会》的优点&#xff0c;专门针对 MySQL 用户&#xff0c;没有过多阐述数据库基础理论&#xff0c;而是紧贴实战&#xff0c;直接从…...






编程日记
2025/9/27 21:13:03



















用 Rust 搭建一个优雅的多线程服务器：从零开始的详细指南




嘿&#xff0c;小伙伴们&#xff01;今天咱们来聊聊怎么用 Rust 搭建一个牛气哄哄的多线程服务器&#xff0c;还能在需要的时候优雅地关机。为啥要用 Rust 呢&#xff1f;因为 Rust 是个超级靠谱的语言&#xff0c;它能保证内存安全&#xff0c;写并发代码的时候不用担心那些让…...






编程日记
2025/9/19 19:15:40



















redis 数据结构-01（ SET、GET、DEL）




使用 Redis 字符串&#xff1a;SET、GET、DEL 
Redis 字符串是用于存储和操作文本或二进制数据的基本数据类型。它们是 Redis 中最简单但功能最丰富的数据结构&#xff0c;可作为构建更复杂结构的基石。了解如何有效地使用字符串对于充分利用 Redis 的缓存、会话管理以及其他各…...






编程日记
2025/6/7 18:31:00



















【Nacos】env NACOS_AUTH_TOKEN must be set with Base64 String.




【Nacos】env NACOS_AUTH_TOKEN must be set with Base64 String. 
问题描述 
env NACOS_AUTH_TOKEN must be set with Base64 String.原因分析 
从错误日志中可以看出&#xff0c;Nacos 启动失败的原因是缺少必要的环境变量 NACOS_AUTH_TOKEN。 NACOS_AUTH_TOKEN: Nacos 用于生…...






编程日记
2025/9/27 3:43:51



















秋招准备——2.跨时钟相关




格雷码异步FIFO跨时钟域处理 
格雷码 
一、格雷码规律 
相邻性&#xff1a;相邻两个数的格雷码只有一位不同&#xff0c;例如&#xff1a; 0000 → 0001&#xff08;仅最低位变化&#xff09;0001 → 0011&#xff08;仅次低位变化&#xff09;0011 → 0010&#xff08;仅最低位…...






编程日记
2025/9/15 11:07:09



















激光打印机常见打印故障简单处理意见




一、 
问题描述&#xff1a; 
给打印机更换新的硒鼓时拉开硒鼓封条时有微量碳粉带出&#xff1b; 
原因&#xff1a; 
出厂打印测试时&#xff0c;可能会有微量碳粉在磁辊上或者磁辊仓&#xff1b; 
解决方法&#xff1a; 
擦干净即可正常使用&#xff1b; 二、 
问题描述&…...






编程日记
2025/9/17 14:12:32



















语言学中的对象语言与元语言 | 概念 / 区别 / 实例分析




注&#xff1a;英文引文&#xff0c;机翻未校。 语言学中的“对象语言”和“元语言” 
刘福长 
现代外语 1989年第3期&#xff08;总第45期&#xff09; 
在阅读语言学著作时&#xff0c;我们有时会遇到这样两个术语&#xff1a;对象语言&#xff08;object language&#xff0…...






编程日记
2025/9/17 21:01:43



















【2025最新】Windows系统装VSCode搭建C/C++开发环境（附带所有安装包）




文章目录 为什么选择VSCode作为C/C开发工具&#xff1f;一、VSCode安装过程&#xff08;超简单&#xff01;&#xff09;二、VSCode中文界面设置&#xff08;再也不用对着英文发愁&#xff01;&#xff09;三、安装C/C插件&#xff08;编程必备神器&#xff01;&#xff09;四、…...






编程日记
2025/9/15 11:32:24