首页  思维导图  详情

数据库

2023-05-09 15:04:12   0  举报





AI智能生成

数据库脑图

作者其他创作

大纲/内容

SQL

JOIN

CROSS JOIN

交叉连接，计算笛卡儿积

INNER JOIN

内连接，返回满足条件的记录

OUTER JOIN

LEFT

返回左表所有行，右表不存在补NULL

RIGHT

返回右表所有行，左边不存在补NULL

FULL

返回左表和右表的并集，不存在一边补NULL

SELF JOIN

自连接，将表查询时候命名不同的别名。

tidb

nosql

redis

单线程

同一时间只占用一个 CPU，只能有一个指令在运行，并行读写是不存在的。

数据结构

String（字符串）

string类型是二进制安全的。意思是redis的string可以包含任何数据。比如jpg图片或者序列化的对象 string类型是Redis最基本的数据类型，一个键最大能存储512MB。

缓存、限流、分布式锁、分布式session

哈希（Hash）类型:(map<string,string>)

用户信息

List类型

List类型是按照插入顺序排序的字符串链表（所以它这里的list指的相当于java中的linkesdlist）

时间轴、简单队列

set类型

Set类型看作为没有排序的字符集合。如果多次添加相同元素，Set中将仅保留该元素的一份拷贝。

赞、踩

Sorted-Sets类型

Sorted-Sets中的每一个成员都会有一个分数(score)与之关联

排行榜

位图

HLL

GEO（地理信息）

设计

链表

字典

跳跃表

底层结构

跳跃列表（skipList）

stream

添加消息

xadd mystream * name zhangsan age 18;

创建消费组

xgroup create mystream cg1 $

消费消息

xreadgroup group cg1 godconsumer streams mystream >

ACK

xack mystream cg1 1636612497298-0

查询未消费消息

XPENDING mystream cg1

设计

Consumer Group

消费组，使用 XGROUP CREATE 命令创建，一个消费组有多个消费者(Consumer)。

last_delivered_id

游标，每个消费组会有个游标 last_delivered_id，任意一个消费者读取了消息都会使游标 last_delivered_id 往前移动。

pending_ids

消费者(Consumer)的状态变量，作用是维护消费者的未确认的 id。 pending_ids 记录了当前已经被客户端读取的消息，但是还没有 ack (Acknowledge character：确认字符）。

多路 I/O 复用机制

处理客户端请求时，不会阻塞主线程；Redis 单纯执行（大多数指令）一个指令不到 1 微秒[4]，如此，单核 CPU 一秒就能处理 1 百万个指令（大概对应着几十万个请求吧），用不着实现多线程（网络才是瓶颈）。

使用操作系统提供的虚拟内存来存储数据

持久化

RDB 全量持久化

这段时间，redis 是无法处理请求的。

AOF 增量持久化

数据迁移方式

aof（日志文件）

纯文本格式（redis文本协议的流水记录），加载的过程相当于历史重放

rdb（快照文件）

二进制格式，直接进行加载，所以一般情况下rdb数据加载会比aof加载快

replication（主从复制）

淘汰策略

FIFO 淘汰最早数据

First In First Out，先进先出。

LRU 剔除最近最少使用

Least Recently Used，最近最少使用。

LFU 剔除最近使用频率最低

Least Frequently Used，最不经常使用。

指令

noeviction:返回错误当内存限制达到并且客户端尝试执行会让更多内存被使用的命令（大部分的写入指令，但DEL和几个例外）

allkeys-lru: 尝试回收最少使用的键（LRU），使得新添加的数据有空间存放。

volatile-lru: 尝试回收最少使用的键（LRU），但仅限于在过期集合的键,使得新添加的数据有空间存放。

allkeys-random: 回收随机的键使得新添加的数据有空间存放。

volatile-random: 回收随机的键使得新添加的数据有空间存放，但仅限于在过期集合的键。

volatile-ttl: 回收在过期集合的键，并且优先回收存活时间（TTL）较短的键,使得新添加的数据有空间存放。

删除策略

定期删除

redis默认是每隔 100ms 就随机抽取一些设置了过期时间的key，检查其是否过期，如果过期就删除。注意这里是随机抽取的。为什么要随机呢？你想一想假如 redis 存了几十万个 key ，每隔100ms就遍历所有的设置过期时间的 key 的话，就会给 CPU 带来很大的负载！

惰性删除

定期删除可能会导致很多过期 key 到了时间并没有被删除掉。所以就有了惰性删除。假如你的过期 key，靠定期删除没有被删除掉，还停留在内存里，除非你的系统去查一下那个 key，才会被redis给删除掉。这就是所谓的惰性删除，也是够懒的哈

高可用

Sentine l哨兵

数据不一致

异步补偿

跳表

事务

事务提供了一种将多个命令请求打包

批量操作在发送 EXEC 命令前被放入队列缓存

不保证原子性

Lua脚本

没有隔离级别

且没有回滚

命令

watch key1 key2 ... : 监视一或多个key,如果在事务执行之前，被监视的key被其他命令改动，则事务被打断（类似乐观锁）

multi : 标记一个事务块的开始（ queued ）

exec : 执行所有事务块的命令（一旦执行exec后，之前加的监控锁都会被取消掉）　

discard : 取消事务，放弃事务块中的所有命令

unwatch : 取消watch对所有key的监控

redlock 红锁

多套环境

Tair

neo4j

mysql

逻辑架构

服务层

Binary log

解释器

优化器

全表扫描（顺序扫描(sequential scan)）

索引扫描

核心服务

存储引擎

引擎事务日志

系统文件层

引擎

InnoDB

插入缓冲（insert buffer),二次写(double write),自适应哈希索引(ahi),预读(read ahead)

