MySQL · 性能优化

简介

用户首先需要清楚当前数据库的应用类型。一般而言，可分为两类：

• OLTP（Online Transaction Processing，在线事务处理）：多用在日常事务处理应用中。一般用户操作的并发量大、复杂的查询较少。OLTP 是 IO 密集型操作，需要将更多注意力放在提高 IO 的配置上。
• OLAP（Online Analytical Processing，在线分析处理）：多用在数据库仓库、集市中，一般需要执行复杂的 SQL 语句进行查询。复杂的查询如排序、连接、比较等非常消耗 CPU，需要将注意力放在提高 CPU 的配置上。

MySQL 优化思路：

• 优化成本：硬件配置 > 架构优化 > MySQL 配置参数 > 表结构与索引
• 优化效果：表结构与索引 > MySQL 配置参数 > 架构优化 > 硬件配置

硬件优化

• 硬盘，固态硬盘 SSD > 机械硬盘 HDD。
• 内存，内存的大小直接影响数据库的性能，MySQL 内存一般设置为物理内存大小的 80%。
• RAID（独立磁盘冗余数组），基本思想就是把多个相对便宜的硬盘组合起来，成为一个磁盘数组，使性能达到甚至超过一个价格昂贵、容量巨大的硬盘。RAID 0 性能最高、可靠性最低，RAID 1 可靠性最高、性能最低。对于数据库应用来说，RAID 10 是最好的选择，它同时兼顾了 RAID 1 和 RAID 0 的特性。

架构优化

• 加缓存
• 分库分表
• 读写分离

配置参数优化

• innodb_buffer_pool_size
• max_connections
• innodb_flush_log_at_trx_commit
• sync_binlog
• binlog_cache_size
• innodb_log_file_size

表结构与索引优化

• 主键

• 选择合适的数据类型，例如：datetime vs timestamp、varchar vs char

• SQL 改写，例如： select *、模糊匹配 like 等

• 紧急调优方法，查看实时会话，show processlist

• 常规调优方法，分析慢查询日志，利用工具 pt-query-digest

• 执行计划，调整索引

  • select_type：查询类型（SIMPLE - 简单查询、PRIMARY - 主查询、UNION - 并集、SUBQUERY - 子查询）。

  • table：输出结果集的表。

  • type：访问类型（ALL - 全表查询性能最差、index、range、ref、eq_ref、const、NULL）。

  • possible_keys：查询时可能用到的索引。

  • key：实际使用的索引。

  • key_len：索引字段的长度。

  • rows：扫描的行数，行数越少肯定性能越好。

  • extra：额外信息。

基准测试工具

sysbench

是一个模块化的、跨平台的多线程基准测试工具，主要用于测试各种不同系统参数下的数据库负载情况。主要包括以下几种测试方式：

• CPU 性能
• 磁盘 IO 性能
• 数据库 OLTP 基准测试

CentOS 安装

curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash
sudo yum -y install sysbench