《数据库原理》学习笔记，Mysql join到底该不该用？Mysql外键到底该不该用？Mysql分库分表相关知识点。

数据库原理

1. 关系数据结构

关系模型把数据表示为关系的集合，数据结构用单一的二维表结构来表示实体以及实体之间的联系

• 关系，一个关系对应一个二维表，二维表名就是关系名。二维表可以是原数据表，也可以是运算产生的表。关系由关系模式（表头）和关系实例（表数据）组成；
• 属性，二维表中的列，列值称为属性值，属性的个数称为关系的元或度
• 值域，属性值的取值范围称为值域
• 元组，二维表中的行，关系的一个实例
• 分量，元组中的一个属性值
• 码或键，唯一标识该元组的属性或者属性组。
• 超码或超键，在关系中移除某个属性值它仍然是这个关系的码或键，称为超码或超键
• 候选码，移除一个属性就不是码或键
• 主码，被指定用来唯一标识元组的某个候选码
• 全码，主码为所有属性的集合时称为全码
• 外码，关系中某个属性或属性组是另一个关系的主码称为外码
• 被参照关系，以外码为主码的关系，也称为主关系
• 参照关系，以外码为主码的关系

用集合论的观点定义关系：

• 关系是一个度为 K 的元组的有限集合
• 元组不是属性值的集合，因为元组的分量是有序的

2. 完整性约束

• 实体完整性，关系必须有主码，主码的值必须唯一，且所有属性不能为空
• 参照完整性，关系与关系的参照关系，关系内属性之间的参照关系
• 用户定义完整性，由用户根据需求定义，主要是对属性的取值进行限定，包括：数据类型、精度、取值范围、是否允许空值、是否有默认值等

3. 关系操作集合

一元的关系操作

• 选择：筛选符合条件的行。又称为限制，用于挑选出关系中满足给定条件的元组
• 投影：筛选符合条件的列。用于挑选关系中指定的若干列组成新的关系

二元的关系操作：

• 连接：把两个表做成一个表。用于从两个关系的笛卡尔积（交叉连接）中选取它们属性值满足指定条件的元组

自然连接（内连接）是一种特殊的等值连接，它要求两个关系中进行比较的分量必须是相同的属性组，并且在结果中把重复的属性列去掉。而等值连接并不去掉重复的属性列。

自然连接也可看作是在广义笛卡尔积 R×S 中选出同名属性上符合相等条件元组，再进行投影，去掉重复的同名属性，组成新的关系。

如果把舍弃的元组也保存在结果关系中，而在其他属性上空值，那么这种连接就叫外连接。如果只把左边关系 R 要舍弃的元组保留就叫左外连接，如果只把右边关系 S 中要舍弃的元组保留右外连接。

扩充的关系操作：

• 广义投影，广义投影是对投影的扩展。对关系 R 的投影操作，是在 R 的若干属性列上的投影。
• 赋值，对于两个相容的关系，可以直接把整个关系赋值给另一个
• 半连接，两个关系 R 和 S 的半连接是指它们的自然连接的结果在关系 R 上的投影。
• 聚集，指在指定的值集上进行数学聚集函数运算

4. 索引结构

索引支持对于所要求的数据进行快速定位的附加数据结构称为索引，每一个索引都是基于关系中的一个属性或属性组来建立的，通次分为：

• 顺序索引，利用索引文件实现记录域到物理地址之间的映射关系
• 散列索引，通过散列函数计算出关键码的哈希值，进行处理之后作为记录存储地址

顺序索引，B 树是顺序索引最常见的物理层面存储方法：

• 聚集索引和非聚集索引，聚集索引是指数据库表行中数据的物理顺序与键值的逻辑（索引）顺序相同。
• 稠密索引和稀疏索引
• 主索引和辅索引
• 唯一索引
• 单层索引和多层索引

数据库范式

