MySQL -- 体系结构、索引结构

1  MySQL体系结构

英文版：

中文版：

以上图可知，MySQL的体系结构划分为以下4层：

（1）网络接入层：

（2）服务层：

（3）存储引擎层：

（4）文件系统层：

2  索引结构

2.1  索引的分类

》1 从功能逻辑上来说，分为：普通索引、唯一索引、主键索引、全文索引。

2.1.1  普通索引(NORMAL)：

        不加任何限制条件，只为提高查询效率的索引。

        想象你在图书馆想找一本书，书架上按书名的首字母排序，这样可以帮助你更快地找到书，但不限制书名是否重复。普通索引就像这种排序，它只是为了提高查找的效率，但不要求书名唯一。

        例：创建一个普通索引，用于加快 name 字段的查询速度：

CREATE INDEX idx_name ON student(name);

         查询时，MySQL 将使用该索引来加快查询速度：

SELECT * FROM student WHERE name = 'John';

2.1.2  唯一索引(UNIQUE)：

        限制该索引的值必须是唯一的，但允许有空值，一张表可以有多个唯一索引。

        现在，假设你在查找一本独特的书，这本书在图书馆只能有一本。唯一索引就像图书馆规定，每本书的 ISBN 编号必须唯一（但可以有空位，比如没有 ISBN 的书）。唯一索引确保在数据库中某个字段的值不会重复。

        创建一个唯一索引，确保 email 字段的值在表中是唯一的：

CREATE UNIQUE INDEX idx_email ON student(email);

         唯一索引不仅提升查询速度，还会强制执行唯一性约束。

2.1.3  主键索引：

        一种特殊的唯一索引，增加了不为空的约束，一张表最多只有一个主键索引。

        主键索引就像图书馆的图书编号系统，每本书都有一个唯一的编号，并且每本书必须有一个编号，不能为空。主键索引要求数据唯一且不能为空，相当于确保每条数据都有明确的“身份”。

        通常主键索引是在创建表时定义的，并要求该字段的值是唯一且不能为空：

CREATE TABLE student (
    id INT AUTO_INCREMENT,
    name VARCHAR(255),
    PRIMARY KEY (id)  -- 主键索引
);

         MySQL 自动为主键字段 id 创建索引。

2.1.4  全文索引(FULLTEXT)：

        全文索引 (FULLTEXT) 是 MySQL 中用于加快文本数据字段（例如 TEXT、VARCHAR）的搜索效率的索引。它与普通的索引不同，专门用于大文本数据的搜索，特别是在包含大量文字描述的字段中通过关键词进行快速搜索。

        （1）全文索引适用场景

        全文索引主要用于处理大量文本内容的字段，例如：博客文章内容、产品描述、用户评论

        它通过分词和关键词匹配的方式进行搜索，而不是简单的字符串匹配，因此适合文本量较大的字段。

        （2）创建全文索引

        首先，我们在一个示例表 articles 中创建一个包含 title 和 content 字段的表，其中 content 是一个 TEXT 类型字段，存储了文章的内容。

CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255),
    content TEXT
);

         接下来，我们为 content 字段创建全文索引：

CREATE FULLTEXT INDEX idx_content ON articles(content);

        （3）使用全文索引查询

        一旦创建了全文索引，你可以使用 MATCH ... AGAINST 来进行全文搜索。下面是一个示例：

SELECT * FROM articles 
WHERE MATCH(content) AGAINST('database optimization');

在这个查询中，MySQL 会使用全文索引来搜索 content 字段，查找所有包含关键词 "database" 和 "optimization" 的文章。这比传统的 LIKE 模式要高效得多，尤其是在处理大文本时。

以上，MySQL 使用全文索引搜索 content 字段，只会查找同时包含关键词 "database" 和 "optimization" 的文章。也就是说，"database" 和 "optimization" 是被分开的两个关键词，而不是作为一个短语。

如果你想查找包含确切短语 "database optimization" 的内容，可以将查询方式修改为以下布尔模式的查询：

SELECT * FROM articles 
WHERE MATCH(content) AGAINST('"database optimization"' IN BOOLEAN MODE);

        （4）布尔模式的全文搜索

