MySQL学习总结

1、三大范式

1. 第一范式：确保每列保持原子性。
2. 第二范式：确保表中每列都和主键相关。
3. 第三范式：确保每列都和主键列直接相关，而不是间接相关。

2、数据库四大特性：ACID

1. A（atomic）：原子性：指事务包含的所有操作要么全部成功，要么全部失败回滚。使用undo log实现。如果事务执行过程中出错或者用户回滚，系统通过undo log日志返回事务开始的状态。
2. C（consistency）：一致性：指事务必须使数据库从一个一致性状态转换为另一个一致性状态。即：一个事务执行前和执行之后必须处于一致性状态。通过回滚、恢复，以及并发情况下的隔离性实现。
3. I（isolation）：隔离性：多个用户并发访问数据库的时候，数据库要为每一个用户开启一个事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。通过锁和MVCC使事务相互隔离开。
4. D（durability）：持久性：指一个事务一旦被提交，那么对数据库中数据改变就是永久性的。使用redo log实现，即使系统崩溃，可通过redo log把数据恢复。

3、数据库中的组件

1. Server层：不同存储引擎共用一个server层。
   1. 连接器：连接查看是否有权限。
   2. 查询缓存：之前查询的缓存。Key就是查询语句，value就是查询结果。
   3. 分析器：进行词法分析。
   4. 优化器：确定执行方案。
   5. 执行器：执行前，再次确认有无权限，将最终任务提交给存储引擎。
   6. 存储引擎：常用是Innodb。

4、查询语句经过组件流程

1. 连接器进行连接，检查语句是否有权限，没权限直接返回错误信息，有权限，在MySQL8之前，会先去查看查询缓存。有缓存直接走缓存，没有缓存，向下走。
2. 分析器进行词法分析，判断该sql语句是否有语法错误。没问题进行下一步。
3. 优化器进行确定执行方案，确定了执行计划之后开始执行。
4. 执行器在执行之前会查看是否有权限，没有权限返回错误信息，有就调用引擎接口，返回引擎的执行结果。

5、更新语句的执行情况

1. 开始和查询一样，连接器进行权限认证，分析器进行此法分析，优化器进行方案选择，执行器再进行一次权限检查，调用引擎接口。
2. 不管是否有索引，引擎会查询到要修改的那一行数据，有缓存的话直接将该数据页返回给执行器，没有就先从磁盘中读到内存，再返回。
3. 执行器得到从引擎返回的数据，然后进行相应的更新操作，又调用引擎接口写入新的行数据。引擎把数据保存在内存中，同时记录redo log，此时的redo log进入prepare状态。引擎通知执行器执行完毕，随时可以提交。
4. 执行器收到这个通知之后，生成这个操作的binlog，且binlog写入到磁盘，再调用引擎提交事务的接口，随后引擎将redo log改为提交状态。
5. 更新完成。

6、MySQL中的四种隔离级别

1. 读未提交：一个事务还没提交，变更就可以被其他事务看到。
2. 读已提交：一个事务提交之后，做的变更会被其他事务看到。
3. 可重复读：是MySQL默认隔离级别，确保同一事务的多个实例在并发读取数据的时候会看到同样的行数。
4. 可串行化：对于同一行记录，读写都会加锁，后访问的事务必须等前一个事务完成才能继续执行。

7、数据库索引：

