【缓慢施工中】关系型数据库学习整理（MySQL-InnoDB）
|
754
|
0
|
数据库

17702 字
|
1.2 小时

1. 数据库的三范式

1NF：列具有原子性，不可拆分（也可以表述为“表都是二维的”）
2NF：一个表必须有一个主键，其他列完全依赖于主键（限制部分依赖，即不可只依赖于联合主键的一部分）
3NF：其他列必须和主键直接相关，不能间接相关（限制列的冗余性，不可传递依赖）

2. 什么是事务？事务的ACID特性？如何实现？

事务是用户定义的一组数据库命令，具有ACID特性。
Atomic 原子性：一条事务要么全部成功执行，要么全部失败回滚
Consistency 一致性：无论事务是否成功，数据库完整性约束不被破坏（eg.主键约束，外键约束，非空约束，唯一性约束，check约束）
Isolation 隔离性：多个事务并发操作时，各个事务互不影响
Durability 持久性：事务提交后，对数据库的改变是永久的
ACD是用日志机制实现的，如果事务发生错误，根据日志回滚。详细区分下，D是用redo log实现的，A和C是用undo log实现的。
I是用锁机制实现的，只有持有锁的才可以更新数据。

3. MySQL锁的类型

3.1 按读写分：

3.1.1 共享锁

Shared Lock，又称S锁、读锁。对于已经加了读锁的数据，其他事务仍然可以加读锁，但是不能加写锁。

3.1.2 排他锁

eXclusive Lock，又称X锁、写锁，对于已经加了写锁的数据，其他事务不可读不可写。InnoDB在可重复读(RR，InnoDB默认隔离级别) 隔离等级下，对于update、delete和insert语句，会自动给涉及的数据行加排它锁

3.2 按粒度分：

3.2.1 表锁

开销小，加锁快，高并发下性能低。
数据库引擎总是一次性按相同顺序获取所有表锁，要么全部满足要么全部等待，故能避免死锁。

3.2.2 行锁

开销大，加锁慢，高并发下性能高。InnoDB是通过给索引上的索引项加锁来实现行锁的，正确通过索引条件检索数据时才会使用行锁，否则InnoDB会执行全表扫描，此时则会使用表锁。
行锁在事务执行到对应语句时获得，故可能发生死锁。

3.2.2.1 行锁的细分

在InnoDB默认的可重复读隔离级别下，行锁可以进一步细分为：

  1. 记录锁（Record），只锁特定的单行。记录锁只会在唯一索引（包括主键）上使用等值查询语句时被施加，否则加临键锁；只使用联合唯一索引中的一部分列时，也会导致进化为临键锁
  2. 间隙锁（GAP），锁开区间(a,b)，此开区间可以是第一条记录之前的范围也可以是最后一条记录之后的范围。注意间隙锁的意义仅在于阻止在区间上执行新的数据插入，故间隙锁之间互相兼容、共享间隙锁和排他间隙锁也没有实质区别。详情查看MySQL官方文档
  3. 临键锁（Next-Key），锁左开右闭区间(a,b]，是记录锁和间隙锁的结合。和间隙锁一样，此区间可以是第一条记录之前的范围，而对于最后一条记录之后的范围则仍然是一个开区间(x, +∞)。
  4. 插入意向锁，在插入一条记录行前，由INSERT操作产生的一种特殊的间隙锁。插入意向锁之间互不排斥，所以即使多个事务在同一区间插入多条记录，只要记录本身（主键、唯一索引）不冲突，那么事务之间就不会出现冲突等待。

3.2.2.2 行锁的加锁规则

注：以下规则基于InnoDB默认的可重复读隔离级别。在读已提交隔离级别下，只会加记录锁。参考5.5节

  1. 加锁的基本单位是临键锁
  2. 查询过程中访问到的对象都要加锁
  3. 索引等值查询，且给唯一索引加锁时，临键锁会退化为记录锁
  4. 索引等值查询，且向右遍历时最后一个值不满足查询条件，临键锁会退化为间隙锁
  5. 索引上的范围查询会访问到不满足条件的第一个值为止

规则来源

3.2.2.3 间隙锁/临键锁的补充说明

一些文献——包括MySQL官方文档——指出在唯一索引的等值查询上不需要间隙锁：

Gap locking is not needed for statements that lock rows using a unique index to search for a unique row.

但是，不需要并不代表间隙锁/临键锁在唯一索引上不存在，部分博客中“间隙锁存在于非唯一索引中”的表述是不准确的，不能粗暴地根据索引是否具有唯一性约束来判断间隙锁和临键锁的存在性。从逻辑的角度上来说，如果唯一索引上不存在间隙锁，那么MVCC机制防止幻读的特性便不成立（参阅5.5节）；而从实验验证的角度上，通过进行3.2.1.2节来源页面的实验，能够证实这五条加锁规则的正确性。根据上述规则，即使是在唯一索引中，也会由于临键锁退化或使用了范围查询等原因导致间隙锁出现。
这里详述两个例子，对于表：

create table t(
    id int(10) not null primary key,
    c int(10) not null,
    d int(10) not null,
index(c));
insert into t values(0, 0, 0);
insert into t values(5, 5, 5);
insert into t values(10, 10, 10);
insert into t values(15, 15, 15);
insert into t values(20, 20, 20);
insert into t values(25, 25, 25);
insert into t values(30, 30, 30);

例1. 当在唯一索引id上执行等值查询，但是查询目标并不存在时，根据加锁规则4，会出现退化的间隙锁。

session1 session 2
begin; \
select * from t where id = 7 for update; \
\ begin;
\ insert into t values(8, 8, 8);blocked
\ update t set c = c + 1 where id = 10;pass
rollback; \
\ rollback;

在查询过程中对访问对象区间(5, 10]加临键锁，而由于找到的索引10并不满足等值查询要求id = 7，故退化为间隙锁，锁住区间(5, 10)，导致会话2插入id=8的数据阻塞。

例2. 当在唯一索引id上执行范围查询，根据加锁规则5，可能会出现间隙锁。

session1 session 2
begin; \
select * from t where id >= 10 and id < 15 for update; \
\ begin;
\ insert into t values(8, 8, 8);passed
\ insert into t values(12, 12, 12);blocked
\ update t set d = d + 1 where id = 10;blocked
\ update t set d = d + 1 where id = 15;pass
rollback; \
\ rollback;

在查询过程中首先在区间(5, 10]加临键锁，找到的索引是10，满足等值查询的要求，根据规则3，此处退化为记录锁[10]；继续向右遍历，加临键锁(10, 15]，此时找到的索引15不满足id < 15的条件，遍历停止，且根据规则4临键锁退化为间隙锁(10, 15)，整个上锁的区间实际上是由记录锁和间隙锁组成的[10, 15)。故对id=8、id=15的操作成功，对id=10、id=12的操作阻塞。

3.2.2.4 死锁的解决方法

  • 简单方案：超时，超时后回滚事务
  • 复杂点的方案：主动死锁检测，基于等待图(wait-for graph)，图中以事务为顶点，锁为边，判断依赖图中是否存在环即可。InnoDB也使用了这种方法。检测到死锁以后，选择插入/更新/删除的行数最少的事务回滚，此数据会在事务的数据结构中记录。

