（面经整理二）Mysql数据库索引、事务、锁

Mysql数据库

Mysql数据库引擎

• InnoDB
  • InnoDB是事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键，InnoDB是默认的MySQL引擎。
• MyIsam
  • 它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM拥有较高的插入、查询速度，但不支持事务。
  • 补充：ISAM索引方法–索引顺序存取方法
• Memory（也叫HEAP）堆内存嘛
• Mrg_Myisam：（分表的一种方式–水平分表）
• Blackhole（黑洞引擎）

数据库索引

数据库索引其实就是为了使查询数据效率快。索引是定义在table基础之上，有助于无需检查所有记录而快速定位所需记录的一种辅助存储结构，由一系列存储在磁盘上的索引项组成，每一种索引项由索引字段和行指针构成。

索引是存储引擎用于提高数据查询效率的一种数据结构，索引类似于字典里的目录。Mysql中的索引是在存储引擎层实现的，索引的数据结构和存储引擎有关。

MySQL中索引底层原理分类

• hash索引：底层就是 hash 表。进行查找时，根据 key 调用hash 函数获得对应的 hashcode，根据 hashcode 找到对应的数据行地址，根据地址拿到对应的数据。
• B树索引：B树是一种多路搜索树，n 路搜索树代表每个节点最多有 n 个子节点。每个节点存储 key + 指向下一层节点的指针+ 指向 key 数据记录的地址。查找时，从根结点向下进行查找，直到找到对应的key。
• B+树索引：B+树是b树的变种，主要区别在于：B+树的非叶子节点只存储 key + 指向下一层节点的指针，也就是只存索引，不存数据，数据都保存在叶子节点中。另外，B+树的叶子节点之间通过指针来连接，构成一个有序链表，因此对整棵树的遍历只需要一次线性遍历叶子结点即可。

Mysql索引分类

索引主要分为四类

1、单列索引：一个索引只包含单个列，但一个表中可以有多个单列索引。

• 普通索引：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点。

• 唯一索引：索引列中的值必须是唯一的，但是允许为空值，

• 主键索引：是一种特殊的唯一索引，不允许有空值。

2、组合索引：在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀匹配原则。
3、全文索引：全文索引，只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引
4 、空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有四中GEOMETRY、POINT、LINESTRING、POLYGON。在创建空间索引时，使用SPATIAL关键字。要求，引擎为MyISAM，创建空间索引的列，必须将其声明为NOT NULL。

如何查询查询语句有没有走索引——可以使用explain语句

EXPLAIN SELECT * FROM book WHERE year_publication = 1990\G;

最左前缀匹配原则：
组合索引遵从最左前缀，利用索引中最左边的列集来匹配行，这样的列集称为最左前缀例如，这里由id、name和age3个字段构成的索引，索引行中就按id/name/age的顺序存放，索引可以索引下面字段组合(id，name，age)、(id，name)或者(id)。如果要查询的字段不构成索引最左面的前缀，那么就不会是用索引，比如，age或者（name，age）组合就不会使用索引查询，会进行全表扫描。

聚簇索引和非聚簇索引（按查询方式划分）

聚簇索引的叶子节点就是数据节点，而非聚簇索引的叶子节点仍然是索引节点，只不过有指向对应数据块的指针。
聚簇索引的顺序，就是数据在硬盘上的物理顺序。一般情况下主键就是默认的聚簇索引。
聚簇索引在查询的时候会更快，因为主键索引树的叶子节点直接就是我们要查询的整行数据了。而非主键索引的叶子节点是主键的值，而非主键索引需要回表查询多次。通过覆盖索引也可以只查询一次。

覆盖索引：指一个查询语句的执行只用从索引中就能够取得，不必从数据表中读取。也可以称之为实现了索引覆盖。 当一条查询语句符合覆盖索引条件时，MySQL只需要通过索引就可以返回查询所需要的数据，这样避免了查到索引后再返回表操作，减少I/O，提高效率。

MySQL中优化sql语句查询

1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
2.应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
3.应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：select id from t where num=10 or num=20可以这样查询：
select id from t where num=10 union all
select id from t where num=20
4.在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。
5.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。
关于索引语句优化，可以参考 https://www.cnblogs.com/zhaoyang-1989/p/4824876.html

