深入理解mysql

深入理解mysql

1.概述

mysql是关系型数据库有三规范，列数据的不可分割，每个表要有唯一性，创建主键id,表中不能有其他表非主键字段。

但是工作中我们常常回反三范式，比如冗余其他表的字段，减少连接查询的次数；甚至会冗余某个sql的数据，用一个冗余表来存放；是典型的空间换时间。

2.事务

2.1四大特征

原子性，事务不可分割，要么同时成功要么失败

一致性，如果失败，那么事务就会进行回滚

隔离性，事务开始后，不能受其他事务的干扰

持久性，事务开启了，就不能终止

2.2隔离级别

1.读未提交 会出现的问题：脏读 不可重复读 幻读

2.读已提交 会出现的问题：不可重复读 幻读

3.可重复读 会出现的问题：幻读

4.串行化

脏读：读到事务还未提交的又修改后的值，事务回滚，修改后的值也回滚发生了脏读；a事务吧a字段的100改为50，其他事务读取a的值为50，a事务进行回滚操作，a字段的值回滚为100，在其他事务已经读了之前的数据，认为是50，发生了脏读。

不可重复读：在同一个事务内对同一个数据，读取两次，得到值不一样，期间有其他事务提交了修改操作。

幻读：在同一个事务内对同一个数据数量，读取两次，得到数量值不一样，期间有其他事务提交了添加或者删除操作。

2.3大事务优化

• 查询等数据准备操作放在事务之前
• 远程调用设置超时时间，防止事务等太久
• 修改加锁操作尽量放在最后
• 能异步的尽量异步

2.4mvcc原理

1. 每个事务进行修改操作时，都会生产一个undolog回滚日志；

2. 这个日志有事务id和回滚指针来连接起来，然后在可重复读的事务隔离级别下，事务查询查询操作还会生成一个redview视图，里面就是一堆事务id组成，由所有未提交的事务id和已创建的最大事务id组成

3. 然后就是一次往上对比undolog日志的事务id，如果小于最小事务id，就是可见，大于最大事务id就是不可见，在最大和最小事务中间的，看看有没有在视频组中，在就是不可见的，不在就是可见的。

2.5buffer pool

mysql的缓存，减少对磁盘io的次数，mysql在查询或修改时，会先从buffer pool读，没有才会去从磁盘读取。

在修改和新增时，mysql不会直接对磁盘操作，而是先修改buffer pool，然后异步写入磁盘。

2.6修改sql执行流程

加载buffer pool缓存数据 -> 添加undolog回滚日志 -> 更新缓存数据 -> 添加redolog日志 -> 写入binlog日志 -> 异步更新到io磁盘

3.索引

3.1存储引擎

myisam：数据文件和索引是分开的，不支持事务，不支持外键，支持全文索引；适合对事务要求不高 添加和删除多

innodb：数据和索引放在同一个文件，支持事务，必须要有主键

3.2结构类型

b树：就是在红黑树的基础上，把每个节点 存储更多的索引。降低红黑树的高度。查找的时候，如果第一排没有，就会从两个索引相近的区间查找，一直下去直到找到 一般就只有2到4行

b+树:b树的区别就是，只有最后的叶子节点 才会放索引和索引对应的值，其他的非叶子节点只放索引不放值(主键索引放的是那一行的数据，普通索引放的是那一行的主键id)，这样非叶子节点就能存放更多索引，叶子节点还会去冗余非叶子节点的索引，然后叶子节点的值于值之间是有指针连接的 ，值是排好序的，方便范围查找

hash:通过hash运算计算出位置，只需要一次io，但是不支持区间查询，所以基本不用。

3.3索引类型

主键索引 也是聚簇索引,不需要回表，一个表只有一个，然后是唯一的，不能为空

普通索引 如果没有覆盖索引，会有一次主键id回表查询的操作

唯一索引 可以为空但是不能重复，

复合索引 一个索引建立多个字段，遵循最左匹配原则

3.4索引下推

在mysql5.6版本以后，首先通过普通索引查找数据，正常情况下应该是会通过主键id回表，但是如果还有其他查询条件，且在这个普通索引中，那么还会进行一次过滤，减少回表的次数，一般在联合索引中最常见。

3.5sql优化

联合索引：

1. 经常使用范围查询的字段，建在最后面；

2. 对于基数打的字段，如果不进行条件过滤，但是想要命中后面的索引字段，可以在过滤条件为空的情况下使用in所有，比如 where 性别 in （男，女，未知）；

3. 对于某个经常使用的返回查询，可以新建一个字段来定义，如果 最近7天登录，之前是 where 登录时间 <= 7天，可以改成 where 是否7天登录 = true，是否7天登录字段可以通过定时任务来刷。

关联查询：

1. 小表驱动大表，小表是指过滤数据后少的表。
2. 关联字段查询加索引
3. 不建议使用多关联查询，可以换个思路来优化；如冗余字段 冗余表 空间换时间的方式

count数据量太大（只能满足非条件查询的总数）：

1. 用redis专门做一个缓存计数器
2. 用mysql表来记录

4.日志

4.1undolog

回滚事务，一般在于innodb的情况下开启事务，然后做修改操作的时候会添加一个undolog日志，一般用于回滚

4.2redolog

一般在于innodb的情况下开启事务，然后做修改操作的时候会添加一个redolog日志，主要记录数据页的物理修改，一般用于事务提交后，但是还没同步到磁盘，就宕机了，就可以通过redolog恢复。

4.3binlog

一般做修改操作的时候会添加一个binlog，记录修改的sql，日志添加顺序是undolog-redolog-binlog,mysql5.7版本默认是关闭这个设置的，是不会添加binlog，8.0默认开启，一般用于数据恢复，或者主从复制。