《MySQL实战45讲》学习笔记_mysql扫描行数

1.Mysql查询一条sql的执行过程：

• 首先连接器负责连接到指定的数据库上，接着看看查询缓存中是否有这条语句，如果有就直接返回结果。
• 如果缓存没有命中的话，就需要分析器来对SQL语句进行语法和词法分析，判断SQL语句是否合法。
• 现在来到优化器，就会选择使用什么索引比较合理，SQL语句具体怎么执行的方案就确定下来了。
• 最后执行器负责执行语句、有无权限进行查询，返回执行结果。
  从上面的简单测试结果其实可以看到，索引列存在NULL就会存在书中所说的导致优化器在做索引选择的时候更复杂，更加难以优化。

2.Mysql更新操作

• 当需要更新一个数据页时，如果数据页在内存中（buffer）中就直接更新，如果数据没在内存中，InnoDB会将这些更新操作缓存在change buffer中，就不需要从磁盘中读入这个数据页，在下次查询访问到该数据页时，再执行相关操作。change buffer只限于用于普通索引的场景下，而不适用于唯一索性。
• 对于读多写少的业务，changge buffer使用效果最好，例如：账单类、日志类系统。假如对于写完之后马上要查询的系统，不适合使用change buffer。
• 普通索引和唯一索引，查询性能上几乎无差别，主要是对更新性能的影响
  如果更新后面马上伴随着查询，应该关闭 change buffer，而在其他情况下 change buffer 能提升性能
• 更新过程图例：

update t set xx=? where id=?
1

• 查询图例：

select * from t where id=?
1

• 查询过程：redo log 主要节省的是随机写磁盘的 IO 消耗（转成顺序写），而 change buffer 主要节省的则是随机读磁盘的 IO 消耗。

3.redo log和binlog

• 首先，Mysql大体上可以分为Server层和存储引擎层两部分。其中Server层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖MySQL的大多数核心服务功能。而存储引擎层负责数据的存储和提取，起架构模式是插件式的，支持InnoDB、MyISAM、Memory等多个存储引擎。MySQL5.5.5版本默认试用InnoDB存储引擎了。
• 那么当执行update table set x=x+1 where id=?时，相关记录的日志是如何操作的呢？
  • 1.首先更新流程根查询流程一样，也是会经过连接器、查询缓存、分析器、优化器、执行器等流程。与查询流程不一样的是，更新流程还设计两个重要的日志模块，它们分别是：redo log（重做日志）、binlog（归档日志）。
  • 2.mysql更新的时候，InnoDB引擎会先把记录写到redo log（粉板-孔乙己酒馆老板记账）里，并更新内存，这个时候更新就算完成了。同时，InnoDB引擎会在适当的时候（系统比较空闲的时候）将这个操作记录到磁盘里面。
  • 3.redo log的大小是固定的，比如可配置4组（1GB），那么这块“粉板”总共可以记录4GB的操作，从头开始写，写到末尾又回到开头循环写，如下：
  • 
  • 其中，write pos是当前记录的位置，一边写一边后移，写到第3号文件末尾后就回到0号文件开头。check point是当前要擦除的位置，也是往后推移并且循环的，擦除记录当前要把记录跟到数据文件。其中write pos和check point之间“粉板”的位置表示还可以用来记录新的操作，如果满了，这个时候不能再执行新的更新，得停下来先擦掉一些记录，把check point推进一下。
  • 4.有了redo log，InnoDB就可以保证及时数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为crash-safe。
  • 5.重要的日志模块：binlog。上面提到Mysql主要分为两层：Server和存储引擎层，前面我们提到的“粉板”是InnoDB存储引擎特有的日子，而Server层也有自己的日子，即binlog（归档日志）。
  • 6.为什么Mysql会有两份日志呢？Mysql自带的引擎是MyISAM，但是MyISAM没有crash-safe能力，binlog日志只能用于归档。而InnoDB是另一个公司以插件形式引入MYSQL的，既然只能依靠binlog是没有crash-safe能力的，所以InnoDB试用另外一套日志系统redo log来实现crash-safe能力。
  • 7.不同点：
    • 1.redo log是InnoDB引擎特有的，binlog是MYSQL的Server层实现的，所有引擎都可以试用。
    • 2.redo log是物理日志，记录的是“在某个数据页上做了什么修改”；binlog是逻辑日志，记录的是这个语句的原始记录，比如“给ID=2的这一行c字段加1”。
    • 3.redo log是循环写，空间固定会用完；binlog是可以最追加写入的（binlog文件写到一定大小会切换到下一个，并不会覆盖以前的日志）。
  • 8.更新操作的日志流程：
    
  • ps:redo log的写入拆成了两个步骤：prepare和commit，即“两阶段”提交，两阶段提交是为了两份日志之间的逻辑一致。

4.事务

• 谈到事务就离不开事务的各种隔离级别，当数据库上有多个事务同时执行的时候，就可能出现脏读（Dirty Read）、不可重复读（NonRepeatable Read）、幻读（Phantom Read）的问题，为了解决这些问题，就有了“隔离级别”的概念。
• 在谈事务的隔离级别之前，你首先要知道，你隔离的越严实，效率就越低。具体的隔离级别如下：
  • 1.读未提交（Read Uncommitted）：一个事务还没提交时，它做的变更就能被别的食物看到。
  • 2.读提交（Read Committed）:一个事物提交之后，它做的变更才会被其他事务看到。
  • 3.可重复读（Repeatable Read）：一个事务执行构成中看到的数据，总是跟这个食物在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他食物也是不可见的。
  • 4.串行话（Serializable）:对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。
