Delete、Drop和Truncate区别

delete、truncate仅仅删除表里面的数据，drop会把表的结构也删除掉。

delete是DML语句，操作完了，还可以回滚，truncate和drop是DDL语句，删除之后立即生效，不能回滚。

执行效率：drop > truncate > delete

三大范式

第一范式：

确保每列保持原子性：也就是列都是不可再分。

举例说明：

在上面表中 家庭信息和学历信息不满足原子性要求，不满足第一范式，进行调整如下：

第二范式：

首先满足第一范式，并且表中非主键列不存在对主键的部分依赖。

• 第二范式要求每个表只描述一件事情。

数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关。

举例说明：

在上述表中，同一个订单号可以包括不同产品号，因此主键必须是订单号、产品号联合组成。

产品号、产品数量、产品价格与订单号、产品号都相关，但是订单金额 订单时间、订单人仅与订单号相关，与产品号无关。

因此不满足第二范式要求进行调整如下分成两张表。

第三范式:

确保每列都和主键列直接相关,而不是间接相关。

• 满足第二范式，并且表中的列不存在对主键列的传递依赖。

举例说明：

上表所有属性完全依赖于学号，满足第二范式，但是班主任性别、班主任年龄直接依赖于班主任姓名，并非主键学号。

进行调整如下。

MyISAM与InnoDB的区别

InnoDB支持事务，MyISAM不支持。

InnoDB支持外键，MyISAM不支持。

InnoDB是聚集索引，数据文件是和索引绑在一起的。

MyISAM是非聚集索引，索引和数据文件是分离的，索引保存的是数据文件的指针。

InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。

MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快。

InnoDB支持表、行(默认)级锁，MyISAM支持表级锁。

Join语句

left join、right join、inner join的区别：

left join(左连接)：

• 返回包括左表中的所有记录和右表中联结字段相等的记录。
• 左表是驱动表，右表是被驱动表。

right join(右连接)：

• 返回包括右表中的所有记录和左表中联结字段相等的记录。
• 右表是驱动表，左表是被驱动表。

inner join(等值连接)：

• 只返回两个表中联结字段相等的行。
• 数据量比较小的表作为驱动表，大表作为被驱动表。

join查询在有索引条件下：

• 驱动表有索引不会使用到索引。
• 被驱动表建立索引会使用到索引。

所以在以小表驱动大表的情况下，给大表建立索引会大大提高执行速度。

Join原理：

Simple Nested-Loop:

• 驱动表中的每一条记录与被驱动表中的记录进行比较判断（就是个笛卡尔积）。

• 对于两表联接来说，驱动表只会被访问一遍，但被驱动表却要被访问到好多遍。

Index Nested-Loop：

• 基于索引进行连接的算法。
• 它要求被驱动表上有索引，可以通过索引来加速查询。

Block Nested-Loop：

• 它使用Join Buffer来减少内部循环读取表的次数。
• Join Buffer用以缓存联接需要的列。

选择Join算法优先级：

• Index Nested-LoopJoin > Block Nested-Loop Join > Simple Nested-Loop Join。

当不使用Index Nested-Loop Join的时候，默认使用Block Nested-Loop Join。

分页查询优化

select * from table where type = 2 and level = 9 order by id asc limit 190289,10;

延迟关联：

• 先通过where条件提取出主键，在将该表与原数据表关联，通过主键id提取数据行，而不是通过原来的二级索引提取数据行

select a.* from table a, (select id from table where type = 2 and level = 9 order by id asc limit 190289,10 ) b where a.id = b.id

书签方式:

• 找到limit第一个参数对应的主键值，再根据这个主键值再去过滤并limit

select * from table where id > (select * from table where type = 2 and level = 9 order by id asc limit 190289, 1) limit 10;

分区表

MySQL数据库在5.1版本及以上时添加了对分区的支持。

通俗地讲表分区是将一大表，根据条件分割成若干个小表。

但是对于应用程序来讲，分区的表和没有分区的表是一样的。

• 换句话来讲，分区对于应用是透明的，只是数据库对于数据的重新整理。

分区的目的

在执行查询时，优化器根据分区定义过滤那些没有需要的数据的分区。

• 这样查询就可以无需扫描所有分区，只需要查找包含需要数据的分区即可。

将数据按照一个较粗的粒度分别存放在不同的表中。

• 这样做可以将相关的数据存放在一起，另外，当想要一次批量删除整个分区的数据也会变得很方便。

分区表技术不是用于提升 MySQL 数据库的性能，而是方便数据的管理，解决数据迁移和备份的问题。

分区类型：

RANGE 分区：

• 基于属于一个给定连续区间的列值，把多行分配给分区：最常见的是基于时间字段。

LIST 分区：

• LIST 分区和 RANGE 分区类似，区别在于 LIST 是枚举值列表的集合，RANGE 是连续的区间值的集合。

HASH 分区：

• 基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。

KEY 分区：

• 类似于按 HASH 分区，区别在于 KEY 分区只支持计算一列或多列，且 MySQL 服务器提供其自身的哈希函数。
• 必须有一列或多列包含整数值。

分区表注意事项：唯一索引:

在 MySQL 数据库中，分区表的索引都是局部，而非全局。

也就是说，索引在每个分区文件中都是独立的，所以分区表上的唯一索引必须包含分区列信息，否则创建会报错。

事务

ACID

原子性（atomicity）:

• 指事务是一个不可分割的工作单位，要么全部提交，要么全部失败回滚。

一致性（consistency）：

• 指事务执行前后，数据从一个合法性状态变换到另外一个合法性状态。
  • 这种状态是语义上的而不是语法上的，跟具体的业务有关。

隔离型（isolation）：

• 指一个事务的执行不能被其他事务干扰。
• 即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

持久性（durability）：

• 指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，其他操作和数据库故障不应该对其有任何影响。

数据并发产生的问题

丢失更新（Lost Update）

• 两个事务 Session A、Session B，如果事务Session A 修改了另一个未提交事务Session B 修改过的数据，意味着发生了丢失更新。

脏读（ Dirty Read ）：