你还可以使用布尔模式进行更加复杂的查询，允许你使用诸如 +、- 这样的符号来控制关键词的强制包含或排除：

SELECT * FROM articles
WHERE MATCH(content) AGAINST('+database -optimization' IN BOOLEAN MODE);

在这个查询中：

• +database 表示搜索结果必须包含 "database"。
• -optimization 表示搜索结果不能包含 "optimization"。

        （5）全文索引与 LIKE 的区别

与 LIKE 不同，FULLTEXT 索引是基于分词和关键词的搜索，适用于大文本字段。LIKE 只能进行简单的字符匹配，效率较低，尤其是在数据量大的情况下。例如

-- 使用LIKE进行模糊查询

SELECT * FROM articles WHERE content LIKE '%fulltext%';

这种方式需要扫描整个表的 content 字段进行逐字匹配，而 FULLTEXT 索引会提前分词并优化搜索过程，从而提高效率。

》2  按照物理实现来分，分为：聚簇索引 和 非聚簇索引。

        2.1.1  聚簇索引：

        针对主键构建的索引，是一种数据存储方式，表的数据行都存放在索引树的叶子页中。

        比方：想象你在图书馆查找一本书的某个章节，而这本书的目录（索引）不仅告诉你章节的页码，还直接包含了那一章的全部内容。也就是说，目录和内容是放在一起的。你查找到目录后，马上就能阅读那一章内容，不需要再去找别的地方。

        SQL 例子： 在 InnoDB 存储引擎中，主键就是聚簇索引。比如我们有一个用户表 users，主键是 id，这个 id 索引不仅指向用户记录，还包含了用户的完整信息：

CREATE TABLE users (
    id INT PRIMARY KEY,  -- 主键，也就是聚簇索引
    name VARCHAR(100),
    email VARCHAR(100)
);

         当你执行查询 id=1 的用户时：

SELECT * FROM users WHERE id = 1;

        MySQL 通过聚簇索引（主键索引）直接找到 id=1 的这行数据，而且这行数据就存储在索引的叶子节点中。所以，它很快就能返回完整的用户信息，而不需要再额外查找其他地方的数据。

        2.1.2  非聚簇索引：

        针对非主键构建的索引，它不保存完整记录，且按照索引列排序。

        比方：次你在图书馆的目录上查找一本书的页码，但目录不会给你这本书的内容，它只告诉你这本书在哪个书架和位置。你必须拿着这个信息，去那个书架取下这本书，然后才能阅读内容。也就是说，目录和内容是分开的，目录只是指向内容的一个"指针"。

        SQL 例子： 假设我们在 users 表的 name 字段上创建一个非聚簇索引

CREATE INDEX idx_name ON users(name);

         当你执行查询 name='Alice' 的用户时：

SELECT * FROM users WHERE name = 'Alice';

        MySQL 会通过 idx_name 索引找到匹配的 name 对应的用户记录的位置。这个位置是存储在聚簇索引中的行，也就是说它必须通过 name 索引找到指向的 id，再用 id 去聚簇索引中获取完整的数据行。

• 聚簇索引 就像一本书的目录和内容结合在一起，查找某一条记录时，索引直接包含完整的数据，不需要再做额外查找。
• 非聚簇索引 则像图书馆目录，它只告诉你记录在数据库中的位置，必须再根据位置去查找完整的记录。

》3 按照作用字段个数来分，分成：单列索引 和 联合索引。

        2.1.1  单列索引 (Single-column Index)

        比方：想象你在图书馆查找书籍，图书馆有一个单独的目录，按照书名的首字母排列。当你想查找一本书时，你只需要根据书名的首字母查找这个目录。这就是单列索引——图书馆为每本书的单一特征（书名）建立的索引。

        定义： 单列索引是针对表中的一个字段创建的索引。可以是普通索引、唯一性索引或全文索引。单列索引只根据一个字段进行查询优化。

        SQL 例子： 假设我们有一个用户表 users，我们可以为 name 字段创建一个单列索引：

