您的位置: 首页  >  文章  >  MySQL知识拾遗

MySQL知识拾遗

分类: 文章 • 2024-08-31 18:07:04

索引

覆盖索引

覆盖索引（covering index）指一个查询语句的执行只用从索引中就能够取得，不必从数据表中读取。也可以称之为实现了索引覆盖。 当一条查询语句符合覆盖索引条件时，MySQL只需要通过索引就可以返回查询所需要的数据，这样避免了查到索引后再返回表操作，减少I/O提高效率。 如，表covering_index_sample中有一个普通索引 idx_key1_key2(key1,key2)。当我们通过SQL语句：select key2 from covering_index_sample where key1 = ‘keytest’;的时候，就可以通过覆盖索引查询，无需回表。

查询优化器

一条SQL语句的查询，可以有不同的执行方案，至于最终选择哪种方案，需要通过优化器进行选择，选择执行成本最低的方案。 在一条单表查询语句真正执行之前，MySQL的查询优化器会找出执行该语句所有可能使用的方案，对比之后找出成本最低的方案。这个成本最低的方案就是所谓的执行计划。 优化过程大致如下： 1、根据搜索条件，找出所有可能使用的索引 2、计算全表扫描的代价 3、计算使用不同索引执行查询的代价 4、对比各种执行方案的代价，找出成本最低的那一个

索引下推

索引下推：Index Condition Pushdown

MySQL 5.6引入了索引下推优化，默认开启，使用SET optimizer_switch = ‘index_condition_pushdown=off’;可以将其关闭。官方文档中给的例子和解释如下： people表中（zipcode，lastname，firstname）构成一个索引

SELECT * FROM people WHERE zipcode=‘95054’ AND lastname LIKE ‘%etrunia%’ AND address LIKE ‘%Main Street%’;

如果没有使用索引下推技术，则MySQL会通过zipcode='95054’从存储引擎中查询对应的数据，返回到MySQL服务端，然后MySQL服务端基于lastname LIKE '%etrunia%'和address LIKE '%Main Street%'来判断数据是否符合条件。 如果使用了索引下推技术，则MYSQL首先会返回符合zipcode='95054’的索引，然后根据lastname LIKE '%etrunia%'和address LIKE '%Main Street%'来判断索引是否符合条件。如果符合条件，则根据该索引来定位对应的数据，如果不符合，则直接reject掉。 有了索引下推优化，可以在有like条件查询的情况下，减少回表次数。

查询条件存在范围条件

范围条件有：<、<=、>、>=、between等。范围列可以用到索引（联合索引必须是最左前缀）， 但是范围列后面的列无法用到索引，索引最多用于一个范围列，如果查询条件中有两个范围列则无法全用到索引。使用in关键字时索引列不受影响，因为属于多值匹配。

聚集（簇）索引的优势

看上去聚簇索引的效率明显要低于非聚簇索引，因为每次使用辅助索引检索都要经过两次B+树查找，那 么聚簇索引的优势在哪？

（1） 由于行数据和叶子节点存储在一起，这样主键和行数据是一起被载入内存的，找到叶子节点就可以 立刻将行数据返回了，如果按照主键Id来组织数据，获得数据更快。

（2）辅助索引使用主键作为"指针" 而不是使用行地址值作为指针的好处是，减少了当出现行移动或者 数据页分裂时辅助索引的维护工作，使用主键值当作指针会让辅助索引占用更多的空间，换来的好 处是InnoDB在移动行时无须更新辅助索引中的这个"指针"，使用聚簇索引可以保证不管这个主键 B+树的节点如何变化，辅助索引树都不受影响。

事务

事务是一组原子性的执行语句，要么全部执行成功，要么全部执行失败。

MVCC

对于使用InnoDB存储引擎的表来说，它的聚簇索引记录中都包含两个必要的隐藏列（row_id并不是必要的，我们创建的表中有主键或者非NULL唯一键时都不会包含row_id列）：

  • trx_id：每次对某条聚簇索引记录进行改动时，都会把对应的事务id赋值给trx_id隐藏列。
  • roll_pointer：每次对某条聚簇索引记录进行改动时，都会把旧的版本写入到undo日志中，然后这个隐藏列就相当于一个指针，可以通过它来找到该记录修改前的信息。

版本链

每次对记录进行改动，都会记录一条undo日志，每条undo日志也都有一个roll_pointer属性（INSERT操作对应的undo日志没有该属性，因为该记录并没有更早的版本），可以将这些undo日志都连起来，串成一个链表，所以现在的情况就像下图一样：
MySQL知识拾遗

小结

所谓的MVCC（Multi-Version Concurrency Control ，多版本并发控制）指的就是在使用READ COMMITTED、REPEATABLE READ这两种隔离级别的事务在执行普通的SELECT操作时访问记录的版本链的过程，这样子可以使不同事务的读-写、写-读操作并发执行，从而提升系统性能。

