MYSQL 调优和使用必读

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
日志服务 SLS,月写入数据量 50GB 1个月
简介:

MYSQL 应该是最流行了 WEB 后端数据库。WEB 开发语言最近发展很快,PHP, Ruby, Python, Java 各有特点,虽然 NOSQL 最近越來越多的被提到,但是相信大部分架构师还是会选择 MYSQL 来做数据存储。

MYSQL 如此方便和稳定,以至于我们在开发 WEB 程序的时候很少想到它。即使想到优化也是程序级别的,比如,不要写过于消耗资源的 SQL 语句。但是除此之外,在整个系统上仍然有很多可以优化的地方。

1. 选择合适的存储引擎: InnoDB

除非你的数据表使用来做只读或者全文检索 (相信现在提到全文检索,没人会用 MYSQL 了),你应该默认选择 InnoDB 。

你自己在测试的时候可能会发现 MyISAM 比 InnoDB 速度快,这是因为: MyISAM 只缓存索引,而 InnoDB 缓存数据和索引,MyISAM 不支持事务。但是 如果你使用 innodb_flush_log_at_trx_commit = 2 可以获得接近的读取性能 (相差百倍) 。

1.1 如何将现有的 MyISAM 数据库转换为 InnoDB:

 
 
  1. mysql -u [USER_NAME] -p -e "SHOW TABLES IN [DATABASE_NAME];" | tail -n +2 | xargs -I '{}' echo "ALTER TABLE {} ENGINE=InnoDB;" > alter_table.sql
  2. perl -p -i -e 's/(search_[a-z_]+ ENGINE=)InnoDB/\1MyISAM/g' alter_table.sql
  3. mysql -u [USER_NAME] -p [DATABASE_NAME] < alter_table.sql
AI 代码解读

1.2 为每个表分别创建 InnoDB FILE:

 
 
  1. innodb_file_per_table=1
AI 代码解读

这样可以保证 ibdata1 文件不会过大,失去控制。尤其是在执行 mysqlcheck -o –all-databases 的时候。

2. 保证从内存中读取数据,讲数据保存在内存中

2.1 足够大的 innodb_buffer_pool_size

推荐将数据完全保存在 innodb_buffer_pool_size ,即按存储量规划 innodb_buffer_pool_size 的容量。这样你可以完全从内存中读取数据,最大限度减少磁盘操作。

2.1.1 如何确定 innodb_buffer_pool_size 足够大,数据是从内存读取而不是硬盘?

方法 1

 
 
  1. mysql> SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool_pages_%';
  2. +----------------------------------+--------+
  3. | Variable_name | Value |
  4. +----------------------------------+--------+
  5. | Innodb_buffer_pool_pages_data | 129037 |
  6. | Innodb_buffer_pool_pages_dirty | 362 |
  7. | Innodb_buffer_pool_pages_flushed | 9998 |
  8. | Innodb_buffer_pool_pages_free | 0 | !!!!!!!!
  9. | Innodb_buffer_pool_pages_misc | 2035 |
  10. | Innodb_buffer_pool_pages_total | 131072 |
  11. +----------------------------------+--------+
  12. 6 rows in set (0.00 sec)
AI 代码解读

发现 Innodb_buffer_pool_pages_free 为 0,则说明 buffer pool 已经被用光,需要增大 innodb_buffer_pool_size

InnoDB 的其他几个参数:

 
 
  1. innodb_additional_mem_pool_size = 1/200 of buffer_pool
  2. innodb_max_dirty_pages_pct 80%
AI 代码解读

方法 2

或者用iostat -d -x -k 1 命令,查看硬盘的操作。

2.1.2 服务器上是否有足够内存用来规划

执行 echo 1 > /proc/sys/vm/drop_caches 清除操作系统的文件缓存,可以看到真正的内存使用量。

2.2 数据预热

默认情况,只有某条数据被读取一次,才会缓存在 innodb_buffer_pool。所以,数据库刚刚启动,需要进行数据预热,将磁盘上的所有数据缓存到内存中。数据预热可以提高读取速度。

对于 InnoDB 数据库,可以用以下方法,进行数据预热:

1. 将以下脚本保存为 MakeSelectQueriesToLoad.sql

 
 
  1. SELECT DISTINCT
  2. CONCAT('SELECT ',ndxcollist,' FROM ',db,'.',tb,
  3. ' ORDER BY ',ndxcollist,';') SelectQueryToLoadCache
  4. FROM
  5. (
  6. SELECT
  7. engine,table_schema db,table_name tb,
  8. index_name,GROUP_CONCAT(column_name ORDER BY seq_in_index) ndxcollist
  9. FROM
  10. (
  11. SELECT
  12. B.engine,A.table_schema,A.table_name,
  13. A.index_name,A.column_name,A.seq_in_index
  14. FROM
  15. information_schema.statistics A INNER JOIN
  16. (
  17. SELECT engine,table_schema,table_name
  18. FROM information_schema.tables WHERE
  19. engine='InnoDB'
  20. ) B USING (table_schema,table_name)
  21. WHERE B.table_schema NOT IN ('information_schema','mysql')
  22. ORDER BY table_schema,table_name,index_name,seq_in_index
  23. ) A
  24. GROUP BY table_schema,table_name,index_name
  25. ) AA
  26. ORDER BY db,tb
  27. ;
