1.MySQL索引、锁、事务

阅读量

一、MySQL索引

1.数据结构

1.1、数据以文本形式保存

  1. 将所要保存的数据，写入文本文件。这个文本文件就是你的数据库。
  2. 为了方便读取，数据必须分成记录，每一条记录的长度规定为等长。比如，假定每条记录的长度是800字节，那么第5条记录的开始位置就在3200字节。（(5-1) * 800）
  3. 大多数时候，我们不知道某一条记录在第几个位置，只知道主键（primary key）的值。这时为了读取数据，可以一条条比对记录。但是这样做效率太低，实际应用中，数据库往往采用B+树格式储存数据。

    1.2、什么是B树？

    要理解B树，必须从二叉查找树（Binary search tree）讲起。
    加载图片！
    二叉查找树是一种查找效率非常高的数据结构，它有三个特点。
  4. 每个节点最多只有两个子树。
  5. 左子树都为小于父节点的值，右子树都为大于父节点的值。
  6. 在n个节点中找到目标值，一般只需要log(n)次比较。

二叉查找树的结构不适合数据库，因为它的查找效率与层数相关。越处在下层的数据，就需要越多次比较。极端情况下，n个数据需要n次比较才能找到目标值。对于数据库来说，每进入一层，就要从硬盘读取一次数据，这非常致命，因为硬盘的读取时间远远大于数据处理时间，数据库读取硬盘的次数越少越好。

B树是对二叉查找树的改进。它的设计思想是，将相关数据尽量集中在一起，以便一次读取多个数据，减少硬盘操作次数。

加载图片！
B树的特点也有三个。

  1. 一个节点可以容纳多个值。比如上图中，最多的一个节点容纳了4个值。
  2. 除非数据已经填满，否则不会增加新的层。也就是说，B树追求”层”越少越好。
  3. 子节点中的值，与父节点中的值，有严格的大小对应关系。一般来说，如果父节点有N个值，那么就有N+1个子节点。比如上图中，父节点有两个值（7和16），就对应三个子节点，第一个子节点都是小于7的值，最后一个子节点都是大于16的值，中间的子节点就是7和16之间的值。

这种数据结构，非常有利于减少读取硬盘的次数。假定一个节点可以容纳100个值，那么3层的B树可以容纳100万个数据，如果换成二叉查找树，则需要20层！假定操作系统一次读取一个节点，并且根节点保留在内存中，那么B树在100万个数据中查找目标值，只需要读取两次硬盘。

1.3、B+树

B树虽然减少了磁盘读取次数，但是未解决回旋查找的问题。如上图中查找大于12的数字，那么它首先定位到12的位置，然后再返回查找16，然后再查找12，再返回查找16，再查找18….。
什么是B+树？

1.3、索引

索引是帮助MySQL高效获取数据的数据结构（索引的本质是：数据结构，目的是：提高查询效率）。

数据库以B树格式储存，只解决了按照”主键”查找数据的问题。如果想查找其他字段，就需要建立索引（index）。

所谓索引，就是以某个字段为关键字的B树文件。假定有一张”雇员表”，包含了员工号（主键）和姓名两个字段。可以对姓名建立索引文件，该文件以B树格式对姓名进行储存，每个姓名后面是其在数据库中的位置（即第几条记录）。查找姓名的时候，先从索引中找到对应第几条记录，然后再从表格中读取。

1.4、索引优势与劣势

索引优势：提高查询效率、降低数据排序成本。
索引劣势：实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也占内存空间。索引降低了插入、更新、删除的效率。

1.5、索引分类

  1. 单值索引：即一个索引只包含单个列，一个表可以有多个单列索引。
  2. 唯一索引：索引列的值必须唯一，但允许有空值。
  3. 复合索引：一个索引包含多个列。（index MultiIdx(id,name,age)）
  4. 全文索引：只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引。

2.索引失效原因

2.1、分析

如某个表中对id、age两个字段建立复合索引，那它底层存储结构如下:
加载图片！
可以得出以下结论：

  1. 左边的值，即id的值是顺序的：1、1、2、2、3、3
  2. 右边的值，即age的值是无序的：1、2、1、4、1、2
  3. 在左边的值相等的情况下，右边的值是有序的：如选区左边的值1，那么右边的值为1、2 （最左前缀法则）

即若对连个字段进行排序，先对A进行排序，在A相同的情况下，再对B进行排序。

2.2、最左前缀法则

