MySQL优化方向&思路

陈银山

　　硬件级别
　　操作系统和硬件级别的优化着眼点：
　　1、对于CPU密集型的应用场景要使用更快速度的CPU甚至更多数量的CPU，为有着更多查询的场景使用更多的CPU等。基于多核以及超线程(hyperthreading)技术，现代的CPU架构越来越复杂、性能也越来越强了，但MySQL对多CPU架构的并行计算能力的利用仍然是有着不太尽如人意之处，尤其是较老的版本如MySQL 5.1之前的版本甚至无法发挥多CPU的优势。不过，通常需要实现的CPU性能提升目标有两类：低迟延和高吞吐量。低延迟需要更快速度的CPU，因为单个查询只能使用一颗；而需要同时运行许多查询的场景，多CPU更能提供更好的吞吐能力，然而其能否奏效还依赖于实际工作场景，因为MySQL尚不能高效的运行于多CPU，并且其对CPU数量的支持也有着限制。一般来说，较新的版本可以支持16至24颗CPU甚至更多。
　　2、是否有着合适大小的物理内存，并通过合理的配置平衡内存和磁盘资源，降低甚至避免磁盘I/O。现代的程序设计为提高性能通常都会基于局部性原理使用到缓存技术，这对于频繁操作数据的数据库系统来说尤其如此——有着良好设计的数据库缓存通常比针对通用任务的操作系统的缓存效率更高。缓存可以有效地延迟写入、优化写入，但并能消除写入，并综合考虑存储空间的可扩展性等，为业务选择合理的外部存储设备也是非常重要的工作。
　　3、是否选择了合适的网络设备并正确地配置了网络对整体系统系统也有着重大影响。延迟和带宽是网络连接的限制性因素，而常见的网络问题如丢包等，即是很小的丢包率也会赞成性能的显著下降。而更重要的还有按需调整系统中关网络方面的设置，以高效处理大量的连接和小查询。
　　4、是否基于操作系统选择了适用的文件系统。实际测试表明大部分文件系统的性能都非常接近，因此，为了性能而苦选文件系统并不划算。但考虑到文件系统的修复能力，应该使用日志文件系统如ext3、ext4、XFS等。同时，关闭文件系统的某些特性如访问时间和预读行为，并选择合理的磁盘调度器通常都会给性能提升带来帮助。
　　5、MySQL为响应每个用户连接使用一个单独的线程，再加内部使用的线程、特殊目的线程以及其它任何由存储引擎创建的线程等，MySQL需要对这些大量线程进行有效管理。Linux系统上的NPTL线程库更为轻量级也更有效率。MySQL 5.5引入了线程池插件，但其效用尚不明朗。
　　来源：网络
　　OS级别
　　优化脚本（针对Centos 6.x）
#!/bin/bash　　
# Authotby:Tommy.Gandolf
　　
#
　　
# Add SwapFile
　　
Mem=$(free -m |awk '/Mem:/{print $2}')
　　
Swap=$(free -m |awk '/Swap:/{print $2}')
　　
if ["$Swap" == 0 ]; then
　　
        if [ $Mem -le 1024 ]; then
　　
                dd if=/dev/zero of=/swapfilecount=1024 bs=1M
　　
                mkswap /swapfile
　　
                swapon /swapfile
　　
                chmod 600 /swapfile
　　
        elif [ $Mem -gt 1024 -a $Mem -le 2048]; then
　　
                dd if=/dev/zero of=/swapfilecount=2048 bs=1M
　　
                mkswap /swapfile
　　
                swapon /swapfile
　　
                chmod 600 /swapfile
　　
        fi
　　
