真的，MySQL索引入门看这个就够了

MySQL索引入门指南

在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构。它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。以下是MySQL索引的详细入门指南。

一、索引的分类

1. 按数据结构分类

   B+tree索引：B+树索引类型也是MySQL存储引擎采用最多的索引类型。B+树索引具有以下特性：

   叶子节点才存放数据，非叶子节点只存放索引。

   每个节点里的数据是按主键顺序存放。

   每一个叶子节点都有两个指针，分别指向下一个叶子节点和上一个叶子节点，形成一个双向链表。

   Hash索引：基于哈希表实现，适用于等值查询，不支持范围查询。

   Full-text索引：全文索引，适用于全文搜索。

   

   InnoDB存储引擎中，B+Tree索引类型因其高效的查询效率而被广泛使用。一个高度为3的B+tree就能存千万条记录，查询一个数据的磁盘I/O次数维持在3-4次。

2. 按物理存储分类

   聚簇索引：叶子节点存放的是一行完整的用户记录，叶子节点间按主键id递增。

   二级索引（辅助索引）：子节点存放的是主键值，而不是实际数据。

   

   回表：如果查询的数据不在二级索引里，就会先检索二级索引，找到对应的叶子节点，获取到主键值后，再通过检索主键索引查询到数据的过程。

   覆盖索引：在查询时使用了二级索引，如果查询的列在二级索引里查询的到，那么就不需要回表。

3. 按字段特性分类

   主键索引：建立在主键上的索引被称为主键索引，一张数据表只能有一个主键索引，索引列值不允许有空值。

   唯一索引：建立在UNIQUE字段上的索引被称为唯一索引，一张表可以有多个唯一索引，索引列值允许为空。

   普通索引：建立在普通字段上的索引被称为普通索引。

   前缀索引：对字符类型字段的前几个字符或对二进制类型字段的前几个bytes建立的索引，可以大大减少索引占用的存储空间，提升查询效率。

4. 按字段个数分类

   单列索引：单独一个列上建立的索引。

   联合索引（复合索引、组合索引）：建立在多列上的索引。

二、如何更好创建和使用索引

1. 只为用于搜索、排序、分组的列创建索引

   只为出现在where子句中的列、order by或group by子句中的列、连接子句中的连接列创建索引。

2. 索引列中不重复值的个数

   如果某列中不重复值的个数比例较低，说明该列包含过多重复值，过多的回表操作会影响性能。

3. 索引列的类型尽量小

   数据类型越小，索引占用的存储空间就越少，查询效率越高。

4. 前缀索引、覆盖索引

   前缀索引适用于文本字段，可以减小索引大小，提升查询速度。

   覆盖索引可以避免回表操作，提升查询性能。

5. 主键索引最好是自增的

   自增主键可以避免数据插入时的页分裂问题，提升查询效率。

6. 索引最好设置为not null

   NULL值会占用物理空间，且会使索引、索引统计和值比较更复杂。

7. 避免冗余和重复索引

   重复索引会增加维护成本，对查询没有好处。

三、索引优化的方法

1. 前缀索引优化

   对文本字段使用前缀索引，减小索引大小，提升查询速度。

2. 覆盖索引优化

   使用覆盖索引避免回表操作，提升查询性能。

3. 主键索引最好是自增的

   使用自增主键避免页分裂问题，提升查询效率。

4. 索引最好设置为NOT NULL

   避免NULL值带来的额外开销。

5. 防止索引失效

   避免以%开头的Like模糊查询、对索引列做函数操作、联合索引非最左匹配原则等导致索引失效的情况。

四、防止索引失效的常见场景

1. 以%开头的Like模糊查询

   如LIKE '%abc'，无法利用索引。

2. 对索引列做了函数、表达式计算、类型转换操作

   如WHERE YEAR(date_column) = 2023，无法利用索引。

3. 联合索引非最左匹配原则

   联合索引需要按照索引列的顺序进行匹配，否则无法利用索引。

4. 索引字段是字符串，但查询时不加单引号

   如WHERE name = 123，如果name是字符串类型，则无法利用索引。

5. 尽量使用覆盖索引，避免select *

   使用SELECT *会查询所有列，可能无法利用覆盖索引。

6. WHERE子句中，OR条件的使用

   如果OR前的条件列是索引列，而OR后的条件列不是索引列，则索引会失效。

五、使用Explain查看执行计划

使用EXPLAIN关键字可以查看SQL查询语句的执行计划，从而分析索引是否失效。执行计划中的type列表明了访问方法的类型，常见的扫描类型按执行效率从低到高排序为：all（全表扫描）、index（全索引扫描）、range（索引范围扫描）、ref（非唯一索引扫描）、eq_ref（唯一索引扫描）、const（结果只有一条的主键或唯一索引扫描）。应避免全表扫描和全索引扫描。

通过以上内容的学习，你可以对MySQL索引有一个全面的了解，并能够在实际应用中更好地创建和使用索引，提升数据库查询性能。