Mysql性能调优
提高Mysql系统的性能,相应速度
替换有问题的硬件(CPU/磁盘/内存等)
服务程序运行参数的调整
对SQL查询进行优化
一：并发及连接控制
连接数,连接超时
max_connections 允许的最大并发连接数
connect_timeout 等待建立连接的超时秒数,默认10秒,只在登陆时有效
wait_timeout 等待关闭连接的不活动超时秒速，默认28800秒(8小时)
1）查看服务器启动后已经同时使用的连接的最大数量。
mysql> FLUSH STATUS; Query OK, 0 rows affected (0.00 sec) mysql> SHOW GLOBAL STATUS LIKE 'max_used_connections'; +----------------------+-------+ | Variable_name | Value | +----------------------+-------+ | Max_used_connections | 3 | +----------------------+-------+ 1 row in set (0.05 sec)
2）查看当前的最大连接数限制
mysql> SHOW VARIABLES LIKE 'max_connections'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 151 | +-----------------+-------+ 1 row in set (0.00 sec)
mysql> show status like 'Threads%'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | Threads_cached | 58 | | Threads_connected | 57 | //这个数值指的是打开的连接数 | Threads_created | 3676 | | Threads_running | 4 | //这个数值指的是激活的连接数，这个数值一般远低于connected数值，代表当前并发数。 +-------------------+-------+
显示当前正在执行的MySQL连接
show processlist
显示当前MySQL状态
mysqladmin -u<user> -p<pwd> -h<host> status // 显示mysql的其他状态 mysqladmin -u<user> -p<pwd> -h<host> extended-status
OS ulimit参数
[root@0087~]# ulimit -n 65535
二：MySQL缓存参数控制
缓冲区，线程数量，开表数量
key_buffer-size 用于MyISAM引擎的关键索引缓存大小
sort_buffer_size 为每个要排序的线程分配此大小的缓存空间
read_buffer_size 为顺序读取表记录保留的缓存大小
read_fnd_buffer_size 为安排序结果读取表记录保留的缓存大小
thread_cache_size 允许保存在缓存中被重用的线程数量
table_open_cache 为所有线程缓存的打开的表的数量
MySQL缓存参数控制
当 Key_reads / Key_read_requests 较低时，可适当加大key_buffer_size的缓存值，以提高性能。而增大sort_buffer_size的值，可以显著提高ORDER和GROUP的响应速度。
1）查看key_read相关数值
mysql> SHOW GLOBAL STATUS LIKE 'key_read%'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | Key_read_requests | 0 | | Key_reads | 0 | +-------------------+-------+ 2 rows in set (0.00 sec)
2）查看当前的key_buffer_size缓存大小
mysql> SHOW VARIABLES LIKE 'key_buffer_size'; +-----------------+---------+ | Variable_name | Value | +-----------------+---------+ | key_buffer_size | 8388608 | +-----------------+---------+ 1 row in set (0.03 sec)
3）查看当前的sort_buffer_size大小
mysql> SHOW VARIABLES LIKE 'sort_buffer_size'; +------------------+--------+ | Variable_name | Value | +------------------+--------+ | sort_buffer_size | 262144 | +------------------+--------+ 1 row in set (0.00 sec)
4）查看检索表记录时的读取缓存大小
缓存值read_buffer_size和read_rnd_buffer_size会影响SQL查询的响应速度：
mysql> SHOW VARIABLES LIKE 'read_%_size'; +----------------------+--------+ | Variable_name | Value | +----------------------+--------+ | read_buffer_size | 131072 | | read_rnd_buffer_size | 262144 | +----------------------+--------+ 2 rows in set (0.00 sec)
三：MySQL线程重用和开表控制
分析“已打开表的数量/当前可缓存表的数量”，比值不超过95%就基本正常。
1）查看当前已打开、一共打开过多少个表
mysql> SHOW GLOBAL STATUS LIKE 'open%tables'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Open_tables | 23 | | Opened_tables | 72 | +---------------+-------+ 2 rows in set (0.01 sec)
2）查看当前可缓存多少个打开的表
mysql> SHOW VARIABLES LIKE 'table_open_cache'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | table_open_cache | 2000 | +------------------+-------+ 1 row in set (0.00 sec)
四：SQL查询优化
常用日志种类及选项
| 类型 | 用途 | 配置 |
| 错误日志 | 记录启动/运行/停止过程中的错误信息 | log-error[=name] |
| 查询日志 | 记录客户端连接和查询操作 | general-log,general-log-file= |
| 慢查询日志 | 记录耗时较长或不使用索引的查询操作 | slow-query-log,slow-query-log-file=,long-query-time= |
优化SQL查询
记录慢查询
| 选项 | 含义 |
| slow-query-log | 启用慢查询 |
| slow-query-log-file | 制定慢查询日志文件 |
| long-query-time | 指定超时时间(默认10秒)的查询才会被记录 |
| log-queries-not-using-indexes | 记录未使用索引的查询 |
1）调整my.cnf配置文件，启用慢查询
[root@dbsvr1 ~]# vim /etc/my.cnf [mysqld] .. .. slow_query_log=1 //启用慢查询 slow_query_log_file=mysql-slow.log //制定慢查询日志文件 long_query_time=5 //查询耗时超过5秒才记录 log_queries_not_using_indexes=1 //记录未使用索引的查询 [root@dbsvr1 ~]# service mysql restart Shutting down MySQL..... [确定] Starting MySQL.... [确定]
2）查看慢查询日志（mysqldumpslow工具）
[root@dbsvr1 ~]# mysqldumpslow /var/lib/mysql/mysql-slow.log Reading mysql slow query log from /var/lib/mysql/mysql-slow.log Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), 0users@0hosts .. ..
3）了解与查询相关的缓存选项
查看当前的查询缓存大小：
mysql> SHOW VARIABLES LIKE 'query_cache%'; +------------------------------+---------+ | Variable_name | Value | +------------------------------+---------+ | query_cache_limit | 1048576 | //超过此大小则不再缓存 | query_cache_min_res_unit | 4096 | | query_cache_size | 1048576 | //缓存空间的大小 | query_cache_type | OFF | | query_cache_wlock_invalidate | OFF | +------------------------------+---------+ 5 rows in set (0.00 sec)
查看当前的查询缓存统计数据：
mysql> SHOW GLOBAL STATUS LIKE 'qcache%'; +-------------------------+---------+ | Variable_name | Value | +-------------------------+---------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 1031368 | //缓存中的空闲内存 | Qcache_hits | 0 | | Qcache_inserts | 0 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 100 | //不适合缓存的数量 | Qcache_queries_in_cache | 0 | | Qcache_total_blocks | 1 | +-------------------------+---------+ 8 rows in set (0.00 sec)
五：关于MySQL状态和相关变量的查看
1）查看服务器的相关状态值（运行中动态变化）
使用SHOW GLOBAL STATUS语句，可结合LIKE条件做模糊过滤。
默认有400多个状态值：
mysql> SHOW GLOBAL STATUSG *************************** 1. row *************************** Variable_name: Aborted_clients Value: 0 *************************** 2. row *************************** Variable_name: Aborted_connects Value: 0 *************************** 3. row *************************** Variable_name: Binlog_cache_disk_use Value: 0 .. .. //省略中间的大量状态值 .. .. *************************** 438. row *************************** Variable_name: Uptime Value: 5322 *************************** 439. row *************************** Variable_name: Uptime_since_flush_status Value: 2283 439 rows in set (0.00 sec)
2）查看服务器的运行选项（一般为静态限制，可通过my.cnf文件配置，或SET修改）
使用SHOW VARIABLES语句，也可结合LIKE条件做模糊过滤。
默认也有400多个（接近500个）配置选项：
mysql> SHOW VARIABLESG *************************** 1. row *************************** Variable_name: auto_increment_increment Value: 1 *************************** 2. row *************************** Variable_name: auto_increment_offset Value: 1 .. .. //省略中间的大量状态值 .. .. *************************** 489. row *************************** Variable_name: wait_timeout Value: 28800 *************************** 490. row *************************** Variable_name: warning_count Value: 0 490 rows in set (0.01 sec)
查看行锁等待数量
show status like 'innodb_row_lock_current_waits';
系统启动后到现在总共等待的次数
show status like 'innodb_row_lock_waits';
查看表锁等待的数量
show status like 'table%';
查看隔离事务默认的选项
show variables like 'tx_isolation';
调优思路总结
| 办法 | 具体操作 |
| 升级硬件 | ＣＰＵ／内存／硬盘 |
| 加大网络带宽 | 付费加大带宽 |
| 调整mysql服务运行参数 | 并发连接数，连接超时时间，重复使用的线程数 |
| 调整查询相关的参数 | 查询缓存，索引缓存 |
| 启用慢查询日志 | slow-query-log |
| 网络架构不合理 | 调整网络架构 |
优化总结：
1.任何情况下SELECT COUNT(*) FROM tablename是最优选择；
2.尽量减少SELECT COUNT(*) FROM tablename WHERE COL = ‘value’ 这种查询；
3.杜绝SELECT COUNT(COL) FROM tablename WHERE COL2 = ‘value’ 的出现。