Mysql存储引擎Innodb和MyISAM的区别

一、mysql架构

mysql是一个单进程多线程架构的数据库。 

二、存储引擎

InnoDB：

• 支持事务
• 行锁
• 读操作无锁
• 4种隔离级别，默认为repeatable
• 自适应hash索引
• 每张表的存储都是按主键的顺序记性存放
• 支持全文索引（InnoDB1.2.x - mysql5.6）
• 支持MVCC（多版本并发控制）实现高并发

MyISAM：

• 不支持事务
• 表锁
• 支持全文索引 

三、InnoDB体系架构

1、后台线程

• Master Thread
  • 负责将缓冲池中的数据异步刷新到磁盘，保证数据的一致性
• IO Thread
  • 负责IO请求的回调处理　　
• Purge Thread
  • 回收已经使用并分配的undo页（事务提交后，其所使用的undolog不再需要）

2、内存池

• 缓冲池（一块内存区域）
  • InnoDB基于磁盘存储，将记录按照页的方式进行管理（由于基于磁盘，速度较慢，所以需要引入缓冲池提高性能）
  • 读取页：先从缓冲池获取，缓冲池没有，才会从磁盘获取
  • 修改页：先写重做日志缓冲，再修改缓冲池中的页，然后以一定的频率刷新到磁盘（Checkpoint机制），在还没有刷新到磁盘之前，该页被称为脏页
  • innodb_buffer_pool_size设置大小
  • 存放对象：索引页、数据页、自适应hash索引和lock信息
  • 缓冲池可以配置多个（innodb_buffer_pool_instances），每个页根据hash值平均分配到不同的缓冲池实例中，用于减少数据库内部资源竞争
• LRU List
  • 将最新的页放在队列前端，最近最少使用的放在尾端，当缓冲池不够用时，将尾端的页删除出缓冲池（如果此页是脏页，会先刷新到磁盘）。innodb采用的是midpoint技术进行LRU，具体参看《MySQL技术内幕 InnoDB存储引擎》
• Flush List
  • 脏页列表
• 重做日志redolog缓冲
  • 为了防止脏页在刷新到磁盘时宕机，必须先redolog，再修改页；
  • 数据库发生宕机时，通过redolog完成数据的恢复（ACID-D持久性）
  • 默认大小8M，通过innodb_log_buffer_size
  • 将redolog缓冲刷新到redolog文件中的时机
    • master会将redolog缓冲每隔1s刷新到redolog文件中
    • 每个事物提交
    • redolog缓冲池剩余空间小于1/2
• Checkpoint
  • 缓冲池不够用时，将脏页刷新到磁盘
  • 数据库宕机时，只需要重做Checkpoint之后的日志，缩短数据库的恢复时间
  • redolog不可用时，将脏页刷新到磁盘

四、InnoDB逻辑存储结构

1、表空间

• 默认情况下，只有一个表空间ibdata1，所有数据存放在这个空间内
• 如果启用了innodb_file_per_table，则每张表内的数据可以单独放到一个表空间内
  • 每个表空间只存放数据、索引和InsertBuffer Bitmap页，其他数据还在ibdata1中

2、Segment段（InnoDB引擎自己控制）

• 数据段：B+ tree的叶子节点
• 索引段：B+ tree的非叶子节点
• 回滚段

3、Extent区

• 每个区的大小为1M，页大小为16KB，即一个区一共有64个连续的页（区的大小不可调节，页可以）

4、Page页

• InnoDB磁盘管理的最小单位
• 默认每个页大小为16KB，可以通过innodb_page_size来设置（4/8/16K）
• 每个页最多存放7992行数据

5、Row行

五、索引

1、hash索引

• 定位数据只需要一次查找，O(1)
• 自适应hash索引：InnoDB会监控对表上各个索引页的查询，如果观察到建立hash索引可以带来速度提升，则建立hash索引（即InnoDB会自动的根据访问频率和模式来自动的为某些热点页建立hash索引）
• 默认是开启的
• 只可用于等值查询，不可用于范围查询

2、B+树索引

• 树的高度一般为2~4层，需要2~4次查询（100w和1000w行数据，如果B+ tree都是3层，那么查询效率是一样的）
• B+树索引能查到的是数据行所在的页
• 包含聚集索引和辅助索引

3、聚集索引

• 即主键索引
• 叶子节点存放的是行记录数据所在的页，而页中的每一行都是完整的行（叶子节点也被称为数据页）
• 针对范围查询也比较快

聚集索引图：

其中，根节点部分的Key:80000001代表主键为1；Pointer:0004代表指向数据页的页号（即第4页）；

数据页节点的的PageOffset:0004代表第4页，其中存储的数据是完整的每一行。

 

4、辅助索引

• 叶子节点存放的也是行记录数据所在的页，但还是页中存放的不是完整的行，而是仅仅是一对key-value和一个指针，该指针指向相应行数据的聚集索引的主键
• 假设辅助索引树高3层，聚集索引树为3层，那么根据辅助索引查找数据，需要先经过3次IO找到主键，再经过3次IO找到行做在的数据页
• 针对辅助索引的插入和更新操作：辅助索引页如果在缓冲池中，则插入；若不在，则点放到InsertBuffer对象中，之后在以一定的平率进行InsertBuffer和辅助索引页子节点的合并

辅助索引图：

其中，idx_c表示对第c列做了索引；idx_c中的Key:7fffffff代表c列的一个值，其实是-1；idx_c中的Pointer:80000001代表该行的主键是80000001，即1；下面的就是聚集索引部分。

 