• 两个事务 Session A、Session B，Session A 读取了已经被 Session B 更新但还没有被提交的字段。
  • 之后如果 Session B 回滚，那么Session A 读取的内容就是临时且无效的。

不可重复读（ Non-Repeatable Read ）：

• 两个事务Session A、Session B，Session A 读取了一个字段，然后 Session B 更新了该字段。
  • 之后 Session A 再次读取同一个字段，该字段的值发生了变化。

幻读（ Phantom ）：

• 两个事务Session A、Session B, Session A 从一个表中读取了一个字段, 然后 Session B 在该表中插入了一些新的行。
  • 如果 Session A 再次读取同一个表, 就会多出几行。

事务隔离级别

READ UNCOMMITTED（读未提交）：

• 所有事务都可以看到其他未提交事务的执行结果，不能避免脏读、不可重复读、幻读。

READ COMMITTED（读已提交）：

• 一个事务只能看见已经提交事务所做的改变。
• 可以避免脏读，但不可重复读、幻读问题仍然存在。

REPEATABLE READ（重复读）：

• 事务A在读到一条数据之后，此时事务B对该数据进行了修改并提交，事务A再读该数据，读到的还是原来的内容。
• 可以避免脏读、不可重复读，但幻读问题仍然存在，这是MySQL的默认隔离级别。

SERIALIZABLE（可串行化）：

• 确保事务可以从一个表中读取相同的行。

MySQL默认隔离级别为什么是可重复读

MySQL在5.0这个版本以前，binlog只支持STATEMENT这种格式，而这种格式在读已提交(Read Commited)这个隔离级别下主从复制是有bug的，因此MSQL将可重复读(Repeatable Read)作为默认的隔离级别。

STATEMENT主从复制的Bug？

• 在master上执行的顺序为先删后插，而此时binlog为STATEMENT格式，它记录的顺序为先插后删。

• 从(slave)同步的是binglog，如果从机执行的顺序和主机不一致，就会出现主从不一致。

隔离级别设为可重复读(Repeatable Read)，在该隔离级别下引入间隙锁。

• 当Session 1执行delete语句时，会锁住间隙。

• 那么，Ssession 2执行插入语句就会阻塞住。

保证主从复制不出问题。

为什么大家将隔离级别设为读已提交(Read Commited)

在RR隔离级别下，存在间隙锁，导致出现死锁的几率比RC大的多。

在RR隔离级别下，条件列未命中索引会锁表，而在RC隔离级别下，只锁行。

在RC隔离级别下，半一致性读(semi-consistent)特性增加了update操作的并发性。

半一致性读：一个update语句，如果读到一行已经加锁的记录，此时InnoDB返回记录最近提交的版本，由MySQL上层判断此版本是否满足update的where条件。

• 若满足(需要更新)，则MySQL会重新发起一次读操作，此时会读取行的最新版本(并加锁)。

有两个Session，Session1和Session2：

Session1执行：

update test set color = 'blue' where color = 'red'; 

先不Commit事务。

与此同时Ssession2执行：

update test set color = 'blue' where color = 'white';

session 2尝试加锁的时候，发现行上已经存在锁，InnoDB会开启semi-consistent read，返回最新的committed版本(1,red),(2，white),(5,red),(7,white)。

MySQL会重新发起一次读操作，此时会读取行的最新版本(并加锁)。

而在RR隔离级别下，Session2只能等待。

互联网项目请用：读已提交(Read Commited)这个隔离级别。

事务实现原理

原子性：使用undo log（回滚日志），undo log记录了回滚需要的信息。

• 当事务执行失败或调用了rollback，导致事务需要回滚，便可以利用undo log中的信息将数据回滚到修改之前的样子。

隔离性： 使用悲观锁和乐观锁对事务处理。

持久性：

• 使用redo log（重写日志）。

一致性：通过原子性、隔离性、持久性来保证一致性。

不同隔离级别，都使用了什么锁？

对于任何隔离级别，表级别的表锁、元数据锁、意向锁都是会使用的，但对于行级别的锁则会有些许差别。

在 读未提交 和 读已提交 隔离级别下，都只会使用记录锁，不会用间隙锁，当然也不会有 Next-Key 锁了。

可重复读 隔离级别，会使用记录锁、间隙锁和 Next-Key锁。

MVCC

MVCC是为了解决 读-写 之间阻塞的问题（排他锁会阻塞读操作），写操作还是需要加锁（Next-Key Lock）。

如果没有MVCC，那么修改数据的操作会加排它锁，其它的读写操作都会阻塞，这样的话效率会比较低。

MVCC通过Undo Log + Read View进行数据读取。

• Undo Log保存了历史快照。
• Read View规则判断当前版本的数据是否可见。

快照读与当前读

快照读：

快照读读取的是快照数据。

• 不加锁的简单的 SELECT 都属于快照读，即不加锁的非阻塞读。

快照读的实现是基于MVCC，它在很多情况下，避免了加锁操作，降低了开销。

• 既然是基于多版本，那么快照读可能读到的 并不一定是数据的最新版本，而 有可能是之前的历史版本。

快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读。

SELECT * FROM player WHERE ...

当前读:

读取的是记录的 最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。

加锁的 SELECT，或者对数据进行增删改都会进行当前读。

SELECT * FROM student LOCK IN SHARE MODE; # 共享锁
SELECT * FROM student FOR UPDATE; # 排他锁
INSERT INTO student values ... # 排他锁
DELETE FROM student WHERE ... # 排他锁
UPDATE student SET ... # 排他锁

Undo Log版本链

InnoDB 聚簇索引记录中包含 3 个隐藏的列：

DB_ROW_ID（隐藏的自增 ID）：

• 如果表没有主键，InnoDB 会自动按 ROW ID 产生一个聚集索引树。

DB_TRX_ID（事务ID）：

• 记录最近更新这条行记录的事务 ID，大小为 6 个字节。

DB_ROLL_PTR（回滚指针） ：

• 指向该行回滚段的指针，大小为 7 个字节。
• 该行记录上所有旧版本，在 undo 中都通过链表的形式组织。

每次对记录进行改动，都会记录一条undo日志，每条undo日志都有一个roll_pointer属性。

