MySQL精简笔记

▮基础类型

类型	描述
整型	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年）

▮基础命令

• 库操作

操作	SQL 命令
创建数据库	create database if not exists 库 character set utf8mb4;
删除数据库	drop database if exists 库;
显示所有数据库	show databases;
使用数据库	use 库;

• 表操作

操作	SQL 命令
创建表	create table 表 (字段 类型);
删除表	drop table if exists 表;
显示所有表	show tables;
查看表结构	desc 表;

• 列操作

操作	SQL 命令
新增列	alter table 表 add column 字段 类型;
修改列类型	alter table 表 modify column 字段 新类型;
重命名列	alter table 表 change column 旧字段 新字段 新类型;
删除列	alter table 表 drop column 字段;

• 行操作

操作	SQL 命令
新增或替换行	replace into 表 (字段) values (值);
仅新增行	insert into 表 (字段) values (值);
有则不新增	insert ignore into 表 (字段) values (值);
导入文件	load data infile '路径' into table 表;
删除行	delete from 表 where 条件;
查询行	select 字段 from 表;
更新行	update 表 set 字段1 = x, 字段2 = y where 条件;

▮数据库三范式

• 表中的每个列都应该是不可分割的最小单元
• 表中的每一列都完全依赖于主键
• 每一列都直接依赖于主键，并且不传递依赖于其他列

▮进阶查询

操作	SQL 命令
去重	select distinct 字段 from 表;
排序	select * from 表 order by 字段 [asc升/desc降];
分页	select * from 表 limit n [n,s] [n offset s];（s起始，n个数）
条件查询	select * from 表 where 表达式;

▮聚合查询

• COUNT，SUM，MAX，MIN，AVG
• group by 字段 having 条件

▮多表查询

操作	SQL 命令
内连接1	select 字段 from 表1 别名1 join 表2 别名2 on 条件;
内连接2	select 字段 from 表1 别名1, 表2 别名2 where 条件;
左连接	select 字段 from 表1 别名1 left join 表2 别名2 on 条件;
右连接	select 字段 from 表1 别名1 right join 表2 别名2 on 条件;
子查询	(select 字段 from 表 where 条件) 别名;
合并查询1	select 字段 from 表1 union select 字段 from 表2;
合并查询2	select 字段 from 表1 union all select 字段 from 表2;
交集查询1	select 字段 from 表1 intersect select 字段 from 表2;
交集查询2	select 字段 from 表1 where 字段 in (select 字段 from 表2);
交集查询3	select 字段 from 表1 where exists (select * from 表2 where 条件);

▮约束

约束	SQL 命令
非空	not null
唯一	unique
默认值	default 值
主键	primary key 字段 [auto_increment];
外键	foreign key 字段 references 参照表 (参照字段);
检查约束	check (表达式);

▮索引

• 底层原理

  • B树和B+树
  • B树：多路平衡查找树，叶子节点在同层，数据存在每个节点，节点中的键按照升序排序
  • B+树：在B树的基础上，数据只存在叶子节点，叶子节点之间相互连接

• 创建索引 (主键，唯一，外键)

操作	SQL 命令
创建索引	create index 索引名 on 表(字段1, 字段2);
显示索引	show index from 表;
删除索引	drop index 索引名 on 表;

• 索引类型

  • 数据结构：B+树索引、Hash索引、Full-text 索引（使用倒排索引）
  • 物理存储：聚簇索引（聚集索引）、二级索引（辅助索引）
  • 索引字段：主键索引、唯一索引、普通索引、前缀索引
  • 字段数量：单列索引、联合索引

• 索引覆盖和回表查询

  • 索引覆盖：索引包括了查找的所有列，不需要回表查询
  • 回表查询：从二级索引获取到主键，再根据主键去聚簇索引查询数据

• 创建原则
  

• 索引失效
  

▮事务

• ACID

