MySQL索引和SQL优化

一、索引

索引是一种为了高效查询数据的数据结构。

索引类型：主键索引、唯一索引、普通索引、全文索引、组合索引。

本文以MySQL数据库为研究对象，讨论与数据库索引相关的一些话题。特别需要说明的是，MySQL支持诸多存储引擎，而各种存储引擎对索引的支持也各不相同，因此MySQL数据库支持多种索引类型，如BTree索引，哈希索引，全文索引等等。为了避免混乱，本文将只关注于BTree索引，因为这是平常使用MySQL时主要打交道的索引，至于哈希索引和全文索引本文暂不讨论。

1、常见的查询算法和数据结构

1. 索引的本质

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构。

2. 常见的查询算法

我们知道，数据库查询是数据库的最主要功能之一。我们都希望查询数据的速度能尽可能的快，因此数据库系统的设计者会从查询算法的角度进行优化。那么有哪些查询算法可以使查询速度变得更快呢？

2.1 顺序查找（linear search ）

最基本的查询算法当然是顺序查找（linear search），也就是对比每个元素的方法，不过这种算法在数据量很大时效率是极低的。 
数据结构：有序或无序队列 
复杂度：O(n) 

2.2 二分查找（binary search）

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

2.3 二叉排序树查找

二叉排序树的特点是：

若它的左子树不空，则左子树上所有结点的值均小于它的根结点的值；

若它的右子树不空，则右子树上所有结点的值均大于它的根结点的值；

它的左、右子树也分别为二叉排序树。

搜索的原理：

若b是空树，则搜索失败，否则：

若x等于b的根节点的数据域之值，则查找成功；否则：

若x小于b的根节点的数据域之值，则搜索左子树；否则：

查找右子树。

数据结构：二叉排序树 
时间复杂度： O(logN)，即以2为底，N的对数

2.4 哈希散列法(哈希表)

其原理是首先根据key值和哈希函数创建一个哈希表（散列表），然后根据键值，通过散列函数，定位数据元素位置。

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

2.5 分块查找

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

先选取各块中的最大关键字构成一个索引表；

查找分两个部分：先对索引表进行二分查找或顺序查找，以确定待查记录在哪一块中；然后，在已确定的块中用顺序法进行查找。

这种搜索算法每一次比较都使搜索范围缩小一半。它们的查询速度就有了很大的提升，复杂度为(n/m*log2m)。

如果稍微分析一下会发现，每种查找算法都只能应用于特定的数据结构之上，例如二分查找要求被检索数据有序，而二叉树查找只能应用于二叉查找树上，但是数据本身的组织结构不可能完全满足各种数据结构（例如，理论上不可能同时将两列都按顺序进行组织），所以，在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。

3. 常见的数据结构

上面讲到了二叉树，它的搜索时间复杂度为O(logN)，所以它的搜索效率和树的深度有关，如果要提高查询速度，那么就要降低树的深度。要降低树的深度，很自然的方法就是采用多叉树，再结合平衡二叉树的思想，我们可以构建一个平衡多叉树结构，然后就可以在上面构建平衡多路查找算法，提高大数据量下的搜索效率。

常见的数据结构有：数组、链表、哈希表、BST（二叉查找树）、AVL（平衡二叉树）、B Tree（平衡多路搜索树）等等。

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的数学性质及插入删除算法，有兴趣的朋友可以查阅其它文献进行详细研究。

3.5 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更适合实现外存储索引结构，具体原因与外存储器原理及计算机存取原理有关，将在下面讨论。

3.6 带有顺序访问指针的B+Tree

一般在数据库系统或文件系统中使用的B+Tree结构都在经典B+Tree的基础上进行了优化，增加了顺序访问指针。 

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

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

2、索引数据结构和相关的计算机原理

