MYSQL性能调优概述——卧底个人整理

时间：2010-02-25 来源：pkman110

原文链接：http://bbs.linuxtone.org/thread-5152-1-1.html

1，硬件优化
      a>内存——大内存、大内存位宽，尽量不要用SWAP；
      b>硬盘——15000RPM、RAID5、raid10
      c>CPU——64位、高主频、高缓存，高并行处理能力
      d>网络——标配的千兆网卡足矣，尽可能在同一局域网内，尽量避免诸如防火墙策略等不必要的开销

2，架构上的优化
      a>纵向拆解
            最简单的一台服务同时负责web、中间件、数据库多个角色；纵向拆解后就是数据库服务器专机专用，避免额外的服务可能导致的性能下降和不稳定性；如果将数据库服务器专机专用仍然无法满足需求，可以考虑在数据库和应用服务器之间加Memcached。
      b>横向拆解
            主从同步、负载均衡、高可用性集群，当单个mysql数据库无法满足日益增加的需求时，可以考虑在数据库这个逻辑层面增加多台服务器，以达到稳定、高效的效果。

http://bbs.linuxtone.org/thread-5152-1-1.html
3，操作系统级别的优化
      a>64位系统可以分给单个进程更多的内存、服务调优，禁用不必要启动的服务，修改文件描述符限制，留更多的资源给mysql；
      b>文件系统调优，给数据仓库一个单独的文件系统,推荐使用XFS，一般效率更高、更可靠。
      c>可以考虑在挂载分区时启用noatime选项。

4，数据库服务的优化
      a>使用linux/bsd操作系统进行编译安装，对编译参数进行性能优化，精简不必要启用的功能
      b>合适的应用程序接口。
      c>保持每个表都不要太大，可以对大表做横切和纵切；比如说我要取得某ID的lastlogin，完全可以做一张只有“ID”和“lastlog”的小表，而非几十、几百列数据的并排大表；另外对一个有1000万条记录的表做更新比对10个100万记录的表做更新一般来的要慢
      d>myisam引擎，表级锁，单锁开销小，但影响范围大，适合读多写少的表，不支持事物日志；表锁定不存在死锁
      e>innodb引擎，行级锁，锁定行的开销要比锁定全表要大，但影响范围小，适合写操作比较频繁的数据表；行级锁可能存在死锁。

5，my.cnf内参数的优化；
      优化总原则：给mysql的资源太少，则mysql施展不开；给mysql的资源太多，可能会拖累整个OS。
      a>总体资源占用的优化；
      open_files_limit——mysqld可以打开的文件的数量；
      max_connections——允许的并行客户端连接数目；
      max_connect_errors——允许的主机的错误连接数；
      table_cache——每个链接允许打开的表的数量；
      max_allowed_packet——从服务器接收的包的大小；
      thread_cache_size——缓存多少个待用线程；
      b>具体buffer的优化
      sort_buffer_size——每个线程可以分配的缓冲区的大小；
      join_buffer_size——不走索引的join操作可分配的缓冲区的大小；
      query_cache_size——为查询分配的缓存；
      query_cache_limit——不缓存大于该限制的查询结果；
      query_cache_min_res_unit——不缓存小于该限制的查询结果；
      tmp_table_size——内存内的临时表表超过该限制值，则写入硬盘；
      binlog_cache_size——二进制日志文件的缓存大小；
      key_buffer_size——myisam引擎的索引块共用缓冲区；
      read_buffer_size——为从数据表顺序读取数据的读操作保留的缓存区的长度；
      innodb_additional_mem_pool_size——InnoDB用来存储数据目录信息＆其它内部数据结构的内存池的大小。你应用程序里的表越多，你需要在这里分配越多的内存。
      innodb_buffer_pool_size——InnoDB用来缓存它的数据和索引的内存缓冲区的大小。理论上来说是越大越好，但要注意不要设置过大,否则,由于物理内存的竞争可能导致操作系统的换页颠簸.
      innodb_data_file_path——innodb表空间的指定以及大小，初始表空间大一些可以减少日后自增加表空间的系统开销。
      innodb_thread_concurrency——在InnoDb核心内的允许线程数量；
      innodb_log_buffer_size——InnoDB用来往磁盘上的日志文件写操作的缓冲区的大小。当日志大小超过该限定时，日志会被写入磁盘，比写入内存的I/O开销大。
      innodb_log_file_size——每个日志文件的大小。
      max_allowed_packet——包服务所能处理的请求包的最大大小以及服务所能处理的最大的请求大小。


6，查询优化,
      a>建表时表结构要合理，每个表不宜过大；在任何情况下均应使用最精确的类型。例如，如果ID列用Int是一个好主意，而用text类型则是个蠢办法；TIME列酌情使用DATE或者DATETIME。
      b>索引，所有的查询都走科学的索引，单个索引命中率低时使用联合索引；
      c>查询时尽量减少逻辑运算（与运算、或运算、大于小于某值的运算）；
      d>减少不当的查询语句，不要查询应用中不需要的列，比如说select * from 等操作。
      e>减小事务包的大小；
      f>将多个小的查询适当合并成一个大的查询，减少每次建立/关闭查询时的开销；
      g>将某些过于复杂的查询拆解成多个小查询，和上一条恰好相反
      h>建立和优化存储过程来代替大量的外部程序交互。


7，DEBUG工具：
      a>vmstat——vmstat 命令报告关于内核线程、虚拟内存、磁盘、陷阱和 CPU 活动的统计信息,
      b>iostat——iostat命令报告CPU、硬盘等设备的输入输出情况，可能需要安装sysstat rpm包
      c>top——动态显示当前系统的资源占用，和上文面的两个命令相比，top更侧重于进程。
      d>free——显示内存和swap占用情况。
      e>show processlist——显示当前运行或等待的线程，判断哪些查询语句总是处于等待状态
      f>EXPLAIN——“EXPLAIN + SQL语句”查看索引使用情况。
      g>show create table + “table_name” ——查看指定表的表结构
      h> select count(distinct  “row_name”) from "table_name";查看列内数据的唯一性，确定给哪一列创建索引。
      i>create index 创建索引，并用 show processlist、top观察创建索引后的效果。