主页 / dba / 问题 / 3255
Accepted
David Cournapeau
Asked: 2011-06-11 02:29:15 +0800 CST

估计mysql中行访问的分布

  • 772

我试图了解一个相当大的表的“热数据”部分的大小，我想知道这是否可以直接在 mysql 中完成。我知道使用 percona 版本的 mysql，我可以访问诸如“每个表访问的行数”之类的数字，但我实际上需要这些数据是基于每行的（例如，id 为 1 的行被读取了 200 次，行id 2 被读取了 300 次，其中 id 是自增列）

mysql statistics

2 个回答

  1. 如果您想要每个 id 的这种精细统计信息，听起来您可能想尝试一些复杂的方法来收集此类信息。让我们探讨一下这个场景：

    如果您在 wp 数据库中有一个具有以下布局的表：

    CREATE TABLE `wp_posts` (
  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `post_author` bigint(20) unsigned NOT NULL DEFAULT '0',
  `post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_content` longtext NOT NULL,
  `post_title` text NOT NULL,
  `post_excerpt` text NOT NULL,
  `post_status` varchar(20) NOT NULL DEFAULT 'publish',
  `comment_status` varchar(20) NOT NULL DEFAULT 'open',
  `ping_status` varchar(20) NOT NULL DEFAULT 'open',
  `post_password` varchar(20) NOT NULL DEFAULT '',
  `post_name` varchar(200) NOT NULL DEFAULT '',
  `to_ping` text NOT NULL,
  `pinged` text NOT NULL,
  `post_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_modified_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_content_filtered` text NOT NULL,
  `post_parent` bigint(20) unsigned NOT NULL DEFAULT '0',
  `guid` varchar(255) NOT NULL DEFAULT '',
  `menu_order` int(11) NOT NULL DEFAULT '0',
  `post_type` varchar(20) NOT NULL DEFAULT 'post',
  `post_mime_type` varchar(100) NOT NULL DEFAULT '',
  `comment_count` bigint(20) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `post_name` (`post_name`),
  KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`ID`),
  KEY `post_parent` (`post_parent`),
  KEY `post_author` (`post_author`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    并且你想跟踪对该表的访问，你必须使用一系列结合使用触发器、黑洞表、MySQL 复制和“社会责任”编码的操作来强制记录表的 ID。

    触发器

    触发器遵循任何 INSERT、UPDATE 和 DELETE 的过程。在这种情况下，您将需要三 (3) 种类型的触发器：1) INSERT 之后，2) UPDATE 之后，3) DELETE 之后。

    每次 wp_posts 有 INSERT、UPDATE 或 DELETE 时，将 ID 记录在另一个表中。什么样的表？？？

    黑洞表

    让我们创建一个表来记录 ID

    CREATE TABLE wp_posts_idtracker
(
  `ID` bigint(20) unsigned NOT NULL,
  `DTSTAMP` datetime NOT NULL
) ENGINE=BLACKHOLE;

    现在这里是记录 ID 访问的触发器：

    CREATE TRIGGER wp_posts_insertafter AFTER INSERT ON wp_posts FOR EACH ROW
INSERT INTO wp_posts_idtracker VALUES (NEW.ID,NOW());

CREATE TRIGGER wp_posts_updateafter AFTER UPDATE ON wp_posts FOR EACH ROW
INSERT INTO wp_posts_idtracker VALUES (OLD.ID,NOW());

CREATE TRIGGER wp_posts_deleteafter AFTER DELETE ON wp_posts FOR EACH ROW
INSERT INTO wp_posts_idtracker VALUES (OLD.ID,NOW());

    等一下 ！！！表 wp_posts_idtracker 是一个BLACKHOLE表。它什么都不存储。那么统计数据写在哪里？？？确保带有 wp_posts 的 MySQL 实例启用了二进制日志记录。重要的是，统计信息被写入二进制日志。您如何阅读这些统计数据？？？

    MySQL复制

    使用商用硬件，员工使用 MySQL Replication Slave。设置奴隶只接受一个表：wp_posts_idtracker。将此行放在从站的 /etc/my.cnf 中：

    [mysqld]
