MySQL索引完全手册：真实业务图文讲解17种索引运用技巧(必须收藏)

MySQL索引是数据库中用于提高数据检索效率的重要技术。它通过在数据表列上创建索引结构，如B+Tree、Hash索引等，加速查询速度。索引类型包括主键索引、唯一索引、普通索引、全文索引、空间索引等，各适用于不同的查询场景。合理设计索引可以显著提升数据库性能，但过多索引也会导致维护成本增加和写操作变慢。索引优化需考虑查询模式、数据量和存储引擎特性。

肖哥弹架构 跟大家“弹弹” 代码设计技巧，需要代码关注

欢迎 点赞，关注，评论。

关注公号Solomon肖哥弹架构获取更多精彩内容

历史热点文章

•  数据库：全文索引实现技巧，架构师是这样实现的
•  myqsl 12种锁，提供12个真实业务与流程图，轻松掌握运用场景与方式
•  数据库我是这样写出来的，Java MVCC升级版1，持续更新
•  数据库我是这样写出来的，Java版本1，持续更新
•  打破僵局：深度解析数据库死锁的策略与实践(专家篇)
•  架构师通过合作式锁定协议——保证数据库底层持久化的安全方案

1. B+Tree索引

B+Tree索引是MySQL中最常用的索引类型，适用于快速定位、顺序访问和范围查询。

构建原理：基于B+Tree数据结构，节点包含数据键值和子节点指针，叶子节点包含数据记录或指针。

设计目标：快速数据访问，支持顺序和范围查询。

业务场景：电商平台商品信息检索。

• 场景表结构：

登录后复制

CREATE TABLE products (
    product_id INT NOT NULL AUTO_INCREMENT COMMENT '商品ID',
    product_name VARCHAR(255) NOT NULL COMMENT '商品名称',
    price DECIMAL(10, 2) NOT NULL COMMENT '商品价格',
    create_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    PRIMARY KEY (product_id),
    INDEX idx_product_name (product_name) COMMENT '商品名称索引'
) COMMENT '商品信息表';

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.

• 场景索引代码：

登录后复制

-- 创建商品名称的B+Tree索引
CREATE INDEX idx_product_name ON products (product_name);

• 1.
• 2.

• 索引不起作用条件：

登录后复制

-- 索引列参与了函数操作
SELECT * FROM products WHERE LOWER(product_name) = 'iphone'; -- 索引不起作用，因为使用了函数LOWER

-- 索引列使用了范围查询
SELECT * FROM products WHERE product_name > 'iphone'; -- 索引部分起作用，只能用于>操作，不能用于全名匹配

-- 查询条件中使用了不同的列顺序
SELECT * FROM products WHERE price = 1299; -- 索引不起作用，因为没有为price列创建索引

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.

2. Hash索引

Hash索引通过哈希函数快速定位键值，适合等值查询，不适合范围查询

构建原理：基于哈希表，快速定位键值。

设计目标：快速等值查询。

业务场景：用户登录系统，快速验证用户名或邮箱。

• 场景表结构：

登录后复制

CREATE TABLE users (
    user_id INT NOT NULL AUTO_INCREMENT COMMENT '用户ID',
    username VARCHAR(255) NOT NULL COMMENT '用户名',
    email VARCHAR(255) NOT NULL COMMENT '用户邮箱',
    UNIQUE INDEX idx_email (email) COMMENT '邮箱唯一索引'
) COMMENT '用户信息表';

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.

• 场景索引代码：

登录后复制

-- MySQL的Memory引擎自动将所有唯一索引和主键索引创建为Hash索引，无需手动创建

• 1.

• 索引不起作用条件：

登录后复制

-- 索引列参与了范围查询
SELECT * FROM users WHERE email > 'user@example.com'; -- 索引不起作用，Hash索引不支持范围查询

-- 索引列使用了LIKE操作且以通配符开头
SELECT * FROM users WHERE email LIKE '%example.com'; -- 索引不起作用，Hash索引不支持LIKE查询

