小工具      在线工具  汉语词典  dos游戏  css  js  c++  java

数据库索引原理及优化

Java 额外说明

收录于:52天前

本文内容主要来源于网络主流文章。仅根据个人理解稍加整合,最后附有参考链接。

一、摘要
本文以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;
}


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);
}


2.2.3 二叉排序树查找
二叉排序树的特点是:

若它的左子树不空,则左子树上所有结点的值均小于它的根结点的值;
若它的右子树不空,则右子树上所有结点的值均大于它的根结点的值;
它的左、右子树也分别为二叉排序树。
搜索的原理:

若b是空树,则搜索失败,否则:
若x等于b的根节点的数据域之值,则查找成功;否则:
若x小于b的根节点的数据域之值,则搜索左子树;否则:
查找右子树。
数据结构:二叉排序树
时间复杂度: O(log2N)

2.2.4 哈希散列法(哈希表)
其原理是首先根据key值和哈希函数创建一个哈希表(散列表),燃耗根据键值,通过散列函数,定位数据元素位置。

数据结构:哈希表
时间复杂度:几乎是O(1),取决于产生冲突的多少。

2.2.5 分块查找
分块查找又称索引顺序查找,它是顺序查找的一种改进方法。其算法思想是将n个数据元素”按块有序”划分为m块(m ≤ n)。每一块中的结点不必有序,但块与块之间必须”按块有序”;即第1块中任一元素的关键字都必须小于第2块中任一元素的关键字;而第2块中任一元素又都必须小于第3块中的任一元素,依次类推。
  
算法流程:

先选取各块中的最大关键字构成一个索引表;
查找分两个部分:先对索引表进行二分查找或顺序查找,以确定待查记录在哪一块中;然后,在已确定的块中用顺序法进行查找。
这种搜索算法每一次比较都使搜索范围缩小一半。它们的查询速度就有了很大的提升,复杂度为。如果稍微分析一下会发现,每种查找算法都只能应用于特定的数据结构之上,例如二分查找要求被检索数据有序,而二叉树查找只能应用于二叉查找树上,但是数据本身的组织结构不可能完全满足各种数据结构(例如,理论上不可能同时将两列都按顺序进行组织),所以,在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法。这种数据结构,就是索引。

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. 所有的叶子节点必须在同一层次,也就是它们具有相同的深度;


由于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);


关于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,再取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 效率。预读长度一般为页的整数倍。页是计算机管理的内存的逻辑块。硬件和操作系统通常将主存和磁盘存储区域划分为连续的相同大小的块。每个存储块称为页(在许多操作系统中,页大小通常为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相比),磁盘块3从加载通过磁盘块1的P2指针的磁盘地址到内存,第二次IO发生,29处26和30,磁盘块3的P2指针被锁定,磁盘块8通过磁盘块加载到内存中指针。第三次IO发生。同时在内存中进行二分查找,找到29,查询结束。总共三个IO。真实情况是3层b+树可以表示数百万数据。如果百万级数据搜索只需要3次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组织的索引结构,这棵树的叶子节点数据域保存了完整的数据记录。该索引的键是数据表的主键,因此InnoDB表数据文件本身就是主索引。

这里写图片描述
上图是InnoDB主索引(同时也是数据文件)的示意图,可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集,所以InnoDB要求表必须有主键(MyISAM可以没有),如果没有显式指定,则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键,如果不存在这种列,则MySQL自动为InnoDB表生成一个隐含字段作为主键,这个字段长度为6个字节,类型为长整形。

与MyISAM索引的第二个区别在于,InnoDB的辅助索引数据字段存储的是对应记录主键的值,而不是地址。换句话说,InnoDB中的所有二级索引都引用主键作为数据字段。例如,下图显示了在 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;

上面三个查询按照 (a ), (a,b ),(a,b,c )的顺序都可以利用到索引,这就是最左前缀匹配。

如果查询语句是:

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

如果查询语句是:

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

如果用到了最左前缀,但是顺序颠倒会用到索引码?

例如:

select * from table where b=2 and a=1;
select * from table where b=2 and a=1 and c=3;
1
2
如果用到了最左前缀而只是颠倒了顺序,也是可以用到索引的,因为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中使用前缀索引,也不能用作覆盖索引。

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关键字的原因

