MySQL索引

索引介绍

索引是提高数据库表数据检索速度的数据库对象。它们像书籍索引一样工作，允许快速查找数据而无需扫描整个表。

索引如何工作

没有索引：全表扫描 有索引：使用B树结构快速查找

索引的好处

更快的SELECT查询 - 特别是带WHERE、JOIN、ORDER BY子句 强制唯一性 - PRIMARY KEY和UNIQUE约束 更快的排序 - ORDER BY操作 O** - Less data read from disk / 减少I/O - 从磁盘读取更少数据

索引的缺点

存储开销 - 索引消耗磁盘空间 写入变慢 - INSERT、UPDATE、DELETE需要维护索引 维护 - 索引需要定期重建

索引类型

主键索引

自动为主键约束创建。

唯一索引

确保列中的所有值都是唯一的。

-- Add unique index to existing table

普通索引

用于加快数据检索的基本索引。

-- Add index to existing table

全文索引

用于对文本列进行全文搜索。

-- For InnoDB and MyISAM tables

-- Use MATCH AGAINST for searching

-- Natural language mode

-- Boolean mode

空间索引

用于空间数据类型（POINT、GEOMETRY等）。

-- Query using spatial functions

复合（多列）索引

在多个列上的索引。

-- Composite index column order matters
-- Index can be used for:
-- - user_id alone
-- - user_id AND status
-- But NOT for status alone (without user_id)

创建索引

在新表上创建索引

    
    -- Indexes

在现有表上添加索引

-- Add simple index

-- Add unique index

-- Add composite index

-- Add full-text index (MySQL 5.6+)

-- Add spatial index

显式索引名称

-- Custom index name

管理索引

查看索引

-- Show indexes on a table

-- Detailed index information

-- From information_schema

删除索引

-- Drop index

-- Drop multiple indexes

-- Using ALTER TABLE

分析索引使用

-- Use EXPLAIN to see query execution

-- EXPLAIN output columns
-- id: Query identifier
-- select_type: Type of SELECT
-- table: Table being accessed
-- type: How rows are found (ALL, index, range, etc.)
-- possible_keys: Indexes available
-- key: Index actually used
-- key_len: Length of key used
-- rows: Rows examined
-- Extra: Additional information

索引基数

-- Check index cardinality (unique values)

-- High cardinality: Many unique values (good for indexing)
-- Low cardinality: Few unique values (less effective)

索引优化策略

选择正确的列

-- Index columns used in WHERE clauses

-- Index columns used in JOINs

-- Index ORDER BY columns

-- Index columns in GROUP BY

复合索引顺序

-- Consider query patterns
-- Query: WHERE user_id = ? AND status = ?
-- Should have index on (user_id, status)

-- Left-to-right rule
-- Index supports queries starting from left columns

-- Can be used for:
-- WHERE user_id = 5
-- WHERE user_id = 5 AND status = 'completed'
-- But NOT for WHERE status = 'completed' alone

覆盖索引

-- Index contains all columns needed by query
-- Query doesn't need to access main table

-- Create covering index

-- This query uses covering index

-- EXPLAIN shows "Using index" in Extra column

前缀索引

-- Index on first N characters of string column
-- Useful for long text columns with repetitive prefixes

-- Create prefix index

-- Choose appropriate prefix length

避免索引

-- Don't index small tables
-- Don't index columns with low cardinality (few unique values)
-- Don't index columns frequently updated
-- Don't index columns with long text values (use prefix index)

索引类型深入

B树索引

大多数存储引擎的默认索引类型。

结构：平衡树结构 用法：等值和范围查询 支持：=、<、>、BETWEEN、LIKE 'prefix%'

哈希索引

仅Memory存储引擎。

结构：哈希表 用法：仅等值查询 支持：=、IN()、<> 不支持：范围查询、排序

全文索引

用于文本搜索功能。

-- Search modes
-- Natural language (default)

-- Boolean mode

-- Query expansion

空间（R树）索引

用于空间数据类型。

-- Spatial queries

索引维护

分析表

-- Update index cardinality

-- Check table analysis

优化表

-- Defragment table and reclaim unused space

-- Helps with:
-- - Deleted rows
-- - Split pages
-- - Index rebuilding

重建索引

-- Rebuild specific index

-- Rebuild all indexes

检查索引使用

-- Identify unused indexes (MySQL 8.0+)

索引最佳实践

何时创建索引

-- Create indexes for:
-- 1. Primary keys (automatic)
-- 2. Foreign keys (automatic for InnoDB)
-- 3. Columns in WHERE clauses
-- 4. Columns in JOIN conditions
-- 5. Columns in ORDER BY
-- 6. Columns in GROUP BY
-- 7. Columns for uniqueness

何时避免索引

-- Avoid indexes for:
-- 1. Tables with few rows
-- 2. Columns with low cardinality (few unique values)
-- 3. Columns frequently updated
-- 4. Columns with long text values (use prefix)
-- 5. Columns only in SELECT, not WHERE/JOIN/ORDER BY

监控索引性能

-- Enable performance schema

-- Check slow queries

-- Use EXPLAIN regularly

小结

索引对数据库性能至关重要：

类型：PRIMARY、UNIQUE、INDEX、FULLTEXT、SPATIAL 创建：CREATE INDEX、ALTER TABLE、在CREATE TABLE中内联 复合：单个索引中的多个列 维护：ANALYZE、OPTIMIZE、重建 优化：选择正确的列、顺序、覆盖索引

正确的索引设计可以显著提高查询性能。

---

上一个：ALTER命令

下一个：临时表