MySQL索引

基础知识

索引是创建在表上的，对数据库表中一列或多列的值进行排序的一种结构，可以提高查询的速度。

通俗的来说，数据库中存储的数据比作字典的话，索引就相当于是字典中的目录。如果没有索引，查找一个数据就需要从第一页开始全局检索直至找到需要的诗句，有了索引可以先在目录中根据拼音查找到该数据所在的页数，因此通过索引可以大大减少了查询时间，

存储类型

索引有两种存储类型：B树（BTREE）索引和哈希（HASH）索引，InnoDB和MyISAM存储引擎支持BTREE索引，MEMORY引擎两种都支持，默认为BTREE

大多数的存储引擎都支持B树索引，B树通常意味着所有的值按照顺序存储，并且每个叶子节点到根的距离相同，B树索引能顾加快数据访问的速度

在引入B数之前，数据结构中比较熟悉的一种树二叉树，那么为何不用二叉树来做索引，主要有几个问题：

• 如果索引数据很多，树的层次会很高（只有左右两个子节点），数据量大时查询还是会慢
• 二叉树每个节点只存储一个记录，一次查询在树上找的时候花费磁盘IO次数较多

所以它并不适合直接拿来做索引存储，算法设计人员在二叉树的基础之上进行了变种，引入了BTREE的概念，B树与二叉树有几个区别

• 不再是二叉搜索，而是N叉搜索，树的高度会降低，查询速度变快
• 叶子节点与非叶子节点都可以存储数据，且可以存储多个数据。
• 通过中序遍历，可以访问树上所有节点

BTREE被作为实现索引的数据结构被创造出来，是因为它能够完美的利用“局部性原理”，其设计逻辑是这样的：

• 内存读写快，磁盘读写慢，而且慢很多
• 磁盘预读：磁盘读写并不是按需读取，而是按页预读，一次会读一页的数据，每次加载一些看起来是冗余的数据，如果未来要读取的数据就在这一页中，可以避免未来的磁盘读写，提高效率（通常，一页数据是4K）
• 局部性原理：软件设计要尽量遵循“数据读取集中”与“使用到一个数据，大概率会使用其附近的数据”，这样磁盘预读能充分提高磁盘IO效能

B树特征：

1. 根节点至少包含两个孩子
2. 树中每个结点最多含有m个孩子(m >= 2)
3. 除了根节点和叶结点外，其他每个结点至少含有ceil(m/2)个孩子，ceil为向上取整
4. 所有叶子结点位于同一层（高度相同）
5. 假设每个非终端结点中包含有n个关键字信息，其中
6. Ki(i = 1…n)为关键字，且按顺序升序排列
7. 关键字的个数n必须满足：[ceil(m / 2) - 1] <= n <= m - 1
8. 非叶子结点的指针P[1],P[2],…,P[M];其中K[1]指向关键字小于K[1]的子树，P[M - 1] 指向关键字大于K[M - 1]的子树，其它P[i]指向关键字属于(K[i-1], K[i])的子树，比如看图中关键字值为8的这个结点，P1所对应的这个子树，其值均小于8

查询效率O(log n）

B+树特征

B+树是在BTree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构。

B+树是B树的变体，基本定义与B树相同，其改进点在于：

• B+树仍然是N叉树，但是层级变得更小，非叶子结点的子树指针与关键字个数相同（所以相对于B树，B+树能够存储更多的关键字）
• 非叶子结点的子树指针P[i]，指向关键字值[K[i], K[i+1])的子树，注意区间为左闭右开，左侧值可以取到。
• 范围查找方面，当定位min与max之后，中间叶子节点，就是结果集，不用中序回溯（范围查询在SQL中用得很多，这是B+树比B树最大的优势）
• 非叶子结点仅仅用来索引，数据都保存在叶子结点中（B+树所有的检索都是从根部开始，检索到叶子结点才能结束，而且非叶子结点不存储数据的话就能存储更多关键字）
• 所有叶子结点均有一个链指针指向下一个叶子节点，这样以来就不用中序遍历，可以直接通过next指针快速访问

范围查询 where age < 10 and age <35

B+ 树更适合用来做存储引擎索引

1、B+树的特点使得磁盘IO的代价更小，B+树的内部节点并没有指向关键字具体指针信息，因此其内部节点相对于B树更小,所以B+树的磁盘读写的代价更低

2、B+树的查询效率更加稳定（所有数据都存放在叶子节点，查询效率是O(logn))

