MySQL InnoDB索引介绍

happyLazyAnt

已于 2023-08-09 17:25:16 修改

阅读量104

点赞数

分类专栏： MySQL 文章标签： mysql

于 2023-08-03 13:48:51 首次发布

本文链接：https://blog.csdn.net/happyLazyAnt/article/details/132081787

版权

MySQL 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

MySQL InnoDB索引介绍

什么是索引

索引简单来说就是一个目录，能够标记该内容的在书中的位置，启动快速查询内容的目的。而MySQL的索引是一种单独的数据结构，由数据表中的一列或多列组合而成，可以用来快速查询数据表中有某一特定值的记录。

InnoDB索引的特点

聚簇索引

聚簇索引（Clustered Index）是InnoDB特殊的索引，直接存储了行数据。通常情况下聚簇索引与主键索引具有相同的含义。由于，通过聚簇索引可以直接访问包含行数据的数据页，能够减少磁盘的I/O次数。
InnoDB选取聚簇索引的规则：
1. 如果定义了主键索引，则InnoDB会将该主键索引作为聚簇索引；
2. 如果没有定义主键索引，InnoDB则会选择第一个所有列都不能为NULL的唯一索引作为主键索引。
3. 如果即没有主键索引，也没有合适的唯一索引，InnoDB会在包含row ID的列上增加一个名为GEN_CLUST_INDEX的隐藏聚簇索引。其中行 ID 是一个 6 字节字段，随着新行的插入而单调增加，因此，表数据是按行ID排序就是按插入顺序排列的。

辅助索引

在InnoDB中，其他非聚簇索引都称为辅助索引（Secondary Index）。每个表最多包含64个辅助索引。每一个辅助索引记录都是由索引所包含的列数据和主键索引包含的列数据组成，其中主键索引包含的列数据用于查找聚簇索引上的行数据。可以看出主键索引的列被冗余存储，所以其值的长度越小，可以节省辅助索引的存储空间。

B+树

InnDB使用B+树索引。B+树能够支持 =, >, >=, <, <=, BETWEEN 和非%开头的like操作。

MySQL如何使用索引

MySQL通过索引与where条件快速匹配查找数据；
当有多个索引时，MySQL通常选择返回行数最少的索引；
一个索引包含多个列时，MySQL只能使用索引的最左前缀来查找数据，例如：索引包含三列(col1, col2, col3)，则只有该三种情况(col1), (col1, col2)和 (col1, col2, col3)可以使用索引。
在联合查询中，MySQL对于类型和字段大小相同的列能够更有效的使用索引。如果 VARCHAR 和 CHAR 声明为相同的大小，则将它们视为相同。例如：对于VARCHAR(10) 和 CHAR(10) 的大小相同，但 VARCHAR(10) 和 CHAR(15) 的大小不同。
在联合查询中，对于非二进制字符串的列，必须使用相同的字符集才能使用索引，如UTF-8与latin1不能使用索引。
如果查询的值(select中的列值)都包含在整个索引中，此时MySQL直接使用索引中的值，而不会再次获取查询行数据（叫做覆盖索引Covering Index）。
有时 MySQL 不使用索引，即使有可用的索引。因为在一些情况下，优化器评估使用索引将需要 MySQL 访问表中很大比例的行，此时表扫描可能会更快，因为它需要更少的查找。但是，如果查询结果只需要某些列的值，MySQL 无论如何都会使用索引，因为它可以直接使用索引值返回。

查询优化

主键优化

主键由一个或者多个非NULL列组成，并代表了最主要的查询，而且创建时默认会关联创建一个唯一索引。由于非NULL特性和索引能够提供更好的查询效率。如果一个表没有一个或者一组列可作为主键，可以额外创建一个具有自动增量值的单独列作为主键，该自增唯一ID也可以作为连接查询时的外键使用，提高查询效率。

外键优化

一个大表如果有许多列，且会查询许多不同列的组合，则可以将不常用的数据拆分到每个包含少量字段的单独小表中。每个小表可通过复制主表的主键ID与主表进行关联。这样，每个小表都可以通过主键来快速查找其数据，并可以使用联接操作仅查询所需的列集。为了最大限度地提高性能，MySQL会尝试从磁盘读取尽可能少的数据块。而对于小表来说，每个数据块中可以容纳更多行，这样MySQL一次性读取的数据就更多，只需更少的IO操作和占用更少的内存。

索引前缀

对于字符类型的列，可以通过colname(N) 来制定前N个字符作为索引。而BLOB和TEXT类型的列，则必须为索引指定前缀的长度。InnoDB支持最大前缀长度为767字节（innodblarge_prefix打开时最大支持3072字节）。
如果查询条件的长度大于索引前缀长度，MySQL会先通过索引来排出不满足条件的行，然后再对剩余的行进行匹配。