• 为了理解上面的隔离级别，举下面sql的例子：

  create table T(c int) engine=InnoDB;
  insert into T(c) values(1);
  1
  2

  • 1.分别开启事务A和事务B，具体时序图如下：
    
  • 2.读未提交：V1=>2,这个时候事务B虽然还没提交，但是结果已经被A看到了，因此V2，V3均为2.
  • 3.读提交：V1=>1，V2=>2。事务B的更新在提交后才能被A看到，所以V3也是2.
  • 4.可重复度：V1、V2为1，V3为2。之所以V2还是1，遵循的就是这个要求：事务在执行期间看到的数据前后必须时一致的。
  • 5.串行化：则在事务B把1改成2的时候，会被锁住。知道事务A提交后，事务B才可以继续执行。所以从A的角度抗，V1、V2为1，V3为2.
• 在实现上，数据库里面会创建一个视图（Read View）,访问的时候以视图的逻辑结果为准。在“可重复读”隔离界别下，这个视图在事务启动时创建的，整个事务存在期间都用这个视图。在“读提交”隔离级别下，这个视图是在每个SQL语句开始执行的时候创建的。这里需要注意，“读未提交”隔离级别下直接返回记录上的最新值，并没有视图的概念；而“串行化”隔离级别下直接用加锁的方式来避免并行访问。
• 查看数据库隔离级别命令：

mysql>show variables like 'transaction_isolation';
1

• 事务隔离的实现，理解了事务的隔离级别，我们再来看看事务的隔离具体是怎么实现的（以可重复读为例）：
  • 1.在MYSQL中，实际上每条记录在更新的时候都会记录一条回滚操作（回滚日志 undo log）。记录上的最新值，通过回滚操作，都可以得到一个状态的值。
  • 2.假设一个值从1被按顺序改成2、3、4，在回滚日志里面就会有类似下面的记录：
    
  • 当前值是4，但是查询这条记录的时候，不同时刻启动的事务会有不同的read view，如图中看到的，就是数据库的多版本控制MVCC。对于read-view A，要得到1，就必须将当前的值依次执行图中所有的回滚操作得到。同时，即使现在有另外一个事务正在将4改成5，这个事务跟read viewA、B、C对应的事务也是不冲突的。
  • 3.回滚日志不能一直保留，在不需要的时候才删除，也就是说，系统会判断，当没有事务再需要用到这个回滚日志，回滚日志就会删除（即当系统里面没有比这个回滚日志更早的read view的时候）。
  • 4.建议尽量不要使用长事务，长事务意味着系统里面会存在很老的事务视图，由于这些事务随时可能访问系统里面的任何数据，所以这个事务提交之前，数据库里面它可能用到的回滚记录都必须要保留，这就会导致大量的占用存储空间。除了对回滚段的影响，长事务还占用锁资源，也可能拖垮整个库。
• 事务的启动方式
  • 1.显示启动事务：begin 或 start transaction。配套的提交commit或回滚rollback。
  • 2.关闭自动提交配置，set autocommit=0，这个命令会将这个线程的自动提交关闭，意味着如果你执行一个select语句，这个事务就启动了，而且并不会自动提交，这个事务持续存在直达你主动执行commit或rollback语句，或者断开连接（建议总是使用set autocommit=1来开启自动提交，通过显示语句的方式来启动事务）。

5.索引

• 索引的出现就是为了提高查询效率，但是索引的实现方式有很多种。常见索引模型有以下几种：
  • 1.哈希表：key-value的存储数据结构，但出现重复时，便在未节点新增相关节点，形成链表形式。哈希表的好处：查询等值数据非常快（例如Memcache及一些NoSQL引擎）。缺点：区间查询时速度很慢，因为key不是连续的，查询范围的value需要遍历整个哈希表。
    
  • 2.有序数组：在等值查询和范围查询中的性能非常优秀（需要数组的key时按照递增顺序保存）。缺点：一旦数据的插入，必须挪动相关后面的记录，成本太高。所以有序数组只适用静态存储引擎，即相关数据一旦插入不太会变动的。
    
  • 3.二叉树：二叉树查找复杂度O(logN)，例如：平衡二叉树维持左子树节点的值小于root节点，右子树节点的值大于等于root节点。
• InnoDB的索引模型：InnoDB试用了B+树索引模型，主键索引和非主键索引(二级索引)模型如下：
  。基于主键索引和普通索引的查询有什么区别？
  • 基于非主键索引的查询需要多扫描一颗索引树（即回表查询主键索引）。
• 索引维护
  • B+树为了维护索引的有序性，在插入新的值的时候需要做必要的维护，即数据页中的数据过多或过少的时候就会进行数据页的分裂和合并。
