网站建设公司有哪几家,物流网站怎么做推广,自己做的网站怎么发布win7,高端 网站建设深入浅出MySQL sql执行流程第一步#xff1a;通过连接器进行连接第二步#xff1a;解析器解析 SQL第三步#xff1a;执行SQL 行记录存储格式行溢出日志数据库三大范式第一范式第二范式第三范式 索引索引分类B树索引BTree vs Hash需要索引1、字段需要频繁的查询操作2、字段用… 深入浅出MySQL sql执行流程第一步通过连接器进行连接第二步解析器解析 SQL第三步执行SQL 行记录存储格式行溢出日志数据库三大范式第一范式第二范式第三范式 索引索引分类B树索引BTree vs Hash需要索引1、字段需要频繁的查询操作2、字段用于排序和分组的3、字段有唯一性约束的 不需要索引1、表数据太少2、经常更新的字段3、字段存在大量重复数据 索引失效情况1、对索引使用左、左右模糊查询2、对索引进行表达式计算3、联合索引非最左匹配4、WHERE 中 OR前和OR后 2、事务ACID 1、脏读2、不可重复读3、幻读事务隔离级MVCC3、锁1、全局锁2、表级锁1、元数据锁MDL不需要显示调用 2、意向锁3、行级锁1、Record Lock记录锁2、Gap Lock间隙锁3、Next-Key Lock临键锁 4、InnoDB 与 MyISAM sql执行流程
MySQL 的架构共分为两层上层的Server 层和下层的存储引擎层 Server 层主要负责建立连接、分析和执行 SQL。存储引擎层主要负责数据的存储和提取。
第一步通过连接器进行连接
我们首先需要连接 MySQL 服务器然后才能执行 SQL。因为 MySQL 是基于 TCP 协议进行传输的所以连接的过程需要先经过 TCP 的三次握手。 查询缓存 如果 SQL 是查询语句MySQL 就会先去缓存里查找缓存数据查询缓存是以 key-value 形式保存在内存中的。 如果查询的语句命中查询缓存那么就会直接返回 value 给客户端。如果查询的语句没有命中查询缓存中那么就要往下继续执行等执行完后查询的结果就会被存入查询缓存中。 但其实对于更新比较频繁的表查询缓存的命中率很低的所以MySQL 8.0 版本直接将查询缓存删掉了。
第二步解析器解析 SQL
在正式执行 SQL 查询语句之前 MySQL 会先对 SQL 语句做解析这个工作交由「解析器」来完成。 词法和语法分析。MySQL 会根据输入的字符串识别出表名字段名这些关键字。然后检验我们输入的 SQL 语句语法是否正确比如把 from 写成了 form这时 MySQL 解析器就会报错。
第三步执行SQL
SELECT 查询语句流程主要可以分为三个阶段 1、预处理阶段 检查 SQL 查询语句中的表或者字段是否存在把 select* 中的 * 符号扩展为表上的所有列 2、优化阶段 优化器主要负责确定 SQL 查询语句的执行方案比如在表里面有多个索引的时候优化器会基于查询成本的考虑来决定选择使用哪个索引。 3、执行阶段 根据执行计划执行 SQL 查询语句从存储引擎读取记录返回给客户端 连接器— 解析器 — 执行器
行记录存储格式
InnoDB 提供了 4 种行格式比较经典的是Compact格式。 在compact行格式下一行记录分为两个部分额外信息和真实数据。 额外信息 3 个部分变长字段的长度列表、NULL 值列表、记录头信息 变长字段的长度列表主要是存储变长字段所占用的数据大小然后读取的时候根据这个长度列表来读取对应的数据长度。 如果表里没有变长字段那行格式就不会有变长字段长度列表 NULL 值列表因为表中的某些列可能会存储 NULL 值如果把这些 NULL 值都放到记录的真实数据中会比较浪费空间所以 Compact 行格式把这些值为 NULL 的列存储到 NULL值列表中。如果字段都是 NOT NULL就不会有 NULL 值列表 记录头信息主要包含标识这条数据是否被删除、下一条记录的位置等等。 真实数据 记录真实数据部分除了我们定义的字段最前面还有三个隐藏字段分别为row_id、trx_id、roll_pointer。 如果表里既没有指定主键又没有唯一约束那么 InnoDB 就会为记录添加 row_id 隐藏字段占六个字节。 trx_id是事务id表示这个数据是由哪个事务生成的占6个字节。 roll_pointer这条记录上一个版本的指针。roll_pointer 是必需的占 7 个字节。
行溢出
MySQL 中磁盘和内存交互的基本单位是页一个页的大小一般是 16KB也就是 16384字节而一个 varchar 类型的列最多可以存储 65532字节一些大对象如 TEXT、BLOB 可能存储更多的数据这时一个页可能就存不了一条记录。这个时候就会发生行溢出多的数据就会存到另外的「溢出页」中。
日志
undo log 回滚日志是 Innodb 存储引擎层生成的日志实现了事务中的原子性主要用于事务回滚和 MVCC多版本并发控制。 在每个事务的执行过程中都记录下回滚时需要的信息到一个日志里那么在事务执行中途发生了 MySQL 崩溃后我们可以通过这个日志回滚到事务之前的数据。 redo log 重做日志是 Innodb 存储引擎层生成的日志实现了事务中的持久性主要用于断电等故障恢复 redo log 它记录了某个数据页做了什么修改比如对表空间A中的B数据页C偏移量的地方做了更新每当执行一个事务就会产生这样的一条或者多条物理日志。 在事务提交时会先把 redo log 持久化到磁盘。当系统崩溃 MySQL 重启后可以根据 redo log 的内容把所有数据恢复到最新的状态。 undo log 记录了此次事务「开始前」的数据状态记录的是更新之前的值 redo log 记录了此次事务「完成后」的数据状态记录的是更新之后的值 binlog 归档日志是 Server 层生成的日志主要用于数据备份和主从复制 MySQL 在完成一条更新操作后Server 层还会生成一条 binlog等之后事务提交的时候会将该事务执行过程中产生的所有 binlog 统一写 入 binlog 文件。 binlog 文件是记录了所有数据库表结构变更和表数据修改的日志不会记录查询类的操作比如 SELECT 和 SHOW 操作。
数据库三大范式
第一范式
所谓第一范式是指数据库表的每一列都不能再分也就是某个属性不能有多个值。简而言之第一范式就是无重复的列。比如某条记录里小明的课程字段有数学语文英语那就应该分为三条记录。
第二范式
满足第二范式必须先满足第一范式第二范式要求数据库表中的每行必须可以被唯一地区分。这个唯一区分的方法通常是给表加上一个存储唯一标识的列其实也就是主键。简而言之第二范式就是有主键非主键字段依赖主键。
第三范式
满足第三范式必须先满足第二范式。第三范式要求一个在数据库表中不能包含其它表中的非主关键字。 部门表里有ID、nameID是主键。 那么在员工表里最多只能加入部门ID而不能再加入部门名称等字段否则就会有大量的数据冗余。 简而言之第三范式就是属性不依赖于其它非主属性。
索引
索引的定义就是帮助存储引擎快速获取数据的一种数据结构形象的说索引就相当于数据的目录。
索引分类
按「数据结构」分类Btree索引、Hash索引、Full-text索引。按「物理存储」分类聚簇索引、二级索引
B树索引
BTree 是一种多叉树特点是只在叶子节点中按主键顺序放数据(主键值或实际数据)在非叶子节点中只放索引。每一个叶子节点都有两个指针分别指向下一个叶子节点和上一个叶子节点形成一个双向链表。 每一层父节点的索引值都会出现在下层子节点的索引值中因此在叶子节点中包括了所有的索引值信息。 BTree 相比于 B 树和二叉树来说最大的优势在于查询效率很高。
因为BTree 只在叶子节点存储数据非叶子只放索引所以它每个非叶子节点可以存储更多的索引键值对所以从根节点到叶子节点的路径更短在相同的磁盘 I/O 次数下就能查询更多的节点。而且由于它的叶子节点是按有序链表连接的因此在进行范围查询的时候可以通过一次I/O操作访问多个相邻的数据。因为即使在数据量很大的情况查询一个数据的磁盘 I/O 效率依然很高。 一张表只能有一个聚簇索引那就还会有非聚簇索引非聚簇也是用了 B 树的数据结构只不过非聚簇索引的叶子节点存放的是主键值不是实际数据。 聚簇索引的 BTree 和非聚簇索引的 BTree 的区别 主键索引的 BTree 的叶子节点存放的是实际数据所有完整的用户记录都存放在主键索引的 BTree 的叶子节点里 二级索引的 BTree 的叶子节点存放的是主键值而不是实际数据。查询时需要先获得主键值再通过主键索引中的 BTree 树查询到对应的叶子节点来获取整行数据。这个过程叫「回表」也就是说要查两个 BTree 才能查到数据。 MyISAM索引文件和数据文件是分离的索引文件仅保存数据记录的地址也就是一个指向对应数据块的指针。
BTree vs Hash
Hash 在做等值查询的时候效率很快搜索复杂度为 O(1)。但数据库中经常查询多条数据这时候由于B数据的有序性叶子节点又有链表相连他的查询效率会比Hash快的多。
需要索引
1、字段需要频繁的查询操作
当某个字段经常用于查询操作时可以考虑为该字段创建索引。索引可以加快查询速度特别是在大型数据集中。
2、字段用于排序和分组的
经常用于 GROUP BY 和 ORDER BY 的字段这样在查询的时候就不需要再去做一次排序了因为建立索引之后在 BTree 中的记录都是排序好的。
3、字段有唯一性约束的
当需要确保某个列的唯一性时可以在该列上创建唯一索引。唯一索引可以防止重复值的插入并提高查找的效率。
不需要索引
索引缺点 创建和维护索引也需要时间和空间而且时间和空间复杂度一般会随着数据量的增加而增大
1、表数据太少
表数据太少的时候不需要创建索引
2、经常更新的字段
经常更新的字段不用创建索引因为如果索引字段频繁修改那么维护 BTree的成本就会上升会影响数据库性能的。
3、字段存在大量重复数据
字段中存在大量重复数据比如性别字段男女的记录分布均匀那么无论搜索哪个值都可能得到一半的数据。这种情况MySQL很可能会通过查询优化器进行全表扫描。
索引失效情况
1、对索引使用左、左右模糊查询
使用 like 关键字进行左查询或者左右模糊查询会导致索引失效。因为索引底层是 B 树它是按照「索引值」有序排列存储的所以只能根据前缀进行比较。如果使用 like ‘%XX’ 方式来查询因为查询的结果可能是「AXX、BXX、CXX」等之类的所以不知道从哪个索引值开始比较于是就只能通过全表扫描的方式来查询。
2、对索引进行表达式计算
查询条件中对索引进行表达式计算会使索引失效。 select* from t_user where id1 10;因为索引保存的是索引字段的原始值而不是 id 1 表达式计算后的值所以这样查询无法走索引。
3、联合索引非最左匹配
联合索引要能正确使用需要遵循最左匹配原则也就是按照最左优先的方式进行索引的匹配。 如果创建了一个 (a, b, c) 联合索引 where a1where a1 and b2 and c3where a1 and b2都可以 where c3where b2 and c3都不行。 因为在联合索引的情况下数据是按照索引第一列排序第一列数据相同时才会按照第二列排序
4、WHERE 中 OR前和OR后
WHERE 子句中如果 OR 前的条件列是索引列而 OR 后的条件列不是索引列那么索引会失效。因为 OR 的含义就是两个只要满足一个即可因此只有一个条件列是索引列是没有意义的只要有条件列不是索引列就会进行全表扫描。
2、事务
事务是一组数据库操作的逻辑单元用来确保数据的正确性和可靠性。 事务特性
ACID
原子性Atomicity一个事务中的所有操作要么全部完成要么全部不完成。事务在执行过程中发生错误会被回滚到事务开始前的状态就像这个事务从来没有执行过。 一致性Consistency是指事务操作前和操作后数据满足完整性约束数据库保持一致性的状态。比如转账时转出账户钱少了多少那转入账户的钱就要对应多出来多少。 隔离性Isolation数据库允许多个并发事务同时对其数据进行读写和修改的能力隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致因为多个事务同时使用相同的数据时不会相互干扰每个事务都有一个完整的数据空间对其他并发事务是隔离的。 持久性Durability事务处理结束后对数据的修改就是永久的即便系统故障也不会丢失。 并行事务会引发什么问题
1、脏读
指如果一个事务「读到」了另一个未提交事务修改过的数据就意味着发生了「脏读」现象。 A改了数据但是还没提交事务B就能读到A改过的数据。如果这时A回滚了事务那B读的数据就是过期数据这就是脏读。
2、不可重复读
指在一个事务内多次读取同一个数据如果出现前后两次读到的数据不一样的情况就意味着发生了「不可重复读」现象。强调前后读出的数据不一致
3、幻读
不可重复读是由于另一个事务对数据的更改所造成的而幻读是由于另一个事务插入或删除引起的。强调前后读取的记录数量不一致
事务隔离级
读未提交隔离级别下可能发生脏读、不可重复读和幻读现象 读已提交隔离级别下可能发生不可重复读和幻读现象但是不可能发生脏读现象 可重复读隔离级别下可能发生幻读现象但是不可能发生脏读和不可重复读现象 串行化隔离级别下脏读、不可重复读和幻读现象都不可能会发生。 MySQL InnoDB 引擎的默认隔离级别是可重复读 可重复读中怎么解决幻读 针对快照读普通 select 语句是通过 MVCC多版本并发控制来解决了幻读的因为在可重复读隔离级别下事务执行过程中看到的数据一直跟这个事务启动时看到的数据保持一致以此避免幻读问题。 针对当前读select … for update 等语句是通过 next-key lock记录锁间隙锁方式解决了幻读因为当执行 select … for update 语句的时候会加上 next-key lock如果有其他事务在 next-key lock 锁范围内插入了一条记录那么这个插入语句就会被阻塞无法成功插入所以就很好了避免幻读问题。
MVCC
也就是多版本并发控制MVCC主要用于解决并发控制中的读-写冲突问题同时提供了高度的隔离性和一致性。其实通过版本链来控制并发事务访问同一个记录时的操作。 MVCC会为每个行记录都维护多个版本每个版本都有自己的版本号和时间戳如果有事务对记录进行修改就创建一个新的版本。当一个事务执行读取操作时只会读取已提交的数据版本并且不会读取其他事务正在修改的数据版本从而避免了读-写冲突的发生。旧版本的数据对于其他正在执行的事务仍然可见从而保持了事务的隔离性。
3、锁
在 MySQL 里根据加锁的范围可以分为全局锁、表级锁和行锁三类。
1、全局锁
全局锁主要应用于做全库逻辑备份这样在备份数据库期间不会因为数据或表结构的更新而出现备份文件的数据与预期的不一样。 但是加上全局锁意味着整个数据库都是只读状态。 那么如果数据库里有很多数据备份就会花费很多的时间关键是备份期间业务只能读数据而不能更新数据这样会造成业务停滞。
2、表级锁
表锁用于对整个表进行锁定而且会限制事务对该表的访问和操作 表锁的颗粒度太大会影响并发性能。表级锁不会出现死锁发生锁冲突几率高并发低。
1、元数据锁MDL不需要显示调用
当我们对数据库表进行操作时会自动给这个表加上元数据锁。 元数据锁 是为了保证当用户对表执行 CRUD 操作时防止其他线程对这个表结构做了变更。 元数据锁在事务提交后才会释放这意味着事务执行期间元数据锁 是一直持有的。可能会出现线程A开启了事务但还未提交(加了MDL锁)另一个修改表结构的线程会被阻塞从而导致后续的大量select语句线程被阻塞的情况因为写锁获取优先级高于读锁。
2、意向锁
我们知道正常在更新记录的时候引擎会对记录加独占锁而在InnoDB引擎中当对表里的记录加独占锁之前需要先对表加上意向独占锁然后再对该记录加独占锁。 意向锁的目的是为了快速判断表里是否有记录被加锁。 比如有一个事务想对表里的某个记录加独占锁它会先在表上加意向独占锁。然后如果有其他事务也考虑对同一记录加锁时就会先检查表上有没有意向独占锁。如果有它们就知道已经有事务打算在表中的这个记录上加独占锁就不用去遍历表里的记录了否则还要一一遍历。
3、行级锁
不同隔离级别下行级锁的种类是不同的。在读已提交隔离级别下行级锁的种类只有记录锁1。在可重复读隔离级别下行级锁有记录锁还有间隙锁123。 行级锁会出现死锁发生锁冲突几率低并发高。
1、Record Lock记录锁
锁住的是一条记录。分为有 S 共享锁和 X 排他锁S锁之间兼容但与X锁不兼容。X锁与S锁、X锁都不兼容。
2、Gap Lock间隙锁
锁定一个范围但是不包含记录本身只存在于可重复读隔离级别目的是为了解决可重复读隔离级别下幻读的现象。比如表中有一个范围 id 为35间隙锁那么其他事务就无法插入 id 4 这条记录了这样就有效的防止幻读现象的发生。
3、Next-Key Lock临键锁
Record Lock Gap Lock 的组合锁定一个范围并且包含记录本身。例如表中有一个范围 id 为35] 的临键锁那么其他事务即不能插入 id 4 记录也不能修改 id 5 这条记录。 MySQLInnoDB引擎的行锁是通过索引加载的也就是说行锁是加在索引响应的行上的要是对应的SQL语句没有走索引则会全表扫描行锁则无法实现取而代之的是表锁此时其它事务无法对当前表进行更新或插入操作。
4、InnoDB 与 MyISAM
1事务InnoDB支持事务MyISAM不支持事务 。 2锁级别 InnoDB 最小粒度支持行级锁MyISAM 最小粒度支持表级锁。 3索引InnoDB主键索引采用聚集索引B树叶子存储数据MyISAM采用非聚集索引B树叶子存储指向数据文件的指针。 4外键InnoDB支持外键而MyISAM不支持。 适用场景 1、InnoDB适合需要事务的场景MyISAM适合不需要事务的场景 2、读查询比较多的可以考虑MyISAM如果既有读也有写使用InnoDB。 3、系统奔溃后MyISAM恢复起来更困难 4、如果需要使用外键那这两个表必须是InnoDB表因为MyISAM表暂时不支持外键。
