MySQL 常问知识点(简略)

前言#

稍微整理一下吧, 因为平时关注这方面不多, 所以不会很深入

事务隔离级别#

事务中可能遇到的问题#

脏读

同一时间有两个事务 A 和 B, A 对某条数据进行修改或增加, 在 B 中也体现出来, 但是 A 可能回滚, 导致 B 获取的数据可能是脏的

可重复读

有事务 A, 在 A 开始后, 数据库中的某些数据发送变更, 但是在 A 中读取相关数据永远保持在 A 刚启动的时候不会自己更改, 通常针对数据的更新

不可重复读

有事务 A, 在 A 开始后, 数据库中的某些数据发送变更, 而 A 事务中查询到的相关数据也会随之更改, 通常针对数据的更新

幻读

事务A对某些行的内容作了更改，但是还未提交，此时事务B插入了与事务A更改前的记录相同的记录行，并且在事务A提交之前先提交了，而这时，在事务A中查询，会发现好像刚刚的更改对于某些数据未起作用，但其实是事务B刚插入进来的，让用户感觉很魔幻，感觉出现了幻觉，这就叫幻读。

事务隔离级别#

读未提交

不加锁, 速度快, 但是不加锁会导致 脏读/不可重复读/幻读 的问题出现

读提交

一个事务只能督导其他事务已经提交过的数据

可能出现 不可重复读/幻读 的问题

可重复读(默认)

可重复读是 MySQL 的默认事务隔离级别

在事务启动之后, 对数据的所有改动都不会体现到事务中, 因此对数据的新增也不会出现在其中

可重复读可能出现 幻读 的问题

串行化

是最高的隔离级别, 会在事务过程中涉及到的数据都加上行锁, 多事务串行执行, 效率很堵但是安全, 同时不存在数据的不一致问题

InnoDB 和 Myisam 的区别#

• InnoDB 支持事务, Myisam 不支持
• InnoDB 支持行级锁, Myisam 只支持表级
• InnoDB 支持外键, Myisam 不支持
• InnoDB 是索引组织表, Myisam 是堆表

默认为 InnoDB

慢查询#

时间#

10s, 但是使用 GORM 这种当大于200ms 就会有调试日志输出

查找问题#

• Explain: 可以输出 MySQL 内部是如何使用索引来处理查询语句的, 可以作为找慢查询的原因时使用
• 视图: 创建视图来查看

解决方法#

• 数据量过大考虑分表
• 查看是否查询了额外的数据, 对语句进行分析和重写
• 查看是否命中了索引, 优化表结构
• 如果每次都是重复的查询数据, 考虑加入缓存
• 不需要查询出的列数据, 进行 sql 优化
• 查询不需要的数据, 使用LIMIT进行跳过

drop/delete/truncate 的区别#

drop#

直接删除表

truncate#

清空表数据, 当再次新建时id 从1开始

delete#

删除表里的数据, 可以加where来进行筛选

连接种类#

• 全连接: join/inner join
• 左连接: left join
• 右连接: right join
• 交叉连接: cross join

三范式#

1NF#

作为关系型数据库, 必须要有主键, 每一个字段必须保持原子性, 不能再继续分割

2NF#

作为关系型数据库, 所有非主键字段完全的依赖他的主键

3NF#

所有的非主键字段和他的主键字段之间不能产生传递依赖, 必须是直接依赖关系, 传递依赖可以再启用一张表来做关联

数据库优化方法#

创建高性能索引

根据业务来设计符合要求的索引

优化查询性能

设计表时要充分考虑表结构和字段

查询时尽量命中索引

优化服务器设置

适当的修改数据库配置

负载均衡

存储过程和触发器#

存储过程

将重复度高的 sql 语句预先存储到数据库中, 调用时通过填充数据来执行 sql, 以提高效率

触发器

触发器顾名思义, 就是在数据库中设置某个条件时自动触发某些 sql, 因为会导致效率变慢, 尤其是多条命中时, 所以一般不使用

锁与死锁#

本数据库有三种锁(InnoDB)

