参考地址:https://zhuanlan.zhihu.com/p/112857507/

MySQL基础篇

1.MySQL的binlog有有几种录入格式？分别有什么区别？（重点）

有三种格式，statement，row和mixed。

statement：模式下，每一条会修改数据的sql都会记录在binlog中。不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。由于sql的执行是有上下文的，因此在保存的时候需要保存相关的信息，同时还有一些使用了函数之类的语句无法被记录复制。
row级别下，不记录sql语句上下文相关信息，仅保存哪条记录被修改。记录单元为每一行的改动，基本是可以全部记下来但是由于很多操作，会导致大量行的改动(比如alter table)，因此这种模式的文件保存的信息太多，日志量太大。
mixed，一种折中的方案，普通操作使用statement记录，当无法使用statement的时候使用row。此外，新版的MySQL中对row级别也做了一些优化，当表结构发生变化的时候，会记录语句而不是逐行记录。

索引

索引的优点

可以大大加快数据的检索速度，这也是创建索引的最主要的原因。通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。索引的缺点

时间方面：创建索引和维护索引要耗费时间，具体地，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，会降低增/改/删的执行效率；空间方面：索引需要占物理空间。

索引使用场景（重点）

where

order by

MySQL 回表

一、基础概念

1.什么是回表？
定义：回表是指在MySQL中，当使用非聚簇索引（也称为二级索引或辅助索引）查询数据时，由于索引中不包含查询所需的所有列，MySQL需要通过索引找到对应的主键值，然后再次访问聚簇索引（通常是主键索引）以获取完整的数据行。这个过程就称为“回表”。
原因：非聚簇索引只包含索引列和对应的主键值，而不包含其他列的数据。因此，当查询需要其他列的数据时，就必须进行回表操作。
2.为什么需要回表？
数据完整性：为了确保数据的完整性和一致性，MySQL将实际的数据行存储在聚簇索引中。
查询效率：虽然回表会增加查询的开销，但使用索引可以大大提高查询的效率。索引可以快速定位到符合条件的记录，然后通过回表获取完整的数据行。

二、深入理解

1.回表的过程

步骤一：MySQL使用非聚簇索引查找符合条件的记录，并获取对应的主键值。
步骤二：MySQL使用主键值在聚簇索引中查找完整的数据行。
示例：假设有一个用户表users，包含id（主键）、name（二级索引）和email列。执行查询SELECT * FROM users WHERE name = 'Alice';时，MySQL会先通过name索引找到符合条件的记录的主键值（如id=1），然后再通过主键索引查找id=1的完整数据行。

2.回表的影响
性能开销：回表会增加磁盘I/O操作，因为需要多次访问索引和数据表。在数据量较大的情况下，回表的开销会更加显著。
查询速度：回表会导致查询速度变慢，因为需要额外的查询步骤来获取完整的数据行。

三、示例代码与注释

-- 创建一个示例表 users
CREATE TABLE users (
id INT PRIMARY KEY,        -- 主键索引
name VARCHAR(100),         -- 二级索引
email VARCHAR(100),        -- 其他列
age INT                    -- 其他列
);

-- 为 name 列创建二级索引
CREATE INDEX idx_name ON users(name);

-- 插入一些示例数据
INSERT INTO users (id, name, email, age) VALUES
(1, 'Alice', 'alice@example.com', 30),
(2, 'Bob', 'bob@example.com', 25),
(3, 'Charlie', 'charlie@example.com', 35);

-- 执行一个需要回表的查询
-- 查询所有名字为 'Alice' 的用户的所有信息
SELECT * FROM users WHERE name = 'Alice';

-- 解释：
-- 1. MySQL 首先使用 idx_name 索引查找名字为 'Alice' 的记录，获取到主键值 id=1。
-- 2. 然后，MySQL 使用主键值 id=1 在聚簇索引中查找完整的数据行。
-- 3. 最后，返回完整的数据行，包括 id、name、email 和 age 列。

四、回表的优化

1.覆盖索引定义：覆盖索引是指索引包含了查询所需的所有列。使用覆盖索引可以避免回表操作，因为查询可以直接从索引中获取所需的数据。示例：如果查询只需要name和email列，可以创建一个包含这两列的复合索引：CREATE INDEX idx_name_email ON users(name, email);。这样，查询SELECT name, email FROM users WHERE name = ‘Alice’;就可以直接从索引中获取数据，无需回表。
2.合理设计索引

根据实际的查询需求设计合适的索引，确保索引能够覆盖查询所需的列。
避免创建过多或无效的索引，以免增加回表查询的开销。

3.使用EXPLAIN分析查询计划

使用EXPLAIN语句分析查询计划，了解查询是否存在回表操作。
根据分析结果优化查询语句和索引设计，减少回表次数。

五、总结

回表是MySQL查询优化中的一个重要概念，它涉及到使用非聚簇索引查询数据时获取完整数据行的过程。回表会增加查询的开销和性能消耗，但通过合理设计索引和使用覆盖索引等方法可以有效减少回表次数，提高查询性能。希望本文能够帮助小白读者更好地理解MySQL回表的概念和优化方法。

「真诚赞赏，手留余香」

MySQL面试-理论篇

MySQL基础篇

1.MySQL的binlog有有几种录入格式？分别有什么区别？（重点）

索引

索引的优点

索引使用场景（重点）

where

order by

MySQL 回表

一、基础概念

二、深入理解

三、示例代码与注释

四、回表的优化

五、总结

CATALOG

FEATURED TAGS

FRIENDS