上文说过，二叉树、红黑树等数据结构也可以用来实现索引，但是文件系统及数据库系统普遍采用B-/+Tree作为索引结构，这一节将结合计算机组成原理相关知识讨论B-/+Tree作为索引的理论基础。

1. 两种类型的存储

在计算机系统中一般包含两种类型的存储，计算机主存（RAM）和外部存储器（如硬盘、CD、SSD等）。在设计索引算法和存储结构时，我们必须要考虑到这两种类型的存储特点。主存的读取速度快，相对于主存，外部磁盘的数据读取速率要比主从慢好几个数量级，具体它们之间的差别后面会详细介绍。 上面讲的所有查询算法都是假设数据存储在计算机主存中的，计算机主存一般比较小，实际数据库中数据都是存储到外部存储器的。

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

2. 主存存取原理

目前计算机使用的主存基本都是随机读写存储器（RAM），现代RAM的结构和存取原理比较复杂，这里本文抛却具体差别，抽象出一个十分简单的存取模型来说明RAM的工作原理。

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

主存的存取过程如下：

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

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

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毫秒的时间，显然是个灾难。

4. 局部性原理与磁盘预读

由于存储介质的特性，磁盘本身存取就比主存慢很多，再加上机械运动耗费，磁盘的存取速度往往是主存的几十万分之一，因此为了提高效率，要尽量减少磁盘I/O。为了达到这个目的，磁盘往往不是严格按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存。这样做的理论依据是计算机科学中著名的局部性原理：当一个数据被用到时，其附近的数据也通常会马上被使用。程序运行期间所需要的数据通常比较集中。（上面是空间局部性原理，还有时间局部性原理：当一个数据被用到时，在很短的时间内很有可能会再用到该数据。）

由于磁盘顺序读取的效率很高（不需要寻道时间，只需很少的旋转时间），因此对于具有局部性的程序来说，预读可以提高I/O效率。预读的长度一般为页（page）的整倍数。页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页（在许多操作系统中，页的大小通常为4k），主存和磁盘以页为单位交换数据。当程序要读取的数据不在主存中时，会触发一个缺页异常，此时系统会向磁盘发出读盘信号，磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中，然后异常返回，程序继续运行。

对于大部分操作系统而言，一页的大小是4KB，磁盘预读的大小是16KB(读取一页的数据+预读三页的数据)。磁盘中一页的大小是4KB，而数据库系统中一页的大小是16KB。

3、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作为索引结构效率是非常高的。

1. B+树性能分析

从上面介绍我们知道，B树的搜索复杂度为O(h)=O(logdN)，所以树的出度d越大，深度h就越小，I/O的次数就越少。B+Tree恰恰可以增加出度d的宽度，因为每个节点大小为一个页大小，所以出度的上限取决于节点内key和data的大小：

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

由于B+Tree内节点去掉了data域，因此可以拥有更大的出度，从而拥有更好的性能。

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和InnoDB存储引擎介绍非聚集索引和聚集索引两种不同的索引实现形式。

4、MySQL索引实现

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

1. MyISAM索引实现

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址，根据地址再找到数据。MyISAM存储引擎的数据文件(以.myd结尾的文件)与索引文件(以.myi结尾的文件)是分两个文件存放的。下图是MyISAM索引的原理图：

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

同样也是一颗B+Tree，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。 
MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。

数据与索引在一起的叫聚簇索引，数据与索引不在一起的叫非聚簇索引。MyISAM的索引都是非聚簇索引，InnoDB的主键索引是聚簇索引，InnoDB的其他索引都是非聚簇索引。

2. InnoDB索引实现

虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。

第一个重大区别是InnoDB的数据文件本身就是索引文件。InnoDB存储引擎的数据文件和索引文件是放在同一个文件(以.ibd结尾的文件)中存储的。从上文知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存索引字段及其对应数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

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

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

这里以英文字符的ASCII码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录(这个操作叫做回表)。

了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。还有一个原因是每页中存储的索引个数就少了，树的深度自然就增加了，IO次数就会增加。再例如，用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

