1、最大连接数优化：

设定的最大连接数查询：
mysql> show variables like '%max_connections%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 100   | 
+-----------------+-------+
1 row in set (0.00 sec)

实际中应用的并发数值：

mysql> show status like '%Max%connections%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 24    | 
+----------------------+-------+
1 row in set (0.00 sec)

Max_used_connections/max_connections=24/100=0.24-----------------------此数值过于小了，可以优化

https://4bo.cn/aff.php?aff=021

2、myisam 引擎内存优化：
优化前的值：
mysql> show variables like '%key%';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    3645
Current database: *** NONE ***

+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| delay_key_write          | ON         | 
| have_rtree_keys          | YES        | 
| key_buffer_size          | 268435456  | 
| key_cache_age_threshold  | 300        | 
| key_cache_block_size     | 1024       | 
| key_cache_division_limit | 100        | 
| max_seeks_for_key        | 4294967295 | 
| ssl_key                  |            | 
+--------------------------+------------+
8 rows in set (0.01 sec)

mysql> show status like '%key%';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| Com_preload_keys       | 0      | 
| Com_show_keys          | 0      | 
| Handler_read_key       | 0      | 
| Key_blocks_not_flushed | 0      | 
| Key_blocks_unused      | 225061 | 
| Key_blocks_used        | 6899   | 
| Key_read_requests      | 457635 | 
| Key_reads              | 7940   | 
| Key_write_requests     | 2600   | 
| Key_writes             | 848    | 
+------------------------+--------+
10 rows in set (0.00 sec)
 Key_reads / Key_read_requests

参数更改24小时候变化：
mysql> show status like '%key%';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    401185
Current database: *** NONE ***

+------------------------+----------+
| Variable_name          | Value    |
+------------------------+----------+
| Com_preload_keys       | 0        | 
| Com_show_keys          | 0        | 
| Handler_read_key       | 0        | 
| Key_blocks_not_flushed | 0        | 
| Key_blocks_unused      | 240561   | 
| Key_blocks_used        | 10170    | 
| Key_read_requests      | 50339497 | 
| Key_reads              | 185590   | 
| Key_write_requests     | 702758   | 
| Key_writes             | 93625    | 
+------------------------+----------+
10 rows in set (0.00 sec)


3、创建临时表优化

mysql> show global status like '%created_tmp%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Created_tmp_disk_tables | 711557  | 
| Created_tmp_files       | 3135    | 
| Created_tmp_tables      | 1044753 | 
+-------------------------+---------+
3 rows in set (0.00 sec)

参考：created_tmp_disk_tables / created_tmp_tables * 100% <= 25%
       只有256MB以下的临时表才能全部放内存，超过的就会用到硬盘临时表

mysql> show variables like '%tmp%';
+-------------------+----------+
| Variable_name     | Value    |
+-------------------+----------+
| bdb_tmpdir        | /tmp/    | 
| max_tmp_tables    | 32       | 
| slave_load_tmpdir | /tmp/    | 
| tmp_table_size    | 33554432 | 
| tmpdir            | /tmp/    | 
+-------------------+----------+
5 rows in set (0.01 sec)
解析：
tmp_table_size 
如果一张临时表超出该大小，MySQL产生一个The table tbl_name is full形式的错误，如果你做很多高级GROUP BY查询，增加tmp_table_size值。
max_tmp_tables 
（该选择目前还不做任何事情)。一个客户能同时保持打开的临时表的最大数量。


mysql> show variables like '%tmp%';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    23
Current database: *** NONE ***

+-------------------+----------+
| Variable_name     | Value    |
+-------------------+----------+
| bdb_tmpdir        | /tmp/    | 
| max_tmp_tables    | 32       | 
| slave_load_tmpdir | /tmp/    | 
| tmp_table_size    | 58720256 | 
| tmpdir            | /tmp/    | 
+-------------------+----------+
5 rows in set (0.00 sec)


my.cnf参数：
[mysqld]
wait_timeout=120
interactive_timeout=120
skip-name-resolve
tmp_table_size=56M


mysql> show status like '%tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     | 
| Created_tmp_files       | 5     | 
| Created_tmp_tables      | 2     | 
+-------------------------+-------+
3 rows in set (0.00 sec)

4、打开表Open_tables优化：

mysql> show status like '%open%table%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Com_show_open_tables   | 0     | 
| Open_tables            | 64    | 
| Opened_tables          | 0     | 
| Slave_open_temp_tables | 0     | 
+------------------------+-------+
4 rows in set (0.01 sec)

参考值：
open_tables / opened_tables * 100% >= 85%