表级锁

表级锁的使用很少, 因为他会将整个表锁住, 效率低, 同时因为将整个表锁住, 同一时间也就不会有第二个事务进行修改操作, 避免出现死锁问题

行级锁

数据库本身并不支持行级锁, InnoDB 是通过引擎层自己实现的, 行级锁的效率更高, 但是可能会出现死锁的情况, 在 InnoDB 中, 行锁在需要对某条数据进行修改时加上, 在事务结束时才会释放掉, 而如果有两个事务以两个顺序对多个数据进行加锁, 就会导致死锁, 例如

事务1	事务2
update t set k=k+1 where id = 1 (加锁行1)
	update t set k=k+1 where id = 2 (加锁行2)
update t set k=k+1 where id = 2 (等待行锁2释放)
	update t set k=k+1 where id = 1 (等待行锁1释放)

我们在对数据库操作时, 应该尽可能的考虑到锁冲突问题, 遇到事务必须慎重, 如果有可能加行锁的操作, 可以考虑以下几种方式规避

• 尽量的将加行锁操作放到事务后面
• 对于事务中对多个表进行处理时, 约定好使用一致的处理顺序
• 以批方式处理多个数据时, 先对数据进行排序
• 可以控制事务的并发数量
• 将锁提升为排他锁, 使用 for update 语句
• 将锁提升为表锁, 例如在更新时更新条件没有索引会自动加表锁

页面锁

存储引擎为 BerkeleyDB 才会出现, 没有使用过

索引#

索引并不是越多越好

索引优点: 加快查询速度(命中索引的前提下)

索引缺点: 索引需要占据一定的空间和资源, 同时索引会加重插入/删除/修改数据的负担

索引可以增加数据的查找速度, 索引是在引擎层进行实现的, 本数据库提供四种索引:

• B-Tree: 最常见的索引类型, 大部分引擎都支持
• HASH: Memory 支持, 使用场景简单
• R-Tree: 空间索引, MyISAM 独有, 常见用于地理空间数据类型
• Full-text: 全文索引, MyISAM 独有

对于引擎对索引类型的支持如下

索引	MyISAM	InnoDB	Memory
B-Tree	Y	Y	Y
HASH	N	N	Y
R-Tree	Y	N	N
Full-Text	N	Y(5.6+)	N

索引类型#

普通索引

最基本的索引类型, 没有唯一性之类的限制

UNIQUE(唯一索引)

唯一的, 不允许重复的索引

复合索引

多个列当做一个索引, 用于组合搜索, 效率比索引合并快

主键(特殊)

唯一的, 每个表只能有一列主键

单列索引和联合索引#

单列索引

指的就是为单独的某个列设置索引

联合索引

将多个列合到一起作为一个索引, 创建联合索引等于创建了从左到右的多个索引. 例如: 创建联合索引a+b+c 等于创建了三个索引, 分别是 单列索引 a, 联合索引a+b, 联合索引 a+b+c

索引合并#

当查询语句的WHERE是AND的时候, 如果有多个索引, 会自动查找一个最佳的索引使用

而当查询语句通过OR连接时, MySQL会使用索引合并技术, 将命中的几个索引的范围扫描合并成一个新的索引(只限于单表), 如果数据量不大, 不会将索引合并, 因为会影响一部分效率

什么时候需要索引#

• 频繁的作为查询条件的字段应该创建索引
• 唯一性很差的字段不适合作为单独索引, 即使他很频繁的作为查询条件(例如用户表中的性别字段这种重复性很高的)
• 更新非常频繁的字段不适合创建索引
• 不会出现在WHERE中的字段不应该创建索引
• 数据量比较大, 比如表中的数据量大于 1w 条
• 索引选择性高(不重复的索引值/表记录数)

选择索引#

• 在WHERE子句中出现的列, 在join子句中出现的列可以设置索引
• 索引列的基数越大, 效果越好
• 如果一个列长度为200, 而前10个字符是多数唯一的, 可以考虑对这个列的前10个字符做短索引, 节省索引空间, 也可以提高查询速度
• 牢记索引的最左前缀原则(详见下条)

