在mysql如何创建高性能索引

知其然要知其所以然，探索每一个知识点背后的意义，你知道的越多，你不知道的越多，一起学习，一起进步，如果文章感觉对您有用的话，关注、收藏、点赞，有困惑的地方可以评论，我们一起探讨！

索引基础

在MySQL中，存储引擎用类似目录的方法使用索引，先在索引中找到对应值，然后根据匹配的索引记录找到对应的数据行，索引可以包含一个或多个列的值。如果索引包含多个列，那么列的顺序也十分重要，因为MySQL只能高效地使用索引的最左前缀列，创建包含两个列的索引，和创建两个只包含一个列的索引是大不相同的。

索引的类型

在MySQL中，索引是在存储引擎层而不是服务器层实现的：不同存储引擎的索引的工作方式并不一样，也不是所有的存储引擎都支持所有类型的索引。

• B+树索引
  • 这是 InnoDB 存储引擎默认采用的索引类型 。在 B + 树结构中，所有数据记录都存放在叶子节点，且叶子节点通过双向链表相连，非叶子节点仅存储索引值，用于引导查询方向。这使得范围查询和顺序扫描极为高效。比如在一张按时间排序的日志表中，若对时间字段建立 B + 树索引，在查询某个时间段内的日志记录时，能快速定位到起始节点，然后沿着链表顺序读取数据，避免大量随机 I/O 操作。在精确查询时，也能通过树的层级快速定位到对应叶子节点获取数据。
• 哈希索引
  • Memory 存储引擎默认支持 。哈希索引基于索引列的值计算哈希值，通过比较哈希值实现查找。在等值查询场景中，哈希索引速度极快。例如在用户登录系统中，根据用户 ID 查询用户密码，使用哈希索引能迅速定位到对应记录。但哈希索引无法进行范围查询和排序，因为哈希值无序，不适用于需要范围查找或排序的场景。
• 空间索引
  • 用于对地理位置、二维几何对象等数据类型进行索引 ，支持空间查询与分析。在基于地理位置的应用，如外卖平台查找附近商家、地图导航应用查找周边兴趣点等场景中发挥关键作用。例如外卖平台根据用户位置查找附近一定范围内的商家，通过对商家位置信息建立空间索引，可高效实现查询。空间索引适用于 POINT、LINESTRING、POLYGON 等几何类型列，创建时需关注坐标系、尺度单位等参数。
• 全文索引
  • 专门用于文本内容的快速搜索 ，提供关键词语法解析与匹配功能。在新闻资讯、博客文章等内容搜索场景中应用广泛。比如在新闻网站搜索新闻时，对文章内容建立全文索引，用户输入关键词，就能快速检索到包含该关键词的新闻文章。不过，全文索引仅适用于 TEXT、CHAR 等文本类型列，且创建时需注意最小词汇长度、停止词等参数设置。
• 前缀索引
  • 对较长列值截取后建立的索引 。当列值较长时，使用前缀索引可减少索引存储空间，提升索引效率。例如在存储较长 URL 的列上创建前缀索引，可缩短索引长度，加快查询速度。但前缀过短会降低索引选择性，影响查询效率，需合理选择前缀长度。
• 联合索引
  • 在多个列上同时建立的索引 。能提高涉及多列查询的效率，减少索引数量与存储空间占用。比如在员工表中，若经常根据部门和职位联合查询，可在这两列上创建复合索引。需注意，复合索引遵循最左前缀原则，第一个列对索引排序和匹配效率影响最大，后面列只有在前面列值相同情况下才起作用。
• 唯一索引
  • 在索引列加上唯一约束后建立 ，确保索引列数值在表中唯一。既提升查询效率，又防止数据重复与不一致。例如在用户表中，对用户 ID 列建立唯一索引，可保证每个用户 ID 唯一，避免重复插入，同时查询用户信息时，基于唯一索引能快速定位到指定记录，InnoDB 是 MySQL 中最常用的存储引擎，它允许唯一索引列中有多个 NULL 值，NULL 代表 “未知的值”，两个 NULL 值被认为是不相等的