系统内部字段

主键ID

事务ID

回滚指针

数据库事务

原理

在Innodb中，每次开启一个事务时，都会为该session分配一个事务对象。而为了对全局所有的事务进行控制和协调，有一个全局对象trx_sys，对trx_sys相关成员的操作需要trx_sys->mutex锁。

trx_sys

事务链表和集合

trx_sys->mysql_trx_list 包含了所有用户线程的事务对象，即使是未开启的事务对象，只要还没被回收到trx_pool中，都被放在该链表上。当session断开时，事务对象从链表上摘取，并被回收到trx_pool中，以待重用。

trx_sys->rw_trx_list 读写事务链表，当开启一个读写事务，或者事务模式转换成读写模式时，会将当前事务加入到读写事务链表中，链表上的事务是按照trx_t::id有序的；在事务提交阶段将其从读写事务链表上移除。

trx_sys->serialisation_list 序列化事务链表，在事务提交阶段，需要先将事务的undo状态设置为完成，在这之前，获得一个全局序列号trx->no，从trx_sys->max_trx_id中分配，并将当前事务加入到该链表中。随后更新undo等一系列操作后，因此进入提交阶段的事务并不是trx->id有序的，而是根据trx->no排序。当完成undo更新等操作后，再将事务对象同时从serialisation_list和rw_trx_list上移除。

集合

trx_sys->rw_trx_ids 记录了当前活跃的读写事务ID集合，主要用于构建ReadView时快速拷贝一个快照

trx_sys->rw_trx_set 这是的映射集合，根据trx_id排序，用于通过trx_id快速获得对应的事务对象。一个主要的用途就是用于隐式锁转换，需要为记录中的事务id所对应的事务对象创建记录锁，通过该集合可以快速获得事务对象

Innodb使用一种称做ReadView(视图)的对象来判断事务的可见性（也就是ACID中的隔离性）

Innodb的多版本数据使用UNDO来维护的，例如聚集索引记录(1) =>(2)=>(3)，从1更新成2，再更新成3，就会产生两条undo记录。

事务回滚段

对于普通的读写事务，总是为其指定一个回滚段（默认128个回滚段）。而对于只读事务，如果使用到了InnoDB临时表，则为其分配(1~32)号回滚段。（回滚段指定参阅函数trx_assign_rseg_low）

当为事务指定了回滚段后，后续在事务需要写undo页时，就从该回滚段上分别分配两个slot，一个用于update_undo，一个用于insert_undo。分别处理的原因是事务提交后，update_undo需要purge线程来进行回收，而insert_undo则可以直接被重利用。