上文转自:https://blog.csdn.net/suifeng3051/article/details/52669644
参考文章:

http://blog.csdn.net/suifeng3051/article/details/49530299?locationNum=1
http://tech.meituan.com/mysql-index.html
https://yq.aliyun.com/articles/39841
http://blog.csdn.net/lovelion/article/details/8462814

. . .

相关推荐

额外说明

springboot实践----拦截器配置

以前使用SSM框架开发的时候,总会配置相对应的请求实现token的校验,当项目换成Springboot后,原来的xml配置方式显得过于臃肿(不好看,也不好维护),所以可以直接进行配置类的编写实现访问拦截过滤。 一、springboot 2.0  以前 为

额外说明

Leetcode练习6(之字形变换)

题目:比如输入字符串为 “LEETCODEISHIRING” 行数为 3 时,排列如下: L C I RE T O E S I I GE D H N之后,你的输出需要从左往右逐行读取,产生出一个新的字符串。 题解(1):最容易想到的做法就是找规律,找z字

额外说明

14、Shell脚本条件判断、函数和循环

一、shell脚本条件判断 shell脚本支持条件判断,虽然可以通过&&和||来实现简单的条件判断,但是稍微复杂一点的场景就不适合了。shell脚本提供了if then条件判断语句,写法 if 条件判断 ; then //判断成立要做的事情 fi

额外说明

Unity 热更新技术 | (二) AssetBundle - 完整系列教程学习

- 博客主页:https://xiaoy.blog.csdn.net - 本文由 呆呆敲代码的小Y 原创,首发于 CSDN- - 学习专栏推荐:Unity系统学习专栏 - 游戏制作专栏推荐:游戏制作 -Unity实战100例专栏推荐:Unity 实战10

额外说明

安装和配置mongobd环境变量

安装和配置mongobd环境变量,超级详细,适合新手老手安装,看步骤 第一步先从官网下载好安装包,并双击打开它 第二步 第三步 第四步 第五步 第六步 第七步 第八步 第八步 安装完成,下面配置环境变量 第一步 第二步 打开高级系统设置 第三步 点击环境

额外说明

mysql报错:Data source rejected establishment of connection, message from server: \“Too many connectio

最近在做压力测试嘛,需要逐步增加用户量做验证,每个用户单独创建数据库进行连接,就要不断去创建数据库,这个报错也很容易理解,mysql连接数不够用了 目录 报错 原因 解决 查看mysql版本 查看当前mysql最大连接数限制 确定连接数不够原因 修改最大

额外说明

TOGAF 架构内容框架

一、概述 在执行ADM的过程中,会产生很多输出,比如流程图、架构需求、项目计划、项目一致性评估等等。为了能以一种一致的、结构化的方式来对照和展现这些主要的工作产品,用一个架构内容框架 (Architecture Content Framework )来放

额外说明

Linux命令200例:man用于显示和阅读关于Linux内置命令的使用说明

-作者简介,黑夜开发者,CSDN领军人物,全栈领域优质创作者✌。CSDN专家博主,阿里云社区专家博主,2023年6月csdn上海赛道top4。 -数年电商行业从业经验,历任核心研发工程师,项目技术负责人。 -本文已收录于专栏:Linux命令大全。 -本专

额外说明

数字思维:改变世界的4个方程

在17世纪之前,与电有关的现象(例如,磁性、闪电和会放电的鱼),一直是科学奇闻。18世纪,本杰明·富兰克林首次对电进行了广泛和系统的研究,尽管在他之前也有其他科学家研究过电现象。据说,在1752年进行的一项实验中,富兰克林利用风筝从暴风雨中捕捉到了电能。

额外说明

聊天ai机器人_适用于您网站的14种最佳AI聊天机器人软件(已比较)

聊天机器人 您是否正在为您的网站寻找最佳的聊天机器人软件? 您是否正在为您的网站寻找最佳的聊天机器人软件? 聊天机器人可让您通过自动回答常见客户问题来腾出时间。它们还可以用来产生潜在客户、改善用户体验并增加销量。 聊天机器人可让您通过自动回答常见客户问题

ads via 小工具