• 1.
• 2.
• 3.
• 4.
• 5.

3. Full-Text索引

全文索引用于文本字段的高效搜索，支持复杂的查询语句。

构建原理：基于倒排索引，用于全文检索。

设计目标：优化全文搜索效率。

业务场景：文章内容搜索。

• 场景表结构：

登录后复制

CREATE TABLE articles (
    article_id INT NOT NULL AUTO_INCREMENT COMMENT '文章ID',
    title VARCHAR(255) NOT NULL COMMENT '文章标题',
    content TEXT NOT NULL COMMENT '文章内容',
    FULLTEXT(idx_title_content) (title, content) COMMENT '标题和内容的全文索引'
) COMMENT '文章信息表';

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.

• 场景索引代码：

登录后复制

-- 创建全文索引
ALTER TABLE articles ADD FULLTEXT(idx_title_content) (title, content);

• 1.
• 2.

• 索引不起作用条件：

登录后复制

-- 查询中使用了全文索引不支持的列
SELECT * FROM articles WHERE article_id = 1; -- 索引不起作用，因为article_id没有包含在全文索引中

-- 查询中使用了全文索引不支持的操作符
SELECT * FROM articles WHERE title LIKE '%keyword'; -- 索引不起作用，全文索引不支持LIKE操作符

• 1.
• 2.
• 3.
• 4.
• 5.

4. 主键索引(PRIMARY KEY)

主键索引确保数据表中每条记录的唯一性，通常是B+Tree索引

构建原理：自动在主键上创建的B+Tree索引。

设计目标：唯一标识表中的每一行。

业务场景：用户账户信息管理。

• 场景表结构：

登录后复制

CREATE TABLE user_accounts (
    account_id INT NOT NULL AUTO_INCREMENT COMMENT '账户ID',
    account_name VARCHAR(255) NOT NULL COMMENT '账户名称',
    PRIMARY KEY (account_id) COMMENT '账户ID主键索引'
) COMMENT '用户账户表';

• 1.
• 2.
• 3.
• 4.
• 5.

• 场景索引代码：

登录后复制

-- 主键索引在定义主键时自动创建，无需额外SQL代码

• 1.

• 索引不起作用条件：

登录后复制

-- 查询使用了非主键的其他列
SELECT * FROM user_accounts WHERE account_name = '用户名'; -- 索引不起作用，因为account_name不是主键

• 1.
• 2.

5. 唯一索引(UNIQUE)

唯一索引保证键值的唯一性，允许有空值

构建原理：基于B+Tree，不允许重复键值。

设计目标：保证列值的唯一性。

业务场景：确保用户邮箱地址的唯一性。

• 场景表结构：

登录后复制

CREATE TABLE user_emails (
    email_id INT NOT NULL AUTO_INCREMENT COMMENT '邮箱ID',
    user_id INT NOT NULL COMMENT '用户ID',
    email VARCHAR(255) NOT NULL COMMENT '用户邮箱',
    UNIQUE INDEX idx_email (email) COMMENT '邮箱唯一索引'
) COMMENT '用户邮箱表';

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.

• 场景索引代码：

登录后复制

-- 在创建唯一索引
ALTER TABLE user_emails ADD UNIQUE(idx_email) (email);

• 1.
• 2.

• 索引不起作用条件：

登录后复制

-- 插入重复的邮箱地址
INSERT INTO user_emails (user_id, email) VALUES (1, 'existing_email@example.com'); -- 索引阻止插入，因为邮箱已存在

-- 使用了非唯一索引的列进行查询
SELECT * FROM user_emails WHERE user_id = 1; -- 索引不起作用，因为user_id没有定义唯一索引

• 1.
• 2.
• 3.
• 4.
• 5.

6. 普通索引(INDEX)

普通索引用于加速查询，没有唯一性约束。

构建原理：基于B+Tree，允许键值重复。

设计目标：提高普通查询的效率。

业务场景：订单状态查询。

• 场景表结构：

登录后复制

