MySQL 索引 (Indexes)
一、索引基础与核心概念 (Index Fundamentals and Core Concepts)
1. 什么是索引 (What is an Index?)
1.1 定义与目的 (Definition and Purpose)
在数据库中，索引（Index）是一种特殊的数据结构，它独立于表的数据存储，但指向表中的数据。其主要目的是加速数据库表中数据的检索速度。可以将索引类比为一本书的目录：没有目录，你需要逐页翻阅才能找到特定章节；有了目录，你可以快速定位到章节所在的页码。同样，没有索引，数据库系统在查询数据时可能需要扫描整个表（全表扫描，Full Table Scan）；有了索引，数据库系统可以利用索引快速定位到包含所需数据的行，从而显著提高查询效率。
索引的主要目的包括:
提高查询性能：这是索引最核心、最直接的目的。通过减少需要扫描的数据量，索引能够大幅缩短 SELECT 查询的响应时间。
保证数据唯一性：唯一索引（Unique Index）和主键索引（Primary Key Index）可以确保索引列（或列组合）的值在表中是唯一的，防止插入重复数据。
加速表连接 (JOIN) 操作：在连接操作中，如果连接条件涉及的列上有索引，可以显著提高连接效率。
加速排序 (ORDER BY) 和分组 (GROUP BY) 操作：在某些情况下，如果排序或分组的列上有合适的索引，数据库可以直接利用索引的有序性来避免额外的排序步骤。
1.2 索引的优点 (Advantages of Indexes)
大幅提升查询速度：对于具有选择性（Cardinality）高的列（即列中不同值的数量多），索引的查询加速效果尤为明显。
通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性：这有助于维护数据完整性。
可以加速表和表之间的连接，特别是在实现数据的参考完整性方面：外键约束通常会依赖（或自动创建）索引。
在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间：如果排序/分组操作能利用到索引的有序性。
1.3 索引的缺点 (Disadvantages of Indexes)
尽管索引能带来巨大的性能提升，但它们并非没有代价：
创建和维护索引要耗费时间，这种时间随着数据量的增加而增加：当表中的数据量很大时，创建索引可能是一个耗时的操作。
索引需要占用物理存储空间：除了数据表占数据空间之外，每一个索引还要占一定的物理空间。如果要建立组合索引，所需的空间会更大。如果表的数据量很大，索引文件甚至可能比数据文件更快达到最大限制。
当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度（DML 操作性能下降）：INSERT, UPDATE, DELETE 操作不仅要修改数据行，还需要更新相关的索引结构，这会带来额外的开销。索引越多，DML 操作的开销越大。
不恰当或过多的索引可能反而降低性能：查询优化器在选择使用哪个索引时也会消耗时间。如果存在大量很少使用或设计不佳的索引，它们不仅占用空间、拖慢DML，还可能干扰优化器选择最优执行计划。
因此，创建索引需要权衡其带来的查询性能提升与维护开销、存储成本之间的关系。不是索引越多越好。
2. 索引的数据结构 (Data Structures for Indexes)
MySQL 支持多种索引类型，它们底层依赖于不同的数据结构。理解这些数据结构是理解索引工作原理和性能特征的关键。
2.1 B-Tree / B+Tree (B树 / B+树)
B-Tree 及其变种 B+Tree 是关系型数据库中最常用、最重要的索引数据结构。MySQL 的 InnoDB 和 MyISAM 存储引擎都主要使用 B+Tree来实现其索引（MyISAM 官方文档有时也称其为B-Tree，但其实现更接近B+Tree的某些特性，尤其是在叶子节点存储数据指针方面）。
B-Tree (平衡多路搜索树 – Balanced Multiway Search Tree):
平衡性 (Balanced): B-Tree 的所有叶子节点都位于同一深度，这保证了在最坏情况下，查找操作的时间复杂度也是对数级别的 O(log N)。
多路性 (Multiway): 每个节点可以拥有多个子节点（“多路”指的是一个节点可以存储多个键值和指向多个子节点的指针）。这与二叉搜索树（每个节点最多两个子节点）不同。多路特性使得 B-Tree 的高度相对较低，从而减少磁盘 I/O 次数。
有序性 (Sorted): 每个节点内的键值是按升序排列的。
数据存储: 在经典的 B-Tree 中，数据（或指向数据的指针）可以存储在内部节点和叶子节点中。
B+Tree (B+树):
B+Tree 是 B-Tree 的一种优化变体，更适合数据库索引的场景。
所有数据（或指向数据的指针）都只存储在叶子节点中：内部节点（非叶子节点）只存储键值（作为索引）和指向下一层节点的指针，不存储实际数据。这使得内部节点可以存储更多的键值，从而进一步降低树的高度。
叶子节点之间通过指针相连形成一个有序链表：这对于范围查询（Range Queries, 例如 WHERE age > 20 AND age < 30）和全索引扫描非常有利，因为一旦定位到范围的起始点，就可以沿着叶子节点的链表顺序访问所有符合条件的记录，而无需回溯到父节点。
内部节点的键值也会在叶子节点中冗余出现：通常是作为叶子节点中键值的上限或下限。
B+Tree 结构详解:
根节点 (Root Node): 树的顶端。
内部节点 (Internal Nodes / Branch Nodes): 存储索引键值和指向子节点的指针。键值用于指导搜索方向。例如，一个内部节点可能包含键值 [50, 100]，它会有三个子指针：一个指向键值 < 50 的子树，一个指向键值 >= 50 且 < 100 的子树，一个指向键值 >= 100 的子树。
叶子节点 (Leaf Nodes): 存储索引键值和指向实际数据行（或聚集索引键）的指针。在 InnoDB 的聚集索引中，叶子节点直接存储数据行本身。在 InnoDB 的二级索引和 MyISAM 的索引中，叶子节点存储的是指向数据行的指针（对于 InnoDB 二级索引，是主键值）。所有叶子节点都在同一层，并通过双向链表连接，以支持高效的范围查询。
InnoDB 中的 B+Tree 实现:
聚集索引 (Clustered Index): InnoDB 表是索引组织表 (Index-Organized Table, IOT)。表的数据行本身就存储在主键的 B+Tree 索引的叶子节点中。因此，每个 InnoDB 表必须有一个主键（如果用户没有显式定义，InnoDB 会选择一个唯一的非空索引，或者内部生成一个隐藏的行ID作为主键）。查询通过主键访问数据时非常快，因为找到索引键就找到了数据。
叶子节点：包含主键值和该主键对应的完整数据行。
内部节点：只包含主键值和指向下一层节点的指针。
二级索引 (Secondary Index): InnoDB 表上除了聚集索引（主键索引）之外的其他索引都是二级索引。
叶子节点：包含二级索引列的值和对应行的主键值。
内部节点：只包含二级索引列的值和指向下一层节点的指针。
当通过二级索引查找数据时，如果查询需要的列不仅仅是二级索引列和主键列，那么会先通过二级索引找到对应的主键值，然后再用这个主键值去聚集索引中查找完整的数数据行。这个过程称为回表 (Table Fetch by Primary Key / Row Lookup)。
MyISAM 中的 B+Tree 实现:
MyISAM 表的索引（包括主键索引和普通索引）都是非聚集索引。数据文件 (.MYD) 和索引文件 (.MYI) 是分开的。
叶子节点：包含索引列的值和指向数据文件中实际数据行的物理地址指针 (row pointer)。
内部节点：只包含索引列的值和指向下一层节点的指针。
通过 MyISAM 的任何索引查找数据都需要一次额外的指针跳转来获取数据行，主键索引和二级索引在结构上没有本质区别。
磁盘 I/O 与 B+Tree 高度:
数据库数据通常存储在磁盘上，磁盘 I/O 操作远慢于内存操作。索引结构的设计目标之一就是尽量减少磁盘 I/O 次数。B+Tree 的多路特性使得其高度非常低。例如，一个高度为 3 的 B+Tree，如果每个节点能存储数百个键值，就可以索引数百万甚至上亿条记录。这意味着大多数查询可能只需要 2-4 次磁盘 I/O 就能定位到数据（假设根节点和部分内部节点能被缓存到内存中）。
树的高度 (h) 大约是 ( log_m N )，其中 (N) 是键值总数，(m) 是树的阶数（一个节点能容纳的最大子节点数或键值数）。(m) 通常很大。
B+Tree 查找过程 (例如，查找键值为 K 的记录):
从根节点开始。
在当前节点中，通过二分查找或顺序查找（如果节点内键值不多）找到第一个大于等于 K 的键值，或者确定 K 应该在哪个键值区间。
根据上一步的结果，沿着对应的指针进入下一层子节点。
重复步骤 2 和 3，直到到达叶子节点。
在叶子节点中查找键值 K。如果找到，则获取对应的数据（或数据指针）。如果未找到，则记录不存在。
B+Tree 范围查询过程 (例如，查找键值在 [K1, K2] 之间的记录):
使用上述查找过程定位到第一个键值等于或大于 K1 的叶子节点和记录。
从该记录开始，沿着叶子节点的（单向或双向）链表顺序扫描，直到遇到的键值大于 K2。
收集所有满足条件的记录。
这种叶子节点链表结构使得范围查询非常高效。
2.2 哈希索引 (Hash Indexes)
哈希索引基于哈希表（Hash Table）数据结构实现。
结构与原理:
哈希函数 (Hash Function): 对索引列的值应用一个哈希函数，生成一个哈希码 (hash code)。
哈希桶 (Hash Buckets): 哈希码用于确定数据应该存放在哈希表的哪个桶（bucket）中。
指针: 哈希表的每个桶中存储的是指向实际数据行的指针（或键值对）。
哈希冲突 (Hash Collisions): 如果多个不同的键值通过哈希函数计算出相同的哈希码，就会发生哈希冲突。哈希表需要有解决冲突的机制，例如链地址法（在同一个桶内形成一个链表存储冲突的元素）或开放地址法。
适用场景:
哈希索引主要适用于精确等值查询 (Exact Equality Lookups)，例如 WHERE column = 'value'。
当哈希冲突不严重时，其查找时间复杂度可以接近 O(1)。
限制:
不支持范围查询: 因为哈希索引中的数据不是按索引列值有序存储的，所以无法有效地处理范围查询（如 WHERE column > 10）或排序操作。查询优化器不会为这类查询选择哈希索引。
不支持部分键值查找/前缀查找: 只能对完整的索引列值进行哈希。
哈希冲突可能影响性能: 如果哈希函数设计不佳或数据分布不均导致大量冲突，哈希索引的性能会急剧下降，甚至不如全表扫描。
通常只适用于内存: 在磁盘上实现高效的哈希表比较困难，因为冲突链表可能导致大量的随机 I/O。
MySQL 中的哈希索引:
MEMORY (HEAP) 存储引擎: MEMORY 表支持显式创建 HASH 索引。这是用户可以主动控制的哈希索引。
CREATE TABLE memory_hash_example ( -- 创建一个使用MEMORY引擎的表示例
id INT NOT NULL, -- ID列，非空
data VARCHAR(100), -- 数据列
INDEX idx_data_hash (data) USING HASH -- 在data列上创建一个名为idx_data_hash的哈希索引
) ENGINE = MEMORY; -- 指定存储引擎为MEMORY
INSERT INTO memory_hash_example VALUES (1, 'apple'), (2, 'banana'), (3, 'cherry'); -- 插入示例数据
-- 这个查询可以高效利用哈希索引
EXPLAIN SELECT * FROM memory_hash_example WHERE data = 'banana'; -- 查询data列等于'banana'的记录，并查看执行计划
-- EXPLAIN 的 type 列可能会显示 'ref' 或 'const' (如果优化器能确定只有一行)
代码解释:
ENGINE = MEMORY: 表存储在内存中。
INDEX idx_data_hash (data) USING HASH: 显式为 data 列创建哈希索引。
InnoDB 的自适应哈希索引 (Adaptive Hash Index – AHI):
AHI 是 InnoDB 存储引擎的一项特性，它在内部自动开启（由 innodb_adaptive_hash_index 参数控制，默认开启）。InnoDB 会监控对B+Tree索引的查找模式，如果发现某些索引页被频繁以等值查询的方式访问，InnoDB 可能会在内存中为这些热点页的键值动态构建一个哈希索引，以进一步加速这些等值查找。
AHI 对用户是透明的，不能由用户直接控制或配置其为哪些列创建。
它只作用于B+Tree索引中被频繁访问的等值查询部分。
在某些高并发、写密集或具有大量范围查询的负载下，AHI 的维护开销（如锁竞争）有时反而可能成为性能瓶颈，此时可以考虑关闭它。
可以通过 SHOW ENGINE INNODB STATUS; 的 ADAPTIVE HASH INDEX 部分查看AHI的活动情况。
2.3 R-Tree (空间索引 – Spatial Indexes)
R-Tree 及其变种（如 R*-Tree）是专门用于索引多维空间数据（如地理坐标、几何形状）的数据结构。MySQL 从 5.7.5 版本开始支持基于Boost.Geometry库的更完善的空间数据类型和空间索引，使用的是R-Tree。
结构与原理:
R-Tree 将空间对象组织在一个层次结构中，类似于B-Tree。
每个节点对应一个最小边界矩形 (Minimum Bounding Rectangle – MBR)，该MBR包围了其所有子节点代表的MBR或空间对象。
叶子节点存储指向实际空间对象（及其MBR）的指针。
查询时，通过比较查询区域与节点MBR的相交情况来剪枝搜索空间。
适用场景:
地理信息系统 (GIS): 存储和查询地图数据、点、线、多边形等。
例如：查找某个点附近的餐馆、判断一个坐标是否在某个区域内、查找两个区域是否相交。
MySQL 中的空间索引:
只适用于 GEOMETRY 类型的列（及其子类型如 POINT, LINESTRING, POLYGON 等）。
必须在 MyISAM 或 InnoDB (从MySQL 5.7.5开始，InnoDB支持空间索引) 表上创建。
使用 CREATE SPATIAL INDEX 语法创建。
支持的空间关系查询函数包括 ST_Contains(), ST_Within(), ST_Intersects(), ST_Distance() (配合索引可能有限) 等。
CREATE TABLE locations ( -- 创建地理位置表
id INT AUTO_INCREMENT PRIMARY KEY, -- ID，自增主键
name VARCHAR(100), -- 名称
geom GEOMETRY NOT NULL, -- 地理几何对象，非空 (例如，一个点 POINT)
SPATIAL INDEX idx_spatial_geom (geom) -- 在geom列上创建名为idx_spatial_geom的空间索引
) ENGINE=InnoDB; -- 使用InnoDB引擎
-- 插入一个点数据 (例如，经度-122.4194, 纬度37.7749 旧金山)
INSERT INTO locations (name, geom) VALUES ('San Francisco Office', ST_GeomFromText('POINT(-122.4194 37.7749)')); -- 插入旧金山办事处的位置点
INSERT INTO locations (name, geom) VALUES ('New York Office', ST_GeomFromText('POINT(-74.0060 40.7128)')); -- 插入纽约办事处的位置点
-- 假设有一个查询区域 (一个多边形)
SET @query_polygon = ST_GeomFromText('POLYGON((
-122.5 37.7, -122.3 37.7, -122.3 37.9, -122.5 37.9, -122.5 37.7
))'); -- 定义一个查询多边形区域
-- 查询哪些位置点在该区域内 (这个查询可以利用空间索引)
SELECT id, name, ST_AsText(geom) AS location_wkt -- 选择ID，名称，并将几何对象转为WKT文本格式
FROM locations -- 从locations表
WHERE ST_Contains(@query_polygon, geom); -- 条件：查询多边形包含位置点
代码解释:
GEOMETRY NOT NULL: 定义存储空间数据的列。
SPATIAL INDEX idx_spatial_geom (geom): 为 geom 列创建空间索引。
ST_GeomFromText('POINT(...)'): 从文本表示 (Well-Known Text, WKT) 创建几何对象。
ST_Contains(@query_polygon, geom): 空间函数，判断 @query_polygon 是否包含 geom 列中的点。空间索引可以加速这类函数的执行。
2.4 Full-Text 索引 (全文索引)
全文索引用于在大量文本数据中执行复杂的词语搜索，而不仅仅是简单的字符串匹配（如 LIKE '%keyword%'，这种方式通常无法有效利用B-Tree索引且性能低下）。
原理:
分词 (Tokenization/Parsing): 将文本内容分割成独立的词语（tokens）。
停用词处理 (Stop Word Filtering): 移除常见的、对搜索意义不大的词语（如 “the”, “a”, “is”）。MySQL有内置的停用词列表，也可以自定义。
词干提取 (Stemming – MySQL原生支持有限，通常需应用层或插件): 将词语转换为其基本形式（如 “running” -> “run”）。
构建倒排索引 (Inverted Index): 创建一个数据结构，其中键是词语，值是包含该词语的文档列表（以及可能的位置信息）。
例如：
word1 -> doc_id1, doc_id3
word2 -> doc_id2, doc_id3, doc_id5
适用场景:
搜索引擎功能：在文章、产品描述、评论等文本字段中搜索关键词。
支持自然语言查询或布尔逻辑查询。
MySQL 中的全文索引:
支持 CHAR, VARCHAR, TEXT类型的列。
可以在 MyISAM 和 InnoDB (从MySQL 5.6开始) 表上创建。
使用 CREATE FULLTEXT INDEX 语法创建。
查询时使用 MATCH() ... AGAINST() 语法。
CREATE TABLE articles ( -- 创建文章表
id INT AUTO_INCREMENT PRIMARY KEY, -- ID，自增主键
title VARCHAR(200) NOT NULL, -- 标题，非空
body TEXT NOT NULL, -- 内容，非空
FULLTEXT INDEX idx_ft_title_body (title, body) -- 在title和body列上创建名为idx_ft_title_body的全文索引
) ENGINE=InnoDB; -- 使用InnoDB引擎
INSERT INTO articles (title, body) VALUES -- 插入文章数据
('MySQL Performance Tuning', 'This article discusses various techniques for MySQL performance optimization, including indexing.'),
('Introduction to Database Indexing', 'Indexing is crucial for database speed. Learn about B-Trees and Hash indexes.'),
('Advanced SQL Queries', 'Explore complex SQL queries for data analysis.');
-- 自然语言模式搜索 (Natural Language Mode - 默认)
SELECT id, title, MATCH(title, body) AGAINST('database performance' IN NATURAL LANGUAGE MODE) AS relevance_score -- 搜索包含'database'或'performance'的文章，并计算相关性得分
FROM articles
WHERE MATCH(title, body) AGAINST('database performance' IN NATURAL LANGUAGE MODE) -- 条件：匹配关键词
ORDER BY relevance_score DESC; -- 按相关性得分降序排列
-- 布尔模式搜索 (Boolean Mode)
SELECT id, title
FROM articles
WHERE MATCH(title, body) AGAINST('+MySQL -SQL' IN BOOLEAN MODE); -- 搜索必须包含'MySQL'但不包含'SQL'的文章
-- '+' 表示必须存在, '-' 表示必须不存在, '*' 是通配符等
代码解释:
FULLTEXT INDEX idx_ft_title_body (title, body): 为 title 和 body 列联合创建全文索引。
MATCH(title, body) AGAINST('keywords' IN NATURAL LANGUAGE MODE): 执行自然语言模式的全文搜索。MySQL会计算一个相关性得分。
MATCH(title, body) AGAINST('query_string' IN BOOLEAN MODE): 执行布尔模式的全文搜索，允许使用操作符（如 +, -, *, ""等）来构建更精确的查询。
InnoDB 和 MyISAM 全文索引的差异:
事务支持: InnoDB 全文索引支持事务，MyISAM 不支持。
实现细节: 内部实现和一些特性（如配置选项、倒排索引的更新机制）有所不同。InnoDB 的全文索引与 InnoDB 缓冲池等集成得更好。
配置: InnoDB 允许更细致的配置，例如自定义停用词表可以直接通过 InnoDB 系统表配置，而 MyISAM 通常需要修改配置文件并重启。
NGram 解析器: MySQL 5.7.6 开始，InnoDB 和 MyISAM 都支持内置的 NGram 解析器，这对于没有明确词边界的语言（如中文、日文、韩文 – CJK）进行分词非常重要。
-- 创建使用 NGram 解析器的全文索引 (假设内容是中文)
-- CREATE TABLE articles_cjk (
-- id INT AUTO_INCREMENT PRIMARY KEY,
-- content TEXT,
-- FULLTEXT INDEX idx_ft_content_ngram (content) WITH PARSER ngram -- 使用ngram解析器创建全文索引
-- ) ENGINE=InnoDB CHARACTER SET utf8mb4;
-- 需要设置 ngram_token_size (例如 ngram_token_size=2 表示按两个字符一组进行分词)
-- SET GLOBAL innodb_ft_min_token_size = 2; (如果使用InnoDB)
-- SET GLOBAL ft_min_word_len = 2; (如果使用MyISAM)
-- 或者在配置文件中设置
二、索引的类型 (Types of Indexes)
MySQL 提供了多种索引类型，它们不仅仅是底层数据结构的不同，更在于其逻辑功能和在数据库设计中的应用目的。
1. 普通索引 (Normal/Non-Unique Index)
定义:
普通索引（也称为非唯一索引）是最基本的索引类型。它没有任何特殊的约束，仅仅是为了加速查询。索引列的值可以重复。一个表可以有多个普通索引。
目的:
主要目的是提高对数据的检索速度。
语法:
-- 创建表时定义普通索引
CREATE TABLE products_example ( -- 创建一个名为 products_example 的表示例
product_id INT AUTO_INCREMENT PRIMARY KEY, -- 产品ID，自增主键
product_name VARCHAR(100) NOT NULL, -- 产品名称，非空
category_id INT, -- 分类ID
supplier_id INT, -- 供应商ID
unit_price DECIMAL(10,2), -- 单价
INDEX idx_category_id (category_id), -- 在 category_id 列上创建一个名为 idx_category_id 的普通索引
KEY idx_supplier_id (supplier_id) -- KEY 是 INDEX 的同义词，在 supplier_id 列上创建普通索引
) ENGINE=InnoDB;
-- 在已存在的表上添加普通索引
CREATE INDEX idx_product_name ON products_example (product_name); -- 在 products_example 表的 product_name 列上创建名为 idx_product_name 的普通索引
-- 或者使用 ALTER TABLE
ALTER TABLE products_example ADD INDEX idx_unit_price (unit_price); -- 在 products_example 表的 unit_price 列上添加名为 idx_unit_price 的普通索引
代码解释:
INDEX idx_category_id (category_id): 在 products_example 表的 category_id 列上创建了一个名为 idx_category_id 的普通索引。
KEY idx_supplier_id (supplier_id): KEY 是 INDEX 的同义词，功能完全相同。
CREATE INDEX idx_product_name ON products_example (product_name);: 这是另一种在已存在表上创建索引的语法。
ALTER TABLE products_example ADD INDEX idx_unit_price (unit_price);: 通过 ALTER TABLE 语句添加索引。
适用场景:
经常作为查询条件（WHERE 子句）、连接条件（JOIN ON 子句）或排序依据（ORDER BY 子句）的列。
列中的值允许重复。
企业级应用示例:
在一个电商平台的 orders (订单) 表中，customer_id (客户ID) 和 order_status (订单状态) 列经常被用于查询。
CREATE TABLE orders_demo ( -- 创建一个名为 orders_demo 的订单表示例
order_id BIGINT AUTO_INCREMENT PRIMARY KEY, -- 订单ID，自增主键
customer_id INT NOT NULL, -- 客户ID，非空
order_date DATETIME NOT NULL, -- 订单日期，非空
order_status VARCHAR(20) NOT NULL, -- 订单状态，非空 (例如: 'PENDING', 'PROCESSING', 'SHIPPED', 'DELIVERED', 'CANCELLED')
total_amount DECIMAL(12,2) NOT NULL, -- 总金额，非空
shipping_address_id INT, -- 收货地址ID
-- ... 其他订单相关列 ...
INDEX idx_ord_customer_id (customer_id), -- 为客户ID创建普通索引，方便按客户查询订单
INDEX idx_ord_status_date (order_status, order_date) -- 为订单状态和订单日期创建复合普通索引，方便按状态和日期范围查询
) ENGINE=InnoDB;
-- 插入示例数据
INSERT INTO orders_demo (customer_id, order_date, order_status, total_amount) VALUES -- 插入订单数据
(101, NOW() - INTERVAL 5 DAY, 'DELIVERED', 150.00),
(102, NOW() - INTERVAL 3 DAY, 'SHIPPED', 75.50),
(101, NOW() - INTERVAL 2 DAY, 'PROCESSING', 200.00),
(103, NOW() - INTERVAL 1 DAY, 'PENDING', 50.25),
(102, NOW(), 'PENDING', 120.00);
-- 查询场景1: 查找某个客户的所有订单
EXPLAIN SELECT * FROM orders_demo WHERE customer_id = 101; -- 查询客户ID为101的所有订单，并查看执行计划
-- 优化器应使用 idx_ord_customer_id 索引
-- 查询场景2: 查找所有处于 'PENDING' 状态且在过去24小时内创建的订单
EXPLAIN SELECT * FROM orders_demo -- 查询待处理订单
WHERE order_status = 'PENDING' AND order_date >= (NOW() - INTERVAL 1 DAY); -- 条件：状态为'PENDING'且订单日期在过去24小时内
-- 优化器应能有效利用 idx_ord_status_date 复合索引 (至少是其 order_status 前缀部分)
代码解释:
idx_ord_customer_id (customer_id): 允许快速查找特定客户的订单。
idx_ord_status_date (order_status, order_date): 这是一个复合索引。
它可以高效支持基于 order_status 的查询。
对于 WHERE order_status = 'value' AND order_date > 'date_value' 这样的查询，它也能很好地工作，因为索引的前导列 order_status 被用于等值匹配，后续的 order_date 列则用于范围扫描。
2. 唯一索引 (Unique Index)
定义:
唯一索引与普通索引类似，但它增加了一个约束：索引列的值（或列组合的值，如果是复合唯一索引）必须是唯一的，允许存在多个 NULL 值（除非该列也被定义为 NOT NULL）。如果尝试插入或更新数据导致唯一索引列出现重复值（非 NULL），操作将失败。
目的:
加速查询（与普通索引相同）。
确保数据的唯一性，防止业务逻辑上不应重复的数据出现。
语法:
-- 创建表时定义唯一索引
CREATE TABLE users_example ( -- 创建一个名为 users_example 的用户表示例
user_id INT AUTO_INCREMENT PRIMARY KEY, -- 用户ID，自增主键
username VARCHAR(50) NOT NULL, -- 用户名，非空
email VARCHAR(100), -- 邮箱
phone_number VARCHAR(20), -- 电话号码
UNIQUE INDEX uidx_username (username), -- 在 username 列上创建名为 uidx_username 的唯一索引
UNIQUE KEY uidx_email (email) -- 在 email 列上创建名为 uidx_email 的唯一索引 (允许NULL，但存在的非NULL值必须唯一)
) ENGINE=InnoDB;
-- 在已存在的表上添加唯一索引
ALTER TABLE users_example ADD UNIQUE INDEX uidx_phone (phone_number); -- 在 users_example 表的 phone_number 列上添加唯一索引
代码解释:
UNIQUE INDEX uidx_username (username): 确保每个 username 都是唯一的。由于 username 列定义为 NOT NULL，所以不允许 NULL。
UNIQUE KEY uidx_email (email): 确保每个非 NULL 的 email 都是唯一的。如果 email 列允许 NULL，那么可以有多行的 email 值为 NULL，因为 NULL 在唯一性约束中不等于任何其他值（包括另一个 NULL）。
ALTER TABLE users_example ADD UNIQUE INDEX uidx_phone (phone_number);: 添加 phone_number 的唯一索引。
与主键的区别:
数量: 一个表只能有一个主键，但可以有多个唯一索引。
NULL 值: 主键列不允许为 NULL。唯一索引列默认允许 NULL 值（除非列本身定义为 NOT NULL），并且可以包含多个 NULL。
逻辑含义: 主键通常用于唯一标识表中的每一行，是行的“身份证”。唯一索引则用于确保某个业务属性的唯一性，例如用户邮箱、产品序列号等。
InnoDB 聚集索引: 在 InnoDB 中，主键索引是聚集索引。唯一索引（如果不是主键）是二级索引。
企业级应用示例:
在一个 product_skus (产品SKU库存单位) 表中，每个SKU（Stock Keeping Unit）编码必须是唯一的。同时，如果产品有条形码 (barcode)，条形码也应该是唯一的。
CREATE TABLE product_skus_demo ( -- 创建产品SKU表示例
sku_id INT AUTO_INCREMENT PRIMARY KEY, -- SKU ID，自增主键
product_id INT NOT NULL, -- 关联的产品ID，非空
sku_code VARCHAR(50) NOT NULL, -- SKU编码，非空，业务上要求唯一
barcode VARCHAR(100), -- 条形码，可能为空，但如果存在则要求唯一
attributes JSON, -- SKU属性 (例如: {'color': 'Red', 'size': 'XL'})
stock_quantity INT DEFAULT 0, -- 库存数量
UNIQUE INDEX uidx_sku_code (sku_code), -- 确保 sku_code 唯一
UNIQUE INDEX uidx_barcode (barcode) -- 确保非NULL的 barcode 唯一
-- FOREIGN KEY (product_id) REFERENCES products(product_id) -- 假设有关联到 products 表
) ENGINE=InnoDB;
-- 尝试插入数据
INSERT INTO product_skus_demo (product_id, sku_code, barcode, stock_quantity) VALUES -- 插入SKU数据
(101, 'PROD101-RED-XL', '1234567890123', 100); -- SKU 1
INSERT INTO product_skus_demo (product_id, sku_code, barcode, stock_quantity) VALUES
(101, 'PROD101-BLUE-L', '1234567890124', 50); -- SKU 2
-- 尝试插入重复的 sku_code (会失败)
-- INSERT INTO product_skus_demo (product_id, sku_code, barcode, stock_quantity) VALUES
-- (102, 'PROD101-RED-XL', '9876543210987', 70);
-- 会报错: Duplicate entry 'PROD101-RED-XL' for key 'product_skus_demo.uidx_sku_code'
-- 尝试插入重复的 barcode (会失败)
-- INSERT INTO product_skus_demo (product_id, sku_code, barcode, stock_quantity) VALUES
-- (102, 'PROD102-GREEN-M', '1234567890123', 30);
-- 会报错: Duplicate entry '1234567890123' for key 'product_skus_demo.uidx_barcode'
-- 插入 barcode 为 NULL 的记录 (允许，且可以有多个NULL)
INSERT INTO product_skus_demo (product_id, sku_code, stock_quantity) VALUES
(103, 'PROD103-NOBARCODE-S', 20); -- barcode 为 NULL
INSERT INTO product_skus_demo (product_id, sku_code, stock_quantity) VALUES
(104, 'PROD104-ANOTHERNULL-M', 25); -- barcode 也为 NULL (允许)
SELECT * FROM product_skus_demo WHERE barcode = '1234567890123'; -- 此查询会高效利用 uidx_barcode
代码解释:
uidx_sku_code (sku_code): 强制 sku_code 列的唯一性，这对于库存管理至关重要。
uidx_barcode (barcode): 强制 barcode 列的唯一性（对于非 NULL 值）。一个产品可能没有条形码（NULL），但如果它有，那么这个条形码在所有SKU中必须是唯一的。
3. 主键索引 (Primary Key Index)
定义:
主键索引是一种特殊的唯一索引，用于唯一标识表中的每一行。它具有以下特性：
唯一性: 主键列的值必须唯一。
非空性: 主键列的值不允许为 NULL。
数量: 一个表只能有一个主键。
目的:
唯一标识表中的每一行记录。
作为其他表的外键引用的目标。
在 InnoDB 中，主键索引是聚集索引，表的物理存储顺序与主键顺序一致。
语法:
-- 创建表时定义主键 (最常见)
CREATE TABLE employees_pk_example ( -- 创建一个带主键的员工表示例
employee_id INT AUTO_INCREMENT, -- 员工ID，设为自增
first_name VARCHAR(50) NOT NULL, -- 名字，非空
last_name VARCHAR(50) NOT NULL, -- 姓氏，非空
hire_date DATE, -- 入职日期
PRIMARY KEY (employee_id) -- 将 employee_id 定义为主键
) ENGINE=InnoDB;
-- 复合主键
CREATE TABLE order_items_pk_example ( -- 创建一个带复合主键的订单项表示例
order_id INT NOT NULL, -- 订单ID，非空
item_sequence_number INT NOT NULL, -- 项目序号，非空
product_id INT NOT NULL, -- 产品ID，非空
quantity INT, -- 数量
PRIMARY KEY (order_id, item_sequence_number) -- 将 order_id 和 item_sequence_number 组合定义为主键
-- FOREIGN KEY (order_id) REFERENCES orders(order_id), -- 假设关联到订单表
-- FOREIGN KEY (product_id) REFERENCES products(product_id) -- 假设关联到产品表
) ENGINE=InnoDB;
-- 在已存在的表上添加主键 (通常在表没有主键或需要修改主键时使用，但较少见于已填充数据的表)
-- ALTER TABLE some_table ADD PRIMARY KEY (column_name);
代码解释:
PRIMARY KEY (employee_id): 将单列 employee_id 设置为主键。AUTO_INCREMENT 通常与整数类型的主键一起使用，让MySQL自动生成唯一的ID。
PRIMARY KEY (order_id, item_sequence_number): 定义了一个复合主键，由 order_id 和 item_sequence_number 两列组成。这意味着 (order_id, item_sequence_number) 的组合必须在表中唯一。
InnoDB 中的聚集索引特性:
如前所述，在 InnoDB 中，主键索引决定了表中数据的物理存储顺序。数据行本身就存储在主键B+Tree的叶子节点中。
优点:
基于主键的查询非常快，因为一旦找到主键索引的叶子节点，就直接获取了数据，无需二次查找（回表）。
对于基于主键的范围查询，由于数据物理上连续存储（或接近连续），性能也较好。
缺点/考量:
主键选择: 主键的选择对 InnoDB 表的性能至关重要。理想的主键应该是短小的、有序的（例如自增整数），并且不经常被修改。
不推荐使用UUID作为主键 (无序的字符串): 虽然UUID能保证全局唯一，但它们通常是无序的字符串，作为InnoDB主键会导致B+Tree频繁的页分裂和随机I/O，插入性能差，索引碎片化严重。如果必须使用UUID，可以考虑使用有序UUID（如MySQL 8.0+的 UUID_TO_BIN(uuid, 1) 和 BIN_TO_UUID(binary_uuid, 1)）或者将其作为唯一索引，另外使用一个自增整数做主键。
避免使用经常更新的列作为主键: 更新主键值是一项非常昂贵的操作，因为它可能涉及到数据行的物理移动和所有二级索引的更新（因为二级索引存储主键值）。
二级索引的开销: InnoDB 的二级索引叶子节点存储的是主键值。如果主键很大（例如，一个很长的字符串），那么所有的二级索引也会相应地变大，占用更多存储空间，并可能降低二级索引的查询效率（因为B+Tree的每个节点能容纳的条目变少，树的高度可能增加）。
插入性能: 如果主键是随机的（如UUID），插入新行时可能需要在B+Tree的中间位置插入，导致页分裂和不连续的物理存储。自增主键通常在B+Tree的末尾插入，效率较高。
企业级应用示例:
customers 表: customer_id INT AUTO_INCREMENT PRIMARY KEY
invoices 表: invoice_id CHAR(16) PRIMARY KEY (假设发票号是业务生成的、固定长度的唯一标识符，并且需要保证其顺序性，例如基于时间或序列)
user_activity_log 表: (user_id INT, activity_timestamp TIMESTAMP(6), sequence_in_microsecond INT) PRIMARY KEY (超高频写入的日志表，可能需要非常精细的复合主键来保证唯一性和某种程度的查询局部性，但也要注意主键大小)
主键选择策略 – 代理键 vs. 自然键 (Surrogate Key vs. Natural Key):
代理键 (Surrogate Key): 一个没有业务含义、仅用于唯一标识行的键，通常是自增整数（如 AUTO_INCREMENT 列）。
优点: 简单、高效（特别是对于InnoDB聚集索引）、稳定（不随业务数据变化而变化）。
缺点: 没有业务含义，需要额外连接才能获取业务相关信息。
自然键 (Natural Key): 一个或多个具有业务含义的列，其值在业务上是唯一的（例如，国家代码 country_code CHAR(2)，身份证号 id_card_number VARCHAR(18)）。
优点: 具有业务含义，可能减少连接操作。
缺点:
可能较长或类型复杂，影响InnoDB聚集索引性能。
如果业务规则变化导致自然键可能不再唯一或发生变化，维护成本极高。
可能包含敏感信息。
企业实践: 大多数情况下，特别是在InnoDB表中，推荐使用无业务含义的自增整数代理键作为主键。业务上的自然唯一键（如用户邮箱、订单号）则通过创建唯一索引来保证其唯一性。这种设计兼顾了InnoDB聚集索引的性能和业务数据的完整性。
CREATE TABLE articles_with_surrogate_pk ( -- 使用代理主键的文章表示例
article_internal_id BIGINT AUTO_INCREMENT PRIMARY KEY, -- 内部使用的自增代理主键 (高效)
article_uuid CHAR(36) NOT NULL, -- 业务上使用的UUID作为文章的公共标识符
title VARCHAR(255) NOT NULL, -- 标题
-- ... 其他列
UNIQUE INDEX uidx_article_uuid (article_uuid) -- 为业务UUID创建唯一索引，确保其唯一性并提供查询入口
) ENGINE=InnoDB;
-- 这样，InnoDB的聚集索引基于短小的 article_internal_id，性能好。
-- 应用可以通过 article_uuid (有唯一索引支持) 来查询或引用文章。
4. 复合索引 (Composite/Compound Index)
定义:
复合索引（也称为组合索引或多列索引）是建立在表的多个列上的索引。索引的顺序对于其使用和性能至关重要。
目的:
加速涉及多个列的查询条件。
覆盖查询 (Covering Index)，如果查询所需的所有列都在索引中，则无需访问表数据。
支持基于索引前缀的查询。
语法:
CREATE TABLE employees_composite_idx ( -- 创建带复合索引的员工表示例
emp_no INT NOT NULL, -- 员工编号
dept_no CHAR(4) NOT NULL, -- 部门编号
job_title VARCHAR(50) NOT NULL, -- 职位
hire_date DATE NOT NULL, -- 入职日期
salary INT, -- 薪水
PRIMARY KEY (emp_no), -- 主键
INDEX idx_dept_job_hire (dept_no, job_title, hire_date) -- 在 dept_no, job_title, hire_date 上创建复合索引
) ENGINE=InnoDB;
-- 插入数据
INSERT INTO employees_composite_idx VALUES (1, 'd001', 'Manager', '2020-01-01', 90000);
INSERT INTO employees_composite_idx VALUES (2, 'd001', 'Engineer', '2021-03-15', 70000);
INSERT INTO employees_composite_idx VALUES (3, 'd002', 'Analyst', '2022-06-01', 60000);
INSERT INTO employees_composite_idx VALUES (4, 'd001', 'Engineer', '2020-07-20', 75000);
代码解释:
INDEX idx_dept_job_hire (dept_no, job_title, hire_date): 创建了一个复合索引，包含了 dept_no, job_title, 和 hire_date 三列，并且顺序是固定的。
最左前缀原则 (Leftmost Prefix Principle):
这是理解和使用复合索引的核心原则。当查询条件使用了复合索引中的一个或多个前导列时，该索引可以被利用。
对于索引 idx_dept_job_hire (dept_no, job_title, hire_date):
可以使用索引的查询条件:
WHERE dept_no = 'd001' (使用了第一个前缀列)
WHERE dept_no = 'd001' AND job_title = 'Engineer' (使用了前两个前缀列)
WHERE dept_no = 'd001' AND job_title = 'Engineer' AND hire_date = '2021-03-15' (使用了所有三列)
WHERE dept_no = 'd001' AND job_title LIKE 'Eng%' (第二个列使用了范围/前缀匹配，前提是第一个列是等值匹配)
ORDER BY dept_no, job_title, hire_date (如果 WHERE 条件允许，可以直接利用索引顺序避免排序)
不能有效使用（或不能完全使用）索引的查询条件:
WHERE job_title = 'Engineer' (没有使用最左前缀列 dept_no)
WHERE hire_date = '2021-03-15' (没有使用最左前缀列)
WHERE dept_no = 'd001' AND hire_date = '2021-03-15' (跳过了中间的 job_title 列，只能利用到 dept_no 部分的索引)
WHERE job_title = 'Engineer' AND dept_no = 'd001' (即使条件顺序颠倒，优化器有时能重排，但最好按索引顺序写)
索引列的顺序非常重要。设计复合索引时，应将最具选择性（能过滤掉最多行）且最常用于等值匹配的列放在前面。
索引下推 (Index Condition Pushdown – ICP):
ICP 是 MySQL 5.6 引入的一项优化。在没有 ICP 之前，对于复合索引，如果 WHERE 条件中有些列在索引中但不能通过索引直接定位（例如，不是最左前缀的一部分，或者是范围查询后的进一步过滤），存储引擎会先将满足索引前缀条件的行（及其所有列）读取出来，然后由 MySQL Server 层根据剩余条件进行过滤。
有了 ICP，对于某些情况，存储引擎层可以在访问索引时就使用索引中包含的列来评估这些额外的 WHERE 条件，只有满足所有这些条件下推条件的行才会被读取。这减少了从存储引擎到 Server 层的数据传输量，也减少了回表次数（如果需要回表的话）。
ICP 主要用于二级索引。
示例：对于索引 idx_dept_job_hire (dept_no, job_title, hire_date)
查询：SELECT emp_no FROM employees_composite_idx WHERE dept_no = 'd001' AND job_title LIKE '%gineer%';
没有 ICP：存储引擎使用 dept_no = 'd001' 找到所有 d001 部门的行，将这些行的 job_title 等信息传给 Server 层，Server 层再判断 job_title LIKE '%gineer%'。
有 ICP：存储引擎使用 dept_no = 'd001' 定位，然后在索引层面直接检查 job_title 是否包含 “gineer”（如果 LIKE 的模式和索引类型允许）。只有同时满足的才返回主键给 Server 层进行回表（如果 emp_no 不在索引中）。
EXPLAIN 输出中的 Extra 列会显示 Using index condition 表示 ICP 被使用。
企业级应用示例:
一个CRM系统中，销售人员经常需要查询特定区域 (region)、特定行业 (industry) 且在最近一段时间内 (last_contact_date) 有过联系的潜在客户 (leads)。
CREATE TABLE crm_leads_demo ( -- 创建CRM潜在客户表示例
lead_id INT AUTO_INCREMENT PRIMARY KEY, -- 潜在客户ID
lead_name VARCHAR(100) NOT NULL, -- 名称
company_name VARCHAR(150), -- 公司名称
region_code VARCHAR(10) NOT NULL, -- 区域代码 (例如: 'NA', 'EMEA', 'APAC')
industry_code VARCHAR(20) NOT NULL, -- 行业代码 (例如: 'TECH', 'FINANCE', 'HEALTHCARE')
status VARCHAR(15) DEFAULT 'NEW', -- 状态 (例如: 'NEW', 'CONTACTED', 'QUALIFIED', 'LOST')
last_contact_date DATE, -- 最后联系日期
assigned_salesrep_id INT, -- 分配的销售代表ID
-- ... 其他列 ...
INDEX idx_reg_ind_contact (region_code, industry_code, last_contact_date DESC) -- 复合索引，注意 last_contact_date 使用降序
) ENGINE=InnoDB;
-- 插入示例数据
INSERT INTO crm_leads_demo (lead_name, region_code, industry_code, last_contact_date, status) VALUES
('Lead A', 'NA', 'TECH', '2023-10-15', 'CONTACTED'),
('Lead B', 'EMEA', 'FINANCE', '2023-11-01', 'NEW'),
('Lead C', 'NA', 'TECH', '2023-09-20', 'QUALIFIED'),
('Lead D', 'NA', 'HEALTHCARE', '2023-10-25', 'CONTACTED'),
('Lead E', 'EMEA', 'TECH', '2023-10-05', 'CONTACTED');
-- 常见查询: 查找北美(NA)的科技(TECH)行业，在2023年10月1日之后联系过的潜在客户，按最后联系日期降序排列
EXPLAIN SELECT lead_id, lead_name, company_name, last_contact_date -- 选择列
FROM crm_leads_demo -- 从 crm_leads_demo 表
WHERE region_code = 'NA' -- 条件1: 区域为'NA'
AND industry_code = 'TECH' -- 条件2: 行业为'TECH'
AND last_contact_date >= '2023-10-01' -- 条件3: 最后联系日期在此之后
ORDER BY last_contact_date DESC; -- 按最后联系日期降序排列
代码解释:
INDEX idx_reg_ind_contact (region_code, industry_code, last_contact_date DESC):
列的顺序是 region_code, industry_code, last_contact_date。
last_contact_date DESC: 在索引定义中指定了该列的排序方式为降序。MySQL 8.0+ 支持在索引中混合升序/降序。对于旧版本，虽然可以写 DESC，但存储引擎可能仍按升序存储，只是在扫描时反向扫描（如果优化器认为合适）。对于8.0+，是真的按降序存储，这对于 ORDER BY ... DESC 非常有利。
查询语句中的 WHERE 条件与索引的前缀匹配 (region_code 和 industry_code 是等值，last_contact_date 是范围）。
ORDER BY last_contact_date DESC 可以直接利用索引 idx_reg_ind_contact 的顺序（尤其是第三列的 DESC 定义），避免了额外的 “filesort” 操作。EXPLAIN 的 Extra 列中不应出现 Using filesort。
复合索引列顺序选择策略:
等值查询优先: 将最常用于等值比较的列放在前面。
选择性 (Cardinality): 在等值列中，将选择性高的列（不同值的数量多，能过滤掉更多行）放在前面。可以使用 SHOW INDEX FROM table_name; 查看索引的 Cardinality 值（这是一个估计值）。
范围查询列: 将用于范围查询的列放在等值查询列之后。一旦遇到范围查询，该列之后的索引列通常不能再用于进一步的精确查找，只能用于扫描。
排序需求: 如果查询经常需要按某些列排序，并且这些列可以作为索引的一部分，考虑将它们按排序顺序包含在索引中（并注意升/降序）。
覆盖查询: 考虑是否可以通过增加索引列来实现覆盖查询，以避免回表。
5. 覆盖索引 (Covering Index)
定义:
覆盖索引并不是一种特殊的索引类型，而是一种索引的使用方式或查询优化的结果。当一个查询所需的所有数据都可以直接从索引中获取，而无需访问数据表本身（即无需回表）时，这个索引就被称为该查询的“覆盖索引”。
目的:
极大地提高查询性能，因为它避免了代价较高的随机I/O去读取数据行（尤其对于InnoDB二级索引的回表操作）。
如何实现:
确保查询的 SELECT 列表、WHERE 子句、ORDER BY 子句、GROUP BY 子句中涉及的所有列都包含在同一个索引中。
EXPLAIN 中的体现:
当一个查询使用了覆盖索引时，EXPLAIN 输出的 Extra 列会显示 Using index。
企业级应用示例:
在一个用户表中，经常需要根据用户的注册日期范围查找用户的ID、用户名和邮箱，并按用户名排序。
CREATE TABLE users_covering_example ( -- 创建用户表示例 (用于覆盖索引)
user_id INT AUTO_INCREMENT PRIMARY KEY, -- 用户ID
username VARCHAR(50) NOT NULL, -- 用户名
email VARCHAR(100) NOT NULL, -- 邮箱
registration_date DATE NOT NULL, -- 注册日期
last_login TIMESTAMP, -- 最后登录时间
INDEX idx_regdate_uname_email (registration_date, username, email) -- 复合索引，设计为覆盖索引
) ENGINE=InnoDB;
INSERT INTO users_covering_example (username, email, registration_date) VALUES -- 插入用户数据
('alpha_user', 'alpha@example.com', '2023-01-15'),
('beta_user', 'beta@example.com', '2023-01-20'),
('gamma_user', 'gamma@example.com', '2023-02-10'),
('delta_user', 'delta@example.com', '2023-01-18');
-- 查询1: 查找2023年1月注册的用户ID, 用户名, 邮箱，并按用户名排序
EXPLAIN SELECT user_id, username, email -- 选择的列: user_id, username, email
FROM users_covering_example -- 从 users_covering_example 表
WHERE registration_date >= '2023-01-01' AND registration_date < '2023-02-01' -- 条件: 注册日期在1月份
ORDER BY username; -- 按用户名排序
代码解释与分析:
索引 idx_regdate_uname_email (registration_date, username, email) 包含了 registration_date, username, email。
查询 SELECT user_id, username, email ... WHERE registration_date ... ORDER BY username;
SELECT 列表: user_id, username, email。
WHERE 条件: registration_date。
ORDER BY: username。
问题: user_id (主键) 没有包含在 idx_regdate_uname_email 索引中。
对于 InnoDB，二级索引的叶子节点会自动包含主键值。所以，即使 user_id 没有显式在 idx_regdate_uname_email 定义中，InnoDB 也可以从该二级索引的叶子节点获取 registration_date, username, email 以及 user_id。
因此，对于 InnoDB，这个查询可以实现覆盖索引。EXPLAIN 的 Extra 列应显示 Using index（可能还会看到 Using where）。
ORDER BY username: 由于 registration_date 是范围查询，username 在索引中位于其后，此时 ORDER BY username 可能无法完全利用索引顺序避免排序，除非 registration_date 的范围非常小或者优化器有其他策略。如果 registration_date 是等值查询，则 ORDER BY username 更容易利用索引顺序。
修改为更明确的覆盖索引 (假设不依赖InnoDB二级索引自动包含主键的特性，或者要覆盖更多列):
如果查询是 SELECT username, email FROM ... WHERE registration_date ... ORDER BY username;
那么 idx_regdate_uname_email (registration_date, username, email) 就能完美覆盖。
另一个覆盖索引示例:
统计每个部门 (dept_no) 的员工数量。
表 employees_composite_idx (emp_no PK, dept_no, job_title, hire_date, salary)
需要一个索引能覆盖 dept_no (用于 GROUP BY)。
-- 假设已存在索引: INDEX idx_dept_job_hire (dept_no, job_title, hire_date)
-- 这个索引可以用于覆盖下面的查询
EXPLAIN SELECT dept_no, COUNT(*) AS num_employees -- 选择部门编号和员工数量
FROM employees_composite_idx -- 从 employees_composite_idx 表
GROUP BY dept_no; -- 按部门编号分组
代码解释与分析:
查询只需要 dept_no 列来进行分组和计数。
索引 idx_dept_job_hire 的最左前缀是 dept_no。
MySQL 可以只扫描这个索引，而无需访问数据表，即可完成分组和计数。
EXPLAIN 的 Extra 列应显示 Using index (或者 Using index for group-by)。
设计覆盖索引的考量:
权衡: 为了覆盖查询而向索引中添加过多的列，会增加索引的大小，降低写操作性能，并可能使索引的B+Tree结构更臃肿。
针对性: 覆盖索引通常是为特定的、频繁执行的、对性能要求高的查询而设计的。
只包含必要的列: 不要为了覆盖而随意添加不必要的列到索引中。
6. 前缀索引 (Prefix Index)
定义:
对于 CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT 类型的列，可以只索引列值的前一部分字符（前缀），而不是整个列值。这称为前缀索引。
目的:
当列值很长时（如 TEXT 字段或很长的 VARCHAR），索引整个列会非常占用空间，并可能降低索引效率（B+Tree节点能容纳的条目减少）。前缀索引可以显著减小索引大小。
提供一种在长字符串列上进行索引的方法。
语法:
CREATE TABLE logs_prefix_example ( -- 创建日志表示例 (用于前缀索引)
log_id BIGINT AUTO_INCREMENT PRIMARY KEY, -- 日志ID
timestamp DATETIME, -- 时间戳
log_message TEXT, -- 日志消息 (可能很长)
INDEX idx_log_message_prefix (log_message(100)) -- 在 log_message 列的前100个字符上创建前缀索引
) ENGINE=InnoDB;
ALTER TABLE logs_prefix_example
ADD INDEX idx_another_prefix (log_message(50)); -- 添加另一个只索引前50个字符的前缀索引
代码解释:
INDEX idx_log_message_prefix (log_message(100)): 为 log_message 列创建一个前缀索引，只索引每个值的前100个字符。
选择合适的前缀长度:
这是使用前缀索引的关键。前缀长度需要足够长，以保证较高的选择性（即，不同值的前缀大部分是不同的），但又不能太长，否则就失去了减小索引大小的意义。
计算选择性:
可以通过以下查询来估算不同前缀长度的选择性：
SELECT -- 查询不同前缀长度的选择性
COUNT(DISTINCT LEFT(log_message, 10)) / COUNT(*) AS sel10, -- 前10个字符的选择性
COUNT(DISTINCT LEFT(log_message, 20)) / COUNT(*) AS sel20, -- 前20个字符的选择性
COUNT(DISTINCT LEFT(log_message, 30)) / COUNT(*) AS sel30, -- 前30个字符的选择性
COUNT(DISTINCT LEFT(log_message, 50)) / COUNT(*) AS sel50, -- 前50个字符的选择性
COUNT(DISTINCT log_message) / COUNT(*) AS sel_full -- 完整列值的选择性 (作为基准)
FROM logs_prefix_example; -- 从 logs_prefix_example 表
目标是找到一个前缀长度，其选择性接近完整列的选择性，但长度尽可能短。例如，如果 sel30 接近 sel_full，那么前缀长度30可能是一个不错的选择。
选择性的理想值接近1（表示几乎所有前缀都不同）。
限制:
覆盖查询受限: 如果查询需要访问超出前缀长度的列部分，前缀索引不能完全覆盖该查询（即，不能仅通过索引满足 SELECT 列表），仍然需要回表。
ORDER BY 和 GROUP BY 受限: 通常情况下，MySQL 不能使用前缀索引来优化 ORDER BY 或 GROUP BY 操作，因为索引只包含部分信息，无法保证完整值的顺序。
企业级应用示例:
存储用户评论 (user_reviews 表，review_text TEXT)，需要根据评论内容的前几个关键词进行搜索或匹配。
URL存储 (url_store 表，full_url VARCHAR(2048))，经常需要根据URL的前缀（例如域名或路径的前几段）进行查询。
CREATE TABLE url_store_demo ( -- 创建URL存储表示例
id INT AUTO_INCREMENT PRIMARY KEY, -- ID
full_url VARCHAR(2048) NOT NULL, -- 完整URL
INDEX idx_url_prefix (full_url(255)) -- 为URL创建前缀索引，索引前255个字符 (255通常是旧版MySQL单列索引的最大长度限制之一，对于VARCHAR也是一个常用长度)
) ENGINE=InnoDB;
INSERT INTO url_store_demo (full_url) VALUES -- 插入URL数据
('https://www.example.com/path/to/resource1?param=value'),
('https://www.example.com/another/path/document.html'),
('http://subdomain.example.org/some/very/long/path/that/exceeds/prefix/length/for/sure');
-- 查询1: 查找特定域名的URL
EXPLAIN SELECT id FROM url_store_demo WHERE full_url LIKE 'https://www.example.com/%'; -- 查询特定域名的URL
-- 这个查询可以利用 idx_url_prefix，因为 LIKE 的模式是从头开始匹配的。
7. 降序索引 (Descending Indexes – MySQL 8.0+)
定义:
从 MySQL 8.0 开始，InnoDB 存储引擎原生支持在索引定义中为列指定 DESC (降序) 排序。这意味着索引中的键值会按降序物理存储（或逻辑上按降序处理）。在之前的版本中，虽然语法上可以写 DESC，但存储引擎通常会忽略它，仍然按升序存储，只是在需要降序扫描时反向读取索引。
目的:
优化那些需要特定列以降序排序（ORDER BY column DESC）的查询，避免额外的排序操作 (filesort)。
更有效地支持混合排序顺序的复合索引（例如 ORDER BY col_a ASC, col_b DESC）。
语法:
CREATE TABLE events_desc_idx ( -- 创建带降序索引的事件表示例 (MySQL 8.0+)
event_id INT AUTO_INCREMENT PRIMARY KEY, -- 事件ID
event_type VARCHAR(30), -- 事件类型
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- 创建时间
priority INT, -- 优先级
INDEX idx_type_created_desc (event_type ASC, created_at DESC) -- 复合索引: event_type升序, created_at降序
) ENGINE=InnoDB;
INSERT INTO events_desc_idx (event_type, priority) VALUES ('LOGIN', 1), ('LOGOUT', 1), ('ERROR', 5), ('LOGIN', 1);
UPDATE events_desc_idx SET created_at = NOW() - INTERVAL FLOOR(RAND()*10) HOUR WHERE event_id > 0; -- 随机更新创建时间
-- 查询: 查找 'LOGIN' 类型的事件，按创建时间最新（降序）的排在前面
EXPLAIN SELECT event_id, created_at
FROM events_desc_idx
WHERE event_type = 'LOGIN'
ORDER BY created_at DESC; -- 按创建时间降序排列
代码解释与分析:
INDEX idx_type_created_desc (event_type ASC, created_at DESC):
event_type 默认是 ASC (升序)。
created_at DESC 明确指定 created_at 列在索引中按降序排列。
查询 WHERE event_type = 'LOGIN' ORDER BY created_at DESC;
WHERE event_type = 'LOGIN' 可以利用索引的 event_type 部分进行快速定位。
ORDER BY created_at DESC 可以直接利用索引中 created_at 的降序排列，避免 filesort。
EXPLAIN 的 Extra 列中不应出现 Using filesort。
与旧版本的区别:
在 MySQL 8.0 之前，如果索引定义中写了 DESC，优化器在处理 ORDER BY ... DESC 时，可能会选择对升序索引进行反向扫描（Backward Index Scan）。反向扫描的性能通常不如正向扫描，尤其是在有大量等值键的情况下。MySQL 8.0+ 的原生降序索引提供了更直接、更高效的降序支持。
企业级应用示例:
最新评论/帖子列表: CREATE INDEX idx_post_created_desc (post_id, created_at DESC) – 快速获取某个帖子的最新评论，或全局最新帖子。
排行榜: CREATE INDEX idx_score_player_desc (game_id, score DESC, player_id ASC) – 快速获取某个游戏的最高分玩家列表。
8. 不可见索引 (Invisible Indexes – MySQL 8.0+)
定义:
从 MySQL 8.0 开始，支持创建“不可见索引”(Invisible Indexes)。不可见索引在物理上存在，并且会像普通索引一样在DML操作时被维护，但是查询优化器默认不会使用它来生成执行计划。
目的:
测试索引影响: DBA 可以将一个现有索引设置为不可见，以评估删除该索引对查询性能的影响，而无需实际删除它（删除大索引可能很耗时，且如果发现误删，重建也耗时）。如果性能下降，可以快速将其恢复为可见。
逐步上线新索引: 在创建新索引后，可以先将其设置为不可见，进行充分测试（例如，通过设置会话变量 optimizer_switch='use_invisible_indexes=on' 来强制优化器考虑它），确认无误后再设为可见。
临时禁用索引: 在某些特殊维护操作或批量导入期间，如果某个索引的维护开销过大，可以临时将其设为不可见。
语法:
-- 创建一个不可见索引
CREATE TABLE T_invisible_idx ( -- 创建带不可见索引的表示例
id INT PRIMARY KEY,
c1 INT,
c2 VARCHAR(10),
INDEX idx_c1_invisible (c1) INVISIBLE -- 创建一个在c1列上的不可见索引
) ENGINE=InnoDB;
-- 修改现有索引为不可见
ALTER TABLE T_invisible_idx ALTER INDEX idx_c1_invisible INVISIBLE; -- 将idx_c1_invisible索引设为不可见
-- 修改不可见索引为可见
ALTER TABLE T_invisible_idx ALTER INDEX idx_c1_invisible VISIBLE; -- 将idx_c1_invisible索引设为可见
-- 查看索引的可见性状态
SHOW INDEX FROM T_invisible_idx; -- 会有一个 Visible 列 (YES/NO)
-- 或者查询 INFORMATION_SCHEMA.STATISTICS 表的 IS_VISIBLE 列
SELECT INDEX_NAME, IS_VISIBLE FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'T_invisible_idx';
代码解释:
INDEX idx_c1_invisible (c1) INVISIBLE: 在创建索引时直接指定为不可见。
ALTER TABLE ... ALTER INDEX ... INVISIBLE/VISIBLE: 修改现有索引的可见性。
强制优化器使用不可见索引 (用于测试):
-- 当前会话级别，让优化器考虑使用不可见索引
SET SESSION optimizer_switch = 'use_invisible_indexes=on'; -- 开启使用不可见索引的开关
EXPLAIN SELECT * FROM T_invisible_idx WHERE c1 = 10; -- 此时优化器会考虑 idx_c1_invisible
-- 在 Extra 中可能看到使用了该索引
SET SESSION optimizer_switch = 'use_invisible_indexes=off'; -- 恢复默认 (不使用不可见索引)
企业级应用:
安全地移除旧索引:
DBA 怀疑某个索引 idx_old 不再被频繁使用或者有更好的替代索引。
ALTER TABLE ... ALTER INDEX idx_old INVISIBLE;
监控系统一段时间（例如一周或一个月），观察是否有性能下降、慢查询增多，或者是否有应用报错（虽然不太可能因为索引不可见而报错，但性能问题可能间接触发超时等）。
如果没有负面影响，可以安全地 DROP INDEX idx_old;
如果有负面影响，ALTER TABLE ... ALTER INDEX idx_old VISIBLE; 快速恢复。
新功能上线前的索引性能评估:
为新功能涉及的查询创建一个或多个新索引，并设置为 INVISIBLE。
在测试环境或预发环境，通过 SET SESSION optimizer_switch='use_invisible_indexes=on'，针对性地测试新查询的性能，验证新索引是否被正确使用以及效果如何。
确认无误后，在生产环境创建这些索引（可以先是可见的，或者先不可见再改可见）。
不可见索引为DBA提供了一个强大的、低风险的工具来管理和优化索引策略。
三、创建和管理索引的语法与选项 (Syntax and Options for Creating and Managing Indexes)
掌握了索引的类型和基本概念后，我们需要深入了解创建和管理索引的具体SQL语法以及各种可用选项，这些选项对索引的行为和性能有重要影响。
1. CREATE INDEX 语法详解
CREATE INDEX 语句用于在已存在的表上添加新的索引。
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name -- 指定索引类型（可选，默认为普通B-Tree索引）和索引名称
[index_type] -- 可选，显式指定索引使用的数据结构 (例如 USING BTREE, USING HASH)
ON tbl_name (key_part, ...) -- 在哪个表的哪些列上创建索引
[index_option] -- 可选，指定索引的其他选项
[algorithm_option | lock_option] ... -- 可选，指定创建索引时的算法和锁选项
key_part: {col_name [(length)] | (expr)} [ASC | DESC] -- 定义索引的列部分，可指定前缀长度、表达式(MySQL 8.0.13+)，以及升降序(MySQL 8.0+)
index_option: { -- 索引的特定选项
KEY_BLOCK_SIZE [=] value -- MyISAM表，指定索引键块的大小
| index_type -- 同上，例如 USING BTREE
| WITH PARSER parser_name -- 全文索引，指定分词解析器
| COMMENT 'string' -- 为索引添加注释
| CLUSTERING [=] {YES | NO} -- NDB Cluster表，指定是否作为聚集索引的一部分
| VISIBLE | INVISIBLE -- MySQL 8.0+，指定索引是否可见
| ENGINE_ATTRIBUTE [=] 'string' -- MySQL 8.0.21+，为索引指定引擎特定的属性
| SECONDARY_ENGINE_ATTRIBUTE [=] 'string' -- MySQL 8.0.21+，为二级引擎的索引指定属性
}
algorithm_option:
ALGORITHM [=] {
DEFAULT | INPLACE | COPY} -- 指定创建索引时使用的算法
lock_option:
LOCK [=] {
DEFAULT | NONE | SHARED | EXCLUSIVE} -- 指定创建索引期间对表的锁定级别
各部分详解:
[UNIQUE | FULLTEXT | SPATIAL]:
UNIQUE: 创建唯一索引。
FULLTEXT: 创建全文索引。
SPATIAL: 创建空间索引。
如果省略，则创建普通（B-Tree）索引。
index_name:
索引的名称。在数据库内，表上的索引名称应该是唯一的。良好的命名规范很重要，例如 idx_tablename_column1_column2 或 uidx_tablename_column。
index_type (子句 USING BTREE 或 USING HASH):
显式指定索引使用的数据结构。
USING BTREE: 明确指定使用B-Tree索引。这是InnoDB和MyISAM表上非全文/空间索引的默认和主要类型。
USING HASH: 明确指定使用哈希索引。主要用于MEMORY引擎表。InnoDB的自适应哈希索引是内部的，不能通过此语法创建。
CREATE INDEX idx_email_btree ON users_example (email) USING BTREE; -- 在email列上创建名为idx_email_btree的B-Tree索引
CREATE TABLE mem_table_hash (id INT, name VARCHAR(10), INDEX idx_name_hash (name) USING HASH) ENGINE=MEMORY; -- 在MEMORY表的name列上创建哈希索引
ON tbl_name (key_part, ...):
tbl_name: 要在其上创建索引的表名。
key_part: 定义索引包含的列以及相关属性。
col_name: 列名。
[(length)]: 前缀长度。只对字符串类型 (CHAR, VARCHAR, BINARY, VARBINARY, TEXT, BLOB) 有效。指定只索引列值的前 length 个字节（对于多字节字符集，是字符数乘以每个字符的最大字节数，或直接指定字节数，取决于具体上下文和版本）。
CREATE INDEX idx_lastname_prefix ON employees_pk_example (last_name(10)); -- 在last_name列的前10个字符上创建前缀索引
(expr): 表达式索引 (Functional Key Parts / Indexes on Expressions) – MySQL 8.0.13+ 支持。可以对表达式的结果创建索引，而不仅仅是列本身。这对于索引函数结果或计算值非常有用。
-- 假设有一个JSON列 user_preferences
-- CREATE TABLE user_settings (
-- user_id INT PRIMARY KEY,
-- user_preferences JSON,
-- INDEX idx_pref_theme ((CAST(user_preferences->>'$.theme' AS CHAR(30)))) -- 对JSON字段中提取的theme值创建索引
-- );
-- 这个索引会作用于从 user_preferences JSON中提取并转换为CHAR的 'theme' 字段的值。
-- 索引一个日期字段的年份部分
CREATE TABLE sales_by_year (
sale_id INT PRIMARY KEY,
product_name VARCHAR(100),
sale_timestamp TIMESTAMP,
INDEX idx_sale_year ((YEAR(sale_timestamp))) -- 在sale_timestamp的年份部分创建索引
);
-- 查询 WHERE YEAR(sale_timestamp) = 2023 将能利用此索引。
企业级考量 (表达式索引):
查询匹配: 查询中的 WHERE 子句必须与索引表达式完全匹配才能利用该索引。例如，如果索引是 (YEAR(col))，那么查询 WHERE YEAR(col) = 2023 可以用，但 WHERE col BETWEEN '2023-01-01' AND '2023-12-31' 可能无法直接利用表达式索引进行范围扫描（尽管优化器可能转换）。
复杂性: 过度复杂的表达式会增加索引维护成本和查询优化难度。
虚拟列 (Generated Columns) 的替代: 在MySQL 5.7+，可以使用虚拟列（Generated Columns）并将索引建在虚拟列上，这通常更清晰，并且优化器支持更好。表达式索引可以看作是为没有显式虚拟列的情况提供便利。
-- 使用虚拟列达到类似效果
CREATE TABLE sales_by_year_virtual (
sale_id INT PRIMARY KEY,
product_name VARCHAR(100),
sale_timestamp TIMESTAMP,
sale_year INT AS (YEAR(sale_timestamp)) STORED, -- 定义一个存储型虚拟列 sale_year
INDEX idx_sale_year_virtual (sale_year) -- 在虚拟列上创建索引
);
-- 查询 WHERE sale_year = 2023;
-- STORED 虚拟列的值会被物化存储，VIRTUAL 虚拟列的值在读取时计算。索引通常建在STORED虚拟列上更优。
[ASC | DESC]: 升序/降序 (MySQL 8.0+)。指定该列在索引中的排序方式。默认为 ASC。如前所述，这对于优化 ORDER BY 非常重要。
index_option:
KEY_BLOCK_SIZE [=] value: 仅用于 MyISAM 表。指定索引B-Tree中每个叶子节点块的大小（以字节为单位）。较小的值可以节省空间，但可能增加树的高度；较大的值可能减少树高，但可能浪费空间（如果节点未填满）。通常使用默认值。
WITH PARSER parser_name: 仅用于 FULLTEXT 索引。指定用于分词的解析器。
默认解析器基于空格分词，并处理停用词。
ngram: 内置的NGram解析器，用于CJK等语言。需要配合 ngram_token_size 系统变量。
MeCab 解析器插件：一个流行的日语分词器，可以作为插件安装并在此处指定。
CREATE TABLE documents_cjk (
doc_id INT PRIMARY KEY,
content TEXT,
FULLTEXT INDEX ft_idx_content_ngram (content) WITH PARSER ngram -- 创建使用ngram解析器的全文索引
) ENGINE=InnoDB CHARACTER SET utf8mb4;
-- 同时需要在MySQL配置文件或全局/会话级别设置 ngram_token_size, 例如：
-- SET GLOBAL ngram_token_size=2; (需要SUPER权限，对后续创建的ngram索引生效)
COMMENT 'string': 为索引添加一个注释字符串，最长1024字节。方便DBA理解索引的用途。注释存储在 INFORMATION_SCHEMA.STATISTICS 表的 INDEX_COMMENT 列中。
CREATE INDEX idx_customer_email ON customers_ext (email) COMMENT 'Unique email for customer login and communication'; -- 为索引添加注释
CLUSTERING [=] {YES | NO}: 仅用于 NDB Cluster (MySQL Cluster)。指定该索引是否应该是表的聚集索引的一部分或如何与聚集主键交互。对于InnoDB或MyISAM无效。
VISIBLE | INVISIBLE (MySQL 8.0+): 控制索引对优化器的可见性。默认为 VISIBLE。
ENGINE_ATTRIBUTE [=] 'string', SECONDARY_ENGINE_ATTRIBUTE [=] 'string' (MySQL 8.0.21+): 允许为索引传递存储引擎特定的属性。这为未来的存储引擎扩展和特性提供了接口。例如，某些引擎可能允许通过这个接口配置特定的压缩选项或索引参数。具体可用的属性取决于存储引擎。
algorithm_option: ALGORITHM [=] {DEFAULT | INPLACE | COPY}:
指定在线创建索引（Online DDL）时MySQL使用的算法。这主要影响创建索引期间对表的并发DML操作（INSERT, UPDATE, DELETE）的允许程度。
DEFAULT: MySQL自动选择最佳算法。对于大多数支持在线DDL的存储引擎（如InnoDB），它通常会尝试 INPLACE。
INPLACE: 允许在创建索引的同时进行并发DML。MySQL会尽量避免拷贝整个表。这是首选的在线DDL算法。并非所有DDL操作都支持 INPLACE，例如修改列数据类型可能需要 COPY。创建二级索引通常支持 INPLACE。
COPY: 创建索引时，会创建一个表的临时副本，在副本上构建索引，然后用新表替换旧表，并重建其上的所有索引。在此期间，DML操作通常会被阻塞或受到严重限制。这是最慢且对并发影响最大的算法，但对于某些不支持 INPLACE 的操作是必需的。
-- 尝试使用 INPLACE 算法创建索引，如果不支持则会报错或回退到COPY (取决于严格模式等)
CREATE INDEX idx_new_col ON some_large_table (new_column) ALGORITHM=INPLACE, LOCK=SHARED; -- 尝试INPLACE算法，并使用共享锁
企业级考量 (ALGORITHM):
对于大型生产表，创建索引时务必考虑使用 ALGORITHM=INPLACE（如果支持）以减少对业务的影响。
在执行DDL前，可以通过查询 INFORMATION_SCHEMA.PLUGINS 或特定存储引擎的文档来确认操作是否支持 INPLACE。
监控DDL操作的进度，例如通过 SHOW PROCESSLIST 或 performance_schema 中的 events_stages_current 表。
lock_option: LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}:
指定在线创建索引时对表的锁定级别，与 ALGORITHM 选项协同工作。
DEFAULT: MySQL根据操作类型和 ALGORITHM 自动选择最合适的锁级别。
NONE: 如果操作支持，则在执行DDL期间不获取表锁，允许最大程度的并发DML。通常与 ALGORITHM=INPLACE 结合使用创建二级索引。
SHARED: 获取共享元数据锁。允许其他事务读取表，但阻止修改表结构 (其他DDL) 或执行需要排他锁的DML (如某些 ALTER TABLE 操作)。并发的 INSERT, UPDATE, DELETE 通常仍然是允许的，如果 ALGORITHM=INPLACE。
EXCLUSIVE: 获取排他元数据锁。在DDL操作完成之前，阻止所有其他事务对表的读写访问。这是对并发影响最大的锁级别。
-- 在创建索引时，明确指定不加显著的表锁，允许并发DML
ALTER TABLE very_active_table ADD INDEX idx_some_col (some_column) ALGORITHM=INPLACE, LOCK=NONE; -- 尝试无锁或最低锁级别创建索引
企业级考量 (LOCK):
目标是尽可能使用 LOCK=NONE 或 LOCK=SHARED 配合 ALGORITHM=INPLACE，以最大限度地减少对在线业务的阻塞。
在繁忙的生产系统上执行DDL（包括创建索引）前，务必仔细评估其对并发和锁的影响。最好在业务低谷期进行。
使用像 Percona Toolkit 的 pt-online-schema-change 或 Facebook 的 OSC (Online Schema Change) 这样的第三方工具，可以在不支持 LOCK=NONE 的DDL操作（如修改列类型）上模拟在线操作，它们通过创建触发器和数据拷贝的复杂机制来实现。
2. ALTER TABLE ... ADD INDEX 语法
ALTER TABLE 是另一种添加索引的方式，其选项与 CREATE INDEX 基本相同。
ALTER TABLE tbl_name -- 指定要修改的表
ADD [UNIQUE | FULLTEXT | SPATIAL] INDEX [index_name] -- 添加索引的类型和名称
[index_type] -- 例如 USING BTREE
(key_part,...) -- 索引的列定义
[index_option] ...; -- 其他索引选项
-- 示例:
ALTER TABLE employees_pk_example
ADD UNIQUE INDEX uidx_emp_hire_date (employee_id, hire_date) COMMENT 'Ensure no duplicate hire events for an employee on the same day', -- 添加唯一复合索引并加注释
ADD INDEX idx_lastname_firstname (last_name(10), first_name(10)) ALGORITHM=INPLACE, LOCK=SHARED; -- 添加带前缀的复合索引，指定算法和锁
代码解释:
ALTER TABLE ... ADD INDEX 允许在一个 ALTER TABLE 语句中添加多个索引（尽管通常建议分开执行大型DDL以更好地控制）。
其 index_name, index_type, key_part, index_option 的含义与 CREATE INDEX 中的相同。ALGORITHM 和 LOCK 选项也可以在这里使用。
3. CREATE TABLE ... INDEX 语法
在创建表的同时定义索引是最常见的方式。
CREATE TABLE new_table_with_indexes ( -- 创建新表示例
id INT AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
email VARCHAR(100),
status TINYINT DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id), -- 定义主键
UNIQUE INDEX uidx_email (email) INVISIBLE, -- 定义唯一索引，并设为不可见 (MySQL 8.0+)
INDEX idx_status_created (status, created_at DESC) USING BTREE COMMENT 'For filtering by status and recent activity' -- 定义复合B-Tree索引，带降序和注释
) ENGINE=InnoDB;
代码解释:
在 CREATE TABLE 语句的末尾，可以直接列出主键、唯一索引、普通索引、全文索引或空间索引的定义。
语法与 CREATE INDEX 或 ALTER TABLE ADD INDEX 中的索引定义部分类似。
4. 管理索引 (Managing Indexes)
4.1 查看索引 (Showing Indexes)
SHOW INDEX[ES] FROM tbl_name [FROM db_name]:
这是最常用的查看表上所有索引信息的命令。
SHOW INDEX FROM employees_composite_idx; -- 显示 employees_composite_idx 表的所有索引信息
-- SHOW INDEXES FROM orders_demo FROM my_database; -- 显示指定数据库my_database中orders_demo表的索引
输出列解释:
Table: 表名。
Non_unique: 如果索引不是唯一索引，则为1；如果是唯一索引（包括主键），则为0。
Key_name: 索引的名称。主键的名称总是 PRIMARY。
Seq_in_index: 列在索引中的序号（从1开始）。对于复合索引，这指示了列的顺序。
Column_name: 索引包含的列名。如果索引是基于表达式（MySQL 8.0.13+），这里可能显示 NULL 或表达式定义，需要配合 Expression 列。
Collation: 列在索引中的排序规则（字符集校对）。‘A’ 表示升序，NULL 可能表示该列类型不适用排序规则（如数字）或使用了特殊排序。
Cardinality: 非常重要的列。索引中唯一值的估计数量。这个值由MySQL定期（或在 ANALYZE TABLE 后）更新。基数越高，索引的选择性越好，优化器越倾向于使用它。对于复合索引，这是基于所有列组合的估计唯一值。这个值是估计的，可能不完全准确。
Sub_part: 对于前缀索引，这里显示索引的前缀长度（字节数）。对于完整列索引，为 NULL。
Packed: 指示键是否被压缩。通常为 NULL，除非使用了特殊的压缩技术（如MyISAM的键压缩）。
Null: 如果列允许 NULL 值，则为 ‘YES’；否则为空字符串 ‘’。对于唯一索引，如果此列为 ‘YES’，表示允许 NULL（且多个NULL不违反唯一性）。
Index_type: 索引使用的数据结构，如 BTREE, HASH, FULLTEXT, SPATIAL。
Comment: 创建索引时指定的注释。
Index_comment: 同 Comment。
Visible (MySQL 8.0+): YES 或 NO，指示索引是否对优化器可见。
Expression (MySQL 8.0.13+): 如果索引基于表达式，这里显示表达式的定义。
查询 INFORMATION_SCHEMA.STATISTICS 表:
INFORMATION_SCHEMA 是一个元数据库，STATISTICS 表提供了关于索引的更详细、更结构化的信息，可以通过SQL查询。
SELECT -- 查询索引的详细信息
TABLE_SCHEMA, -- 数据库名
TABLE_NAME, -- 表名
INDEX_NAME, -- 索引名
SEQ_IN_INDEX, -- 列在索引中的顺序
COLUMN_NAME, -- 列名
CARDINALITY, -- 基数
SUB_PART, -- 前缀长度
INDEX_TYPE, -- 索引类型
COMMENT, -- 创建时的关键字，如 PRIMARY, UNIQUE
INDEX_COMMENT, -- 索引的注释字符串
IS_VISIBLE, -- (MySQL 8.0+) 是否可见
EXPRESSION -- (MySQL 8.0.13+) 表达式定义
FROM
INFORMATION_SCHEMA.STATISTICS -- 从 STATISTICS 系统表
WHERE
TABLE_SCHEMA = DATABASE() -- 条件：当前数据库 (DATABASE()函数返回当前数据库名)
AND TABLE_NAME = 'employees_composite_idx' -- 条件：表名为 'employees_composite_idx'
ORDER BY
INDEX_NAME, SEQ_IN_INDEX; -- 按索引名和列顺序排序
4.2 删除索引 (Dropping Indexes)
DROP INDEX index_name ON tbl_name:
DROP INDEX idx_unit_price ON products_example; -- 删除 products_example 表上的 idx_unit_price 索引
企业级考量: 删除索引前务必确认该索引不再被任何重要查询使用，或者有更好的替代索引。误删关键索引可能导致严重的性能问题。使用不可见索引特性（MySQL 8.0+）可以安全地测试删除索引的影响。
ALTER TABLE tbl_name DROP INDEX index_name:
ALTER TABLE users_example DROP INDEX uidx_phone; -- 删除 users_example 表上的 uidx_phone 索引
删除主键:
主键比较特殊，删除主键需要使用 ALTER TABLE ... DROP PRIMARY KEY。
-- ALTER TABLE some_table_with_pk DROP PRIMARY KEY;
-- 警告: 删除主键是非常危险的操作，尤其是对于InnoDB表，因为它会导致表结构重组。
-- 通常只有在需要重新定义主键时才这样做，并且表最好是空的或数据量很小。
如果InnoDB表删除了显式主键，且没有其他唯一非空索引，InnoDB会内部选择或创建一个隐藏的聚集索引。
ALGORITHM 和 LOCK 选项 (用于 DROP INDEX 和 ALTER TABLE DROP INDEX):
与创建索引类似，删除索引也可以指定 ALGORITHM 和 LOCK 选项，以控制在线操作的行为。删除索引通常比创建索引快得多，并且通常支持 INPLACE 和 LOCK=NONE 或 LOCK=SHARED。
DROP INDEX old_idx ON large_table ALGORITHM=INPLACE, LOCK=NONE; -- 尝试以最小影响删除索引
4.3 重命名索引 (Renaming Indexes – MySQL 5.7+)
从 MySQL 5.7 开始，可以使用 ALTER TABLE ... RENAME INDEX 来重命名索引，而无需删除和重建。
ALTER TABLE products_example
RENAME INDEX idx_product_name TO idx_prod_name_search; -- 将索引 idx_product_name 重命名为 idx_prod_name_search
这是一个元数据操作，通常非常快。
4.4 禁用和启用索引键 (Disabling/Enabling Index Keys – MyISAM specific, deprecated behavior)
在非常古老的MySQL版本中，MyISAM表支持 ALTER TABLE ... DISABLE KEYS 和 ALTER TABLE ... ENABLE KEYS。
DISABLE KEYS: 临时禁用表的非唯一索引的更新。在批量导入大量数据到MyISAM表时，先禁用索引，导入数据，然后再启用索引，可以比每插入一行都更新索引更快。
ENABLE KEYS: 重新构建并启用之前被禁用的索引。
-- 对于 MyISAM 表 (旧用法，不推荐在新设计中使用)
-- ALTER TABLE myisam_table DISABLE KEYS;
-- -- ... 执行大量 INSERT 操作 ...
-- ALTER TABLE myisam_table ENABLE KEYS;
注意:
此功能主要针对 MyISAM。InnoDB 的行为不同，通常不需要（也不支持）这种方式来优化批量导入；InnoDB 有其自身的批量加载优化（如关闭唯一性检查、增加缓冲池等，但不是禁用索引键本身）。
对于 InnoDB，如果需要进行大规模数据加载，更好的策略是：
如果表是新的，先创建表结构（不含二级索引和外键），加载数据，然后再添加二级索引和外键。
调整 InnoDB 相关配置参数，如 innodb_bulk_load_xtradb_cluster (如果适用集群), innodb_autoinc_lock_mode (对于自增主键), unique_checks=0, foreign_key_checks=0 (会话级别，小心使用)。
4.5 ANALYZE TABLE tbl_name
ANALYZE TABLE 用于分析和存储表的键分布信息（主要是索引的基数 Cardinality）。查询优化器依赖这些统计信息来选择最佳的执行计划。
ANALYZE TABLE employees_composite_idx; -- 分析 employees_composite_idx 表，更新其键分布统计信息
当表的数据发生显著变化后（大量增删改），索引的统计信息可能变得不准确。执行 ANALYZE TABLE 可以更新这些信息，帮助优化器做出更好的决策。
InnoDB 通常会自动（异步地）更新统计信息，但有时手动执行 ANALYZE TABLE 也是有益的，特别是在进行性能调优或基准测试之前。
对于大型表，ANALYZE TABLE 可能需要一些时间。
可以通过 innodb_stats_auto_recalc (控制自动重新计算) 和 innodb_stats_persistent (控制统计信息是否持久化到磁盘) 等参数来配置InnoDB的统计信息行为。持久化统计信息（MySQL 5.6+ 默认开启）可以避免服务器重启后统计信息丢失导致查询计划波动。
这些创建和管理的命令及选项是DBA和开发人员日常工作中与索引交互的基础。理解它们如何影响索引的构建过程、并发性和最终的索引结构至关重要。
暂无评论内容