数据库索引原理及优化

一、摘要

本文以MySQL数据库为钻研对象，探讨与数据库索引相干的一些话题。特地须要阐明的是，MySQL反对诸多存储引擎，而各种存储引擎对索引的反对也各不相同，因而MySQL数据库反对多种索引类型，如BTree索引，哈希索引，全文索引等等。为了防止凌乱，本文将只关注于BTree索引，因为这是平时应用MySQL时次要打交道的索引，至于哈希索引和全文索引本文暂不探讨。

二、常见的查问算法及数据结构

为什么这里要讲查问算法和数据结构呢？因为之所以要建设索引，其实就是为了构建一种数据结构，能够在下面利用一种高效的查问算法，最终进步数据的查问速度。

2.1 索引的实质

MySQL官网对索引的定义为：索引（Index）是帮忙MySQL高效获取数据的数据结构。提取句子骨干，就能够失去索引的实质：索引是数据结构。

2.2 常见的查问算法

咱们晓得，数据库查问是数据库的最次要性能之一。咱们都心愿查问数据的速度能尽可能的快，因而数据库系统的设计者会从查问算法的角度进行优化。那么有哪些查问算法能够使查问速度变得更快呢？

2.2.1 程序查找（linear search ）

最根本的查问算法当然是程序查找（linear search），也就是比照每个元素的办法，不过这种算法在数据量很大时效率是极低的。 数据结构：有序或无序队列 复杂度：O(n) 实例代码：

//程序查找
int SequenceSearch(int a[], int value, int n)
{
int i;
for(i=0; i<n; i++)
if(a[i]==value)
return i;
return -1;
}
123456789

2.2.2 二分查找（binary search）

比程序查找更快的查询方法应该就是二分查找了，二分查找的原理是查找过程从数组的两头元素开始，如果两头元素正好是要查找的元素，则搜素过程完结；如果某一特定元素大于或者小于两头元素，则在数组大于或小于两头元素的那一半中查找，而且跟开始一样从两头元素开始比拟。如果在某一步骤数组为空，则代表找不到。 数据结构：有序数组 复杂度：O(logn) 实例代码：

//二分查找，递归版本
int BinarySearch2(int a[], int value, int low, int high)
{
int mid = low+(high-low)/2;
if(a[mid]==value)
return mid;
if(a[mid]>value)
return BinarySearch2(a, value, low, mid-1);
if(a[mid]<value)
return BinarySearch2(a, value, mid+1, high);
}
1234567891011

2.2.3 二叉排序树查找

二叉排序树的特点是：

  1. 若它的左子树不空，则左子树上所有结点的值均小于它的根结点的值；
  2. 若它的右子树不空，则右子树上所有结点的值均大于它的根结点的值；
  3. 它的左、右子树也别离为二叉排序树。

搜寻的原理：

  1. 若b是空树，则搜寻失败，否则：
  2. 若x等于b的根节点的数据域之值，则查找胜利；否则：
  3. 若x小于b的根节点的数据域之值，则搜寻左子树；否则：
  4. 查找右子树。

数据结构：二叉排序树 工夫复杂度： O(log2N)

2.2.4 哈希散列法(哈希表)

其原理是首先依据key值和哈希函数创立一个哈希表（散列表），燃耗依据键值，通过散列函数，定位数据元素地位。

数据结构：哈希表 工夫复杂度：简直是O(1)，取决于产生抵触的多少。

2.2.5 分块查找

分块查找又称索引程序查找，它是程序查找的一种改良办法。其算法思维是将n个数据元素”按块有序”划分为m块（m ≤ n）。每一块中的结点不用有序，但块与块之间必须”按块有序”；即第1块中任一元素的关键字都必须小于第2块中任一元素的关键字；而第2块中任一元素又都必须小于第3块中的任一元素，顺次类推。 算法流程：

  1. 先选取各块中的最大关键字形成一个索引表；
  2. 查找分两个局部：先对索引表进行二分查找或程序查找，以确定待查记录在哪一块中；而后，在已确定的块中用程序法进行查找。

这种搜索算法每一次比拟都使搜寻范畴放大一半。它们的查问速度就有了很大的晋升，复杂度为。如果略微剖析一下会发现，每种查找算法都只能利用于特定的数据结构之上，例如二分查找要求被检索数据有序，而二叉树查找只能利用于二叉查找树上，然而数据自身的组织构造不可能齐全满足各种数据结构（例如，实践上不可能同时将两列都按程序进行组织），所以，在数据之外，数据库系统还保护着满足特定查找算法的数据结构，这些数据结构以某种形式援用（指向）数据，这样就能够在这些数据结构上实现高级查找算法。这种数据结构，就是索引。