3.3 意向锁

分为意向共享锁（Intention Shared Lock，IS，表示事务想要对表中某几行设置共享锁）和意向排他锁（Intention eXclusive Lock，IX，表示事务想要对表中某几行设置排他锁）。意向锁都是表锁，事务在记录上加入行级的读锁/写锁之前，由InnoDB自动添加对应的表级意向锁。
引入意向锁机制的目的主要是辅助判断表锁、行锁的冲突。若不存在意向锁机制，事务在尝试加表锁时，需要逐行扫描该表内是否存在行锁，这会导致性能严重下降。引入意向锁机制后，事务在加行锁之前先申请意向锁，若成功获取则继续，否则则等待直到获取到意向锁为止；后来的事务尝试加表锁时，只要发现该表上存在意向锁，则可以立刻知道此时表内存在行锁，便可以直接进入等待状态。
虽然意向锁是表锁，但是由于其只是一种“意向”，代表表中的某行正在被读写而非整个表，所以多个意向锁之间并不会相互冲突，此时还是需要进一步检查行锁是否冲突。同理，意向锁也不与任何行级锁冲突。

3.4 乐观锁与悲观锁思想

这并不是两种具体存在的锁，而是两种不同的关于锁的设计思想。

乐观锁

乐观锁认为对同一数据的并发操作并不总是发生，属于小概率事件，故不用每次都对数据上锁，也就是不采用数据库自身的锁机制，而是通过程序来实现。常见的乐观锁实现方法是采用CAS(Compare and Swap)机制，在表中加一列version作为版本号，约定数据被修改时version值需要递增。当一个应用进程第一次进行数据的读取时，同时也读取此时version的值，示意sql如下：

SELECT data, version FROM t WHERE id=#{id};

而后需要更新数据时，则将sql按照这种方式写：

UPDATE t SET version=version+1 data=#{newdata} WHERE id=#{id} AND version=#{version};

若在这段时间内有别的事务更改了这条数据，sql的执行就会失败，可以通过SQL执行返回的结果得知是否执行成功。若执行失败，则重复尝试，直到成功。

悲观锁

悲观锁认为对数据的并发修改有很大可能会发生，故在每次获取数据（读写都算）时都要加锁，其具体实现便是数据库的锁机制。

3.5 自增锁（Auto-Inc Locking）

在InnoDB中，自增长值必须是索引且必须是表的第一列。对自增长列进行插入时，需要用到自增锁机制。
在远古版本（5.1.22以前）的MySQL中，自增锁统一采用一种特殊的表锁方式实现，不同于2阶段锁思想，此表锁在完成执行自增操作的SQL语句后立即释放。
在5.1.22版本以后，InnoDB引入了新的基于互斥量(mutex)的自增锁机制，使得自增长值的插入性能大幅度提高。不过在默认设置下，也不是每个插入操作都会使用基于mutex的自增，例如对于无法预先获得插入行数的语句，还是会使用传统的基于表锁的自增锁机制。
注意，默认情况下，MySQL的自增是从1开始而不是0.

3.6 插入意向锁(Insert Intention Lock)与Insert死锁问题

插入意向锁是一种特殊的间隙锁，不属于意向锁；由于是间隙锁，属于行锁而非表锁。
在执行Insert语句之前，InnoDB对所属的间隙申请插入意向锁，插入意向锁之间是不互斥的，可以共存；得到插入意向锁以后，对准备插入的数据行申请行级排它锁，此时可能发生唯一键冲突，即多个并发事务尝试插入键值相同的数据。
在Insert步骤发生唯一键冲突时，所有陷入冲突的事务都需要改变上锁流程，变成如下两步：首先申请共享锁，然后申请排它锁。若冲突发生在主键上，则RC隔离级别下上述共享/排他锁的粒度为记录锁，RR隔离级别下粒度为临键锁；若冲突发生在唯一二级索引上，则无论隔离级别，粒度均为临键锁。
修改了上锁流程为先后申请共享锁和排它锁后，这些冲突的事务一定都能申请到共享锁，且在申请排他锁时阻塞，互相等待彼此的共享锁释放，此时发生死锁。MySQL的死锁检测机制此时会介入，回滚其中一些事务，让其中的一个事务得以完成。

https://www.cnblogs.com/JiangLe/p/12680228.html
https://juejin.cn/post/7052880067298328589

3.7 间隙锁互相兼容导致的死锁问题

https://xiaolincoding.com/mysql/lock/deadlock.html#%E4%B8%BA%E4%BB%80%E4%B9%88%E4%BC%9A%E4%BA%A7%E7%94%9F%E6%AD%BB%E9%94%81

参考

https://juejin.cn/post/7018137095315128328
https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html#innodb-gap-locks
https://www.jianshu.com/p/478bc84a7721
https://segmentfault.com/a/1190000023869573
https://developer.aliyun.com/article/873307
https://www.cnblogs.com/kismetv/p/10787228.html
https://bbs.huaweicloud.com/forum/thread-83643-1-1.html

4. 事务之间的相互影响？

影响 描述
脏读 一个事务读取了另一个事务未提交（或将要回滚）的数据
不可重复读 同一个事务中多次读取同一行数据不一致，即其他事务在这期间update/delete
幻读 同一个查询在不同时间生成不同的行集合，即其他事务在这期间insert
丢失更新（写问题，上面三个是读） 两个事务修改同一数据，先提交的事务做出的修改被覆盖

5. 事务的隔离级别及实现原理？

5.1 表格：标准SQL各隔离等级下问题是否会发生

隔离等级 脏读 不可重复读 幻读 丢失更新
读未提交 (RU) √ √ √ √
读已提交 (RC) × √ √ √
可重复读 (RR) × × √ ×
串行化 (S) × × × ×

5.2 标准SQL事务隔离级别（仅供参考，重点了解后面的InnoDB MVCC方案）

读未提交：最低隔离级别，事务可以读到另一事务未提交的结果。所有问题都发生。实现方式：事务在更新数据时加行级共享锁，事务结束时释放。
读已提交：只有事务提交后，更新结果才可以被其他数据读取。解决脏读。实现方式：1. 对读取的数据加行级共享锁，读完该行立即释放；2. 更新数据时，加行级排他锁，事务结束时释放。
可重复读（InnoDB的默认隔离等级）：保证事务多次读取同一数据时结果一致，除非被自己修改。解决脏读、不可重复读。实现方式：1. 读数据时，加行级共享锁，事务结束时释放；2. 更新数据时，加行级排他锁，事务结束时释放。
可串行化：事务串行执行，最高隔离级别，解决所有问题，但是牺牲并发性。实现方式：1. 读数据时，加表级共享锁，事务结束时释放；2. 更新数据时，加表级排他锁，事务结束时释放。

5.3 两阶段锁(2 Phase Locking, 2PL)规则

在一个事务里面，分为加锁(lock)阶段和解锁(unlock)阶段，所有的加锁操作都发生在解锁操作之前,如下图所示:

2PL

