MySql45讲学习笔记

一条sql的查询过程

分析器：做词法分析，语法分析，词法分析识别sql中关键字，语法分析判断语法错误。
优化器：决定使用哪个索引以及各个表的关联顺序。
分析器决定你要做什么，优化器决定你要怎么做，执行器具体执行。
更新语句执行过程
mysql

事务

事务就是要保证一组数据库操作，要么全部成功，要么全部失败。
事务的四种隔离状态
读未提交是指，一个事务还没提交时，它做的变更就能被别的事务看到。
读提交是指，一个事务提交之后，它做的变更才会被其他事务看到。
可重复读是指，一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一 致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。
串行化，顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突 的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。
Oracle数据库默认隔离级别是读提交，mysql自己本地是可重复读。
事务隔离的实现方式
在实现上，数据库里面会创建一个视图，访问的时候以视图的逻辑结果为准。在“可重复读”隔离 级别下，这个视图是在事务启动时创建的，整个事务存在期间都用这个视图。在“读提交”隔离级 别下，这个视图是在每个SQL语句开始执行的时候创建的。这里需要注意的是，“读未提交”隔离 级别下直接返回记录上的最新值，没有视图概念;而“串行化”隔离级别下直接用加锁的方式来避 免并行访问。
事务的启动方式

1. 显式启动事务语句， begin 或 start transaction。配套的提交语句是commit，回滚语句是 rollback。
2. set autocommit=0，这个命令会将这个线程的自动提交关掉。意味着如果你只执行一个 select语句，这个事务就启动了，而且并不会自动提交。这个事务持续存在直到你主动执行 commit 或 rollback 语句，或者断开连接。建议设置set autocommit=1；

如何避免长事务对业务的影响?

1. 确认是否使用了set autocommit=0。这个确认工作可以在测试环境中开展，把MySQL的 general_log开起来，然后随便跑一个业务逻辑，通过general_log的日志来确认。一般框架 如果会设置这个值，也就会提供参数来控制行为，你的目标就是把它改成1。
2. 确认是否有不必要的只读事务。有些框架会习惯不管什么语句先用begin/commit框起来。我 见过有些是业务并没有这个需要，但是也把好几个select语句放到了事务中。这种只读事务 可以去掉。
3. 业务连接数据库的时候，根据业务本身的预估，通过SETMAX_EXECUTION_TIME命令， 来控制每个语句执行的最长时间，避免单个语句意外执行太长时间。(为什么会意外?在后 续的文章中会提到这类案例)
   其次，从数据库端来看:
4. 监控 information_schema.Innodb_trx表，设置长事务阈值，超过就报警/或者kill;
5. Percona的pt-kill这个工具不错，推荐使用;
6. 在业务功能测试阶段要求输出所有的general_log，分析日志行为提前发现问题;
7. 如果使用的是MySQL5.6或者更新版本，把innodb_undo_tablespaces设置成2(或更大的 值)。如果真的出现大事务导致回滚段过大，这样设置后清理起来更方便

索引

索引常见模型
1.哈希表
哈希表这种结构适用于只有等值查询的场景，对于区间查询，需要扫描全部数据。
2.有序数组
有序数组在等值查询和范围查询都相对优秀，如果仅仅看查询效率，有序数组就是最好的数据结构了。但是，在需要更新数据的时候就麻烦 了，你往中间插入一个记录就必须得挪动后面所有的记录，成本太高。
有序数组只适用于静态存储引擎，比如你要保存2017年某个城市的所有人口信息，这类不会修改的数据。
3.N叉树
由二叉搜索树衍变而来，对于二叉搜索树来说节点树增多，树高太高，一次查询访问数据块过多，导致查询效率降低。为了让一个查询尽量少的读磁盘，衍生出N叉树，N取决于数据块的大小。
以InnoDB的一个整数字段索引为例，这个N差不多是1200。这棵树高是4的时候，就可以存 1200的3次方个值，这已经17亿了。考虑到树根的数据块总是在内存中的，一个10亿行的表上一 个整数字段的索引，查找一个值最多只需要访问3次磁盘。其实，树的第二层也有很大概率在内 存中，那么访问磁盘的平均次数就更少了。
在MySQL中，索引是在存储引擎层实现的，所以并没有统一的索引标准，即不同存储引擎的索引的工作方式并不一样。而即使多个存储引擎支持同一种类型的索引，其底层的实现也可能不同。
InnoDB 的索引模型
在InnoDB中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。又因为前面我们提到的，InnoDB使用了B+树索引模型，所以数据都是存储在B+树中的。
主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。从性能和存储空间方面考量，自增主键往往是更合理的选择。
为什么 InnoDB要这么选择。B+树能够很好地配合磁盘的读写特性，减少单次查询的磁盘访问次数。
覆盖索引
alter table T add index(k);
如果执行的语句是select ID from T where k between 3 and 5，这时只需要查ID的值，而ID的值已经在k索引树上了，因此可以直接提供查询结果，不需要回表。也就是说，在这个查询里面， 索引k已经“覆盖了”我们的查询需求，我们称为覆盖索引。

