Mysql-深入浅出索引

索引是面试的必考题,这个专题值得花费1-2天进行专门理解

1. 什么是索引

索引:索引是一种用于快速查询和检索数据的数据结构,其本质可以看成是一种排序好的数据结构

索引的作用就类似于书的目录。

通过索引,可以大大加快索引的检索速度,这也是创建索引的最主要原因,避免全表查询
通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性

所谓唯一性索引,实际上是数据库系统的一种约束,系统在创建该索引的时候检查是否会有重复的键值,并且在每次update和insert的时候进行检查,从而不允许具有索引值相同的行

相对的,创建索引和维护索引需要耗费很多时间,当对表中的数据进行增删改的时候,如果数据有索引,那么索引也需要动态地进行修改,会降低SQL的执行效率

值得注意的一点是:使用索引并不一定能够提高查询性能

相反的,当选取的主键的性质和特征不当,将会导致页分裂等重量级的底层数据结构调整,将会导致严重的性能问题,当数据库的数据量不大,使用索引也不一定能够带来很大的提升

2. 索引的常见模型

索引的出现是为了提高查询效率,但是实现索引的方式却有很多种,比较常用的数据结构有

• 哈希表
• 有序数组
• 搜索树

2.1 哈希表

哈希表简述:哈希表通常由存储空间(通常为数组)+哈希函数组成,其中存储空间负责具体数据的存储,而哈希函数和存储数据的特征值决定了数据在存储空间中的存储位置

它是一种以键-值(key-value)存储数据的结构,它的核心是输入值和哈希函数,哈希函数会将一个输入值x经过运算后成为一个固定的位置下标,x->H(x),虽然哈希函数具有抗碰撞性,但是依然会产生碰撞,当产生碰撞的s时候,会有如下方案来解决哈希冲突

• 拉链法:当有多个输入产生相同的哈希值的时候,在该节点上创建一个链表,将相同哈希值的输入通过尾插法或者头插法插入到链表上,当链表过长的时候就会导致查找性能退化成O(n)的算法,因此通常也会采取将链表转化为红黑树的策略来降低搜索的复杂度
• 再哈希法:使用另外的哈希函数,计算出不同的哈希值使用

如图所示,假设我们需要通过身份证查询名字,如果这时候使用哈希表作为我们实现索引的方案,那么过程如下:

首先将身份证号进行hash,然后会产生一个对应的下标,根据下标位置查找位置

public String search(String id){
    Integer index = hash(id);//复杂的运算
    Students[] name = memeory[index];
    int length = name.length();
    if(name.length == 1){
        return name.name;
    }
    String nameret = null;
    for(Student student:Students){
        if(name.id == student.id){
            nameret = name.name;
            break;
        }
    }
   if(nameret == null){
       throw new RunTimeException("缺页异常");
   }
   return nameret;//if not null
}

需要注意的是,图中四个ID_card_n的值并不是递增的,这样做的好处是增加新的值是很快的,但是在做区间查询的时候将会相当慢

在使用哈希表实现索引的时候,做区间查询必须根据查询得到的结果进行判断,这是因为值与索引的位置没有直接的关系,因此有一个缺点:执行区间查询将会导致全表查询

所以,哈希表这种结构只适合于只有等值查询的场景

2.2 有序数组

而有序数组在等值查询和范围查询中的性能都非常好,如图所示,假设身份证号是不会重复的,那么这个数组就是按照身份证号递增的顺序保存的,既然是有序的,那么就可以使用二分法进行查询。

这个索引的实现是支持范围查询的,比如说想找[ID_CARD_n1,ID_CARD_n4],可以先通过二分法找到ID_CARD_n1,如果不存在这个ID_CARD_n1,那么就通过找到二分左边的边界,然后找到第一个大于ID_CARD_n4的身份证号,算法结束

在修改数据的时候,开销是很大的

这是因为数组在内存的存储是连续的,假设想要删除数据或者在中间插入数据,将会导致大量的元素移动。

所以,有序数组只适合于静态的存储引擎,比如说要保存的是某城市去年的人口信息,这种信息应该是不变的。

2.3 搜索树

