一、数据库主要考点

image.png

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

image.png

二、索引模块

常见问题

  • 为什么需要要索引？
  • 什么样的信息能成为索引？
  • 索引的数据结构
  • 密集索引和稀疏索引的区别

2.1 为什么要使用索引？

使用索引可以快速查询数据。

2.2 什么样的信息能成为索引

索引数据结构：

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

二叉查找树：每个节点存储关键字和指向子树的指针，图中的P1和P2就是执行子树的指针。

image.png

对于树中的每个节点的左子树均小于当前节点的值，右子树均大于当前节点的值，这棵树不仅仅是二叉树还是平衡二叉树。任意一个节点的左子树高度只差均不超过一，就是平衡二叉树，O(logn)

image.png

2.3 B-Tree

如果每个节点最多有m个孩子那么这样的树就是M阶B树

image.png

上图为三阶B树。

B树的定义：

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

注意：解释一下第二条性质，比如上图3阶B树，3/2=1.5，这里会舍弃小数位，也就是除了根和叶子节点，其他节点至少为一个。

建立B树的目的是为了减少树的高度，存储更多的信息，减少io次数。

假设每个非终端节点中包含有n个关键字信息，其中
a) Ki(i=1…n)为关键字，且关键字按顺序升序排序K(i-1)b)关键字的个数n必须满足：[ceil(m / 2)] <= 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树和二叉查找树的查找效率是一样的。

2.4 B+-Tree

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

  • 非叶子节点的子树指针与关键字个数相同
  • 非叶子节点的子树指针P[i],指向关键字值[K[i], K[i+1])的子树
  • 非叶子节点仅用来索引，数据都保存在叶子节点中。
  • 所有叶子节点具有一个链指针指向下一个叶子节点

image.png

结论：

  • B+Tree更适合用来做存储索引
  • B+树的磁盘读写代价更低
  • B+树的查询效率更稳定
  • B+树有利于对数据库的扫描

2.5 Hash索引

image.png

将hash值作为索引的缺点

  • 仅仅能满足”=”, “IN”,不能使用范围查询，因为hash值是无序的
  • 无法用来避免数据的排序操作
  • 不能利用部分索引键查询
  • 不能避免表扫描
  • 遇到大量Hash值相等的情况后性能不一定就会比B-Tree索引高。

2.6 位图索引

现在 已知只有Oracle，锁粒度大，可能添加删除都会被锁住。
image.png

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

密集索引：文件中的每个搜索码值都对应一个索引值，不仅保存了索引值，还保存了位于同一行的其他列信息，由于密集索引决定了数据的顺序，所以一个表密集索引只能有一个。

稀疏索引：文件只为索引码的某些值建立索引项，只保存键位信息及主键

image.png

2.7.1 MyISAM

特点：全部都是稀疏索引

2.7.2 InnoDB

InnoDB的特点：有且仅有一个密集索引

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

image.png

无数据

image.png

有数据

image.png

生成文件

image.png

innodb：

frm文件：为表结构信息
idb：索引和数据存在一起

myisam：
myd：存储数据（数据文件占0b，但是索引文件是不为0的）
myi：存储索引

innodb索引和数据存在一块，myisam索引和数据是分开的。

2.8 如何定位并优化慢查询Sql

  • 根据慢日志定位慢查询sql
  • 使用explain等工具分析sql
  • 修改sql或者尽量让sql走索引

可以看到慢查询是关闭的。

image.png

slow_query_log: 慢查询日志是否开启
slow_query_log_file:慢查询日志的位置
long_query_time:超过多少秒的查询算作慢查询。

查询系统的状态：慢查询的数量。
image.png

打开慢查询日志
image.png

查询慢查询日志是否打开

image.png

设置慢查询的时间：

image.png

image.png

重新打开连接更新，我们可以在配置文件中设置这些属性使用set global方式设置，重启数据库服务参数都会变为和之前一样。

本次会话的慢查询。

image.png

可以看到执行了三秒多，所以会被记录为慢查询

image.png

可以发现有一条慢查询日志了

image.png