1. 索引是帮助MySQL高效获取数据的排好序的数据结构。
2. 哈希表的优点：同一索引位置增加值速度很快，缺点：做区间查询的时候速度很慢。
3. 有序数组优点：查询效率很快，缺点：做更新数据很麻烦。
4. 二叉树缺点：索引不止存在内存中，还要写在磁盘上，当二叉树过高，每次查询需要访问节点过多，访问数据块过多（IO次数过多）。
5. InnoDB的索引模型：使用B+树，在InnoDB中，每一张表就是多个B+树，就是一个主键索引树+多个非主键索引树。
6. 效率：使用主键索引>使用非主键索引>不使用索引。
7. 基于非主键索引的查询需要多扫描一个索引树，因此，应用中应多使用主键查询。
8. 覆盖索引：在这个查询中，索引k已经覆盖了查询需求，不再需要进行回表。覆盖索引可以减少树的搜索次数，显著提升查询性能。
9. 最左前缀原则：MySQL建立联合索引的时候回遵守最左前缀原则，即：最左优先，在检索数据的时候从联合索引的最左边开始匹配。 此时联合索引是（name，age），可以利用联合索引快速查找到name为张三的，或者姓名中第一个字是“张”的。
10. 索引下推：MySQL5.6之前在模糊查找的时候，会一个一个进行回表，在主键索引上找出数据行进行字段值的比较。MySQL5.6引入索引下推，对索引中包含的字段先做判断，直接过滤掉不满足的条件记录，减少回表的次数。

7.1、普通索引

1. 是最基本的索引，没有任何限制
2. 创建语句：

CREATE INDEX index_name ON table(column(length))

7.2、唯一索引

1. 与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
2. 创建语句：

CREATE UNIQUE INDEX indexName ON table(column(length))

7.3、主键索引

1. 是一种特殊的唯一索引，一个表只能有一个主键，且不允许有空值。
2. 创建语句：

CREATE TABLE `table` (    `id` int(11) NOT NULL AUTO_INCREMENT ,    `title` char(255) NOT NULL ,    PRIMARY KEY (`id`) );

7.4、组合索引

1. 指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合。
2. 创建语句：

ALTER TABLE `table` ADD INDEX name_city_age (name,city,age); 

8、聚集索引和非聚集索引：

1. 聚集索引就是可以根据索引直接找到数据，不会有回表过程的索引，主键索引就是聚集索引。
2. 非聚集索引：通过索引没有找到数据，需要根据索引上的值再次回表查询。
3. 区别：聚集索引可以一次查找到需要的数据，非聚集索引需要回表才能找到需要的数据。一张表只有一个聚集索引，非聚集索引可以有多个。

9、索引的注意事项以及优缺点：

1. 如何建立合适索引：
   1. 重复率小的列建议生成索引。
   2. 数据具有唯一性。
   3. 频繁使用group by、order by的列建议生成。可以大幅提高分组和排序效率。
   4. 有外键的列应该建立索引。
2. 优点：
   1. 大大减小了服务器需要扫描的数据量。
   2. 可以加速表与表之间的连接。
   3. 可以显著减少查询中的分组和排序时间
3. 缺点：
   1. 创建和维护需要耗费时间。
   2. 索引占用物理内存。

10、索引有效和失效场景

1. 有效：
   1. 全值匹配：和索引中所有的列进行匹配。
   2. 匹配最左前缀：索引列最左边的若干个列。
   3. 匹配范围值。
   4. 只访问索引的查询。
2. 失效：口诀（模型数空运最快）
   1. 模：模糊查询，使用like+%开头。
   2. 型：数据类型错误。
   3. 数：函数，对索引的字段使用内部函数，此时应该建立基于函数的索引。
   4. 空：null，索引不存储空值。
   5. 运：运算，对索引列进行+、-、*、/等运算会导致索引失效。
   6. 最：不符合最左前缀原则。
   7. 快：全表扫描更快。如果数据库预计使用全表扫描比使用索引更快。

11、Binlog、redolog、undolog

1. Binlog：记录的是所有数据库表结构变更（create、alter table）以及表数据修改（insert、update、delete）的二进制日志，主从数据库同步用到的都是binlog。
2. Redolog：有了redolog，InnoDB就可以保证及时数据库发生异常重启，之前提交的记录都不会丢失。
   1. 记录更新的时候，innodb引擎会先把记录写在redolog中，并更新内存，同时InnoDB引擎会在空闲的时候将这个操作记录更新到磁盘中。
   2. 如果更新太多redolog处理不了的时候，需要先将redolog部分数据写到磁盘，然后擦除redolog部分数据。
