MySQL索引简介（包含索引优化，索引失效，最左前缀简洁版）

目录

一、索引的基本概念

1.什么是索引

2.索引类型

3.索引原理

4.举例说明

二、索引的优化与应用

1.索引的优化

2.优化示例

3.索引优化的原因

三、索引的优缺点

1.优点

2.缺点

四、什么情况下索引失效

?五、最左前缀（详解）

---

一、索引的基本概念

1.什么是索引

? ?索引是一种数据结构，用于快速查找和访问数据库中的数据。它类似于书本的目录，可以帮助数据库管理系统快速定位到存储数据的位置。通过创建索引，我们可以加快数据库的查询速度并提高系统的性能。索引可以基于一个或多个列，在数据库表中对数据进行逻辑和物理排序，使得查询操作更加高效。

2.索引类型

1. 主键索引（PRIMARY KEY）：每张表只能有一个主键，确保记录的唯一性。
2. 唯一索引（UNIQUE）：确保数据的每一行在索引列上是唯一的。
3. 普通索引（INDEX）：基本的索引类型，没有唯一性的要求，用于加速查询。
4. 全文索引（FULLTEXT）：用于对文本字段进行索引，加快文本搜索操作。
5. 组合索引（Composite Index）：多列值组成一个索引，用于多条件查询。

3.索引原理

MySQL中最常用的索引类型是B-Tree索引。B-Tree索引非常适合处理大量数据的访问。它保持数据有序，允许搜索、插入、删除和顺序访问数据。

4.举例说明

假设我们有一张订单表orders，字段包括order_id、customer_id和order_date。

CREATE TABLE orders (
  order_id INT AUTO_INCREMENT,
  customer_id INT,
  order_date DATE,
  PRIMARY KEY (order_id)
);

如果我们经常按照customer_id来查询，添加一个索引会是一个很好的选择：

CREATE INDEX idx_customer_id ON orders (customer_id);

现在，如果执行下面的查询，MySQL就能利用索引快速找到数据。

SELECT * FROM orders WHERE customer_id = 1001;

二、索引的优化与应用

1.索引的优化

索引虽然可以提高查询性能，但是也不是越多越好。不恰当的索引可能会导致性能问题，以下是几种优化策略：

1. 选择适当的索引列：选择具有高选择性的列作为索引，这样的列包含很多唯一的值。

2. 避免冗余索引：如果一个索引是另一个索引的前缀，则可能是多余的。

3. 使用短索引：对于字符串类型的字段，使用前缀索引可以节省空间和提升性能。

4. 索引维护：删除不再使用或低效的索引，重新构建碎片化的索引。

2.优化示例

假设orders表现在非常大，我们注意到customer_id和order_date常常一起出现在查询条件中。为此，我们可以建立组合索引：

CREATE INDEX idx_customer_order ON orders (customer_id, order_date);

这样，任何同时涉及customer_id和order_date的查询都可以利用这个索引。

3.索引优化的原因

优化索引的原要目的包括：

1. 提高查询效率：使用适当的索引可以减少查询的响应时间。
2. 减少资源使用：有效的索引可以减少CPU和IO资源的使用。
3. 提升写操作性能：减少不必要的索引可以加快插入和修改操作。

三、索引的优缺点

1.优点

• 查询效率提升：这是索引最显著的优点，特别是在大数据量下。
• 排序和分组加速：索引可以加快ORDER BY和GROUP BY操作。

2.缺点

• 降低更新表的性能：插入、删除、修改等写操作会导致索引也需要被更新，这将消耗额外的时间。
• 占用额外的空间：索引虽然能提高读取效率，但同时也会占用磁盘空间。
• 索引失效：在某些情况下，比如查询中使用了函数或计算，索引可能会失效，导致性能下降

四、什么情况下索引失效

? 索引可能会失效的情况有很多。下面列举了一些常见的情况，并详细解释了每种情况下为什么索引会失效：

1. 不使用索引列进行查询：
? ?- 当查询条件没有包含索引列时，数据库可能会选择忽略索引而进行全表扫描。这通常发生在查询使用的是表达式、函数或其他操作，而不是直接使用索引列的原始值。因为索引只会为存储的原始值建立索引，而不会为计算结果或表达式的值建立索引。

