自拟！数据库死知识！！！
自拟！数据库死知识
因为数据库方面有很多地方的知识其实实际开发中很少会去在意，但这些冷知识也非常有必要去了解或深入，所以本章记录数据库知识中普通开发不会在意的事。
数据库基本概念
键属性
主键，外键。
主键一般确实是会用自动生成的id，但业务中很少会关注主键值。业务中的驱动主键才是数据库结构的核心，所以主键在开发中很多时候都是便于selectByid查询这样的简单操作，而多用做标识的是与主键组合的业务主键。
而外键在数据库设计中其实是提倡少设置的，因为一旦设计了外键，那么该字段的值将与子表深度绑定。以至于在业务扩展时间，影响更新、删除等功能，所以设置外键需要酌情考虑。
以下是网络上对外键的评价
- 增加了复杂性： a. 每次做DELETE 或者UPDATE都必须考虑外键约束，会导致开发的时候很痛苦, 测试数据极为不方便; b. 外键的主从关系是定的，假如那天需求有变化，数据库中的这个字段根本不需要和其他表有关联的话就会增加很多麻烦。
- 增加了额外工作： 数据库需要增加维护外键的工作，比如当我们做一些涉及外键字段的增，删，更新操作之后，需要触发相关操作去检查，保证数据的的一致性和正确性，这样会不得不消耗资源；（个人觉得这个不是不用外键的原因，因为即使你不使用外键，你在应用层面也还是要保证的。所以，我觉得这个影响可以忽略不计。）
- 对分库分表不友好 ：因为分库分表下外键是无法生效的。
- ......
范式
简单的记忆，
第一范式，指表示二维表，一个字段对应一个值，关系型数据库
第二范式，指拆开一个表中的非联系字段，将一张开拆成三张，其中两张是各自的信息，另外一张是两张表主键的对应关系。
第三范式，从第二范式表的基础上，继续拆分简单的说就是只能用主属性去从中间表中查到C表的信息。但在实际开发设计中，很难有表符合第三范式，因为如果消除了非主属性的传递依赖的话，字段将非常非常冗余。
存储过程
简单的描述就是一段预编译过的执行sql语句的集合，使用存储过程可以比一般执行sql更快。
但是阿里巴巴规范中明写禁止使用存储过程，因为难以调试和扩展，没有移值性。
DML和DDL
DML为操作语音，CRUD
DDL为定义概念，定义表 比如删表、改表等
数据库字符
一般将数据库设置为UTF-8和UTF8MB4
但这两种都会出现兼容不了特殊字符的情况，比如4字符的emoJi表情或复杂的繁体。
所以开发者需要在常见的字符集ASCII、GB2312、GBK、UTF-8......，中根据字段值需要，并且更改数据库配置文件进行字符的定制。
MySql
关系模型
关系模型其实指的是，将表设计当成java中的对象设计就ok。
存在一对一，一对多，多对多关系。
引擎
使用show engines 查看当前版本支持的所有引擎，在 My.ini中通过default-storage-engine = XXX设置引擎。
在Mysql5.5之前，默认引擎还是MyISAM。
当前最新版本默认使用的是 InnoDB ，并且目前位置只有InnoDB支持事务。
并且没有MyISAM 不支持事务以及行级锁的原因，当业务出现重大bug崩溃后，是无法安全恢复的。
行级锁
目前innoDB默认为行级锁，这代表了在并发时是只会锁住当前操作的数据行的。
但是MyISAM没有行级锁，那也代表了表级锁的他在并发业务中，是直接锁住整张表的，离谱。
InnoDB和MyISAM
innoDB：行级锁，事务，外键，安全恢复【redo log】,MVCC
MyISAM：表级锁，不支持，不支持，我没有,我也没有
所以选择谁，按照一般开发逻辑肯定是默认的号innoDB，但是如果项目不涉及并发、事务的情况用用MyISAM可能也是一种优化，但是以目前的开发环境实在找不到使用MyISAM的理由。
MVCC
上面提到的mvcc，为什么只有innoDB独有。
首先明白mvcc：多版本并发控制，是优化并发的原理。
那么myisam连行级锁都没有，当然也不会考虑到并发的问题。
那么回到MVCC上，
概念是：维护数据的多个版本，使得读写操作不冲突，只限制写与写之间的操作。
当前读和快照读
当前读：指使用select lock in share mode(共享锁)或select for update (排它锁)，让读数据时，确保是最新的版本，因为会锁住当前行数据，所以也是悲观锁的体现。
快照读：平时开发的时候最简单的select查询就是快照读，是基于mvcc实现的，当也因为这样，当我们读数据时，这条数据可被修改，但是我们依然读到旧版本数据。
依据实现
mvcc依靠 undolog日志【记录历史版本数据，一般用于回滚】实现，
三个场景
读-读 ： 正常使用
读-写 ： 经过上述介绍，会出现脏数据、幻读、不可重复读
写-写 ： 一般发生冲突，出现更新丢失
MVCC解决了读-写问题，根据为每个修改版本打上标识，确保读操作读到的是事务开始前的数据库快照，但是不能解决写-写问题。
具体实现
https://www.cnblogs.com/xuwc/p/13873611.html
查询缓存
8版本之后移除，因为虽然使用缓存提高执行sql效率。
但是也带来了很多的额外开销，因为创建缓存和销毁缓存都需要数据库本身完成。
事务
ACID特性
原子性A:事务动作不可分割，要不全完成，要不全回滚
一致性C：就是说事务回滚或不回滚都要保证数据库总数据不变，前后一直
隔离性I：事务之间相互隔离
持久性D：事务提交说明录入数据库中，被持久化，不受故障影响。
实现原理
使用 redo log保证持久，使用 undo log实现回滚操作，即原子性。
使用隔离机制，锁或MVCC保证隔离性。
事务问题
老生常谈的事务4个问题，
脏读：读到的不是最新版本数据
丢失修改： 简单的就是因两个事务同时修改一条数据，可能导致的覆盖更新
不可重复读： 指一个事务内读两次同一份数据时，被另一个事务修改这个数据，导致第二次读的数据与第一次不同。
幻读：和不可重复读一样，但是是被另一个事务插入、删除很多数据，导致本事务看数据像幻觉一样多了很多或少了很多。
事务隔离级别
read-uncommitted读未提交：最低级别，允许读还没有被提交过的数据，也就导致出现脏读、不可重复和幻读。
read-committed读已提交：允许读已提交的数据，确定是最新数据，可以防止脏读，但是读的时候还是有可能被另一事务插入或删除、更新，还是会出现不可重复和幻读
repeatable-read可重复读：重复读统一数据，结果相同，也导致了除非事务自己修改，不会干扰到数据。避免脏读和不可重复，但是无法阻止另一事物对数据的删除和新增，也就导致幻读。
serializable可串行化：完全服从ACID，无敌的隔离级别。
默认为可重复读，分布式下并发条件多，所以选择可串行化好。
Sql语句
https://shockerli.net/post/1000-line-mysql-note/
Sql语句执行流程
Server层：连接器[令牌认证]、缓存器[8后移除]、分析器、优化器、执行器
存储引擎：存储数据、读取数据
在sql指令通过连接器 与存储器没命中后，分析器分析其sql语句：
1、提取select、表、字段、条件等关键字
2、编译sql是否合法。
优化器优化，评断是系统决定的，但因为用户设置的索引所以可能不会是最优的。
执行器执行，调用引擎接口，返回结果。
举例：
select * from user where age = 11 and name ='学生'
1、先判断当前sql指令是否合法，比如是否通过令牌，是否通过缓存未命中、是否可执行等
2、提取sql语句，比如 select [查询] user[表] age、name[条件]
3、优化器优化，根据条件以及查询引擎，比如age设置了索引，则走age查询...
4、调用引擎接口，返回引擎存储数据
存储事项
- 不用字符串存储日期，因为日期查询需要逐个对比，效率低下
- 使用tinyint记录标识，比如删除或业务判断
- Timestamp 和DateTime类型，前者可能好用，因为随服务器时区变化且所占字节只有4位，后者占8位，但是也导致了前者存储的时间跨度远小于后者。而且前者存储与后者不同，属于数字时间类，可读性差
索引[Mysql]
作用
简单的说索引是一张表的目录，而这个目录好不好用就取决与这个目录的标题对文章内容的定位程度了。
所以可以看出索引的优缺点；
缺点： 创建索引和维护索引都需要消耗时间，对数据库表进行删除或新增操作时，如果有索引需求则需要动态的删除和新增索引，降低了sql效率，所以目录不能太多哟。
优点：很明显，有了目录之后，根据字段[目录]查询数据则会快很多很多。
索引只适合在数据量大的情况下有很大的提升。
结构
常见的就是Hash和B+树的组合
Hash表：
key-value组合，通过key快速定位，时间为0(1)。通过哈希算法，将value排序在指定的hash域中，然后key就可以快速找到对应hash域index下的value。
但是由于哈希算法无法保证唯一，所以会出现冲突情况，虽然会使用链地址将同一个hash域延长。但是当数据越多，链表越长，也就导致搜索呈O(N)指数增长。
那么为了解决这个问题，mysql和java一样采取了树的概念，前者使用B+树，后者使用红黑树。
此外，Hash表还有一个弊端，不支持范围搜索，因为使用hash算法得出的值，所以不确定输入值之间的关系。
B树与B+树
B为平衡的意思。
B树从上往下搜索，根据key使用二分法，很有可能出现没到达叶子节点，检索就结束。
B+树从上往下，从左往右，并且B+树只有叶子叶子会存储data数据，所以其余节点轻量只存储key进行检索。
检索过程
innnDB: 叶子节点存储的是 key ，和本行数据。所以在索引检索时，叶子节点的key只可能是本行数据的主键索引[唯一不变性]，所以在使用其他字段查询索引时，都是在一颗只包含该字段与主键值的树中查找主键值，用来辅助搜索主键的位置。拿到位置后，再走一变主键索引。所以除主键以外的任何索引都要注意字段的长度和数目，这也称为聚簇索引。
myisam： 所有的叶子节点存储的是key和data存储地址，也就说明找到key还需要根据地址拿到数据值，称为非聚集索引
索引类型
主键索引
以主键为key
二级索引
可以是任何字段，其存在的意义就是辅助寻找一级索引[主键索引]的位置
有：
1、唯一索引
2、普通索引
3、前缀索引，使用字段前几字符作为key
4、全文索引，检索大文本数据中关键字的信息。
聚集索引和非聚集索引
聚集：将key和data数据值聚集在一个叶子节点中，优势就是找到key就等于找到data，
缺点：更新代价大，因为数据在叶子节点中，所以挪动B+树的时候需要移动数据。依赖有序的数据。
非聚集： key和data非开，比如二级索引，检索时key和一级索引的key是在一起的，会进行回表操作再查一次一级索引的B+树。
优点：就是和聚集反过来，更新代价小，因为不存放数据。
缺点：回表操作。
问题：非聚集索引一定回表查询吗(覆盖索引)?
不是
SELECT name FROM table WHERE name='guang19';
当查询字段就是非聚集字段时，则可以直接返回。
如果是myisam引擎，当返回值为key[id]时，也不需要在进行再检索操作。
SELECT id FROM table WHERE id=1;
覆盖索引
判断是否符合覆盖索引，只需要判断本次sql语句返回的值是否可以直接通过条件进行返回。
创建注意事项
合适的字段
- 不设置NULL字段，难优化
- 被频繁查询的字段，索引就是做这个的
- 被作为条件查询的字段，经常被where条件查询，可能触发覆盖索引或少一次回表操作。
- 被频繁排序的字段，因为该字段已经被索引排序，所以利用排序好了的索引加快查询速度。
- 被经常用于连接的字段，相当于3.
不合适的字段
- 被频繁更新的字段，加大维护索引的成本
- 字段值过长
注意点
- 多建立联合索引
- 避免建立重复意义重复,AB互相命中这种
- 字符串类型索引尽量建立前缀索引，控制字段长
使用建议
- 特大型表不适合建立索引，太难维护了
- 避免在where中使用函数，使索引失效
- 使用逻辑主键，不用业务主键；数据库定义的主键
- 删除长期未用的索引
- 分页查询慢时，注意索引的生效效率
添加方式
主键索引：
ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )
唯一索引：
ALTER TABLE `table_name` ADD UNIQUE ( `column` )
普通索引：
ALTER TABLE `table_name` ADD INDEX index_name ( `column` )
全文索引：
ALTER TABLE `table_name` ADD FULLTEXT ( `column`)
联合索引：
ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )
索引失效场景
最左匹配原则
当使用联合索引(age,name,address)这样的顺序设置时。
以age为主，以下关于三个字段的任何查询条件，只要有age字段即可走索引。
反之如果没有age，则索引失效。
select * 问题
*代表全表扫描，不会使用索引。
并且查询所有字段问题，本身就是一个低效率的东西。
所以所以覆盖索引的概念，查索引的字段时最高效的优化。
where 后出现计算、函数
select * from user where id+1=2;
或者使用 sum 、 substr等计算函数，索引失效。
条件字段与数据库字段类型不一致
当数据库字段为varchar时，
查询为：select * from user where name = 你好
则会失效。
反过来，需要符合java的隐形转换原理，比如string自动转成int这样的。
Like问题
当模糊查询时，%在条件左变，索引失效。
这里联想记忆就是查英语字典，是不是最快的都是先前面几个已知的字母。
or关键字
使用or的前提上，其两边字段都有索引才会走索引，否则失效
not in 与not exists
普通索引使用not in ，索引失效；主键则有效
not exists索引全失效
order by 排序问题
将order by 在sql中当成一个 and看待，后前面的sql一起判断是否是上述情况。
order by中一起出现 asc 和desc 索引失效