• 关于建表一定要求自增主键的看法？
  • 1.自增主键是指自增列上定义的主键，在建表语句中一般是这么定义的：NOT NULL PRIMARY KEY AUTO_INCREMENT。
  • 2.插入新纪录的时候可以不指定ID的值，系统会获取当前ID最大值增加1作为下一条记录的ID值。
  • 3.随着新纪录的插入，正好符合前面提到的递增插入的场景，每一次插入一条新的记录，都是追加的操作，都不涉及挪动其他的记录，也不会触发叶子节点的分裂。
  • 4.业务逻辑字段做主键，则往往不容易保证有序插入，这样写数据成本相对比较高。
  • 5.存储空间出发：如果表中有一个唯一的字段，比如是字符串的身份证号，那么用身份证号做主键，还是用自增字段做主键？由于每个非主键索引的叶子结点都是主键的值，如果用身份证号做主键，那么每个二级索引的叶子结点占用约20个字节，而如果用整形做主键，则只要4个字节，如果长整形（bigint）则是8个字节。显然，主键长度越小，普通索引的叶子结点就越小，普通索引占用的空间也就越小。所以，从性能和空间方面考虑，自增主键往往是最合理的选择。
  • 6.什么场景适合用业务字段直接做主键呢？比如：
    • 1.只有一个索引。
    • 2.该索引必须是唯一索引。
  • 即典型的kv结构，由于没有其他索引，所以也就不用考虑其他索引的叶子结点大小的问题。直接将这个索引设置为主键，可以避免每次查询需要搜索两棵树。
• 联合索引查询？
  • 1.对于sql：select * from T where k between 3 and 5，需要执行几次树的索引操作，会扫描多少行？

      create table T( ID int primary key,
      k int NOT NULL DEFAULT 0,
      s varchar(16) NOT NULL DEFAULT '',
      index k(k))engine=InnoDB;
  1
  2
  3
  4

  
  • 2.整个查询流程：
    • 1.在k索引树上查找到k=3的记录，ID=300;
    • 2.再到ID索引树查找ID=300对应的R3；
    • 3.在k索引树取下一个值k=5,ID=500;
    • 4.再回到ID索引树查找ID=599对应的R4；
    • 5.在k索引树取下一个值k=6，不满足条件，循环结束。
  • 整个查询过程共回表2次，查询k索引树3条记录。
  • 3.覆盖索引：如果执行sql：select ID from T where k betweent 3 and 5,这时只需要查询ID的值，即ID值已经在k索引树上了，因此不需要回表操作。所以，索引k已经“覆盖了”我们的查询需求。覆盖索引可以减少树的搜索次数，显著的提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。ps：在引擎内部使用覆盖索引在索引k上其实读了3条记录（R3~R5，分别对索引k上的记录项），但是对于MySQL的Server层来说，它就是找到引擎拿到了两条记录，因此MySQL认为扫描行数2。
  • 4.最左前缀原则：对于B+树的索引结构，可以利用索引的“最左前缀”来定位记录，不只是索引的全部定义，只要满足最左前缀，就可以利用索引来加速检索，这个最左前缀可以是联合索引的最左N个字段，也可以是字符串索引的最左M个字符。例如：市民身份认证系统中，我们使用（name，age）这个联合索引来分析（即不需要单独为name建立索引）：
    
  • 其中，联合索引是从左往右依次按照字典序进行排序的。比如要查询姓张的名字：where name like ‘张%’，这时也是可以用上（name，age）这个索引的，查找到第一个符合条件的记录是ID3，然后向后遍历，直到不满足条件为止。
  • 5.最左索引字段顺序：1.如果通过调整顺序，可以减少维护一个索引，那么这个顺序往往是需要优先考虑采用的。2.考虑空间原则，如果name字段比age字段大，那么我们可以创建一个（name、age）的联合索引和一个（age）的单独字段索引。
  • 6.索引下推：如果我们要查询名字第一个是张，且年龄为10的男孩，sql如下：select * from T where name like ‘张%’ and age=10 and ismale=1;根据前缀索引规则，所以只要找到满足第一个条件记录ID=3后，还是要判断其他条件是否满足，根据mysql版本的不同，具体如下：
    • 1.mysql 5.6以前，只能从ID=3的开始一个个回表查询，到主键索引上找到数据行，在对比字段值。如下：
      
    • 2.mysql 5.6之后版本引入了索引下推优化，可以在索引遍历过程中，对索引包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表查询的次数。
      
  • 7.总结：索引的设计需要在满足需求的情况下，尽量少地访问资源是数据库设计的重要原则之一。我们在设计表结构时，也要以减少资源消耗作为目标。

6.全局锁和表锁

