MySQL InnoDB之索引

B+树

B+树的插入

表5-1

B+树的删除

表5-2

聚集索引

按照每张表的主键构造一棵B+树。

辅助索引

辅助索引也为一棵B+树,叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以外,每个叶子节点中的索引行中还包含了一个书签(bookmark)。该书签用来告诉innodb存储引擎哪里可以找到与索引对应的行数据。innodb存储引擎的辅助索引的书签就是相应行数据的聚集索引建。

B+树索引的使用

联合索引

对表上的多个列进行索引。

create table buy_log(
    userid int unsigned not null,
    buy_date date
)ENGINE=InnoDB;

insert into buy_log values(1, '2009-01-01');
insert into buy_log values(2, '2009-01-01');
insert into buy_log values(3, '2009-01-01');
insert into buy_log values(1, '2009-02-01');
insert into buy_log values(3, '2009-02-01');
insert into buy_log values(1, '2009-03-01');
insert into buy_log values(1, '2009-04-01');

alter table buy_log add key(userid);
alter table buy_log add key(userid, buy_date);

explain select * from buy_log where userid=2;
explain select * from buy_log where userid=1 order by buy_date desc limit 3;

MySQL InnoDB联合索引学习

覆盖索引

从辅助索引中就可以得到查询的记录,而不需要查询聚集索引中的记录。

explain select count(*) from buy_log;
explain select count(*) from buy_log where buy_date>='2011-01-01' and buy_date<='2011-02-01';

MPR优化

Multi-Range Read优化的目的是为了减少磁盘的随记访问,并且将随机访问转化为较为顺序的数据访问。

在查询辅助索引时,首先根据得到的查询结果,按照主键进行排序,并按照主键排序的顺序进行书签查找。根据主键来顺序访问文件。

ICP优化

Index Condition Pushdown是MySQL数据库在取出索引的同时,判断是否可以进行where条件的过滤。对于某些查询,可以大大减少sql层对记录的索取。

drop table if exists t;
create table t(
    a int,
    b int,
    key (a),
    key (b)
)engine=innodb;

>insert into t select 1,1;
>insert into t select 1,2;
>insert into t select 2,3;
>insert into t select 2,4;
>insert into t select 1,2;

>explain select * from t where a=1 and b=2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
   partitions: NULL
         type: index_merge
possible_keys: a,b
          key: b,a
      key_len: 5,5
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using intersect(b,a); Using where; Using index
1 row in set, 1 warning (0.00 sec)

哈希索引

直接寻址技术 数组T[]

哈希表 利用哈希函数h将关键字域U映射到哈希表T[0, m-1]的槽位上。

自适应哈希索引为数据库自身创建并使用。

全文检索

将存储于数据库中的整本书或整篇文章中的任意内容信息查找出来。

采用倒排索引的形式(full inverted index):{单词,(单词所在所当的ID,在具体文档中的位置)}

Auxiliary Table(辅助表)存放倒排索引的分词 word, ilist(DocumentId, Position)

FTS Index Cache 全文检索索引缓存,是一个红黑树,根据(word, ilist)进行排序。

create table fts_a(
    FTS_DOC_ID bigint unsigned auto_increment not null,
    body text,
    primary key(FTS_DOC_ID)
);

insert into fts_a select NULL, 'pease porridge in the pot';
insert into fts_a select NULL, 'pease porridge hot, pease porridge cold';
insert into fts_a select NULL, 'Nine days old';
insert into fts_a select NULL, 'Some like it hot, some like it cold';
insert into fts_a select NULL, 'Some like it in the pot';
insert into fts_a select NULL, 'Nine days old';
insert into fts_a select NULL, 'I like cold days';

create fulltext index idx_fts on fts_a(body);


select * from fts_a where body like '%pease%';
select * from fts_a where match(body) against ('porridge' in natural language mode);
select * from fts_a where match(body) against ('porridge');
select * from fts_a where match(body) against ('+pease -hot' in boolean mode)\G
select * from fts_a where match(body) against ('pease hot' in boolean mode)\G
select fts_doc_id, body from fts_a where match(body) against ('"pease hot" @30' in boolean mode)\G
select fts_doc_id, body, match(body) against ('like > pot' in boolean mode) as relevance from fts_a;
select fts_doc_id, body, match(body) against ('like >hot <some' in boolean mode) as relevance from fts_a;
select * from fts_a where match(body) against ('po*' in boolean mode);
select * from fts_a where match(body) against ('"like hot"' in boolean mode);

MySQL索引背后的数据结构及算法原理