全局锁和表锁

全局锁
顾名思义，全局锁就是对整个数据库实例加锁。MySQL提供了一个加全局读锁的方法，命令是 Flush tables with read lock (FTWRL)。当你需要让整个库处于只读状态的时候，可以使用这个命 令，之后其他线程的以下语句会被阻塞:数据更新语句(数据的增删改)、数据定义语句(包括 建表、修改表结构等)和更新类事务的提交语句。
全局锁的典型使用场景是，做全库逻辑备份。也就是把整库每个表都select出来存成文本。
以前有一种做法，是通过FTWRL确保不会有其他线程对数据库做更新，然后对整个库做备份。 注意，在备份过程中整个库完全处于只读状态。
看来加全局锁不太好。但是细想一下，备份为什么要加锁呢?我们来看一下不加锁会有什么问
题。 假设你现在要维护“极客时间”的购买系统，关注的是用户账户余额表和用户课程表。
现在发起一个逻辑备份。假设备份期间，有一个用户，他购买了一门课程，业务逻辑里就要扣掉 他的余额，然后往已购课程里面加上一门课。
如果时间顺序上是先备份账户余额表(u_account)，然后用户购买，然后备份用户课程表，会出现两个表业务逻辑不一致，也就是备份系统备份的得到的库不是一个逻辑时间点。
在可重复读的事务隔离级别下，开启一个事务，可以拿到一致性视图
官方自带的逻辑备份工具是mysqldump。当mysqldump使用参数–single-transaction的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于MVCC的支持，这个过程中数据是 可以正常更新的。
对于不支持事务的引擎，则需要使用FTWRL进行加锁，备份。

让全库只读，还可以使用set global readonly=true的方式，因为修改gloable变量的方式影响面大，所以不建议使用，另外，在异常处理机制上有差异。如果执行FTWRL命令之后由于客户端发生异常断开，那么 MySQL会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为 readonly之后，如果客户端发生异常，则数据库就会一直保持readonly状态，这样会导致整个 库长时间处于不可写状态，风险较高。
表级锁
MySql里的表级锁有两种，一种是表锁，一种是元数据锁。
表锁的语法是 lock tables …read/write，可以用unlock tables主动释放锁， 也可以在客户端断开的时候自动释放。
lock tables语法除了会限制别的线程的读写 外，也限定了本线程接下来的操作对象。
举个例子, 如果在某个线程A中执行lock tables t1 read, t2 write; 这个语句，则其他线程写t1、读 写t2的语句都会被阻塞。同时，线程A在执行unlock tables之前，也只能执行读t1、读写t2的操 作。连写t1都不允许，自然也不能访问其他表。
在还没有出现更细粒度的锁的时候，表锁是最常用的处理并发的方式。而对于InnoDB这种支持 行锁的引擎，一般不使用lock tables命令来控制并发，毕竟锁住整个表的影响面还是太大。
元数据锁
另一类表级的锁是MDL(metadata lock)。MDL不需要显式使用，在访问一个表的时候会被 自动加上。MDL的作用是，保证读写的正确性。你可以想象一下，如果一个查询正在遍历一个 表中的数据，而执行期间另一个线程对这个表结构做变更，删了一列，那么查询线程拿到的结果 跟表结构对不上，肯定是不行的。
行锁
顾名思义，行锁就是针对数据表中行记录的锁。这很好理解，比如事务A更新了一行，而这时候事务B也要更新同一行，则必须等事务A的操作完成后才能进行更新。
在InnoDB事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。
如果你的事务中需要锁多个行，要把 最可能造成锁冲突、最可能影响并发度的锁尽量往后放。
例：
1. 从顾客A账户余额中扣除电影票价;
2. 给影院B的账户余额增加这张电影票价;
3. 记录一条交易日志。
因为可能存在多人同事购买电影票，给影院余额添加票价可能会产生冲突，所以最好的顺序是 3，1，2
死锁和死锁检测
死锁产生：
当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致 这几个线程都进入无限等待的状态，称为死锁。
两个事务，事务A更改第一行记录，事务B更改了第二行记录，当事务A想要更改第二行记录，事务B想要更改第一行记录时，由于事务A和事务B均未提交，导致行锁未释放，所以产生死锁。
解决策略：
一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout来设置。
另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数innodb_deadlock_detect设置为on，表示开启这个逻辑。
innodb_lock_wait_timeout的默认值是50s，由于对于在线服务来说时间过长，所以一般采用死锁检测的策略。innodb_deadlock_detect的默认值本身就是on。主动死锁检测在发生死锁的时候，是能够快速发 现并进行处理的，但是它也是有额外负担的。