3、B+树更利于数据库的扫描，进行范围查询

哈希结构

哈希结构和B树结构不能支持范围查询，哈希表对数据不排序，范围查询效率比较低，要查询整个哈希表结构；哈希结构通过一定的算法查询数据，在相同情况下，遇到大量的哈希值相等时性能不一定比B树性能高

主键索引、辅助索引、聚集索引、非聚集索引

mysql两种主流的存储引擎是MyISAM和INNODB

MyISAM存储引擎相关索引

主键索引

MyISAM引擎是使用B+树作为索引结构，MyISAM引擎的索引文件和数据文件是分离的，因此MyISAM的索引方式称之为非聚集索引。MyISAM引擎索引结构的叶子节点的data域存放的并不是实际的数据记录，而是数据记录的地址。

辅助索引

MyISAM中，主键索引和辅助索引在结构是一样的，B+树构建辅助索引，其叶子节点data域存放的是地址

主键索引要求key是唯一的，辅助索引的key以重复的

根据以上图可知，在MyISAM中，按照B+树的搜索算法搜索索引，主要分为两步：如果指定key存在，则取出其data域的值，然后以data域的值为地址，读取相应地址数据

在磁盘存储上,myisam非聚集索引将关键字和数据分开存储的，对应student表存在三个文件：student.MYD、student.MYI、student.frm

student.frm(存储表的结构)

student.MYD(存储表的数据)

student.MYI(存放表的索引数据)

INNODB存储引擎相关索引

主键索引

INNODB中的主键索引，叶子节点中，索引关键字和数据是在一起存放的

可以看到，索引关键字和数据一起存储在叶子节点上

辅助索引

在InnoDB的辅助索引，叶子节点上存放的是索引的关键字和对应的主键，如图

辅助索引的B+树，先根据索引关键字找到对应的主键，再去主键索引树上找到对应的行记录数据

从索引树上看，InnoDB的关键字和数据是存放在一块的，这种索引称之为聚集索引

在磁盘存储上，InnoDB聚集索引存在两个文件:

student.frm(存储表的结构)

student.ibd(存放索引和数据)

memory存储引擎

memory存储引擎使用内存来存储创建表数据，每个memory表实际值对应一个磁盘文件，格式是.frm(表结构定义)，memory类型表访问非常快，因为它的数据都存放在内存中，并且默认使用hash索引（不适合范围查询），一旦服务关闭，表中的数据就会丢失

MyISAM和innodb区别

---

种类 | 锁机制 | B-树索引 | 哈希索引 |外键 |事务 | 索引缓存 | 数据缓存

---

MYISAM| 表锁 | 支持 | 不支持 |不支持 |不支持 | 支持 | 不支持

---

INNODB| 行锁 | 支持 | 不支持 |支持 |支持 | 支持 | 支持

---

memory| 表锁 | 支持 | 支持 |不支持 | 不支持 | 支持 | 支持

---

锁机制：表示数据库在并发请求时，多个事务在操作时，并发操作的力度

B树索引和哈希索引：主要是用于加快SQL查询效率

外键：主要设置两个表连接的主要字段，关键信息

事务：保证SQL组合的原子操作，要么全部成功，要么全不成功，不能部分成功

索引缓存和数据缓存：在没有对数据和索引做修改之前，重复查询的数据可以不用进行磁盘IO，读取上一次内存中的查询缓存就可以