CREATE INDEX idx_name ON users(name);

        当你查询用户名字为 Alice 的记录时，MySQL 可以使用这个单列索引快速查找：

SELECT * FROM users WHERE name = 'Alice';

        这个查询会使用 idx_name 单列索引来加快查询速度。

        2.1.2  多列索引 (Multi-column Index)

        比方：现在，想象图书馆有一个复杂的目录，按书名、作者和出版年份的组合排列。如果你知道这三个信息中的前两个（比如书名和作者），你就可以快速找到这本书。这就是多列索引——图书馆为书籍的多个特征建立的组合索引。

        定义： 多列索引是在表的多个字段上创建的组合索引。你可以通过这几个字段组合进行查询优化，但必须遵循“最左前缀原则”，即只有当查询条件中包含组合索引的第一个字段时，索引才会被使用。

        SQL 例子： 假设我们在 users 表的 id、name 和 gender 字段上创建一个多列索引：

CREATE INDEX idx_id_name_gender ON users(id, name, gender);

以下查询可以利用这个多列索引进行优化，因为它包含了第一个字段 id：

SELECT * FROM users WHERE id = 1 AND name = 'Alice';

但是，如果你只查询 name 或 gender 而没有 id，则不会使用该索引：

SELECT * FROM users WHERE name = 'Alice';  -- 不使用 idx_id_name_gender 索引

最左前缀原则：

  多列索引的使用必须遵循“最左前缀原则”，即只有当查询条件包含了索引定义的第一个字段时，索引才会被使用。例如，在 idx_id_name_gender 索引中，必须在查询中包含 id 才能使用这个索引。如果你查询的条件是从 name 或 gender 开始，而没有包含 id，MySQL 就无法使用这个索引。

总结：

• 单列索引：针对单个字段创建的索引，用于加速基于该字段的查询操作。一个表可以有多个单列索引，每个索引只作用于一个字段。
• 多列索引：在多个字段上创建的组合索引，用于优化基于多个字段的查询。它遵循“最左前缀原则”，即只有当查询条件包含索引的第一个字段时，才会使用该索引。

        不同的存储引擎支持的索引类型也不一样

• InnoDB：支持 B+ tree、Full-text 等索引，不支持 Hash 索引
• MyISAM：支持 B+ tree、Full-text 等索引，不支持 Hash 索引
• Memory：支持 B+ tree、Hash 等索引，不支持 Full-text 索引

3  索引的设计原则

        适合创建索引的情况

3.1  频繁作为 WHERE 条件的字段

        某个字段在SELECT、UPDATE 或 DELETE 语句的 WHERE 条件中经常被使用到，那么就需要给这个字段创建索引了。尤其是在数据量大的情况下，创建普通索引就可以大幅提升数据查询的效率。

3.2 经常 GROUP BY 和 ORDER BY 的列

3.2.1 以下先介绍一下GROUP BY 和 ORDER BY

---

 （1）GROUP BY 子句

        GROUP BY 子句的作用是将查询结果中具有相同值的行进行分组，并对每个分组进行计算。通常，GROUP BY 与聚合函数一起使用，例如 COUNT()、SUM()、AVG()、MAX() 和 MIN()，以对分组后的数据进行统计。

功能：

• 将数据按照指定的列进行分组。
• 每个分组内可以应用聚合函数，执行统计操作。

使用场景： 假设我们有一张员工薪资表 employees，表结构如下：

        我们可以使用 GROUP BY 子句对不同部门的薪资进行汇总，求每个部门的平均薪资：

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

        执行结果为：

 工作原理：

1. GROUP BY 先将所有行按指定的列（这里是 department）进行分组。
2. 然后，聚合函数 AVG(salary) 会计算每个部门的平均工资。

注意事项：

• GROUP BY 中的列必须出现在 SELECT 子句中，除非该列是通过聚合函数计算得出的。
• 如果查询中包含非聚合列，则必须在 GROUP BY 中列出这些列。

---

（2）ORDER BY 子句

        ORDER BY 子句的作用是对查询结果进行排序。你可以根据一个或多个列进行排序，默认是升序（ASC），如果需要降序，可以使用 DESC。

