MySQL学习笔记集--索引

索引

索引是数据库中用于提高查询效率的一种数据结构。

它类似于书籍的目录，通过索引可以快速定位到表中的特定行，而无需扫描整个表。

索引的类型

1. 主键索引（Primary Key Index）

   • 自动创建，用于唯一标识表中的每一行。

   CREATE TABLE users (
       id INT PRIMARY KEY,
       name VARCHAR(50)
   );
   

2. 唯一索引（Unique Index）

   • 确保索引列中的值唯一。

   CREATE UNIQUE INDEX idx_name ON users (name);
   

3. 普通索引（Index）

   • 提高查询效率，允许重复值。

   CREATE INDEX idx_age ON users (age);
   

4. 组合索引（Composite Index）

   • 在多个列上创建索引，提高多列查询的效率。

   CREATE INDEX idx_name_age ON users (name, age);
   

5. 全文索引（Full-Text Index）

   • 用于全文搜索，适用于文本字段。

   CREATE FULLTEXT INDEX idx_content ON articles (content);
   

6. 空间索引（Spatial Index）

   • 用于地理数据类型。

   CREATE SPATIAL INDEX idx_location ON locations (coordinates);
   

索引的创建

1. 创建表时创建索引

   CREATE TABLE users (
       id INT PRIMARY KEY,
       name VARCHAR(50),
       age INT,
       INDEX idx_age (age)
   );
   

2. 在现有表上创建索引

   CREATE INDEX idx_name ON users (name);
   

3. 创建组合索引

   CREATE INDEX idx_name_age ON users (name, age);
   

4. 创建唯一索引

   CREATE UNIQUE INDEX idx_email ON users (email);
   

索引的使用

1. 查询优化

   • 使用EXPLAIN语句查看查询执行计划，检查索引是否被使用。

   EXPLAIN SELECT * FROM users WHERE age = 30;
   

2. 索引的覆盖

   • 确保查询的所有列都在索引中，以提高查询效率。

   SELECT name, age FROM users WHERE age = 30; -- 覆盖索引idx_name_age
   

3. 索引的更新

   • 索引会随着数据的插入、更新和删除自动维护。

索引的维护

1. 更新索引统计信息

   ANALYZE TABLE users;
   

2. 重建索引

   ALTER TABLE users DROP INDEX idx_age;
   ALTER TABLE users ADD INDEX idx_age (age);
   

3. 删除索引

   DROP INDEX idx_age ON users;
   

索引的性能优化

1. 选择合适的索引类型

   • 根据查询需求选择主键索引、唯一索引或普通索引。

2. 避免索引滥用

   • 索引虽然提高查询效率，但会增加插入、更新和删除的开销。

3. 使用前缀索引

   • 对于长文本字段，可以创建前缀索引以减少索引大小。

   CREATE INDEX idx_content ON articles (content(255));
   

4. 定期维护索引

   • 定期更新索引统计信息，确保查询优化器能够正确选择索引。