chapter03_架构优化和索引_3_高性能索引策略-优快云博客

本文围绕MySQL索引展开，介绍了隔离列对索引使用的影响，阐述索引选择性及前缀索引长度选择。还讲解了聚集索引、MyISAM和InnoDB的数据布局，插入行的过程，以及覆盖索引、为排序使用索引扫描的相关知识，最后总结了索引策略。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

隔离列

查询中没有隔离的列，不会使用索引

隔离：既不是表达式的一部分，也不在函数中

示例
```
  SELECT id FROM my_table WHERE id + 1 = 5;

  SELECT * FROM my_table WHERE TO_DAYS(current_date) >= '1970-01-01';
```
都不会使用索引，必须把id列单独拿出来
前缀索引和索引选择性

(1) 索引选择性

不重复的所有索引的数量 / 记录的所有行数T

这个值在 [1/T, 1]之间，T是记录的所有行数

(2) 索引选择性越接近1，代表查询时的选择性越好，越容易查到唯一的记录；但是短的索引占据的空间越小

(3) 原则：

索引前缀的选择性接近于全列的选择性

索引前缀的选择性：

选取一个前缀长度以后，看看当前长度下SELECT一下能出现多少行，除以一下总行数；

全列的选择性：

对于索引列，看看SELECT DISTINCT的时候能出现多少行，除以一下总行数

示例
```
  全列的选择性：
  SELECT COUNT(DISTINCT city)/COUNT(*) FROM city_demo;

  结果
  COUNT(DISTINCT city)/COUNT(*)
           0.0312

  不同前缀长度的索引选择性

  SELECT COUNT(DISTINCT LEFT(city, 3))/COUNT(*) AS sel3,
         COUNT(DISTINCT LEFT(city, 4))/COUNT(*) AS sel4,
         COUNT(DISTINCT LEFT(city, 5))/COUNT(*) AS sel5,
         COUNT(DISTINCT LEFT(city, 6))/COUNT(*) AS sel6,
         COUNT(DISTINCT LEFT(city, 7))/COUNT(*) AS sel7
  FROM city_demo;

  结果
     sel3    sel4   sel5   sel6   sel7
    0.0239  0.0293 0.0305 0.0309 0.0310
```
所以索引前缀长度选择7比较合适，于是__建立前7位的前缀索引__
```
  ALTER TABLE city_demo ADD KEY (city(7))
```
聚集索引

(1) InnoDB是唯一支持聚集索引的引擎

(2) 节点页只包含被索引的列(作为key)，叶子页包含了行的__全部__数据

—> 每个表__只能有一个__聚集索引

(3) InnoDB按照PRIMARY KEY添加聚集索引，如果没有定义主键会尝试用唯一的非空索引代替

(4) 优点

1° 把相关数据物理上存在一起，数据访问快

2° 使用覆盖索引的查询可以使用包含在叶子节点中的主键值(???)

(5) 缺点

1° 当数据在内存而不是磁盘中时，相关数据物理上在不在一起都无所谓了，没什么优势

2° 插入速度严重依赖于插入顺序

3° 当插入或行的主键被更新时，如果恰好要放在一个满的页中，需要进行__分页__操作 —> 速度慢并且占有更多的磁盘空间

4° 当表存储的比较稀疏的时候，聚集表可能比全表扫描慢

5° 使用了聚集索引的表在使用__第二索引__时，要经过__两次__索引查找

因为第二索引中的叶结点保存的__行指针__是行的主键值而不是行的物理位置，所以第一次先根据第二索引确定对应的主键值，第二次再根据主键值从聚集索引中找到记录

MyISAM的数据布局

(1) MyISAM没有使用聚集索引，所以它有一个__行存储空间__，专门用来存储记录

示例

  CREATE TABLE layout_test (
      col1 INT NOT NULL,
      col2 INT NOT NULL,
      PRIMARY KEY (col1),
      KEY (col2)
  );

行存储空间

  行号  col1   col2
   0     99     8
   1     12     56
   2    3000    62
       ......
  9997   18     8
  9998  4700    13
  9999   3      93

(2) 对于MyISAM中的索引和主键索引，它们在存储结构上__没有任何差别__，都采用了B+树，非叶结点保存的是键值和下一个结点的指针，叶结点保存的是键值和__行记录的位置指针__

主键索引的结构(col1)

                       根结点

                        ...

                  内部结点   ...

    叶结点键值    3   12   18    ...     4700     
    叶结点行号  9999   1  9997   ...     9998

col2索引的结构

                       根结点

                        ...

                  内部结点   ...

    叶结点键值  8   8    13    ...   93     
    叶结点行号  0  9997 4700   ...  9999