5、MySQL为什么选择B+树索引

前提知识：内存与磁盘文件之间是以页(dataPage，大小为4KB)为单位进行数据交互的，根据预读原理，操作系统每次从磁盘文件读取页的整数倍的数据到内存中，一般是16KB。数据库系统的设计者将一页的大小设计为16KB(InnoDB默认页的大小是16KB)。

MySQL使用B+树数据结构作为索引，目的是在尽可能减少IO次数的前提下查询更多的数据。由于索引数据是存放在磁盘文件中的，每次IO只能读取固定大小的数据到内存中，为了提高查询效率，只能减少IO次数。索引数据结构是以key和value的形式存储数据的，key是索引字段，value是索引字段对应的数据记录。常见的可以存储键值对的数据结构有哈希表、二叉树、平衡二叉树(AVL)、红黑树、B树、B+树等。哈希表数据结构存储数据时需要设计一个好的哈希函数，使得数据在哈希表中散列均匀，减少哈希冲突。另外哈希表中的数据是无序存储的，不能使用范围查找，在进行范围查找时需要从哈希表中逐一查询，效率较低，而实际业务中避免不了需要进行范围查找，所以哈希表数据结构不适合作为索引。Memory存储引擎中使用了哈希索引。二叉树、平衡二叉树和红黑树数据结构中，每个节点最多只能有两个子节点，在数据量增多时，必然会增加树的深度，在读取数据时自然而然会增加IO的次数。在B树(平衡多叉树)中每个节点可以有多个子节点，即使数据量增多时，树的深度并不会明显增加。由于在B树中数据均匀的存储在叶子结点和非叶子结点中，随着数据量的增加，树的深度必然会增加。一般而言，3层结构的B树可以存储几千条甚至上万条数据，但是远远不能满足业务需求。基于此对B树进行了改进，引入了B+树。在B+树中，只有叶子结点存储数据，而非叶子结点不存储数据，只存储索引字段和指向子节点的指针。所以对于非叶子结点，B+树能比B树存储更多的索引数据。另外，在B+Tree的每个叶子节点增加一个指向相邻叶子节点的指针，就形成了带有顺序访问指针的B+Tree，极大提高了区间查询效率。一般而言，3到4层结构的B+树可以存储千万级别的数据，足以满足实际业务需求。另外，为了能够存储更多的索引数据，索引字段占用的内存空间要尽可能的少，通常作为索引的字段不要太长。

6、创建和优化索引的策略

MySQL的优化主要分为结构优化（Scheme optimization）和查询优化（Query optimization）。本文讨论的高性能索引策略主要属于结构优化范畴。本文的内容完全基于上文的理论基础，实际上一旦理解了索引背后的机制，那么选择高性能的策略就变成了纯粹的推理，并且可以理解这些策略背后的逻辑。

下面先简单介绍一下联合索引和最左匹配原则，再介绍索引优化策略。

联合索引（复合索引）：

相对于一般索引只有一个字段，联合索引可以为多个字段创建一个索引。比如，在（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；

如果用到了最左前缀而只是颠倒了顺序，也是可以用到索引的，因为mysql查询优化器会判断纠正这条sql语句该以什么样的顺序执行效率最高，最后才生成真正的执行计划。但我们还是最好按照索引顺序来查询，这样查询优化器就不用重新编译了。

前缀索引（只取一个列的前面几个字符作为索引）：

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

一般来说以下情况可以使用前缀索引：

1.字符串列(varchar,char,text等)，需要进行全字段匹配或者前匹配，也就是=‘xxx’ 或者 like ‘xxx%’。