5、联合索引（多列索引）

• 左边匹配原则（如果索引为(a,b)，则where a=x可以用到索引，但是b=x用不到，如果是覆盖索引有可能会用到）

6、覆盖索引

• 从辅助索引中直接获取记录
• 对于统计操作，例如count(1)，有可能联合索引，右边也会匹配（优化器自己会做），因为count(1)操作不需要获取整行的详细数据，所以不需要去聚集索引的叶子节点去获取数据，直接在辅助索引树中就完成了操作
• select username from xxx where username='lisi'，如果username是辅助索引，那么整个查询在辅助索引树上就可以完成，因为辅助索引树上虽然没有保存完整的行，但是保存着<username,lisi>这个key-value对；如果select username, age from xxx where username='lisi'，那么就要走聚集索引了

索引介绍1.有关概念：

定义：创建在表上，对数据库表中的一列或者多列的值进行排列得到的结果（相当于表中数据的向导）。
工作方式：一个表创建的索引以文件的形式存储下来，要用该表的数据就先把索引从磁盘上加载到内存（磁盘IO），从内存中先读索引，再根据索引找对应的数据。

作用：提高查询效率（优势）
劣势：

索引也要存储，过多的索引会占用空间
索引并非越多越好，过多的索引会导致cpu使用率降低（过多的索引会导致cpu处理索引的时间过多，处理数据的时间相对少）
由于数据改动会影响索引的改动，过多的索引会引起磁盘IO频繁而造成cpu负载过重

2.索引的分类

普通索引：没有任何限制条件，可以给任意类型的字段添加普通索引
唯一性索引：使用unique修饰的字段，值是不能重复的，主键索引就隶属于唯一性索引
主键索引：使用primary key修饰的字段MYSQL会自动创建为其创建索引，InnoDB存储引擎中不设置主键也会自动找一个字段创建主键索引，一个表只能有一个主键索引
单列索引：在一个字段上创建的索引
多列索引：在表的多个字段上创建的索引
全文索引：使用fulltext参数设置全文索引，只支持char、varchar、text类型的字段上，常用于数据量比较大的 字符串类型中，可以提高查询速度，只有myisam存储引擎支持
空间索引：空间型数据的索引，使用spatial修饰
索引创建和删除的SQL语句：
创建：
1.创建表的时候创建索引

CREATE TABLE table_name(
属性 数据类型,
[unique|fulltext|spatial|primary] [index|key] [索引名] (属性(属性长度) [asc|desc])
);
注意：primary后面必须是key而不能是index
索引名一般以 idx_属性名 这样的形式命名
指定属性长度指建立前缀索引（例如：name属性值’abcde‘，长度3，即以'abc'为索引）
[asc|desc]:指定数据按索引升序还是降序排列
例：创建一个student表，表中有id、name、sex三个属性，id为索引，索引名为idx_id
create table student(
id int,
name varchar(20),
sex varchar(10),
index idx_id (id)
);

