-
基础类型
bit,tinyint,smallint,int,bigint
fload,double
(M:整数+小数的位数,D:小数的位数)decimal,numeric
(M,D)(正数)char
(255),varchar
(64kb),text
(64kb),mediumtext
(16MB),blob
(字节,64kb)datatime
(8),timestamp
(4,70-38年)
-
基础命令
- 数据库
create if not exists database 库 set utf8mb4
drop if exists 库
show databases
- 表
create table 表(字段 类型)
drop table 表
show tables
desc 表
- 新增列:
alter table 表 add 字段 类型
- 修改列:
alter table 表 modify 字段 新类型
- 修改列:
alter table 表 change 旧字段 新字段 新类型
- 删除列:
alter table 表 drop column 字段
- 行和列
- 新增或替换:
replace into 表(字段) values(值)
- 仅新增:
insert into 表(字段) values(值)
- 有则不增:
insert ignore 表(字段) values(值)
- 导入文件:
load data infile 路径 into table 表
delete from 表 where 条件
select 字段 from 表
update 表 set 字段1=x,字段2=y
- 新增或替换:
- 数据库
-
进阶查询
- 去重:
select distinct 字段 from 表
- 排序:
select * from 表 order by 字段
(ASC升/DESC降) - 条件:
select * from 表 where 表达式
- 分页:
select * from 表 limit n / n,s / n offset s
(s起始,n个数)
- 去重:
-
聚合函数
- COUNT
- SUM
- MAX
- MIN
- AVG
group by 字段 having 条件
-
多表查询
- 内连接:
select 字段 from 表1 别名1 join 表2 别名2 on 条件
- 内连接:
select 字段 from 表1 别名1,表2 别名2 where 条件
- 左连接:
select 字段 from 表1 left join 表2 on 条件
- 右外连接:
select 字段 from 表1 right join 表2 on 条件
- 子查询:
(语句)别名
- 合并查询:
union(并集),union all(并集),intersect(交集,mysql不支持),in(交集),exists(交集合)
- 内连接:
-
数据库三范式
- 表中的每个列都应该是不可分割的最小单元
- 表中的每一列都完全依赖于主键
- 每一列都直接依赖于主键,并且不传递依赖于其他列
-
约束
- 非空:
not null
- 唯一:
unique
- 默认:
default
- 主键:
primary key 字段 auto-increment
- 外键:
foreign key
- 检查:
check (x>1 and x<3)
- 非空:
-
索引
-
底层原理
- B树和B+树
- B树:多路平衡查找树,叶子节点在同层,数据存在每个节点,节点中的键按照升序排序
- B+树:在B树的基础上,数据只存在叶子节点,叶子节点之间相互连接
-
创建索引
- 主键,唯一,外键
create index 索引名 on 表(字段)
show index from 表
drop index 索引名 on 表
-
索引类型
- 数据结构:B+树索引、Hash索引、Full-text 索引(使用倒排索引)
- 物理存储:聚簇索引(聚集索引)、二级索引(辅助索引)
- 索引字段:主键索引、唯一索引、普通索引、前缀索引
- 字段数量:单列索引、联合索引
-
索引覆盖和回表查询
- 索引覆盖:索引包括了查找的所有列,不需要回表查询
- 回表查询:从二级索引获取到主键,再根据主键去聚簇索引查询数据
-
创建原则
-
索引失效
-
-
事务
-
AICD
- 原子性:要么全部完成,要么全部不做
- 一致性:结果一致,A->B,A减B增,总量不变
- 隔离性:不同事务互不干扰,也不可见
- 持久性:执行完的数据保存到磁盘中
-
脏读,不可重复读,幻读
- 脏读:读未提交数据,因事务回滚而数据失效
- 不可重读读:同一个数据,每次读的结果不一样
- 幻读:旧数据没有改变,但是新插入的数据导致事务出现问题(快照读,MVCC只能看到数据的改变,看不到数据的插入)
-
-
相关锁
- 行锁:指对表中的某一行数据进行锁定,粒度最细,并发性能影响最小。
- 表锁:指对整张表进行锁定
- 间隙锁:索引范围中的“空隙”进行锁定,防止其他事务在这个范围内插入新数据。间隙锁用于解决幻读问题
-
分析慢查询
- 慢查询日志
slow_query_log=1(开启慢日志)
long_query_time=2(超过2s)
- Skywalking,Prometheus,Arthas等工具
- 慢查询日志
-
explain/desc(seleect信息)
- 可能使用的索引:
possible_keu
- 命中索引:
key
- 索引长度:
key_len
- 优化建议:
Extran(Using where/Index, condition)
- 读取行数:
rows
- 索引等级:
type
- 可能使用的索引:
-
SQL优化
-
使用集群
-
分库分表
-
表优化
- 合适的数据类型
-
索引优化
- 索引要符合创建原则
-
SQL语句优化
- select指定列
- 尽可能的使用索引,避免索引失效
- 多表连接要以小表为驱动
- union all > union > or
-
-
MySQL的存储引擎
- MyISAM(早期,表锁)
- InnoDB(默认,事务,表行锁,外键)
- MEMORY(内存,表锁)
-
相关日志
- 回滚日志:
undo log
(反向操作,原子性,一致性) - 重做日志:
redo log
(内存缓冲池,数据页16kb,持久性) - 二进制日志:
bin log
(DDL数据定义,DML数据操纵语句) - 中继日志:
relay log
(主从结构,同步数据) - 错误日志:
error log
- 慢查询日志:
show query log
- 一般查询日志:
general log
- 回滚日志:
-
MVCC(多版本并发控制,隔离性)
-
针对修改操作:一行数据的隐藏字段
(最近事务ID,回滚指针,隐藏主键)
和undo log构成此数据的版本链 -
针对读操作:生成ReadView,RC(读已提交)每次读都生成新的,RR(读未提交)只用第一次读生成的
- ReadView:
(活跃事务的ID集合,最小活跃事务ID,预分配事务ID,快照创建事务ID)
- 根据ReadView到数据的版本链中比对:读取符合规则的(两种版本可读:当前事务中,非活跃的其它事务)
- ReadView:
-