MySQL--索引

目录

一.MySQL的索引能提高效率的原因主要有以下几点：

二.索引的类型

三.EXPLAIN执行计划分析

四.什么情况添加索引

五.联合索引注意哪些细节

六.不合理使用索引的场景

七.MySQL为什么有时会选错索引

一.MySQL的索引能提高效率的原因主要有以下几点：

1. 快速定位数据：索引是一种数据结构，能够帮助数据库快速定位到符合条件的数据，减少了数据库需要扫描整个表的时间，从而提高了查询的效率。

2. 减少数据读取量：通过使用索引，数据库可以直接定位到需要的数据行，而不需要读取整个表的数据，减少了数据的读取量，提高了查询的效率。

3. 降低磁盘I/O的次数：索引可以减少磁盘I/O的次数，因为数据库可以直接通过索引定位到需要的数据行，而不需要每次都进行全表扫描，从而减少了磁盘I/O的开销，提高了查询的效率。

4. 加速数据排序和分组：当使用索引进行排序或分组操作时，索引可以帮助数据库直接定位到需要的数据行，而不需要进行全表扫描，从而加速了排序和分组操作的执行速度。

索引可以帮助数据库快速定位到需要的数据，减少数据的读取量和磁盘I/O的次数，加速数据的排序和分组操作，从而提高了查询的效率。因此，在数据库设计和查询优化中，合理使用索引是提高效率的重要手段之一。

二.索引的类型

1.普通索引：最基本的索引类型，可以加快数据的检索速度，但不要求字段的唯一性。可以通过CREATE INDEX语句创建普通索引。

CREATE INDEX index_name ON table_name(column_name);

2.唯一索引：索引列的值必须是唯一的，不允许重复值。唯一索引可以保证数据的完整性，避免出现重复数据。可以通过CREATE UNIQUE INDEX语句创建唯一索引。

CREATE UNIQUE INDEX index_name ON table_name(column_name);

3.主键索引 ：主键索引是一种唯一性索引，用于标识表中的唯一记录。主键索引要求索引列的值是唯一的且不为NULL。在MySQL中，主键索引通常与PRIMARY KEY约束一起使用，可以通过在创建表时指定PRIMARY KEY或ALTER TABLE语句添加主键索引。

ALTER TABLE table_name ADD PRIMARY KEY (column_name);

4.复合索引 ：将多个列组合在一起创建索引，可以提高多列查询的效率。复合索引可以在多个列上进行排序和过滤，适用于多条件查询。可以通过CREATE INDEX语句创建复合索引。

CREATE INDEX index_name ON table_name(column1, column2);

 5.全文索引（仅适用于全文索索字段）：用于全文搜索的索引类型，支持对文本内容进行全文搜索。全文索引适用于大段文本的搜索，提供了更高效的全文搜索功能。可以通过ALTER TABLE语句添加全文索引。

ALTER TABLE table_name ADD FULLTEXT INDEX index_name (column_name);

6.空间索引 ：用于空间数据类型的索引，如地理信息数据。空间索引可以加速空间数据的查询和分析，适用于需要处理地理位置信息的场景。可以通过ALTER TABLE语句添加空间索引。

ALTER TABLE table_name ADD SPATIAL INDEX index_name (column_name);

 删除索引：alter tatable_name drop index idxindx_name;

三.EXPLAIN执行计划分析

(加粗列需重点关注)

1.select_type各种值的解释

2.type各种值的解释

3.key_len各种字段类型对应的长度

4.Extra常见值的解释

四MySQL ：： MySQL 8.0 参考手册 ：： 10.8.2 EXPLAIN 输出格式

四.什么情况添加索引

添加索引可以提高查询效率和性能，但并非所有情况都适合添加索引。以下是一些适合添加索引的情况：

1. 经常用于查询的字段：对于经常用于查询条件的字段，例如WHERE子句中的条件字段、连接字段或排序字段，可以考虑添加索引。索引可以加快对这些字段的检索速度。

2. 外键字段：用于连接表之间关系的外键字段，添加索引可以提高关联查询的效率。

3. 唯一性约束字段：表中需要保证唯一性的字段，如用户名、邮箱等，可以添加唯一索引来保证数据的完整性。

4. 经常用于排序和分组的字段：对于经常用于排序和分组操作的字段，添加索引可以加快排序和分组操作的速度。

5. 大表的字段：对于大表中的字段，添加索引可以减少数据扫描的时间，提高查询效率。

6. 频繁进行关联查询的字段：对于频繁进行关联查询的字段，如JOIN操作的连接字段，可以添加索引来加快关联查询的速度。

7. 全文搜索字段：对于需要进行全文搜索的字段，可以添加全文索引来提高搜索效率。

