欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页

InnoDB的索引

程序员文章站 2022-06-02 12:27:59
...

索引基本上是数据库的核心，是数据库性能的关键，通过索引可以快速查询、定位到数据，Sql优化的一大原则是：数据尽量走索引，减少全表扫面。减少了全表扫面也就减少了磁盘IO。但是有一误区，并不是走了索引就一定快。

InnoDB的表结构决定了，InnoDB属于索引组织表结构，InnoDB的索引是重中之重。

分类

InnoDB的索引从功能上分为两类：

  • 聚集索引：主键索引，这类索引可以直接定位到数据。
  • 辅助索引：非主键索引，这类索引需要通过索引中的信息再去聚集索引查询数据（索引覆盖不包括）。

按照索引实现时的数据结构，索引还可以做如下划分（这是不同维度的划分方法）：

  • B+树索引：基于B+树的索引，大部分索引都采用这个数据结构实现，聚集索引也是采用B+树索引实现，通常情况下我们只能建立B+索引。
  • 全文索引：基于语法分词的索引，提升模糊搜索效率，但是不适合中、日、韩语法（这些语言没有明显的分词语法）。
  • 哈希索引：无法人工干预，InnoDB自身的优化措施，速度很快，是InnoDB的自适应索引，也是InnoDB的关键特性。

B+树索引: 传统意义的索引，索引的效率基于树的高度。B+树不是一个二叉树，但是是从二叉平衡树构造来的，索引的update、insert、delete是需要进行左右旋来重新进行平衡和构造的。而且由于insert导致的页拆分也会带来磁盘IO，这就是为什么索引滥用会带来update、insert的灾难。

**InnoDB的B+树索引不能找到某个具体的row，而且定位到page，通过对page进行二分查找定位具体row数据。**page中的数据是按照主键顺序存放的，索引查找起来容易。

Explain 命令

通过Explain可以分析Mysql的查询计划。如下例子，通过查询计划，可以很好的知道mysql优化器有没有使用索引，以及做了什么。

mysql> explain select * from test where a like '100%' \G
*************************** 1. 行 ***************************
id           : 1
select_type  : SIMPLE
table        : test
partitions   : NULL
type         : range
possible_keys: PRIMARY,a
key          : PRIMARY
key_len      : 302
ref          : NULL
rows         : 1
filtered     : 100.00
Extra        : Using where
1 行于数据集 (0.02 秒)
  • id：表示表示查询中执行select子句或操作表的顺序，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行；
  • 查询中每个select子句的类型（简单OR复杂）；
  • table：对那个表执行；
  • type：访问类型，表示MySQL在表中找到所需行的方式，
    • ALL：全表扫描；
    • index：走索引，只遍历索引树；
    • range：索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行。显而易见的索引范围扫描是带有between或者where子句里带有<, >查询。当mysql使用索引去查找一系列值时，例如IN()和OR列表，也会显示range（范围扫描）,当然性能上面是有差异的；
    • ref：使用非唯一索引扫描或者唯一索引的前缀扫描，返回匹配某个单独值的记录行；
    • eq_ref：类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件；
    • const：当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量；
    • system：system是const类型的特例，当查询的表只有一行的情况下，使用system；
    • NULL：MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。
  • possible_keys：可以使用的索引，但是不一定使用；
  • key：实际使用的索引，如果没有使用则为null；
  • key_len：索引中使用的字节数，可通过该列计算查询中使用的索引的长度；
  • ref：表的连接匹配条件，即哪些列或常量被用于查找索引列上的值；
  • rows：估算的行数，不准确，因为范围时，他是范围的值；
  • extra：包含不适合在其他列中显示但十分重要的额外信息；
    • Using index：是否使用了覆盖索引；
    • Using where：mysql服务器将在存储引擎检索行后再进行过滤；
    • Using temporary：MySQL需要使用临时表来存储结果集，常见于排序和分组查询；
    • Using filesort：MySQL中无法利用索引完成的排序操作称为“文件排序”；
    • Using join buffer：改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。

聚集索引

InnoDB是按照索引组织数据，即索引组织表，表中数据按照主键顺序存放，聚集索引（clustered index）的特点就是按照每张表的主键构建B+树，而叶子节点存储的是row数据，也就是非主键数据也作为索引的一部分，clustered index的叶子节点也叫数据页。

实际应用中，每张表仅能构建一个聚集索引，查询优化器倾向使用聚集索引，并且由于数据按照主键的顺序存放，所以查询优化器可以针对范围查找做优化。

使用EXPLAIN分析查询计划，如果通过聚集索引也就是主键查询，对于主键的排序查找和范围查找很快。

