MySQL

Jing1042025/9/4大约 13 分钟

MySQL

部分基础SQL知识

关系型数据库与非关系型数据库

关系型数据库：MySQL、SQL Server等，是一种结构性数据库，数据以二维表的形式存在，每一列代表数据的一个属性，每一行代表一个数据实体。要求ACID（原子性、一致性、），扩展性较差
非关系型数据库：MongoDB、Redis等，不同于二位存储，比如MongoDB为json文档，可以只存储存在的属性，redis具有多种数据结构。要求BASE，扩展性比较强

范式

第一范式：每一列数据都是不可分割的原子项
第二范式：第一范式的基础上，非主属性要依赖于整个主键，而不是主键的部分属性
第三范式：第二范式的基础上，任何非主属性都直接依赖于主键，不能间接相关。

连表查询-Join

内连接 INNER JOIN：返回匹配的数据
左连接 LEFT JOIN：返回左边表所以行，右表如果没有匹配的就返回空
右连接 RIGHT JOIN：返回右边表所有行，左表如果没有匹配的就返回空
全连接：MySQL不支持，可以使用UNION将左连接和右连接合并

如何保证插入数据唯一性

UNIQUE：保证属性唯一，如果插入重复的，MySQL将报错
INSERT...ON DUPLICATE KEY UPDATE：如果插入的数据存在键的冲突，那么更新数据
INSERT IGNORE：如果插入的数据存在键的冲突，那么忽视该SQL

CHAR和VARCHAR的区别

CHAR存储的是固定长度字符集，如果不足字符长度则会补空格
VARCHAR存储的是可变长度字符集，定义的字符长度是最大长度

要注意定义时输入的长度都是字符长度而不是字节长度，比如VARCHAR(10)，后面这个10是字符长度，根据编码不同，一个字符占用的字节数可能不同

一条SQL语句执行过程是怎样的

连接器：连接器进行建立连接、管理连接、校验用户身份；查询缓存：如果SQL语句命中缓存，直接返回结果集；解析SQL：将SQL语句进行词法分析、语法分析等；执行SQL：预处理阶段将*转换为各个列，优化阶段寻找最小执行计划，执行阶段根据执行计划查询数据返回给客户端

每个database的文件结构

以InnoDB引擎为例，一个database会有字符集以及字符校验规则文件、每个表的元数据文件、以及每个表的idb也就是索引和数据文件

存储引擎

MySQL里面常用的存储引擎有InnoDB、MyISAM、Memory等，数据存储引擎是可以自己扩展的，其本质就是数据的存储方式和读取数据的实现逻辑，比如MyISAM使用MYD和MYI两个后缀名文件分别存储数据和索引，但是InnoDB则是使用ibd文件直接存储索引和数据。Memory字如其名就是存在内存里面，一重启就消失。

InnoDB支持事务，MyISAM不支持事务；InnoDB支持行级锁，锁粒度更小，但是MyISAM只支持表级锁；InnoDB支持外键，MyISAM不支持；InnoDB使用ibd文件存储索引及数据，使用聚簇索引（即主键索引中，数据是放在叶子节点上的，辅助索引（也就是不用主键的索引）叶子节点放的是主键，还需要二次索引一下），因此主键不能过长，MyISAM使用MYI和MYD分别存储索引和数据文件，使用的是非聚簇索引，主键索引和辅助索引最终都指向数据地址，查询速度一样；InnoDB的COUNT方法要全表扫描，计算数量较慢，但是MyISAM有一个变量存储总数值，直接访问比较快；InnoDB具有redolog，崩溃后能够根据日志恢复，MyISAM则不行。

索引

索引分类

数据结构：B+树索引（InnoDB、MyISAM、Memory）、Hash索引（Memory）、Full-text索引（InnoDB、MyISAM）
物理存储：聚簇索引、辅助索引
字段特性：主键索引、普通索引、唯一索引、前缀索引
字段个数：单列索引、联合索引

