01 数据库.txt
UP 返回
1 设计一个数据库需要的模块
存储管理 缓存机制 SQL解析 日志管理 权限划分 容灾机制 索引管理 锁管理
2 索引
2.1 为什么要使用索引
数据量少的话使用全表扫描会比索引更快，但是很多时候我们需要避免全表扫描。类似于偏旁部首
索引是越多越好吗：
数据量小的表不需要建索引，建索引会增加额外的索引开销
数据变更需要维护索引，更多的索引意味着更多的维护成本
更多的索引意味着更多的存储空间
2.2 什么样的信息可以成为索引
主键 唯一键以及普通键
2.3 索引的数据结构
生成索引，建立二叉查找树进行二分查找
左孩子总比父小，右孩子总比父大
但是数据库结点很多，为了组织起这么多结点树的深度会很深，同时每查找一层都会发生一次IO，浪费性能，尤其是当树变成链表时将会很深很深，比如5-7-9-11-13-...
生成索引，建立B-Tree结构进行查找
每个父节点最多有m个孩子，则称为m阶b树（根最少有两个孩子，其他节点最多有m(m>=2)个孩子，除根和叶子，其他节点最少有ceil(m/2)个孩子，所有叶子在同一层）
17 35
p1 p2 p3
↙ ↓ ↘
8 12 26 30 65 87
p1 p2 p3 p1 p2 p3 p1 p2 p3
↙ ↓ ↘ ↓ ↙ ↓ ↘
3 5 9 10 13 15 28 29 36 60 75 79 90 99
每个非终端结点中：
a) 关键字的值都是升序排列 b) 关键字的个数在ceil(m/2)-1和m-1之间 c) 非叶子节点的指针p1 p2...pm，p1指向关键字小于结点最左边关键字的子树，pm指向大于最右边关键字的子树，其他节点指向关键字在中间范围的子树
生成索引，建立B+-Tree结构进行查找。MySQL就是如此
DATA 5 28 65
| p1 p2 p3
| ↙ ↓ ↘
| 5 10 20 28 35 56 65 80 90
| p1 p2 p3 p1 p2 p3 p1 p2 p3
| ↙ ↓ ↘ ↙ ↓ ↘ ↙ ↓ ↘
| 5 10 20 28 35 56 65 80 90
| 8 15 26 30 38 60 73 85 96
| 9 18 27 33 50 63 79 88 99
——→ Q → Q → Q → Q → Q → Q → Q → Q → Q
概念和B树差不多，但是：
非叶子节点的关键字和指针个数相同（意味着可以存储更多关键字）
非叶子节点的子树指针P[i]指向关键字值[k[i],K[i+1])的子树（比如第二层的p2指针，他的子树值都比p3指针的关键字要小；p2 p3上的值有时候可能选取最后一个如18 27，但是不影响这个条件）
非叶子节点仅用来索引，数据都保存在叶子节点中（说明B+树所有的检索都是从根开始到叶子结束，非叶子结点不存储数据那么树就能变得更大）
所有叶子节点均有一个链指针指向下一个叶子节点（方便做范围统计，比如查找>10的结果，只要确定了10后面的就可以连着查下去，不用再次从根查询）
B+树更适合做存储索引：
B+树的磁盘读写代价更低（因为不存储数据，内部节点更小，所以一个块中就能存放更多的结点，一次性就能读取更多的索引到内存）
B+树的查询效率更稳定（因为所有数据都是从根查到叶子结束）
B+树更有利于对数据库的扫描（B树并不能解决这个问题，而B+树只用扫描全部的叶子节点即可；包括范围查询）
生成索引，建立Hash结构进行查找+
理论上查询效率要比B+树高，但是会存在一些缺点：
仅仅能满足=和in的条件，不能使用范围查找
无法被用来避免数据的排序操作（hash值无法用来做key的排序）
不能利用部分索引键查询（因为只有一个hash值是可用的）
不能避免表扫描
遇到大量hash值相等的时候，性能并不一定比B树索引高
BitMap索引（非主流索引，目前仅oracle支持）
只适用于某个字段的值只有固定的几个值的情况。比如存储颜色，约定好每一位代表什么颜色，直接用0 1标识对应的位置即可。
而且做crud时可能需要锁定大量的数据，因为一次更改就会引起位图的改变，不适用于高并发的情况
Blue 1 0 0 0...
Green 0 0 0 1...
Red 0 1 0 0...
Yellow 0 0 1 0...
2.4 密集索引和稀疏索引
密集索引文件中的每个搜索码值（即数据行）都对应一个索引值（密集索引决定了表的物理排列顺序，故一张表只有一个密集索引）
稀疏索引文件只为索引码的某些值建立索引项（其叶子节点一般只存储键位信息及其数据地址，或者主键）
MySQL的两种主流引擎：
InnoDB（聚簇）：若一个主键被定义，该主键则作为密集索引；若没有主键，该表的第一个唯一非空索引则为密集索引；若不满足以上条件，innodb内部会生成一个隐藏主键作为密集索引（一个6字节的列且自动递增）
可见innodb必须要有主键，因为非主键索引存储相关键位和其对应的主键值，包含两次查找。innodb将主键组织成一个B+树，数据就存储在叶子节点上，所以加载的时候数据和索引树会一同加载进入内存。对主键的查找直接在该树上查找；对其他键索引的查找，找到的是对应记录的主键，然后再用该主键去主键索引树上查找
这种数据库的文件有两个，一个frm文件记录表结构，一个ibd文件存储数据和索引
MyISAM（非聚簇）：其不论主键，唯一键或者普通索引都是稀疏索引。主键索引和非主键索引只是存储的数据不同，都是通过查找到数据地址访问到最终数据
这种数据库的文件有三个，一个frm文件记录表结构，一个myi文件存储索引，一个myd文件存储数据
2.5 如何定位并优化慢查询SQL
■根据慢日志定位慢查询SQL
进入mysql，使用语句：
show variables like '%quer%'
得到的结果中，slow_query_log表示查询日志是否打开，slow_query_log_file是日志位置，long_query_time表示查询时间超过多少算为慢查询。默认slow_query_log为off关闭，long_query_time为10s
show status like '%slow_queries%'
得到的结果是从本次客户端打开开始，所有的慢查询条数（下次打开会重新计数）
set global slow_query_log=on; #设置全局变量，查询日志打开
set global long_query_time=1; #设置慢查询判断时间为1s（该修改不会立即生效，需要关闭连接再重新连上以后才会有用）
上面两个设置在mysql重启以后会失效，如果要永久修改，应该去配置文件里改值，比如my.ini文件
编辑存储过程，向表person_info插入200w条数据： （该表的字段有：id name sex age）
drop procedure if exists my_procedure; #删除已存在的存储过程
delimiter $
create procedure my_procedure() #创建存储过程
begin
DECLARE n int DEFAULT 1;
WHILE n < 2000000 DO
insert into person_info (id,name,sex,age) value (n,CONCAT('张三',n),1,n%1000);
set n = n + 1;
if n % 1000 = 0
then
commit;
end if;
END WHILE;
end $
CALL my_procedure(); #调用执行
插入数据以后执行查询
select name from person_info order by name desc
重新查询慢查找日志，会发现存在了慢查询。打开日志txt，可以看到实际的查询耗时（和navicat中显示的可能不一样，以日志的查询时间Query_time为准）
show status like '%slow_queries%'
■使用explain等工具分析SQL
在查询语句前加explain执行，来分析语句（这样查询语句不会被执行，但是会被分析）
explain select name from person_info order by name desc
返回的结果中，id表示语句的执行顺序，越大则越先执行，复合查询里面会出现。explain查询结果中有两个关键字段：
type 表示mysql找到需要的数据行的方式。方式有很多，但是只要显示的是index或者all，说明他走的就是全表扫描，这条语句就应该被优化
（方式如下：system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>all，越在后面查询越慢）
extra 出现以下两项意味着mysql根本不能使用索引，效率会受到很大影响，应尽可能优化
using filesort 表示mysql会对结果使用一个外部索引排序，而不是从表里按索引次序读到相关内容，可能在内存或者磁盘上进行排序。mysql中无法利用索引完成的排序操作称为“文件排序”
using temporary 表示mysql在对查询结果排序时使用临时表。常见于排序order by和分组查询group by
上面的查询语句得到的分析结果中。type就是ALL，extra就是Using filesort
■修改SQL或者尽量让SQL走索引
对该表添加name索引
alter table person_info add index idx_name(name) #以name创建索引idx_name。slow_queries只会对查询做统计，添加索引的语句虽然也很耗时，但是并不会增加慢查询数量
重新使用分析语句explain select name from person_info order by name desc会发现type还是index，但是key里面可以看到存在了索引idx_name，extra变成了Using index走了索引。查询会明显要快一些
分析explain select count(id) from person_info语句时，会发现他不走主键索引而是走的idx_name（事实上到底走什么索引由mysql自己判断，他会选择最快的方式），可能的原因是密集索引毕竟需要存储数据，而稀疏索引只存储键，这样就可以加载更多的结点，加快统计数量
可以强制使该查询走主键索引：explain select count(id) from person_info force index(primary)，会发现实际的统计确实要慢一些。mysql自己选择的索引不一定是最优的，所以有时候可以多次测试来强制使用某个索引来查询
2.6 联合索引的最左匹配原则
■ 最左匹配原则的实例
对于上面的表，加一个联合索引：alter table person_info add index idx_age_sex(age,sex)
此时分析：explain select * from person_info where age=133 and sex=1 会发现走的是联合索引idx_age_sex
分析：explain select * from person_info where age=133 走的也是联合索引idx_age_sex
但是分析：explain select * from person_info where sex=1 会发现走的是全表扫描
■解释说明
最左前缀匹配原则：mysql会一直向右匹配直到遇到范围查询（>,<,between,like）就停止匹配，比如a=3 and b=4 and c>5 and d=6，如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，abd的顺序可以任意调整
=和in可以乱序，比如a=1 and b=2 and c=3建立(a,b,c)的索引可以是任意顺序，mysql的查询优化器会帮助优化成索引可以识别的形式
因为联合索引是根据顺序组织树结构，第一个是绝对有序的，在第一个有序的情况下再对第二个字段按顺序排序，后面依次类推。所以一旦左边的字段没有用到，自然是无法走该联合索引
3 锁
3.1 MyISAM与InnoDB关于锁方面的区别
MyISAM默认用表级锁，不支持行级锁；InnoDB默认用行级锁，也支持表级锁
当数据库使用MyISAM，比如一个session中查询1到200w条数据，另一个session对第200w零1条数据做更新，会发现更新语句会阻塞直到查询结束才执行
所以MyISAM当查询时会对表加读锁，增删改时会加写锁。当读锁未释放时，写锁也无法获得。
InnoDB在走索引的时候，使用的是行级锁以及get锁（走普通非唯一索引的时候用到），不走索引的时候仍然是表级锁
mysql可以手动对表加锁：
lock tables person_info read; #加读锁（tables可以不加s，对应的下面也不用加）
unlock tables #释放锁。上面加锁以后不执行下面的话，同一张表将无法发生增删改
在查询时也可以对表加上写锁：
select * from person_info for update 此时读锁变成了排它锁（写锁），另外一个session的查询也要去等待
mysql的事务是默认提交的：
show variables like 'autocommit' 结果显示on，说明事务会自动提交
set autocommit = 0 关闭自动提交，这样以后写的语句需要手动执行commit 语句才可以改变数据（只针对当前session，其他的仍然是自动提交）
update.......
commit;
InnoDB可以加行级锁：
select * from person_info where id=3 lock in share mode #加行级共享锁。mysql还支持意向共享锁(IS)、意向排他锁(IX)
锁的力度越细，代价越高，相比表级锁在表的头部直接加锁，行级锁需要扫描到某行来上锁，代价比较大。innodb支持事务的同时相比myisam带来了更大的开销。innodb必须要有主键，通过主键效率很高，但是其他索引需要两次查找，在查找情况更多的系统中效率是低于myisam的
MyISAM适合的场景
频繁执行全表count语句（innodb每次执行需要重新统计表中所有数据；myisam使用一个变量来保存数据条数，执行时只用读出变量即可）
对数据进行增删改的频率不搞，查询非常频繁
没有事务
InnoDB适合的场景
数据增删改查都相当频繁
可靠性要求比较高，要求支持事务
3.2 数据库锁的分类
按锁的粒度划分：表级锁，行级锁，页级锁
innodb对行级上锁时，会先上一个表级别的意向锁；myisam仅支持表级锁；BDB引擎的数据库支持页级锁，基于表级和行级之间的锁，即一个逻辑块中相邻的几条数据
按锁级别划分：共享锁，排它锁
按加锁方式划分：自动锁，显式锁
意向锁，myisam的表锁，增删改加上的锁都是自动锁，由mysql自动添加；select for update，lock in share mode这种手动加的锁就是显式锁
按操作划分：DML锁，DDL锁
对数据修改的锁是DML锁，对标结构修改的锁是DDL锁
按使用方式划分：乐观锁，悲观锁
乐观锁实现：
为表添加一个version字段，用来标记版本号，每一次更新就自增：
select version from test_table where id=2; #更新前先查询版本号
update test_table set money=123,version=0+1 where version =0 and id =2; #下一次更新要把版本号作为筛选条件。如果返回为0，则说明更新失败，交给其他逻辑去处理
3.3 数据库事务的四大特性
ACID
Atomic 原子性 事务包含的所有操作要么全部执行，要么全部不执行，失败回滚
Consistency 一致性 数据库从一个一致状态转变为另外一个一致状态（满足完整性约束。比如两个用户钱的和为2000，那不论他们如何转账，最后的和应该还是2000）
Isolation 隔离性 一个事务的执行不应该影响另一个事务的执行
Durability 持久性 更改应该永久保存在磁盘中。当发生事故时，已提交的事务的更新不能丢失，应该可以恢复
3.4 事务隔离级别以及各级别下的并发访问问题
事务并发访问引起的问题以及如何避免
更新丢失 mysql所有事务隔离级别在数据库层面上均可避免
比如其中一个事务要从100扣除10，另一个事务要向100加上20，结果后面的事务先执行了，前面的事务才去执行，就导致结果不一致（现在已经不会出现这种情况）
脏读 READ-COMMITTED事务隔离级别以上可避免（READ-COMMITTED也是oracle默认的事务隔离级别） ▶set session transaction isolation level read committed
一个事务可以读到另一个事务未提交的更新数据
select @@tx_isolation #查看数据库的事务提交级别
set session transaction isolation level read uncommitted #将当前session事务级别修改为read uncommitted（表示可以脏读）
在两个session中执行上述语句修改隔离级别，然后在session1中执行事务：
start transaction;
update account set balance =1000-100 where id=1; #①
select * from account where id=1;
rollback #事务1发生了回滚，但是事务2并不知道。两个事务都以为自己的操作没有问题
session2中执行事务
start transaction;
select * from account where id=1; #①中执行的语句虽然还未提交，但是此时已经可以被读到
update account set balance =900+200 where id=1; #业务可能基于上面被读到的数据进行修改，导致出错
commit;
不可重复读 REPEATABLE-READ事务隔离级别以上可避免（REPEATABLE-READ是mysql默认的隔离级别） ▶set session transaction isolation level REPEATABLE read
在之前的隔离级别中，虽然避免了脏读，但是一旦事务2提交了语句，事务1这时再次读时会发现数据和之前读的不一致了，但是他本身并不知道别的事务在做什么
update account set balance =balance-100 where id=1; #在这个语句提交的时候，如果有别的事务对balance做了修改，那么mysql会自动用新的值来对数据做-100的更新
幻读 SERIALIZABLE事务隔离级别可避免（只有在该模式下SQL语句会自动加锁不用手动的去加。进入该模式后，含有读操作的事务没有提交时，另一个事务的写操作会被阻塞。。事实上mysql在REPEATABLE-READ级别下也可以避免幻读，具体原因见3.5）
select * from account lock in share mode; #比如在这句中数据库查到了三条数据
update account set balance =1000; #按理说这句话应该更新了三条数据。但是如果此时有一个事务对表插入了一条数据，便会返回更新了4条，出现幻读情况
不可重复读侧重于修改，幻读侧重于新增或删除。事务隔离级别越高，串行越严重，并发越差
3.5 InnoDB可重复读隔离级别下如何避免幻读（对应视频3-15）
表象：快照读（非阻塞读）--伪MVCC
当前读：
select ... lock in share mode，select ... for update，update，delete，insert 即加了锁的增删改查语句
内在：next-key锁（行锁+gap锁）
DOWN 返回