Xupeng's blog

Feb 27, 2016 - 5 minute read - Comments

最容易被忽视的,是常识

同事最近在拆分数据库和清理数据,这当中有一些趣事,比如在拆分一个叫 luz 的集群时,新拆出的集群被命名为了 lua,可以从至少两个角度来理解这个名字,最显而易见的它是一门语言,除此之外它还代表从 z 到 a 的新一轮轮回;再比如拆分另一个叫 eag 的集群时,新集群被命名为 eager,不过它诞生时的含义其实是「eag 的 er 砸」。

也有一些哭笑不得的问题,比如发现有些应用的数据清理 cron 曾经有日子没被成功执行,导致数据文件的体积相比它应有的大小暴涨了一个数量级;还有比如发现 Sentrynodestore_node 表涨到了几百 GB,检查的时候看到 nodestore 的 schema:

CREATE TABLE `nodestore_node` (
    `id` varchar(40) NOT NULL,
    `data` longtext NOT NULL,
    `timestamp` datetime NOT NULL,
    PRIMARY KEY (`id`),
    KEY `nodestore_node_d80b9c9a` (`timestamp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

它都有哪些问题呢?

脱离业务场景,孤立地看这个 schema,除了这三点可疑之处外其实并没有太多值得挑剔的:

  1. Primary key 比较长(40 个字符),通常是一个不当设计的预警信号。
  2. 写入纪录的顺序和主键索引序可能不同,潜在的问题是影响写入性能,并可能导致数据页碎片化进一步影响读性能。
  3. timestamp 字段使用了 MySQL 的关键字。

那么再了解一点点业务,通过阅读 Sentry 这部分的代码可以了解到这些:

  1. id 要存储的原始数据是 UUID version 4,16 字节,但在存储之前用 base64 编码为可打印字符串,编码后的长度为 24 字节。
  2. data 所存储的数据是把结构化数据序列化,之后用 zlib 压缩,再把压缩之后的数据使用 base64 编码为可打印字符串。
  3. timestamp 字段是数据写入时的时间,这个字段的索引用于按时间删除旧数据。

考虑业务的需要,这个 schema 还有哪些问题呢? id 字段使用了 utf8 编码(在 MySQL 中是 1-3 字节长度的变长编码),最大长度是 40 字符,id 字段是主键,在索引中以最大宽度对齐,再加上 varchar 类型最大长度标示 2 字节,也就是 40 * 3 + 2 = 122 字节,不仅在主键中如此,在二级索引 nodestore_node_d80b9c9a 中也是如此。

那么怎么改进呢?

  1. 由于业务上 id 是 16 字节 的 UUID version 4,但编码为 24 字节的可打印字符串,因此在不修改业务代码的前提下,可以对业务透明地把 id 列的定义修改为 id char(24) CHARACTER SET latin1 NOT NULL,这样在索引中的宽度就可以从 122 字节减小为 24 字节。
  2. 如果对业务代码做小幅重构,可以使用 binary(16) 来存储 UUID version 4,可以进一步减小为 16 字节。
  3. data 字段所存储数据的长度大多分布在几 KB 到几十 KB 之间,以 20KB(20480) 为例,base64 编码之后的长度为 27308,比未编码数据增长了 33+%,如果可以牺牲对人类的可读性(实际上人类也直读不了 base64 编码的字符串),直接存储 zlib 压缩之后的二进制数据,就可以省掉这 33% 的空间浪费,这一部分的浪费在整个系统中所占的比重相当可观。
  4. 在 Sentry 的场景下,nodestore 是典型的写多读少,如果再加入一列自增列作为 primary key,把 id 改为有唯一性约束的二级索引,可以提升写入性能、减轻数据页的碎片化,同时还能进一步节省空间。
  5. 使用自增列(记作 dummy_id)作为 primary key 之后,在这个场景下 timestamp 字段实际上和 dummy_id 同序,按时间删除旧数据的需求就可以不必借助 timestamp 字段的索引,使用笨笨的二分法找到需要的时间边界,然后按对应的 dummy_id 作为条件来删除即可,每行纪录可以节省 24 字节。

做到这些有多难呢?

回顾一下上面所提到的问题和相应的优化手段,可以发现所需要的只是在理解业务的基础上,对数据库的工作方式也有基本的理解,但更实际的情况是,大量的产品开发者在编写 Model 代码时随手就会写下 max_length=255,完全没有意识到产生的列定义会是 varchar(255),或者压根儿没有意识到这么做会有什么影响,几年前我自己在做产品开发的工作时也是这样的习惯。相对来说,我愿意相信这里写了 max_length=40 而不是 max_length=255 是一个经过深思熟虑了的决定。

要做到对数据库和索引的工作方式有基本的理解,难吗?很多同学会觉得不就是读一读文档吗没有什么技术含量啊,事实也正是如此,只要能阅读和理解文档,加上必要的动手实践,都能有超越类似场景要求的理解,比掌握一门语言要简单太多,既不需要高于普通人的智商,也不需要加班加点地埋头苦干,所需要的仅仅是踏实的态度和一点耐心,再加上一点思考。

再举个例子

这是随手从某著名产品中摘出的 schema,就不在这里做更多的问题分析了,不过我相信仅仅依靠上面提到的几点常识,就能看到很多问题,这多多少少能进一步说明,优秀的产品一样会有糟糕的实现,但是,这些产品不都好好的是吗?是不是应该再思考一下技术的价值,以及技术和产品的关系呢?😂

CREATE TABLE `storage_blobs` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `oid` varchar(255) NOT NULL,
    `size` bigint(20) DEFAULT NULL,
    `created_at` datetime NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `index_storage_blobs_on_oid` (`oid`)
) ENGINE=InnoDB AUTO_INCREMENT=2580 DEFAULT CHARSET=utf8;

CREATE TABLE `users` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `login` varchar(255) NOT NULL,
    `crypted_password` varchar(40) DEFAULT NULL,
    `salt` varchar(40) DEFAULT NULL,
    `created_at` datetime DEFAULT NULL,
    `updated_at` datetime DEFAULT NULL,
    `remember_token` varchar(255) DEFAULT NULL,
    `remember_token_expires_at` datetime DEFAULT NULL,
    `wants_email` tinyint(1) DEFAULT '1',
    `disabled` tinyint(1) DEFAULT '0',
    `plan` varchar(255) DEFAULT NULL,
    `billed_on` date DEFAULT NULL,
    `auth_token` varchar(255) DEFAULT NULL,
    `upgrade_ignore` varchar(255) DEFAULT NULL,
    `upgrade_accept` int(11) DEFAULT NULL,
    `role` varchar(255) DEFAULT NULL,
    `billing_attempts` int(11) DEFAULT '0',
    `spammy` tinyint(1) DEFAULT '0',
    `last_ip` varchar(255) DEFAULT NULL,
    `plan_duration` varchar(255) DEFAULT NULL,
    `bouncing_email` tinyint(1) DEFAULT NULL,
    `billing_extra` text,
    `gift` tinyint(1) DEFAULT NULL,
    `type` varchar(255) DEFAULT 'User',
    `last_read_broadcast_id` int(11) DEFAULT NULL,
    `raw_data` blob,
    `referral_code` varchar(255) DEFAULT NULL,
    `billing_type` varchar(255) DEFAULT 'card',
    `braintree_customer_id` int(11) DEFAULT NULL,
    `bcrypt_auth_token` varchar(255) DEFAULT NULL,
    `suspended_at` datetime DEFAULT NULL,
    `organization_billing_email` varchar(255) DEFAULT NULL,
    `gravatar_email` varchar(255) DEFAULT NULL,
    `time_zone_name` varchar(255) DEFAULT NULL,
    `session_fingerprint` varchar(255) DEFAULT NULL,
    `token_secret` varchar(40) DEFAULT NULL,
    `require_2fa` tinyint(1) DEFAULT NULL,
    `restrict_oauth_applications` tinyint(1) DEFAULT NULL,
    `spammy_reason` varchar(255) DEFAULT NULL,
    `seats` int(11) NOT NULL DEFAULT '0',
    `split_diff_preferred` tinyint(1) NOT NULL DEFAULT '0',
    `custom_volume_discount` int(11) NOT NULL DEFAULT '0',
    `tenant_id` int(11) NOT NULL DEFAULT '1',
    PRIMARY KEY (`id`),
    UNIQUE KEY `unique_index_users_on_login` (`login`),
    UNIQUE KEY `unique_index_users_on_login_and_tenant_id` (`login`,`tenant_id`),
    KEY `index_users_on_billed_on` (`billed_on`),
    KEY `index_users_on_type` (`type`),
    KEY `index_users_on_referral_id` (`referral_code`),
    KEY `index_users_on_last_ip` (`last_ip`),
    KEY `index_users_on_plan_and_billing_type_and_type` (`plan`,`billing_type`,`type`),
    KEY `index_users_on_updated_at` (`updated_at`),
    KEY `index_users_on_spammy` (`spammy`),
    KEY `index_users_on_braintree_customer_id` (`braintree_customer_id`),
    KEY `index_users_on_suspended_at` (`suspended_at`),
    KEY `by_billing_email` (`organization_billing_email`),
    KEY `by_gravatar_email` (`gravatar_email`),
    KEY `index_users_on_role` (`role`),
    KEY `index_users_on_created_at` (`created_at`),
    KEY `index_users_on_tenant_id` (`tenant_id`)
) ENGINE=InnoDB AUTO_INCREMENT=198 DEFAULT CHARSET=utf8;

我昨天由这个问题吐槽了一下 Sentry,引来了一些讨论和建议,比如大句哥哥和 XTAo 说 Sentry 官方推荐用 PostgreSQL,不过就这个具体的问题来说,如此设计恐怕在 PostgreSQL 下也不是好的实践,更重要的不在于用什么,而是怎么用好它,话说回来,要把 Sentry 用好,也得对 Sentry 有更多的了解和理解才行。

最后,推荐几则不错的文档和书

Tags: mysql software architect

MySQL "replace into" 的坑 团结紧张,严肃活泼

comments powered by Disqus