普通索引与唯一索引的选择

	在查询过程中，唯一索引查到这个值就会结束，而普通索引查找到第一个不满足的值结束。当满足的值在当前数据页最后一个位置，需要获取下一个数据页，造成资源浪费，但是这种概率比较小，性能差异不是很大。
	在更新过程中，唯一索引需要把数据页读入内存中来判断有没有冲突，对于普通索引来说，则是将更新记录在change buffer中，change buffer 因为减少了随机磁盘访问，所以对更新性能的提升是会很明显的。
	
	第一种情况是，这个记录要更新的目标页在内存中。这时，InnoDB的处理流程如下: 对于唯一索引来说，找到3和5之间的位置，判断到没有冲突，插入这个值，语句执行结束;对于普通索引来说，找到3和5之间的位置，插入这个值，语句执行结束。 这样看来，普通索引和唯一索引对更新语句性能影响的差别，只是一个判断，只会耗费微小的CPU时间。但，这不是我们关注的重点。 
	第二种情况是，这个记录要更新的目标页不在内存中。这时，InnoDB的处理流程如下:对于唯一索引来说，需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结 束;对于普通索引来说，则是将更新记录在change buffer，语句执行就结束了。将数据从磁盘读入内存涉及随机IO的访问，是数据库里面成本最高的操作之一。change buffer 因为减少了随机磁盘访问，所以对更新性能的提升是会很明显的。
	对于有大量数据插入的情况下，建议使用普通索引，因为唯一索引插入一次就需要将数据页读入内存中，可能会影响业务库的内存命中率。
	因此，对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时change buffer的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。
	反过来，假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记 录在change buffer，但之后由于马上要访问这个数据页，会立即触发merge过程。这样随机访问 IO的次数不会减少，反而增加了change buffer的维护代价。所以，对于这种业务模式来 说，change buffer反而起到了副作用。

索引选择和实践
回到我们文章开头的问题，普通索引和唯一索引应该怎么选择。其实，这两类索引在查询能力上 是没差别的，主要考虑的是对更新性能的影响。所以，我建议你尽量选择普通索引。
如果所有的更新后面，都马上伴随着对这个记录的查询，那么你应该关闭change buffer。而在 其他情况下，change buffer都能提升更新性能。
在实际使用中，你会发现，普通索引和change buffer的配合使用，对于数据量大的表的更新优 化还是很明显的。
特别地，在使用机械硬盘时，change buffer这个机制的收效是非常显著的。所以，当你有一个 类似“历史数据”的库，并且出于成本考虑用的是机械硬盘时，那你应该特别关注这些表里的索 引，尽量使用普通索引，然后把change buffer 尽量开大，以确保这个“历史数据”表的数据写入 速度。
redo log 主要节省的是随机写磁盘的IO消耗(转成顺序写)，而change buffer主要节省的则是随机读磁盘的IO消耗。
普通索引与唯一索引的选择：
首先，业务正确性优先。咱们这篇文章的前提是“业务代码已经保证不会写入重复数据”的情况 下，讨论性能问题。如果业务不能保证，或者业务就是要求数据库来做约束，那么没得选， 必须创建唯一索引。这种情况下，本篇文章的意义在于，如果碰上了大量插入数据慢、内存 命中率低的时候，可以给你多提供一个排查思路。
然后，在一些“归档库”的场景，你是可以考虑使用唯一索引的。比如，线上数据只需要保留半 年，然后历史数据保存在归档库。这时候，归档数据已经是确保没有唯一键冲突了。要提高 归档效率，可以考虑把表里面的唯一索引改成普通索引。

给字符串字段加索引

	使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查 询成本。使用前缀索引，就没法使用覆盖索引，因为你即使前缀索引已经包含了全部信息，系统去不确定是否截断了完整信息，需要到id索引中再去对比一次。
	遇到前缀区分度不高的字符串索引，例如身份证号，我们国家的身份证号，一共18位，其中前6位是地址码，所以同一个县的人的身份证号前6位一般会是相同的。但是，索引选取的越长，占用的磁盘空间就越大，相同的数据页能放下的索引值就越少，搜索的 效率也就会越低。
	对于这种情况，我们建立索引的第一种方式是使用倒序存储，这样的话，每次查询可以这样写：
	mysql> select field_list from t where id_card = reverse('input_id_card_string');
	由于身份证号的最后6位没有地址码这样的重复逻辑，所以最后这6位很可能就提供了足够的区 分度。当然了，实践中还需要使用count(distinct)方法去做个验证。
	第二种方式可以使用hash字段：
	mysql> alter table t add id_card_crc int unsigned, add index(id_card_crc);