2.字符串本身可能比较长，而且前几个字符就开始不相同。比如我们对中国人的姓名使用前缀索引就没啥意义，因为中国人名字都很短，另外对收件地址使用前缀索引也不是很实用，因为一方面收件地址一般都是以XX省开头，也就是说前几个字符都是差不多的，而且收件地址进行检索一般都是like ’%xxx%’，不会用到前匹配。相反对外国人的姓名可以使用前缀索引，因为其字符较长，而且前几个字符的选择性比较高。同样电子邮件也是一个可以使用前缀索引的字段。

3.前一半字符的索引选择性就已经接近于全字段的索引选择性。如果整个字段的长度为20，索引选择性为0.9，而我们对前10个字符建立前缀索引其选择性也只有0.5，那么我们需要继续加大前缀字符的长度，但是这个时候前缀索引的优势已经不明显，没有太大的建前缀索引的必要了。

一些文章中也提到：

MySQL 前缀索引能有效减小索引文件的大小，提高索引的速度。但是前缀索引也有它的坏处：MySQL 不能在 ORDER BY 或 GROUP BY 中使用前缀索引，也不能把它们用作覆盖索引(Covering Index)。

覆盖索引：索引的叶子节点中包含了需要查询的全部数据。

回表：对于InnoDB搜索引擎，如果使用辅助索引，首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录，这个操作叫做回表。

创建和优化索引的策略：

1、索引字段要尽可能少的占用存储空间，对较小的数据列使用索引，这样会使索引文件更小，同时内存中可以装载更多的索引键。

2、在满足业务需求的情况下索引字段要尽可能自增。

3、尽量不要为null字段创建索引。如果某列存在空值，即使对该列建索引也不会提高性能。

4、尽量对where,on,group by,order by中出现的列创建索引。

5、尽量创建联合索引，而不是多个索引。

6、尽量选择区分度高的列作为索引，区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0。

7、不要给所有的字段都创建索引，因为新增或删除数据时维护索引的成本较高。

8、索引字段尽量不要频繁修改。

9、尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

10、不要过多创建索引, 权衡索引个数与DML之间关系，DML也就是插入、删除数据操作。这里需要权衡一个问题，建立索引的目的是为了提高查询效率的，但建立的索引过多，会影响插入、删除数据的速度，因为我们修改的表数据，索引也需要进行调整重建。一个表的索引数最好不要超过6个，如果索引太多的话，就需要考虑一下那些不常使用到的列上建的索引是否有必要。

11、为较长的字符串使用前缀索引。

二、SQL优化及执行计划

1、SQL优化

上面提到了索引优化策略，下面看一下SQL查询优化策略。

1.查询条件中，一定不要使用select *，因为会返回过多无用的字段，会降低查询效率。应该使用具体的字段代替*，只返回使用到的字段。

2.不要在where条件中使用前后都是%的like模糊查询，如：

SELECT * FROM t_order WHERE customer LIKE '%zhang%';

这样会导致数据库引擎放弃索引进行全表扫描（如果开启索引条件下推，可能会使用索引）。

优化：尽量在字段后面使用模糊查询，%不要放在前面。如下：

SELECT * FROM t_order WHERE customer LIKE 'zhang%';

3.尽量不要在where条件中等号的左侧对索引字段进行表达式或函数操作，索引列不能参与计算，不会使用索引，会导致全表扫描。因为B+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用表达式或函数才能比较，显然成本太大。如下：

SELECT * FROM t_order WHERE score/10 = 10;

SELECT * FROM t_order WHERE SUBSTR(customer,1,5) = 'zhang';

SELECT * FROM t_order WHERE from_unixtime(create_time) = '2022-11-18';

将表达式或函数操作移动到等号右侧。优化如下：

SELECT * FROM t_order WHERE score = 10*10;

SELECT * FROM t_order WHERE customer LIKE 'zhang%';

SELECT * FROM t_order WHERE create_time = unix_timestamp('2022-11-18');

4. where条件数据类型不匹配也无法使用索引，例如，字符串与数字比较不使用索引。
假如字段a的数据类型是char(10)，
EXPLAIN SELECT * FROM t WHERE a="1"; – 走索引 
EXPLAIN SELECT * FROM t WHERE a=1; – 不走索引 