2.3 均衡多路搜寻树B树（B-tree）

下面讲到了二叉树，它的搜寻工夫复杂度为O(log2N)，所以它的搜寻效率和树的深度无关，如果要进步查问速度，那么就要升高树的深度。要升高树的深度，很天然的办法就是采纳多叉树，再联合均衡二叉树的思维，咱们能够构建一个均衡多叉树结构，而后就能够在下面构建均衡多路查找算法，进步大数据量下的搜寻效率。

2.3.1 B Tree

B树（Balance Tree）又叫做B- 树（其实B-是由B-tree翻译过去，所以B-树和B树是一个概念） ，它就是一种均衡多路查找树。下图就是一个典型的B树：

从上图中咱们能够大抵看到B树的一些特点，为了更好的形容B树，咱们定义记录为一个二元组[key, data]，key为记录的键值，data示意其它数据（上图中只有key，没有画出data数据 ）。上面是对B树的一个具体定义：

  1. 有一个根节点，根节点只有一个记录和两个孩子或者根节点为空；
  2. 每个节点记录中的key和指针互相距离，指针指向孩子节点；
  3. d是示意树的宽度，除叶子节点之外，其它每个节点有[d/2,d-1]条记录，并且些记录中的key都是从左到右按大小排列的，有[d/2+1,d]个孩子；
  4. 在一个节点中，第n个子树中的所有key，小于这个节点中第n个key，大于第n-1个key，比方上图中B节点的第2个子节点E中的所有key都小于B中的第2个key 9，大于第1个key 3;
  5. 所有的叶子节点必须在同一档次，也就是它们具备雷同的深度；

12345

因为B-Tree的个性，在B-Tree中按key检索数据的算法十分直观：首先从根节点进行二分查找，如果找到则返回对应节点的data，否则对相应区间的指针指向的节点递归进行查找，直到找到节点或找到null指针，前者查找胜利，后者查找失败。B-Tree上查找算法的伪代码如下：

BTree_Search(node, key) {
if(node == null) return null;
foreach(node.key){
if(node.key[i] == key) return node.data[i];
if(node.key[i] > key) return BTree_Search(point[i]->node);
}
return BTree_Search(point[i+1]->node);
}
data = BTree_Search(root, my_key);
123456789

对于B-Tree有一系列乏味的性质，例如一个度为d的B-Tree，设其索引N个key，则其树高h的下限为logd((N+1)/2)，检索一个key，其查找节点个数的渐进复杂度为O(logdN)。从这点能够看出，B-Tree是一个十分有效率的索引数据结构。

另外，因为插入删除新的数据记录会毁坏B-Tree的性质，因而在插入删除时，须要对树进行一个决裂、合并、转移等操作以放弃B-Tree性质，本文不打算残缺探讨B-Tree这些内容，因为曾经有许多材料具体阐明了B-Tree的数学性质及插入删除算法，有趣味的敌人能够查阅其它文献进行具体钻研。

2.3.2 B+Tree

其实B-Tree有许多变种，其中最常见的是B+Tree，比方MySQL就广泛应用B+Tree实现其索引构造。B-Tree相比，B+Tree有以下不同点：

  • 每个节点的指针下限为2d而不是2d+1；
  • 内节点不存储data，只存储key；
  • 叶子节点不存储指针；

上面是一个简略的B+Tree示意。

因为并不是所有节点都具备雷同的域，因而B+Tree中叶节点和内节点个别大小不同。这点与B-Tree不同，尽管B-Tree中不同节点寄存的key和指针可能数量不统一，然而每个节点的域和下限是统一的，所以在实现中B-Tree往往对每个节点申请等同大小的空间。一般来说，B+Tree比B-Tree更适宜实现外存储索引构造，具体起因与外存储器原理及计算机存取原理无关，将在上面探讨。

2.3.3 带有程序拜访指针的B+Tree

个别在数据库系统或文件系统中应用的B+Tree构造都在经典B+Tree的根底上进行了优化，减少了程序拜访指针。