AI 代码解读

2. 执行

 
 
  1. mysql -uroot -AN < /root/MakeSelectQueriesToLoad.sql > /root/SelectQueriesToLoad.sql
AI 代码解读

3. 每次重启数据库,或者整库备份前需要预热的时候执行:

 
 
  1. mysql -uroot < /root/SelectQueriesToLoad.sql > /dev/null 2>&1
AI 代码解读

2.3 不要让数据存到 SWAP 中

如果是专用 MYSQL 服务器,可以禁用 SWAP,如果是共享服务器,确定 innodb_buffer_pool_size 足够大。或者使用固定的内存空间做缓存,使用 memlock 指令。

3. 定期优化重建数据库

mysqlcheck -o –all-databases 会让 ibdata1 不断增大,真正的优化只有重建数据表结构:

 
 
  1. CREATE TABLE mydb.mytablenew LIKE mydb.mytable;
  2. INSERT INTO mydb.mytablenew SELECT * FROM mydb.mytable;
  3. ALTER TABLE mydb.mytable RENAME mydb.mytablezap;
  4. ALTER TABLE mydb.mytablenew RENAME mydb.mytable;
  5. DROP TABLE mydb.mytablezap;
AI 代码解读

4. 减少磁盘写入操作

4.1 使用足够大的写入缓存 innodb_log_file_size

但是需要注意如果用 1G 的 innodb_log_file_size ,假如服务器当机,需要 10 分钟来恢复。

推荐 innodb_log_file_size 设置为 0.25 * innodb_buffer_pool_size

4.2 innodb_flush_log_at_trx_commit

这个选项和写磁盘操作密切相关:

innodb_flush_log_at_trx_commit = 1 则每次修改写入磁盘
innodb_flush_log_at_trx_commit = 0/2 每秒写入磁盘

如果你的应用不涉及很高的安全性 (金融系统),或者基础架构足够安全,或者 事务都很小,都可以用 0 或者 2 来降低磁盘操作。

4.3 避免双写入缓冲

 
 
  1. innodb_flush_method=O_DIRECT
AI 代码解读

5. 提高磁盘读写速度

RAID0 尤其是在使用 EC2 这种虚拟磁盘 (EBS) 的时候,使用软 RAID0 非常重要。

6. 充分使用索引

6.1 查看现有表结构和索引

 
 
  1. SHOW CREATE TABLE db1.tb1\G
AI 代码解读

6.2 添加必要的索引

索引是提高查询速度的唯一方法,比如搜索引擎用的倒排索引是一样的原理。

索引的添加需要根据查询来确定,比如通过慢查询日志或者查询日志,或者通过 EXPLAIN 命令分析查询。

 
 
  1. ADD UNIQUE INDEX
  2. ADD INDEX
AI 代码解读
6.2.1 比如,优化用户验证表:

添加索引

 
 
  1. ALTER TABLE users ADD UNIQUE INDEX username_ndx (username);
  2. ALTER TABLE users ADD UNIQUE INDEX username_password_ndx (username,password);
AI 代码解读

每次重启服务器进行数据预热

 
 
  1. echoselect username,password from users;” > /var/lib/mysql/upcache.sql
AI 代码解读

添加启动脚本到 my.cnf

 
 
  1. [mysqld]
  2. init-file=/var/lib/mysql/upcache.sql
AI 代码解读
6.2.2 使用自动加索引的框架或者自动拆分表结构的框架

比如,Rails 这样的框架,会自动添加索引,Drupal 这样的框架会自动拆分表结构。会在你开发的初期指明正确的方向。所以,经验不太丰富的人一开始就追求从 0 开始构建,实际是不好的做法。

7. 分析查询日志和慢查询日志

记录所有查询,这在用 ORM 系统或者生成查询语句的系统很有用。

 
 
  1. log=/var/log/mysql.log
AI 代码解读

注意不要在生产环境用,否则会占满你的磁盘空间。

记录执行时间超过 1 秒的查询:

 
 
  1. long_query_time=1
  2. log-slow-queries=/var/log/mysql/log-slow-queries.log
AI 代码解读

8. 激进的方法,使用内存磁盘

现在基础设施的可靠性已经非常高了,比如 EC2 几乎不用担心服务器硬件当机。而且内存实在是便宜,很容易买到几十G内存的服务器,可以用内存磁盘,定期备份到磁盘。

将 MYSQL 目录迁移到 4G 的内存磁盘

 
 
  1. mkdir -p /mnt/ramdisk
  2. sudo mount -t tmpfs -o size=4000M tmpfs /mnt/ramdisk/
  3. mv /var/lib/mysql /mnt/ramdisk/mysql
  4. ln -s /tmp/ramdisk/mysql /var/lib/mysql
  5. chown mysql:mysql mysql