InnoDB的索引

InnoDB引擎创建表时，如果有主键则使用该主键作为索引key，如果没有主键则选择第一个不含NULL的唯一列作为key；如果都没有则会生成一个自增id作为key
其余的索引都会是二级索引
InnoDB中主键索引和二级索引使用的都是B+树结构
这里要明晰一下索引覆盖和回表，假设我们在user表的name和age字段创建了联合索引，然后我们使用该索引符合最左规则去查询name这一列，因为这一name数据包含在该索引中（叶子节点包含的是name，age，主键），也就不用在去主键索引上查，这就叫索引覆盖。但是如果我们查的是name，age，sex三个字段，那就要通过主键再去主键索引上查，这就叫回表。

联合索引

也就是在多个列上创建的索引，这里涉及最左匹配原则，比如还是name和age的例子，走索引时是先按照name去查，name相同的再按照age去查，如果只查age就不会走索引。
会出现部分字段用到索引部分字段不用索引的情况，也就是范围查询（between），联合索引的匹配顺序是从左到右的。碰到范围查询，索引匹配就会“截断”，后面的字段无法利用索引。截断效应的原因：B+Tree 是有序的，范围查询已经把某一段区间选出来了，后面的字段无法保证连续有序，因此无法再用索引快速定位。SELECT * FROM user WHERE name='Tom' AND age > 20 AND salary=5000;
匹配过程：
name='Tom'：精确匹配
可以用到索引的第一列 name。
age > 20：范围匹配（大于某个值）
还能用到索引的第二列 age。
salary=5000：在 age 之后，但因为 age 已经是范围查询，
salary 无法再用索引。
所以，最终这个查询用到的索引是 (name, age)，但 salary 就失效了。
建立联合索引的时候，区分度大的放前面（左边），比如id，区分度小的放后面比如性别

聚簇索引变化，存储会发生变化吗

如果不是索引数据，就不会变化，如果更改的是索引数据因为是B+树要保持有序，就要更改存储

什么适合做主键

具有唯一性并且尽量是自增的
尽量不要用业务数据，因为可能会业务变化
通常自增id没问题，但是如果是分布式，那么多个数据库的id可能出现冲突

复习自增id、uid和uuid

自增id比较简单不过多赘述，uid保证大体有序（可依据时间戳和机器码），通过某种算法（雪花算法等）生成，一般占8字节，适合分布式，uuid占16字节，字符串类型，并且无序，索引插入时可能会导致B+树分裂

性别为什么不适合做索引

假设100万条数据，50万男50万女，那么这个时候查询男性的全列数据，走索引的话就会回表50万次，还不如全表扫描呢

id还是uuid？

uuid不一定是有序的，插入进去的时候会导致页分裂并且它存储空间也比较大

B+树

InnoDB存储引擎的索引数据结构是B+树，该结构非叶子节点存储的都是索引key和指针，这样分层数就变小，IO减少；叶子节点存放数据项（主键索引）或索引key和主键（二级索引），并且使用双向链表连接便于范围查找。要注意一个叶子节点相当于存储中的一页（16KB），里面可以存储多个数据项，这里面的数据使用的是单向链表连接。
相较于B树，B+树层数更小，且叶子节点有双向链表便于范围查找
相较于Hash，B+树能够范围查找
相较于二叉树，B+树io层数更少
相较于跳表，io次数少

六种索引失效的情况

WHERE中有OR，并且一个为索引一个不为索引
左模糊匹配或者左右模糊匹配 like""%aaa",like""%aaa%"
对索引列使用了表达式运算符
对索引列使用了函数
联合索引不符合最左匹配原则
隐式类型转换（本质是使用了CAST函数）

索引的优缺点

优点就是加快索引速率，缺点是会占用物理内存，创建和维护也有一定开销

前缀索引

