我对MySQL的小看法小经验（一）

linuxx

　　谨把此文送给我的亲徒弟王亚东，和郭立强同学（之前一直答应跟强子说说MySQL）。
　　首先，我要正式开始在51CTO上浪了，请都别拦我！
　　然后，我从业时间短，经验不是很丰富，大家随时来喷，纯技术的争论我永远欢迎！
　　最后，我是一个具有开源精神的人，也是一个有情怀的工程师（初级初级），大家多交流。
　　ok，进入正文
　　openstack的技术很成熟了，虚拟化的技术很成熟了，中国有阿里云为扛把子的大片Iaas提供商，国际上有AWS这样的Iaas巨头，稳的很，他们提供的RDS可以说是大大的方便了我们运维人的工作，运维人员不需要再手动的人肉的原始的去处理硬盘内存CPU的故障，可以不用再去IDC忍受辐射的折磨，不需要再考虑我的高可用我的参数怎样优化，但是我们还是会面对企业大客户的私有云，这里都是服务器，这里没有RDS，届时我们如果需要MySQL怎么办呢？你说我可以docker run一个，ok完全没有问题，docker run 一个 -v出来一个卷，持久化也就有了，我们现在的生产也在用，包括我们的备份已经不是mysqldump，而是直接docker run一个备份容器，dockerhub上多的很，mongodb的什么的都有，只有你想不到没有docker做不到，所以我很喜欢容器技术，但是今天我还是要复古一把说一下，在物理服务器上搭建完mysql后的做法，这些经验有的我是看别的大神的，也有一些是自己真正遇到的，就当是我的个人总结篇吧！
　　高可用当然就不必说了，数据库吗，当然要做高可用，不然你就只能从删库到跑路了。高可用的方法也有很多，不一一赘述，之后可能会说，今天主要是说一下，mysql的优化。
　　一，数据库服务器的硬件优化
　　内存 cpu 不必说，在老板同意，经济条件允许的情况是自然是越多越好越大越好。
　　磁盘使用SSD，并作Raid1+0，同样经济条件允许哈，选一个好的raid卡还是很有必要的。
　　二，my.cnf配置文件
　　1.socket=/tmp/mysql.socket 安全套接字文件，这个是unix下特有的，用户可以不通过TCP/IP，而是  直接使用socket连接MySQL。
　　2.skip-locking老版本哈，（新版的skip-external-locking），跳过外部锁定，如果你有多台服务  器使用同一个数据库目录，那么每台服务器都必须开启external-locking。当外部锁定（external-  locking）起作用时，每个进程若要访问数据表，则必须等待之前的进程完成操作并解除锁定。由于  服务器访问数据表时经常需要等待解锁，因此在单服务器环境下external locking会让MySQL性能下  降。默认的话skip-external-locking这条参数是开启状态，如果多服务器情况下希望打开这个锁定  功能可以在skip-external-locking加#注释掉，单服务器的情况下skip-external-locking加上就好  了。
　　3.skip-name-resolve禁止MySQL对外部 连接进行DNS解析，使用这一选项可以消除MySQL进行DNS解析  的时间。但需要注意，如果开启该选项，则所有远程主机连接授权都要使用IP地址方式，否则  MySQL将无法正常处理连接请求。默认情况下当新的连接出现时，MySQL/MariaDB 会进行DNS查询解  析用户的IP地址/主机名。对于每个客户端连接，它的IP都会被解析为主机名。然后，主机名又被反解  析为IP来验证两者是否一致，当 DNS 配置错误或服务器出现问题时，这很可能会导致延迟。这就是为  什么要关闭 DNS 的反向查询的原因我们申请一个AWS的RDS我们发现给我们的就是一个域名并不是IP地  址，所以我觉得AWS没有开启这个参数的。
　　4.back_log 这个是mysql暂时停止响应新的请求之前，短时间内多少个请求可以被存在堆栈之中，如  果短时间内还有很多连接的话，需要增大这个参数的值，但是这样会出现一个情况，你在netstat -  anplt时会看到很多TIME-WAIT状态的连接，这个都是等待的，如果你想要这些请求放到你的堆栈  中，那就不要将你的内核参数/proc/sys/net/ipv4/tcp_tw_recycle调为1（默认就是0），在my.cnf  中这个数字推荐设置为小于512的整数。
　　5.key_buffer_size = 384M 这个参数是指定索引缓冲区大小的，增加它可以根号的索引处理性能。建  议key_buffer设置为物理内存的1/4(针对MyISAM引擎)，甚至是物理内存的30%~40%，当然如果我们  key_buffer_size设置太大，系统就会频繁的换页，降低系统性能。因为MySQL使用操作系统的缓存  来缓存数据，所以我们得为系统留够足够的内存，在很多情况下数据要比索引大得多。如果机器性  能优越，可以设置多个key_buffer,分别让不同的key_buffer来缓存专门的索引，这个是针对新手而  言的比较靠谱的符合客观规律的办法，当熟一些后我们还可以更深入地优化key_buffer_size，使  用show status来查看Key_read_requests,Key_reads,Key_write_requests以及Key_writes以调整到  更适合你的应用的大小，Key_reads/Key_read_requests的大小正常情况下得小于1/100，而且是越  小越好。
　　6.启用InnoDB 的每张表一个数据文件设置InnoDB是一个存储引擎。MySQL和MariaDB使用InnoDB作为默  认存储引擎。以前，MySQL 使用系统表空间来保存数据库中的表和索引。这意味着服务器唯一的目  的就是数据库处理，它们的存储盘不用于其它目的。InnoDB 提供了更灵活的方式，它把每个数据库  的信息保存在一个.ibd数据文件中。每个.idb文件代表它自己的表空间。当删除或截断一个数据库  表时，你可以回收未使用空间。你可以将你的数据库表存在在单独的存储设备上，这样可以大大提  升你的磁盘I/O。MySQL5.6以上默认启用innodb_file_per_table=1。
　　7.在MySQL中避免使用Swappiness，“交换”是一个当系统移动部分内存到一个称为“交换空间”的特  殊磁盘空间时的过程。通常当你的系统用完物理内存后就会出现这种情况，系统将信息写入磁盘而  不是释放一些内存。正如你猜测的磁盘比你的内存要慢得多。这选项默认linux/unix系统是启用的  
　　使用sysctl vm.swappiness查看，使用sudo sysctl -w vm.swappiness=0关闭。
　　8.max_connections 指令告诉你当前你的服务器允许多少并发连接。MySQL/MariaDB 服务器允许有   SUPER 权限的用户在最大连接之外再建立一个连接。只有当执行 MySQL 请求的时候才会建立连接，  执行完成后会关闭连接并被新的连接取代。请记住，太多的连接会导致内存的使用量过高并且会锁  住你的 MySQL 服务器。一般小网站需要 100-200 的连接数，而较大可能需要 500-800 甚至更多。  这里的值很大程度上取决于你 MySQL/MariaDB 的使用情况。我们可以不重启mysql来设置这个值  （如果修改my.cnf文件是需要mysql的）
　　mysql>set global max_connetctons = 300
　　9.thread_cache_size指令用来设置你服务器缓存的线程数量，可以设置为0~16384，默认为0。这个值  表示可以重新利用保存在混村中的线程的数量，当断开连接时如果缓存中还有空间，那么客户端的  线程将被放到内存中；如果线程重新被请求，那么请求将从缓存中读取；如果缓存中是空的或者是  新的请求，那么这个线程将被重新创建；如果有很多新的线程，增加这个值可以改善系统性能。通  过比较Connections和Threads_created状态的变量，可以看到这个变量的作用，我们根据物理内存  设置规则如下：1G内存配成8，2G内存配16，3G内存32，4G以上64+。当客户端断开连接时，如果当  前线程数小于thread_cache_size，它的线程将被放入缓存中。下一个请求通过使用缓存池中的线程  来完成。要提高服务器的性能，你可以设置thread_cache_size的值相对高一些。你可以通过以下方  法来查看线程缓存命中率。
　　mysql> show status like "Threads_created";
　　mysql> show status like "Connections";
　　用以下的公式来计算线程池命中率
　　100 -（（Threads_created/Connections）*100）
　　如果你得到一个较低的数字，这意味着大多数 mysql 连接使用新的线程，而不是从缓存加载。在这  种情况下，你需要增加thread_cache_size的值，当然我们同样不需要修改my.cnf文件重启mysql
　　可以使用：
　　mysql> set global threads_cache_size = 64