cat >>/etc/fstab >/etc/security/limits.conf
　　
echo  'ulimit -SHn 65535' >> /etc/rc.local
　　
echo 'ulimit  -s 65535' >> /etc/rc.local
　　

　　
# Update systemtime_zone
　　
yum install lrzzsntpdate sysstat -y
　　
echo '*/5 * * * *root /usr/sbin/ntpdate time.windows.com > /dev/null 2>&1' >>/var/spool/cron/root
　　
echo '*/10 * * * *root /usr/sbin/ntpdate time.nist.gov > /dev/null 2>&1' >>/var/spool/cron/root
　　

　　
# Cchange defaultsshd port
　　
sed -i '/#Port22/s/#Port 22/Port 65535/' /etc/ssh/sshd_config
　　
#sed -i '/#PermitRootLoginyes/s/#PermitRootLogin yes/PermitRootLogin no/' /etc/ssh/sshd_config
　　
sed -i'/#PermitEmptyPasswords no/s/#PermitEmptyPasswords no/PermitEmptyPasswords no/'/etc/ssh/sshd_config
　　
sed -i '/#UseDNSyes/s/#UseDNS yes/UseDNS no/' /etc/ssh/sshd_config
　　
service sshdrestart
　　

　　
# User add sudopower
　　
/usr/sbin/useradd adminroot
　　
echo'1q2w3e4r' | passwd --stdin adminroot  && history -c
　　
sed -i'108a\leerwjamesmengroot   ALL=(ALL)       NOPASSWD: ALL'/etc/sudoers
　　
# Empty iptablesrules
　　
echo '*/5 * * * *root /etc/init.d/iptables  stop' >>/etc/crontab
　　
/etc/init.d/crond  restart
　　
iptables -F -t nat
　　
iptables -X
　　
iptables -P INPUTDROP
　　
iptables -P OUTPUTACCEPT
　　
iptables -PFORWARD ACCEPT
　　

　　
modprobeiptable_nat
　　
modprobe ip_conntrack_ftp
　　
modprobeip_nat_ftp
　　

　　
iptables -A INPUT-i lo -j ACCEPT
　　
iptables -A OUTPUT-o lo -j ACCEPT
　　
iptables -A INPUT-m state --state ESTABLISHED,RELATED -j ACCEPT
　　

　　
iptables -A INPUT-m state --state NEW -m tcp -p tcp --dport 22 -j ACCEPT
　　
iptables -A INPUT-m state --state NEW -m tcp -p tcp --dport 80 -j ACCEPT
　　
iptables -A INPUT-m state --state NEW -m tcp -p tcp --dport 65535 -j ACCEPT
　　
iptables -Nsynflood
　　
iptables -Asynflood -m limit --limit 10/s --limit-burst 100 -j RETURN
　　
iptables -Asynflood -p tcp -j REJECT --reject-with tcp-reset
　　
iptables -A INPUT-p tcp -m state --state NEW -j synflood
　　
iptables -A INPUT-p icmp --icmp-type echo-request -m limit --limit 1/s --limit-burst 5 -j ACCEPT
　　
iptables -A INPUT-p icmp --icmp-type echo-request -j DROP
　　
service iptablessave
　　
service iptablesrestart
　　

　　
# Lock file systemimpotant
　　
chattr +i/etc/passwd
　　
chattr +i/etc/inittab
　　
chattr +i/etc/group
　　
chattr +i/etc/shadow
　　
chattr +i/etc/gshadow
　　
# Rename chattrword
　　
mv /usr/bin/chattr/usr/bin/mvchattr
　　
# Hide systemversion information
　　
:>/etc/redhat-release
　　
:> /etc/issue
　　
# Optimize thekernel parameters
　　
cat >>/etc/sysctl.conf  /etc/ld.so.conf.d/local.conf
　　
ldconfig  -v > /dev/null 2>&1
　　
groupadd  -r mysql
　　
useradd  -g mysql -r -s /sbin/nologin  -M mysql
　　
tar zxvfmysql-5.5.43.tar.gz
　　
cd mysql-5.5.43
　　
cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql\
　　
-DMYSQL_DATADIR=/data/mydata\
　　
-DSYSCONFDIR=/etc\
　　
-DWITH_INNOBASE_STORAGE_ENGINE=1\
　　
-DWITH_PARTITION_STORAGE_ENGINE=1\
　　
-DWITH_FEDERATED_STORAGE_ENGINE=1\
　　
-DWITH_BLACKHOLE_STORAGE_ENGINE=1\
　　
-DWITH_MYISAM_STORAGE_ENGINE=1\
　　
-DWITH_ARCHIVE_STORAGE_ENGINE=1\
　　
-DWITH_READLINE=1\
　　
-DENABLED_LOCAL_INFILE=1\
　　
-DENABLE_DTRACE=0\
　　
-DDEFAULT_CHARSET=utf8\
　　
-DDEFAULT_COLLATION=utf8_general_ci\
　　
-DWITH_EMBEDDED_SERVER=1\
　　
-DCMAKE_EXE_LINKER_FLAGS='-ljemalloc' \
　　
-DWITH_SAFEMALLOC=OFF
　　
make -j `grepprocessor /proc/cpuinfo | wc -l`
　　
make install
　　

　　
sed -i's@executing mysqld_safe@executing mysqld_safe\nexportLD_PRELOAD=/usr/local/lib/libjemalloc.so@' $MYSQL_INSTALL_DIR/bin/mysqld_safe
　　
# lsof -n | grepjemalloc
　　
　　配置优化
cat >/etc/my.cnf = 95%
　　这里的值分别计算为：87% 和76%
6.进程使用情况
Show global statuslike ‘thread%’；