对于使用READ UNCOMMITTED隔离级别的事务来说，直接读取记录的最新版本就好了，对于使用SERIALIZABLE隔离级别的事务来说，使用加锁的方式来访问记录。

READ COMMITTED、REPEATABLE READ这两个隔离级别的一个很大不同就是生成ReadView的时机不同，READ COMMITTED在每一次进行普通SELECT操作前都会生成一个ReadView，而REPEATABLE READ只在第一次进行普通SELECT操作前生成一个ReadView，之后的查询操作都重复这个ReadView就好了。

锁

行锁

InnoDB行锁是基于索引实现的，行锁锁定的是索引，而不是记录本身。

一般常说的共享锁与排他锁都是行锁，只有被锁定时的where条件没有对应索引时，它们才是表锁。

间隙锁

间隙锁针对的是区间，是InnoDB引擎在 RR或串行化 事务级别主动加的。

产生场景（范围查询）：

For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), UPDATE, and DELETE statements, locking depends on whether the statement uses a unique index with a unique search condition, or a range-type search condition.

  • For a unique index with a unique search condition, InnoDB locks only the index record found, not the gap before it.
  • For other search conditions, InnoDB locks the index range scanned, using gap locks or next-key locks to block insertions by other sessions into the gaps covered by the range. For information about gap locks and next-key locks.

1.用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB不仅会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁
2.使用相等条件请求给一个不存在的记录加锁，InnoDB也会使用间隙锁

小结

  1. MySQL 执行 select…lock in share mode 语句时加读锁，执行 update、insert、delete、select…for update时加写锁，使用innoDB情况下，读锁和写锁一般都是行锁，只有被锁定时的where条件没有对应索引时，才会锁表。

  2. innoDB使用RR或串行化时，会使用间隙锁。

    使用RR时，不管加共享锁还是排他锁，如果满足（1）范围查询唯一索引（2）查询普通索引，都会加上间隙锁；

    使用Serializable时，隐式地将所有select语句转化为select…lock in share mode，所有执行语句都会加上间隙锁。

隔离级别

事务的隔离级别分为：读未提交、读已提交、可重复读、串行化

读未提交

当前事务可以读到其他事务未提交的数据，如果其他事务最后没有提交，那读取到的数据就是无效的数据，因此读未提交存在脏读的问题。

读已提交

当前事务只能读到其他事务已提交的数据，解决了脏读的问题，但是又引出了一个新的问题–不可重复读。不可重复读在官网称之为幻影行，即同一事务前后两次相同查询结果不同。

The so-called phantom problem occurs within a transaction when the same query produces different sets of rows at different times. For example, if a SELECT is executed twice, but returns a row the second time that was not returned the first time, the row is a “phantom” row.

读已提交还可能会导致死锁的问题：例如事务A、B各插入一条数据后，事务A查询数据，事务A想通过lock in share mode加共享锁，发现有一行记录加了排他锁（事务B新增的数据），所以事务A的查询会阻塞， 事务B再执行加锁查询语句时，出现死锁。

死锁的出现有多种情况，但原理都是一样的，即某一行记录被事务X锁定，事务M尝试对该行加锁，因此事务M阻塞，事务X又尝试获取事务M持有的锁，所以陷入了彼此等待锁的场景，即是死锁。

可重复读

针对常规SELECT，采用快照读的方式：第一次读时建立快照，之后无论其他事务是否新增/删除了记录，读取的永远都是那个快照对应的数据，可能会存在 幻读 的问题。这里对幻读的理解和网上大部分的理解不一样，大部分博客说的是其他事务执行插入后，当前事务插入记录时发现存在主键冲突，实际上幻读还应包括 (1) 其他事务删除了某条记录后提交，由于隔离级别为RR，当前事务仍能读到该记录，然而执行删除时却发现删除不成功 (2) 其他事务修改了某条记录后提交，比如set number = 6，当前事务第一次select读到number=5，执行相同update语句的话，会发现无法修改成功。

为了避免幻读，事务执行SELECT时，可以选择加锁SELECT，当读取数据记录时，除了锁住记录本身（Record Lock），同时将符合条件的间隙锁起来（Gap Lock），预防第一次读和第二次读的期间，其他事务往间隙插入新数据、删除区间内记录、修改区间内记录。

串行化

当autocommit禁用的时候，InnoDB隐式地将所有SELECT语句转化为SELECT…LOCK IN SHARE MODE；如果启用autocommit的话，一个SELECT语句就是一个事务。

其实串行化解决幻读的方法也是通过加入间隙锁。

小结

MySQL默认的隔离级别是：Repeatable read。

