mysql字符串索引原理_十一、MySQL中的索引原理 －系统的撸一遍MySQL

MySQL支持的索引类型

MySQL支持多种索引类型，每一个存储引擎对其有着不同程度的支持。

MySQL支持以下四种索引，具体支持情况见下表：

索引

MyISAM

InnoDB

Memory

B-Tree

支持

支持

支持

HASH

不支持

不支持

支持

R-Tree

支持

不支持

不支持

Full-Text

支持

不支持

不支持

B-Tree

算是平时用到最多的一种索引类型，大部分引擎都支持。

Hash

目前只有Memory支持，对于等值查询有着较高的效率，不支持范围查询。

R-tree

空间索引，在MyISAM中实现，用于地理位置的查询。

Full-Text

全文索引，紧在MyISAM中支持，MySQL5.6后InnoDB也开始支持。

支持的索引规则

1、最左前缀规则，使用a、b、c联合索引的时候，需要按从左到右的顺序进行SQL的查询，可以包含一部分字段，比如只根据a进行检索也可以使用索引，但是不可以颠倒顺序进行查询。

2、只查询索引中的字段，比如：

//简单的登陆SQL，假设username为唯一索引，id为主键

SELECT * FROM user WHERE username = 'fuckphp' AND password = 'fuckphp';

//如上SQL的效率会比下面的效率低

SELECT id FROM user WHERE username = 'fuckphp' AND password = 'fuckphp';

3、可以使用前缀索引，例如：

//前十个字符进行索引

ALTER TABLE book ADD INDEX title_idx(title(10));

//使用title_idx索引进行查询

SELECT * FROM book WHERE title like 'MySQL%';

4、如果索引字段为字符串类型，那么如果检索字段用整型匹配，将无法使用索引。

5、如果条件为or的时候，如果前面条件查询索引但是后面的没有MySQL将不会使用索引，例如。

//username为索引，但mobile不是索引，所以这个SQL不会走索引

SELECT * FROM user WHERE username = 'fuckphp' OR mobile = '11011074110';

MySQL索引的实现

MySQL使用B+Tree作为索引结构

B＋树规则

M阶的B+树，每个非叶节点最多拥有M-1个元素，最少有M/2个元素。

每个非叶节点最多有M个子节点，子节点数目总比元素数目多一个。

其余最少拥有两个子节点。

当每个节点中的元素数量或者子节点数量不符合规则的时候，将会触发分裂或者合并。

InnoDB中的B+Tree存储

(偷图一张)

主键

B+树中的节点中不包含数据，只保存主键与指针，如果不存在主键，InnoDB会自动创建一个隐藏的作为主键。

聚集

同时Innodb的数据索引聚集在同一个结构内(聚集索引，叶子节点直接保存数据)，而非引用指针。

硬盘

硬盘读取数据按页进行预加载，innodb根据每页大小/每个元素的大小来约定B+tree的度，保证读取每个节点只进行一次IO。

辅助索引

辅助索引基于B+Tree建立，但是叶子节点保存查询ID的主键，在通过主键来进行二次查找。

MyISAM中的B+Tree存储

MyISAM中的数据文件和索引文件时分开的，B+Tree索引叶子节点中保存了数据的指针，如上图，所以属于非聚集。

相关资料