1.建立表格

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `name` varchar(30) NOT NULL,
  `phone` varchar(30) NOT NULL,
  `lan_id` int(11) NOT NULL,
  `area_id` int(11) NOT NULL,
  `time` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_phone_lan_area` (`phone`,`lan_id`,`area_id`),
  KEY `idx_create_time` (`time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2.分析

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
# 使用了索引 idx_phone_lan_area (换顺序编译器会优化)
EXPLAIN SELECT * FROM user WHERE phone="121" AND lan_id=1
EXPLAIN SELECT * FROM user WHERE lan_id=1 And phone="121"

# 未使用索引 （违反最左前缀法则）
EXPLAIN SELECT * FROM user WHERE lan_id=1

# 未使用索引 （违反最左前缀法则） 看上面的图（phone使用了索引，但lan_id索引失效）
# 即范围查找之后的索引会失效
EXPLAIN SELECT * FROM user WHERE phone>"121" AND lan_id=1

# 未使用索引 （违反最左前缀法则） 看上面的图
# 对于字符串"abcd"、"acdv",字符串在B+树中存储的时候，也是按照字母大小排序，先排首字母,
# 首字母相同，再从第二个字母开始排....
# "%121"   要查询的数据在右边，所以叫后缀: 不走索引 
# "%121%"  中缀：不走索引
# "121%"   前缀：走索引
EXPLAIN SELECT * FROM user WHERE phone LIKE "%121"
EXPLAIN SELECT * FROM user WHERE phone LIKE "%121%"
EXPLAIN SELECT * FROM user WHERE phone LIKE "121%"

2.3、索引失效

  1. 违法最左前缀法（包括like非前缀查找、范围查找的某些情况）
  2. 不要在索引列上做任何操作（计算、函数、(自动or手动)类型转换），这样会导致索引失效而转向全表扫描（如：+、-、*、/、!=、<>、sum()等函数、手动/自动类型转换。
  3. is null、is not null
  4. or
  5. <、<=、=、>、>=、BETWEEN、IN 不会导致索引失效。<>、not in、!= 则会导致全表扫描，索引失效。
1
EXPLAIN SELECT * FROM user WHERE phone LEFT(phone, 3) = "121"

2.4、慎用 select *

  1. select * 会查询许多不必要的字段，浪费性能、网络带宽。
  2. select * 使用不到覆盖索引。
    1
    2
    3
    4
    5
    # `select`查询字段和`where`中使用的索引字段一致，会使用到覆盖索引
    EXPLAIN SELECT phone, lan_id FROM user WHERE phone = "11" AND lan_id = "234"

    # like 中缀、前缀使用索引。下面这行代码会走覆盖索引
    EXPLAIN SELECT id FROM user WHERE id LIKE "%12%"

2.5、order by 优化

当查询语句中使用 order by 进行排序时，如果没有使用索引进行排序，会出现 filesort 文件内排序，这种情况在数据量大或者并发高的时候，会有性能问题，需要优化。
2.5.1、filesort 出现的情况举例

  1. order by 字段不是索引字段
  2. order by 字段是索引字段，但是 select 中没有使用覆盖索引，如：select * from staffs order by age asc
  3. order by 中同时存在 ASC 升序排序和 DESC 降序排序，如：select a, b from staffs order by a desc, b asc
  4. order by 多个字段排序时，不是按照索引顺序进行 order by，即不是按照最左前缀法则，如：select a, b from staffs order by b asc, a asc

2.5.2、索引层面解决方法

  1. 使用主键索引排序
  2. 按照最左前缀法则，并且使用覆盖索引排序，多个字段排序时，保持排序方向一致
  3. 在 SQL 语句中强制指定使用某索引，force index(索引名字)
  4. 不在数据库中排序，在代码层面排序

2.6、group by 优化

其原理也是先排序后分组，其优化方式可参考order by。where高于having,能写在where限定的条件就不要去having限定了。

3.MyIsam 和 InnoDB的常见区别

InnoDB MyIsam
事务 支持 不支持
外键 支持 不支持
存储结构 数据文件、表结构文件
（索引与数据在一起：聚集索引）
聚集索引默认主键为索引，若无主键就选择唯一的非空字段代替。
若无此字段，会生产一个隐式索引。		 数据文件、表结构文件、索引文件
（即索引与数据分开：非聚集索引）
索引 聚集索引、不支持全文索引 非聚集索引、全文索引
锁 行级锁、表级锁、间隙锁 表级锁

二、锁

mysql锁分为共享锁和排他锁，也叫做读锁和写锁。

  1. 读锁是共享的，可以通过lock in share mode实现，这时候只能读不能写。
  2. 写锁是排他的，它会阻塞其他的写锁和读锁。可分为表锁、行锁、间隙锁。
  • 表锁会锁定整张表并且阻塞其他用户对该表的所有读写操作，比如alter修改表结构的时候会锁表。
  • 行锁又可以分为乐观锁和悲观锁:
  1. 悲观锁可以通过for update实现。
  2. 乐观锁则通过版本号实现。

