目录
MySQL
连接器 复制和客户端建立连接、获取权限、维持和管理连接
查询缓存 key是查询的语句 value是查询的结果，有缓存就直接返回给客户端 MySQL8.0开始就没有这个功能了，每次数据改变都需要去更新缓存
分析器
词法分析：SQL类型 每个字符串代表的是什么？
语法分析：判断SQL是否符合mysql的语法规则
优化器：决定走那个索引，或者连表的执行顺序是怎么样的（先查主表还是先查连接表）
CBO：基于成本的优化
RBO：基于规则的优化
执行器：先判断是否有该表权限，执行语句
MySQL同步原理
主服务器将数据的改变记录到二进制的binlog日志中，节点服务器会在一定时间内对主服务器的binlog进行探测是否改变，如果已经改变，用一个线程读取主服务器的binlog内容到节点服务器的relaylog中，再由sql线程负责读取relaylog 更新到节点服务器中，从而保证数据一致。
注意：复制最好保证数据库的版本一致
MySQL为什么要主从同步
1.如果在一个情景下，有一个sql需要锁表，就会导致不能使用读操作，会影响业务，使用主从复制，读写分离，主库锁表的情况下，也可以在从库中读取。
2.主备，当主服务宕机时，由备用服务器来代替主服务器提供服务
redolog（只存在innoDB 物理日志）
数据日志如果每次改动MySQL数据的时候都去直接更新，会导致效率很低，所以使用redolog来保存没有持久化的数据，规定时间mysql在进行读取数据进行持久化，同时服务器宕机也能够保证数据不会丢失。
支持4GB大小，多了就得处理并且去覆盖，物理日志
不要也可以，但是为了保证持久性，所以每次数据的改变都需要去刷盘，会产生大量的随机IO，对性能影响很大。
有三个重要的指针
更新指针 redolog buffer写入的最新位置
刷盘指针 redolog buffer已经刷盘的位置
脏页指针 是指 buller poll 那些脏页已经成功刷盘的最新位置
redolog buffer恢复的的时候重放之前的修改，就可以恢复，再根据每个事务的状态来决定是提交还是回滚
binlog（复制\备份+恢复）
server层特有的 逻辑日志
binlog日志当写满一个日志文件之后，会新建一个新的日志文件。
写入机制：事务的执行过程中先把日志写到binlog cache中，事务提交的时候再写到binlog文件中。
一个事务的bin log是不可以给拆开的，无论事务多大都要保证一次性写入（这也是大事务造成主从延迟的主要原因）
每个线程都会有自己的binlog cache 但是共用一份binlog文件（参数 binlog_cache_size 默认是8MB）
- 数据更新步骤 将id=1改为id=2
- 先将id=1信息写到回滚的 undo log中
- 在根据 redo log配置，写到 redo log
- 数据变化SQL写入到binlog日志给与子库同步数据
- 事务提交
- redo log和bin log 才可以保证关系型数据库的ACID特性
innoDB和MyISAM的区别
Myisam（256TB）不支持事务操作、不支持MVCC、支持表锁不支持外键，非聚簇索引。适用于select为主的数据库
Innodb（64TB）支持事务操作、支持MVCC、支持表锁和行锁和聚簇索引（也有非聚簇索引），适用于大量的insert和update操作（高并发）
区别:聚集索引的数据跟索引是放在一起的，非聚集索引是分开的
非聚簇索引：索引文件和数据文件是分开的，按照插入的顺序存储在磁盘上。叶子节点存放的数据记录的行指针。
总结一下，MyISAM 引擎中索引查询的步骤为，先按照 B+ 树查询到叶子节点，如果指定的键值存在，则取出其对应的行指针的值，然后通过行指针，读取相应数据行的记录。
聚簇索引：innoDB的数据文件本身就是索引文件，叶子节点的键值就是表的主键。聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。
聚簇索引的叶子节点都包含主键值，事物id，用于事务MVCC回滚。
结论：
- 聚簇索引:通常由主键或者非空唯一索引实现的，叶子节点存储了一整行数据
- 非聚簇索引：又称二级索引，就是我们常用的普通索引，叶子节点存了索引值和主键值，在根据主键从聚簇索引查
为什么MyISAM比InnoDB快
1.MyISAM只缓存了索引块，减少缓存换入换出的频率
2.MyISAM是非聚簇索引，innoDB是聚簇索引，innoDB的二级索引需要回表查询，而MyISAM所有索引直接指向数据行存储的位置
3.innoDB还要维护MVCC一致；虽然场景没有但是它还是会去检查，MyISAM表锁牺牲了写性能，提高了读性能
数据库的锁
行锁：粒度小，加锁慢，不容易冲突
页锁：加锁的粒度和时间处于行锁和表锁之间，会出现死锁
表锁：粒度大，加锁快，容易冲突
行锁
行锁都是锁在索引上的，如果要锁的列没有索引，会进行全表记录加锁，如果锁的是二级索引，那么一般情况是下是先锁二级索引再锁聚簇索引
- 临键锁（Next-key Lock）：Record Lock+Gap Lock，锁定一个范围(前开后闭)，包含记录本身。记录锁只能锁住已经存在的记录，为了避免插入新记录，需要依赖间隙锁。（记录锁+间隙锁）
临键锁是innoDB的最基本锁单位，某种情况下回退化为记录锁或者间隙锁
- 记录锁（Record Lock）：也被称为记录锁，属于单个行记录上的锁。解决脏读，重复读
- 间隙锁（Gap Lock）：锁定一个范围（双开区间），不包括记录本身。（解决幻读，在事务隔离级别为可重复读的时候生效）
如果要锁id=6的记录，发现记录不存在的时候就会锁定所在区间，避免区间有新的记录插入
- 意向锁：当我们想加表锁的时候需要先去判断每一行是否有行锁，复杂度就会很高。我们可以在加行锁的时候在表级别加上意向锁。在意向锁中只有表级的共享锁和行的共享锁会兼容，其他都会互斥。表锁之间的意向锁都兼容
排它锁(X锁)和共享锁(S锁).
事务对数据加X锁的时候，只能本身事务读取和修改
事务对数据加S锁的时候，其他事务只能对数据加S锁不能加X锁，直到事务释放S锁
S锁也称为读锁
场景
待消化
案例三：主键索引范围锁
死锁：
- t1时刻：T1事务select加锁过程：默认加NextKey（临建锁）锁，但id=3不存在，退化为GAP锁
- t2时刻：T2事务同样select获取到GAP锁，因为GAP锁是共存锁
- t3时刻：INSERT加锁过程：因为其他事务有GAP锁，需要加插入意向锁，等待T2释放
- t4时刻：因为其他事务有GAP锁，需要加插入意向锁，等待T1释放，死锁
- T1事务在等待插入意向锁，T2拥有GAP X锁，同样等待插入意向，导致死锁吗，选择T2进行回滚（选择行锁数量少的事务进行回滚）
(129条消息) 深入MySQL死锁场景_TechingOn的博客-CSDN博客_mysql死锁场景
索引的分类
- 主键索引：唯一的标识，主键不可重复
- 唯一索引：可以有多个唯一索引
- 联合索引：可以覆盖多个数据列
- 普通索引：index、key来标识
- 全文索引（FullText）：快速定位数据
原则：小数据量不要加索引，索引一般加载常用来查询的字段上，索引不是越多越好。
MySQL每次只使用一个索引，与其说 数据库查询只能用一个索引，倒不如说，和全表扫描比起来，去分析两个索引 B+树更耗费时间，所以where A=a and B=b 这种查询使用（A，B）的组合索引最佳，B+树根据（A，B）来排序。
说一下innoDB的索引结构
Hash索引（适用于等值查询），b+树索引（适用于范围查询），全文索引，空间索引
索引的好处和坏处
好处： 1.提高数据的检索效率，降低数据库的io成本。
2.减低数据的排序成本。
缺点：1.占用物理存储空间。
2.随着数量量的增大，索引的维护成本也随之增大。
3.发生数据的修改操作时，需要维护。
为什么索引结构默认是b+树索引 *
- hash：虽然可以快速定位，但是没有顺序，io复杂度高
- 二叉树：树的高度不均衡，不能自平衡，查找效率不高
- 红黑树：树的高度随着数据量的增加而增加，io代价大
- B+树：节点间是有序的，可以存取多个数据。叶子节点之间存储其他的节点，方便全表扫描
B树跟B+树
B树是一个节点可以存储多个数据的，而且数据已经排序。
B+树拥有B树的特点，叶子节点之间存在指针，叶子节点存了所有的元素的，而且排好序。（适用于范围查找，查找效率稳定）
B树
B+树
为什么官方建议使用自增长主键作为索引。
结合b+树的特点，自增逐渐是连续的，每次插入都是插入到最后，减少分裂和移动的频率。
索引的查询原理
c1和c2都是索引列的情况下，优化器只会选中其中一个作为查询条件，比如在二级索引通过c1=1过滤出两条记录，得到两条数据的主键1和2，然后再去回表查询两行记录，最后在server层过滤c2=1的记录
大部分情况下MySQL只会使用一个索引列 联合索引解决
怎么去识别我们写的SQL有没有走索引
在查询sql前面加一个explain
type 性能类型
possible_keys sql查询可能使用的索引
key sql查询实际使用的索引
key_len 索引的长度，原则上越短越好
Extra sql执行分析
使用索引的注意事项
MySQL每次只使用一个索引，与其说 数据库查询只能用一个索引，倒不如说，和全表扫描比起来，去分析两个索引 B+树更耗费时间，所以where A=a and B=b 这种查询使用（A，B）的组合索引最佳，B+树根据（A，B）来排序。（where b=0 and a=1 底层的优化器会让其走索引）
- 主键，unique字段
- 和其他表做连接的字段需要加索引
- 在where 里使用 >, >=, = ,
- 使用不以通配符开始的like，where A like ‘China%’
- 聚合函数里面的 MIN()， MAX()的字段
- order by 和 group by字段
- 索引列较长的截取变量部分，独立列进行存储，比如说手机号可以将末尾六位（只要唯一）作为索引
索引失效
- 搜索一个索引而在另一个索引上做 order by， where A = a order by B，只会使用A上的索引，因为查询只使用一个索引。
- 联合索引（abc）只能通过条件a、ab、ac、abc
- 字符串不加单引号（类型隐式转换）
- or前后两个条件，只要一个没有索引就会失效.
- 尾部模糊查询，索引不会失效，头部模糊查询会导致索引失效（覆盖索引除外）
- 索引列有运算或者使用了函数，函数upper()等，or、！ = （<>）,not in 等
索引下推
简称ICP,也就是把查询中使用索引有关的查询条件下推到存储引擎中。索引条件下推是为了减少回表次数，也就是减少IO操作
SELECT * from user where name like '陈%' and age=20
按最左前缀原理5.6之前是需要做两次的回表 先是根据name查询得到id，再根据id去做一次age的回表查询
5.6之后在索引的内部（server层）就判断看age是否等于20，不等的直接跳过，只需要回表一次
MVCC
MVCC 是一种多版本并发控制，为每一次修改都保存了一个版本基于undo版本链+ReadView来实现的
原理：我们保存在数据库的每条字段都有两个隐藏的字段，一个是trx_id（事务id）和roll_pointer（指向更新这个事务之前的undo log）Undo log版本链：
ReadView机制：
事务
事务具有原子性、一致性、隔离性、持久性（ACID）
- 原子性（undolog记录需要回滚的数据信息）、一致性：要么都执行，要么都不执行回滚。
- 一致性：基于其他三大特性实现的
- 隔离性：一个事务的执行不能受其他事务的干扰基于行锁和MVCC（多版本并发控制）
- 持久性（redolog记录修改时的日志数据）：一但提交数据库中的数据就会永久改变
事务带来的问题：
脏读：读取到其他事务（修改）未提交的数据，称为脏数据
丢失修改：指一个事务修改的同时另一个事务也进行修改，会导致第一个事务修改的结果丢失。（乐观锁）
不可重复读：指一个事务多次读取同一数据，读取的期间有人修改了数据会导致数据不一致的问题。
幻读：一个事务在读取表的数据的同时，有另一个事务插入了数据，事务1发现多了之前没有查询到的数据，好像发生了幻觉一样。
事务的隔离级别：
读未提交：允许读取未提交的数据，导致脏读、幻读、不可重复读
读已提交：允许读取已提交的数据，可以阻止脏读
可重复读： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读
序列化： 所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰。（临建锁解决幻读）
innodb是怎么解决幻读的
将隔离性调到串行化
主要是靠临建锁解决的幻读，第一次读和第二次读的数据不一样就是幻读，调整到串行化后，所有的读都会加共享的临建锁。
分布式事务两阶段加锁（提高并发）
在一个事务里边只有在提交（commit）/回滚（rollback）是在解锁，其余的时间都是在加锁阶段。
对于数据库来说着两个方案的结果都是一致的
但是根据时序可以看出方案二锁住库存的时间更短，库存作为热点，将其放到最后会大大的提高并发，减少库存表锁的时间
避免死锁：产生的原因就是加锁顺序不一致导致的
(125条消息) Mysql事务隔离机制jerry_dyy的博客-CSDN博客mysql事务隔离机制
规范
表规范：单表行数超过500万行或者表容量超过2GB进行分库分表
分库分表：水平拆分就是一表-->维度区分多表、多库 垂直拆分就是表-->字段区分多表、多库
索引列较长的截取变量部分，独立列进行存储，比如说手机号可以将末尾六位作为索引
sql调优
深度分页时，需要注重分页效率，偏移量过大时加上where子查寻提高效率
例如：分页从第1000条开始直接分页查询效率会比较慢，
加上where id>1000 order by id limit 0 10
count(*) ≈ count(1) > count(id) > count(普通索引列) > count(未加索引列)
优化order by效率，建立联合索引
连表尽量使用join 优化器会有限选择小表
group by会默认排序，如果不需要排序就加上order by null
1882
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
