文章目录

• 前言
• • 一、MySql索引
  • • 1.1 什么是索引
    • 1.2 索引的优点
    • 1.3 索引的缺点
    • 1.4 索引的分类
    • 1.5 索引的操作
    • 1.6 索引的底层原理
    • 1.7 聚簇索引和非聚簇索引
    • 1.8 局部性原理
    • 1.9 磁盘预读
    • 1.10 回表
    • 1.11 索引覆盖
    • 1.12 索引下推
    • 1.13 最左匹配
  • 二、事务
  • • 2.1 什么是事务
    • 2.2 事务的四大特征
    • 2.2 事务隔离级别
    • 2.3 MVCC
  • 三、锁
  • • 3.1 锁的分类
    • 3.2 表锁
    • 3.3 行锁
    • 3.4 共享锁(读锁)
    • 3.5 排他锁(写锁)
    • 3.6 悲观锁
    • 3.7 乐观锁
  • 四、InnoDB和MYISAM的区别
  • • 相关问题
  • 参考文章

前言

一、MySql索引

1.1 什么是索引

一种提高mysql查询效率的数据结构

1.2 索引的优点

大大加快了查询效率

1.3 索引的缺点

• 维护索引需要耗费数据库资源
• 索引需要占用磁盘空间
• 当对表的数据进行增删改操作时，速度会受到影响

1.4 索引的分类

InnoDB存储引擎中

• 主键索引

创建一个主键后，数据库会自动创建索引，innodb默认为聚簇索引
主键索引的索引列值不能为空

• 普通索引(辅助索引/二级索引)

给表中的某个字段加一个索引，也叫单值索引
即一个索引只包含一个列，一个表可以有多个单值索引
按照单个条件（字段）查询时可以用

• 唯一索引

索引列的值必须唯一，但允许有空值(但只能存在一个null)

• 联合索引

多个列组合在一起创建的索引，即一个索引包含多个列
按照多个字段进行查询时可用 where id=xxxx and age=x

• Full Text 全文索引(My5.7版本之前 只能用于MYISAM引擎)

全文索引类型为FULLTEXT，在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。全文索引可以在CHAR、VARCHAR、TEXT类型列上创建。MYSQL只有MYISAM存储引擎支持全文索引

1.5 索引的操作

• 主键索引—自动创建

create table user(id varchar(20) primary key,name varchar(10));
建表后，自动为主键创建索引

• 查看索引

show index from user(表名)

• 创建普通索引

• 建表时创建
create table user(id varchar(20) primary key,name varchar(10),key(name));
这种方式创建索引不能手动定义索引名字，是一个默认的索引名(与列名一致)
• 建表后创建
create index name_index(索引名) on user(表名)（name）;

• 创建唯一索引

• 建表时创建
create table user(id varchar(20) primary key,name varchar(10),unique(name));
• 建表后创建
create unique index name_index on user(name);

• 创建联合索引

• 建表时创建
create table user(id varchar(20) primary key,name varchar(10),age int,key(name,age));
这种方式创建索引不能手动定义索引名字，是一个默认的索引名
• 建表后创建
create index name_age_index on user(name,age);

1.6 索引的底层原理

索引的存储结构：1、hash表 2、B+树

• 哈希表

问题：1、会存在哈希冲突的问题，所以要有一个优秀的哈希算法
2、哈希表会整个全部加载到内存中，对内存空间比较依赖
3、不能进行范围查询，只能逐个比较
注意：mysql中的memory存储引擎使用的是hash索引，而且innodb支持自适应hash

• BST 二叉搜索树

缺点：如果插入的数据递增，就会退化成链表

• AVL树 二叉平衡树

特点：在进行插入时会进行旋转操作，保证左右两棵树尽可能平衡，左子树跟右子树高度之差不能超过1。
缺点：AVL树在插入时会进行多次旋转操作，会很浪费时间，它是牺牲插入性能来提升查询性能，那我们如果想要插入性能和查询性能都差不多呢？是不是就不能用AVL树了。

• 红黑树

红黑树是非严格的二叉平衡树，最长路径不超过最短路径的2倍，做到了插入性能和查询性能近乎差不多。但是，随着数据的插入，树的深度会不断增加，从而导致io次数增多，查询速度会变慢。

• B树

为了解决树的深度过深的问题，就出现了多叉树。
缺点：非叶子节点会存储data，导致整个树存储的数据量较少。

• B+树

优点：非叶子节点只存储key
只在叶子节点存储数据，并且每一个节点可以存储多条记录
各叶子节点通过指针连接形成链表
一个3层的B+树能够存储10亿左右的数据

Q、为什么说B+树比B树更适合数据库索引？
1）B+树的磁盘读写代价更低
　　B+树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了；
2）B+树查询效率更加稳定
　　由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当；
3）B+树便于范围查询（最重要的原因，范围查找是数据库的常态）
　　B树在提高了IO性能的同时并没有解决元素遍历的我效率低下的问题，正是为了解决这个问题，B+树应用而生。B+树只需要去遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作或者说效率太低；

