MySQL
2022-08-12 17:54:46 0 举报AI智能生成
登录查看完整内容
MySQL基础知识梳理
作者其他创作
大纲/内容
客户端与MySQL服务器通过连接器建立连接（TCP连接）——查缓存，缓存命中直接返回结果，缓存找不到——分析器语法分析——优化（索引）——执行器执行（返回结果，缓存到查询缓存中）
MySQL查询过程
磁盘读写代价低
只需遍历叶子结点
首先答B+树的两个优点
虽然可以快速查找，但是无序，只适合等值查询，不适合范围查找，排序时也无法像B+树一样利用索引
存储引擎中只有Memory显式支持哈希索引
在大量重复数据的情况下，哈希碰撞，查询效率极低
不支持部分索引列的匹配查找（因为索引始终索引的所有列的全部内容）
Hash缺点
B+树本来就是B-树的改进版本，B+树数据都在叶子结点上，并且叶子结点间有顺序访问指针，更支持范围查找
B-树缺点
树的高度随数量的增加而增加
IO代价高
红黑树缺点
不是平衡树，树易于一边倾斜效率低下
IO代价也高
二叉树缺点
为什么使用B+树而不是用B- 树，Hash，红黑树，二叉树
先在非聚簇索引中查找到主键，再根据主键聚簇索引查找数据的过程
什么是回表
不一定哦，当查询字段全部命中索引时，就直接查找到了，不必再回表，比如对age建立索引，则在select age from s where age > 2;时，直接就找到了
非聚簇索引一定回表吗
回表查询
使用多个字段同时建立一个索引
联合索引
在多列索引时，MySQL会一直向右匹配直到遇到范围查询
最左前缀原则
指运行时间超过long_query_time值的SQL
1.设置慢查询
2.分析慢查询日志
3.定位低效率的SQL
4.expain分析执行计划（如命中索引没）
尽量覆盖索引
索引符合最左匹配原则
避免索引失效
索引优化
分页查询优化
插入成组插入
数据有序插入等
SQL语句优化
分表
合表
中间表等
数据库结构优化
优化器优化、架构优化等（磁盘预读，读写分离等）
5.优化
如何优化
慢SQL
其他面试题
读写分离——一个主库附有多个从库，每次只需写入主库，主库自动把数据同步给从库
主从同步——数据可以从一个数据库服务器复制到其他服务器上
读写分离？主从同步？
提高数据库性能，只在主库写，主库自动把数据复制到从服务器，每次从从服务器读
提高数据安全，可以在从服务器上备份而不破坏主服务器相应数据
数据备份
作用
当多用户并发存取数据时，即多个事务对同一批数据操作时，为了保证数据的正确性一致性完整性，就需要考虑锁
并发度最高，粒度最细，开销大，加锁慢，发生锁冲突的概率最低，但有可能发生死锁
行锁
指一次锁定相邻的一组记录，注意，InnoDB是不支持页锁的，只支持另两种
页锁
并发度最低，粒度最粗，开销小，加锁快，锁冲突的概率最高，但不会发生死锁
表锁
按锁的粒度
共享锁（读锁）
排他锁（写锁）
悲观锁
乐观锁
按锁的类别
分类
使用较低的隔离级别
尽量使用索引使加锁更准确
只读无需加锁
选择合理的事务大小，尽量一次性请求足够级别的锁
锁优化
锁
逻辑上的一组操作，要么都执行要么都不执行
原子性
靠“我们”共同保证
一致性
串读（可串行化）
幻读
可重复读
不可重复读
读已提交
脏读
读未提交
隔离性
InnoDB的默认隔离级别
持久性
四大特性
事务
关系型数据库、开源免费、不适合复杂业务
超码——能唯一标识一个元组的一个/一组属性
主码——最小的超码是主码，主码可以有多个
候选码——从主码中选一个做主码
外码——在另一个表中是主码
各种码/键
第一范式——原子性
第二范式——非主属性对主属性完全函数依赖（排除了部分函数依赖）
第三范式——非主属性直接依赖于非主属性（排除了传递函数依赖）
BC范式——排除了任何属性（包括主属性）传递函数依赖和部分函数依赖
范式
Primary Key——主键
Foreign Key——外键
Not Null——不允许为空
Unique——唯一不重复
AI——自增
约束
概念
-- 如果 taobao_0326 库存在，就删掉这个库DROP DATABASE IF EXISTS `taobao_0326`;-- 重新创建一个新库CREATE DATABASE `taobao_0326` DEFAULT CHARACTER SET utf8mb4;
建库删库
建表
drop table if exists student;
drop
truncate table stutent;
truncate
删表
drop删除，直接把数据连带着表结构全部删除，删除速度也最快，而truncate只是清空表中所有数据，表结构仍是存在；共同点：都属于DDL，都不可回滚；下面DML中还有一个delete删除，delete删除是按行删，删除速度也最慢
建表删表
DDL——数据定义语言，结构处理，如新建库表
tinyInt、smallInt、mediumInt、int、bigInt
整数
float
double
浮点
数值型
char——定长，固定大小
varchar——变长，varchar(45)表示最长45字符
blob——0-65535字节二进制形式的长文本，存储图片、音频这些
longblob
text——0-65535字节长文本数据
longtext
字符型
data——YYYY-MM-DD
time——HH:MM:SS
datatime——YYYY-MM-DD hh:mm:ss
表示时间戳，从1970-01-01 00：00：00开始到现在经过的秒数
timestamp——YYYY-MM-DD hh:mm:ss
year——yyyy
日期型
字段常用数据类型
DCL——数据控制语言，如添加用户，修改密码
增
delete from students;删除整张表delete from students where……删除指定的几行
删
in——外表大内表小适用in
exists——外表小内表大适用exists
inner join
内连接（默认）
left join
right join
外连接
自连接
连表查询（笛卡儿积）
limit m offset n——从n开始取m条，n可以取到0
distinct去重
union——合并重复行
union all ——不合并重复行
查
对于not in和not exists，一般来说not exists效率更高一点，因为如果查询语句使用了not in，那么内外表都进行全表扫描，没有用到索引；而not extsts的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快
where筛选的是数据库中本来就有的字段，是先where筛选再select，而having是先select再having筛选，是在已经筛选过的新库表里再having
where VS having
改——update students set name = b where
DML——数据操纵语言，CURD
转大写
upper()
转小写
lower()
拼接两个字符串
year()
month()
sum()
count()
max()
min()
avg()
聚合函数
部分常用函数
SQL语句
InnoDB支持事务、外键，而MyISAM并不支持；InnoDB的主键索引是聚簇索引，而MyISAM无论主键还是二级都是非聚簇索引；InnoDB采取行锁（默认）和表锁，而MyISAM采取表锁；InnoDB不支持全文索引而MyISAM支持；InnoDB不保存表的具体行数，而MyISAM设有一个变量保存行数
InnoDB VS MyISAM
聚簇索引：数据和索引是一个文件，即索引中存储的就是数据；非聚簇索引：数据和索引在不同的文件，索引存储的是指向数据的指针（该行数据的主键/行号）
全文索引：就是做的项目里实现的倒排索引，建立所有词的频率和出现位置
解释
存储引擎
特殊的数据文件，包含数据库里所有记录的引用指针；就是为了提升查询速度
提升查询速度
优点
索引的创建维护需消耗额外的时间
索引也占据了一定物理空间
增删改操作也要额外维护索引，性能下降
缺点
普通索引
主键索引
唯一索引
全文索引
时空索引
按应用层次划分
Hash索引
B-树索引——数据分布在各个结点
B+树大大降低了树的高度，内部节点并没有指向关键字具体信息的指针，因此其内部节点相对B-树更少，所以减少了IO磁盘读写次数，读写代价低
B+树的数据只存储在叶子节点，其他结点保存的是指向数据的索引，故而只需扫描叶子节点，无需中序遍历整棵树，更适用于范围查询，排序等
B+树索引——也是InnoDB默认使用的
R树索引等
按存储结构划分
聚簇索引
非聚簇索引
按物理地址与逻辑地址
注意：InnoDB的主键索引是聚簇索引，二级索引是非聚簇索引；而MyISAM无论是主键索引还是二级索引都是非聚簇索引
使用 ！= 或者 <>
对索引列使用函数或运算符
模糊查询中，%放在前
对其他字段or
not in导致索引失效
索引失效
索引
注意：UTF-8中一个中文占3个字节，中文数字标点都占一个字节
606，626
MySQL
0 条评论
回复 删除
下一页
