索引

索引的原理

索引的目的是为了提高查询效率，例如查字典时用的目录，查飞机、火车票等等。

本质都是：通过不断地缩⼩想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事

件，也就是说，有了这种索引机制，我们可以总是⽤同⼀种查找⽅式来锁定数据。

磁盘IO与预读

当⼀次IO时，不光把当前磁盘地址的数据，⽽是把相邻的数据也都读取到内存缓冲区内。

索引的数据结构

树

树状图是⼀种数据结构，它是由n（n>=1）个有限结点组成⼀个具有层次关系的集合。它是根朝上，⽽叶朝下的。

它具有以下的特点：每个结点有零个或多个⼦结点；没有⽗结点的结点称为根结点；每⼀个非根结点有且只有

⼀个⽗结点；除了根结点外，每个⼦结点可以分为多个不相交的⼦树。

B+树

B+树是通过⼆叉查找树，再由平衡⼆叉树，B树演化⽽来。

聚集索引与辅助索引

数据库中的B+树索引可以分为聚集索引（clustered index）和辅助索引（secondary index），

聚集索引与辅助索引相同的是：不管是聚集索引还是辅助索引，其内部都是B+树的形式，即⾼度是平衡的，

叶⼦结点存放着所有的数据。

聚集索引与辅助索引不同的是：叶⼦结点存放的是否是⼀整⾏的信息

聚集索引的好处之⼀：它对主键的排序查找和范围查找速度非常快，叶⼦节点的数据就是⽤户所要查询的数据。

如⽤户需要查找⼀张表，查询最后的10位⽤户信息，由于B+树索引是双向链表，

所以⽤户可以快速找到最后⼀个数据⻚，并取出10条记录

聚集索引的好处之⼆：范围查询（range query），即如果要查找主键某⼀范围内的数据，通过叶⼦节点的上层中间

节点就可以得到⻚的范围，之后直接读取数据⻚即可

2、辅助索引

表中除了聚集索引外其他索引都是辅助索引（Secondary Index，也称为非聚集索引），与聚集索引的区别是：

辅助索引的叶⼦节点不包含⾏记录的全部数据。

叶⼦节点除了包含键值以外，每个叶⼦节点中的索引⾏中还包含⼀个书签（bookmark）。该书签⽤来告诉

InnoDB存储引擎去哪⾥可以找到与索引相对应的⾏数据。

由于InnoDB存储引擎是索引组织表，因此InnoDB存储引擎的辅助索引的书签就是相应⾏数据的聚集索引键。

辅助索引的存在并不影响数据在聚集索引中的组织，因此每张表上可以有多个辅助索引，但只能有⼀个聚集索

引。当通过辅助索引来寻找数据时，InnoDB存储引擎会遍历辅助索引并通过叶⼦级别的指针获得只想主键索引

的主键，然后再通过主键索引来找到⼀个完整的⾏记录。

聚集索引和非聚集索引的区别

聚集索引

1.纪录的索引顺序与⽆⼒顺序相同

因此更适合between and和order by操作

2.叶⼦结点直接对应数据

从中间级的索引⻚的索引⾏直接对应数据⻚

3.每张表只能创建⼀个聚集索引

非聚集索引

1.索引顺序和物理顺序⽆关

2.叶⼦结点不直接指向数据⻚

3.每张表可以有多个非聚集索引，需要更多磁盘和内容

多个索引会影响insert和update的速度

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

MySQL索引管理

⼀ 功能

#1. 索引的功能就是加速查找

#2. mysql中的primary key，unique，联合唯⼀也都是索引，这些索引除了加速查找以外，还有约

束的功能

⼆ MySQL常⽤的索引

普通索引INDEX：加速查找

唯⼀索引：

-主键索引PRIMARY KEY：加速查找+约束（不为空、不能重复）

-唯⼀索引UNIQUE:加速查找+约束（不能重复）

联合索引：

-PRIMARY KEY(id,name):联合主键索引

-UNIQUE(id,name):联合唯⼀索引

-INDEX(id,name):联合普通索引 

三 索引的两⼤类型hash与btree

#我们可以在创建上述索引的时候，为其指定索引类型，分两类

hash类型的索引：查询单条快，范围查询慢

btree类型的索引：b+树，层数越多，数据量指数级增⻓（我们就⽤它，因为innodb默认⽀持它）

#不同的存储引擎⽀持的索引类型也不⼀样 

四 创建/删除索引的语法

#⽅法⼀：创建表时

CREATE TABLE 表名 (

字段名1 数据类型 [完整性约束条件…],

字段名2 数据类型 [完整性约束条件…],

[UNIQUE | FULLTEXT | SPATIAL ] INDEX | KEY

[索引名] (字段名[(⻓度)] [ASC |DESC])

);

#⽅法⼆：CREATE在已存在的表上创建索引

CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名

ON 表名 (字段名[(⻓度)] [ASC |DESC]) ;

#⽅法三：ALTER TABLE在已存在的表上创建索引

ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX

索引名 (字段名[(⻓度)] [ASC |DESC]) ;

 

#删除索引：DROP INDEX 索引名 ON 表名字;

 

 

#1、and与or的逻辑

条件1 and 条件2:所有条件都成立才算成立，但凡要有⼀个条件不成立则最终结果不成立

条件1 or 条件2:只要有⼀个条件成立则最终结果就成立

#2、and的⼯作原理

条件：

a = 10 and b = 'xxx' and c > 3 and d =4

索引：

制作联合索引(d,a,b,c)

 

对于连续多个and：mysql会按照联合索引，从左到右的顺序找⼀个区分度⾼的索引字段(这

样便可以快速锁定很⼩的范围)，加速查询，即按照d—>a->b->c的顺序

#3、or的⼯作原理

条件：

a = 10 or b = 'xxx' or c > 3 or d =4

索引：

制作联合索引(d,a,b,c)

 

⼯作原理:

对于连续多个or：mysql会按照条件的顺序，从左到右依次判断，即a->b->c->d