2、数据库相关

1、主要考点思维导图

2、如何设计一个关系型数据库

存储管理：数据逻辑关系转为物理存储关系。

缓存机制：优化执行效率。

SQL解析：将Sql语句进行解析。

日志管理：记录操作。

权限划分：多用户管理。

容灾机制：灾难恢复模块。

索引管理：优化数据数据查询效率。

锁管理：使数据库支持并发操作。

3、索引模块

为什么要使用索引？

• 快速查询数据。

全表扫描：将全部数据加载到内存中，只有少量数据，进行全表扫描，将数据加载到内存中，进行数据查询。

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

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

索引的数据结构？

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

4、二叉查找树

二叉查找树的性质：

对于某个节点来说，左子树的任意节点的值均小于此节点，右子树的任意节点的值均大于此节点。

平衡二叉树： 任意一个节点的左右子树高度差不超过一。

一个平衡二叉树经过删除与插入容易退化成线性的二叉树，但是影响程序运行速度的主要是I/O，如果我们把这些索引块都放在磁盘中，拿左图中的二叉查找树来讲，去找6，会发生三次io分别将5,7,6加入进内存中，即检索深度每增加一机会增加一次I/O。

而我们的数据很多，树的深度就会很深，I/O 的次数也会很多，这样数据一多，就会比全表扫描都慢，所以为了降低I/O的次数我们要将树的高度变得矮一些，即每个节点能存储的数据多一些。

5、B Tree

如果每个节点最多有m个孩子，那么这样的树就是m阶b树。如图就是一个三阶b树的样子。

现实中每个索引的孩子数上限是远大于3的，每个块中主要包括关键字和指向孩子的指针，最多能有几个孩子取决于存储快的容量与数据库的相关配置，通常情况是m是很大的。

当B-Tree进子节点的增删时，b树通过上移下移分裂来保持特征，所以不会变成线性的情况。

B Tree定义：

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

假设每个非终端节点中包含有n个关键字信息，其中（关键字数量以及大小）

• a）Ki（i=1…n）为关键字，且关键字按顺序升序排序Ki-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-1]的子树，其它[P]指向关键字属于（K[i-1]，K[i]）的子树。

通过合并，分裂，上移下移节点。来保持它的特性，数据远比二叉树要矮很多，并且不会成为线性的结构。

B+Tree

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

