MySQL索引和锁

part 01：MySQL索引

SQL的执行顺序：from---where--group by---having---select---order by

 

1、MySQL存储引擎（表级别的）

• InnoDB（聚集索引）：支持事务；面向在线事务处理（OLTP）；特点是行锁设计，支持外键，并支持非锁定锁（默认读取操作不会产生锁）。MySQL5.5.8开始，InnoDB是默认的存储引擎。数据文件本身就是索引文件；主键索引叶子节点存储整行数据，非主键索引叶子节点存储主键（考虑数据一致性问题，节省存储空间）
• MyISAM（非聚集索引）：不支持事务、表锁设计，支持全文索引；面向联机分析处理（OLAP）；索引文件和数据文件是分开的；会保存表的行数；适用于读多写少，大量查询的情况

 

2、为什么选择B+树作为索引结构

• Hash索引：在存储关系上没有任何顺序关系，对应区间查询无法直接通过索引查询，需要全表扫描。
• 二叉查找树：解决了排序的基本问题，但是由于无法保证平衡，可能退化为链表，高度过高
• 平衡二叉树：通过旋转解决了平衡问题，但是旋转操作效率太低
• 红黑树：解决了旋转效率过低的问题，但在数据量大时，树的高度还是太高，IO次数太多
• B+树：在B树的基础上，将非叶子节点改造为不存储数据纯索引节点，一个节点可以存储多个数据（可以增大度），进一步降低了树的高度（一般度会超过100，高度为3-5之间）；此外将叶子节点使用指针连接成链表，有序，范围查询更加高效；并不是度越大越好，IO一次交互只能交换一页（4K）的大小，度最好能一次IO查找完

 

3、B+树的叶子节点可以存储哪些东西

• 主键索引叶子节点存储整行数据，非主键索引叶子节点存储主键

 

4、查询在什么时候不走预期的索引

• 非最左前缀原理，查询不从联合索引的最左前列开始或者跳过索引中的列
• 在索引列做操作（参与计算、使用函数或类型转换）
• 在中间索引列用到了范围（>、<、like等），则后面的索引全失效，不包括本身，当最左边的索引列用到了范围，则索引全失效，包括本身
• like左模糊查询会导致索引失效（可以使用覆盖索引，查询的都是索引）
• 使用不等于（!= 、<>）
• 使用is null 和 is not null
• 字符串不加引号
• 使用or操作中有字段没有索引

#优化索引口诀

全值匹配我最爱，最左前缀要遵守

带头大哥不能丢，中间兄弟不能断

索引列上少计算，范围之后全失效

like百分写最右， 覆盖索引不写星

不等空值还有or，索引失效要少用

var引号不能丢，SQL高级也不难

 

5、explain命令字段

• select_type：表示查询的类型
  • SIMPLE(简单SELECT，不使用UNION或子查询等)
  • PRIMARY(子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的select被标记为PRIMARY)
  • UNION(UNION中的第二个或后面的SELECT语句)
  • DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)
  • UNION RESULT(UNION的结果，union语句中第二个select开始后面所有select)
  • SUBQUERY(子查询中的第一个SELECT，结果不依赖于外部查询)
  • DEPENDENT SUBQUERY(子查询中的第一个SELECT，依赖于外部查询)
  • DERIVED(派生表的SELECT, FROM子句的子查询)
  • UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)
• table：输出结果集的表
• possible_keys:表示查询时，可能使用的索引
• key:表示实际使用的索引
• key_len：索引字段的长度

• type：表示表的连接类型
  • system：最快，主键或唯一索引查找常量值，只有一条记录，很少能出现
  • const：查询的结果集只有一条数据（用主键索引或者唯一索引进行等值查询）
  • eq_ref：PK或者unique上的join查询，等值匹配，对于前表的每一行(row)，后表只有一行命中
  • ref：非唯一索引，等值匹配，可能有多行命中
  • range：索引上的范围扫描，例如：between/in
  • index：索引上的全集扫描，例如：InnoDB的count，查询的记录都为索引
  • ALL：最慢，全表扫描(full table scan)

6、数据库优化指南

• 创建并使用正确的索引
• 只返回需要的字段
• 较少交互次数
• 设置合理的Fetch Size(数据每次返回给客户端的条数)

 

part 02：MySQL事务

1、事务的基本要素

• 原子性：强调事务的不可分割，多条语句要么都成功，要么都失败
• 一致性：强调的是事务执行的前后，数据要保持一致
• 隔离性：一个事务的执行不应该受到其他事务的干扰
• 持久性：事务一旦结束（提交／回滚），数据就持久保持到了数据库

 

2、事务的并发问题

• 脏读：一个事务读到另一个事务还没有提交的数据
• 不可重复读：一个事务读到另一个事务已经提交的update的数据，导致在当前的事务中多次查询结果不一致；
• 虚读／幻读：一个事务读到另一个已经提交的insert的数据，导致当前的事务中多次的查询结果不一致；

 

