准备面试数据库学习日记

基础模块:

存储模块，程序实例（存储管理，缓存机制，sql解析，日志管理，权限划分，容灾机制，索引管理，索管理）

如何设计一个关系化数据库：

首先将其划分成两大部分，首先存储模块，类似一个文件系统，还需要一个程序实例模块对其进行逻辑管理，其中包含存储管理（数据逻辑关系转换成物理存储关系），缓存机制（优化执行效率），sql解析（sql语句进行解析），日志管理（记录操作），权限划分（多用户管理），容灾机制（灾难恢复），索引管理（优化数据查询效率），索管理（使数据库进行并发操作）。

为什么要使用索引：

可以避免全表扫描，提升检索速率
通过索引大幅增加查找效率，快速查询数据

什么样的信息成为索引：

主键、唯一键以及普通键等

索引的数据结构：

生成索引，建立二叉查找数进行二分查找
生成索引，建立B-Tree结构进行查找
生成索引，建立B±Tree结构进行查找
生成索引，建立Hash结构进行查找

B-Tree：

根节点至少包括两个孩子
树中每个节点最多包含有m个孩子（m>=2）
除了根节点和叶节点外，其他每个节点至少有ceil（m/2）个孩子（ceil函数是取上限的意思）
所有叶子节点位于同一层

B+ -Tree：

除了叶子结点，子树指针个数与关键字个数相同
非叶子节点子树P[i]，指向关键字值（K[i],k[i+1]）
非叶子节点只用来作索引，数据都保留在叶子节点中（不存储数据，就使得可以存储更多的关键字）
每个叶子节点都有一个链指针指向下一个叶子结点（例：找>10得数，在找到一个叶子节点时就不需要回查，就可以直接横向查询，便于范围统计）

B+Tree更适合用来做索引的原因

B+Tree的磁盘读写代价更低
B+Tree的查询效率更加稳定每一个数据的查询长度，稳定的O(logn）
B+Tree更加利于对数据库的扫描，更利于范围查询。

Hash索引的缺点

虽然效率可能大于B+Tree，但是hash只能查找==或者in的情况
无法来进行排序
不能进行部分索引查找
不能避免表扫描，找到hash对应得块后也无可避免要对该块进行表扫描
在特殊情况下可能会成为线性结构效率低下

BitMap

在对于字段值只有固定几个进行查询时，效率十分高效，但是并不广泛使用，并且在数据增删改查时会出现锁情况，索引不适合高并发的情形

密集索引和稀疏索引的区别

密集索引文件中的每个搜索码都对应一个索引值
稀疏索引文件中只为某些值建立索引项

如何定位并且优化慢查询sql

1.根据慢日志定位慢查询sql
2.使用explain工具分析sql（如果使用all或者index，或者在extra项中出现using filesort 或Using temporary 则需要执行优化）
3.修改sql或者尽量让sql走索引（可以使用force index （索引名）强制使用索引来进行测试）

联合索引最左匹配原则的成因

mysql联合索引会根据最左边第一字段order by后，再将第二个子段order by，因此第一个字段是由绝对排序的，然而第二个字段可能会是无序的，所以直接使用第二个子端进行条件判段是用不到索引的，所以第一位在最左才会有最左匹配原则

索引是越多越好吗？

1.数据量小的表不需要建立索引，建立会增加额外的索引开销
2.数据变更需要维护索引，因此更多索引意味着更多的维护成本
3.更多索引意味着也需要更多的空间

锁的基础类型

按锁级别划分
添加读|写锁 lock tables xxx read | write；
读锁就是共享锁（读锁的同时可以其他读），写锁是排它锁（写锁的同时不可以其他写）

按锁的粒度划分：
表级锁，表级锁会在操作时将整个表锁住
行级锁，行级锁会在操作时只讲被操作的行锁住
例：MyISAM是只支持表级锁，不支持行级锁
InooDB是支持行级锁也支持表级锁，会自动提交commit，防止堵塞（特别的inooDB在不走索引的情况下，会使用表级锁）
页级锁

按加锁方式划分：
自动锁、显式锁

按操作划分：
DML锁：对数据进行增删改查的锁
DDL锁：对表结构进行修改的锁

使用方式划分：
乐观锁：在一般处理的时候不会调用锁机制，只有在数据提交更新的时候对数据的冲突进行查询，并且返回所对应的信息。
悲观锁：对外部的修改保持排他态度

MyISAM和InooDB适用场景

MyISAM的适用场景：
1.频繁执行全表的count语句
2.对数据的增删改频率不高但是查询非常频繁
3.没有事务的情况
InooDB的适用场景：
1.增删改查都相当频繁
2.可靠性要求比较高，要求支持事务

数据库事务的四大特性

ACID
原子性（Atomic）
一致性（Consistency）
隔离性（Isolation）
持久性（Durability）

事务的隔离级别以及各级别下的并发访问问题

更新丢失–mysql所有事物隔离级别在数据库层面上均可避免
脏读（在一个事物操作后并未commit，但是另一个事务却可以读取）–READ-COMMITTED事务隔离级别上均可避免
不可重复读（在一个事务对其进行连续操作的时候，另一个事务对其修改并且commit，导致当前事务中的数据发生变化）–REPEATABLE-READ事务隔离级别上均可以避免
幻读（一个事物对所有数据修改并未提交，另一个事物新增一条数据，导致修改的操作所影响的行数加一）–SERIALIZABLE事务隔离级别被可避免

未提交读–READ-UNCOMMITTED
已提交读–READ-COMMITTED
可重复读–REPEATABLE-READ
串行化–SERIALIZABLE

当前读和快照度

当前读：增删改查加锁模式
快照度：不加锁的增删改查

next-key锁

行锁
Gap锁：防止幻读加的Gap锁，如果where条件全部命中，不会加Gap锁，如果部分或者全部都不命中，则会加Gap锁，上锁后会阻止插入操作，并且只会锁住周围区间不会影响全部

关键语法

GROUP BY
HAVING
统计相关：COUNT，SUM，MAX，MIN，AVG