将这些undo日志都连起来，串成一个链表，这个链表称之为 版本链。

• 版本链的 头节点 就是当前记录 最新的值。
• 每个版本中包含生成该版本时对应的 事务id。

ReadView

ReadView就是事务在使用MVCC机制进行快照读操作时产生的读视图。

判断一下版本链中的哪个版本是当前事务可见的，这是ReadView要解决的主要问题。

ReadView主要包含4个内容：

creator_trx_id：

• 创建这个 Read View 的事务 ID。

trx_ids：

• 在生成ReadView时当前系统中 活跃的 读写事务的事务id列表(活跃：启动了但还没提交)。

up_limit_id：

• 活跃的 事务中 最小的事务 ID。

low_limit_id：

• 表示生成ReadView时系统中应该分配给下一个事务的id 值。

ReadView规则:

如果被访问版本的 trx_id 属性值与 ReadView 中的 creator_trx_id 值 相同:

• 意味着当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问。

如果被访问版本的 trx_id 属性值 小于 ReadView 中的 up_limit_id 值:

• 表明生成该版本的事务在当前事务生成ReadView前已经提交，所以该版本可以被当前事务访问。

如果被访问版本的 trx_id 属性值 大于或等于 ReadView中的 low_limit_id 值:

• 表明生成该版本的事务在当前事务生成ReadView后才开启，所以该版本 不可以 被当前事务访问。

如果被访问版本的 trx_id 属性值在 ReadView 的 up_limit_id 和 low_limit_id 之间。

需要判断一下trx_id属性值是不是在trx_ids 列表中。

• 如果在，说明创建ReadView时生成该版本的事务还是活跃的，该版本不可以被访问。

• 如果不在，说明创建ReadView时生成该版本的事务已经被提交，该版本可以被访问。

MVCC整体操作流程

首先 获取事务自己的版本号，也就是事务 ID。

获取 ReadView。

查询得到的数据，然后与 ReadView 中的事务版本号进行比较。

如果不符合 ReadView 规则，就需要从 Undo Log 中获取历史快照。

最后 返回符合规则的数据。

如果某个版本的数据对当前事务不可见的话，那就顺着版本链找到下一个版本的数据，继续按照上边的步骤判断可见性。

• 依此类推，直到版本链中的最后一个版本。

如果最后一个版本也不可见的话，那么就意味着该条记录对该事务完全不可见，查询结果就不包含该记录。

READ COMMITTED事务，在每次查询开始时都会生成一个独立的ReadView。

REPEATABLE READ事务，只会在第一次执行查询语句时生成一个ReadView，之后的查询就不会重复生成了。

MVCC幻读被彻底解决了吗

可重复读隔离级别（默认隔离级），根据不同的查询方式，分别提出了避免幻读的方案：

• 针对快照读（普通 select 语句），是通过 MVCC 方式解决了幻读。
• 针对当前读（select ... for update 等语句），是通过 next-key lock（记录锁+间隙锁）方式解决了幻读。

索引

索引分类

单列索引：基于单个字段建立的索引。

唯一索引：

• 指索引中的索引节点值不允许重复，一般配合唯一约束使用。

主键索引：

• 一种特殊的唯一索引，和普通唯一索引的区别在于不允许有空值。

普通索引：

• 通过KEY、INDEX关键字创建的索引。

多列索引：由多个字段组合建立的索引。

聚簇索引和非聚簇索引:

聚簇索引（主键索引）：

• 叶子节点存放的是实际数据，所有完整的用户数据都存放在聚簇索引的叶子节点。

非聚簇索引（二级索引）：

• 叶子节点存放的是主键值，而不是实际数据。

二级索引：

使用 二级索引 字段作为条件查询的时候，如果要查询的数据都在 聚簇索引 的叶子节点里，需要检索两颗B+树：

• 先在 二级索引 的 B+ 树找到对应的叶子节点，获取主键值。
• 然后用上一步获取的主键值，在 聚簇索引 中的 B+ 树检索到对应的叶子节点，然后获取要查询的数据。

这个过程叫做回表。

全文索引：

类似于ES、Solr搜索中间件中的分词器。

只能创建在CHAR、VARCHAR、TEXT等这些文本类型字段上，而且使用全文索引查询时，条件字符数量必须大于3才生效。

哪些情况下适合建索引

作为where条件语句查询的字段。

关联字段需要建立索引。

排序字段可以建立索引。

分组字段可以建立索引。

统计字段可以建立索引，如count()，max()。

哪些情况下不适合建索引 　

频繁更新的字段不适合建立索引。

where条件中用不到的字段不适合建立索引。

表数据比较少的不需要建索引。

数据重复且发布比较均匀的的字段不适合建索引。

参与列计算的列不适合建索引。

索引覆盖

要查询的列，在使用的索引中已经包含，这种情况称之为索引覆盖。

索引区分度

索引的区分度等于：count(distinct 具体的列)/count(*)，表示字段不重复的比例。

唯一键的区分度是1，而对于一些状态值，性别等字段区分度往往比较低，在数据量比较大的情况下，甚至有无限接近0。

索引结构

InnoDB中使用了B+树来实现索引。

• 所谓的索引就是一颗 B+ 树，一个表有多少个索引就会有多少颗 B+ 树。

InnoDB的整数字段建立索引为例：

• 一个页默认16kb，整数（bigint）字段的长度为8B，另外还跟着6B的指向其子树的指针，这意味着一个索引页可以存储接近1200条数据(16kb/14B ≈ 1170)。
• 如果这颗B+树高度为4，就可以存1200的3次方的值，差不多17亿条数据。

考虑到树根节点总是在内存中的，树的第二层很大概率也在内存中，所以一次搜索最多只需要访问2次磁盘IO。

假设1亿数据量的表，根据主键id建立了B+树索引，搜索id=2699的数据，流程如下：

• 内存中直接获取树根索引页，对树根索引页内的目录进行二分查找，定位到第二层的索引页。
• 内存中直接获取第二层的索引页，对索引页内的目录进行二分查找，定位到第三层的索引页。
• 从磁盘加载第三层的索引页到内存中，对索引页内的目录进行二分查找，定位到第四层数据页。
• 从磁盘加载第四层的数据页到内存中，数据页变成缓存页，对缓存页中的目录进行二分查找，定位到具体的行数据。