如图所示,二叉树的搜索特点是:父节点左子树所有节点的值都小于父节点的值,而父节点右子树所有节点的值都大于父节点的值。

在维持这棵树为平衡二叉树的时候,可以达到O(logN)的时间复杂度,而如果没有维护这棵树,则可能退化为链表,变为O(n)的时间复杂度

平衡二叉树:是一棵空树或者它的左右两个子树的高度的差绝对值不超过1,并且两棵子树都是平衡二叉树

树可以有二叉,也可以有多叉,在多叉的情况下,儿子之间的大小保证从左到右是递增的。二叉树是搜索效率最高的,但是实际上绝大多数的数据库存储并不使用二叉树,这是因为索引不止存在于内存中,还要写到磁盘上

因此在实际上执行查询的时候,每通过索引访问一次不存在于内存而位于磁盘中的页的时候,这时候就会导致I/O,这里结合场景进行理解,比如说有一个100w行的表,树高20(2^20),一次查询最多可能访问20个数据块,磁盘访问速度为10ms,而且为随机I/O,在最差情况下搜索一条记录需要200ms,这还是在复杂调整树的结构的前提的执行效率,因此一旦数据量达到一定的量级,二叉搜索树而言,无论是维护平衡性还是I/O取节点,都将带来非常高昂的成本

为了让一个查询尽可能少的与磁盘设备进行交互,就必须让查询过程尽可能访问少的数据库

不使用二叉树而是使用N叉树,这里的N叉树取决于数据块的大小

理解:首先我们知道,二叉树的寻址方式是这样的,首先在每一次的搜索中,它能够访问到当前的节点root和left和right,因此在单次搜索中除了判断left和right是不是需要的数据之外,还可以根据left和right确定下一次的寻址目标,在这样的情况下,每一次I/O最多只能够读取到2个索引数据,但是实际上,每一次I/O所能够传送的最大数据块,理论上可以存储非常多的索引结构,因此,采用N叉树从I/O的吞吐量上能够减少持续I/O的概率,从数据结构的角度上来看,它降低了树的高度

以InnoDB的一个整数字段索引为例,这个N差不多是1200,当这棵树的高度达到4的时候,可以存储17亿的数据,考虑到root节点始终是在内存中的,一个10亿行的表上一个整数字段的索引,查找一个值顶多需要进行3此的磁盘I/O,而对于之前的100w的数据,只需要一次I/O,并且根据程序的局部性原理(时间局部性和空间局部性),其他数据也大概率被存储到内存中了,大大降低I/O的概率。

在Mysql中,索引是在存储引擎层实现的,索引并没有统一的实现标准,不同存储引擎的索引的工作方式并不一致,即使多个存储引擎支持同一种类型的索引,其底层的实现也可能是不同的

3. InnoDB中索引的推演

select * from table where 列名 = xxx;

3.1 在一个页之中进行查找

假设目前表中的记录比较少,可以在一页中完全存储下来,在查找记录的时候

可以根据搜索条件的不同分为两种情况:

• 以主键为搜索条件(这时候具有页目录),由于主键为自增主键,在页目录中可以使用二分法快速定位到对应的槽,然后再遍历该分组就能够快速找到指定的记录
• 以其他列作为搜索区间,因为数据页中并没有对非主键列建立页目录,因此只能够从最小记录开始,依次遍历单链表中的每条记录,然后对比每条记录是否符合条件,这样的查询将会导致全表查询

3.2 在很多页中进行查找

从很多页中进行记录的查找,可以分为如下两步

• 定位到记录所在的页
• 从定位到的页中查找响应的记录

在没有索引的情况下,不论是根据主键或者是根据非主键查找,并不知道查找到对应的页,因此需要遍历所有的页

3.3 尝试设计一个索引

建一个表

create table index_demo(
    c1 INT,
    c2 INT,
    c3 char(1),
    primary key(c1)
)ROW_FORMAT = Compact;

那么首先从底层开始理解,它每一条记录就是链表上的一个节点,而这个节点的结构体表示是如何的呢?看图

与之前学习的数据结构知识相关,链表的组织形式是具有头节点和尾节点的,其中record_type通过枚举量来告知该节点的类型