功能：

• 对查询结果集进行排序，按指定的列排列。
• 可以按多列进行排序，且可以对每列分别指定升序或降序。

使用场景： 假设我们有一张学生成绩表 students，表结构如下：

 我们可以使用 ORDER BY 子句对学生的成绩进行降序排列：

SELECT name, score
FROM students
ORDER BY score DESC;

        执行结果为：

工作原理：

• ORDER BY 根据 score 列对查询结果进行排序。
• DESC 指定降序排列，最大的分数在最上面。

多列排序： 如果需要按多列排序，可以指定多个列。例如，我们按部门排序，再按工资升序排列：

SELECT name, department, salary
FROM employees
ORDER BY department ASC, salary ASC;

执行结果为：

注意事项：

• ORDER BY 可以应用于查询中的任何列，无论该列是否出现在 SELECT 子句中。
• 默认情况下，ORDER BY 是按升序排列，如果需要降序必须显式指定 DESC。

---

（3）GROUP BY 和 ORDER BY 结合使用

        这两个子句可以结合使用，通常的流程是先使用 GROUP BY 对数据进行分组，计算每个分组的聚合值，再使用 ORDER BY 对结果进行排序。

        示例： 在前面的员工薪资例子中，我们现在不仅要按部门分组计算平均工资，还要按平均工资进行降序排列：

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
ORDER BY avg_salary DESC;

执行结果为：

 工作流程：

1. GROUP BY 将员工按 department 分组，并计算每个部门的平均工资。
2. 然后 ORDER BY 按计算得出的 avg_salary 进行降序排序。

---

        索引就是让数据按照某种顺序进行存储或检索，因此当我们使用 GROUP BY 对数据进行分组查询，或者使用ORDER BY对数据进行排序的时候，就需要对 分组 或者 排序 的字段进行索引。如果需要索引的列有多个，那么可以在这些列上建立 组合索引。

        例1：当查询只涉及到一个字段的 GROUP BY 或 ORDER BY 时，可以为该字段创建索引。例如，以下 SQL 语句使用 GROUP BY 对 student_id 进行分组：

SELECT student_id, COUNT(*) AS num 
FROM student_info 
GROUP BY student_id;

创建索引：为提高查询速度，我们可以为 student_id 字段创建索引。这种方式也适用于 ORDER BY 操作。

CREATE INDEX idx_student_id ON student_info(student_id);

        例2：当查询中涉及多个字段时，比如同时有 GROUP BY 和 ORDER BY，可以考虑创建联合索引。

SELECT student_id, COUNT(*) AS num 
FROM student_info 
GROUP BY student_id 
ORDER BY create_time DESC;

创建索引：如果你有多个查询条件，例如同时使用 GROUP BY 和 ORDER BY，可以创建一个联合索引。例如，针对 student_id 和 create_time，可以使用以下语法创建联合索引：

CREATE INDEX idx_student_id_create_time ON student_info(student_id, create_time DESC);

---

3.3  经常DISTINCT的列

DISTINCT 用来在查询结果中移除重复的记录，确保返回的每一行数据都是唯一的。例如：

例：你有以下表

        当你执行以下 SQL 语句：

SELECT DISTINCT student_id FROM student_info;

        结果将是：

这个查询只会返回 student_id 列中的唯一值，不包含其他列。

        如果你想获取多个列的唯一组合，例如 student_id 和 class_id 的组合，可以执行类似这样的查询：

SELECT DISTINCT student_id, class_id FROM student_info;

        结果将是：

这里的 DISTINCT 会作用于 (student_id, class_id) 的组合，确保每一行的 student_id 和 class_id 组合都是唯一的。

---

3.4  多表 JOIN 连接操作时，创建索引注意事项

---

        JOIN 的作用是在 SQL 查询中，用来将两个或多个表根据某个条件进行关联，从而获取这些表中的相关数据。

        常见的 JOIN 类型：

        （1）INNER JOIN：只返回两个表中匹配的记录。未匹配的记录不会出现在结果中。

