记录下自己面试遇到的数据库相关问题

Mysql体系结构

Connector：JDBC，ODBC
管理服务和工具组件：管理系统和控制工具
SQL接口：接受用户的sql命令，返回结果
查询解析器：接受的sql会被查询解析器解析，判断权限和语法结构
查询优化器：sql语句在执行前会被优化
缓存：查询缓存中如果有命中的结果，查询语句可以直接从缓存中拿到数据
插入式存储引擎：MyISAM，InnoDB

数据库范式

码：能够唯一表示一条数据的属性或属性集合。
非主属性：不包含在任意一个候选码
主属性：与非主属性对立
一范式：列不可拆分
二范式：在一范式的基础上，消除非主属性对码的部分函数依赖
三范式：在二范式的基础上，消除非主属性对码的传递函数依赖
BC范式：在三范式的基础上，消除主属性对码的部分和传递函数依赖

索引

为什么索引：

如果不适用索引，在执行数据库查询时，会扫描数据库所有行，然后将符合筛选条件的数据进行排序并生成一张新的临时表进行返回，这个过程会涉及到临时表的创建和排序，当记录行数过多时，大大影响范围查询的效率。
添加索引后，叶子层有序，并且连续（B+）。叶子层有序使得已经排序，叶子层连续符合局部性原理，差某个数据，接下来大概率访问相邻的数据，节点间链表连续，节点内物理地址连续，避免磁盘不同扇区地址的随机寻址，磁盘顺序读取时效率很高，只需要旋转，不需要寻道（大部分情况下）。磁盘预读，预读长度为页的整数倍，数据库将一个节点的大小设置等于一个页，每个节点只需要一次I/O就可以完全载入。内存的分页机制（引申页面置换算法：最佳置换算法（淘汰未来最久不会被用到的页面，无法实现）；先进先出；最近最久未使用（LRU）；简单clock置换算法（类似垃圾回收机制中的标记，用过的页面标记1，置换时，为0的清除，为1的变为0===》改进考虑是否被修改，优先淘汰未被修改且未被访问。）；页面缓冲算法（PBA修改的页面要写回磁盘，最好一个队列，把弹出的修改页面挂起，一起写回硬盘，避免硬盘频繁切换））。

索引优势：

1.大大减少服务器需要扫描的数据量
2.帮助服务器避免排序和创建临时表
3.可以将随机I/O变为顺序I/O，磁盘I/O加快

索引缺点：

1.索引本身仪表的形式存储，因此会占用额外的存储空间
2.索引的创建和维护需要时间成本，随着数据量的增大而增大
3.索引会导致数据修改操作的效率变低，因为同时需要维护索引

索引分类

逻辑角度：单列索引（细分为主键索引、唯一索引、普通索引）；组合索引；全文索引；空间索引
主键索引：不允许重复，不允许为空
唯一索引：不允许重复，允许空
普通索引：无限制
全文索引：用大文本对象的列构建的索引
组合索引：多个列组合构建的索引，多个列不能为空，最左前缀原则（考虑根据多个键遍历B+树）因此查询最频繁的索引放在最左（组合索引相当于建立了，col1的单列索引，col1，col2的组合索引，col1，col2，col3的组合索引）。
空间索引：对空间数据类型的字段建立的索引，底层可通过R树实现，使用较少

哈希索引：

优点：O（1）查询单条数据速度快
缺点：
1.不支持范围查询，范围查询时n*O（1）相当于O（n），另外，由于地址不连 续的问题，导致内存页面频繁置换，因此不如B+树
2.无法避免数据的排序操作，因为哈希散列之后数据没有顺序关系
3.不支持部分索引列的匹配查找，由于利用全部索引列的内容进行哈希散列，调 用时也必须同时使用所有列，不能进行部分查询
4.无法避免表扫描；出现哈希冲突，需要遍历链表（拉链法）
5.冲突很多的情况下，维护的代价很高，性能不一定比B数索引高

BTree索引：

设计扁平M阶B树，避免索引过大，如果索引过大访问索引就意味着也需要磁盘IO
M阶B树定义：
1.每个叶子节点高度一样
2.非叶子节点至少m/2子树， m/2-1关键字
3.非叶子节点之多m棵子树，m-1关键字
4.叶子节点指针为空

B树索引的问题：
1.无法范围查询
2.数据量大时，高度依旧增加
3.查找时到了某个节点，需要把节点数据从磁盘加载到内存，在节点内查询

B+树索引

M阶B+树定义：
1.节点之多m棵子树，m个关键字
2.至少m/2向上取整子树，关键字相同
3.叶子有链且有序
4.节点是子树最大值的一个copy

与B树的不同：
1.非叶子节点只存储键值信息，不再存储数据。（有效减少索引大小）
2.所有叶子节点形成链表 ，方便范围查询
3.数据都存在也叶子节点中

B+树优点
1.IO次数更少，非叶子节点数据少，意味着一个页面内可以存更多的数据索引
2.范围查询方便，利用链表
3.查询性能稳定，都是到了叶子节点才拿到数据

物理存储

聚簇索引：
聚簇索引的主键索引的叶子结点存储的是键值对应的数据本身；辅助索引的叶子结点存储的是键值对应的数据的主键键值。（主键索引到了叶子拿到一行数据，辅助索引到了叶子拿到主键，再利用主键索引找到行数据）

非聚簇索引
非聚簇索引的主键索引和辅助索引几乎是一样的，只是主索引不允许重复，不允许空值，他们的叶子结点都存储指向键值对应的数据的物理地址。

索引优化

1.独立的列。索引列不能是表达式的一部分，也不能使函数参数。
2.前缀索引和索引选择性：选用长前缀保证选择性，但是也不能太长节省空间
3.尽量使用覆盖索引（select结果从索引中能够拿到，避免回表（利用主键查询再来一次））：一个索引包含所有需要查询的字段的值
4.使用索引扫描来做排序：
TIPS
1.优先使用自增Key作为主键
2.最左前缀匹配原则
3.索引列不能参与计算
4.选择区分度高的列做索引
5.能扩展就不要新建索引

不走索引

1.索引列进行运算或者使用函数不走索引（注意隐式类型转换，调用select * from article where CAST(id AS signed int) = 100）id本身为varchar
2.Like %在前
3.联合索引最左前缀原则，一直向右知道遇到范围查询后面的列不走索引（a = 1 and b = 2 and c > 3 and d = 4，d不走索引，如果建立(a,b,d,c)索引就都走了，因此区分度高德字段放在左边，查询时=可以乱序）
4.负向条件不适用：！=、<>、not in 、not exist、not like

MVCC只在读已提交，可重复度两个隔离级别下工作
避免幻读，需要达到串行化，用MVCC+next—key lock，确定where后面的条件范围，进行加行锁。