根据日志位置查看日志信息：刚刚显示的3.3秒是不正确的要以慢查询日志为准，实际话费了6s

image.png

2.9 使用explain分析慢查询日志：

image.png

关键参数：type，表示找到数据行的方式，从最优到最差，all或index就代表全表扫描。
type结果的含义，性能从高到底。

image.png

关键参数：Extra，出现以下两个参数就说明我们有可能要去优化语句了。

image.png

那么如何优化呢？修改sql，尽量让sql走索引。上面表中account有索引，name没有索引
，更改sql根据account去排序，会多显示出一个key字段，表示我们用到了哪一个索引，这里显示的是account表示我们用的了这个索引。

image.png

虽然改进以后的sql还是进了慢查询日志，但是sql查询时间，明显提升，执行时间变为了4s。

image.png

或者通过给name加索引来实现。

image.png

这里要注意，ddl的sql不会计入慢sql。

重新执行
image.png

慢查询日志，只花费3.4s

image.png

判断下面的统计，会走哪一个索引

image.png

结果走了account索引，查询优化器来决定，大致原因，密集索引把数据也都存到叶子节点当中，效率会比稀疏索引要低。稀疏索引只保存索引信息，稀疏索引性能更好，所以走了稀疏索引。

插播一条count（1）和count（）之间没有区别，因为count（）count（1）都不会去过滤空值，但count（列名）就有区别了，因为count（列名）会去过滤空值。

image.png

执行强制索引，比较性能

image.png

重新执行这两条sql语句，结果，稀疏索引的效率要高一些

image.png

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

联合索引：即多列组成的索引。

image.png

会发现走了联合索引：

image.png

依然走联合索引

image.png

不走索引，走的全表扫描

image.png

image.png

image.png
首先对第一个字段排序，再第二个字段排序，第一个字段是绝对有序的，第二个开始就是无序的，类似于order by。

2.11 索引是建立的越多越好吗？

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

三、锁模块

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

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

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

3.1.1 MyISAM

有两张表，一张表person_info_large使用innodb作为存储引擎，另一张表person_info_myisam使用myisam作为存储引擎。

image.png

统计person_info_large的表数据

image.png

统计person_info_myisam表数据

image.png

下面执行一个查询id在1到200万之间的数据，sql执行时间很长，我们可以在另一个session中去做操作。

image.png

去更新两百万之外的数据，发现更新明显变慢了，因为表被锁住了，myisam查询就会上表锁。

image.png

再进行一次尝试，给person_info_myisam，强制上读锁。（这里如果上写锁就跟write）

image.png

再更新表中数据，也会被阻塞

image.png

只有释放锁，才能被进行更新。

  1. unlock tables;

读锁又叫共享锁，在进行范围查询，依然能进行读操作。共享锁，读读不互斥。下面先上写锁，再上读锁或者写锁，当上写锁再上读锁是不可能的，必须等写锁释放。一旦先上写锁，再上写锁，也是不可能的了，写锁又名排他锁。

update person_info_myisam where account = account where id between 1 and 2000000;

3.1.2 InnoDB

InnoDB使用二段锁。加锁和解锁是两步执行的，默认事务是自动提交的，提交之后释放锁。lock share mode上读锁，当前session加了共享锁之后其他session就无法加排他锁了，innoDB默认支持行级别锁。

set autocommit = 0; 关闭掉自动提交，只在当前session有效。

先读再读，是可以的。当不走索引的话，整张表都会被锁住，也就是InnoDB的行级锁是基于索引的。

image.png

3.2 数据库锁的分类

按锁的粒度划分，可分为表级锁、行级锁、页级锁
按照锁级别划分，可分为共享锁、排他锁
按加锁方式划分，可分为自动锁、显式锁
按操作划分，可分为DML锁、DDL锁
按使用方式划分，可分为乐观锁、悲观锁

乐观锁：
乐观锁假设每一次都没有其他事务同时去修改数据，再更新的时候进行判断，如果和先前查出来的版本号不一致，则根据业务进行重试或者直接取消执行。

image.png

3.3 MyISAM和InnoDB适合场景