最终组织成为的链表如图所示,由于页的分配并不是连续的,因此需要额外的空间来记录下一页对本页的偏移量

给所有的页建立一个目录项

因为这些16KB的页在物理存储是不连续的,所以想要从这么多页中根据主键值快速定位到某些记录,我们就需要给这些页做一个目录,每一个目录项对应一个页,每个目录项包括:

• 页的用户记录中的最小的主键值,用key进行表示
• 页号,用pageNo表示

这里的话就有点像跳表,只不过跳表的增强部分是链表,而目录这个实现可以基于数组

页分裂:在对页中的记录增删改查的过程中,需要通过记录移动的方式来保证下一个记录页中的主键值必须大于上一个页用户记录的主键值

如图所示,讲述一下一个基本的索引的工作原理

如图构建得到的页表结构在数据库中就被称作为索引,首先,系统会创建一个目录,该目录具有一条链表,该链表记录有各个页的最小索引,通过二分,找到小于targetKey的第一个目录项,然后找到大于targetKey的第一个目录项,如果在查找过程中直接命中tatgetKey,那么就直接return,否则,将查找到的pageNo加入到目标页中,执行搜索,然后执行顺序查找(二分优化)

3.4 二级页表优化

当数据过多的时候,也会导致目录项指数级的增加的,这时候可以产生二级页表,也就是说实现一个页表的页表

3.5 多级页表优化

二级页表的问题在于,它依然避免不了每个页都要去进行遍历,解决办法是,再生成一层目录页,这个目录页是用来记录目录页的目录页,它标志了每一个目录页中的最小主键,通过此链表再次缩小查找的范围

最终形成的组织如图所示

这个结构实际上就是B+树,也没有什么高深的,对上图解读一番:

首先,这个图中的蓝色框框都是一个一个的数据页,在第0层(最低层)的这些节点是叶子节点,他们用于存储真正的记录。在这些叶子节点中,有若干条记录,记录的数量与记录的存储格式和页的大小相关,

而第1层的这些节点属于中间节点,他们在迭代生成的过程是目录页,用来存储的是关于真正记录的路径信息,举个例子来说就是,记录每一个叶子节点所在记录的页码

而顶层节点也就是根节点了,它用来存储这些目录页的寻址信息,便于快速得到N叉树的搜索路径

同时,同层的节点之间还有指针指向,便于进行二分查找

3.6 B+树与B树

B树也称作为B-树,其全称是多路平衡查找树,B+树是B树的一种变体

B树和B+树有什么异同?为什么要选择用B+树?

• B树的节点同时存放值和键,而B+树只有叶子节点存放key和data
• B树的叶子节点都是独立的,B+树的叶子节点之间具有双向指针,形成了双向链表
• B树的检索过程相当于对范围内的每个节点的关键字做一个二分查找,其查找效率是不稳定的,不能做出稳定的查找复杂度估算,而B+树由于值都在叶子节点中存储,因此每个值的查找复杂度都是相同的,具有稳定的查找复杂度估算过程。

之所以选择用B+树

首先第一个是为了提高查询速度,减少I/O次数,无论是使用普通的二叉搜索树,还是哈希表,都将会导致大量的I/O操作,但是使用B+树,在树的高度只有4层的情况下就可以存储上亿条数据,也就是说在亿级的数量面前,最多只需要执行4次I/O就可以检索到数据,而对于平衡二叉树而言,20的树高才能存储到百万级的数据,开销非常大

第二个是为了便于执行范围查询,由于存储值的叶子节点之间是形成了双向链表的结构,因此相对于哈希表来说,范围查询能够更加高效

第三个是能够提供稳定的查询复杂度和查询效率,这是因为B+树的值都是存储到叶子节点中的,因此在这样的情况下,查询的路径和I/O次数都很稳定

第四个是改进了B树中不便于执行范围查询的缺点

MyISAM引擎中,B+ Tree叶子节点的data域存储的是数据记录的地址,在按照B+树的算法执行后,搜索到叶子节点的值,然后通过数据记录的地址进行寻址取值,这称为非聚簇索引