AI 代码解读

9. 用 NOSQL 的方式使用 MYSQL

B-TREE 仍然是最高效的索引之一,所有 MYSQL 仍然不会过时。

用 HandlerSocket 跳过 MYSQL 的 SQL 解析层,MYSQL 就真正变成了 NOSQL。

10. 其他

  • 单条查询最后增加 LIMIT 1,停止全表扫描。
  • 将非”索引”数据分离,比如将大篇文章分离存储,不影响其他自动查询。
  • 不用 MYSQL 内置的函数,因为内置函数不会建立查询缓存。
  • PHP 的建立连接速度非常快,所有可以不用连接池,否则可能会造成超过连接数。当然不用连接池 PHP 程序也可能将
  • 连接数占满比如用了 @ignore_user_abort(TRUE);
  • 使用 IP 而不是域名做数据库路径,避免 DNS 解析问题

11. 结束

你会发现优化后,数据库的性能提高几倍到几百倍。所以 MYSQL 基本还是可以适用大部分场景的应用的。优化现有系统的成本比系统重构或者迁移到 NOSQL 低很多。

原文发布时间为:2015-07-27



本文来自云栖社区合作伙伴“Linux中国

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
打赏
0
0
0
0
209
分享
相关文章
MySQL性能调优:监控和优化
MySQL性能调优:监控和优化
183 1
MySQL索引策略与查询性能调优实战
在实际应用中,需要根据具体的业务需求和查询模式,综合运用索引策略和查询性能调优方法,不断地测试和优化,以提高MySQL数据库的查询性能。
405 66
MySQL复制以及调优
本文介绍了MySQL自带复制方案的实现及其注意事项。复制方案能提供数据备份、负载均衡与分布式数据管理的优势。文章详细描述了复制步骤:主库(master)记录更改到二进制日志,发送同步消息给从库(slave),从库接收后将日志复制到本地并执行。实现复制包括配置主库的server-id和二进制日志、创建复制账号、初始化主库数据、设置从库参数及开启复制。此外,还探讨了三种日志格式(row、statement、mixed)的特点及选择建议,并分析了主从复制延迟的优化方法,如控制事务大小、优化日志传输和多线程还原日志等。最后,文中列出了搭建过程中需要注意的关键点。
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第27天】本文深入探讨了MySQL的索引策略和查询性能调优技巧。通过介绍B-Tree索引、哈希索引和全文索引等不同类型,以及如何创建和维护索引,结合实战案例分析查询执行计划,帮助读者掌握提升查询性能的方法。定期优化索引和调整查询语句是提高数据库性能的关键。
772 1
【MySQL调优】如何进行MySQL调优?从参数、数据建模、索引、SQL语句等方向,三万字详细解读MySQL的性能优化方案(2024版)
MySQL调优主要分为三个步骤:监控报警、排查慢SQL、MySQL调优。 排查慢SQL:开启慢查询日志 、找出最慢的几条SQL、分析查询计划 。 MySQL调优: 基础优化:缓存优化、硬件优化、参数优化、定期清理垃圾、使用合适的存储引擎、读写分离、分库分表; 表设计优化:数据类型优化、冷热数据分表等。 索引优化:考虑索引失效的11个场景、遵循索引设计原则、连接查询优化、排序优化、深分页查询优化、覆盖索引、索引下推、用普通索引等。 SQL优化。
929 15
【MySQL调优】如何进行MySQL调优?从参数、数据建模、索引、SQL语句等方向,三万字详细解读MySQL的性能优化方案(2024版)
【MySQL】专栏合集,从基础概念到调优
【MySQL】专栏合集,从基础概念到调优
74 0
【MySQL调优】如何进行MySQL调优?一篇文章就够了!
MySQL调优主要分为三个步骤:监控报警、排查慢SQL、MySQL调优。 排查慢SQL:开启慢查询日志 、找出最慢的几条SQL、分析查询计划 。 MySQL调优: 基础优化:缓存优化、硬件优化、参数优化、定期清理垃圾、使用合适的存储引擎、读写分离、分库分表; 表设计优化:数据类型优化、冷热数据分表等。 索引优化:考虑索引失效的11个场景、遵循索引设计原则、连接查询优化、排序优化、深分页查询优化、覆盖索引、索引下推、用普通索引等。 SQL优化。
【MySQL调优】如何进行MySQL调优?一篇文章就够了!
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第26天】数据库作为现代应用系统的核心组件,其性能优化至关重要。本文主要探讨MySQL的索引策略与查询性能调优。通过合理创建索引(如B-Tree、复合索引)和优化查询语句(如使用EXPLAIN、优化分页查询),可以显著提升数据库的响应速度和稳定性。实践中还需定期审查慢查询日志,持续优化性能。
567 0
AI助理

你好,我是AI助理

可以解答问题、推荐解决方案等