《数据库原理》学习笔记，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就会增多，带来的便是查询性能变差。

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表。

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

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

读写分离 

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引擎创建的表，在删除后会自动释放内存。