MySQL配置优化

2019-02-16 09:21:12 MySQL

MySQL 优化

MySQL优化总结 - Create by Vien 2016.11.27

自己不断尝试，总结的。有错误请大家指出，谢谢~ 注：下文提到的所有状态值均可以通过SHOW STAUTS LIKE ''查询变量值(配置项值)可以通过SHOW VARIABLES LIKE ''查询

key_buffer_size 只针对MyISAM表起作用，指定索引缓冲区大小，决定索引处理尤其是索引读的速度。通过Key_read_requests和Key_reads两个状态值，合理设置该项。应该使Key_reads/Key_read_requests尽可能小其实也可以通过索引文件总共占有空间来设置这个值，但注意要是MyISAM表的索引文件。如果都是用的MyISAM引擎。可以用root去指定的schema数据库(存放了其他数据库的信息)，也就是use information_schema;然后查询指定数据库索引文件大小:SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024),2),'MB') AS 'INDEX_TOTAL_SIZE' FROM TABLES WHERE table_schema = '数据库名字';

query_cache_size 查询缓存。query_cache_type要设置1.使用查询缓存效率可以提高到238%。可以从以下三个方面判断调整： 1.Qcache_lowmem_prunes很大，并且一直在增长，说明缓存经常不够 2.Qcache_hits很大，说明查询重复查询很多，若命中率Qcache_hits/(Qc*ache_hits+Qcache_inserts)100%很大，通常高于80%，就需要增加查询缓存大小 3.内存使用率(query_cache_size-Qcache_free_memory)/query_cache_size*100%，通常高于80%就需要增加查询缓存大小总结：综合以上三种情况：查询缓存不足次数、命中率、内存使用率，在系统内存允许的情况下，适当调整查询缓存大小。

query_cache_limit 说到query_cache_size不得不提一下这个参数。试想如果一个草鸡炒鸡大的查询记录占了大量内存，而往往小查询记录是最有效的记录集，那么设置该项就很有必要了。默认为1M，尽量设置不要超过默认。

query_cache_min_res_unit 这也是与query_cache_size配套使用的。查询缓存最小结果集单元。设置得当会减少内存块申请分配次数，但是过大又会导致内存碎片数值上升。默认4K，建议1-16K。系统稳定后，可以根据设置最小内存块为(query_cache_size-Qcache_free_memeory)/Qcache_queries_in_cache 很好理解，就是当前在缓存中的查询结果的平均占用内存大小，设置为最小块的大小。建议定期进行碎片整理：FLUSH QUERY CACHE

table_open_cache 所有线程能打开的表的数量。通过检查状态值Open_tables和Opened_tables设定该项大小。如果当前打开表数量Open_tables与打开过表的数量Opened_tables接近，并且Opened_tables还在增长,是时候增加该项了！通常可以设置为以下状态： Open_tables/Opened_tables>=0.85 Open_tables/table_open_cache<=0.95 但也要视系统能力而定，因为对于Linux，通过文件描述符维护一个打开的文件，如果设置过大，超过系统处理文件描述符的能力，就会造成文件描述符不足，从而造成性能不稳定尤其是连接失败的情况。

thread_cache_size 可重用线程数。默认值是8+(max_connections/100)。可以根据线程缓存命中率(1-Thread_created/Connections)*100%优化调整此参数。登陆root，use mysql，show status like ''查看，其中Thread_created表示创建过的线程数，Connections表示试图连接MySQL的次数。创建过的少，而试图连接的多，命中率自然就高。

max_connections "Too many connections"错误，说明max_connections值太小。常常是因为应用程序设计不合理导致交互时间过长，没有正确关闭数据库连接或者并发量较高。如果是前两者的问题可以通过应用程序中的连接池或者MySQL中的线程池有助于解决此问题。使用连接池通常将wait_timeout设置为一个较大的值，系统默认是8h。刚才提到MySQL的线程池设置是thread_cache_size，我认为如果有较好的数据库连接池模型使用thread_cache的意义不大。

back_log MySQL服务连接请求队列所能处理的最大连接请求数，如果队列满了，后续连接才会拒绝。如果短时间内有大量连接，可以适当增大此参数。其实也就是ServerSocket的accept()方法TCP连接监听队列大小问题，如果队列满了，就会阻塞。不过这个大小也跟OS有关。

slow-query-log 记录查询慢的情况，便于找到性能差的查询，进行调优。开启慢查询日志通常搭配以下配置使用： log_queries_not_using_indexs对没使用索引的进行检查 slow_query_log_file=文件路径,设置慢查询日志路径 long_query_time设置慢查询时间，也就是说超过多少秒就被写入日志

sort_buffer_size 可以根据Sort_merge_passes大小设定该项。那么问题来了：BUT WHY?经过老衲一番研究，官方文档也只是说了一句Sort_merge_passes大的时候需要增加sort_buffer_size，并没有说这个状态到底是个鸟东西。其实是这样的：MySQL的外部排序算法用了归并，然后这个值大就说明归并次数多，也就是说缓存小了呗。WHAT？归并是什么？我就想说一句：晚安！

join_buffer_size 通常增加索引是获取最快连接的方法。但是不能增加索引，使连接变快的方式就是增大这个参数。对于没有索引的多表复杂连接，需要多块连接内存。其实全局这个值可以设定较小，然后在会话级别个别调整。默认256K。