• 第一范式(1NF)：属性不可分割，即每个属性都是不可分割的原子项。(实体的属性即表中的列)
• 第二范式(2NF)：满足第一范式；且不存在部分依赖，即非主属性必须完全依赖于主属性。(主属性即主键；完全依赖是针对于联合主键的情况，非主键列不能只依赖于主键的一部分)
• 第三范式(3NF)：满足第二范式；且不存在传递依赖，即非主属性不能与非主属性之间有依赖关系，非主属性必须直接依赖于主属性，不能间接依赖主属性。（A -> B, B ->C, A -> C）

第一范式，属性列中不能有可以再分的列。

比如这个表里面，手机号和住址信息存在一个列中，这样是不符合第一范式的。

第二范式，主要是针对联合索引方面，非主键列不能只依赖于主键列的一部分。

比如这个表里，stu_id 和 kc_id 建立联合索引，score 依赖于这两个索引才能查出，而 kc_name 只依赖于 kc_id 就可以查出，这样是不符合第二范式的。

第三范式，表中不能有传递依赖。

比如这个表中，sex_code 依赖于 id, 而 sex_desc 依赖于 sex_code，这样 sex_desc 间接依赖于 id，这样是不符合第三范式的。

SQL 优化

1.Mysql Join

• 不利于分库分表
• 数据量到达一定程度时，笛卡儿积会过于庞大
• … 待补充

2.Mysql 外键

考虑到需要分库分表，建议不使用物理外键，代码实现逻辑外键

参考：https://zhuanlan.zhihu.com/p/471358569

参考：https://zhuanlan.zhihu.com/p/62020571

参考：https://www.zhihu.com/question/39062169/answer/156096473

3.Mysql 子查询

执行子查询时，MYSQL 需要创建临时表，查询完毕后再删除这些临时表，所以，子查询的速度会受到一定的影响，多了一个创建和销毁临时表的过程。

4. 最左前缀原则

MySQL 中的索引可以以一定顺序引用多列，这种索引叫作联合索引。如 User 表的 name 和 city 加联合索引就是(name,city)，而最左前缀原则指的是，如果查询的时候查询条件精确匹配索引的左边连续一列或几列，则此列就可以被用到。

select * from user where name=xx and city=xx ; //可以命中索引
select * from user where name=xx ; // 可以命中索引
select * from user where city=xx ; // 无法命中索引

查询的时候如果两个条件都用上了，但是顺序不同，如 city= xx and name =xx，那么现在的查询引擎会自动优化为匹配联合索引的顺序，这样是能够命中索引的。

5.varchar 和 char

char 的长度是不可变的，而 varchar 的长度是可变的。

定义一个 char[10]和 varchar[10]。

如果存进去的是‘2222’, 那么 char 所占的长度依然为 10，除了字符‘2222’外，后面跟六个空格，varchar 就立马把长度变为 4 了，取数据的时候，char 类型的要用 trim()去掉多余的空格，而 varchar 是不需要的。

• char 的存取速度还是要比 varchar 要快得多，因为其长度固定，方便程序的存储与查找。
• char 也为此付出的是空间的代价，因为其长度固定，所以难免会有多余的空格占位符占据空间，可谓是以空间换取时间效率。
• varchar 是以空间效率为首位。
• char 的存储方式是：对英文字符（ASCII）占用 1 个字节，对一个汉字占用两个字节。
• varchar 的存储方式是：对每个英文字符占用 2 个字节，汉字也占用 2 个字节

6. 单表数据量过大

• 限定数据的范围：务必禁止不带任何限制数据范围条件的查询语句。比如：我们当用户在查询订单历史的时候，我们可以控制在一个月的范围内。
• 读 / 写分离：经典的数据库拆分方案，主库负责写，从库负责读；
• 缓存：使用 MySQL 的缓存，另外对重量级、更新少的数据可以考虑使用应用级别的缓存；
• 垂直分区：根据数据库里面数据表的相关性进行拆分。
• 水平分区：保持数据表结构不变，通过某种策略存储数据分片。这样每一片数据分散到不同的表或者库中，达到了分布式的目的。水平拆分可以支撑非常大的数据量。

