MYSQL进阶解析--上

最新推荐文章于 2025-04-18 21:42:01 发布

半旧夜夏

最新推荐文章于 2025-04-18 21:42:01 发布

阅读量674

点赞数 16

文章标签： mysql 数据库

本文链接：https://blog.youkuaiyun.com/m0_64972409/article/details/144374152

版权

本文将带您了解MySQL的逻辑架构，揭示其高效运作的内在机制。从数据库的存储引擎到查询执行流程，我们将一一剖析，对MySQL的工作原理有全面的认识。不仅如此，文章还将重点讨论索引优化的高级技巧，包括索引设计的最佳实践、以及如何通过索引调优来提升查询效率。如果您是数据库新手，本文都能为您提供宝贵的知识和实用的技巧，助您在数据库管理和优化的道路上更进一步。

一、基本认识和使用

连接Mysql服务

docker run -d \
-p 3309:3306 \
-v /mysql/mysql/conf:/etc/mysql/conf.d \
-v /mysql/mysql/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=1234 \
--name mysql \
--restart=always \
mysql:8.0.30

docker exec -it mysql env LANG=C.UTF-8 /bin/bash

mysql -uroot -p1234

可选：修改密码和连接用户

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456'

二、逻辑架构剖析

MYSQL逻辑架构示意图

1，客户端连接器

MySQL服务器之外的客户端程序，与具体语言有关，比如Java的JDBC，Navicat连接工具等。本质是通过TCP连接服务器进行通信

2，连接层（连接池）

①客户端访问Mysql之前，第一件事就是建立TCP连接

②经过三次握手连接成功，服务器对TCP传输的用户名和密码进行身份认证和权限校验

③用户名或密码不正确时，返回 Access denied for user 并结束执行

④登录成功，将从权限表查询该账号拥有的权限，进行权限判断，后续操作都依赖当前查询的权限等级

⑤TCP连接收到请求后，会单独分配一个线程进行后续客户端操作，固会使用线程池进行流程。每个线程从池中获取，省去创建和销毁线程的开销

3，SQL接口

处理执行sql语句的一套组件，接收用户的sql命令，并返回执行结果给用户。支持DML（数据操作语言）、DDL（数据定义语言）、存储过程、视图、触发器、自定义函数等多种SQL语言接口

4，解析器

负责对sql命令进行验证和解析，包含词法分析，语法分析，语义分析，并创建语法树

词法分析：检查sql语句关键字是否正确

语法分析：检查语句是否符合规范和要求，并创建语法树

语义分析：检查语法树是否合法，比如表名，字段名是否存在

5，查询优化器

负责为sql语句确定最优的执行路径，生成一个高效的执行计划，比如选择适当的索引，表的连接顺序等，旨在减少查询的时间和开销

6，缓存

顾名思义缓存查询结果，如果找到对应的缓存结果直接返回。它由一系列小缓存组成，并且可以在不同客户端共享

为什么MySQL8之后去掉了查询缓存？

只有相同的SQL语句才会命中查询缓存。两个查询请求在任何字符上的不同（例如：空格、注释、大小写），都会导致缓存不会命中。在两条查询之间有 INSERT 、 UPDATE 、 DELETE 、 TRUNCATE TABLE 、 ALTER TABLE 、 DROP TABLE 或 DROP DATABASE 语句也会导致缓存失效。因此 MySQL的查询缓存命中率不高。所以在MySQL 8之后就抛弃了这个功能。

7，存储引擎

负责数据的存储和提取，服务器通过API与存储引擎进行通信，不同的引擎具有不同的功能，管理表的结构不同对应的算法也不相同，常见的有InnoDB和MyISAM

8，存储层

所有的数据、数据库、表的定义、表的每一行的内容、索引，都是存在文件系统上，以文件的方式存在，并完成与存储引擎的交互。

三、查询流程

客户端通过TCP协议向服务器发送SQL查询请求，通过SQL接口，在8.0版本之前会先进行查询缓存，如果缓存命中则直接返回结果，如果没有命中或8.0之后版本继续执行后续流程。解析器进行语句的解析，包含词法，语法，语义分析，并生成解析树，预处理器会进一步检查SQL语句，比如表名是否存在，权限访问和删除WHERE 1 = 1等不必要内容，生成新的语法树。在查询优化器确定查询的最佳方式，包含合适索引和表的顺序等，根据优化器的决定生成执行计划，执行引擎根据计划执行查询。查询执行引擎通过API接口与存储引擎进行交互，存储引擎负责数据的存储和提取。最后查询结果会被返回给客户端，如果开启了缓存将会将结果缓存起来

