MySQL优化入门

MySQL是目前常用的RDBMS(RelationalDatabaseManagementSystem/关系数据库管理系统),还有其他如PostgreSQL,Oracle,DB2等关系数据库管理系统。而数据库性能的重要性无需强调,在这里简单说一下,安装MySQL之后的优化相关的话题。

计算MySQL使用内存

首先确认一下,计算MySQL进程占用内存的方法。

MySQL占用内存 = 全局缓存  + ( 线程缓存 x 最大连接数 )

全局缓存的占用内存,用以下方法计算。

max_heap_table_size参数不一定分配内存,在这里为了安全也计算到全局缓存。

全局缓存 = key_buffer_size
                + innodb_buffer_pool_size
                + innodb_additional_mem_pool_size
                + innodb_log_buffer_size
                + max_heap_table_size
                + query_cache_size

线程缓存的占用内存,用以下方法计算。

在通常查询中myisam_sort_buffer_size使用的可能性很小,因此没有计算到线程缓存。并且在这里把max_allowed_packet计算在线程缓存里,如果接传送的数据量不大的话,可使用net_buffer_length进行计算。

线程缓存 = sort_buffer_size
                + read_rnd_buffer_size
                + join_buffer_size
                + read_buffer_size
                + max_allowed_packet
                + thread_stack

MySQL缓存

如何使用缓存对MySQL的性能表现至关重要,MySQL有以下2种缓存。

  • 全局缓存(Global Cache)
  • 线程缓存(Thread Cache)

全局缓存

innodb_buffer_pool_size
  • 缓存InnoDB的索引及数据
  • 使用InnoDB时至关重要的参数
innodb_additional_mem_pool_size
  • InnoDB存储的数据目录信息及内部数据结构
  • 不足时往MySQL错误日志文件输出警告(Warning)
  • 使用默认值,查看MySQL错误日志文件不足时再增加
innodb_log_buffer_size
  • InnoDB事务日志使用的缓冲区
  • 事务结束或者一定间隔将缓存区的日志写到文件(同步到磁盘)
  • 尽量给其他参数多配置内存
key_buffer_size
  • 缓存MyISAM的索引
query_cache_size
  • 缓存查询(SELECT)的结果
  • 对MySQL性能有直接影响
  • query_cache_type参数可改变MySQL缓存行为

线程缓存

sort_buffer_size
  • ORDER BY,GROUP BY时使用区域
  • 根据程序的使用情况进行配置
read_rnd_buffer_size
  • 读取排序后数据时使用
  • 提高ORDER BY性能
join_buffer_size
  • 进行表结合时,如没有使用索引的话使用该区域
  • 表结合推荐使用索引,所以该参数无需配置的过大
read_buffer_size
  • 读取全表时的使用区域
  • 不使用索引的查询是,不应该使用的因此该参数无需配置的过大
myisam_sort_buffer_size
  • MyISAM的DDL(DataDefinitionLanguage)的索引排序时使用的区域
  • 通常查询不会使用该区域,因此默认就可以
max_allowed_packet
  • 数据包发送缓冲区是存储接传送数据包的内存区域
  • 被net_buffer_length参数初期化,根据需要扩张到max_allowed_packe指定的大小

内存以外的参数

max_connections
  • 可连接MySQL数据库的最大连接数
  • 默认是151
innodb_lof_file_size
  • 保存InnoDB更新日志到磁盘
  • innodb_log_file已满时,innodb_buffer_pool更新日志写入磁盘
  • 调整innodb_buffer_pool_size时,innodb_log_file_size也需调整
  • 调整的越大Crash Recovery的时间也会跟着变长
table_open_cache
  • 保存使用表(Table)的文件指针
  • 至少需要「同时连接数 x Table数」
  • MyISAM是一个表(Table)需要2个文件指针
  • 注意OS限制 ※cat /proc/sys/fs/file-max
thread_cache_size
  • 通过缓存线程(Thread),降低连接时的负荷
  • 根据实际的负荷进行配置