事务锁维护在不同的Isolation level下数据库的Atomicity和Consistency两大基本特性。

内存锁为了维护内存结构的一致性，比如Dictionary cache、sync array、trx system等结构。 InnoDB并没有直接使用glibc提供的库，而是自己封装了两类：

一类是mutex，实现内存结构的串行化访问

一类是rw lock，实现读写阻塞，读读并发的访问的读写锁

事务特性

Atomicity(原子性)

undo log

Consistency （一致性）

doublewrite buffer

保证数据页的准确性

crash recovery

保证恢复时能够将所有的变更apply到数据

崩溃恢复时存在还未提交的事务，那么根据XA规则提交或者回滚事务

Isolation(隔离性)

读未提交(READ UNCOMMITED)

脏读

事务A读到了事务B还没有提交的数据

READ-UNCOMMITTED 在该隔离级别下会读到未提交事务所产生的数据更改，这意味着可以读到脏数据，实际上你可以从函数row_search_mvcc中发现，当从btree读到一条记录后，如果隔离级别设置成READ-UNCOMMITTED，根本不会去检查可见性或是查看老版本。这意味着，即使在同一条SQL中，也可能读到不一致的数据。

读已提交(READ COMMITTED)

不可重复读

在一个事务里面读取了两次某个数据，读出来的数据不一致

每次select都生成一个快照读

READ-COMMITTED 在该隔离级别下，可以在SQL级别做到一致性读，当事务中的SQL执行完成时，ReadView被立刻释放了，在执行下一条SQL时再重建ReadView。这意味着如果两次查询之间有别的事务提交了，是可以读到不一致的数据的。

可重复读(REPEATABLE READ)

事务后第一个select语句才是快照读的地方

幻读（当前读）

在一个事务里面的操作中发现了未被操作的数据

并发的事务中有事务发生了插入、删除操作

行锁只能锁住行，即使把所有的行记录都上锁，也阻止不了新插入的记录。

REPEATABLE-READ 可重复读和READ-COMMITTED的不同之处在于，当第一次创建ReadView后（例如事务内执行的第一条SEELCT语句），这个视图就会一直维持到事务结束。也就是说，在事务执行期间的可见性判断不会发生变化，从而实现了事务内的可重复读。

序列化(SERIALIZABLE)

Durability(持久性)

WAL（Write-Ahead Logging）的原则

Redo log和数据页都做了checksum校验

为了解决半写的问题，即写一半数据页时实例crash，这时候数据页是损坏的。InnoDB使用double write buffer来解决这个问题，在写数据页到用户表空间之前，总是先持久化到double write buffer，这样即使没有完整写页，我们也可以从double write buffer中将其恢复出来。你可以通过innodb_doublewrite选项来开启或者关闭该特性。

事务类型

只读事务

读写事务

读写事务并不意味着一定在引擎层就被认定为读写事务了，5.7版本InnoDB里总是默认一个事务开启时的状态为只读的。举个简单的例子，如果你事务的第一条SQL是只读查询，那么在InnoDB层，它的事务状态就是只读的，如果第二条SQL是更新操作，就将事务转换成读写模式。

事务开启

BEGIN

START TRANSACTION READ ONLY

当Server层接受到任何数据更改的SQL时，都会直接拒绝请求，返回错误码ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION，不会进入引擎层。

这个选项可以强约束一个事务为只读的，而只读事务在引擎层可以走优化过的逻辑，相比读写事务的开销更小，例如不用分配事务id、不用分配回滚段、不用维护到全局事务链表中。

START TRANSACTION READ WRITE

START TRANSACTION WITH CONSISTENT SNAPSHOT

innobase_start_trx_and_assign_read_view

这里会进入InnoDB层，调用函数innobase_start_trx_and_assign_read_view，注意只有你的隔离级别设置成REPEATABLE READ（可重复读）时，才会显式开启一个Read View

AUTOCOMMIT = 0

无需显式开启事务，如果你执行多条SQL但不显式的调用COMMIT（或者执行会引起隐式提交的SQL）进行提交，事务将一直存在

事务回滚

