{"msg":"操作成功","code":200,"data":{"createBy":"admin","createTime":"2020-04-05 21:58:02","updateBy":"admin","updateTime":"2020-04-05 21:58:02","remark":null,"id":24,"articleTitle":"MySQL（六）索引INDEX","articleUrl":"mysql_index","articleThumbnail":"https://www.asumimoe.com/imgfiles/20220908/9e192744fab244d39d3b15fca035c9f7.jpg","articleFlag":"0","draftStatus":"1","reprintStatement":"1","articleSummary":"索引是MySQL性能优化的核心组件，它通过特殊的数据结构加速数据检索。理解索引原理和正确使用索引是数据库优化的关键。","articleContent":"索引是MySQL性能优化的核心组件，它通过特殊的数据结构加速数据检索。理解索引原理和正确使用索引是数据库优化的关键。\n\n## 索引的基本概念\n\n### 1. 什么是索引？\n\n索引是数据库中一种特殊的数据结构，类似于书籍的目录，它存储了表中特定列的值及其在数据表中的位置信息，用于快速定位数据。\n\n### 2 .索引的作用\n\n- **加速数据检索**：减少全表扫描，提高查询效率\n- **保证数据唯一性**：唯一索引确保列值的唯一性\n- **加速表连接**：提高JOIN操作效率\n- **优化排序和分组**：减少ORDER BY和GROUP BY操作的成本\n\n## 索引的数据结构\n\n### 1. B+树索引（默认索引类型）\n\n```mermaid\ngraph TD\n    A[根节点] --> B[非叶子节点]\n    A --> C[非叶子节点]\n    B --> D[叶子节点]\n    B --> E[叶子节点]\n    C --> F[叶子节点]\n    C --> G[叶子节点]\n    D --> H[数据指针1]\n    E --> I[数据指针2]\n    F --> J[数据指针3]\n    G --> K[数据指针4]\n```\n\n**特点**：\n\n- 多叉平衡树结构\n- 所有数据存储在叶子节点\n- 叶子节点形成有序链表\n- 非叶子节点只存储索引键值\n\n**优势**：\n\n- 范围查询高效\n- 插入/删除操作平衡性好\n- 适合磁盘I/O优化\n\n### 2.哈希索引\n\n- 基于哈希表实现\n- 仅支持等值查询（=, IN）\n- 不支持范围查询和排序\n- Memory存储引擎默认索引\n\n### 3.全文索引（FULLTEXT）\n\n- 用于文本内容的搜索\n- 支持自然语言搜索\n- 仅适用于MyISAM和InnoDB（5.6+）\n\n### 4.R-Tree索引\n\n- 用于空间数据类型（GEOMETRY）\n- 支持GIS地理空间查询\n\n## 索引的类型\n\n### 1.按功能分类\n\n| 索引类型 | 关键字      | 特点                     | 示例                        |\n| -------- | ----------- | ------------------------ | --------------------------- |\n| 主键索引 | PRIMARY KEY | 唯一且非空，表只能有一个 | `id INT PRIMARY KEY`        |\n| 唯一索引 | UNIQUE      | 值唯一，允许多个NULL     | `email VARCHAR(100) UNIQUE` |\n| 普通索引 | INDEX/KEY   | 基本索引类型             | `INDEX idx_name (name)`     |\n| 全文索引 | FULLTEXT    | 用于文本搜索             | `FULLTEXT (content)`        |\n| 空间索引 | SPATIAL     | 用于地理空间数据         | `SPATIAL INDEX (location)`  |\n\n### 2.按物理实现分类\n\n1. **聚簇索引（Clustered Index）**\n   - InnoDB的主键索引\n   - 索引与数据存储在一起\n   - 表数据按主键顺序物理存储\n\n2. **非聚簇索引（Secondary Index）**\n   - 普通索引\n   - 索引与数据分离存储\n   - 通过主键值指向实际数据行\n\n## 索引的创建与管理\n\n### 1.创建索引\n\n```sql\n-- 创建表时定义索引\nCREATE TABLE users (\n    id INT AUTO_INCREMENT PRIMARY KEY,\n    username VARCHAR(50) NOT NULL,\n    email VARCHAR(100) NOT NULL,\n    age INT,\n    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n    UNIQUE INDEX idx_email (email),\n    INDEX idx_age (age),\n    INDEX idx_username_email (username, email)\n);\n\n-- 为已有表添加索引\nALTER TABLE users ADD INDEX idx_created_at (created_at);\nCREATE INDEX idx_age_name ON users(age, username);\n```\n\n### 2.查看索引\n\n```sql\nSHOW INDEX FROM users;\n\n-- 输出示例\n+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+\n| Table | Non_unique | Key_name          | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |\n+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+\n| users |          0 | PRIMARY           |            1 | id          | A         |       10000 |     NULL | NULL   |      | BTREE      |         |               |\n| users |          0 | idx_email         |            1 | email       | A         |       10000 |     NULL | NULL   |      | BTREE      |         |               |\n| users |          1 | idx_age           |            1 | age         | A         |          20 |     NULL | NULL   | YES  | BTREE      |         |               |\n+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+\n```\n\n### 3.删除索引\n\n```sql\nALTER TABLE users DROP INDEX idx_age;\nDROP INDEX idx_email ON users;\n```\n\n## 索引优化策略\n\n### 1.索引设计原则\n\n1. **选择高选择性列**：索引列不重复值比例要高\n\n   ```sql\n   -- 计算选择性\n   SELECT COUNT(DISTINCT gender)/COUNT(*) FROM users; -- 0.02（低）\n   SELECT COUNT(DISTINCT email)/COUNT(*) FROM users; -- 0.99（高）\n   ```\n\n2. **遵循最左前缀原则**：组合索引(a,b,c)可优化：\n\n   - WHERE a = ?\n   - WHERE a = ? AND b = ?\n   - WHERE a = ? AND b = ? AND c = ?\n\n3. **避免过度索引**：每个索引增加写操作成本\n\n4. **考虑索引覆盖**：查询列都在索引中\n\n   ```sql\n   -- 索引覆盖示例\n   CREATE INDEX idx_covering ON users(age, username);\n   SELECT age, username FROM users WHERE age > 25; -- 无需回表\n   ```\n\n### 2.索引使用最佳实践\n\n1. **避免索引列参与计算**\n\n   ```sql\n   -- 不推荐\n   SELECT * FROM users WHERE YEAR(created_at) = 2023;\n   \n   -- 推荐\n   SELECT * FROM users \n   WHERE created_at BETWEEN '2023-01-01' AND '2023-12-31';\n   ```\n\n2. **注意LIKE查询模式**\n\n   ```sql\n   -- 可使用索引\n   SELECT * FROM users WHERE username LIKE 'john%';\n   \n   -- 无法使用索引\n   SELECT * FROM users WHERE username LIKE '%john%';\n   ```\n\n3. **合理使用OR条件**\n\n   ```sql\n   -- 优化前（可能全表扫描）\n   SELECT * FROM users WHERE age < 20 OR age > 30;\n   \n   -- 优化后\n   SELECT * FROM users WHERE age < 20\n   UNION\n   SELECT * FROM users WHERE age > 30;\n   ```\n\n## 索引性能分析\n\n### 1.EXPLAIN命令详解\n\n```sql\nEXPLAIN SELECT * FROM users WHERE age > 30;\n```\n\n**关键字段解析**：\n\n- **type**：访问类型（性能排序）\n  - system > const > eq_ref > ref > range > index > ALL\n- **key**：实际使用的索引\n- **rows**：预估扫描行数\n- **Extra**：额外信息\n  - Using index：索引覆盖\n  - Using where：存储引擎返回行后过滤\n  - Using filesort：需要额外排序\n  - Using temporary：使用临时表\n\n### 3.索引效率诊断\n\n```sql\n-- 查看索引使用统计\nSELECT * FROM sys.schema_index_statistics \nWHERE table_schema = 'your_db' AND table_name = 'users';\n\n-- 检查未使用索引\nSELECT * FROM sys.schema_unused_indexes;\n```\n\n## 索引的代价与维护\n\n### 1.索引的代价\n\n1. **存储空间**：索引占用磁盘空间\n2. **写操作成本**：\n   - INSERT：需更新所有索引\n   - UPDATE：更新影响的所有索引\n   - DELETE：删除索引项\n3. **维护成本**：索引碎片影响性能\n\n### 2.索引维护操作\n\n```sql\n-- 重建索引（InnoDB）\nALTER TABLE users ENGINE=InnoDB;\n\n-- 优化表（重组数据+重建索引）\nOPTIMIZE TABLE users;\n\n-- 分析索引统计信息\nANALYZE TABLE users;\n```\n\n### 3.监控索引性能\n\n```sql\n-- 查看索引读写统计\nSELECT index_name, rows_read, rows_inserted, rows_updated, rows_deleted\nFROM performance_schema.table_io_waits_summary_by_index_usage\nWHERE OBJECT_SCHEMA = 'your_db' AND OBJECT_NAME = 'users';\n```\n\n## 常见索引误区\n\n1. **索引越多越好**：\n\n   - 增加写操作负担\n   - 占用存储空间\n   - 优化器选择困难\n\n2. **所有列都建索引**：\n\n   - 低选择性列索引无效\n   - 浪费资源\n\n3. **过度依赖索引**：\n\n   - 小表全表扫描更快\n   - 数据分布均匀时索引可能无效\n\n4. **忽视组合索引顺序**：\n\n   - 错误顺序使索引失效\n\n   ```sql\n   -- 索引 (a,b) 无法优化 WHERE b = ?\n   ```","categoryId":4,"viewCount":869,"categoryName":"MySQL","author":"球接子","authorAvatar":null,"tagIds":[2,17],"tagNames":["MySQL","数据库"]}}