👩💻博客主页：大家好我是poizxc2014的博客主页
✨欢迎关注🖱点赞🎀收藏⭐留言✒
📖个人主页：poizxc2014的博客_CSDN博客-数据库,mysql,java领域博主💻首发时间：🎞2022年05月02日🎠
🎨你做三四月的事，八九月就会有答案，一起加油吧
🔥💖🔮😘🔏🀄🎧如果觉得博主的文章还不错的话，👍请三连支持一下博主哦🤞如果觉得博主的文章还不错的话，请三连支持一下博主哦
最后的话，在很多方面还做的不好的地方，欢迎大佬指正，一起学习哦，冲冲冲
目录
关系型数据库主要：
1.架构 2.索引 3.锁 4.语法 5.理论范式
如何设计一个关系型数据库：
RDBMS 程序实例: 1.存储管理 2.缓存机制 3. SQL解析 4.日志管理 5.权限划分 6.容灾机制 7.索引管理 8.锁管理
存储（文件系统）:
1.机械硬盘
2.SSD固态硬盘索引模块：
为什么要使用索引
1.快速查询数据
2.直接加载到内存里进行全表扫描--灵感来源于字典
3.关键信息组成了索引
什么样的信息能成为索引
主键、唯一键以及普通键等 红黑树是平衡二叉树的一种
索引的数据结构
1. 建立二叉查找树进行二分查找 2. 建立B Tree结构进行查找 3. 建立B+ Tree结构进行查找 4. 建立Hash结构进行查找
二叉查找树：
每个节点最多有两个树的数结果，对于数中的每个节点x，左子数小于x，右子数大于x 用二叉查找树作为索引能提高效率
对半搜索
平衡二叉数 O(logn) 线性二叉数 O(n)，为了降低IO
B Tree规则：
1.根节点至少包括两个孩子 2.数中每个节点最多含有m个孩子(m>=2) 3.除根节点和叶节点外，其他每个节点至少有ceil(m/2)个孩子 4.所有叶子节点都位于同一层 约束的目的：每个索引块尽可能存储更多的信息，让数的高度尽可能的减少IO次数 5.关键字按顺序升序排序 任意节点的关键字上限比孩子树少一个 最左侧的关键字大于子数；最右边关键字的最右边孩子大于关键字节点里所有的值；关键字的中间孩子值，位于区间内
B+树是B树的变体，其定义基本与B数相同，除了：
1.非叶子节点的子树指针与关键字个数相同 2.非叶子节点的子树指针，指向关键字值区间内 3.非叶子节点仅用来做索引，数据都保存在叶子节点中 4.所有叶子节点均有一个链指针指向下一个叶子节点(支持范围统计)
B+Tree更适合用来做存储索引：
1.B+树的磁盘读写代价更低 2.B+数查询效率更加稳定 3.B+树更有利于对数据库的扫描
Hash索引：
缺点 1.仅仅能满足"=","in",不能使用范围查询 2.无法被用来避免数据的排序操作 3.不能利用部分索引键查询 4.不能避免表扫描 5.遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高
BitMap索引是个神奇(oracle)：
1.位图索引 2.索引模块
密集索引和稀疏索引的区别
1.密集索引文件中的每个搜索码值都对应一个索引值 2.稀疏索引文件只为索引码的某些值建立索引项
InnoDB：
1.若一个主键被定义，该主键则作为密集索引 2.若没有主键被定义，该表的第一个唯一非空索引则作为密集索引 3.若不满足以上条件，innode内部会生成一个隐藏主键(密集索引) 4.非主键索引存储相关键位和其对于的主键值，包含两次查找 InnoDB(聚簇)索引和数据存放在同一个idb后缀文件下 Myisam(非聚簇)索引和数据存放是分开的
为什么要使用索引
因为索引能够避免全表扫描查找数据，提示检索效率
什么样的信息能成为索引
主键、唯一键，对数据有唯一区分性
索引的数据结构
主流是：B+树，Hashmap，bitmap mysql不支持bitmap,基于innodeDB和myisam不显式支持hash
mysql其他衍生
如何定位并优化慢查询sql
1.根据慢日志定位慢查询sql
2.使用explain等工具分析sql
3.修改sql或者尽量让sql走索引
show variables like '%quer%';查询慢sql配置
show status like '%slow_queries%'; 查询慢sql条数
set global show_query_log=on;
set global long_query_time=1;
Explain关键字段：
1.type:
system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>all
2.extra:
filesort外部索引排序
temporary临时表
explain select name from person_info_large order by name desc;
explain select account from person_info_large order by account desc;
select account from person_info_large order by account desc;
alter table person_info_large add index idx_name(name);
select count(id) from person_info_large;
select count(id) from person_info_large force index(primary);联合索引的最左匹配原则的成因
1.最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配知道遇到范围查询> < between like 就停止匹配，比如a=3 and b=4 and c>5 and d=6如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,c,d)的索引则都可以用到，a,b,d的顺序可以任意调整 2.=和in可以乱序，比如a=1 and b=2 and c=3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式
索引是建立的越多越好吗
1.数据量小的表不需要建立索引，建立会增加额外的索引开销 2.数据变更需要维护索引，因此更多的索引意味着更多的维护成本 3.更多的索引意味着也需要更多的空间
锁模块
MyISAM与InnoDB关于锁方面的区别是什么
1.MyISAM默认用的是表级锁，不支持行级锁
2.InnoDB默认用的是行级锁，也支持表级锁
select count(*) from person_info_large;
select count(*) from person_info_myisam;
select *from person_info_myisam where id between 1 and 2000000;
lock tables person_info_myisam read |write;
unlock tables;
update person_info_large set title ="test" where id =1;
show variables like 'autocommit';
set autocommit =0;#关闭自动提交
update person_info_large set title="test4" where id =4;
commit;
共享锁与排它锁
先读后写MyISAM适合的场景：
1.频繁执行全表count语句 2.对数据进行增删改的频率不高，查询非常频繁 3.没有事务
InnoDB适合的场景：
1.数据的增删改都相当频繁 2.可靠性要求比较高，要求支持事务
数据库锁的分类
1.按锁的粒度划分，可分为表级锁、行级锁、页级锁
2.按锁级别划分，可分为共享锁、排它锁
3.按加锁方式划分，可分为自动锁、显式锁
4.按操作划分，可分为DML锁、DDL锁
5.按使用方式划分，可分为乐观锁、悲观锁
show variables like 'autocommit';
select version from test_innodb where id =2;
update test_innodb set money =123,version =0+1 where version=0 and id =2;数据库事务的四大特性ACID
1.原子性Atomic 2.一致性consistency 3.隔离性Isolation 4.持久性Durability
事务并发访问引起的问题以及如何避免
1.更新丢失--mysql所有事务隔离级别在数据库层面上均可避免
2.脏读--READ-COMMITTED事务隔离级别以上可避免
select @@tx_isolation;
set session transaction isolation level read uncommitted;
start transaction;
update account_innodb set balance =1000-100 where id =1;
select *from account_innodb where id =1;
rollback;
set session transaction isolation level read committed;#oracle默认级别
3.不可重复读--REPEATABLE-READ事务隔离级别以上可避免
select @@tx_isolation;
set session transaction isolation level repeatable read;
4.幻读--SERIALIZABLE事务隔离级别可避免
select @@tx_isolation;
set session transaction isolation level serializable read;InnoDB可重复读隔离级别下如何避免幻读
1.表象：快照读(非阻塞读)--伪MVCC 2.内在：next-key锁(行锁+gap锁)
当前读和快照读
1.当前读：select...lock in share mode,select...for update
2.当前读：update,delete,insert
3.快照读：不加锁的非阻塞读，select
select @@tx_isolation;
set session transaction isolation level repeatable read;
start transaction;
select * form account_innodb where id =2;
select * form account_innodb where id =2 lock in share mode;
commit;RC、RR级别下的InnoDB的非阻塞读如何实现
1.数据行里的DB_TRX_ID、DB_ROLL_PTR、DB_ROW_ID字段 2.undo日志 3.read view
next-key锁(行锁+gap锁)
1.行锁 2.Gap锁
对主键索引或者唯一索引会用Gap锁吗
1.如果where条件全部命中，则不会用Gap锁，只会加记录锁 2.如果where条件部分命中或者全不命中，则会加Gap锁
Gap锁用在非唯一索引或者不走索引的当前读中
1.非唯一索引 2.不走索引
关键语法
1.group by 2.having 3.统计相关：count,sum,max,min,avg
group by
1.满足“SELECT子句中的列名必须为分组列或列函数”
2.列函数对于group by子句定义的每个组各返回一个结果
查询所有同学的学号、选课数、总成绩
select student_id,count(course_id),sum(score) from score group by student_id;
查询所有同学的学号、姓名、选课数、总成绩
select s.student_id,stu.name,count(s.course_id),sum(s.score)
from
score s,
student stu
where
s.student_id=stu.student_id
group by s.student_id;having
1.通常与group by子句一起使用
2.where过滤行，having过滤组
3.出现在同一sql的顺序：where>group by>having
查询平均成绩大于60分的同学的学号和平均成绩
select student_id,avg(score)
from score
group by student_id
having avg(score)>60;
查询没有学全所有课的同学的学号、姓名
select stu.student_id,stu.name
from
student stu,
score s
where stu.student_id=s.student_id
group by s.student_id
having count(*)<
(
select count(*) from course
)
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