3. Binlog和redolog区别：
   1. Redolog是InnoDB引擎独有，binlog是server层实现的，所有引擎都可以使用。
   2. Redolog是物理日志，记录在某个数据页上做了什么修改，binlog是逻辑日志，记录的是这个语句的原始逻辑。
   3. Redolog是循环写的，空间固定会用完；binglog可以追加写入的，不会覆盖之前的。
4. Undolog：一般是逻辑日志。在每条记录更新的时候都会进行记录。

12、MVCC：基于“数据版本”对并发事务进行访问的。

1. 在MySQL InnoDB引擎下RC、RR基于MVCC进行并发事务控制的。
2. Undolog版本链（trx_id记录的是最后一次进行更新事务的编号，DB_ROLL_PTR是一个指针信息，指向上一次进行版本变化的时候数据是什么，最开始的数据是没有trx_id和DB_ROLL_PTR）。
3. Unolog版本链是不会立刻删除的，MySQL确保版本链数据不再被其他并发事务引用的时候再删除。
4. Readview就是“快照读”sql执行的时候MVCC读取数据的依据。

13、MySQL中的锁：全局锁、表级锁、行锁、间隙锁

1. 全局锁：对整个数据库实例加锁。典型场景：做全库逻辑备份
2. 表级锁：表锁和元数据锁
   1. 表锁（lock table … read/write）。InnoDB支持行锁的引擎一般不会使用，毕竟锁住整个表影响面太大。
   2. 元数据锁：不需要显示使用，在访问一个表的时候回被自动加上，作用：保证读写正确性。记住：读读不互斥，读写、写写是互斥的。
3. 行锁：行锁是在引擎层由各个引擎自己实现的。InnoDB支持行锁。
   1. InnoDB事务中，行锁是需要的时候加上的，但并不是不需要的时候就立刻释放，是要等到事务结束的时候才释放，这就是两阶段锁协议。
   2. 两阶段锁：其实就是一个事务在对某一行进行更新的时候，只有commit之后，其他的事务才能进行更新，这就是两阶段锁。并且MVCC也使用到行锁，如果只是单纯靠MVCC，那么在事务B在更新前后得到的readview是不一致的，但是此时隔离级别又是RR的，所以产生冲突，个人理解是因为此时事务A还没有进行commit，那么事务B在更新之后是会被阻塞的，更别说什么select了，所以前后的readview还是一致的。
4. 间隙锁：锁定一个范围，但不包括记录本身（锁粒度比行锁更大一些，锁住了某个范围内的多个行，包括根本不存在的数据）。目的：防止同一个事务的两次当前读出现幻读，也为了让其他事务无法再间隙中新增数据。

14、当前读和快照读

1. 快照读就是最普通的查询语句（select…）
2. 当前读是指执行下列语句的时候进行数据读取的方式：insert、update、delete、select… for update（加排它锁）、select … lock in share mode（加共享锁）。

15、乐观锁和悲观锁：

1. 乐观锁：假设数据一般情况下不会造成冲突，只是在更新的时候判断再次期间别人是否做了更新。适用于读操作多的场景，可以提高程序的吞吐量。
   1. 实现：
      1. CAS
      2. 版本号控制
   2. 优点：读多写少并发场景下，可以避免数据库加锁的开销，提高DAO层的响应性能。
   3. 缺点：写多读少情况下，会导致CAS空旋，开销比悲观锁还大。
2. 悲观锁：假设读取数据的时候默认其他线程会更改数据，因此需要进行加锁操作。
   1. 分类：共享锁（读锁），只能读不能改；排它锁（写锁）：不能与其他锁共存，如果一个事务获取了一个数据行的排它锁，其他数据就不能在获取改行的其他说，包括写读锁和写锁，获取排它锁的事务是可以对数据行读取和修改的。
   2. 优点：适合写多杜少的并发环境，做到数据的安全性。
   3. 缺点：加锁增加系统开销，数据处理吞吐量低。

16、InnoDB和MyISAM的区别：

1. InnoDB支持事务，MyISAM不支持。
2. InnoDB支持外键，MyISAM不支持。
3. InnoDB支持表、行锁，MyISAM只支持行锁。
4. InnoDB必须要有主键，MyISAM可以没有。