MyISAM适合场景

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

InnoDB合适的场景

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

3.4 数据库事务的四大特性

ACID

  • 原子性（Atomic）：要么全做，要么全不做
  • 一致性（Consistency）：从一个一致性状态，转化为另一个一致状态
  • 隔离性（Isolation）：多个事务并发执行，不影响其他事务执行
  • 持久性（Durability）：事务提交之后，改动应该永久保存在数据库。

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

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

更新丢失：mysql所有事务隔离级别在数据库层面均可避免

image.png

脏读: 事务读取到未提交的事务的数据，READ-COMMITTED事务隔离级别以上可避免

不可重复读：其他事务修改了数据并提交，当前事务几次读取数据不一致，REPEATABLE-READ事务隔离级别以上可避免

幻读：SERIALIZABLE事务隔离级别可避免

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

image.png

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

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

3.6.1 当前读和快照读

当前读：select…lock in share mode, select…for update
当前读：update，delete, insert

当前读：是加了锁的增删改查语句，读取的是数据最新版本，让其他事务不能改变数据。

快照读：不加锁的非阻塞读，select，在不为串行化才有快照读，串行化的快照读，等同于当前读，退化成当前读。

image.png

快照读基于MVCC，行级锁的变种，避免加锁操作开销更低，可能读到的不是最新版本和历史版本。

RC下：
一个事务先更改数据，提交事务，另一个事务执行快照读和当前读，读到的是一样的，读到的是更新后的数据。

RR下：
B事务先进行当前读
A事务先更改数据
B事务执行快照读和当前读

当前读为最新的数据，快照读还是600。
事务首次执行快照读的时机很重要，如果更改前没有执行快照读，快照读也能读到最新的数据。
RR级别下，创建快照的时机决定数据的版本。

3.6.2 快照读的实现

主要依据数据行立马的DB_TRX_ID、DB_ROLL_PTR、DB_ROW_ID等字段。

DB_TRX_ID: 用来字段用来标志，最后一次修改本行的事务id。
DB_ROLL_PTR: 回滚指针，只写入回滚段的undo 日志
DB_ROW_iD: 新行插入，单调递增的行id，聚集索引会包含这个值，如果没有主键也没有唯一键，创建的隐藏主键就是这个字段。

undo 日志
分为两种：
insert undolog:事务提交后就可以丢弃
update undolog：事务回滚需要，快照读也需要。

修改field2，从12到32，将undolog存到日志中。
image.png
其他事务又做了修改，从13改为45，又多了一条undolog记录，数据多个版本就是这样实现的。

image.png

readview主要是来进行可见性判断的，即当我们执行快照读select时，会针对我们查询的数据，创建一个read view，决定当前事务能看到的是哪个版本的数据，有可能是当前最新版本的数据，也有可能让你看某个版本的数据

readview遵循一个可见性算法，将要修改数据的DB_TRX_ID取出来，和系统其他活跃事务做对比，如果大于等于这些id，就通过DB_ROLL_PTR取出undolog上一层的DB_TRX_ID，直到小于这些活跃事务id为止，这样就保证了我们获取到的数据版本就是当前可见的最稳定的版本。

image.png

启动事务id，都会递增。

rr级别下，在创建事务之后，会创建一个快照readview，后面再读取，也会使用这个readview；
rc级别下，创建事务之后，每次创建快照读，都会创建新的快照。

rr和rc支持mvcc，多版本并发控制。不加锁，极大增加了性能，其实是伪MVCC机制，undolog其实是串行存储，不属于多版本共存。

3.6.3 RR级别如何避免幻读

首先可以明确的是,MVCC在快照读的情况下可以解决幻读问题,但是在当前读的情况下是不能解决幻读的。

真正防止幻读的是next-key锁（行锁+gap锁）

行锁，就是record lock。
gap锁：gap就是索引树中插入新纪录的空隙，gaplock间隙锁锁定一个范围，但是不包括记录本身。gap锁的目的是方式同一个事务两次当前读出现幻读的情况。

