Stay Hungry, Stay Foolish.

MySQL 基础（一）

TTS

2024年2月26日

MySQL 基础（一）

数据类型

整型

tinyint，1字节
smallint，2字节
mediumint，3字节
int，4字节
bigint，8字节
默认是带符号的，可以加unsigned表示无符号。
int(n)，n不是表示长度，不会影响存储，仅用于控制显示的宽度。

浮点型

float，4字节
double，8字节
double(m,d)，m表示总长度，d表示小数位，超出四舍五入。
存在精度丢失问题，避免等值比较。

实数型

decimal(m,d)，16字节，m表示总长度，d表示小数位，超出四舍五入。
不存在精度丢失问题。
适合存储金额等精度敏感数据。

日期类型

date，3字节，年月日
time，3字节，时分秒
year，1字节，年
datetime，8字节，年月日时分秒，默认为空。
timestamp，4字节，时间戳
- 1970-01-01 00:00:00UTC 到当前时间的毫秒数。
- 最大到 2038 年。
- 默认当前时间。
- 创建和修改时间一般用 timestamp。

字符串

char(n)，定长字符串
- n表示字符串长度，超出截断。
- 最大长度255，与字符编码无关。
- 长度不够补空格。
varchar(n)，变长字符串
- n表示字符串最大长度，超出截断。
- 不同字符编码，一个字符占用字节数不同。
- 一行数据最大长度64K，减去其他字段占用才能计算n最大值。
- 一般建议n 不要超过 5K，如果大于可使用 text 并且独立表。
text，大型文本
- 不需要指定长度限制。
- 一行数据最大长度64K和字符编码决定的最大长度限制。
- 不能建索引。
blob，二进制大对象
- 其限制及使用类似 text。

数据类型的属性

not null，非空
default，默认值
unsigned，无符号
auto_increment，自增，适用于整型。
primary key，主键，唯一标识，不能重复，一个表只能有一个主键。

sql

DDL

数据库定义语言，如创建数据库表结构等。

DML

数据库操作语言，如数据的增删改查。

查询分页

使用limit m,n：m表示起始位置，n表示查询条数。
数据量很大时，可以用覆盖索引分页查出 ID，再根据 ID 查数据。

批量插入数据

insert 的值支持多行，可以同时插入多行数据。
在一个事务内批量插入，避免每次插入后自动提交。

清空表数据

DELETE，常规删除操作，可以回滚。
TRUNCATE，属于 DDL 操作，不能回滚，速度快，自增标识会重置。

约束

主键约束：用来唯一标识一行数据，不能重复，不能为空。
唯一约束：用来唯一标识一行数据，不能重复，可以为空。
自增长约束：从1开始每次加1，和主键配合使用。
外键约束：用来和其他表建立联系的字段，是另一表的主键，可以重复可以为空，可以有多个外键。
非空约束：不能为空。
默认值约束：不指定值时使用默认值填充。

可空字段可能导致的问题

count数据丢失，count(*)和count(可空字段)结果不一样，前者是推荐用法。
select数据丢失，对可空字段进行非等于比较查询时，NULL数据丢失。
空指针异常，sum(可空字段)统计不存在的数据时，结果为NULL而非0。
增加查询难度，当需要进行条件比较时需考虑NULL值，增加is not null判断条件。
索引失效，null值在索引中被视为最小值，某些场景下可能出现索引失效。

数据库设计范式

关系型数据库设计范式，是保证数据完整性和减少冗余的原则。
第一范式：要求表字段是不可分割的单一属性。
第二范式：在第一范式基础上，要求表中每个字段都和主键相关，不能依赖于主键的一部分。
第三范式：在第二范式的基础上，要求除主键外的其它字段必须互不依赖。
优点：符合三范式可以确保数据是准确、一致和易于维护的。
缺点：过度规范化导致设计复杂，降低性能，实际应用中需要权衡规范化和性能。

mysql 架构

Server 层

实现跨存储引擎的功能，包括：
- 连接器：管理客户端连接，实现认证、权限、加密等。
- 分析器：词法分析、语法分析、语义分析等。
- 优化器：SQL语句优化，如选择索引、join优化等。
- 执行器：负责执行具体操作，如 CRUD、函数、存储过程等。

存储引擎层

负责数据的存储和提取。
插件式，一个数据库的多个表支持不同的存储引擎。
常用是 InnoDB 和 MyISAM，默认是 InnoDB。

执行查询语句的过程

建立连接，验证身份，给于权限。
查询缓存，有则返回，mysql 8.0版本后移除，因为有更新或条件不同则缓存失效作用不大。
分析器，进行sql语法分析。
优化器，选择最优方案，生成执行计划。
执行器，根据执行计划，调用存储引擎接口执行。

mysql 连接

mysql 连接器

