面试常考的数据库知识

数据库知识点总结

一、 数据库(例：MySQL)
(1)增删改查
Insert into Stu()values()
Updata Stu set name=”ww” where id=1
Delete from Stu where id=2
Select *from Stu where (a join b on a.id=b.id) join c on b.id=c.id
Delect:删除行，表的结构，属性，索引，完整，数据操作语言，需要手动commit
Drop: drop Stu,删除表，被依赖的约束，触发器，索引全没了,数据定义语言，自动
Truncate：用于删除表单数据，不删除表本身。相当于delete不写where

(2)三范式
平凡依赖，全部依赖，传递依赖，多值依赖
范式就是在建数据库遵守的规则，让我们数据冗余小，操作可行性高

1. 第一范式遵守的是列的原子性，就是列不可在分了，列元素都是基础类型，也就是二维表
2. 不存在非关键字段对任一候选关键字段部分函数依赖，候选码三个属性，而两个属性就决定了一个非关键字段
   举例，订单，购买人，产品，颜色，价格，（购买人，产品是主键）而颜色部份依赖产品，所以要分割产品出去
   3．不能出现传递依赖，就是非主键列啊，依赖于非主键B，而非主键B依赖于主键C
   订单号主键，产品，颜色，非主键列颜色依赖产品，产品1依赖订单号
   (3)表设计
   1.多表字段尽量唯一，比如group表加一个gname，
3. 必须用两个表示，不要合成一个字段
   3.主键不能与业务逻辑有关，最好是无意义不重复的uuid,设置自增
   ALTER TABLE tbl AUTO_INCREMENT = 100;
4. 字段使用多的，状态信息类的最好用数字字母
5. 字段长度，id自增设置long字段比实际长3-5字段，但是不能太长，MySQL用的B+树，索引在B树遍历，查询时间太长
6. 尽量不要建立外键，保持表的独立性，必须的话，用id
7. 动态表，静态表隔离，城市信息静态，
8. 用数字码code或数字代替实际名字,名字是注释，code是数据库真正存的，name变了，code不变，而且节省空间，
9. 尽量不要有null,很费时间，可以设置默认值
   Alert table 表名 motify 字段名 default 默认值
10. 数据库不能存照片啥的，路径，连接存储
11. 建表一开始就遵循三大范式，改来改去更费时间，
12. 一对多通过id建立关系，而不是重复数据
13. 设计时预留字段，没准要添加
14. 设置控制字段，isValid筛选数据
15. 删除字段，禁止使用delete不会真正删除，通过设置state状态，修改他表示有没有修改
    (4)主外键
    (5)关联查询(left join、right join、inner join)
    1，left join a left join b 靠着a,行数大于等于a原来,因为a.id=b.nid
    b.nid有多个相同值，并也在a.id中也会显示
    2.可能a有的，b没有，还是显示null
    Select *from Stu where (a join b on a.id=b.id) join c on b.id=c.id
    3.innerjoin full outer join
    (6)数据库函数使用
    Dcount daverage dsum长和group by一起用dmax
    (7)mysql的四大特性
    Acid,原子性，隔离性，一致性，持久性
    (8)四种隔离级别…
    为了解决并发导致的脏读，不不可重复读，幻读问题
    隔离级别越高，越能保持数据完整性和一致性，但并发程度低
    不可重复读锁住行就行，侧重修改，查找之间，别人修改了
    幻读要锁表，侧重新怎删除，我查id=1没有，B插进去了，我不知道啊，查着没有，插入出问题了
16. read-uncommit
    ,读未提交，会导致读脏数据，即B更新，但还没提交，A就拿到了数据，然后B又removeback了，A看到的是改后的，其实现在真实的没改，A更新会按照B改后的更新，实际更新会跟着实际数据走
17. read.commit （orical）
    不可重复读，提交后再读，导致A一样的查询，结果变了。
    3．Repeatable read（MySQL数据库库默认）
    可重复读，会导致幻读，使用了MVCC机制select并不会更新数据版本，但是insert update delete 会更新真实实时的数据
    4．串行没并发
    二、数据库拔高
    (1)全局锁，表锁，行锁，死锁，乐观锁，悲观锁…
    1.全局锁
    用来全库逻辑备份，业务全要停，日志也不能同步
    1.flush tables with read lock 冲刷
    2.set global readonly =true
18. 执行第一种命令之后由于客户端发生异常断开，那么 MySQL 会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为 readonly 之后，如果客户端发生异常，则数据库就会一直保持 readonly 状态，这样会导致整个库长时间处于不可写状态，风险较高。
19. readonly值会被用来做其他逻辑，比如用来判断一个库是主库还是备库（readonly）。
    2.表锁和行锁
    表锁虽然开销小，锁表快，但高并发下性能低。行锁虽然开销大，锁表慢，但高并发下相比之下性能更高。事务和行锁都是在确保数据准确的基础上提高并发的处理能力。Innodb才有了事务，默认行锁InnoDB的行锁是针对索引加的锁，不是针对记录加的锁。并且该索引不能失效，否则都会从行锁升级为表锁。
    如果大量更新数据，还用行锁，MySQL会觉得行锁太多影响效率，转为表锁，即没有使用索引
    行锁：劣势：开销大，枷锁慢，会出现死锁，
    优点：粒度小，锁冲突概率低，并发处理能力强
