MySQL索引详解

目录

索引

索引分类

聚簇索引

非聚簇索引

联合索引

回表

MyISAM 索引的原理

HASH索引

哪些字段适合创建索引

哪些字段不适合创建索引

索引失效场景

索引优化

最左前缀原则

索引覆盖

索引下推

索引

• MySQL 官方对索引的定义为：索引（Index）是帮助 MySQL 高效获取数据的数据结构。
• 索引的本质：索引是数据结构。你可以简单理解为“排好序的快速查找数据结构”，满足特定查找算法。这些数据结构以某种方式指向数据， 这样就可以在这些数据结构的基础上实现高级查找算法。索引是在存储引擎中实现的，因此每种存储引擎的索引不一定完全相同，并且每种存储引擎不一定支持所有索引类型。同时，存储引擎可以定义每个表的最大索引数和最大索引长度。所有存储引擎支持每个表至少 16 个索引，总索引长度至少为 256 字节。有些存储引擎支持更多的索引数和更大的索引长度。
• 优点
  • 降低数据库的 IO 成本，这也是创建索引最主要的原因。
  • 通过创建唯一索引，可以保证数据库表中每一行数据的唯一性。
  • 在实现数据的参考完整性方面，可以加速表和表之间的连接。换句话说，对于有依赖关系的子表和父表联合查询时，可以提高查询速度。
  • 在使用分组和排序子句进行数据查询时，可以显著减少查询中分组和排序的时间，降低了 CPU 的消耗。
• 缺点
  • 创建索引和维护索引要耗费时间，并且随着数据量的增加，所耗费的时间也会增加。
  • 索引需要占磁盘空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，存储在磁盘上，如果有大量的索引，索引文件就可能比数据文件更快达到最大文件尺寸。
  • 虽然索引大大提高了查询速度，同时却会降低更新表的速度。