每一个数据库连接，都会创建一个线程来处理。
默认最大连接数是151，超过会等待。
默认连接方式是 TCP，默认端口 3306。
认证方式：
- 密码认证模式，通过用户名和密码认证。
- SSL认证模式，用户名密码基础上增加 SSL 安全连接。

客户端连接池

客户端连接数据库时，一般通过连接池方式。
连接池维持一定数量的连接，需要时取出，使用完放回。
一般会设置最小连接数和最大连接数来控制数量。
优点：减少频繁创建和销毁连接带来的开销，限制连接数。

mysql存储引擎

mysql 采用插件式存储引擎，一个数据库的多个表支持使用不同的存储引擎。
常用是 InnoDB 和 MyISAM，默认是 InnoDB。
InnoDB
- 默认存储引擎，支持ACID事务、外键和行锁。
- 并发条件下要求数据一致性，适合更新比较频繁的场景。
MyISAM
- 不支持事务，不支持外键，只支持表锁。
- 适用于读多写少且对事务要求不高的场景。

索引

定义：索引是单独的、物理的对数据库表中的一列或多列进行排序的存储结构。
作用：相当于图书的目录，用于提高查询效率，降低 IO 成本。

分类

主键索引：主键唯一且不为空，是一种特殊的唯一索引。
唯一索引：索引列值必须唯一，但允许有空值。
普通索引：索引列允许重复。
联合索引：对多列进行索引，使用最左匹配原则。
全文索引：一般不用，不是 mysql 专长。

数据结构

B+树，平衡多路查找树，时间复杂度O(logn)。
哈希，时间复杂度O(1)，只支持等值查询，不支持排序和范围，innodb 自动创建的内存索引。

物理存储

聚集索引：叶子节点包含完整一行数据，类比于字典的按首字母排序组织。
- 一个表必须有一个聚集索引。
- 默认使用主键，然后使用非空唯一索引，都没有则生成隐藏自增列作为聚集索引。
非聚集索引（辅助索引）：叶子节点仅包含主键，查询非主键字段需要回表二次查询。
- NULL值作为最小数看待，全部放在树的最左边。

索引分裂

插入数据时，如果不是插入到叶子节点的最后，而是插入到中间，则可能导致索引的页的分裂，导致磁盘数据移动。
使用自增ID作为主键可以减少索引分裂的发生。

最左匹配原则

以下情况会失效：
（A、B、C）索引，查询（B、C）没有最左列会失效，注意 mysql 会自动优化查询条件的顺序。
Like VAL%可以使用索引，而 Like %VAL%会失效。
索引列运算、函数或隐式转换会失效。

哪些列适合加索引

经常作为查询条件的字段。
需要 join 连接的字段。
需要排序的字段。
需要group by 的字段。
字段值的离散程度大时才需要加索引，值重复率高的不适合加索引。

覆盖索引

索引包含所有需要查询的字段，是常用的优化手段。
对于非聚集索引，查询结果只包含索引字段或主键，可以避免回表做二次查询，提高效率。

索引下推

将服务层的工作下推到存储引擎层，减少回表次数。
在联合索引中，同时使用范围查询和等值查询，索引下推就可以发挥作用。

事务

事务的ACID特性

A（原子性）：要么都成功，要么都失败，不可能出现部分完成情况。
C（一致性）：事务执行前后，数据库完整性约束不会被破坏。
I（隔离性）：并发环境，事务不会相互干扰。
D（持久性）：事务成功，数据必须完整存盘不会丢失。

事务的隔离级别

隔离级别是指多个并发事务中保持数据一致性的隔离程度，决定了一个事务对其他事务产生的影响。
并发事务可能出现的不一致情况：
- 脏读：读到其他事务未提交的数据。
- 不可重复读：一个事务内，一开始读取的数据和结束前任意时刻读取的同一批数据出现不一致。
- 其他事务对某些行的修改或删除。
- 幻读：读到其他事务插入的数据。
隔离级别从低到高分别为：
- 读取未提交（RU，Read Uncommitted）：允许脏读、不可重复读、幻读。
- 读取已提交（RC，Read Committed）：不允许脏读，允许不可重复读、幻读。
- 可重复读（RR，Repeateable Read）：不允许脏读和不可重复，允许幻读。
- 串行化（S，Serializable）：所有事务串行化执行，不允许脏读、不可重复读、幻读。

MVCC

MVCC 是多版本并发控制。
可以解决脏读、不可重复读和部分幻读的事务隔离问题。
有了 MVCC，只有写写会相互阻塞，读读、读写、写读都可以并行处理，提高了并发度。
MVCC 的实现主要依赖：undo 日志和读试图。
- undo 日志记录事务 ID 和旧版本数据，用于回退。
- 读视图用于支持事务的快照读。
快照读：读取的是历史数据，不加锁。
当前读：读取的是最新数据，加锁。