【重难点】【MySQL 09】面试场景题_mysql场景题

【重难点】【MySQL 09】面试场景题

1.有人建议给每张表都建一个自增主键，这样做有什么优点跟缺点

优点

1. 数据库自动编号，速度快，而且是增量增长，按顺序存放，对于检索非常有利
2. 数字型，占用空间小，易排序，在程序中传递也方便
3. 如果通过非系统增加记录时，可以不用指定该字段，不用担心主键重复问题

它的缺点其实来自于它的优点

1. 因为自动增长，在需要手动插入指定 ID 的记录时会显得麻烦，尤其是当前系统与其他系统集成时，需要数据导入时，很难保证原系统的 ID 不发生主键冲突（前提是老系统也是数字型的）。特别是在新系统上线时，新旧系统并行存在，并且是异库异构的数据库的情况下，需要双向同步时
2. 在系统集成或切割拼接时，为了区分新旧数据，可能想在老数据主键前统一加一个字符标识（例如用 ”o“ 表示 old）来表示这是老数据，那么自动增长的数字型又面临一个挑战

2.平时怎么写数据库的模糊查询

使用 LIKE

SQL 的模式匹配允许使用 ”_“ 匹配任何单个字符，而 ”%“ 匹配任意数目字符（包括零个）。在 MySQL 中，SQL 的模式缺省时忽略大小写的

需要注意是否走索引的问题，MySQL 在使用 LIKE 查询的时候，只有不以 % 开头的时候才会使用到索引，详细原因见【重难点】【MySQL 03】索引的分类和用法、复合索引详解、全文索引详解、聚簇索引和非聚簇索引详解 里的最左前缀原则

全文索引

使用方法详见【重难点】【MySQL 03】索引的分类和用法、复合索引详解、全文索引详解、聚簇索引和非聚簇索引详解

全文搜索引擎

例如 Elasticsearch，博主并没有学习使用过，只是告诉大家有这种方法可以进行模糊查询

3.数据库里有 1000 万条用户信息，需要给每位用户发送信息（必须发送成功），要求节省内存，如何设计消息表

实际上是设计一个百万级大用户量网站的站内信群发数据库，如果使用正常的方法实现，庞大的数据量会让消息表撑爆，即使分区也无济于事

我们先一步一步从几百个用户量的企业内部网站开始设计站内信系统

面对这样的用户量，我们不需要考虑消息数据量太大的问题，所以按照怎么方便怎么来的原则，群发就每人复制一条消息数据，这样用户可以自己管理自己的消息，可以非常方便地进行已读、删除等操作

表 T_Message

这样，用户接收自己的消息时只要做如下查询

SELECT * FROM T_Message WHERE ReceiverId = thisId
1

这种方法很简单，也是最容易想到的，对于几百个用户量地情况这样的设计也确实足够了

几千到几万的用户量

这样的用户量如果勉强要用上面那种设计也是可以的，只是 T_Message 可能要考虑分区。此外，消息正文复制对于这样的用户量来讲空间浪费太多，因为考虑到接收者一般是无法修改消息正文的，所以我们可以让所有接收者共享一条消息正文。具体数据库设计方法和上面大同小异，只是把消息正文字段改为消息正文 ID，并把消息正文存放在另外一张表里

表 T_Message

表 T_MessageText

这样就大大节省了消息的存储空间，但是查询的时候就稍微麻烦一点，需要进行联合查询

SELECT T_Message.*,T_MessageText.* 
FROM T_Message
INNER JOIN T_MessageText
ON T_Message.MessageTextId=T_MessageText.Id
WHERE T_Message.ReceiverId=thisId
1
2
3
4
5

这种方法除了不能随便删除消息正文外，用户仍然可以自己管理自己的消息

百万级用户量

当用户了达到百万级，我们要考虑的就不只是消息正文的存储空间了，我们还要考虑表记录条数。很容易想象，一千万个用户，向每个人群发一条消息，那么消息表中就要存放一千万条消息记录。群发两条信息就要两千万条消息记录，这显然不现实

作为设计者，可能不仅仅要从技术的角度去考虑这个问题，更要从用户实际情况去着手寻找解决问题的办法。这里有一个概念叫 ”活跃用户“，即经常登录网站的用户，相对于哪些一时冲动注册而接下来又从来不登录的用户来说，活跃用户对网站的忠诚度更高，从商业的角度来看，忠诚的客户享受更好的服务

根据这个思路，我们来探索一种方法。假设这 1000 万用户，其中活跃用户为 100 万。那么如何判断活跃用户呢？其实不用判断，我们只需要保证用户在登录后能收到群发消息即可，对于那些再也不登录的用户，自然就不会收到消息，也不需要在消息表中添加记录。因此，我们在群发消息的时候，可以在消息表中插入一条记录，并用一个群发标志表示这是一条群发消息。这样，用户在登录后只要查询群发消息就可以看到这条消息了。但是用户需要有消息的控制权，所以必须要将这条群发消息拷贝一份。要达到这个目的，我们不仅仅要在用户登录时查询群发消息，还要查询群发消息是否已经拷贝了一份，如果没有拷贝，则拷贝一份插入消息表，将这条新记录的群发标识改为原始群发消息的 Id。如果已经存在原始群发消息的拷贝，则什么都不做。这样的话，我们只需要为在发送群发消息之后登录过的用户消耗空间

表 T_Message

表 T_MessageText

4.五百万数字排序，内存只能容纳 5 万个，如何排序，如何优化

归并排序

1. 将 500 万的数字分成 40 个有序文件，分别在内存中排序，然后对这 40 个有序文件进行归并排序
2. 读取每个文件中第一个数（最小数），存放在一个大小为 40 的数组 data 中
3. 选择数组中最小的数 min_data，及其相应的文件索引（来自哪个文件）index
4. 将 min_data 写入到文件 result，然后更新数组 data（根据 index，读取该文件的下一个数代替 min_data）
5. 如果该文件读取完毕，则在 data 数组中其余 39 个元素中找到最小的数及其相应的文件索引，不要将其写入到文件 result，只需要用其文件的下一个数代替 min_data，如果该文件也读取完毕，则在 data 数组中其余 38 个元素中找到最小的数及其相应的文件索引，不要将其写入到文件 result，只需要用其文件的下一个数代替 min_data，如果该文件也读取完毕，则以此类推直到找到为止，然后重复第 3 步；如果全部文件均读取完毕则进入下一步
6. 将数组中剩余 39 个元素排好序写入文件 result

位图法

对这些数进行位图排序，只需要约 5000000/8 = 625000byte，就是 0.625M，排序后输出

但是该方法有局限性，需要大致知道数据的范围，而且数据不能重复

5.一个表，字段有 ID、姓名、分数，要求查出平均分大于 80 的 ID，按照分数降序排序

select id from table group by id having avg(score) > 80 order by avg(score) desc
1

这是一个分组排序问题，参考【重难点】【MySQL 02】SQL语句执行过程、COUNT(常量)、COUNT(*) 与 COUNT(列名) 的对比、多表查询的连接概念、将查询结果分组并排序