垦荒学园
  • 中文

      • 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命令

      下一个：临时表