数据库相关杂记

数据库

• 理论

• 若在一张表中，在属性（或属性组）X的值确定的情况下，必定能确定属性Y的值，那么就可以说Y函数依赖于X，写作 X → Y。

 在一张表中，若 X → Y，且对于 X 的任何一个真子集（假如属性组 X 包含超过一个属性的话），X ' → Y 不成立，那么我们称 Y 对于 X 完全函数依赖，记作 X F→ Y（X少一个属性都不行）。  

假如 Y 函数依赖于 X，但同时 Y 并不完全函数依赖于 X，那么我们就称Y 部分函数依赖于 X，记作 X  P→ Y。

假如 Z 函数依赖于 Y，且 Y 函数依赖于 X （有『Y 不包含于 X，且 X 不函数依赖于 Y』这个前提），那么我们就称 Z 传递函数依赖于 X ，记作 X T→ Z。设 K 为某表中的一个属性或属性组，若除 K 之外的所有属性都完全函数依赖于 K（K 确定的情况下，该表除 K 之外的所有属性的值也就随之确定）那么 K 就是码。主属性：包含在任何一个码中的属性称为主属性。

2NF在1NF的基础之上，消除了非主属性对于码的部分函数依赖

第三范式（3NF） 3NF在2NF的基础之上，消除了非主属性对于码的传递函数依赖。

BCNF在 3NF 的基础上消除主属性对于码的部分与传递函数依赖。

• 原理

二叉查找树结构由于树的深度过大而造成磁盘I/O读写过于频繁，进而导致查询效率低下，一个基本的想法就是：采用多叉树结构，还有一个将关键的地址信息提前放到内存中也是数据库的思路。下面是B树：

一颗m阶B树，每个结点至多m个子树，若B树包含N个关键字，所有的叶子结点都在第I层，l层一定有N+1个叶子结点；因为根至少有两个孩子，因此第2层至少有两个结点。除根和叶子外，其它结点至少有┌m/2┐个子树，因此在第3层至少有2*┌m/2┐个结点，在第4层至少有2*(┌m/2┐^2)个结点，在第 I 层至少有2*(┌m/2┐^(l-2) )个结点，而l层现有N+1个结点，于是有： N+1 ≥ 2*┌m/2┐I-2，即： I≤ log┌m/2┐((N+1)/2 )+2；所以当B树包含N个关键字时，B树的最大高度为l-1（因为计算B树高度时，叶结点所在层不计算在内），即：l - 1 = log┌m/2┐((N+1)/2 )+1。

插入（insert）操作：插入一个元素时，首先在B树中是否存在，如果不存在，即在叶子结点处结束，然后在叶子结点中插入该新的元素，如果叶子结点空间满了则将该结点分成两部分，中间关键字元素上移到父结点中（当然，如果父结点空间满了，也同样需要“分裂”操作）。

删除操作：如果该元素在B树中存在，则将该元素在其结点中进行删除，如果他有孩子结点则上移孩子结点中的某相近元素(“左孩子最右边的节点”或“右孩子最左边的节点”)，然后判断，如果某一结点中元素数目小于ceil(m/2)-1，则看其某相邻兄弟结点，如果他的元素个数大于ceil(m/2)-1，则向父节点借元素来满足条件；如果小于ceil(m/2)-1，则该结点与其相邻的某一兄弟结点进行“合并”成一个结点，以此来满足条件。

• B树：有序数组+平衡多叉树，可以在非叶子结点命中；B+树：叶子结点用序数组链表串起来（有利于遍历），并且让非叶子结点只作索引使用, 去掉了其中指向data record的指针, 让出的空间能够存放更多的key, 因此能有更大的出度. 这有什么用? 这样就意味着存放同样多的key, 树的层高能进一步被压缩, 使得检索的时间更短。B*树：非叶子结点也有指向兄弟结点的指针。

• Innodb的查找操作：首先通过B+树索引找到叶节点，再找到对应的数据页（包括File Header，Page Header，Infimun + Supremum Records，大量的User Records，Free Space，Page Directory，File Trailer），然后将数据页预读到内存中，通过二分查找数据页中Page Directory的槽（Page Directory中存放了记录的相对位置，这些记录指针称为Slots槽），然后根据槽的指针指向链表中的行记录，之后在链表中依次查找。

