mysql运行原理(更新中...)_mysql 数据删了索引还在-优快云博客

本文链接：https://blog.youkuaiyun.com/a1234name/article/details/99627665

本文深入解析MySQL的存储引擎、查询执行流程、事务隔离级别、索引类型及优化、锁机制等核心概念，帮助读者掌握数据库性能调优的关键。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

本文章为丁老师(丁奇)讲mysql所做的一部分笔记。

存储引擎：主要是用来进行数据的读写的。

1.一条查询语句是怎么执行的：

首先连接数据库进来是mysql server层的

连接器：负责跟客户端建立连接、获取权限、维持和管理连接。尽量建立长连接，适时断开连接释放资源，密码错误会在这里报错

查询缓存：MySQL拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前执行过的语句及其结果可能会以key-value对的形式，被直接缓存在内存中。key是查询的语句，value是查询的结果。如果你的查询能够直接在这个缓存中找到key，那么这个value就会被直接返回给客户端。(尽量减少查询缓存的使用，弊大于利，在更新语句时会把这个表上的缓存全部清掉)

分析器：识别关键字，语法，知道你要做什么，语法错误，字段名表名不存在会在这里报错

优化器：主要作用时告诉你该怎么做，预估时间，选择索引方式。

执行器：开始执行的时候，要先判断一下你对这个表T有没有执行查询的权限，如果没有，就会返回没有权限的错误，有索引调用满足条件的接口，没索引全表扫描，一条一条找出满足条件的形成结果集。在慢查询日志中rows_examined(这个执行过程中扫描了多少行)

2.更新语句执行

也会按查询语句那样执行，不过涉及到两个重要的日志模块redo log(物理日志)和binlog(逻辑日志)

redo log:InnoDb存储引擎特有，让他拥有了crash-safe的能力，物理日志，循环进行write pos当前记录位置，checkpoint擦除的位置

MySQL里经常说到的WAL技术，WAL的全称是Write-Ahead Logging，它的关键点就是先把更新的写在redo log上，等空闲时再写入磁盘。

binlog:逻辑日志，所有存储引擎都具有(在mysql SERVER层实现的)，采用追加写入的方式(写完切换到下一个，不会覆盖)，主要记录语句的原始逻辑(做了什么改动)，比如“给ID=2这一行的c字段加1”，有两种模式，为statement格式时记录sql语句，row格式记录两条，更新前和更新后的。

redo log：在innodb引擎上实现的，记录的是“在某个数据页上做了什么修改”，采用循环写入

写入时采用两段提交使两个日志一致，redo log写完处于preare状态，再写binlog,写完之后再提交。如果在写binlog之前服务器出现异常，之后检测到未提交binlog和redo log不一致，则回滚保持数据一致，如果在写完binlog之后提交之前异常，之后检测到未提交，binlog和redolog完整，重启后自动commit。

3.事务(在引擎层实现)

4种隔离级别：

读未提交：别人改数据的事务尚未提交，我在我的事务中也能读到。(每次都用最新的数据,没有视图概念)
读已提交：别人改数据的事务已经提交，我在我的事务中才能读到。(每个sql语句都会创建一个视图)
可重复读：别人改数据的事务已经提交，我在我的事务中也不去读。(一个事物都用一个视图)
串行：我的事务尚未提交，别人就别想改数据。(采用加锁方式，读-读允许并发,读-写串行,写-读串行)
这4种隔离级别，并行性能依次降低，安全性依次提高。

Oracle数据库的默认隔离级别其实就是“读提交”，因此对于一些从Oracle迁移到MySQL的应用，为保证数据库隔离级别的一致，你一定要记得将MySQL的隔离级别设置为“读提交”。

在MySQL中，实际上每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值，通过回滚操作，都可以得到前一个状态的值。有一个回滚日志，在系统判断回滚日志不需要时进行删除(减少长事务的使用)。

事务启动方式有以下两种：

显式启动事务语句， begin 或 start transaction。配套的提交语句是commit，回滚语句是rollback。
set autocommit=0，这个命令会将这个线程的自动提交关掉。意味着如果你只执行一个select语句，这个事务就启动了，而且并不会自动提交。这个事务持续存在直到你主动执行commit 或 rollback 语句，或者断开连接。

建议你总是使用set autocommit=1, 通过显式语句的方式来启动事务。

4.索引

索引的常见模型：哈希表，有序数组，搜索树

哈希表是一种以键-值（key-value）存储数据的结构，key通过计算得到，再把数据当做value放进去，如果key经过换算之后，出现同一个值的话，再拉出一个链表，先找到key，再遍历这个链表。特点：无序，增加数据时快，做区间查询速度慢，需要全表扫描。

有序数组在等值查询和范围查询场景中的性能都非常优秀，查询效率非常的好，但往中间添加一个数据时，后面的所有数据都会移动，成本大，只适用于静态存储引擎。