MyISAM和INNODB存储引擎使用锁

MyISAM采用的是表级锁

INNODB支持行锁和表锁，默认使用行锁

表级锁和行级锁对比：

表级锁：MySQL中锁定力度最大的一种锁，对当前操作的整个表加锁，实现简单，资源消耗比较少，加锁块，不会出现死锁，锁的力度最大，触发锁冲突的概率最高，并发度最低，MyISAM和INNODB都支持表级锁

行级锁：MySQL中锁定粒度最小的一种锁，只针对当前操作的行进行加锁，行级锁能大大减少数据库操作的冲突，其加锁力度最小，并发度高，但是加锁的开下也是最大，加锁慢，会出现死锁

innodb行锁实践：

INNODB的行锁是通过给索引上的索引项加锁来实现的，而不是给表的行记录加锁实现的，这意味着，只有通过索引条件检索数据时，INNODB才使用行级锁，否则使用是表锁（如果没有索引，存储引擎只能给所有行加锁，和表锁一样）

注意：使用select ...for update可以主动获取锁（排他锁）

索引优缺点

优点

索引的优点是可以提高检索数据的速度，这是创建索引的最主要的原因;对于有依赖关系的子表和父表之间的联合查询时，可以提高查询速度;使用分组和排序子句进行数据查询时，同样可以显著节省查询中分组和排序的时间。

缺点
索引的缺点是创建和维护索引需要耗费时间，耗费时间的数量随着数据量的增加而增加;索引需要占用物理空间，每一个索引要占一定的物理空间;增加、删除和修改数据时，要动态的维护索引，造成数据的维护速度降低了。

索引分类

MySQL索引包括普通索引、唯一性索引、单列索引、多列索引和空间索引等。

普通索引

定义

在创建普通索引时，不附加任何限制条件。这类索引可以创建在任何数据类型中，其值是否唯一和非空由字段本身的完整性约束条件决定。建立索引以后，查询时可以通过索引进行查询。例如，在 student 表的stu_id字段上建立一个普通索引。查询记录时，就可以根据该索引进行查询。

唯一性索引

定义

使用UNIQUE参数可以设置索引为唯一性索引。在创建唯一性索引时，限制该索引的值必须是唯一的。例如，在student 表的stu_name字段中创建唯一性索引，那么stu_name字段的值就必需是唯一的。通过唯一性索引，可以更快速地确定某条记录。主键就是一种特殊唯一性索引。

创建唯一性索引采用UNIQUE INDEX 索引名 (字段)创建

全文索引

定义

使用FULLTEXT参数可以设置索引为全文索引。全文索引只能创建在CHAR、VARCHAR或TEXT类型的字段上。查询数据量较大的字符串类型的字段时，使用全文索引可以提高查询速度。例如，student 表的 information字段是TEXT类型，该字段包含了很多的文字信息。在 information字段上建立全文索引后，可以提高查询information字段的速度。MySQL数据库从3.23.23版开始支持全文索引，但只有MyISAM存储引擎支持全文检索。在默认情况下，全文索引的搜索执行方式不区分大小写。但索引的列使用二进制排序后，可以执行区分大小写的全文索引。

创建全文索引采用**FULLTEXT **INDEX 索引名 (字段)创建

单列索引

定义

在表中的单个字段上创建索引。单列索引只根据该字段进行索引。单列索引可以是普通索引，也可以是唯一性索引，还可以是全文索引。只要保证该索引只对应一个字段即可。

多列索引

定义

多列索引是在表的多个字段上创建一个索引。该索引指向创建时对应的多个字段，可以通过这几个字段进行查询。但是，只有查询条件中使用了这些字段中第一个字段时，索引才会被使用。例如，在表中的id、name和 sex字段上建立一个多列索引，那么，只有查询条件使用了id字段时该索引才会被使用。

空间索引

定义

