数据库总结

大纲

（1）架构
（2）索引
（3）锁
（4）语法
（5）理论范式

一、架构

如何设计一个数据库？

首先分为两个大模块，分别是存储（文件系统）和程序实例

存储模块

存储数据的地方，如机械硬盘，SSD固态硬盘，磁盘阵列等

程序实例模块

有了存储模块后，我们需要使用这些数据，程序实例模块就是 将数据库的逻辑地址转换为数据库的物理地址

• 存储管理：对逻辑与物理地址映的管理
• 缓存机制：数据库的存储模块是硬盘，硬盘的IO是比较慢的，如果每次要数据，都是去数据库存储中查询的话会很慢。所以数据库会一块或页的方式，将多条数据组合在一起，当查询到页中的一条数据时，会将数据页缓存到内存中，在下一次查询时如果在缓存中找到了，可以直接使用。其实缓存中还会缓存SQL语句。因为SQL是需要翻译的，如果一条SQL语句经常被使用，每次都翻译会很耗费时间，所以会把SQL翻译后放在缓存中。管理缓存有多种方法，如LUR页面淘汰机制
• SQL解析：外界要访问数据库是通过SQL来访问的，人类看得懂的SQL必须经过解析才能让机器看得懂，然后映射出要查寻数据的地址。
• 日志管理：记录使用过的SQL，方便做数据库的组成同步和灾难恢复。（BinLog）
• 权限划分
• 容灾机制：当数据库出现了异常，该如何解决呢，可以通过设计容灾机制来恢复数据
• 索引管理 和 锁管理是重点，决定数据库的并发访问，访问的速度等

二、索引

问题

1. 为什么要使用索引
2. 什么样的信息能成为索引
3. 索引的数据结构
4. 稀疏索引和密集索引的区别

索引的数据结构

B-Tree

B-Tree结构的数据可以让系统高效的找到数据所在的磁盘块。为了描述B-Tree，首先定义一条记录为一个二元组[key, data] ，key为记录的键值，对应表中的主键值，data为一行记录中除主键外的数据。对于不同的记录，key值互不相同。

一棵m阶的B-Tree有如下特性：

1. 每个节点最多有m个孩子。
2. 除了根节点和叶子节点外，其它每个节点至少有Ceil(m/2)个孩子。
3. 若根节点不是叶子节点，则至少有2个孩子
4. 所有叶子节点都在同一层，且不包含其它关键字信息
5. 每个非终端节点包含n个关键字信息（P0,P1,…Pn, k1,…kn）
6. 关键字的个数n满足：ceil(m/2)-1 <= n <= m-1
7. ki(i=1,…n)为关键字，且关键字升序排序。
8. Pi(i=1,…n)为指向子树根节点的指针。P(i-1)指向的子树的所有节点关键字均小于ki，但都大于k(i-1)

B+ -Tree

B+Tree是在B-Tree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构。

从上一节中的B-Tree结构图中可以看到每个节点中不仅包含数据的key值，还有data值。而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。

B+Tree相对于B-Tree有几点不同：

1. 非叶子节点只存储键值信息。
2. 所有叶子节点之间都有一个链指针。
3. 数据记录都存放在叶子节点中。
4. 将上一节中的B-Tree优化，由于B+Tree的非叶子节点只存储键值信息，假设每个磁
   块能存储4个键值及指针信息，则变成B+Tree后其结构如下图所示：
   

对比B-Tree与B+ -Tree

B+ -Tree的磁盘读写代价更低
B+ -Tree的查询效率更稳定（因为数据是存储在叶子节点，所以每次查找数据都是从根节点开始向下查找的，每次查找的次数都是一样的）
B+ -Tree更有利于对数据库的扫描（叶子节点是相连的，只要查询到一个叶子节点就可以全局扫描，不用再次从根节点开始查）

Hash索引

根据索引键作为key，获得Hash都定位到Hash桶中，再遍历桶中的链表
优点：只需要一次操作（计算索引的Hash值）就能完成定位

缺点：

