MySQL优化⼆（连接优化和缓存优化）
MySQL优化⼆（连接优化和缓存优化）
上⽂这篇⽂章中提到了⼀个很重要的概念，就是show variables是⽤来表⽰系统编译或者配置在myf中的变量值。⽽show status则称之为状态值，显⽰的是当前服务实例运⾏所具有的状态信息，是⼀个动态改变的值。因此常⽤来观测当前MySQl的运⾏是否正常，如果不正常那么依靠调整静态参数来提⾼MySQL的性能。所以明⽩这两个概念的不同，是后⾯调优的基础。
⼀、MySQL 连接优化
记得有⼀次在公司内部连接MySQL的时候，总是连接不上。到DBA后，经过排查原因，是当前MySQL连接数满了，经过调整后，解决了问题。引发连接数过多的错误原因⼀般有两个，第⼀的确是有很多⼈在连接MySQL，造成连接数⽤完。第⼆种是max_connections值过⼩。
1、连接参数（show variables）
mysql> show variables like '%connect%';
+-----------------------------------------------+-----------------+
| Variable_name | Value |
+-----------------------------------------------+-----------------+
| character_set_connection | utf8 |
| collation_connection | utf8_general_ci |
| connect_timeout | 10 |
| disconnect_on_expired_password | ON |
| init_connect | |
| max_connect_errors | 100 |
| max_connections | 151 |
| max_user_connections | 0 |
| performance_schema_session_connect_attrs_size | 512 |
+-----------------------------------------------+-----------------+
max_connections是指MySQL服务实例能够同时接受的的最⼤并发连接数。MySQL实际上⽀持最⼤连接数加⼀的算法，保障当连接数⽤完的时候，超级管理员依然可以和服务端建⽴连接，进⾏管理。
max_user_connections设置指定账号的最⼤并发连接数。
max_connect_errors当某台⾮法主机恶意连接MySQL服务端，遭到的错误达到设置值后，MySQL会解决来⾃该主机的所有连接。但执⾏flush hosts后会清零。
2、连接状态(show status)
有⼀点需要注意的，变量值（show variables）是以⼩写字母开头的，⽽状态值（show status）是以⼤写字母开头。这样区分有助于记忆和分类
mysql> show status like '%connections%';
+-----------------------------------+-------+
| Variable_name | Value |
+-----------------------------------+-------+
| Connection_errors_max_connections | 0 |
| Connections | 197 |
| Max_used_connections | 2 |
+-----------------------------------+-------+
Connection_errors_max_connections当MySQL的最⼤并发数⼤于系统变量（show variables）中max_connections的最⼤并发数，因此⽽被拒绝的次数，将会记录在这个变量⾥。如果Connection_error_max_connections值⽐较⼤，则说明当前系统并发⽐较⾼，要考虑调⼤max_connections的值。
Connections表⽰MySQL从启动⾄今，成功建⽴连接的连接数，这个值是不断累加的。
查看mysql索引Max_used_connections表⽰MySQL从启动⾄今，同⼀时刻并发的连接数，取得是最⼤值。如果这个值⼤于max_connections则表明系统经常处于⾼并发的状态，应该考虑调⼤最⼤并发连接数。
3、连接线程参数（thread variabls and status）
mysql> show variables like 'thread%';
+--------------------+---------------------------+
| Variable_name | Value |
+--------------------+---------------------------+
| thread_cache_size | 9 |
| thread_concurrency | 10 |
| thread_handling | one-thread-per-connection |
| thread_stack | 262144 |
+--------------------+---------------------------+
thread_cache_size设置连接线程缓存的数⽬。这个缓存相当于MySQL线程的缓存池（thread cache pool），将空闲的连接线程放⼊连接池中缓存起来，⽽⾮
⽴即销毁。当有新的连接请求时，如果连接池中有空闲的连接，则直接使⽤。否则要重新创建线程。创建线程是⼀个不⼩的系统开销。MySQL的这部分线程处理和Nginx 的线程处理有异曲同⼯之妙，以后介绍Nginx的线程处理时，会拿来做对⽐。
thread_handling默认值是： one-thread-per-connection 表⽰为每个连接提供或者创建⼀个线程来处理请求，直⾄请求完毕，连接销毁或者存⼊缓存池。当值是no-threads 时，表⽰在始终只提供⼀个线程来处理连接，⼀般是单机做测试使⽤的。
thread_stack stack 是堆的意思，由这篇博客，知道进程和线程都是有唯⼀的ID的，进程的ID系统会维护，⼆线程的ID，则由具体的线程库区维护，当进程或者线程休眠的时候，进程的上下⽂信息要在内存中开辟出⼀块区域，保存进程的上下⽂信息，以便于迅速唤醒程序。默认为MySQL的每个线程设置的堆栈⼤⼩为：262144/1024=256k
查看线程状态信息
mysql> show status like 'Thread%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_cached | 1 |
| Threads_connected | 1 |
| Threads_created | 2 |
| Threads_running | 1 |
+-------------------+-------+
Thread_cached当前线程池的线程数
Thread_connected当前的连接数
Thread_cached: 当前连接线程创建数, 如果这个值过⾼，可以调整thread cache size 也就是调整线程缓存池的⼤⼩。
Thred_runnint: 当前活跃的线程数。
连接请求堆栈
MySQL在很短的时间内，突然收到很多的连接请求时，MySQL会将不能来得及处理的连接请求保存在堆栈中，以便MySQL后续处理。back_log参数设置了堆栈的⼤⼩，可以通过如下命令查看：
mysql> show variables like 'back_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| back_log | 80 |
+---------------+-------+
连接异常
mysql> show status like 'Aborted%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| Aborted_clients | 0 |
| Aborted_connects | 219 |
+------------------+-------+
Aborted_clients MySQL 客户机被异常关闭的次数。
Aborted_connects试图连接到MySQL服务器⽽失败的连接次数。
other
mysql> show status like 'Slow%';
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| Slow_launch_threads | 0 |
| Slow_queries | 0 |
+---------------------+-------+
mysql> show variables like 'slow_launch_time';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| slow_launch_time | 2 |
+------------------+-------+
Slow_lunch_threads创建线程的时间过长，超过slow_launch_time的设定值，则会记录。
可以通过使⽤Connection_error%来查看连接的错误状态信息：
mysql> show status like 'Connection_error%';
+-----------------------------------+-------+
| Variable_name | Value |
+-----------------------------------+-------+
| Connection_errors_accept | 0 |
| Connection_errors_internal | 0 |
| Connection_errors_max_connections | 0 |
| Connection_errors_peer_address | 0 |
| Connection_errors_select | 0 |
| Connection_errors_tcpwrap | 0 |
+-----------------------------------+-------+
Connection_errors_peer_address查MySQL客户机IP地址是发⽣的错误数。
⼆、缓存的优化
MySQL 内部处处皆缓存，等什么时候看了MySQL的源码，再来详细的分析缓存的是如何利⽤的。这部分主要将各种显式的缓存优化：
1. 查询缓存优化
2. 结果集缓存
3. 排序缓存
4. join 连接缓存
5. 表缓存Cache 与表结构定义缓存Cache
6. 表扫描缓存buffer
7. MyISAM索引缓存buffer
8. ⽇志缓存
9. 预读机制
10. 延迟表与临时表
1、查询缓存优化
查询缓存不仅将查询语句结构缓存起来，还将查询结果缓存起来。⼀段时间内，如果是同样的SQL，则直接从缓存中读取结果，提⾼查数据的效率。但当缓存中的数据与硬盘中的数据不⼀致时，缓存就会失效。
mysql> show variables like '%query_cache%';
+------------------------------+---------+
| Variable_name | Value |
+------------------------------+---------+
| have_query_cache | YES |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 1048576 |
| query_cache_type | OFF |
| query_cache_wlock_invalidate | OFF |
+------------------------------+---------+
have_query_cache是否⽀持查询缓存。
query_cache_limit如果某条select语句的结果集⼤⼩超过了query cache limit的值时，这个结果集将不会被添加到查询缓存。
query_cache_min_res_unit查询缓存是以块来申请内存空间的，每次申请的块⼤⼩为设定值。4K 是⾮常⼀个合理的值，不必修改。
query_cache_size查询缓存的⼤⼩。
query_cache_type查询缓存的类型，值有 0（OFF）、1（ON）、2（DEMOND）。OFF表⽰查询缓存
是关闭的。ON 表⽰查询总是先到查询缓存中去查，除⾮在select 语句中包含sql_no_cache选项。 DEMOND 表⽰不适⽤缓存，除⾮在select 语句中包含sql_cache选项。
query_cache_wlock_invalidate该参数⽤于设置⾏级排它锁与查询缓存之间的关系，默认为为0（OFF），表⽰施加⾏级排它所的同时，该表的所有查询缓存依然有效。如果设置为1（ON），表⽰事假⾏级排它锁的同时，该表的所有查询缓存失效。
查看查询缓存的命中率
mysql> show status like 'Qcache%';
+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 1031360 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 1 |
+-------------------------+---------+
查看当前缓存的状态信息：
Qcache_free_blocks
表⽰查询缓存中处以重现状态的内存块数（碎⽚数量）。如果Qcache_free_blocks的值较⼤，则意味着查询缓存中碎⽚⽐较多，表明查询结果集较⼩，此时可以减⼩query_cache_min_res_unit的值。使⽤flush query cache会对缓存中的若⼲个碎⽚进⾏整理，从⽽得到⼀个⽐较⼤的空闲块。缓存碎⽚率 = Qc
ache_free_blocks/ Qcache_total_blocks * 100%
Qcache_free_memory
表⽰当前MySQL服务实例的查询缓存还有多少可⽤内存。
Qcache_hits
表⽰使⽤查询缓存的次数，该值会依次增加。如果Qcache_hits⽐较⼤，则说明查询缓存使⽤的⾮常频繁，此时需要增加查询缓存。
Qcache_inserts
表⽰查询缓存中此前总共缓存过多少条select 语句的结果集。
Qcache_lowmen_prunes
表⽰因为查询缓存已满⽽溢出，导致MySQL删除的查询结果个数。如果该值⽐较⼤，则表明查询缓存过⼩。
Qcache_not_cached
表⽰没有进⼊查询缓存的select个数
Qcache_queryies_in_cache
表⽰查询缓存中缓存这多少条select语句的结果集
Qcache_total_blocks
查询缓存的总个数
缓存命中率的计算⽅式：查询缓存的命中率 = Qcache_hits / Com_select * 100%
其中Com_select为当前MySQL实例执⾏select 语句的个数。⼀般情况下Com_select = Qcache_insert + Qcache_not_cached。⽽
Qcache_not_cached中包含有数据频繁变化⽽导致查询缓存失效的select语句，因此命中率⼀般来说较低。如果抛开失效的因
素，查询缓存的命中率 = Qcache_hits / (Qcache_hits + Qcache_inserts)如果使⽤这种公式计算出查询缓存的命中率⽐较⾼的话，这就意味着⼤部分select 语句都命中了查询缓存。
通过如下命令查看当前系统⼀共执⾏了多少条select语句
mysql> show status like 'Com_select';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select | 1 |
+---------------+-------+
2、结果集缓存
结果集缓存是会话缓存，MySQL客户机成功连接服务器之后。MySQL服务器会为每个MySQL客户机保留结果集缓
存。缓存MySQL客户机连接线程的连接信息以及缓存返回MySQL客户机的结果集信息，当MySQL客户机向服务器发送select 语句时，MySQL将select语句的执⾏结果暂存在结果集缓存中。结果集的缓存⼤
⼩由net_buffer_length参数值定义:
mysql> show variables like 'net_buffer_length';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| net_buffer_length | 16384 |
+-------------------+-------+
如果结果集超过net_buffer_length设置的值，则⾃动扩充容量，但不超过：max_allowd_packet的阈限值：
mysql> show variables like 'max_allowed_packet';
+--------------------+---------+
| Variable_name | Value |
+--------------------+---------+
| max_allowed_packet | 4194304 |
+--------------------+---------+
3、排序缓存
MySQL 常⽤的有InnoDB 和MyISAM 两种数据存储引擎。因此在优化的时候，每种引擎都会采⽤适合⾃⼰引擎的优化⽅法。关于MySQL 与InnoDB 表结构⽂件和数据⽇志⽂件的不同，可以先看本⼈的博客，以便对这些基础概念有⾜够的了解，接下来看引擎的优化的⽅法才能如鱼得⽔，不觉得枯燥。
1、普通排序缓存
排序缓存是会话缓存, 如果客户机向服务端发送的SQL语句中含有设计排序的order by 或者group by ⼦句。MySQL就会选择相应的排序算法，在普通排序索引上进⾏排序，提升排序速度。普通排序索引的⼤⼩由sort_buffer_size参数定义，如果要提升排序的速度，⾸先应该添加合适的索引，此后则应该增⼤排序索引缓存sort_buffer_size.
mysql> select @@global.sort_buffer_size / 1024;
+----------------------------------+
| @@global.sort_buffer_size / 1024 |
+----------------------------------+
| 256.0000 |
+----------------------------------+
1 row in set (0.00 sec)
接下来我们来看下与排序缓存相关的参数有哪些：
mysql> show variables like '%sort%';
+--------------------------------+---------------------+
| Variable_name | Value |
+--------------------------------+---------------------+
| innodb_disable_sort_file_cache | OFF |
| innodb_ft_sort_pll_degree | 2 |
| innodb_sort_buffer_size | 1048576 |
| max_length_for_sort_data | 1024 |
| max_sort_length | 1024 |
| myisam_max_sort_file_size | 9223372036853727232 |
| myisam_sort_buffer_size | 8388608 |
| sort_buffer_size | 262144 |
+--------------------------------+---------------------+
mysql> show status like '%sort%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Sort_merge_passes | 0 |
| Sort_range | 0 |
| Sort_rows | 0 |
| Sort_scan | 0 |
+-------------------+-------+
max_length_for_sort_data
默认⼤⼩为1024字节，对每⼀列的进⾏排序操作是，如果该列的值长度较长，通过增加该参数来提升MySQL性能。
max_sort_length
order by 或者 group by 的时候使⽤该列的前max_sort_length字节进⾏排序，排序操作完成后，会将此次排序的信息记录到本次会话的状态⾥。
Sort_merge_passes
使⽤临时⽂件完成排序操作的次数。MySQL在进⾏排序操作时，⾸先尝试在普通排序缓存中完成排序。如果缓存空间不够⽤，MySQL将利⽤缓存进⾏多次排序。并把每次的排序结果存放到临时⽂件中，最后再把临时⽂件中的数据做⼀次排序。Sort_merge_passes值就是记录了使⽤⽂件进⾏排序的次数。由于⽂件排序要牵涉到读⽂件，打开⽂件句柄，然后关闭⽂件等操作。所以读取⽂件的系统消耗⽐较⼤，通过增⼤普通排序缓存sort_buffer_size来减少使⽤临时⽂件排序的次数，从⽽增加排序的性能。
Sort_range
使⽤范围排序的次数
Sort_rows
已经排序的记录⾏数
Sort_scan
通过全表扫描完成排序的次数
2、MyISAM排序缓存
当我们使⽤alter table 语句或者create index 语句创建MyISAM表的索引，或者导⼊⼀部分数据使⽤load data infile path，这些操作都会导致索引被重建，重建索引时需要对索引字段进⾏排序操作，为了加快重建索引的效率，MyISAM提供了排序缓存⽤于实现索引的排序⼯作，这些⽅法都是尽量是排序的⼯作在内存中完成。MyISAM排序缓存的⼤⼩由myisam_sort_buffer_size定义。索引重建之后，该缓存⽴马释放。
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论