MySQL中的索引基本使用详解。

MySQL中的索引基本使用详解

索引是MySQL中用于提高查询性能的核心机制，本文将系统讲解索引的基本概念、类型、使用方法及注意事项。

---

一、索引基础概念

1. 什么是索引？

索引是数据库表的特殊数据结构（通常是B+树），用于加速数据检索，类似书籍的目录。

类比：

• 数据库表 = 书籍内容
• 索引 = 书籍目录
• 查询 = 通过目录快速定位内容

2. 索引的优缺点

优点	缺点
加速数据检索	占用存储空间
加速表连接操作	降低写操作速度(INSERT/UPDATE/DELETE)
减少服务器需要扫描的数据量	需要维护成本
为排序和分组优化	过度使用可能导致性能下降

---

二、索引类型详解

1. 普通索引(INDEX)

最基本的索引类型，没有任何限制条件。

	-- 创建表时添加普通索引
	CREATE TABLE users (
	    id INT PRIMARY KEY,
	    username VARCHAR(50),
	    email VARCHAR(100),
	    INDEX idx_username (username)
	);
	-- 已有表添加普通索引
	ALTER TABLE users ADD INDEX idx_email (email);

2. 唯一索引(UNIQUE)

确保索引列的值唯一，允许NULL值（但只能有一个NULL）。

	-- 创建唯一索引
	CREATE TABLE products (
	    id INT PRIMARY KEY,
	    product_code VARCHAR(20),
	    UNIQUE INDEX uk_product_code (product_code)
	);

3. 主键索引(PRIMARY KEY)

特殊的唯一索引，不允许NULL值，一张表只能有一个主键。

	-- 创建表时定义主键
	CREATE TABLE orders (
	    order_id INT AUTO_INCREMENT,
	    customer_id INT,
	    order_date DATETIME,
	    PRIMARY KEY (order_id)
	);

4. 复合索引(多列索引)

在多个列上创建的索引，遵循最左前缀原则。

	-- 创建复合索引
	CREATE TABLE order_items (
	    id INT PRIMARY KEY,
	    order_id INT,
	    product_id INT,
	    quantity INT,
	    INDEX idx_order_product (order_id, product_id)  -- 复合索引
	);

5. 全文索引(FULLTEXT)

用于全文搜索，仅适用于MyISAM和InnoDB(MySQL 5.6+)引擎。

	-- 创建全文索引
	CREATE TABLE articles (
	    id INT PRIMARY KEY,
	    title VARCHAR(200),
	    content TEXT,
	    FULLTEXT INDEX ft_content (title, content)
	) ENGINE=InnoDB;

6. 空间索引(SPATIAL)

用于地理空间数据类型(GEOMETRY)。

	-- 创建空间索引
	CREATE TABLE locations (
	    id INT PRIMARY KEY,
	    name VARCHAR(100),
	    coordinates POINT,
	    SPATIAL INDEX sp_index (coordinates)
	) ENGINE=MyISAM;  -- 注意：InnoDB从MySQL 5.7.5开始支持空间索引

---

三、索引使用方法

1. 创建索引

创建表时指定索引

	CREATE TABLE employees (
	    id INT PRIMARY KEY,
	    first_name VARCHAR(50),
	    last_name VARCHAR(50),
	    email VARCHAR(100),
	    department_id INT,
	    hire_date DATE,
	    INDEX idx_last_name (last_name),
	    INDEX idx_dept_hire (department_id, hire_date)  -- 复合索引
	);

已有表添加索引

	-- 添加普通索引
	ALTER TABLE employees ADD INDEX idx_email (email);
	-- 添加唯一索引
	ALTER TABLE employees ADD UNIQUE INDEX uk_email (email);
	-- 添加复合索引
	ALTER TABLE employees ADD INDEX idx_name (first_name, last_name);

2. 查看索引

	-- 查看表的索引信息
	SHOW INDEX FROM employees;
	-- 或使用
	SHOW KEYS FROM employees;