CREATE TABLE orders (
    order_id INT NOT NULL AUTO_INCREMENT COMMENT '订单ID',
    order_status VARCHAR(50) NOT NULL COMMENT '订单状态',
    INDEX idx_order_status (order_status) COMMENT '订单状态索引'
) COMMENT '订单信息表';

• 1.
• 2.
• 3.
• 4.
• 5.

• 场景索引代码：

登录后复制

-- 创建订单状态的普通索引
ALTER TABLE orders ADD INDEX idx_order_status (order_status);

• 1.
• 2.

• 索引不起作用条件：

登录后复制

-- 使用了索引列上的非等值查询
SELECT * FROM orders WHERE order_status LIKE '%完成'; -- 索引不起作用，因为使用了LIKE操作

-- 查询中使用了非索引列的排序
SELECT * FROM orders ORDER BY order_id; -- 索引不起作用，因为排序列不是order_status

• 1.
• 2.
• 3.
• 4.
• 5.

7. 联合索引(Composite Index)

联合索引跨多个列，提高多条件查询效率。

构建原理：跨多个列的B+Tree索引。

设计目标：提高多列查询的效率。

业务场景：按日期和用户ID查询交易记录。

• 场景表结构：

登录后复制

CREATE TABLE transactions (
    transaction_id INT NOT NULL AUTO_INCREMENT COMMENT '交易ID',
    user_id INT NOT NULL COMMENT '用户ID',
    transaction_date DATE NOT NULL COMMENT '交易日期',
    amount DECIMAL(10, 2) NOT NULL COMMENT '交易金额',
    INDEX idx_user_date (user_id, transaction_date) COMMENT '用户ID和交易日期的联合索引'
) COMMENT '交易记录表';

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.

• 场景索引代码：

登录后复制

-- 创建用户ID和交易日期的联合索引
ALTER TABLE transactions ADD INDEX idx_user_date (user_id, transaction_date);

• 1.
• 2.

• 索引不起作用条件：

登录后复制

-- 查询没有使用最左前缀
SELECT * FROM transactions WHERE transaction_date = '2024-08-01'; -- 索引不起作用，因为没有包括user_id

-- 查询中包含了索引列之外的条件
SELECT * FROM transactions WHERE user_id = 1 AND amount > 100; -- 索引部分起作用，amount条件未用到索引

• 1.
• 2.
• 3.
• 4.
• 5.

8. 空间索引(Spatial Index)

空间索引用于地理空间数据，优化区域查询

构建原理：基于R-Tree，用于空间数据索引。

设计目标：优化空间数据查询效率。

业务场景：地理信息系统中地点检索。

• 场景表结构：

登录后复制

CREATE TABLE geospatial_data (
    geo_id INT NOT NULL AUTO_INCREMENT COMMENT '地理空间数据ID',
    location POINT NOT NULL COMMENT '地理位置',
    INDEX spatial_idx_location (location) COMMENT '地理位置的空间索引'
) COMMENT '地理空间数据表';

• 1.
• 2.
• 3.
• 4.
• 5.

• 场景索引代码：

登录后复制

-- 创建地理位置的空间索引 
ALTER TABLE geospatial_data ADD SPATIAL spatial_idx_location (location);

• 1.
• 2.

• 索引不起作用条件：

登录后复制

-- 查询中使用了非空间索引列 
SELECT * FROM geospatial_data WHERE geo_id = 1; 
-- 索引不起作用，因为条件中没有使用location列 
-- 查询中使用了空间索引不支持的操作 
SELECT * FROM geospatial_data WHERE location = POINT(121.47, 31.24); 
-- 索引可能不起作用，因为使用了直接比较

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.

9. 前缀索引

前缀索引只对字符串的一部分创建索引，节省空间。

构建原理：对字符串列的前缀部分创建索引，以减少索引占用的空间。

设计目标：在列前缀足够唯一的情况下，优化查询性能，节省存储空间。

业务场景：存储大量URL或长文本数据，需要快速检索。

• 场景表结构：

