在连接器上进行权限检查。首先在连接器如果没有权限,直接返回错误信息;如果有权限,在 MySQL8.0 版本以前,会先查询缓存,以这条 sql 语句为 key 在内存中查询是否有结果,如果有直接缓存,如果没有,执行下一步
在分析器上进行词法分析和语法分析。词法分析会提取 sql 语句的关键元素 select,提取需要查询的表名为 t_employee,需要查询所有的列,查询条件 age = 27 和 name = ‘李四’;然后语法分析会判断这个 sql 语句是否有语法错误,比如关键词是否正确等等,如果检查没问题就执行下一步
优化器进行确定执行方案。优化器会根据自己的优化算法进行选择执行效率最好的一个方案(优化器认为,有时候不一定最好)。那么确认了执行计划后就准备开始执行了
执行器执行优化器选择的执行方案,执行前会校验该用户有没有权限,如果没有权限,就会返回错误信息,如果有权限,就会去调用引擎的接口,返回接口执行的结果
第一范式(1NF):确保每列保持原子性即列不可分,即数据库表中的字段都是单一属性的。这个单一属性由基本类型构成,包括整型、实数、字符型、逻辑型、日期型等。
第二范式(2NF):属性完全依赖于主键,也就是说在一个数据库表中,一个表中只能保存一种数据,不可以把多种数据保存在同一张数据库表中。
第三范式(3NF):在第二范式的基础上,属性和主键不能间接相关(减少数据冗余,这样就可以通过主外键进行表之间连接)。比如我们表比较多,需要关联时,但我们的A表只需要关联B表的一个字段,而且每次都需要关联查询你,这时我们可以采用A表放置一个冗余字段来存B表的那个字段。这个操作其实就是一个反范式的。
varchar与char的区别
变长和固定长度
varchar(50)中50的涵义
字符最大长度50,所代表的字节数与字符集有关,比如是utf8占3个字节,那么varchar(50)字段在表中最大取到150个字节。(varchar(50)和(200)存储hello所占空间一样,但后者在排序时会消耗更多内存,因为order by col采用fixed_length计算col长度(memory引擎也一样) )。
int(20)中20的涵义
是指显示字符的长度不影响内部存储,只是影响带 zerofill 定义的 int 时,前面补多少个 0,易于报表展示
varchar(20)和int(20)中的20含义一样吗?
int(M) M表示的不是数据的最大长度,只是数据宽度,并不影响存储多少位长度的数据;varchar(M) M表示的是varchar类型数据在数据库中存储的最大长度,超过则不存
CHAR_LENGTH是字符数,而LENGTH是字节数。Latin字符的这两个数据是相同的,但是对于Unicode和其他编码,它们是不同的。
BLOB是一个二进制对象,可以容纳可变数量的数据。有四种类型:TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB,它们只是在所能容纳价值的最大长度上有所不同。
TEXT是一个不区分大小写的BLOB。有四种类型:TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。它们对应于四种BLOB类型,并具有相同的最大长度和存储要求。
BLOB和TEXT类型之间的唯一区别在于对BLOB值进行排序和比较时区分大小写,对TEXT值不区分大小写
explain出来的各种item的意义:
id:每个被独立执行的操作的标志,表示对象被操作的顺序。一般来说, id 值大,先被执行;如果 id 值相同,则顺序从上到下。
select_type:查询中每个 select 子句的类型。
table:名字,被操作的对象名称,通常的表名(或者别名),但是也有其他格式。
partitions:匹配的分区信息。
type:join 类型。
possible_keys:列出可能会用到的索引。
key:实际用到的索引。
key_len:用到的索引键的平均长度,单位为字节。
ref:表示本行被操作的对象的参照对象,可能是一个常量用 const 表示,也可能是其他表的
key:指向的对象,比如说驱动表的连接列。
rows:估计每次需要扫描的行数,数值越大越不好,说明没有用好索引
filtered:rows*filtered/100 表示该步骤最后得到的行数(估计值)。
extra:重要的补充信息。
explain 中的索引问题:
Explain 结果中,一般来说,要看到尽量用 index(type 为 const、 ref 等, key 列有值),避免使用全表扫描(type 显式为 ALL)。比如说有 where 条件且选择性不错的列,需要建立索引
被驱动表的连接列,也需要建立索引。被驱动表的连接列也可能会跟 where 条件列一起建立联合索引。当有排序或者 group by 的需求时,也可以考虑建立索引来达到直接排序和汇总的需求
数据库调优可以从两个方面考虑:一是应用层架构;二是数据库架构
应用层架构优化。核心是减少对数据库的调用次数,本质上是从业务应用层来审视流程是否合理,常用的方案有:
引入缓存,虚拟一层中间层,减少对数据库的读写
将多次单个调用改为批量调用,比如说循环十次主键 select * FROM t where id = 'xx’改为使用 IN 一性次读取 select * FROM t where id IN (‘xx’,‘xx’,…)
使用搜索引擎
数据库架构优化。核心是优化各种配置,提升数据库的性能,可分为:
(1)优化 SQL 及索引,以达到减少数据访问、返回更少数据、减少交互次数等目标。常用的手段包括:创建并正确地使用索引(比如说减少回表查询)、优化执行计划、数据分页处理、只取需要的字段、慢查询优化、使用搜索引擎等
(2)优化数据库表结构。常用的的手段包括:使用占用空间最小的数据类型、使用简单的数据类型、尽可能地使用 NOT NULL 定义字段、尽量少使用 text 字段、分库分表等
(3)优化系统配置。包括操作系统的配置优化和数据库的配置优化
A、操作系统优化。数据库是基于操作系统(多为 Linux 系统)的,所以对于合理使用操作系统也会影响到数据库的性能。比如将数据库服务器应和业务服务器隔离、或者设置 net.ipv4.tcp_max_syn_backlog = 65535 以增加 tcp 支持的队列数等等
B、数据库配置文件优化,以 MySQL 配置为例,可以修改 innodb_buffer_pool_size(设置数据和索引缓存的缓冲池)、max_connections 等参数
(4)优化硬件配置。比如说使用更好的服务器、更快的硬盘、更大的内存等等
一致性(Consistency): 数据一致更新,所有数据变动都是同步的
可用性(Availability): 好的响应性能,有限的时间内返回结果
分区容错性(Partition tolerance): 可靠性,在网络故障、某些节点不能通信的时候系统仍能继续工作
(以上三者无法兼顾)
XA 二阶段提交方案(2PC)
顾名思义,2PC 分为两个阶段:第一阶段是表决阶段,所有参与者都将本事务能否成功的信息反馈发给协调者;第二阶段是执行阶段,协调者根据所有参与者的反馈,通知所有参与者在所有分支上提交或者回滚。
缺点很明显:A、性能问题,锁会跨越两个阶段,期间其他节点处于阻塞状态;B、协调者单点故障;C、丢失消息导致的不一致问题。如果发生局部网络问题,一部分事务参与者收到了提交消息,另一部分事务参与者没收到提交消息。
TCC 方案
TCC 其实就是采用的补偿机制,其核心思想是:针对每个操作,都要注册一个与其对应的确认和补偿(撤销)操作。分为三个阶段: Try、Confirm、Cancel:
Try 阶段:这个阶段说的是对各个服务的资源做检测以及对资源进行锁定或者预留;
Confirm 阶段:主要是对业务系统做确认提交,Try 阶段执行成功并开始执行 Confirm 阶段时,默认 Confirm 阶段是不会出错的。即:只要 Try 成功,Confirm 一定成功。
Cancel 阶段:主要是在 try 阶段不全部成功的情况下,进行取消或者补偿,回滚已经执行成功的业务逻辑。
这种方案能保证强一致性,但是对业务侵入太大,而且实现难度较大,特别是回滚时需要按照网络状态、系统故障等不同的失败原因实现不同的回滚策略。为了满足一致性的要求,confirm 和 cancel 接口必须实现幂等。
基于消息的最终一致性方案
A、在系统 A 处理任务 A 前,首先向消息中间件发送一条消息;
B、消息中间件收到后将该条消息持久化,但并不投递。此时下游系统 B 仍然不知道该条消息的存在;
C、消息中间件持久化成功后,便向系统 A 返回一个确认应答;
D、系统 A 收到确认应答后,则可以开始处理任务 A;
E、任务 A 处理完成后,向消息中间件发送 Commit 请求。该请求发送完成后,对系统 A 而言,该事务的处理过程就结束了,此时它可以处理别的任务了;
F、但 commit 消息可能会在传输途中丢失,此时消息中间件可以通过事务回查机制来查询消息状态,使得最终一致;
G、消息中间件收到 Commit 指令后,便向系统 B 投递该消息,从而触发任务 B 的执行;
H、当任务 B 执行完成后,系统 B 向消息中间件返回一个确认应答,告诉消息中间件该消息已经成功消费,此时,这个分布式事务完成。如果系统 B 未应答,那么消息中间件的确认服务会去查询系统 B 的处理结果,如果未成功便会重发,当时也有可能存在误发的情况,所以系统 B 需要保证幂等。
这种方案需要消息中间件支持事务消息(如阿里的 RocketMq),而且对业务代码的侵入性很高。
本地消息表
本地消息表与业务数据表处于同一个数据库中,这样就能利用本地事务来保证在对这两个表的操作满足事务特性,并且使用了消息队列来保证最终一致性。 在分布式事务操作的一方完成写业务数据的操作之后向本地消息表发送一个消息,本地事务能保证这个消息一定会被写入本地消息表中;之后将本地消息表中的消息转发到 Kafka 等消息队列中,如果转发成功则将消息从本地消息表中删除,否则继续重新转发。 在分布式事务操作的另一方从消息队列中读取一个消息,并执行消息中的操作。
这种方式本质是将分布式事务拆成了两个本地事务,但同时业务会和消息强耦合,对业务的侵入性也很高
(基础不够可先不看)