• MySQL的锁大致可以氛围全局锁、表级锁和行锁。
  • 全局锁：就是对整个数据库实例加锁。MySQL提供一个加全局读锁的方法，命令是Flush tables with read lock（FTWRL），使用这个命令会使得整个库处于只读状态，之后其他线程以下的语句被阻塞:数据更新语句（增删改）、数据定义语句（建表、修改表结构）和更新类食物的提交语句。
    • 1.全局锁的典型使用场景：全库逻辑备份。即把整个库每个表都select出来存成文本。
    • 2.让整个库处于只读状态，有如下的风险点：
      • 1.如果在主库上备份，那么备份期间都不能执行更新，业务基本上就得停摆。
      • 2.如果在从库上备份，那么备份从库不能执行主库同步过程的binlog，会导致主从延迟。
    • 3.逻辑备份工具是mysqldump，当mysqldump使用参数-single-transaction，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于MVCC的支持，这个过程中数据是可以正常更新的。single-transaction方法只适用于所有表使用事务引擎的库。
  • 表锁：一种是表锁，一种是元数据锁（meta data lock，DML）。表锁的语法是lock tables …read/write。可以使用unlock tables主动释放锁。lock tables语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。对于InnoDB这种支持行锁的引擎，一般不适用lock tables命令来控制并发，毕竟锁住整个表的影响面还是太大。
  • MDL不需要显式使用，在访问一个表的时候会被自动加上。MDL的作用是，保证读写的正确性。MySQL5.5版本中引入了MDL，当对一个表做增删改查操作的时候，加MDL读锁；当要对表做结构变更操作的时候，加MDL写锁。
    • 1.读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。
    • 2.读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。
  • 行锁：行锁实在引擎层由各个引擎自己实现的（MyISAM引擎是不支持行锁的，InnoDB是支持行锁的，这也是MySQL默认使用InnoDB替代的重要原因之一），即：事务A更新了一行记录，而这个时候事务B也需要更新同一行，则必须要等待事务A操作完成后才能进行更新。
    • 1.两阶段锁：例如：事务B的update语句按照如下执行，会有什么影响呢？
      
    • 过程:当事务A执行完两条update语句时（事务A持有两个记录的行锁），直到事务A执行commit之后，事务B才能继续执行。
    • 结论：在InnoDB事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放，这个就是两阶段锁协议。
    • 2.减少行锁的影响面？如果你的事务需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。举个例子：顾客A在影院B购买一张电影票，涉及的表操作如下：
      • 1.从顾客A账户余额中扣除电影票价。
      • 2.影院B的账户余额增加这张电影票价。
      • 3.记录一条交易日志。
    • 我们将3个步骤的操作放在一个事务中，那么如何安排执行顺序呢？比如：同时，有个顾客C也在影院B买票，那么这两个顾客的事务操作共同操作的就是步骤2了，因为他们需要更新同一个影院的账户的余额，需要修改同一行数据。
    • 根据1的两阶段锁，所有操作需要的行锁都是在事务提交时候才释放的。所以，把步骤2放在最后（例如：步骤3，1，2），那么步骤2的行锁时间就最少，这就最大程度的减少了事务之间的锁等待，提升并发度。
    • 3.死锁和死锁检测？在并发系统中，多个线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态。例如如下:
      
    • 死锁解除的策略：1.设置等待超时时间：innodb_lock_wait_timeout来设置。2.死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务可以继续执行。innodb_deadlocak_detect=on来表示开启死锁检测（默认50s）。
    • 假设有1000个并发线程要同时更新同一行，那么死锁检测操作就是100万的量级，很有可能消耗大量CPU资源，那么我们怎么解决这种热点行更新的性能问题呢？
      • 1.头疼医头：如果能确保业务一定不会发生死锁，可以临时把死锁检测关掉off。
      • 2.控制并发度：比如同一行同时最多有10个线程在更新，那么死锁检测成本很低，就不会出现这个问题。即可在中间件实现，也可以在MYSQL实现。即：对于相同行的更新，在进入引擎之前排队，这样InnoDB内部就不会有大量的死锁检测工作了。
      • 3.举例：还是以影院账号为例，可以考虑影院余额放在多个记录上，比如10个记录，即影院的账户总额等于10个记录的总和。这样每次要给影院账户加金额的时候，随机选择其中一条记录来加，这样冲突概率变成了原来的1/10，可以减少锁等待的个数，也减少了死锁检测的CPU消耗。

7.事务到底是隔离还是不隔离？

• 1.如果是可重复读隔离级别下，事务T启动时创建一个视图read-view，之后事务T执行期间，即使有其他事务修改了数据，事务T看到的仍然跟在启动时看到的一样（即：在可重复隔离级别下执行的事务，好像与世无争，不受外界影响）。
• 2.在6中，我们讨论过行锁的概念，一个事务要更新一行，如果刚好有另外一个事务拥有这一行的行锁，它又不能这么超然了，被锁住了，进入等待状态。那么，既然进入了等待状态，那么等待这个事务自己获取行锁更新数据的时候，它读到的值又是什么？例如执行如下SQL和事务执行顺序。

  CREATE TABLE `t`(
      `id` int(11) NOT NULL,
      `k` int(111) DEFAULT NULL,
      PRIMARY KEY(`id`)
  )ENGINE=InnoDB;
  insert into t(id,k) values(1,1),(2,2);
  1
  2
  3
  4
  5
  6

  id	k
  1	1
  2	2

  • 事务执行顺序
    
  • 其中，begin/start transaction命令并不是一个事务的起点，在执行到他们之后的第一个操作InnoDB表的语句，事务才真正启动,这里为了做演示，我们默认autocommit=1，自动事务提交。如果想马上启动一个事务，可以试用start transaction with consistent snapshot这个命令。
  • 其中事务B查询到的k=3，而事务A查到的k=1（请注意事务开启了start transaction with consitent snapshot这个马上启动事务的视图）。
  • 关于视图，MySQL有如下概念：
    • 1.view:它是用来查询语句定义的虚拟表，在调用的时候执行查询语句并生成结果。创建视图的语句：create view…，而它的查询方法与表一样。
    • 2.另一个是InnoDB在实现MVCC时用到的一致性读视图，即consistent read view，用于支持RC（读已提交）和RR（重复读）隔离级别的实现。它没有物理结构，作用是事务执行期间用来定义“我能看到什么数据”。