事务（InnoDB引擎）

数据库事务是构成单一逻辑工作单元的操作集合。

关于事务的定义有几点需要解释下：

• 1.数据库事务可以包含一个或多个数据库操作,但这些操作构成一个逻辑上的整体。
• 2.构成逻辑整体的这些数据库操作,要么全部执行成功,要么全部不执行。
• 3.构成事务的所有操作,要么全都对数据库产生影响,要么全都不产生影响,即不管事务是否执行成功,数据库总能保持一致性状态。
• 4.以上即使在数据库出现故障以及并发事务存在的情况下依然成立。

事务的四大特性（ACID）

• 原子性：是当事务回滚时能够撤销所有已经成功执行的sql语句。当事务对数据库进行修改时，InnoDB会生成对应的undo log。如果事务执行失败，调用了rollback，事务进行回滚。
• 持久性：指事务一旦提交，它对数据库的改变就应该是永久性的。主要依靠redo log保证持久性。所有修改先写入日志，再更新到Buffer Pool，保证了数据不会因MySQL宕机而丢失，从而满足了持久性要求。
• 一致性：指事务执行结束后，数据库的完整性约束没有被破坏，事务执行的前后都是合法的数据状态。在保证原子性，隔离性和持久性的基础上保证一致性，靠undo log和redo log来保证。
• 隔离性：多个并发事务在访问相同的数据集的时候，要保证隔离性。主要靠数据库的锁来保证。

MVCC与事务的隔离级别

• 读未提交：一个事务可以读取到另一个事务未提交的修改。这会带来脏读、幻读、不可重复读问题。

• 读已提交：一个事务只能读取另一个事务已经提交的修改。其避免了脏读，但仍然存在不可重复读和幻读问题。

• 可重复读：同一个事务中多次读取相同的数据返回的结果是一样的。其避免了脏读和不可重复读问题，但幻读依然存在。

• 串行化：事务串行执行。避免了以上所有问题。

MySQL中，默认的隔离级别是REPEATABLE-READ（可重复读）。

脏读:一个事务正在对一条记录做修改，在这个事务完成并提交之前，这条数据是处于待定状态的（可能提交也可能回滚），这时，第二个事务来读取这条没有提交的数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。这种现象被称为脏读。

不可重复读：事务A首先读取了一条数据，然后执行逻辑的时候，事务B将这条数据改变了，然后事务A再次读取的时候，发现数据不匹配了，就是所谓的不可重复读了。

幻读：事务A读取与搜索条件相匹配的若干行。事务B以插入或删除行等方式来修改事务A的结果集，然后再提交。在一个事务A中，第一次查询某条记录，是没有的，但是，当试图更新这条不存在的记录时，竟然能成功，并且，再次读取同一条记录，它就神奇地出现了。
解决：使用表级锁，锁定整张表，事务A多次读取数据的总量之后才释放该锁，然后才允许其他事务新增数据。

在MySQL中，MVCC只在读取已提交（Read Committed）和可重复读（Repeatable Read）两个事务级别下有效。其是通过Undo日志中的版本链和ReadView一致性视图来实现的.

数据库锁

锁类型：表锁、页锁、行锁

表锁

表锁分为读锁（共享锁）和写锁（排他锁）

• 读锁（read lock），也叫共享锁（shared lock）针对同一份数据，多个读操作可以同时进行而不会互相影响（select）
• 写锁（write lock），也叫排他锁（exclusive lock）
  当前操作没完成之前，会阻塞其它读和写操作（update、insert、delete）
  表锁的默认引擎是Myisam。
  表锁的特点

对整张表加锁
开销小
加锁快
无死锁
锁粒度大，发生锁冲突概率大，并发性低

MyISAM的读写锁调度是写优先，这也是MyISAM不适合做写为主表的引擎，因为写锁以后，其它线程不能做任何操作，大量的更新使查询很难得到锁，从而造成永远阻塞。

行锁

行锁主要分为四种

• 读锁（read lock），也叫共享锁（shared lock） 允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁

• 写锁（write lock），也叫排他锁（exclusive lock）
  允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享锁和排他锁

