mysql优化及案例分析
2025-09-17 10:29:10 0 举报《MySQL优化及案例分析》是一本专注于提升MySQL数据库性能的专业书籍。本书详细探讨了优化MySQL数据库的核心技术和策略，并通过真实案例深入分析优化的实际应用，帮助读者掌握在实际工作中的应对方法。书中不仅包含了索引优化、查询优化等基础知识点，还涵盖了配置调整、硬件选择、架构优化等高级话题。除此之外，本书通过实际优化案例，让读者在理解理论的同时，更能掌握解决实际问题的能力。形式多样的修饰语，如“深入”、“全面”、“实用”，以及“先进”，贯穿全书，突出展示了其内容的详实与先进性。全书旨在为数据库管理员和系统架构师提供一份详尽的参考资料，帮助他们高效地管理和优化MySQL数据库环境。
作者其他创作
大纲/内容
Mysql 优化
Buffer pool 优化
设置多个Buffer pool 提高并发性能
一般来说来说默认规则如果Mysql分配的没存小于1G、那么最多可以分配一个Buffer pool
如果内存很大比喻8G可以分配多个Buffer pool
innodb_buffer_pool_seze=8589934592
innodb_buffer_pool_instance=4
线上环境buffer pool 大小设置
总大小：一般设置为服务器内存的50%-60左右
buffer pool总大小 = （chunk * buffer pool 数量 ）的倍数
Buffer pool 动态扩容chunk 机制
buffer pool 的动态扩容机制的实现是将buffer pool 的内存划分一个个chunk(默认大小128M)块
一个buffer pool 中所有的chunk块共享一套 free、 flush、lru 链
动态扩容是指需要从新划分chunk块分配给buffer pool 就可以了
索引设计原则
索引设计尽量覆盖条件语句后的字段
where
group by
order by
索引字段尽量选择基数（值的种类比较多的）大的数据
组合索引不要设计太多一般一张表两三个基本可以覆盖所有的查询语句
索引使用要符合左前原则
对于一些大值字段建立索引时建议建立定长索引
索引字段不要出现套函数情况
索引字段特别是主键一定要是自增的
在组合索引设计时一定要把范围查找的字段放在在最后，应为在SQL里一旦你的一个字段做范围查询使用到索引，那么这这个字段接下来的条件都不能使用索引、这就是规则
案列分析
too many connections
操作系统文件句柄数太小
导致mysql 设置max_connections没有达到此值
修改文件句柄数量
ulimit -HSn 65535
查看是否修改
cat /etc/security/limits.conf
cat /etc/rc.local
线上数据库随机某名奇妙的随机性能抖动
原因
此时buffer pool 内存写满了，需要进行数据flush刷到磁盘、刷磁盘的速度太慢导致执行SQL变慢
磁盘上所有redo log日志文件都写满了，需要从头复写第一个日志文件、此时可能涉及到第一个redo log 中有很多更新操作的改动了缓存页、此时缓存页还没有flush到磁盘，此时就必须把缓存也flush到磁盘才能继续执行更新语句。
解决方案
尽量减少缓存页flush到磁盘的频率
控制频率很难控制，除非使用超大内存的服务器
尽量提升缓存页flush到磁盘的速度
采用SSD固态硬盘（SSD固态硬盘有个有点随机IO的速率非常高）
可以使用fio工具来来测试SSD的随机IO的速率
还需要设置一些参数来配合SSD使用
innodb_io_capacity 这个参数告诉数据库使用多大IO速率把缓存页flush到磁盘上
实际上flush的使用会按照innodb_io_capacity乘以一定的百分比来进行刷盘、这个百分比就是脏页的百分比
脏页的百分比使用 innodb_max_dirty_pages_pct来控制的默认是75%这个一般不用动它
innodb_flush_enighbors
也是就是会在flush缓存页的时候会把临近的缓存页也一起flush到磁盘上去、这样会导致一次flush到磁盘上的缓存页太多
如果我们使用的是SSD的话可以将其设置为0 ，禁止flush临近的缓存页
0 条评论
下一页
为你推荐
查看更多