回滚的方式是提取undo日志，做逆向操作

InnoDB的undo是单独写在表空间中的，本质上和普通的数据页是一样的。如果在事务回滚时，undo页已经被从内存淘汰，回滚操作（特别是大事务变更回滚）就可能伴随大量的磁盘IO。因此InnoDB的回滚效率非常低

事务管理执行

Server层的MDL锁模块，维持了一个事务过程中所有涉及到的表级锁对象。通过MDL锁，可以堵塞DDL，避免DDL和DML记录binlog乱序

InnoDB的trx_sys子系统，维持了所有的事务状态，包括活跃事务、非活跃事务对象、读写事务链表、负责分配事务id、回滚段、Readview等信息，是事务系统的总控模块；

InnoDB的lock_sys子系统，维护事务锁信息，用于对修改数据操作做并发控制，保证了在一个事务中被修改的记录，不可以被另外一个事务修改；

InnoDB的log_sys子系统，负责事务redo日志管理模块；

InnoDB的purge_sys子系统，则主要用于在事务提交后，进行垃圾回收，以及数据页的无效数据清理。

事务ID

在InnoDB中一直维持了一个不断递增的整数，存储在trx_sys->max_trx_id中；每次开启一个新的读写事务时，都将该ID分配给事务，同时递增全局计数。事务ID可以看做一个事务的唯一标识。

多版本并发控制MVCC

undo日志

回滚段undo是实现InnoDB MVCC的根基。每次修改聚集索引页上的记录时，变更之前的记录都会写到undo日志中。回滚段指针包括undo log所在的回滚段ID、日志所在的page no、以及page内的偏移量，可以据此找到最近一次修改之前的undo记录，而每条Undo记录又能再次找到之前的变更。

Readview

ReadView::id 创建该视图的事务ID；

ReadView::m_ids 创建ReadView时，活跃的读写事务ID数组，有序存储；

ReadView::m_low_limit_id 设置为当前最大事务ID；

ReadView::m_up_limit_id m_ids集合中的最小值，如果m_ids集合为空，表示当前没有活跃读写事务，则设置为当前最大事务ID。

快照读

当前读

加排他锁

间隙锁

1.对主键或唯一索引，如果当前读时，where条件全部精确命中(=或者in)，这种场景本身就不会出现幻读，所以只会加行记录锁。

2.没有索引的列，当前读操作时，会加全表gap锁，生产环境要注意

3.非唯一索引列，如果where条件部分命中(>、<、like等)或者全未命中，则会加附近Gap间隙锁。

场景

update ... (更新操作)

delete ... (删除操作)

insert ... (插入操作)

select ... lock in share mode (共享读锁)

select ... for update (写锁)

可见性判断

聚集索引

二级索引

GTID

GTID即全局事务ID (global transaction identifier),

锁

表锁

LOCK_X（排他锁）

LOCK_S（共享锁）

意向共享锁（ Intent share lock，简称IS锁）

意向排它锁（ Intent Exclusive lock，简称IX锁）

行锁

记录锁(RK)

LOCK_REC_NOT_GAP

锁带上这个 FLAG 时，表示这个锁对象只是单纯的锁在记录上，不会锁记录之前的 GAP。在 RC 隔离级别下一般加的都是该类型的记录锁（但唯一二级索引上的 duplicate key 检查除外，总是加 LOCK_ORDINARY 类型的锁）。

类型

间隙锁(GK) LOCK_GAP

表示只锁住一段范围，不锁记录本身，通常表示两个索引记录之间，或者索引上的第一条记录之前，或者最后一条记录之后的锁。可以理解为一种区间锁，一般在RR隔离级别下会使用到GAP锁。

LOCK_ORDINARY(Next-Key Lock)

而NEXT-KEY LOCK正是为了解决RR隔离级别下的幻读问题。所谓幻读就是一个事务内执行相同的查询，会看到不同的行记录。在RR隔离级别下这是不允许的。

插入意向锁(IK) LOCK_INSERT_INTENTION(插入意向锁)