5.尽量不要使用in 和not in，可能会造成全表扫描。如下：

SELECT * FROM t_order WHERE id IN (2,3)

SELECT * FROM t_order1 WHERE customer IN (SELECT customer FROM t_order2)

优化：对于连续的数值，能用between就不要用in，如：

SELECT * FROM t_order WHERE id BETWEEN 2 AND 3

对于子查询，可以用exists代替。如：

SELECT * FROM t_order1 t1 WHERE EXISTS (SELECT * FROM t_order2 t2 WHERE t1.customer = t2.customer)

6.尽量不要使用or，可能会造成全表扫描。如下：

SELECT * FROM t_order WHERE id = 1 OR id = 3

优化：可以用union代替or。如下：

SELECT * FROM t_order WHERE id = 1

UNION

SELECT * FROM t_order WHERE id = 3;

7.where条件里尽量不要进行null值的判断，null的判断可能会造成全表扫描。如下：

SELECT * FROM t_order WHERE score IS NULL;

优化：给字段添加默认值，对默认值进行判断。如：

SELECT * FROM t_order WHERE score = 0;

8.正则表达式不使用索引，所以这是在SQL中很难看到regexp关键字的原因。

9.尽量不要使用where 1=1的条件，为了方便拼装查询条件，有时会加上该条件，这样会造成全表扫描。如下：

SELECT * FROM t_order WHERE 1=1

优化：如果用代码拼装sql，则由代码进行判断，没where加where，有where加and。如果用mybatis，请用mybatis的where语法。

10.程序要尽量避免大事务操作，提高系统并发能力。

MySQL索引失效的情况：

1、使用like进行模糊查询时，以%开头会导致索引失效。

2、对索引字段进行表达式或函数操作会导致索引失效。

3、索引字段类型不匹配存在隐式转换会导致索引失效，例如varchar类型的数字不加单引号会被转换成int类型，导致索引失效，进行全表扫描。

4、当or左右查询字段只有一个是索引时，该索引失效，只有当or左右查询字段均为索引时，才会生效。

5、对于组合索引没有使用到第一个索引字段会导致索引失效，即不满足最左匹配原则。

6、对于组合索引，如果中间某个索引字段使用了范围查询，会导致后续索引字段失效。

7、在索引字段上使用不等于操作符会导致索引失效。

8、当全表扫描速度比索引速度快时，mysql会使用全表扫描，此时索引失效。

9、如果索引字段存在null值，在索引列上使用 IS NULL 或 IS NOT NULL操作，索引不一定失效！

10、in或or在很多情况下会导致索引失效，但是要根据实际情况进行判断。

索引条件下推Index Condition Pushdown（ICP）：

在存储引擎层只能存取数据，查询数据时，存储引擎将满足部分条件的数据返回给server层，server层根据其他条件再对数据进行过滤。如果开启了索引条件下推，把在server层过滤的索引条件下推到存储引擎层，在存储引擎层将满足条件的数据返回给server层，在server层就不需要再进行过滤。

在MySQL5.7版本索引条件下推默认开启，可以修改参数optimizer_switch的index_condition_pushdown=on开启或off关闭索引条件下推，即set optimizer_switch='index_condition_pushdown=off'。通过语句show variables like 'optimizer_switch';可以查看参数optimizer_switch的值。

假设t_tab_user表有以下几个字段，并且在last_name和first_name两个字段上建立了联合索引。

id	last_name	first_name	phone
1001	Zhao	Xiaolong	13120001234
1002	Zhao	Xiaohu	13112349000
1003	Qian	Duoduo	13134568080
1004	Sun	Yulong	13156786666

select * from t_tab_user where last_name='Zhao' and first_name like '%long';

