数据库、Mysql

数据库

设计范式

• 第一范式：数据库表中每一个字段都要是不可拆分的原子数据项
• 第二范式：在第一范式的基础上，所有非码属性必须完全依赖于候选码（针对联合主键，消除对联合主键的部分依赖）
• 第三范式：第二范式基础上，所有非码属性不依赖于其他非码属性（消除传递依赖）

主键、候选键、超键

唯一标识------>超键
没有多余的列的超键 -------->候选键
被选中的候选键-------->主键
 

索引

帮助快速定位数据的数据结构，但是会增加磁盘占用，影响插入删除修改的速度（要调整索引结构保证正确性）

不适合建立索引的情况：

• 数据量较少
• 数据更新较为频繁的列
• 重复值多没有区分性的列如性别

从索引的底层数据结构分类：

• B+树索引：相比于B树每个节点都保存着关键字域和数据域，B+树只有在叶子结点中保存数据，非叶子结点只保存索引的关键字（1、每个叶子结点数据多且数据集中，一个磁盘块包含的结点和数据较多减少了磁盘IO次数 2、每个数据要搜索的层数相同，稳定），同时叶子结点串成一个链表，遍历全表更快
• Hash索引：类似于hashmap的hash定位，用于memory引擎，对于=和in速度较快，范围查询慢
• 全文索引：针对文本分词提供搜索定位
• R树索引（空间索引）：B+树向高维的扩展，适用于地理信息数据

mysql的引擎
innodb：支持事务、外键、行级锁（针对索引列，否则使用表锁）
myisam：不支持事务，在查询和插入密集型场景下速度快，较为适用
都是用的B+树索引
又可分为：

• 普通索引
• 唯一索引
• 主键索引

聚集索引和非聚集索引

聚集索引：表示B+树的叶子节点索引关键字和真实数据行放在一起，索引关键字顺序和实际数据物理数据一致，目前innodb引擎中主键索引作为聚集索引（只能有一个），其他普通索引又叫辅助索引，B+树的数据域存的是主键值，获取到主键值后再通过聚集索引获取数据行，需要二次查询索引（回表），可以通过建立覆盖索引避免回表即将需要获取的数据项建立联合索引，直接通过索引获取数据。

非聚集索引：数据域存放的是指向实际数据行的指针，myisam中都是非聚集索引，数据域存放实际数据的地址值。（从定义上看上述辅助索引也属于非聚集索引）

联合索引

为何要使用组合索引

• 效率高，减少查询开销，索引列越多，通过索引筛选出的数据越少
• 覆盖索引，MySQL可以直接通过遍历索引取得数据，而无需回表，这减少了很多的随机io操作

最左前缀原则： 使用索引时会从左至右匹配直到遇到范围查询（当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+树是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左匹配特性）。

例：index(a,b,c)
where a=3    只使用了a
where a=3 and b=5    使用了a,b
where a=3 and b=5 and c=4    使用了a,b,c
where b=3 or where c=4    没有使用索引
where a=3 and c=4    仅使用了a
where a=3 and b>10 and c=7    使用了a,b
where a=3 and b like 'xx%' and c=7    使用了a,b

其实相当于创建了多个索引：key(a)、key(a,b)、key(a,b,c)

事务

Mysql中只有Innodb支持事务，事务有ACID特性，通过redolog、undolog、mvcc等实现

提供了四大隔离级别：

1. 读未提交：读到其他事务未提交的数据，其他事务回滚该数据无效，脏读
2. 读提交：通过mvcc解决，每行记录有多个版本，每个版本都记录使其产生的事务的id，每次查询生成一个快照，未提交的和提交了但是在快照创建后提交的都不可读到。解决脏读
3. 可重复读：和读提交类似，但是读是生成一个当前事务的全局快照。解决脏读、不可重复读（通过行锁加间隙锁一定程度上解决了幻读（锁定数据项之间的空间，插入要等待事务完成））
4. 串行化：通过加锁实现，读锁是共享锁，写锁是排他锁。依次执行，解决脏读、不可重复读、幻读

sql优化

• 避免返回不必要的数据
• 避免在where子句中执行null值判断
• 避免在=左侧进行运算操作
• 避免用%对前缀进行模糊查询

explain+语句查看执行计划：type为all即为全表扫描

limit分页查询

limit 10000,20会扫描后丢弃前10000行

优化：增加主键或唯一索引排序后给出查询范围
子查询优化法：select * from table where id>(select id from table limit 10000,1)limit size

连接查询

left join 返回左表全部，right inner类似

 

死锁

死锁的四个条件

1. 互斥条件：每个资源只能同时被一个线程占有
2. 保持占有：线程在申请其他资源时不释放当前已有资源
3. 不可强制获取：针对已被其他线程占有的资源不能强制剥夺
4. 循环等待：多个线程间形成互相等待对方占有资源的循环等待

预防死锁

银行家算法