为什么 MySQL 索引使用 B+树#

B-tree

B 树不管是叶子结点还是非叶子节点, 都会保存数据, 会导致在非叶子节点中可以保存的指针数量变少了, 指针少的时候要保存大量的数据, 增加树的高度, 导致 IO 操作变多了, 降低查询性能.

Hash

Hash 没有顺序, 查询的 IO 复杂度高

二叉树

二叉树的高度不均匀, 不能自平衡, 查找效率跟数据相关, 数据越大树越高, IO 的代价就很高

红黑树

数据量越大树的高度越高, IO 的代价很高

平衡二叉树

平衡二叉树的深度很大, 因为平衡二叉树一个节点最多两个子节点, 导致同样的数据量, 平衡二叉树的深度比 B+tree 的要深很多, 导致查询会变慢.

最左前缀#

MySQL 一条查询只能使用一个索引, 当出现多个索引时, 会使用范围最小的那个索引

InnoDB使用 B+ 树进行数据存储, 所以要想命中索引, 必须按照联合索引当初建立时的顺序来, 不然无法命中索引, 另外, 范围查询(<, >)也不命中索引

比如说有表 user, 下面有字段 name 是索引

一条语句 SELECT * FROM user WHERE name='ak' AND age=15 分两步

1. 从表中寻找 name 为 ak 的数据
2. 再从结果中寻找 age 为 15的数据

因为name为索引, 所以步骤1很快速完成, 但是步骤2就没有索引, 可以优化

如果这一条语句是很频繁的查询, 可以将name和age合并使用联合索引, 提高这个语句的查询效率

于是我们将索引修改为name 和 age, ALTER TABLE user ADD INDEX name_age (name, age)

因为你在设置联合索引时, 总归有个先后, 例如本例就是name+age, 而MySQL的最左查询会从最左开始解析, 那么过程如下

重新执行语句 SELECT * FROM user WHERE name='ak' AND age=15

这样就会将联合索引生效, 提高效率, 他的执行如下

1. 找到name='ak',命中了name和name+age
2. 找到age=15, 命中了name+age索引

但是如果我们更改WHERE的顺序为

SELECT * FROM user WHERE age=15 AND name='ak' 则无法命中索引, 这是为什么呢?

1. 找到age=15, 发现索引只有name和name+age, 因为没有以 age开头的, 所以本条没有命中索引
2. 找到name='ak' 没有索引

所以就没有命中索引

数据库中存在索引但是没有使用#

IN

IN会扫描全表, 慎重使用

NOT IN

与IN一样会扫描全表, 考虑使用 not exists或者其他方式规避

<>

不等于符号, 考虑修改语句, 比如a<>0修改为a>0 or a<0

IS NULL和IS NOT NULL

判断字段是否为空, 一般不会应用索引, b 数索引不索引空值, 考虑修改语句, 比如a IS NOT NULL修改为a>0

命中的数据大于总数50%

命中索引但是命中的数据数量超过了表内数据总数量的50%, 也会直接全表扫描, 因为此时全表扫描比索引快(大概率是索引的设计有问题)

LIKE

LIKE操作符可以匹配通配符查询, 使用%可以查询几乎任意范围的匹配, 注意, 以%开头则不会引用索引, 比如%TEXT%不会引用索引, 而TEXT%可以引用范围索引

UNION

UNION用来将多个SELECT查询出的结果集进行合并. 因为是将多个结果进行合并, 也不会走索引, 但是因为其运行时是先取出若干个结果, 再去重, 因此可能会占用大量的资源, 不推荐使用

怎么判断是否命中索引#

在 sql 前加EXPLAIN, 比如

EXPLAIN SELECT * FROM user WHERE age=15 AND name='ak'

然后查看输出的字段数据来判断

分库分表#

什么是分库分表#

分库

从单个数据库拆分成多个数据库, 将数据分散在多个数据库中

分表