InnoDB引擎中,索引即数据,数据即索引,主键即主索引,当不存在主键的时候,会先通过unique+notnull属性的键进行替代,如果都没有,就会隐式产生一个索引6Byte的自增主键

其余索引均为辅助索引,需要回表

4. InnoDB的索引模型详解

在InnoDB中表都是根据主键顺序以索引的形式存放的,这种存储方式称为索引组织表,表中的每一个索引都会产生和维护一棵B+树。

create table T(
    id int primary key,
    k int not null,
    name varchar(16),
    index(k)#声明一个索引
)engine = InnoDB;

insert into t(id,k) values(100,1),(200,2),(300,3),(500,5),(600,6);

4.1 聚簇索引

索引按照物理实现方式,可以分为两种:聚簇(聚集)和非聚簇(非聚集)索引,通常也将非聚集索引称为二级索引或者辅助索引

聚簇索引并不是一种单独的索引类型,而是一种数据存储方式,所有的记录都存储在了叶子节点上与底层实现数据结构有关,在它的存储中,可以看到其非叶子节点的部分是存储目录页的,而目录页并不是数据,但是却以数据的方式存储在了这种数据结构上,因此说索引即数据,数据即索引

聚簇:指的就是数据行和相邻的键值聚簇的存储在一起

使用记录主键值的大小和进行记录和页的排序:

• 页内的记录是按照主键的大小顺序排成一个单向链表
• 各个存放用户记录的页也是根据页中的用户记录的主键大小顺序排成一个双向链表
• 存放目录项记录的页分为不同的层次,在同一层次中的页也是根据目录项记录的主键大小顺序排成一个双向链表

B+树的叶子节点存储的是完整的用户记录

使用聚簇索引需要注意的问题

• 插入速度严重依赖于插入的顺序,按照主键的顺序进行插入是最快的方式,否则将会出现严重的页分裂,严重影响性能,也就是说,当涉及到乱序插入的时候,就会导致内部数据存储顺序的调整
• 更新主键的代价很高,将会导致被更新的行移动
• 二级索引需要两次索引查找,第一次查找,首先查找到二级索引所对应记录的主键值,然后回表,回表指的是拿到主键值后,再去查找聚簇索引所在的树,因此尽量通过主键进行查询

在Mysql中,InnoDB是支持聚簇索引的,而MyISAM是不支持聚簇索引的

由于物理存储排序方式只能有一种,所以每个MYSQL的表中只能有一个聚簇索引,一般情况下就是主键

如果没有定义主键,InnoDB就会选择非空的unique索引进行代替,如果都没有,那么就会隐式地定义一个主键作为聚簇索引

为了比较充分的利用聚簇索引的聚簇特性,所以InnoDB表的主键列尽量使用有序的顺序id,而不建议使用无序的id,比如uuid等,这些主键无法保证数据的顺序增长

4.2 非聚簇索引(辅助索引)

如果当一个非主键列经常作为查询条件的时候,就可以考虑为这个列加上索引,但是依然是存在回表的问题的

但是这种方式比从头到尾遍历这棵B+树的效率高得多

回表的问题

在学回表的时候,通常会有这样的疑问,为什么不能够在非聚簇索引的叶子节点存储一个完整的记录呢?

原因是在实际开发中,非聚簇索引一旦多了起来,那么就会造成重复存储,将会导致大量的空间浪费

面试题:聚簇索引和非聚簇索引有什么区别?

• 聚簇索引的叶子节点就是数据记录,非聚簇索引的叶子节点存储的是数据的位置,非聚簇索引不会影响数据表的物理存储顺序
• 一个表只能有一个聚簇索引,只能以一种排序存储的方式,但是可以有多个聚簇索引辅助数据的搜索,也就是多个索引目录提供数据的检索
• 使用聚簇索引的时候,数据的查询效率高,但是如果对数据进行插入、删除、更新等操作,效率会比非聚簇索引低

唯一索引(Unique Key):唯一索引也是一种约束,唯一索引的属性列不能出现重复值,但是允许数据为null,一张表允许创建多个唯一索引,建立唯一索引的目的大部分时候都是为了该属性列的值的唯一性,而不是为了查询的效率

