锐客网

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

mysql数据太多了怎么处理 mysql多少数据会慢

IT技术数据

敲重点！MySQL数据查询太多会OOM吗？ 我mysql数据太多了怎么处理的主机内存只有100Gmysql数据太多了怎么处理，现在要全表扫描一个200G大表mysql数据太多了怎么处理，会不会把DB主机的内存用光？
逻辑备份时，可不就是做整库扫描吗？若这样就会把内存吃光 ， 逻辑备份不是早就挂mysql数据太多了怎么处理了？
所以大表全表扫描，看起来应该没问题 。这是为啥呢？
假设，我们现在要对一个200G的InnoDB表db1. t，执行一个全表扫描 。当然，你要把扫描结果保存在客户端，会使用类似这样的命令：
InnoDB数据保存在主键索引上，所以全表扫描实际上是直接扫描表t的主键索引 。这条查询语句由于没有其他判断条件，所以查到的每一行都可以直接放到结果集，然后返回给客户端 。
那么，这个“结果集”存在哪里呢？
服务端无需保存一个完整结果集 。取数据和发数据的流程是这样的：
查询结果发送流程
可见：
所以MySQL其实是“边读边发” 。这意味着，若客户端接收得慢，会导致MySQL服务端由于结果发不出去，这个事务的执行时间变长 。
比如下面这个状态 ， 就是当客户端不读 socket receive buffer 内容时，在服务端show processlist看到的结果 。
若看到State一直是“Sending to client”，说明服务器端的网络栈写满了 。
若客户端使用–quick参数，会使用mysql_use_result方法：读一行处理一行 。假设某业务的逻辑较复杂，每读一行数据以后要处理的逻辑若很慢 ， 就会导致客户端要过很久才取下一行数据，可能就会出现上图结果 。
因此 ， 对于正常的线上业务来说，若一个查询的返回结果不多，推荐使用mysql_store_result接口，直接把查询结果保存到本地内存 。
当然前提是查询返回结果不多 。如果太多，因为执行了一个大查询导致客户端占用内存近20G，这种情况下就需要改用mysql_use_result接口 。
若你在自己负责维护的MySQL里看到很多个线程都处于“Sending to client” ， 表明你要让业务开发同学优化查询结果，并评估这么多的返回结果是否合理 。
若要快速减少处于这个状态的线程的话，可以将net_buffer_length设置更大 。
有时 ， 实例上看到很多查询语句状态是“Sending data”，但查看网络也没什么问题，为什么Sending data要这么久？
一个查询语句的状态变化是这样的：
即“Sending data”并不一定是指“正在发送数据”，而可能是处于执行器过程中的任意阶段 。比如，你可以构造一个锁等待场景，就能看到Sending data状态 。
读全表被锁：
Sending data状态
可见session2是在等锁，状态显示为Sending data 。
所以，查询的结果是分段发给客户端，因此扫描全表，查询返回大量数据，并不会把内存打爆 。
以上是server层的处理逻辑，在InnoDB引擎里又是怎么处理？
InnoDB内存的一个作用 ， 是保存更新的结果，再配合redo log，避免随机写盘 。
内存的数据页是在Buffer Pool (简称为BP)管理，在WAL里BP起加速更新的作用 。
BP还能加速查询 。
而BP对查询的加速效果 ， 依赖于一个重要的指标 ， 即：内存命中率 。
可以在show engine innodb status结果中，查看一个系统当前的BP命中率 。一般情况下，一个稳定服务的线上系统 ， 要保证响应时间符合要求的话，内存命中率要在99%以上 。
执行show engine innodb status ，可以看到“Buffer pool hit rate”字样，显示的就是当前的命中率 。比如下图命中率，就是100% 。
若所有查询需要的数据页都能够直接从内存得到，那是最好的，对应命中率100% 。
InnoDB Buffer Pool的大小是由参数 innodb_buffer_pool_size确定，一般建议设置成可用物理内存的60%~80% 。
在大约十年前，单机的数据量是上百个G ， 而物理内存是几个Gmysql数据太多了怎么处理；现在虽然很多服务器都能有128G甚至更高的内存，但是单机的数据量却达到了T级别 。
所以 ， innodb_buffer_pool_size小于磁盘数据量很常见 。若一个 Buffer Pool满了，而又要从磁盘读入一个数据页，那肯定是要淘汰一个旧数据页的 。
使用的最近最少使用 (Least Recently Used, LRU)算法，淘汰最久未使用数据 。
InnoDB管理BP的LRU算法 ， 是用链表实现的：
最终就是最久没有被访问的数据页Pm被淘汰 。
若此时要做一个全表扫描，会怎样？若要扫描一个200G的表，而这个表是一个历史数据表，平时没有业务访问它 。
那么 ， 按此算法扫描，就会把当前BP里的数据全部淘汰 ， 存入扫描过程中访问到的数据页的内容 。也就是说BP里主要放的是这个历史数据表的数据 。
对于一个正在做业务服务的库，这可不行呀 。你会看到，BP内存命中率急剧下降，磁盘压力增加，SQL语句响应变慢 。
所以，InnoDB不能直接使用原始的LRU 。InnoDB对其进行了优化 。
InnoDB按5:3比例把链表分成New区和Old区 。图中LRU_old指向的就是old区域的第一个位置，是整个链表的5/8处 。即靠近链表头部的5/8是New区域，靠近链表尾部的3/8是old区域 。
改进后的LRU算法执行流程：
该策略 ， 就是为了处理类似全表扫描的操作量身定制 。还是扫描200G历史数据表：
可以看到，这个策略最大的收益，就是在扫描这个大表的过程中 ， 虽然也用到了BP，但对young区完全没有影响，从而保证了Buffer Pool响应正常业务的查询命中率 。
MySQL采用的是边算边发的逻辑 ， 因此对于数据量很大的查询结果来说，不会在server端保存完整的结果集 。所以，如果客户端读结果不及时 ， 会堵住MySQL的查询过程，但是不会把内存打爆 。
而对于InnoDB引擎内部，由于有淘汰策略，大查询也不会导致内存暴涨 。并且，由于InnoDB对LRU算法做了改进，冷数据的全表扫描，对Buffer Pool的影响也能做到可控 。
全表扫描还是比较耗费IO资源的，所以业务高峰期还是不能直接在线上主库执行全表扫描的 。
MySQL中的数据量太大了，怎么办可以将历史数据迁移出来mysql数据太多了怎么处理，另外放一个库mysql数据太多了怎么处理，作为历史库
mysql 如何处理亿级数据1、数据表 collect ( id, title ,info ,vtype) 就这4个字段，其中 title 用定长，info 用text, id 是逐渐，vtype是tinyint ， vtype是索引 。这是一个基本的新闻系统的简单模型 。现在往里面填充数据 ， 填充10万篇新闻 。
2、最后collect 为 10万条记录 ， 数据库表占用硬盘1.6G 。OK ,看下面这条sql语句：select id,title from collect limit 1000,10; 很快；基本上0.01秒就OK，再看下面的select id,title from collect limit 90000,10; 从9万条开始分页 。
3、8-9秒完成 。
4、看下面一条语句:select id from collect order by id limit 90000,10; 很快，0.04秒就OK 。因为用了id主键做索引当然快 。
mysql 数据量超过百万后怎么处理1、应尽量避免在 where 子句中使用!=或操作符mysql数据太多了怎么处理 ， 否则将引擎放弃使用索引而进行全表扫描 。
2、对查询进行优化mysql数据太多了怎么处理，应尽量避免全表扫描mysql数据太多了怎么处理，首先应考虑在 where 及 order by 涉及的列上建立索引 。
3、应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num is null
可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
select id from t where num=0
4、尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num=10 or num=20
可以这样查询：
select id from t where num=10
union all
select id from t where num=20
5、下面的查询也将导致全表扫描：(不能前置百分号)
select id from t where name like ‘?1?7c%’
若要提高效率，可以考虑全文检索 。
6、in 和 not in 也要慎用 ， 否则会导致全表扫描，如：
select id from t where num in(1,2,3)
对于连续的数值，能用 between 就不要用 in mysql数据太多了怎么处理了：
select id from t where num between 1 and 3
如果mysql里面的数据过多，查询太慢怎么办？问题
我们有一个 SQL，用于找到没有主键 / 唯一键的表，但是在 MySQL 5.7 上运行特别慢，怎么办？
实验
我们搭建一个 MySQL 5.7 的环境，此处省略搭建步骤 。
写个简单的脚本，制造一批带主键和不带主键的表：
执行一下脚本：
现在执行以下 SQL 看看效果：
...
执行了 16.80s ， 感觉是非常慢了 。
现在用一下 DBA 三板斧，看看执行计划：
感觉有点惨，由于 information_schema.columns 是元数据表，没有必要的统计信息 。
那我们来 show warnings 看看 MySQL 改写后的 SQL：
我们格式化一下 SQL：
可以看到 MySQL 将
select from A where A.x not in (select x from B) //非关联子查询
转换成了
select from A where not exists (select 1 from B where B.x = a.x) //关联子查询
如果我们自己是 MySQL ， 在执行非关联子查询时，可以使用很简单的策略：
select from A where A.x not in (select x from B where ...) //非关联子查询:1. 扫描 B 表中的所有记录，找到满足条件的记录，存放在临时表 C 中 ， 建好索引2. 扫描 A 表中的记录 ， 与临时表 C 中的记录进行比对 ， 直接在索引里比对，
而关联子查询就需要循环迭代：
select from A where not exists (select 1 from B where B.x = a.x and ...) //关联子查询扫描 A 表的每一条记录 rA：扫描 B 表 ， 找到其中的第一条满足 rA 条件的记录 。
显然，关联子查询的扫描成本会高于非关联子查询 。
我们希望 MySQL 能先"缓存"子查询的结果（缓存这一步叫物化 ， MATERIALIZATION），但MySQL 认为不缓存更快，我们就需要给予 MySQL 一定指导 。
...
可以看到执行时间变成了 0.67s 。
整理
我们诊断的关键点如下：
\1. 对于 information_schema 中的元数据表 ， 执行计划不能提供有效信息 。
\2. 通过查看 MySQL 改写后的 SQL，我们猜测了优化器发生了误判 。
\3. 我们增加了 hint ， 指导 MySQL 正确进行优化判断 。
但目前我们的实验仅限于猜测，猜中了万事大吉 ， 猜不中就无法做出好的诊断 。
【mysql数据太多了怎么处理 mysql多少数据会慢】mysql数据太多了怎么处理的介绍就聊到这里吧，感谢你花时间阅读本站内容，更多关于mysql多少数据会慢、mysql数据太多了怎么处理的信息别忘了在本站进行查找喔 。

    推荐阅读


    上一篇：服务器端口没开 怎么不开放服务器端口号

    下一篇：js获取更改后的输入密码，js用户名密码正确之后跳转