如果没有开启索引条件下推，在使用上面sql语句查询数据时，联合索引只会使用last_name，不会使用first_name，在存储引擎层会查到id为1001和1002两条数据返回给server层，server层再使用where条件进行过滤，最终返回给客户端id=1001的记录。通过explain执行计划可以看到extra字段的值是Using where，该值表示存储引擎层返回给server层的记录不全是客户端想要的数据记录，在server层使用where进行过滤。

如果开启了索引条件下推，在使用上面sql语句查询数据时，联合索引会使用last_name和first_name，在存储引擎层会查到id为1001的数据记录返回给server层，server层不再使用where条件进行过滤，最终返回给客户端id=1001的记录。通过explain执行计划可以看到extra字段的值是Using index condition，该值表示使用了索引条件下推，存储引擎层返回给server层的记录就是客户端想要的数据记录，在server层无需使用where进行过滤。

2、执行计划

MySQL本身的功能架构分为三个部分，分别是应用层、逻辑层、物理层，不只是MySQL ，其他大多数数据库产品都是按这种架构来进行划分的。

SQL语句执行过程主要如下所示，以查询为例：

首先接受到查询sql之后，数据库会立即分配一个线程对其进行处理；第一步查询处理器会对SQL查询进行优化，优化后会生成执行计划；然后交由计划执行器来执行。计划执行器需要访问更底层的事务管理器、存储管理器来操作数据，他们各自的分工各有不同；最终通过调用物理层的文件获取到查询结构信息，将最终结果响应给应用层。

所谓的执行计划，就是mysql如何执行一条Sql语句，包括sql查询的顺序、是否使用索引、以及使用索引信息等等。

MySQL提供了explain关键字来直观的查看一条SQL的执行计划。

//1. 查询table_name

select * from table_name where name="explain";

//2. 查看上述语句的执行计划

explain select * from table_name where name="explain";

执行查看上述2语句后，可以得出以下执行计划结果：

上面这个执行计划给到的信息是: 这个结果通过一个简单的语句全表扫描，共扫描1行，使用where条件在table_name表中筛选出的。

下面对执行计划结果中每个参数进行详解。

1.id

id由一组数字组成，表示一个查询中各个子查询的执行顺序。

id相同表示各个子查询的优先级相同，执行顺序由上至下。

id不同，若id值越大，则该子查询的优先级越高，越先被执行。

id为null时表示一个结果集，不需要使用它查询，常出现在包含union等查询语句中。

2.select_type

select_type表示每个子查询的查询类型。一些常见的查询类型如下所示：

select_type	description
SIMPLE	不包含任何子查询或union等查询
PRIMARY	包含子查询最外层查询就显示为PRIMARY
SUBQUERY	在select或where子句中包含的查询
DERIVED	from子句中包含的查询
UNION	出现在union后的查询语句中
UNION RESULT	从UNION中获取结果集

3.table

表示该语句查询的表。

4.partitions

表分区、表创建的时候可以指定通过那个列进行表分区。

例如：

create table tmp (

    id int unsigned not null AUTO_INCREMENT,

    name varchar(255),

    PRIMARY KEY (id)

) engine = innodb

partition by key (id) partitions 5;

5.type

访问类型。下表按照顺序性能依次提升，最差要用到range类型，如果是index或all，需要优化。

type	描述
all	扫描全表数据
index	遍历索引(full index scan)
range	索引范围查找
index_subquery	在子查询中使用ref
unique_subquery	在子查询中使用eq_ref
ref_or_null	对Null进行索引的优化的ref
fulltext	使用全文索引
ref	使用非唯一索引查找数据
eq_ref	在join查询中使用PRIMARY KEY or UNIQUE NOT NULL索引关联
const	使用主键或者唯一索引，且匹配的结果只有一条记录
system const	连接类型的特例，查询的表为系统表

6.possible_keys