特性	描述
原子性	要么全部完成，要么全部不做。事务中的所有操作作为一个整体被提交或回滚。
一致性	结果一致，如从状态 A 转移到状态 B，A 减少的数量等于 B 增加的数量，确保总量不变。
隔离性	不同事务之间互不干扰，一个事务的操作对其他事务不可见，直到该事务成功提交。
持久性	一旦事务成功提交，其结果就会被永久保存到磁盘上，即使系统发生故障也不会丢失。

• 脏读，不可重复读，幻读

问题	描述
脏读	读取了未提交的数据，如果事务回滚，读取的数据将无效。
不可重读	两次读取同一数据时结果不一致，因为其他事务修改了数据。
幻读	读取同一范围的数据时，由于其他事务插入了新的行，导致读取的行数发生变化。 快照读，MVCC只能看到数据的修改，看不到数据的插入

▮相关锁

• 行锁：指对表中的某一行数据进行锁定，粒度最细，并发性能影响最小。
• 表锁：指对整张表进行锁定
• 间隙锁：索引范围中的“空隙”进行锁定，防止其他事务在这个范围内插入新数据。间隙锁用于解决幻读问题

▮分析慢查询

• 慢查询日志
  • slow_query_log=1（开启慢日志）
  • long_query_time=2（超过2s）
• Skywalking，Prometheus，Arthas等工具

▮explain/desc（seleect信息）

指标	解释
possible_key	可能使用的索引
key	实际使用的索引
key_len	使用的索引长度
extra	优化建议，例如 using where, using index, using index condition
rows	估计的读取行数
type	访问类型，例如 all, index, range, ref, eq_ref, const, system, null

▮SQL优化

• 表优化

  • 合适的数据类型

• 索引优化

  • 索引要符合创建原则

• SQL语句优化

  • select指定列
  • 尽可能的使用索引，避免索引失效
  • 多表连接要以小表为驱动
  • union all > union > or
    

• 使用集群

• 分库分表

▮MySQL的存储引擎

• MyISAM（早期，表锁）
• InnoDB（默认，事务，表行锁，外键）
• MEMORY（内存，表锁）

▮相关日志

日志类型	名称	描述
undo log	回滚日志	用于回滚事务，记录反向操作，确保原子性和一致性
redo log	重做日志	用于记录数据页的物理更改，内存缓冲池，数据页16kb，确保持久性
bin log	二进制日志	记录数据定义语言 (ddl) 和数据操纵语言 (dml) 语句，用于复制和恢复
relay log	中继日志	在主从复制环境中，存储从主服务器接收的更新，以便同步到从服务器
error log	错误日志	记录错误消息和警告
slow query log	慢查询日志	记录执行时间超过指定阈值的慢查询
general log	一般查询日志	记录所有客户端连接和查询语句

▮MVCC（多版本并发控制，隔离性）

• 针对修改操作：行的隐藏字段（最近事务ID，回滚指针，隐藏主键）和 undo log构成此数据的版本链

• 针对读操作：生成ReadView，RC（读已提交）每次读都生成新的，RR（读未提交）只用第一次读生成的

  • ReadView：（活跃事务的ID集合，最小活跃事务ID，预分配事务ID，当前事务ID）
  • 根据ReadView到数据的版本链中比对：读取符合规则的（两种版本可读：当前事务中，非活跃的其它事务）
    

▮集群

• 主从结构，读写分离
• 主节点的bin log日志记录了所有定义和操作语句
• 从节点获取主节点的bin log，写入到relay log
• 从节点执行relay log中的语句

▮分库分表

• 垂直分库：不同业务分到不同库（用户，订单，商品）
• 垂直分表：不同字段放入不同表（text，blob，不常用字段）
• 水平分库：一个库变多个库（1_100w，100w200w）
• 水平分表：一张表变多张表（1_100w，100w200w）

▮ 分库分表后出现的问题

• 分布式一致性
• 跨节点查询、分页，排序
• 主键避重
• MyCat等工具有解决办法

▮分库分表后如何生成全局ID

• UUID(不推荐，无序，引起页的分裂和合并)
• redis全局ID
• 雪花算法（递增，1符号位，41时间戳，10机器号，12序列号）