mysql 性能优化思路 - mysqldumpslow /tmp/mysql-slow.log 字符集 utf-8 create database

提高MySQL服务的性能，响应速度：

1.替换有问题的硬件：内存，CPU，磁盘

2.服务的配置参数的配置

3.SQL的优化

1.服务参数的配置：
1.1 连接数，连接超时：
max_connections=1000
connect_timeout=50#等待建立连接的超时数，只有在登录时有效，默认是10秒
wait_timeout=120#等待关闭连接的不活动的超时时间，默认8小时，28800秒
2.优化SQL查询：
2.0 slow_query_log=1
2.1 slow_query_log_file=mysql-slow.log
2.2 long_query_time=5
2.3 log_queries_not_using_indexes=1 
service mysql restart
#mysqldumpslow

 

3.理想比率
mysql> show global status like 'Max_used_connections';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 28    |
+----------------------+-------+
1 row in set (0.00 sec)

mysql> show variables like '%max_connections%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set (0.00 sec)
'Max_used_connections' /  max_connections <85%

#######################################
mysql> show global status like 'open%tables%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables   | 64    |
| Opened_tables | 4994  | 大-->缓存太小，不断的打开表
+---------------+-------+


mysql> show variables like 'table_open_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| table_open_cache | 64    |  -- 设置成max_connection 512
+------------------+-------+
set global table_open_cache=1024;

https://www.cnblogs.com/fjping0606/p/6531292.html
Open_tables / Opened_tables >= 0.85
Open_tables / table_open_cache <= 0.95

 

4.缓存参数控制：
mysql> show variables like 'thread%size';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| thread_cache_size | 0     | -- 可重用线程数
+-------------------+-------+

mysql> show global status like 'threads%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 0     |
| Threads_connected | 27    |  线程重用情况
| Threads_created   | 91    |
| Threads_running   | 2     |
+-------------------+-------+

https://www.oschina.net/question/17_525

Threads_created表示创建过的线程数，如果发现Threads_created值过大的话，
表明 MySQL服务器一直在创建线程，这也是比较耗资源，可以适当增加配置文件中thread_cache_size值
https://www.landui.com/help/show-1609.html

 3.utf-8

CREATE DATABASE db_name DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

 参考：https://www.cnblogs.com/micro-chen/p/5810838.html