深入理解mysql 从入门到精通

1. MySQL结构

由下图可得MySQL的体系构架划分为：1.网络接入层 2.服务层 3.存储引擎层 4.文件系统层

1.网络接入层
提供了应用程序接入MySQL服务的接口。客户端与服务端建立连接，客户端发送SQL到服务端，Java中通过JDBC来实现连接数据库。

2.服务层

• 管理工具和服务：系统管理和控制工具，例如备份恢复、Mysql复制、集群等
• 连接池：主要负责连接管理、授权认证、安全等等。每个客户端连接都对应着服务器上的一个线程。服务器上维护了一个线程池，避免为每个连接都创建销毁一个线程。当客户端连接到MySQL服务器时，服务器对其进行认证。可以通过用户名与密码认证，也可以通过SSL证书进行认证。登录认证后，服务器还会验证客户端是否有执行某个查询的操作权限。由于每次建立连接需要消耗很多时间，连接池的作用就是将这些连接缓存下来，下次可以直接用已经建立好的连接，提升服务器性能。
• SQL接口：接受用户的SQL命令，并且返回用户操作的结果。
• 解析器：SQL命令传递到解析器的时候会被解析器验证和解析。MySQL是一个DBMS（数据库管理系统），没法直接理解SQL语句。Parser（解析器）负责对SQL语句进行解析好让DBMS知道该怎么做。
• 查询优化器： SQL语句在查询之前会使用查询优化器对查询进行优化。它使用的是“选取-投影-联接”策略进行查询以此选择一个最优的查询路径。 select uid,name from user where gender = 1; select 查询先根据 where语句进行选取，而不是先将表全部查询出来以后再进行条件过滤。select查询先根据 uid 和 name进行属性投影，而不是将属性全部取出以后再进行过滤。将这两个查询条件联接起来生成最终查询结果
• 缓存（8.0版本之前支持查询缓存，8.0之后不支持了）：查询缓存，如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。通过LRU算法将数据的冷端溢出，未来得及时刷新到磁盘的数据页，叫脏页。这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等

3.存储引擎层
负责数据的存储和读取，与数据库文件打交道。 服务器中的查询执行引擎通过API与存储引擎进行通信，通过接口屏蔽了不同存储引擎之间的差异。MySQL采用插件式的存储引擎。MySQL为我们提供了许多存储引擎，每种存储引擎有不同的特点。我们可以根据不同的业务特点，选择最适合的存储引擎。MySQL区别于其他数据库的最重要的一个特点就是插件式的表存储引擎，注意：存储引擎是基于表的。

4.系统文件层
该层主要是将数据库的数据存储在文件系统之上，并完成与存储引擎的交互。存储引擎是基于表的，以下分别使用MyISAM和InnoDB存储引擎建立两张表，看看其在文件系统中对应的文件存储格式。

存储引擎为MyISAM：
*.frm：存储表结构
*.MYD：MyISAM DATA，用于存储表的数据
*.MYI：MyISAM INDEX，用于存储表的索引

存储引擎为InnoDB：
*.frm：存储表结构 （mysql8.0之后去掉了frm表结构存储在ibd中）
*.ibd：InnoDB DATA，表数据和索引的文件。

区别：
MyISAM不支持事务，而InnoDB支持事务；
MyISAM支持表级锁，InnoDB支持行级锁和表级锁；
MyISAM不支持外键，而InnoDB支持外键；
MyISAM采用非聚簇索引，而InnoDB采用聚簇索引和非聚簇索引；
MyISAM支持管理非事务表，提高了全文检索的能力，如果业务上需要大量的select 请求，可以考虑使用MyISAM ，而InnoDB支持事务操作，适用于大量的update和insert操作。

【拓展】一个SQL语句在MySQL中的整体流程

2. MySQL存储引擎

MySQL区别于其他数据库的最重要的一个特点就是插件式的表存储引擎，也就是说存储引擎是基于表的。存储引擎的概念是MySQL里面才有的，不是所有的关系型数据库都有存储引擎这个概念 。

使用select version();查看MySQL数据库版本
使用show engines可以查看MySQL数据库有哪些引擎

这里介绍MySQL中常用的InnoDB和MyISAM，MySQL默认的数据库引擎是InnoDB

1.InnoDB引擎

InnoDB 是一个事务安全的存储引擎，它具备提交、回滚以及崩溃恢复的功能以保护用户数据。InnoDB 的行级别锁定保证数据一致性提升了它的多用户并发数以及性能。InnoDB 将用户数据存储在聚集索引中以减少基于主键的普通查询所带来的 I/O 开销。为了保证数据的完整性，InnoDB 还支持外键约束。默认使用B+TREE数据结构存储索引。

推荐参考：MySQL事务的四个特征（ACID）以及隔离级别

