AI 辅助 MySQL｜学习路线

发布时间：1970-01-20 16:11:29
免费阅读
MySQL 学习 数据库优化 SQL 性能 索引管理 事务处理 分布式系统 数据库集群 数据分片
本文详细介绍了AI辅助MySQL的学习路线，分为基础知识、进阶知识和高阶知识三部分。基础知识包括数据类型、数据操作语言（DML）、数据定义语言（DDL）、存储过程、常用函数和联表查询等内容，帮助初学者掌握MySQL的基本操作。进阶知识涉及性能监控、SQL执行计划、索引、SQL执行过程、存储引擎、日志、事务和锁等，帮助用户深入理解MySQL的内部机制和优化技巧。高阶知识则涵盖了分布式事务、MySQL集群和分库分表等复杂场景，适用于应对大规模数据和高并发需求。通过这一学习路线，读者可以逐步提升MySQL的使用和优化能力，应对不同层次的数据库挑战。
文章内容
思维导图
常见问题
社交分享

AI 辅助 MySQL｜学习路线

一、基础知识

一）数据类型

需要了解 MySQL 的数据类型，在实际使用中才能为每个字段选择适合的类型。

1、数值类型: INT, FLOAT, DECIMAL 等

2、字符串类型: VARCHAR, CHAR 等

3、日期和时间类型: DATE, DATETIME, TIMESTAMP 等

4、其他常用类型: BOOLEAN, ENUM, SET 等

二）数据操作语言 (DML)

这就是我们常说的 CURD，吃饭的家伙得了解。

1、插入数据: INSERT INTO

2、查询数据: SELECT

3、更新数据: UPDATE

4、删除数据: DELETE

三）数据定义语言 (DDL)

表相关调整，业务常变动，改表要学会！

1、创建数据库: CREATE DATABASE

2、创建表: CREATE TABLE

3、添加约束: ALTER TABLE

4、修改表结构: ALTER TABLE

5、删除表: DROP TABLE

6、索引管理: CREATE INDEX, DROP INDEX

四）存储过程

现在业务上基本很少使用存储过程，但是使用存储过程进行造数据是真的好用。

1、存储过程的概念和作用

2、创建存储过程: CREATE PROCEDURE

3、调用存储过程: CALL

五）常用函数

查数据必备技能，学会从 360 个维度不同姿势查询数据。

1、字符串函数: CONCAT, SUBSTRING, LENGTH 等

2、数值函数: SUM, AVG, MAX, MIN 等

3、日期和时间函数: NOW, DATE_FORMAT, TIMESTAMPDIFF 等

4、条件函数: IF, CASE WHEN 等

六）联表查询

不建议联表查询，但是技能你得掌握。

1、内连接: INNER JOIN

2、外连接: LEFT JOIN, RIGHT JOIN, FULL JOIN

3、自连接: 在同一表中进行连接

二、进阶知识

一）性能监控

出现问题时候要知道如何排查，快速定位问题。

1、show profile：查询剖析工具，可以指定具体的性能、IO 等信息

2、show processlist：查看所有连接的线程个数，来观察是否有大量线程处于不正常的状态或者其他不正常的特征，特别是死锁线程

3、slow_query_log：定位慢 SQL 语句，解决潜在的性能问题

二）SQL 执行计划

通过分析 SQL 的执行计划，就知道什么时候该给表添加索引，使用哪个索引来查找记录从而让 SQL 语句运行更快。

1、id：该语句的唯一标识

2、select_type：查询类型

3、table：访问的表名

4、partitions：当前查询匹配记录的分区

5、type：连接类型

6、possible_keys：指出 MySQL 可能使用的索引

7、key：显示 MySQL 实际决定使用的索引，如果没有选择索引，键是 NULL

8、key_len：使用索引的长度，如果没有选择索引，键是 NULL

9、ref：显示使用哪个列或常数与 key 一起从表中选择行

10、rows：执行查询时必须检查的行数，多行之间的数据相乘可以估算要处理的行数

11、filtered：执行查询时必须检查的行数，多行之间的数据相乘可以估算要处理的行数

12、extra：包含 MySQL 解决查询的详细信息

三）索引

索引是学习数据库必知必会的一个内容，绝大部分的慢 SQL 都是由于索引设置或使用不合理导致的，学会正确使用索引能够避免 90% 以上的慢 SQL。

1、索引的用处

2、索引分类

1）主键索引

2）唯一索引

3）前缀索引

4）全文索引

5）组合索引

6）普通索引

3、索引的数据结构

