主页 / dba / 问题 / 16580
Accepted
Putnik
Asked: 2012-04-17 09:52:01 +0800 CST

GUID 作为 MySQL 导入缓慢的原因

  • 772

我们遇到 MySQL 恢复数据库非常慢：15 MB 转储约 3 分钟。这是具有大量 RAM 的新专用服务器。

我们被告知最可能的原因是 GUID 在许多表中被用作主键：

CREATE TABLE `acl_actions` (
  `id` char(36) NOT NULL,
  `date_entered` datetime NOT NULL,
  `name` varchar(150) default NULL,
  ........
  `category` varchar(100) default NULL,
  `deleted` tinyint(1) default '0',
  PRIMARY KEY  (`id`),
  KEY `idx_aclaction_id_del` (`id`,`deleted`),
  KEY `idx_category_name` (`category`,`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

说明：

这肯定会影响批量插入性能，因为在转储导入的情况下，保存索引的 MYI 文件将执行随机 I/O，而不是在单调递增整数的情况下它会收到顺序 I/O 的提升。您的 key_buffer_size 也可以发挥作用，但在这种情况下，根据免费的密钥块和使用的水印密钥块，它似乎并没有过小。

我对那个人不太确定，而且我自己也没有足够的经验，所以我不得不在这里问：为什么会有随机IO？因为 id 几乎是随机的，所以索引的内容不会那么线性？

是否有可能因为 GUID 而大幅降低性能？除了将 UID 全局替换为自动增量 ID 之外，是否有任何解决方法？

UPD：毕竟我们找到了缓慢的主要原因：binlog。我们禁用了 binlog，这减少了 12 倍的导入时间！

mysql restore

1 个回答

  1. Best Answer

    SIZE MATTERS毫无疑问，陈词滥调适用于此。

    较小的键会比较大的键更快地加载到表中（这在 InnoDB 中变得更加明显）。索引中的列越大，BTREE 页中的键就越少。因此，你会得到更高的 BTREE 树。即使 BTREE 的高度为 3 或更高（这对于小表来说是不利的）也是具有大列的结果。这同样适用于名称（最多 150 个字符）和类别（最多 100 个字符）字段。将所有列设为 CHAR 而不是 VARCHAR 会提高 SELECT 性能，但是磁盘空间的显着增加 + BTREE 节点管理会随着表的增长而降低 SELECT 性能。

    查看您所有的键，我可以看到该表在索引大小与表大小方面非常不平衡。

    请运行此查询

    SELECT data_lenth,index_length
FROM information_schema.tables
WHERE table_name = 'acl_action';

    您将看到所有索引页的总和与表一样大或更大。从物理的角度来看，文件 acl_actions.MYI 会比 acl_actions.MYD 大。

    这是随机 I/O 发挥作用的地方：注意你的索引idx_aclaction_id_del

    请注意，您将其定义为

    KEY `idx_aclaction_id_del` (`id`,`deleted`),

    如果您决定查询已删除的记录，由于该索引中列的顺序，您将进行全索引扫描。

    KEY `idx_aclaction_del_id` (`deleted`,`id`),

    这样所有删除的键都被组合在一起。同样，所有未删除的行都组合在一起。

    尝试像这样更改表格设计

    CREATE TABLE `acl_actions` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `guid` char(36) NOT NULL,
  `date_entered` datetime NOT NULL,
  `name` varchar(150) default NULL,
  ........
  `category` varchar(100) default NULL,
  `deleted` tinyint(1) default '0',
  PRIMARY KEY  (`id`),
  UNQIUE KEY  (`guid`),
  KEY `idx_aclaction_id_del` (`deleted`,`id`),
  KEY `idx_category_name` (`category`,`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

    您应该运行以下查询

    SELECT name,categrory FROM acl_actions PROCEDURE ANALYSE();

    这将为具有给定数据集的那些列推荐正确的大小。

    • 2

相关问题