MySQL索引详解与优化

索引介绍

索引是什么

索引是MySQL中用于加速数据检索的一种数据结构。它类似于书籍的目录，能够显著提高查询效率，减少磁盘I/O操作。

索引的优劣势

优势：

• 提高查询性能，降低I/O成本。


• 通过索引列排序，减少CPU消耗。


• 辅助索引可以避免全表扫描，提高查询速度。


• 覆盖索引减少了回表查询的次数，进一步提升性能。

劣势：

• 占用额外的磁盘空间。


• 增加插入、更新和删除操作的成本。


• 需要维护索引文件，增加了存储负担。

索引的分类

MySQL支持多种类型的索引，包括单列索引、组合索引、全文索引、空间索引等。

单列索引

• 普通索引：
  

  CREATE INDEX index_name ON table(column(length));
  ALTER TABLE table_name ADD INDEX index_name(column(length));



• 唯一索引：
  

  CREATE UNIQUE INDEX index_name ON table(column(length));
  ALTER TABLE table_name ADD UNIQUE INDEX index_name(column(length));

组合索引

ALTER TABLE article ADD INDEX index_name_time (title(50), time(10));

删除索引

DROP INDEX index_name ON table;

查看索引

SHOW INDEX FROM table_name \G;

索引原理分析

索引的存储结构

• 索引是在存储引擎层面实现的，不同存储引擎使用不同的索引类型。


• MyISAM和InnoDB存储引擎仅支持B+树索引。


• MEMORY/HEAP存储引擎支持HASH和B-TREE索引。

非聚集索引（MyISAM）

在MyISAM中，索引和数据是分开存储的。主键索引和辅助索引都是B+树结构，存储的是每一行数据的地址。

聚集索引（InnoDB）

InnoDB使用聚集索引将数据存储在主键索引上。如果没有显式定义主键，InnoDB会自动选择一个唯一字段或生成一个伪列作为主键。

利用组合索引形成覆盖索引

通过组合索引，可以在不回表的情况下完成查询，从而提高查询效率。

索引的使用场景

哪些情况需要创建索引

1. 主键自动建立唯一索引。


2. 频繁作为查询条件的字段应创建索引。


3. 多表关联查询中，关联字段应创建索引。


4. 查询中排序的字段应创建索引。


5. 统计或分组字段应创建索引。

哪些情况不需要创建索引

1. 表记录较少时，索引的开销可能超过其带来的收益。


2. 频繁更新的字段不适合创建索引。


3. 查询频率低的字段通常不需要索引。

索引失效

查看执行计划

使用EXPLAIN命令可以查看SQL语句的执行计划，了解查询是否使用了索引。

• id：每个查询都会分配一个唯一的ID，表示查询的顺序。


• select_type：查询类型，如简单查询、子查询、联合查询等。


• table：显示查询涉及的表名。


• type：查询类型，从最好到最差依次为system、const、eq_ref、ref、range、index、ALL。


• possible_keys：可能使用的索引。


• key：实际使用的索引。


• key_len：索引长度，用于判断是否使用了整个索引。


• ref：显示引用的列或常量。


• rows：估算的扫描行数。


• extra：包含额外信息，如using where、using index、using filesort等。

索引失效分析

• 索引全值匹配：条件与索引一一对应。


• 最左前缀原则：查询必须从索引的最左前列开始，不能跳过中间列。


• 避免在索引上进行计算或函数操作。


• 范围条件右边的列失效。


• 尽量使用覆盖索引。


• 避免在索引字段上使用不等号。


• 主键索引字段不可使用NULL判断。


• 索引字段使用LIKE时不以通配符开头。


• 索引字段字符串要加单引号。


• 避免在索引字段上使用OR。