普通索引(Index):普通索引的唯一作用就是为了快速查询数据,一张表可以创建多个普通索引,并且允许数据重复和null

前缀索引(Prefix):前缀索引只适用于字符串类型的数据,前缀索引是对文本前几个字符创建索引,相比普通索引建立的数据更小,因为只取前几个字符

全文索引(Full Text):全文索引是为了检索大文本数据中的关键信息。

聚簇索引的优点

• 查询速度非常快,相比于非聚簇索引,聚簇索引少了一次回表的操作
• 对排序查找和范围查找优化:对于主键的排序查找和范围查找非常快

缺点

• 对有序性数据的依赖性程度高,对于自增单调的数据插入和维护简单,而对于UUID等数据,会导致插入和查找的速度慢
• 更新的代价大,索引列的数据被修改的时候,可能引发页分裂,可能产生严重的性能问题

4.3 非聚簇索引一定回表查询吗?

这是不一定的,关键在于你查询的关键字是否包含在key中

比如说

select ID from T where k between 3 and 5

这时候只需要查询ID的值,而ID的值已经在k索引树上了,因此可以直接提供查询结果,不需要回表,在这个查询中,索引k已经覆盖了查询需求,称之为覆盖索引

覆盖索引能够显著减少树的搜索次数,显著提升查询性能,使用覆盖是一个常见的手段

4.4 联合索引

可以同时以多个列的大小作为排序规则,也就是同时为多个列建立索引,比如说想要让c2和c3的列大小进行排序,这个包含两层含义:

• 先把各个记录和页按照c2列进行排序
• 在记录的c2列相同的情况下,采用c3列进行排序

当存在联合索引的时候,这时候在目录项中就会进行额外存储,各条记录内节点内部的排序是这样的,先根据c2的大小进行排序,再根据c3的大小进行排序,假如都相同则是进行稳定排序,即先进入的先排序,后进入的后排序

而在叶子节点中,用户记录是由c2、c3、主键c1(便于回表)组成的

注意,c2和c3并不是分别建立索引

最左前缀匹配原则

当一个查询是低频的,但是我们又不希望创建一个索引导致巨大的维护成本,那么B+树可以利用最左前缀来定位记录

如图所示,假设我们执行

select name from T where name like "张%";

然后执行引擎会查询到ID3,然后依次向后遍历,直到不满足条件为止

对最左前缀原则的完整描述是:

在使用联合索引的时候,Mysql会根据联合索引中的字段顺序,从左到右依次到查询条件中去匹配,如果查询条件中存在于联合索引最左侧字段相匹配的字段,则就会使用该字段过滤掉一批数据,知道联合索引中全部字段匹配完成,或在执行过程中遇到范围查询,如>、<、between和以%开头的like查询等条件的时候,才会停止匹配

所以在使用联合索引的时候,要将区分度高的字段放在最左边,这样可以过滤掉更多的数据。

索引下推

如果现在有一个需求：检索出表中“名字第一个字是张，而且年龄是 10 岁的所有男孩”。那么，SQL 语句是这么写的：

select * from t where name like "张%" and age = 10 and is_simple = 1

按照最左前缀匹配原则,它将会找到第一个索引字段为张的所有记录,然后向后遍历

在Mysql5.6之后,引入了索引下推优化

可以在索引遍历的过程中,对索引中的字段先做判断,直接过滤掉不满足条件的记录,减少回表的次数

在Mysql5.6之前,没有索引下推的话是这样执行的:

没有索引下推的时候,这个过程尽管索引的存储格式中存储了age的值,但是InnoDB却不会去查看age的值,而是根据name的第一个字段"张"取出来一条条去回表判断,实际上是不必要的。

而引入了索引下推后:

在这个过程中,由于age也是判断条件,因此会将索引中的值与条件进行判断,避免不必要的回表

5. InnoDB的B+树索引的注意事项

5.1 根页面位置万年不动

所谓根页面位置万年不动,指的是这个数据页永远都是作为根页面的,下面简单讲述一下:

我们假设一个页面可以存储2条用户记录(不包括最大记录和最小记录)