　　如果我们在mysql服务器配置文件中设置了thread_cache_size，当客户端断开之后，服务器处理此客户的线程将会缓存起来以响应下一个客 户而不是销毁（前提是缓存数未达上限）。threads_created表示创建过的线程数，如果发现threads_created值过大的话，表明mysql服务器一直在创建线程，这也是比较耗资源，可以适当增加配置文件中thread_cache_size值，查询服务器 thread_cache_size配置：
Show variableslike ‘thread_cache_size’；

7.查询缓存（query cache）
Show global statuslike ‘qcache%’;

　　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：缓存中块的数量。
　　在查询下服务器关于query_cache的配置
show  variables like 'query_cache%';

　　各字段的解释：
　　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%
　　示例服务器 查询缓存碎片率 ＝ 1.8％，查询缓存利用率 ＝ 0.6％，查询缓存命中率 ＝ 20.6％，命中率很差，可能写操作比较频繁吧，而且可能有些碎片
8.排序使用情况
Show global statuslike ‘sort%’；　　sort_merge_passes 包括两步。mysql 首先会尝试在内存中做排序，使用的内存大小由系统变量 sort_buffer_size 决定，如果它的大小不够把所有的记录都读到内存中，mysql 就会把每次在内存中排序的结果存到临时文件中，等 mysql 找到所有记录之后，再把临时文件中的记录做一次排序。这再次排序就会增加 sort_merge_passes。实际上，mysql 会用另一个临时文件来存再次排序的结果，所以通常会看到 sort_merge_passes 增加的数值是建临时文件数的两倍。因为用到了临时文件，所以速度可能会比较慢，增加 sort_buffer_size 会减少 sort_merge_passes 和 创建临时文件的次数。但盲目的增加 sort_buffer_size 并不一定能提高速度
9.文件打开数
Show globalstatus  like ‘open_files%’；

show  variables like 'open_files_limit';

　　比较合适的设置：open_files/ open_files_limit * 100%  5000，最好采用innodb引擎，因为innodb是行锁而myisam是表锁，对于高并发写入的应用innodb效果会好些。示例中的服务器 table_locks_immediate / table_locks_waited ＝ 235，myisam就足够了。
11.表扫描情况
Show global statuslike ‘handler_read%’；

　　Handler_read_first此选项表明SQL是在做一个全索引扫描，注意是全部，而不是部分，所以说如果存在WHERE语句，这个选项是不会变的。如果这个选项的数值很大，既是好事 也是坏事。说它好是因为毕竟查询是在索引里完成的，而不是数据文件里，说它坏是因为大数据量时，简便是索引文件，做一次完整的扫描也是很费时的

　　Handler_read_key此选项数值如果很高，那么恭喜你，你的系统高效的使用了索引，一切运转良好

　　Handler_read_next此选项表明在进行索引扫描时，按照索引从数据文件里取数据的次数
　　Handler_read_prev此选项表明在进行索引扫描时，按照索引倒序从数据文件里取数据的次数，一般就是ORDER BY ... DESC
　　Handler_read_rnd简单的说，就是查询直接操作了数据文件，很多时候表现为没有使用索引或者文件排序
　　Handler_read_rnd_next此选项表明在进行数据文件扫描时，从数据文件里取数据的次数
　　说到判断查询方式优劣这个问题，就再顺便提提show profile语法，在新版MySQL里提供了这个功能：
　　Set profiling=on;
　　Show profile;

Show profiles;

　　调出服务器完成的查询请求次数：
Show globalstatus like ‘com_select’;

　　计算表扫描率：
　　表扫描率 ＝ handler_read_rnd_next / com_select
　　如果表扫描率超过4000，说明进行了太多表扫描，很有可能索引没有建好，增加read_buffer_size值会有一些好处，但最好不要超过8mb
　　END