B+树

B+ 树与 B 树差异点：

• 叶子节点（最底部的节点）才会存放实际数据（索引+记录），非叶子节点只会存放索引。

• 所有索引都会在叶子节点出现，叶子节点之间构成一个有序链表。

  • Innodb 的 B+ 树的叶子节点之间是用 双向链表 进行连接，既能向右遍历，也能向左遍历。

• 非叶子节点的索引也会同时存在在子节点中，并且是在子节点中所有索引的最大（或最小）。

• 非叶子节点中有多少个子节点，就有多少个索引。

B+ 树的非叶子节点不存放实际的记录数据，仅存放索引。

• 因此数据量相同的情况下，相比存储即存索引又存记录的 B 树，B+树的非叶子节点可以存放更多的索引。

因此 B+ 树可以比 B 树更 矮胖 ，查询底层节点的磁盘 I/O次数会更少。

MyISAM 存储引擎：

• B+ 树索引的叶子节点保存数据的物理地址，即用户数据的指针。

InnoDB 存储引擎：

• B+ 树索引的叶子节点保存数据本身。

为什么使用B+树而不使用跳表？

B+树 是多叉树结构，每个结点都是一个16k的数据页，能存放较多索引信息。

• 三层左右可以存储2kw左右的数据，查询一次数据，如果这些数据页都在磁盘里，最多需要查询三次磁盘IO。

跳表是链表结构，一条数据一个结点，如果最底层要存放2kw数据，且每次查询都要能达到二分查找的效果，2kw大概在2的24次方左右，所以，跳表大概高度在24层左右。

• 最坏情况下，这24层数据会分散在不同的数据页里，也即是查一次数据会经历24次磁盘IO。

针对写操作，B+树需要拆分合并索引数据页，跳表则独立插入，并根据随机函数确定层数，没有旋转和维持平衡的开销，因此跳表的写入性能会比B+树要好。

为什么Redis有序集合底层选择跳表而非B+树

Redis是基于内存的数据库，因此不需要考虑磁盘IO。

• B+树在数据写入时，存在拆分和合并数据页的开销，目的是为了保持树的平衡。
• 跳表在数据写入时，只需要通过随机函数生成当前节点的层数即可，然后更新每一层索引，往其中加入一个节点，相比于B+树而言，少了旋转平衡带来的开销。

由于跳表的查询复杂度在O(logn)，因此Redis中Zset数据类型底层结合使用skiplist和hash，用空间换时间，利用跳表支持范围查询和有序查询，利用Hash支持精确查询。

为什么建议使用自增ID作为主键

MySQL 在底层以数据页为单位来存储数据的，一个数据页大小默认为 16k，也就是说如果一个数据页存满了，MySQL 就会去申请一个新的数据页来存储数据。

• 如果主键为自增 ID 的话，MySQL 在写满一个数据页的时候，直接申请另一个新数据页接着写就可以了。
• 如果主键是非自增 ID，为了确保索引有序，MySQL 就需要将每次插入的数据都放到合适的位置上。

当往一个快满或已满的数据页中插入数据时，新插入的数据会将数据页写满，MySQL 就需要申请新的数据页。

并且把上个数据页中的部分数据挪到新的数据页上。这就造成了页分裂，这个大量移动数据的过程是会严重影响插入效率的。

索引下推ICP(Index Condition Pushdown)

一张user表，有age_name的联合索引。

执行查询explain SELECT * from user where age >10 and name = 'a'。

• 看见Extra中显示了Using index condition，表示出现了索引下推。

ICP流程：

• 联合索引首先通过条件找到所有age>10的数据，根据联合索引中已经存在的name数据进行过滤，找到符合条件的数据。

索引合并

在MySQL 5.1之后进行引入，它可以在多个索引上进行查询，并将结果合并返回。

索引失效

1、不满足最左匹配原则

2、索引列上有计算

explain select * from 某表 where id+1=2;

3、索引列用了函数

explain select * from 某表 where SUBSTR(height,1,2)=17;

4、字段类型不同

5、like左边包含%

6、列对比

• id字段本身是有主键索引的，同时height字段也建了普通索引的，并且两个字段都是int类型，类型是一样的

• 如果把两个单独建了索引的列，用来做列对比时索引就会失效

explain select * from user where id=height

7、使用or关键字

因为最后加的address字段没有加索引，从而导致其他字段的索引都失效了

如果使用了or关键字，那么它前面和后面的字段都要加索引，不然所有的索引都会失效

explain select * from user where id=1 or height='175' or address='成都';

8、not in和not exists

主键字段中使用not in关键字查询数据范围，可以走索引

而普通索引字段使用了not in关键字查询数据范围，索引会失效

9、order by 没加where或limit

如果order by语句中没有加where或limit关键字，该sql语句将不会走索引

explain select * from user order by code, name;

10、对不同的索引做order by

explain select * from user order by code, height limit 100;

CBO

CBO（Cost-Based Optimizer，基于成本的优化器）。

优化器的选择是基于成本（cost），哪个索引的成本越低，优先使用哪个索引。

一条 SQL 的计算成本计算如下：

Cost  = Server Cost + Engine Cost
      = CPU Cost + IO Cost

CPU Cost：

• 表示计算的开销，比如索引键值的比较、记录值的比较、结果集的排序……这些操作都在 Server 层完成。

IO Cost：

• 计算读取内存 IO 开销以及读取磁盘 IO 的开销。

Explain

table列:

表示 explain 的一行正在访问哪个表。

当 from 子句中有子查询时，table列是 <derivenN> 格式：

• 表示当前查询依赖 id=N 的查询，先执行 id=N 的查询。
• 当有 union 时，UNION RESULT 的 table 列的值为<union1,2>，1和2表示参与 union 的 select 行id。

id列：

id列的编号是 select 的序列号，有几个 select 就有几个id，并且id的顺序是按 select 出现的顺序增长的。

• id列越大执行优先级越高，id相同则从上往下执行，id为NULL最后执行。

select_type列：

SIMPLE：