登录后复制

CREATE TABLE logs (
    log_id INT NOT NULL AUTO_INCREMENT COMMENT '日志ID',
    message TEXT NOT NULL COMMENT '日志信息',
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    INDEX idx_message_prefix (message(255)) COMMENT '日志信息前缀索引'
) COMMENT '日志信息表';

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.

• 场景索引代码：

登录后复制

-- 创建日志信息的前缀索引
ALTER TABLE logs ADD INDEX idx_message_prefix (message(255));

• 1.
• 2.

• 索引不起作用条件：

登录后复制

-- 查询条件包含不在前缀索引内的部分
SELECT * FROM logs WHERE message LIKE '部分信息%'; -- 索引不起作用，因为%在后缀

-- 查询使用了前缀索引列的范围查询
SELECT * FROM logs WHERE message > '某个值'; -- 索引不起作用，因为使用了范围查询

• 1.
• 2.
• 3.
• 4.
• 5.

10. 聚簇索引(Clustered Index)

聚簇索引的叶子节点直接包含数据行，提高范围查询效率。

构建原理：行数据存储在B+Tree的叶子节点，非叶子节点包含键值和指向子节点的指针。

设计目标：数据物理存储顺序与索引顺序一致，提高范围查询效率。

业务场景：按主键查询或插入数据，如用户ID。

• 场景表结构：

登录后复制

CREATE TABLE customers (
    customer_id INT NOT NULL AUTO_INCREMENT COMMENT '客户ID',
    first_name VARCHAR(50) NOT NULL COMMENT '名字',
    last_name VARCHAR(50) NOT NULL COMMENT '姓氏',
    birth_date DATE NOT NULL COMMENT '出生日期',
    PRIMARY KEY (customer_id) COMMENT '客户ID聚簇索引'
) COMMENT '客户信息表';

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.

• 场景索引代码：

登录后复制

-- 主键自动创建聚簇索引，无需额外SQL代码

• 1.

• 索引不起作用条件：

登录后复制

-- 没有使用主键查询
SELECT * FROM customers WHERE first_name = 'John'; -- 索引可能部分起作用，但不是聚簇索引的优势

-- 使用了非索引列的范围查询
SELECT * FROM customers WHERE birth_date > '2000-01-01'; -- 索引不起作用，因为没有使用聚簇索引列

• 1.
• 2.
• 3.
• 4.
• 5.

11. 非聚簇索引(Non-clustered Index)

非聚簇索引的叶子节点包含指向数据行的指针。

构建原理：索引和数据物理存储分离，索引包含键值和指向数据行的指针。

设计目标：提供快速的索引查找，然后通过指针回表查询数据。

业务场景：查询非主键列，如商品分类。

• 场景表结构：

登录后复制

CREATE TABLE product_categories (
    category_id INT NOT NULL AUTO_INCREMENT COMMENT '分类ID',
    category_name VARCHAR(100) NOT NULL COMMENT '分类名称',
    description TEXT COMMENT '分类描述',
    PRIMARY KEY (category_id),
    INDEX idx_category_name (category_name) COMMENT '分类名称索引'
) COMMENT '商品分类表';

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.

• 场景索引代码：

登录后复制

-- 创建非聚簇索引
ALTER TABLE product_categories ADD INDEX idx_category_name (category_name);

• 1.
• 2.

• 索引不起作用条件：

登录后复制

-- 查询使用了索引列的范围查询
SELECT * FROM product_categories WHERE category_name > 'Books'; -- 索引不起作用

-- 查询中包含了索引列之外的排序
SELECT * FROM product_categories ORDER BY description; -- 索引不起作用

• 1.
• 2.
• 3.
• 4.
• 5.

12. 自适应哈希索引

InnoDB存储引擎根据查询模式自动创建哈希索引以加速查询

构建原理：InnoDB自动在热点B+Tree索引上创建Hash索引，以加速等值查询。

设计目标：自动优化频繁等值查询的性能。

业务场景：频繁查询的列，如用户表的用户名。