1. 仅仅能满足 “=”, “IN”, 不能使用范围查询
2. 无法被用来避免数据库的排序操作
3. 不能利用部分索引键查询（针对复合索引）
4. 不能避免表扫描（针对大量索引的Hash值相等的情况）
5. 遇到大量Hash值相等的情况后，性能并不一定比B-Tree索引高

BitMap位图索引

密集索引与稀疏索引

• 密集索引：为每一个搜索码键都建立了索引
  
  图1 顺序文件（右）上的稠密索引（左）

密集索引的定义：叶子节点保存的不只是键值，还保存了位于同一行记录里的其他列的信息，由于密集索引决定了表的物理排列顺序，一个表只有一个物理排列顺序，所以一个表只能创建一个密集索引

• 稀疏索引：只为部分搜索码键建立索引
  
  图2 顺序文件上的稀疏索引

稀疏索引：叶子节点仅保存了键位信息以及该行数据的地址，有的稀疏索引只保存了键位信息机器主键

mysam存储引擎，不管是主键索引，唯一键索引还是普通索引都是稀疏索引

innodb存储引擎：有且只有一个密集索引。密集索引的选取规则如下：

1. 若主键被定义，则主键作为密集索引
2. 如果没有主键被定义，该表的第一个唯一非空索引则作为密集索引
3. 若不满足以上条件，innodb内部会生成一个隐藏主键（密集索引）
4. 非主键索引存储相关键位和其对应的主键值，包含两次查找
   
   密集索引的索引和数据是放在同一个文件中的，而稀疏索引则是将索引和数据分离

回答问题

1. 为什么要使用索引：
   因为使用索引可以避免全局扫描，提升检索效率
2. 什么样的信息能成为索引：
   主键、唯一键等能唯一确定一行信息的字段
3. 索引的数据结构：
   主流是B-Tree，还有Hash索引和BitMap索引。MySql不支持BitMap索引，基于MyISAM引擎和InnoDB引擎的MySql不显式支持Hash索引。
4. 密集索引和稀疏索引的区别：
   密集索引为每一个搜索码键都建立了索引, 稀疏索引只为部分搜索码键建立索引

现在来看看如何SQL调优

一、优化SQL语句的一般步骤

1、了解SQL的执行频率

命令：show status like ‘Com_%’; – Com_XXX表示XXX语句执行的次数

1. Com_select : SELECT操作的次数 以下同理
2. Com_insert :
3. Com_update
4. Com_delete
   以上的参数对所有储存引擎的表操作都会进行累计，下面是专门对Innodb引擎的
5. Innodb_row_read : SELECT查询返回的行数
6. Innodb_row_inserted : 插入的行数
7. Innodb_row_updated : 更新的行数
8. Innodb_row_deleted : 删除的行数

便于用户了解数据库基本情况：

1. Connections : 试图连接MySQL服务器的次数
2. Uptime：服务工作时间
3. Slow_queries : 慢查询的次数（记录执行较慢SQL语句执行次数）

2、定位执行效率较低的SQL语句

1. 查询慢日志
2. 慢日志是在语句执行完毕后，才会记录的，如果当前语句还没执行完，可以通过 show processlist 命令实时查看SQL执行情况

3、通过EXPLAIN分析低效SQL的执行计划

如：explain select * from user
定位到低效的SQL后，先寻找他执行慢的原因， 通过explain可以得到一系列参数

• select_type：表示查询的类型，SIMPLE(单表)、PRIMARY（主键查询，即有子查询的外层查询）、UNION（UNION中的第二个或者后面的查询语句）、SUNQUERY（子查询的第一个SELECT）等
• table：输出结果集的表
• type：使用的查询方式
  由慢到快依次为：
  ALL 一> index 一> range 一> ref 一> eq_ref 一> cont,system -> NULL

1. ALL：全表扫描
2. index：索引全扫描
3. range：索引范围扫描
4. ref：非唯一索引扫描或唯一索引的前缀扫描
5. eq_ref：唯一索引
6. cont,system：单表中最多有一个匹配行
7. NULL：不用访问表或索引

