搜索
首页数据库mysql教程15 个有用的 MySQL/MariaDB 性能调整和优化技巧

这篇文章将告诉你一些基本的,但非常有用的关于如何优化 MySQL/MariaDB 性能的技巧。注意,本文假定您已经安装了 MySQL 或 Maria

MySQL 是一个强大的开源关系数据库管理系统(简称 RDBMS)。它发布于 1995 年(20年前)。它采用结构化查询语言(SQL),这可能是数据库内容管理中最流行的选择。最新的 MySQL 版本是 5.6.25,于 2015 年 5 月 29 日发布。

关于 MySQL 一个有趣的事实是它的名字来自于 Michael Widenius(MySQL 的创始人)的女儿“ My”。尽管有许多关于 MySQL 有趣的传闻,不过本文主要是向你展示一些有用的实践,以帮助你管理你的 MySQL 服务器。

15 个有用的 MySQL/MariaDB 性能调整和优化技巧

MySQL 性能优化

2009 年 4 月,MySQL 被 Oracle 收购。其结果是MySQL 社区分裂,创建了一个叫 MariaDB 的分支 。创建该分支的主要原因是为了保持这个项目可以在 GPL 下的自由。

今天,MySQL 和 MariaDB 是用于类似 WordPress、Joomla、Magento 和其他 web 应用程序的最流行的 RDMS 之一(如果不是最多的)。

这篇文章将告诉你一些基本的,但非常有用的关于如何优化 MySQL/MariaDB 性能的技巧。注意,本文假定您已经安装了 MySQL 或 MariaDB。如果你仍然不知道如何在系统上安装它们,你可以按照以下说明去安装:

重要提示: 在开始之前,不要盲目的接受这些建议。每个 MySQL 设置都是不同的,在进行任何更改之前需要慎重考虑。