read_rnd_buffer_size 随机读缓冲大小。这个参数设置一个较大的值可以提升ORDER BY的性能，等等，那跟sort_buffer_size什么关系？其实MySQL从sort_buffer里取出大量指针，并使用行指针排序，当按照排序好的顺序执行读到read_rnd_buffer的时候，幸运的话，它是非常连续的。还有一点需要注意的是，他跟read_buffer_size(为每个线程对MyISAm表执行顺序读所分配的内存)使用范围不一样，read_buffer_size限定于MyISAM使用，而该项所有引擎都可以用。相同的是这个也是会话级别的，所以全局不应该太大，在大查询会话中增加此参数就行了

max_heap_table_size 用户创建的memeory表允许增长的最大容量。通常与下面的tmp_table_size共同限定内存表大小，如果内存表使用频繁，可以增大这个参数值。

tmp_table_size 内存临时表的最大内存。超出部分会转化为磁盘临时表。过多使用GROUP BY，ORDER BY查询，而且系统内存充足可以考虑增大此参数。别忘记提升ORDER BY性能还有上面说过的sort_buffer_size

华丽丽的分割线下面是比较有(cao)趣(dan)的配置

thread_concurrency 网上好多人说要把这个调成系统核心数乘以2的值，以便于更好的利用CPU。其实官方手册也确实有提到这个，还有一些人说这个只是针对与另一个UNIX-LIKE系统Solaris，对于Linux来说不用理会该项，但有人亲测过在Linux下设置0会报错让调整为1，但是设置1在并发情况下必然阻塞，影响性能。就我看来，whatever，老子用的5.7，早就废除这个参数了，不行你试试看，设置完了，让你MySQL服务起不来信不信。

skip_name_resolve 说好的"默认关闭，MySQL检查客户端连接会解析主机名，开启后MySQL服务只使用IP，提高性能，这种情况下，授权表中的Host字段必须为IP或者localhost。"，然而亲测是只要打开此项就崩盘，无论用本机IP还是127.0.0.1或者localhost都连不上，最坑的是反复尝试了各种情况之后，应用程序就是连不上mysql服务，给我家Tomcat都搞出小情绪了，直接装死，不改回去不肯起来。而且我不小心手贱写了个程序测试了一下开相同数量的connection，域名、IP地址速度几乎是没有差别的。

欢迎小伙伴们在下方评论区留言 ~ O(∩_∩)O
文章对我有帮助, 点此请博主吃包辣条 ~ O(∩_∩)O

AdSense Anaconda Android apache API apt Auth AWS B-tree Bandwagon Blog bower brew bytes Caffe Catalina certbot Charles cloudcone Composer conda CoreML CPU crontab CSS csv Cuda cv2 datetime Digitalocean DNS Docker Docker-Compose Eloquent Excel export Flask FTP GET Git GitHub GitLab Gmail GoDaddy Google GTM hash Homebrew Homestead HTML http HTTPS IDEA image imagemagick imagick imgick import InnoDB ios iou iPhone ISO8601 iTerm2 Java JavaScript JPG JS Keras Laravel Laravel-Admin lazyload Linux list Livewire lnmp load logs Lravel Mac Markdown matplotlib md5 mix MobileNet Mojave mongo MongoDB MySQL Namesilo Nginx Node npm numpy Nvidia Nvidia-Docker onevps OpenCV Openpose openpyxl oss Outline parse PayPal PHP php-fpm PhpStorm PHP扩展 PIL Pillow pip PNG POST Protobuf PyCharm pyenv pymongo Python Python,人工智能,机器学习,VOC,xml Queue Redis requests RGB Sanctum save selenium SEO Shadowsock Shadowsocks ShadowsocksR simplemde Spring Boot SQLServer ssd SSH ssl SSL证书 SSR str Sublime sudo swap Swift Tensorflow TensorflowLite Terminal Terminator timestamp Ubuntu urllib UTC v2ray Valet Validation Validator VienBlog virtualenvs VPN VPS Vultr Web Windows WordPress Xcode xlsx yaml YAPI YUV zip zmq zsh 上网下载图片主从同步云主机云存储云开发云服务器人工智能优化优惠码伪原创作弊与反作弊免费ss账号免费提现切片前端加密协议博客友链双击事件后台运行后端命令国内镜像源图标图片操作图片转换域名多身份认证大小写转换姿态检测安卓模拟器安装定时任务定时执行密码密钥导出导入小程序码延迟加载异常微信微信小程序快捷方式慢查询懒加载提现搜索引擎搬瓦工搭梯子教程数据库数据重复文件上传无法登录日志日期时区时间时间戳服务器机器学习权限梯子模拟浏览器港版支付宝漏洞爬虫生活服务用户管理病毒登录目标检测科学上网系统升级索引组件组件开发编辑器自动付款自定义组件英文伪原创计划任务计算机视觉订阅通知认证语法读写分离远程连接配置文件重定向错误异常错误提示队列阿里云香港香港手机号

亲情非友情链接

MySQL配置优化

MySQL优化总结 - Create by Vien 2016.11.27

猜你喜欢

评论

未登录

标签