• 索引分类

  • MySQL的索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引等。·
  • 从功能逻辑上说，索引主要有4种，分别是普通索引、唯一索引、主键索引、全文索引。·
  • 按照物理实现方式，索引可以分为2种：聚簇索引和非聚簇索引。
  • 按照作用字段个数进行划分，分成单列索引和联合索引。
  • MySQL8.0新增了降序索引和隐藏索引
    • 降序索引
      • 降序索引以降序存储键值。虽然在语法上，从MysQL 4版本开始就已经支持降序索引的语法了，但实际上该DESC定义是被忽略的，直到MySQL 8.x版本才开始真正支持降序索引(仅限于InnoDB存储引擎)。
    • 隐藏索引
      • 在MysQL 5.7版本及之前，只能通过显式的方式删除索引。此时，如果发现删除索引后出现错误，又只能通过显式创建索引的方式将删除的索引创建回来。如果数据表中的数据量非常大，或者数据表本身比较大，这种操作就会消耗系统过多的资源，操作成本非常高。
      • 从MySQL 8.x开始支持隐藏索引(invisible indexes)，只需要将待删除的索引设置为隐藏索引，使查询优化器不再使用这个索引(即使使用force index(强制使用索引)，优化器也不会使用该索引)，确认将索引设置为隐藏索引后系统不受任何响应，就可以彻底删除索引。这种通过先将索引设置为隐藏索引，再删除索引的方式就是软删除。
      • 同时，如果你想验证某个索引删除之后的查询性能影响，就可以暂时先隐藏该索引。

  • 聚簇索引

  • 聚簇索引就是按照每张表的主键构造一颗B+树（索引页 + 数据页），同时叶子节点中存放的就是整张表的行记录数据。

  • 特点

    • 根页面位置万年不动
    • 我们把具有这两种特性的 B+树称为聚簇索引，所有完整的用户记录都存放在这个聚簇索引的叶子节点处。这种聚簇索引并不需要我们在 MySQL 语句中显式的使用 INDEX语句去创建，InnoDB 存储引擎会自动的为我们创建聚簇索引。
    • B+树的叶子节点存储的是完整的用户记录。所谓完整的用户记录，就是指这个记录中存储了所有列的值（包括隐藏列）。
    • 使用记录主键值的大小进行记录和页的排序，这包括三个方面的含义：
      • 页内的记录是按照主键的大小顺序排成一个单向链表。
      • 各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表。
      • 存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表。
    • 每个叶子结点内存放的都是数据页
    • 聚簇索引默认是主键，如果表中没有定义主键，InnoDB 会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。
    • 每当为某个表创建一个 B+树索引(聚簇索引不是人为创建的，默认就有）的时候，都会为这个索引创建一个根节点页面。当根节点中的可用空间用完时继续插入记录，此时会将根节点中的所有记录复制到一个新分配的页，一个 B+树索引的根节点自诞生之日起，便不会再移动。
      • 数据页和数据页之间是通过双向链表来关联的，索引页和索引页之间也是通过双向链表来关联的，数据与数据之间是通过单向链表来关联的。
      • 
      • 优点
        • 数据访问更快，因为聚簇索引将索引和数据保存在同一个 B+树中，因此从聚簇索引中获取数据比非聚簇索引更快
        • 聚簇索引对于主键的排序查找和范围查找速度非常高
        • 快按照聚簇索引排列顺序查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不用从多个数据块中提取数据，所以节省了大量的 io 操作
      • 缺点
        • 二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数
        • 更新主键的代价很高，因为将会导致被更新的行移动。因此，对于 InnoDB 表，我们一般定义主键为不可更新
        • 插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于 InnoDB 表，我们一般都会定义一个自增的 ID 列为主键

• 非聚簇索引

  • 在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找。辅助索引叶子节点存储的不再是行的物理位置，而是主键值。通过辅助索引首先找到的是主键值，再通过主键值找到数据行的数据页，再通过数据页中的Page Directory找到数据行。

• 联合索引

  • 在维护 B+ 树的时候，会根据联合索引的字段依次去判断，假设联合索引为：name + address + age，那么 MySQL 在维护该索引的 B+ 树的时候，首先会根据 name 进行排序，name 相同的话会根据第二个 address 排序，如果 address 也一样，那么就会根据 age 去排序，如果 age 也一样，那么就会根据主键字段值去排序，且对于非主键索引，MySQL 在维护 B+ 树的时候，仅仅是维护索引字段和主键字段。

• 回表

  • 根据非聚簇索引查询到的主键值再次从聚簇索引的根节点开始查找，这样再次查找到的记录才是完成的。
  • mysql会为每一个索引维护一个B+树，并按照索引字段进行排序

• MyISAM 索引的原理

  • MyISAM 存储引擎的表会把索引信息另外存储到一个称为索引文件的另一个文件中。MyISAM 会单独为表的主键创建一个索引，只不过在索引的叶子节点中存储的不是完整的用户记录，而是主键值＋数据记录地址的组合。

  • MyISAM 中查找数据需要进行一次回表操作，但是MyISAM 的回表操作是十分快速的，因为是拿着地址偏移量直接到文件中取数据的，反观 InnoDB 是通过获取主键之后再去聚簇索引里找记录，虽然说也不慢，但还是比不上直接用地址去访问。

  • MyISAM 的索引文件仅仅保存数据记录的地址。在 MyISAM 中，主键索引和二级索引(Secondary key)在结构上没有任何区别，只是主键索引要求 key 是唯一的，而二级索引的 key 可以重复。

• HASH索引

  • memory存储引擎使用hash索引

    • 缺点
      • Hash 索引仅能满足(=)(<>)和 IN 查询。如果进行范围查询，哈希型的索引，时间复杂度会退化为 o(n);而树型的“有序"特性，依然能够保持 o(log2N)的高效率。
      • Hash 索引还有一个缺陷，数据的存储是没有顺序的，在 ORDER BY 的情况下，使用 Hash 索引还需要对数据重新排序。
      • 对于联合索引的情况，Hash 值是将联合索引键合并后一起来计算的，无法对单独的一个键或者几个索引键进行查询。
      • 对于等值查询来说，通常 Hash 索引的效率更高，不过也存在一种情况，就是索引列的重复值如果很多，效率就会降低。这是因为遇到 Hash 冲突时，需要遍历桶中的行指针来进行比较，找到查询的关键字，非常耗时。所以，Hash 索引通常不会用到重复值多的列上，比如列为性别、年龄的情况等。
    • 优点
      • 使用hash算法，对于等值查询效率非常高

• 哪些字段适合创建索引

  • 区分度高的字段
  • 经常作为WHERE查询条件的字段
  • 经常GROUP BY和ORDER BY的字段
  • 列类型小的字段（tinyInt、int..）
  • 如果字符串很大，需要对字符串前缀创建索引
    • 在varchar字段创建索引时必须指定长度，具体长度取决于文本的区分度

• 哪些字段不适合创建索引

  • 区分度低的字段
  • 数据量小的表
  • WHERE中用不到的字段
  • 不建议使用无序的列创建索引

• 索引失效场景

  • 表达式计算、函数、类型转换会导致索引失效
  • != 或 <> 会导致索引失效
  • is not null 会导致索引失效
  • 如果使用了 or 关键字两边必须都是索引，否则会导致索引失效
  • like关键字以%开头会导致索引失效

• 索引优化

  • 多表join连接查询时，连接表的数量尽量不要超过3张，因为每增加一张表就相当于增加了一次嵌套的循环，数量级增长会非常快，严重影响查询的效率。
  • 对 WHERE 条件创建索引，因为WHERE才是对数据条件的过滤。如果在数据量非常大的情况下，没有WHERE条件过滤是非常可怕的。
  • 对用于连接的字段创建索引，并且该字段在多张表中的类型必须一致。而不能一个为int另一个为varchar类型。
  • 保证被驱动表的JOIN字段已经创建了索引需要JOIN的字段，数据类型保持绝对一致。
  • LEFT JOIN时，选择小表作为驱动表，大表作为被驱动表。减少外层循环的次数。INNER JOIN时，MySQL会自动将小结果集的表选为驱动表。选择相信MySQL优化策略。
  • 尽量不要使用子查询，用JOIN替代
    • 执行子查询时，MySQL需要为内层查询语句的查询结果建立一个临时表，然后外层查询语句从临时表中查询记录。查询完毕后，再撤销这些临时表。这样会消耗过多的CPU和lO资源，产生大量的慢查询。
    • 子查询的结果集存储的临时表，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响。
  • 尽量不要使用NOT IN或者NOT EXISTS，用LEFT JOIN xxx ON xx WHERE xx IS NULL替代
  • 使用小表驱动大表，哪个表小就用哪个来驱动（LEFT JOIN、EXISTS、IN...）
  • 索引最好设置为 NOT NULL
    • 索引列存在 NULL 就会导致优化器在做索引选择的时候更加复杂，更加难以优化，因为可为 NULL 的列会使索引、索引统计和值比较都更复杂，比如进行索引统计时，count 会省略值为NULL 的行。
    • NULL 值是一个没意义的值，但是它会占用物理空间，所以会带来的存储空间的问题，因为 InnoDB 存储记录的时候，如果表中存在允许为 NULL 的字段，那么行格式中至少会用 1 字节空间存储 NULL 值列表
  • 排序优化
    • 在MysQL中，支持两种排序方式，分别是 FileSort和Index排序。
      • lndex排序中，索引可以保证数据的有序性，不需要再进行排序，效率更高。
      • FileSort排序则一般在内存中进行排序，占用CPU较多。如果待排结果较大，会产生临时文件I/O到磁盘进行排序的情况，效率较低。
    • 优化建议
      • SQL中，可以在WHERE子句和ORDER BY子句中使用索引，目的是在WHERE子句中避免全表扫描，在ORDER BY子句避免使用FileSort排序。当然，某些情况下全表扫描，或者FileSort排序不一定比索引慢。但总的来说，我们还是要避免，以提高查询效率。
        • 减少使用order by，和业务沟通能不排序就不排序，或将排序放到程序端去做。Order by、group by、distinct这些语句较为耗费CPU，数据库的CPU资源是极其宝责的。
        • 无法使用Index时，需要对FileSort方式进行调优。
        • 尽量使用Index完成，ORDER BY排序。如果WHERE和ORDER BY后面是相同的列就使用单索引列，如果不同就使用联合索引。
  • GROUP BY优化
    • group by使用索引的原则几乎跟order by一致，group by即使没有过滤条件用到索引，也可以直接使用索引。
    • group by先排序再分组，遵照索引建的最佳左前缀法则
    • 当无法使用索引列，增大max_length_for_sort_data和sort_buffer_size参数的设置where效率高于having，能写在where限定的条件就不要写在having中了
    • 包含了order by、group by、distinct这些查询的语句，where条件过滤出来的结果集请保持在1000行以内，否则SQL会很慢。
    • distinct和group by区别
      • DISTINCT和GROUP BY都是可以使用索引进行扫描搜索的。所以，在一般情况下，对于相同语义的DISTINCT和GROUP BY语句，我们可以对其使用相同的索引优化手段来进行优化。
      • 对于GROUP BY来说，在MYSQL8.0之前默认会依据字段进行隐式排序。触发filesort，导致sql执行效率低下。但从Mysql8.0开始，Mysql就删除了隐式排序，所以此时在语义相同，无索引的情况下，group by和distinct的执行效率也是近乎等价的。
      • 相比于distinct来说，group by的语义明确。且由于distinct关键字会对所有字段生效，在进行复合业务处理时，group by的使用灵活性更高，group by能根据分组情况，对数据进行更为复杂的处理，例如通过having对数据进行过滤，或通过聚合函数对数据进行运算。

• 最左前缀原则

  • 在MysQL建立联合索引时会遵守最佳左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。
  • MySQL可以为多个字段创建索引，一个索引可以包括16个字段。对于多列索引，过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。如果查询条件中没有使用这些字段中第1个字段时，多列(或联合)索引不会被使用。当遇到范围查询(>、<、between、like)也会停止匹配。

• 索引覆盖

  • 索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据，当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了满足查询结果的数据就叫做覆盖索引，简单说就是，索引列+主键包含SELECT到FROM之间查询的列。
  • 非聚簇复合索引的一种形式，它包括在查询里的SELECT、JOIN和WHERE子句用到的所有列〈即建索引的字段正好是覆盖查询条件中所涉及的字段)。

