手机号 mysql 索引_每日一面 - mysql中，我存十亿个手机号码，考虑存储空间和查询效率，怎么设计？...

首先提出假设：

1. 手机号码不会更新，只会插入和删除。

2. 查询包括精确查询某个手机号是否存在，以及获取某一号码段的所有手机号

假设表只有一个字段，就是手机号 phone，并且**设置为主键**。如果不设置主键并且没有唯一索引，InnoDB 会给我们自动生成一个隐藏主键列，浪费空间。

## MyISAM or InnoDB

如果插入和删除并不频繁，手机号是提前载入的字典表，而不是用户主动注册而产生的，则 MyISAM 看上去比 InnoDB 要好。因为 MyISAM 不涉及事务，更新都是表级锁。如果是用户触发的插入和删除，则需要用 InnoDB。

## 字段类型

考虑三种类型，BigInt，Char，Varchar

这几种类型在 InnoDB 引擎下默认行格式的存储方式为：

- 对于 bigint 类型，如果**不为 NULL，则占用8字节**，首位为符号位，剩余位存储数字，数字范围是 -2^63 ~ 2^63 - 1 = -9223372036854775808 ~ 9223372036854775807。**如果为 NULL，则不占用任何存储空间**。

- 对于**定长字段**，不需要存长度信息直接存储数据即可，如果不足设定的长度则补充。对于 char 类型，补充 0x20， 对应的就是空格。

- 数据开头有可变长度字段长度列表，所以 varchar 只需要保存实际的数据即可，不需要填充额外的数据。正是**由于这个特性，对于可变长度字段的更新，一般都是将老记录标记为删除**，在记录末尾添加新的一条记录填充更新后的记录。这样提高了更新速度，但是增加了存储碎片。

由于手机号**不更新**，并且不同国家的手机号**长度不同**，并且可能有特殊字符，字符类型在默认的编码和排序规则下进行范围匹配也能满足我们的需求，所以为了**节省空间**，使用 varchar 类型。

## 索引类型

由于涉及到范围查询，所以最好不用 Hash 索引，而是用默认的 B+ 树索引

## 分区

这个数据量比较大了，需要用分区。phone 可以作为分区键，可以按照**范围分区**，例如：

```

PARTITION BY RANGE COLUMNS( phone ) (

PARTITION p0 VALUES LESS THAN ('13100000000'),

PARTITION p1 VALUES LESS THAN ('13200000000'),

。。。。

PARTITION pn VALUES LESS THAN MAXVALUE

);

```

也可以按照 **hash 分区**，例如：

```

PARTITION BY HASH( phone )

PARTITIONS 64;

```

这样查询某个手机号是否存在这种业务就能更快，因为一张表被划分成了很多张小表。并且如果涉及多张小表 MySQL 还可以多线程并发查，效率提升很多。如果考虑获取某一号码段的所有手机号，那最好还是按照范围分区，可以使逻辑查询范围更小。但是 hash 分区数据可能比范围分区更加均衡。

注意，对于 HASH 分区个数最好是 2^n。因为**对于 2^n 取余相当于对 2^n - 1 取与**运算，增加了查询时的计算分区的效率

## 进一步优化

对于查询某个手机号是否存在，可以在数据库上层加一层**布隆过滤器**，提高效率。

同时为了提高准确性，可以通过号码号段，不同号段使用不同的**布隆过滤器**。在插入数据库的同时，放入布隆过滤器中。如果布隆过滤器中检测不存在，则肯定不存在。为了减少布隆过滤器的误判概率，可以使用更多的布隆过滤器，同时设置交叉范围，例如一个 13000000000~13200000000 用布隆过滤器 A，13100000000~13300000000 用布隆过滤器 B， 13211111111就要经过布隆过滤器 A 和 布隆过滤器 B 的验证。