由于MySQL数据库是自动提交事务的，在进行锁测试时要把自动提交改为手动提交事务。

1
set autocommit = 0;

1.行级锁

1.建立A链接，执行以下操作：

1
2
3
update student set age = 10 where id = 22;
select * from student where id = 22;  
# 结果：age为10

2.建立B链接，执行以下操作：

1
2
3
4
select * from student where id = 22;  
# 结果：age为原来的值没有变为10
# 因为A执行了update但是未commit，所以A依旧持有锁。（避免了脏读的发生）
# 如果A未commit时，B对那一行数据进行update操作，那么B的操作会被阻塞，直到A执行commit。

2.表级锁

索引失效，行级锁会变为表级锁。
1.建立A链接，执行以下操作：

1
update student set age = 10 where id = 22 or id = 33;

2.建立B链接，执行以下操作：

1
2
3
update student set age = 23 where id = 44;
# 结果：B的操作被阻塞。
# A更新的是id=22或33的值，B更新的是id=44的值。若是行级锁，B不可能被阻塞，所以是表级锁。

3.间隙锁

如数据库中，id为1、3、5、7、9，则缺少的2、4、6、8就是间隙。间隙说引发的锁就是间隙锁。
间隙锁一般发生在范围查找中，如A操作id为1-9的这段数据，数据库就会把这段数据加锁。
1.建立A链接，执行以下操作：

1
update student set age = 10 where id >1 AND id < 9;

2.建立B链接，执行以下操作：

1
2
insert into student value(4, "插入数据");
# 结果：B的操作被阻塞。因为A还未释放锁。

4.分析InnoDB引擎当前的锁情况

1
show status like 'innodb_now_lock%';

三、事务

3.1、事务基本特性：原子性、一致性、隔离性、持久性。（ACID）

事务是由一组SQL语句组成的逻辑处理单元，具有4个属性，通常简称为事务的ACID属性。
事务的隔离性是通过锁实现，而事务的原子性、一致性和持久性则是通过事务日志实现 。

  1. 原子性：一个事务中的所有操作要么全部成功，要么全部失败。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
  2. 一致性：事务的执行结果必须使数据库从一个一致性的状态转换到另外一个一致性的状态，也就是说一个事务执行前后都必须处于一致性状态。比如A转账给B 100块钱，假设中间sql执行过程中系统崩溃A也不会损失100块，因为事务没有提交，修改也就不会保存到数据库。
  3. 隔离性：一个事务的修改在最终提交前，其他事务是不能干扰的。多个并发事务之间要相互隔离。
  4. 持久性：一旦事务提交，所做的修改就会永久保存到数据库中。（数据从内存到数据库 -> 持久化）

3.2、事务的ACID原则靠什么保证的呢？

  1. 原子性由undo log日志保证，它记录了需要回滚的日志信息，事务回滚时撤销已经执行成功的sql
  2. 一致性一般由代码层面来保证
  3. 隔离性由MVCC来保证
  4. 持久性由内存+redo log来保证，mysql修改数据同时在内存和redo log记录这次操作，事务提交的时候通过redo log刷盘，宕机的时候可以从redo log恢复

3.3、事务隔离性的4个隔离级别

  1. 可重复读(默认)：指在一个事务中读取到了别的事务插入的数据，导致前后读取不一致。（本来是2条数据，后来加了一条，再读就是3条）(可以阻止脏读和不可重复读，但幻读仍有可能发生)
  2. 读未提交：就是一个事务可以读取到另一个事务未提交的数据。（可能会导致脏读、幻读或不可重复读）
  3. 读已提交：一个事务要等另一个事务提交后才能读取数据。（可以阻止脏读，但是幻读或不可重复读仍有可能发生）
  4. 串行化：在该级别下，所有事务形成队列，依次逐个执行，这样事务之间就完全不可能产生干扰。（可以防止脏读、不可重复读以及幻读）

3.4、并发事务处理带来的问题

  1. 更新丢失(Lost Update)：事务A和事务B选择同一行，然后基于最初选定的值更新该行时，由于两个事务都不知道彼此的存在，就会发生丢失更新问题。
  2. 脏读(Dirty Reads)：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据
  3. 不可重复读(Non-Repeatable Reads)：事务 A 多次读取同一数据，事务B在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致。
  4. 幻读(Phantom Reads)：幻读与不可重复读类似。它发生在一个事务A读取了几行数据，接着另一个并发事务B插入了一些数据时。在随后的查询中，事务A就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

