mysql性能优化
- 练习相关优化选项
- 启用慢查询日志
- 查看各种系统变量、状态变量
一、性能优化
1.查看服务运行的参数
mysql> show variables\G;
mysql> show variables like "%innodb%";
2. 并发链接数量
mysql> flush status;
mysql> show global status like "Max_used_connections";
mysql> show variables like "max_connections%";
3. 链接超时时间
mysql> show variables like "%timeout%";
4.允许保存在缓存中被重用的线程数量
mysql> show variables like "thread_cache_size";
5. 用于MyISAM引擎的关键索引缓存大小
mysql> show variables like "key_buffer_size";
6. 为每个要排序的线程分配此大小的缓存空间
mysql> show variables like "sort_buffer_size";
7. 为顺序读取表记录保留的缓存大小
mysql> show variables like "read_buffer_size";
8. 为所有线程缓存的打开的表的数量
mysql> show variables like "table_open_cache";
二、Sql查询优化
1. 常用日志种类及选项
[root@master10 ~]# vim /etc/my.cnf
...
slow_query_log=1
slow_query_log_file=mysql-slow.log
long_query_time=5
log_queries_not_using_indexes=1
...
[root@master10 ~]# systemctl restart mysqld
[root@master10 ~]# mysqldumpslow /var/lib/mysql/mysql-slow.log
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 | 1031832 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 40 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 1 |
+-------------------------+---------+
8 rows in set (0.00 sec)