MySQL常见优化手段

1. 配置优化

1.1 缓存设置

  • 查询缓存：查询缓存可以显著减少对同一查询的重复执行次数。

    SET GLOBAL query_cache_size = 268435456;  -- 设置查询缓存大小为 256MBSET GLOBAL query_cache_type = ON;         -- 启用查询缓存

    例如，执行 SELECT * FROM users WHERE id = 1; 后，再次执行相同查询将直接从缓存中读取结果。

  • 表缓存：表缓存大小决定了可以同时打开的表数量。

    SET GLOBAL table_open_cache = 2000;  -- 设置表缓存大小为 2000

  例如，如果有大量并发查询访问不同的表，增大表缓存可以减少表打开和关闭的开销。#### 1.2 内存分配
- **Innodb Buffer Pool**：用于缓存数据和索引，是 InnoDB 存储引擎的关键配置。```sqlSET GLOBAL innodb_buffer_pool_size = 1073741824;  -- 设置缓冲池大小为 1GB

例如，对于一个包含大量数据的表，增大 innodb_buffer_pool_size 可以减少磁盘 I/O，提高查询性能。

  • 临时表内存：临时表大小限制了内存中可以创建的临时表大小。
    SET GLOBAL tmp_table_size = 67108864;       -- 设置临时表大小为 64MB
SET GLOBAL max_heap_table_size = 67108864;  -- 设置最大内存表大小为 64MB
    例如，在执行复杂查询（如 GROUP BY 或 ORDER BY）时，临时表可能会存储在磁盘中，通过增大这两个参数可以减少磁盘写入次数。
1.3 日志和同步设置

  • 二进制日志：二进制日志用于记录所有的写操作。

    SET GLOBAL sync_binlog = 1;  -- 每次写入事务提交后同步二进制日志

    例如，设置 sync_binlog = 1 确保每次事务提交后立即同步日志，提供最高数据安全性，但可能会影响性能。

  • InnoDB 日志：InnoDB 日志文件大小影响恢复速度和性能。

    SET GLOBAL innodb_log_file_size = 268435456;  -- 设置 InnoDB 日志文件大小为 256MB
SET GLOBAL innodb_log_buffer_size = 8388608;  -- 设置 InnoDB 日志缓冲区大小为 8MB

    例如，较大的日志文件减少了日志文件切换的频率，从而提高了写入性能。

2. 查询优化

2.1 索引

  • 索引设计：为频繁查询的列创建索引。

    CREATE INDEX idx_users_email ON users(email);

    例如，执行 SELECT * FROM users WHERE email = 'example@example.com'; 时，索引 idx_users_email 可以显著提高查询速度。

  • 索引使用情况：使用 EXPLAIN 分析查询计划。

    EXPLAIN SELECT * FROM users WHERE email = 'example@example.com';

    例如，通过 EXPLAIN 语句可以看到查询是否使用了索引以及查询执行的具体步骤。
    在 MySQL 中，创建索引的最佳实践可以大大提高查询性能。以下是一些创建和优化索引的建议及示例：

2.1.1. 确定索引需求
  • 分析查询：通过 EXPLAIN 命令分析查询的执行计划，找出查询中使用频繁的列。
    EXPLAIN SELECT * FROM orders WHERE user_id = 123;
2.1.2. 创建基本索引

  • 单列索引：对于单列的查询条件，可以创建单列索引。

    CREATE INDEX idx_user_id ON orders(user_id);

    例如，这个索引可以加速 WHERE user_id = 123 的查询。

  • 唯一索引：当需要确保某列的唯一性时，可以创建唯一索引。

    CREATE UNIQUE INDEX idx_email ON users(email);

    例如，防止用户注册时重复的邮箱地址。

2.1.3. 组合索引

  • 复合索引：当查询条件涉及多个列时，可以创建复合索引。

    CREATE INDEX idx_user_date ON orders(user_id, order_date);

    例如，这个索引可以加速 WHERE user_id = 123 AND order_date >= '2023-01-01' 的查询。

  • 索引顺序：组合索引的顺序应与查询中使用的列的顺序一致。

    CREATE INDEX idx_user_date_status ON orders(user_id, order_date, status);

    例如，WHERE user_id = 123 AND order_date >= '2023-01-01' AND status = 'shipped' 的查询会用到这个索引。

2.1.4. 避免常见的索引问题
  • 避免过多索引：每个索引都会占用磁盘空间并影响插入、更新操作的性能。只创建必要的索引。
  • 避免在高基数列上创建索引：如果列中的唯一值很少（如布尔值），创建索引可能不会带来性能提升。
  • 避免在经常变更的列上创建索引：索引会增加数据修改的成本，频繁变更的列（如日志记录时间）上应慎用索引。
2.1.5. 使用覆盖索引
  • 覆盖索引：如果查询仅访问索引中的列，MySQL 可以直接从索引中获取数据，而不需要访问表。
    CREATE INDEX idx_user_id_name ON users(user_id, name);
    例如，对于查询 SELECT name FROM users WHERE user_id = 123，这个索引可以直接提供所需数据，无需访问表数据。
2.1.6. 使用前缀索引（对于长文本列）
  • 前缀索引：对于长文本列（如 VARCHAR），可以使用前缀索引。
    CREATE INDEX idx_name_prefix ON users(name(10));
    例如，name(10) 表示只索引 name 列的前 10 个字符，这样可以减少索引大小。
2.1.7. 定期优化索引

  • 监控索引使用情况：使用 SHOW INDEX FROM table_name 命令查看表中的索引，并评估其有效性。

    SHOW INDEX FROM orders;

  • 重建索引：当数据发生大量变化时，可以通过 OPTIMIZE TABLE 来重建索引并整理碎片。

    OPTIMIZE TABLE orders;
2.1.8. 示例优化
示例 1：单列索引优化

假设你有一个 orders 表，查询经常基于 order_date 列。

CREATE INDEX idx_order_date ON orders(order_date);
示例 2：组合索引优化

假设你查询经常涉及 user_id 和 status 列。

CREATE INDEX idx_user_status ON orders(user_id, status);
示例 3：覆盖索引优化

假设你有一个 products 表，查询经常只需要 product_id 和 price 列。

CREATE INDEX idx_product_id_price ON products(product_id, price);
2.2 查询重写

  • 避免全表扫描：使用适当的 WHERE 条件。

    SELECT * FROM orders WHERE order_date >= '2023-01-01';

    例如，限制返回数据量可以减少全表扫描，提高查询效率。

  • 减少子查询：使用 JOIN 代替子查询。

    SELECT u.name, o.order_date
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.order_date >= '2023-01-01';

    例如，上述查询使用 JOIN 代替子查询，可以提高执行效率。

  • 分页查询：优化分页查询。

    SELECT * FROM products ORDER BY id LIMIT 1000, 20;

    例如，通过索引字段分页可以提高查询速度。

3. 数据库结构优化

3.1 正规化与反正规化

  • 正规化：设计符合第三范式（3NF）的数据库结构。

    -- 正规化示例
CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(100)
);CREATE TABLE orders (id INT AUTO_INCREMENT PRIMARY KEY,user_id INT,order_date DATE,FOREIGN KEY (user_id) REFERENCES users(id)
);

  • 反正规化：适当反正规化，减少 JOIN 操作。

    -- 反正规化示例
CREATE TABLE user_orders (user_id INT,user_name VARCHAR(100),order_id INT,order_date DATE
);
3.2 分区和分表

  • 表分区：将大表按时间分区。

    CREATE TABLE orders (id INT AUTO_INCREMENT PRIMARY KEY,order_date DATE,...
) PARTITION BY RANGE (YEAR(order_date)) (PARTITION p2022 VALUES LESS THAN (2023),PARTITION p2023 VALUES LESS THAN (2024)
);

  • 水平分表：将大表拆分为多个较小的表。

    -- 假设按用户ID分表
CREATE TABLE orders_1 (id INT AUTO_INCREMENT PRIMARY KEY,user_id INT,order_date DATE,...
);CREATE TABLE orders_2 (id INT AUTO_INCREMENT PRIMARY KEY,user_id INT,order_date DATE,...
);

4. 系统和硬件优化

4.1 磁盘 I/O

  • SSD：使用 SSD 提高磁盘 I/O 性能。
    例如，将数据库文件存储在 SSD 上，可以显著提高读取和写入速度。

  • RAID：使用 RAID 10 配置提高数据读写速度。
    例如，RAID 10 结合了 RAID 1 和 RAID 0 的优点，既提供数据冗余又提高读写性能。

4.2 内存和 CPU

  • 内存升级：增加服务器内存。
    例如，更多的内存允许更大的缓冲池和缓存，提高查询性能。

  • 多核 CPU：使用多核 CPU 提高并发处理能力。
    例如，MySQL 可以利用多核 CPU 进行并行查询处理，提高整体性能。

4.3 网络
  • 网络延迟：确保低延迟和高带宽的网络连接。
    例如，优化服务器和客户端之间的网络连接，减少网络延迟，提高数据库访问速度。

5. 监控与维护

5.1 监控工具

  • performance_schema：使用 MySQL 自带的性能模式。

    SELECT * FROM performance_schema.events_statements_summary_by_digest ORDER BY COUNT_STAR DESC LIMIT 10;

  • 第三方监控工具：使用 Percona Monitoring and Management。
    例如，PMM 提供了丰富的监控功能，可以帮助发现和解决性能瓶颈。

5.2 定期维护

  • 统计信息更新：定期运行 ANALYZE TABLE 和 OPTIMIZE TABLE。

    ANALYZE TABLE users;
OPTIMIZE TABLE users;

  • 备份与恢复：定期备份数据库。

    mysqldump -u root -p database_name > backup.sql

    例如，定期进行数据库备份，确保数据安全，并进行恢复演练，验证备份的可靠性。

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

如若内容造成侵权/违法违规/事实不符，请联系长河编程网进行投诉反馈email:809451989@qq.com，一经查实，立即删除！

相关文章

GoogleCTF2023 Writeup

GoogleCTF2023 Writeup

GoogleCTF2023 Writeup Misc NPC Crypto LEAST COMMON GENOMINATOR? Web UNDER-CONSTRUCTION NPC A friend handed me this map and told me that it will lead me to the flag. It is confusing me and I don’t know how to read it, can you help me out? Attach…
阅读更多...
软件更新的双刃剑：从”微软蓝屏”事件看网络安全的挑战与对策

软件更新的双刃剑：从”微软蓝屏”事件看网络安全的挑战与对策

引言 原文链接 近日，一场由微软视窗系统软件更新引发的全球性"微软蓝屏"事件震惊了整个科技界。这次事件源于美国电脑安全技术公司"众击"提供的一个带有"缺陷"的软件更新，如同一颗隐形炸弹在全球范围内引爆，…
阅读更多...
17.5【C语言】static的补充说明

17.5【C语言】static的补充说明

static &#xff08;静态的) 作用&#xff1a;修饰局部变量&#xff0c;修饰全局变量&#xff0c;修饰函数 对比两段代码 #include <stdio.h> void test() {int a 5;a;printf("%d ", a); } int main() {int i 0;for(i0; i<5; i){test();}return 0; } …
阅读更多...
高并发内存池——链表设计

高并发内存池——链表设计

自由链表类的设计 由于申请的空间块经过对齐之后大小至少为8&#xff0c;因此可以考虑在未被使用的内存块中取前8字节存储下一个空间的地址 FreeList类初步声明 class FreeList { private:void* _freelistnullptr; //自由链表头指针size_t _size0; //自由链表的长度size_t …
阅读更多...
【Django】anaconda环境变量配置及配置python虚拟环境

【Django】anaconda环境变量配置及配置python虚拟环境

文章目录 配置环境变量配置python虚拟环境查看conda源并配置国内源在虚拟环境中安装django 配置环境变量 control sysdm.cpl,,3笔者anaconda安装目录为C:\ProgramData\anaconda3 那么需要加入path中的有如下三个 C:\ProgramData\anaconda3 C:\ProgramData\anaconda3\Scripts C:…
阅读更多...
最新风车IM即时聊天源码及完整视频教程2024年7月版

最新风车IM即时聊天源码及完整视频教程2024年7月版

堡塔面板 试验性Centos/Ubuntu/Debian安装命令 独立运行环境&#xff08;py3.7&#xff09; 可能存在少量兼容性问题 不断优化中 curl -sSO http://io.bt.sy/install/install_panel.sh && bash install_panel.sh 1.宝塔环境如下: Nginx 1.20 Tomcat 8 MySQL 8.0 R…
阅读更多...
Java 开发环境配置

Java 开发环境配置

1. 下载 JDK 直接在oracle 官网下载 https://www.oracle.com/java/technologies/downloads或者使用博主已经从oracle下载的 jdk21&#xff1a;https://download.csdn.net/download/u011171506/89585231jdk8&#xff1a;https://download.csdn.net/download/u011171506/8958523…
阅读更多...
快醒醒，别睡了!...讲《数据分析pandas库》了—/—＜4＞

快醒醒，别睡了!...讲《数据分析pandas库》了—/—＜4＞

一、废话不多说&#xff0c;直接开讲 1、DataFrame的索引和切片 1.1 选择列 当想要获取 df 中某列数据时&#xff0c;只需要在 df 后面的方括号中指明要选择的列即可。如果是 一列&#xff0c;则只需要传入一个列名;如果是同时选择多列&#xff0c;则传入多个列名即可&#xf…
阅读更多...
SAPUI5基础知识20 - 对话框和碎片(Dialogs and Fragments)

SAPUI5基础知识20 - 对话框和碎片(Dialogs and Fragments)

1. 背景 在 SAPUI5 中&#xff0c;Fragments 是一种轻量级的 UI 组件&#xff0c;类似于视图&#xff08;Views&#xff09;&#xff0c;但它们没有自己的控制器&#xff08;Controller&#xff09;。Fragments 通常用于定义可以在多个视图中重用的 UI 片段&#xff0c;从而提…
阅读更多...
集成千兆网口（Gigabit Ethernet Port）的作用主要是提供高速的有线网络连接，其工作原理涉及以下几个关键点：

集成千兆网口（Gigabit Ethernet Port）的作用主要是提供高速的有线网络连接，其工作原理涉及以下几个关键点：

传输速率&#xff1a; 千兆网口支持的最高传输速率达到1 Gbps&#xff08;即每秒10亿位&#xff09;&#xff0c;是传统百兆网口&#xff08;100 Mbps&#xff09;的十倍速度。这使得它能够处理更大量、更高质量的数据传输。 数据传输效率&#xff1a; 千兆网口能显著提高局域…
阅读更多...
C#如何引用dll动态链接库文件的注释

C#如何引用dll动态链接库文件的注释

1、dll动态库文件项目生成属性中要勾选“XML文档文件” 注意&#xff1a;XML文件的名字切勿修改。 2、添加引用时XML文件要与DLL文件在同一个目录下。 3、如果要是添加引用的时候XML不在相同目录下&#xff0c;之后又将XML文件复制到相同的目录下&#xff0c;需要删除引用&am…
阅读更多...
【机器学习】Python、NumPy和向量化的基础知识以及三者结合的用法和示例

【机器学习】Python、NumPy和向量化的基础知识以及三者结合的用法和示例

引言 在机器学习中&#xff0c;NumPy是一个非常重要的库&#xff0c;特别是在进行向量化操作时。向量化是一种优化技术&#xff0c;可以显著提高数组计算的效率&#xff0c;特别是在处理大型数据集时。NumPy提供了丰富的数组运算功能&#xff0c;使得向量化操作变得简单高效 文…
阅读更多...
驾驭代码的无形疆界:动态内存管理揭秘

驾驭代码的无形疆界:动态内存管理揭秘

目录 1.:为什么要有动态内存分配 2.malloc和free 2.1:malloc 2.2:free 3.calloc和realloc 3.1:calloc 3.1.1:代码1(malloc) 3.1.2:代码2(calloc) 3.2:realloc 3.2.1:原地扩容 3.2.2:异地扩容 3.2.3:代码1(原地扩容) 3.2.3:代码2(异地扩容) 4:常见的动态内存的错误…
阅读更多...
掀桌子了！原来是咱们的大屏设计太酷，吓着前端开发老铁了

掀桌子了！原来是咱们的大屏设计太酷，吓着前端开发老铁了

掀桌子了&#xff01;原来是咱们的大屏设计太酷&#xff0c;吓着前端开发老铁了 艾斯视觉观点认为&#xff1a;在软件开发的世界里&#xff0c;有时候创意和设计的火花会擦得特别亮&#xff0c;以至于让技术实现的伙伴们感到既兴奋又紧张。这不&#xff0c;我们的设计团队刚刚…
阅读更多...
Vue的安装配置

Vue的安装配置

1.安装node js Node.js — 在任何地方运行 JavaScript (nodejs.org) 2.测试nodejs是否安装成功 node -v npm -v3.通过npm 安装 vue npm install -g vue/cli4.测试vue是否安装成功 vue --version5.打开PyCharm&#xff0c;创建项目&#xff1a;flask-web vue create flask…
阅读更多...
【H.264】H.264详解（二）—— H264视频码流解析示例源码

【H.264】H.264详解（二）—— H264视频码流解析示例源码

文章目录 一、前言二、示例源码【1】目录结构【2】Makefile源码【3】h264parser.c源码【4】编译运行【5】源码下载地址 声明&#xff1a;此篇示例源码非原创&#xff0c;原作者雷霄骅。雷霄骅&#xff0c;中国传媒大学通信与信息系统专业博士生&#xff0c;在此向雷霄骅雷神致敬…
阅读更多...
MySQL客户端命令一节将.sql文件导入MySQL

MySQL客户端命令一节将.sql文件导入MySQL

MySql客户端命令 直接输入SQL语句 使用MySQL客户端连接到服务器之后&#xff0c;可以发送SQL语句到服务器执行&#xff0c;并且以&#xff1b;和\g, \G作为结束不同的结束方式显示内容有所不同** TIPS: ;和\g结尾以表格的形式显示结果\G以行的形式显示结果 在连接到服务器之后…
阅读更多...
FineBI连接MySQL5.7

FineBI连接MySQL5.7

一、在FineBI系统管理中&#xff0c;点击【新建数据库连接】 选择MySQL数据库 配置数据库连接&#xff0c;如下&#xff0c;其中数据库名称就是需要连接的目标数据库
阅读更多...
5.CSS学习（浮动）

5.CSS学习（浮动）

浮动&#xff08;float&#xff09; 是一种传统的网页布局方式&#xff0c;通过浮动&#xff0c;可以使元素脱离文档流的控制&#xff0c;使其横向排列。 其编写在CSS样式中。 float:none(默认值) 元素不浮动。 float:left 设置的元素在其包含…
阅读更多...
Web3 职场新手指南：从技能到素养，求职者如何脱颖而出？

Web3 职场新手指南：从技能到素养，求职者如何脱颖而出？

随着 2024 年步入下半年&#xff0c;Web3 行业正在经历一系列技术革新。通过改进的跨链交互机制和兼容性&#xff0c;逐步消除市场碎片化的问题。技术的进步为开发者和用户都打开了新的前景。然而&#xff0c;复杂的技术和快速变化的市场环境也让许多新人望而却步。求职者如何找…
阅读更多...
最新文章
推荐文章

Copyright @ 2022~2023

友情链接： 我的编程经验分享 一条长河网 尧图网站开发