INSERT INTENTION锁是GAP锁的一种，如果有多个session插入同一个GAP时，他们无需互相等待，例如当前索引上有记录4和8，两个并发session同时插入记录6，7。他们会分别为(4,8)加上GAP锁，但相互之间并不冲突（因为插入的记录不冲突）。

隐式锁

InnoDB 通常对插入操作无需加锁，而是通过一种“隐式锁”的方式来解决冲突。聚集索引记录中存储了事务id，如果另外有个session查询到了这条记录，会去判断该记录对应的事务id是否属于一个活跃的事务，并协助这个事务创建一个记录锁，然后将自己置于等待队列中。该设计的思路是基于大多数情况下新插入的记录不会立刻被别的线程并发修改，而创建锁的开销是比较昂贵的，涉及到全局资源的竞争。

存储

页

16k

每个page至少要包含两个记录

Row size too large (> 8126).

最小存储单元

推荐自增 id 做主键

自增ID可以保证每次插入时B+索引是从右边扩展的，可以避免B+树和频繁合并和分裂（对比使用UUID）。如果使用字符串主键和随机主键，会使得数据随机插入，效率比较差。

指针

6字节

text

64K

768字节的时候，剩余部分会保存在另外的页面（off-page）

如果我们想在创建的表的时候，保证创建的表中的text字段都能安全的达到64k上限（而不是等插入的时候才发现），那么需要将默认为OFF的innodb_strict_mode设置为ON，这样在建表时会先做判断

流程

一般情况下，当我们需要读入一个Page时，首先根据space id 和page no找到对应的buffer pool instance。然后查询page hash，如果page hash中没有，则表示需要从磁盘读取。在读盘前首先我们需要为即将读入内存的数据页分配一个空闲的block。当free list上存在空闲的block时，可以直接从free list上摘取；

buffer pool

我们知道InnoDB使用buffer pool来缓存从磁盘读取到内存的数据页。buffer pool通常由数个内存块加上一组控制结构体对象组成

LOB

对于 VARCHAR、VARBINARY、BLOB、TEXT 这类可变长字段，如果数据长度过长，会将其单独存储在主索引记录之外的 LOB page 上（从主索引所属的 tablespace 上分配）

8.0前

InnoDB 将一个 LOB 字段直接存储在多个 LOB page 中，这些 LOB page 组成一个单向链表

随机访问 LOB 中的数据是非常低效的

8.0

LOB index 来索引 LOB page

MVCC

在 LOB 字段更新了部分数据后，user tablespace 中还是只有一个 LOB，更新操作只会修改部分 LOB data page，并且 LOB 中同时存在被修改 LOB data page 的多个版本

日志

binlog

格式

Statement

记录sql

优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。(相比row能节约多少性能与日志量，这个取决于应用的SQL情况，正常同一条记录修改或者插入row格式所产生的日志量还小于Statement产生的日志量，但是考虑到如果带条件的update操作，以及整表删除，alter表等操作，

Row

不记录sql语句上下文相关信息，仅保存哪条记录被修改。

缺点:所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容,比如一条update语句，修改多条记录，则binlog中每一条修改都会有记录，这样造成binlog日志量会很大，特别是当执行alter table之类的语句的时候，由于表结构修改，每条记录都发生改变，那么该表每一条记录都会记录到日志中。

ROW格式会产生大量日志，因此在考虑是否使用ROW格式日志时应该跟据应用的实际情况，其所产生的日志量会增加多少，以及带来的IO性能问题。)

Mixed

是以上两种level的混合使用，一般的语句修改使用statment格式保存binlog

有数据一致性问题走Row

查看

show binlog events;

【Log_name】:日志名称

【pos】上次操作点的序号

【event_type】事件类型

TABLE_MAP_EVENT

FORMAT_DESCRIPTION_EVENT

代表binlog日志的第一条，且只会在第一次出现

ROTATE_EVENT

当MySQL切换至新的binlog文件的时候，MySQL会在旧的binlog文件中写入一个ROTATE_EVENT，表示新的binlog文件的文件名

实测没有换文件也会有该事件

QUERY_EVENT

记录更新操作的语句

ROWS_EVENT

WRITE_ROWS_EVENT

在开启ROW模式记录的binlog文件中，WRITE_ROWS_EVENT记录了插入的行记录。