• 主键索引
  • 是主键列上建立的特殊唯一索引 ，不允许有空值，还可作为表间关系连接点。MySQL 中常采用自增整数类型作为主键列，保证插入数据高效性与唯一性。例如在订单表中，订单 ID 作为主键，通过主键索引能快速定位特定订单记录，在与其他表关联查询时，主键索引起关键连接作用。

索引的优点

• 索引大大减少了服务器需要扫描的数据量
• 索引可以帮助服务器避免排序和临时表
• 索引可以将随机I/O变为顺序I/O

思考

• 索引是不是最好的解决方案？
  • 我认为不是
    • 只有当索引帮助存储引擎快速查找到记录带来的好处大于其带来的额外工作时，索引才是有效的
    • 对于非常小的表，大部分情况下简单的全表扫描更高效
    • 对于中到大型的表，索引就非常有效
    • 对于特大型的表，建立和使用索引的代价随之增长，这种情况下，需要一种技术可以区分出查询需要的一组数据，而不是一条记录一条记录地去匹配，例如分库、分表、大数据、冷数据定时迁移等
    • 如果表的数量特别多，可以建立一个元数据信息表，用来查询需要用到的某些特性，例如执行那些需要聚合多个应用分布在多个表的数据的查询，则需要记录“哪个用户的信息存储在哪个表中”的元数据

高性能的索引策略

• 独立的列

  • ”独立的列“是指索引列不能是表达式的一部分，也不能是函数的参数
  • 如果查询中的列不是独立的，则MySQL就不会使用索引