一个字符串，大小是200字符，给这个建立索引的话占用空间大，每一节点存储的个数少，但是如果该列前几个字符就能区别，那么我们直接在前几个字符上建立索引即可

优化索引

前缀索引优化
覆盖索引优化
主键最好是自增的
避免索引失效

事务

ACID

A原子性：要么全部发生，要么全都不发生，通过undolog回滚日志实现
C一致性：A有600，B有800，合计1400；A给B转两百，分两步A扣200，B加200，最后应该为A400，B1000，合计仍未1400
I隔离性：分别由独立的空间，不会相互干扰，通过MVCC多版本并发控制实现
D持久性：事务处理结束后是永久的，不会因故障而消失，通过redolog重做日志实现

并发相关问题

脏读：A事务将某个数据从400改为了600，B读取到了六百并使用该值进行了i相关操作，但是A事务触发了回滚，数据又变回了400，出现脏读问题。会出现在读未提交这一隔离级别。
不可重复读：B事务读到了某个数据为400，A事务将该数据修改为600并且提交，B事务再次读该数据，发现数据变成了600，出现不可重复读。发生在读提交隔离级别
幻读：在可重复读级别下，AB两个事务开启时都创建了一个快照，B事务只会读到这个快照内容，但是此时A事务创建了一条新数据项，B事务即使看不到但是也使用SQL语句调用了该项，就会显示成功，出现幻读。

MySQL是如何避免并发问题的

锁机制：具有数据库锁、表级锁、行级锁等多种锁机制
事务隔离级别：具有多种事务隔离级别
MVCC：多版本并发控制，根据事务选择合适版本的数据

事务隔离级别

读未提交：事务没有提交已经改变的数据就能被观测到
读提交：事务之后提交修改后的数据，该数据才能被观测到（也是使用了mvcc快照，但是是在sql语句执行前生成的）
可重复读：使用MVCC实现，每个事务所看到的数据都是事务开启时的快照，是默认的隔离级别
串行化：上读写锁，严格顺序同步

如何在可重复读级别下防止幻读呢

事务开启后使用SELECT....FOR UPDATE加锁，加的是行级锁和间隙锁，区间内其他事务不能做修改和插入数据

MVCC实现原理

数据库为每一行数据都维护了三个隐藏列：trx_id（修改该行的事务id）、roll_pointer（指向旧版本数据的指针，用于undolog）、row_id（没有主键时帮我们维护的自增id）
读提交每次SQL都会生成快照，可重复读会在事务开启时生成快照，快照维护四个属性，快照创建者creator_trx_id，当前活跃的最小事务min_trx_id，当前活跃的事务id列表m_ids，最大事务max_trx_id（还没创建的）
读取数据时会根据快照和数据隐藏列去查找读哪个版本，会进行四个判断，如果数据的trx_id就是快照的creator_trx_id说明该数据就是当前事务更改的，能够读到，如果不是则判断trx_id是否小于快照的min_trx_id，如果小于，说明当前数据是之前某个提交事务修改的，也可以查看，如果不是该范围则进行第三个判断，如果trx_id大于等于快照的max_trx_id，则说明是该事务之后的事务提交的，则不能读到该数据，如果也不在这个范围里就得判断该trx_id是否在m_ids列表里了（这个列表里的事务不等于min-max范围的所有事务，因为有的也已经提交了），如果不在这个列表里，说明trx_id的事务已经提交，可以读到，如果还在说明还没有提交，就得根据roll_pointer找到上一版本的旧数据进行重新判断

update是不是原子性的

是的，通过锁+undolog实现，修改时给数据行加锁，并创建undolog，如果回滚根据log回退

事务中太多sql会导致什么弊端

锁住太多数据
产生太多undolog，回滚时间长
主从延迟

锁

锁的分类

全局锁：会让数据库进入只能读不能写的状态，一般用于数据库备份
表级锁：分为三种，表锁、元数据锁、意向锁。
行级锁：颗粒度最小