初始情况下:

Page root = new Page();
root.insert(page1);
root.insert(paeg2);

当插入第三条数据的时候

root.insert(page3);

这时候内部会触发复制扩容机制

Page levelPage = new Page(root);
root.clear();
root.insert(folder1);
root.insert(folder2);

上述代码的意思是,当根节点需要扩容的时候,它会将它的上一层节点进行复制,然后清空本身的数据,然后存储目录项到本身。

• 每当为某个表创建一个B+树的索引的时候聚簇索引不是人为创建的,而是默认就有
• 随后向表中插入用户记录的时候,先把用户保存到此根节点中
• 当根节点中的可用空间用完的时候,此时根节点会将所有记录复制到一个新分配的页,然后对这个页执行页分裂操作,然后根据主键的大小,创建目录项,并且存储到根节点上,根节点就这样升级为存储目录项记录的页

为什么要这样设计根节点呢?

这是因为通过根节点能够访问整颗树,因此这样设计根节点,就可以非常方便的使用根节点,而且是在客户端无感知的情况下进行扩容

5.2 内节点中目录项记录的唯一性

为了让新插入记录能够找到自己在哪个页中,需要保证B+树同一层内节点的目录项除页号这个字段以外是唯一的,所以对于二级索引的内节点的目录项记录的内容实际上是由三个部分组成的

• 索引列的值
• 主键值
• 页号

CREATE TABLE `geek` (
	`a` int(11) NOT NULL, 
    `b` int(11) NOT NULL, 
    `c` int(11) NOT NULL, 
    `d` int(11) NOT NULL, 
    PRIMARY KEY (`a`,`b`), 
    KEY `c` (`c`), 
    KEY `ca` (`c`,`a`), 
    KEY `cb` (`c`,`b`)
) ENGINE=InnoDB;

问:上述有索引重复吗?

回答:有,由于内节点中目录项记录的唯一性,因此二级索引通常在尾部还会加上主键值作为区分,因此:

①索引c->cab

②索引ca->cab

③索引cb->cba

其中①和②所产生的索引数据组成是相同的,然后根据业务:

select * from geek where c=N order by a limit 1;
select * from geek where c=N order by b limit 1;

在这两个语句中,首先我们知道ab是主键索引,注意顺序,排序的优先性是a先排序,因此a是有序的,因此第一个语句的order by a直接依赖于主键索引即可

而第二个语句必须依赖于order by b,也就是给b建个索引,那么cb就是必须的。

5.3 一个页面中至少要存储2条记录

InnoDB的一个数据页至少可以存放两条记录

6. 索引的维护总结

B+树为了维护索引的有序性,在插入新值的时候,需要做必要的维护,维护分为两种情况:

• 页空间还能容纳下记录,不需要申请新的页,需要挪动后面的数据,这时候就会导致数据的大量移动
• 页空间无法容纳下记录,需要申请新的页,并且需要挪动新的数据页,这个过程就叫做页分裂

这会导致性能的下降,还会导致数据页的利用率下降

原本放在一个页中的数据,现在分到两个页中,整体空间利用率降低为50%

相对的,还有合并这一说,由于数据记录是可以被删除的,当相邻的两个页删除了数据,会将数据页做一个合并的处理。这个就相当于os里面的整理内存碎片的过程

要求建表语句中一定要有自增主键,哪些场景下适用?

自增主键的原理是取当前ID的最大值+1作为下一条记录的ID值

每次插入一条新的记录,都是追加的操作,都不涉及到挪动其他记录,那么就不设计到页分裂了。

有业务逻辑的字段作为主键,往往不容易保证有序插入,这样的写成本是比较高的,因为可能会导致叶子节点的分裂,除了考虑性能外,如果表中确实有唯一字段,那么是否应该用此字段作为主键呢?

主键长度越小,普通索引节点就越小,普通索引所占的空间就越小

当只有一个索引并且该索引就是唯一索引,就可以使用业务主键了

alter table T drop index k;
alter table T add index(k);

alter table T drop primary key;
alter table T add primary key(id);