• 场景表结构：

登录后复制

CREATE TABLE users (
    user_id INT NOT NULL AUTO_INCREMENT COMMENT '用户ID',
    username VARCHAR(255) NOT NULL COMMENT '用户名',
    INDEX idx_username (username) COMMENT '用户名索引'
) COMMENT '用户信息表';

• 1.
• 2.
• 3.
• 4.
• 5.

• 场景索引代码：

登录后复制

-- InnoDB自动创建自适应哈希索引，无需额外SQL代码

• 1.

• 索引不起作用条件：

登录后复制

-- 查询使用了非等值查询
SELECT * FROM users WHERE username LIKE 'user%'; -- 索引不起作用

-- 查询使用了索引列的范围查询
SELECT * FROM users WHERE username > 'user1'; -- 索引不起作用

• 1.
• 2.
• 3.
• 4.
• 5.

13. 覆盖索引(Index-Only Scan)

覆盖索引包含查询所需的所有字段，查询无需访问数据行。

构建原理：索引包含查询所需的所有列，查询可以直接从索引中获取数据。

设计目标：避免访问主表数据，直接从索引中读取所需数据。

业务场景：查询只需要索引列，如获取商品名称和价格。

• 场景表结构：

登录后复制

CREATE TABLE products (
    product_id INT NOT NULL AUTO_INCREMENT COMMENT '商品ID',
    product_name VARCHAR(255) NOT NULL COMMENT '商品名称',
    price DECIMAL(10, 2) NOT NULL COMMENT '商品价格',
    INDEX idx_cover_product_name_price (product_name, price) COMMENT '覆盖索引'
) COMMENT '商品信息表';

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.

• 场景索引代码：

登录后复制

-- 创建覆盖索引
ALTER TABLE products ADD INDEX idx_cover_product_name_price (product_name, price);

• 1.
• 2.

• 索引不起作用条件：

登录后复制

-- 查询需要访问非索引列
SELECT * FROM products WHERE product_name = 'iPhone'; -- 索引不起作用，因为查询了非索引列product_id

-- 查询使用了索引不支持的操作符
SELECT * FROM products WHERE product_name LIKE '%Phone'; -- 索引不起作用

• 1.
• 2.
• 3.
• 4.
• 5.

14. 部分索引(Partial Index)

部分索引只对满足特定条件的记录创建索引。

构建原理：基于表中满足特定条件的行创建索引。

设计目标：优化特定条件下的查询性能。

业务场景：只对活跃用户创建索引，查询活跃用户信息。

• 场景表结构：

登录后复制

CREATE TABLE users (
    user_id INT NOT NULL AUTO_INCREMENT COMMENT '用户ID',
    is_active BOOLEAN NOT NULL DEFAULT TRUE COMMENT '是否活跃',
    INDEX idx_active_users (user_id) WHERE is_active COMMENT '活跃用户索引'
) COMMENT '用户信息表';

• 1.
• 2.
• 3.
• 4.
• 5.

• 场景索引代码：

登录后复制

-- 创建部分索引
ALTER TABLE users ADD INDEX idx_active_users (user_id) WHERE is_active;

• 1.
• 2.

• 索引不起作用条件：

登录后复制

-- 查询包含了非活跃用户
SELECT * FROM users WHERE is_active = FALSE; -- 索引不起作用

-- 查询没有使用索引列
SELECT * FROM users WHERE user_id = 1; -- 索引不起作用，因为is_active条件未满足

• 1.
• 2.
• 3.
• 4.
• 5.

15. 函数索引(Functional Index)

函数索引基于列值的函数结果创建，优化特定函数查询。

构建原理：基于列值的函数结果创建索引。

设计目标：优化基于列值函数结果的查询。

业务场景：基于用户邮箱的特定格式处理后的查询。

• 场景表结构：

登录后复制