从单张表拆分成多张表, 将数据散落在多张表中

为什么要分库分表#

提升性能, 增加可用性

分库分表的前提是负载已经大到基本的数据库结构优化和缓存等机制都使用了还是有效率问题

由于分库分表会增加逻辑复杂性, 因此不到万不得已不推荐使用

提升性能

数据量越来越大, 数据库的查询 QPS 就越来越高, 数据库的压力和读写需要的时间也会越来越多, 业务效率的瓶颈就会变成数据库这里, 因此就需要对数据库进行优化

如果数据库的 QPS 过高了, 就需要考虑进行拆库, 通过分库来分担单个数据库的连接压力. 一般的, 单库的连接数最好不要超过1000

如果单表的数据超过了一定量级, 对这个表进行操作, 速度就会变慢(阿里的<<JAVA 开发手册>>提到每张表的数据不要超过500万行或者容量超过2G). 此时需要对表按照某个规则进行切分, 分为多个表, 来减少每个表的数据量, 恢复性能.

提高可用性

鸡蛋不能放在一个篮子里, 单个数据库如果发生了意外, 很有可能会丢失所有数据, 因此, 除了考虑使用 主从 等多节点部署之外, 也可以考虑拆分数据来解决问题, 比如我们的数据库宕机了, 那么:

• 单库部署的情况下, 数据库宕机, 会影响100%的数据, 而且数据恢复的耗时可能也很久
• 如果我们拆分成2个库, 部署在不同的机器, 如果其中一个宕机, 故障影响就是50%, 还有50%的数据可以继续提供服务, 同时恢复耗时受数据量影响也会缩短

需要注意, 拆库并不是无限制的, 这是牺牲了资源来提升性能和可用性, 要取舍, 毕竟资源总是有限的

怎样分库分表#

三种切分方案

从上一节总结, 切分方案分为三类

切分方案	解决的问题
只分库	库读写 QPS 过高, 连接数不足了
只分表	单表数据量过大, 性能瓶颈
分库+分表	连接数不足+数据量过大

选择切分方案

关于系统架构, 都有一个共识就是避免过度设计, 只有确定业务量数据巨量, 或者真的遇到了瓶颈, 再考虑数据切分

分表: 可以根据 500w 行的标准按照逻辑分割进行拆分, 例如, 业务每个月插入的数据为400w 行, 就应该按照每月来进行拆分

分库: 可以按照每库1000个数据库连接按照逻辑进行拆分

如何切分

水平切分: 按照业务维度进行横向的切分, 也就是按行切分, 例如将用户表按照用户等级进行拆分, 将订单表按照创建月份进行拆分等等

垂直切分: 按照字段进行切分, 比如说订单表可能存在订单信息, 卖家买家信息, 支付信息, 可以通过字段拆分成三张表, 订单/买家/卖家 来减少容量

读写分离#

读写分离也是为了提高速度和可用性

主从复制#

搭建在多台服务器上的数据库系统, 将其中一台当做主数据库, 其他为从数据库, 实现主从同步. 其中主数据库负责进行写操作, 从数据库负责读操作, 这就将请求分流为多个部分, 来增加访问速度, 同时其中一台崩溃也避免了数据发生丢失

这里也说了, 读取是从服务器, 写入是主服务器, 那么他们是不同的物理服务器, 怎么实现的数据一致呢?

我们将主服务器命名为 A, 从服务器有一个为 B, 当用户在A插入数据时, 流程如下:

1. 修改A本地的数据
2. 将修改记录写入A的日志系统
3. 发送给B
4. B写入B的日志系统
5. B从B的日志系统中读取记录, 修改自己的数据

主从同步复制有三种方式:

• 同步复制: 用户写入A, A监控到从数据库B也修改完成后才返回成功
• 异步复制(默认): 只要A自己成功就返回成功
• 半同步复制: A自己和若干个从服务器中有一个成功就返回成功

读写分离#

开源方案

读写分离有几个开源方案, 目前还活着的就是 mariadb-corporation/MaxScale：一个智能数据库代理。 (github.com), 虽然是 MariaDB出的, 但是也兼容 MySQL