补充：B树的范围查找用的是中序遍历，而B+树用的是在链表上遍历

1.7 聚簇索引和非聚簇索引

聚簇索引：将数据存储与索引放到了一起，索引结构的叶子节点保存行数据

非聚簇索引：将数据与索引分开存储，索引结构的叶子节点指向了数据对应的位置

在InnoDB中，聚簇索引默认是主键，如果表中没有定义主键，InnoDB 会选择一个唯一且非空的索引代替。如果没有这样的索引，InnoDB
会隐式定义一个主键（类似oracle中的RowId）来作为聚簇索引。如果已经设置了主键为聚簇索引又希望再单独设置聚簇索引，必须先删除主键，然后添加我们想要的聚簇索引，最后恢复设置主键即可。

1.8 局部性原理

数据和程序都有聚集成群的倾向，分为空间局部性和时间局部性

1.9 磁盘预读

内存跟磁盘再进行交互的时候要保证每次读取需要一个逻辑单位，这个逻辑单位叫做页(datapage)，它的大小一般是4k或者8k,在进行读取时一般是4k的整数倍。Innodb每次读取16kb的数据。

1.10 回表

当使用其他索引时，叶子节点并不包含整行数据值，需要去主键索引中进行数据查找的过程叫做回表。

1.11 索引覆盖

一个包含查询所需字段的索引称为“覆盖索引”，就是说当前索引中包含要查询的所有字段，那么就不用回表了，这个就是索引覆盖(覆盖索引)。
MySQL只需要通过索引就可以返回查询所需要的数据，而不必在查到索引之后进行回表操作，减少IO，提高了效率。

1.12 索引下推

• 不使用索引条件下推优化时存储引擎通过索引检索到数据，然后返回给MySQL服务器，服务器然后判断数据是否符合条件。
• 当使用索引条件下推优化时，如果存在某些被索引的列的判断条件时，MySQL服务器将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器。索引条件下推优化可以减少存储引擎查询基础表的次数，也可以减少MySQL服务器从存储引擎接收数据的次数。

1.13 最左匹配

最左优先，以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(>、<、between、like)就会停止匹配。适用于联合索引的情况。
注意：Mysql引擎在查询时为了更好的利用索引，在查询过程中会动态调整字段顺序
例：复合索引三个字段(name,age,sex)
基于name,sex,age查询 能否利用索引？调整后：name,age,sex,所以可以
基于name,age,sex查询 能否利用索引？肯定可以
基于age,sex查询 能否利用索引？不可以，因为它不包含name(最左前缀)
基于sex,age,name查询 能否利用索引？调整后：name,age,sex,所以可以

二、事务

2.1 什么是事务

数据库中的事务是指对数据库执行一批操作，这些操作最终要么全部执行成功，要么全部失败。

2.2 事务的四大特征

• 原子性(Atomicity)

事务的整个过程如原子操作一样，最终要么全部成功，或者全部失败，这个原子性是从最终结果来看的，从最终结果来看这个过程是不可分割的。

• 一致性(Consistency)

事务开始之前、执行中、执行完毕，这些时间点，多个人去观察事务操作的数据的时候，看到的数据都是一致的。

• 隔离性(Isolation)

一个事务的执行不能被其他事务干扰。每个事务的执行过程是相对独立的。

• 持久性(Durability)

一个事务一旦提交，他对数据库中数据的改变就应该是永久性的。当事务提交之后，数据会持久化到硬盘。

2.2 事务隔离级别

• 读未提交(read uncommitted)

事务A和事务B，事务A未提交的数据，事务B读取到了，此时事务B读取到的数据就称为“脏数据”。

• 读已提交(read committed)

事务A和事务B，事务A提交的数据，事务B才能读取到。这种级别可以避免”脏读”，但会导致“不可重复读”和“幻读”的问题。

不可重复读：是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。

幻读：是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。

不可重复读和幻读的区别：
不可重复读的重点是修改:同样的条件, 你读取过的数据, 再次读取出来发现值不一样了。
幻读的重点在于新增或者删除：同样的条件, 第1次和第2次读出来的记录数不一样。

• 可重复读(repeatable read)

事务A和事务B，事务A提交的数据，事务B不能读取到。就是说事务B读取到的数据是事务A开始之前的数据，事务B是可重复读读取数据。这种级别虽然可以避免“不可重复读”问题，但依旧有“幻读”的问题，这是MySQL默认的隔离级别。

• 串行化(serializable)

事务A和事务B，事务A在操作数据库时，事务B只能排队等待。这种隔离级别很少使用，因为其吞吐量太低，用户体验差。这种级别可以避免“幻读”问题。

2.3 MVCC

MVCC是指多版本并发控制。

三、锁