2. 使用函数或表达式对索引列进行操作：
? ?- 当在查询中对索引列使用函数或表达式时，数据库可能无法使用索引进行查找。例如，如果在索引列上使用`LOWER`函数将值转换为小写进行比较，索引将无法提供有效的匹配。这是因为索引只存储原始值，而不存储函数或表达式的结果。

3. 索引列上存在类型转换：
? ?- 如果查询中的条件需要将索引列进行类型转换，例如将字符串转换为数字进行比较，数据库可能无法使用索引。类型转换可能导致无法利用索引存储的排序顺序，从而使索引失效。

4. 数据不均匀分布或数据重复性高：
? ?- 当数据在索引列上分布不均匀或存在大量重复值时，索引可能会失效。对于数据不均匀分布的情况，如果查询涉及到的数据存储在索引的某一部分，而其他部分几乎没有被使用，索引将无法提供有效的筛选。对于存在大量重复值的情况，索引可能无法准确地缩小查询范围。

5. 复合索引未按照最佳顺序使用：
? ?- 当使用复合索引（多列组合的索引）时，查询中的条件未按照最佳顺序使用该复合索引，可能导致索引失效。复合索引的效果通常取决于索引列的顺序。如果查询没有按照索引列的顺序进行筛选，数据库可能无法有效使用索引。

6. 数据表过度索引化：
? ?- 在某些情况下，如果数据库表被过度索引化，即存在过多的索引，这可能会导致索引失效和性能下降。过多的索引会导致数据库在执行更新操作时需要维护和更新多个索引，从而增加了开销。此外，过多的索引还会占用额外的存储空间，并且可能会使优化器在选择最佳索引时出现混乱。

7. 数据库统计信息过期：
? ?- 数据库依赖统计信息来确定查询优化器和索引使用情况。如果统计信息过期或不准确，可能导致索引失效。统计信息包括数据分布、索引列的基数（唯一值的数量）以及索引的选择性等。如果统计信息不准确，查询优化器可能会做出错误的决策，导致索引选择不当。

? ? 总之，为了保持索引的有效性，需要综合考虑查询条件、数据分布、数据类型、查询计划和统计信息等因素，在设计和使用索引时进行细致的权衡和优化。

?

?五、最左前缀（详解）

我们以一个示例来说明最左前缀原则：

假设我们有一张学生表(students)，包含以下字段：student_id、first_name、last_name和age。我们希望针对first_name、last_name和age字段创建一个组合索引。

CREATE INDEX idx_student_name_age ON students (first_name, last_name, age);

遵循最左前缀原则，这个组合索引可以在以下查询中被有效利用：

1. 使用first_name进行查询：

   SELECT * FROM students WHERE first_name = 'John';
   

   在这个查询中，MySQL可以利用first_name列的索引部分进行快速查找。

2. 使用first_name和last_name进行查询：

   SELECT * FROM students WHERE first_name = 'John' AND last_name = 'Doe';
   

   在这个查询中，MySQL可以利用first_name和last_name两个列的索引部分进行查找。

3. 使用first_name、last_name和age进行查询：

   SELECT * FROM students WHERE first_name = 'John' AND last_name = 'Doe' AND age = 25;
   

   在这个查询中，MySQL可以利用整个组合索引进行查找。

但是，最左前缀原则也意味着当我们只使用索引的后续列或中间列时，索引将不会被有效利用。

例如，如果只使用last_name进行查询：

SELECT * FROM students WHERE last_name = 'Doe';

? ? ?虽然存在组合索引?idx_student_name_age，但由于查询中没有使用最左边的列?first_name，MySQL将无法使用这个索引，并且必须执行全表扫描来查找匹配的记录。

? ? ?综上所述，最左前缀原则告诉我们，在创建组合索引时，应该根据查询频率和查询的列顺序来选择最适合的组合索引。将最常用的列或特定查询条件置于索引的最左边，以确保索引能够最大限度地被利用，提高查询的性能和效率。

一句话来说 组合索引就是遵从了最左前缀，利用索引中最左边的字段来触发索引，这样的字段称为最左前缀。