3.4.1、幻读和不可重复读的区别：

  1. 不可重复读的重点是修改：在同一事务中，同样的条件，第一次读的数据和第二次读的数据不一样。（因为中间有其他事务提交了修改）
  2. 幻读的重点在于新增或者删除：在同一事务中，同样的条件,，第一次和第二次读出来的记录数不一样。（因为中间有其他事务提交了插入/删除）

3.5、脏读、不可重复读、幻读 和 事务隔离级别 的联系

数据库的事务隔离越严格，并发副作用越小，但付出的代价就越大，因为事务隔离实质上就是使事务在一定程度上“串行化”进行，这显然与“并发”是矛盾的。
同时，不同的应用对读一致性和事务隔离程度的要求也是不同的，比如许多应用对“不可重复读”和“幻读”并不敏感，可能更关心数据并发访问的能力。
1.Read uncommitted
读未提交，就是一个事务可以读取另一个未提交事务的数据。
事例：老板要给程序员发工资，程序员的工资是3.6万/月。但是发工资时老板不小心按错了数字，按成3.9万/月，该钱已经打到程序员的户口，但是事务还没有提交，就在这时，程序员去查看自己这个月的工资，发现比往常多了3千元，以为涨工资了非常高兴。但是老板及时发现了不对，马上回滚差点就提交了的事务，将数字改成3.6万再提交。
分析：实际程序员这个月的工资还是3.6万，但是程序员看到的是3.9万。他看到的是老板还没提交事务时的数据。这就是脏读。
那怎么解决脏读呢？Read committed！读提交，能解决脏读问题。

2.Read committed
读提交，顾名思义，就是一个事务要等另一个事务提交后才能读取数据。
事例：程序员拿着信用卡去享受生活（卡里当然是只有3.6万），当他埋单时（程序员事务开启），收费系统事先检测到他的卡里有3.6万，就在这个时候！！程序员的妻子要把钱全部转出充当家用，并提交。当收费系统准备扣款时，再检测卡里的金额，发现已经没钱了（第二次检测金额当然要等待妻子转出金额事务提交完）。程序员就会很郁闷，明明卡里是有钱的…
分析：这就是读提交，若有事务对数据进行更新（UPDATE）操作时，读操作事务要等待这个更新操作事务提交后才能读取数据，可以解决脏读问题。但在这个事例中，出现了一个事务范围内两个相同的查询却返回了不同数据，这就是不可重复读。
那怎么解决可能的不可重复读问题？Repeatable read ！

3.Repeatable read
重复读，就是在开始读取数据（事务开启）时，不再允许修改操作。MySQL的默认事务隔离级别
事例：程序员拿着信用卡去享受生活（卡里当然是只有3.6万），当他埋单时（事务开启，不允许其他事务的UPDATE修改操作），收费系统事先检测到他的卡里有3.6万。这个时候他的妻子不能转出金额了。接下来收费系统就可以扣款了。
分析：重复读可以解决不可重复读问题。写到这里，应该明白的一点就是，不可重复读对应的是修改，即UPDATE操作。但是可能还会有幻读问题。因为幻读问题对应的是插入INSERT操作，而不是UPDATE操作。
什么时候会出现幻读？
事例：程序员某一天去消费，花了2千元，然后他的妻子去查看他今天的消费记录（全表扫描FTS，妻子事务开启），看到确实是花了2千元，就在这个时候，程序员花了1万买了一部电脑，即新增INSERT了一条消费记录，并提交。当妻子打印程序员的消费记录清单时（妻子事务提交），发现花了1.2万元，似乎出现了幻觉，这就是幻读。
那怎么解决幻读问题？Serializable！

4.Serializable 序列化
Serializable 是最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读。简单来说，Serializable会在读取的每一行数据上都加锁，所以可能导致大量的超时和锁争用问题。这种事务隔离级别效率低下，比较耗数据库性能，一般不使用。

比较
| 事务隔离级别 | 读数据一致性 | 脏读 | 不可重复读 | 幻读 |
| :————————— | :————————————— | :— | :——— | :— |
| 读未提交（read-uncommitted） | 最低级被，只能保证不读取物理上损坏的数据 | 是 | 是 | 是 |
| 读已提交（read-committed） | 语句级 | 否 | 是 | 是 |
| 可重复读（repeatable-read） | 事务级 | 否 | 否 | 是 |
| 串行化（serializable） | 最高级别，事务级 | 否 | 否 | 否 |
需要说明的是，事务隔离级别和数据访问的并发性是对立的，事务隔离级别越高并发性就越差。所以要根据具体的应用来确定合适的事务隔离级别，这个地方没有万能的原则。