3、如何解决事务的并发问题(脏读，幻读)，设置事务的隔离级别

• 1 read uncommitted :未提交读，以上三种安全问题都有可能发生
• 2 read committed：已提交读，可以避免脏读，但是其他两种问题都有可能发生（Oracle默认）
• 4 repeatable read：可重复读，可以避免脏读和不可重复读的问题，但是虚读有可能发生（MySQL默认）；
• 8 serializable：串行化的，可以避免以上三种问题的发生

      注意⚠️：级别越高，越安全，效率越低

 

 

4、MVCC（多版本并发控制）：

• 可重复读的隔离级别下使用了mvcc机制，select操作会读取历史版本，不会读取其他事物已提交的更新操作；insert、update和delete操作的是当前版本，会在其他事物已提交的操作基础上进行修改
• MVCC多版本并发控制是MySQL中基于乐观锁理论实现隔离级别的方式，用于读已提交和可重复读取隔离级别的实现。在MySQL中，会在表中每一条数据后面添加两个字段：最近修改该行数据的事务ID，指向该行（undolog表中）回滚段的指针。Read View判断行的可见性，创建一个新事务时，copy一份当前系统中的活跃事务列表。意思是，当前不应该被本事务看到的其他事务id列表。已提交读隔离级别下的事务在每次查询的开始都会生成一个独立的ReadView,而可重复读隔离级别则在第一次读的时候生成一个ReadView，之后的读都复用之前的ReadView。

 

5、undoLog、redoLog、binLog

• undoLog：回滚日志文件，主要用于事务中执行失败，进行回滚，以及MVCC中对于数据历史版本的查看。 当一条数据需要更新前 ,会先把修改前的记录存储在undolog中,如果这个修改出现异常,,则会使用undoLog来实现回滚操作,保证事务的一致性。 当事务提交之后， undoLog并不能立马被删除,而是会被放到待清理链表中,待判断没有事物用到该版本的信息时才可以清理相应undolog
• redoLog：重做日志文件，记录数据修改之后的值，用于持久化到磁盘中。 当一条数据需要更新时 ,InnoDB会先将数据更新，然后记录redoLog 在内存中，然后找个时间将redoLog的操作执行到磁盘上的文件上。不管是否提交成功我都记录，你要是回滚了，那我连回滚的修改也记录。它确保了事务的持久性。
• binLog： 逻辑日志，其记录是对应的SQL语句，对应的事务 。而innodb存储引擎层面的 redoLog 是物理日志，是关于每个页（Page）的更改的物理情况。 binlog日志只在事务提交完成后进行一次写入

binlog不是循环使用，在写满或者重启之后，会生成新的binlog文件，redolog是循环使用。

binlog可以作为恢复数据使用，主从复制搭建，redolog作为异常宕机或者介质故障后的数据恢复使用。

 

5、锁的分类：

• 从性能上分
  • 乐观锁（在提交前，进行版本对比，发现冲突了，返回错误信息）：适用于读多写少的场景
  • 悲观锁
    • 共享锁（读锁、S锁）：多个事务都能访问到同一数据，但是只能读不能修改。
      • 表锁：lock tables 表名 read
      • 行锁：select ..... lock in share mode
    • 排它锁（写锁、X锁）：一个事务对数据加上排它锁，其他事务不能读不能写， insert ，delete ， update在事务中都会自动默认加上排它锁。
      • 表锁： lock tables 表名 write
      • 行锁：select.....for update
• 从对数据操作的粒度分
  • 表锁：偏向于MyISAM存储引擎，开销小，加锁快，发生锁冲突分概率最高，并发度最低
  • 行锁：InnoDB支持行锁，是针对索引加的锁， 不是针对记录加的锁，并且该索引不能失效，否则都会从行锁升级为表锁。会出现死锁，发生锁冲突几率低，并发度高

6、InnoDB有三种行锁的算法：

1，Record Lock ：单个行记录上的锁。

2，Gap Lock ：间隙锁，锁定一个范围，但不包括记录本身。GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况。

锁定范围3-8，当插入7时等待，插入10 时可以成功

3，Next-Key Lock ：1+2，锁定一个范围，并且锁定记录本身。对于行的查询，都是采用该方法，主要目的是解决幻读的问题。

 

InnoDB 采用 Next-Key Lock 解决幻读问题。在insert into test(xid) values (1), (3), (5), (8), (11);后，由于xid上是有索引的，该算法总是会去锁住索引记录。现在，该索引可能被锁住的范围如下：(-∞, 1], (1, 3], (3, 5], (5, 8], (8, 11], (11, +∞)。Session A（select * from test where id = 8 for update）执行后会锁住的范围：(5, 8], (8, 11]。除了锁住8所在的范围，还会锁住下一个范围，所谓Next-Key。