使用SPATIAL参数可以设置索引为空间索引。空间索引只能建立在空间数据类型上，这样可以提高系统获取空间数据的效率。MySQL 中的空间数据类型包括GEOMETRY 和POINT、LINESTRING和 POLYGON等。目前只有MyISAM存储引擎支持空间检索，而且索引的字段不能为空值。对于初学者来说，这类索引很少会用到。

索引操作

索引创建

普通索引采用INDEX关键字指定字段，可以采用explain查看索引是否被引用，其他索引加修饰关键字UNIQUE等即可。

在已存在的表上创建索引

create [unique|fulltext|apatial] index 索引名 on 表名 (属性名 [(长度)] [ASC|DESC]);

例如，在test的id上建立名为index_id的普通索引：

在test的id上建立名为index_id的全文索引：

这里发现，首次创建失败，失败的原因有两个，一个是当前表test的存储引擎是InnoDB而不是MyISAM，第二点是全文索引的字段数据类型必须是CHAR、VARCHAR、TEXT等类型。经过修改创建成功。

ALTER创建索引

创建语法如下

alter table 表名 add [unique|fulltext|spatial] index 索引名（属性名 [(长度)] [(ASC|DESC)]）;

例如：创建一个学生表，根据已存在的表创建多列索引：

删除索引

对于已存在的索引，删除语法如下：

DROP INDEX 索引名 ON 表名

例如删除test表中的索引full_name

索引执行过程

单表查询-普通索引

对于student表来进行数据查询，通过SID来查询的时候（SID是主键），因为使用主键索引(INNODB会自动给主键字段创建主键索引树)，当通过SID作为where检索条件过滤时，首先从B+树的主键索引快速找到数据，因为是INNODB的存储引擎，将主键索引和数据都存放在一起，找到SID，就可以找到这一行数据，不用把整个表扫描一遍、效率比较高

例如：

使用name字段来查询”LG1213“名称

explain select * from student where Sname="LG1213"

首先根据name字段来查询学生信息，用到了idx_name辅助索引，INNODB的辅助索引叶子节点存储的是辅助索引的值和对应的行的主键（此处对应的主键SID），根据以上的SQL，先查询name字段的辅助索引B+树，找到name=’LG1213‘的节点，获取对应的主键SID=6，然后在拿SID=6去主键索引树上寻找数据，上面SQL总共搜索两次B+索引树。

通过name找到对应主键ID

explain select SID from student where Sname = "LG1213";

上面的SQL执行过程中使用了idx_name辅助索引，在辅助索引树上根据name来查找对应行的主键，上面select中只查询ID，因此在辅助索引树上就可以拿到结果，不用再去主键索引树上来查询结果

通过name找到对应性别（非主键）

explain select Ssex from student where Sname = "LG1213";

查询两次B+树，一次通过name辅助索引树查询到主键，在通过主键索引树查询到对应一行数据获取sex值

单表查询索引执行过程-普通索引+排序或者分组概念

一个订单表，包含用户id，商品id，以及下单时间

CREATE TABLE `orderlist` (
  `userid` int(11) NOT NULL,
  `productid` int(11) NOT NULL,
  `date` datetime DEFAULT NULL
) ENGINE=InnoDB

查询用户id=1且按照日期升序排序的结果

select * from orderlist where userid=1 order by date;

分析SQL执行计划：

explain select * from orderlist where userid=1 order by date\G

通过explain分析，可以看到type=ALL，该SQL执行是查询了整张表，效率比较低，对数据查询userid=1之后的所有数据记录，还要按照data子弹进行升序排序，Extra栏出现了Using fileSort现象，得到的所有的结果集，对结果集进行文件排序，出现了该fileSort，SQL语言性能比较差，需要进行优化。

fileSort：

Using filesort表示在索引之外，需要额外进行外部的排序动作。导致该问题的最常见的就是只用了where和order by，一般可以通过合适的索引来减少或者避免。

接下来给userid和date分别创建索引，然后重复执行SQL，查看执行计划，看有什么不同

