索引

一. 索引目的

索引的目的在于提高查询效率,可以类比字典,如果要查“mysql”这个单词,我们肯定需要定位到m字母,然后从下往下找到y字母,再找到剩下的sql。如果没有索引,那么你可能需要把所有单词看一遍才能找到你想要的,如果我想找到m开头的单词呢?或者ze开头的单词呢?是不是觉得如果没有索引,这个事情根本无法完成?

二. 索引原理

通过不断的缩小想要获得数据的范围来筛选出最终想要的结果,同时把随机的事件变成顺序的事件,也就是我们总是通过同一种查找方式来锁定数据。

三. 索引数据结构(B+树)

3.1 什么是B+树

B+ 树是一种树数据结构,是一个n叉排序树,每个节点通常有多个叶子节点,一棵B+树包含根节点、内部节点和叶子节点。根节点可能是一个叶子节点,也可能是一个包含两个或两个以上叶子节点的节点。

  • 浅蓝色的块我们称之为一个磁盘块,可以看到每个磁盘块包含几个数据项(深蓝色所示)和指针(黄色所示)
  • 如图磁盘块1包含数据项17和35,包含指针P1、P2、P3,P1表示小于17的磁盘块,P2表示在17和35之间的磁盘块,P3表示大于35的磁盘块,真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。
  • 非叶子节点只不存储真实的数据,只存储指引搜索方向的数据项,如17、35并不真实存在于数据表中。

3.2 b+树的查找过程

如图所示,如果要查找数据项29,那么首先会把磁盘块1由磁盘加载到内存,此时发生一次IO,在内存中用二分查找确定29在17和35之间,锁定磁盘块1的P2指针,内存时间因为非常短(相比磁盘的IO)可以忽略不计,通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存,发生第二次IO,29在26和30之间,锁定磁盘块3的P2指针,通过指针加载磁盘块8到内存,发生第三次IO,同时内存中做二分查找找到29,结束查询,总计三次IO。真实的情况是,3层的b+树可以表示上百万的数据,如果上百万的数据查找只需要三次IO,性能提高将是巨大的,如果没有索引,每个数据项都要发生一次IO,那么总共需要百万次的IO,显然成本非常非常高。

四. 建索引的几大原则

1. 最左前缀匹配原则,mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配;

例如:a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引,d是用不到索引的,如果建立(a,b,d,c)的索引则都可以用到,a,b,d的顺序可以任意调整。

2. =和in可以乱序;

例如:a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序,mysql的查询优化器会帮你优化成索引可以识别的形式。

3. 尽量选择区分度高的列作为索引,字段不重复的比例越大扫描的记录数越少;
4. 索引列不能参与计算
5. 尽量的扩展索引,不要新建索引

例如:表中已经有a的索引,现在要加(a,b)的索引,那么只需要修改原来的索引就可以。

五. 索引的分类

1. 普通索引:
  • MySQL 中最基本的索引类型,它没有任何限制,唯一任务就是加快系统对数据的访问速度
  • 普通索引允许在定义索引的列中插入重复值和空值。
  • 创建普通索引时,通常使用的关键字是 INDEX 或 KEY。
// 在 tb_student 表中的 id 字段上建立名为 index_id 的索引。
CREATE INDEX index_id ON tb_student(id);
2. 唯一索引
  • 唯一索引与普通索引类似,不同的是创建唯一性索引的目的不是为了提高访问速度,而是为了避免数据出现重复。
  • 唯一索引列的值必须唯一,允许有空值。如果是组合索引,则列值的组合必须唯一。
  • 创建唯一索引通常使用 UNIQUE 关键字。
// 在 tb_student 表中的 id 字段上建立名为 index_id 的索引
CREATE UNIQUE INDEX index_id ON tb_student(id);
3. 组合索引
  • 组合索引也称为复合索引或多列索引。相对于单列索引来说,组合索引是将原表的多个列共同组成一个索引。多列索引是在表的多个字段上创建一个索引。该索引指向创建时对应的多个字段,可以通过这几个字段进行查询。但是,只有查询条件中使用了这些字段中第一个字段时,索引才会被使用。
// tb_student 表中的 name 和 address 字段上建立名为 index_na 的索引
CREATE INDEX index_na ON tb_student(name,address);
4. 全文索引
  • 全文索引主要用来查找文本中的关键字,只能在 CHAR、VARCHAR 或 TEXT 类型的列上创建。
  • 在 MySQL 中只有 MyISAM 存储引擎支持全文索引。
  • 全文索引允许在索引列中插入重复值和空值。
  • 对于大容量的数据表,生成全文索引非常消耗时间和硬盘空间。
  • 创建全文索引使用 FULLTEXT 关键字。
// 在 tb_student 表中的 info 字段上建立名为 index_info 的全文索引
CREATE FULLTEXT INDEX index_info ON tb_student(info);
5. 空间索引
  • 空间索引是对空间数据类型的字段建立的索引,使用 SPATIAL 关键字进行扩展。
  • 创建空间索引的列必须将其声明为 NOT NULL,空间索引只能在存储引擎为 MyISAM 的表中创建。
  • 空间索引主要用于地理空间数据类型 GEOMETRY。
// 在 tb_student 表中的 line 字段上建立名为 index_line 的索引
CREATE SPATIAL INDEX index_line ON tb_student(line);

参考网址

https://tech.meituan.com/2014/06/30/mysql-index.html
http://c.biancheng.net/view/7897.html