UPDATE_ROWS_EVENT

在开启ROW模式记录的binlog文件，UPDATE_ROWS_EVENT记录了更新的行记录。

DELETE_ROWS_EVENT

在开启ROW模式记录的binlog文件，DELETE_ROWS_EVENT记录了删除的行记录。

XID_EVENT

当事务提交时，无论哪种模式都会记录。

GTID_LOG_EVENT

在启用GTID模式（主从复制模式）后，MySQL将为每个事务都分配了个GTID。

PREVIOUS_GTIDS_LOG_EVENT

开启GTID模式（主从复制）后，每个binlog开头都会有一个PREVIOUS_GTIDS_LOG_EVENT（在后简称PE）事件，它的值是上一个binlog的PE+GTID信息。

STOP_EVENT

当MySQL数据库停止时，会在当前的binlog末尾添加一个STOP_EVENT事件表示数据库停止。

日志解析

# at 523 #210405 21:08:18 server id 1 end_log_pos 639 Query thread_id=13 exec_time=0 error_code=0 SET TIMESTAMP=1617628098/*!*/; update user set username='zhangqian' where uid = '15' /*!*/;

postion（描述）

at后面的数字代表在binlog日志文件的第几个字节开始（at 523 ）

timestamp（事件发生的时间戳）

即第二行的（#210405 21:08:18）

server id（服务器标识）

（1），代表执行的主机编号

end_log_pos（结束字节数）

结束的字节位置639

Query（类型）

事件类型。

thread_id

处理的线程编号(13)

exec_time

执行花费的时间

error_code

错误码

SET TIMESTAMP=1617628098/*!*/;代表执行的时间戳

update user set username='zhangqian' where uid = '15' 代表执行的语句，遇到下一个#at 则为下一个binlog日志事件。

删除binlog

(1) 使用reset master,该命令将会删除所有日志，并让日志文件重新从000001开始。

（2）使用命令

purge master logs to "binlog_name.00000X"

将会清空00000X之前的所有日志文件.

(3) 使用--expire_logs_days=N选项指定过了多少天日志自动过期清空。

事务日志

redo

存储

redo log block

512字节

不管是log buffer中还是os buffer中以及redo log file on disk中，都是这样以512字节的块存储的

组成

日志头

12字节

日志体

492字节

日志尾

8字节

undo

作用

旧版本数据读取

purge时扫描undo log record

崩溃恢复时扫描undo log record

大事务回滚

存储

undo log segment

回滚

rollback segment称为回滚段，每个回滚段中有1024个undo log segment。

binlog 与事务日志一致性

备库通过二进制日志重放主库提交的事务，而主库binlog写入在commit之前，如果写完binlog主库crash，再次启动时会回滚事务。但此时从库已经执行，则会造成主备数据不一致

XA事务（内部分布式事物）

–自动为每个事务分配一个唯一的ID（XID） –COMMIT会被自动的分成Prepare和Commit两个阶段。 – Binlog会被当做事务协调者(Transaction Coordinator)，Binlog Event会被当做协调者日志。

两种XA事务方式

隐式XA

若关闭了binlog，且存在不止一种事务引擎时，则XA控制对象为tc_log_mmap；

若打开binlog，且使用了事务引擎，则XA控制对象为mysql_bin_log；

其他情况，使用tc_log_dummy，这种场景下就没有什么XA可言了，无需任何协调者来进行XA。

显式XA

XA规范主要定义了(全局)事务管理器(TM)和(局部)资源管理器(RM)之间的接口

崩溃提交或回退

binlog与 redolog的顺序性

5.6前

prepare_commit_mutex锁以串行的方式来保证MySQL数据库上层二进制日志和Innodb存储引擎层的事务提交顺序一致，然后会导致组提交（group commit）特性无法生效

5.6

BLGC（Binary Log Group Commit），并把事务提交过程分成三个阶段，Flush stage、Sync stage、Commit stage。

引入队列机制保证Innodb commit顺序与binlog落盘顺序一致，并将事务分组，组内的binlog刷盘动作交给一个事务进行，实现组提交目的。在MySQL数据库上层进行提交时首先按顺序将其放入一个队列中，队列中的第一个事务称为leader，其他事务称为follow，leader控制着follow的行为。