SELECT * FROM orders 
INNER JOIN customers ON orders.customer_id = customers.customer_id;

        作用：获取两个表中满足连接条件的共有数据。

        （2） LEFT JOIN (LEFT OUTER JOIN)：返回左表的所有记录，即使右表中没有匹配的记录也会返回，右表中没有匹配时显示 NULL。

SELECT * FROM orders 
LEFT JOIN customers ON orders.customer_id = customers.customer_id;

       作用：获取左表的所有数据以及右表的匹配数据，不匹配的右表数据为 NULL。

       例：假设你有两个表，orders 表存储了订单信息，customers 表存储了客户信息。

orders 表：

customers 表：

        现在，我们想知道每个订单对应的客户信息，但有些订单可能没有客户记录。

        当你执行以下 SQL 语句：

SELECT * FROM orders 
LEFT JOIN customers ON orders.customer_id = customers.customer_id;

        （3）RIGHT JOIN (RIGHT OUTER JOIN)：返回右表的所有记录，即使左表中没有匹配的记录，左表没有匹配时显示 NULL。

SELECT * FROM orders 
RIGHT JOIN customers ON orders.customer_id = customers.customer_id;

        （4）FULL JOIN (FULL OUTER JOIN)：返回左表和右表中的所有记录，无论是否匹配，未匹配的部分显示为 NULL。

SELECT * FROM orders 
FULL JOIN customers ON orders.customer_id = customers.customer_id;

作用：获取两个表中的所有数据，无论是否匹配，未匹配的部分以 NULL 填充。

---

1. 首先，连接表的数量尽量不要超过 3 张，因为每增加一张表就相当于增加了一次嵌套的循环，数量级增长会非常快，严重影响查询的效率。
2. 其次，对 WHERE 条件创建索引，因为WHERE才是对数据条件的过滤。如果在数据量非常大的情况下，没有WHERE条件过滤是非常可怕的。
3. 最后，对用于连接的字段创建索引。并且该字段在多张表中的类型必须一致，如果用上函数转化的话，索引会失效。

---

3.5  列的数据类型应该尽量小

使用较小的数据类型有助于提升数据库性能，尤其是对主键来说：

1. 查询更快：数据类型越小，比较操作越快。
2. 减少索引空间：小数据类型占用的索引空间少，可以在一个数据页内存放更多记录，减少磁盘 I/O。
3. 主键优势：主键越小，聚簇索引和二级索引的存储空间越少，提升查找和读写效率。

---

3.6  使用字符串前缀创建索引

当字符串列很长时，直接索引有两个问题：

1. 比较速度慢。
2. 占用存储空间大。

        可以使用前缀索引，即只索引字符串的前几位，节省空间并加快查询速度。虽然不能精确定位，但可以缩小范围，再通过主键回表查询完整记录。

        散列度（选择性）计算：

        为了找到合适的前缀长度，可以计算散列度。散列度越接近 1，表示前缀索引的效果越好。

SELECT COUNT(DISTINCT LEFT(column_name, index_length)) / COUNT(*) 
FROM table_name;

        假设我们有一个用户地址表，存储了很多用户的地址信息，像这样：

        我们想对 address 列建立索引，但地址字符串很长，比较慢且占用大量存储空间。为了优化，我们可以使用前缀索引，只截取 address 列的前几个字符进行索引。

CREATE INDEX index_name ON table_name(column_name(prefix_length));

其中：

1. index_name 是你为索引起的名字。
2. table_name 是表的名称。
3. column_name 是你要为其创建前缀索引的列。
4. prefix_length 是你要索引的字符数长度。

        示例：

        假设我们有一个 users 表，表中有一个 address 列存储用户地址。如果我们决定为 address 列的前 6 个字符创建索引，可以这样做：

CREATE INDEX idx_address_prefix ON users(address(6));

        散列度的作用：

        我们要选择一个合适的前缀长度，确保索引能够尽量唯一。散列度表示前缀索引的唯一性，计算方式是截取前缀后不同值的数量占总行数的比例。越接近 1，说明前缀的唯一性越强。

        散列度计算的示例：

        假设我们试着只截取地址的前 3 个字符：