open_tables / table_cache * 100% <= 95%


5、线程优化：

 

mysql> show global status like '%thread%';

+------------------------+-------+

| Variable_name          | Value |

+------------------------+-------+

| Delayed_insert_threads | 0     | 

| Slow_launch_threads    | 0     | 

| Threads_cached         | 0     | 

| Threads_connected      | 2     | 

| Threads_created        | 26263 | 

| Threads_running        | 1     | 

+------------------------+-------+

6 rows in set (0.00 sec)


mysql> show  status like '%thread%';

+------------------------+-------+

| Variable_name          | Value |

+------------------------+-------+

| Delayed_insert_threads | 0     | 

| Slow_launch_threads    | 0     | 

| Threads_cached         | 0     | 

| Threads_connected      | 1     | 

| Threads_created        | 26399 | 

| Threads_running        | 1     | 

+------------------------+-------+

6 rows in set (0.00 sec)


 

mysql> show variables like '%thread_cache%';

+-------------------+-------+

| Variable_name     | Value |

+-------------------+-------+

| thread_cache_size | 0     | 

+-------------------+-------+

1 row in set (0.00 sec)


6、查询缓存优化：
mysql> show global status like 'qcache%';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    33232
Current database: *** NONE ***

+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Qcache_free_blocks      | 0     | 
| Qcache_free_memory      | 0     | 
| Qcache_hits             | 0     | 
| Qcache_inserts          | 0     | 
| Qcache_lowmem_prunes    | 0     | 
| Qcache_not_cached       | 0     | 
| Qcache_queries_in_cache | 0     | 
| Qcache_total_blocks     | 0     | 
+-------------------------+-------+
8 rows in set (0.00 sec)

解析：

mysql查询缓存变量解释：

qcache_free_blocks：缓存中相邻内存块的个数。数目大说明可能有碎片。flush query cache会对缓存中的碎片进行整理，从而得到一个空闲块。

qcache_free_memory：缓存中的空闲内存。

qcache_hits：每次查询在缓存中命中时就增大

qcache_inserts：每次插入一个查询时就增大。命中次数除以插入次数就是不中比率。

qcache_lowmem_prunes： 缓存出现内存不足并且必须要进行清理以便为更多查询提供空间的次数。这个数字最好长时间来看；如果这个数字在不断增长，就表示可能碎片非常严重，或者内存 很少。（上面的 free_blocks和free_memory可以告诉您属于哪种情况）

qcache_not_cached：不适合进行缓存的查询的数量，通常是由于这些查询不是 select 语句或者用了now()之类的函数。

qcache_queries_in_cache：当前缓存的查询（和响应）的数量。

qcache_total_blocks：缓存中块的数量。


mysql> show variables like 'query_cache%';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| query_cache_limit            | 1048576 | 
| query_cache_min_res_unit     | 4096    | 
| query_cache_size             | 0       | 
| query_cache_type             | ON      | 
| query_cache_wlock_invalidate | OFF     | 
+------------------------------+---------+
5 rows in set (0.00 sec)
解析:

query_cache_limit：超过此大小的查询将不缓存

query_cache_min_res_unit：缓存块的最小大小

query_cache_size：查询缓存大小

query_cache_type：缓存类型，决定缓存什么样的查询，示例中表示不缓存 select sql_no_cache 查询

query_cache_wlock_invalidate：当有其他客户端正在对myisam表进行写操作时，如果查询在query cache中，是否返回cache结果还是等写操作完成再读表获取结果。

query_cache_min_res_unit的配置是一柄”双刃剑”，默认是4kb，设置值大对大数据查询有好处，但如果你的查询都是小数据查询，就容易造成内存碎片和浪费。

查询缓存碎片率 = qcache_free_blocks / qcache_total_blocks * 100%

如果查询缓存碎片率超过20%，可以用flush query cache整理缓存碎片，或者试试减小query_cache_min_res_unit，如果你的查询都是小数据量的话。

查询缓存利用率 = (query_cache_size - qcache_free_memory) / query_cache_size * 100%

查询缓存利用率在25%以下的话说明query_cache_size设置的过大，可适当减小；查询缓存利用率在80％以上而且qcache_lowmem_prunes > 50的话说明query_cache_size可能有点小，要不就是碎片太多。

查询缓存命中率 = (qcache_hits - qcache_inserts) / qcache_hits * 100%

示例服务器 查询缓存碎片率 ＝ 20.46％，查询缓存利用率 ＝ 62.26％，查询缓存命中率 ＝ 1.94％，命中率很差，可能写操作比较频繁吧，而且可能有些碎片。