• 索引就是排序好的B+树，利于二分查找。

聚集索引：叶子结点储存了真实的数据（一个叶子结点既一数据页，一个数据页内数据的索引地址相邻其物理地址必相邻，而数据库会整页预读数据），一张表只有一个。创建PRIMARY KEY时此行变为唯一索引，默认是聚集索引（可以指定为非聚集索引）。

非聚集索引：叶子结点储存的是数据地址。MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址，因此他没有聚簇索引（不支持事务且只有表锁-->效率高）。

• 优化

1. 每组前50之后未点击测试通过的版本select count(1) as valve from t_image_build_version a where  (select count(id) from t_image_build_version where image_id = a.image_id and id < a.id) > 50 //

2. 比如一个订单，我们可以用 select * from order_table where id = 'xxx' for update 进行加行锁，那么其他的事务就不能对其进行修改。

3. redolog buffer丢失，lru 新老 T，写缓冲不适合唯一索引（因为如果有索引还要读盘去），自适应hash

4. 在 where 子句中使用 or 来连接条件时如果有一方无索引将导致引擎放弃使用索引而进行全表扫描，如：select id from t where num=10 or age=20 可以这样查询：select id from t where num=10 union all select id from t where age=20，UNION用于合并多个SELECT语句的结果集，内部的SELECT语句必须拥有相同数量的列，列也必须拥有相似的数据类型，列的顺序必须相同,结果消除重复行。如果不消除重复的话，使用UNION ALL。

5. 索引优化：

①考虑在where字句中出现列或者join字句中出现的列上建索引。最左前缀原则：在创建联合索引的时候，尽量将经常参与查询的字段放在联合索引的最左边（最左边为null的话退化成遍历）。like '%abd%'不会使用索引，而like ‘aaa%’可以使用索引。>,

② 应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：select id from t where num/2=100应改为:select id from t where num=100*2。

③ 注意in 和 not in 也要慎用，否则会导致全表扫描，如：select id from t where num in(1,2,3,4,5,6)对于只能是整数的数值，能用 between 就不要用 in了select id from t where num between 1 and 6

④ 如果这些列中有一列含有null，该列就会从索引中排除，应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：select id from t where num is null可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：select id from t where num=0。

⑤ select test_index where id = 3 order by name desc;  如果ID是单列索引，name不是索引或者name也是单列索引，则order by不会使用索引。因为Mysql的一次查询只会从众多索引中选择一个索引，而这次查询中使用的是ID列索引，而不是name列索引。在这种场景下，如果想让order by也使用索引的话，就建立联合索引（id，name），这里需要注意最左前缀原则，不要建立这样的联合索引（name，id）。最后需要注意mysql对排序记录的大小有限制：max_length_for_sort_data 默认为1024；也就意味着如果需要排序的数据量大于1024，则order by不会使用索引，而是使用using filesort（速度比较慢的外部排序）。

6. ORDER BY 索引失效：

① 最常见的情况 用来查找结果的索引(key2) 和 排序的索引(key1) 不一样,SELECT * FROM t1 WHERE key2=constant ORDER BY key1;（但是可以建联合索引）

② 排序字段顺序与索引中列顺序不一致，无法使用索引排序，比如索引是idx_kp1_kp2(key_part1,key_part2) SELECT * FROM t1 ORDER BY key_part2, key_part1;

③ order by中的升降序和索引中的默认升降不一致，无法使用索引排序 SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC; ④ key_part1是范围查询，key_part2无法使用索引排序SELECT * FROM t1 WHERE key_part1> constant ORDER BY key_part2;

⑤ order by和group by 字段列不一致SELECT * FROM t1 WHERE key_part1=constant ORDER BY key_part2 group by key_part4; ⑥ 索引本身是无序存储的，比如hash 索引，不能利用索引的有序性。

⑦ order by字段只被索引了前缀 ，key idx_col(col(10)) select * from t1 order by col ;      

⑧ 对于还有join的关联查询，排序字段并非全部来自于第一个表，使用explain 查看执行计划第一个表 type 值不是const 。

7. 索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。

8. 尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