2PL是为了保证事务的隔离性而被设计出来，可以从数学上证明：如果事务是良构(well-structured)的且是两阶段的，那么任何一个合法的调度都是隔离的。可以看到，下文所阐述的InnoDB MVCC机制下的事务隔离实现中，所有的解锁都是发生在事务结束时，晚于任意一个加锁操作。

5.4 当前读和快照读

当前读指读取的记录是最新版本，会对正在读取的数据加锁，属于悲观锁；属于当前读的操作有：select lock in share mode (共享锁)、select for update (排他锁)、update (排他锁)、insert (排他锁)、delete (排他锁)以及串行化事务隔离级别下的所有操作。其中，两种加锁的select语句被称为一致性锁定读(consistent locking read)。
快照读只有在读已提交和可重复读下可行，又称一致性非锁定读(consistent nonlocking read)，基于MVCC实现，可能读到历史版本的数据。属于快照读的操作有：不加锁的select操作。

5.5 InnoDB的MVCC机制原理

5.5.1 隐藏列与undo log

MVCC(Multi Version Concurrency Control)，即多版本并发控制，是“维持一个数据的多个版本，使读写操作没有冲突”的一个抽象概念，其具体实现就是快照读。再次强调只在读已提交和可重复读两个隔离等级下工作。
InnoDB中事务有一个唯一ID，在事务开始时申请，并且按照时间严格递增。
每行数据会有两个隐藏列用于实现MVCC机制，一个是最近更新本行数据的事务idDB_TRX_ID，一个是指向上一个旧版本的回滚指针DB_ROLL_PTR，当行数据更新时，会将旧的数据版本存入回滚日志undo log(关于undo log更详细的介绍，见17节)中，同时记录下更新事务的id和旧版本的回滚指针。
回滚日志分为insert undo log（记录insert，只在事务回滚时需要用到，事务提交后可丢弃）和update undo log（记录update和delete，在回滚和快照读时都需要用到）

5.5.2 读视图与快照读的执行过程

读视图(Read View)是在事务执行快照读的瞬间，产生的当前数据库系统的快照。读视图用列表形式m_ids记录此时系统中所有活跃（即未提交）事务的ID，此外还记录了最小的活跃IDup_limit_id和最大活跃IDlow_limit_id方便进行快速判断。有了这些记录的id，结合行数据隐藏列里记录的最近一次更新的事务idDB_TRX_ID，就可以进行可见性判断，决定当前事务能看到哪个版本的数据。判断的规则如下：

  • 若DB_TRX_ID小于up_limit_id，说明该数据的最近一次更新发生在读视图创建之前，故数据的最新版本可以被看见
  • 若DB_TRX_ID大于low_limit_id，说明该数据的最近一次更新发生在读视图创建之后，故数据的最新版本肯定不可以被看见，需要读取历史版本
  • 若DB_TRX_ID处于up_limit_id和low_limit_id之间，则只能去查活跃事务列表。若存在于列表中，则说明该数据的最近一次更新发生在读视图创建之后，数据的最新版本不可以被看见，需要读取历史版本；若不在列表中，说明最近一次更新发生在读视图创建之前，最新版本可以被看到。

当一个事务运行到一行查询语句时，若是执行快照读，则创建读视图（注意，根据不同的隔离等级，这里可能不创建新的读视图，参见5.6），随后查询该行数据，获得版本字段DB_TRX_ID。根据上文的规则，利用读视图与版本字段进行可见性判断，若最新版本数据可见，则直接读取并返回；若不可见，则根据回滚指针开始查询undo log中的日志链，直到找到可见的版本，再读取并返回。

5.6 MVCC机制下InnoDB的事务隔离实现方式

事务隔离级别 实现方式
未提交读(RU) 事务对当前被读取的数据不加锁，且是当前读；事务在更新某数据的瞬间，必须先对其加行级共享锁，事务结束时释放。
读已提交(RC) 事务对当前被读取的数据不加锁，且是快照读；事务在更新某数据和执行当前读（例如select for update）时，必须先对其加行级排他锁（Record），事务结束时释放。注意：RC隔离等级下，每一次执行查询语句时都会建立新的读视图即最新的快照，故查询得到的是已被提交过的最新版本，有不可重复读问题
可重复读(RR) 事务对当前被读取的数据不加锁，且是快照读；事务在更新某数据和执行当前读（例如select for update）时，必须先对其加行级排他锁（Record，GAP，Next-Key），事务结束时释放。注意：RR隔离等级下，第一次执行快照读时创建一次读视图即快照，此后同一事务下的所有查询都通过该快照进行，故在InnoDB在这个级别解决了不可重复读的问题；而通过间隙锁，InnoDB在这个级别解决了幻读的问题；
串行化(S) 事务在读取数据时，必须先对其加表级共享锁 ，事务结束时释放，都是当前读；事务在更新数据时，必须先对其加表级排他锁，事务结束时释放。

简而言之，在MVCC能工作的RC和RR隔离等级下，所有的读都是快照读故不加锁，所有的写都加行级排它锁；两个隔离等级的区别在于：1. 在RC隔离等级下，每次读都单独创建快照，而在RR隔离等级下，事务中所有的查询都共用第一条查询语句的快照；2.在RC隔离等级下，更新数据时只加Record记录锁用于解决脏读问题，而在RR隔离等级下还会加间隙锁/临键锁，用来解决幻读问题。而不可重复读问题则是由快照机制解决。

强调一下RC的快照读问题：RC每次读之前都创建新的快照，所以只要两次查询之间有其他事务修改了数据的值并且成功提交，则这两次查询就会返回不同的值。RC下的查询语句在数据行没有加锁时会直接读取最新的值，若有锁则通过undo log获取其已提交的最新快照版本(fresh snapshot)。

参考：

https://dev.mysql.com/doc/refman/8.0/en/innodb-consistent-read.html
https://www.modb.pro/db/75331
https://segmentfault.com/a/1190000025156465
https://juejin.cn/post/6871046354018238472
https://segmentfault.com/a/1190000039809030
https://segmentfault.com/a/1190000023332101
https://juejin.cn/post/6844903908146413576

6. MySQL索引类型

6.1 数据结构角度

名称 支持引擎 性质
Fulltext 全文索引 InnoDB MyISAM 主要提高对文本的查询效率
B+Tree B+树索引 InnoDB MyISAM Memory 略
Hash 哈希索引 Memory InnoDB * （在hash桶不大的情况下）检索效率高；只能使用等值查询“=”

6.1.1 InnoDB中的哈希索引

InnoDB使用哈希索引对字典进行查找，哈希冲突解决方法为拉链法，哈希函数采用除法散列方式。需要强调的是，InnoDB使用的是自适应哈希索引(Adaptive Hash Index, AHI)的，InnoDB会监控对索引页的查询，当观察到建立哈希索引可以带来速度提升时会自动生成和使用哈希索引，不能人为干预是否在一张表中生成哈希索引。AHI直接在哈希桶中记录数据所在的地址，故可以一次性找到索引，不需要在B+树中搜索数个层级。

6.1.2 InnoDB中的全文索引