• “快照”如何在MVCC工作的？
  • 1.在RR隔离级别下，事务在启动时就拍了快照（基于库），InnoDB里面每个事务有一个唯一的事务ID，叫做transaction_id，它是在事务开始的时候向InnoDB的事务系统申请的，是按申请顺序严格递增的。
  • 2.每行数据有多个版本，即每次事务更新数据时候，都会生成一个新的数据版本，并且把transaction_id赋值给这个数据版本的事务ID，记作row trx_id。同时，旧的数据版本要保留，并且在新的数据版本中，能够有信息可以直接拿到它。（即：数据表中的一行记录，其实可能有多个版本（row），每个版本有自己的row trx_id）。如下图，一个记录被多个事务连续更新后的状态：
    
  • 图中该行数据的4个版本，当前最新版本V4,k=22,它是被transaction_id=25的事务更新的，即它的row trx_id=25；并且，每个事务的更新都会生成undo log（回滚日志），即图中的三个虚线箭头，就是undo log，而V1、V2、V3并不是物理上真实存在的，而是每次需要的时候根据当前版本和undo log计算出来的。例如：当需要V2的时候，就是通过V4依次执行U3和U2计算出来的。
  • 3.按照可重复度的定义，一个事务启动的时候，能够看到所有已提交的事务结果。但是之后，这个事务执行期间，其他事务的更新对它不可见。（即：一个事务只需要在启动的时候声明：以我启动的时刻为准，如果一个数据版本是在我启动之前生成的，就认；否则，如果是我启动以后才生成的，我就不认，我必须要找到它的上一个版本，如果上一个版本也不可见，那就得继续往前找。还有，如果是这个事务自己更新的数据，它自己还是要认的）。
  • 4.InnoDB为每一个事务构造了一个数组，用来保存这个事务启动瞬间，当前正在“活跃”的所有事务ID。“活跃”指的是，启动了还没提交。数组里面事务ID的最小值记为低水位，当前系统里面已经创建的事务ID的最大值加1记为高水位。这个视图数组和高视为，就组成了当前事务的一致性视图（read-view）。关于数据版本的可见性规则，就是基于数据的row trx_id和这个一致性视图的对比结果得到的，这个视图数组把所有的row trx_id分成以下几种不同的情况：
    
  • 这样，对于当前事务的启动瞬间来说，一个数据版本的row trx_id，有以下几种可能：
    • 1.如果落在绿色部分，表示这个版本已提交的事务或者当前事务自己生成的，这个数据是可见的。
    • 2.如果落在红色部分，表示这个版本是由将来启动的事务生成的，肯定是不可见的，。
    • 3.如果落在黄色部分，那就包括两种情况.1:若row trx_id在数组中，表示这个版本是由还没提交的事务生成的，不可见。2.若row trx_id不在数组中，表示这个版本是已经提交了事务生成的，可见。
  • 比如，对于上面2中的图来说，如果有一个事务的低水位是18，那么当它访问这一行数据时，就会从V4通过U3计算出V3,所以他看到的这一行的值是11。
  • 所以，这就是InnoDB所有的数据都有多个版本的特性，实现了秒级创建快照的能力。
  • 5.结合刚开始的图，我们来分析下事务A，为什么拿到的k=1？
    • 1.事务A在开始前，系统里里面只有一个活跃的事务ID=99.
    • 2.事务A、B、C的版本号分别是100、101、102，且当前系统只有这四个事务。
    • 3.三个事务开始前，（1，1）这一行的数据row trx_id=90。
  • 这样，事务A的视图数组就是[99,100],事务B的视图数据[99,100,101]，事务C的视图数组是[99,100,101,102]。为了简便，只画了事务A的逻辑查询，如下图：
    
    • 4.从上图可知，具体的数据时序更新流程如下图：
      • 1.事务C，数据(1,1)=>(1,2),row trx_id=102,90本班已经是历史版本了。
      • 2.事务B，数据(1,2)=>(1,3),row trx_id=101,102又成了历史版本。
      • 3.事务A,查询时事务B还未提交，但是它生成的(1,3)这个版本已经变成了当前版本，但是这个版本对事务A必须不可见，否则就变成了脏读。因为事务Ade视图数组是[99,100]，当然，读数据都是从当前版本读起的，所以事务A读数据流程是这样的：
        • 1.拿到数据(1,3)，判断当前row trx_id=101，比高水位大，处于红色区域，不可见。
        • 2.接着，找到上一个历史版本，row trx_id=102,比高水位大，处于红色区域不可见。
        • 3.再往前找，找到(1,1)，row trx_id=90，比低水位小，处于绿色区域，可见。
      • 这样执行下来，虽然这一行数据被修改过，但是事务A无论在什么时候查询，看到的这行数据结果都是一致的，即：一致性读。
    • 5.事务A的查询语句的视图数组是在事务A启动的时候生成的，这时候：
      • 1.(1,3)还没提交，属于情况6.1。
      • 2.(1,2)虽然提交了，但是是在视图数组创建之后提交的，属于情况2，不可见。
      • 3.(1,1)是在视图数组创建之前提交的，可见。
  • 6.对于一个事务视图来说，除了自己的更新总是可见以外，有三种情况：
    • 1.版本未提交，不可见。
    • 2.版本已提交，但是在视图创建后提交的，不可见。
    • 3.版本已提交，而且是在视图创建前提交的，可见。
  • 7.事务B的update更新语句执行逻辑。
    • 1.事务B的视图数组是先生成的，之后事务C才提交，不是应该看不见(1,2)吗？如何算出(1,3)来的呢？
      
    • 2.如果食物B再更新之前查询一次数据，那么这个查询返回的k值确实是1。但是，当它要去更新数据的时候，就不能再在历史版本上更新了，否则事物C的更新就丢失了，因此食物B此时的set k+1，是在(1,2)的基础上操作的，即用到了当前读的概念。
    • 3.当前读：更新数据都是先读后写的，而这个读，只能读当前的值，成为当前读（current read）。因此事务B在更新的时候，当前读拿到的数据是(1,2)，更新后生成了新版本的数据(1,3)，这个新版本的row trx_id=101。事务B查询语句的时候，一看自己的版本号=101，最新的数据版本号也是1010，是自己的更新，可以直接使用，所以查询得到的k=3。
    • 4.当前读，除了update语句外，select语句如果加锁，也是当前读。如果把事务A的查询语句select * from t where id=1，加上lock in share mode(乐观锁，S锁-共享锁) 或者for update(悲观锁,X锁-排他锁),也是可以读到版本号是101的数据，返回k=3。
    • 5.如果事务C不是马上提交，而是如下C`的操作：
      
    • 事务C不同的是，更新后并没有马上提交，在它提交前，事务B的更新语句先发起了。虽然事务C还没有提交，但是(1,2)这个版本也已经生成了，并且是当前最新版本。事务C没提交，按照两阶段锁协议，这个版本上的写锁还没释放，而事务B是当前读，必须要读最新版本，而且必须要加锁，因此被锁住了。必须等事务C四方这个锁，才能继续当前读，如下图:
      
• 事务的可重复读的能力是如何实现的呢？
  • 1.可重复读的核心就是一致性读（consistent read），而事务更新数据的时候，只能用当前读。如果当前的记录的行锁被其他事务占着的话，就需要进入锁等待。
  • 2.读已提交和可重复的逻辑类似，主要区别如下:
    • 1.RR级别下，只需要事务开始的时候创建一致性视图，之后事务里的其他查询都是用这个一致性视图。
    • 2.RC级别下，每个语句执行前都会重新算出一个新的视图。
• 在读已提交隔离级别下，我们来看下事务ABC分别拿到k是多少？
  
  • 1.事务Ade查询语句视图数组是在执行这个语句的时候创建的（因为在读已提交的隔离级别下，start transaction with consistent snapshot相当于start transaction），时序上(1,2),(1,3)的生成时间都在创建这个视图数组之前。但是，在这个时刻：
    • 1.(1,3)还没提交，属于情况1，不可见。
    • 2.(1,2)提交了，属于情况3，可见。
    • 3.所以事务A查询返回的k=2，显然事务B查询结果k=3。
• 总结：
  • InnoDB的行数据有多个版本，每个数据版本都有自己的row trx_id，这个事务或者语句有自己的一致性视图。普通查询语句是一致性读，一致性读会根据row trx_id和一致性视图确定数据版本的可见性：
    • 1.可重复读：查询只承认在事务启动前的就已经提交完成的数据。
    • 2.读已提交：查询只承认在语句启动前就已经提交完成的数据。
    • 3.当前读，总是读取当前已经提交完成的最新版本。

8.普通索引、唯一索引选择？

• 在不同业务场景下，应该选择普通索引还是唯一索引？假如在维护一个市民系统，每个人都有一个唯一的身份证号，其中业务代码已经保证了不会写入两个重复的身份证号。则根据身份证号查询sql如下：

select name from CUser where id_card='xxxx';
1

• 所以我们一定会在id_card字段上建索引，由于身份证号字段比较大，不建议作为主键。那么，我们针对id_card字段要么创建唯一索引，要么创建一个普通索引。如果业务代码已经保证了不会写入重复的身份证号。从性能角度角度考虑，如何选择索引呢？
  
• 查询过程？
  • 1.select id from T where k=5,先通过B+树从根节点开始，按层搜索搜索到叶子结点，即上图右下角的数据页(InnoDB默认数据页大小16KB)，然后可以认为数据页内部通过二分法来定位。
  • 2.普通索引：查找到满足条件的第一个记录(5,500)后，需要查找下一个记录，值到碰到第一个不满足k=5的条件记录，结束检索。（ps：需要多做依次“查询和判断下一条记录”，只需要一次寻址和计算）
  • 3.唯一索引：由于索引的唯一性，查找到第一个满足条件的记录后，就会停止继续检索。
• 更新过程？
  • 为了说明普通索引和唯一索引对更新语句性能的，必须要先了解changge bufffer。
    • 1.change buffer:当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中，在不影响数据已执行的前提下，InnoDB会将这些更新操作缓存在change buffer中，这样就不需要从磁盘中读入这个数据页了。在下一次查询需要访问这个数据页的时候，将数据页读入内存，然后执行change buffer中与这个页有关的操作（即：merge操作），通过merge操作就能保证数据逻辑的正确性。
    • 2.change buffer实际上它是可以持久化的数据，即change buffer在内存中有拷贝，也会被写入磁盘上。将change buffer中的操作应用到原数据页，得到最新结果的过程称为merge。处理访问这个数据页会出发merge外，系统有后台线程会定期merge。在数据库正常关闭（shutdown）过程中，也会执行merge操作。
    • ps：merge过程是否会把数据直接写会磁盘？其中merge具体执行流程如下：
      • 1.从磁盘读入数据页到内存（老版本的数据）。
      • 2.从change buffer里找到这个数据页的change buffer记录（可能有多个），依次应用，得到新版数据页。
      • 3.写redo log。这个redo log包含了数据的变更和change buffer的变更。
      • 4.结束。这时候，数据页和内存中change buffer对应的磁盘为止还没有修改，属于脏页，之后各自刷回自己的物理数据就是另外一个过程了。
    • 3.显然，如果将更新操作先记录在change buffer中，减少读磁盘，语句的执行速度会得到明显的提升。而且，数据读入内存是需要占用buffer pool的，所以这种方式还能够避免占用内存，提高内存的利用率。
    • • ps：关于使用change buffer机制，之后主机异常重启，是否会丢失change buffer数据？答：不会丢失，虽然是只更新内存，但是在事务提交的时候，我们把change buffer的操作记录到了redo log里面，所以崩溃恢复的时候，change buffer也能找回来。
    • 4.change buffer使用条件？
      • 对唯一索引来说，所有的更新操作都需要判断这个操作是否违反唯一性约束。比如：插入(4,400)这记录，需要判断是表中是否存在k=4的记录，而这必须要将数据页读入内存才能判断，如果都已经读入内存了，那直接更新内存会更快，就没必要使用change buffer。ps：因此唯一索引的更新就不能使用change buffer，实际上也只有普通索引可以使用。
      • 普通索引：既然只有普通索引可以使用change buffer，且change buffer用的是buffer pool的内存，可以通过设置innodb_change_buffer_max_size设置。
    • 5.针对记录要更新的目标页在内存中，如果要插入新纪录(4,400),那么整个InnoDB更新操作处理的流程如下图：
      • 1.唯一索引：找到k=3和k=5中间为止，判断没有冲突，插入这个值，语句执行结束。
      • 2.普通索引，找到k=3和k=5的为止，插入这个值，语句执行结束。
      • 3.唯一索引和普通所以在性能上影响不大。
    • 6.针对记录要更新的目标页不在内存中，如果要插入新纪录(4,400)，那么整个InnoDB更新操作处理流程如下图：
      • 1.唯一索引：将数据页读入内存，判断没有冲突，插入新值，语句执行结束。
      • 2.普通索引：则是更新记录在change buffer，语句就执行结束。
      • 3.将数据从磁盘读入内存涉及随机IO访问，这个是数据库里面成本最高的操作之一，change buffer因为减少了随机磁盘访问，所以对更新性能的提升是会很明显的。
    • 7.change buffer使用场景？
      • 1.上面已经分析了，只有普通索引才会使用到change buffer，并不适用于唯一索引。那么所有的普通索引的场景，使用change buffer都可以起到加速作用吗？
      • 2.因为merge的时候才是数据真正更新的时刻，而change buffer的主要卖就是将记录变更动作缓存下来，所以在一个数据页做merge之前，change buffer记录的变更越多（也就是这个页面上更新的次数越多），收益就越大。即：对于写多读少的业务来说，页面在写完后马上被访问的概率比较小，此时change buffer使用效果最好（例如：账单类、日志类系统），实际使用中，普通索引和change buffer配合使用，对于数据量大的表更新优化还是很明显的。
      • 3.如果业务更新模式是写入后马上查询，那么及时满足了条件，将更新先记录在change buffer中，但之后由于马上要访问这个数据页，就会立即出发merge过程，这样随机访问的IO次数不会减少，反而增加了change buffer的维护代价。所针对立即写立即读额业务场景，change buffer反而起到副作用（可设置关闭change buffer参数）。
      • 4.总结：唯一和普通索引在查询性能是几乎无差别，主要考虑的是更新性能的影响，建议尽量使用普通索引。在实际使用中，处于成本考虑用的基本都是机械硬盘，那就应该特别关注这些表里的索引，尽量使用普通索引，change buffer的capacity尽量设置大，以确保这个历史数据标的数据写入速度。
• change buffer和redo log区别？
  • 执行如下sql：

      insert into t(id,k) values(id1,k1),(id2,k2);
  1

  • 假设当前k索引树如下，查找到位置后，k1所在的数据也在内存（InnoDB
    buffer）中，k2所在的数据也不在内存中，下图即为changge buffer的更新状态图：
    
  • 执行流程：
    • 1.Page 1在内存中，直接更新内存；
    • 2.Page 2没有在内存中，就在内存的change buffer区域，记录下：我要往Page 2插入一行。
    • 3.将上述两个动作记录redo log中（图3和图4）。
    • 4.事务commit。
  • 整个过程，仅写入了两处内存，一处磁盘，而且还是顺序写入。（ps：其中图中的两个虚线箭头为后台操作，不影响更新的响应时间）
  • 之后执行如下读请求（如果读语句发生在更新语句后不久，内存中的数据都还在，那么此时的这两个读操作就与系统空间（ibdata1）和redo log（ib_log_fileX）无关了，图中已忽略）：

      select * from t where k in(k1,k2)；
  1

  
  • 执行流程：
    • 1.读Page 1，直接从内存返回（虽然磁盘上还是之前的数据，但是这里直接从内存中返回结果，结果正确的）。
    • 2.读Page 2，需要把Page 2从磁盘写入内存中，然后应用change buffer里面的操作日志，生成一个正确的版本并返回结果。
  • 总结：redo log主要节省的是随机写磁盘的IO消耗（转成顺序写），而change buffer主要节省的则是随机读磁盘的IO消耗。

9.MySQL索引选择？

• 在MySQL中一张表其实可以支持多个索引，具体在执行SQL的时候选择哪个索引，就是优化器的工作了。
• 例如，我们分别在表t加上a、b两个做索引，并且分别插入10万条记录（1,1,1）…(100000,100000,10000)，其中我们定义存储过程idata()来插入这10万数据;

CREATE TABLE `t`(
    `id` int(11) not null,
    `a` int(11) default null,
    `b` int(11) default null,
    PRIMARY KEY(`id`),
    key `a`(`a`),
    key `b`(`b`)
)ENGINE=InnoDB;

// 执行查询语句
select * from t where a between 10000 and 20000;
1
2
3
4
5
6
7
8
9
10
11

• 执行explain，可以看到符合预期，走到索引a
  
• 如果执行如下事务顺序操作：
  
• 在整个过程中，事务B把数据删除了，然后又插入了10万数据，事务B再执行查询语句，就不会再选择索引a，这里可以通过慢查询日志（show log）查看具体执行情况。为了对比，我们选择强制指定查询索引a=>force index(a)。可以看到两者的查询速度，上面的查询扫描了10万行，显然走了全表扫描，执行时间40ms，下面的查询执行了21ms。即我们在没有使用force index的时候，MYSQL用错了索引，导致了更长的执行时间：
  
• 优化器如何选择索引？
  • 1.优化器选择索引的目的是为了找到一个最优的执行方案，并用最小的代码去执行语句。在数据库里面，扫描行数（rows）是影响执行代码的因素之一，扫描行数越少，意味着访问磁盘的数据的次数越少，消耗的CPU资源越少。当然，扫描行数并不是唯一的判断标准，优化器还会结合是否使用临时表、是否排序等因素进行综合判断。
  • 2.扫描行数如何判断？MySQL在整整开始执行语句之前，并不能精确地直到满足这个sql的记录有多少条，而是根据统计信息（抽样统计-InnoDB默认会选择N个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到这个索引的基数了。索引的统计信息随着变更数据行数超过1/M的时候，就会触发重做一次索引统计）来估算记录数。这个统计信息就是索引的“区分度”，一个索引上的不同值越多，这个索引的区分度就越好，而一个索引上的不同的值个数，即称为“基数”（cardinality），基数越大，索引的区分度越好。
  • ==ps：==索引统计可以通过参数innodb_stats_persistent设置，当开启时on，统计信息会持久化存储（N=20，M=10）。当关闭off时，统计信息只会存储在内存中（N=8，M16）。
  • 3.show index命令可以查看索引的基数。对于表t，虽然三个字段的值都是一样的，但是cardinality是不同的(当然也不是准确的)，如下图：
    
  • 4.针对优化器选择索引是会考虑回表的成本的（例如下图，优化器宁愿选择10万的rows【没有回表的代价】，也没选择37000的rows），因为优化器每次从索引a上拿到一个值，都要回表查出整行数据，这个代价也是要算进去的。
    
  • 5.既然优化器选择的并不是最优的，即MySQL选错索引还是由于没能准确的判断出扫描行数（rows）。我们对于扫描行数的矫正，可以通过命令：analyze table xx，来重新统计索引信息。
• 索引选择异常和处理？
  • 方法1：force index强行选择一个索引。MySQL会根据词法解析的结果分析出可能可以使用的索引作为候选项，然后在候选项中依次判断每个索引要扫描多少航。但是如果存在force index指定的索引在索引候选项时，就直接选择这个索引，不再评估其他索引的执行代价。
  • ps：force index在开发初期并不会直接写上，往往是在现实生产环境出现问题，才会去使用force index去修复sql，但是整个过程需要介入QA、开发、DBA等业务方，不够敏捷，建议还是数据库内部解决。
  • 方法2：可以考虑修改语句，引导MySQl使用我们期望的索引。例如：“
    order by b limit 1”改成“order by b,a limit 1”,两者的语义逻辑是一致的。改完之后的效果如下图：
    
  • 之前优化器选择使用索引b，是因为它认为使用b可以避免排序（b本身是索引，已经有序，选择索引b，不用再排序仅遍历，及时扫描行数更多，也判定代价更小），这种order by b,a的写法，要求按照b,a排序，意味着使用这两个索引都需要排序，因此，扫描行数成了影响决策的主要条件，于是此时优化器选择了只扫描1000行的索引a。（ps：这里因为刚好使用到了 limit 1，两次order by都是取b的最小一行，逻辑是一致的，才可以使用此优化方法，如果改成limit 100，优化器还是会判断索引b的代价高。所以此优化方法并不具备通用性）。
  • 方法3：在某些场景下，新建一个更合适的索引，来提供优化器做选择，或者删掉误用的索引。
• 总结：
  • 1.矫正索引统计信息可以使用：analyze table xx。
  • 2.为了优化器选择正确的索引，可以使用force index强行指定sql使用索引，或修改sql语句来诱导优化器，再者通过删除或新增索引来解决。