1）哈希：通过索引的 key 进行一次 Hash 计算，就可以快速获取磁盘文件指针，对于指定索引查找文件非常快，但是对于范围查找没法支持，有时候也会出现 Hash 冲突的情况。

2）二叉树：左边子节点的数据小于父节点数据，右边子节点的数据大于父节点数据。

3）红黑树：红黑树是平衡树的一种，它复杂的定义和规则都是为了保证树的平衡性。

4）B 树：B树是一种多路搜索树，它的每个节点都可以拥有多于两个孩子节点。M路的B树最多拥有M个孩子节点，设计成多路是为了降低树的高度。

5）B+ 树：B+ 树是在 B 树的基础上进行改造，它的数据都在叶子节点，同时叶子节点之间还加了指针形成链表。

4、面试常见词

1）回表

2）索引下推

3）最左匹配

4）覆盖索引

5、索引优化细节

四）SQL 执行过程

当你在客户端输入一条 SQL 查询语句后，MySQL 是如何处理和执行这条语句的呢？涉及到 MySQL 的很多模块和组件，以及它们之间的交互和协作。

1、连接器：连接器负责跟客户端建立连接，获取权限、维持和管理连接

2、查询缓存：当执行查询语句的时候，会先去查询缓存中查看结果，找到则直接返回

3、分析器：进行词法和语法分析，判断是否存在词法或语法上的错误信息

4、优化器：MySQL 基于成本优化，选择最优索引和表的连接关系等

5、执行器：先从存储引擎中找到数据，如果在内存中直接返回，如果不在内存中，查询后返回

五）存储引擎

存储引擎是 MySQL 的组件，用于处理不同表类型的SQL操作。使用合适的存储引擎，将会提高整个数据库的性能 。你想过 MySQL 的存储引擎为什么默认是 InnoDB 嘛？

1、MyISAM、InnoDB、MEMORY、MERGE 引擎信息

2、MyISAM 和 InnoDB 的区别？面试高频问题

3、InnoDB 核心概念

1）表空间：InnoDB 将所有数据（包括表数据，索引，回滚信息，插入缓冲索引页，系统事务信息，二次写缓冲）逻辑地放在一个空间中，称为共享表空间。

2）段：一个索引（InnoDB 都是 B+ 索引）由两个段管理，叶子节点段（leaf segment）和非叶子节点段（non leaf segment）

3）区：InnoDB 申请空间的最小单位，由连续页组成的空间，大小为 1MB，保持不变。

4）页：InnoDB访问的最小单位，默认16KB。一个区中一共有64个连续的页。

六）日志

日志是 MySQL 数据库的重要组成部分，记录着数据库运行期间各种状态信息。MySQL 日志主要包括错误日志、查询日志、慢查询日志、事务日志、二进制日志几大类。

1、事务日志

1）重做日志 (Redo Log)：InnoDB 存储引擎层的日志，通常是物理日志，记录的是数据页的更新内容。确保事务的持久性，防止在发生故障的时间点，尚有脏页未写入磁盘。在重启 MySQL 服务的时候，根据 redo log 进行重做恢复到最后一次提交事务的数据点，从而达到事务的持久性这一特性。

2）撤销日志 (Undo Log)：记录数据被修改前的内容（逻辑日志，生成与操作相反的语句），可以用来在事务失败时进行 rollback，用于回滚。同时可以提供多版本并发控制下的读（MVCC），也即非锁定读。

3）doublewrite buffer

2、二进制日志 (Binlog)：归档日志（二进制日志）：记录的是操作而不是数据值，在主从复制中，从库利用主库上的 Binlog 进行重播，实现主从同步，还可用于数据库的基于时间点的还原。

3、错误日志 (Error Log)：主要记录MySQL服务器启动和停止过程中的信息、服务器在运行过程中发生的故障和异常情况等。

七）事务

事务（Transaction）是用来维护数据库完整性的，它能够保证一系列的MySQL操作要么全部执行，要么全不执行。如果没有事务，那么数据库的数据就会发生各种错乱问题。

1、事务的概念和特性

2、事务隔离级别

1）读未提交（Read Uncommitted）

2）读已提交（Read Committed）

3）可重复读（Repeated Read）

4）串行化（Serializable）

3、ACID 属性

1）原子性（Atomicity）

2）一致性（Consistency）

3）隔离性（isolation）

4）持久性（durability）

4、事务的并发控制

八) 锁

锁是数据库系统区分与文件系统的一个关键特性，为了保证数据一致性，必须有锁的介入。数据库系统使用锁是为了支持对共享资源进行并发访问，提供数据的完整性和一致性。

