MySQL杂谈

联结类型

https://www.cnblogs.com/canger/p/9760217.html

join：笛卡尔积
left join：左连接
right join:右连接
左独有
右独有
inner join：内连接
full join：左连接+union+右连接
左独有uniuon右独有

三大范式

第一范式：每一列不可再分，具有原子性
第二范式：每列都和主键相关(如订单表和商品表)
第三范式：在第二范式基础上，通过外键关联不同表

需求>性能>范式

面试题

1、为什么InnoDB表必须有主键，并且推荐使用整型的自增主键？
用户可以指定主键，如果用户不指定，InnoDB使用这个表的某个唯一字段列作为主键，如果表中没有唯一列，InnoDB生成一个隐藏列作为主键列，默认是按照递增顺序
InnoDB表是按照主键索引进行组织的，底层索引数据结构是B+树，
在主键索引B+树中，非叶子节点存储主键值，叶子节点存储主键值和行数据，
在二级索引B+树中，非叶子节点存储对应的索引键值，叶子节点存储索引键值和对应行记录的主键值，
可以看出整个InnoDB的索引都是围绕主键展开的。
使用自增主键，主要考虑读写效率，比如插入一行数据，只要在最后一个叶子节点后面插入即可，如果主键是不规则的，可能导致频繁的B+树节点分裂降低效率
使用整型的自增主键，有利于提高查找效率，因为整型值之间的比较效率高于字符串值之间的比较
1）InnoDB的每一张表都有主键索引树，可以有其他索引树比如唯一索引树，普通索引树，围绕主键展开；
2）主键自增，目的是提高插入的性能，直接在最后一个叶子节点插入，减少页分裂；
3）整型的主键，目的是为了提交比较的效率，相比字符串比较

2、为什么非主键索引叶子节点存储的是主键值？(一致性和节省存储空间)
假设不存储主键值，存储整个行记录，会增加维护B+树之间数据一致性的成本；
另外，存储主键值也可以减少存储空间

3、MyISAM的B+树与InnoDB的B+树的区别？
1）MyISAM索引与数据是分离的；
2）MyISAM B+树非叶子节点也是存储索引键值，但是叶子节点存储的是指向行记录的指针；
3)InnoDB B+树非叶子节点存储索引键值，主键索引B+树叶子节点存储行数据，非主键索引B+树叶子节点存储主键值

4、为什么InnoDB表数据文件和索引文件是同一个？
因为InnoDB表的主键索引B+树叶子节点存储的是整个行记录

5、索引是什么？
索引是帮助MySQL高效获取数据的排好序的数据结构
索引存储在文件里
索引常见有二叉树、红黑树、HASH、BTREE

6、B+树对B树做了什么改进？
B+树中非叶子节点不存储数据data，只存储key，可以增加节点的度
叶子节点不存储指针，存储的是顺序访问指针

7、B+树节点、页、行记录的关系？
B+树节点一般就是一个页大小，InnoDB的主键索引叶子节点页存储若干行按照顺序存储的行记录

8、MySQL为什么不用散列表、平衡二叉查找树、跳表实现索引?
散列表不支持区间查找
平衡二叉查找树中序遍历是升序排列，但也不支持区间查找
跳表可以代替B+树，查找、插入、删除时间复杂度都是O(logn),支持区间查找

9、怎么改造平衡二叉树使其支持区间查询？
节点只作为索引存储数据键值，叶子节点通过双向链表连接在一起

10、平衡二叉树已经改造成支持区间查询，但还有什么需要改进？
数据量太大会导致平衡二叉树非常占用内存空间，所以需要持久化到磁盘，而程序读写磁盘是通过操作系统的文件系统，文件系统读写磁盘的基本单位是页，磁盘读写的单位是扇区，
文件系统的页就是连续多个扇区，而MySQL的数据页是文件系统页的整数倍，一个页对应一个节点，如果平衡二叉树的高度太深，会导致需要进行多次磁盘IO，
所以改进方法是把二叉树改成m叉树

11、索引的优缺点？
缺点：插入数据为了保证m叉树的平衡，需要进行页分裂(节点分裂)；删除数据频繁会导致页的合并

12、索引常见类型？
散列表、红黑树(平衡二叉查找树)、B+树、跳表、有序数组(静态数据)、位图(辅助)、布隆过滤器(辅助)

12、索引文件名
InnoDB:frm-表结构文件 idb-索引和数据文件
MyISAM:frm-表结构文件 MYI-索引文件 MYD-数据文件