二叉树是搜索效率最高的，但是实际上大多数的数据库存储却并不使用二叉树。其原因是，索引不止存在内存中，还要写到磁盘上。树高20，一次查询可能需要访问20个数据块。

为了让一个查询尽量少地读磁盘，就必须让查询过程访问尽量少的数据块。那么，我们就不应该使用二叉树，而是要使用“N叉”树。这里，“N叉”树中的“N”取决于数据块的大小。N叉树由于在读写上的性能优点，以及适配磁盘的访问模式，已经被广泛应用在数据库引擎中了。

在InnoDB中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。又因为前面我们提到的，InnoDB使用了B+树索引模型，所以数据都是存储在B+树中的。

每一个索引在InnoDB里面对应一棵B+树(可以说是由二叉搜索树，平衡二叉树，B树演变而来)

在InnoDB里，主键索引也被称为聚簇索引,非主键索引也被称为二级索引

如果语句是select * from T where ID=500，即主键查询方式，则只需要搜索ID这棵B+树；
如果语句是select * from T where k=5，即普通索引查询方式，则需要先搜索k索引树，得到ID的值为500，再到ID索引树搜索一次。这个过程称为回表。

我们在应用中尽量多使用主键查询。没有主键的表，innodb会给默认创建一个Rowid做主键

主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。

如果不采用自增主键，在插入一个数据时，会向B+树的之间插入，需要挪动后面的数据，如果该页数据已满，会申请新的数据页，挪动部分数据过去，这样就导致了页分裂，性能会受到影响，空间利用率也会降低。

从性能和存储空间方面考量，自增主键往往是更合理的选择。

覆盖索引

如果执行的语句是select ID from T where k between 3 and 5，这时只需要查ID的值，而ID的值已经在k索引树上了，因此可以直接提供查询结果，不需要回表。也就是说，在这个查询里面，索引k已经“覆盖了”我们的查询需求，我们称为覆盖索引。

由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

我们知道，身份证号是市民的唯一标识。也就是说，如果有根据身份证号查询市民信息的需求，我们只要在身份证号字段上建立索引就够了。而再建立一个（身份证号、姓名）的联合索引，是不是浪费空间？

如果现在有一个高频请求，要根据市民的身份证号查询他的姓名，这个联合索引就有意义了。它可以在这个高频请求上用到覆盖索引，不再需要回表查整行记录，减少语句的执行时间

最左前缀原则：B+树这种索引结构，可以利用索引的“最左前缀”，来定位记录。

不只是索引的全部定义，只要满足最左前缀，就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左N个字段，也可以是字符串索引的最左M个字符。

索引的复用能力。因为可以支持最左前缀，所以当已经有了(a,b)这个联合索引后，一般就不需要单独在a上建立索引了。因此，第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。

如果既有联合查询，又有基于a、b各自的查询呢？查询条件里面只有b的语句，是无法使用(a,b)这个联合索引的，这时候你不得不维护另外一个索引，也就是说你需要同时维护(a,b)、(b) 这两个索引。

这时候，我们要考虑的原则就是空间了。比如上面这个市民表的情况，name字段是比age字段大的，那我就建议你创建一个（name,age)的联合索引和一个(age)的单字段索引。

索引下推

在用到复合索引时：

select * from tuser where name like '张%' and ismale=1;

select * from tuser where name like '张%' and age=10 and ismale=1;

会在这张复合的索引表根据最左原则，找到姓张的，第一句找到之后开始回表，第二句会在复合索引表中先判断满足age=10的才进行回表，这样做可以有效的减少回表次数。

问到为什么要重建索引。索引可能因为删除，或者页分裂等原因，导致数据页有空洞，重建索引的过程会创建一个新的索引，把数据按顺序插入，这样页面的利用率最高，也就是索引更紧凑、更省空间。

删除了表的记录，但是索引文件还是不会变。InnoDB 这种引擎导致的,虽然删除了表的部分记录,但是它的索引还在, 并未释放.只能是重新建表才能重建索引.

limit和where是在mysql server层做的，先读出来放在server的一个临时内存中进行判断过滤。

联合索引可用到覆盖索引，最左前缀(调整顺序可少维护一个索引)，索引下推(有效减少回表次数)

5.锁

根据加锁范围：MySQL里面的锁可以分为：全局锁、表级锁、行级锁
一、全局锁：
对整个数据库实例加锁。
MySQL提供加全局读锁的方法：Flush tables with read lock(FTWRL)
这个命令可以使整个库处于只读状态。使用该命令之后，数据更新语句、数据定义语句和更新类事务的提交语句等操作都会被阻塞。
使用场景：全库逻辑备份。
风险：
1.如果在主库备份，在备份期间不能更新，业务停摆
2.如果在从库备份，备份期间不能执行主库同步的binlog，导致主从延迟
官方自带的逻辑备份工具mysqldump，当mysqldump使用参数--single-transaction的时候，会启动一个事务，确保拿到一致性视图。而由于MVCC的支持，这个过程中数据是可以正常更新的。