参数

“双1”，是保证CrashSafe的根本。

sync_binlog

刷新binlog_cache中的信息到磁盘由os决定。

每N次事务提交刷新binlog_cache中的信息到磁盘。

innodb_flush_log_at_trx_commit

它控制了重做日志（redo log）的写盘和落盘策略

事务每次提交都会将log buffer中的日志写入os buffer并调用fsync()刷到log file on disk中

事务安全

OS crash可能丢失

每次提交都仅写入到os buffer，然后是每秒调用fsync()将os buffer中的日志写入到log file on disk

mysqld 进程crash可能丢失

事务提交时不会将log buffer中日志写入到os buffer，而是每秒写入os buffer并调用fsync()写入到log file on disk中

MyISAM

区别

InnoDB支持事务，MyISAM不支持

InnoDB支持外键，而MyISAM不支持

InnoDB是聚集索引，使用B+Tree作为索引结构，数据文件是和（主键）索引绑在一起的（表数据文件本身就是按B+Tree组织的一个索引结构），必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。

MyISAM是非聚集索引，也是使用B+Tree作为索引结构，索引和数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。

InnoDB的B+树主键索引的叶子节点就是数据文件，辅助索引的叶子节点是主键的值；而MyISAM的B+树主键索引和辅助索引的叶子节点都是数据文件的地址指针。

InnoDB表必须有主键（用户没有指定的话会自己找或生产一个主键），而Myisam可以没有

InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快（注意不能加有任何WHERE条件）；

因为InnoDB的事务特性

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

InnoDB的行锁是实现在索引上的，而不是锁在物理行记录上。潜台词是，如果访问没有命中索引，也无法使用行锁，将要退化为表锁。

sql

select

from

join

Nested-Loop Join（嵌套循环链接），不像其他商业数据库可以支持哈希链接和合并连接

Simple Nested-Loop Join

Index Nested-Loop Join

驱动表会根据关联字段的索引进行查找，当在索引上找到了符合的值，再回表进行查询，也就是只有当匹配到索引以后才会进行回表。至于驱动表的选择，MySQL优化器一般情况下是会选择记录数少的作为驱动表，但是当SQL特别复杂的时候不排除会出现错误选择。在索引嵌套链接的方式下，如果非驱动表的关联键是主键的话，这样来说性能就会非常的高，如果不是主键的话，关联起来如果返回的行数很多的话，效率就会特别的低，因为要多次的回表操作。先关联索引，然后根据二级索引的主键ID进行回表的操作。这样来说的话性能相对就会很差。

Block Nested-Loop Join

最先介绍的Simple Nested-Loop Join算法，而是会优先使用Block Nested-Loop Join的算法。 Block Nested-Loop Join对比Simple Nested-Loop Join多了一个中间处理的过程，也就是join buffer，使用join buffer将驱动表的查询JOIN相关列都给缓冲到了JOIN BUFFER当中，然后批量与非驱动表进行比较，这也来实现的话，可以将多次比较合并到一次，降低了非驱动表的访问频率。也就是只需要访问一次S表。这样来说的话，就不会出现多次访问非驱动表的情况了，也只有这种情况下才会访问join buffer。在MySQL当中，我们可以通过参数join_buffer_size来设置join buffer的值，然后再进行操作。默认情况下join_buffer_size=256K，在查找的时候MySQL会将所有的需要的列缓存到join buffer当中，包括select的列，而不是仅仅只缓存关联列。在一个有N个JOIN关联的SQL当中会在执行时候分配N-1个join buffer。

Sort Merge Join

Hash Join

where

order by

limit

group by

使用松散（Loose）索引扫描实现 GROUP BY

GROUP BY 条件字段需要符合最左前缀索引;

在使用GROUP BY 的同时，只能使用 MAX 和 MIN 这两个聚合函数;

如果引用到了该索引中 GROUP BY 条件之外的字段条件的时候，必须以常量形式存在;

使用紧凑(Tight)索引扫描实现 GROUP BY

使用临时表实现 GROUP BY