架构
- 存储(文件系统)： 把数据保存到计算机持久化的存储介质中，如机械硬盘、固态硬盘等。
- 程序实例： 管理存储的数据
- 存储管理： 将数据的逻辑关系转换称物理的存储关系，并将数据有组织的存储到物理介质中。通常数据库的性能瓶颈受限于IO速度，每次读取一条记录和读取多条的开销是差不多的。所以通常数据的最小组织单位是块、页、表。
- 缓存机制： 利用内存空间提高数据库效率，将每次读取的数据缓存到内存中，以提高下次访问相同数据的速度。经验得知，被访问数据周围的数据下次被访问的概率较大，所以会把每次读取的多条数据都缓存起来。缓存必须有淘汰机制，避免占用内存。
- SQL解析： 由于数据库使用SQL(标准查询语句)来操作数据，所以必须提供对SQL语句的解析模块。优化：可以将编译好的SQL语句缓存起来。
- 日志管理： 记录数据库操作的日志。
- 权限划分： 对多用户进行权限划分，防止操作越界。
- 容灾机制： 数据发生异常崩溃后的恢复功能
- 索引管理： 优化查询效率。
- 锁机制： 保证并发操作的安全可靠执行。
索引
常见面试问题
- 为什么要使用索引
- 什么样的信息能成为索引
- 索引的数据结构
- 密集索引(聚集索引)和稀疏索引(非聚集索引)的区别
Q:为什么要使用索引
数据库最基本的查询方式，是把多个数据块/页/表的所有数据加载到内存，遍历所有数据筛选符合查询条件的数据。在数据量很大的情况下，这种方式的效率是非常慢的。所以全表扫描仅适合数据非常小的情况。 由此产生了利用索引来查询数据的方式，索引的灵感来自于字典的目录，我们依据偏旁部首或拼音这些关键信息，可以快速定位到需要查询数据所在的页，大大的提高了查询的效率。
Q:什么样的信息适合作为索引
主键、唯一键、普通键
能使数据具备一定区分性的字段
Q:索引的数据结构
- 二分搜索树、平衡二叉树、红黑树
- B - Tree
- B+ - Tree：mysql索引使用B+ - Tree建立索引
- Hash
索引的数据结构
二分搜索树
每个节点最多只能有2个子节点，左子树的值均小于当前节点，右子树的值均大于当前节点。
普通二分搜索树可能退化称链表。平衡二叉树则存在节点深度过高，IO次数多造成的性能问题。
B-Tree
定义
- 根节点至少包括两个孩子
- 树中每个节点最多含有m个孩子(m>=2)
- 除根节点和叶子节点外，其他每个节点至少有ceil(m/2)个孩子
- 所有叶子节点都位于同一深度
- 假设每个普通节点(非根非叶子节点 )中包含有n个关键字信息，其中
a) Ki ( i = 1…n )为关键字，且关键字按升序排列，即 K ( i - 1 ) < K ( i )
b) 关键字的个数n必须满足：[ ceil ( m / 2 ) - 1 ] <= n <= m - 1
c) 非叶子节点的指针：P[1]，P[2]，… ，P[M]；其中P[1]指向关键字均小于K[1]的子树，P[M]指向关键字大于K[M - 1]的子树，其他P[ i ] 指向关键字在区间 (K[ i - 1 ]，K[ i ])区间的子树。
B±Tree
定义基本与B-Tree相同，除了以下几点
- 非叶子节点的子树指针与关键字个数相同
- 非叶子节点的子树指针P[ I ]，指向的子树包含的关键字均位于区间 [ K[ i ]，K[ i + 1 ] ] 之间
- 非叶子节点仅用来做为索引，数据都是保存在叶子节点中
- 所有叶子节点间均有一个指向下一个叶子节点的指针
Hash
Q:密集索引和稀疏索引的区别
- 密集索引： 该索引中键值的逻辑顺序决定了表中相应行的物理顺序。聚集索引确定表中数据的物理顺序。聚集索引类似于电话簿，后者按姓氏排列数据。由于聚集索引规定数据在表中的物理存储顺序，因此一个表只能包含一个聚集索引。但该索引可以包含多个列（组合索引），就像电话簿按姓氏和名字进行组织一样。
- **稀疏索引：**稀疏索引文件只为索引码的某些值建立索引项。该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同。索引是通过二叉树的数据结构来描述的，我们可以这么理解密集索引：索引的叶节点就是数据节点。而稀疏索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。
对于mysql的两种常用引擎而言，MYISAM中的主键索引、唯一索引、普通索引均为稀疏索引，保存索引的文件和保存数据的文件不在同一文件中。
而InnoDB必须有且仅有一个密集索引，且索引和数据信息存储在同一文件。
数据库存储文件后缀 - .frm: 表的结构信息
- .ibd: InnoDB的密集索引和数据存储文件
- .MYI: MyISAM的稀疏索引保存文件
- .MYD: MyISAM的数据保存文件
InnoDB选取密集索引的规则
- 若一个主键被定义，则该主键作为密集索引
- 若没有主键被定义，则第一个唯一非空索引作为密集索引
- 若以上条件均不满足，则innoDB内部自动生成一个隐藏主键作为密集索引(6字节自增字段)
- 非主键索引(稀疏索引)存储相关键位和其对应的主键值，所以需要两次查找才能定位数据。
由索引衍生出的问题
- 如何定位并优化慢查询SQL
- 联合索引的最左匹配原则的成因
- 索引是否是越多越好
Q:如何定位并优化慢查询SQL
- 根据慢查询日志定位慢查询SQL
# 查询全局变量中关于慢查询的设置
show variables like '%query%';
# 查询当前session慢查询条数
show status like '%slow_queries%';
- 使用explain等工具分析慢查询SQL
explain select name from person_info_large order by name desc;
- 修改SQL或者尽量使用索引
# 由于name不是索引，可以尝试将查询字段修改为使用唯一索引的account字段
explain select account from person_info_large order by accountdesc;
# 使用name字段创建索引
alter table person_info_large add index idx_name(name);
TIPS：
# 此条sql语句使用的唯一稀疏索引account，由于mysql默认的查询优化，密集索引是和数据存储在一起的，而count函数统# 计数量时仅需使用单独存储的稀疏索引就行了。
select count(id) from person_info_large;
# 使用force index(索引) 可以让mysql使用指定的索引
select count(id) from person_info_large force index(idx_name);
Q:联合索引的最左匹配原则的成因
最左匹配原则定义：
- 最左匹配原则是指，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)，是可以乱序的。因为mysql查询优化器可以把条件顺序优化成联合索引的顺序。
最左匹配原则的成因：
mysql在建立联合索引时，会按照联合索引中涉及字段的定义顺序，依次进行排序。如联合索引(A、B、C)，先依据A进行排序，在A排序的基础上依据B排序，在AB的基础上依据C排序。
此时我们可以说对于查询条件为字段A而言，数据是有序的。但仅依靠B、C则是无序的。
这就是最左匹配原则的成因。
Q:索引是否是越多越好
不是，原因如下
- 对于数据量不大的表，没有建立索引的必要，因为增加额外的开销远超直接全表扫描查询的花费。
- 对于数据量较大的表，需要考虑作为索引的字段选择问题，仅对能切实提高查询效率的必要字段构建索引。因为更多的索引意味着在修改数据时需要更多的维护时间。
- 更多的索引也需要更多的空间。
事务和锁
常见的面试问题
- MyISAM和InnoDB关于锁方面的区别
- 数据库事务的四大特性
- 事务隔离级别以及各级别下的并发访问问题
- InnoDB可重复读隔离级别下如何避免幻读
- RC、RR级别下的InnoDB的非阻塞度如何实现
Q:MyISAM和InnoDB关于锁方面的区别
- MyISAM默认使用的是表级锁，不支持行级锁
- InnoDB默认使用的行级锁，支持表级锁
验证MyISAM是表级锁
# 在session1中对person_info_myisam表进行查询操作
select * from person_info_myisam where id between 1 and 2000000;
# 在session2中对person_info_myisam的第2000001条数据进行修改操作
update person_info_myisam set account = account where id = 2000001;
修改操作会被查询操作阻塞，直到查询完成才能继续执行。
# 为表加锁sql语句
lock tables person_info_myisam read;
lock tables person_info_myisam write;
# 解锁sql语句
unlock tables;
read锁也叫共享锁，共享锁允许其他共享锁，但禁止其他排它锁
# 在session1中对person_info_myisam的1到2000000条数据进行修改操作
update person_info_myisam set account = account where id between 1 and 2000000;
# 在session2中查询第2000001条数据
select * from person_info_myisam where id = 2000001;
write锁也叫排它锁，排它锁禁止其他共享锁和排它锁
# 在session1中对person_info_myisam的1到2000000条数据进行修改操作
update person_info_myisam set account = account where id between 1 and 2000000;
# 在session2中查询第2000001条数据
select * from person_info_myisam where id = 2000001;
# 在session1中对person_info_myisam的1到2000000条数据进行修改操作
update person_info_myisam set account = account where id between 1 and 2000000;
# 在session2中对person_info_myisam的第2000001条数据进行修改操作
update person_info_myisam set account = account where id = 2000001;
可以对查询操作加上排它锁，使用select for update
select * from person_info_myisam where id between 1 annd 2000000 for update;
InnoDB的事务使用的是二段锁
二段锁定义
在一个事务操作中，分为加锁阶段和解锁阶段，且所有的加锁操作在解锁操作之前，具体如下图所示：
加锁时机
当对记录进行更新操作或者select for update(X锁)、lock in share mode(S锁)时，会对记录进行加锁，锁的种类很多，不在此赘述。
解锁时机
在一个事务中，只有在commit或者rollback时，才是解锁阶段。
验证InnoDB是行级锁
# 关闭自动提交
set autocommit = 0;
# 或者 开启事务
start transaction;
# 开启事务
start transaction;
# 在session1中查询id为1的数据
select * from perosn_info_large where id = 1;
# 提交操作
commit;
# 开启事务
start transaction;
# 在session2中修改id为1的数据
update person_info_large set title = title where id = 1;
# 提交操作
commit;
发现此时更新操作并未被阻塞，原因是session1中的查询操作是非阻塞读，需要显示的为查询操作加上共享锁
select * from person_info_large where id = 1 lock in share mode;
# 对session1中的查询id为1的操作加上共享锁
select * from person_info_large where id = 1 lock in share mode;
# 在session2中对id为2的数据进行修改
update person_info_large set title = title where id = 2;
此时修改操作并未被阻塞，说明InnoDB使用的是行级锁。
InnoDB中查询涉及到索引的时候 使用的是行级锁。当查询条件未涉及索引时，使用的表级锁和GAP锁。当对InnoDB使用表级锁的时候，使用的是意向锁
MyISAM适合的场景
- **频繁执行全表count语句：**MyISAM保存了数据条数，直接获取。InnoDB需要遍历查询
- **对数据进行修改的频率不高，查询频率高：**任何修改操作都会对表上锁，效率低。虽然可以通过配置，使新增操作在表尾进行，但还是会产生非常多的碎片信息。
- **不使用事务：**不支持
InnoDB适合的场景
- **数据增删改查频率都相当频繁：**修改是行级锁，查询是非阻塞读。效率高。
- 可靠性要求高，需要事务支持：
数据库中锁的分类
- **按锁的力度划分：**表级锁、行级锁、页级锁。其中InnoDB在对行上锁的时候，会先上一个表级别的意向锁。
- **按锁级别的划分：**共享锁、排它锁。
- **按加锁方式划分：**自动锁(隐式锁)、显式锁。
– 自动锁包括：DDL、MyISAM表锁、InnoDB意向锁。
– 显式锁包括：select for update、lock in share mode。 - 按操作划分： DML锁、DDL锁
- 按使用方式划分：乐观锁、悲观锁
Q:数据库事务的四大特性
事务定义
数据库事务是指，作为单个逻辑单元执行的一系列操作。
特性ACID
- **A(Atomic)原子性：**单个事务中包含的所有操作，要么全部执行，要么全部不执行。
- **C(Consistency)一致性：**事务执行前后，数据完整性保持一致。如转账操作中，A、B账户转账前后的账户总金额不变。
- **I(Isolation)隔离性：**事务并发执行时，一个事务不应该影响其他事务。这里的影响不是指完全避免。通常事务的隔离级别越高，安全性越高、并发性越低。隔离级别越低，安全性越低、并发性越高。
- **D(Durability)持久性：**事务一旦提交，对数据库的改变是永久性的。意味着存储介质发生故障，也能确保提交的事务不被丢失。以InnoDB为例，所有数据库操作都会保存在一个日志文件中(redo_log_file)，每次数据库启动时，执行日志文件中操作恢复数据。
Q:事务隔离级别以及各级别下的并发访问问题
# 查询数据库当前事务隔离级别
select @@tx_isolation;
# 修改当前session的事务隔离级别
set session transaction isolation level read uncommitted;
一类更新丢失
二类更新丢失
脏读
不可重复读
幻读
总结
Q:InnoDB可重复读(RR)隔离级别下如何避免幻读
- 表象： 快照读(非阻塞读) 伪MVCC
- 内在： next-key锁(行锁 + gap锁)
当前读和快照读
当前读
select lock in share mode(共享锁)、select for update、insert、delete、update。
读取的是记录数据的最新版本，并且当前读返回的记录都会加上锁，保证其他事务不会再并发的修改这条记录。
update的过程，首先会执行当前读，然后把返回的数据加锁，之后执行update。加锁是防止别的事务在这个时候对这条记录做什么，默认加的是排他锁，也就是你读都不可以，这样就可以保证数据不会出错了。
快照读
不加锁的select。
读取的是记录数据的可见版本（可能是过期的数据），不用加锁。基于多版本并发控制MVCC。
innodb默认隔离级别是RR， 是通过MVVC来实现了，读方式有两种，执行select的时候是快照读，其余是当前读。执行快照的时机决定了读到的版本。所以，mvvc不能根本上解决幻读的情况。
next-key锁(行锁+gap锁)
- **行锁：**对行记录上锁
- gap锁： gap即插入索引树中的间隙。gap锁可以锁定一个范围，但不包括记录本身。其目的是防止在同一事务中两次当前读出现幻读的情况。gap锁只出现在RR、Serializable级别。
使用gap锁的情况
- where条件全部命中，对命中数据行使用记录锁(行锁)，不使用gap锁。
- where条件部分命中或全不命中，使用gap锁。
- 使用非唯一索引(普通索引)的当前读
- 不使用索引的当前读
Q:RC、RR级别下的InnoDB的非阻塞度如何实现
- 每个数据行中的隐藏字段
DB_TRX_ID、DB_ROLL_PTR、DB_ROW_ID
– ·DB_TRX_ID·：操作本行数据的最新事务ID，每次开启事务，事务ID递增1
– ·DB_ROLL_PTR·：写入回滚段(rollback segment)的undo日志记录，如果一行记录被更新，undo日志要包含重建(回滚)该行记录的信息
– ·DB_ROW_ID·：行号，随新增操作递增。如果·InnoDB自动构建聚集索引时，包含此字段。 - UNDO日志：每当数据变更时，记录之前版本的数据。当之前的事务需要访问数据时，需要根据undo记录链式寻找满足此事务可见性(见read view)的记录。
– insert undo log：新增数据时记录，用户回滚新增操作。当事务提交或者回滚时删除。
– update undo log：更新、删除、快照读使用。
– 日志记录过程：
- 使用排它锁锁定该行记录。
- 拷贝修改前的记录到undo日志。
- 执行修改操作，维护DB_TRX_ID记录当前事务ID，维护DB_ROLL_PTR指向undo日志中之前记录信息。
- **read view：**可见性判断，当前事务执行快照读的时候，针对查询的数据创建一个read view决定当前事务能看到哪个版本的数据。可能是最新版本，也可能是之前版本。read view遵循可见性算法，将查询数据的DB_TRX_ID和系统其它活跃的事务ID对比，>=则按照DB_ROLL_PTR沿着undo日志继续查找，直到遇到<的日志记录，以保证获取到当前最稳定版本的数据。
在RR级别下，当开启事务后，首次快照读创建数据的快照，即read view，并记录当前活跃的其他事务。之后再次快照读还是调用同一个read view。如果首次快照读是在其他事务执行修改之前，则后续也无法访问到其他时候修改后的数据版本。
在RC级别下，每次查询均创建一个新的read view，所以可以在RC级别下访问到其他事务提交的数据。
常考语法
- GROUP BY
- HAVING
- 统计函数： COUNT、SUM、MAX、MIN、AVG
2260
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