需要注意的是，并不是所有字段都适合添加索引，过多或不必要的索引可能会导致性能下降。在添加索引时，需要根据具体的业务需求和查询场景进行合理的索引设计，避免不必要的索引，以提升数据库性能。在实际应用中，可以通过分析查询语句的执行计划和性能测试来评估是否需要添加索引。

五.联合索引注意哪些细节

1. 索引字段顺序：联合索引中的字段顺序很重要。应该将最常用于过滤和排序的字段放在联合索引的最左边，这样可以最大程度地提高索引的效率。

2. 索引字段数量：联合索引中的字段数量不宜过多。过多的字段会增加索引的大小，降低索引的效率。通常建议不要超过3-4个字段。

3. 索引字段选择：只选择必要的字段添加到联合索引中。不必要的字段会增加索引的大小，降低索引的效率。

4. 覆盖索引：如果查询中只使用了联合索引中的字段，并且不需要访问表中其他字段，可以利用覆盖索引来提高查询效率。

5. 索引合并：MySQL可以同时使用多个索引，但在使用多个单列索引时，MySQL会尝试将这些索引进行合并。因此，尽量设计一个合适的联合索引，避免MySQL在查询时进行索引合并操作。

6. 索引的维护：随着数据的增加和修改，索引也需要定期维护。定期对联合索引进行重建或优化可以保持索引的高效性。

7. 避免冗余索引：避免在联合索引中包含重复的字段。重复的字段不仅占用空间，还会降低索引的效率。

使用联合索引时需要注意索引字段的顺序、数量和选择，同时注意索引的维护和避免冗余索引，以确保索引的有效性和性能。合理设计和使用联合索引可以提高数据库查询的效率和性能。

六.不合理使用索引的场景

1. 对于小表：对于小表而言，添加索引可能会增加额外的开销，因为数据库引擎可能会选择全表扫描而不是使用索引来查询数据。

2. 频繁进行数据更新的字段：对于频繁进行数据更新的字段，添加索引会增加更新操作的开销，因为每次更新都需要更新索引。

3. 不经常查询的字段：对于很少被用于查询的字段，添加索引并不会带来明显的性能提升，反而会增加索引维护的成本。

4. 查询中使用的函数操作：如果查询中使用了函数操作，如对字段进行函数运算或使用函数进行条件筛选，索引可能无法生效，导致无法利用索引提高查询效率。

5. 高基数列：对于基数（不重复值的数量）非常高的列，如性别列，添加索引并不能提高查询效率，因为索引的选择性不高，数据库引擎可能会选择全表扫描。

6. 索引列上有大量NULL值：在索引列上存在大量NULL值会降低索引的选择性，导致索引失效，不适合在该列上添加索引。

7. 过多的索引：过多的索引会增加数据库的存储开销和维护成本，同时可能导致查询优化器选择错误的索引，降低查询性能。

在以上情况下，过度使用索引可能会导致性能下降，因此需要根据具体情况慎重考虑是否添加索引，避免不合理使用索引带来的负面影响。最佳实践是根据实际需求和查询场景进行合理的索引设计和添加。

七.MySQL为什么有时会选错索引

1. 统计信息不准确：MySQL的查询优化器会根据表的统计信息来选择合适的索引，如果统计信息不准确或过时，可能导致优化器选择错误的索引。

2. 索引选择性不高：索引选择性是指索引中不同值的数量与表的总行数的比值，选择性越高，索引的效率越高。如果索引选择性不高，优化器可能会选择错误的索引。

3. 索引列顺序不合适：对于复合索引，索引列的顺序很重要。如果查询条件中的列顺序与索引列的顺序不一致，可能导致优化器选择错误的索引。

4. 索引覆盖度不足：如果查询需要访问的列不在索引中，优化器可能不会选择该索引，导致选错索引。

5. 索引失效：在某些情况下，索引可能会失效，例如在查询中使用了函数操作或类型转换，会导致优化器无法使用索引。

6. 数据分布不均匀：如果数据分布不均匀，某些索引可能无法起到有效的过滤作用，导致优化器选择错误的索引。

7. 查询条件复杂：对于复杂的查询条件，优化器可能难以确定最佳的索引选择，可能会选择错误的索引。

为避免MySQL选错索引，可以采取以下措施：

• 定期更新表的统计信息，保持统计信息的准确性。
• 确保索引选择性较高，避免选择性低的索引。
• 确保查询中的列顺序与索引列顺序一致，尽量使用覆盖索引。
• 避免在查询中使用函数操作或类型转换，以免导致索引失效。
• 优化查询条件，尽量简化复杂的查询条件，帮助优化器选择合适的索引。

通过合理的索引设计和查询优化，可以降低MySQL选错索引的概率，提高查询性能和效率。