gap锁在rc等更低的事务隔离级别是没有的。rr和serilizable都支持gap锁。

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

如果where条件全部命中，则不会用Gap锁,只会加记录锁,精确查询所有数据都有，都命中了插入其他数据行，也没有影响。

唯一索引和主键索引（密集索引）也要加行锁。（考虑并发情况，一个根据唯一键删除，一个根据主键，更新唯一键）

image.png
如果where全部命中或者全部不命中，则会加Gap锁。
全部都没命中：删除一个不存在的值：
部分命中：执行当前读，选取部分数据。

部分命中，部分区间加上Gap锁，全部都没命中则所有区间上锁。

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

非唯一索引

这里做是对id为9，则会对(6,9],(9,11]上锁。还需要加上主键的值才能做出精确的判断，也要在主键的精确范围内。

image.png

不走索引：会对所有gap上锁，相当于锁表，这样上锁比表锁代价更大。

image.png

总结：
recordlock：即行级锁
gaplock：会用于非唯一索引或者不走索引的情况下的当前读，以及仅命中检索条件的部分结果集并且用到主键索引以及唯一索引的当前读中，

四、关键语法

GROUP BY

HAVING

统计相关：COUNT,SUM,MAX,MIN,AVG

group by:对查询结果进行分组统计，select子句必须为分组列或者列函数如sum，这个条件只针对同一张表成立。

列函数对于group by子句定义的每一个组各返回一个结果。

group by会用到临时表可以用explain查看。

HAVING,与group by配合使用，where过滤行，having过滤组，
出现顺序：where group by having

五、sql的优化

1、在表中建立索引，优先考虑where、group by使用到的字段。

2、尽量避免使用select ，返回无用的字段会降低查询效率。如下：
SELECT FROM t
优化方式：使用具体的字段代替，只返回使用到的字段。

3、尽量避免使用in 和not in，会导致数据库引擎放弃索引进行全表扫描。如下：
SELECT FROM t WHERE id IN (2,3)
SELECT FROM t1 WHERE username IN (SELECT username FROM t2)
优化方式：如果是连续数值，可以用between代替。如下：
SELECT FROM t WHERE id BETWEEN 2 AND 3
如果是子查询，可以用exists代替。如下：
SELECT FROM t1 WHERE EXISTS (SELECT FROM t2 WHERE t1.username = t2.username)

4、尽量避免使用or，会导致数据库引擎放弃索引进行全表扫描。如下：
SELECT FROM t WHERE id = 1 OR id = 3
优化方式：可以用union代替or。如下：
SELECT FROM t WHERE id = 1
UNION
SELECT FROM t WHERE id = 3
（PS：如果or两边的字段是同一个，如例子中这样。貌似两种方式效率差不多，即使union扫描的是索引，or扫描的是全表）

5、尽量避免在字段开头模糊查询，会导致数据库引擎放弃索引进行全表扫描。如下：
SELECT FROM t WHERE username LIKE ‘%li%’
优化方式：尽量在字段后面使用模糊查询。如下：
SELECT FROM t WHERE username LIKE ‘li%’

6、尽量避免进行null值的判断，会导致数据库引擎放弃索引进行全表扫描。如下：
SELECT FROM t WHERE score IS NULL
优化方式：可以给字段添加默认值0，对0值进行判断。如下：
SELECT FROM t WHERE score = 0

7、尽量避免在where条件中等号的左侧进行表达式、函数操作，会导致数据库引擎放弃索引进行全表扫描。如下：
SELECT FROM t2 WHERE score/10 = 9
SELECT FROM t2 WHERE SUBSTR(username,1,2) = ‘li’
优化方式：可以将表达式、函数操作移动到等号右侧。如下：
SELECT FROM t2 WHERE score = 109
SELECT FROM t2 WHERE username LIKE ‘li%’

8、当数据量大时，避免使用where 1=1的条件。通常为了方便拼装查询条件，我们会默认使用该条件，数据库引擎会放弃索引进行全表扫描。如下：
SELECT * FROM t WHERE 1=1
优化方式：用代码拼装sql时进行判断，没where加where，有where加and。