一般地，实际项目中不会考虑Read uncommited 和 Serializable这两种隔离级别。原因为：

  • Read uncommited导致出现脏读。
  • Serializable对所有查询语句加锁，性能较差

使用RR作为默认级别有一个原因是解决了RC在使用statement方式进行主从同步存在bug，详见 日志-binlog

日志

redo log

确保事务的持久性。

防止在发生故障的时间点，尚有脏页未写入磁盘，在重启mysql服务的时候，根据redo log进行重做，从而达到事务的持久性这一特性。

物理格式的日志，记录的是物理数据页面的修改的信息，其redo log是顺序写入redo log file的物理文件中去的

undo log

保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读。

逻辑格式的日志，在执行undo的时候，仅仅是将数据从逻辑上恢复至事务之前的状态，而不是从物理页面上操作实现的，这一点是不同于redo log的。

binlog

用于复制，在主从复制中，从库利用主库上的binlog进行重播，实现主从同步；用于数据库的基于时间点的还原。

逻辑格式的日志，可以简单认为就是执行过的事务中的sql语句。但又不完全是sql语句这么简单，而是包括了执行的sql语句（增删改）反向的信息，也就意味着delete对应着delete本身和其反向的insert；update对应着update执行前后的版本的信息；insert对应着delete和insert本身的信息。

RC隔离级别binlog的bug

MySQL的binlog用来记录数据的更改，用于主从同步，有以下几种格式：

  • statement：记录的是修改SQL语句
  • row：记录的每行实际数据的变更
  • mixed：statement与row的混合

在RC隔离级别下使用statement方式存在主从同步的bug。

如下表，事务执行顺序是：A开始、B开始、B提交、A提交。Binlog要求SQL语句串行化，顺序以commit为序，两会话的执行时序是 B开始、B提交、A开始、A提交，这就是slave上的执行顺序。

在RC隔离级别下，事务A可以读取到事务B已提交的数据。所以在master上，事务A先开始，先删后插，读取到一条记录，在slave上，先插后删，读取到nothing。出现了主从数据不一致的问题。

在RR隔离级别下，事务A进行删除操作后，Mysql会加一把test_id在0~200的间隙锁，所以事务B的插入会阻塞，最后还是A先提交，因此master上与slave的执行顺序均是：A开始、A结束、B开始、B结束。

时间 事务A 事务B
1 set transaction_isolation=‘read-committed’;
2 begin;
3 delete from test_tab where test_id < 200;
4 begin;
5 insert into test_tbl (test_title, test_author) values (“test3”, “txB”);
6 commit;
7 commit;

redo log和binlog对比

redo log：当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写入redo log里面，并更新内存，这个时候更新就算完完成了。同时，innodb引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做的。redo log是固定大小的，从前往后写，写完后会继续从开头开始写，把以前的内容覆盖。有了redo log，innodb就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为crash-safe。

binlog：二进制日志文件，用于记录mysql的数据更新或则潜在更新，mysql的主从复制就是依靠binlog。

区别：

1、redo log是innodb引擎特有的，binlog是mysql的server层实现的所有引擎都可以使用。

2、redo log是物理日志：记录的是：“在某个数据页上做了什么修改”；

​ binlog是逻辑日志，记录的是这个语句的原始逻辑，比如：“给id=2 的这一行的c字段加1”。

3、redo log是循环写的，空间会用完，binlog是可以追加写的。

4、假设现在是一个更新操作，现在是内部流程：

①：执行器先找到引擎id=2这一行。id是主键，引擎直接找到这一行，如果id=2这一行所在的数据也本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。

②：执行器拿到引擎的行数据，把这个值加上1，比如原来是N，现在就是N+1，得到新的一行数据，再调用引擎接口写入这行新数据。

③：引擎将这行新数据更新到内存中，同时将这个更新操作记录到redo log里面，此时redo log处于prepare状态。然后告知执行器执行完成了，随时可以提交事务。

④：执行器生成这个操作的binlog，并把binlog写入磁盘。

⑤：执行器调用引擎的提交事务接口，引擎把刚刚写入的redo log改成提交(commit)状态，更新完成。

范式

  • 第一范式: 属性不可再分.
  • 第二范式: 在一范式的基础上, 要求数据库表中的每个实例或行必须可以被惟一地区分. 通常需要为表加上一个列, 以存储各个实例的惟一标识. 这个惟一属性列被称为主关键字或主键.
  • 第三范式: 在二范式的基础上, 要求一个数据库表中不包含已在其它表中已包含的非主关键字信息. 所以第三范式具有如下特征：1). 每一列只有一个值. 2). 每一行都能区分. 3). 每一个表都不包含其他表已经包含的非主关键字信息.

参考链接

https://blog.****.net/weixin_44922510/article/details/103472407

https://juejin.im/post/5c9b1b7df265da60e21c0b57

https://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-isolation-levels.html

相关推荐