乐趣区

关于java:数据库索引原理及优化

jiezi

数据库索引原理及优化

一、摘要

本文以 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 关键字的起因
退出移动版