如图所示，在B+Tree的每个叶子节点减少一个指向相邻叶子节点的指针，就造成了带有程序拜访指针的B+Tree。做这个优化的目标是为了进步区间拜访的性能，例如图4中如果要查问key为从18到49的所有数据记录，当找到18后，只需顺着节点和指针程序遍历就能够一次性拜访到所有数据节点，极大提到了区间查问效率。

这一节对B-Tree和B+Tree进行了一个简略的介绍，下一节联合存储器存取原理介绍为什么目前B+Tree是数据库系统实现索引的首选数据结构。

三、索引数据结构设相干的计算机原理

上文说过，二叉树、红黑树等数据结构也能够用来实现索引，然而文件系统及数据库系统广泛采纳B-/+Tree作为索引构造，这一节将联合计算机组成原理相干常识探讨B-/+Tree作为索引的实践根底。

3.1 两种类型的存储

在计算机系统中个别蕴含两种类型的存储，计算机主存（RAM）和内部存储器（如硬盘、CD、SSD等）。在设计索引算法和存储构造时，咱们必须要思考到这两种类型的存储特点。主存的读取速度快，绝对于主存，内部磁盘的数据读取速率要比主从慢好几个数量级，具体它们之间的差异前面会具体介绍。 下面讲的所有查问算法都是假如数据存储在计算机主存中的，计算机主存个别比拟小，理论数据库中数据都是存储到内部存储器的。

一般来说，索引自身也很大，不可能全副存储在内存中，因而索引往往以索引文件的模式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘I/O耗费，绝对于内存存取，I/O存取的耗费要高几个数量级，所以评估一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。换句话说，索引的构造组织要尽量减少查找过程中磁盘I/O的存取次数。上面具体介绍内存和磁盘存取原理，而后再联合这些原理剖析B-/+Tree作为索引的效率。

3.2 主存存取原理

目前计算机应用的主存根本都是随机读写存储器（RAM），古代RAM的构造和存取原理比较复杂，这里本文抛却具体差异，形象出一个非常简略的存取模型来阐明RAM的工作原理。

从形象角度看，主存是一系列的存储单元组成的矩阵，每个存储单元存储固定大小的数据。每个存储单元有惟一的地址，古代主存的编址规定比较复杂，这里将其简化成一个二维地址：通过一个行地址和一个列地址能够惟一定位到一个存储单元。上图展现了一个4 x 4的主存模型。

主存的存取过程如下：

当零碎须要读取主存时，则将地址信号放到地址总线上传给主存，主存读到地址信号后，解析信号并定位到指定存储单元，而后将此存储单元数据放到数据总线上，供其它部件读取。写主存的过程相似，零碎将要写入单元地址和数据别离放在地址总线和数据总线上，主存读取两个总线的内容，做相应的写操作。

这里能够看出，主存存取的工夫仅与存取次数呈线性关系，因为不存在机械操作，两次存取的数据的“间隔”不会对工夫有任何影响，例如，先取A0再取A1和先取A0再取D3的工夫耗费是一样的。

3.3 磁盘存取原理

上文说过，索引个别以文件模式存储在磁盘上，索引检索须要磁盘I/O操作。与主存不同，磁盘I/O存在机械运动消耗，因而磁盘I/O的工夫耗费是微小的。

磁盘读取数据靠的是机械运动，当须要从磁盘读取数据时，零碎会将数据逻辑地址传给磁盘，磁盘的控制电路依照寻址逻辑将逻辑地址翻译成物理地址，即确定要读的数据在哪个磁道，哪个扇区。为了读取这个扇区的数据，须要将磁头放到这个扇区上方，为了实现这一点，磁头须要挪动对准相应磁道，这个过程叫做寻道，所消耗工夫叫做寻道工夫，而后磁盘旋转将指标扇区旋转到磁头下，这个过程消耗的工夫叫做旋转工夫，最初便是对读取数据的传输。 所以每次读取数据破费的工夫能够分为寻道工夫、旋转提早、传输工夫三个局部。其中：

  • 寻道工夫是磁臂挪动到指定磁道所须要的工夫，支流磁盘个别在5ms以下。
  • 旋转提早就是咱们常常据说的磁盘转速，比方一个磁盘7200转，示意每分钟能转7200次，也就是说1秒钟能转120次，旋转提早就是1/120/2 = 4.17ms。
  • 传输工夫指的是从磁盘读出或将数据写入磁盘的工夫，个别在零点几毫秒，绝对于前两个工夫能够忽略不计。

