王也高清头像,昆明网站快速优化排名,管理系统英文,上海发布官网最新版本文为笔者学习林晓斌老师《MySQL 实战 45 讲》课程的学习笔记#xff0c;并进行了一定的知识扩充。 sql 查询语句的执行流程 大体来说#xff0c;MySQL 可以分为 Server 层和存储引擎层两部分。
Server 层包括连接器、查询缓存、分析器、优化器和执行器。
连接器负责接收客… 本文为笔者学习林晓斌老师《MySQL 实战 45 讲》课程的学习笔记并进行了一定的知识扩充。 sql 查询语句的执行流程 大体来说MySQL 可以分为 Server 层和存储引擎层两部分。
Server 层包括连接器、查询缓存、分析器、优化器和执行器。
连接器负责接收客户端的请求并对权限进行验证对连接进行管理。确认有权限后进行数据查询首先会查询缓存缓存中存的是 sql 语句与结果集的映射关系如果缓存命中则直接返回数据。如果缓存未命中则开始真正执行 sql 语句这就需要分析器对传过来的 sql 进行语法分析之后再经由优化器进行优化生成最终的执行计划。最后由执行器去调用操作引擎返回结果集。
另外需要注意的是由于对于经常需要更新的非静态表缓存命中率是非常低的。因此MySQL 8.0 版本直接将查询缓存的整块功能删掉了。
日志系统sql 更新语句是怎么执行的
查询语句的那一套流程更新语句也是同样会走一遍。不过在此基础上还涉及到两个日志模块即 redo log重做日志和 binlog归档日志。redo log 用于数据库突然崩溃时的恢复crash-safe 能力binlog 用于恢复误操作时间节点前的数据或者数据库节点扩容时保证主从数据库数据一致性。
这两种日志有以下三点不同。
redo log 是 InnoDB 引擎特有的binlog 是 MySQL 的 Server 层实现的所有引擎都可以使用。redo log 是物理日志记录的是“在某个数据页上做了什么修改”binlog 是逻辑日志记录的是这个语句的原始逻辑比如“给 ID2 这一行的 c 字段加 1 ”。redo log 是循环写的空间固定会用完binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个并不会覆盖以前的日志。
mysql update Table set cc1 where ID2;上面更新语句的执行逻辑
执行器先找到引擎取出 ID 2 这一行。执行器拿到引擎给的行数据并 1得到新的行数据随后再调用引擎接口写入这行新数据。引擎将这个更新操作记录到 redo log 里此时 redo log 处于 prepare 状态。然后告知执行器执行完成了随时可以提交事务。执行器生成这个操作的 binlog并把 binlog 写入磁盘。执行器再调用引擎的提交事务接口引擎把刚给写入的 redo log 改成 commit 状态完成更新。
下面给出这个 update 语句更加具体的执行流程图图中浅色框表示是在 InnoDB 内部执行的深色框表示是在执行器中执行的。 从上面的描述我们可以发现redo log 的写入拆成了两个步骤prepare 和 commit其实这就是我们常说的”两阶段提交”。它保证了数据的一致性。
误操作后恢复数据以及搭建一些备库来增加系统的读能力的时候。现在常见的做法是用全量备份加上应用 binlog 来实现。如果不采用“两阶段提交”就可能导致数据恢复后和历史数据不同或者搭建备库的时候出现主从数据库数据不一致的问题。
事务隔离级别
事务的实现是基于存储引擎的MySQL 的 Innodb 存储引擎支持四种隔离级别读未提交、读已提交、可重复读、串行化。为的是解决多个事务产生的问题脏读、不可重复读、幻读。 不同隔离级别下事务 A 读到的值不同
V1V2V3读未提交222读已提交122可重复读112串行化112
解释
若隔离级别是“读未提交” 则 V1 的值就是 2。这时候事务 B 虽然还没有提交但是结果已经被 A 看到了。因此V2、V3 也都是 2。若隔离级别是“读提交”则 V1 是 1V2 的值是 2。事务 B 的更新在提交后才能被 A 看到。所以 V3 的值也是 2。若隔离级别是“可重复读”则 V1、V2 是 1V3 是 2。之所以 V2 还是 1遵循的就是这个要求事务在执行期间看到的数据前后必须是一致的。且在“可重复读”隔离级别下只会读到已经提交的数据。若隔离级别是“串行化”则在事务 B 执行“将 1 改成 2”的时候会被锁住。直到事务 A 提交后事务 B 才可以继续执行。所以从 A 的角度看 V1、V2 值是 1V3 的值是 2。
数据库索引
MySQL 数据库的索引是在存储引擎中实现的不同的存储引擎支持的索引类型不同且即使是同一种索引类型其实现方式也可能不同。
索引的常见实现方式有 3 种即哈希、有序数组、搜索树。
哈希适合精确查询、数据插入速度快因为写入数据时只需要追加、范围查询慢因为不是有序的
有序数组精确查询和范围查询速度都快但是写入速度慢。因为在数组中写入一个中间值时需要把大于它的值都后移。
搜索树搜索效率很高但数据库的索引不仅仅存在内存中还存在磁盘中。如果采用二叉树树的层级会很深而层级深意味着与磁盘间的 IO 操作数量更多而 IO 操作是十分耗时的。为此大多数数据库用的都是 N 叉树。
InnoDB 索引模型
在 InnoDB 中表中的数据都是根据主键顺序以索引的形式存放的即使不指定主键也生成一个默认的主键这种存储方式的表称为索引组织表。又因为前面我们提到的InnoDB 使用了 B 树索引模型所以数据都是存储在 B 树中的。
根据叶子节点的内容索引类型分为主键索引和非主键索引。
主键索引的叶子节点存的是整行数据。在 InnoDB 里主键索引也被称为聚簇索引clustered index。非主键索引的叶子节点内容是主键的值。在 InnoDB 里非主键索引也被称为二级索引secondary index。
因此为了找到数据
如果语句是 select * from T where ID500即主键查询方式则只需要搜索 ID 这棵 B 树如果语句是 select * from T where k5即普通索引查询方式则需要先搜索 k 索引树得到 ID 的值为 500再到 ID 索引树搜索一次。这个过程称为回表。
也就是说基于非主键索引的查询需要多扫描一棵索引树。因此我们在应用中应该尽量使用主键查询。
索引维护
B 树为了维护索引有序性在插入新值的时候需要做必要的维护。如果插入的新数据对应的主键 id 比原来的大那只需要追加就行。但如果比原来的小就需要挪动后面的数据再进行插入。这个过程中还可能发生页分裂。此时对性能就会受到影响。
因此你可能在一些建表规范里面见到过类似的描述要求建表语句里一定要有自增主键这保证了新插入的数据只要追加就行避免了数据挪动和页分裂带来的性能影响。
除此之外我们还可以从存储的角度来看看使用递增主键的好处。假设你的表中确实有一个唯一字段比如字符串类型的身份证号那应该用身份证号做主键还是用自增字段做主键呢
由于每个非主键索引的叶子节点上都是主键的值。如果用身份证号做主键那么每个二级索引的叶子节点占用约 20 个字节而如果用整型做主键则只要 4 个字节如果是长整型bigint则是 8 个字节。
显然主键长度越小普通索引的叶子节点就越小普通索引占用的空间也就越小。
所以从性能和存储空间方面考量自增主键往往是更合理的选择。
有没有什么场景适合用业务字段直接做主键的呢还是有的。比如有些业务的场景需求是这样的
只有一个索引该索引必须是唯一索引。
由于没有其他索引所以也就不用考虑其他索引的叶子节点存储空间大小的问题。
这时候我们就要优先考虑上一段提到的“尽量使用主键查询”原则直接将这个索引设置为主键可以避免每次查询需要搜索两棵树。
覆盖索引
select ID from T where k between 3 and 5对于上面的 sql 语句如果查询值仅为主键 ID且 k 加了索引那我们就称它为覆盖索引。
因为 ID 的值已经在 k 索引树上了可以直接提供查询结果不需要回表。也就是说在这个查询里面索引 k 已经“覆盖了”我们的查询需求因此我们称为覆盖索引。
由于覆盖索引可以减少树的搜索次数显著提升查询性能所以使用覆盖索引是一个常用的性能优化手段。
最左前缀匹配原则
最左匹配原则的底层原理
我们都知道索引的底层是一颗B树那么联合索引当然还是一颗B树只不过联合索引的健值数量不是一个而是多个。构建一颗B树只能根据一个值来构建因此数据库依据联合索引最左的字段来构建B树。
例子假如创建一个a,b的联合索引那么它的索引树是这样的 可以看到a的值是有顺序的112233而b的值是没有顺序的121412。所以b 2这种查询条件没有办法利用索引因为联合索引首先是按a排序的b是无序的。
同时我们还可以发现在a值相等的情况下b值又是按顺序排列的但是这种顺序是相对的。所以最左匹配原则遇上范围查询就会停止剩下的字段都无法使用索引。例如a 1 and b 2 a,b字段都可以使用索引因为在a值确定的情况下b是相对有序的而a1and b2a字段可以匹配上索引但b值不可以因为a的值是一个范围在这个范围中b是无序的。
由此我们可以推出最左前缀原则最左优先以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(、、between、like)就会停止匹配。
例如如果建立(a,b)顺序的索引我们的条件只有bxxx是匹配不到(a,b)索引的但是如果查询条件是a 1 and b 2或者b2 and a1就可以因为优化器会自动调整a,b的顺序并不需要严格按照索引的顺序来再比如a 1 and b 2 and c 3 and d 4 如果建立(a,b,c,d)顺序的索引a、b、c能用到索引但d是用不到索引的因为c字段是一个范围查询它之后的字段会停止匹配。
联合索引建立原则
在建立联合索引的时候如何安排索引内的字段顺序?
由于最左前缀原则在创建联合索引时将过滤能力强的列放在前面。对于需要频繁排序的列也是放在前面因为索引是有序的对于查询时需要排序的列如果能走索引能提高查询性能。
select * from staffs where id like A%;//前缀都是排好序的使用的都是联合索引
select * from staffs where id like %A%;//全表查询
select * from staffs where id like %A;//全表查询联合索引场景分析
假设我们创建一个联合索引 (id,name,age)
create table staffs (id int default null,name char(32) default null,age int default null,KEY id_name_age_index (id,name,age)
)1.全值匹配查询
select * from staffs where id 1 and name jack and age 20;
select * from staffs where age 20 and id 1 and name jack;
select * from staffs where name jack and id 1 and age 20;过滤条件同时出现 idnameage且为精确查询则不管三者的顺序如何都能走整个联合索引。
2.匹配最左列
符合最左匹配原则的场景
select * from staffs where id 1; // 使用联合索引中的 id 索引
select * from staffs where id 1 and name jack; // 使用联合索引中的 id,name 索引
select * from staffs where id 1 and name jack and age 20; // 命中整个联合索引不符合最左匹配原则的场景
select * from staffs where name jack; // 对整个索引树进行扫描与全表扫描不同但也慢
select * from staffs where age 20; // 同上
select * from staffs where name jack and age 20; // 同上3.匹配列前缀
如果 id 是字符类型
select * from staffs where id like A%;//前缀都是排好序的使用的都是联合索引
select * from staffs where id like %A%;//全表查询
select * from staffs where id like %A;//全表查询4.匹配范围值
select * from staffs where id 1 and id 3; // 联合索引中的 id 走索引
select * from staffs where id 4 and age 20 and age 50; // id 走索引age 不走索引
select * from staffs where age 20 and age 50; // 不走索引5.精确匹配第一列并范围匹配其他列
select * from staffs where id 1 and age 50;参考
MySQL 实战45讲数据库常见知识点总结