非聚集索引

非聚集索引（secondary index），非聚集索引并不包含数据，而是包含一个bookmark，使用bookmark记录聚集索引的值，然后再通过聚集索引定位数据。

使用非聚集索引不影响聚集索引的效率，也就是如果非聚集索引树高度为N，聚集索引树高度为M，则进行一次查询的逻辑IO次数为N+M。

索引覆盖

索引覆盖是一个很重要的概念，也叫覆盖索引，非聚集索引因为包含的信息很少，定位索引外的数据时需要通过聚集索引，这样会使效率降低，但是如果我们要查询的信息在索引中已经包括，那么就可以形成索引覆盖，即无需查询聚集索引，减少逻辑IO；

比如创建下表：

create table test(
    a varchar(100),
    b varchar(200),
    c varchar(300),
    d varchar(400),
    primary key (a),
    key(b,c)
)engine = innodb;

insert test select '1000','1000','1000','1000';
insert test select '2000','2000','2000','2000';
insert test select '3000','3000','3000','3000';
insert test select '4000','4000','4000','4000';
insert test select '5000','5000','5000','5000';
insert test select '6000','6000','6000','6000';
insert test select '7000','7000','7000','7000';
insert test select '8000','8000','8000','8000';

执行如下操作：

#使用主键索引
explain select * from test where a = '1000';

#全表扫描，类型不匹配，不走索引
explain select * from test where a = 1000;

#主键索引，索引可以匹配前缀
explain select * from test where a like '100%';

#全表扫描，不走索引
explain select * from test where a like '%100%';

#全表扫描
explain select * from test where b = '1000';

#覆盖索引
explain select a,b,c from test where b = '1000' and c = '1000';

#

全文索引 Fulltext

B+树所以可以索引前缀，也就是like ‘xx%’的方式，但是如果使用‘%xx%’则无法走索引，这个时候可以使用全文索引。全文索引的实现通过倒排索引结构实现。

通过Match() 和 Against() 进行全文搜索，match指定搜索的字段，against指定算法和搜索内容。

InnoDB通过辅助表来建立倒排索引，InnoDB在transaction commit的时候将数据insert到FTS Index Cache中，然后再批量的刷新到倒排索引辅助表中。

InnoDB在创建表时，如果有全文索引，则自动添加FTS_DOC_ID列，类型必须是：bigint unsigned not null并添加unique index，如果手动添加必须符合规范；stopword表示某些无意义的单词无需进行索引，比如then，is，a等等，这一类进行查询无必要。

全文索引的限制：

  • 每个表只能有一个全文索引；
  • 多列组成的全文索引必须使用同一个字符集和排序规则；
  • 对于没有单词界定符的语言：中、日、韩都不支持全文索引；

相关SQL语句

添加索引
alert table xx add {index|key} idx_xx [index_type] (index_col_name,....,index_col_name)

删除索引
alert table xx drop {index|key|primary key} idx_xx

创建索引
create [unique] index idx_xx [index_type] on xx (a,b,c)

删除索引
drop index idx_xx on xx

显示索引
show index from xx

Cardinality值

cardinality是一个非常重要的值，这个值在show index from xx命令中显示，cardinality值表示索引中值唯一的条目的数量，这个值是查询优化器的参考值，不实时更新，可以通过下面的命令更新：

analyze table  xx \G;

通过这个值，我们可以看到我们创建的索引使用效率，如果 cardinality / table_rows_num 接近1，表示索引很有效，如果接近0，则应该考虑去掉索引；

对于低选择性的表不需要创建B+树索引，高选择性（重复性低）则很有必要创建。

InnoDB的Cardinlity的更新策略：

  • 当表中1/6的数据发生了变换；
  • 自上次更新次数超过20亿次；

在线索引创建：FIC

MySql旧版本的索引创建过程比较笨重：

  • 先创建一个临时表，这个临时表的schema和原表一致；
  • 在临时表上创建索引，执行操作；
  • 将数据copy到临时表中，进行表锁；
  • 删除原表，将临时表更名为原表名；

InnoDB的FIC，在创建辅助索引的时候，通过添加S锁，表仅仅提供read功能，但是primary key的创建和维护扔需要通过临时表。

新版本的Online DDL原理：对于操作，写到缓存日中，等index创建工作完成后，再对缓存日志执行，如果缓存日志空间不够会抛出异常；

上一篇： 数据库 乐观锁与悲观锁

下一篇： mysql InnoDB 行锁的实现 -Oracle是对每行数据加锁实现

推荐阅读