以上这两个语句组都可以重建一个索引,但是第二种方式是不好的,因为当你删除主键的时候,会导致一棵树的重建,同时还会导致二级索引失效,这是十分危险

7. 使用索引的注意点

7.1 选择合适的字段作为索引

• 不为NULL的字段:索引字段的数据的域尽量不要存在null,对于数据为null的字段,数据库比较难以优化,如果字段被频繁查询,但是右避免不了为null,可以使用1 true false这样清晰的字符进行替代
• 被频繁查询的字段 ：我们创建索引的字段应该是查询操作非常频繁的字段。
• 被作为条件查询的字段 ：被作为 WHERE 条件查询的字段，应该被考虑建立索引。
• 频繁需要排序的字段 ：索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。
• 被经常频繁用于连接的字段 ：经常用于连接的字段可能是一些外键列，对于外键列并不一定要建立外键，只是说该列涉及到表与表的关系。对于频繁被连接查询的字段，可以考虑建立索引，提高多表连接查询的效率。

7.2 被频繁更新的字段要慎重建立索引

虽然索引能带来查询上的效率，但是维护索引的成本也是不小的。 如果一个字段不被经常查询，反而被经常修改，那么就更不应该在这种字段上建立索引了。

7.3 尽可能考虑联合索引而不是单索引

因为索引是需要占用磁盘空间的，可以简单理解为每个索引都对应着一颗 B+树。如果一个表的字段过多，索引过多，那么当这个表的数据达到一个体量后，索引占用的空间也是很多的，且修改索引时，耗费的时间也是较多的。如果是联合索引，多个字段在一个索引上，那么将会节约很大磁盘空间，且修改数据的操作效率也会提升。

7.4 使用前缀索引替代普通索引

前缀索引仅限于字符串类型，较普通索引会占用更小的空间，所以可以考虑使用前缀索引替代普通索引。

7.5 避免索引失效

使用 SELECT * 进行查询,查询全部的字段，有一些字段可能没有建立索引，导致需要去表中查询，所以会导致索引失效;

创建了组合索引，但查询条件未准守最左匹配原则,指的是查询顺序没有按照索引的顺序;

在索引列上进行计算、函数、类型转换等操作;

以 % 开头的 LIKE 查询比如 like '%abc';;

查询条件中使用 or，且 or 的前后条件中有一个列没有索引，涉及的索引都不会被使用到;

发生隐式转换open in new window

7.6 删除长期不使用的索引

删除长期未使用的索引，不用的索引的存在会造成不必要的性能损耗 MySQL 5.7 可以通过查询 sys 库的 schema_unused_indexes 视图来查询哪些索引从未被使用

7.7 避免创建冗余的索引

冗余索引指的是索引的功能相同，能够命中索引(a, b)就肯定能命中索引(a) ，那么索引(a)就是冗余索引。如（name,city ）和（name ）这两个索引就是冗余索引，能够命中前者的查询肯定是能够命中后者的 在大多数情况下，都应该尽量扩展已有的索引而不是创建新索引。

8. MyISAM索引的方案

B树的索引适用的存储引擎有:

• MyISAM
• InnoDB
• Memory

即使多个索引支持同一种类型的索引,但是他们的实现原理也是不同的,InnoDB和MyISAM默认的索引是Btree而Memory默认的索引是Hash索引

MyISAM使用的是B+树作为索引结构,叶子节点的data域存放的是数据记录的地址

8.1 MyISAM的索引原理

MyISAM采用的数据结构虽然是B+树,但是其存储结构不是索引即数据的形式,而是在叶子节点中存储数据记录的地址,然后再通过数据记录的地址去找这条记录所对应的数据

如上图所示,MyISAM是将索引和数据分开存储存储的

• 将表中的记录按照记录的插入顺序单独存储在一个文件中,这个文件就是数据文件,这个文件并不划分为若干个数据页,有多少个记录就往这个文件中塞多少记录就可以了,由于在插入数据的时候没有刻意按照主键的大小进行排序,所以不能通过二分法进行查找
• MyISAM会单独为表的主键创建一个索引,只不过会在索引的叶子节点中存储的不是完整的记录,而是主键+数据记录地址的组合