查询涉及到的字段上若存在索引，则该索引将被列出来。当该列为NULL时，就要考虑当前的SQL是否需要优化了。注意：possible_keys表示可能使用的索引，不一定会使用。

7.key

显示MySQL在查询中实际使用的索引，若没有使用索引，显示为NULL。

Tips:查询中若使用了覆盖索引(覆盖索引：索引的数据覆盖了需要查询的所有数据)，则该索引仅出现在key列表中

8.key_length

索引长度。

int类型占4位，若字段允许为null，则索引长度为4+1=5，若字段不能为null，则索引长度为4+0=4。

char()、varchar()类型字段索引长度的计算公式：

(Character Set：utf8mb4=4,utf8=3,gbk=2,latin1=1) * 列长度 + 1(允许null) + 2(变长列)

例如：

CREATE TABLE `student` (

  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,

  `name` varchar(128) NOT NULL DEFAULT '',

  `age` int(11),

  PRIMARY KEY (`id`),

  UNIQUE KEY `idx` (`name`),

  KEY `idx_age` (`age`)

) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;

name字段是唯一索引：编码为utf8mb4，列长为128，不允许为NULL，字段类型为varchar(128)，所以name的索引长度为key_length = 128 * 4 + 0 + 2 = 514。

age字段是普通索引，age是int类型，int类型占4位，age允许null，所以索引长度为4+1=5。

9.ref

表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

10.rows

扫描行数,也就是说，需要扫描多少行，才能获取目标行数，一般情况下会大于返回行数。通常情况下，rows越小，效率越高。也就有大部分SQL优化，都是在减少这个值的大小。

Tips: 理想情况下扫描的行数与实际返回行数理论上是一致的，但这种情况及其少，如关联查询，扫描的行数就会比返回行数大大增加。

11.extra

extra的信息非常丰富，常见的有：

1.Using index 使用覆盖索引

2.Using where 使用where子句来过滤结果集(存储引擎层返回给server层的记录不全是客户端想要的数据记录，在server层需要使用where进行过滤)。

3.Using filesort 使用文件排序，使用非索引列进行排序时出现，非常消耗性能，尽量优化。

4.Using temporary 使用了临时表（使用非索引列排序或group by，或其他情况，需要优化）。

5.Using intersect并集操作，即两个索引同时检索的结果再求并集，此时应将两个字段的索引合并成一个，即建立联合索引。

6.Using index condition 使用了索引条件下推，存储引擎层返回给server层的记录就是客户端想要的数据记录

标准SQL语句执行顺序：

正常情况下先执行join连接，再执行where条件过滤，但是mysql会进行优化处理，即先执行where条件过滤再执行join连接。

mysql优化层次：

where和having的区别：

where:

where是一个约束声明，使用where约束来自数据库的数据;

where是在结果返回之前起作用的;

where中不能使用聚合函数。

having:

having是一个过滤声明;

在查询返回结果集以后，对查询结果进行的过滤操作;

在having中可以使用聚合函数。

聚合函数就是例如SUM, COUNT, MAX, AVG等的函数。where、聚合函数、having 的执行顺序是：where>聚合函数(sum,min,max,avg,count)>having，由于where先于聚合函数发生，所以在where中不能使用聚合函数，而聚合函数先于having发生，聚合之后再通过having过滤，所以在having中可以使用聚合函数。

where、group by和having的执行顺序：where > group by > having，where在分组之前过滤数据，having在分组之后过滤数据。

where子句在聚合前先筛选记录，也就是说作用在group by 子句和having子句前，而 having子句在聚合后对组记录进行筛选。

having只能使用 select 和from之间列出的字段或聚合函数，而where不能使用select后写的常量的别名(但是having却可以)，换言之where只可以使用常量和表中的字段。例如：

select user_id id from t_user where user_id = 1;可以查到数据，而select user_id id from t_user where id = 1;却查不到数据，会提示错误：ERROR 1054 (42S22): Unknown column 'id' in 'where clause'。