mysql索引类型有哪些 mysql创建不同索引的方法对比

MySQL 支持多种索引类型，每种类型适用于不同的查询场景和数据特征。以下是主要索引类型及其创建方法的详细说明：

1. B-Tree 索引

   适用场景：等值查询、范围查询、排序操作。

   特点：

   默认索引类型（InnoDB 和 MyISAM 引擎）。

   支持全键值、键值范围和键前缀查找。

   顺序存储特性适合范围查询。

   缺点：对高基数列（大量不同值）效果可能不佳。

2. Hash 索引

   适用场景：仅等值查询。

   特点：

   基于哈希表实现，查询速度极快。

   不支持范围查询和排序（哈希表无序）。

   限制：

   仅 Memory 引擎显式支持。

   InnoDB 的自适应 Hash 索引由引擎自动管理，不可干预。

3. Fulltext 索引（全文索引）

   适用场景：文本搜索（如关键词查找）。

   特点：

   MySQL 5.6 后 InnoDB 引擎支持。

   专门为文本搜索优化。

   缺点：维护成本高，占用空间大。

4. 空间数据索引（R-Tree）

   适用场景：地理空间数据查询（如地理位置、地图）。

   特点：

   专门为地理空间数据优化。

   缺点：实现复杂，维护成本高。

MySQL 创建索引主要通过 CREATE INDEX 和 ALTER TABLE 语句实现，语法如下：

• UNIQUE：创建唯一索引，列值必须唯一。
• FULLTEXT：创建全文索引。
• SPATIAL：创建空间数据索引。
• index_type：指定索引类型（如 USING BTREE、USING HASH）。
• WITH PARSER：指定全文索引的解析器（如 ngram 用于中文分词）。

1. 创建 B-Tree 索引

   CREATE INDEX idx_name ON users (name);ALTER TABLE users ADD INDEX idx_email (email);

2. 创建唯一索引

   CREATE UNIQUE INDEX idx_username ON users (username);ALTER TABLE users ADD UNIQUE INDEX idx_phone (phone);

3. 创建全文索引

   CREATE FULLTEXT INDEX idx_content ON articles (content);ALTER TABLE articles ADD FULLTEXT INDEX idx_title (title) WITH PARSER ngram;

4. 创建复合索引

   CREATE INDEX idx_name_email ON users (name, email);ALTER TABLE users ADD INDEX idx_city_age (city, age);

1. 选择性原则

   只为必要列创建索引，避免过度索引导致写入性能下降。

   选择性高的列（如唯一值多的列）优先建索引。

2. 索引类型选择

   等值查询为主：Hash 索引。

   范围查询为主：B-Tree 索引。

   文本搜索：Fulltext 索引。

   地理空间数据：R-Tree 索引。

3. 复合索引优化

   将选择性高的列放在复合索引的前列。

   避免冗余索引（如已有 (A,B)，再建 (A) 是冗余的）。

4. 定期维护

   重建索引消除碎片：ALTER TABLE table_name ENGINE=InnoDB;

   删除未使用的索引：通过 Performance Schema 监控索引使用情况。

1. 未使用最左前缀

   复合索引 (A,B,C)，查询条件为 B=1 AND C=2 时索引失效。

2. LIKE 模糊查询以 % 开头

   WHERE name LIKE '%张%' 无法使用索引。

3. 隐式类型转换

   列类型为 VARCHAR，但查询条件使用数字（如 WHERE id='123' 而非 WHERE id=123）。

4. OR 条件未全列索引

   WHERE A=1 OR B=2，若 A 和 B 未同时建索引，则索引失效。

1. 使用 EXPLAIN 分析查询

   EXPLAIN SELECT * FROM users WHERE name = 'John' AND age > 20;

   关注 type 列（如 range、ref 表示使用索引，ALL 表示全表扫描）。

   关注 key 列（显示实际使用的索引）。

2. 监控工具

   Performance Schema：提供索引使用频率、扫描行数等统计。

   sys schema：简化 Performance Schema 的数据展示。

• 写入开销：索引越多，插入、更新、删除操作需要维护的索引越多，写入性能下降越明显。
• 权衡策略：

  高频查询列建索引，低频查询列不建。

  避免为宽表（列多）的每一列建索引。

• 索引类型选择：根据查询类型（等值、范围、文本、空间）选择 B-Tree、Hash、Fulltext 或 R-Tree。
• 创建方法：优先使用 CREATE INDEX 或 ALTER TABLE，结合 UNIQUE、FULLTEXT 等关键字。
• 优化方向：定期监控索引使用情况，删除冗余索引，重建碎片化索引，调整复合索引顺序。

通过合理设计索引，可以在查询性能和写入性能之间取得平衡，提升数据库整体效率。