mysql - MySql-处理表的大小和性能

coder 2023-10-13 原文

我们有一个Analytics产品。我们为每位客户提供一个JavaScript代码，然后将其放在自己的网站中。如果用户访问我们的客户站点，则Java脚本代码会击中我们的服务器，以便我们代表该客户存储此页面访问。每个客户都包含唯一的域名。

我们将此页面访问存储在MySql表中。

以下是表架构。

CREATE TABLE `page_visits` (
  `domain` varchar(50) DEFAULT NULL,
  `guid` varchar(100) DEFAULT NULL,
  `sid` varchar(100) DEFAULT NULL,
  `url` varchar(2500) DEFAULT NULL,
  `ip` varchar(20) DEFAULT NULL,
  `is_new` varchar(20) DEFAULT NULL,
  `ref` varchar(2500) DEFAULT NULL,
  `user_agent` varchar(255) DEFAULT NULL,
  `stats_time` datetime DEFAULT NULL,
  `country` varchar(50) DEFAULT NULL,
  `region` varchar(50) DEFAULT NULL,
  `city` varchar(50) DEFAULT NULL,
  `city_lat_long` varchar(50) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  KEY `sid_index` (`sid`) USING BTREE,
  KEY `domain_index` (`domain`),
  KEY `email_index` (`email`),
  KEY `stats_time_index` (`stats_time`),
  KEY `domain_statstime` (`domain`,`stats_time`),
  KEY `domain_email` (`domain`,`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

我们没有此表的主键。

MySQL服务器详细信息

它是Google Cloud MySql(版本5.6)，存储容量为10TB。

截至目前，我们的表中有3.5亿行，表大小为300 GB。我们将所有客户详细信息存储在同一表中，即使一个客户与另一个客户之间没有关系。

问题1 :对于表中具有大量行的少数客户，因此针对这些客户的查询性能非常慢。

查询示例1:

SELECT count(DISTINCT sid) AS count,count(sid) AS total FROM page_views WHERE domain = 'aaa' AND stats_time BETWEEN CONVERT_TZ('2015-02-05 00:00:00','+05:30','+00:00') AND CONVERT_TZ('2016-01-01 23:59:59','+05:30','+00:00');
+---------+---------+
| count   | total   |
+---------+---------+
| 1056546 | 2713729 |
+---------+---------+
1 row in set (13 min 19.71 sec)

我将在这里更新更多查询。我们需要5-10秒以内的结果，这可能吗？

问题2 :表大小正在迅速增加，到今年年底，我们可能会达到5 TB的表大小，因此我们希望对表进行分片。我们希望将与一位客户相关的所有记录保存在一台机器上。分片的最佳做法是什么？

我们正在考虑以下解决上述问题的方法，请向我们提出克服这些问题的最佳做法。

为每个客户创建单独的表

1)如果我们为每个客户创建单独的表，则优缺点是什么？截至目前，我们有3万个客户，到今年年底，我们可能达到10万个客户，这意味着数据库中有10万个表。我们同时访问所有表以进行读取和写入。

2)我们将使用同一张表，并将根据日期范围创建分区

更新:“客户”是否由域确定？ 答案是肯定的

谢谢

最佳答案

首先，批判数据类型是否过大:

  `domain` varchar(50) DEFAULT NULL,  -- normalize to MEDIUMINT UNSIGNED (3 bytes)
  `guid` varchar(100) DEFAULT NULL,  -- what is this for?
  `sid` varchar(100) DEFAULT NULL,  -- varchar?
  `url` varchar(2500) DEFAULT NULL,
  `ip` varchar(20) DEFAULT NULL,  -- too big for IPv4, too small for IPv6; see below
  `is_new` varchar(20) DEFAULT NULL,  -- flag?  Consider `TINYINT` or `ENUM`
  `ref` varchar(2500) DEFAULT NULL,
  `user_agent` varchar(255) DEFAULT NULL,  -- normalize! (add new rows as new agents are created)
  `stats_time` datetime DEFAULT NULL,
  `country` varchar(50) DEFAULT NULL,  -- use standard 2-letter code (see below)
  `region` varchar(50) DEFAULT NULL,  -- see below
  `city` varchar(50) DEFAULT NULL,  -- see below
  `city_lat_long` varchar(50) DEFAULT NULL,  -- unusable in current format; toss?
  `email` varchar(100) DEFAULT NULL,

对于IP地址，请使用inet6_aton()，然后将其存储在BINARY(16)中。

对于country，请使用CHAR(2) CHARACTER SET ascii －仅2个字节。

国家(地区)+城市(+)+(可能)latlng-将其归一化为“位置”。

所有这些更改可能会将磁盘占用空间减少一半。较小->更易于缓存->更少的I / O->更快。

其他问题 ...

为了大大加快sid计数器的速度，请更改

KEY `domain_statstime` (`domain`,`stats_time`),

至

KEY dss (domain_id,`stats_time`, sid),

那将是一个“覆盖索引”，因此不必在索引和数据之间跳动2713729次-跳动花费了13分钟。 (domain_id在下面讨论。)

这与上面的索引DROP一起是多余的:
KEY domain_index(domain)

“客户”由domain确定吗？

每个InnoDB表都必须具有PRIMARY KEY。有三种获取PK的方法；您选择了“最差”的-引擎制造的隐藏的6字节整数。我认为某些组合列没有可用的“自然” PK？然后，需要一个显式的BIGINT UNSIGNED。 (是的，这将是8个字节，但是各种形式的维护都需要显式的PK。)

如果大多数查询都包含WHERE domain = '...'，那么我建议以下内容。 (这将大大改善所有此类查询。)

id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
domain_id MEDIUMINT UNSIGNED NOT NULL,   -- normalized to `Domains`
PRIMARY KEY(domain_id, id),  -- clustering on customer gives you the speedup
INDEX(id)  -- this keeps AUTO_INCREMENT happy

建议您查看pt-online-schema-change进行所有这些更改。但是，我不知道它是否可以在没有显式PRIMARY KEY的情况下工作。

“为每个客户提供单独的表”？不。这是一个普遍的问题。答案是肯定的。我不会重复所有没有100K表的原因。

分片

“共享”正在将数据拆分到多台计算机上。

要进行分片，您需要将代码放在某个地方，该代码可以查看domain并确定哪个服务器将处理该查询，然后将其交出。如果存在写扩展问题，建议使用分片。您没有提到这种情况，因此尚不清楚是否建议使用分片。

在对domain(或domain_id)进行分片时，可以使用(1)哈希来选择服务器，(2)字典查找(10万行)，或(3)混合。

我喜欢混合-将其散列为1024个值，然后查看1024行表以查看具有数据的计算机。由于添加新的分片并将用户迁移到其他分片是主要任务，因此我认为混合是合理的折衷方案。查找表需要分发给将操作重定向到分片的所有客户端。

如果您的“写作”用完了，请参阅high speed ingestion以了解加快该写作的可能方法。

分区
PARTITIONing正在跨多个“子表”拆分数据。

只有limited number of use cases，分区可以为您带来任何性能。您没有指出任何适用于您的用例的内容。阅读该博客，看看您是否认为分区可能有用。

您提到了“按日期范围划分”。大多数查询会包含日期范围吗？如果是这样，则建议这样的分区。 (有关最佳实践，请参见上面的链接。)我想到了其他一些选择:

计划A:PRIMARY KEY(domain_id, stats_time, id)但这很庞大，并且每个二级索引都需要更多开销。 (每个二级索引都静默包含PK的所有列。)

计划B:让stats_time包含微秒，然后调整值以避免出现延迟。然后使用stats_time而不是id。但这需要增加一些复杂性，尤其是当有多个客户端插入数据时。 (如果需要，我可以详细说明。)

方案C:拥有一个将stats_time值映射到ID的表。在进行实际查询之前先查找id范围，然后同时使用WHERE id BETWEEN ... AND stats_time ...。 (同样，凌乱的代码。)

汇总表

是否存在许多以日期范围内的事物计数形式的查询？建议有可能基于每小时的汇总表。 More discussion。
COUNT(DISTINCT sid)尤其难以折叠成汇总表。例如，不能将每个小时的唯一计数加在一起以获得当天的唯一计数。但是我也有一个technique。

关于mysql - MySql-处理表的大小和性能，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/34717787/

小和 mysql code br DEFAULT database query-performance

有关mysql - MySql-处理表的大小和性能的更多相关文章

ruby - 如何指定 Rack 处理程序 - 2
Rackup通过Rack的默认处理程序成功运行任何Rack应用程序。例如:classRackAppdefcall(environment)['200',{'Content-Type'=>'text/html'},["Helloworld"]]endendrunRackApp.new但是当最后一行更改为使用Rack的内置CGI处理程序时，rackup给出“NoMethodErrorat/undefinedmethod`call'fornil:NilClass”:Rack::Handler::CGI.runRackApp.newRack的其他内置处理程序也提出了同样的反对意见。例如Rack
使用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
Ruby-vips 图像处理库。有什么好的使用示例吗？ - 2
我对图像处理完全陌生。我对JPEG内部是什么以及它是如何工作一无所知。我想知道，是否可以在某处找到执行以下简单操作的ruby代码:打开jpeg文件。遍历每个像素并将其颜色设置为fx绿色。将结果写入另一个文件。我对如何使用ruby-vips库实现这一点特别感兴趣https://github.com/ender672/ruby-vips我的目标-学习如何使用ruby-vips执行基本的图像处理操作(Gamma校正、亮度、色调……)任何指向比“helloworld”更复杂的工作示例的链接——比如ruby-vips的github页面上的链接，我们将不胜感激!如果有ruby-
ruby-on-rails - 无法安装 mysql2 0.3.14 gem - 2
我看到其他人也遇到过类似的问题，但没有一个解决方案对我有用。0.3.14gem与其他gem文件一起存在。我已经完全按照此处指示完成了所有操作:https://github.com/brianmario/mysql2.我仍然得到以下信息。我不知道为什么安装程序指示它找不到include目录，因为我已经检查过它存在。thread.h文件存在，但不在ruby目录中。相反，它在这里:C:\RailsInstaller\DevKit\lib\perl5\5.8\msys\CORE\我正在运行Windows7并尝试在Aptana3中构建我的Rails项目。我的Ruby是1.9.3。$gemin
ruby - Faye WebSocket，关闭处理程序被触发后重新连接到套接字 - 2
我有一个super简单的脚本，它几乎包含了FayeWebSocketGitHub页面上用于处理关闭连接的内容:ws=Faye::WebSocket::Client.new(url,nil,:headers=>headers)ws.on:opendo|event|p[:open]#sendpingcommand#sendtestcommand#ws.send({command:'test'}.to_json)endws.on:messagedo|event|#hereistheentrypointfordatacomingfromtheserver.pJSON.parse(event.d
Ruby 的数字方法性能 - 2
我正在使用Ruby解决一些ProjectEuler问题，特别是这里我要讨论的问题25(Fibonacci数列中包含1000位数字的第一项的索引是多少？)。起初，我使用的是Ruby2.2.3，我将问题编码为:number=3a=1b=2whileb.to_s.length但后来我发现2.4.2版本有一个名为digits的方法，这正是我需要的。我转换为代码:whileb.digits.length当我比较这两种方法时，digits慢得多。时间./025/problem025.rb0.13s用户0.02s系统80%cpu0.190总计./025/problem025.rb2.19s用户0.0
ruby - Ruby 性能中的计时器 - 2
我正在寻找一个用ruby演示计时器的在线示例，并发现了下面的代码。它按预期工作，但这个简单的程序使用30Mo内存(如Windows任务管理器中所示)和太多CPU有意义吗？非常感谢deftime_blockstart_time=Time.nowThread.new{yield}Time.now-start_timeenddefrepeat_every(seconds)whiletruedotime_spent=time_block{yield}#Tohandle-vesleepinteravalsleep(seconds-time_spent)iftime_spent
ruby - 如何使用 Ruby HTTP::Net 处理 404 错误？ - 2
我正在尝试解析网页，但有时会收到404错误。这是我用来获取网页的代码:result=Net::HTTP::getURI.parse(URI.escape(url))如何测试result是否为404错误代码？最佳答案像这样重写你的代码:uri=URI.parse(url)result=Net::HTTP.start(uri.host,uri.port){|http|http.get(uri.path)}putsresult.codeputsresult.body这将打印状态码和正文。
ruby - 如何使用 ruby mysql2 执行事务 - 2
我已经开始使用mysql2gem。我试图弄清楚一些基本的事情——其中之一是如何明确地执行事务(对于批处理操作，比如多个INSERT/UPDATE查询)。在旧的ruby-mysql中，这是我的方法:client=Mysql.real_connect(...)inserts=["INSERTINTO...","UPDATE..WHEREid=..",#etc]client.autocommit(false)inserts.eachdo|ins|beginclient.query(ins)rescue#handleerrorsorabortentirelyendendclient.commi
ruby-on-rails - 如果条件与 &&，是否有任何性能提升 - 2
如果用户是所有者，我有一个条件来检查说删除和文章。delete_articleifuser.owner?另一种方式是user.owner?&&delete_article选择它有什么好处还是它只是一种写作风格最佳答案性能不太可能成为该声明的问题。第一个要好得多-它更容易阅读。您future的自己和其他将开始编写代码的人会为此感谢您。关于ruby-on-rails-如果条件与&&，是否有任何性能提升，我们在StackOverflow上找到一个类似的问题：

mysql - MySql-处理表的大小和性能

有关mysql - MySql-处理表的大小和性能的更多相关文章

随机推荐