• 索引下推

  • 如果没有ICP，存储引擎会遍历索引以定位基表中的行，并将它们返回给MySQL服务器，由MySQL服务器评估WHERE后面的条件是否保留行。
  • 启用ICP后，如果部分WHERE条件可以仅使用索引中的列进行筛选，则MySQL服务器会把这部分WHERE条件放到存储引擎筛选。然后，存储引擎通过使用索引条目来筛选数据，并且只有在满足这一条件时才从表中读取行。
  • 索引下推的下推其实就是指将部分上层（服务层）负责的事情，交给了下层（引擎层）去处理。
  • 索引下推的目的是减少回表次数，减少IO操作。
  • 假设表中创建了name, city的联合索引，我们想查询 select * from user where name="LiSi" and city like "%Z%" and age > 25;语句
    • 不使用索引下推
      • 存储引擎根据(name, city)联合索引，找到name值为LiSi的记录，共4条记录。然后回表扫描，去聚簇索引中取出完整的行记录，并把这些记录返回给Server层，server层再根据剩下的条件进行过滤；
    • 使用索引下推
      • 存储引擎根据(name, city)联合索引，找到name值为LiSi的记录，共4条记录。由于联合索引中包含city列，存储引擎直接在联合索引中按city like "%Z%"进行过滤，过滤后剩下2条记录，把这些记录返回给Server层，server层再根据剩下的条件进行过滤；；
  • 使用条件
    • 对InnoDB存储引擎来说，索引下推只适用于二级索引（也叫辅助索引）;
    • 引用了子查询的条件不能下推；
    • 引用了存储函数的条件不能下推，因为存储引擎无法调用存储函数

• COUNT(*)、COUNT(1)、COUNT(字段)的效率
  • 三者本质上没有区别
    • 如果是MyISAM只需要O(1)的复杂度，因为MyISAM每张表都存储了一个row_count值，一致性由表级锁保证
    • 如果是InnoDB因为有MVCC和行级锁机制无法维护一个row_count值因此需要全表扫描是O(n)的复杂度
  • 对于COUNT(*)、COUNT(1)来说，它们不找具体的行，只找具体的行数，所以会使用占用空间更小的二级索引统计，没有二级索引的时候才使用聚主键引统计