• 查询语句中不包含UNION或者子查询的查询都算作是SIMPLE类型。

PRIMARY：

• 对于包含UNION、UNION ALL或者子查询的大查询来说，它是由几个小查询组成。
• 其中最左边的那个查询的select_type值就是PRIMARY。

type列：

从最优到最差分别为：system > const > eq_ref > ref > range > index > ALL。

• 一般来说，得保证查询达到range级别，最好达到ref。

eq_ref：

• primary key 或 unique key 索引使用，最多只会返回一条符合条件的记录。

ref:

• 当通过普通二级索引来查询某个表，那么对该表的访问方法就可能是ref。
• 相比 eq_ref，不使用唯一索引，可能会找到多个符合条件的行。

ref_or_null：

• 当对普通二级索引查询，该索引列的值可以是NULL值时，那么对该表的访问方法就可能是ref_or_null

range：

• 范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。
• 使用一个索引来检索给定范围的行。

index:

• 当使用索引覆盖，但需要扫描全部的索引记录时，该表的访问方法就是index。

index_merge：

• 一般情况下对于某个表的查询只能使用到一个索引，但在某些场景下可以使用多种索引合并的方式来执行查询。

ALL：

• 全表扫描。

possible_keys列：

显示查询可能使用哪些索引来查找。

key_len列:

显示了索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。

key_len计算规则如下：

字符串：

• char(n)：n字节，varchar(n)：2字节，如果是utf-8，则长度 3n + 2

数值类型：

• tinyint：1字节，smallint：2字节，int：4字节，bigint：8字节

　

时间类型：

• date：3字节，timestamp：4字节，datetime：8字节

rows列：

读取并检测的行数，注意这个不是结果集里的行数。

• 如果使用全表扫描查询时，执行计划的rows列就代表预计需要扫描的行数。

• 如果使用索引查询时，执行计划的rows列就代表预计扫描的索引记录行数。

ref列：

显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），字段名。

• ref列展示的就是与索引列作等值匹配的值什么，比如只是一个常数或者是某个列。

Extra列：

Using index

• 查询的列被索引覆盖，并且where筛选条件是索引的前导列。
• 一般是使用了覆盖索引(索引包含了所有查询的字段)。

Using where

• 使用全表扫描来执行对某个表的查询，并且该语句的WHERE子句中有针对该表的搜索条件时。

Using where Using index

• 查询的列被索引覆盖，并且where筛选条件是索引列之一但是不是索引的前导列。
  • 意味着无法直接通过索引查找来查询到符合条件的数据。

Using index condition

• 查询的列不完全被索引覆盖，where条件中是一个前导列的范围。

Using temporary

• 在执行DISTINCT、GROUP BY、UNION等子句的查询时，如果不能利用索引来完成查询，MySQL通过建立内部的临时表来执行查询。

Using filesort

• 会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。

日志文件

MySQL日志主要包括错误日志、查询日志、慢查询日志、事务日志、二进制日志。

binlog

binlog用于记录数据库执行的写入性操作(不包括查询)信息，以二进制的形式保存在磁盘中。

• binlog是mysql的逻辑日志，并且由Server层进行记录，使用任何存储引擎的mysql数据库都会记录binlog日志。
• 逻辑日志：可以理解为记录的是sql语句。
• 物理日志：因为mysql数据最终是保存在数据页中的，物理日志记录的就是数据页变更。

binlog是通过追加的方式进行写入的。

通过max_binlog_size参数设置每个binlog文件的大小。

• 当文件大小达到给定值之后，会生成新的文件来保存日志。

binlog使用场景:

主从复制和数据恢复

binlog刷盘时机：

对于InnoDB存储引擎而言，只有在事务提交时才会记录biglog，此时记录还在内存中。

biglog什么时候刷到磁盘中？

• mysql通过sync_binlog参数控制biglog的刷盘时机，取值范围是0-N：
  • 0：不去强制要求，由系统自行判断何时写入磁盘。
  • 1：每次commit的时候都要将binlog写入磁盘。
  • N：每N个事务，才会将binlog写入磁盘。

binlog日志格式：

STATMENT:

• 基于SQL语句的复制，每一条会修改数据的sql语句会记录到binlog中。

• 优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO，从而提高了性能。

• 缺点：在某些情况下会导致主从数据不一致，比如执行sysdate()、slepp()等。

ROW:

• 基于行的复制，记录哪条数据被修改了。

• 缺点：会产生大量的日志，alter table的时会让日志暴涨。

MIXED：

• 基于STATMENT和ROW两种模式的混合复制。
• 一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog。

在 MySQL 5.7.7 之前，默认的格式是STATEMENT，MySQL 5.7.7之后，默认值是ROW。

redo log

记录事务对数据页做了哪些修改

redo log包括两部分：

• 内存中的日志缓冲(redo log buffer)。
• 磁盘上的日志文件(redo log file)。

mysql每执行一条DML语句，先将记录写入redo log buffer，后续某个时间点再一次性将多个操作记录写到redo log file。

• 这种先写日志，再写磁盘的技术就是WAL(Write-Ahead Logging) 技术。

mysql支持三种将redo log buffer写入redo log file的时机，通过innodb_flush_log_at_trx_commit参数配置。

redo log记录形式：

redo log采用了大小固定，循环写入的方式，当写到结尾时，会回到开头循环写日志。

redo log与bin log区别:

undo log

undo log主要记录了数据的逻辑变化。

比如一条INSERT语句，对应一条DELETE的undo log。

对于每个UPDATE语句，对应一条相反的UPDATE的undo log，这样在发生错误时，就能回滚到事务之前的数据状态。

锁机制

锁结构

当一个事务想对这条记录做改动时，会看看内存中有没有与这条记录关联的锁结构。

• 当没有的时候就会在内存中生成一个锁结构与之关联。

锁结构信息：

• trx信息：这个锁结构是哪个事务生成的。
• is_waiting : 当前事务是否在等待。

锁分类

读锁、写锁：

读锁：

• 共享锁，用S表示。
  • 针对同一份数据，多个事务的读操作可以同时进行而不会互相影响，相互不阻塞的。

写锁：

