草庐IT

具有 550M 行和 128MB 内存的 mysql 表

coder 2023-10-24 原文

如果有人能解释 MySQL 为何不在默认配置下使用大表，我将不胜感激。

note: I don't need advice how to increase the memory, improve the performance or migrate etc. I want to understand why it is working and performing well.

我有下表:

CREATE TABLE `daily_reads` (
  `a` varchar(32) NOT NULL DEFAULT '',
  `b` varchar(50) NOT NULL DEFAULT '',
  `c` varchar(20) NOT NULL DEFAULT '',
  `d` varchar(20) NOT NULL DEFAULT '',
  `e` varchar(20) NOT NULL DEFAULT '',
  `f` varchar(10) NOT NULL DEFAULT 'Wh',
  `g` datetime NOT NULL,
  `PERIOD_START` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `i` decimal(16,3) NOT NULL,
  `j` decimal(16,3) NOT NULL DEFAULT '0.000',
  `k` decimal(16,2) NOT NULL DEFAULT '0.00',
  `l` varchar(1) NOT NULL DEFAULT 'N',
  `m` varchar(1) NOT NULL DEFAULT 'N',
  PRIMARY KEY (`a`,`b`,`c`,`PERIOD_START`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

它在具有 1 个 CPU 内核、6GB RAM、CentOS 7 的虚拟机上运行(对该虚拟机的访问权限非常有限)。

它在具有 128MB 缓冲池的默认 MySQL 配置上运行 (SELECT @@innodb_buffer_pool_size/1024/1024)

数据库大小约为 96GB，“读取”表中有约 5.6 亿行，其他表中有约 7.1 亿行。

select database_name, table_name, index_name, stat_value*@@innodb_page_size
from mysql.innodb_index_stats where stat_name='size';

PRIMARY:83,213,500,416(没有其他索引)

我每月读取约 50 万次，写入仅作为 ETL 过程的一部分直接从 Informatica 到数据库(每月约 7500 万次写入)。

只通过存储过程调用读取查询:

CALL sp_get_meter_data('678912345678', '1234567765432', '2017-01-13 00:00:00', '2017-05-20 00:00:00');

// striped out the not important bits:
...
SET daily_from_date = DATE_FORMAT(FROM_DATE_TIME, '%Y-%m-%d 00:00:00');
SET daily_to_date = DATE_FORMAT(TO_DATE_TIME, '%Y-%m-%d 23:59:59');
...
SELECT
    *
FROM
    daily_reads
WHERE
    A = FRIST_NUMBER
    AND
    B = SECOND_NUMBER
    AND
    daily_from_date <= PERIOD_START
    AND
    daily_to_date >= PERIOD_START
ORDER BY
    PERIOD_START ASC;

我对 InnoDB 的理解非常有限，但我认为我需要将所有索引放入内存中以进行快速查询。读取过程仅需几毫秒。我认为在默认的 MySQL 配置上查询 500M+ 表的速度在技术上是不可能的...？

我错过了什么？

注意:我不需要如何增加内存、提高性能或迁移等方面的建议。我想了解它为何运行良好且性能良好。

最佳答案

长答案:您的主键是多个以 a 和 b 开头的列的组合。

您的 WHERE 子句说明了这一点。

 WHERE a = FRIST_NUMBER
   AND b = SECOND_NUMBER
   AND etc etc.

这个 WHERE 子句确实非常有效地利用了与您的主键关联的索引。它随机访问索引到它需要的第一行，然后按顺序扫描它。因此，它实际上不必分页您的索引或表中的大部分内容来满足您的查询。

简短回答:当查询利用索引时，MySQL 快速且便宜。

如果您想要一个完美 用于此查询的索引，它将是 (a, b, daily_from_date) 上的复合索引。这将使用相等匹配来命中索引中的第一个匹配行，然后范围扫描您选择的日期范围的索引。但是你现在的表现已经很不错了。

您询问索引是否必须完全适合内存。不是。DBMS 软件的全部目的 是处理内存中不可能一次容纳的大量数据。好的 DBMS 实现可以很好地维护内存缓存，并在需要时从大容量存储中刷新这些缓存。 innodb 缓冲池就是这样一种缓存。请记住，对表的任何插入或更新最终都需要将表数据和索引数据写入大容量存储。

关于具有 550M 行和 128MB 内存的 mysql 表，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/45485999/

mysql550Mcode39DEFAULTinnodbquery-performance

有关具有 550M 行和 128MB 内存的 mysql 表的更多相关文章

  1. ruby-on-rails - Ruby net/ldap 模块中的内存泄漏 - 2

    作为我的Rails应用程序的一部分，我编写了一个小导入程序，它从我们的LDAP系统中吸取数据并将其塞入一个用户表中。不幸的是，与LDAP相关的代码在遍历我们的32K用户时泄漏了大量内存，我一直无法弄清楚如何解决这个问题。这个问题似乎在某种程度上与LDAP库有关，因为当我删除对LDAP内容的调用时，内存使用情况会很好地稳定下来。此外，不断增加的对象是Net::BER::BerIdentifiedString和Net::BER::BerIdentifiedArray，它们都是LDAP库的一部分。当我运行导入时，内存使用量最终达到超过1GB的峰值。如果问题存在，我需要找到一些方法来更正我的代

  2. ruby - 具有身份验证的私有(private) Ruby Gem 服务器 - 2

    我想安装一个带有一些身份验证的私有(private)Rubygem服务器。我希望能够使用公共(public)Ubuntu服务器托管内部gem。我读到了http://docs.rubygems.org/read/chapter/18.但是那个没有身份验证-如我所见。然后我读到了https://github.com/cwninja/geminabox.但是当我使用基本身份验证(他们在他们的Wiki中有)时，它会提示从我的服务器获取源。所以。如何制作带有身份验证的私有(private)Rubygem服务器？这是不可能的吗？谢谢。编辑:Geminabox问题。我尝试“捆绑”以安装新的gem..

  3. ruby-on-rails - Ruby 中的内存模型 - 2

    ruby如何管理内存。例如:如果我们在执行过程中采用C程序，则以下是内存模型。类似于这个ruby如何处理内存。C:__________________|||stack|||------------------||||------------------|||||Heap|||||__________________|||data|__________________|text|__________________Ruby:? 最佳答案 Ruby中没有“内存”这样的东西。Class#allocate分配一个对象并返回该对象。这就是程序

  4. ruby-on-rails - Rails 3.1 中具有相同形式的多个模型？ - 2

    我正在使用Rails3.1并在一个论坛上工作。我有一个名为Topic的模型，每个模型都有许多Post。当用户创建新主题时，他们也应该创建第一个Post。但是，我不确定如何以相同的形式执行此操作。这是我的代码:classTopic:destroyaccepts_nested_attributes_for:postsvalidates_presence_of:titleendclassPost...但这似乎不起作用。有什么想法吗？谢谢! 最佳答案 @Pablo的回答似乎有你需要的一切。但更具体地说...首先改变你View中的这一行对此#

  5. 使用canal同步MySQL数据到ES - 2

    文章目录一、概述简介原理模块二、配置Mysql使用版本环境要求1.操作系统2.mysql要求三、配置canal-server离线下载在线下载上传解压修改配置单机配置集群配置分库分表配置1.修改全局配置2.实例配置垂直分库水平分库3.修改group-instance.xml4.启动监听四、配置canal-adapter1修改启动配置2配置映射文件3启动ES数据同步查询所有订阅同步数据同步开关启动4.验证五、配置canal-admin一、概述简介canal是Alibaba旗下的一款开源项目，Java开发。基于数据库增量日志解析，提供增量数据订阅&消费。Git地址：https://github.co

  6. ruby - 具有两个参数的 block - 2

    我从用户Hirolau那里找到了这段代码:defsum_to_n?(a,n)a.combination(2).find{|x,y|x+y==n}enda=[1,2,3,4,5]sum_to_n?(a,9)#=>[4,5]sum_to_n?(a,11)#=>nil我如何知道何时可以将两个参数发送到预定义方法(如find)？我不清楚，因为有时它不起作用。这是重新定义的东西吗？ 最佳答案 如果您查看Enumerable#find的文档，您会发现它只接受一个block参数。您可以将它发送两次的原因是因为Ruby可以方便地让您根据它的“并行赋

  7. ruby-on-rails - 在 RSpec 中，如何以任意顺序期望具有不同参数的多条消息？ - 2

    RSpec似乎按顺序匹配方法接收的消息。我不确定如何使以下代码工作:allow(a).toreceive(:f)expect(a).toreceive(:f).with(2)a.f(1)a.f(2)a.f(3)我问的原因是a.f的一些调用是由我的代码的上层控制的，所以我不能对这些方法调用添加期望。 最佳答案 RSpecspy是测试这种情况的一种方式。要监视一个方法，用allowstub，除了方法名称之外没有任何约束，调用该方法，然后expect确切的方法调用。例如:allow(a).toreceive(:f)a.f(2)a.f(1)

  8. 键删除后 ruby​​ 哈希内存泄漏 - 2

    你好，我无法成功如何在散列中删除key后释放内存。当我从哈希中删除键时，内存不会释放，也不会在手动调用GC.start后释放。当从Hash中删除键并且这些对象在某处泄漏时，这是预期的行为还是GC不释放内存？如何在Ruby中删除Hash中的键并在内存中取消分配它？例子:irb(main):001:0>`ps-orss=-p#{Process.pid}`.to_i=>4748irb(main):002:0>a={}=>{}irb(main):003:0>1000000.times{|i|a[i]="test#{i}"}=>1000000irb(main):004:0>`ps-orss=-p

  9. ruby-on-rails - 具有同名的模块和类 - 2

    我有一个模块stat存在于目录结构中:lib/stat_creator/stat/在lib/stat_creator/stat.rb中，我在lib/stat_creator/stat/目录中有我需要的文件，以及:moduleStatCreatormoduleStatendend当我使用该模块时，我将这些类称为StatCreator::Stat::Foo.new现在我想要一个存在于应用程序中的根Stat类。我在app/models中制作了我的Stat类，并在routes.rb中进行了设置。但是，如果我转到Rails控制台并尝试在应用程序/模型中使用Stat类，例如:Stat.by_use

  10. ruby-on-rails - 在具有 ActiveRecord 条件的相关模型中按字段排序 - 2

    我正在尝试按Rails相关模型中的字段进行排序。我研究的所有解决方案都没有解决如果相关模型被另一个参数过滤？元素模型classItem相关模型:classPriority我正在使用where子句检索项目:@items=Item.where('company_id=?andapproved=?',@company.id,true).all我需要按相关表格中的“位置”列进行排序。问题在于，在优先级模型中，一个项目可能会被多家公司列出。因此，这些职位取决于他们拥有的company_id。当我显示项目时，它是针对一个公司的，按公司内的职位排序。完成此任务的正确方法是什么？感谢您的帮助。PS-我

随机推荐