13、索引失效的常见情况？
违背最左前缀原则(联合索引、字符串索引)
在索引列上进行函数、计算、自动类型转换
联合索引某些情况前面字段列使用范围查询，导致后面列用不到索引
索引列使用！=
索引列使用is null或is not null 
like查询以%开头(不一定不走索引，覆盖索引)
字符串索引列不加单引号
索引列使用or连接

14、MyISAM与InnoDB的区别？
MyISAM不支持事务，InnoDB支持事务；
MyISAM不支持行锁，支持表锁，InnoDB支持表锁与行锁；
MyISAM不支持外键，InnoDB支持外键；
MyISAM索引与数据是分离的，InnoDB的主键索引存储索引键值与行记录，非主键索引存储索引键值和主键；
MyISAM如果数据文件之间没有缝隙，插入可以与查询并发运行，也就是说频繁的select和insert业务用MyISAM比较好；
Myisam存储记录的行数，count函数不带where条件性能比较好

15、B树与B+树的区别？
共同点：都是多叉树，叶子节点都是最后一层；
不同点：B树是普通的多叉树，每个节点都存储数据和指针；B+树非叶子节点只存储索引键值和指针，叶子节点存储索引键值和数据

索引优化

联合索引：多个字段联合起来组成一个索引，主键也可以是多个字段

覆盖索引：当前索引树已经包含所有要查询的列，比如id是主键，name是索引，通过name索引查询id或者id，name

最左前缀原则(定位记录)：联合索引或者字符串类型的索引查询的一个原则通过where条件查询要查询最左边的列

索引下推(利用最左前缀定位记录后，减少回表)-MySQL5.6
比如一条SQL select * from tuser where name like '张 %' and age=10 and ismale=1;,通过
最左前缀原则定位到第一条记录后，没有索引下推的时候是拿到一条记录就回表一次，使用索引下推后，
先过滤掉不满足查询条件的记录，减少回表的次数

MVCC

MVCC-多版本并发控制

并发控制
1、独占锁
2、读写锁(读读并行)
3、数据多版本(读写并行)

InnoDB每行记录都有一个事务id，并且有指向上一个版本记录的指针，
即通过某一行记录，可以得到它的上一个版本，这里的上一个版本实质
存储在undo log中，update/delete记录的是行的完整记录，记录修改前和删除前的记录，insert记录的是行的主键值，
比如现在有一行记录版本是V3，上一版本的指针指向update记录，那么V3上一个版本V2就是update这行记录，
根据这一行update记录，同理可以知道再上一个版本的记录。如果一行记录的上一个版本记录的指针指向insert记录，
它的上一个版本就是删除这一行

上面已经可以根据每一行记录信息和undo log得到之前的所有版本，那么MVCC快照读怎么判断哪些版本对于本事务是可见的？
也就是MVCC的一致性视图(consisent read view)怎么实现？
答：事务数组+高水位。事务数组由当前事务启动时候已经启动的但是还没提交的事务组成，高水位是当前最大事务id+1，
然后通过行记录的DB_TRX_ID字段判断当前行记录是否可见，如果不可见那么通过DB_ROLL_PTR指针找到它的上一个版本，直到找到可见版本为止
如何才是可见的？
一个数据版本，对于一个事务一致性视图，自己的更新是可见的，还有下面3种情况：
1、版本未提交，不可见；
2、版本已提交，但是在视图创建后提交，不可见；
3、版本已提交，而且是在视图创建前提交，可见

比如现在事务的最大id是8，当前事务id是7，id为1,3，5的事务已经提交，所以这个事务的视图数组为[2,4,6,7,8],高水位为9，
那么id为1的事务是可见的，视图数组是有间隙的，判断一行是否可见，看row trx_id,比如有一行记录的trx_id为5，进行下面判断：
1、判断是否大于等于高水位，结果没有，进行2判断；
2、判断是否小于低水位2，结果没有；进行3判断；
3、发现5不在视图数组里，证明是已经提交的记录，可见

隔离级别

串行化隔离级别
1、普通select会加S表锁，相当于select lock in share mode
2、select for update和insert/update/delete会加X表锁


读未提交隔离级别
1、普通select不加锁；
2、select lock in share mode、select for update和insert/update/delete会加锁
读未提交隔离级别产生脏读原因
答：普通select不加锁，快照读也是不加锁，但是快照读是一致性读

读已提交隔离级别
1、普通select是快照读，每次select生成新的快照
2、select lock in share mode、select for update和insert/update/delete是当前读，加锁
3、没有GAP锁

可重复读隔离级别
1、普通select是快照读，整个事务只有一个快照
2、select lock in share mode、select for update和insert/update/delete是当前读，加锁
3、GAP锁

不同隔离级别实现原理
https://cloud.tencent.com/developer/article/1352976