锐客网

  1. 首页 > 生活百科 > it技术 > >

mysql的io怎么优化 mysql io优化

IT技术数据

有哪些方法可以改善 MySQL 的 IO 瓶颈问题通过sysbench的oltp_read_write测试来模拟业务压力、以此来给指定的硬件环境配置一份比较合理的MySQL配置文件 。
环境介绍
硬件配置
请点击输入图片描述
软件环境
请点击输入图片描述
优化层级与指导思想
优化层级
MySQL数据库优化可以在多个不同的层级进行，常见的有：
SQL优化
参数优化
架构优化
本文重点关注：参数优化
指导思想
日志先行 -- 一个事务能否成功提交的关键是日志是否成功落盘，与数据没有太大的关系；也就是说对写的优化可以表述为各方面的资源向写操作倾斜 。
瓶颈分析 -- 通过show global status 的各个计数器的值基本上就能分析出当前瓶颈所在，再结合一些简单的系统层面的监控工具如top iostat 就能明确瓶颈 。
整体性能是“读”“写”之间的再平衡 。
Mysql某个表有近千万数据 ， CRUD比较慢，如何优化？ 数据千万级别之多 ， 占用的存储空间也比较大 ， 可想而知它不会存储在一块连续的物理空间上，而是链式存储在多个碎片的物理空间上 。可能对于长字符串的比较，就用更多的时间查找与比较，这就导致用更多的时间 。
可以做表拆分，减少单表字段数量，优化表结构 。
在保证主键有效的情况下 ， 检查主键索引的字段顺序，使得查询语句中条件的字段顺序和主键索引的字段顺序保持一致 。
主要两种拆分 垂直拆分，水平拆分 。
垂直分表
也就是“大表拆小表”，基于列字段进行的 。一般是表中的字段较多，将不常用的 ， 数据较大，长度较长（比如text类型字段）的拆分到“扩展表“ 。一般是针对 那种 几百列的大表，也避免查询时，数据量太大造成的“跨页”问题 。
垂直分库针对的是一个系统中的不同业务进行拆分，比如用户User一个库，商品Product一个库 ， 订单Order一个库 。切分后，要放在多个服务器上，而不是一个服务器上 。为什么？ 我们想象一下，一个购物网站对外提供服务 ， 会有用户，商品，订单等的CRUD 。没拆分之前 ， 全部都是落到单一的库上的 ， 这会让数据库的单库处理能力成为瓶颈 。按垂直分库后，如果还是放在一个数据库服务器上， 随着用户量增大，这会让单个数据库的处理能力成为瓶颈 ， 还有单个服务器的磁盘空间，内存 ， tps等非常吃紧 。所以我们要拆分到多个服务器上，这样上面的问题都解决了，以后也不会面对单机资源问题 。
数据库业务层面的拆分 ， 和服务的“治理”，“降级”机制类似 ， 也能对不同业务的数据分别的进行管理，维护，监控 ， 扩展等 。数据库往往最容易成为应用系统的瓶颈，而数据库本身属于“有状态”的，相对于Web和应用服务器来讲，是比较难实现“横向扩展”的 。数据库的连接资源比较宝贵且单机处理能力也有限，在高并发场景下，垂直分库一定程度上能够突破IO、连接数及单机硬件资源的瓶颈 。
水平分表
针对数据量巨大的单张表（比如订单表），按照某种规则（RANGE,HASH取模等），切分到多张表里面去 。但是这些表还是在同一个库中，所以库级别的数据库操作还是有IO瓶颈 。不建议采用 。
水平分库分表
将单张表的数据切分到多个服务器上去 ， 每个服务器具有相应的库与表 ， 只是表中数据集合不同 。水平分库分表能够有效的缓解单机和单库的性能瓶颈和压力，突破IO、连接数、硬件资源等的瓶颈 。
水平分库分表切分规则
1. RANGE
从0到10000一个表 ， 10001到20000一个表；
2. HASH取模
一个商场系统 ， 一般都是将用户，订单作为主表，然后将和它们相关的作为附表 ， 这样不会造成跨库事务之类的问题 。取用户id，然后hash取模 ， 分配到不同的数据库上 。
3. 地理区域
比如按照华东，华南，华北这样来区分业务，七牛云应该就是如此 。
4. 时间
【mysql的io怎么优化 mysql io优化】 按照时间切分 ， 就是将6个月前 ， 甚至一年前的数据切出去放到另外的一张表，因为随着时间流逝，这些表的数据 被查询的概率变?。悦槐匾汀叭仁荨狈旁谝黄?nbsp;， 这个也是“冷热数据分离” 。
分库分表后面临的问题
事务支持
分库分表后，就成了分布式事务了 。如果依赖数据库本身的分布式事务管理功能去执行事务，将付出高昂的性能代价； 如果由应用程序去协助控制，形成程序逻辑上的事务，又会造成编程方面的负担 。
跨库join
只要是进行切分 ， 跨节点Join的问题是不可避免的 。但是良好的设计和切分却可以减少此类情况的发生 。解决这一问题的普遍做法是分两次查询实现 。在第一次查询的结果集中找出关联数据的id,根据这些id发起第二次请求得到关联数据 。
跨节点的count,order by,group by以及聚合函数问题
这些是一类问题，因为它们都需要基于全部数据集合进行计算 。多数的代理都不会自动处理合并工作 。解决方案：与解决跨节点join问题的类似，分别在各个节点上得到结果后在应用程序端进行合并 。和join不同的是每个结点的查询可以并行执行 ， 因此很多时候它的速度要比单一大表快很多 。但如果结果集很大，对应用程序内存的消耗是一个问题 。
数据迁移，容量规划，扩容等问题
来自淘宝综合业务平台团队，它利用对2的倍数取余具有向前兼容的特性（如对4取余得1的数对2取余也是1）来分配数据，避免了行级别的数据迁移，但是依然需要进行表级别的迁移，同时对扩容规模和分表数量都有限制 。总得来说，这些方案都不是十分的理想，多多少少都存在一些缺点，这也从一个侧面反映出了Sharding扩容的难度 。
ID问题
一旦数据库被切分到多个物理结点上 ， 我们将不能再依赖数据库自身的主键生成机制 。一方面，某个分区数据库自生成的ID无法保证在全局上是唯一的；另一方面 ， 应用程序在插入数据之前需要先获得ID,以便进行SQL路由.
一些常见的主键生成策略
UUID
使用UUID作主键是最简单的方案，但是缺点也是非常明显的 。由于UUID非常的长 ， 除占用大量存储空间外 ， 最主要的问题是在索引上，在建立索引和基于索引进行查询时都存在性能问题 。
Twitter的分布式自增ID算法Snowflake
在分布式系统中 ， 需要生成全局UID的场合还是比较多的，twitter的snowflake解决了这种需求，实现也还是很简单的，除去配置信息，核心代码就是毫秒级时间41位 机器ID 10位 毫秒内序列12位 。
跨分片的排序分页
一般来讲，分页时需要按照指定字段进行排序 。当排序字段就是分片字段的时候 ， 我们通过分片规则可以比较容易定位到指定的分片 ， 而当排序字段非分片字段的时候，情况就会变得比较复杂了 。为了最终结果的准确性，我们需要在不同的分片节点中将数据进行排序并返回 ， 并将不同分片返回的结果集进行汇总和再次排序，最后再返回给用户 。
mysql使用io过高应该怎么优化查找看看那个文件引起的负载过高，然后做优化 。
iostat ,iotop ,pt-ioprofile 或者别的工具
MySQL性能调优 – 你必须了解的15个重要变量前言:
MYSQL 应该是最流行了 WEB 后端数据库 。虽然 NOSQL 最近越来越多的被提到 ， 但是相信大部分架构师还是会选择 MYSQL 来做数据存储 。本文作者总结梳理MySQL性能调优的15个重要变量，又不足需要补充的还望大佬指出 。
1.DEFAULT_STORAGE_ENGINE
如果你已经在用MySQL 5.6或者5.7，并且你的数据表都是InnoDB，那么表示你已经设置好了 。如果没有 ， 确保把你的表转换为InnoDB并且设置default_storage_engine为InnoDB 。
为什么？简而言之，因为InnoDB是MySQL(包括Percona Server和MariaDB)最好的存储引擎 – 它支持事务，高并发，有着非常好的性能表现(当配置正确时) 。这里有详细的版本介绍为什么
2.INNODB_BUFFER_POOL_SIZE
这个是InnoDB最重要变量 。实际上，如果你的主要存储引擎是InnoDB，那么对于你，这个变量对于MySQL是最重要的 。
基本上 ， innodb_buffer_pool_size指定了MySQL应该分配给InnoDB缓冲池多少内存，InnoDB缓冲池用来存储缓存的数据，二级索引，脏数据(已经被更改但没有刷新到硬盘的数据)以及各种内部结构如自适应哈希索引 。
根据经验，在一个独立的MySQL服务器应该分配给MySQL整个机器总内存的80% 。如果你的MySQL运行在一个共享服务器，或者你想知道InnoDB缓冲池大小是否正确设置，详细请看这里 。
3.INNODB_LOG_FILE_SIZE
InnoDB重做日志文件的设置在MySQL社区也叫做事务日志 。直到MySQL 5.6.8事务日志默认值innodb_log_file_size=5M是唯一最大的InnoDB性能杀手 。从MySQL 5.6.8开始，默认值提升到48M,但对于许多稍繁忙的系统，还远远要低 。
根据经验，你应该设置的日志大小能在你服务器繁忙时能存储1-2小时的写入量 。如果不想这么麻烦，那么设置1-2G的大小会让你的性能有一个不错的表现 。这个变量也相当重要，更详细的介绍请看这里 。
当然，如果你有大量的大事务更改，那么，更改比默认innodb日志缓冲大小更大的值会对你的性能有一定的提高，但是你使用的是autocommit，或者你的事务更改小于几k，那还是保持默认的值吧 。
4.INNODB_FLUSH_LOG_AT_TRX_COMMIT
默认下，innodb_flush_log_at_trx_commit设置为1表示InnoDB在每次事务提交后立即刷新同步数据到硬盘 。如果你使用autocommit，那么你的每一个INSERT, UPDATE或DELETE语句都是一个事务提交 。
同步是一个昂贵的操作(特别是当你没有写回缓存时)，因为它涉及对硬盘的实际同步物理写入 。所以如果可能 ， 并不建议使用默认值 。
两个可选的值是0和2:
* 0表示刷新到硬盘，但不同步(提交事务时没有实际的IO操作)
* 2表示不刷新和不同步(也没有实际的IO操作)
所以你如果设置它为0或2，则同步操作每秒执行一次 。所以明显的缺点是你可能会丢失上一秒的提交数据 。具体来说，你的事务已经提交了 ， 但服务器马上断电了，那么你的提交相当于没有发生过 。
显示的，对于金融机构，如银行，这是无法忍受的 。不过对于大多数网站，可以设置为innodb_flush_log_at_trx_commit=0|2，即使服务器最终崩溃也没有什么大问题 。毕竟，仅仅在几年前有许多网站还是用MyISAM，当崩溃时会丢失30s的数据(更不要提那令人抓狂的慢修复进程) 。
那么，0和2之间的实际区别是什么？性能明显的差异是可以忽略不计，因为刷新到操作系统缓存的操作是非常快的 。所以很明显应该设置为0 ， 万一MySQL崩溃(不是整个机器) ， 你不会丢失任何数据，因为数据已经在OS缓存，最终还是会同步到硬盘的 。
5.SYNC_BINLOG
已经有大量的文档写到sync_binlog，以及它和innodb_flush_log_at_trx_commit的关系，下面我们来简单的介绍下：
a) 如果你的服务器没有设置从服务器，而且你不做备份 ， 那么设置sync_binlog=0将对性能有好处 。
b) 如果你有从服务器并且做备份 ， 但你不介意当主服务器崩溃时在二进制日志丢失一些事件 ， 那么为了更好的性能还是设置为sync_binlog=0.
c) 如果你有从服务器并且备份，你非常在意从服务器的一致性 ， 以及能及时恢复到一个时间点(通过使用最新的一致性备份和二进制日志将数据库恢复到特定时间点的能力)，那么你应该设置innodb_flush_log_at_trx_commit=1，并且需要认真考虑使用sync_binlog=1 。
问题是sync_binlog=1代价比较高 – 现在每个事务也要同步一次到硬盘 。你可能会想为什么不把两次同步合并成一次，想法正确 – 新版本的MySQL(5.6和5.7，MariaDB和Percona Server)已经能合并提交，那么在这种情况下sync_binlog=1的操作也不是这么昂贵了，但在旧的mysql版本中仍然会对性能有很大影响 。
6.INNODB_FLUSH_METHOD
将innodb_flush_method设置为O_DIRECT以避免双重缓冲.唯一一种情况你不应该使用O_DIRECT是当你操作系统不支持时 。但如果你运行的是Linux，使用O_DIRECT来激活直接IO 。
不用直接IO ， 双重缓冲将会发生 ， 因为所有的数据库更改首先会写入到OS缓存然后才同步到硬盘 – 所以InnoDB缓冲池和OS缓存会同时持有一份相同的数据 。特别是如果你的缓冲池限制为总内存的50% ， 那意味着在写密集的环境中你可能会浪费高达50%的内存 。如果没有限制为50%，服务器可能由于OS缓存的高压力会使用到swap 。
简单地说，设置为innodb_flush_method=O_DIRECT 。
7.INNODB_BUFFER_POOL_INSTANCES
MySQL 5.5引入了缓冲实例作为减小内部锁争用来提高MySQL吞吐量的手段 。
在5.5版本这个对提升吞吐量帮助很小，然后在MySQL 5.6版本这个提升就非常大了，所以在MySQL5.5中你可能会保守地设置innodb_buffer_pool_instances=4，在MySQL 5.6和5.7中你可以设置为8-16个缓冲池实例 。
你设置后观察会觉得性能提高不大，但在大多数高负载情况下，它应该会有不错的表现 。
对了，不要指望这个设置能减少你单个查询的响应时间 。这个是在高并发负载的服务器上才看得出区别 。比如多个线程同时做许多事情 。
8.INNODB_THREAD_CONCURRENCY
InnoDB有一种方法来控制并行执行的线程数 – 我们称为并发控制机制 。大部分是由innodb_thread_concurrency值来控制的 。如果设置为0，并发控制就关闭了，因此InnoDB会立即处理所有进来的请求(尽可能多的) 。
在你有32CPU核心且只有4个请求时会没什么问题 。不过想像下你只有4CPU核心和32个请求时 – 如果你让32个请求同时处理，你这个自找麻烦 。因为这些32个请求只有4 CPU核心，显然地会比平常慢至少8倍(实际上是大于8倍) ， 而然这些请求每个都有自己的外部和内部锁 ， 这有很大可能堆积请求 。
下面介绍如何更改这个变量，在mysql命令行提示符执行：
对于大多数工作负载和服务器，设置为8是一个好开端，然后你可以根据服务器达到了这个限制而资源使用率利用不足时逐渐增加 。可以通过show engine innodb status\G来查看目前查询处理情况，查找类似如下行：
9.SKIP_NAME_RESOLVE
这一项不得不提及 ， 因为仍然有很多人没有添加这一项 。你应该添加skip_name_resolve来避免连接时DNS解析 。
大多数情况下你更改这个会没有什么感觉 ， 因为大多数情况下DNS服务器解析会非常快 。不过当DNS服务器失败时，它会出现在你服务器上出现“unauthenticated connections” ，而就是为什么所有的请求都突然开始慢下来了 。
所以不要等到这种事情发生才更改 。现在添加这个变量并且避免基于主机名的授权 。
10.INNODB_IO_CAPACITY, INNODB_IO_CAPACITY_MAX
* innodb_io_capacity：用来当刷新脏数据时，控制MySQL每秒执行的写IO量 。
* innodb_io_capacity_max: 在压力下，控制当刷新脏数据时MySQL每秒执行的写IO量
首先，这与读取无关 – SELECT查询执行的操作 。对于读操作 ， MySQL会尽最大可能处理并返回结果 。至于写操作 ， MySQL在后台会循环刷新，在每一个循环会检查有多少数据需要刷新 ， 并且不会用超过innodb_io_capacity指定的数来做刷新操作 。这也包括更改缓冲区合并（在它们刷新到磁盘之前，更改缓冲区是辅助脏页存储的关键） 。
第二，我需要解释一下什么叫“在压力下”，MySQL中称为”紧急情况” ， 是当MySQL在后台刷新时，它需要刷新一些数据为了让新的写操作进来 。然后，MySQL会用到innodb_io_capacity_max 。
那么，应该设置innodb_io_capacity和innodb_io_capacity_max为什么呢？
最好的方法是测量你的存储设置的随机写吞吐量 ， 然后给innodb_io_capacity_max设置为你的设备能达到的最大IOPS 。innodb_io_capacity就设置为它的50-75%，特别是你的系统主要是写操作时 。
通常你可以预测你的系统的IOPS是多少 。例如由8 15k硬盘组成的RAID10能做大约每秒1000随机写操作，所以你可以设置innodb_io_capacity=600和innodb_io_capacity_max=1000 。许多廉价企业SSD可以做4,000-10,000 IOPS等 。
这个值设置得不完美问题不大 。但是，要注意默认的200和400会限制你的写吞吐量 ， 因此你可能偶尔会捕捉到刷新进程 。如果出现这种情况，可能是已经达到你硬盘的写IO吞吐量，或者这个值设置得太小限制了吞吐量 。
11.INNODB_STATS_ON_METADATA
如果你跑的是MySQL 5.6或5.7 ， 你不需要更改innodb_stats_on_metadata的默认值 ， 因为它已经设置正确了 。
不过在MySQL 5.5或5.1 ， 强烈建议关闭这个变量 – 如果是开启，像命令show table status会立即查询INFORMATION_SCHEMA而不是等几秒再执行，这会使用到额外的IO操作 。
从5.1.32版本开始 ， 这个是动态变量，意味着你不需要重启MySQL服务器来关闭它 。
12.INNODB_BUFFER_POOL_DUMP_AT_SHUTDOWNINNODB_BUFFER_POOL_LOAD_AT_STARTUP
innodb_buffer_pool_dump_at_shutdown和innodb_buffer_pool_load_at_startup这两个变量与性能无关，不过如果你偶尔重启mysql服务器(如生效配置) ， 那么就有关 。当两个都激活时，MySQL缓冲池的内容(更具体地说，是缓存页)在停止MySQL时存储到一个文件 。当你下次启动MySQL时 ， 它会在后台启动一个线程来加载缓冲池的内容以提高预热速度到3-5倍 。
两件事：
第一，它实际上没有在关闭时复制缓冲池内容到文件，仅仅是复制表空间ID和页面ID – 足够的信息来定位硬盘上的页面了 。然后它就能以大量的顺序读非常快速的加载那些页面 ， 而不是需要成千上万的小随机读 。
第二，启动时是在后台加载内容，因为MySQL不需要等到缓冲池内容加载完成再开始接受请求(所以看起来不会有什么影响) 。
从MySQL 5.7.7开始，默认只有25%的缓冲池页面在mysql关闭时存储到文件，但是你可以控制这个值 – 使用innodb_buffer_pool_dump_pct ， 建议75-100 。
这个特性从MySQL 5.6才开始支持 。
13.INNODB_ADAPTIVE_HASH_INDEX_PARTS
如果你运行着一个大量SELECT查询的MySQL服务器(并且已经尽可能优化) ， 那么自适应哈希索引将下你的下一个瓶颈 。自适应哈希索引是InnoDB内部维护的动态索引，可以提高最常用的查询模式的性能 。这个特性可以重启服务器关闭，不过默认下在mysql的所有版本开启 。
这个技术非常复杂，在大多数情况下它会对大多数类型的查询直到加速的作用 。不过，当你有太多的查询往数据库，在某一个点上它会花过多的时间等待AHI锁和闩锁 。
如果你的是MySQL 5.7，没有这个问题 – innodb_adaptive_hash_index_parts默认设置为8 ， 所以自适应哈希索引被切割为8个分区，因为不存在全局互斥 。
不过在mysql 5.7前的版本，没有AHI分区数量的控制 。换句话说，有一个全局互斥锁来保护AHI ， 可能导致你的select查询经常撞墙 。
所以如果你运行的是5.1或5.6，并且有大量的select查询，最简单的方案就是切换成同一版本的Percona Server来激活AHI分区 。
14.QUERY_CACHE_TYPE
如果人认为查询缓存效果很好 ， 肯定应该使用它 。好吧，有时候是有用的 。不过这个只在你在低负载时有用，特别是在低负载下大多数是读?。×啃椿蛘呙挥?。
如果是那样的情况，设置query_cache_type=ON和query_cache_size=256M就好了 。不过记住不能把256M设置更高的值了，否则会由于查询缓存失效时，导致引起严重的服务器停顿 。
如果你的MySQL服务器高负载动作 ， 建议设置query_cache_size=0和query_cache_type=OFF，并重启服务器生效 。那样Mysql就会停止在所有的查询使用查询缓存互斥锁 。
15.TABLE_OPEN_CACHE_INSTANCES
从MySQL 5.6.6开始，表缓存能分割到多个分区 。
表缓存用来存放目前已打开表的列表，当每一个表打开或关闭互斥体就被锁定 – 即使这是一个隐式临时表 。使用多个分区绝对减少了潜在的争用 。
从MySQL 5.7.8开始，table_open_cache_instances=16是默认的配置 。
欢迎做Java的工程师朋友们私信我资料免费获取免费的Java架构学习资料（里面有高可用、高并发、高性能及分布式、Jvm性能调优、Spring源码，MyBatis，Netty,Redis,Kafka,Mysql,Zookeeper,Tomcat,Docker,Dubbo,Nginx等多个知识点的架构资料）
其中覆盖了互联网的方方面面，期间碰到各种产品各种场景下的各种问题，很值得大家借鉴和学习，扩展自己的技术广度和知识面 。
MYSQL实战优化——数据页、表空间 经过前面的介绍现在我们都知道 ， 一行一行的数据是存放在数据页里的，所以接下来我们该分析一下数据页的结构了 。之前介绍过 ， 每个数据页，实际上是默认有16kb的大?。敲凑?6kb的大小就是存放大量的数据行吗？明显不是的，其实一个数据页拆分成了很多个部分，大体上来说包含：文件头、数据页头，最小记录和最大记录、多个数据行、空闲空间、数据页目录、文件尾部 。下面我们来看一张图：
简单来说，就是平时我们创建的那些表，其实都有一个表空间的概念，在磁盘上都会对应着“表名.ibd”这样的一个磁盘数据文件 。所以在物理层面，表空间就是对应一些磁盘上的数据文件 。有的表空间，比如系统表空间可能对应的是多个磁盘文件，我们自己创建的表对应的表空间可能就是对应了一个“表名.ibd”数据文件 。
在表空间的磁盘文件里会有很多的数据页 ， 但是如果一个表空间包含了太多数据页的话就不便于管理 ， 所以在表空间里又引入了一个的概念 ， 英文就是extent，一个数据区对应着连续的64个数据页，每个数据页是16kb ， 所以一个数据区是1mb，然后256个数据区被划分为一组 。
对于表空间而言，它的第一组数据区的第一个数据区的前3个数据页都是固定的 ， 里面存放了一些描述性的数据 。比如fsp_hdr这个数据页，它里面就存放了表空间和这一组数据区的一些属性 。ibuf_bitmap数据页，里面存放的是这一组数据页的所有insert buffer的一些信息 。inode数据页，这里也存放了一些特殊信息 。
我们现在先不去具体了解它们是干什么的，只要知道第一组数据区的第一个数据区的前3个数据页，都是存放一些特殊信息的 。然后这个表空间里的其它各组数据区，每一组数据区的第一个数据区的头两个数据页都是存放特殊信息的，比如xdes数据页就是用来存放这一组数据区的一些相关属性的，其实就是很多描述这组数据区的东西 。下面我们通过一张图来看一下表空间的存储结构 。
1、linux操作系统的存储系统软件层原理分析以及IO调度优化原理
简单来说，linux的存储系统分为 VFS层、文件系统层 ， Page Cache缓存层，通用Block层、IO调度层、Block设备驱动层、Block设备层 ， 如下图：
最后IO完成调度之后，就会决定哪个IO请求先执行，哪个IO请求后执行，此时可以执行的IO请求就会交给Block设备驱动层，最后经过驱动把IO请求发送给真正的存储硬件，也就是Block设备层 。硬件设备完成IO读写操作 ， 最后就把响应经过上面的层级反向依次返回，最终MySQL可以得到本次IO读写操作的结果 。
关于mysql的io怎么优化和mysql io优化的介绍到此就结束了，不知道你从中找到你需要的信息了吗 ？如果你还想了解更多这方面的信息，记得收藏关注本站 。

    推荐阅读


    上一篇：如何投放头条营销广告，如何投放头条营销广告视频

    下一篇：翡翠直播间工具，翡翠直播间可靠吗