创建后用show create table 表名; 来看创建表的详细语句:
-----------------------------------------------+
student | CREATE TABLE student (
id int(11) DEFAULT NULL,
name varchar(20) DEFAULT NULL,
sex varchar(10) DEFAULT NULL,
KEY idx_sex (sex)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
±--------±---------------------------------
第四行可以看出id是一个普通索引，索引名为idx_id

2.在已经创建的表上添加索引

2.1 ：create [unique|fulltext|spatial|primary] [index|key] [索引名] on 表名 (属性);
例：在student表中创建一个名为idx_name的以name为唯一性索引的索引
create unique index idx_name on student (name);
2.2：alter table 表名 add [unique|fulltext|spatial|primary] [index|key] [索引名] (属性);
例：在student表中创建一个名为idx_id的以id为主键索引的索引
alter table student add primary key idx_id (id);
-----------------------------------------------+
| student | CREATE TABLE student (
id int(11) DEFAULT NULL,
name varchar(20) DEFAULT NULL,
sex varchar(10) NOT NULL,
PRIMARY KEY (id), UNIQUE KEYidx_name(name), KEYidx_sex(sex)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
±--------±---------------------------------

删除：

drop index 索引名 on 表名;
注意：一个表的主键索引不能这样删除，这样删除会报错
需要用alter table 表名 drop primary key;来删除
例：删除上面创建的idx_name，idx_id，idx_sex
drop index idx_name on student;
drop index ide_sex on student;
alter table student drop primary key;
-------------------------------------------------------------------+
| student | CREATE TABLE student (
id int(11) NOT NULL,
name varchar(20) DEFAULT NULL,
sex varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
±--------±------------------------------------------

分析有无索引SQL语句的执行过程：
首先student表中设置id为主键索引，并将其创建唯一性索引idx_id,为name创建唯一性索引idx_name
-----------------------------------------------------+
| student | CREATE TABLE student (
id int(11) NOT NULL,
name varchar(20) DEFAULT NULL,
sex varchar(10) DEFAULT NULL,
PRIMARY KEY (id),
UNIQUE KEY idx_id (id),
UNIQUE KEY idx_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
±--------±-----------------------------

再插入一些记录
insert into student values(1,‘zhaolei’,‘nan’),(2,‘qiandian’,‘nan’),(3,‘sunfen’,‘nan’),(4,‘wulan’,‘nv’);
mysql> select * from student;
±—±---------±-----+
| id | name | sex |
±—±---------±-----+
| 1 | zhaolei | nan |
| 2 | qiandian | nan |
| 3 | sunfen | nan |
| 4 | wulan | nv |
±—±---------±-----+

再根据explain +SQL来分析SQL的执行计划:

1.为了看出效果，我们先把idx_name 删除掉：drop index idx_name on student;
再根据explain select * from student where name=‘zhaolei’\G 来查看select * from student where name=‘zhaolei’\G的执行计划

mysql> explain select * from student where name=‘zhaolei’\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: student
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 4
filtered: 25.00
Extra: Using where
±--------±---------------------------------
possible_keys和key表示本次查询没有用到索引，rows表示查询了4条数据，意味着进行了全表扫描，当数据量很大时，全表扫描效率很低。

2.给name字段添加索引：create index idx_name on student (name);
再根据explain select * from student where name=‘zhaolei’\G 来查看select * from student where name=‘zhaolei’\G的执行计划
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: student
partitions: NULL
type: ref
possible_keys: idx_name
key: idx_name
key_len: 63
ref: const
rows: 1
filtered: 100.00
Extra: NULL
±--------±---------------------------------
可以看到，此次查询命中了idx_name的索引，而且此次查询’zhaolei’只查询了一条数据,高效很多

索引的底层原理
MySQL支持两种索引，B-树索引和哈希索引
MySQL中innoDB存储引擎是基于B-树的存储结构（实际上是B+树）

先来看看这几种树

B-树：

B-树是一种多路搜索树，是一种m阶树（一个B-树的节点有多少个子节点，就可以把这个树称为几阶树，二叉树也可以叫做二阶树），图为三阶树。
B-树每个节点可存储多个数据（二叉树一个节点存储一个数据），存储同样的数据量B-树的高度（层数）是很低的
B-树无论是叶子节点还是非叶子节点都存数据和数据地址
B-树对数据查询的耗时情况不均匀，有可能在根节点就找到数据，有可能在子节点或叶子节点才找到数据（每个数据在树中只出现一次，要么在叶子节点，要么在非叶子节点（子节点和根节点））
B-树一个节点的大小一般和磁盘一个块（block块操作）的大小一致，这样在节点的读取或存储过程中，磁盘IO的次数最少（如果一个节点大小设为两个块，一个节点的存储就需要进行两次block块操作，如果一个节点小于一个块，又会造成一次块操作处理的数据少实际块利用率低的问题）
B+树：

B+树是B-树的变体，非叶子节点只存关键字，叶子节点存放关键字和数据（数据/数据地址），而B-树无论是叶子节点还是非叶子节点都存数据和数据地址
B+树有一条有序链表串联整个叶子节点

B * 树：

B*树是B+树的变体，区别在于它把非叶子节点的数据也用链表串起来

关于这几个树不再详细介绍，我是参考B树、B-树、B+树、B*树之间的关系理解的，个人感觉这位老兄总结的不错

MYSQL为什么采用B+树而不用B-树？

B-树的每个节点（叶子节点和非叶子节点）存储的是都关键字和对应的数据地址
B+树的非叶子节点存储关键字，不存储数据地址。对于相同大小的非叶子节点，B+树存储的关键字多，对于相同数量的数据的关键字，B+树使用更少的节点存，这样树的高度（层数）就更少，使用磁盘IO的次数也少，查询效率高
B+树的叶子节点存关键字和数据地址
B-树每一个节点都存储了关键字和数据地址，离根节点近的数据查询快，离根节点远的数据查询慢，耗时不均匀
而B+树所有的数据都在叶子节点上，因此在B+树上搜索关键字，耗时相对是均匀的，没有快慢之分
区间查找B+树是很快的，因为B+树的叶子节点被连接成一个有序的链表结构，因此做整表查询和区间查询更快（先从根节点向下遍历树，找到一个数据后，直接遍历链表得到其他数据，不用遍历整个树）
哈希索引：
底层实现是哈希表，哈希表不能保证数据有序，不适合进行区间查找（因为哈希表的每个数据根据计算的散列码来存储，数据与数据之间无规律可循，无法保证有序，区间查找也相当于查询整个表）

MYISAM和INNODB存储引擎的索引结构
MYISAM：
主键索引：

辅助（非主键）索引：

都是基于B+树的存储结构
索引和数据本身分开（非聚集索引）
主键索引和辅助索引的叶子节点都只存关键字和数据地址，区别在于主键索引的关键字是唯一不能重复的

INNODB：
主键索引：

辅助（非主键）索引：

都是基于B+树的存储结构
索引和数据不分开（聚簇型索引）
主键索引：叶子节点中存关键字和相应的数据
辅助索引：叶子节点中存关键字和主键

对比两大存储引擎索引的查询过程

左边为INNODB,右边为MYISAM
可以看出：

INNODB引擎中根据主键查询时，直接找到要查找的关键字所在的叶子节点就可以直接从叶子节点种拿到所有数据；根据辅助索引查询时，先找到要查找的关键字所在的节点，节点中只能找到辅助索引的数据和主键的数据，要查找其他数据得根据该节点中的主键去主键索引中查找
MYISAM引擎中无论是根据主键索引查找还是根据辅助索引查找，都会找到要查找的关键字的叶子节点，然后根据叶子节点中的数据地址去查找所需数据

INNODB引擎中，每个表存储在硬盘包括两个文件：除了表结构文件，还需要一个 .ibd文件来存储数据和索引
MYISAM引擎中，每个表存储在硬盘包括三个文件：除了表结构文件，还需要一个 .myi文件来存储索引,一个 .myd文件来存储数据

系统(Linux或Windows)中查看数据库中存储的有关表的文件

命令行方式：
Linux中在/usr/local目录下找MySQL安装的目录
Windows中在某个盘里找到你安装MySQL的地方
然后 cd 上面找到的目录
cd data
cd 库名
ls（linux） dir（Windows）
可以看到 表名.frm（表结构文件） 表名.ibd（索引+数据文件）--->该表的存储引擎是INNODB
可以看到 表名.frm（表结构文件） 表名.myi（索引文件） 表名.myd（数据文件）--->该表的存储引擎是MYISAM

我的结果:只看了以INNODB引擎存储的表

2. 鼠标点击到MySQL存储的目录，再点击data,再点击数据库名

索引的设计原则：

可以看出，使用索引能提高查询效率，但是给表创建过多的索引，效率反而会降低，因此在设计表索引的时候，
需要遵循以下的设计原则：
1、给区分度高的字段创建索引 eg：学号、身份证号
2、给经常需要排序，分组和多表联合操作的字段创建索引
3、经常作为查询条件的字段创建索引
4、索引的数据不宜过多
5、使用数据量少的索引（如前缀索引，主要针对字符串索引，字符串类型的数据尽量创建前缀索引）
6、对于多列索引，优先指定最左边的列集
7、删除不再使用或者很少使用的索引
————————————————

六、锁

1、锁分类

-悲观锁-Pessimistic lock
全局锁：flush table with read lock;使用全局锁会锁住整个数据库，使其处于只读状态；
表锁： lock table和 意向锁(Intention Locks) MataData Lock ，意向锁不用显示调用；
行锁(Record-lock)
间隙锁( gap locks)
临键锁( next-key lock) ，由行锁和间隙锁组成；
-乐观锁- Optimistic Lock
自旋cas机制；可通过version版本和时间戳来判断；

2、全局锁
2.1 全局锁： flush tables with lock：全局锁主要是对整个数据库的实例加锁，一个库被全局锁锁住后处于只读状态，以下动作将会被禁止：
DML：data manipulation language 对数据的增查改删更新操作；
DDL: data definition language ,包括对表的修改，增加字段删除字段；
使用场景：全库逻辑备份。如果没有全局锁，那么系统备份得到的数据不是一个逻辑时间点的，这个视图的逻辑是不一致的，数据备份会出现问题。

3、表锁
3.1 lock tables 表锁
加锁：lock table tableName read／write;
解锁：unlock table：
使用场景 ：在没有行锁这种粒度锁的时候，处理并发的时候就会使用表级锁；
lock table car write；
select *　from car;
unlock tables；
特点：

(1). 可以主动unlock tables释放，在客户端断开连接的时候自动释放；
(2). lock tables除了限制别的线程的读写外，也限定了本线程接下来的操作对象；
例如线程执行：
lock tables t1 read, t2 write; //除了阻塞其他线程外，自己本身也只能读t1，读写t2，不能写t1.
3.2 MDL-意向锁
Mate Data Lock， 意向锁 -也称为 元数据锁 ，在mysql5.5版本中引入了MDL，引擎自己维护，用户不用手动操作。当对一个表做增查改删的时候+MDL读锁，当要对表结果做变更的时候，+MDL写锁。
意向共享锁：如果一张表上面至少有一个意向共享锁，说明有其他的事务给其中的某些数据行加上了共享锁；
意向排他锁：如果一张表上面至少有一个意向排他锁，说明有其他的事务给其中的某些数据行加上了排他锁；
作用：
（1）是防止DDL和MDL之间冲突，保证DDL-(数据定义语言)和DML-(操纵语言：增查改删)的数据一致性；
MDL的（增查改删）读锁和 DDL之间不互斥；
MDL的写锁和DDL的写锁修改表结构之间互斥；在对表结构进行修改时，加MDL锁，另外线程只能等待。
（2）提高加锁的效率；
加x锁的前提是：表中没有任何锁，包括行锁的存在，意向锁避免了加行锁时检查锁状态的全表行锁扫描，先加意向锁后加x锁，它相当于一个标记，提高了加锁的效率；

4、行锁【Innodb】
4.1、行锁实现原理
行锁是存储引擎innodb自己设计的，不是所有的存储引擎都支持，比如myisam就不支持；
行锁是基于索引实现的，所以没有建立索引的更新将会扫描全表，使用的是表锁；通过锁住索引实现行及锁。
如果select …for update没走索引，就会锁表，innodb内部是全表根据主键索引逐行扫描，逐行加锁，最后统一释放。

使用注意点： 没有索引或者索引失效时，InnoDB 的行锁变表锁。
4.2、两阶段锁协议
行锁是需要的时候加上的，不是不需要了就立即释放，在commit后才释放，释放时机如下：
1. 执行commit语句或者rollback；
2. 退出数据库；
3. 客户端断开连接；
4.3、行锁的使用优化
由于行锁的等到事务提交后才释放，不是用完就释放，所以把有冲突的抢锁操作尽量放到事务操作的最后一步进行。如何理解： “尽量把临界资源的锁往后放”，实例如下：
实例场景：支付订单中一般流程较长，步骤较多，比如商品库存就是一临界资源，容易产生竞争，可以将扣减库存放在订单事务操作的最后一步，让事务在获取锁的等待时间尽量短，提高并发度。

5、行锁与索引的关系分析
从一条简单的SQL语句开始，分析索引对加锁的影响：
SQL: delete from Order where orderId = 33;
情况一：在RC级别下，id为主键索引，orderId为普通索引，忽略订单ID重复的情况；

表结构如下：
CREATE TABLE `Order` (
`id` int(11) NOT NULL,
`orderId` bigint NOT NULL,
`goods_name` varchar(45) DEFAULT NULL COMMENT '商品名称',
PRIMARY KEY (`id`),
KEY `orderId` (`orderId`)
) ENGINE=InnoDB

分析：此时加行锁会锁住对应条件的二级索引及主键索引的行记录；
情况二：在RC级别下，id为主键索引，orderId为唯一索引；
表结构如下：
CREATE TABLE `Order` (
`id` int(11) NOT NULL,
`orderId` bigint NOT NULL,
`goods_name` varchar(45) DEFAULT NULL COMMENT '商品名称',
PRIMARY KEY (`id`),
UNIQUE KEY `orderId_UNIQUE` (`orderId`),
) ENGINE=InnoDB

分析：由于id是唯一索引时，where会走orderIde列的索引进行过滤，在找到orderId=33的记录后对唯一索引orderId=33的记录加X锁。
同时会回表查询主键索引orderId=33的数据，并对orderId=33的数据也加上X锁。此时依然加行锁，锁住唯一索引及主键索引对应的行记录
情况三：在RC级别下，只有id主键索引；
表结构如下：
CREATE TABLE `Order` (
`id` int(11) NOT NULL,
`orderId` bigint NOT NULL,
`goods_name` varchar(45) DEFAULT NULL COMMENT '商品名称',
PRIMARY KEY (`id`),
) ENGINE=InnoDB

分析：从上面分析的行锁的锁原理可知，因为没有索引，所以走的是全表扫描，此时没有行锁，会对主键索引上每一条记录施加X锁，会锁住整个的表数据；
思考： 为什么会对所有记录施加X锁，而不是表锁或者说符合条件的数据加X锁呢？
这是由于InnoDB的实现决定的，由于没有索引， 无法在存储引擎层过滤（执行计划里的Using Where），所以存储引擎对每一条数据加锁后返回给Sql Server进行过滤，因为Innodb的行锁是通过索引来实现的。
5.1、行锁使用的注意事项
（1）在不通过索引条件查询的时候，或没有索引的情况下，InnoDB只能使用表锁。
（2）由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。
（3）当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁。
（4）即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。因此，在分析锁冲突时，别忘了检查SQL的执行计划，以确认是否真正使用了索引。

对于在RR级别下，对数据的写操作不仅会锁住行还会锁住相邻值之间的间隙；也就是会出现 gap 锁 和 Next-key lock。
6、三种细粒度锁的实现
record lock-行锁：
gap lock-间隙锁：
Next-Key lock-临键锁：行锁和间隙锁共同实现的锁；

6.1、行锁Record lock
实现：通过索引实现，锁住一行记录，锁粒度最小，并发度高。
使用条件：等值查询；

6.2、使用间隙锁Gap Lock
使用条件：查询一个不存在的值，锁定就是该值左右的区间，就是间隙锁。
间隙锁与间隙锁之间是不冲突的，跟间隙锁冲突的是： 往间隙之间插入一条数据的这个操作 。

6.3、临键锁 Next-key Lock
临键锁锁住的是什么？
使用条件：范围查询，包含等值和区间
锁住的是下一个记录的左开右闭的区间；
解决了RR级别下的数据幻读；

7、小结
Mysql的行锁和表锁区别：
表级锁： 锁住整张表。 开销小，加锁快；不会出现死锁；锁粒度最大，发生锁冲突的概率最高，并发度低；
行级锁： 锁住一行数据。开销大，加锁慢；容易出现死锁；锁粒度最小，发生锁冲突的概率最低，并发度高；
行锁的实现：
如果有索引，那么会先扫描索引文件，查询到主键id，通过索引锁定行记录实现行锁；
如果没有索引，就会锁住全表的数据；
行锁的出现大大提高了数据库的并发度，当然实现相对复杂，并且产生死锁的概率也提升了，使用时需要注意。
————————————————

1、latch

• 保证并发线程操作临界资源的正确性
• 自旋锁，自旋指定的次数后，若还没获取到锁，则进入等待状态，等待被唤醒

2、lock

• 事务锁，锁定的可能是表、页或行
• 释放点：事务commit或rollback
• 两种标准的行级锁
  • 共享锁：S lock，事务T1获取了r行的S锁，事务T2也可以获取r行的S锁
  • 排他锁：X lock，事务T1获取了r行的S锁，事务T2就不能获取r行的X锁；事务T1获取了r行的X锁，事务T2就不能获取r行的X/S锁

七、事务

1、隔离级别

• 读不提交
• 读并且提交
  • 可避免脏读：一个事务读到另一个事务没有提交的数据，如果另一个事务发生回滚，第一个事务读到的数据就是垃圾数据
• 可重复读
  • 会有幻读，InnoDB通过Next-Key Lock解决了
    • 幻读：指两次执行同一条 select 语句会出现不同的结果，第二次读会增加一数据行，并没有说这两次执行是在同一个事务中。使用表锁即可避免。
  • 可避免不可重复读：在同一个事务中两条一模一样的 select 语句的执行结果的比较。如果前后执行的结果一样，则是可重复读；如果前后的结果可以不一样，则是不可重复读。通常是发生了update。增加读取时的共享锁（禁止修改）即可避免。
  • 默认的事务隔离级别
• 序列化

一、B+树结构：

二、从B+树查找数据流程

三、B+树性质

MyISAM与InnoDB 的区别

区别：
1. InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务； 

2. InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败； 

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

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

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

 

 

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

那么为什么InnoDB没有了这个变量呢？

    因为InnoDB的事务特性，在同一时刻表中的行数对于不同的事务而言是不一样的，因此count统计会计算对于当前事务而言可以统计到的行数，而不是将总行数储存起来方便快速查询。InnoDB会尝试遍历一个尽可能小的索引除非优化器提示使用别的索引。如果二级索引不存在，InnoDB还会尝试去遍历其他聚簇索引。
    如果索引并没有完全处于InnoDB维护的缓冲区（Buffer Pool）中，count操作会比较费时。可以建立一个记录总行数的表并让你的程序在INSERT/DELETE时更新对应的数据。和上面提到的问题一样，如果此时存在多个事务的话这种方案也不太好用。如果得到大致的行数值已经足够满足需求可以尝试SHOW TABLE STATUS
 

5. Innodb不支持全文索引，而MyISAM支持全文索引，在涉及全文索引领域的查询效率上MyISAM速度更快高；PS：5.7以后的InnoDB支持全文索引了

6. MyISAM表格可以被压缩后进行查询操作

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

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

例如：

    t_user(uid, uname, age, sex) innodb;

    uid PK
    无其他索引
    update t_user set age=10 where uid=1;             命中索引，行锁。

    update t_user set age=10 where uid != 1;           未命中索引，表锁。

    update t_user set age=10 where name='chackca';    无索引，表锁。
 

8、InnoDB表必须有唯一索引（如主键）（用户没有指定的话会自己找/生产一个隐藏列Row_id来充当默认主键），而Myisam可以没有

9、Innodb存储文件有frm、ibd，而Myisam是frm、MYD、MYI

        Innodb：frm是表定义文件，ibd是数据文件

        Myisam：frm是表定义文件，myd是数据文件，myi是索引文件 

如何选择：
    1. 是否要支持事务，如果要请选择innodb，如果不需要可以考虑MyISAM；

    2. 如果表中绝大多数都只是读查询，可以考虑MyISAM，如果既有读也有写，请使用InnoDB。

    3. 系统奔溃后，MyISAM恢复起来更困难，能否接受；

    4. MySQL5.5版本开始Innodb已经成为Mysql的默认引擎(之前是MyISAM)，说明其优势是有目共睹的，如果你不知道用什么，那就用InnoDB，至少不会差。 

InnoDB为什么推荐使用自增ID作为主键？

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

innodb引擎的4大特性

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

一级索引和二级索引(索引怎么使用)

索引设计和工作原理

索引的设计和工作原理。 要创建高性能索引，首先需要知道什么是索引。 在维基百科中，数据库索引是一种数据结构，它以额外的写入和存储空间为代价加快了数据库表上的数据检索操作。 简单来说，索引就像书的目录一样，可以从书中的页码中快速找到所需的内容。

MySQL公式索引(Index )的定义是存储引擎用于快速查找记录的数据结构。

索引是物理数据页，数据库页大小(Page Size )决定了一页可以存储的索引行数和指定大小的索引所需的页数。

索引可以加快搜索，但插入、删除和更新索引列的速度也会降低，从而导致索引维护成本增加。 索引相关的理论知识有二分搜索法、聪明的宝贝表和B Tree。

1、一级索引
索引和数据存储在一起，都存储在同一个B+tree中的叶子节点。一般主键索引都是一级索引。

2、二级索引
二级索引树的叶子节点存储的是主键而不是数据。也就是说，在找到索引后，得到对应的主键，再回到一级索引中找主键对应的数据记录。

3、例子介绍
下面我们通过一个具体的示例进行演示一级索引和二级索引。有如下表pl_ranking（编程语言排行榜表），该表包含3个字段，如下：

id：主键
plname：编程语言名称
ranking：排名
id plname ranking
15 C 2
16 Java 1
18 Php 6
23 C# 5
26 C++ 3
29 Ada 17
50 Go 12
52 Lisp 15
... ... ...

从图中我们可以看到，索引和数据都在一颗树的叶子节点，是存在一起的。通过定位索引就直接可以查找到数据。如果查找id=16的编程语言，

select id, plname, ranking from pl_ranking where id=16;
则只需要读取3个磁盘块，就可以获取到数据。

从上图中我们发现，该B+tree根据plname列进行构建的，只存储一级索引数据。比如，查找编程语言为“Java”的数据。

select id, plname, ranking from pl_ranking where plname='Java';
首先通过二级索引树中找到Java对应的主键id为 “16”（读取2个磁盘块）。然后在去主键索引中查找id为“16” 的数据（读取3个磁盘块）。

4、一级索引和二级索引的关系：回表
一级索引可以单独存在，二级索引不能单独存在，必须依附于一级索引，这叫做“回表”。

索引原理

数据库查询是数据库的核心功能，而索引是加快查询的重要技术手段。 索引数据结构选择的本质是结合当前数据读写的硬件环境选择优良的数据结构进行数据的存储和扫描，数据库中的大部分索引都是通过B Tree实现的。 当然也和其他数据结构有关。 除了b树索引外，MySQL还需要关注散列索引。

然后，我们将逐一学习散列索引和B Tree索引。 因为后面的大部分内容都是关于b树的，所以为了使b树的内容更一致，我们先来谈谈散列索引。

Hash 索引

伶俐的宝贝表是数据库中伶俐的宝贝索引的基础，是基于键值key、value存储数据的构造。 简单地说，聪明的宝贝表是指使用聪明的宝贝函数将索引列计算到桶或槽中的数组，实际的存储器根据聪明的宝贝函数将key换算到确定的存储器位置，在该数组位置存储value。 访问时，只要输入想要查找的密钥，就可以通过聪明的宝贝函数计算得出确定的存储位置，读取数据。

 

如下图所示，将名字设为key，用智能宝贝函数计算名字域的数据，得到dbdxz，存储到桶和槽的数组中，同时将指向实际数据行的指针存储为value，制作智能宝贝表。

43644437c9e542048858b9fbab60b205?from=pc">

 

接下来我们从伶俐的宝贝索引如何实现、Hash 碰撞处理、MySQL 如何使用 Hash，三个方面学习伶俐的宝贝索引。

首先讲解伶俐的宝贝索引是如何实现的？数据库中伶俐的宝贝索引是基于伶俐的宝贝表实现的，对于伶俐的宝贝索引列的数据通过 Hash 算法计算，得到对应索引列的dbdxz形成伶俐的宝贝表，由dbdxz及dbdxz指向的真实数据行的指针组成了伶俐的宝贝索引。伶俐的宝贝索引的应用场景是只在对伶俐的宝贝索引列的等值查询才有效。

如下图所示，根据表中的 name 字段构建 Hash 索引，通过 Hash 算法对每一行 name 字段的数据进行计算，得出 Hash 码。由 Hash 码及 Hash 码指向真实数据行的指针组成了伶俐的宝贝索引。

因为伶俐的宝贝索引只存储伶俐的宝贝值和行指针，不存储实际字段值，所以其结构紧凑，查询速度也非常快，在无伶俐的宝贝冲突的场景下访问伶俐的宝贝索引一次即可命中。但是伶俐的宝贝索引只适用于等值查询，包括 =、IN()、<=> （安全等于， select null <=> null 和 select null=null 是不一样的结果) ，不支持范围查询。

另外，伶俐的宝贝索引的性能跟伶俐的宝贝冲突数量成反比，伶俐的宝贝冲突越多其维护代价越大性能越低。

接下来我们看看 Hash 碰撞如何处理？Hash 碰撞是指不同索引列值计算出相同的dbdxz，如上图所示， 表中 name 字段为 John Smith 和 Sandra Dee 两个不同值根据 Hash 算法计算出来的dbdxz都是 152，这就表示出现了 Hash 碰撞。 对于 Hash 碰撞通用的处理方法是使用链表，将 Hash 冲突碰撞的元素形成一个链表，发生冲突时在链表上进行二次遍历找到数据。

Hash 碰撞跟选择的 Hash 算法有关系，为了减少 Hash 碰撞的概率，优先选择避免 Hash 冲突的 Hash 算法，例如，使用 Percona Server 的函数 FNV64() ，其伶俐的宝贝值为 64 位，出现 Hash 冲突的概率要比 CRC32 小很多。

其次是考虑性能，优先选择数字类型的 Hash 算法，因为字符串类型的 Hash 算法不仅浪费空间而且不方便进行比较。

常见的 CRC32、SHA1 和 MD5 Hash 函数生成的返回值如下图所示。

 

综合建议 Hash 算法使用优先级为：FNV64 > CRC32 （大数据量下 Hash 冲突概率较大）> MD5 > SHA1。

最后再看看，MySQL 中如何使用 Hash 索引？在 MySQL 中主要是分为 Memory 存储引擎原生支持的 Hash 索引 、InnoDB 自适应伶俐的宝贝索引及 NDB 集群的伶俐的宝贝索引3类。

 

Memory 存储引擎原生支持的 Hash 索引，如上图所示，Memory 存储引擎创建表时即可原生显式创建并使用 Hash 索引。

相比 InnoDB，虽然不能原生显示创建 Hash 索引，但是可以伪造伶俐的宝贝索引来加速定值查询的性能。例如为超长文本（如网站 URL）进行 Hash 计算后的字段 url_hash 创建 B+Tree 索引，获得 Hash 索引的功能。

关于伶俐的宝贝索引，InnoDB 提供了 InnoDB 自适应伶俐的宝贝索引的强大功能，接下来重点描述 InnoDB 自适应伶俐的宝贝索引。

InnoDB 自适应伶俐的宝贝索引是为了提升查询效率，InnoDB 存储引擎会监控表上各个索引页的查询，当 InnoDB 注意到某些索引值访问非常频繁时，会在内存中基于 B+Tree 索引再创建一个伶俐的宝贝索引，使得内存中的 B+Tree 索引具备伶俐的宝贝索引的功能，即能够快速定值访问频繁访问的索引页。

B+Tree 索引

在数据库中大部分索引都是通过 B+Tree 来实现的。 对于 B+Tree 具体的定义可以参考《数据结构》等相关书籍。 在 MySQL 数据库中讨论索引时，如果没有明确指定类型，则默认是指使用 B+Tree 数据结构进行存储，其说法等价于 B+Tree、B-Tree、BTREE（看到创建索引语句为 BTREE 也不要惊讶，等同于 B+Tree）。

如下图所示为一个简单的、标准的 B+tree，每个节点有 K 个键值和 K+1 个指针。

 

对于 MySQL 存储引擎而言，其实际使用的 B+Tree 索引是为了满足数据读写性能，以及适配磁盘访问模式优化后的数据结构，每一个叶子节点都包含指向下一个叶子节点的指针。

 

在 MySQL 中，索引是在存储引擎层而非服务器层实现的，所以不同存储引擎层支持的索引类型可以不同。例如，虽然 MyISAM 和 InnoDB 的索引都是使用 B+Tree 实现的，但是其实际数据存储结构有不少差异。下图中 B+Tree 示例一共2层，图中每个页面都已经被随机编号（编号可以认定为页面号），其中页面号为 20 的页面是 B+Tree 的根页面（根页面通常是存放在内存中的），根页面存储了 <key+pageno>，pageno 是指向具体叶子节点的页面号。其他页面都是叶子节点，存放了具体的数据 <key+data>。

B+Tree 索引能够快速访问数据，就是因为存储引擎可以不再需要通过全表扫描来获取数据，而是从索引的根结点（通常在内存中）开始进行二分查找，根节点的槽中都存放了指向子节点的指针，存储引擎根据这些指针能够快速遍历数据。例如，通过页面号为 20 的根节点可以快速得知 Key<10 的数据在 pageno 33 的页面，key在 [10,16) 范围的数据在 pageno 56 的页面。

叶子节点存放的 <key+data> ，对于真正要存放哪些数据还得取决于该 B+Tree 是聚簇索引（Clustered Index）还是辅助索引（Secondary Index）。

聚簇索引和辅助索引

聚簇索引是一种数据存储方式，它表示表中的数据按照主键顺序存储，是索引组织表。InnoDB 的聚簇索引就是按照主键顺序构建 B+Tree，B+Tree 的叶子节点就是行记录，数据行和主键值紧凑地存储在一起。 这也意味着 InnoDB 的主键索引就是数据表本身，它按主键顺序存放了整张表的数据。

而 InnoDB 辅助索引（也叫作二级索引）只是根据索引列构建 B+Tree，但在 B+Tree 的每一行都存了主键信息，加速回表操作。

聚簇索引占用的空间就是整个表数据量的大小，而二级索引会比聚簇索引小很多， 通常创建辅助索引就是为了提升查询效率

InnoDB 只能创建一个聚簇索引（假想下如果能支持多个聚簇索引，那就意味着一张表按不同排序规则冗余存储多份全表数据了），但可以创建多个辅助索引。

相比索引组织表，还有一种堆表类型，堆表是根据数据写入的顺序直接存储在磁盘上的。对于堆表而言，其主键和辅助索引唯一的区别就是键值是否唯一，两者都是根据索引列排序构建 B+Tree 的，在每个叶子节点加上指向堆表的行指针（row data pointer） 。堆表在各类数据库中也被广泛使用，MyISAM 存储引擎的表就是堆表。

----------.区别

1.1事务
Innodb支持事务，MyISAM不支持。

1.2外键
Innodb支持外键，MyISAM不支持。

1.3索引
Innodb是聚集索引，主键索引的叶子节点才有值，而其他索引只有向主键索引的指针。
MyISAM是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和其他索引是独立的。

1.4锁粒度
Innodb最小粒度是行锁，MyISAM最小粒度是表锁。

1.5表的行数
Innodb不保存表的行数，需要select count(*)…MyISAM中有一个变量保存表的行数，所以获取行数很快。

1.6存储结构
MyISAM有三个文件：表定义文件、数据文件、索引文件。
InnoDB：所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件）。InnoDB表的大小只受限于操作系统文件的大小，一般为2GB。

1.7可移植性、备份及恢复
MyISAM：数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作。
InnoDB：免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump，在数据量达到几十G的时候就相对痛苦了。

2.使用
1.如果需要事务，选Innodb。
2.系统奔溃后，MyISAM恢复起来更困难，能否接受，不能接受就选 InnoDB。
3. 如果表中绝大多数都只是读查询，可以考虑 MyISAM，如果既有读写也挺频繁，请使用InnoDB。

3.支持特性的图

innoDB与MyISAM

1、InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务；
2、InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败；
3、InnoDB是聚集索引，使用B+Tree作为索引结构，数据文件是和（主键）索引绑在一起的（表数据文件本身就是按B+Tree组织的一个索引结构），必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。
4、MyISAM是非聚集索引，也是使用B+Tree作为索引结构，索引和数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
5、InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快（注意不能加有任何WHERE条件）；
6、Innodb不支持全文索引，而MyISAM支持全文索引，在涉及全文索引领域的查询效率上MyISAM速度更快高；PS：5.7以后的InnoDB支持全文索引了
MyISAM表格可以被压缩后进行查询操作
7、InnoDB支持表、行(默认)级锁，而MyISAM支持表级锁
InnoDB的行锁是实现在索引上的，而不是锁在物理行记录上。潜台词是，如果访问没有命中索引，也无法使用行锁，将要退化为表锁。
InnoDB表必须有唯一索引（如主键）（用户没有指定的话会自己找/生产一个隐藏列Row_id来充当默认主键），而Myisam可以没有
Innodb存储文件有frm、ibd，而Myisam是frm、MYD、MYI
8、Innodb：frm是表定义文件，ibd是数据文件
Myisam：frm是表定义文件，myd是数据文件，myi是索引文件

选择原则

是否要支持事务，如果要请选择innodb，如果不需要可以考虑MyISAM；
如果表中绝大多数都只是读查询，可以考虑MyISAM，如果既有读也有写，请使用InnoDB。
系统奔溃后，MyISAM恢复起来更困难，能否接受；
MySQL5.5版本开始Innodb已经成为Mysql的默认引擎(之前是MyISAM)，说明其优势是有目共睹的，如果你不知道用什么，那就用InnoDB，至少不会差。