1、锁的概念和作用

2、锁的类型

1）共享锁

2）排他锁

3）意向锁

4）记录锁

5）间隙锁

6）临键锁

7）插入意向锁

8）自增锁

9）死锁

3、锁的兼容性

4、加锁过程

5、事务隔离级别和锁的关系

6、MVCC (多版本并发控制)

1）特点：读不加锁，读写互斥

2）MVCC 的概念和原理

3）快照读和当前读

三、高阶知识

一）分布式事务

随着互联网的快速发展，软件系统由原来的单体应用转变为分布式应用，分布式系统环境下由不同的服务之间通过网络远程协作完成事务称之为分布式事务。

1、CAP 理论

2、Base 理论

3、二阶段提交

4、三阶段提交

5、一致性问题

6、分布式事务解决方案

1）XA 方案

2）Seata 方案

3）TCC 事务

二）MySQL 集群

假设你有一个非常大的网站，每天有数百万用户访问，数据库的读写请求非常庞大。为了应对这个高负载的情况，单个服务器可能无法满足需求，因此你需要构建一个 MySQL 集群。

集群主从机器的设计是为了实现高可用性和水平扩展。这就像是你有一个主服务器和多个从服务器，它们共同工作来处理用户请求和数据库操作。

1、主从同步

1）传统的主从复制

2）半同步复制

3）组复制

2、读写分离架构

二）分库分表

互联网发展的发展，也带来的数据量过大问题。单库单表已经不足以支撑庞大的数据量，一个表存储几十亿的数据，不得爆炸，为了解决这问题推出了分库分表的方案。

1、拆分维度

1）垂直拆分

2）水平拆分

2、主键生成策略

1）UUID

2）COMB

3）雪花算法

4）数据库 ID 表

5）Redis 生成 ID

3、分片策略

1）基于业务分片

2）哈希取模分片

3）一致性 Hash 分片

4、扩容策略

1）停机扩容

2）平滑扩容

5、分库分表引入的问题

思维导图生成中，请稍候...

问题 1： MySQL 中有哪些常见的数据类型？
回答： MySQL 的常见数据类型包括数值类型（如 INT、FLOAT、DECIMAL）、字符串类型（如 VARCHAR、CHAR）、日期和时间类型（如 DATE、DATETIME、TIMESTAMP）以及其他常用类型（如 BOOLEAN、ENUM、SET）。

问题 2： 什么是 DML 和 DDL？
回答： DML（数据操作语言）包括插入（INSERT INTO）、查询（SELECT）、更新（UPDATE）和删除（DELETE）数据。DDL（数据定义语言）用于创建和修改数据库结构，如创建数据库（CREATE DATABASE）、创建表（CREATE TABLE）、修改表结构（ALTER TABLE）等。

问题 3： 如何优化 MySQL 查询性能？
回答： 可以通过使用索引、分析 SQL 执行计划、监控慢查询日志（slow_query_log）以及优化 SQL 语句来提升查询性能。

问题 4： MySQL 中有哪些常见的索引类型？
回答： 常见的索引类型包括主键索引、唯一索引、前缀索引、全文索引、组合索引和普通索引。

问题 5： InnoDB 和 MyISAM 存储引擎的主要区别是什么？
回答： InnoDB 支持事务、行级锁和外键，适合高并发和事务处理场景；MyISAM 不支持事务和行级锁，但查询速度较快，适合读多写少的场景。

问题 6： MySQL 的事务隔离级别有哪些？
回答： MySQL 的事务隔离级别包括读未提交（Read Uncommitted）、读已提交（Read Committed）、可重复读（Repeated Read）和串行化（Serializable）。

问题 7： 什么是分布式事务？
回答： 分布式事务是指在分布式系统中，多个服务通过网络协作完成的事务。常见的解决方案包括二阶段提交（2PC）、三阶段提交（3PC）和 TCC 事务。

问题 8： 什么是 MySQL 集群？
回答： MySQL 集群是通过主从同步和读写分离架构实现高可用性和水平扩展的数据库系统，主服务器负责写操作，从服务器负责读操作。

问题 9： 分库分表的常见策略有哪些？
回答： 分库分表的常见策略包括垂直拆分、水平拆分、基于业务分片、哈希取模分片和一致性 Hash 分片。

问题 10： MySQL 的日志类型有哪些？
回答： MySQL 的日志类型包括事务日志（如重做日志 Redo Log 和撤销日志 Undo Log）、二进制日志（Binlog）、错误日志（Error Log）和慢查询日志（Slow Query Log）。