3.5、并发事务处理带来的问题的解决办法：

  1. “更新丢失”通常是应该完全避免的。但防止更新丢失，并不能单靠数据库事务控制器来解决，需要应用程序对要更新的数据加必要的锁来解决，因此，防止更新丢失应该是应用的责任。
  2. “脏读” 、 “不可重复读”和“幻读” ，其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决：
  • 一种是加锁：在读取数据前，对其加锁，阻止其他事务对数据进行修改。
  • 另一种是数据多版本并发控制（MultiVersion Concurrency Control，简称 MVCC 或 MCC），也称为多版本数据库：不用加任何锁， 通过一定机制生成一个数据请求时间点的一致性数据快照 (Snapshot)， 并用这个快照来提供一定级别 （语句级或事务级） 的一致性读取。从用户的角度来看，好象是数据库可以提供同一数据的多个版本。

3.6、事务日志

事务日志包括：重做日志redo和回滚日志undo
1、redo log（重做日志） 实现持久化和原子性
在innoDB的存储引擎中，事务日志通过重做(redo)日志和innoDB存储引擎的日志缓冲(InnoDB Log Buffer)实现。事务开启时，事务中的操作，都会先写入存储引擎的日志缓冲中，在事务提交之前，这些缓冲的日志都需要提前刷新到磁盘上持久化，这就是DBA们口中常说的“日志先行”(Write-Ahead Logging)。当事务提交之后，在Buffer Pool中映射的数据文件才会慢慢刷新到磁盘。此时如果数据库崩溃或者宕机，那么当系统重启进行恢复时，就可以根据redo log中记录的日志，把数据库恢复到崩溃前的一个状态。未完成的事务，可以继续提交，也可以选择回滚，这基于恢复的策略而定。

在系统启动的时候，就已经为redo log分配了一块连续的存储空间，以顺序追加的方式记录Redo Log，通过顺序IO来改善性能。所有的事务共享redo log的存储空间，它们的Redo Log按语句的执行顺序，依次交替的记录在一起。

2、undo log（回滚日志） 实现一致性
undo log 主要为事务的回滚服务。在事务执行的过程中，除了记录redo log，还会记录一定量的undo log。undo log记录了数据在每个操作前的状态，如果事务执行过程中需要回滚，就可以根据undo log进行回滚操作。单个事务的回滚，只会回滚当前事务做的操作，并不会影响到其他的事务做的操作。

Undo记录的是已部分完成并且写入硬盘的未完成的事务，默认情况下回滚日志是记录下表空间中的（共享表空间或者独享表空间）

二种日志均可以视为一种恢复操作，redo_log是恢复提交事务修改的页操作，而undo_log是回滚行记录到特定版本。二者记录的内容也不同，redo_log是物理日志，记录页的物理修改操作，而undo_log是逻辑日志，根据每行记录进行记录。

MySQL日志总结：
错误日志：记录出错信息，也记录一些警告信息或者正确的信息。
查询日志：记录所有对数据库请求的信息，不论这些请求是否得到了正确的执行。
慢查询日志：设置一个阈值，将运行时间超过该值的所有SQL语句都记录到慢查询的日志文件中。
二进制日志：记录对数据库执行更改的所有操作。
中继日志：中继日志也是二进制日志，用来给slave 库恢复。
事务日志：重做日志redo和回滚日志undo。

3.7、事务的使用

  1. 开启事务：start transaction
  2. 提交事务：commit（所有语句全部执行完毕，未发生异常，提交事务，更新到数据库当中。）
  3. 回滚事务：rollback（当遇到一突发情况，撤销执行的sql语句）
    执行流程：
    1
    2
    3
    4
    5
    start transaction
    update student set age = age + 10;
    update student set gread = gread + 4;
    rollback # 若执行rollback，则会跳回到 start transaction
    commit
赏

失败是成功之母

支付宝
微信

扫一扫，分享到微信

微信分享二维码

tag:

    缺失模块。
    1、请确保node版本大于6.2
    2、在博客根目录（注意不是yilia根目录）执行以下命令：
    npm i hexo-generator-json-content --save

    3、在根目录_config.yml里添加配置： 

      jsonContent:
    meta: false
    pages: false
    posts:
      title: true
      date: true
      path: true
      text: false
      raw: false
      content: false
      slug: false
      updated: false
      comments: false
      link: false
      permalink: false
      excerpt: false
      categories: false
      tags: true

JAVA<br><br>学习永不止步<br>简简单单