SELECT COUNT(DISTINCT LEFT(address, 3)) / COUNT(*) FROM users;

这个查询会告诉我们：截取前 3 个字符后，有多少个不同的地址。

• 如果结果接近 1，说明前 3 个字符的唯一性高，前缀索引效果好。
• 如果结果远低于 1，说明前缀太短，导致很多地址前 3 个字符是一样的（例如很多地址都以 "123" 开头），索引效果不好。

理解：
        短前缀（比如前 3 个字符）可能导致很多地址看起来相同，比如 123 Apple Street 和 124 Apple Street，这样前缀索引无法很好地区分这些记录。
        长前缀（比如前 6 个字符）可能能更好地区分地址，从而提高查询效率。
通过计算散列度，我们就能判断应该截取多长的前缀来保证索引的唯一性和效率。

---

3.7  区分度高(散列性高)的列适合作为索引

        当选择索引列时，区分度高（散列性高）的列更适合作为索引，因为它能更有效地帮助你快速查找数据。下面是对该概念的简化解释：

（1）什么是基数？

        基数指的是某一列中不重复值的个数。例如：

• 列中的值为 2, 5, 8, 2, 5, 8, 2, 5, 8，虽然有 9 条记录，但不重复的值只有 3 个，所以基数是 3。

（2）为什么区分度高的列适合作为索引？

        索引的目的是快速定位特定的数据。如果列中的值非常集中（基数很小，比如性别字段），索引的作用会减弱，因为很多记录会有相同的值，查询时还是要扫描大量相同值的数据。

        反之，如果列中的值很分散（基数大、区分度高），索引可以帮助快速锁定唯一的值，这样的列更适合作为索引。例如唯一性字段（如身份证号）非常适合创建索引。

（3）如何计算列的区分度？

        你可以使用以下 SQL 语句来计算某个列的区分度

SELECT COUNT(DISTINCT 列名) / COUNT(*) FROM 表名;

• 结果越接近 1，说明该列的区分度越高，适合建立索引。
• 如果区分度超过 33%（0.33），通常就可以认为是比较高效的索引。

---

3.8  使用最频繁的列放到联合索引的左侧

        这样也可以较少的建立一些索引。同时，由于"最左前缀原则"，可以增加联合索引的使用率。

3.9  在多个字段都要创建索引的情况下，联合索引优于单值索引

        当多个字段都需要索引时，联合索引通常优于多个单值索引。联合索引可以同时对多个列进行排序和查询，减少数据库扫描的次数，提升查询性能。

4  不适合创建索引的情况

4.1  数据量小的表不适合创建索引

        如果表中的记录很少（例如少于 1000 条），则不需要创建索引。因为数据量少时，查询速度已经很快，创建索引反而可能浪费资源，甚至在某些情况下查询索引的开销大于全表扫描

4.2  避免对频繁更新的表创建过多的索引

        频繁更新的字段如果有太多索引，会增加更新的负担。每次更新数据时，数据库需要同步更新索引，这会消耗系统资源，导致更新操作变慢。建议只为必要的列创建索引，并尽量减少索引中的字段数量。

4.3  有大量重复数据的列不适合建立索引

        对于大量重复数据的列（如性别字段），索引无法发挥效果，因为它无法有效区分数据。反而会因为维护索引而增加开销。例如，学生表中的 gender 字段只有 "男" 和 "女" 两种值，建立索引不但不会加快查询，还会拖慢更新操作。

5  限制索引的数量

        在实际工作中，我们也需要注意平衡，索引的数目不是越多越好。我们需要限制每张表上的索引数量，最好单张表索引数量不超过6个。原因：

1. 每个索引都需要占用 磁盘空间，索引越多，需要的磁盘空间就越大。
2. 索引会影响INSERT、DELETE、UPDATE等语句的性能，因为表中的数据更改的同时，索引也会进行调整和更新，会造成负担。
3. 优化器在选择如何优化查询时，会根据统一信息，对每一个可以用到的 索引来进行评估，以生成出一个最好的执行计划，如果同时有很多个索引都可以用于查询，会增加MySQL优化器生成执行计划时间，降低查询性能.