[MySql]聊一聊mysql表结构设计的问题

突然想起和一个朋友谈论的一个关于数据库表设计的问题，感觉蛮有趣的，顺便也记录一下；问题如下：
以一个月为一个周期，存这一个月内的用户信息，包括姓名，年龄，电话....，用什么方式存数据，存起来快，查的时候也快呢？

问题分析

这个问题理解起来很简单，可以代入场景，例如每个月的会员充值记录，那么我们要如何来设计这个表结构，能满足业务又能满足存储快，查询快的要求呢？

业务场景

像这种数据，常见的业务场景就是根据月份统计充值金额，充值人数之类的；这种场景下，设计表需要考虑数据量的问题，在量少的情况下，我们其实直接一张表存储数据即可，但是很多时候，我们都是需要考虑以后的扩容问题，所以还是要分表，那要不要考虑分库呢？看业务吧，如果数据量分分钟上亿，那就很有必要的，不然还是考虑分表即可；

库表设计

分表，要考虑两个问题：

1.分表的数量；

如果是为了保证以后的扩容，也就是表数量的扩容，例如我刚开始建立了5张表，现在想扩张到10张表，那么就需要使用一致性哈希的算法来计算分表的策略，否则就会有问题，例如我们使用一个简单的方式来作为分表策略：

// userId => 1001,1002... table => User0, User1...
// 例如传入参数 1006，那么获得的table下标就是1了，对应的表就是User1
private static final int TABLE_COUNT = 5;
private static int getTableIndex(int userId) {
    return userId % TABLE_COUNT;
}

但是这种方式就会存在问题，也就是如果TABLE_COUNT改成了10，那么传入参数1006的时候，对应的表就会变成User6了；也就是和之前数据所在的表不符，这样也就无法正确地操作数据了；一致性哈希算法就能保证不管TABLE_COUNT的值如何变化，都能保持数据操作的正确性；(有兴趣可自行了解，这里不做过多介绍)不过我们暂时不考虑扩容的问题，直接分表100张吧；

2.用什么作为分表的依据

假设我们的表结构如下：

CREATE TABLE User0 (
    `UserId` int(11) NOT NULL COMMENT '用户ID',
    ...
    `CreateTime` DATETIME NOT NULL COMMENT '充值时间'
)

其实还是要考虑业务情况，例如我们总是需要根据时间对数据进行处理，例如统计每月的数据，那我们就只能可以按年月来分表，否则数据会分散，做统计处理的时候就难搞了-.-，那如果我们总是根据用户来进行查询，例如查询某个用户今年充值了几个月啊，都充值了多少钱啊，那么自然需要根据UserId来进行分表了；分表对数据统计带来的麻烦是比较头疼的，所以数据量不大还是单表舒服点。

如何建立索引

(以下谈论建立在单表情况下)
为了保证查询的高效性，索引必须建的好，区分度要高，那我们应该尽量把查询数据时用到的WHERE条件作为索引，能建立唯一索引自然更好。在这个例子中该如何选择呢？

CREATE TABLE User0 (
    `UserId` int(11) NOT NULL COMMENT '用户ID',
    ...
    `CreateTime` DATETIME NOT NULL COMMENT '充值时间'
)

假设表结构如上，基本上一张用户表都会至少包含上面的两个元素，其实我们就可以根据UserId和CreateTime来作为唯一索引，为什么不用UserId或者单独再建一个冗余列作为唯一索引呢？
1.每个用户的充值数据不可能只有一条，所以UserId肯定是不唯一的，所以没法直接作为唯一索引；
2.上面说过业务场景，基本都是会用到充值时间作为查询条件，所以应该尽可能对该列创建索引
这里又引申出一个小问题，这个联合索引应该怎么写？

方案1：ALERT TABLE User0 ADD UNIQUE INDEX UserIdAndCreateTimeIndex （`UserId`, `CreateTime`);
方案2：ALERT TABLE User0 ADD UNIQUE INDEX CreateTimeAndUserIdIndex （`CreateTime`, `UserId`);

这两种方案的区别就在于联合索引中CreateTime列和UserId列的顺序问题；这有什么影响呢？
我们需要知道一个概念，就是Mysql的最左前缀匹配原则，假设我们使用方案1，从这个例子出发，简单地说就是SELECT * FROM User0 WHERE UserId=?;这样查询会使用到我们建立的唯一索引，但是SELECT * FROM User0 WHERE CreateTime=?;这个查询就用不上了；
所以从业务出发，我们在做查询统计的时候，多半是会针对CreateTime进行查询，所以我们应该选择方案2，让更多情况下的查询匹配上我们的唯一索引，提高sql执行的效率；
那么我们能不能单独对CreateTime或者UserId做索引呢？其实也不是不行，但是生效不大，因为CreateTime和UserId的区分度可能不会很高，反而是数据库还要维护这个索引树，导致数据插入的时候效率变差。

[MySql]聊一聊mysql表结构设计的问题