那么拜访一次磁盘的工夫，即一次磁盘IO的工夫约等于5+4.17 = 9ms左右，听起来还挺不错的，但要晓得一台500 -MIPS的机器每秒能够执行5亿条指令，因为指令依附的是电的性质，换句话说执行一次IO的工夫能够执行40万条指令，数据库动辄十万百万乃至千万级数据，每次9毫秒的工夫，显然是个劫难。

3.4 局部性原理与磁盘预读

因为存储介质的个性，磁盘自身存取就比主存慢很多，再加上机械运动消耗，磁盘的存取速度往往是主存的几百分分之一，因而为了提高效率，要尽量减少磁盘I/O。为了达到这个目标，磁盘往往不是严格按需读取，而是每次都会预读，即便只须要一个字节，磁盘也会从这个地位开始，程序向后读取肯定长度的数据放入内存。这样做的理论依据是计算机科学中驰名的局部性原理：当一个数据被用到时，其左近的数据也通常会马上被应用。程序运行期间所须要的数据通常比拟集中。

因为磁盘程序读取的效率很高（不须要寻道工夫，只需很少的旋转工夫），因而对于具备局部性的程序来说，预读能够进步I/O效率。预读的长度个别为页（page）的整倍数。页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储区宰割为间断的大小相等的块，每个存储块称为一页（在许多操作系统中，页得大小通常为4k），主存和磁盘以页为单位替换数据。当程序要读取的数据不在主存中时，会触发一个缺页异样，此时零碎会向磁盘收回读盘信号，磁盘会找到数据的起始地位并向后间断读取一页或几页载入内存中，而后异样返回，程序持续运行。

四、数据库索引所采纳的数据结构B-/+Tree及其性能剖析

到这里终于能够剖析为何数据库索引采纳B-/+Tree存储构造了。上文说过数据库索引是存储到磁盘的而咱们又个别以应用磁盘I/O次数来评估索引构造的优劣。先从B-Tree剖析，依据B-Tree的定义，可知检索一次最多须要拜访h-1个节点（根节点常驻内存）。数据库系统的设计者奇妙利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只须要一次I/O就能够齐全载入。为了达到这个目标，在理论实现B-Tree还须要应用如下技巧：每次新建节点时，间接申请一个页的空间，这样就保障一个节点物理上也存储在一个页里，加之计算机存储调配都是按页对齐的，就实现了一个node只需一次I/O。

B-Tree中一次检索最多须要h-1次I/O（根节点常驻内存），渐进复杂度为O(h)=O(logdN)。个别理论利用中，出度d是十分大的数字，通常超过100，因而h十分小（通常不超过3）。

综上所述，如果咱们采纳B-Tree存储构造，搜寻时I/O次数个别不会超过3次，所以用B-Tree作为索引构造效率是十分高的。

4.1 B+树性能剖析

从下面介绍咱们晓得，B树的搜寻复杂度为O(h)=O(logdN)，所以树的出度d越大，深度h就越小，I/O的次数就越少。B+Tree恰好能够减少出度d的宽度，因为每个节点大小为一个页大小，所以出度的下限取决于节点内key和data的大小：

dmax=floor(pagesize/(keysize+datasize+pointsize))//floor示意向下取整
1

因为B+Tree内节点去掉了data域，因而能够领有更大的出度，从而领有更好的性能。

4.2 B+树查找过程

B-树和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，显然老本十分十分高。

这一章从实践角度探讨了与索引相干的数据结构与算法问题，下一章将探讨B+Tree是如何具体实现为MySQL中索引，同时将联合MyISAM和InnDB存储引擎介绍非汇集索引和汇集索引两种不同的索引实现模式。

五、MySQL索引实现

在MySQL中，索引属于存储引擎级别的概念，不同存储引擎对索引的实现形式是不同的，本文次要探讨MyISAM和InnoDB两个存储引擎的索引实现形式。

5.1 MyISAM索引实现

MyISAM引擎应用B+Tree作为索引构造，叶节点的data域寄存的是数据记录的地址。下图是MyISAM索引的原理图：

这里设表一共有三列，假如咱们以Col1为主键，则上图是一个MyISAM表的主索引（Primary key）示意。能够看出MyISAM的索引文件仅仅保留数据记录的地址。在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是惟一的，而辅助索引的key能够反复。如果咱们在Col2上建设一个辅助索引，则此索引的构造如下图所示：