锁的作用：满足事务的隔离性，保证事务的一致性

3.1 锁的分类

1、按照锁的粒度：表锁和行锁
2、按照锁的级别：共享锁和排他锁
3、按照锁的方式：乐观锁和悲观锁

3.2 表锁

优点：开销小，加锁快，不会出现死锁
缺点：锁定粒度大，发生锁冲突的概率最高，并发度最低

3.3 行锁

优点：锁定粒度小，发生锁冲突的概率最低，并发度最高
缺点：开销大，加锁慢，会出现死锁
只有innodb存储引擎支持

3.4 共享锁(读锁)

共享锁就是多个事务对于同一数据可以共享一把锁，都能访问数据，但是只能读数据不能写数据(只读)。

3.5 排他锁(写锁)

排他锁就是一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行数据的其他锁，包括共享锁和排他锁，但是获取了该数据行排他锁的事务可以对数据进行读取和修改。

3.6 悲观锁

悲观锁，正如其名，具有强烈的独占和排他特性。它指的是对数据被外界(包括本系统当前的其他事务，以及来自外部系统的事务处理)修改持保守态度。因此，在整个数据处理过程中，将数据处于锁定状态。悲观锁的实现，往往依靠数据库提供的锁机制(也只有数据库层提供的锁机制才能真正保证数据访问的排他性，否则，即使在本系统中实现了加锁机制，也无法保证外部系统不会修改数据)。

之所以叫做悲观锁，是因为这是一种对数据的修改抱有悲观态度的并发控制方式。我们一般认为数据被并发修改的概率比较大，所以需要在修改之前先加锁。
悲观并发控制实际上是“先取锁再访问”的保守策略，为数据处理的安全提供了保证。但是在效率方面，处理加锁的机制会让数据库产生额外的开销，还有增加产生死锁的机会。另外还会降低并行性，一个事务如果锁定了某行数据，其他事务就必须等待该事务处理完才可以处理那行数据。

3.7 乐观锁

乐观锁机制采取了更加宽松的加锁机制。乐观锁是相对悲观锁而言，也是为了避免数据库幻读、业务处理时间过长等原因引起数据处理错误的一种机制，但乐观锁不会刻意使用数据库本身的锁机制，而是依据数据本身来保证数据的正确性。

相对于悲观锁，在对数据库进行处理的时候，乐观锁并不会使用数据库提供的锁机制。一般的实现乐观锁的方式就是记录数据版本。乐观锁就是 version字段（比较跟上一次的版本号，如果一样则更新，如果失败则要重复读-比较-写的操作。）

乐观并发控制相信事务之间的数据竞争(data race)的概率是比较小的，因此尽可能直接做下去，直到提交的时候才去锁定，所以不会产生任何锁和死锁。

四、InnoDB和MYISAM的区别

存储引擎：不同的数据文件在磁盘的不同组织形式
区别：
1、 InnoDB支持事务， MyISAM不支持
2、 InnoDB支持外键， MyISAM不支持
3、 InnoDB支持表锁和行锁， MyISAM只支持表锁
4、 InnoDB在5.6版本之后支持全文索引
5、 InnoDB索引的叶子节点直接存放数据，而 MyISAM存放地址
6、应用场景：
MyISAM 适用 【Select】频繁，并发少
InnoDB 适用 【Insert Delete Update】频繁，高并发
原因：
(1) MyISAM 仅缓存索引块，而InnoDB缓存 {索引+数据}
(2) InnoDB 回表需要映射块，而MyISAM 直接映射数据地址
(3) InnoDB 需要维护MVCC机制

相关问题

Q1:mysql的索引一般有几层？
答：一般情况下，3-4层就足以支撑千万级别的表查询。
Q2:创建索引的字段是长了好还是短了好？
答：短了好。原因：在层数不变的情况下，可以存储更多的数据量。
Q3:我们在创建表的时候是用代理主键还是自然主键？
答：能使用代理主键尽量使用代理主键
Q4:主键设置好之后，要不要自增？
答：在满足业务的情况下，尽量自增，因为不自增会增加索引维护的成本(会破坏中间页的结构)。自增的话插入数据时直接在后面追加，不自增时插入数据会破坏前面的数据结构。
聚簇索引的数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。如果主键不是自增id，那么可以想象，它会干些什么，不断地调整数据的物理地址、分页，当然也有其他一些措施来减少这些操作，但却无法彻底避免。但，如果是自增的，那就简单了，它只需要一页一页地写，索引结构相对紧凑，磁盘碎片少，效率也高。

参考文章

mysql事务：https://blog.csdn.net/qq_43549291/article/details/112120707
mysql锁机制：https://blog.csdn.net/qq_32679835/article/details/93745182
MVCC多版本并发控制机制：https://blog.csdn.net/weixin_44666068/article/details/104616119
mysql存储引擎分析：https://blog.csdn.net/u014229282/article/details/81121442