• possible_keys：表示查询时可能使用索引
• key：表示实际使用索引
• key_len：使用索引字段的长度
• rows：扫描的行数
• Extra：执行情况的的说明和描述

我们的SQL，优化器一般会做一些优化才执行，这样效率会有相应提升，想查看优化器对SQL做了什么优化，可以通过 explain extended 语法来执行语句，然后通过
show wranings; 就可以看出优化器优化后的SQL语句了

4、通过show profile 分析 SQL各个操作的用时

show profile; – 查看当前SQL的Query ID
show profile for query ID; – 查看一条SQL全部操作所用时的详细情况

5、通过 trace分析分析优化器如何执行计划

一条SQL可以有多种执行计划（根据那个索引来查询、是否全表扫描更快…），但是每种计划的速度不一样，可以用一个变量 cost （代价）来表示，cost越小，证明该SQL执行方式越优，优化器会选择cost最小的那种方式来执行。
步骤：

• 打开trace： SET optimizer_trace=“enabled=on”, END_MARKERS_IN_JSON=on;
• 设置optimizer_trace内存的大小,内存过小不能够完全显示：
  SET OPTIMIZER_TRACE_MAX_MEM_SIZE=1000000
• 执行SQL 如 ：explain SELECT * FROM atomuser WHERE uid =28778731 AND ptype = “photo” LIMIT 0 , 1;
• 查看具体如何执行SQL： select * from information_schema.optimizer_trace;

6、当确定问题后采取相应的优化措施

如对相应字段创建索引

二、索引调优

1、如何使用索引

• 能使用索引的情况：

1. 全值匹配：对于复合索引来说，各个索引列都指定了等值匹配的条件，如：
   explain select * from user where name = ‘superl’ and age = 21;
   其中 (name, age) 组成了复合索引， 就会根据该索引来查询。
   如果是简单索引也类似
2. 匹配值得范围查询，对索引的值能够进行范围查询。
3. 匹配最左前缀：如复合索引 col1 + col2 + col3, 如果查询条件包含 col1, 则会用到该复合索引，
4. 仅对索引进行查询：条件中只包含索引列
5. 匹配列前缀：仅仅使用索引列的的第一列并且只包含索引列的开头部分
6. 能够实现索引部分精确，其他部分进行范围匹配
7. 如果列名是索引，那么使用column_name is null 作为条件就会使用索引

• 存在索引但是不使用索引的情况

1. 以 % 开头的LIKE 查询不能够利用B-Tree索引
2. 数据类型出现隐式转换时，如：where name = 1; name字段是字符串字段，会将1隐式转换为 “1”;
3. 复合索引中，如果查询条件不包含索引列最左部分
4. 如果使用索引比全表扫描更慢（优化区会自己判断）
5. 用or分割条件， or前面的条件有索引，后面部分没有索引

查看索引的使用情况

命令：show status like ‘Handler_read%’
得到以下参数：

• Handler_read_first
• Handler_read_key：一个行被索引值读的次数，如果该值很小，表明增加索引对性能提升不大，因为被引用次数不多
• Handler_read_next
• Handler_read_prev
• Handler_read_rnd
• Handler_read_rnd_next：读取下一行的次数，如果该值比较大，证明存在大量扫描

二、两个简单实用的优化方法

1、定期分析表和检查表

命令：check table table_name

2、定期优化表

optimize table table_name

三、常用SQL的优化

1、大批量插入数据

• 如果导入的数据按照主键的顺序，可以有效地提交导入数据的效率，如：
  load data infile ‘file_path’ into table table_name;
• 插入数据前关闭唯一性校验
  关闭唯一性检查：SET UNION_CHECKS = 0;
  当导入结束后恢复唯一性检查：SET UNION_CHECKS = 1;
• 插入数据前关闭自动提交
  关闭自动提交：SET AUTOCOMMIT=0;
  当导入数据借宿后恢复自动提交：SET AUTOCOMMIT=1;

2、优化INSERT 语句