你需要明白这些:

  • MySQL/MariaDB 配置文件位于 /etc/my.cnf。 每次更改此文件后你需要重启 MySQL 服务,以使更改生效。
  • 这篇文章使用 MySQL 5.6 版本。 
  • 1. 启用 InnoDB 的每张表一个数据文件设置

    首先,有一个重要的解释, InnoDB 是一个存储引擎。MySQL 和 MariaDB 使用 InnoDB 作为默认存储引擎。以前,MySQL 使用系统表空间来保存数据库中的表和索引。这意味着服务器唯一的目的就是数据库处理,它们的存储盘不用于其它目的。

    InnoDB 提供了更灵活的方式,它把每个数据库的信息保存在一个 .ibd 数据文件中。每个 .idb 文件代表它自己的表空间。通过这样的方式可以更快地完成类似 “TRUNCATE” 的数据库操作,当删除或截断一个数据库表时,你也可以回收未使用的空间。

    这样配置的另一个好处是你可以将某些数据库表放在一个单独的存储设备。这可以大大提升你磁盘的 I/O 负载。

    MySQL 5.6及以上的版本默认启用 innodb_file_per_table。你可以在 /etc/my.cnf 文件中看到。该指令看起来是这样的:

  •  
  • 2. 将 MySQL 数据库数据存储到独立分区上

    注意:此设置只在 MySQL 上有效, 在 MariaDB 上无效。

    有时候操作系统的读/写会降低你 MySQL 服务器的性能,尤其是如果操作系统和数据库的数据位于同一块磁盘上。因此,我建议你使用单独的磁盘(最好是 SSD)用于 MySQL 服务。

    要完成这步,你需要将新的磁盘连接到你的计算机/服务器上。对于这篇文章,我假定磁盘挂在到 /dev/sdb。 

    下一步是准备新的分区:
  • # fdisk /dev/sdb
  • 现在按 “N” 来创建新的分区。接着按 “P”,使其创建为主分区。在此之后,从 1-4 设置分区号。之后,你可以选择分区大小。这里按 enter。在下一步,你需要配置分区的大小。

    如果你希望使用全部的磁盘,再按一次 enter。否则,你可以手动设置新分区的大小。准备就绪后按 “w” 保存更改。现在,我们需要为我们的新分区创建一个文件系统。这可以用下面命令轻松地完成:

  • # mkfs.ext4 /dev/sdb1
  • 现在我们会挂载新分区到一个目录。我在根目录下创建了一个名为 “ssd” 的目录:

  • # mkdir /ssd/
  • 挂载新分区到刚才创建的目录下:

  • # mount /dev/sdb1 /ssd/
  • 你可以在 /etc/fstab 文件中添加如下行设置为开机自动挂载:

     

    现在我们将 MySQL 移动到新磁盘中

    首先停止 MySQL 服务:

  • # service mysqld stop
  • 我建议你同时停止 Apache/nginx,以防止任何试图写入数据库的操作:

  • # service httpd stop
  • # service nginx stop
  • 现在复制整个 MySQL 目录到新分区中:

  • # cp /var/lib/mysql /ssd/ -Rp
  • 这可能需要一段时间,具体取决于你的 MySQL 数据库的大小。一旦这个过程完成后重命名 MySQL 目录:

  • # mv /var/lib/mysql /var/lib/mysql-backup
  • 然后创建一个符号链接:

  • # ln -s /ssd/mysql /var/lib/mysql
  • 现在启动你的 MySQL 和 web 服务:

    以后你的数据库将使用新的磁盘访问。

     

    3. 优化使用 InnoDB 的缓冲池

    InnoDB 引擎在内存中有一个缓冲池用于缓存数据和索引。这当然有助于你更快地执行 MySQL/MariaDB 查询语句。选择合适的内存大小需要一些重要的决策并对系统的内存消耗有较多的认识。

    下面是你需要考虑的:

  • 其它的进程需要消耗多少内存。这包括你的系统进程,页表,套接字缓冲。
  • 你的服务器是否专门用于 MySQL 还是你运行着其它非常消耗内存的服务。
  • 在一个专用的机器上,你可能会把 60-70% 的内存分配给 innodb_buffer_pool_size。如果你打算在一个机器上运行更多的服务,你应该重新考虑专门用于 innodb_buffer_pool_size 的内存大小。

    你需要设置 my.cnf 中的此项:

  • innodb_buffer_pool_size
  •  

    4. 在 MySQL 中避免使用 Swappiness

    “交换”是一个当系统移动部分内存到一个称为 “交换空间” 的特殊磁盘空间时的过程。通常当你的系统用完物理内存后就会出现这种情况,系统将信息写入磁盘而不是释放一些内存。正如你猜测的磁盘比你的内存要慢得多。

    该选项默认情况下是启用的:

  • # sysctl vm.swappiness
  • 运行以下命令关闭 swappiness:

  • # sysctl -w vm.swappiness=0
  •  

    5. 设置 MySQL 的最大连接数

    max_connections 指令告诉你当前你的服务器允许多少并发连接。MySQL/MariaDB 服务器允许有 SUPER 权限的用户在最大连接之外再建立一个连接。只有当执行 MySQL 请求的时候才会建立连接,执行完成后会关闭连接并被新的连接取代。

    请记住,,太多的连接会导致内存的使用量过高并且会锁住你的 MySQL 服务器。一般小网站需要 100-200 的连接数,而较大可能需要 500-800 甚至更多。这里的值很大程度上取决于你 MySQL/MariaDB 的使用情况。

    你可以动态地改变 max_connections 的值而无需重启MySQL服务器:

  • # mysql -u root -p
  •  

    6. 配置 MySQL 的线程缓存数量

    thread_cache_size 指令用来设置你服务器缓存的线程数量。当客户端断开连接时,如果当前线程数小于 thread_cache_size,它的线程将被放入缓存中。下一个请求通过使用缓存池中的线程来完成。

    要提高服务器的性能,你可以设置 thread_cache_size 的值相对高一些。你可以通过以下方法来查看线程缓存命中率:

    你可以用以下公式来计算线程池的命中率:

    如果你得到一个较低的数字,这意味着大多数 mysql 连接使用新的线程,而不是从缓存加载。在这种情况下,你需要增加 thread_cache_size。

    这里有一个好处是可以动态地改变 thread_cache_size 而无需重启 MySQL 服务。你可以通过以下方式来实现:

     

    7. 禁用 MySQL 的 DNS 反向查询

    默认情况下当新的连接出现时,MySQL/MariaDB 会进行 DNS 查询解析用户的 IP 地址/主机名。对于每个客户端连接,它的 IP 都会被解析为主机名。然后,主机名又被反解析为 IP 来验证两者是否一致。

    当 DNS 配置错误或服务器出现问题时,这很可能会导致延迟。这就是为什么要关闭 DNS 的反向查询的原因,你可以在你的配置文件中添加以下选项去设定:

    更改后你需要重启 MySQL 服务。

     

    8. 配置 MySQL 的查询缓存容量

    如果你有很多重复的查询并且数据不经常改变 – 请使用缓存查询。 人们常常不理解 query_cache_size 的实际含义而将此值设置为 GB 级,这实际上会降低服务器的性能。

    背后的原因是,在更新过程中线程需要锁定缓存。通常设置为 200-300 MB应该足够了。如果你的网站比较小的,你可以尝试给 64M 并在以后及时去增加。

    在你的 MySQL 配置文件中添加以下设置:

     

    9. 配置临时表容量和内存表最大容量

    tmp_table_size 和 max_heap_table_size 这两个变量的大小应该相同,它们可以让你避免磁盘写入。tmp_table_size 是内置内存表的最大空间。如果表的大小超出限值将会被转换为磁盘上的 MyISAM 表。

    这会影响数据库的性能。管理员通常建议在服务器上设置这两个值为每 GB 内存给 64M。

     

    10. 启用 MySQL 慢查询日志

    记录慢查询可以帮助你定位数据库中的问题并帮助你调试。这可以通过在你的 MySQL 配置文件中添加以下值来启用:

    第一个变量启用慢查询日志,第二个告诉 MySQL 实际的日志文件存储位置。使用 long_query_time 来定义完成 MySQL 查询多少用时算长。

     

    11. 检查 MySQL 的空闲连接
    声明
    本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn
    MySQL的许可与其他数据库系统相比如何?MySQL的许可与其他数据库系统相比如何?Apr 25, 2025 am 12:26 AM

    MySQL使用的是GPL许可证。1)GPL许可证允许自由使用、修改和分发MySQL,但修改后的分发需遵循GPL。2)商业许可证可避免公开修改,适合需要保密的商业应用。

    您什么时候选择InnoDB而不是Myisam,反之亦然?您什么时候选择InnoDB而不是Myisam,反之亦然?Apr 25, 2025 am 12:22 AM

    选择InnoDB而不是MyISAM的情况包括:1)需要事务支持,2)高并发环境,3)需要高数据一致性;反之,选择MyISAM的情况包括:1)主要是读操作,2)不需要事务支持。InnoDB适合需要高数据一致性和事务处理的应用,如电商平台,而MyISAM适合读密集型且无需事务的应用,如博客系统。

    在MySQL中解释外键的目的。在MySQL中解释外键的目的。Apr 25, 2025 am 12:17 AM

    在MySQL中,外键的作用是建立表与表之间的关系,确保数据的一致性和完整性。外键通过引用完整性检查和级联操作维护数据的有效性,使用时需注意性能优化和避免常见错误。

    MySQL中有哪些不同类型的索引?MySQL中有哪些不同类型的索引?Apr 25, 2025 am 12:12 AM

    MySQL中有四种主要的索引类型:B-Tree索引、哈希索引、全文索引和空间索引。1.B-Tree索引适用于范围查询、排序和分组,适合在employees表的name列上创建。2.哈希索引适用于等值查询,适合在MEMORY存储引擎的hash_table表的id列上创建。3.全文索引用于文本搜索,适合在articles表的content列上创建。4.空间索引用于地理空间查询,适合在locations表的geom列上创建。

    您如何在MySQL中创建索引?您如何在MySQL中创建索引?Apr 25, 2025 am 12:06 AM

    toCreateAnIndexinMysql,usethecReateIndexStatement.1)forasingLecolumn,使用“ createIndexIdx_lastNameEnemployees(lastName); 2)foracompositeIndex,使用“ createIndexIndexIndexIndexIndexDx_nameOmplayees(lastName,firstName,firstName);” 3)forauniqe instex,creationexexexexex,

    MySQL与Sqlite有何不同?MySQL与Sqlite有何不同?Apr 24, 2025 am 12:12 AM

    MySQL和SQLite的主要区别在于设计理念和使用场景:1.MySQL适用于大型应用和企业级解决方案,支持高性能和高并发;2.SQLite适合移动应用和桌面软件,轻量级且易于嵌入。

    MySQL中的索引是什么?它们如何提高性能?MySQL中的索引是什么?它们如何提高性能?Apr 24, 2025 am 12:09 AM

    MySQL中的索引是数据库表中一列或多列的有序结构,用于加速数据检索。1)索引通过减少扫描数据量提升查询速度。2)B-Tree索引利用平衡树结构,适合范围查询和排序。3)创建索引使用CREATEINDEX语句,如CREATEINDEXidx_customer_idONorders(customer_id)。4)复合索引可优化多列查询,如CREATEINDEXidx_customer_orderONorders(customer_id,order_date)。5)使用EXPLAIN分析查询计划,避

    说明如何使用MySQL中的交易来确保数据一致性。说明如何使用MySQL中的交易来确保数据一致性。Apr 24, 2025 am 12:09 AM

    在MySQL中使用事务可以确保数据一致性。1)通过STARTTRANSACTION开始事务,执行SQL操作后用COMMIT提交或ROLLBACK回滚。2)使用SAVEPOINT可以设置保存点,允许部分回滚。3)性能优化建议包括缩短事务时间、避免大规模查询和合理使用隔离级别。

    See all articles

    热AI工具

    Undresser.AI Undress

    Undresser.AI Undress

    人工智能驱动的应用程序,用于创建逼真的裸体照片

    AI Clothes Remover

    AI Clothes Remover

    用于从照片中去除衣服的在线人工智能工具。

    Undress AI Tool

    Undress AI Tool

    免费脱衣服图片

    Clothoff.io

    Clothoff.io

    AI脱衣机

    Video Face Swap

    Video Face Swap

    使用我们完全免费的人工智能换脸工具轻松在任何视频中换脸!

    热工具

    SublimeText3 英文版

    SublimeText3 英文版

    推荐:为Win版本,支持代码提示!

    ZendStudio 13.5.1 Mac

    ZendStudio 13.5.1 Mac

    功能强大的PHP集成开发环境

    MinGW - 适用于 Windows 的极简 GNU

    MinGW - 适用于 Windows 的极简 GNU

    这个项目正在迁移到osdn.net/projects/mingw的过程中,你可以继续在那里关注我们。MinGW:GNU编译器集合(GCC)的本地Windows移植版本,可自由分发的导入库和用于构建本地Windows应用程序的头文件;包括对MSVC运行时的扩展,以支持C99功能。MinGW的所有软件都可以在64位Windows平台上运行。

    适用于 Eclipse 的 SAP NetWeaver 服务器适配器

    适用于 Eclipse 的 SAP NetWeaver 服务器适配器

    将Eclipse与SAP NetWeaver应用服务器集成。

    Atom编辑器mac版下载

    Atom编辑器mac版下载

    最流行的的开源编辑器