CREATE TABLE users (
    user_id INT NOT NULL AUTO_INCREMENT COMMENT '用户ID',
    email VARCHAR(255) NOT NULL COMMENT '用户邮箱',
    INDEX idx_func_email (LOWER(email)) COMMENT '基于函数的邮箱索引'
) COMMENT '用户信息表';

• 1.
• 2.
• 3.
• 4.
• 5.

• 场景索引代码：

登录后复制

-- 创建函数索引
ALTER TABLE users ADD INDEX idx_func_email (LOWER(email));

• 1.
• 2.

• 索引不起作用条件：

登录后复制

-- 查询使用了不同的函数或未应用函数
SELECT * FROM users WHERE email = 'USER@example.com'; -- 索引不起作用，因为未转换为小写

-- 查询使用了索引列的范围查询
SELECT * FROM users WHERE LOWER(email) > 'user@example.com'; -- 索引不起作用

• 1.
• 2.
• 3.
• 4.
• 5.

16. 索引分区

索引分区将大型索引分割为多个分区，提高索引管理查询效率。

构建原理：将大型索引分割成多个更小的分区，以提高索引的管理和查询效率。

设计目标：优化大型表的索引性能。

业务场景：大型订单数据表，按年份分区索引。

• 场景表结构：

登录后复制

CREATE TABLE orders (
    order_id INT NOT NULL AUTO_INCREMENT COMMENT '订单ID',
    order_date DATE NOT NULL COMMENT '订单日期',
    INDEX idx_order_date (order_date) COMMENT '订单日期索引'
) COMMENT '订单信息表';

• 1.
• 2.
• 3.
• 4.
• 5.

• 场景索引代码：

登录后复制

-- 索引分区通常由存储引擎自动管理，但可以通过分区表来实现

-- 例如，创建按年份分区的表
CREATE TABLE orders_partitioned (
    order_id INT NOT NULL AUTO_INCREMENT COMMENT '订单ID',
    order_date DATE NOT NULL COMMENT '订单日期',
    PRIMARY KEY (order_id, order_date) COMMENT '订单ID和订单日期的组合索引'
    ) PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025)
-- 其他分区...
);

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.

• 索引不起作用条件：

登录后复制

-- 查询跨越多个分区
SELECT * FROM orders_partitioned WHERE order_date >= '2023-01-01' AND order_date < '2025-01-01'; -- 索引可能部分起作用

-- 查询没有使用分区键
SELECT * FROM orders_partitioned WHERE order_id = 100; -- 索引不起作用，因为条件中没有使用order_date

• 1.
• 2.
• 3.
• 4.
• 5.

17. 外键索引(Foreign Key Index)

外键索引确保数据的引用完整性，通常由外键约束自动创建。

构建原理：基于外键列创建索引，保证数据的引用完整性。

设计目标：确保外键列的唯一性或唯一性组合，加速外键关联查询。

业务场景：订单表和用户表之间的外键关系。

• 场景表结构：

登录后复制

CREATE TABLE customers (
    customer_id INT NOT NULL AUTO_INCREMENT COMMENT '客户ID',
    PRIMARY KEY (customer_id) COMMENT '客户ID主键索引'
) COMMENT '客户信息表';

CREATE TABLE orders (
    order_id INT NOT NULL AUTO_INCREMENT COMMENT '订单ID',
    customer_id INT NOT NULL COMMENT '客户ID',
    INDEX fk_customer_id (customer_id) COMMENT '外键索引',
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id) COMMENT '外键约束'
) COMMENT '订单信息表';

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.

• 场景索引代码：

登录后复制

-- 外键自动创建索引，无需额外SQL代码

• 1.

• 索引不起作用条件：

登录后复制

-- 查询没有使用外键列
SELECT * FROM orders WHERE order_id = 1; -- 索引不起作用，因为条件中没有使用customer_id

-- 插入违反外键约束的数据
INSERT INTO orders (customer_id) VALUES (999); -- 索引阻止插入，因为没有对应的customer_id

• 1.
• 2.
• 3.
• 4.
• 5.

原创作者: xiaogeit 转载于: https://blog.51cto.com/xiaogeit/11639542