• 排他锁，用X表示。
  • 当前写操作没有完成前，它会阻断其他写锁和读锁。
  • 这样就能确保在给定的时间里，只有一个事务能执行写入，并防止其他用户读取正在写入的同一资源。

对读取的记录加S锁：

SELECT ... LOCK IN SHARE MODE;
# 或
SELECT ... FOR SHARE; # (8.0新增语法)

对读取的记录加X锁：

SELECT ... FOR UPFATE;

表锁:

LOCK TABLES t READ:

• 对表t加表级别的S锁。

LOCK TABLES t WRITE:

• 对表t加表级别的X锁。

意向锁：

有两个事务，分别是T1和T2，其中T2试图在该表级别上应用共享或排它锁：

• 如果没有意向锁，T2就需要去检查各个页或行是否存在锁。
• 如果存在意向锁，那么此时就会受到由T1控制的表级别意向锁的阻塞。

如果事务想要获得数据表中某些记录的共享锁，需要在数据表上添加意向共享锁。

如果事务想要获得数据表中某些记录的排他锁，需要在数据表上添加意向排他锁。

• 意向锁会告诉其他事务已经有人锁定了表中的某些记录。

在为数据行加共享/排他锁之前，InooDB会先获取该数据行所在数据表的对应意向锁。

元数据锁（MDL锁）：

MDL 的作用是 保证读写的正确性。

• 如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，增加了一列，那么查询线程拿到的结果跟表结构对不上。

当对一个表做增删改查操作的时候，加 MDL读锁。

当要对表做结构变更操作的时候，加 MDL 写锁。

行锁：

行锁(Row Lock)也称为记录锁，就是锁住某一行（某条记录Row)。

间隙锁（Gap Locks）:

MySQL解决幻读问题方案有两种：

• 使用 MVCC。
• 采用加锁（Gap Locks）。

图中ID值为8的记录加了Gap锁，意味着 不允许别的事务在id值为8的记录前边的间隙插入新记录。

• ID列的值(3, 8)这个区间的新记录是不允许立即插入的。

间隙锁的实现：

当一个事务对索引范围内的记录执行查询时，MySQL会在查询的范围内对记录和记录之间的间隙进行加锁。

对于范围查询，MySQL会在范围内的记录和记录之间的间隙加锁，以确保其他事务无法在这个范围内插入新记录。

间隙锁是Next-Key Locks的一种特殊形式，同时锁定记录和记录之间的间隙。

• 防止幻读和不可重复读等并发问题的发生。

间隙锁可能会导致一些并发性能问题，特别是在高并发写入场景下。

• 因此，在使用间隙锁时需要谨慎评估并发控制的成本和性能影响。

临键锁（Next-Key Locks）:

既想锁住某条记录，又想阻止其他事务在该记录前边的间隙插入新记录，InnoDB就提出了Next-Key Locks。

• 它是在 可重复读 的情况下使用的数据库锁，Innodb默认的锁就是Next-Key Locks。

1、update 命令会施加一个 X 型记录锁，X 型记录锁是写写互斥的。

• 如果 A 事务对 goods 表中 id = 1 的记录行加了记录锁，B 事务想要对这行记录加记录锁就会被阻塞。

2、insert 命令会施加一个插入意向锁，但插入意向锁是互相兼容的。

• 如果 A 事务向 order 表 insert 一条记录，不会影响 B 事务 insert 一条记录。

select……for update锁表还是锁行

如果查询条件用了索引/主键，select ..... for update 会进行行锁。

如果是普通字段（没有索引/主键）， select ..... for update 会进行锁表。

如何有效的避免死锁的发生

设置事务等待锁的超时时间：

• 当一个事务的等待时间超过该值后，就对这个事务进行回滚，于是锁就释放了，另一个事务就可以继续执行了。
• 在 InnoDB 中，参数 innodb_lock_wait_timeout 是用来设置超时时间的，默认值时 50 秒。

开启主动死锁检测：

• 主动死锁检测在发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。
• 将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑，默认就开启。

修改数据库隔离级别为RC：

• MySql默认级别为RR，RC没有间隙锁Gap Lock和组合锁Next-Key Lock，能一定程度的避免死锁的发生。

尽量少使用当前读for update，数据更新时尽量使用主键。

悲观锁

当竞争不激烈 (出现并发冲突的概率小)时，乐观锁更有优势。

• 因为悲观锁会锁住代码块或数据，其他线程无法同时访问，影响并发，而且加锁和释放锁都需要消耗额外的资源。

当竞争激烈(出现并发冲突的概率大)时，悲观锁更有优势。

• 因为乐观锁在执行更新时频繁失败，需要不断重试，浪费CPU资源。

乐观锁

乐观锁假设认为数据一般情况下不会造成冲突。

• 所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测。

如果发现冲突了，则让返回用户错误的信息，让用户决定如何去做。

使用版本号实现乐观锁

• 版本号的实现方式有两种，一个是数据版本机制，一个是时间戳机制。

使用数据版本（Version）记录机制实现：

• 为数据增加一个版本标识，一般是通过为数据库表增加一个数字类型的 version 字段来实现。

• 当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值加一。

• 当提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对：

  • 如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据。

这种版本号的方法并不是适用于所有的乐观锁场景。

当电商抢购活动时，大量并发进入，如果仅仅使用版本号或者时间戳，就会出现大量的用户查询出库存存在。

• 但是却在扣减库存时失败了，而这个时候库存是确实存在的。

update t_goods 
set status=2,version=version+1
where id=#{id} and version=#{version};

使用条件限制实现乐观锁

这个适用于做数据安全校验，适合库存模型，扣份额和回滚份额，性能更高。

更新库存操作如下：

• 注意：乐观锁的更新操作，最好用主键或者唯一索引来更新，这样是行锁，否则更新时会锁表。

UPDATE t_goods
SET num = num - #{buyNum} 
WHERE
    id = #{id} 
AND num - #{buyNum} >= 0 
AND STATUS = 1

基本原理

服务器处理客户端请求:

不论客户端进程和服务器进程是采用哪种方式进行通信，最后实现的效果都是：

• 客户端进程向服务器进程发送一段文本（MySQL语句），服务器进程处理后再向客户端进程发送一段文本（处理结果）。

