本系列文章主要是本人在游戏服务端开发过程中，遇到的一些不那么为人熟知但我又觉得比较重要的MySQL知识的介绍。希望里面浅薄的文字能为了提供一点点的帮助。
本章的目标是介绍我使用数据库过程中一些零碎的知识点，单独拎出来写不了太多的东西所以整合成一章。
在不影响线上数据库性能的情况下，如何知道一张表大概有多少行
有时候我们可能会通过一张表的数据量大致预估下一个业务的参与人数或者这个系统的使用程度。又或者将一个库下所有表的数据量进行分析，看看MySQL数据分布的情况。比如我想查询test库下所有表的行数(大概行数)可以用这条SQL：
SELECT `TABLE_NAME`,`TABLE_ROWS` FROM `information_schema`.`tables` WHERE `TABLE_SCHEMA`='test'
关于information_schema.tables表包含了MySQL所有表的一些基本情况，比如：大概行数、平均长度、最大长度等等。详细介绍请戳这里。该表的数据会定期进行统计。行数的统计方式也很有趣，是通过采样统计的方式来统计的 —— 从InnoDB表中随机选择N个数据页，算出这些页包含数据行数的平均值，然后用总页数乘以这个值就是了。当然如果需要准确确定一张表有多少行数据可以用SELECT COUNT(*) FROM TABLE_NAME这条命令进行，但是这样会对数据库产生性能影响(因为会遍历这个表的整个主键索引树)。
磁盘相关的确认(大小、类型)
确认磁盘空间和类型(机械硬盘、SSD)这事大家基本都会做。这里我先吐槽下某个云厂商 —— 有一次我们在压测的时候发现写入速率离SSD的写入速率差一大截。后面发现binlog日志刷盘是瓶颈，仔细询问才知道云厂商把binlog日志存在机械硬盘上。他们产品介绍的时候就说他们的MySQL用的是SSD磁盘，问他们binlog（binlog_format=ROW;binlog_row_image=Full）为什么这么慢，还想含糊其辞糊弄过去。这一点让我觉得很无耻。如果选择云厂商的MySQL产品，在使用之前要确认好binlog日志所在磁盘的大小和类型。
除了binlog磁盘的问题，还要额外确认的是MySQL使用的存储方式：本地SSD、云SSD（阿里还有一个ESSD），对于数据量和压力都比较大的项目组，这一点也是很重要的。
云服务对于冷备数据都会有免费空间赠送，要根据项目自己的实际情况规划下如何使用。比如数据量比较大的项目是不是考虑只冷备一天的数据，然后在过期前将冷备数据拉取出来放在本地服务器以避免购买云空间。
sys库的相关视图：
这里主要想介绍下sys库中比较实用的监控视图。
- metrics：数据库各种监控和统计
- schema_index_statistics：各个表索引使用情况
- schema_table_statistics：各个表的使用情况
自增主键的使用
下面这个建表语句，rank就是一个自增主键。
CREATE TABLE `TestTable` (
`rank` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`playerId` int(11) NOT NULL ,
`playInfoStr` varchar(255) DEFAULT NULL,
PRIMARY KEY (`rank`),
UNIQUE KEY `playerId` (`playerId`)
);
曾经有一位同事，就想通过自增主键来实现玩家排名的功能。他的猜想是：谁先首次写入TestTable表，谁的rank就靠前（即使后面更新玩家数据rank也是不变），且rank是连续增长的（每次加1）。写入和更新玩家的数据SQL是同一条，形如：replace into TestTable(name,playerId,playInfoStr) values('playerName',1,'78#12#13')。想法很美好，但实际有很的大问题。对于同一个玩家，可能会有多次更新数据的可能（playInfoStr字段）。但每一次用上面SQL更新，rank就会变化，这样和需求完全不一致。同时这里说明一点：对于自增主键，并不是每一次自增都是加1。在一些事务失败(比如死锁造成的回滚)情况下，自增主键可能加2。所以rank是连续增长的（每次加1）这一点自增主键也是实现不了的。
PS：自增主键每次的自增值（默认是1）是可以设置的。
MySQL原生分表：
一张表数据量过大要怎么处理 —— 这是游戏服务端经常要面临的问题。当某个游戏业务，一个玩家在一张表有上千条数据，我们就需要考虑分表（这里假设有100W玩家，这种表在游戏服务端很常见 - 比如任务、成就、物品等等）。MySQL只支持水平方向上的分表 —— 也就是不同的行可能分配在不同的物理分区中（不支持不同列在不同物理分区的垂直方向上的分表）。MySQL的分表实现方式是用户通过分区函数确认一行数据所在的分区，然后对这行数据进行操作（insert、update、select...）。分区函数是根据一行数据中一个或者多个字段来确认分区的，下面介绍下最常用的几个分表方式：
- 范围分表：
CREATE TABLE `employees` (
`id` int(11) NOT NULL,
`name` varchar(30) COMMENT '雇员名字',
`hired` date NOT NULL DEFAULT '1970-01-01' COMMENT '入职日期',
`job_code` int(11) NOT NULL COMMENT '工号'
)
PARTITION BY RANGE (`job_code`) (
PARTITION p0 VALUES LESS THAN (10000),
PARTITION p1 VALUES LESS THAN (20000),
PARTITION p2 VALUES LESS THAN (30000)
);
上面是一个公司的雇员表（这里只是做举例用，以这种数据量级完全没有必要使用分区），这张表就是根据job_code来进行分区的。当我们插入(1,'张三','2021-09-12',99)这条数据时，就会被分到p0这个分区（这个对于客户端来说是无感知的）。
我们可以在建表之后添加新的分区(30000<=job_code<40000的数据在p3分区)，语句如下：
ALTER TABLE `employees` ADD PARTITION (PARTITION p3 VALUES LESS THAN (40000));
通过范围分区有一些缺点：首先就是数据分布不均的问题。比如这张雇员表，对于大多数公司只会在p0分区才有数据（公司人数不超过10000人）；其次如果分区字段超过定义最大区间会报错，比如插入(1,'张三','2021-09-12',99999)这条数据时就会报：1526 - Table has no partition for value 99999的错误，当然这个问题可以通过添加一个无限大值分区来解决：ALTER TABLEemployeesADD PARTITION (PARTITION p4 VALUES LESS THAN MAXVALUE);但是这又回到了第一个问题上面，有可能导致大部分数据集中在p4分区上了。
范围分区还可以挑date类型进行，比如下面这样（具体到天也是可以的）:
CREATE TABLE `employees` (
`id` int(11) NOT NULL,
`name` varchar(30) COMMENT '雇员名字',
`hired` date NOT NULL DEFAULT '1970-01-01' COMMENT '入职日期',
`job_code` int(11) NOT NULL COMMENT '工号'
)
PARTITION BY RANGE( YEAR(hired) ) (
PARTITION d0 VALUES LESS THAN (2000),
PARTITION d1 VALUES LESS THAN (2010),
PARTITION d2 VALUES LESS THAN (2020),
PARTITION d7 VALUES LESS THAN MAXVALUE
);
- 列表分表：
CREATE TABLE `employees` (
`id` int(11) NOT NULL,
`name` varchar(30) COMMENT '雇员名字',
`hired` date NOT NULL DEFAULT '1970-01-01' COMMENT '入职日期',
`job_code` int(11) NOT NULL COMMENT '工号',
`sex` int(11) NOT NULL COMMENT '性别'
)
PARTITION BY LIST(`sex`) (
PARTITION pMan VALUES IN (1),
PARTITION pWoman VALUES IN (2),
PARTITION pUnkonw VALUES IN (3),
);
上面就是按照性别分区。sex=1就在pMan区；sex=2就在pWoman区；sex=3就在pUnkonw区。一般这种适用于字段是有限数值的，比如按照省份分区（东南西北各个地理位置的省份）。同样如果插入(1,'张三','2021-09-12',99,4)也是会报找不到分区的报错（sex=4不在任何一个定义的分区上）。
CREATE TABLE `employees` (
`id` int(11) NOT NULL,
`name` varchar(30) COMMENT '雇员名字',
`hired` date NOT NULL DEFAULT '1970-01-01' COMMENT '入职日期',
`job_code` int(11) NOT NULL COMMENT '工号'
)
PARTITION BY HASH(`job_code`) PARTITIONS 4;
上面就是根据job_code的值决定一行数据所在的分区（总分区数有4个），HASH分表很好解决了范围分表数据分布不均的问题。
学习资料推荐：
MySQL官方文档：https://dev.mysql.com/doc/refman/8.0/en/
阿里mysql月报：http://mysql.taobao.org/monthly/
一位大牛的MySQL资料：https://github.com/hedengcheng/tech/tree/master/database/MySQL
阿里云MySQL文档：https://help.aliyun.com/document_detail/95798.html
腾讯云MySQL文档：https://cloud.tencent.com/document/product/236
极客时间的《MySQL 45讲》（这个是付费的）：https://time.geekbang.org/column/article/67888
网友评论