replicate-do-table=wp.wp_posts_idtracker

    由于这个BLACKHOLE表将被复制到从站，它将如何存储数据？？？在从机上将其转换为 MyISAM。此外，通过 ID 和 DTSTAMP 以及 DTSTAMP 对其进行索引：

    use wp
ALTER TABLE wp_posts_idtracker ENGINE=MyISAM;
ALTER TABLE wp_posts_idtracker ADD INDEX (ID,DTSTAMP);
ALTER TABLE wp_posts_idtracker ADD INDEX (DTSTAMP);

    现在 Master 会简单地将每次访问 wp_posts 中的一行记录到二进制日志中。MySQL Replicaton 负责将其记录到 Slave。作为使用单独的服务器记录此信息的替代方法，您可能希望在端口 3307 上创建第二个 MySQL 实例，并让它作为运行在端口 3306 上的 MySQL 的从属服务器。您必须确保端口上的 MySQL 的 datadir 3307 与端口 3306 上的 MySQL 数据卷不同。另一种变体是使用MEMORY 存储引擎更改从属服务器上 wp_posts_idtracker 的存储引擎以减少磁盘 I/O（注意：如果使用MEMORY 存储引擎对于表 wp_posts_idtracker，请记住将索引设为 BTREE 而不是默认的 HASH 索引，因为针对 HASH 索引表运行范围查询的性能非常糟糕，即使对于MEMORY表也是如此）。还有一种变体是将二进制日志放在 RAM 磁盘中以实现更快的复制，或者将中继日志也放在 RAM 磁盘中，同时进一步减少磁盘 I/O。

    到目前为止，涉及 INSERT、UPDATE 和 DELETE 的 ID 都安全地存储在 Replication Slave 中。我们是否忘记了对 wp_posts 的任何其他类型的访问？？？哦，是的，SELECT 语句。我们如何记录 SELECT？？？在任何已知的 RDBMS 中都没有 SELECT 触发器。我们如何处理 SELECT？？？

    “对社会负责”的编码

    由于没有用于 SELECT 的特殊机制，开发人员必须自行负责获取在 SELECT 查询中遇到的 ID，并将它们简单地插入到表 wp.wp_posts_idtracker 中。假设您请求的 ID 来自大量 ID。将它们批量发送到 wp.wp_posts_idtracker：

    SELECT * FROM wp_posts WHERE ...
INSERT INTO wp.wp_posts_idtracker SELECT IDs,NOW() FROM wp_posts WHERE ...

    不要担心 INSERT 查询会导致磁盘 I/O 访问 ID。应该在 SELECT 之后缓存这些 ID。如果 wp_posts 是 MyISAM，键将缓存在键缓存中（大小由 key_buffer_size 决定）。如果 wp_posts 是 InnoDB，键将缓存在 innodb 缓冲池中（大小由 innodb_buffer_pool_size 决定）。

    概括

    一旦你使用了这个相当独特的基础设施，你就可以简单地连接到从站并读取你的统计数据。

    这只是一个示例，说明如何为表 ID 创建所需的统计记录类型。您可能有其他想法。不要害怕尝试它们。始终确保您知道在记录统计数据时您尝试实现的每个里程碑的后果。

    试试看 ！！！

    我告诉过你这会很复杂！！！

    • 4
  2. Best Answer

    创建另一个表并使用 DML 触发器记录插入、更新和删除。

    1. 因此，假设您有一个要跟踪的表 A。
    2. 创建另一个表，例如 tbl_row_stats。
    3. 在表 A 上创建用于插入、更新和删除的 DML 触发器。即，每当表 A 发生这三个操作中的任何一个时，在 tbl_row_stats 中插入一行，其中包含表 A 的相应行 ID 和发生的操作。
    4. 稍后您可以使用 group by 子句进行选择，以查看每行每个操作发生的次数。

    为了跟踪选择，您必须将其添加到您的应用程序代码（或存储过程）中。即，无论何时从应用程序中完成选择，都必须完成对 tbl_row_stats 的相应插入。

    • 4

相关问题