当配置完成后, 所有数据库连接都设定为 MaxScale 的连接, MaxScale自己进行管理和分配即可, 还有 web 页面可以查看信息, 而且对于调用者来讲是无痛的, 开发者不需要关注里面的实现逻辑, 跟普通调用单机一样

自己控制

当然你也可以使用直连主从, 通过逻辑来进行读写分配, 只是比较 low, 优点是部署方便

什么是 MySQL 的 XA 事务#

在分布式事务处理中, 遇到一个事务跨越了多张表, 就需要使用XA事务来完成整个事务的正确提交和回滚, 保证全局事务的一致性.

需要提前说明的是, 对于分布式数据库架构来讲, 都会有一个总控, 来接收用户的操作, 并通过一定的规则将其分配到具体的某个节点中

XA 事务的过程#

例如, 事务的整体 SQL 如下

begin;
insert into `user` values("user1", 18);
update `user` set `age`=19 WHERE `name` = "user101";
commit;

根据步骤来解答

1. 总控收到begin, 知道要开始事务
2. 总控收到insert语句, 解析语句, 根据name的值, 计算出应该是分配到哪个节点中, 这里假设是节点1
3. 总控向节点1发送语句xa start xid1开启一个xa事务, 这里的xid是总控生成的一个全局事务 id, 同时也将insert 语句发送到节点1
4. 总控收到 update 语句, 同样的, 先根据name计算出数据保存的节点, 这里假设是节点2
5. 此时总控会先发送xa start xid1, 因为都是属于一个事务. 所以这里使用相同的xid, 同时会向节点2发送 原来的 insert 和新的 update 两个语句. (这里为什么将不属于他节点的 insert 发送过去, 之后会说)
6. 总控接受到了commit, 标识这个事务已经结束了, 准备提交
7. 总控向节点1和节点2发送xa end xid1; xa prepare xid1语句, 告诉节点准备提交, 如果数据正常的走完, 那么节点会回复成功, 如果任何一个节点返回失败, 则向节点1和节点2发送xa rollbak xid1 进行事务的回滚
8. 如果都返回成功, 总控会想节点1和节点2发送xa commit xid1 最终提交事务

MySQL5.6上 XA 事务的冲突问题#

试想一下这样的场景

1. 总控已经向节点1和节点2发送完了xa prepare xid1, 并且得到了成功的回复
2. 总控向节点1发送xa commit xid1 , 并且成功了
3. 总控向节点2发送xa commit xid1, 此时因为网络问题, 节点2出现了问题丢失了与总控的通讯
4. 当网络恢复时, 或者节点2上线了, 此时 xa 的事务已经回滚了, 当总控 commit 时, 数据库实例已经找不到xid1这个事务

这里的问题是, xa prepare 没有严格的持久化, 当连接断开时, 这些事务会被回滚, 造成了数据丢失

MySQL5.7 的 XA 可靠性改进#

MySQL5.7解决了这个问题, 在连接断开时这些数据会持久化保存下来

SQL 的四种语言#

DDL#

数据库定义语言

• CREATE(创建)
• ALTER(修改)
• DROP(删除)
• TRUNCATE(清除)
• COMMENT(注释)
• RENAME(重命名)

DML#

数据操纵语言

• SELECT(查询)
• INSERT(新增)
• UPDATE(更新)
• DELETE(删除)
• MERGE(合并)
• CALL(存储过程调用)
• EXPLAIN PLAN(性能分析)
• LOCK TABLE(锁表)

DCL#

数据库控制语言

• GRANT(授权)
• REVOKE(取消授权)

TCL#

事务控制语言

• SAVEPOINT(设置保存点)
• ROLLBACK(回滚)
• SET TRANSACTION(设置实务)

MySQL 建表的约束条件#

• 主键约束: 唯一, 非空
• 唯一约束: 唯一, 可空, 但是只能有一个空
• 检查约束: 列数据的 范围/格式 进行限制
• 默认约束: 列数据的默认值
• 外键约束: 两表间的关系连接