四、存储引擎

MyISAM和InnoDB的区别

InnoDB：事务外键行锁定，崩溃恢复能力强

MyISAM：读多写少，表锁并发差

1，InnoDB是支持事务的引擎，MyISAM不支持事务处理

2，InnoDB支持外键约束保持数据的完整和一致性，MyISAM不支持外键约束

3，InnoDB采用行锁级别，可以更好的并发操作和控制，MyISAM采用表锁级别，导致并发性能下降

4，InnoDB在服务崩溃情况下具备恢复能力，可在服务异常或崩溃情况下自动回复数据，MyISAM不具备恢复机制，会导致崩溃情况下数据丢失

5，InnoDB使用行锁机制和多版本并发控制(MVCC)，具有更好的并发性能，MyISAM更适合读多写少的场景

五、索引

1，B+树的结果示意图

2，聚簇索引和非聚簇索引

数据存储方式：
   - 聚簇索引：数据行的物理存储顺序与索引顺序相同。每个表只能有一个聚簇索引，因为数据行不能同时按照两种不同的顺序进行物理存储。在InnoDB中，聚簇索引通常是表的主键。
   - 非聚簇索引：索引结构和数据行是分开存储的。非聚簇索引的叶子节点通常包含数据行的物理地址（行指针），而不是数据本身。这意味着通过非聚簇索引查找数据需要两次查找：首先在索引中找到行地址，然后再通过地址访问数据。
2. 索引结构：
   - 聚簇索引：索引的叶子节点直接包含数据记录，因此索引的搜索可以直接获取数据。
   - 非聚簇索引：索引的叶子节点包含索引键和指向数据物理位置的指针，需要通过这些指针来获取数据。
3. 性能特点：
   - 聚簇索引：对于主键的排序查找和范围查找速度非常快，因为数据是按照索引顺序存储的。但是，插入和更新操作可能会比较慢，尤其是当数据不是按照主键顺序插入时，因为可能需要移动数据来维护索引的顺序。
   - 非聚簇索引：对于非主键列的查找可能更快，因为它们不需要维护数据的物理顺序。但是，对于返回大量数据的查询，非聚簇索引可能需要更多的I/O操作，因为需要先通过索引找到数据地址，然后再访问数据。
4. 适用场景：
   - 聚簇索引：适用于经常按照主键进行排序和范围查询的场景。
   - 非聚簇索引：适用于经常根据非主键列进行查询的场景，尤其是当查询不需要返回大量数据时。
5. 存储引擎支持：
   - 聚簇索引：主要由InnoDB存储引擎支持。
   - 非聚簇索引：大多数存储引擎都支持，包括InnoDB和MyISAM。
6. 主键选择：
   - 聚簇索引：建议使用自增的整数作为主键，以保持数据的顺序插入，提高性能。
   - 非聚簇索引：没有这种限制，可以根据查询需求选择不同的列作为索引键。
7. 索引数量：
   - 聚簇索引：每个表只能有一个。
   - 非聚簇索引：一个表可以有多个。

3，回表

4，覆盖索引

指通过索引即可获取到查询的数据，无需读取用户记录或回表查询，一个索引满足了查询的结果的数据就叫覆盖索引。比如创建c2的二级索引，那么该c2的非聚簇索引的叶子节点就包含了主键c1和c2的值，那么在查询SELECT c1,c2时就可以直接返回索引存储的数据值。

5，索引的使用

-- 创建普通索引
CREATE INDEX index_name ON table_name (column_name);

-- 创建唯一索引，确保列值的唯一性
CREATE UNIQUE INDEX index_name ON table_name (column_name);

-- 创建普通索引（MySQL 5.6+ 和 PostgreSQL）
CREATE INDEX index_name ON table_name (column_name);

-- 创建复合索引，基于多列
CREATE INDEX index_name ON table_name (column_name1, column_name2);

-- 查看索引

SHOW INDEX FROM table_name;

-- 删除索引

DROP INDEX index_name ON table_name;