超级详细的MySQL优化（一）

一、MySql架构分析

        1.MySql发展史

        2.MySql分支

           2.1PerconaServer-XtraDB

              PerconaServer是MySQL重要的分支之一，它基于InnoDB存储引擎的基础上，提升了性能和易管理性，最后形成了增强版的XtraDB引擎，可以用来更好地发挥服务器硬件上的性能。所以PerconaServer也可以称为增强的MySQL与开源的插件（plugin）的结合。

           2.2MariaDBMariadb

                MariaDBMariadb是由MySQL创始人Monty创建的，是一款高度兼容的MySQL产品，主要由开源社区维护，采用GPL授权许可。Oracle把MySQL收购之后，为避免MySQL在开源粒度上的下降，MariaDB由此而生。

            2.3InnoSQLInnoSQL是网易杭州研究院维护的MySQL分支。

        3.MySQL的连接

           3.1MySQL连接介绍

              3.1.1通信类型

                 3.1.1.1同步通信同步通信依赖于被调用方，受限于被调用方的性能。也就是说，应用操作数据库，线程会阻塞，等待数据库的返回。

                 3.1.1.2异步通信异步可以避免应用阻塞等待，但是不能节省SQL执行的时间。如果异步存在并发，每一个SQL的执行都要单独建立一个连接，避免数据混乱。但是这样会给服务端带来巨大的压力（一个连接就会创建一个线程，线程间切换会占用大量CPU资源）。另外异步通信还带来了编码的复杂度，所以一般不建议使用。如果要异步，必须使用连接池，排队从连接池获取连接而不是创建新连接。 

              3.1.2连接方式

                3.1.2.1短连接短连接操作步骤：连接-->数据传输-->关闭连接。

                3.1.2.2长连接长连接操作步骤连接-->数据传输-->保持连接-->数据传输-->保持连接-->…………-->关闭连接

              3.1.3协议

                3.1.3.1TCP/IPTCP/IP套接字连接方式是MySQL在任何平台都提供的一种连接方式，也是网络中使用最多的一种方式。

                3.1.3.2UnixSocketUNIXSocket是连接MySQL需要一个物理文件，文件的存放位置在配置文件中有定义，所以只能在MySQL客户端和数据库实例在同一台服务器上的情况下使用。它所有协议中最高效的一个。

             3.2在服务端中查看连接信息

                3.2.1查看服务端中的连接数showglobalstatuslike'Thread%'

                    

                        Thread_cached：线程缓存中的线程数；

                        Thread_connected：当前打开的连接数；

                        Thread_created：为处理连接而创建的线程数；

                        Thread_running：未休眠的线程数；

                3.2.2优化Threads_created

                  

                        threads_created表示创建过的线程数，很明显，threads_created过大，表明mysql服务器一直在创建线程，这也是比较耗资源，说明服务器不健康。

                    3.2.2.1解决方案配置thread_cache_size：thread_cache_size作用是当客户端断开之后，服务器处理此客户的线程将会缓存起来以响应下一个客户而不是销毁（前提是缓存数未达上限）。MySQL<=5.6.7默认为0，MySQL>=5.6.8默认值为-1（自动调整）。可设置为0-16384。

                        查看thread_cache_size：5showvariableslike'thread_cache_size'设置规则：如果是短连接，适当设置大一点，因为短连接往往需要不停创建，不停销毁，如果大一点，连接线程都处于取用状态，不需要重新创建和销毁，所以对性能肯定是比较大的提升。如果是长连接，可以设置成小一点，一般在50-100左右。

                        根据物理内存设置规则：

                        1G--->8

                        2G--->16

                        3G--->32

                        >= 4G--->64

                        setglobalthread_cache_size=64//重启mysql后失效。

                  3.2.3查看连接超时时间3.2.3.1交互式交互式：是指使用客户端工具连接数据库服务端。如：Navicat、SQLyog等。

                     全局：showglobalvariableslike'interactive_timeout'

                     会话：showsessionvariableslike'interactive_timeout'

                     3.2.3.2非交互式非交互式：是指使用JDBC方式连接数据服务端。

                        全局：showglobalvariableslike'wait_timeout'

                        会话：showsessionvariableslike'wait_timeout'

                     3.2.4优化连接超时时间

                        3.2.4.1交互式

                            全局setglobalinteractive_timeout=600;

                            会话setsessioninteractive_timeout=600;

                        3.2.4.2非交互式

                            全局setglobalwait_timeout=600;

                            会话setsessionwait_timeout=600;

                        3.2.4.3总结wait_timeout的作用是设置非交互连接（就是指那些连接池方式、非客户端方式连接的）的超时时间，默认是28800，就是8小时，超过这个时间，mysql服务器会主动切断那些已经连接的，但是状态是sleep的连接。

                        3.2.5查看最大连接数对客户端而言，MySQL服务端的最大连接数是指服务端允许的最大连接并发数。showvariableslike'max_connections';

                        3.2.6优化最大连接数该参数设置过小的最明显特征是出现”Toomanyconnections”异常。如果服务器的并发连接请求量比较大，建议调高该值，以增加并行连接数量，当然这建立在机器能支撑的情况下，因为如果连接数越多，介于MySQL会为每个连接提供连接缓冲区，就会开销越多的内存，所以要适当调整该值，不能盲目提高设值。

                        通过命令修改setglobalmax_connections=200；

                        通过配置文件修改max_connections=100。

每一发奋努力的背后，必有加倍的赏赐。共勉，大佬们请多指示！