create index idx_userid on orderlist(userid);

create index idx_date on orderlist(date);

在SQL执行过程中，并没有选择合适的索引，通过强制指定索引：force index(索引名)

在使用force强制索引，如果前置使用idx_userid，SQL执行查询在辅助索引上找到关键字找到主键，然后在拿主键id，到主键索引树上去搜索数据，搜索到数据之后，需要根据date进行文件排序，效率是比较低的，给定userid的索引，给定date索引还是会出现filesort和全表扫描的问题。

一个SQL查询操作，一次执行使用一个索引，因此选用某一个索引，其他索引不在起作用

因此继续优化，可以创建userid和date的联合索引，由于userID是where的过滤条件，因此联合索引userID在前，date在后

create index idx_userid_date on orderlist(userid,date);

再来查看SQL执行计划

给userid和date创建联合索引后，不会出现fileSort，根据userId=1查询辅助索引树，找到的数据也已经按照date排好序，然后在区主键索引树去查找正行数据就可以啦，效率比较高，

之所以还要使用force index（idx_userid_date）是防止SQL优化器优化索引的时候，因为测试的数据量比较小，有时候使用索引不一定比正表的效率高。

多表查询：连接查询索引的执行过程以及优化

使用多表查询连接的时候，MySQL会首先判断那个表小，表小主要指的是数据行数少。

小表无论如何都是整表遍历的，是不使用索引的，但是大表就需要用到索引

所以在连接查询时，小表总是需要整表搜索的，建索引是没有用的，大表创建索引是能够提交查询效率的

小表是决定查询的次数，大表决定查询时间

explain select * from tb1 inner join tb2 on tb1.id=tb2.id

这里tb2有3条数据，tb1有8条数据，所以tb2是小表，进行全表扫描，大表是tb1,根据索引进行过滤查询，查询SQL执行过程如下：

可以看出先查询的是tb2的整表，然后在tb1的表的索引树上查找

那么如果为连接的多表加入过滤条件呢，比如查询tb1中name为tom的数据

explain select * from tb1 inner join tb2 on tb1.id=tb2.id where tb1.name="tom"

MySQL的执行过程是先根据where条件进行过滤，通过过滤后的数据进行比较确定大小表，tb1有8条数据，根据检索条件过滤完剩1条数据，tb2过滤完剩3条数据，此时小表就变成了tb1，tb2就是大表，tb2上的索引就可以用到了。

在连接查询中，大小表的角色是不一定的，没有where子句，那么就按照表的行数来决定大小表，如果有where子句，那么就按照条件过滤后的函数来确定大小表

左前缀原则

MySQL中索引可以以一定的顺序引用多例，这种索引称之为联合索引，假如User表中name和city加联合索引就是（name,city）,从而左前缀原则指的是，如果查询的时候条件精确匹配索引的左边连续一列或者几列则可以索引

select * from user where name=XX and city=XX; //命中索引的

select * from user where name=XX ; //命中

select * from user where city=XX ; //不能命中索引

select * from user where city=XX and name=XXX; //命中索引的

需要注意的是，查询的时候如果两个条件都使用上了，但是顺序不同，比如city =XX and name=XXX，那么现在的SQL执行引擎会自动优化为匹配联合索引的顺序，这样就能命中索引，

由于最左侧远侧，在创建联合索引时索引的字段的顺序需要好好考虑，order by也遵从最左侧原则

索引的设计原则

可以看出，使用索引是有能提高查询效率，但是给表创建过多的索引，效率反而会降低，因此在设计表索引的时候，

需要遵循以下的设计原则：

1、给区分度高的字段创建索引 eg：学号、身份证号

2、给经常需要排序，分组和多表联合操作的字段创建索引

3、经常作为查询条件的字段创建索引

4、索引的数据不宜过多

5、对于多列索引，优先指定最左边的列集

6、删除不在使用或者很少使用的索引