MySql 会抖一下

		当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”。
		平时执行很快的更新操作，其实就是在写内存和日志，而MySQL偶尔“抖”一下的那个瞬间，可能就是在刷脏页(flush)。

触发flush的集中情况：
1.InnoDB的redo log写满了。这时候系统会停止所有更新操作，把 checkpoint往前推进，redo log留出空间可以继续写。
2.系统内存不足。当需要新的内存页，而内存不够用的时候，就要淘汰一些数据页，空出内存给别的数据页使用。如果淘汰的是“脏页”，就要先将脏页写到磁盘。虽然在redo log 中有记录，但是出于性能考虑，刷脏页的话，一定会写盘，就保证了每个数据页有两种状态。
一种是内存里存在，内存里就肯定是正确的结果，直接返回;
另一种是内存里没有数据，就可以肯定数据文件上是正确的结果，读入内存后返回。 这样的效率最高。
3.MySQL认为系统“空闲”的时候。当然，MySQL“这家酒店”的生意好起 来可是会很快就能把粉板记满的，所以“掌柜”要合理地安排时间，即使是“生意好”的时候，也 要见缝插针地找时间，只要有机会就刷一点“脏页”。
4.MySQL正常关闭的情况。这时候，MySQL会把内存的脏页都flush到磁 盘上，这样下次MySQL启动的时候，就可以直接从磁盘上读数据，启动速度会很快。
对于性能有影响的情况则为：
1. 一个查询要淘汰的脏页个数太多，会导致查询的响应时间明显变长;
2. 日志写满，更新全部堵住，写性能跌为0，这种情况对敏感业务来说，是不能接受的。 所以，InnoDB需要有控制脏页比例的机制，来尽量避免上面的这两种情况。
InnoDB刷脏页的控制策略
1.正确地设置innodb_io_capacity参数
2.InnoDB的刷盘速度就是要参考这两个因素:一个是脏页比例，一个是redo log写盘速度。
InnoDB会根据这两个因素先单独算出两个数字。 参数innodb_max_dirty_pages_pct是脏页比例上限，默认值是75%。InnoDB会根据当前的脏页比例(假设为M)，算出一个范围在0到100之间的数字，计算这个数字的伪代码类似这样:

F1(M) {
  if M>=innodb_max_dirty_pages_pct then
      return 100;
  return 100*M/innodb_max_dirty_pages_pct;
}

innoDB每次写入的日志都有一个序号，当前写入的序号跟checkpoint对应的序号之间的差值， 我们假设为N。InnoDB会根据这个N算出一个范围在0到100之间的数字，这个计算公式可以记为 F2(N)。F2(N)算法比较复杂，你只要知道N越大，算出来的值越大就好了。
然后，根据上述算得的F1(M)和F2(N)两个值，取其中较大的值记为R，之后引擎就可以按 照innodb_io_capacity定义的能力乘以R%来控制刷脏页的速度。平时要多关注脏页比 例，不要让它经常接近75%。另外，在InnoDB中，innodb_flush_neighbors 参数值为1的时候会有的“连坐”机制，将数据页旁边的脏页也一起刷掉，值为0时表示不找邻居，自己刷自己的。

为什么表数据删掉一半，表文件大小不变。

数据库表的空间回收问题
innodb_file_per_table 参数用来控制数据是存在共享表空间里，还是单独的文件
1. 这个参数设置为OFF表示的是，表的数据放在系统共享表空间，也就是跟数据字典放在一起;
2. 这个参数设置为ON表示的是，每个InnoDB表数据存储在一个以 .ibd为后缀的文件中。
默认为ON，因为一个表单独存储为一个文件，更容易管理，而且在你不需要这个表的时候，通过drop table命令，系统就会直接删除这个文 件。而如果是放在共享表空间中，即使表删掉了，空间也是不会回收的。
通过重建表可以使表空间变紧凑，但在特殊情况也会使表空间变大。
在重建表的时候，InnoDB不会把整张表占满，每个页留了1/16给后续的更新用。也就是说，其实重建表之后不是“最”紧凑的。 假如是这么一个过程:

1. 将表t重建一次;
2. 插入一部分数据，但是插入的这些数据，用掉了一部分的预留空间;
3. 这种情况下，再重建一次表t，就可能会出现问题中的现象。