连接管理：

每当有一个客户端进程连接到服务器进程时，服务器进程都会创建一个线程来专门处理与这个客户端的交互。

当该客户端退出时会与服务器断开连接，服务器并不会立即把与该客户端交互的线程销毁掉，而是把它缓存起来。

在另一个新的客户端再进行连接时，把这个缓存的线程分配给该新客户端。

这样就起到了不频繁创建和销毁线程的效果，从而节省开销。

MySQL服务器会为每一个连接进来的客户端分配一个线程，但是线程分配的太多了会严重影响系统性能。

• 所以也需要限制一下可以同时连接到服务器的客户端数量。

查询缓存：

MySQL服务器会把刚刚处理过的查询请求和结果缓存起来，如果下一次有一模一样的请求过来，直接从缓存中查找结果就好了。

这个查询缓存可以在不同客户端之间共享，也就是说如果客户端A刚刚查询了一个语句，而客户端B之后发送了同样的查询请求，那么客户端B的这次查询就可以直接使用查询缓存中的数据。

如果两个查询请求在任何字符上的不同（例如：空格、注释、大小写），都会导致缓存不会命中。

如果查询请求中包含某些系统函数、用户自定义变量和函数、一些系统表，如 mysql 、information_schema、 performance_schema 数据库中的表，那这个请求就不会被缓存。

MySQL的缓存系统会监测涉及到的每张表，只要该表的结构或者数据被修改，如对该表使用了INSERT、 UPDATE、DELETE、TRUNCATE TABLE、ALTER TABLE、DROP TABLE或 DROP DATABASE语句，那使用该表的所有高速缓存查询都将变为无效并从高速缓存中删除。

从MySQL 5.7.20开始，不推荐使用查询缓存，并在MySQL 8.0中删除。

存储引擎：

各种不同的存储引擎向MySQL server层提供统一的调用接口（也就是存储引擎API），包含了几十个底层函数。

在MySQL server完成了查询优化后，只需按照生成的执行计划调用底层存储引擎提供的API，获取到数据后返回给客户端就好了。

Buffer Pool

Buffer Pool作为缓存页。

• MySQL数据以页为单位，每页默认16KB，称为数据页，在Buffer Pool里面会划分出若干个缓存页与数据页对应。

缓存页的元数据信息（描述数据）：

• 它与缓存页一一对应，包含一些所属表空间、数据页的编号、Buffer Pool中的地址等。

后续对数据的增删改查都在Buffer Pool里操作：

• 查询：从磁盘加载到缓存，后续直接查缓存。
• 插入：直接写入缓存。
• 更新删除：缓存中存在直接更新，不存在加载数据页到缓存更新。

缓存页哈希表:

如何在Buffer Pool里快速定位到对应的缓存页？

• 使用哈希表来缓存它们间的映射关系，时间复杂度是O(1)。

• 表空间号+数据页号，作为一个key，缓存页的地址作为value。

• 每次加载数据页到空闲缓存页时，就写入一条映射关系到缓存页哈希表中。

后续的查询，就可以通过缓存页哈希表路由定位了。

Free链表：

使用链表结构，把空闲缓存页的描述数据放入链表中，这个链表称为free链表。

• 往描述数据与缓存页写入数据后，就将该描述数据移出free链表。

Flush链表:

如果修改了Buffer Pool中某个缓冲页的数据，那么它就与磁盘上的页不一致了，这样的缓冲页也被称之为脏页。

创建一个存储脏页的链表，凡是被修改过的缓冲页对应的控制块都会作为节点加入到这个链表中。

• 该链表也被称为Flush链表。

后续异步线程都从flush链表刷缓存页，当Buffer Pool内存不足时，也会优先刷Flush链表里的缓存页。

LRU链表:

借鉴LRU算法思想，把最少使用的缓存页淘汰（命中率低），提供LRU链表出来。

• 当free链表为空的时候，直接淘汰LRU链表尾部缓存页即可。

LRU不足

磁盘预读：

如果按照简单LRU的思路实现内存淘汰，可能会导致部分真正的热点数据被预读的数据淘汰掉。

而预读的数据又不一定被使用到，之后缓存命中率就会下降。

全表扫描:

全表扫描的过程其实也会不断的把数据页加载到Buffer Pool中。

比如数据库备份时，就会把缓存中原有的热点数据淘汰，最终降低缓存命中率。

冷热数据分离设计

给LRU链表做冷热数据分离设计，把LRU链表按一定比例，分为冷热区域，热区域称为young区域，冷区域称为old区域。

数据页第一次加载进缓存页的时候，是先放入冷数据区域的头部，如果1秒后再次访问缓存页，则会移动到热区域的头部。

这样就保证了预读机制与全表扫描加载的数据都在链表队尾。

ChangeBuffer

ChangeBuffer是InnoDB缓存区的一种特殊的数据结构，当用户执行SQL对非唯一索引进行更改时，如果索引对应的数据页不在缓存中时，InnoDB不会直接加载磁盘数据到缓存数据页中，而是缓存对这些更改操作。

• 这些更改操作可能由插入、更新或删除操作(DML)触发。

ChangeBuffer用于存储SQL变更操作，比如Insert/Update/Delete等SQL语句。

ChangeBuffer中的每个变更操作都有其对应的数据页，并且该数据页未加载到缓存中。

当ChangeBufferd中变更操作对应的数据页加载到缓存中后，InnoDB会把变更操作Merge到数据页上。

InnoDB会定期加载ChangeBuffer中操作对应的数据页到缓存中，并Merge变更操作。

在什么场景下会触发ChangeBuffer的Merge操作?

访问变更操作对应的数据页。

InnoDB后台定期Merge。

数据库BufferPool空间不足。

数据库正常关闭时。

RedoLog写满时。

为什么ChangeBuffer只缓存非唯一索引数据?

由于唯一索引需要进行唯一性校验，所以对唯一索引进行更新时必须将对应的数据页加载到缓存中进行校验，从而导致ChangeBuffer失效。

ChangeBuffer适用场景:

数据库大部分索引是非唯一索引。

