数据库知识点总结

一、数据库架构

关系型数据库：架构、索引、锁、语法、理论范式

Q：如何设计一个关系型数据库？

程序实例 存储管理、缓存机制、SQL解析、日志管理(binlog)、权限划分、容灾机制、索引管理、锁模块

存储（文件系统）

数据库以块或者页作为最小存储单位

二、索引模块

为什么要使用索引

• 快速查询数据

什么样的信息能成为索引

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

索引的数据结构

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

二叉查找树

左子树的节点值均小于等于根节点的值，右子树的节点值均大于等于根节点的值。

查找的时间复杂度为：O(logn)

缺点：每查找一次，就需进行一次IO，且二叉树存在退化成线性结构的可能，查询时间复杂度退化为：O(n)

平衡二叉树

任意根节点的左右子树的高度差不超过1。

B-Tree（平衡多路查找树）

定义：

• 根节点至少包含两个孩子
• 树中每个节点最多含有m个孩子（m>=2），m取决于节点容量和其他配置
• 除根节点和叶节点外，其他每个节点至少有ceil(m/2)个孩子
• 所有叶子节点都位于同一层

每一个节点尽可能多存储信息，减低树的高度，减少查询时的IO次数

假设每个非终端节点中包含有n个关键字信息，其中

a) ki(i=1...n)为关键字，且关键字按顺序升序排序k(i-1)<=ki

b) 关键字的个数n必须满足：[ceil(m/2)-1]<=n<=m-1

c) 非叶子节点的指针：P[1],P[2],...P[m];其中P[1]指向关键字小于k[1]的子树；P[m]指向关键字大于k[m]的子树；其他P[i]指向关键字属于（k(i-1),ki）的子树。

B+树

B+树是B树的变体，其定义基本与B树相同，除了：

• 非叶子节点的子树指针与关键字个数相同
• 非叶子节点的子树P[i]，指向关键字值（K[i],K[i+1]）的子树
• 非叶子节点仅用来索引，数据都保存在叶子节点中
• 所有叶子节点均有一个链指针指向下一个叶子节点（顺序排列，可用于范围统计）

结论：B+树更适合用来做存储索引

• B+树的磁盘读写代价更低
• B+树的查询效率更加稳定
• B+树更有利于对数据库的扫描

Hash索引

缺点：

• 仅仅能满足“=”，“IN”，不能使用范围查询。（hash后的值与原来的值的大小关系不一定一致）
• 无法被用来避免对数据的排序操作
• 不能利用部分索引键查询（组合索引）
• 不能避免表扫描
• 遇到大量hash值相等的情况后性能并不一定就会比B-Tree索引高

BitMap索引是个神器

只适用于某字段的值只有固定的几个值的情况！！！

另外“锁”力度非常大，适用于并发较少、统计较多的系统

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

• 密集索引文件中的每个搜索码值都对应一个索引值
• 稀疏索引文件只为搜索码的某些值建立索引项（如只保存了键位信息）

InnoDB

• 若一个主键被定义，该主键则为密集索引
• 若没有主键被定义，该表的第一个唯一非空索引则作为密集索引
• 若不满足以上条件，innodb内部会生成一个隐藏主键（密集索引）
• 非主键索引存储相关键位和其对应的主键值，包含两次查找

如何定位并优化慢查询sql

具体场景具体分析，大致思路：

• 根据慢日志定位慢查询sql

使用命令：show variables like '%quer%'；查看：

slow_query_log，需打开：set global slow_query_log = on;；

slow_query_log_file，慢sql日志文件；

long_query_time，慢sql时间设定：set global long_query_time = 1;可能需要重连或重启mysql才能生效，或者直接修改配置文件my.ini永久保存；

使用命令：show status like 'slow_queries'；查询慢查询数量

• 使用explain等工具分析sql

关注以下几列：

id：越大越先执行，复合查询越里面越先执行

type：表示mysql找到所需信息的方式。（“index”和“all”表示全表扫描）

extra：

 

• 修改sql或者尽量让sql走索引

注：mysql查询优化器会优先走索引，且走条件更严格的索引（如唯一索引）来排除更多的数据。

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

1、最左前缀匹配原则，非常重要的原则，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的顺序可以任意调整。

2、=和in可以乱序，比如a = 1 and b = 2  and c = 3建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。

mysql会首先对复合索引的第一个字段进行排序。再对第二个、第三个...字段进行排序。如果复合索引为两个字段，则第一个字段的数据就是有序的，第二个字段就是无序的，用第二个字段进行搜素，就用不到索引。

索引是建立得越多越好吗

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

三、锁模块

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

• MyISAM默认用的是表级锁，不支持行级锁。当使用select语句时，会将整个表加上一个读锁，此时另外的查询语句不会被影响，读锁也叫共享锁，写锁会被影响；使用update、insert、delete语句时，会将整个表加上一个写锁，其他的查询和更新语句都会受影响，写锁也叫排他锁。查询语句也可以加排他锁，在select语句后面加 for update
• InnoDB默认用的是行级锁，也支持表级锁。InnoDB默认使用二段锁，即：加锁和解锁，先对同一个事务里的一批操作加上锁，commit后再统一解锁，支持事务，自动提交。InnoDB的select语句不会加共享锁，即可以同时更新select的那些行，显示加共享锁：lock in share mode;加上之后，不能对select的那些行进行更新，必须等select语句commit后才能更新，但是是可以读的。当操作不走索引的时候用的是表级锁。