20. 共享锁排他锁：（这都是行锁分类）
21. 共享锁，读操作事务T对数据A上读锁，别人也可以上读锁，但是不可以上写锁了，就是共享读，不许改
22. 排他锁，只允许事务T对A枷锁可读可写，别人神魔也不能干
23. 死锁
24. 一个用户A 访问表A(锁住了表A),然后又访问表B；另一个用户B 访问表B(锁住了表B)，然后企图访问表A；这时用户A由于用户B已经锁住表B，它必须等待用户B释放表B才能继续，同样用户B要等用户A释放表A才能继续，这就死锁就产生了。一般这都是执行顺序不对，要改变程序逻辑;
25. 用户A查询一条纪录，然后修改该条纪录；这时用户B修改该条纪录，这时用户A的事务里锁的性质由查询的共享锁企图上升到独占锁，而用户B里的独占锁由于A 有共享锁存在所以必须等A释放掉共享锁，而A由于B的独占锁而无法上升的独占锁也就不可能释放共享锁，于是出现了死锁。
    1.按钮只能点一次
    2.乐观锁大多是基于数据版本（Version）记录机制实现。乐观锁机制避免了长事务中的数据 库加锁开销（用户A和用户B操作过程中，都没有对数据库数据加锁），大大提升了大并发量下的系统整体性能表现。（即为数据增加一个版本标识，在基于数据库表的版本解决方案中，一般是 通过为数据库表增加一个“version”字段来实现。读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。此时，将提交数据的版本数据与数 据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据。），乐观锁多用于多读，提高吞吐量如果乐观锁经常产生冲突，就不好了，最后一次提交做查询，或者更新时就带着where 原来select到的内容
    3.每次拿数据都认为会修改，上悲观锁都是在冲突非常严重的系统，

(2)索引，索引底层实现原理，存储方式，如何创建索引，优化索引…
1.普通索引
1.create index indexname on mytable (username(length))
2.alert mytable add index [indexnamae] on (username(length))
3.drop index[indexname] on mytable
2. 唯一索引
Unique可有可无，表示只有一个索引列，可以为空
create index indexname on mytable (username(length))
alert mytable add unique [indexnamae] on (username(length))
UNIQUE [indexName] (username(length))
3. 主键索引不允许有空值
ALTER TABLE mytable ADD INDEX city (name(10),city,age);
usernname长度为 16，这里用 10。这是因为一般情况下名字的长度不会超过10
实际上是三级索引，MySQL组合索引“最左前缀”的结果
name(10),city ,age
name(10),city,
name(10),
也就是，where city=”bei”age=10,不会用到索引

3．索引不足：
1.提高了查询速度，但是降低了更新表的速度，还要保存索引文件
2.索引会占据空间，大表的组合索引会很大空间
4.注意事项：
1．索引不包括含有null值的列，只要有一列有null值，复合索引就失效，所以记得设默认值。
2.短索引，一般字符串前几位就可以指定了，提高效率
3.MySQL只使用一个索引，where 用了后面的order就不会用了
4.like, like”%aaa%”就不会用索引，“aaa%”就可以
5.不要在列上运算
select * from users where YEAR(adddate)<2007;
select * from users where adddate<‘2007-01-01’;
5. 使用函数，或者字符串不加引号会导致全表扫描
6. 不要用<> ,no in 因为他会认为返回的数据会多，全表比索引块
4. 索引原理：
https://blog.csdn.net/weixin_42181824/article/details/82261988
5.B树
https://blog.csdn.net/bigtree_3721/article/details/73151472

1. 底层存储是平衡树B，B+，B不是二叉树，也有用哈希桶的，主流的都是平衡树，mysql是B+，orical是B，属的层数越少，查询效率越高
2. B平衡二叉树：
3. 平衡，树的左右分支的层数相差小于等于一
4. 二叉，非叶节点小于等于两个叶节点
5. 非叶节点大于左孩子，小于右孩子
6. 没有重复值
7. B树：
8. a关键字排序左小右大，
9. 非根节点的子节点数>1,<M-1,M是M杈树，也就是节点的查找路径。
10. 关键字数大于ceil(M/2), 小于M-1，因为二分法，数据比较，
11. 所有叶子节点均在同一层、叶子节点除了包含了关键字和关键字记录的指针外也有指向其子节点的指针只不过其指针地址都为null对应下图最后一层节点的空格子;
12. 比如五杈树创建时，超四个就把中间拿出来
13. 利用了局部性原理，数据在磁盘，操作系统会预读几页，数据库做节点4kb一页的大小，一页用完退出来，里面预存这几页，再掉磁盘，效率高，缓解cpu和io速度不匹配问体
    4.B+树
    1.B树是个大升级，关键字不再保存指针，只进行数据索引，存储率大大提升，减少层数，因为预读磁盘页，接近二分查找，更稳定
    2.叶子节点保存着父节点所有指针，因此只有走到最后才能获取地址，平衡树的原因每次查询次数一样，
    3.多遵守的左小右大。叶子节点也有指向
    4.非叶子节点数等于关键字数或是关键字书减一，
    B+对于B的优点:
    层数少，查询快，所有关键字数据都在叶节点，每次都差这麽多次，遍历全表快，遍历一遍叶子节点就可以，