事务四大特性
- 原子性原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。可以通过undo log来保证原子性。
- 一致性事务前后数据的完整性必须保持一致。可以通过undo log+redo log来保证一致性。
- 隔离性事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。可以通过锁来保证隔离性。
持久性持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。可以通过redo log来保证持久性。
事务隔离级别
| 隔离级别 | 脏读 | 不可重复读 | 幻读 | | —- | —- | —- | —- | | 读未交读（Read uncommitted） | 可能 | 可能 | 可能 | | 提交读（Read committed） | 不可能 | 可能 | 可能 | | 可重复读（Repeatable read） | 不可能 | 不可能 | 可能 | | 串行化（Serializable ） | 不可能 | 不可能 | 不可能 |
脏读一个事务读取到了另外一个事务没有提交的数据。主要作用于select。例子：事务T1更新了一行记录的内容，但是并没有提交所做的修改。事务T2读取到了T1更新后的行，然后T1执行回滚操作，取消了刚才所做的修改。现在T2所读取的行就无效了。解决：修改时加排他锁，直到事务提交后才释放，读取时加共享锁。当一个事务进行修改时，其他事务只能读取到该数据的快照。
- 不可重复读在同一事务中，两次读取同一数据，得到内容不同。主要作用于update。例子：事务T1读取一行记录，紧接着事务T2修改了T1刚才读取的那一行记录。然后T1又再次读取这行记录，发现与刚才读取的结果不同。这就称为“不可重复”读，因为T1原来读取的那行记录已经发生了变化。解决：使用MVCC，由于每个事务都有一个版本号，当T2修改了数据，但是版本号比T1大，所以，T1还是会读到原来的数据。
- 幻读同一事务中，用同样的操作读取两次，得到的记录数不相同。主要作用与DELETE和INSERT。例子：事务T1读取一条指定的WHERE子句所返回的结果集。然后事务T2新插入 一行记录，这行记录恰好可以满足T1所使用的查询条件中的WHERE子句的条件。然后T1又使用相同的查询再次对表进行检索，但是此时却看到了事务T2刚才插入的新行。这个新行就称为“幻像”，因为对T1来说这一行就像突然出现的一样。解决：将事务串行化，一个个执行。但是效率极其低下。
Innodb和MyISAM的区别
|
| InnoDB | MyISAM | | —- | —- | —- | | 特点 | 支持事务，行锁，外键，非锁定读 | 不支持事务，支持表锁，全文索引，缓冲池只缓存索引文件 | | 适合操作 | 适合大量的增删操作，因为行锁粒度比表锁更小 | 大量select（1、myisam不缓存数据块，只缓存索引块；2、myisam记录的直接是文件的OFFSET，定位比INNODB要快，INNODB是映射到块，再映射到行；3、INNODB需要维护MVCC） | | 高并发性 | 有MVCC，并发性很高 | 并发性低 | | 索引 | 聚集索引，B+树结构，必须有主键；辅助索引需要查询两次，因此查询速度不如MyISAM；索引和数据文件捆绑 | 非聚集索引，B+树结构，可以没有主键；索引和数据文件分离，索引保存的是数据文件的指针；主键索引和辅助索引独立 | | 文件 | frm存放表定义内容，idb存放数据 | frm存放表定义内容，myd存放数据，myi存放索引 | | 特性 | 插入缓冲、二次写、自适应哈希、预读 | / | | 使用场景 | OLTP（联机事务处理），主要是执行基本日常的事务处理 | OLAP（联机分析处理），数据仓库系统的主要应用，支持复杂的分析操作，侧重决策支持，并且提供直观易懂的查询结果，数据量大 |
锁
共享锁
又称读锁，读取操作创建的锁。
一旦上锁，任何事务（包括当前事务）无法对其修改，其他事务可以并发读取数据，也可在对此数据再加共享锁。
语法：SELECT … LOCK IN SHARE MODE;
排他锁
又称写锁，如果事务对数据A加上排他锁后，则其他事务不可并发读取数据，也不能再对A加任何类型的锁。获准排他锁的事务既能读数据，又能修改数据。
语法：SELECT … FOR UPDATE
意向锁
意向共享锁（IS）：表示事务准备给数据行加入共享锁，也就是说一个数据行加共享锁前必须先取得该表的IS锁。
意向排他锁（IX）：类似上面，表示事务准备给数据行加入排他锁，说明事务在一个数据行加排他锁前必须先取得该表的IX锁。
意向锁是InnoDB自动加的，不需要用户干预。
select for update 是加表锁还是行锁
MVCC
mvcc是多版本并发控制，是乐观锁的一种实现方式。它的实现是通过保存数据在某个时间点的快照，意味着事务无论运行多长时间，看到的数据都是相同的。
每一行数据中保存两个隐藏列，当前行创建时的版本号和删除时的版本号。当开始新的事务的时候，版本号都会增加。我们可以通过版本号来控制数据。查询时要求：删除版本号大于当前事务版本号，创建版本号小于等于当前事务版本号。
只适用于读已提交与可重复读。读未提交会有脏读，总能读取到事务的最新版本，而序列化总是锁定行。
索引
索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。以下是不同的存储引擎支持的索引结构：
每个存储引擎支持的索引类型都不一样，但是值得一提的是，InnoDB其实是会支持哈希索引的，但是是自适应哈希索引。
创建索引的语句如下：
CREATE INDEX index_name ON table_name ( column )
ALTER TABLE table_name ADD INDEX index_name ( column )
哈希索引
哈希索引（hash index）基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码（hash code），哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。
对于hash相同的，采用拉链法的方式解决冲突。其中解决哈希冲突的方法如下：
- 开放地址法线性探测、再平方探测等，根据一定的算法，在接下来的空位置找到地方存储。
- 拉链法对于相同的哈希值，使用链表进行连接，使用数组存储每一个链表。
再哈希法对于冲突的哈希值再次进行哈希处理，直至没有哈希冲突。
B+树索引
B+树特征：
有n个子树的中间节点包含n个元素，每个元素不保存数据，只用来索引，所有数据都保存在叶子节点。
- 所有叶子节点包含元素的信息以及指向记录的指针，且叶子节点按关键字自小到大顺序链接。
- 所有的中间节点元素都同时存在于子节点，在子节点元素中是最大（或最小）元素。
聚集索引
聚集索引是按表的主键构造的B+树，叶子节点存放的为整张表的行记录数据，每张表只能有一个聚集索引。优化器更倾向采用聚集索引，因为直接就能获取行数据。
要用自增id来做主键，不要非空列，避免出现大量分页碎片。
辅助索引
辅助索引也叫非聚集索引，叶子节点除了键值以外还包含了一个bookmark，用来告诉InnoDB在哪里可以找到对应的行数据，InnoDB的辅助索引的bookmark就是相对应行数据的聚集索引键。
也就是先获取指向主键索引的主键，然后通过主键索引来找到一个完整的行。如果辅助索引的树和聚集索引的树的高度都是3，如果不是走主键索引走辅助索引的话，那么需要6次逻辑IO访问得到最终的数据页。辅助索引和聚集索引的概念关系图如下：
B+树相对于B树的优点？
- 单一节点存储更多的元素，使得查询的IO次数更少（B+树一个结点存放1170个索引，而B树只能存放16个）；
- 所有查询都要查找到叶子节点，查询性能稳定；
- 所有叶子节点形成有序链表，便于范围查询。
为什么不使用红黑树？
红黑树的深度往往比B+树大很多，可能会造成磁盘IO读写过于频繁，导致效率低下。
数据库系统的设计者巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入。
为了达到这个目的，在实际实现B-Tree还需要使用如下技巧：每次新建节点时，直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，就实现了一个node只需一次I/O。
为什么不使用哈希表？
虽然哈希表查找元素最快，但它有以下缺点：
- hash表只能匹配是否相等，不能实现范围查找；
- 当需要按照索引进行order by时，hash值没办法支持排序；
- 无法使用组合索引的功能；
- 数据量很大时，哈希冲突会比较严重。
1亿个数据走主键索引需要多少次磁盘IO？
一个结点能保存16k的数据，一个索引段的page大约能存1170个索引指针（一个索引指针key占8字节，指针占6字节，161024/(8+6)=1170），叶子结点可以存放16个数据，所以3层大概能存放11701170*16=2000w个数据，1亿个数据需要4层，所以一共是4次IO。
回表查询与覆盖索引
回表查询
所谓的回表查询就是指辅助索引的查询过程，第一步先定位主键值，再查找行记录，性能比扫一遍索引树更低。
更通俗一点讲就是如果select所需获得列中有非索引列，一次索引查询不能获取所有信息，需要到表中找到相应的列的信息，这就叫回表。
覆盖索引
索引覆盖是一种避免回表查询的优化策略。如果一个索引覆盖（包含）了所有需要查询的字段的值，这个索引就是覆盖索引。查询时可以直接返回索引中的的数据，不需要再通过聚集索引去定位行记录，避免了回表的情况发生。
当使用了覆盖索引后，explain中的Extra列会提示Using Index。
避免全表扫描/索引失效
- 用or分隔开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么都不会用到索引
- 以%开头的LIKE查询不能够利用B-Tree索引
- 复合索引的情况下，不满足最左原则，是不会使用复合索引的
- 如果MySQL估计使用索引比全表扫描更慢，则不适用索引
- 查询字段is null时索引失效
-
联合索引失效
where a=1 and b>1 and c=3;b不是等值匹配，c不会用到索引。
- where b=1 and c=1；不符合最左原则，都不会用到索引。
where c=1 and b=1 and a=3;优化器会自动优化顺序，实际上是会用到的。
前缀索引
前缀索引顾名思义，定义字符串的一部分当做索引，而不是把整个字符串当做索引。默认地，如果你创建索引的语句不指定前缀长度，那么索引就会包含整个字符串。
索引键是一部分，所耗费空间更少，但是相对的，1.会增加扫描次数，比如id1和id3就需要再次判断哪个是正确的，所以如何选择合适的长度尤为重要，一般的定义原则是count(distinct(columnName))/count(*),当前缀索引【count(distinct(columnName(length)))，length是你想要创建列的前缀字节长度】越接近此值越好，当有多个前缀字节都一样且都等于这个值时怎么选择呢，当然是字节越少越好，字节越少越省空间。索引选取的越长，占用的磁盘空间就越大，相同的数据页能放下的索引值就越少，搜索的效率也就会越低；2.会影响覆盖索引，如果前缀索引刚好是要查找的值，那么还会回表一次。索引下推
索引条件下推英文全称’Index Condition Pushdown’，简称ICP，是MySQL5.6版本后新添加的特性，用于优化数据查询。
5.6之前，通过非主键索引查询的时候，存储引擎通过索引查询数据，然后将结果返回MySQL server层，在server层判断是否符合条件，5.6之后，当存在索引的列作为判断条件时，MySQL server将这一部分判断条件传递给存储引擎，由存储引擎筛选出符合条件的索引项，然后回表查询得到结果，将结果返回给MySQL server。
有了这个优化后，满足一定条件下，存储引擎会在回表查询之前对数据进行过滤，可以减少存储引擎回表查询的次数。
举例：(name,level)为复合索引，查询where name like ‘大%’ and level=1，5.6之前，由于name不是等值匹配，索引会查询name的索引进行回表，在server层进行level的过滤；如果用了索引下推，在索引遍历过程中，对索引中的字段先做判断，过滤掉不符合条件的选项，即level这一步直接在引擎层判断，返回的时候server层就不用多做一层判断了。
当explain输出的extra列为Using index condition的时候，就表示用到了索引下推。Explain
在查询条件之前加上explain关键词，可以有效分析sql语句的查询效率，explain出来的信息有10列，如下：
id：选择标识符
- select_type：表示查询的类型。
- table：输出结果集的表
- partitions：匹配的分区
- type：表示表的连接类型
- possible_keys：表示查询时，可能使用的索引
- key：表示实际使用的索引
- key_len：索引字段的长度
- ref：列与索引的比较
- rows：扫描出的行数(估算的行数)
- filtered：按表条件过滤的行百分比
-
select_type
SIMPLE：简单表，即不适用表连接或子查询
- PRIMARY：主查询，即外层的查询
- UNION：第二个或者后面的查询语句
-
type
ALL：全表扫描
- index：索引全扫描
- range：索引范围扫描，常见于<，<=，>，>=，between等操作符
- ref：非唯一索引扫描或唯一索引的前缀扫描，返回匹配某个单独值的记录行(也会出现在join操作中)
- eq_ref：唯一索引，每个索引键值，表中只有一条记录匹配，常出现于用主键作为查询条件的查询
- const/system：单表中最多有一个匹配行，查询起来非常迅速，所以这个匹配行中的其他列的值被优化器在当前查询中当做常量来处理
- null：不用访问表或者索引，直接就能够得到结果
访问速度： null>system > const > eq_ref > ref > range > index > ALL。
Extra
- from
- where
- group by
- having
- select
- order by
- limit
三大范式
第一范式
要求数据库表的每一列都是不可分割的原子数据项。
家庭信息和学校信息不满足第一范式。第二范式
在1NF的基础上，非码属性必须完全依赖于候选码。通俗讲，就是本应是两个表的内容并且没有任何关联，却塞到了一个表里。
比如图中应将产品和订单分别做表。第三范式
在2NF基础上，任何非主属性不依赖于其它非主属性，通俗讲就是，应当拆成两个表，有一定的依赖关系，应当选一个属性作为原来表的外键。
应当将图中的班主任相关信息单独成表，班主任姓名作为外键关联。left join,right join,inner join,outer join的含义及区别
- left join左外连接，就是把左边表的数据全部取出来，而右边表的数据有相等的，显示出来，如果没有，显示NULL。
- right join就是把右边表的数据全部取出来，而左边表的数据有相等的，显示出来，如果没有，显示NULL。
- inner join两个表的字段中的相同值，显示数据记录。
- outer join查询结果是左外连接和右外连接查询结果的并集，即使一些记录关联不上，也能够把部分信息查询出来，MySQL不支持全连接，可以通过union查询。
undolog、redolog、binlog
undolog
undoLog即回滚日志，回滚日志除了能够在发生错误或者用户执行 ROLLBACK 时提供回滚相关的信息，它还能够在整个系统发生崩溃、数据库进程直接被杀死后，当用户再次启动数据库进程时，还能够立刻通过查询回滚日志将之前未完成的事务进行回滚，这也就需要回滚日志必须先于数据持久化到磁盘上，是我们需要先写日志后写数据库的主要原因。
回滚日志并不能将数据库物理地恢复到执行语句或者事务之前的样子；它是逻辑日志，当回滚日志被使用时，它只会按照日志逻辑地将数据库中的修改撤销掉看，可以理解为，我们在事务中使用的每一条 INSERT 都对应了一条 DELETE，每一条 UPDATE 也都对应一条相反的 UPDATE 语句。redolog
redo log首先是一个环形的文件，可以设置每个文件大小，同时是循环写入的，当对数据进行修改后会先写入到redo log，如果开启了binlog那么会再写入到bin log，最后根据实际情况来写入磁盘。这样就算MySQL异常重启了也能够保证数据不丢失，这种功能称之为crash-safe能力。
写入磁盘的实际是由MySQL决定的，例如现在不忙了。或者现在redo log要写满了。此时MySQL会主动进行触发写入到磁盘。
如下所示，write pos是当前写入的位置，而check point则是redo log落盘后对redo log进行清理后的位置。可以看出write pos和check point中间这一部分就是剩余可以写的内容。
binlog
bin log主要记录的是每条sql的操作，用于防止MySQL数据丢失后能够进行找回。binlog和redolog的区别
|
| binlog | redolog | | —- | —- | —- | | 引擎 | 所有引擎都可记录 | 只记录innodb引擎本身的日志 | | 记录内容 | 记录的是关于一个事务的具体操作内容，即逻辑日志 | 记录的是关于每个页的更改的物理情况 | | 写入时间 | 仅在事务提交前提交，只写磁盘一次，不论这个事务有多大 | 事务进行过程中会不停的写入 |
为什么需要两个持久化日志？
binlog用来做数据归档，但不具备崩溃恢复的能力，也就是说如果系统突然崩溃，重启后可能会有部分数据丢失。innodb将所有对页面的修改操作写入一个专门的文件，并在数据库启动时从此文件进行恢复操作。
二阶段提交
- 执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。
- 执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。
- 引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。
- 执行器生成这个操作的 binlog，并把 binlog 写入磁盘。
- 执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。
主要关注最后三个命令，首先写入redolog，这时处于prepare阶段，写入binlog，提交事务后，会变成commit状态，这就是两阶段提交，redolog的写入其实是两个阶段的。
原因：
- 先写 redo log 后写 binlog
假设在 redo log 写完，binlog 还没有写完的时候，MySQL 进程异常重启。由于我们前面说过的，redo log 写完之后，系统即使崩溃，仍然能够把数据恢复回来，所以恢复后这一行 c 的值是 1。但是由于 binlog 没写完就 crash 了，这时候 binlog 里面就没有记录这个语句。因此，之后备份日志的时候，存起来的 binlog 里面就没有这条语句。然后你会发现，如果需要用这个 binlog 来恢复临时库的话，由于这个语句的 binlog 丢失，这个临时库就会少了这一次更新，恢复出来的这一行 c 的值就是 0，与原库的值不同。 - 先写 binlog 后写 redo log
如果在 binlog 写完之后 crash，由于 redo log 还没写，崩溃恢复以后这个事务无效，所以这一行 c 的值是 0。但是 binlog 里面已经记录了“把 c 从 0 改成 1”这个日志。所以，在之后用 binlog 来恢复的时候就多了一个事务出来，恢复出来的这一行 c 的值就是 1，与原库的值不同。
可以看到，如果不使用“两阶段提交”，那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致。
InnoDB
体系架构
重点关注内存池
后台线程
- Master Thread：负责将缓冲池中的数据异步刷新到磁盘，保证数据一致性，主要有脏页刷新、合并插入缓冲、UNDO页的回收；
- IO Thread：负责IO请求的回调处理；
- Purge Thread：回收已经使用并分配的undo页；
- Page Cleaner Thread：将脏页的刷新操作都放入到单独的线程中来完成。
缓冲池
主要用于弥补内存速度与磁盘速度之间的鸿沟，读取时，从磁盘读取到的页放入缓冲池，下次再读取时，直接读取内存中的缓冲池即可；写入时，先修改缓冲池中的页，再以一定的频率刷新到磁盘。
插入缓冲
使用场景：索引是辅助索引、索引不唯一；
过程：先判断插入的非聚集索引页是否在缓冲池，在则插入，不在则放入到Insert Buffer对象中。再以一定的频率和情况进行Insert Buffer和辅助索引页子节点的merge操作，好处是将多个插入合并到一个操作中，提高效率。
合并时机：辅助索引页被读取到缓冲池时、Insert Buffer Bitmap页追踪到该辅助索引页已无可用空间、Master Thread（10秒一次）。
若有收获，就点个赞吧
0 人点赞
