-
本文整理自剑指Java面试-Offer直通车
-
如何设计一个关系型数据库
-
存储
-
文件系统
-
-
程序实例
-
存储管理
-
IO效率极低，所以数据库中的行在存储中没有实际意义，一般都是按块或页存储，一次IO读取多行数据
-
-
缓存机制
-
一旦某行数据被访问了，它周围的数据也极有可能被访问
-
缓存不宜过大，要有淘汰机制
-
-
SQL解析
-
将SQL编译解析，翻译成机器可识别的指令
-
缓存
-
-
日志管理
-
权限划分
-
多用户管理
-
-
容灾机制
-
从错误中恢复
-
-
索引管理
-
锁管理
-
-
-
为什么要使用索引
-
如果不使用索引，数据库会进行全表扫描，使用索引是为了尽量避免全表扫描，加快查询速度
-
全表扫描在数据量少的表中，查询效率反而更快
-
索引的灵感来自于字典
-
-
-
什么样的信息能成为索引
-
能把该记录限定在一定查找范围内的字段，即关键信息，如主键
-
主键、唯一键以及普通键等
-
-
索引的数据结构
-
平衡二叉树（不适合）
-
平衡二叉树查找效率为O(logn)
-
检索深度每加一，都要进行一次IO，严重影响查询效率
-
-
数据库的数据经过增删后，很容易变成线性结构，查询效率为O(n)
-
可通过树的旋转特性，使树保持为平衡二叉树
-
-
-
-
B-Tree（平衡多路查找树，不适合）
-
为了减少IO，需要把平衡二叉树变矮，使每个节点存储的数据变多，则成为了B-Tree
-
查找效率为O(logn)
-
与平衡二叉树相比，B-Tree可以通过合并、上移下移节点等保持结构
-
-
-
B+-Tree（适合，常用）
-
是对B-Tree优化后的一种数据类型，所有数据存在叶子节点中，非叶子节点只存储关键字，会比B-Tree更矮
-
叶子节点有链指针指向下一个叶子节点，可横向做统计
-
B+-Tree更适合来做存储索引
-
磁盘读写代价更低（数据都在叶子节点中，一次读取的索引更多）
-
查找效率更加稳定（非叶子节点只存索引，所有数据走的路径一样，效率O(logn)）
-
有利于数据库的扫描（链指针）
-
-
-
-
Hash（小众）
-
缺点
-
仅能满足“=”，“IN”，不能使用范围查询
-
无法被用来避免数据的排序操作
-
不能利用部分索引建查询（组合索引中的某个索引，B+-Tree支持）
-
不能避免表扫描
-
遇到大量Hash值相等的情况后，性能并不一定比B-Tree索引高
-
-
-
-
BitMap（位图）索引
-
Oracle支持，MySQL不支持
-
只有几种值的情况（如性别），效率很高
-
新增修改的时候，通常会把同一个位图的数据都锁住
-
-
-
-
密集索引和稀疏索引的区别
-
密集索引文件中的每个搜索码值都对应一个索引值
-
叶子节点保存的不止是键值，还保存了位于同一行的其他列的信息
-
由于密集索引决定了表的物理排列顺序，一个表只能有一个密集索引
-
-
-
稀疏索引文件只为索引码的某些值建立索引项
-
叶子节点仅保存了键位信息以及该行数据的地址
-
有的稀疏索引仅保存了键位信息及其主键
-
定位到叶子节点后，还需要通过其中的信息，进一步定位到数据
-
-
-
MySQL中的InnoDB（聚簇）
-
必须定义一个密集索引
-
若一个逐渐被定义，则该主键座位密集索引
-
若没有主键被定义，则该表的第一个唯一非空索引作为密集索引
-
若不满足以上条件，InnoDB内部会生成一个隐藏主键（密集索引）
-
-
-
-
MySQL中的MyISAM（非聚簇）
-
使用了稀疏索引
-
-
-
-
如何定位并优化慢SQL
-
根据慢日志定位慢查询SQL
-
show variables like '%query%'
-
slow_query_log
-
set global slow_query_log = ON
-
-
slow_query_log_file
-
慢SQL日志地址，慢SQL会写入此中
-
-
long_query_time
-
查询时间大于此值，则属于慢SQL
-
set global long_query_time = 1
-
默认10秒太长
-
需要重新连接客户端
-
-
-
-
show status like '%slow_queries%'
-
本次回话的慢查询执行次数
-
-
-
使用explain等工具分析SQL
-
直接加在查询语句前面
-
type，从最优到最差
-
system,const,eq_ref,ref,fulltext,ref_or_null,index_merge,unique_subquery,index_subquery,range,index,all
-
一般情况下，如果是index和all，则表示语句需要优化
-
-
-
extra，出现以下2项意味着MySQL根本不能使用索引，效率会受到重大影响，有可能需要优化语句
-
Using filesort
-
表示MySQL会对结果使用一个外部索引排序，而不是从表里按索引次序读到相关内容
-
可能在内存或者磁盘上进行排序
-
MySQL无法利用索引完成的排序操作称为“文件排序”
-
-
Using temporary
-
表示MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by
-
-
-
-
修改SQL或者尽量让SQL走索引
-
MySQL的查询优化器有时候并非最优结果，可以使用force index语法指定合适的索引
-
-
-
联合索引的最左匹配原则的成因
-
联合索引中，如A_B组成联合索引，where A='' and B='' 走索引，where A='' 也走索引，where B='' 则不会走索引
-
MySQL会一直向右匹配知道遇到范围查询（>,<,between,like）就停止匹配
-
如，a = 3 and b = 4 and c > 5 and d = 6
-
如果建立（a,b,c,d）顺序的索引，d是用不到索引的
-
如果建立（a,b,d,c）的索引，则都可以用到，a,b,d的顺序可以任意调整
-
-
-
联合索引中，=和in可以乱序
-
如，a=1 and b =2 and c = 3 建立（a,b,c）联合索引可以任意顺序，MySQL的查询优化器会优化成索引可以识别的形式
-
-
如下图，建立索引（col3,col2），MySQL会根据第一个字段，建立一个B+-Tree，因此使用此索引，必须要遵循最左匹配原则
-
-
-
索引是建立的越多越好吗
-
不是
-
数据量小的表不需要建立索引，建立索引会增加额外的索引开销
-
数据变更需要维护索引，因此更多的索引意味着更多的维护成本
-
更多的索引意味着需要更多的空间
-
-
MyISAM与InnoDB关于锁方面的区别是什么
-
读锁与写锁
-
读锁
-
读锁未释放时，无法再加上写锁
-
读锁又称共享锁，一个表可以同时加上多个读锁
-
lock tables 表名 read; -> 手动给表加上读锁，unlock tables; -> 释放锁
-
在select后面加上lock in share mode也可以加上共享锁
-
-
写锁
-
写锁未释放时，无法再加上读锁
-
写锁又称排它锁，不能同时拥有多个
-
在select后面加上for update关键字，可以加上排它锁
-
-
-
MyISAM
-
默认用的是表级锁，不支持行级锁
-
如，查询ID在1和2000000之间的数据时，对ID等于2000001的数据进行更新，更新会等待查询完成
-
-
不支持事务
-
当Select时，MyISAM引擎会为查询的表加上表级别的读锁
-
当Insert,Update,Delete时，MyISAM引擎会为增删改的表加上表级别的写锁
-
适合的场景
-
频繁执行全表count语句，MyISAM用一个变量保存了表的行数，count时取出即可
-
对数据进行增删改的频率不高，查询非常频繁时
-
没有事务
-
-
-
InnoDB
-
默认用的是行级锁，也支持表级锁
-
SQL语句用到索引才会加行级锁，否则加表级锁
-
-
支持事务
-
MySQL默认事务自动提交
-
show variables like 'autocommit';
-
set autocommit = 0; -- 关闭自动提交，只针对当前session
-
set autocommit = 1; -- 开启自动提交，只针对当前session
-
或使用begin transaction，手动控制事务
-
-
-
当操作一行数据时，行级锁不会影响另一行数据的操作
-
适合的场景
-
数据增删改查都相当频繁
-
可靠性要求比较高，要求支持事务
-
-
-
-
数据库锁的分类
-
按锁的力度划分
-
表级锁
-
行级锁
-
页级锁
-
-
按锁级别划分
-
共享锁
-
排它锁
-
-
按枷锁方式划分
-
自动锁
-
显式锁
-
-
按操作划分
-
DML锁
-
DDL锁
-
-
按使用方式划分
-
乐观锁
-
悲观锁
-
-
-
数据库事务的四大特性
-
ACID
-
原子性（Atomic）
-
事务中的操作，要么全做，要么全不做
-
-
一致性（Consistency）
-
事务应确保数据的状态从一个一致的状态，转变到另一个一致的状态
-
A和B共有两千元，A向B转账，不管怎么转，最终A和B还是应该有两千元
-
-
隔离性（Isolation）
-
多个事务并发执行时，一个事务的执行不应该影响另一个事务的执行
-
-
持久性（Durability）
-
一个事务一旦提交，它对数据的修改应该永久保存在数据库
-
-
-
事务并发引起的问题及如何避免
-
查看MySQL的事务隔离级别
-
select @@tx_isolation;
-
查询当前会话的事务隔离级别，默认是REPEATABLE-READ，可重复读
-
-
set session transaction isolation level read uncommitted;
-
设置当前会话的事务隔离级别为未提交读
-
-
set session transaction isolation level read committed;
-
设置当前会话的事务隔离级别为提交读
-
-
set session transaction isolation level repeatable read;
-
设置当前会话的事务隔离级别为可重复读
-
-
set session transaction isolation level serializable;
-
设置当前会话的事务隔离级别为串行化
-
-
-
更新丢失，一个事务的更新覆盖了另一个事务的更新
-
-
脏读，一个事务读到另一个事务未提交的数据
-
不可重复读，一个事务中，对同一个数据的多次读取值不一致（别的事务对数据进行了更新）
-
幻读，事务开启过程中，受到其他事务对新增或删除行的影响，像出现幻觉一样
-
-
-
InnoDB可重复读事务隔离级别下如何避免幻读
-
表象
-
快照读（非阻塞读），伪MVCC
-
-
内在
-
next-key锁（行锁+Gap锁）
-
行锁
-
Gap锁
-
Gap->索引中的间隙，锁一个范围
-
Gap锁 会用在非唯一索引或者不走索引的当前读中
-
非唯一索引
-
如下图，对所操作的记录周围的Gap上锁，即 (6,9],(9,11]
-
主键的值也会对范围产生影响，如插入('bb',6)成功，插入('dd',6)则会判定在Gap锁范围内
-
-
-
不走索引
-
锁住所有Gap，需要避免这种情况
-
-
-
-
如果where条件全部命中，则不会用Gap锁，只会用记录锁
-
-
如果where条件部分命中或者全不命中，则会加Gap锁
-
-
-
-
-
快照读和当前读
-
当前读
-
读取的是数据的最新版本，并且读取后需要保证其他事务不能修改当前记录
-
加了锁的增删改查语句，成为当前读
-
select ... lock in share mode
-
select ... for update
-
update
-
delete
-
insert
-
-
-
快照读
-
事务非Serializable级别下，不加锁的select操作，Serializable事务隔离级别时，由于是串行化，快照读会退化为当前读
-
可重复读事务隔离级别下，快照读可能读到数据的历史版本
-
事务A先进行快照读，事务B进行更新并提交，事务A的快照读可读到历史版本
-
事务A开启，事务B进行更新并提交，事务A的快照读，读到的是最新版本
-
-
-
-
RC、RR级别下的InnoDB的非阻塞读（快照读）如何实现
-
数据行里的DB_TRX_ID,DB_ROLL_PTR,DB_ROW_ID三个字段
-
DB_TRX_ID
-
最近一次对本行做修改的事务ID
-
-
DB_ROLL_PTR
-
回滚指针
-
-
DB_ROW_ID
-
行ID
-
-
-
undo日志
-
-
read view
-
做可见性判断
-
RR级别下，第一条快照读，会创建一个read view，将当前数据库的事务状态记录，同一事务中再次进行快照读时，使用的是同一个read view
-
RC级别下，每次进行快照读，都会创建一个新的read view
-
-
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