一致性读是好，但是前提是引擎要支持这个隔离级别。
如果要全库只读，为什么不使用set global readonly=true的方式？
1.在有些系统中，readonly的值会被用来做其他逻辑，比如判断主备库。所以修改global变量的方式影响太大。
2.在异常处理机制上有差异。如果执行FTWRL命令之后由于客户端发生异常断开，那么MySQL会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为readonly之后，如果客户端发生异常，则数据库就会一直保持readonly状态，这样会导致整个库长时间处于不可写状态，风险较高。
二、表级锁
MySQL里面表级锁有两种，一种是表锁，一种是元数据锁(MDL)
表锁的语法是:lock tables ... read/write
可以用unlock tables主动释放锁，也可以在客户端断开的时候自动释放。lock tables语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。
对于InnoDB这种支持行锁的引擎，一般不使用lock tables命令来控制并发，毕竟锁住整个表的影响面还是太大。
MDL：不需要显式使用，在访问一个表的时候会被自动加上。
MDL的作用：保证读写的正确性。
在对一个表做增删改查操作的时候，加MDL读锁；当要对表做结构变更操作的时候，加MDL写锁。
读锁之间不互斥。读写锁之间，写锁之间是互斥的，用来保证变更表结构操作的安全性。
MDL 会直到事务提交才会释放，在做表结构变更的时候，一定要小心不要导致锁住线上查询和更新。

三、行锁

MyISAM引擎就不支持行锁。不支持行锁意味着并发控制只能使用表锁，对于这种引擎的表，同一张表上任何时刻只能有一个更新在执行，这就会影响到业务并发度。InnoDB是支持行锁的，这也是MyISAM被InnoDB替代的重要原因之一。

行锁就是针对数据表中行记录的锁。这很好理解，比如事务A更新了一行，而这时候事务B也要更新同一行，则必须等事务A的操作完成后才能进行更新。

在InnoDB事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。

知道了这个设定(两阶段锁协议)，对我们使用事务有什么帮助呢？那就是，如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。

死锁和死锁检测

当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁

这种情况出现死锁：

当出现死锁以后，有两种策略：

一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数innodb_lock_wait_timeout来设置。
另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数innodb_deadlock_detect设置为on，表示开启这个逻辑。

减少死锁的主要方向，就是控制访问相同资源的并发事务量。

怎么删除表的前10000行。比较多的留言都选择了第二种方式，即：在一个连接中循环执行20次 delete from T limit 500。

确实是这样的，第二种方式是相对较好的。

第一种方式（即：直接执行delete from T limit 10000）里面，单个语句占用时间长，锁的时间也比较长；而且大事务还会导致主从延迟。

第三种方式（即：在20个连接中同时执行delete from T limit 500），会人为造成锁冲突。

普通索引和唯一索引的选择

假设，执行查询的语句是 select id from T where k=5。这个查询语句在索引树上查找的过程，先是通过B+树从树根开始，按层搜索到叶子节点，也就是图中右下角的这个数据页，然后可以认为数据页内部通过二分法来定位记录。

对于普通索引来说，查找到满足条件的第一个记录(5,500)后，需要查找下一个记录，直到碰到第一个不满足k=5条件的记录。
对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。

那么，这个不同带来的性能差距会有多少呢？答案是，微乎其微。

你知道的，InnoDB的数据是按数据页为单位来读写的。也就是说，当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。在InnoDB中，每个数据页的大小默认是16KB。

因为引擎是按页读写的，所以说，当找到k=5的记录的时候，它所在的数据页就都在内存里了。那么，对于普通索引来说，要多做的那一次“查找和判断下一条记录”的操作，就只需要一次指针寻找和一次计算。

普通索引和唯一索引应该怎么选择。其实，这两类索引在查询能力上是没差别的，主要考虑的是对更新性能的影响。所以，我建议你尽量选择普通索引。

如果所有的更新后面，都马上伴随着对这个记录的查询，那么你应该关闭change buffer(change buffer的主要目的就是将记录的变更动作缓存下来，所以在一个数据页做merge之前，change buffer记录的变更越多（也就是这个页面上要更新的次数越多），收益就越大,普通索引可以用，唯一索引不可以用)。而在其他情况下，change buffer都能提升更新性能。

在实际使用中，你会发现，普通索引和change buffer的配合使用，对于数据量大的表的更新优化还是很明显的。

特别地，在使用机械硬盘时，change buffer这个机制的收效是非常显著的。所以，当你有一个类似“历史数据”的库，并且出于成本考虑用的是机械硬盘时，那你应该特别关注这些表里的索引，尽量使用普通索引，然后把change buffer 尽量开大，以确保这个“历史数据”表的数据写入速度。

redo log 主要节省的是随机写磁盘的IO消耗（转成顺序写），而change buffer主要节省的则是随机读磁盘的IO消耗。