InnoDB的数据布局

(1) InnoDB使用了聚集索引，所以聚集索引中本身就包含了所有的表的信息，无需额外的行存储空间

  CREATE TABLE layout_test (
      col1 INT NOT NULL,
      col2 INT NOT NULL,
      PRIMARY KEY (col1),
      KEY (col2)
  );

  INSERT INTO layout_test (col1, col2) VALUES (99, 8);
  INSERT INTO layout_test (col1, col2) VALUES (12, 56);
  INSERT INTO layout_test (col1, col2) VALUES (3000, 62);
  ...
  INSERT INTO layout_test (col1, col2) VALUES (18, 8);
  INSERT INTO layout_test (col1, col2) VALUES (4700, 13);
  INSERT INTO layout_test (col1, col2) VALUES (3, 93);

(2) 聚集索引(col1为主键建立的)的结构

                       根结点

                        ...

                  内部结点   ...

   叶结点

     col1          3     12    18    ...    99   3000  4700
   事务ID         TID    TID   TID    ...   TID   TID   TID
   回滚指针        RP     RP    RP    ...    RP    RP    RP
   行中的其他列    93     56    8      ...    8    62    13

所以聚集索引囊括的信息是整张表，还包括了事务ID和回滚指针用于事务操作；如果用于聚集索引的列不包含表中所有的列或者使用了前缀索引，那么在行中的其他列一项要把__记录的剩余部分__都存储好

(3) 非聚集索引的结构(col2)

                       根结点

                        ...

                  内部结点   ...

    叶结点键值  8   8    13    ...   93     
    主键键值   18  99   4700   ...   3

由于没有行存储结构的概念，所以在叶结点中保存的是对应的主键键值（因为主键唯一性可以唯一确定一个记录）。这也就是为什么在有聚集索引的表中使用其他索引时，要进行两次索引查找的原因：没有行存储结构的概念，叶结点指针存的是主键键值

向InnoDB中插入行的实际过程

(1) 如果定义的表不需要任何特殊的聚集方式，可以添加一个__代理键__id，并设置为auto_increment，这样可以保证按照主键的顺序插入

具体的过程是：由于主键有序，所以新记录在聚集索引的叶结点中总是插入到前一个记录的后面，这样页面会很__紧凑__

(2) 如果插入时主键值随机，那么新行就未必插入到记录的最后，而是要先查找它应该放的位置，然后如果发现待插入的位置满了，就会触发__分页__操作，并且最终的数据会有__碎片__(因为每个页都不满)

(3) 如果发生了(2)的情况，可以用__OPTIMIZE TABLE__进行一下补救

(4) 总之，结论就是尽可能按照主键的顺序插入
覆盖索引

(1) 作用

当查询只查询一些列，并且这些列恰好包含在索引范围内时，由于索引的叶结点中本身就存储了这些列的值，所以不必查询具体数据记录而是直接从索引中获取列

(2) 优势

1° 减少数据访问量

2° 索引按照索引值排序的，因此访问更快

(3) MySQL中__只能使用B+树__作为覆盖索引的底层结构（因为要包含列的数据信息）

(4)

1° 如果查询的列不能被某个索引覆盖，那就不能用覆盖索引了

2° 使用索引必须精确，用=可以，LIKE的话必须满足前缀匹配才行，不能有先导通配符例如LIKE ‘%APOLLO%’，但是LIKE 'APOLLO%'可以

3° 当使用InnoDB引擎时，由于InnoDB会自动给主键值加__聚集索引__，所以如果表上还有第二索引存在的话，这时想查询第二索引覆盖的某些列+主键覆盖的某些列，也会用上覆盖索引

原因是第二索引叶结点保存的第二索引中的值和主键值，所以信息是足够的

(5) 当使用EXPLAIN看查询方式时

type__一项可以看当前查询用没用__索引

Extra__一项可以看当前查询用没有__覆盖索引
为排序使用索引扫描

(1) 产生排序的结果(ORDER BY)有两种方式：文件排序__和__扫描有序的索引

(2) 按照索引对结果进行排序必须满足：

1° 索引的顺序和ORDER BY子句中的顺序一致

2° 所有列排序的方向(ASC, DESC)和索引中的方向一致

3° 多表联接时，必须ORDER BY子句中使用的都是第一个表的索引列

(3) 不满足(2)的其他所有情况都使用__文件排序__
中间有些没看……
索引策略总结

(1) 首先检查响应时间，考虑为任何耗时很长的查询添加索引

(2) 要尽可能__扩展__索引，而不是__新增__索引。通常维护一个多列索引比维护多个单列索引容易

(3) 尽可能让索引变得更有__选择性__