9. sql 中varchar(可变长度字符串)，多花一个字节储存长度；char(固定长度，少了补0)；in(  )括号中的任何一个；SET后面的 = 与WHERE后面的 = 不同；where age=null 永远都是false；字符串是  ' '; like'张_' 匹配一个非空字符；like '%钢'匹配0-n个字符；

10. INT(1)和INT(11)没有区别，数据只要在-2147483648到2147483647（即长度11位内）都能存入和显示。只是当结合可选扩展属性ZEROFILL使用时， 默认补充的空格用零代替，对于声明为INT(5) ZEROFILL的列，值4检索为00004。但是char不同，当定义为char(2)时无论我们写入多少个字符，最多只能存入和显示2个字符。

11. 若一个表只有一个varchar类型，如定义为create table t4(c varchar(N)) charset=gbk;N代表的是字符最大个数，此处N的最大值为(65535-1-2)/2= 32766，其中65535字节是varchar最大的存储字节，减1的原因是实际行存储从第二个字节开始，减2的原因是varchar头部的2个字节表示长度;除2的原因是字符编码是gbk（每个字符最多占2个字节）。

12. ORDER BY 列类型 后只能having , sum,count,avg 等聚合函数；DESC,列类型 ASC（多指标排序）；COUNT(name不是null就++) 总数；SUM(age如果是null当0) 总和；having 过滤组；

13. mysql特有limit，在特定范围查（有始，终行）；前后是两列类型；导出sql脚本是备份数据库数据，恢复时要先create database；（不建议使用外键，降低性能，分表禁区，最好由业务逻辑保证数据关联性和一致性）

14. 此表的 FOREIGN KEY（外键） REFERNNCE 另一表某唯一性键，可重复但外表不能没有；主键唯一，主键与外键实现一对一；中间表实现多对多；

15. explain执行计划

id :实越大优先级越高越先执行，相同时自上而下执行。 

select_type：SIMPLE：简单的select查询，查询中不包含子查询或者union ；PRIMARY：查询中包含任何子查询或者union，最外层查询则被标记为primary ；DERIVED：在from列表中包含的子查询被标记为derived（衍生）；UNION：若第二个select出现在union之后，则被标记为union；

type（两个表的联接类型）：const表示通过索引一次就找到了，eq_ref 唯一性索引扫描，ref 非唯一性索引扫描（返回匹配某个单独值的所有行），range有范围索引扫描，index全索引树扫描，all全表扫描。

extra：key 代表是否使用索引，using index 不用回表光索引的数据就够了，using where 存储层的数据读到server层需要过滤，using index condition 过滤下放到server层，例如：

mysql的最终执行方案是基于代价计算的

SELECT * FROM `t_work_order` WHERE state in (0,1,2); 没有key Extra = 空 -> 他认为你state状态本来就这几种，还不如走全表扫描，

SELECT * FROM `t_work_order` WHERE state in (0); key = dx_state_create_time

SELECT state FROM `t_work_order` WHERE state = 0 AND type = 4 AND cluster_id = 231; key=idx_state_type_cid Extra=Using where; Using index ；因为索引是state_type_cid，所以需要过滤出state。

SELECT cluster_name FROM t_cluster WHERE cluster_name= 'fd_test'; key=cname ， Extra = Using index；

using filesort: 无法利用索引完成的排序操作，如果数据量过大需要用到磁盘swap然后多路排序。

using temporary:  多表查询时使用临时表保存临时的数据，以用于后续的处理。两者的使用场景的区别并不是很明显，不过，还有一个原则:一张表上一般只用 filesort就可以了，如果有多个表的数据需要排序，那么MySQL会先使用 using temporary保存临时数据，然后再在临时表上使用filesort进行排序，最后输出结果。

• using index condition 的理解是，首先mysql分 server 和 storage engine两个组件, server负责sql的parse执行; storage engine去真正的做 数据/index 的读取/写入。以前是这样: server 命令 storage engine 按 index把相应的数据从磁盘数据表读出,传给server, server来按where条件做选择; 现在ICP（index condition pushdown）如果where条件的一部分能够通过使用索引中的字段进行评估，那么mysql server把这部分where条件下推到storage engine（存储引擎层）, 让storage engine根据index做判断, 如果不符合条件则无须读数据表. 这样节省了disk IO。

• 内连接（inner join）就是笛卡尔积通过on deptno相等的条件进行求交集。左连接为左表的全部+相应的右表，若右表没有的补null。