• 意向共享锁（IS） 一个事务给一个数据行加共享锁时，必须先获得表的IS锁

• 意向排它锁（IX） 一个事务给一个数据行加排他锁时，必须先获得该表的IX锁

默认的引擎是InnoDB。
行锁的特点：

对一行数据加锁
开销大
加锁慢
会出现死锁
锁粒度小，发生锁冲突概率最低，并发性高

事务并发量带来的问题：

脏读
幻读
不可重复读
更新丢失

页锁

页锁的开销，并发量和锁的粒度都介于行锁和表锁之间。

Mysql加锁

表锁加锁

• 隐式锁：隐式上锁（默认，自动加锁自动释放）

select // 上读锁
insert、update、delete // 上写锁

• 显式上锁（手动）

lock table tableName read;  // 读锁
lock table tableName write; // 写锁

• 解锁（手动）

unlock tables; // 所有锁表

行锁加锁

• 隐式上锁（默认，自动加锁自动释放）

select // 不会上锁
insert、update、delete // 上写锁

• 显式上锁（手动）

select * from tableName lock in share mode; // 读锁
select * from tableName for update;         // 写锁

• 解锁（手动）

1. 提交事务（commit）
2. 回滚事务（rollback）
3. kill 阻塞进程

行锁的实现算法

• Record Lock锁

单个行记录上的锁；Record
Lock总是会去锁住索引记录，如果InnoDB存储引擎表建立的时候没有设置任何一个索引，这时InnoDB存储引擎会使用隐式的主键来进行锁定

• Gap Lock锁

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引加锁，对于键值在条件范围内但并不存在的记录。

优点：解决了事务并发的幻读问题 不足：因为query执行过程中通过范围查找的话，他会锁定争个范围内所有的索引键值，即使这个键值并不存在。
间隙锁缺点：就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成锁定的时候无法插入锁定键值范围内任何数据。在某些场景下这可能会对性能造成很大的危害。

• Next-key Lock锁

同时锁住数据+间隙锁。在Repeatable Read隔离级别下，Next-key Lock 算法是默认的行记录锁定算法。

• 行锁的注意点

1. 只有通过索引条件检索数据时，InnoDB才会使用行级锁，否则会使用表级锁(索引失效，行锁变表锁)
2. 即使是访问不同行的记录，如果使用的是相同的索引键，会发生锁冲突
3. 如果数据表建有多个索引时，可以通过不同的索引锁定不同的行

乐观锁和悲观锁

并发控制的任务是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性。

乐观并发控制(乐观锁)和悲观并发控制（悲观锁）是并发控制主要采用的技术手段。
悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作;在商品购买场景中，当有多个用户对某个库存有限的商品同时进行下单操作。若采用先查询库存，后减库存的方式进行库存数量的变更，将会导致超卖的产生。

select * from tbl_user where id=1 for update;

乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。

使用数据版本（Version）记录机制实现，即为数据增加一个版本标识，一般是通过为数据库表增加一个数字类型的 “version” 字段来实现。当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值加一。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据。

数据库中drop、truncate、delete三种删除的区别

drop：drop table 表名

删除内容和定义，并释放空间。执行drop语句，将使此表的结构一起删除。

truncate (清空表中的数据)：truncate table 表名

删除内容、释放空间但不删除定义(也就是保留表的数据结构)。与drop不同的是,只是清空表数据而已。

truncate不能删除行数据，虽然只删除数据，但是比delete彻底，它只删除表数据。

delete：delete from 表名 （where 列名 = 值）

与truncate类似，delete也只删除内容、释放空间但不删除定义；但是delete既可以对行数据进行删除，也可以对整表数据进行删除。

执行速度一般来说：drop>truncate>delete,原因：delete语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存，以便进行进行回滚操作。

适用场景：

• 不再需要一张表的时候，用drop
• 想删除部分数据行时候，用delete，并且带上where子句
• 保留表而删除所有数据的时候用truncate

如有错误，请评论指正。
参考链接：

https://www.jianshu.com/p/0d6c828d3c70
https://www.cnblogs.com/zhaoyang-1989/p/4824876.html
https://www.sapril.com/view/5.html