当要插入多条数据时
insert into user values(1, “superl1”, 21);
insert into user values(2, “superl2”, 22);
insert into user values(3, “superl3”, 23);
这样会增大数据库连接、关闭等消耗，可以改用为多个值表的INSERT语句
insert into user values(1, “superl1”, 21),(2, “superl2”, 22),(3, “superl3”, 23);

3、优化ORDER BY语句

1. MySQL中有两种排序方式

• 通过有序索引顺序扫描直接返回有序数据
• 通过对返回数据进行排序（Filesort）

2. Filesort的优化
   大部分情况我们都想直接得到的查询到的数据，不需要额外的排序（浪费资源）。但是一些情况不能让Filesort消失，这样就必须对Filesort进行优化了。MySQL有两种排序方式：

• 两次扫描算法：第一次获取排序字段和行指针信息，第二次根据行指针获取记录。第二次操作会造成大量的随机I/O操作；优点是第排序时候内存开销较少
• 一次扫描算法。一次性取出满足条件的行的所有字段。排序的时候内存开销较大，但是排序效率比两次扫描算法要高。

4、优化GROUP BY 语句

如果要避免排序结果的消耗，可以指定GROUP BY NULL禁止排序。

5、优化嵌套查询

有时候子查询可以被更有效率的连接（JOIN）代替，因为连接不需要在内存中创建临时表来完成这个逻辑上需两个步骤的查询工作

6、优化OR条件查询

OR之间的每个条件都必须用到索引

7、优化分页查询

• 方法一、在索引上完成排序分页的操作，最后根据主键关联回原表查询所需要的其他列内容
• 方法二、缩小扫描的范围，可以先找出一个临界值，再利用范围查找

8、使用SQL提示（建议优化器需要如何做）

• USE INDEX：希望优化器使用指定索引 select count(*) from user use index(my_index)
• IGNORE INDEX：忽略索引
• FORCE INDEX：强制索引

问题：

• 最左匹配原则的成因
  为何要使用最左匹配原则：
  可以参考： http://www.cnblogs.com/bigtiger/p/6054781.html
• 索引是越多越好吗？
  答案是否定的
  1. 数据量小的表不需要建立索引，建立索引会增加额外的开销
  2. 数据变更需要维护索引，因此更多的索引意味着更多的维护成本
  3. 更多的索引也意味着更多的开销

锁

问题

1. MyISAM与InnoDB关于锁方面的区别是什么
2. 数据库的四大特性
3. 事务隔离级别以及各级别下的并发访问问题
4. InnoDB可重复度隔离级别下如何避免幻读
5. RC、RR级别下的InnoDB的非阻塞读如何实现

一、MyISAM与InnoDB关于锁方面的区别是什么

1. MyISAM默认使用的是表级锁，不支持行级锁
2. InnoDB默认使用的是行级锁，也支持表级锁

提前准备：读读共享，读写互斥，写写互斥
读锁（共享锁），写锁（排它锁）

• MyISAM在 SELECT操作的时候会把表上读锁，INSERT,UPDATE,DELETE操作会上表级写锁（表级锁与索引无关）
• InnoDB在SELECT操作时默认不加锁（手动加读锁可以通过lock in share mode），INSERT,UPDATE,DELETE操作会上写锁。当操作涉及到索引操作时会使用行级锁，当不涉及索引时会走表级锁
• MyISAM适用的场景：

1. 频繁执行全表count语句。因为MyISAM记录了一个全表数据条数的变量
2. 对数据增删改的频率不高，查询非常频繁
3. 没有事务。MyISAM不支持事务
   *InnoDB使用场景：
4. 数据增删改查都非常频繁
5. 可靠性要求很高，要求支持事务

• 数据库锁的分类：

1. 按锁的粒度划分，分为表级锁，页级锁，行级锁
2. 按锁的级别划分，分为共享锁，排它锁
3. 按加锁方式划分，自动锁，显式锁
4. 按操作划分，分为DML锁，DDL锁
5. 按使用方式划分，分为乐观锁，悲观锁

二、数据库事务的四大特性（ACID）

