MySQL 45讲笔记（1-10讲）

1. SQL语句如何开始执行？

MySQL分为Server和存储引擎两部分：
Server层包含连接器、存储缓存、分析器、执行器等，以及所有的内置函数（事件、日期）等等，还有视图、触发器。
存储引擎是负责数据的存储和提取，我们有InnoDB、MyISAM等等。MySQL建表默认InnoDB，但是也可以指定。

1. 如何去查询？
2. 连接器先建立 TCP连接，查询你的权限，此次连接，你的权限不会被别人所更改。
3. 执行select语句，先查缓存，没有命中就继续。大多数情况下缓存比较麻烦，最好不要用指定缓存查询。8.0之后就没缓存功能了
4. 分析器。分析器用来检查SQL语法，并语法分析。
5. 优化器。MySQL内部对语句判断如何选择索引。
6. 执行器。判断你有权限，继续调用InnoDB，返回结果。

2.日志系统

更新流程：
redolog，binlog
redolog WAL技术，先写日志，不往前去更改，4个盘，循环的写。

redolog是存储引擎层的日志。

binlog是server层日志，追加写。
两阶段提交：

让两份日志之间的逻辑一致。

3. 事务隔离

1. SQL标准的事务隔离级别包括：读未提交（read uncommitted）、
   读提交（read committed）、可重复读（repeatable read）和串行化（serializable ）。
   读未提交是指，一个事务还没提交时，它做的变更就能被别的事务看到。
   读提交是指，一个事务提交之后，它做的变更才会被其他事务看到。
   可重复读是指，一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一
   致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。
   串行化，顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突
   的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

2. 多版本的并发控制(MVCC)，控制了回滚。

4.索引上

1. 有序数组 哈希，只能用于静态存储引擎，写入了就不能去修改。
2. 二叉树比较慢，所以InnoDB用B+树。
3. 主键是一棵B+树，N个叉，只在叶子节点存所有数据的值，也被叫聚簇索引。
4. 非主键索引的叶子是主键值，所以需要查找到主键值回表，去主键B+树去查值，如果说这个索引是个联合索引，又恰好你只需要这些值，那就不用回表，叫覆盖索引。
5. 用自增主键可以防止页分裂的降低效率
6. 最左前缀，

5. 索引下

1. 覆盖索引：只需要查ID的值，而ID的值已经在k索引树上了，因此可以直接提供查询结果，不需要回表。
2. 最左前缀：即当你创建了一个联合索引，该索引的任何最左前缀都可以用于查询。 比如当你有一个联合索引 (col1, col2, col3) ，该索引的所有前缀为 (col1) 、 (col1, col2) 、 (col1, col2, col3) ，包含这些列的所有查询都会使用该索引进行查询。
3. 索引下推：组合索引满足最左匹配，但是遇到非等值判断时匹配停止。name like ‘陈%’ 不是等值匹配，所以 age = 20 这里就用不上 (name,age) 组合索引了。如果没有索引下推，组合索引只能用到 name，age 的判定就需要回表才能做了。5.6之后有了索引下推，age = 20 可以直接在组合索引里判定。

6.全局锁和表锁

MySQL M 里面的锁大致可以分成全局锁、表级锁和行锁三类

1. 全局锁：做全库逻辑备份，保证备份时是唯一视图，MySQL里是mysqldump，参数single-transaction是做这个的，MVCC也能保证数据正常更新。别的引擎用FTWRL
2. 表级锁：lock tables，面积太大。MySQL，引入MDL，增删改查时，加读锁，更改表结构，加写锁。读锁不互斥，写互斥，就是阻塞。
3. 长事务不行，不提交占用MDL，阻塞。解决长事务，kill或者设定等待时间（指定DDL NOWIT/ WAIT N）.
   ALTER TABLE tbl_name NOWAIT add column …
   ALTER TABLE tbl_name WAIT N add column …

7.行锁

1. 两阶段锁：在InnoDB事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束Commit时才释放。这个就是两阶段锁协议。
2. 更改操作顺序，共同操作的放后面，减少事务之间的等待。（影院买票，插日志，改顾客余额，更改影院余额放最后面）
3. 死锁和死锁检测：死锁是并发中循环资源依赖，都在都等别人释放，导致都无限等待。A等待B放id2，B等A释放id1
4. 解决死锁：
   • 设置超时时间，一般不用。
   • 主动死锁检测：解决热点更新的问题。1000个并发线程更改同一行，死锁检测是100万级。
   • 控制并发度，更改电影院账户为10个，分流。

8. 事务如何隔离

1. 视图概念：一个用查询语句定义的虚拟表，调用的时候执行查询语句生成结果。
2. InnoDB实现MVCC时用到的一致性视图，consistent read view，支持RC和RR隔离的实现。
3. 每一行数据有多个由InnoDB发放的Transaction id，MVCC视图可以分为已提交事务，未提交事务集合，未开始事务。分别是可见，部分可见，不可见。
4. 任务视图三种情况：
   • 版本未提交，不可见
   • 版本已提交，视图创建后提交，不可见
   • 版本已提交，视图创建前提交，可见
5. 更新数据都是先读后写，这个读，只能读当前值，成为“当前读”。
6. 可重复读（一致性读）核心就是唯一视图，更新数据只能用当前读，如果要更新数据被别人占用了没提交，就锁住了，称为行锁。

9. 普通索引和唯一索引

1. change buffer 是InnoDB的内存操作，merge是得到新结果。
2. 唯一索引要判断，需要加载入内存，已经在内存中的用内存更快。所以唯一索引不需要change buffer
3. 如果插入值有冲突：
   • 普通索引会将更新记录在change buffer，后面merge后会持久化到磁盘。
   • 唯一索引将数据页读入内存，判断没有冲突，插入后，语句结束。（读入内存需要大量IO，change buffer减少这个操作）
4. 写多读少场景，change buffer的收益最大。（账单类，日志类）。反之，是副作用。
5. 尽量选择普通索引，配置change buffer比较好用
6. redo log WAL，
   • 写操作：如果在内存中数据直接改，没在就change buffer记录中更改，最后都写入redolog中。
   • 读操作：在内存中直接返回，不在的，先读入内存，再根据buffer更改，生成正确版本返回。
7. redo log节省随机写磁盘的IO消耗，change buffer节省随机读磁盘的消耗。

10. MySQL为什么有时候会选错索引？

1. 删完再插入再查，时间变长，索引选择错误。是因为优化器选错索引。
2. 选择索引错误，可以用analyze table t命令来重新统计索引信息。
3. 矫正选择索引错误
   • force index来强制选择索引，在优化器失效时，可以起到矫正作用。
   • 修改SQL语句，order by b limit 1 -> order by b,a limit 1。a 1000行，也纳入排序，优化器知道a少，就索引a了
   • 新建合适的索引