7. 区分英文大小写

• utf8_general_cs，表示区分大小写
• utf8_bin，表示二进制比较，也区分大小写。

# 在每一个条件前加上 binary 关键字
select * from user where binary username = 'admin' and binary password = 'admin';

# 将参数以 binary('')包围
select * from user where username like binary('admin') and password like binary('admin');

8. 场景解析

一个 6 亿的表 a，一个 3 亿的表 b，通过外键 tid 关联，你如何最快的查询出满足条件的第 50000 到第 50200 中的这 200 条数据记录。

如果 A 表 TID 是自增长, 并且是连续的,B 表的 ID 为索引：

select * from a,b where a.tid = b.id and a.tid > 50000 limit 200;

如果 A 表的 TID 不是连续的, 那么就需要使用覆盖索引.TID 要么是主键, 要么是辅助索引,B 表 ID 也需要有索引。

select * from b , (select tid from a limit 50000,200) a where b.id = a .tid;

相关文章：https://zhuanlan.zhihu.com/p/498382688

9. 索引失效

为了避免索引失效，可以考虑使用 <=> 操作符来判断 NULL 值，例如 SELECT * FROM table WHERE column_name <=> NULL，该操作符可以避免 NULL 值被转换，并且可以使用索引扫描进行查询优化，提高查询性能。

如果一个表中包含大量的 NULL 值，可以考虑为该列添加一个默认值，而不是使用 NULL 值，这可以提高索引的效率和查询性能。

分库分表

一般来说数据表写到 2kw 条数据之后，底层 B + 树的层级结构就可能会变高，不同层级的数据页一般都放在磁盘里不同的地方，换言之，磁盘 IO 就会增多，带来的便是查询性能变差。

原文：https://www.zhihu.com/question/464758370/answer/2613020873

1. 垂直分表

垂直分表的原理比较简单，一般就是把某几列拆成一个新表，这样单行数据就会变小，B+ 树里的单个数据页（固定 16kb）内能放入的行数就会变多，从而使单表能放入更多的数据。

2. 水平分表

水平分表有好几种做法，但不管是哪种，本质上都是将原来的 user 表，变成 user_0, user1, user2 …. uerN 这样的 N 多张小表。

从读写一张 user 大表，变成读写 user_1 … userN 这样的 N 张小表。

2.1 根据 id 范围分表

假设每张分表能放 2kw 行数据。那 user0 就放主键 id 为 1~2kw 的数据。user1 就放 id 为 2kw+1 ~ 4kw，user2 就放 id 为 4kw+1 ~ 6kw，userN 就放 2N kw+1 ~ 2(N+1)kw。

假设现在有条数据，id=3kw，将这个 3kw 除 2kw = 1.5，向下取整得到 1，那就可以得到这条数据属于 user1 表。于是去读写 user1 表就行了。这就完成了数据的路由逻辑，我们把这部分逻辑封装起来，放在数据库和业务代码之间。

这样。对于业务代码来说，它只知道自己在读写一张 user 表，根本不知道底下还分了那么多张小表。

对于数据库来说，它并不知道自己被分表了，它只知道有那么几张表，正好名字长得比较像而已。

这还只是在一个数据库里做分表，如果范围再搞大点，还能在多个数据库里做分表，这就是所谓的分库分表。

2.2 根据 id 取模分表

比如一个 id=31 进来，我们一共分了 5 张表，分别是 user0 到 user4。对 31%5=1，取模得 1，于是就能知道应该读写 user1 表。

优点当然是比较简单。而且读写数据都可以很均匀的分摊到每个分表上。

但缺点也比较明显，如果想要扩展表的个数，比如从 5 张表变成 8 张表。那同样还是 id=31 的数据，31%8 = 7，就需要读写 user7 这张表。跟原来就对不上了。

