Mysql进阶篇

1.Mysql服务架构

连接层: 处理客户端连接请求,对用户进行认证
服务层: 可以接收sql,调用存储过程,优化sql,缓存数据....
引擎层: 负责实际与文件层进行交互操作的,可以有不同的引擎选择.
物理文件层: 存储表数据以及各种日志文件.

2.Mysql引擎

存储引擎就是存储数据，建立索引、更新 / 查询数据等技术的实现方式。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能

InnoDB

mysql的默认存储引擎，支持事务处理，有外键约束，支持索引，支持缓存，支持行级锁，遵循ACID原则

每个表都会对应一个表空间文件，用来存储表的结构信息，生成一个ibd文件

MyISAM

MyISAM 是 MySQL 早期的存储引擎。不支持事务，不支持行级锁，只有表锁而且并发量小，不支持外键但支持全局索引

myisam的一张表在磁盘中存储文件有以下三个：.sdi存储表结构，.myd存储数据，.myi存储索引

Memory

表数据存放在内存中，Memory 的一张表在磁盘中的存储文件只有：xxx.sdi，用来存储表结构文件。和 MyISAM 引擎相比，没有了存储数据和索引的文件，因为他们在 Memory 中都被存储在内存中了。

总结对比

事务：MyISAM不支持。InnoDB支持

锁级别：MyISAM只支持表级锁，InnoDB支持行级锁和表级锁，默认使用行级锁，但是行锁只有通过索引查询数据才会使用，否则将使用表锁。

主键和外键：MyISAM 允许没有任何索引和主键的表存在，不支持外键。InnoDB的主键不能为空且支持主键自增长，如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键，支持外键完整性约束

存储结构：innodb数据和索引都放在表空间中，所有的表都保存在同一个数据文件中，innodb的表大小只受限于操作系统文件的大小，一般为2GB

使用场景：如果需要支持回滚事务，崩溃恢复能力的ACID事务，并要求实现行级锁并发控制，选择innodb。

如果数据表主要用来查询记录，读操作远多于写操作时且不需要使用事务，可以选择MyISAM，但在8.0以后该引擎已经被废除

3.数据库范式

第一范式（1NF）：指表的列不可再分，数据库中表的每一列都是不可分割的基本数据项，同一列中不能有多个值；第二范式（2NF）：在 1NF 的基础上，还包含两部分的内容：一是表必须有一个主键；二是表中非主键列必须完全依赖于主键，不能只依赖于主键的一部分；第三范式（3NF）：在 2NF 的基础上，消除非主键列对主键的传递依赖，非主键列必须直接依赖于主键。 BC范式（BCNF）：在 3NF 的基础上，消除主属性对于码部分的传递依赖

4.数据库索引

索引的本质就是一种通过减少查询需要遍历行数，依次来提升搜索效率的数据结构，避免了数据库的全表扫描，就好比书的目录，让你能更快的定位所需内容（一个表最多支持16个索引）

索引优缺点

优点

减少了遍历的行数，增加了搜索效率
通过索引对数据进行排序，降低了数据排序的成本，降低CPU的消耗

缺点

创建索引需要占用内存空间
对数据进行增删改时，索引树需要进行更新，需要一定的时间
在某些场景下会出现索引失效的场景

使用场景

建议添加的情况

在主键上添加索引，强制该列的唯一性
外键上添加索引，提高链接速度
在where子句上加，加快条件的判断速度
按范围存取的列或者在group by或order by中使用的列，因为索引已经排序，这样可以利用索引加快排序查询时间。

不建议添加的情况

区分度不高的列，例如性别，结果集占据了表中数据行的很大比例，这样并不能增加判断速度
频繁增删改的表，因为索引树的结构也需要一直改变
表数据过少的列

5.索引的分类

主键索引

为主键生成一个索引，不允许有空值

单值索引

即一个索引只包含单个列，一个表可以有多个单列索引

唯一索引

索引列的值必须唯一，允许为 nul

组合索引

把多个列打包,创建一个索引,降低了空间开销, 例如几个查询条件经常组合使用.

最左前缀原则

在使用组合索引的列作为条件时,必须要出现最左侧列为条件

全文索引

需要模糊查询时，一般的索引会失效，此时需要创建全文索引

 CREATE FULLTEXT INDEX 索引名 ON 表名(字段名) WITH PARSER ngram

聚簇索引和非聚簇索引

聚簇索引：更新聚簇索引列上的数据时，往往导致表中记录的物理顺序的变更，找到索引就相当于找到了数据，例如innodb中的主键索引

非聚簇索引：索引的存储和数据的存储是分离的，也就是说找到了索引但没找到数据，需要根据索引上的值(主键)再次回表查询，所谓回表查询就是先定位主键，再定位行记录

回表查询优化