• 非叶子节点的子树指针与关键字个数相同。
• 非叶子节点的子树指针P[i]，指向关键字值[K[i]，K[i+1]）的子树。
• 非叶子节点仅用来索引，数据都保存在叶子节点中。
• 所有叶子节点均有一个链指针指向下一个叶子结点(方便横向跨子树做统计)。

B+Tree更适合用来做存储索引

• B+树的磁盘读写代价更低(不存放数据，内部节点更小)。
• B+树的查询效率更加稳定(数据都存在根节点，每次查询的次数都是固定的)。
• B+树更有利于对数据库的扫描(遍历叶子节点就能得到全部索引)。

6、Hash索引

优点：

缺点：

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

因为不稳定。也不能进行范围查询。

7、BitMap索引(位图索引)

还有一种hash索引是BitMap索引,感兴趣可以自己再去网上查一下(mysql不支持).

8、密集索引和稀疏索引

密集索引：

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

• 密集索引：保存的不仅仅是键值(索引值)，还保存了位于同一行记录里的其它信息，由于密集索引决定了表的物理排列顺序，一个表只能有一个物理排列顺序，所以一个表只能创建一个密集索引。
• 稀疏索引：叶子节点仅保留了键位信息以及该行数据的地址，有的稀疏索引是仅保存了键位信息与其主键，定位到叶子节点后依然需要通过地址或者主键信息进一步定位到数据。

Mysql存储引擎：

MyISAM：所有主键,唯一键,普通索引都是稀疏索引。

InnoDB：必须有且只有一个密集索引。

InnoDB：

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

**注意：**非主键索引也就是稀疏索引，它的叶子节点并不存储行数据的物理地址，而是存储的是该行的主键值，所以非主键索引包含了两次查找，先索引自身，再查找主键。

示例:

InnoDB将主键存储到一个B±Tree中，行数据存储到叶子节点上，InnoDB的主键索引和对应的数据是保存在一个文件当中的，所以检索的时候在加载叶子节点的主键进入内存的同时，也加载了对应的数据。

若对稀疏索引进行条件筛选需要两个步骤，首先在稀疏索引的B±Tree中检索该键，然后定位到主键信息，获取到主键信息后，再使用主键从主键索引中查找。

创建表的时候经常有primary key,unique key ,foreign key, index等

primary key 有两个作用，一是约束作用（constraint），用来规范一个存储主键和唯一性，但同时也在此key上建立了一个主键索引；

unique key 也有两个作用，一是约束作用（constraint），规范数据的唯一性，但同时也在这个key上建立了一个唯一索引；

foreign key 也有两个作用，一是约束作用（constraint），规范数据的引用完整性，但同时也在这个key上建立了一个index；

key与index就一个作用,定义一个索引.

为什么要使用索引？

索引避免全表表扫描查找数据，提升检索效率。

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

主键，唯一性，让数据具有区分性的字段，都能成为索引。

索引的数据结构？

主流是B+Ttree、hash接口和bitMap等。

MySQL：不支持bitMap。不显示支持hash结构。基于InnoDB和MyisAM引擎。

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

9、调优SQL

如何定位并优化查询SQL？

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

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

如何定位并优化查询SQL？

1. 根据慢日志定位慢查询SQL？
2. 使用explain等工具分析SQL？
3. 修改SQL或者尽量让SQL走索引？

查看与慢日志相关配置信息

show variables like '%quer%';

慢查询的数量

show status like '%slow_queries%';

设置慢日志查询参数(有些参数，需要重连数据库生效)（重启数据库恢复原值）(永久保存需要改本地配置参数)

set global slow_query_log = on;    //开启慢日志查询
set global long_query_time = 1;    //设置慢日志保存时间1S

查看慢日志文件：

sudo vim /usr/local/mysql/data/baidu-Pro-slow.log

使用explain 进行查询

explain select name from person_info_large

type：MySQL找到数据行的方式(性能依次降低)

Extra：

Extra中出现以下2项意味着MYSQL根本不能使用索引，效率会受到重大影响。应尽可能对此进行优化。

解决方式：

1. 能走索引的，要换乘走索引的字段。
2. 重新创建索引。

select count(id) from person_info_large;
select count(id) from person_info_large force index(primary);

force ：强制使用索引。

查询使用的account索引，而没有使用主键primary作为索引：

原因：

密集索引(主键)：存储了数据和索引，或者其它列的信息，

稀疏索引：只存储了关键字和主键的值。

一次可以在内存中加载更多的稀疏索引的值，因而效率更好，查询优化器，选择使用account稀疏索引。

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

创建的是联合索引（area+titile）index_area_title

走的联合索引（index_area_title）

explain slecet * from person_info_large where area = 'aaaa' and titile = 'bbbb';

走的联合索引（index_area_title）

explain slecet * from person_info_large where area = 'aaaa';

没有去走联合索引（index_area_title）

explain slecet * from person_info_large where titile = 'bbbb';

最左匹配原则定义：

• 1、最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询（>、<、between、like）就停止匹配。比如a=3 and b=4 and c>5 and d=6如果建立（a，b，c）顺序的索引，d是用不到索引的，如果建立（a，b，d，c）的索引则都可以用到，a，b，d的顺序可以任意调整。
• 2、=和in可以乱序，比如a=1 and b=2 and c=3建立（a，b，c）索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。

最左匹配原则的成因：

• 创建复合索引的规则是：首先对复合索引最左边的第一个数据进行排序，在第一个索引的基础上再对第二个索引进行排序。
  
• 类似于order by字段1，order by 字段2，第一个字段是绝对有序的，第二个字段就是无序的了，所以我们如果直接使用第二个条件判断，我们是用不到索引的。
  
• (B±Tree)是根据索引字段值的大小进行构建的，乱序的话大于小于的查找没有意义，所以我们要强调最左匹配。
  

11、索引是建立的越多越好吗？

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

锁

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

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

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

MyISAM进行查询的时候，会加上一个表级的读锁，

为数据库表，添加锁、去除锁。

lock table person_info_myisam read;
unlock tables;

读锁：共享锁，在当前正在进行读操作，其它再进行读操作，没有影响，要是进行写操作，就需要等读结束后，才能进行写的操作。

普通查询：

select * from person_info_myisam;

为读操作，添加排它锁

select * from person_info_myisam for update;

写锁：排它锁。

13、行级锁(InnoDB)

由于InnoDB支持事务,

使用的是二段锁：加锁和解锁是分为两个步骤进行的。

查询事务是否默认提交的(ON为默认提交)

show variables like 'autocommint';

关闭自动提交(只对当前session有用)

set autocommit = 0;

案例：

添加共享锁(读锁)

select * from peron_info_large where id = 3 lock in share mode;
commit;

再进行写操作(添加写锁）

upate person_info_large set title = "test3" where id =3;
commit;

后面的写操作是需要等待。

证明： 加了共享读锁以后，再加写锁是需要等待的。

案例2：

先对第3列添加共享读锁，再更新第4列的数据。

对第3列加锁。

select * from peron_info_large where id = 4 lock in share mode;
commit;

对第4列更新数据。

upate person_info_large set title = "tes4" where id =4;
commit;

没有等待就执行完毕。

证明： InnoDB添加的是行级锁。只对当前行添加锁。

表级锁与索引无关。

行级锁与索引有关：涉及到的行都会被上共享锁或排它锁。

在SQL没有用到索引的时候，走的是表级锁。用到索引的时候，用的是行级锁和gep锁(普通非唯一索引)。

InnoDB支持锁类型：

行级锁：

表级：逆向锁，共享读锁IS，排它写锁IX。

14、两个引擎的优劣

优劣问题:

行级锁不一定优于表级锁。相比表级锁在表的头部直接加锁来讲，行级锁还要扫描到表的某行进行加锁，这样代价比较大，InnoDB支持事务的同时，也比MyISAM带来了更大的开销。

MyISAM的适用场景

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

InnoDB适合的场景

• 数据库锁的分类
• 按锁的粒度划分：表级锁、行级锁、页级锁
• 按锁级别划分：共享锁、排它锁。
• 按加锁方式划分：自动锁、显式锁
• 按操作划分：分为DML锁、DDL锁
• 按使用方式划分：乐观锁、悲观锁数据增删改查，都相当频繁。
• 可靠性要求高，要求支持事务。

悲观锁(依靠数据提供的锁机制)：

对外界的修改持保守态度，在数据处理过程中对数据处于锁定状态。悲观锁的实现往往依靠数据库提供的锁机制，也只有数据库提供的锁机制才能真正的保证数据库访问的排他性。先取锁，再访问。

乐观锁:

乐观锁认为数据一般情况下不会造成冲突，所以在数据进行更新的时候才会进行数据冲突与否的检测。一般乐观锁的实现是记录数据版本：一种是使用版本号，一种是使用时间戳。

乐观锁使用案例：

建表语句

CREATE TABLE 'test_innodb'(
    'id' int(2) NOT NULL AUTO_INCREMENT,
    'money' int(3) DEFAULT NULL,
    'version' int(3) NOT NULL DEFAULT '0'，
    PRIMARY KEY('id')
)ENGINE = InnoDB AUTO_INCREMENT = 5 DEFAULT CHARSET =ut8;

设置事务自动提交

#查看session是否为自动提交
show variables like 'autocommit';
#将session设置为自动提交
set autocommit =1 ;

1、先读取test_innobd的数据，得到Version的值为versionValue

#`结果为：0
select version from test_innodb where id =2 ;

2、每次更新test_innodb表中的money字段时候，为了防止发生冲突，先去检查version再做更新，更新成功的话 version+1

update test_innodb set money = 123,version = 0+1 where version = 0 and id =2;

结果：

version=0：执行成功。数据写入，version=1；
version=1：执行失败。写入失败，错误返回调用者，由调用者进行处理。(库中为1，查询where为1)

15、数据库事务的四大特性

ACID:

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

原子性（Atomic）

• 一个事务的执行被视为一个不可分割的最小单元。事务里面的操作，要么全部成功执行，要么全部失败回滚，不可以只执行其中的一部分。

一致性（Consistency）

• 一个事务的执行不应该破坏数据库的完整性约束。如果上述例子中第2个操作执行后系统崩溃，保证A和B的金钱总计是不会变的。

隔离性（Isolation）

• 事务之间相互独立，互不干挠。

持久性（Durability）

• 事务提交之后，需要将提交的事务持久化到磁盘。即使系统崩溃，提交的数据也不应该丢失。

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

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

• 更新丢失——mysql所有事务隔离级别在数据库层面上均可避免
• 脏读

脏读是指在一个事务处理过程里读取了另一个未提交的事务中的数据。

也就是不能读到前一个事务未完成,未commit的数据。只有commit后才能读到更新后的数据，没有commit，则还是之前的数据。

• 不可重复读

线程1：在当前线程内，多次读，结果不一致(读到了另一事务为commit的数据)

不可重复读是指在对于数据库中的某个数据，一个事务范围内多次查询却返回了不同的数据值，这是由于在查询间隔，被另一个事务修改并提交了,主要是针对update。

• 幻读
  例如事务T1对一个表中所有的行的某个数据项做了从“1”修改为“2”的操作，这时事务T2又对这个表中插入了一行数据项，而这个数据项的数值还是为“1”并且提交给数据库。而操作事务T1的用户如果再查看刚刚修改的数据，会发现还有一行没有修改，其实这行是从事务T2中添加的，就好像产生幻觉一样，这就是发生了幻读。幻读主要是针对insert与delete。
  

更新丢失：

脏读：（脏读是指在一个事务处理过程里读取了另一个未提交的事务中的数据。）

查看事务隔离界别

select @@tx_isolation;

设置事务隔离级别

set session transation isolation level read uncommitted;

事务隔离级别

• READ UNCOMMITTED（读未提交）：事务中的修改，即使没有提交，在其他事务也都是可见的。事务可以读取未提交的数据，这也被称为脏读。
• READ COMMITTED（读已提交）：一个事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的。这个级别有时候也叫做不可重复读，因为两次执行相同的查询，可能会得到不一样的结果。因为在这2次读之间可能有其他事务更改这个数据，每次读到的数据都是已经提交的。
• REPEATABLE READ（可重复读）：解决了脏读，也保证了在同一个事务中多次读取同样记录的结果是一致的。但是理论上，可重读读隔离级别还是无法解决另外一个幻读的问题，指的是当某个事务在读取某个范围内的记录时，另外一个事务也在该范围内插入了新的记录，当之前的事务再次读取该范围内的记录时，会产生幻行。
• SERIALIZABLE（可串行化）：它通过强制事务串行执行，避免了前面说的幻读的问题

16、InnoBD可重复读隔离级别下如何避免幻读

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

当前读和快照读：

• 当前读：select…lock in share mode ,select … for update
• 当前读：update，delete，insert 加了锁的增删改查语句。
• 快照读：不加锁的非阻塞读，select

当前读：

当前读就是加了锁的增删改查语句，不管上的是共享锁还是排它锁上的都是当前读，因为它读取的是最新版本，读取后还保证其它并发事务不能读取当前记录。对读取的记录加锁，除了select lock… 会加共享锁之外，其它的操作加的都是排它锁。

update、delete、insert 也都是当前读，RDBMS主要由程序实例和存储组成，如图所示。程序实例在这里指的是mysqlServer的程序实例，存储就是InnoDB。

拿update来举个例子：

当update发送给mysql之后，mysqlServer会根据where读取第一条满足where的条件记录，InnoDB会将第一条数据返回并加锁。mysqlServer收到加锁的记录后会发起一个update操作，去更新这条记录，一条记录读取完成后再去读取下一条记录，直至没有满足条件的记录出现。

update操作就包括一个当前读来获取数据的最新版本，就如之前在已提交读的隔离级别下出现的幻读的情况一样，由于先前事务新提交了一个数据，当前事务update全表的时候就莫名其妙多了一条数据，即读取到了数据的最新版本，同理delete也一样，insert会稍有不同，简单来说insert会触发唯一键的检查，也会进行一个当前读。

快照读：

快照读与当前读不太一样，它就是简单的select操作，不加锁，是在隔离级别不在串行化的条件下实现的，在serializable下由于是串行读，所以快照读也退化成当前读的lock in share mode的模型。

之所以出现快照读是基于提升并发性能的考虑，快照读的实现是基于多半版并发控制即MVCC，可以认为MVCC是行级锁的一个变种，但是它在很多情况下避免了加锁操作，因此：开销更低，但是快照读读取的可能不是最新版本，是历史版本。

在Read committed(读已提交)情况下当前读和快照读读到的数据是一样的。

在Repeatable read（可重复读）的情况下

情况1：

session1、session2都开启事务，先在session1中读取账户余额发现是600，在session2中修改账户余额为300，再在session1中用当前读查看账户余额为300，用快照读查询账户余额还是600。图中第一条语句为快照读，第二条语句为当前读。这里快照读读到的就是历史版本。

情况2：

session1、ession2都开启事务，我们在session2中更新账户余额，在session1中当前读与快照读查询到的都是最新版本。

在RR级别下可以让我们看不到幻读，是因为采用了伪MVCC机制，关于伪MVCC机制更多的可以去看第五章，其实伪MVCC机制有一些掩耳盗铃的感觉，已经做了更改就是看不见，真正实现避免幻读的还是使用了间隙锁。

17、RC、RR级别下的InnoDB的非阻塞读如何实现

• 数据行里的DB_TRX_ID、DB_ROLL_PTR、DB_ROW_ID字段。
• undo日志
• read view

非阻塞读也就是快照读，要实现快照读离不开三个因子

• DB_TRX_ID
• DB_ROLL_PTR
• DB_ROW_ID

数据行里的DB_TRX_ID、DB_ROLL_PTR、DB_ROW_ID字段：

每行数据记录除了存储数据外,还有额外的一些字段,其中最关键的是三个字段,DB_TRX_ID 、DB_ROLL_PTR、DB_ROW_ID。

DB_TRX_ID：

用来标识最近一次对本行做修改(不管是insert还是update)的事务的标识符，即最后一次修改本行事务的id，至于delete操作在InnoDB看来也不过是一次update操作，将行标识为deleted，也就是说数据行除了这3列，还有别的隐藏列，有个deleted的隐藏列，如果删除了就会将行列标识为deleted，并非真正的去做删除。

DB_ROLL_PTR：

回滚指针只写入回滚段roll_backsagment的undo日志记录，如果一行记录被更新，则undoLogRecord包含重建该行记录被更新之前内容所记录的信息。

DB_ROW_ID：

行号，包含一个随新行插入而单调递增的行id，当有InnoDB自动产生索引时，聚集索引会包含这个行id的值，否则这个行id不会出现在任何索引中。

undo日志

insert undoLog：

事务对insert新纪录产生的undoLog，只在事务回滚时需要，并且在事务提交后，就可以立即丢弃。

update undoLog：

讲解重点，事务对记录进行delete，update产生的undoLog，不仅在事务回滚时需要，在快照读也需要，不能随便删除，只有在数据库所使用的快照中不涉及该日志记录，对应的回滚日志才会被perge线程删除。

日志的工作方式:

假设将Field2中的值从12变成32；

修改的流程：首先用排它锁修改该行，将修改前的值拷贝一份到undoLog中，之后修改当前行的值,修改事务id(DB_TRX_ID)，使用回滚指针指向undoLog的修改前的行。

在这之后假如数据库还有别的快照读在用事务在读取该日志记录，那么对应的undoLog还没有被清除，此时又有事务对同一行数据做修改，那么效果和第一张图一样，又多了一条undoLog。

read view

当我们去执行快照读select时候，会针对我们select的数据创建出一个read view，来决定当前事务能看到的是哪个版本的数据，可能是当前最新版本的数据，也可能是undoLog中某个版本的数据，read view遵循一个可见性算法，将要修改的数据的DB_TRX_ID取出来，与系统其它活跃事务id做对比，如果大于或者等于这些事务id的话，就通过DB_ROLL_PTR去取出undoLog上一层的DB_TRX_ID，直到小于这些活跃事务id为止，这样就保证了我们获取到的事务版本是当前的最稳定的版本。

正是因为生成时机的不同，造成了RC、RR两种不同级别的可见性，在RR(读已提交)级别下，session在开启事务后的第一条快照读，会创建一个快照即read view，将当前系统中活跃的其它事务记录起来，此后在调用快照读的时候还是用的是同一个read view。而在read committed级别下，事务中每条select语句每次调用快照读的时候都会创建一个新的快照，这就是为什么在我们能在RC级别下看到别的事务的增删改。

而在RR(可重复读)下，如果首次快照读是在别的事务做出增删改并提交之前，此后别的事务做了提交也读不到修改的原因。

为什么是伪MVCC呢，因为实现多版本共存只是undo串行化的结果，并没有实际实现多版本共存。

18、next—key锁(行锁+gap锁)

• 行锁
• Gap锁

行锁：就是对单个行记录上的锁，上面也说了。

gap锁：

首先理解什么是gap，gap就是索引树中插入新数据的空隙，gapLock就是锁定一个范围但不包括记录本身。gap锁的目的是为了防止同一事务的两次当前读出现幻读的情况，因此我们抓重点，主要讨论gap锁的情况。

• gap锁在RC级别(以及更低的)是不存在的，所以这就是RC及更低的隔离级别无法避免幻读的原因。
• 在RR级别下支持gap锁。

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

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

全部命中：

精确查询的时候，如果id为1、3、5。均在此table中出现，那就是全部命中，如果只出现1,3时，那就是部分命中。

select * from table where id =1,3,5;

不加Gap锁(全部命中)：

我们这里执行删除id为9的数据，先给系数索引中的数据加上排它锁，再给密集索引中的数据加上排它锁。

加Gap锁( )：

部分命中包含了范围查询,精确查询

全不命中情况：

我们表中id为7和8的没有数据，表结构在图6红线处。

我们开启事务，删除id为7的数据(id=7不存在)。

delete from tb where id =7;

在另外一个session中插入id为8的数据

insert into tb values('i',8);

删除的为7，发现8被阻塞，证明7的间隙加了锁。

部分命中：

开启事务，执行语句18（5、9存在，7不存在）

select * from tb where id in(5,7,9) lock in share mode;

另一个session中执行插入操作

先插入4，成功。

insert into tb values ('ii'，4）；

再插入7，被block住，也就是说对5到9之间间隙加上了gap锁。

insert into tb values('ii',7);

插入8，被block住

insert into tb values ('ii',8);

插入10，成功

insert into tb values ('ii',10);

也就是部分命中也会部分加gap锁

全部命中：

开启事务，执行语句19

select * from tb where id in (5,6,9) lock in share mode;

插入7、8、9都成功，也就是都命中的话，不会加上gap锁。

19、非唯一索引与不走索引的gap锁情况

1）非唯一索引的情况

非唯一索引：

表结构如图7所示，有非唯一普通键id，在删除id为9的数据的过程中，如果我们增加了一个id为9的数据就会导致幻读，所以我们要锁住。

具体锁的范围的官方文档如图8所示，在这里我们删除id为9的数据时要锁的范围是(6,9],(9,11]上锁，当我们向其中插入数据时会上锁。同时根据字母表的排序来说,b<c<d，我们插入的小于(c,6)的就可以，如果在6-11之内就不行。

表中数据结构

我们开启事务,执行语句20,

delete from tb1 where id = 9;

锁住的范围为6~11范围内的行数据。

未关闭事务，在session中执行另外命令。

另一个session中执行插入操作

执行语句21，成功

insert into tb1 values('test',5);

执行语句22，被阻塞

insert into tb1 values('test1',7);

执行语句23，成功

insert into tb1 values('test12',12);

执行语句24，成功

insert into tb1 values('bb',6);

执行语句25，阻塞

insert into tb1 values('dd',6);

不走索引的情况

当前读不走索引的时候，会对所有的get都加上锁，也就是锁表，下图id是没有索引的，当删除id时，会将整张表锁住。

锁小结：

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

20、关键语法

关键语法：

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

1）GROUP BY

根据给定数据列的每个成员，对查询结果进行分组统计，得到分组汇总的表。

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

表关系

建表语句

1）查询所有同学的学号(student_id)、选课数(course_id)、总成绩(score)根据表score

select student_id,count(course_id),sum(score)
  from score
group by student_id

group by 使用规范：

• 如果使用group by ，那么你的Select 语句中选出的列，要么是你groupby里用到的列，要么就是带有之前我们说的如 sum min 等列函数的列。
• 列函数对于group by字句定义的每个组个返回一个结果。

2）查询所有同学的学号、姓名、选课数、总成绩

select stu.student_id,stu.name,count(course_id),sum(score)
  from student stu ,score sco
where
  stu.student_id = sco.studentid
group by stu.student_id;

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

1）HAVING

• 通常与GROUP BY子句一起使用
• WHERE过滤行，HAVING过滤组
• 出现在同一sql的顺序：WHERE>GROUP BY>HAVING
• where和having意义相同，只是顺序有差异
• having是对组内数据进行处理的，也就是先进行group by的组内数据。

1）查询平均成绩大于60分的同学的学号和平均成绩

select student_id,avg(score)
  from score
group by student_id
having avg(score) >60

2)having和where区别(省略group by后效果一致)

#取出student_id 为1的学生的成绩情况

select * from score where student_id = 1;
select * from score having student_id = 1;

3）查询没有学全所有课的同学的学号、姓名

select 
   stu.student_id,stu.name
from student stu,score sco
where stu.student_id = sco.student_id
group by sco.student_id
having
   count(*) < (select count(*) from course)

21、mybatis一级缓存和二级缓存

一级缓存：

也称本地缓存，sqlSession级别的缓存。一级缓存是一直开启的；与数据库同一次回话期间查询到的数据会放在本地缓存中。

如果需要获取相同的数据，直接从缓存中拿，不会再查数据库。

一级缓存失效的四种情况：

1. sqlSession(链接)不同。
2. sqlSession相同，查询条件不同。因为缓存条件不同，缓存中还没有数据。
3. sqlSession相同，在两次相同查询条件中间执行过增删改操作。（因为中间的增删改可能对缓存中数据进行修改，所以不能用）
4. sqlSession相同，手动清空了一级缓存。

二级缓存：

全局缓存；基于namespace级别的缓存。一个namespace对应一个二级缓存。

工作机制：

1. 一个会话，查询一条数据，这个数据会被放在当前会话的一级缓存中。
   
2. 如果会话被关闭了，一级缓存中的数据会被保存带二级缓存。新的会话查询信息就会参照二级缓存。
   
3. sqlSession====>Employee====>employee
   sqlSession====>DepartmentMapper=====>Department
   不同的namespace查出的数据会放在自己对应的缓存中。
   

使用步骤：

1. 开启全局缓存配置。<settings><setting name="cacheEnabled" value="true"/></settings>
2. 因为是namespace级别，需要搭配每个xxxMapper.xml中配置二级缓存
3. pojo需要实现序列换接口。

效果：

查出的数据首先放在一级缓存中，只有一级缓存被关闭或者提交以后，一级缓存数据才会转移到二级缓存

查询顺序：

缓存首先一进来去查二级缓存，二级缓存没有去找一级缓存，一级缓存没有去找数据库。二级缓存----->一级缓存-------->数据库。

使用mybatis二级缓存的缺陷：

（一般情况下不使用mybatis的二级缓存，使用redis等替代）

分布式环境下必然会出现脏数据；

多表联合查询的情况下极大可能会出现脏数据；

只需要加入注解@CacheNamespace即可开启二级缓存。

@CacheNamespace
public interface BranchDao {
  List<Branch> selectInfoByArea(@Param("area") String area);
}

22、Springboot内置的连接池

内置的连接池

目前Spring Boot中默认支持的连接池有dbcp,dbcp2, tomcat, hikari三种连接池。

数据库连接可以使用DataSource池进行自动配置。

1. 由于Tomcat数据源连接池的性能和并发，在tomcat可用时，我们总是优先使用它。
2. 如果HikariCP可用，我们将使用它。
3. 如果Commons DBCP可用，我们将使用它，但在生产环境不推荐使用它。
4. 最后，如果Commons DBCP2可用，我们将使用它。

23、数据库优化

1. sql语句优化:

见资料(扩展资料)

2. 表结构优化:

只能在实际企业中, 多做项目, 练习.

3. 数据库架构优化:

a)分表(表的水平切割, 水平拆分):

单表500万条以上数据, 就可以考虑分表. 一般500万条数据为一张表.

b)分表(表的垂直切分, 纵向拆分):

表的字段比较多的时候使用, 这个拆分没有具体规则, 按照页面业务规则, 一个页面需要的列分成一张表.

垂直拆分出来的多表之间的关系都是一对一关系

高并发读取, 写入量少(大多数企业需求都是这种)：

集群:

读写分离：

多个数据库，一个主库，多个从库。写入的时候向主库中写入数据，主库不允许读取操作。主库中写入的数据会自动向多个从库中同步数据，读取操作从其他从库中读取。从库中不允许用户直接做写入操作，只允许主库向从库中同步数据。多个从库中的表结构和数据都是一样的，这样可以承载高并发的读取操作。

高并发写入, 读取少：

使用场景: 打分系统, 学校使用, 很多学生给很多老师同时打分, 只有校长需要看到分结果.

集群:

多个数据库, 使用不同的服务器, 多个库中库名可以一样, 表结构一样, 里面存的数据不一样,

例如: 一个请求向A库中写入, 另一个请求向B库中写入, 如果一台服务器一块12000转的机械硬盘.

写入速度可以达到300M每秒, 这样两台数据库服务器的集群, 磁盘IO就可以达到600M每秒的写入速度.