• A：原子性（Atomic）
• C：一致性（Consistency）：如果用一个转账的例子来说，要保持一致性，就不能出现当A成功转钱给B时，但此时B还没有收到钱（有一定延时），但是在这个区间内差A的余额少了，B的余额不变，出现了不一致问题。所以要保证这个问题不发生，即保持一致性，只允许查询转账前的结果和转账后的结果，不允许有中间结果。
• I：隔离性（Isolation）
• D：持久性（Durability）

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

• 更新丢失：（两个事务同时对同一个数据进行更新，后一个覆盖了前一个事务的更新数据） --mysq所有事务隔离级别在数据库层面上均可避免。
• 脏读：（一个事务读取了另外一个事务修改后但是未提交的数据，但是另一个事务因为特殊原因回滚了，到时第一个事务读到的是脏数据） – READ-COMMITED事务隔离级别可以避免。
• 不可重复度：（一个事务多次读取同一数据，得到的结果不一致（有其他事务在修改该数据），造成读取的数据是不可靠的） – REPEATABLE-READ事务隔离级别以上可以避免。
• 幻读：（一个事务多次检索数据发现每一检索数据的条数都不一致（有其他事务在插入数据）） – SERIALIZABLE事务隔离级别可以避免

四、InnoDB可重复读隔离级别下如何避免幻读

• 表象：快照读（非阻塞读） --伪MVCC，只有在R-R隔离级别下快照读才有效
• 内在：next-key锁（行锁+gap锁）

当前读：select … lock in share mode, select … for update
当前读：insert、update、delete
快照读：不加锁（事务隔离级别在不为SERIALIZABLE前提下）的非阻塞读，select
快照读是什么意思？
答：快照读的意思是读取下一次读取的数数据有可能是以前版本的数据，更新数据时快照不会改变。

next-key锁（行锁+gap锁）

gap锁（间隙锁，键值在条件范围内，但是并不存在的记录，如 表user中只有id为1,2，3的三条记录，select * from user where id = 4 ，该记录就是间隙）

• 对主键索引或唯一索引会用gap锁吗？

1. 如果where条件全部命中，则不会用gap锁，只会加记录锁
2. 如果where条件部分命中或命中不全，则会加gap锁
   gap锁会用在非唯一索引或不走索引的当前读中
   走非唯一索引：
   
   如图中底部为Gap上锁的区间，如果对 id为9 的行操作会对（6,9]， (9,11] 两个区间进行上锁，当要插入id为9的数据时就要等待

不走索引：

会将整个表的全部字段上锁，代价非常大，需要避免

五、RC、RR级别下的InnoDB的非阻塞读如何实现（快照读）

依赖一下几个因子：

1. 数据行中的DB_TRX_ID (与事务相关)、DB_ROLL_PTR (回滚指针)、DB_ROW_ID (隐藏主键字段)
2. undo日志 （存储老版本的数据）insert undo log 、 update undo log
   一下图的每一行代表一条update日志
   
3. read view ：用于做可见性判断，用一个可见性算法判断当前快照读读取的是数据是undo日志中的记录还是最新的记录。可见性算法是根据DB_TRX_ID 与系统其它事务活跃ID做对比，如果大于或等于就根据DB_ROLL_PTR 指针取出undo日志中的记录，保证获取到的数据是最稳定的版本。

重要语法

• GROUP BY
  1. 满足“SELECT子句中的列名必须为分组列或列函数”
  2. 列函数对于group by子句定义的每一个组各返回一个结果

group by 里出现的某个表的字段，select里面的列要么是该group by里出现的列，要么是别的表的列或者带有函数的列

• HAVING

1. 通常与GROUP BY子句一起使用
2. WHERE进行行过滤，HAVING 进行组过滤
3. 出现在SQL的顺序 WHERE -> GROUP BY -> HAVING
4. 如果省略GROUP BY，HAVING 的作用于WHERE类似

三大范式：

第一范式： 第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库满足第一范式。
第二范式： 第二范式需要确保数据库表中每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）
第三范式： 第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。（消除传递依赖）