表级锁跟索引无关；行级锁除了主键id外其余索引都会被加上共享锁或排它锁。

共享锁和排他锁的兼容性：

	排他锁	共享锁
排他锁	冲突	冲突
共享锁	冲突	兼容

MyISAM适合的场景

• 频繁执行全表count语句
• 对数据进行增删改的频率不高，查询非常频繁
• 没有事务

InnoDB适合的场景

• 数据增删改查都非常频繁
• 可靠性要求比较高，要求支持事务

数据库锁的分类：

• 按锁的粒度划分，可分为表级锁，行级锁，页级锁
• 按锁级别划分，可分为共享锁，排他锁
• 按加锁方式划分，可分为自动锁，显式锁
• 按操作划分，可分为DML锁（对数据操作），DDL锁（对表结构变更）
• 按使用方式划分，可分为乐观锁，悲观锁

数据库事务的四大特性

ACID

• 原子性（Atomic），全都做或全都不做。
• 一致性（Consistency），指数据库的数据应满足完整性
• 隔离性（Isolation），多个事务并发执行时，一个事务不应该影响其他事务的执行
• 持久性（Durability），事务一旦提交，其修改应该永久保存在数据库中，用于数据恢复

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

事务并发访问引发的问题以及如何避免

• 更新丢失——mysql所有事务隔离级别在数据库层面上均可避免
• 脏读——READ-COMMITTED事务隔离级别以上可避免。设置当前事务隔离级别：set session transation isolation level read uncommitted;开启事务：start transation。指一个事务读到另一个事务的未提交数据，例如session1更新了数据，但是还未提交，session2此时读到的数据即为session1更新后的数据，但是session1因为某些原因回滚了，session2读到的由session1更新后的数据就是不存在的，即为脏读。将读的事务隔离级别设置为read committed;(即只能读取事务提交后的数据)可避免这种情况，oracle数据库默认的事务隔离级别就是read committed
• 不可重复读——REPEATABLE-READ事务隔离级别以上可避免。事务A多次读取某条数据，事务B在事务A读取的过程中进行了更新并提交，导致事务A多次读取同一数据时结果不一致。解决办法：将事务隔离级别再调大一级：REPEATABLE-READ（可重复度），mysql默认的事务隔离级别就是REPEATABLE-READ。此时事务A每次读的数据都是一样的，别的事务所做的更新不会影响事务A读的结果，但是事务A的更新是基于其它事务提交后的结果再进行操作。既避免了脏读，又保证了一致性。
• 幻读——SERIALIZABLE事务隔离级别可避免（最高隔离级别）。事务A读取若干行，事务B以插入或删除行的方式来修改事务A的结果集，导致事务A像出现幻觉一样。例如session1使用当前读：select * from xx;session2使用插入操作：insert into xx values(...);此时session1进行更新xx表，发现多出来一条数据，就像出现幻觉一样，即为幻读。此隔离级别下所有的数据库操作都会自动加上锁。session1读的事务（会自动加上lock in share mode）提交后session2的更新操作才能执行。

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

• 表象：快照读（非阻塞读）--伪MVCC
• 内在：next-key锁（行锁+gap锁）

当前读和快照读

• 当前读：select ... lock in share mode，select ... for update
• 当前读：update，delete，insert
• 快照读：不加锁的非阻塞读(前提是事务隔离级别不为serializable)，select。为了多并发，读取的数据可能不是最新版本。

当前读：读取数据的最新情况，并加锁

RC事务隔离级别下，当前读和快照读读到的数据是一样的；RR事务隔离级别下，当前读能读到最新数据，快照读可能读到历史版本也可能读到最新版本。RR事务隔离级别下首次创建快照读决定了读取数据的版本。

next-key锁（行锁+gap锁）

• 行锁
• Gap锁（会锁住数据周围的间隙），比如session1：delete ... where id=9，session2：insert ... id=10；session2会被锁住。

对主键索引或者唯一索引会用Gap锁吗

• 如果where条件全部命中，则不会用Gap锁，只会加记录锁。
• 如果where条件部分命中或全不命中，则会加Gap锁。

Gap锁会用在非唯一索引或者不走索引的当前读中

 

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

• 数据行里的DB_TRX_ID（最后一次对本行数据操作的事务ID）、DB_ROLL_PTR（回滚指针）、DB_ROW_ID（行ID）字段
• undo日志（insert undo log、update undo log）。当对数据进行更新操作时就会记录undo日志，存储老版数据。
• read view。决定select语句可见哪个版本的数据

四、语法部分

关键语法

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

GROUP BY

• 满足“SELECT子句中的列名必须为分组列或列函数”
• 列函数对于group by子句定义的每个组各返回一个结果
• group by里出现某个表的字段，select里面的列要么是该group by里出现的列，要么是别的表的列或者带有函数的列

HAVING

• 通常与group by子句一起使用
• where过滤行，having过滤组
• 出现在同一sql的顺序：where>group by>having
• 如果没有group by，having的作用同where