• 前缀索引

  • 有时候需要索引很长的字符列，这会使索引变得很慢，一个策略是模拟使用哈希索引（即前缀索引）
  • 通常可以选择索引开始的部分字符，这样可以大大节省索引空间，从而提高索引效率
    • 但这样会降低索引的选择性，索引的选择性是指，不重复的索引值（也称为基数）和数据表的总记录数（#T）的的比值，范围从 1/#T到1之间，索引的选择性越高则查询效率越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的行，唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的，对于BLOB、TEXT或者很长的VARCHAR类型的列，必须使用前缀索引，因为MySQL不允许索引这些列的完整长度，诀窍在于选择足够长的前缀以保证较高的选择性，同时又不能太长（以便节约空间）。前缀的”基数“应该接近于完整列的基数，计算前缀索引基数的思路与相关公式原理：
      • 计算前缀索引的基数并没有一个固定的精确公式，不过我们可以通过抽样统计的方式来估算它。
      • 原理：在 MySQL 中，可以使用 SHOW INDEX 语句查看索引的基数信息，但这个基数是 MySQL 基于采样估算出来的，并非精确值。另外，也可以通过 SQL 查询来手动估算前缀索引的基数。
      • 手动估算前缀索引基数的 SQL 示例：假设我们有一个表 your_table，其中有一个较长的字符串列 long_column，我们为其创建了前缀索引（例如取前 N 个字符），以下是计算该前缀索引基数的 SQL 示例：

         SELECT COUNT(DISTINCT LEFT(long_column, N)) 
         FROM your_table;
        ```
      
      

    • LEFT(long_column, N)：这个函数的作用是从 long_column 列中提取前 N 个字符，模拟前缀索引所使用的前缀部分。
    • COUNT(DISTINCT ...)：对提取出来的前缀进行去重操作，然后统计不同前缀值的数量，得到的结果就是该前缀索引的估算基数。
    • 最后按照前缀长度 N 进行分组并排序，这样就能观察到随着前缀长度的增加，基数的变化情况。我们通常希望选择一个既能保证较高基数，又不会过长的前缀长度来创建前缀索引
  • 前缀索引的缺点：MySQL无法使用前缀索引做ORDER BY 和 GROUP BY，也无法使用前缀索引做覆盖扫描
  • 有时候后缀索引也很有用途，可以把字符串反转后存储，并基于此建立前缀索引。可以通过触发器来维护这种索引

• 联合索引

  • 在多个列上建立独立的索引大部分情况下并不能提高MySQL的查询性能，MySQL5.0及之后更新的版本引入了一种叫”索引合并“（index merge）的策略，一定程度上可以使用表上的多个单列索引来定位指定的行，更早版本的MySQL只能使用其中一个单列索引
  • 选择合适的索引列顺序
    • 正确的顺序依赖于使用该索引的查询，并且同时需要考虑如何更好地满足排序和分组的需要
    • 将选择性最高的列放在索引最前列，那些运行频率最高的查询来调整索引列的顺序，让这种情况下索引的选择性最高

• 覆盖索引

  • 覆盖索引定义：如果一个索引包含了查询所需的所有列，那么就可以直接从索引中获取数据，而无需回表查询，这就是覆盖索引。例如，对于查询 SELECT col1, col2 FROM your_table WHERE col1 = 'value'，如果创建了联合索引 (col1, col2)，那么该索引就是覆盖索引，在EXPLAIN的Extra列可以看到“Using index"的信息。
  • 提高查询性能：使用覆盖索引可以减少磁盘 I/O 操作，提高查询速度。

• 使用索引扫描来做排序

  • 要让MySQL使用索引完成排序，需满足以下条件：
    • 1. 索引列顺序：ORDER BY子句的列顺序必须与索引的列顺序完全一致（或反向一致）。
    • 2. 最左前缀匹配：WHERE条件中的列（如果有）需构成索引的最左前缀。
    • 3. 排序方向一致性：所有排序字段的升降序（ASC/DESC）必须与索引定义完全一致或完全相反，MySQL 8.0+支持降序索引，低版本需保证排序方向与索引完全一致。
  • 无法使用索引排序的常见场景：
  1. 索引中断：

  -- 索引: (user_id, order_date)
  SELECT * FROM orders WHERE user_id > 100 ORDER BY order_date; 
  -- user_id是范围查询，后续order_date无法有序
  

  2. 非最左前缀：

  -- 索引: (user_id, order_date)
  SELECT * FROM orders ORDER BY order_date; -- 缺少user_id条件
  

  3. 函数或表达式：

  SELECT * FROM orders ORDER BY YEAR(order_date); -- 索引失效
  

  4. 注意事项：

     • 1.如果有一个索引在列（a, b）上，那么查询ORDER BY a, b可能会使用这个索引来排序。但是如果查询是ORDER BY a DESC, b DESC，同样可以利用索引，因为索引可以反向扫描。但如果排序的列顺序与索引不一致，比如ORDER BY b, a，或者混合了ASC和DESC，比如ORDER BY a ASC, b DESC （mysql8.0解决了降序索引的问题），那么可能无法使用索引，导致文件排序

     • 2.在考虑查询中的WHERE条件时。如果WHERE条件中的列和ORDER BY中的列能够组成一个最左前缀的索引，那么MySQL可能使用这个索引来过滤和排序。例如，如果索引是（a, b, c），查询条件是WHERE a = 1 ORDER BY b, c，这时候可以使用索引来过滤a=1的记录，并且按b和c排序。但如果WHERE条件中使用了范围查询，比如a > 1，那么索引只能用到a列，后面的排序可能无法使用索引，因为范围查询后的列在索引中不再有序。

     • 3.MySQL 5.7及之前， 索引默认按升序（ASC）存储，即使显式定义 DESC（如 INDEX (a DESC)），实际仍按升序存储。MySQL 8.0 引入 降序索引

• 优化器在遇到 ORDER BY ... DESC 时，只能 反向扫描升序索引，效率较低（尤其是多列混合排序时）。

  总结

优化要点	操作建议
索引顺序匹配ORDER BY	设计索引时严格对齐排序字段顺序
避免范围查询中断排序	将等值查询条件放在索引左侧
使用覆盖索引	尽量让SELECT字段包含在索引中
监控Using filesort	定期检查慢查询日志，优化未命中索引的排序操作
升级MySQL版本	8.0+版本支持降序索引、函数索引等高级特性，可覆盖更多排序场景