•  一张表有（天（1/2/3/4........）、温度），查出温度比前一天高的有哪些: selecta.date from A a left join A b on a.date = b.date-1 where a.tem>b.tem;

•  在left join 后面使用关键字：1. on..and..条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。 2. where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

在使用inner join时，on and和where 效果一样的。

•  major_id 和 user_id建有联合索引，SELECT * FROM `user` WHERE major_id = 100 ORDER BY user_id LIMIT 10000,10; 执行时间是0.344s  而SELECT * FROM (SELECT user_id FROM `user` WHERE major_id = 100 ORDER BY user_id LIMIT 10000,10) a LEFT JOIN `user` b ON a.user_id = b.user_id; 0.067s 前者先在 major_id 和 user_id的索引树中查到一个符合user_id就回磁盘表select当前user的所有数据，需要大量次数磁盘IO操作。后者先只查索引树，全都查出来（存到内存）之后统一区磁盘读取，FROM子句查询还是不错的。

• JOIN查询：外层循环的结果集是100行，使用Nested Loop Join算法需要扫描内部表100次，使用Block Nested-Loop Join算法则先从外部表每次读取的10行记录放到join buffer,然后在内部表中直接匹配这10行数据，内部循环就可以一次与这10行进行比较, 这样只需要比较10次，对内部表的扫描减少了9/10。

外表（驱动表）一定要用小结果集，这样就可以用小结果集驱动内表的大结果集（充分利用大表索引）。LEFT JOIN指定左表为驱动表，JOIN默认小表为驱动表；EXPLAIN第一行就是驱动表；驱动表的排序可以直接filesort ，被驱动表排序需要对他的循环查询结果合并后放到using temporary进行排序；优化时可在被驱动表的join字段上建立索引；当被驱动表的join字段上无法建立索引的时候，设置足够的Join Buffer Size。  

• 表A（小表），表B（大表，当然用大表的索引对数据库提升大） ，均建有cc的索引

select * from A where cc in (select cc from B) 效率低，用到了A表上cc列的索引；select * from A where exists(select cc from B where cc=A.cc)效率高，用到B表上cc列的索引 。参考1，参考2

• 关键词 distinct（要求的属性）值一样的记录只返回一条（用于去重）。

修改表之添加列：给stu表添加classname列：ALTER TABLE stu ADD (classname varchar(100)); 两张表的连接查询一定有一个主外键关系，三张表的连接查询就一定有两个主外键关系。 外键就是用来约束这一列的值必须是另一张表的主键值。 注意，WHERE是对分组（group by）前记录的条件，如果某行记录没有满足WHERE子句的条件，那么这行记录不会参加分组；而having是对group by后数据的约束。

• TRUNCATE其实属性DDL语句，因为它是先DROP TABLE，再CREATE TABLE。而且TRUNCATE删除的记录是无法回滚的，但DELETE删除的记录是可以回滚的（回滚是事务的知识！）

• （相比于Statement）PreparedStatement命令解析编译后会被数据库缓存，效率高。还能预防sql攻击：

SELECT * FROM tab_user WHERE username=' a' or 'a'='a ' and password=' a' or 'a'='a ' 。之所以PreparedStatement能防止注入，是因为它把用户输入的单引号转义了，变成了 \'，这样一来，就无法截断SQL语句，进而无法拼接SQL语句，基本上没有办法注入了。CallableStatement调用提前写在数据库中的储存过程方法（提前编译好了语句）。

• setTrue(){..=true;} 函数就是一个幂等函数,无论多次执行，其结果都是一样的.

• 在默认情况下，MySQL每执行一条SQL语句都会自动提交一次事务（开销大）。如果需要在一个事务中包含多条SQL语句，那么需要开启事务和自己显式结束事务。开启事务：start transaction；结束事务：commit或rollback。MySQL的默认隔离级别为Repeatable read。

• 事务 PPT演示

事务的性质：ACID

Atomicity 原子性-->要么全做要么全不做

Consistency 一致性-->拿银行转账来说事务的执行不应改变A、B 两个账户的金额总和

Isolation 隔离性-->事务无法看到对方的中间状态

Durability 持久性。事务就是一个可以可回滚的线程，不可能是串行的，并发效率低。