MySQL 执行查询的过程#

1. 客户端通过 TCP 连接发送查询请求到 MySQL 的连接器, 连接器进行权限验证和资源分配
2. 查询是否存在缓存, 如果命中了缓存, 直接返回结果
3. 分析和校验语法是否正确
4. 优化语句, 处理是否使用索引, 生成执行计划
5. 将执行计划提交给执行器, 将数据保存到结果集中, 同时将数据缓存在缓存中, 将结果返回给客户端

varchar和char区别#

效率上char>varchar

如果确定某个字段的值长度, 可以使用char, 否则使用varchar, 例如md5这种明确知道长度的就是char更好

char

定长字段, 在数据库设定时就确定了值的大小,

varchar

不定长字段, 申请了最大长度, 而实际上占用的长度可能比最大长度小, 视值的真正长度而定, 实际占用的长度为(值长度+1, 最后1个字符表示本值长度)

存储过程的优缺点#

存储过程经过预编译成了代码块, 执行效率比较高, 调用也比较方便

但是因为国内互联网的环境一般是寻求很快的迭代, 项目的生命周期往往较短, 人员的流动也比较大, 对于存储过程的管理繁琐且复杂, 复用性也没用把代码写在服务层那么好, 所以阿里的<<Java 开发手册>>也禁止使用存储过程.

MySQL 的事务日志#

Innodb 的事务日志包含两部分, redo log和undo log

redo log

redo log是为了事务的持久化而出现的 log, 当事务执行过程中, 将执行的任务写入redo中, 当有故障发生时, MySQL 重启后, 根据redo中的数据进行重做, 防止事务出现问题

undo log

undo是为了将记录回滚到某个版本, 事务在未提交之前, 保存了未提交之前的版本数据, 方便事务回滚时对数据进行回复

MySQL 的 binlog#

binlog存储了所有数据库的结构变更和表内的数据变更的二进制日志. 记住是变更, 对数据的查询并不会记录在其中. 数据库的所有记录可以查看数据库的通用日志

事务中可以混合使用存储引擎吗#

最好不要, 可以正常提交, 但是因为事务是由引擎实现的, 在回滚时可能因为数据不一致问题无法回滚

数据量很大的表如何优化查询速度#

• 考虑分表
• 优化索引
• 使用redis进行数据缓存
• 使用 MySQL 缓存
• 主从复制, 读写分离
• 从业务上限制查询的范围
• 从业务上规定查询必须指定条件

数据量很大的表针对很大的分页处理#

例如, 有表user, 每页10条数据, 用户查询到了第100000页, sql 语句类似于

SELECT * FROM `user` WHERE age>18 LIMIT 1000000, 10

因为 MySQL 分页查询不是跳过前1000000条数据, 而是读取到1000010 条数据, 所以导致这个查询非常的慢

可以将 sql 修改为

SELECT * FROM `user` WHERE id in (SELECT id FROM `user` WHERE age>18 LIMIT 1000000, 10)

这样虽然也查询到了1000010条数据, 但是因为索引覆盖了查询, 导致速度提升

或者说在业务中对超大的分页进行处理, 比如推测用户的需求是可能会点击下一页, 将下一页数据提前查询并存储到缓存中等

关联查询的优化#

确定ON和USING子语句中是否含有索引

确保GROUP BY 和 ORDER BY 只有一个表中的列, 不然不会使用索引

数据库结构优化#

• 如果表中有很多列, 考虑将表切分
• 对于经常联合查询的表, 考虑使用中间表来连接提高查询效率(将原来的联合查询修改为对中间表的查询)
• 合理的加入冗余字段(慎重)

回表#

回表查询指的是, 需要扫描两次索引树, 效率比扫描一次索引树更低

如果查询条件为普通索引(辅助索引), 则需要先查找一遍辅助索引树, 得到对应的簇集索引键(主键), 再去簇集索引树中查找对应记录, 发生回表

普通索引指的是非主键索引