顾名思义，全文索引技术主要用于解决文本查找问题。传统的B+树索引可以解决前缀文本的检索问题，例如select * from t where text like 'xxx%'；但是对于以通配符开始的文本检索则无能为力，例如... like '%xxx%'。而在实际应用中，B+树索引支持的前者仅能用于检索以'xxx'开头的文章，这显然是不满足要求的。
InnoDB的全文索引采用全文倒排索引(full inverted index)，这种索引以表的形式存储单词和他们出现的位置信息（文档id，第几个字符开始）。此外，还记录了单词第一次和最后一次出现的文档id，以及此单词在多少个文档中存在。

6.2 物理存储角度

名称 支持引擎 主键索引性质 二级（辅助）索引性质
聚簇索引 InnoDB 主键索引的叶子节点直接存放数据，同一叶子节点内数据按序存放 辅助索引的叶子节点记录主键的键值，需要二次查询
非聚簇索引 MyISAM 主键索引的叶子节点记录数据文件中的偏移地址 辅助索引与主键索引相互独立，叶子节点记录的也是数据文件中的偏移地址

聚簇索引下，主键索引文件就是数据文件；非聚簇索引下，索引文件与数据文件分开。

6.3 逻辑角度

名称 描述
主键索引 一张表只能有一个，不重复，非空。
唯一索引 可以有多个，允许空，仅在空的情况下可以重复
普通索引 普通的索引

6.4 涉及到列的个数角度

名称 描述
单列索引 基于表中一个字段的索引
联合(复合)索引 基于一张表中的多个字段的索引

7. 联合索引

7.1 联合索引的B+树结构

对于表：

