mysql Innodb单表31m千万级数据count计数方案及调优

ENV

  1. 线上环境为RDS,版本5.7.15

    select version()
    output:
    5.7.15-log
  2. 测试环境为docker搭建的mysql,版本5.7.19

    select version()
    output:
    5.7.19
  3. 单表3000万+的class表以及20万+的学校表,需要使用count查询实时数量用于分页,延迟不能太高,否则影响业务
  4. 因需要使用事务功能,使用存储引擎为Innodb(MyISAM count是自动计数单独保存,Innodb需要每次扫描表进行统计)
  5. 本文使用class表进行示例表述,school同理

OPTIMIZE

一. 出现的第一个问题是RDS线上mysql的查询速度始终没有测试库的快,相同的数据和存储结构,索引数据都相同(一开始线上使用count完全不能查询,会出现等待超时).
1. 查看索引

show index from consumer.class;
output:
'class', '0', 'PRIMARY', '1', 'id', 'A', '28663646', NULL, NULL, '', 'BTREE', '', ''
'class', '0', 'UQE_class_loginName', '1', 'loginName', 'A', '28663646', NULL, NULL, 'YES', 'BTREE', '', ''
'class', '1', 'IDX_class_school_id', '1', 'school_id', 'A', '211268', NULL, NULL, '', 'BTREE', '', ''
'class', '1', 'grade_id', '1', 'grade_id', 'A', '8644', NULL, NULL, 'YES', 'BTREE', '', ''
'class', '1', 'schuid', '1', 'schuid', 'A', '216557', NULL, NULL, 'YES', 'BTREE', '', ''

2. 测试时间

set profiling = 1;
SELECT count(*) FROM consumer.class;
show profiles;

3. 分别分析sql的执行

explain select count(*) from consumer.class ;
test output:
'1', 'SIMPLE', 'class', NULL, 'index', NULL, 'IDX_class_school_id', '4', NULL, '28663646', '100.00', 'Using index'
online output:
1
1
SIMPLE
null
null
null
null
null
null
null
null
null
Select tables optimized away

发现线上版本的mysql是经过自己编译器优化的Select tables optimized away,但是效率确实低到不能接受(单独这样查询几分钟过后仍然查不出来,并且显示超时),这是因为mysql5.7.*版本机制相关的问题,具体可参考:
https://bugs.mysql.com/bug.php?id=80580
https://stackoverflow.com/questions/27377549/select-count-not-using-index
4. 强制使用索引,解决了上面线上查询几分钟仍不能查询到结果后返回超时的问题

select count(`id`) from consumer.class force index(primary) where id > 0
explain 
  • 1
    点赞
  • 8
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
MySQL InnoDB是一种非常流行的关系型数据库存储引擎,但是在高并发、大数据量的情况下,需要对InnoDB进行适当的调优才能发挥出其最大的性能。 以下是一些常见的InnoDB调优方法: 1. 调整缓冲池大小:InnoDB的缓冲池是用来存储数据和索引的,缓冲池越大,可以存储的数据和索引也就越多。可以通过设置innodb_buffer_pool_size参数来调整缓冲池的大小。 2. 开启慢查询日志:慢查询日志可以记录执行时间超过指定阈值的SQL语句,以便进行分析和优化。可以通过设置slow_query_log和long_query_time参数来开启慢查询日志。 3. 调整线程数:InnoDB使用多个线程来处理不同的任务,如读取数据、写入数据、刷新缓存等。可以通过设置innodb_thread_concurrency参数来调整InnoDB使用的线程数。 4. 分区表:如果表中的数据量很大,可以考虑将表进行分区,这样可以提高查询速度并减少锁的竞争。 5. 调整日志文件大小:InnoDB使用redo日志和undo日志来保证数据的一致性和可恢复性。可以通过设置innodb_log_file_size参数来调整日志文件的大小,以提高崩溃恢复速度。 6. 调整缓存大小:InnoDB使用缓存来存储数据和索引,缓存大小可以通过设置innodb_buffer_pool_size参数来调整。 7. 使用SSD硬盘:SSD硬盘相比传统机械硬盘有更高的读写速度和更低的访问延迟,可以显著提高InnoDB的性能。 8. 使用索引:在查询数据时,使用索引可以大大提高查询速度。可以通过使用explain命令来分析查询语句是否使用了索引。 以上是一些常见的InnoDB调优方法,需要根据具体情况进行选择和调整。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值