索引覆盖：把所有需要查询的字段都放到普通索引中，这样普通索引查到的叶子结点中已经能够得到所需的所有字段

减少查询的字段，只查带有索引的字段，例如我们只查询主键id
修改表的索引，增加需要的字段例如查询user表时，把name也加到索引里，实现组合索引

6.索引的实现原理

常见的索引结构有B+树，hash索引

hash索引

mysql中只有memory引擎支持hash索引，查询效率很高可以实现一次定位

缺点

仅能满足等值查询，不支持范围搜索和排序
会发生哈希碰撞，只要发生碰撞就会出现全表扫描

B+树

优点

页内结点不存储数据，每次IO可以读取更多的行，减少IO读取次数
带顺序访问指针，所有的索引数据都存储在叶子节点，每个叶子结点都有指向相邻结叶子结点的指针，提高区间访问效率

7.事务

将多组sql放在一起执行，要么全部成功，要么全部失败

事务特性:ACID

原子性:事务是一个不可分割的部分,要么都成功,要么都失败
持久性:一旦事务提交后,对数据库的修改是永久的
隔离性:事务在操作时,对其他事务的可见程度
一致性:保证数据的完整可靠

8.事务并发问题

脏读:读取到了别人未提交的数据
不可重复读:在进行修改操作后,对同一事务两次读取的结果不相同
幻读:在进行新增或删除操作后,数据量发生改变

9.事务隔离级别

读未提交

一个事务可以读到另一个事务未提交的修改,会造成脏读,不可重复读,幻读

读已提交

一个事务只能读取另一个事务提交后的内容,解决了脏读问题,但没有解决不可重复度,幻读

可重复读

一个事务在同一个读操作中,即使别的事务修改了这个事务读取同一个数据源应该相同

串行化

事务只能一个一个进行,读写阻塞

10.事务实现原理

原子性实现

使用了undolog日志,记录了增删改的反方向操作,当事务想要回滚时,利用undolog中的信息进行恢复

持久性

依靠rodolog实现,在执行时会保存已经执行的sql语句到redolog日志中,为了提高效率,会在写入redolog之前会先写入到内存中的redo log buffer缓存区中,redo log buffer中修改的数据会定期刷新到磁盘的redo log文件中

隔离性(读已提交/可重复读)

innodb的隔离级别由MVCC和锁机制来实现

MVCC:多版本并发控制,是innodb引擎实现事务隔离级别的一种具体实现方式,每次事务对某条数据进行操作时,会生成一个版本链,如果是读已提交那么就会每次从版本链上生成一个快照,再次读取时,读取的就是最新数据,可重复读读取的就是之前版本的快照,实现读取驶距的一致性,也成为快照读

锁机制:事务在修改数据之前，需要先获得相应的锁；获得锁之后，事务便可以修改数据；该事务操作期间，这部分数据是锁定的，其他事务如果需要修改数据，需要等待当前事务提交或回滚后释放锁

11.mysql锁机制

按照粒度分为:表锁,行锁,页锁,间隙锁

表锁:对整张表加锁,实现简单,带来的系统负面影响最小。所以获取锁和释放锁的速度很快。但是并发度一般
行级锁:对索引项进行加锁,不是对记录加锁,只有通过索引检索数据时,innodb才能使用行锁,负责就会使用表锁,锁的粒度小,但是访问不同行数据使用相同的索引键就会发生冲突
页锁:颗粒度介于行级锁定与表级锁之间,并发能力也介于二者之间
间隙锁:获取某个范围区间,表table中有id值为1，2，4，5的数据。

1.事务1在执行：select * from table where id >= 1 and id <= 5;（此时事务1为id值1~5的数据加了锁，包括不存在的id=3的行，对3来说就是间隙锁） 2.那么事务2在执行：insert操作插入id值为3的数据时，就需要等待事务1提交完成；否则会造成事务1的幻读（即2次读取结果不同）。 3.如果id=3的记录存在就不会使用间隙锁（此时where条件全部命中），只会使用记录锁。

行级锁又分为：共享锁和排他锁

共享锁：其他的事务可以进行读操作，但会阻塞写操作，只有读锁释放后，才能执行写操作
排他锁：一个事务获取排它锁之后，可以对锁定范围内的数据行执行写操作，在锁定期间，其他事务不能再获取这部分数据行的锁（共享锁、排它锁），只允许获取到排它锁的事务进行更新数据。

12.mysql日志

binlog:二进制日志是 Server 层生成的日志，主要用于数据备份和主从复制；记录了DML和DDL语句，不记录查询语句

redolog：重做日志是 Innodb 存储引擎层生成的日志，实现了事务中的持久性，主要用于掉电等故障恢复

undolog：回滚日志是 Innodb 存储引擎层生成的日志，实现了事务中的原子性，主要用于事务回滚和 MVCC。