3. 删除索引

	-- 删除普通索引
	ALTER TABLE employees DROP INDEX idx_email;
	-- 删除主键索引（主键是特殊的唯一索引）
	ALTER TABLE employees DROP PRIMARY KEY;

---

四、索引使用原则

1. 索引选择策略

• 高选择性列优先：选择区分度高的列（如身份证号比性别更适合建索引）
• 常用查询条件列：WHERE、JOIN、ORDER BY、GROUP BY中频繁出现的列
• 复合索引顺序：遵循最左前缀原则，将选择性高的列放在前面

2. 复合索引最佳实践

	-- 假设有查询：
	SELECT * FROM orders 
	WHERE customer_id = 100 AND order_date > '2023-01-01' 
	ORDER BY total_amount;
	-- 最佳索引设计：
	ALTER TABLE orders ADD INDEX idx_cust_date_amount (
	    customer_id,  -- 精确匹配列
	    order_date,   -- 范围查询列
	    total_amount  -- 排序列
	);

3. 索引失效场景

• 使用函数操作索引列：

  	-- 错误示例：索引失效
  	SELECT * FROM users WHERE YEAR(create_time) = 2023;
  	-- 正确做法：
  	SELECT * FROM users WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31';
  

• 使用不等于操作：

  	-- 索引可能失效
  	SELECT * FROM products WHERE status != 'inactive';
  

• 使用LIKE以通配符开头：

  	-- 索引失效
  	SELECT * FROM users WHERE username LIKE '%john';
  	-- 索引有效
  	SELECT * FROM users WHERE username LIKE 'john%';
  

• 使用OR条件（除非所有条件列都有索引）：

  	-- 只有当name和email都有索引时才有效
  	SELECT * FROM users WHERE name = 'John' OR email = 'john@example.com';
  

4. 索引覆盖查询

当查询的所有列都包含在索引中时，MySQL可以直接从索引获取数据而不需要回表。

	-- 创建覆盖索引
	ALTER TABLE users ADD INDEX idx_name_email (name, email);
	-- 覆盖查询示例（只查询索引列）
	SELECT name, email FROM users WHERE name = 'John';

---

五、索引维护建议

1. 定期分析表：

   	ANALYZE TABLE users;  -- 更新索引统计信息
   

2. 监控索引使用情况：

   	-- 查看未使用的索引（MySQL 5.6+）
   	SELECT * FROM performance_schema.table_io_waits_summary_by_index_usage 
   	WHERE INDEX_NAME IS NOT NULL 
   	AND COUNT_STAR = 0 
   	AND OBJECT_SCHEMA = 'your_database';
   

3. 避免过度索引：
   每个索引都会增加写操作的开销，只创建必要的索引

4. 考虑索引大小：
   大字段(如TEXT)不适合作为索引列，可以考虑使用前缀索引

---

六、常见问题解答

1. 索引越多越好吗？

不是。每个索引都会：

• 占用存储空间
• 降低写操作性能
• 增加优化器选择索引的复杂度

建议：只创建真正需要的索引，定期清理未使用的索引。

2. 为什么我的索引没有被使用？

常见原因：

• 查询条件不符合最左前缀原则
• 使用了索引失效的操作（函数、类型转换等）
• 统计信息不准确（执行ANALYZE TABLE）
• 优化器选择了错误的执行计划

3. 前缀索引怎么用？

	-- 对VARCHAR列只索引前10个字符
	ALTER TABLE users ADD INDEX idx_username_prefix (username(10));

适用场景：当列值很长但前缀区分度足够时。

4. 索引和主键有什么区别？

特性	主键	索引
唯一性	必须唯一	普通索引可以重复
NULL值	不允许	普通索引允许NULL
数量限制	每表一个	可以创建多个
隐式创建	创建表时必须指定	可显式创建

---

通过掌握这些索引基础知识，你可以有效提升MySQL查询性能。在实际应用中，建议结合EXPLAIN分析查询执行计划，验证索引是否被正确使用。