MYSQL性能调优概述

优化思路     硬件(高配置)         ==>架构(服务分离,负载均衡)            ==>单一服务器优化(系统,服务)               ==>库结构(表合理,索引)                  ==>DEBUG工具查询需要优化的地方   1 硬件优化   a>内存--大内存,大内存位宽,尽量不要用SWAP ？   b>硬盘--15000RPM,RAID5,RAID10   c> CPU--64位,高主频,高缓存,高并行处理能力   d>网络--标配的千兆网卡,尽可能在同一局域网,尽量避免诸如防火墙侧罗等不必要的开销 2 架构上的优化   a>纵向拆分      eg:一台服务器同时负责web,中间件,数据库多个角色.         纵向拆分后就是数据库服务器专机专用,避免额外服务导致的性能下降和不稳定性         若仍然无法满足需求,可以考虑在数据库和应用服务器之间加memcached   b>横向拆分      eg:主从同步,读写分离,负载均衡,高可用性集群.         当单一MySQL数据库无法满足日益增长需求时,可以考虑在数据库这个逻辑层面         增加多台服务器,以达到稳定,高效的目的. 3 操作系统级别的优化   a>64位系统可以分给单个进程更多的内存,服务调优.     禁用不必要启动的服务.修改文件描述符限制,留给更多的资源给MySQL   b>文件系统调优:给数据仓库一个单独文件系统,推荐使用XFS,一般效率更高,更可靠   c>可以考虑在挂载分区时启用notime选项 4 数据库服务的优化   a>使用linux/bsd操作系统进行编译安装,对编译参数进行性能优化,精简不必要启用的功能   b>合适的应用程序接口   c>标尺每个表都不要太大,对大表做横切和纵切     eg:比如我要取得某ID的lastlogin,完全可以做一张只有"ID"和"lastlogin"的小表,          而非几十,几百列数据的并排大表        因为对一个有1000万条记录的表做更新比对10个100万的表做更新一般来的要慢   d>myisam引擎,表级锁,但所开销小,但影响范围大,适合读多写少的表,不支持失误日志;        表锁定不存在死锁   e>innodb引擎,行级锁,锁定行的开销要比锁定全表要大,但影响小,适合写操作比较频繁        的数据表;行级锁可能存在死锁 5 my.cnf内参数的优化   优化总原则:给MySQL的资源太小,则MySQL施展不开              给MySQL的资源太多,可能会拖累整个OS   a>总体资源占用的优化     open_file_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>建表时表结构要合理,每个表不宜过大.在任何情况下均应使用最精确的类型.       eg:如果ID列用Int是一个好主意,而用text类型则是个蠢办法          TIME列酌情使用DATE或DATETIME   b>索引,所有的查询都是走科学的索引,单个索引命中率低时使用联合索引   c>查询时尽量减少逻辑运算(与运算,或运算.大于小于某值的运算)   d>减少不当的查询语句,不要查询应用中不需要的列.       eg:select * from 等操作   e>减少事务包的大小   f>将多个小的查询适当合并成一个大的查询,减少每次建立/关闭查询时的开销   g>将某些国语复杂的查询拆分成多个小查询(与上一条恰好相反)   h>建立和优化存储过程来代替大量的外部程序交互 7 DEBUG工具   a>vmstat————vmstat命令报告关于内核线程,虚拟内存,磁盘,陷阱和CPU活动的统计信息   b>iostat————iostat命令报告CPU,硬盘灯设备的输入输出情况,可能需要安装sysstat rpm包   c>top————动态显示当前系统的资源占用,与iostat比,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 观察创建索引后的效果.