同样也是一颗B+Tree，data域保留数据记录的地址。因而，MyISAM中索引检索的算法为首先依照B+Tree搜索算法搜寻索引，如果指定的Key存在，则取出其data域的值，而后以data域的值为地址，读取相应数据记录。 MyISAM的索引形式也叫做“非汇集”的，之所以这么称说是为了与InnoDB的汇集索引辨别。

5.2 InnoDB索引实现

尽管InnoDB也应用B+Tree作为索引构造，但具体实现形式却与MyISAM截然不同。

第一个重大区别是InnoDB的数据文件自身就是索引文件。从上文晓得，MyISAM索引文件和数据文件是拆散的，索引文件仅保留数据记录的地址。而在InnoDB中，表数据文件自身就是按B+Tree组织的一个索引构造，这棵树的叶节点data域保留了残缺的数据记录。这个索引的key是数据表的主键，因而InnoDB表数据文件自身就是主索引。

上图是InnoDB主索引（同时也是数据文件）的示意图，能够看到叶节点蕴含了残缺的数据记录。这种索引叫做汇集索引。因为InnoDB的数据文件自身要按主键汇集，所以InnoDB要求表必须有主键（MyISAM能够没有），如果没有显式指定，则MySQL零碎会主动抉择一个能够惟一标识数据记录的列作为主键，如果不存在这种列，则MySQL主动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都援用主键作为data域。例如，下图为定义在Col3上的一个辅助索引：

这里以英文字符的ASCII码作为比拟准则。汇集索引这种实现形式使得按主键的搜寻非常高效，然而辅助索引搜寻须要检索两遍索引：首先检索辅助索引取得主键，而后用主键到主索引中检索取得记录。

理解不同存储引擎的索引实现形式对于正确应用和优化索引都十分有帮忙，例如晓得了InnoDB的索引实现后，就很容易明确为什么不倡议应用过长的字段作为主键，因为所有辅助索引都援用主索引，过长的主索引会令辅助索引变得过大。再例如，用非枯燥的字段作为主键在InnoDB中不是个好主见，因为InnoDB数据文件自身是一颗B+Tree，非枯燥的主键会造成在插入新记录时数据文件为了维持B+Tree的个性而频繁的决裂调整，非常低效，而应用自增字段作为主键则是一个很好的抉择。

下一章将具体探讨这些与索引无关的优化策略。

六、索引应用策略及优化

MySQL的优化次要分为构造优化（Scheme optimization）和查问优化（Query optimization）。本章探讨的高性能索引策略次要属于构造优化领域。本章的内容齐全基于上文的实践根底，实际上一旦了解了索引背地的机制，那么抉择高性能的策略就变成了纯正的推理，并且能够了解这些策略背地的逻辑。

6.1 联结索引及最左前缀原理

联结索引（复合索引）

首先介绍一下联结索引。联结索引其实很简略，绝对于个别索引只有一个字段，联结索引能够为多个字段创立一个索引。它的原理也很简略，比方，咱们在（a,b,c）字段上创立一个联结索引，则索引记录会首先依照A字段排序，而后再依照B字段排序而后再是C字段，因而，联结索引的特点就是：

  • 第一个字段肯定是有序的
  • 当第一个字段值相等的时候，第二个字段又是有序的，比方下表中当A=2时所有B的值是有序排列的，顺次类推，当同一个B值得所有C字段是有序排列的

    | A | B | C | | 1 | 2 | 3 | | 1 | 4 | 2 | | 1 | 1 | 4 | | 2 | 3 | 5 | | 2 | 4 | 4 | | 2 | 4 | 6 | | 2 | 5 | 5 |

其实联结索引的查找就跟查字典是一样的，先依据第一个字母查，而后再依据第二个字母查，或者只依据第一个字母查，然而不能跳过第一个字母从第二个字母开始查。这就是所谓的最左前缀原理。

最左前缀原理

咱们再来具体介绍一下联结索引的查问。还是下面例子，咱们在（a,b,c）字段上建了一个联结索引，所以这个索引是先按a 再按b 再按c进行排列的，所以：

以下的查问形式都能够用到索引

select * from table where a=1；
select * from table where a=1 and b=2；
select * from table where a=1 and b=2 and c=3；
123