特点

• 支持事务，支持4个事务隔离（ACID）级别
• 行级锁定（更新时锁定当前行）
• 读写阻塞与事务隔离级别相关
• 既能缓存索引又能缓存数据
• 支持外键
• InnoDB更消耗资源，读取速度没有MyISAM快
• 在InnoDB中存在着缓冲管理，通过缓冲池，将索引和数据全部缓存起来，加快查询的速度；
• 对于InnoDB类型的表，其数据的物理组织形式是聚簇表。所有的数据按照主键来组织。数据和索引放在一块，都位于B+数的叶子节点上；

业务场景

• 需要支持事务的场景（银行转账之类）
• 适合高并发，行级锁定对高并发有很好的适应能力，但需要确保查询是通过索引完成的
• 数据修改较频繁的业务

InnoDB引擎调优

• 主键尽可能小，否则会给二级索引带来负担
• 避免全表扫描，这会造成锁表
• 尽可能缓存所有的索引和数据，减少IO操作
• 避免主键更新，这会造成大量的数据移动

2.MyISAM引擎
MyISAM既不支持事务、也不支持外键、其优势是访问速度快，但是表级别的锁定限制了它在读写负载方面的性能，因此它经常应用于只读或者以读为主的数据场景。默认使用B+TREE数据结构存储索引。

特点

• 不支持事务
• 表级锁定（更新时锁定整个表）
• 读写互相阻塞（写入时阻塞读入、读时阻塞写入；但是读不会互相阻塞）
• 只会缓存索引（通过key_buffer_size缓存索引，但是不会缓存数据）
• 不支持外键，读取速度快

业务场景

• 不需要支持事务的场景（像银行转账之类的不可行）
• 一般读数据的较多的业务
• 数据修改相对较少的业务
• 数据一致性要求不是很高的业务

MyISAM引擎调优

• 设置合适索引
• 启用延迟写入，尽量一次大批量写入，而非频繁写入
• 尽量顺序insert数据，让数据写入到尾部，减少阻塞
• 降低并发数，高并发使用排队机制
• MyISAM的count只有全表扫描比较高效，带有其它条件都需要进行实际数据访问

3. Mysql索引

推荐参考： mysql数据库有哪些索引？

3.1 索引结构

MySQL InnoDB存储引擎中的索引结构解析

3.2 回表

当 select 后面跟的查询条件在二级索引树上找不到时，就会进行回表

例：select id,name,age from user where name = '王强'; name是普通索引

这个sql就会进行回表：根据二级索引叶子结点主键ID到聚簇索引上面查找到对应行数据，这个过程就叫回表。简单说就是二级索引树上找不到完整数据，需要通过回表到聚簇索引才能找到。

因为索引是有序的，但是叶子节点ID不是有序的，所以回表操作是通过主键ID查询行数据是随机IO，会有一定性能影响，因此能不回表尽量不回表，也就是我们后面要讲的索引覆盖。

3.3 索引覆盖

当我们select 后面要查询的数据都能在索引树上面取到，不需要回表时，就是索引覆盖

例：select id,name,age from user where name = '王强'; name，age是组合索引

像上面这个查询，所有数据都可以从 name，age组合索引树上面取到，就不需要回表，这就是索引覆盖。

3.4 索引下推

索引下推是为了提升索引查询性能，把server层的过滤推送到存储引擎层，从而减少回表次数，减少IO。

推荐参考：MySQL索引下推（Index Condition Pushdown, ICP）优化深入解析

3.5 索引跳跃

索引跳跃（Index Skip Scan）是一种数据库查询优化技术，它允许数据库系统在某些情况下跳过索引的某些部分进行查询，以提高查询性能。这种技术通常在某些特定的情况下能够带来性能的提升。

注意：

• mysql只是提供了这种机制，但不意味着每次查询都会触发，mysql优化器会根据行数据大小，数据总量，索引树情况去综合选择。
• 并不是所有的数据库系统都对索引跳跃提供支持，而且实际触发索引跳跃的条件也会受到具体数据库的版本和优化器的实现策略的影响。

3.6 MVCC

存在意义：不用加锁，解决多并发场景下的快照读问题

推荐参考：深入理解MySQL中的MVCC（多版本并发控制）

3.7 前缀索引

4. Mysql锁

在 MySQL 数据库中，锁是用来管理并发访问的重要机制，它可以确保数据操作的一致性，防止不同事务之间的冲突和竞争。 MySQL 中常见的锁类型包括行锁、表锁、意向锁、排他锁、共享锁、间隙锁以及临建锁。

推荐参考：MySQL 锁机制全面解析

5. 其他

推荐参考：mysql中如何实现乐观锁

推荐参考：mysql中常见问题

推荐参考：详解Mysql中redo log、undo log、bin log