这就需要考虑数据迁移的问题。

2.3 两种方式结合

id 取模的做法，最大的好处是，新写入的数据都是实实在在的分散到了多张表上。

而根据 id 范围去做分表，因为 id 是递增的，那新写入的数据一般都会落到某一张表上，如果你的业务场景写数据特别频繁，那这张表就会出现写热点的问题。

这时候就可以将 id 取模和 id 范围分表的方式结合起来。

我们可以在某个 id 范围里，引入取模的功能。比如 以前 2kw~4kw 是 user1 表，现在可以在这个范围再分成 5 个表，也就是引入 user1-0, user1- 2 到 user1-4，在这 5 个表里取模。

举个例子，id=3kw，根据范围，会分到 user1 表，然后再进行取模 3kw % 5 = 0，也就是读写 user1- 0 表。

这样就可以将写单表分摊为写多表。

这在分库的场景下优势会更明显，不同的库，可以把服务部署到不同的机器上，这样各个机器的性能都能被用起来。

读写分离 

参考文章：https://zhuanlan.zhihu.com/p/199217698

1. 为什么使用主从复制、读写分离

主从复制、读写分离一般是一起使用的。目的很简单，就是为了提高数据库的并发性能。你想，假设是单机，读写都在一台 MySQL 上面完成，性能肯定不高。如果有三台 MySQL，一台 mater 只负责写操作，两台 salve 只负责读操作，性能不就能大大提高了吗？

所以主从复制、读写分离就是为了数据库能支持更大的并发。

随着业务量的扩展、如果是单机部署的 MySQL，会导致 I / O 频率过高。采用主从复制、读写分离可以提高数据库的可用性。

2. 主从复制的原理

• 当 Master 节点进行 insert、update、delete 操作时，会按顺序写入到 binlog 中。
• salve 从库连接 master 主库，Master 有多少个 slave 就会创建多少个 binlog dump 线程。
• 当 Master 节点的 binlog 发生变化时，binlog dump 线程会通知所有的 salve 节点，并将相应的 binlog 内容推送给 slave 节点。
• I/ O 线程接收到 binlog 内容后，将内容写入到本地的 relay-log。
• SQL 线程读取 I / O 线程写入的 relay-log，并且根据 relay-log 的内容对从数据库做对应的操作。

记录一些思考点

1. 多表数据统计

有许多需要进行数据统计的业务场景，涉及多很多张表，数据量又比较大，实时查询的效率已经极低了，甚至在 SQL 这方面已经没有可以优化的地方了。

可以考虑拆出一个专门的统计表，不实时更新，按一定的频率定时进行统计刷新，最终所有的统计结果都从统计表查询。

也可以针对每次的查询条件做查询缓存，条件相同就直接返回缓存的数据。

2. 连表 +Order

连表操作之后会产生一张临时表，此时进行 order 排序，如果原表过大，会导致 using filesort 排序，严重拖慢查询速度。

in 通常是走索引的，当 in 后面的数据在数据表中超过 30%（上面的例子的匹配数据大约 6000/16000 = 37.5%）的匹配时，会走全表扫描，即不走索引，因此 in 走不走索引和后面的数据有关系。

3. 回表

回表在表数据量很大时，会产生较大的影响。回表：指查询过程中，数据库通过普通索引找到主键，再通过主键去寻找行数据，导致额外的开销。

4.left join

连表之后，假如表和表之间存在多对一的关系，就会出现驱动表的某条数据和被驱动表的数据，组成多条数据的情况。

5.distinct

distinct 在数据量大的情况下，会导致数据查询变得特别慢（需要逐行进行比较），所以能不用就尽量不用。

一般来说，在只需要统计总数量时，使用 DISTINCT 会比使用 GROUP BY 更高效。因为在使用 GROUP BY 时需要进行分组、排序、合并等操作，这些处理都需要占用额外的计算和内存资源。

6.memory

memory 引擎创建的表，在删除后会自动释放内存。