下面三个查问依照 （a ）, （a，b ）,（a，b，c ）的程序都能够利用到索引，这就是最左前缀匹配。

如果查问语句是：

select * from table where a=1 and c=3； 那么只会用到索引a。
1

如果查问语句是：

select * from table where b=2 and c=3； 因为没有用到最左前缀a，所以这个查问是用户到索引的。
1

如果用到了最左前缀，然而程序颠倒会用到索引码？

比方：

select * from table where b=2 and a=1；
select * from table where b=2 and a=1 and c=3；
12

如果用到了最左前缀而只是颠倒了程序，也是能够用到索引的，因为mysql查问优化器会判断纠正这条sql语句该以什么样的程序执行效率最高，最初才生成真正的执行打算。但咱们还是最好依照索引程序来查问，这样查问优化器就不必从新编译了。

前缀索引

除了联结索引之外，对mysql来说其实还有一种前缀索引。前缀索引就是用列的前缀代替整个列作为索引key，当前缀长度适合时，能够做到既使得前缀索引的选择性靠近全列索引，同时因为索引key变短而缩小了索引文件的大小和保护开销。

一般来说以下状况能够应用前缀索引：

  • 字符串列(varchar,char,text等)，须要进行全字段匹配或者前匹配。也就是=‘xxx’ 或者 like ‘xxx%’
  • 字符串自身可能比拟长，而且前几个字符就开始不雷同。比方咱们对中国人的姓名应用前缀索引就没啥意义，因为中国人名字都很短，另外对收件地址应用前缀索引也不是很实用，因为一方面收件地址个别都是以XX省结尾，也就是说前几个字符都是差不多的，而且收件地址进行检索个别都是like ’%xxx%’，不会用到前匹配。相同对外国人的姓名能够应用前缀索引，因为其字符较长，而且前几个字符的选择性比拟高。同样电子邮件也是一个能够应用前缀索引的字段。
  • 前一半字符的索引选择性就曾经靠近于全字段的索引选择性。如果整个字段的长度为20，索引选择性为0.9，而咱们对前10个字符建设前缀索引其选择性也只有0.5，那么咱们须要持续加大前缀字符的长度，然而这个时候前缀索引的劣势曾经不显著，没有太大的建前缀索引的必要了。

一些文章中也提到：

MySQL 前缀索引能无效减小索引文件的大小，进步索引的速度。然而前缀索引也有它的害处：MySQL 不能在 ORDER BY 或 GROUP BY 中应用前缀索引，也不能把它们用作笼罩索引(Covering Index)。

6.2 索引优化策略

  • 最左前缀匹配准则，下面讲到了
  • 主键外检肯定要建索引
  • 对 where,on,group by,order by 中呈现的列应用索引
  • 尽量抉择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，示意字段不反复的比例，比例越大咱们扫描的记录数越少，惟一键的区分度是1，而一些状态、性别字段可能在大数据背后区分度就是0
  • 对较小的数据列应用索引,这样会使索引文件更小,同时内存中也能够装载更多的索引键
  • 索引列不能参加计算，放弃列“洁净”，比方from_unixtime(create_time) = ’2014-05-29’就不能应用到索引，起因很简略，b+树中存的都是数据表中的字段值，但进行检索时，须要把所有元素都利用函数能力比拟，显然老本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);
  • 为较长的字符串应用前缀索引
  • 尽量的扩大索引，不要新建索引。比方表中曾经有a的索引，当初要加(a,b)的索引，那么只须要批改原来的索引即可
  • 不要过多创立索引, 衡量索引个数与DML之间关系，DML也就是插入、删除数据操作。这里须要衡量一个问题，建设索引的目标是为了进步查问效率的，但建设的索引过多，会影响插入、删除数据的速度，因为咱们批改的表数据，索引也须要进行调整重建
  • 对于like查问，”%”不要放在后面。 SELECT * FROMhoudunwangWHEREunameLIKE'后盾%' -- 走索引 SELECT * FROMhoudunwangWHEREunameLIKE "%后盾%" -- 不走索引
  • 查问where条件数据类型不匹配也无奈应用索引 字符串与数字比拟不应用索引; CREATE TABLEa(achar(10)); EXPLAIN SELECT * FROMaWHEREa="1" – 走索引 EXPLAIN SELECT * FROM a WHERE a=1 – 不走索引 正则表达式不应用索引,这应该很好了解,所以为什么在SQL中很难看到regexp关键字的起因