• 行级锁有共享锁（S LOCK）和排他锁（X LOCK）两种。

若事务T对数据对象A加上S锁：则事务T可以读A但不能修改A，其他事务只能再对A加S锁而不能加X锁，直到T释放A上的S锁。

若事务T对数据对象A加上X锁：事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的锁。

锁是根据索引来加的，并且再事务提交或回滚时才释放。

• 数据库并发出现的问题：

脏读：脏读就是指当一个事务A修改了数据但是未提交，另外一个事务B使用了这个数据，然后A回滚了 -> Read Committed可以解决，只需要保证A事务里要改的数据别被别人用到即可。

不可重复读：是指在一个事务A内，因为其他事务B修改了数据，导致A多次读同一数据不一样。 -> Repeatable Read可以解决，需要保证A事务里要读的数据别人不能改。

幻读：是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。

• MySQL InnoDB存储引擎，实现MVCC (相对Lock-Based Concurrency Control)，与copyonwrite类似。读不加锁，读写不冲突，提高读多写少的系统的并发能力。读操作分成两类：快照读，读取的是记录的可见版本 (有可能是历史版本)，不用加锁；当前读，读取的是记录的最新版本，并且，当前读返回的记录，都会加上锁，保证其他事务不会再并发修这条记录。MySQL事务隔离级别和实现原理（看这一篇文章就够了！） - 知乎 mysql的默认隔离级别 - 月染霜华 - 博客园

如下sql ：update/delete/insert（均需当前读） from t1 where id = 10;id建有索引            

（1）RC和RR级别下select..where..;都是快照读，不加锁。

（2）在RC级别下会将所有id = 10的记录加上X锁（如果id非主键索引，对应的主键索引也要加X锁；如果id无索引，所有行都会加锁）。也就是说RC级别在加索引的情况下竟然避免了不可重复读！

（3）RR级别下id如果是唯一索引和RC级别加锁一样（既然是唯一索引，并发插入重复会报错，本身也会避免幻读），并当id是非唯一索引时会加GAP锁，竟然完全避免幻读！文档上写的都是：RR说可能会出现幻读，但是注意innodb引擎下不存在幻读。

（4）select * from table where ? lock in share mode;会针对性的加S锁，select * from table where ? for update;会针对性的加X锁。

• 避免死锁：固定访问顺序；避免锁所有行；提前预定锁定的资源；RR降低为RC（避免gap锁）；mysql本身会通过例如银行家算法等进行死锁检测，如果可以自己杀掉一个，然后放行另一个。

•  游标是将结果集读到内存中，然后你请求一个给你一个，太大可能爆内存。

•  缩小limit扫描范围：SELECT * FROM user WHERE major_id = 17 and user_id >pre_user_id LIMIT 10 (major_id，user_id建有联合索引，每次保留第10个user_id做为下一次的pre_user_id ）相比于limit 1000,10会对前1000行加X锁，影响读的性能

延迟关联也可以解决limit的问题

• count(1)与count(*)只计数不取值，count(id)会取值，慢 https://zhuanlan.zhihu.com/p/75147471

• 连接池：时刻保留空闲con（缓冲，做到取时即可用），连接参数配置c3p0。

• 事务的原子性：在一个转账事务中，在service层中要一个con完成-100和+100的两个Dao层方法，完成后检查无误此con就commit（归还），有错误此con就rollback（数据库回复到以前状态）；但是con不能暴露在service层，可用jdbcUtils管理con（设为private static，然后不同线程用THreadLocal，每一个线程有自己的con），dao层与service层都可调用。

• 问题：使用VARCHAR(5) 和VARCHAR(200) 存储'hello'的磁盘空间开销是一样的,使用更短的列有什么优势吗？更大的定义列会消耗更多的内存，因为MySQL通常会分配固定大小的内存块来保存内部值,尤其是使用内存临时表进行排序或操作时会特别糟糕。不在低区分度的列上建立索引，例如“性别”

线上尽量少使用大SQL，可能一条大SQL就把整个数据库堵死，将复杂SQL拆分为多条简单SQL，化繁为简一条SQL只能在一个CPU上运算，如果SQL比较复杂执行效率会非常低,简单SQL缓存命中率更高,减少锁表时间,充分利用多核CPU，提高并发效率