56,679
社区成员
发帖
与我相关
我的任务
分享MySql 上亿记录处理问题 ！！！！
现在系统单表记录 1 年的数据大概2亿左右，每个月2000W 多的样子
按网上的优化方法进行了一定的优化
对折个表建立了分区表 按记录时间的月进行的分区，也就是12个分区
现在的问题是系统要对这个表数据进行分页访问，经60000W的数据量测试，有如下的问题：
=============问题1==============
统计记录总数的时候发现非常的慢，
在数据引擎为： Myisam 时：
select count(*) from test 很快
select count(id) from test where addtime>='2013-01-01' and addtime<='2013-01-31' 很慢
在数据引擎为：InnoDB 时：
select count(*) from test 很慢
select count(id) from test where addtime>='2013-01-01' and addtime<='2013-01-31' 很快
请问有什么方法能加快记录的统计？？？？？？？？
=============问题2==============
还有分区表貌似没有什么用处，建立分区表后，我按分区关键字段查询记录也没见提高查询速度，我想应该是我用错了，请问我要怎么样才算正确使用分区表来提高查询速度呢？？？？
=============问题3==============
还有分表的话具体怎么实施？如果我按月分表，但是碰到要夸月查询的情况怎么办？
现在我只在统计分析上做了分表，按年，月，日 分表，由存储过程自动计算放入相应的表
=============问题4==============
还有用limit 分页，在查询比较靠后的页时，会很慢。
在使用
SELECT * FROM visitlog where ID >(SELECT ID from visitlog ORDER BY ID ASC LIMIT 20000000,1)
ORDER BY ID ASC
LIMIT 20;
这种方式后有所提高，但是发现只能对ID字段有提高，用在日期型或其他类型上都很慢，怎么办？？？
按网上的优化方法进行了一定的优化
对折个表建立了分区表 按记录时间的月进行的分区，也就是12个分区
现在的问题是系统要对这个表数据进行分页访问，经60000W的数据量测试，有如下的问题：
=============问题1==============
统计记录总数的时候发现非常的慢，
在数据引擎为： Myisam 时：
select count(*) from test 很快
select count(id) from test where addtime>='2013-01-01' and addtime<='2013-01-31' 很慢
在数据引擎为：InnoDB 时：
select count(*) from test 很慢
select count(id) from test where addtime>='2013-01-01' and addtime<='2013-01-31' 很快
请问有什么方法能加快记录的统计？？？？？？？？
=============问题2==============
还有分区表貌似没有什么用处，建立分区表后，我按分区关键字段查询记录也没见提高查询速度，我想应该是我用错了，请问我要怎么样才算正确使用分区表来提高查询速度呢？？？？
=============问题3==============
还有分表的话具体怎么实施？如果我按月分表，但是碰到要夸月查询的情况怎么办？
现在我只在统计分析上做了分表，按年，月，日 分表，由存储过程自动计算放入相应的表
=============问题4==============
还有用limit 分页，在查询比较靠后的页时，会很慢。
在使用
SELECT * FROM visitlog where ID >(SELECT ID from visitlog ORDER BY ID ASC LIMIT 20000000,1)
ORDER BY ID ASC
LIMIT 20;
这种方式后有所提高，但是发现只能对ID字段有提高，用在日期型或其他类型上都很慢，怎么办？？？
...全文
请发表友善的回复…
发表回复
小灰狼 2014-01-09
- 打赏
- 举报
分了区，也要对分区字段创建索引，否则效率还是上不去
Rotel-刘志东 2014-01-09
- 打赏
- 举报
分区表对oltp来说是不能解决现在需求，可以通过分表来实现，
查询可以union或union all处理。
fasdlkfjd 2014-01-03
- 打赏
- 举报
我来简单答一下
统计记录总数的时候发现非常的慢，
在数据引擎为： Myisam 时：
select count(*) from test 很快 因为 MyISAM 存储引擎存储了 count(*) 的值 , 而不是实际计算得来 , 它在更新索引统计 ( analyze table ) 时会精确计算该值并存储 , 所以来的很快
select count(id) from test where addtime>='2013-01-01' and addtime<='2013-01-31' 很慢
当然 , 因为 count(id) 是实际根据索引计算了一遍 , 当然很慢 .
在数据引擎为：InnoDB 时：
select count(*) from test 很慢 当然 , 因为 InnoDB 并不存储 count(*) 的值
select count(id) from test where addtime>='2013-01-01' and addtime<='2013-01-31' 很快
当然 , 因为 count(id) , 这个 id 必须是 parmary key , 因为 InnoDB 是聚集索引 , 按照主键聚集 ( 如果未设主键 , 那么会按照第一个 NOT NULL , UNIQUE 来聚集 , 如果分析一个辅助索引 ( 需要 SQL_NO_CACHE ) 那么速度应该会下降一些 , 具体是多少 , 可以进行基准测试 . 以准确测量问题 .
请问有什么方法能加快记录的统计？？？？？？？？
有 , 利用计数表 ( 缓存表 , 汇总表 , 计数表里的那个计数表 ) , 优点是效能可以加快 , 缺点是可能不那么精确 , 如果想知道具体怎么用 , 可以阅读 《高性能的 MySQL 第三版》第 4.4.2 章节 : 计数器表
还有分区表貌似没有什么用处，建立分区表后，我按分区关键字段查询记录也没见提高查询速度，我想应该是我用错了，请问我要怎么样才算正确使用分区表来提高查询速度呢？？？？
分区表 , 其实你可以想成一种粗粒度的索引 , 如果你没用到你分区所依赖的键 , 那么速度依然很慢 . 当然 , 问题可以很多 , 不仅仅与此 , 因为性能优化 , 无法准确测量就无法优化 , 我想对你来说最重要的是搞清楚 , 问题到底是什么 , 分区表貌似没什么用处 , 查询速度依然慢 . 对这个情况分区表到底是导致这个情况发生的原因 , 还是结果 . 推荐使用 EXPLAIN PARTITION 来查看分区表语句的执行计划 .
还有分表的话具体怎么实施？如果我按月分表，但是碰到要夸月查询的情况怎么办？
现在我只在统计分析上做了分表，按年，月，日 分表，由存储过程自动计算放入相应的表
这个解释起来篇幅太长 , 简单来说一句话 , 有所得必有所失 . 建议翻阅 《高性能的 MySQL 第三版》第 11 章 : 可拓展的 MySQL .
还有用limit 分页，在查询比较靠后的页时，会很慢。
在使用
SELECT * FROM visitlog where ID >(SELECT ID from visitlog ORDER BY ID ASC LIMIT 20000000,1)
ORDER BY ID ASC
LIMIT 20;
这种方式后有所提高，但是发现只能对ID字段有提高，用在日期型或其他类型上都很慢，怎么办？？
建议你仔细查看改写后这种方式的执行计划 ( explain ) 和未改写SELECT * FROM visitlog ORDER BY ID ASC LIMIT 20; 的执行计划 , 看看到底有什么不同 . 一句话 , 随着时间的推移 , 性能优化会转变为对执行计划的纯粹推理 .
我只是路过 ......
rucypli 2013-12-30
- 打赏
- 举报
现在的模式已经适应不了你的应用需求了
要么换更好的硬件 要么改变数据组织方式 一般来说oltp应用分区表没用
空格键 2013-12-30
- 打赏
- 举报
请问版主大人，按月分表的话，在跨月份查询时怎么办？如果只是统计到可以在存储过程中处理
rucypli 2013-12-30
- 打赏
- 举报
不要做分区表 按月分表就行了
tbname_201311 tbname201312 ...
