好的，这张图片介绍了数据库中索引的逻辑分类。您之前的图片（B+树、页结构）讲解的是索引的物理数据结构，而这张图讲解的是这些物理结构在数据库中的不同应用和功能。
这是一个非常核心的知识点，我来为您详细解析和扩展。
一、 深入解析图片中的知识点
图片将索引分为四类：主键索引、普通索引、唯一索引、全文索引。
6.1 主键索引 (Primary Key Index)
-
原文： "当在一个表上定义一个主键
PRIMARY KEY时，InnoDB使用它作为聚集索引。" -
核心知识点：
-
PRIMARY KEY(主键)： 是一种约束（Constraint）。它有两个特性：唯一性（Unique）和非空性（Not Null）。一个表最多只能有一个主键。 -
InnoDB引擎： 这是MySQL 5.5版本后默认的存储引擎。 -
聚集索引 (Clustered Index)： 这是
InnoDB引擎对主键索引的物理实现方式。
-
-
什么是聚集索引？
-
这完美地衔接了您之前关于B+树的图片。
-
在
InnoDB中，聚集索引的B+树的叶子节点中，存储的不是一个指针，而是该键对应的“完整行数据”（Data Rows）。 -
可以理解为，“数据文件本身就是按主键顺序组织的一个B+树”。索引和数据是“聚集”存储在一起的。
-
-
原文： "推荐为每个表定义一个主键。...则添加一个自增列。"
-
核心知识点： 这是一个数据库设计最佳实践。
-
为什么推荐？ 因为
InnoDB表必须有一个聚集索引。 -
选择顺序：
-
如果你定义了
PRIMARY KEY，它就是聚集索引。 -
如果你没有
PRIMARY KEY，InnoDB会寻找第一个所有列都NOT NULL的UNIQUE索引作为聚集索引。 -
如果两者都没有，
InnoDB会自动在内部生成一个隐藏的、6字节长的ROW_ID（称为GEN_CLUST_INDEX），并用它作为聚集索引。
-
-
为什么推荐“自增列”？
-
使用自增ID（如
AUTO_INCREMENT）作为主键，新插入的数据（id=1, 2, 3...）在B+树上是顺序追加的。 -
这只需要在B+树的最后一个叶子页上进行写入，当页满了就新开一个页。这种“追加写”的效率极高。
-
反例： 如果你使用非自增的列（如UUID或身份证号）作主键，新插入的数据会随机分布在B+树的所有叶子页上。这会导致频繁的**“页分裂”（Page Split）——为了插入一个新数据，必须将一个已满的页分裂成两个页，并可能递归地影响到上层索引页。页分裂是非常昂贵**的I/O操作，会严重降低插入性能并导致磁盘碎片。
-
-
6.2 普通索引 (Normal Index)
-
原文： "最基本的索引类型，没有唯一性的限制。"
-
核心知识点：
-
这是最常见的索引，也称为**“二级索引”（Secondary Index）**或“辅助索引”。
-
它的唯一目的就是加快查询速度，允许重复值（例如
age列）。
-
-
物理实现：
-
普通索引也是一个B+树。
-
关键区别： 它的叶子节点存储的不是“完整行数据”，而是**“索引列的值”和该行数据对应的“主键值”**。
-
-
查询过程（回表）：
-
当您使用普通索引查询时（
SELECT * FROM users WHERE name = 'Alice';），数据库会：-
搜索
name索引的B+树，在叶子节点找到 'Alice'。 -
从该叶子节点中，获取 'Alice' 对应的主键ID（例如
id = 123）。 -
数据库再拿着这个主键ID（
123），去搜索**主键索引（聚集索引）**的B+树。 -
在主键索引的叶子节点找到
id = 123的数据，获取完整的行数据。
-
-
这个“先查二级索引，再查主键索引”的过程，就叫做回表（Bookmark Lookup）。
-
-
原文： "可能为多列创建组合索引，称为复合索引或组合索引。"
-
扩展知识点（复合索引）：
-
例如：
CREATE INDEX idx_a_b_c ON table (a, b, c); -
这会创建一个B+树，它在排序时是字典序的：先按
a排序，a相同再按b排序，b相同再按c排序。 -
最左前缀原则（Leftmost Prefix Principle）： 这是复合索引最重要的规则。
-
WHERE a = ?-> 能用上索引。 -
WHERE a = ? AND b = ?-> 能用上索引。 -
WHERE a = ? AND b = ? AND c = ?-> 能用上索引。 -
WHERE b = ? AND c = ?-> 不能用上索引（因为a没有被使用，B+树无法定位）。 -
WHERE a = ? AND c = ?-> 只能用上a的部分，c用不上（因为b被跳过了）。
-
-
6.3 唯一索引 (Unique Index)
-
原文： "当在一个表上定义一个唯一键
UNIQUE时，自动创建唯一索引。" -
核心知识点：
-
它也是一种约束，保证了索引列的值不允许有重复。
-
与主键的区别：
-
一个表可以有多个唯一索引，但只能有一个主键。
-
唯一索引列允许存储
NULL值（并且在大多数数据库中，NULL值可以出现多次，因为NULL不等于NULL）。
-
-
-
物理实现：
-
它本质上也是一个二级索引。其B+Tree结构与普通索引完全相同（叶子节点存储“索引列的值”和“主键值”）。
-
性能区别：
-
查询： 性能与普通索引基本一致。
-
写入（INSERT/UPDATE）： 性能略低于普通索引。因为在插入新值时，数据库必须检查该值是否已存在，以保证唯一性约束。
-
-
6.4 全文索引 (Full-Text Index)
-
原文： "基于文本(CHAR, VARCHAR或TEXT列)上创建...用于全文搜索..."
-
核心知识点：
-
这是一种完全不同类型的索引，它不是为了解决
=、>、BETWEEN等精确匹配或范围匹配。 -
它是为了解决**“文本内容搜索”**，例如在一篇文章（
TEXT列）中搜索包含“database”和“MySQL”这两个单词的文档。
-
-
为什么需要它？
-
如果不用全文索引，你只能用
WHERE content LIKE '%database%';。 -
LIKE的%开头会导致索引完全失效（无法利用B+Tree的有序性），数据库不得不进行全表扫描（Full Table Scan），在亿级数据中这是灾难性的。
-
-
物理实现：
-
它不使用B+Tree。
-
它使用的是一种称为**“倒排索引”（Inverted Index）**的数据结构。
-
倒排索引（概念）：
-
B+树（正排索引）：
文档ID->文档内容（单词列表） -
倒排索引：
单词->[包含该单词的文档ID列表]
-
-
示例：
-
"database"->[doc1, doc5, doc8] -
"MySQL"->[doc1, doc7]
-
-
当搜索
("database" AND "MySQL")时，索引会分别取出两个列表[1, 5, 8]和[1, 7]，然后对它们取交集，得到[doc1]，查询速度极快。
-
-
原文： "仅MyISAM和InnoDB引擎支持。"
-
扩展： InnoDB在MySQL 5.6版本后才开始较好地支持全文索引。在实际的生产环境中，对于大规模的全文搜索，通常会使用更专业的搜索引擎，如 Elasticsearch (ES) 或 Solr，它们提供了更强大、性能更高的倒排索引功能。
二、 SQL代码示例与总结
下面的SQL代码演示了这四种索引的创建：
SQL
-- 创建一个演示表
CREATE TABLE users (
id INT AUTO_INCREMENT, -- 自增列
username VARCHAR(50) NOT NULL,
email VARCHAR(100),
bio TEXT, -- 文本列
-- 1. 主键索引 (Primary Key)
-- InnoDB将使用 id 列创建“聚集索引”
-- 叶子节点存储 id, username, email, bio (整行数据)
PRIMARY KEY (id),
-- 2. 普通索引 (Normal Index)
-- 这是一个“二级索引”
-- 叶子节点存储 (username, id)
INDEX idx_username (username),
-- 3. 唯一索引 (Unique Index)
-- 这_也是_一个“二级索引”
-- 叶子节点存储 (email, id)
-- 并且数据库会检查 email 的唯一性
UNIQUE KEY uk_email (email),
-- 4. 全文索引 (Full-Text Index)
-- 这将创建“倒排索引”
FULLTEXT KEY ft_bio (bio)
) ENGINE=InnoDB;
-- ----------------------------
-- 查询示例
-- ----------------------------
-- 1. 走主键索引 (聚集索引)
-- 速度极快，1次B+树查找（约3-4次IO）
SELECT * FROM users WHERE id = 123;
-- 2. 走普通索引 + 回表
-- a. 查 idx_username 找到 'Alice' 对应的主键 (如 123)
-- b. 查主键索引 找到 id = 123 的整行数据
SELECT * FROM users WHERE username = 'Alice';
-- 3. 走唯一索引 + 回表
-- (查询过程同上，但写入时会检查唯一性)
SELECT * FROM users WHERE email = 'test@example.com';
-- 4. 走全文索引 (不使用 LIKE)
-- 速度快，走倒排索引
SELECT * FROM users WHERE MATCH(bio) AGAINST('MySQL database');
-- 5. 无法走索引 (全表扫描)
-- 速度极慢
SELECT * FROM users WHERE bio LIKE '%MySQL%';
这张图片是您之前所有图片知识点的核心总结，它详细解释了InnoDB存储引擎中聚集索引、非聚集索引（二级索引）、回表和索引覆盖这几个最重要的概念。
这几个概念是理解MySQL性能（尤其是SELECT语句）的基石。
一、 深入解析图片中的知识点
6.5 聚集索引 (Clustered Index)
-
原文： "与主键索引是同义词"
-
解释： 这是一个在
InnoDB引擎上下文中的狭义但正确的说法。InnoDB是一种索引组织表（Index-Organized Table, IOT），它的数据文件本身就是按照主键顺序组织的一个B+树。因此，主键索引的B+树的叶子节点就包含了完整的行数据。 -
原文： "如果没有...PRIMARY KEY，InnoDB使用第一个 UNIQUE NOT NULL 的列作为聚集索引。"
-
原文： "如果表中没有...PRIMARY KEY 或合适的 UNIQUE 索引，InnoDB会...生成...6字节的 ROW_ID 字段...使用 ROW_ID 作为索引。"
-
解释： 这是
InnoDB必须拥有一个聚集索引的选择逻辑（Fallback Logic），这个逻辑非常重要：-
首选 (Priority 1)： 表中定义的
PRIMARY KEY。 -
次选 (Priority 2)： 如果没有
PRIMARY KEY，则选择表中的第一个UNIQUE NOT NULL索引。 -
最后 (Priority 3)： 如果以上两者都没有，
InnoDB会自动、隐藏地创建一个6字节的ROW_ID（也称为GEN_CLUST_INDEX），并用它作为聚集索引。这个ROW_ID对用户是不可见的。
-
-
扩展： 这就是为什么在
InnoDB中强烈推荐使用一个AUTO_INCREMENT的INT或BIGINT作为主键。-
使用自增主键： 新插入的数据
(1, 2, 3...)总是顺序追加到B+树的最后一个叶子页。这种追加写（Append-only）的I/O效率极高，几乎没有开销。 -
使用非自增主键 (如 UUID)： 新插入的UUID是随机的，它会“插”在B+树的任意位置。这会导致频繁的页分裂（Page Split），即为了插入一条数据，必须将一个已满的页（16KB）分裂成两个页，这个I/O代价非常高，会严重降低插入性能。
-
6.6 非聚集索引 (Non-Clustered Index)
-
原文： "聚集索引以外的索引称为非聚集索引或二级索引"
-
解释： 这是
InnoDB中除主键索引外的所有其他索引（如普通索引、唯一索引）。 -
原文： "二级索引的每条记录都包含该行的主键列，以及二级索引指定的列。"
-
解释： 这是
InnoDB二级索引的核心物理结构。-
聚集索引（主键）：
叶子节点存 (主键, 完整行数据) -
二级索引（例如
name列）：叶子节点存 (name列的值, 主键的值)
-
-
为什么只存主键？
-
节省空间： 如果二级索引也存完整数据，那么每建一个索引，数据就会被复制一份，磁盘空间会爆炸。
-
数据一致性： 当你
UPDATE一行数据时（比如修改了age列），InnoDB只需要更新聚集索引（主键B+树）中那一行数据即可。所有的二级索引（name索引、email索引等）完全不需要被修改，因为它们只存储了主键，主键没有变，它们的引用就依然有效。
-
-
原文： "InnoDB使用这个主键值来搜索聚集索引中的行，这个过程称为回表查询"
-
解释： 这是二级索引查询的标准流程，也是其性能开销的来源。
-
图解流程 (以
select * from student where name = '张三';为例)：-
步骤1 (查二级索引)：
-
数据库搜索
name索引（一个B+树）。 -
在
name索引的叶子节点找到'张三'。 -
从这个叶子节点读取到 '张三' 对应的主键值（如图中圈出的
10）。
-
-
步骤2 (查聚集索引 / 回表)：
-
数据库拿着这个主键
id = 10。 -
回头去搜索主键索引（另一个B+树，即数据表本身）。
-
在主键索引的叶子节点找到
id = 10的那一行。 -
读取这一行完整的全部数据（
id,name,sno,gender...）。
-
-
返回结果。
-
-
性能： 一次“回表”查询，在最坏情况下（缓存均未命中），需要
(查二级索引的I/O次数) + (查聚集索引的I/O次数)。如果两棵树都是3层，那么就是3 + 3 = 6次磁盘I/O。
6.7 索引覆盖 (Covering Index)
-
原文： "...查询列表...刚好是普通索引...的列时...就可以直接返回数据，而不用回表查询...称为索引覆盖"
-
解释： 这是“回表查询”的终极优化手段。
-
如何实现？
-
还是用
name索引（叶子节点存(name, id)）。 -
查询A (需要回表)：
SELECT * FROM student WHERE name = '张三';-
数据库需要
*(所有列)，但name索引的叶子节点只有name和id。 -
它必须回表（步骤2）去拿
sno,gender等列。
-
-
查询B (索引覆盖)：
SELECT id, name FROM student WHERE name = '张三';-
数据库需要
id和name列。 -
它搜索
name索引，在叶子节点找到了'张三'，并且在同一个地方也拿到了id。 -
它需要的 (id, name) 已经全部拿到了！
-
数据库不再需要执行“回表”的步骤，直接将
(id, name)返回。
-
-
-
性能： 这个查询（B）的I/O次数被减半了（
6次I/O 降为3次I/O），性能大幅提升。 -
复合索引的妙用：
-
假设你经常执行
SELECT name, age FROM users WHERE name = 'Alice'; -
如果你只建了
INDEX(name)，依然需要回表去拿age。 -
如果你创建一个复合索引：
CREATE INDEX idx_name_age ON users(name, age); -
它的叶子节点存储的是
(name, age, id)。 -
此时，
SELECT name, age FROM users WHERE name = 'Alice';也实现了索引覆盖！
-
二、 详细扩展说明 (代码示例)
让我们用SQL代码来固化这些概念。
SQL
-- 创建一个表，InnoDB引擎
CREATE TABLE t_student (
id INT AUTO_INCREMENT, -- 主键
name VARCHAR(50) NOT NULL,
age INT,
email VARCHAR(100),
-- 1. 聚集索引 (Clustered Index)
-- InnoDB会用 'id' 列构建B+树，叶子节点包含 (id, name, age, email)
PRIMARY KEY (id),
-- 2. 非聚集索引 (Secondary Index)
-- InnoDB会用 'name' 列构建一个*新的*B+树
-- 叶子节点包含 (name, id)
INDEX idx_name (name)
) ENGINE=InnoDB;
-- 插入数据
INSERT INTO t_student (name, age, email) VALUES ('张三', 20, 'zs@email.com');
-- (假设 '张三' 的 id 是 10)
查询场景分析：
场景 1: 走聚集索引
SQL
-- 只需要搜索主键B+树 (1次树搜索)
-- I/O = 约 3-4 次
EXPLAIN SELECT * FROM t_student WHERE id = 10;
-- Type: const/eq_ref (性能最好)
场景 2: 走二级索引 + 回表 (对应图片6.6)
SQL
-- 对应原文: SELECT * from student where name = '张三';
-- I/O = (查idx_name 约3-4次) + (查PRIMARY 约3-4次) = 约 6-8 次
EXPLAIN SELECT * FROM t_student WHERE name = '张三';
-- Type: ref (性能较好)
-- Extra: (空) (没有"Using index"
-
执行计划：
-
EXPLAIN会显示Type: ref，表示使用了非唯一索引。 -
Extra字段不会显示Using index。这暗示它拿到了索引，但还不够，需要回表。
-
场景 3: 走二级索引 + 索引覆盖 (对应图片6.7)
SQL
-- 只需要搜索idx_name B+树 (1次树搜索)
-- I/O = 约 3-4 次 (性能等同于场景1)
EXPLAIN SELECT id, name FROM t_student WHERE name = '张三';
-- Type: ref (性能较好)
-- Extra: Using index
-
执行计划：
-
EXPLAIN同样显示Type: ref。 -
但
Extra字段会明确显示Using index。 -
Using index(在MySQL中称为“覆盖索引”) 是一个强烈的积极信号，它意味着查询没有发生“回表”操作，查询所需的所有数据都从索引树中直接获取了，性能极高。
-
好的，这张图片展示了在MySQL中创建主键索引（PRIMARY KEY）的三种不同SQL语法。这是您之前几张图片中“聚集索引”概念的具体SQL实现。
我将首先识别出所有SQL代码并逐行注释，然后详细解释这些知识点。
一、 SQL代码识别与逐行注释
图片中总共涉及了三个代码块，演示了三种创建主键的方式。
代码块一：方式一
SQL
# 方式一, 创建表时创建主键
create table t_test_pk (
-- 'create table' 是SQL的数据定义语言 (DDL)，用于创建一个新表，表名为 't_test_pk'
id bigint primary key auto_increment,
-- 定义一个名为 'id' 的列
-- 'bigint': 数据类型为长整型 (8字节)，比 'int' (4字节) 范围大得多
-- 'primary key': 在列定义中 *直接* 将此列指定为表的主键
-- 'auto_increment': 指定此列为自增列，每次插入新行时，如果未指定此列的值，数据库会自动为其分配一个唯一的、递增的数字
name varchar(20)
-- 定义一个名为 'name' 的列，数据类型为可变长度字符串，最大长度为20个字符
);
-- 结束 'create table' 语句
代码块二：方式二
SQL
# 方式二, 创建表时单独指定主键列
create table t_test_pk1 (
-- 创建一个新表，表名为 't_test_pk1'
id bigint auto_increment,
-- 定义 'id' 列，类型为 'bigint' 和 'auto_increment'
-- 注意：此时 'id' 列还没有被指定为 'primary key'
name varchar(20),
-- 定义 'name' 列
primary key (id)
-- 在所有列定义之后，使用 'primary key (列名)' 这种 *表级约束* 的语法来指定 'id' 列是主键
-- 这种语法对于创建复合主键 (多列主键) 是 *必须* 的
);
-- 结束 'create table' 语句
代码块三：方式三
SQL
# 方式三, 修改表中的列为主键索引
create table t_test_pk2 (
-- 创建一个新表，表名为 't_test_pk2'
id bigint,
-- 定义 'id' 列，此时它只是一个普通的 'bigint' 列，*允许* 为 NULL
name varchar(20)
-- 定义 'name' 列
);
-- 结束 'create table' 语句
alter table t_test_pk2 add primary key (id);
-- 'alter table' 是SQL DDL语句，用于修改一个已存在的表结构
-- 'add primary key (id)'：为 't_test_pk2' 表添加一个主键约束，指定 'id' 列为主键
-- **[重要]**：这个命令会 *隐式地* 将 'id' 列修改为 'NOT NULL' (非空)
-- 如果 't_test_pk2' 表中已有数据，且 'id' 列包含 NULL 或重复值，此命令将 *执行失败*
alter table t_test_pk2 modify id bigint auto_increment;
-- 'modify' 关键字用于修改 'id' 列的定义
-- 将 'id' 列在保持 'bigint' 和 'primary key' (上一步添加的) 的基础上，*追加* 'auto_increment' 属性
-- **[重要]**：'auto_increment' 属性 *必须* 施加在一个键 (Key) 列上 (主键或唯一键)
-- 这就是为什么这句 'alter' 必须在 'add primary key' 之后执行
二、 详细知识点解析与扩展
1. 主键 (Primary Key) 的本质
-
约束 (Constraint)：
PRIMARY KEY首先是一个约束。它强制实施两条规则：-
唯一性 (Unique)： 主键列中的每一个值都必须是唯一的，不允许重复。
-
非空性 (Not Null)： 主键列中的值不允许为
NULL。
-
-
索引 (Index)： 为了能快速检查“唯一性”并快速通过主键值找到行（
WHERE id = ?），数据库会自动在主键列上创建一个索引。 -
聚集索引 (Clustered Index)： 在
InnoDB引擎中（如您前几张图所示），主键索引就是聚集索引。这意味着，数据行本身是物理地按照主键（id）的顺序存储在B+树的叶子节点上的。
2. AUTO_INCREMENT：InnoDB的最佳实践
-
为什么主键要配合
AUTO_INCREMENT？-
这与
InnoDB的聚集索引（B+Tree）结构强相关。 -
顺序插入（
AUTO_INCREMENT）： 当你使用AUTO_INCREMENT主键时，新插入的数据ID总是递增的（例如1, 2, 3, ... 1000, 1001）。 -
B+树行为： 这意味着新的数据行总是被追加（Append）到B+树的最后一个叶子页上。这是一种顺序I/O，效率极高。当一个页写满（16KB）时，
InnoDB只需再分配一个新页继续写入即可。 -
随机插入（反例：UUID）： 如果你使用
UUID或身份证号作为主键，新插入的数据是完全随机的。例如，新ID可能需要插入到B+树的“中间”某个位置。 -
B+树行为： 如果那个“中间”的叶子页已经满了，
InnoDB就必须执行**“页分裂”（Page Split）**：创建一个新页，把旧页中一半的数据移动到新页中，以便腾出空间插入新数据。这个过程可能还会递归地导致上层索引页的分裂。
-
-
结论： 页分裂是非常昂贵的I/O操作，它会导致磁盘碎片和严重的插入性能下降。因此，使用
AUTO_INCREMENT的自增主键是InnoDB的标准最佳实践。
3. 三种创建方式的对比
-
方式一 (
inline定义)：-
id bigint primary key auto_increment -
优点： 最简洁，最常用。
-
缺点： 只能用于单列主键。
-
-
方式二 (表级约束定义)：
-
primary key (id) -
优点： 语法更清晰，是创建复合主键（Composite Primary Key）的唯一方式。
-
扩展（复合主键）： 如果你需要多个列共同作为主键（例如，订单ID和商品ID），你必须使用这种语法：
SQLcreate table order_items ( order_id int, product_id int, quantity int, primary key (order_id, product_id) -- 复合主键 );
-
-
方式三 (
ALTER TABLE)：-
alter table ... add primary key ... -
优点： 提供了灵活性，允许你为已存在的、没有主键的表（例如，从CSV导入的临时表）添加主键。
-
缺点：
-
开销大： 如果表已有大量数据（例如10亿行），
ADD PRIMARY KEY是一个极其昂贵的操作。InnoDB需要重构（Rebuild）整个表，将无序的数据（堆表）重新组织成一个巨大的B+树（聚集索引）。这个过程可能需要数小时，并占用大量磁盘I/O和临时空间。 -
数据依赖： 如前所述，如果原列中有
NULL或重复值，操作会直接失败。 -
步骤繁琐： 添加
AUTO_INCREMENT必须分两步走。
-
-
4. 扩展：INT vs BIGINT
-
图片中使用了
BIGINT(8字节)。 -
INT(4字节)：-
有符号 (Signed)：-2,147,483,648 到 2,147,483,647
-
无符号 (Unsigned)：0 到 4,294,967,295 (约 42.9亿）
-
-
BIGINT(8字节)：-
无符号 (Unsigned)：0 到 18,446,744,073,709,551,615 (约 1.8 $\times$ 10<sup>19</sup>，一个几乎用不完的数)
-
-
建议： 在早些年，
INT UNSIGNED（42.9亿）被认为足够大。但在今天的互联网应用中，一个订单表、一个日志表、一个点赞表或一个消息表，都可能在几年内超过42.9亿的限制。 -
最佳实践： 除非你100%确定你的表永远达不到42.9亿行，否则默认使用
BIGINT作为主键是一个非常明智的、避免未来“数据类型溢出”灾难的防御性编程选择。从INT迁移到BIGINT的成本远高于一开始就使用BIGINT。
好的，这张图片展示了在MySQL中创建**唯一索引（UNIQUE Index）**的三种SQL语法。这是对PRIMARY KEY（主键）的一个重要补充。
一、 SQL代码识别与逐行注释
图片中总共涉及了三个代码块，演示了三种创建唯一索引（或唯一键）的方式。
代码块一：方式一
SQL
# 方式一, 创建表时创建唯一键
create table t_test_uk (
-- 'create table' DDL语句, 创建一个名为 't_test_uk' 的新表
id bigint primary key auto_increment,
-- 定义 'id' 列, 设为主键 (PRIMARY KEY)，并自动递增
-- 主键本身已经隐含了唯一 (UNIQUE) 和非空 (NOT NULL) 的特性
name varchar(20) unique
-- 定义一个名为 'name' 的列
-- 'varchar(20)': 可变长度字符串, 最大20字符
-- 'unique': 在列定义中 *直接* 将此列指定为唯一键 (Unique Key)
-- 这意味着 'name' 列中的值不允许重复 (但允许为 NULL)
);
-- 结束 'create table' 语句
代码块二：方式二
SQL
# 方式二, 创建表时单独指定唯一列
create table t_test_uk1 (
-- 创建一个新表, 表名为 't_test_uk1'
id bigint primary key auto_increment,
-- 定义 'id' 主键列
name varchar(20),
-- 定义 'name' 列 (此时尚未添加任何约束)
unique (name)
-- 在所有列定义之后，使用 'unique (列名)' 这种 *表级约束* 的语法
-- 来指定 'name' 列是唯一键
-- 这种语法是创建 *复合唯一索引* 所必需的
);
-- 结束 'create table' 语句
代码块三：方式三
SQL
# 方式三, 修改表中的列为唯一索引
create table t_test_uk2 (
-- 创建一个新表, 表名为 't_test_uk2'
id bigint primary key auto_increment,
-- 定义 'id' 主键列
name varchar(20)
-- 定义 'name' 列, 此时它只是一个普通列
);
-- 结束 'create table' 语句
alter table t_test_uk2 add unique (name);
-- 'alter table' DDL语句, 用于修改 't_test_uk2' 表的结构
-- 'add unique (name)': 为 't_test_uk2' 表添加一个唯一约束, 作用于 'name' 列
-- 数据库会自动为此约束创建一个名为 'name' (或类似) 的 *唯一索引*
-- **[重要]**：如果 'name' 列在执行此命令时已包含重复的 *非NULL* 值，此命令将 *执行失败*
二、 详细知识点解析与扩展
1. 唯一索引 (Unique Index) 的本质
-
约束 (Constraint)：
UNIQUE和PRIMARY KEY一样，首先是一种约束。它强制实施唯一性规则，即索引列中的值（或多列组合的值）必须是唯一的。 -
索引 (Index)： 为了能快速检查新插入的值是否“唯一”（是否已存在），数据库必须为此列创建一个索引。因此，“唯一约束”和“唯一索引”在功能上是绑定的。
ADD UNIQUE实际就是ADD UNIQUE INDEX的简写。
2. 唯一索引 vs. 主键索引 (UNIQUE vs. PRIMARY KEY)
这是一个极其重要的面试考点，它们是InnoDB中两种最核心的索引。
| 特性 | PRIMARY KEY (主键) | UNIQUE (唯一索引) |
| 数量 | 每个表最多 1 个 | 每个表可以有多个 |
| 空值 | 不允许 NULL (隐含 NOT NULL) | 允许 NULL |
NULL的唯一性 | (不适用) | 在MySQL InnoDB中，NULL 不等于 NULL，因此允许插入多个 NULL 值。 |
InnoDB 物理实现 | 聚集索引 (Clustered Index) B+树叶子节点存完整行数据。 | 二级索引 (Secondary Index) B+树叶子节点存**(索引列值, 主键值)**。 |
3. 物理实现：二级索引 (Secondary Index)
-
正如您之前的图片（6.6 非聚集索引）所示，
UNIQUE索引在InnoDB中是一个二级索引。 -
它的B+树结构和“普通索引”完全一样。
-
叶子节点： 存储的是
(name, id)键值对。 -
查询：
SELECT * FROM t_test_uk WHERE name = 'some_name';-
这个查询会先搜索
name索引（二级索引）的B+树，找到some_name对应的id。 -
然后再用这个
id去搜索主键索引（聚集索引）的B+树，找到完整的行数据。 -
这个过程就是回表 (Back-lookup)。
-
4. 性能影响 (vs. 普通索引)
-
SELECT(查询)： 性能几乎没有区别。对于点查询（WHERE name = ?），UNIQUE索引和普通INDEX的B+树查找效率是一样的。 -
INSERT/UPDATE(写入)：UNIQUE索引的写入性能低于普通索引。-
原因：
-
普通索引 (Normal Index)：
INSERT新数据时，InnoDB有一个Change Buffer（写缓冲）优化。它可以先把对二级索引的修改“暂存”在内存中，稍后再批量合并（Merge）到磁盘的B+树上，从而把随机I/O变成顺序I/O，大幅提高写入性能。 -
唯一索引 (Unique Index)： 无法使用 Change Buffer。当你
INSERT一个新值时，InnoDB必须立即检查这个值的唯一性。它必须马上访问二级索引的B+树（可能需要一次磁盘I/O）来确定这个值是否已存在。
-
-
结论：
UNIQUE约束牺牲了写入性能（因为它需要实时检查唯一性），换取了数据的完整性。
-
5. 复合唯一索引 (Composite Unique Index)
-
方式二 (
unique (name)) 和 方式三 (add unique (name)) 的语法是创建复合唯一索引的基础。 -
场景： 假设一个系统“不允许同一个用户（
user_id）对同一个商品（product_id）重复点赞”。 -
SQL 实现：
SQLcreate table product_likes ( id int auto_increment primary key, user_id int, product_id int, -- 复合唯一索引 -- 保证 (user_id, product_id) 的组合是唯一的 unique key uk_user_product (user_id, product_id) ); -- 允许的数据: -- (1, 10) -- OK -- (1, 20) -- OK (user_id 相同, product_id 不同) -- (2, 10) -- OK (user_id 不同, product_id 相同) -- 将失败的数据: -- (1, 10) -- Error: Duplicate entry -
这个
uk_user_product索引同时也能高效地支持WHERE user_id = ? AND product_id = ?的查询。
好的，这张图片展示了在MySQL中创建**普通索引（Normal Index）**的三种SQL语法。这是最常见的一种索引，它唯一的目的就是加速查询。
与PRIMARY KEY和UNIQUE不同，普通索引没有任何约束（如唯一性或非空性），它允许列中包含重复的值。
一、 SQL代码识别与逐行注释
图片中总共涉及了三个代码块，演示了三种创建普通索引的方式。
代码块一：方式一
SQL
# 方式一, 创建表时指定索引列
create table t_test_index (
-- 'create table' DDL语句, 创建一个名为 't_test_index' 的新表
id bigint primary key auto_increment,
-- 'id' 列, 主键 (PRIMARY KEY)，InnoDB将用它作为聚集索引
name varchar(20) unique,
-- 'name' 列, 唯一键 (UNIQUE)，InnoDB将为它创建一个二级索引
sno varchar(10),
-- 'sno' 列, 一个普通的 varchar 列, 此时没有索引
index(sno)
-- 在所有列定义之后，使用 'index(列名)' 语法为 'sno' 列创建一个普通索引
-- 这是一个 *二级索引*
-- MySQL 将自动为这个索引命名, 默认的索引名通常就是列名 'sno'
);
-- 结束 'create table' 语句
代码块二：方式二
SQL
# 方式二, 修改表中的列为普通索引
create table t_test_index1 (
-- 创建一个新表 't_test_index1'
id bigint primary key auto_increment,
-- 'id' 列, 主键 (聚集索引)
name varchar(20),
-- 'name' 列, 普通列
sno varchar(10)
-- 'sno' 列, 普通列 (在此表创建时, 它还没有索引)
);
-- 结束 'create table' 语句
alter table t_test_index1 add index (sno);
-- 'alter table' DDL语句, 用于修改 't_test_index1' 表的结构
-- 'add index (sno)': 为 'sno' 列添加一个普通索引
-- 索引名将默认为 'sno'
-- 这是一种在表已存在后, "打补丁" 式地添加索引的常用方法
代码块三：方式三
SQL
# 方式三, 单独创建索引并指定索引名
create table t_test_index2 (
-- 创建一个新表 't_test_index2'
id bigint primary key auto_increment,
-- 'id' 列, 主键 (聚集索引)
name varchar(20),
-- 'name' 列, 普通列
sno varchar(10)
-- 'sno' 列, 普通列 (在此表创建时, 它还没有索引)
);
-- 结束 'create table' 语句
create index index_name on t_test_index2(sno);
-- 'create index' DDL语句, 这是专门用于创建索引的SQL命令, 提供了更丰富的选项
-- 'index_name': *显式地* 为这个新索引指定一个名字 (而不是使用默认的 'sno')
-- 'on t_test_index2(sno)': 指定这个索引是创建在 't_test_index2' 表的 'sno' 列上
-- 这种语法在功能上与 'alter table ... add index' 相同, 但更清晰, 且允许自定义索引名
二、 详细知识点解析与扩展
1. 普通索引的本质 (二级索引)
-
物理实现： 在
InnoDB中，普通索引（如sno索引）和UNIQUE索引一样，都是二级索引（Secondary Index）（或称非聚集索引）。 -
B+树结构： 它是一个B+树。
-
叶子节点内容： 这是它与主键（聚集索引）的核心区别。
-
主键索引 (id)： 叶子节点存储
(id, 完整行数据)。 -
普通索引 (sno)： 叶子节点存储
(sno, id)。即，存储的是“索引列的值”和“该行对应的主键值”。
-
-
允许重复： 因为它没有
UNIQUE约束，所以sno索引的B+树叶子节点中，可以存储多个相同sno值的条目，例如：-
('10001', 3) -
('10001', 8) -
('10002', 5)
-
2. 普通索引的查询过程 (回表)
当您使用这个普通索引时，InnoDB会执行（可能）两个步骤：
-
查询二级索引：
-
SELECT * FROM t_test_index WHERE sno = '10001'; -
InnoDB首先搜索sno索引的B+树，在叶子节点找到sno = '10001'。 -
它找到了对应的主键
id值（例如id = 3和id = 8）。
-
-
回表 (Back-lookup)：
-
InnoDB拿着id = 3，回头去搜索主键索引（聚集索引）的B+树，找到id=3的叶子节点，并读取完整的行数据。 -
InnoDB再拿着id = 8，再次回头去搜索主键索引，找到id=8的叶子节点，并读取完整的行数据。
-
这个“先查二级索引拿到主键，再查主键索引拿到全行”的过程，就是回表。
3. 索引覆盖 (避免回表)
-
如果你的查询只需要二级索引B+树中已有的数据，
InnoDB就不会回表。 -
SELECT id, sno FROM t_test_index WHERE sno = '10001'; -
执行过程：
-
InnoDB搜索sno索引，在叶子节点找到了(sno='10001', id=3)和(sno='10001', id=8)。 -
它发现查询所需要的
id和sno列已经全部拿到了。 -
查询结束。它不需要再访问主键索引。
-
-
这种情况被称为索引覆盖（Covering Index）。使用
EXPLAIN分析这条SQL时，Extra字段会显示Using index。
4. 三种创建方式的对比
-
方式一 (
index(sno))：-
时机：
CREATE TABLE时。 -
优点： 简洁，随表一同创建。
-
缺点： 不能自定义索引名（默认为列名）。
-
-
方式二 (
alter table ... add index)：-
时机： 表已存在后。
-
优点： 动态添加索引，用于后期优化。
-
缺点： 不能自定义索引名。在生产环境的大表上执行此操作是一个重度I/O操作，可能会导致长时间的表锁定（在旧版MySQL中）或高I/O负载（在新版MySQL的"Online DDL"中）。
-
-
方式三 (
create index ... on ...)：-
时机： 表已存在后。
-
优点： 语法最清晰，允许自定义索引名（如
create index idx_sno on ...）。 -
为什么要自定义索引名？
-
可读性： 在
EXPLAIN或慢查询日志中看到idx_sno比看到sno更能清晰地传达“这是一个索引”的信息。 -
复合索引： 对于
(a, b, c)这样的复合索引，自定义一个idx_a_b_c的名字是必须的，否则MySQL会自动生成一个如a的名字，这具有很强的误导性。 -
管理：
DROP INDEX idx_sno ON t_test_index2;这样管理起来更方便。
-
-
5. 索引的代价 (The Cost of Indexes)
-
t_test_index表现在有三个B+树：-
id的聚集索引 (B+树 1，存完整数据) -
name的二级索引 (B+树 2，存(name, id)) -
sno的二级索引 (B+S树 3，存(sno, id))
-
-
索引不是免费的：
-
空间代价： 每一个二级索引都是一个B+树，都需要额外占用磁盘空间。
-
写入代价： 当你执行
INSERT、DELETE或UPDATE(更新name或sno) 时，InnoDB必须同时维护所有这三个B+树。INSERT一行数据，就必须向3个B+树中都插入条目。
-
-
结论： 索引是
SELECT（读）和INSERT/UPDATE/DELETE（写）之间的一种权衡（Trade-off）。索引会加速读，但会减慢写。因此，只应为真正需要（WHERE/JOIN/ORDER BY中频繁使用）的列创建索引。
好的，这张图片展示了在MySQL中创建复合索引（Composite Index）（也称组合索引）的三种SQL语法。这是InnoDB中一种极其重要的索引类型，是SELECT性能优化的关键手段。
一、 SQL代码识别与逐行注释
图片中总共涉及了三个代码块，演示了三种创建复合索引的方式。
代码块一：方式一
SQL
# 方式一, 创建表时指定索引列
create table t_test_index4 (
-- 'create table' DDL语句, 创建一个名为 't_test_index4' 的新表
id bigint primary key auto_increment,
-- 'id' 列, 主键 (PRIMARY KEY)，InnoDB的聚集索引
name varchar(20),
-- 'name' 列, 普通列
sno varchar(10),
-- 'sno' 列, 普通列
class_id bigint,
-- 'class_id' 列, 普通列
index (sno, class_id)
-- 'index(...)': 创建一个索引
-- (sno, class_id): 这是一个 *复合索引*。它索引了 'sno' 和 'class_id' *两列*
-- 索引名将默认为第一个列名, 即 'sno' (这在管理上可能引起混淆)
);
-- 结束 'create table' 语句
代码块二：方式二
SQL
# 方式二, 修改表中的列为复合索引
create table t_test_index5 (
-- 创建一个新表 't_test_index5'
id bigint primary key auto_increment,
-- 'id' 列, 主键
name varchar(20),
-- 'name' 列, 普通列
sno varchar(10),
-- 'sno' 列, 普通列
class_id bigint
-- 'class_id' 列, 普通列 (创建表时均无索引)
);
-- 结束 'create table' 语句
alter table t_test_index5 add index (sno, class_id);
-- 'alter table' DDL语句, 修改 't_test_index5' 表结构
-- 'add index (sno, class_id)': 添加一个 *复合索引*，包含 'sno' 和 'class_id' 两列
-- 索引名将默认为 'sno'
代码块三：方式三
SQL
# 方式三, 单独创建索引并指定索引名
create table t_test_index6 (
-- 创建一个新表 't_test_index6'
id bigint primary key auto_increment,
-- 'id' 列, 主键
name varchar(20),
-- 'name' 列, 普通列
sno varchar(10),
-- 'sno' 列, 普通列
class_id bigint
-- 'class_id' 列, 普通列
);
-- 结束 'create table' 语句
create index index_name on t_test_index6 (sno, class_id);
-- 'create index' DDL语句, 专门用于创建索引
-- 'index_name': *显式地* 为这个新索引指定一个名字 (例如 'idx_sno_classid' 会更规范)
-- 'on t_test_index6 (sno, class_id)': 指定索引创建在 't_test_index6' 表的 (sno, class_id) 列上
-- 这是创建复合索引 *最推荐* 的方式, 因为它允许清晰地命名
二、 详细知识点解析与扩展
1. 什么是复合索引？
-
定义： 复合索引是一个包含多个列的索引。
-
物理实现 (InnoDB)：
-
它本质上仍然是一个二级索引 B+树。
-
叶子节点： 存储的是
(索引列1, 索引列2, ..., 主键)。 -
在本例
index(sno, class_id)中，InnoDB的B+树叶子节点存储的是(sno, class_id, id)。
-
-
排序规则 (核心)：
-
B+树中的条目是**按字典序（lexicographical order）**排序的。
-
对于
(sno, class_id)索引：-
先严格按照
sno列的值进行排序。 -
如果
sno列的值相同，再按照class_id列的值进行排序。
-
-
这种排序规则导致了复合索引最重要的特性——最左前缀原则。
-
2. 最左前缀原则 (Leftmost Prefix Principle)
这是使用复合索引时必须遵守的规则。一个索引 idx(a, b, c) 可以被视为三个索引：
-
a -
a, b -
a, b, c
数据库（B+树）只能从最左边的列开始查找。
以 index(sno, class_id) 为例：
| 查询 (WHERE 子句) | 是否使用索引 | 索引使用情况 |
WHERE sno = '...' | 是 | 使用了 sno 部分。B+树可以按 sno 快速定位。 |
WHERE sno = '...' AND class_id = ... | 是 | 完美使用。先按 sno 定位，再在 sno 相同的结果中按 class_id 定位。 |
WHERE class_id = ... | 否 | 无法使用。B+树是按 sno 排序的，不是按 class_id 排序的。数据库不知道 class_id 在哪里，只能全表扫描。 |
WHERE sno > '...' | 是 | 使用了 sno 进行范围查找。 |
WHERE sno = '...' ORDER BY class_id | 是 (高效) | 完美使用。先按 sno 找到所有匹配行，而这些行天然就是按 class_id 排序的（因为这是索引的第二排序列），ORDER BY 操作无需额外排序（EXPLAIN 中不会出现 Using filesort）。 |
WHERE sno = '...' ORDER BY name | 否 (部分) | 索引只用于 WHERE。先按 sno 找到所有行，但这些行是按 class_id 排序的，不是按 name。数据库必须将结果加载到内存中，再进行一次排序（EXPLAIN 中会出现 Using filesort）。 |
3. 复合索引与索引覆盖 (Covering Index)
这是复合索引的第二个杀手锏。
-
回顾： 二级索引的叶子节点存储
(sno, class_id, id)。 -
场景一 (回表查询)：
SQLSELECT * FROM t_test_index6 WHERE sno = 'S100' AND class_id = 1;-
过程： 1. 搜索
index_nameB+树，找到('S100', 1)对应的id(例如id=5)。 2. 回表：拿着id=5去搜索主键B+树，找到并返回id=5的完整行数据(5, '张三', 'S100', 1)。 -
EXPLAIN中 没有Using index。
-
-
场景二 (索引覆盖)：
SQLSELECT sno, class_id FROM t_test_index6 WHERE sno = 'S100' AND class_id = 1;-
过程： 1. 搜索
index_nameB+树，找到('S100', 1)。 2. 发现查询所需要的sno和class_id已经在索引叶子节点上。 3. 无需回表，直接返回数据。 -
EXPLAIN中会显示Using index。
-
-
场景三 (索引覆盖 - 妙用)：
SQLSELECT id, sno, class_id FROM t_test_index6 WHERE sno = 'S100' AND class_id = 1;-
过程： 1. 搜索
index_nameB+树，找到('S100', 1, 5)（即sno,class_id,id）。 2. 发现查询所需要的id,sno,class_id全部都在索引叶子节点上！ -
EXPLAIN中也会显示Using index。 -
结论： 在
InnoDB中，主键id自动包含在所有二级索引的叶子节点中，因此SELECT (索引列..., 主键)这样的查询天然就是索引覆盖。
-
4. 索引列的顺序：金科玉律
-
index(sno, class_id)和index(class_id, sno)是两个完全不同的索引。 -
如何决定顺序？
-
查询频率：
(a, b)索引可以支持WHERE a和WHERE a AND b。(b, a)索引可以支持WHERE b和WHERE b AND a。看你的业务哪个WHERE子句最常用。 -
选择性（Cardinality）： 优先将选择性高（基数大，即不重复的值多）的列放在最左边。
-
例如：
sno(学号) 几乎是唯一的，而class_id(班级ID) 重复度很高。 -
index(sno, class_id)是高效的。 -
index(class_id, sno)是低效的（B+树的前几层会充满大量重复的class_id）。
-
-
好的，这张图片展示了如何在MySQL中**查看（Introspect）**一个表上已经创建的索引。这是您之前学习了如何创建索引（PRIMARY, UNIQUE, INDEX, COMPOSITE）之后的逻辑延续。
一、 SQL代码识别与逐行注释
图片中总共涉及了三种查看索引的方式，我将它们识别出来并逐行注释。
代码块一：方式一 (show keys)
SQL
# 方式一: show keys from 表名
mysql> show keys from t_test_index6\G
-- 'show keys from t_test_index6': 这是一个MySQL的特定命令 (非标准SQL)，用于显示 't_test_index6' 表上定义的所有索引的详细信息。
-- 'from' 关键字也可以用 'in' 替换，例如 'show keys in t_test_index6'，效果相同。
--
-- '\G': 这不是SQL的一部分, 而是MySQL命令行客户端的一个 *终止符*。
-- 它替代了分号 (';')。
-- '\G' (Go) 的作用是让查询结果以 *垂直* (Vertical) 格式显示，即每列数据占一行。
-- 这对于查看列非常多 (很宽) 的输出结果 (比如索引信息) 非常有用，可读性远高于水平表格。
(注：图片中的 mysql> 提示符和 ***... row ...*** 是MySQL客户端的输出，而不是SQL代码本身。)
代码块二：方式二 (show index)
SQL
# 方式二
show index from t_test_index6;
-- 'show index from t_test_index6':
-- 这条命令与 'show keys from t_test_index6' 在功能上是 *完全等价* 的, 它们是彼此的同义词。
--
-- ';': 这是标准的SQL语句终止符。
-- 它会使查询结果以 *水平表格* (Tabular) 格式显示, 即每行数据占一行。
-- 当列太多时, 水平表格的可读性会很差。
代码块三：方式三 (desc)
SQL
# 方式三, 简要信息: desc 表名;
desc t_test_index6;
-- 'desc': 这是 'DESCRIBE' 命令的缩写, 同样是MySQL的特定命令。
-- 它的 *主要* 功能是显示表的 *列结构* (Column Schema), 包括列名、数据类型、是否允许NULL等。
--
-- 't_test_index6': 目标表名。
-- 它也会在 'Key' 这一列中 *简要地* 标识出哪些列是索引的一部分。
二、 详细知识点解析 (解析 SHOW KEYS 输出)
这张图片的核心知识点在于理解 show keys from t_test_index6\G 的输出。
这个输出显示了 t_test_index6 表（来自您上一张图片）上的所有索引。上一张图我们知道，这个表有两个索引：
-
id上的PRIMARY KEY(主键索引)。 -
(sno, class_id)上的复合索引index_name。
SHOW KEYS 的输出为每一列索引字段显示一行。所以 (sno, class_id) 这个复合索引会占用两行。
让我们逐个字段解析图片中的输出：
************ 1. row ************ (主键索引)
-
Table:t_test_index6(表名) -
Non_unique:0(是否为非唯一索引？0表示false，说明这是一个唯一索引。PRIMARY KEY当然是唯一的。) -
Key_name:PRIMARY(索引的名称。主键索引的名字永远是PRIMARY。) -
Seq_in_index:1(该列在索引中的序号。id是这个索引中的第1列。) -
Column_name:id(被索引的列名。) -
Cardinality:0(基数。见下方扩展说明。0通常表示表是空的或刚创建，统计信息尚未更新。) -
Index_type:BTREE(索引类型。这印证了您之前所有图片的内容：InnoDB默认使用 B+树 索引。)
************ 2. row ************ (复合索引的第一列)
-
Table:t_test_index6 -
Non_unique:1(是否为非唯一索引？1表示true，说明这是一个普通索引（非唯一）。) -
Key_name:index_name(索引的名称。这就是您在create index index_name ...中指定的名字。) -
Seq_in_index:1(该列在索引中的序号。sno是(sno, class_id)索引中的第1列。) -
Column_name:sno(被索引的列名。) -
Cardinality:0 -
Index_type:BTREE
************ 3. row ************ (复合索引的第二列)
-
Table:t_test_index6 -
Non_unique:1(同上，属于同一个索引。) -
Key_name:index_name(同上，Key_name相同，表示它们同属index_name这一个索引。) -
Seq_in_index:2(该列在索引中的序号。class_id是(sno, class_id)索引中的第2列。) -
Column_name:class_id(被索引的列名。) -
Cardinality:0 -
Index_type:BTREE
三、 详细扩展说明
1. Cardinality (基数)：优化器的眼睛
-
Cardinality(基数) 是SHOW KEYS输出中最重要的字段之一（尽管图中是0）。 -
它是什么？ 它是索引中不重复值的估算值。
-
为什么重要？ MySQL的查询优化器会读取这个值，来判断一个索引的**“选择性”（Selectivity）**。
-
高基数 (High Cardinality)：
Cardinality值接近表的总行数。例如id列或username列。这意味着索引的选择性极高，通过索引能快速筛选掉绝大部分数据。优化器非常倾向于使用这个索引。 -
低基数 (Low Cardinality)：
Cardinality值非常低。例如gender(性别) 列（基数=2或3），is_deleted(是否删除) 列（基数=2）。这意味着索引的选择性极差，通过索引（如WHERE gender = 'male'）可能仍会返回50%的数据。 -
优化器的决策： 当基数非常低时，优化器可能会认为“走B+树索引（需要I/O）还不如直接全表扫描（反正也要读大部分数据）来得快”，从而放弃使用索引。
-
-
如何更新？
Cardinality是一个估算值，InnoDB通过采样来计算，它不是实时更新的。如果发现索引的基数不准（EXPLAIN发现优化器选错了索引），可以执行ANALYZE TABLE t_test_index6;来手动触发统计信息的重新计算。
2. desc 命令中的 Key 列
desc t_test_index6; 命令虽然图中没有显示其输出，但它的 Key 列会显示索引的简要信息：
-
PRI: 表示该列是PRIMARY KEY(主键) 的一部分。 -
UNI: 表示该列是UNIQUE(唯一索引) 的一部分。 -
MUL: 表示该列是Multiple(多重) 的一部分。-
MUL的含义是： 该列是一个非唯一索引的第一列，或者是一个复合索引的后续列，允许出现重复值。 -
在
t_test_index6的desc输出中，id列的Key会是PRI，sno列的Key会是MUL（因为它是index_name(sno, class_id)的第一列）。class_id的Key也可能是MUL（或为空，取决于MySQL版本）。
-
3. Seq_in_index 与最左前缀原则
-
Seq_in_index(1, 2, ...) 完美地在物理层面展示了**“最左前缀原则”**。 -
index_name索引的顺序是(sno [Seq=1], class_id [Seq=2])。 -
这意味着你可以高效使用这个索引来查询：
-
WHERE sno = ...(使用了第1列) -
WHERE sno = ... AND class_id = ...(使用了第1列和第2列)
-
-
你不能高效使用这个索引来查询：
-
WHERE class_id = ...(跳过了第1列sno，B+树无法定位)
-
4. 高级方式：查询 information_schema
SHOW KEYS 和 SHOW INDEX 命令是MySQL提供的“快捷方式”。
所有这些元数据（metadata）的“真正”来源是系统数据库 information_schema。
你可以通过标准SQL查询它，以获得更精细的控制（例如在程序中获取索引信息）：
SQL
-- 这条SQL查询与 'show keys from t_test_index6' 几乎等价
SELECT
TABLE_NAME,
NON_UNIQUE,
INDEX_NAME AS Key_name,
SEQ_IN_INDEX,
COLUMN_NAME,
CARDINALITY,
INDEX_TYPE
FROM
information_schema.STATISTICS
WHERE
TABLE_SCHEMA = DATABASE() -- DATABASE() 是一个返回当前数据库名称的函数
AND TABLE_NAME = 't_test_index6';
好的，这张图片展示了如何在MySQL中删除索引，特别是**删除主键索引（PRIMARY KEY）**时的一个重要陷阱和它的解决方案。
一、 SQL代码识别与逐行注释
图片中包含了MySQL命令行中的一系列操作，我将按执行顺序识别出SQL代码并逐行注释。
代码块一：删除主键的通用语法
SQL
# 语法
alter table 表名 drop primary key;
-- 'alter table': 这是一个SQL数据定义语言 (DDL) 命令，用于修改已存在表的结构。
-- '表名': 你希望修改的表的名称。
-- 'drop primary key': 这是一个 'alter table' 的子命令，用于删除该表的主键约束。
-- 注意：一个表最多只有一个主键，所以你不需要指定任何列名或索引名。
代码块二：第一次尝试删除主键 (失败)
SQL
# 示例, 删除t_test_index6表中的主键
mysql> alter table t_test_index6 drop primary key;
-- 'alter table t_test_index6': 锁定 't_test_index6' 表，准备修改。
-- 'drop primary key': 尝试删除该表的主键。
-- [命令失败]
-- 图片中的输出是: ERROR 1075 (42000): Incorrect table definition;
-- there can be only one auto column and it must be defined as a key
-- (错误的表定义；只能有一个自增列，并且它必须被定义为一个键)
代码块三：解决错误的步骤 (删除自增属性)
SQL
# 检查提示由于自增列的错误, 先删除自增属性
mysql> alter table t_test_index6 modify id bigint;
-- 'modify id bigint': 使用 'modify' 子命令来 *修改* 'id' 列的定义。
-- 通过将 'id' 列的定义 *重新声明* 为 'bigint'，而 *省略* 了 'auto_increment' 关键字...
-- ...这个命令就成功地 *移除* 了 'id' 列的 'auto_increment' 属性。
-- 此时 'id' 列仍然是主键，但不再是自增列了。
代码块四：第二次尝试删除主键 (成功)
SQL
# 重新删除主键
mysql> alter table t_test_index6 drop primary key;
-- 'alter table t_test_index6 drop primary key;':
-- 再次执行与代码块二 *完全相同* 的命令。
-- [命令成功]
-- 因为 'id' 列的 'auto_increment' 属性（即依赖项）已被移除，
-- MySQL现在允许删除主键约束。
代码块五：验证结果
SQL
# 查看结果
mysql> show keys from t_test_index6\G
-- 'show keys from t_test_index6': 一个MySQL命令，用于显示表上的所有索引。
-- '\G': MySQL命令行客户端的终止符，使输出结果垂直显示（每列一行），更易于阅读。
-- [结果分析]
-- 图片中的输出显示了 'index_name' (复合索引)，
-- 但 *不再显示* 名为 'PRIMARY' 的主键索引。
-- 这证明主键索引已被成功删除。
二、 详细知识点解析
这张图片的核心知识点是 AUTO_INCREMENT 属性与 KEY 之间的依赖关系。
-
ERROR 1075 错误详解
-
错误信息： "there can be only one auto column and it must be defined as a key" (它必须被定义为一个键)。
-
含义：
AUTO_INCREMENT（自增）这个属性不能独立存在于一个普通列上。它必须依附于一个键（KEY）。这个键可以是PRIMARY KEY(主键)，也可以是UNIQUE KEY(唯一键)。 -
为什么会出错？
-
在
t_test_index6表中，id列同时是PRIMARY KEY和AUTO_INCREMENT。 -
AUTO_INCREMENT属性依赖于PRIMARY KEY才能存在。 -
当你尝试
DROP PRIMARY KEY时，MySQL检查发现id列的AUTO_INCREMENT属性将失去它的“宿主”（KEY），变成一个非法状态。 -
因此，MySQL拒绝执行
DROP PRIMARY KEY操作，并抛出 Error 1075。
-
-
-
正确的删除步骤（两步法）
-
为了删除主键，你必须先解除这种依赖关系。
-
第一步：移除
AUTO_INCREMENT属性。-
alter table t_test_index6 modify id bigint; -
这句命令将
id列的定义修改为“一个普通的bigint列”（但它此时恰好还是主键）。AUTO_INCREMENT属性被移除了。
-
-
第二步：移除
PRIMARY KEY。-
alter table t_test_index6 drop primary key; -
现在
id列只是一个普通的bigint主键列（没有自增），没有任何其他属性依赖它。 -
DROP PRIMARY KEY命令可以安全执行。
-
-
-
show keys结果分析-
删除主键后，
show keys的结果（如图所示）只剩下了index_name。 -
index_name是您在上一张图片中创建的(sno, class_id)复合索引。 -
PRIMARY键已经不存在了。
-
三、 详细扩展说明
3.1 DROP PRIMARY KEY 的真正代价
DROP PRIMARY KEY 在 InnoDB 中是一个极其昂贵的操作，绝不仅仅是删除一个索引那么简单。
-
回顾 (聚集索引)： 在
InnoDB中，主键索引就是聚集索引。数据表本身（所有行数据）是物理地存储在主键B+树的叶子节点上的。 -
删除主键会发生什么？
-
当你
DROP PRIMARY KEY，InnoDB就失去了它的聚集索引。 -
InnoDB表不允许没有聚集索引。它必须找到一个替代品。 -
InnoDB会立即触发回退（Fallback）逻辑（参见您图片6.5的知识点）：-
a. 寻找第一个
UNIQUE NOT NULL的索引？-
在
t_test_index6表中，index_name(sno, class_id)不是UNIQUE。
-
-
b. 找不到？
InnoDB被迫在内部创建一个隐藏的6字节ROW_ID(GEN_CLUST_INDEX) 作为新的聚集索引。
-
-
全表重构 (Table Rebuild)： 为了使用这个新的
ROW_ID作为聚集索引，InnoDB必须重写（Rebuild）整个表。它会遍历旧的B+树中的所有数据，为每一行分配一个新的ROW_ID，然后将(ROW_ID, 完整行数据)写入到一个全新的B+树中。 -
所有二级索引重构 (Secondary Index Rebuild)：
-
原有的二级索引
index_name的叶子节点存储的是(sno, class_id, id)。 -
因为聚集索引（主键）从
id变成了ROW_ID，InnoDB也必须重构所有二级索引。 -
index_name的新B+树叶子节点将变为(sno, class_id, 6字节的ROW_ID)。
-
-
-
结论：
-
在
InnoDB中，DROP PRIMARY KEY会触发全表数据重构和所有二级索引重构。 -
对于一个大表（例如10亿行数据），这个操作会消耗海量的I/O和CPU，运行时间可能长达数小时，并可能锁表，导致严重的生产事故。
-
这解释了为什么“删除主键”在数据库设计中是一个需要被极度慎重对待的操作。
-
好的，这张图片是您索引系列图片的最后一部分，内容包括删除二级索引以及创建索引的注意事项（最佳实践）。这是对前面所有知识点的一个重要总结。
一、 SQL代码识别与逐行注释
图片中包含了删除二级索引的语法和示例。
代码块一：语法
SQL
# 语法
alter table 表名 drop index 索引名;
-- 'alter table': SQL数据定义语言 (DDL) 命令，用于修改一个已存在的表结构。
-- '表名': 你希望修改的表的名称。
-- 'drop index': 这是一个 'alter table' 的子命令，用于删除一个索引。
-- '索引名': 你希望删除的索引的 *具体名称* (例如 'index_name' 或 'uk_email')。
--
-- [扩展]:
-- 'drop index' 也可以用于删除 'UNIQUE' 索引, 语法完全相同。
-- 还有一个等价的语法: 'DROP INDEX 索引名 ON 表名;'
代码块二：示例和结果验证
SQL
# 示例, 删除t_test_index6表中名为index_name的索引
mysql> alter table t_test_index6 drop index index_name;
-- 'alter table t_test_index6': 锁定 't_test_index6' 表，准备修改。
-- 'drop index index_name': 明确指定删除名为 'index_name' 的索引。
-- 这个 'index_name' 是您在 'image_d7463d.png' 中创建的 (sno, class_id) 复合索引。
-- [命令成功]: Query OK, 0 rows affected (0.02 sec) ...
# 查看结果
mysql> show keys from t_test_index6\G
-- 'show keys from t_test_index6': 再次查看该表上定义的所有索引。
-- '\G': 垂直显示结果。
-- [命令输出]: Empty set (0.00 sec)
-- 'Empty set': 这是一个 *关键* 结果。它表示 "空集合"。
-- 结合您上一张图片 ('image_d5f157.jpg') 的操作 (您删除了 'PRIMARY KEY')，
-- 和当前的操作 (您删除了 'index_name')，
-- 't_test_index6' 这张表现在 *已经没有任何索引了*。
二、 详细知识点解析
7.5.2 删除其他索引 (二级索引)
-
与删除主键的对比：
-
删除主键 (DROP PRIMARY KEY)： 您不需要指定名字，因为一个表最多只有一个主键。如上一张图片所示，
InnoDB中删除主键是一个非常重的操作，它会导致全表重构，因为InnoDB必须用ROW_ID（或UNIQUE NOT NULL键）来重建聚集索引。 -
删除二级索引 (DROP INDEX index_name)： 您必须指定索引名，因为一个表可以有多个二级索引。
InnoDB只需要将这个二级索引（它本身是一个B+树）所占用的磁盘空间标记为“可重用”即可。这个操作不需要重构表数据（聚集索引），因此它的开销远小于删除主键。
-
-
Empty set的含义：-
t_test_index6表在您的一系列操作后，已经从一个“索引组织表”（id为主键）-> 变成了“ROW_ID组织表”（删除主键后，InnoDB自动创建隐藏ROW_ID作为聚集索引）-> 变成了一个没有任何二级索引的表（删除index_name后）。 -
现在的
t_test_index6在InnoDB中是一个堆表（Heap Table）。 -
性能后果： 这意味着任何针对此表的查询（
SELECT ... WHERE name = ...或WHERE sno = ...），都无法使用索引，MySQL将被迫执行全表扫描（Full Table Scan），即从头到尾读取聚集索引（ROW_ID索引）的每一个数据页来查找数据，性能会极其低下。
-
7.6 创建索引的注意事项 (最佳实践总结)
这部分内容是整个索引知识点的核心，它回答了“为什么、什么时候、什么代价”来使用索引。
-
1. “索引应该创建在高频查询的列上”
-
解释： 索引的唯一目的是加速
SELECT查询。因此，您应该优先为WHERE子句、JOIN ON子句、ORDER BY子句和GROUP BY子句中最常使用的列创建索引。 -
示例：
SELECT * FROM users WHERE email = ?，如果这个查询每秒执行1000次，那么email列必须被索引。
-
-
2. “索引需要占用额外的存储空间”
-
解释： 这回到了您的B+树和页结构（16KB Page）的知识点。
-
聚集索引（主键）： 它本身就是数据，占用空间是数据本身的大小。
-
二级索引： 每一个二级索引（
UNIQUE,INDEX）都是一个独立的B+树结构。index(name)是一个B+树，index(sno)是另一个B+树。 -
代价： 如果你的表有1个主键和5个二级索引，那么你的磁盘上就有6个B+树。这不仅会使磁盘占用量（例如）翻倍，而且会增加I/O负担。
-
-
3. “对表进行插入、更新和删除操作时...可能会影响性能”
-
解释： 这是索引的核心权衡（Trade-off）。索引加速
SELECT，但拖慢INSERT、UPDATE、DELETE。 -
INSERT： 当你INSERT一行新数据时，InnoDB必须：-
将数据行插入到聚集索引（主键B+树）的叶子节点。
-
并且，为每一个二级索引，都插入一个新条目（例如
(name, id)）到它们各自的B+树中。
-
（1个
INSERT操作 导致 6个B+树的写入操作）。
-
-
DELETE： 同理，InnoDB必须从所有6个B+树中删除对应的条目。 -
UPDATE：-
如果你
UPDATE一个非索引列：性能很高，InnoDB只需修改聚集索引中那一行数据。 -
如果你
UPDATE一个索引列（例如name）：性能很差，InnoDB不仅要修改聚集索引，还必须去修改name索引B+树（可能涉及删除旧节点、插入新节点）。
-
-
-
4. “创建过多或不合理的索引会导致性能下降”
-
解释： 这是第2点和第3点的必然结果。
-
“过多”： 索引越多，
INSERT/DELETE的写入惩罚（Write Penalty）就越重。 -
“不合理”： 指的是创建了低效的索引。
-
冗余索引（Redundant）： 例如你创建了
index(a)和index(a, b)。index(a)是完全多余的，因为index(a, b)（根据最左前缀原则）已经可以服务于WHERE a = ?的查询。 -
低选择性索引（Low Cardinality）： 例如
index(gender)。gender列只有 '男', '女' 两种值。当你查询WHERE gender = '男'时，索引会返回50%的数据。优化器会认为：与其通过索引B+树读50%的id，再“回表”50%的次数去查主键B+树，还不如我直接“全表扫描”主键B+树来得快。因此，优化器会放弃使用这个索引。
-
-
结论： 只创建“高选择性”的、能覆盖“高频查询”的、且数量“最少”的索引。
-
扫一扫
2459
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
