mysql笔记

1.索引

索引是帮助数据库获取数据排好序的数据结构
索引数据结构：
1.二叉树
2.B树
3.红黑树
4.Hash表

mysql大部分采取的索引结构是B+树，B+树是B树的变种。
B+树有以下几个特征：
1.非叶子节点不存储数据，只存储索引
2.叶子节点包含所有索引字段
3.叶子节点通过指针链接，提高区间访问性能
4.所有节点有序排列

选B+树而不是B树的原因就是因为它的非叶子节点不存储数据，这样有更多的空间存储索引，从而减小树的高度，提高了查找效率

2.存储引擎

常用的是innodb，比较常见的是innodb和myisam

myisam的B+树的叶子节点存储的是数据的地址（非聚集）

innodb的B+叶子节点放的是一整行数据（聚集）

思考：为什么innodb表建议用整型的自增主键？
因为b+数的节点是有序的，如果是整型自增的主键索引对比UUID这种字符串型主键，则排序简单，效率更高，空间占比更小

联合索引，根据下图索引在B+树的存储结构，可以发现索引是需要符合最左匹配原则的（因为他会按顺序从左到右找）

3 sql优化

1.让返回的字段尽量少，避免mysql需要扫描所有索引、或者mysql认为扫描所有索引不如扫描全表从而不走索引
2.join表关联的字段尽量加索引，小表驱动大表（左连接 左边的是驱动表，右链接 右边的是驱动表）
3.count(*)和count（1）效率最高（mysql有优化）>count(索引）>count（id/主键
4.单表中数据超过500w行或容量超过2G，建议分库分表
5.in操作能避免就避免，若果非要用，in后面集合的数量控制在1000之内
详细可以看一下阿里巴巴手册

4 mysql事务隔离

一致性，隔离性，持久性，原子性
隔离性是由mysql的锁和MVCC机制实现。

mvcc的读可以粗略理解为读的快照数据，当前事务的数据存在一个快照版本（undo.log日志）

undo是回滚日志，保证事务的原子性（要么成功要么失败——>失败就回滚）

能看到undo的哪些日志，是通过可见性算法实现的（根据事务id和undo日志绑定的trx_id）
mysql默认事务级别是可重复读（repeateable read）

update操作会有行锁

INNODB 日志

mysql5.7 有查询缓存，8.0没有（因为这个缓存很鸡肋，除非数据没有修改且查询语句一模一样）
执行器记录日志
undo日志 ：记录了回滚sql
redo日志：记录在ibd磁盘哪一页做了什么修改（物理修改）支持顺序写，效率非常高
binlog归档日志：记录数据被改成了什么（逻辑修改）
先写redolog后刷新磁盘的机制叫做WAL机制

开始事务——>修改pool buff——>写入redo log buff——>写入page cache——>写入redo log磁盘——>提交事务

redo log的写入机制有三种策略
innodb_flush_log_at_trx_commit参数控制
设置为0 效率最高，事务提交时只写入buff中，由于不写入page cache 也不写入磁盘，所以一旦宕机就可能数据丢失
设置为1 效率最低，事务提交时会写入磁盘，所以宕机数据不丢失
设置为2 效率中等，事务提交时写入page cache，所以只要操作系统不宕机，则数据不丢失

binlog mysql5.7默认关闭，mysql8.0默认开启
binlog写入磁盘的机制主要通过sync_binlog参数控制，默认是0
为0时，提交事务写入page cache，什么时候写入磁盘由操作系统控制，可能丢失数据
为1时，提交事务写入磁盘
设置为N（N>1）提交事务时写入page cache，但是累计N个事务后会写入磁盘