CREATE TABLE `t1`  (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) NULL DEFAULT NULL,
  `c` int(11) NULL DEFAULT NULL,
  `d` int(11) NULL DEFAULT NULL,
  `e` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`a`) USING BTREE,
  INDEX `index_bcd`(`b`, `c`, `d`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

其中的联合索引bcd，其B+树结构如下所示：

B+树结构

7.2 联合索引OR多个单列索引？

当存在多个可用的单列索引时（即不因为使用OR等操作导致索引失效），MySQL会根据查询优化策略，选择其中一个单列索引使用；如果同时存在单列索引和联合索引，也是根据优化策略选择二者之一使用。

7.3 联合索引的最左匹配原则

联合索引遵循最左匹配原则，即在查询条件中使用了联合索引的第一个字段，联合索引才会被使用。当代的SQL优化器会自动优化SQL语句中的字段顺序，只要使用了第一个字段即可，不一定非要按顺序写。

7.4 联合索引的范围查询问题

最左匹配原则遇到范围查询则停止使用该字段之后的索引，例如：

SELECT * FROM table WHERE a > 1 and b = 2;

若要建立联合索引，应当是KEY '联合索引' ('b', 'a') ，这样a和b的查询都可以利用索引；如果是(a,b)，那就只有a能用到了

7.5 参考

https://www.cnblogs.com/rjzheng/p/12557314.html
https://juejin.cn/post/6844904073955639304

8.索引过多的弊端

  • 索引本身很大，消耗内存/磁盘空间。
  • 索引在增删改时也需要维护，降低效率

9.索引失效的场景

索引失效时，会执行全表扫描，此时行锁退化成表锁，意向锁机制派上用场。

  1. 使用or
  2. （只是可能导致索引失效）使用!=或<>进行查询
  3. 联合索引遇到范围匹配后边的列。参考6.4.3、6.4.4
  4. 写SQL语句时若字段不一致，例如对一个varchar字段用数字进行查询，此时需要进行类型转换而且导致索引失效。
  5. 对索引字段进行操作，例如计算、函数、类型转换，典型例子select * from t where ABS(val) = 1;
  6. （只是可能导致索引失效）使用is null 或者is not null（这也是为什么最好索引列要是not NULL）
  7. 使用以通配符开头的模糊匹配，例如"%July"会导致失效，而"July%"则不会

9.1 !=或<>的索引失效问题

在聚簇索引上，使用不等查询仍然可能走索引；最终还是要看优化器的估算。

9.2 NULL在B+树索引中的存储方式 & is not null的索引失效问题

首先主键是不允许NULL的，所以NULL只出现在二级索引中；在二级索引的B+树里，NULL记录都放在B+树最左侧的叶子节点。由于检索二级索引需要回表，优化器在进行优化时会大概估计回表次数，从而决定是干脆在聚簇索引上扫库还是走二级索引。

10. InnoDB为什么要用自增id作为主键？

InnoDB使用B+树聚簇索引，数据记录本身被存于主索引的叶子节点，同一叶子节点内数据按主键顺序存放。
文件系统读取磁盘时一般以页为单位进行读取，为了配合文件系统工作以提高读取效率，MySQL底层也是以数据页为单位来存储数据和进行磁盘管理的，一个叶子节点就是一个数据页，默认为16k。当一个数据页无法容纳准备插入的新数据时，会开辟一个新的数据页。
若自增主键，每次新增记录时都在当前最后一个叶子节点的数据页，即使需要开辟新的数据页，也只需要链接到最后一个叶子节点的后面；而如果采用随机主键，新的数据大概率需要插入到已有的数据页中，若该数据页无法容纳这行新数据，则需要对该数据页进行分裂操作，这样一来不仅需要进行非常耗时的数据拷贝（最坏情况下，该目标数据页长期未使用已被清出缓存写回磁盘，此时又需要重新从磁盘读取），还会使得索引结构不够紧凑、查询性能下降。后继不得不重建表并进行页面优化。

参考

https://zhuanlan.zhihu.com/p/98818611

11. 覆盖索引和回表

覆盖索引：索引包含了需要查询字段的值，例如InnoDB的主键索引
回表：索引不能拿到数据，只拿到对应主键，还需查询主键索引，例如InnoDB的二级（辅助）索引。
回表的性能低下，因为二级（辅助）索引的叶子节点中存放的主键是无序的，很可能分布在多个不同的主键聚簇索引的叶子节点上，再去访问这么多节点需要读取许多不同的数据页，会导致频繁的磁盘IO。

12. 索引优化

  1. 尽可能在索引列上设置not null，而是设置一个默认值，例如0或者1970-01-01
  2. 充分利用复合索引的范围查询、最左匹配机制
  3. 合理选择逻辑主键或业务主键。

逻辑主键和业务主键
逻辑主键指主键值与当前表的业务逻辑无关，例如InnoDB的自增主键。业务主键则相反，例如以银行卡号作为主键。
逻辑主键的优势有：1. 插入性能高（见第9节：InnoDB自增主键原因）；2. 若出现业务变化，例如要为作为业务主键的id加一位字母，会导致巨大的维护开销。

13. MySQL为何选择B+树作为索引 / B+树对比其他数据结构的优势

主要从查询性能优劣（树的高度）、查询性能稳定（查询深度一致性）、范围查询/排序/扫库效率等三个角度去思考。

13.1 B+树对比B树的优势

  1. 查询效率高：通常一个节点的大小设置为一个页的大小。由于B+树的非叶子节点不存放数据，故一个非叶子节点可以存放更多的数据关键字，树的高度显著降低，查询时需要的磁盘IO次数显著少。
  2. 查询性能稳定：B+树数据都在叶子结点，每次查询的深度相同；B树非叶子节点也存放数据，查询时深度不一致，性能不稳定。
  3. 扫库效率高：B+树的叶子结点构成链表，扫库顺序遍历即可。B树则必须中序遍历，在叶子结点和父结点之间来回访问，效率低。

13.1.1 拓展：生产环境中的B+树高度一般是多少？

一般是2~3层。下文进行一个粗略的估算：
InnoDB默认将节点大小设置为一个数据页的大小，即默认16K；B+树非叶子节点只存储索引的键值和前往下一级节点的指针。这里我们设键值的类型为BIGINT，大小8字节，指针大小在InnoDB中设置为6字节，这样一组索引值+指针一共占用14字节的空间。由于是估算，这里忽略InnoDB页中应当包含的头尾数据，此时一个数据页可以存放16 1024 / 14 = 1170个索引。而对于叶子节点，实际应用中一条数据往往是1K左右大小，即一个数据页可以存放16条数据。
这样，对于一个2层的B+树，根节点存放1170条索引，对应的叶子节点每个可存放16条数据，一共可以存放1170 16 = 18720条数据；而对于一个3层的B+树，根节点和第一层可以存放1170 1170 = 1368900条索引，叶子节点可以存放1368900 16 = 21902400条数据。即一个3层B+树已经可以存放两千万条数据，已经能够满足大部分场景的需求。如果有更大的索引要求，就应该考虑水平分表了。

参考：

https://www.nowcoder.com/discuss/965851?trackId=2a7vtesb832g8ojodjzyw

13.2 B+树对比红黑树的优势

  1. 查询效率高：红黑树是一种平衡二叉排序树，二叉性质导致树的高度过高，效率降低；
  2. 查询性能稳定：红黑树非叶子节点也存放数据，查询时深度不一致，性能不稳定。
  3. 插入性能高：插入数据时，维护红黑树结构需要进行左旋/右旋，往往会造成大量节点的修改，大量磁盘IO带来效率降低。

13.3 B+树对比哈希表的优势

  1. 范围查询效率高：B+树的节点关键字具有有序性，范围查询效率很高；而对建立哈希索引的数据进行范围查询则只能全表扫描
  2. 排序效率高：同样是由于有序性
  3. 某个哈希桶过大以后查询性能下降，查询性能不一定更好且不稳定

14. InnoDB和MyISAM对比

14.1 对比表格

InnoDB MyISAM
支持事务，可以使用commit和rollback语句，实现了标准的4个事务隔离级别 不支持事务
支持行锁 只有表锁
聚簇索引，主索引叶子结点直接存放数据，主索引在缓存中时可以无需额外磁盘IO 非聚簇索引，主索引叶子结点存放数据在数据文件中的偏移地址
二级索引存放的是主键的值，需要二次查询 二级索引和主索引互相独立。
必须有主键 无需主键
支持外键 不支持外键
有异常崩溃恢复机制，基于redo log 无异常崩溃恢复机制
适合大量update 适合大量select

14.2 为何MyISAM适合大量读（大量select）？

首先，MyISAM采用非聚簇索引，索引文件与数据文件分开导致其索引文件体积更小，可以在内存中一次性缓存更多索引，查询更快；
其次，由于MVCC机制，InnoDB查询时需要创建读视图进行可见性判断，必要时还得去查找undo log寻找历史版本，自然就更慢。

15. MySQL Binary Log 与主从复制、读写分离

15.1 Bin log 定义与记录模式

二进制日志是MySQL Server层自带的日志机制，不是redo log和undo log那样的InnoDB在引擎层实现的日志机制。Bin log名称中的“二进制”指的是其日志文件的存储方式是二进制文件（故不能通过cat等命令查看，必须通过MySQL提供的解析工具mysqlbinlog查看），而在物理/逻辑日志的分类上，bin log属于逻辑日志。Bin log具有三种记录模式，对应参数binlog_format，分别是：

  • STATEMENT：5.1版本以前仅有的模式，直接记录对数据库执行过修改的SQL语句；
  • ROW：逐行记录行数据的更改情况，此性质导致其空间消耗往往远大于STATEMENT；
  • MIXED：默认采用STATEMENT，对于STATEMENT不能记录的则换成ROW

Bin log会记录所有对数据库执行更改的操作，这意味着select、show等操作肯定不会被记录，而一些没有实际影响的update操作——例如对不存在的数据进行update——则仍有可能被记录。
Bin log在事务提交时一次性写入磁盘中的记录文件。
Bin log的主要作用是用于数据恢复和主从复制。

15.2 主从复制

主从复制(Replication)是用来建立一个与主数据库完全一样的数据库环境，即从数据库。主从复制有两个主要用途：

  1. 读写分离，主节点负责写，从节点负责读，从而提高并发性能。
  2. 高可用，从数据库可以作为后备，当主数据库故障时，可以切换到从数据库继续工作

MySQL自带主从复制功能，依赖bin log机制实现。其步骤如下(重点记忆三个线程)：

  1. 主节点进行insert、update、delete操作时，按顺序写入binlog。
  2. 从节点从库连接主节点主库，主节点对每个Slave分别创建binlog dump线程，Slave建立IO线程与主节点保持通信，并建立SQL线程准备执行主节点推送来的操作。
  3. 当主节点的binlog发生变化时，binlog dump线程会通知所有的从节点，并将相应的binlog内容推送给Slave节点。
  4. I/O线程接收到binlog内容后，将内容写入到本地的relay log。
  5. SQL线程读取I/O线程写入的relay log，并且根据relay log的内容对从数据库做对应的操作。

relay log，即中继日志，用于存放主节点dump过来的日志，等待从节点的sql线程读取并执行。relay log的格式与bin log的完全一致，甚至可以通过bin log的解析工具mysqlbinlog来解析。从节点的SQL线程执行完一个relay log文件中的所有内容后便会自动删除该文件，故MySQL中没有设计显式删除relay log文件的机制。

15.3 读写分离

MySQL自带的主从复制机制只保证主节点对外提供服务，而从节点仅作为数据备份，不对外提供服务。故实现读写分离需要借助一些中间件，且是建立在主从复制的基础上的。

由于网络延迟，从节点的数据并不是最新的，故读写分离的框架往往会保证同一线程的同一次数据库连接内，若存在写入操作，则以后的读操作都从主节点读取，从而保证数据一致性。

参考

https://dev.mysql.com/doc/refman/8.0/en/replica-logs-relaylog.html
https://blog.csdn.net/cwb521sxm/article/details/97303289
https://zhuanlan.zhihu.com/p/68035302
https://segmentfault.com/a/1190000023775512

16. InnoDB Redo Log

16.1 Redo Log的定义与性质

重做日志，InnoDB引擎层的机制，用于数据库异常崩溃的恢复，防止在数据库崩溃时尚有脏页未写入磁盘。Redo log保证了事务ACID性质中的D，即持久性。
是物理日志，直接记录数据页的修改，其记录内容可以大致理解为[页号-偏移量-修改后的值]。
Redo Log包括两部分：一个是内存中的日志缓存(Redo Log buffer)，另一个是磁盘上的日志文件(Redo Log
file)。Redo log file文件大小固定，由一组日志文件组成，且采用循环写的方式，当写到结尾时会回到开头覆盖写，下图展示了一个由4个文件组成的redo log file：

Redo log的循环写入

图中，write pos是下一条redo log将要写入的位置，一边写一边后移；checkpoint则是下一条需要刷新到数据文件的redo log的位置，也是一边刷新一遍后移。从write pos开始到checkpoint之间的部分就是redo log上剩余的空间，从checkpoint开始到write pos之间的部分就是尚未刷新入磁盘上redo log file的日志。如果write pos追上了checkpoint，那么redo log就被写满了，此时不可再执行新的数据更新操作，必须立刻执行redo log数据页的刷新。

16.2 Redo Log的工作流程：WAL

磁盘IO是非常慢的，倘若每次更新的数据都必须立刻落盘，数据库性能会极其低下，让人无法接受。为了解决这个问题，每次进行数据更新时，InnoDB都不会立刻将修改后的数据页写回磁盘，而是先将这些脏页都存入内存中的缓存池(Buffer Pool)，过后由InnoDB的Master thread定时执行脏页的刷新落盘，通常是每秒一次，一次最多写100个脏页。

Redo log也采用了同样的先缓存再定期落盘的思想以减少磁盘IO。每条update语句执行从而产生redo log时，先写入内存中的redo log buffer，过后由InnoDB的Master thread定时执行日志的刷新，将buffer中的日志刷新到redo log file并移动checkpoint。在Master thread的每秒定时操作中，总是先刷新redo log，再尝试刷新缓存池中的脏页。这种“先写日志，过一段时间再写入磁盘”的技术就是MySQL里经常说到的WAL(Write-Ahead Logging)技术。

以事务中一次update语句的执行为例，Redo Log工作的过程如下：

  1. 先将原始数据从磁盘中读入内存缓存，修改数据的内存拷贝
  2. 生成一条重做日志并写入Redo Log buffer，记录的是数据被修改后的值
  3. 在三种情况下，将Redo Log buffer中的内容采用追加写的方式刷新到Redo Log file。这三种情况分别是：
    1. Master Thread每秒固定执行redo log buffer的刷新
    2. 事务提交时 *（根据用户设置的参数，事务提交时可能并不会执行，详见后文）
    3. redo log buffer 剩余空间不足一半时
  4. 定期将内存中修改的数据刷新到磁盘中

在一个事务的执行中，步骤1、2在每条update语句运行时都要执行，此外步骤3也固定会在每秒被Master Thread执行，故在事务执行过程中redo log就已经逐步写入磁盘，并不是在事务提交时一次性写入所有内容。

16.3 Redo log file落盘的详细说明

16.2节步骤3的“将buffer中的日志刷新到磁盘中的日志文件”实际上也分为两步进行：首先，将内存buffer中的内容写入操作系统内核中的文件系统缓存（下文简称os buffer）；其次，使用系统调用fsync()将文件系统缓存的数据以追加的方式写入磁盘上的文件。

需要注意的是，系统调用fsync()的执行速度受到磁盘IO性能的限制，且直到写盘操作完成前都将处于等待状态。默认情况下，每次事务提交后都会执行一次往os buffer的写入和fsync()，确保本次事务的redo log被写入磁盘上的redo log file，而这会带来一个性能隐患：若在短期内出现大量事务提交，默认设置下的redo log刷新机制会导致大量fsync()发生，带来的时间成本往往是不可接受的。《MySQL技术内幕：InnoDB存储引擎》上介绍了一个实验，设置一个存储过程用来插入一行数据，然后使用CALL命令执行该过程500000次，一共花费2分钟才完成500000行数据的插入。诚然，用50万次事务提交来插入50万行数据很不合理，但是该实验足以说明高访问下fsync()有可能成为性能瓶颈。为了解决这个问题，InnoDB提供了参数innodb_flush_log_at_trx_commit，可以通过修改该参数来调整redo log在事务提交时调用fsync()的行为。

参数值 含义
0（延迟写） 事务提交时不再进行redo log的刷新操作，完全交由Master Thread每秒写入os buffer并调用fsync()写入到redo log file中。若MySQL崩溃，会丢失约1秒钟的数据。
1（默认，实时写，实时刷） 事务每次提交都会将redo log buffer中的日志写入os buffer并调用fsync()刷新到redo log file中。即使MySQL崩溃也不会丢失任何数据，但是事务的提交性能较差，直接影响数据库的性能。
2（实时写，延迟刷） 事务每次提交时仅将redo log写入os buffer，不主动进行fsync()，由文件系统内部的定时机制自行执行fsync()。此设置下只要OS不崩溃，理论上来说日志数据也是安全的，只是落盘的时点完全交由OS处理，不可控制

16.4 基于Redo log的数据恢复流程

Log Sequence Number(LSN)，日志序列号，表示redo log写入的总量，单位是字节。例如，当前redo log的LSN是1000，一个新的事务写入了100字节的redo log后，LSN就变为1100。
每次启动InnoDB时，无论上次是否异常关闭，都会检查redo log并尝试进行恢复操作。从checkpoint开始到LSN为止的redo log就是需要刷新到数据页上进行恢复的内容。
此外，除了redo log本身会保存LSN，在每个数据页的头部也都有一个值FIL_PAGE_LSN记录该页的LSN，即该页最后一次刷新时LSN的值。通过比较数据页的LSN和redo log的LSN，就可以决定一个数据页是否需要进行恢复。

16.5 Redo log和Bin log的对比

区别项 Redo log Bin log
物理/逻辑分类 物理日志 逻辑日志
磁盘日志文件的记录方式与文件大小 由一组文件组成的总大小固定的记录文件，采用循环写的方式 一个文件写满以后创建新的文件写后续内容，采用追加写的方式
实现层级 由InnoDB引擎层实现，存储引擎负责写，并不是所有引擎都有 由MySQL Server层实现，执行器负责写，所有引擎都可以使用
用途 用于异常崩溃的恢复(crash-safe) 用于主从复制和数据恢复
保护对象 针对事务，保证事务的持久性 针对数据库，保护数据库
写入时间 在事务进行中不断地被写入 事务提交后一次性写入
幂等性 幂等 不幂等

16.6 为什么只靠bin log不能做到崩溃恢复(crash-safe)？

假设没有redo log机制，InnoDB在操作数据时还是将数据拷贝到内存中的缓冲池(Buffer Pool)进行保存和改写，随后定期由Master thread刷新落盘。在这种情况下倘若发生MySQL崩溃，缓存池中未落盘的数据都会丢失，此时如果想单纯依靠Bin log进行数据恢复很可能会出现错误，究其原因，在于Bin log是全量日志，无法判断日志中的哪些内容已经落盘。

举个例子，假设Bin Log以STATEMENT的方式记录，记录下了如下SQL：

update abc set d = 233 where e = 233;  /*这句的内容不重要*/
update t set c = c + 1 where id = 1;

对于崩溃的时点存在两种情况：第一，Master thread在执行缓存刷盘时，刚执行完第一句就崩了，此时盘上数据的c是没有增加过的；第二，执行完第二句sql的落盘后才崩溃，此时盘上数据的c是已经被增加后的。
由于第二条语句并不是set c = 某个立即数而是让c自增，在尝试进行数据恢复时，我们会发现无法确定盘上的c是否已经进行了自增，正是因为这个原因，我们无法判断该从何处开始重新执行bin log中记录的sql。如果从错误的位置开始恢复数据，在设置了主从复制的情况下还可能导致从节点和主节点的数据不一致，造成更严重的后果。因此在没有redo log的场合下，Bin log的全量日志性质使其只适用于恢复误删数据或者误删表的情况，因为这些误操作的删除语句在日志中相当容易定位。
正因如此，redo log在设计时就采用了记录物理日志+使用checkpoint记录落盘进度的方案，从而实现了crash-safe。

16.7 两阶段提交(2-Phase Commit, 2PC)

16.7.1 问题的引入：分布式一致性问题

对于需要主从复制的场景，MySQL同时开启了Bin log和Redo log后，就存在两个日志之间的一致性问题。当一个事务准备提交时，无论是先写Redo log再写Bin log，还是先写Bin log再写Redo log，都会有发生崩溃时只写了其中一个而尚未来得及写另一个的可能性。下面对这两种情况分别讨论：

假设在事务完成时，先写Redo log后写Bin log，而崩溃发生在写完Redo log之后、写入Bin log之前，那么主节点在重启后可以根据Redo log成功恢复数据，但是从节点通过主从复制机制获得的Bin log并没有这条事务的日志，导致从节点比主节点少数据；
反过来也是类似的，假设在事务完成时，我们先写Bin log后写Redo log，而崩溃发生在写完Bin log之后、写入Redo log之前，此时主节点重启后找不到该事务对应的redo log，无法恢复该事务的数据，而拿到Bin log进行复制的从节点反而有了该事务数据。

通过上述分析可以发现，如果只是简单的调整Bin log和Redo log的写入顺序，一旦主节点在执行事务期间崩溃，我们无论如何都无法保证主从节点数据一致，这就是事务的分布式一致性问题。

16.7.2 两阶段提交下的Update过程

为了解决分布式一致性问题，人们提出了两阶段提交这一概念。在MySQL中，两阶段提交体现在对redo log进行状态标记。更新内存缓存中的数据后，首先由存储引擎写redo log并标记为prepare状态，随后由执行器写bin log，最后将redo log标记为commit状态，此后可以提交事务。

Update过程

16.7.3 两阶段提交下崩溃恢复的实现

在写入redo log并将redo log标记为prepare状态时，会记录XID，即当前事务id，同时在写binlog时也会记录XID。在记录了XID的情况下，就可以在崩溃恢复保证一致性，具体的恢复逻辑如下：

  1. 如果redo log里面的事务是完整的，也就是已经有了commit标识，则直接提交
  2. 如果redo log里面的事务处于prepare状态，则查找bin log，查看是否存在与redo log相同的XID，如果存在说明bin log完整，则提交事务，否则回滚事务，不恢复此条数据。

接下来分三个情况讨论，证明以上规则的正确性：

  1. 如果在写入redo log之前崩溃，那么此时redo log与bin log中都没有关于该事务的内容，已经具有一致性；
  2. 如果在写入redo log prepare阶段后崩溃，执行崩溃恢复时，由于redo log没有被标记为commit，需要查找bin log寻找是否存在与此条redo log相同中的XID，此时肯定无法找到，那么回滚该事务，不恢复其数据
  3. 如果在写入bin log后崩溃，执行崩溃恢复时，由redo log中的XID可以找到对应的bin log，则正常进行提交，恢复该事务的数据。

在这样的机制下，两阶段提交能在崩溃恢复时，能够对提交中断的事务进行补偿，来确保redo log与bin log的数据一致性。

参考

https://juejin.cn/post/6987557227074846733
https://segmentfault.com/a/1190000023827696
https://cloud.tencent.com/developer/article/1679325
https://spongecaptain.cool/post/database/logicalandphicallog/
https://www.51cto.com/article/696677.html
https://blog.csdn.net/qq_33591903/article/details/122030252

17. TODO: Undo log

17.1 关于undo log

回滚日志，是逻辑日志，用于回滚事务对数据库的修改，也用于实现MVCC的快照读。由于其是逻辑日志，回滚过程也是逻辑的，可以理解为对于每一条Insert，回滚时执行对应的Delete、对于每一条Update，回滚时执行反向的Update将原始数据更新回来。

为什么不能是物理日志？
物理日志例如Bin log记录一个数据页的变化，通过物理日志进行数据恢复时，会将整个数据页恢复到原来的情况。假设一个场景：一个数据页上同时有多个事务操作不同位置的数据，而其中某一个事务失败需要回滚，此时若采用物理日志回滚整个页，会导致其他事务作出的修改失效。
——《MySQL技术内幕：InnoDB存储引擎》

注意：
InnoDB实际上是将undo log当做一种数据来维护和使用的，存放在数据库的一个特殊段中，所以undo log在变化时也需要写redo log。

17.2 purge与undo log的分类

17.3 对比表格

区别项 Redo log Undo log
用途 用于异常崩溃的恢复 用于事务回滚、实现MVCC的快照读
记录内容 物理格式日志，记录物理数据页的二进制修改内容 逻辑日志，以表的形式记录行数据修改的历史版本

18. MySQL的内部构造/MYSQL执行SQL语句过程

  1. 服务(server)层：
    1. 连接器：管理连接，验证身份和权限
    2. 查缓存：以键值对方式存放SQL语句和结果。由于失效太频繁，目前已经被默认关闭、移除。优化器对语句进行优化以后以后会重新查缓存
    3. 分析器：语法检查和分析，生成语法树
    4. 优化器：对语句进行逻辑优化（例如拆解带NOT的和取范式/析取范式，常量表达式的计算、化简和传递）和代价优化（对于联合索引、二级索引上的范围查询，估算判断是利用索引还是全表扫描；确定多个表join的顺序），生成执行计划。可以用Explain来查看计划
    5. 执行器：操作存储引擎，返回结果
  2. 存储引擎层：数据库引擎，存储数据，提供读写接口

    参考

    https://cloud.tencent.com/developer/article/1678069

19. 数据库的分库分表

19.1 分表

垂直分表：将某些数据较大的列拆出，与主键等组成独立的表。
水平分表：例如按性别分两个用户表。减少单表的数据量
垂直和水平分表都可以减小单表的访问负担，提高查询性能，减小表锁造成的冲突；
垂直分表还可以减小表的体积，减少磁盘IO。

19.2 分库

垂直分库：按照业务将表分类到不同的数据库上面，每个库可以放在不同的服务器上。例如商品信息访问量大，而店铺信息访问量相对较低，则将商品信息相关的表放入商品库，店铺信息相关的表放入店铺库。
水平分库：例如根据店铺ID的奇偶性分为两个库。

20. filesort

外部排序，算法使用的是归并排序。当一条SQL语句需要对查询结果进行排序，而排序的字段没有建立索引或由于最左匹配原则无法在该字段使用索引时，就会借用外部文件系统进行归并排序。

21. 高并发方案/数据库优化

  1. 在服务层与数据库层之间加缓存
  2. 分布式架构，分散计算压力
  3. 数据库优化：在常检索、排序字段创建索引
  4. 数据库优化：主从复制、读写分离
  5. 数据库优化：水平、垂直分表

22. MySQL基本数据类型

22.1 数值类型

22.1.1 整数类型

TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，分别为1字节、2字节、3字节、4字节、8字节。任何整数类型都可以加上UNSIGNED属性，表示无符号整数。
整数类型占用的空间是固定的，对于任何整数类型声明字段长度时，实际上只限制其显示长度。例如声明某字段int(5)，其占用空间仍然是4字节；在该字段上插入数值123，由于不足5位，故在print时会补前导0，显示'00123'。可见整数类型的声明字段长度比较鸡肋，基本没有使用场景。

22.1.2 小数类型

FLOAT 4 字节，DOUBLE 8 字节。
DEMICAL(P,D)用于存储保留精确度的小数。P是表示有效数字数的精度，范围为1~65；D是表示小数点后的位数，D的范围是0~30。相比起FLOAT和DOUBLE，不存在浮点数运算误差。

22.2 字符串类型

22.2.1 常用字符串类型

CHAR：定长字符串，根据声明的字段长度分配固定大小的空间，最大空间255字节。插入时会删除后置空格，若长度小于声明长度，则存放时用空格补充空位，检索返回结果时会删除后面的空格；也由于定长，存储效率更高。
VARCHAR：变长字符串，声明的字段长度只限制其最大长度，根据当前存放的字符内容长度来动态分配空间，最大空间65535字节。但会在数据开头使用额外1~2个字节存储字符串长度（列长度小于255字节时使用1字节表示，否则2字节），在结尾使用1字节表示字符串结束，实际最大可用65532。
TEXT：长文本，也是变长字符串，最大空间65535字节。相比较VARCHAR，不能设置默认值。还有更大的MEDIUMTEXT(16MB)和LONGTEXT(4GB)
BLOB：二进制格式的长文本，可以用于存放图片、音频等数据。与TEXT对应，也存在MEDIUM和LONG版本。

注：对于CHAR和VARCHAR，其字段长度是字符个数，而不是所占空间的以字节计算的大小。字段长度与字节长度不能直接划等号，与选择的编码和实际存放内容有关。举例：在GBK编码下存放汉字，每个字符占用2字节，CHAR(2)占用4个字节的空间；而在UTF-8编码下存放ASCII字符，每个字符只占用1字节，CHAR(2)就只占用2字节的空间，而UTF-8编码存放汉字时一个汉字占用3字节，CHAR(2)就占用6字节了。

22.2.2 CHAR和VARCHAR的选择

CHAR是固定长度，所以它的处理速度比VARCHAR的速度要快，但是它的缺点就是浪费存储空间。所以对存储不大，但在速度上有要求的可以使用CHAR类型，反之可以使用VARCHAR类型来实现。

存储引擎对于选择CHAR和VARCHAR的影响:
对于MyISAM存储引擎，最好使用固定长度的数据列代替可变长度的数据列。这样可以使整个表静态化，从而使数据检索更快，用空间换时间。
对于InnoDB存储引擎，最好使用可变长度的数据列，因为InnoDB数据表的存储格式不分固定长度和可变长度，因此使用CHAR不一定比使用VARCHAR更好，但由于VARCHAR是按照实际的长度存储，比较节省空间，所以对磁盘I/O和数据存储总量比较好。

22.3 日期和时间类型

DATE：只存储日期，YYYY-MM-DD
对于完整的日期-时间，则有DATETIME和TIMESTAMP。

区别项 DATETIME TIMESTAMP
大小 8字节 4字节
时间范围 1000-01-01 00:00:00/9999-12-31 23:59:59 1970-01-01 00:00:00/2038 （实际上是记录一个4字节的int作为秒数，所以时间范围比较小）
时区转换 无转换 转换成UTC时间
存入NULL时的反应 字面意义上存入NULL 自动存入当前时间

参考：

http://c.biancheng.net/view/7175.html
https://kalacloud.com/blog/difference-between-mysql-datetime-and-timestamp-datatypes/

23. SQL连接（JOIN）

  • 内连接 INNER JOIN：
    交集。
  • 左外连接 LEFT (OUTER) JOIN：
    返回左表的所有行，若某行在右表没有匹配值，则属于右表的字段会是NULL。
  • 右外连接 RIGHT (OUTER) JOIN：
    类似左外连接。
  • 全外连接 FULL (OUTER) JOIN：
    左外连接+右外连接。返回左右表的所有行。
  • 交叉连接 CROSS JOIN：
    笛卡尔积，左表中的每一行和右表中的每一行组合，左表有M行右表有N行那么结果就有MN行

24. 为什么不建议使用select * ？

三个主要问题：

  • 磁盘IO大
  • 浪费网络带宽
  • 【重要】可能导致无意义的回表。例如，对(name, phone)构建联合索引，当select name, phone时，这个二级索引实际上做到了覆盖索引，可以直接通过索引得到需要的数据；而如果select *，为了拿到不包含于此联合索引中的其他列的数据，必须回表。
知识共享许可协议
本作品采用知识共享署名-非商业性使用-相同方式共享 4.0 国际许可协议进行许可。
InnoDBMySQL
暂无评论

发送评论 编辑评论 


				

			

						

				

					

						

							

								
							

							
						

					

				

				

					

						

							

								
							

							
						

					

				

				

					

						

							

								
							

							
							
															
													

					

				

			

			
			

				

					
				

			

				

											

							
							
						

																				
					
											
						

	

		
|´・ω・)ノ
ヾ(≧∇≦*)ゝ
(☆ω☆)
（╯‵□′）╯︵┴─┴
￣﹃￣
(/ω＼)
∠( ᐛ 」∠)＿
(๑•̀ㅁ•́ฅ)
→_→
୧(๑•̀⌄•́๑)૭
٩(ˊᗜˋ*)و
(ノ°ο°)ノ
(´இ皿இ｀)
⌇●﹏●⌇
(ฅ´ω`ฅ)
(╯°A°)╯︵○○○
φ(￣∇￣o)
ヾ(´･ ･｀｡)ノ"
( ง ᵒ̌皿ᵒ̌)ง⁼³₌₃
(ó﹏ò｡)
Σ(っ °Д °;)っ
( ,,´･ω･)ﾉ"(´っω･｀｡)
╮(╯▽╰)╭
o(*////▽////*)q
＞﹏＜
( ๑´•ω•) "(ㆆᴗㆆ)
😂
😀
😅
😊
🙂
🙃
😌
😍
😘
😜
😝
😏
😒
🙄
😳
😡
😔
😫
😱
😭
💩
👻
🙌
🖕
👍
👫
👬
👭
🌚
🌝
🙈
💊
😶
🙏
🍦
🍉
😣
Source: github.com/k4yt3x/flowerhd
	

	

		
颜文字
Emoji
小恐龙
花!
	


									

			

			
			
		

	






上一篇
下一篇 

                    

                    
			        推荐文章
		            

		            

							
非关系型数据库学习笔记-Redis