江苏龙网

  1. 主页 > 生活百科 > >

Mysql优化及问题定位，看这一篇就够了

MySQL优化

前言我最近由于换工作，博客更新暂缓，后面争取一周两篇 。
MySQL 系列到这里就差不多了，Mysql集群、分库分表及分布式事务由于我还是停留在理论上，没在生产环境上玩过，又怕写不好，这部分内容我会在有底气的一天补上来 。
下一个系列，想写 JAVA 相关的，java 虚拟机及问题定位，java 并发，java 源码等等 。
本文内容

  • explain 查看执行计划
  • show profile 定位问题
  • 硬件的选择及mysql 使用内存估计
Mysql 单机扛不住的时候，考虑读写分离，主库用于写，从库用于查 。主要还是为了减小 insert/update/delete 锁开销降低了数据库的并发 。
当业务量级真的达到需要分库分表的时候，数据库上云吧 。上云的花费对于业务盈利来说估计也就是九牛一毛了 。
数据库上云之后，运维也比较方便了 。
Cpu/内存/硬盘选择内存你如果给 Mysql 配置的内存较高，将其当成一个内存数据库使用（索引数据和业务数据都在内存中），那么其性能一定不会差 。
内存较大的服务器价格不菲，我们要选择合适的内存大小 。
一般我们倾向于将索引数据和一部分访问频率比较频繁的热数据放入到内存中就可以了，但是还是要预留出来一部分内存，防止发生 swap 降低性能 。当下图中的 swap 中 si 和 so 为 0 就行了 。代表系统没有发生 swap 。当你内存较小的时候发生 swap 对性能影响是不小的 。
 vmstat -t 1 1000
Mysql优化及问题定位，看这一篇就够了

文章插图
 
再不发生 swap 的前提下，一般推荐将系统内存的 80% 的内存分配给 mysql 使用 。
Mysql优化及问题定位，看这一篇就够了

文章插图
 
 图片来自 《MySQL数据库频繁出现OOM问题该如何化解》 https://www.huaweicloud.com/zhishi/19122601.html // 计算 mysql 内存数值大小 https://www.mysqlcalculator.com/1、innodb_buffer_pool_size
实际中主要关心的还是 innodb_buffer_pool_size （主要用于缓存业务数据和索引数据）配置，以下是一些参考设置 。
典型值为5-6GB（8GB RAM），20-25GB（32GB RAM），100-120GB（128GB RAM） 。
2、key_buffer_size 默认 8M
 show global status like 'key_read%'; Key_read_requests:0 Key_reads:0key_cache_miss_rate = Key_reads / Key_read_requests * 100%;
key_cache_miss_rate 在0.1%以下都很好(每1000个请求有一个直接读硬盘)
3、max_connections 最大连接数默认是 151。
一般我们都是使用线程池，这个值也不太需要调多大，当你 mysql 实例上有很多个数据库供多个项目使用的时候需要调整这个值 。
4、read_buffer_size 默认 128 KB
内存足够大的时候，推荐设置为 1M，这样读取扫描表数据的时候会更快 。但也不是越大越好 。
read_rnd_buffer_size 默认 256 KB
sort_buffer_size 默认 256 KB
join_buffer_size 默认 256 KB
硬盘数据库的瓶颈主要还是磁盘 io 这一块，SSD 性能相对来说会更好一些 。
Mysql 数据的文件还是需要放到 SSD 上的 。
当你定时备份数据库数据的时候，可以将备份的数据压缩发送到另一个存储型服务器 。
Cpu当 cpu 总是 100% 的时候你就需要考虑增加 cpu 的核数了 。
一般我们选择 4 核 8g内存，8 核 16g，16 核 64g ，32 核 128 g 。
查看数据库中数据和索引大小information_schema.TABLES 保存数据了数据表中的数据大小和索引大小 。
 SELECT ts.TABLE_SCHEMA AS '数据库', CONCAT( ROUND( SUM( ts.DATA_LENGTH / 1024 / 1024 ), 2 ), 'MB' ) AS '总数据大小', CONCAT( ROUND( SUM( ts.index_length / 1024 / 1024 ), 2 ), 'MB' ) AS '索引数据大小', CONCAT( ROUND( SUM( ( ts.index_length + ts.DATA_LENGTH ) / 1024 / 1024 ), 2 ), 'MB' ) AS '索引数据大小'  FROM information_schema.TABLES AS ts  WHERE ts.TABLE_SCHEMA NOT IN ( 'mysql', 'information_schema', 'performance_schema' )  GROUP BY ts.TABLE_SCHEMA;
Mysql优化及问题定位，看这一篇就够了

文章插图
 
内存的容量小于索引数据的时候，需要考虑增加内存容量 。
定位慢 sql1、druid 连接池也是可以打印慢 sql 。一般执行时间长于 1s 的都要优化 。
 spring: datasource: druid: filter: stat:         enabled: true           # 执行时间小于 1 秒记录为慢 sql         slow-sql-millis: 1000         log-slow-sql: true         db-type: mysql         merge-sql: true


推荐阅读


上一篇：在罗布泊发现了一处氏族社会的墓葬 在新疆罗布泊失踪的科学家

下一篇：尼安德特人是人类吗 人类的祖先是智人还是尼安德特人