技术总结（四十一）

一、MySQL 索引概述

• 索引的概念：索引就好比一本书的目录，它能帮助 MySQL 快速定位到表中的数据行，而不用全表扫描。通过创建合适的索引，可以大大提高查询的效率。例如，在一个存储了大量员工信息的表中，如果经常要根据员工的工号来查询员工记录，为工号字段创建索引后，数据库就能快速找到对应记录，而不是逐行去检查表中的每一条数据。
• 索引的类型：
  • B-Tree 索引（默认常用的索引类型）：它以 B 树数据结构来存储索引数据，适用于全键值、键值范围和键前缀查找等情况。像常见的INT、VARCHAR等类型的字段创建索引时，一般就是 B-Tree 索引，比如在一个电商商品表中，对商品编号、商品名称等字段创建的索引往往就是 B-Tree 索引。
  • 哈希索引：基于哈希表实现，只支持等值查询（也就是=、<=>操作符），对于范围查询等就不太适用了。例如在一些缓存系统中，如果只是简单地根据某个唯一标识快速查找对应缓存值，哈希索引可能比较合适。不过在 MySQL 中，哈希索引主要是在内存存储引擎（如 Memory 引擎）中使用，InnoDB 和 MyISAM 等常用存储引擎默认的索引不是哈希索引。
  • 全文索引：主要用于在文本类型字段（比如TEXT、VARCHAR等较长文本字段）中进行全文搜索，能够帮助查找包含特定关键词的文本内容。例如在一个博客文章表中，要查找包含特定关键词的文章内容，就可以使用全文索引，它支持一些复杂的文本匹配语法，像MATCH AGAINST语句来实现模糊搜索功能。

二、索引优化的原则

• 选择合适的字段创建索引：
  • 经常出现在WHERE子句中的字段：比如在电商订单表中，如果经常根据订单状态（如已支付、已发货等状态）来查询订单，那就应该给订单状态字段创建索引，这样查询满足特定状态的订单时效率会显著提高。
  • 用于连接操作（JOIN）的字段：例如在多表查询中，有订单表和用户表通过用户 ID 进行关联查询，如果在两个表中对应的关联字段（用户 ID）都创建了索引，那么在执行连接操作时数据库就能更快地匹配关联记录，减少数据匹配的时间开销。
  • 字段区分度高的：区分度简单理解就是某个字段不同值的数量占总记录数的比例。像性别字段只有男、女两种值，区分度就很低，如果对它创建索引，在查询时可能并不能很好地缩小查找范围，而身份证号等唯一性高、区分度极高的字段创建索引，对查询效率提升作用明显。
• 避免过度索引：
  • 索引不是越多越好：每一个索引都需要额外的存储空间来保存索引数据，并且在对表进行插入、更新、删除操作时，数据库需要同时维护索引数据的一致性，过多的索引会导致这些操作变得很慢。比如一个简单的小型日志表，本身数据量不大且查询场景很单一，如果创建大量索引，反而会让插入新日志记录的速度变得很慢，影响整体性能。
  • 定期评估索引的有效性：随着业务的发展和数据的变化，有些之前创建的索引可能不再常用或者作用不大了，需要定期去查看索引的使用情况（可以通过数据库的相关性能分析工具查看索引是否被查询使用等情况），对于不再有用的索引进行删除优化。

三、具体的优化策略

• 复合索引的合理使用：
  • 遵循最左前缀原则：如果创建了一个包含多个字段的复合索引（比如在员工表中创建了(name, age, department)这样的复合索引），在查询时，只有按照索引中字段的顺序从左到右使用字段进行条件查询时，索引才会被有效利用。例如WHERE name = '张三' AND age = 30这样的查询能用到复合索引，而WHERE age = 30 AND department = '研发部'就不能完全利用这个复合索引，因为跳过了最左边的name字段。
  • 合理确定复合索引的字段顺序：将区分度高、选择性好且经常用于查询条件的字段放在复合索引的前面。比如在一个学生成绩表中，如果经常根据课程名称和成绩范围来查询学生记录，课程名称的区分度一般比成绩的区分度高（课程种类相对固定，成绩是个数值范围），那创建复合索引时可以写成(course_name, score)这样的顺序。
• 优化查询语句以更好利用索引：
  • 避免在索引字段上使用函数操作：例如在一个存储日期的字段create_date上创建了索引，如果查询语句写成WHERE YEAR(create_date) = 2024，数据库在执行时就无法直接利用索引了，因为对索引字段进行了函数运算。正确的做法是尽量将条件改写成可以直接匹配索引的形式，比如通过日期范围等方式来查询 2024 年的数据（WHERE create_date >= '2024-01-01' AND create_date <= '2024-12-31'）。
  • 避免使用OR连接条件（除非每个OR分支都能利用索引）：比如WHERE status = 1 OR name = '李四'这样的查询，如果status字段和name字段分别有索引，但是数据库在处理OR连接时往往很难同时有效利用这两个索引，可能会导致全表扫描。可以考虑改写查询逻辑，比如通过UNION操作等方式来分别查询满足不同条件的记录后再合并结果，提高查询效率。
• 根据数据量和业务场景选择合适的存储引擎及索引策略：
  • InnoDB 存储引擎：支持事务、行级锁等特性，适合对数据一致性、并发控制要求高的业务场景。它的索引结构（默认 B-Tree 索引）配合其聚簇索引（主键索引的数据行和索引数据存储在一起）的特点，在很多情况下能高效地支持查询、插入等操作。例如在一个电商系统中，商品表、订单表等核心数据表使用 InnoDB 存储引擎，通过合理创建索引（如对商品的分类字段、订单的用户 ID 字段等创建索引）可以很好地满足业务的查询和更新需求。
  • MyISAM 存储引擎：不支持事务，但是在一些以读为主的简单应用场景中，它的表级锁机制和索引结构（同样有 B-Tree 索引等）在查询性能上也有不错的表现，特别是在数据量不是特别巨大且并发访问不是很复杂的情况下。比如一个小型的企业公告信息表，使用 MyISAM 存储引擎，对公告标题等字段创建索引，方便员工快速查询相关公告内容。