业务是写多读少，或者不是写后立刻读取。

普通索引还是唯一索引?

从索引修改角度来看：

• 由于非唯一索引无法使用ChangeBuffer，对索引的修改会引起大量的磁盘IO，影响数据库性能。

如果不是业务中要求数据库对某个字段做唯一性检查，最好使用普通索引而不是唯一索引。

SQL查询

一条查询语句的执行过程一般是经过连接器、分析器、优化器、执行器等阶段后，最后到达存储引擎。

SQL更新

SQL写入

先去看缓冲区有没有这条数据，如果有就不进行缓存。

• 将要修改的那一行数据所在的一整页加载到缓冲池Buffer Pool中。

将旧值写入undo日志中，便于回滚以及mvcc机制的运作。

将Buffer Pool中的数据更新为新的数据。

写入redo日志缓冲池，redo日志的内容实际和binlog差不多，但是作用不同。

• 什么时候刷新buffer log到redo log日志，有innodb_flush_log_at_trx_commit参数可以控制

同时innodb写入磁盘用了两段提交。

准备提交事务前，将redo日志写入磁盘。

准备提交日志前，将binlog日志写入磁盘。

将commit标记写到redo日志中，事务提交完成。

• 该操作时为了保证事务提交后redo和binlog数据一致性。

将缓冲区的数据写入磁盘，注意这里的写入不是及时写入的，而是随机的。

存储引擎

InnoDB行格式

InnoDB提供了4种行格式，分别是Compact、Redundant、Dynamic和Compressed行格式。

COMPACT行格式

一行数据被分为了两个部分，一部分是记录的额外信息，一部分是记录的真实数据。

行溢出

页的大小默认情况下16K，也就是16384字节：

• VARCHAR(M)最多可以存储的远远不止16384字节，这样就出现了一个页存放不了一条记录的局面。

在Compact和Redundant行格式中：

• 对于占用字节数非常大的列，在记录的真实数据中只会存储一小部分数据（768个字节），剩余的数据分散存储在其他的页。

为了可以找到它们，在记录的真实数据中会记录这些页的地址。

主从复制

三个线程：

binlog dump线程：

• 主库中有数据更新时，将更新的事件类型写入到主库的binlog文件中，并创建log dump线程通知slave有数据更新。
• 将此时的binlog名称和当前更新的位置同时传给slave的I/O线程。

I/O线程：

• 该线程会连接到master，向log dump线程请求一份指定binlog文件位置的副本，并将请求回来的binlog存到本地的relay log中。

SQL线程：

• 该线程检测到relay log有更新后，会读取并在本地做redo操作，将发生在主库的事件在本地重新执行一遍，来保证主从数据同步。

基本过程

主库写入数据并且生成binlog文件。

从库服务器上的IO线程连接Master服务器，请求从执行binlog日志文件中的指定位置开始读取binlog至从库。

主库接收到从库的IO线程请求后，会根据Slave的请求信息分批读取binlog文件然后返回给从库的IO线程。

Slave服务器的IO线程获取到Master服务器上IO线程发送的日志内容、日志文件及位置点后，会将binlog日志内容依次写到Slave端自身的Relay Log（即中继日志）。

从库服务器的SQL线程会实时监测到本地Relay Log中新增了日志内容，然后把RelayLog中的日志翻译成SQL并且按照顺序执行SQL来更新从库的数据。

主从延时原因

主从延迟主要是出现在 relay log 回放 这一步。

当主库的 TPS 并发较高，产生的 DDL 数量超过从库一个 SQL 线程所能承受的范围，那么延时就产生了。

还有就是可能与从库的大型 query 语句产生了锁等待。

主从延时解决方案

缩短主从同步时间：

• 提升从库机器配置，可以和主库一样，甚至更好。
• 避免大事务。
• 搞多个从库，即一主多从，分担从库查询压力。
• 优化网络宽带。
• 选择高版本 MySQL，支持主库 binlog 多线程复制。

从业务场景考虑：

• 使用缓存：
  • 在同步写数据库的同时，也把数据写到缓存，查询数据时，会先查询缓存。
  • 这种情况会带来 MySQL 和 Redis 数据一致性问题。
• 查询主库：
  • 直接查询主库，这种情况会给主库太大压力，核心场景可以使用，比如订单支付。

GTID复制

GTID是什么？

从 MySQL 5.6.5 开始新增了一种基于 GTID 的复制方式。

• 通过 GTID 保证了每个在主库上提交的事务在集群中有一个唯一的ID。

• 这种方式强化了数据库的主备一致性，故障恢复以及容错能力。

在原来基于二进制日志的复制中，从库需要告知主库要从哪个偏移量进行增量同步：

• 如果指定错误会造成数据的遗漏，从而造成数据的不一致。

借助GTID，在发生主备切换的情况下，MySQL的其它从库可以自动在新主库上找到正确的复制位置，这大大简化了复杂复制拓扑下集群的维护，也减少了人为设置复制位置发生误操作的风险。

另外，基于GTID的复制可以忽略已经执行过的事务，减少了数据发生不一致的风险。

GTID复制原理流程：

Master进行数据更新时、在事务前产生GTID号、一起记录到binlog日志

Slave的I/O线程将变更的binlog数据，写入到本地中继日志relay_log

Slave的SQL线程从中继日志中获取GTID号，和本地的binlog对比查看是否有记录

• 有记录，说明该GTID事务已执行，Slave数据库会忽略

• 如果没有记录，Slave数据库从relay_log中继日志中获取数据，且执行该GTID的事务，记录到binlog中

根据GTID号就可以知道事务最初是在哪个数据库上提交的

有了GTID号、可以方便主从复制的故障切换

主从故障切换:

同一个事务的GTID在所有节点上都是一致的，Slave机器根据GTID就可以知道数据的停止点在哪。

并且，MySQL还提供了参数master_auto_position，能够自动的获取GTID值，让运维更加省心了。

GTID优缺点:

优点

• 根据GTID可以明确知道事务最开始是在哪个数据库提交的
• GTID对于宕机切换，非常方便，明确数据起止点

缺点

• 开启了GTID的数据库，和未开启的数据库实例之间、是无法混用复制的