SQL优化和索引分析优化

性能下降的分析

索引失效
关联查询太多join
- 设计缺陷或不得已的需求
服务器调优及各个参数设置
- 缓冲
- 线程数

SQL语句的执行顺序

手写SQL的顺序

1538460297737

机读的执行顺序

从 from开始执行读取

1538460170651

SQL解析过程

1538460388979

7种Join连接

两表共有部分：inner join 内连接

select xx from TableA A inner join TableB B on A.key = B.key
两表共有部分+A表部分 ： left join 左外连接

select xx from TableA A left join TableB B on A.ley = B.key
两表共有部分+B表部分： right join 右外连接

select xx from TableA A right join TableB B on A.key = B.key
两表中的A表独有部分 ： left join where B.key is null 左连接之后去掉两表的共有部分，只要左外连接之后的B表为null 的部分

select xx from TableA A left join TableB B on A.ley = B.key where B.key is null
两表中的B表独有部分 ： right join where A.key is null 左连接之后去掉两表的共有部分，只要左外连接之后的B表为null 的部分

select xx from TableA A right join TableB B on A.ley = B.key where A.key is null
两表的全集： A独有部分+共有部分+B独有部分

利用 union 联合加去重： left join union right join

select xx from TableA A left join TableB B on A.ley = B.key union select xx from TableA A right join TableB B on A.key = B.key
两表中的全集，并去掉共有部分： A独有部分+B独有部分，利用 union： left join where B.key is null union right join where A.key is null

select xx from TableA A left join TableB B on A.ley = B.key where B.key is null union select xx from TableA A right join TableB B on A.ley = B.key where A.key is null

索引

定义

索引 Index 是帮助Mysql高效获取数据的数据结构，可以理解为 排好序的快速查找数据结构

主要用于排序和查找，索引会影响排序和查找

B树索引（多路搜索树）

1538482995573

好处

降低数据库的IO成本，提高数据检索效率
降低CPU的消耗，降低数据排序的成本

坏处

降低更新表的速度，更改数据时，不仅要保存数据，还要更新一下索引文件，每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。
创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加
索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大

哪些情况需要创建索引

主键自动回建立主键索引，也是唯一索引
频繁作为查询条件的字段应创建索引
查询中与其他表关联的字段，外键关系建立索引
单列索引和组合索引的选择，在高并发情况下建立组合索引好
查询中排序的字段，排序字段若通过索引去访问将大大调高排序的效率，因为索引的B+树结构，本身也是排过序的
查询中统计或者分组的字段，因为分组的前提是排序，所以也可以通过索引来提高效率

哪些情况不要创建索引

where条件里用不到的字段不创建索引
频繁更新的字段不合适创建索引，因为每次更新数据，还需要更新索引信息
表的记录太少了
经常增删改的表，因为要更新索引信息
数据的差异率不高，数据重复率高，并且分布平均的字段，数据列中包含许多重复的内容。

索引的原理

为了探究为什么使用BTree这种数据结构作为索引，搜了一些资料，下面理论知识引用于一篇很好的博客，作为摘记：http://blog.codinglabs.org/articles/theory-of-mysql-index.html

B-Tree

为了描述B-Tree，首先定义一条数据记录为一个二元组[key, data]，key为记录的键值，对于不同数据记录，key是互不相同的；data为数据记录除key外的数据。那么B-Tree是满足下列条件的数据结构：

d为大于1的一个正整数，称为B-Tree的度。

h为一个正整数，称为B-Tree的高度。

每个非叶子节点由n-1个key和n个指针组成，其中d<=n<=2d。

每个叶子节点最少包含一个key和两个指针，最多包含2d-1个key和2d个指针，叶节点的指针均为null 。

所有叶节点具有相同的深度，等于树高h。

key和指针互相间隔，节点两端是指针。

一个节点中的key从左到右非递减排列。

所有节点组成树结构。

每个指针要么为null，要么指向另外一个节点。

如果某个指针在节点node最左边且不为null，则其指向节点的所有key小于v(key1)v(key1)，其中v(key1)v(key1)为node的第一个key的值。

如果某个指针在节点node最右边且不为null，则其指向节点的所有key大于v(keym)v(keym)，其中v(keym)v(keym)为node的最后一个key的值。

如果某个指针在节点node的左右相邻key分别是keyikeyi和keyi+1keyi+1且不为null，则其指向节点的所有key小于v(keyi+1)v(keyi+1)且大于v(keyi)v(keyi)。

BTree结构

关于B-Tree有一系列有趣的性质，例如一个度为d的B-Tree，设其索引N个key，则其树高h的上限为logd((N+1)/2)logd((N+1)/2)，检索一个key，其查找节点个数的渐进复杂度为O(logdN)O(logdN)。从这点可以看出，B-Tree是一个非常有效率的索引数据结构。

B+Tree

B-Tree有许多变种，其中最常见的是B+Tree，例如MySQL就普遍使用B+Tree实现其索引结构。

与B-Tree相比，B+Tree有以下不同点：

每个节点的指针上限为2d而不是2d+1。

内节点不存储data，只存储key；叶子节点不存储指针。

图3是一个简单的B+Tree示意。

B+Tree结构

由于并不是所有节点都具有相同的域，因此B+Tree中叶节点和内节点一般大小不同。这点与B-Tree不同，虽然B-Tree中不同节点存放的key和指针可能数量不一致，但是每个节点的域和上限是一致的，所以在实现中B-Tree往往对每个节点申请同等大小的空间。

一般来说，B+Tree比B-Tree更适合实现外存储索引结构，具体原因与外存储器原理及计算机存取原理有关，将在下面讨论。

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

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

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

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

为什么使用B-Tree（B+Tree）

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

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

局部性原理与磁盘预读

由于存储介质的特性，磁盘本身存取就比主存慢很多，再加上机械运动耗费，磁盘的存取速度往往是主存的几百分分之一，因此为了提高效率，要尽量减少磁盘I/O。为了达到这个目的，磁盘往往不是严格按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存。这样做的理论依据是计算机科学中著名的局部性原理：

当一个数据被用到时，其附近的数据也通常会马上被使用。

程序运行期间所需要的数据通常比较集中。

由于磁盘顺序读取的效率很高（不需要寻道时间，只需很少的旋转时间），因此对于具有局部性的程序来说，预读可以提高I/O效率。

预读的长度一般为页（page）的整倍数。页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页（在许多操作系统中，页得大小通常为4k），主存和磁盘以页为单位交换数据。当程序要读取的数据不在主存中时，会触发一个缺页异常，此时系统会向磁盘发出读盘信号，磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中，然后异常返回，程序继续运行。

B-/+Tree索引的性能分析

上文说过一般使用磁盘I/O次数评价索引结构的优劣。先从B-Tree分析，根据B-Tree的定义，可知检索一次最多需要访问h个节点。数据库系统的设计者巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入。为了达到这个目的，在实际实现B-Tree还需要使用如下技巧：

每次新建节点时，直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，就实现了一个node只需一次I/O。

B-Tree中一次检索最多需要h-1次I/O（根节点常驻内存），渐进复杂度为O(h)=O(logdN)O(h)=O(logdN)。一般实际应用中，出度d是非常大的数字，通常超过100，因此h非常小（通常不超过3）。

综上所述，用B-Tree作为索引结构效率是非常高的。

而红黑树这种结构，h明显要深的多。由于逻辑上很近的节点（父子）物理上可能很远，无法利用局部性，所以红黑树的I/O渐进复杂度也为O(h)，效率明显比B-Tree差很多。

上文还说过，B+Tree更适合外存索引，原因和内节点出度d有关。从上面分析可以看到，d越大索引的性能越好，而出度的上限取决于节点内key和data的大小：

dmax=floor(pagesize/(keysize+datasize+pointsize)) dmax=floor(pagesize/(keysize+datasize+pointsize))

floor表示向下取整。由于B+Tree内节点去掉了data域，因此可以拥有更大的出度，拥有更好的性能。

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

BTree和B+Tree的比较

B树：有序数组+平衡多叉树；
B+树：有序数组链表+平衡多叉树；

B+树的关键字全部存放在叶子节点中，非叶子节点用来做索引，而叶子节点中有一个指针指向一下个叶子节点。做这个优化的目的是为了提高区间访问的性能。而正是这个特性决定了B+树更适合用来存储外部数据

引用一段话：

走进搜索引擎的作者梁斌老师针对B树、B+树给出了他的意见（为了真实性，特引用其原话，未作任何改动）： “B+树还有一个最大的好处，方便扫库，B树必须用中序遍历的方法按序扫库，而B+树直接从叶子结点挨个扫一遍就完了，B+树支持range-query非常方便，而B树不支持。这是数据库选用B+树的最主要原因。
比如要查 5-10之间的，B+树一把到5这个标记，再一把到10，然后串起来就行了，B树就非常麻烦。B树的好处，就是成功查询特别有利，因为树的高度总体要比B+树矮。不成功的情况下，B树也比B+树稍稍占一点点便宜。
B树比如你的例子中查，17的话，一把就得到结果了，
有很多基于频率的搜索是选用B树，越频繁query的结点越往根上走，前提是需要对query做统计，而且要对key做一些变化。
另外B树也好B+树也好，根或者上面几层因为被反复query，所以这几块基本都在内存中，不会出现读磁盘IO，一般已启动的时候，就会主动换入内存。”

B+树的基于范围的查询简洁很多。由于叶子节点有指向下一个叶子节点的指针，因此从块1到块2的访问，通过块1指向块2的指针即可。从块2到块3也是通过一个指针即可。

引用一篇博文中网友评论的一段话：

数据库索引采用B+树的主要原因是B树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题。正是为了解决这个问题，B+树应运而生。B+树只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作（或者说效率太低）。

正如上面所说，在数据库中基于范围的查询是非常频繁的，因此MySQL最终选择的索引结构是B+树而不是B树

数据最终会保存在本地磁盘文件系统中，每次读取数据时，必然会产生本地IO操作，会消耗时间，如果本地IO操作次数过多，会严重影响数据的读取效率，特别是在海量的数据情况下，索引这种数据结构的出现就是为了解决这种问题，建立合适的数据结构来存储数据会大大减少本地磁盘IO操作。

mysql索引图

解读：

所有的数据都是存放在叶子节点，非叶子节点不存放数据
磁盘块中有两个值和三个指针，三个指针分别指向P1 表示小于第一个的磁盘块，P2 表示在第一个和第二个之间的磁盘块，P3 则表示大于第二个的磁盘块，本身已经有所排序，这两个值都不是最后的真实数据值，只是为了叶子节点中的真实数据值而建立的中介搜索值
InnoDB中主键是磁盘块的key值，最终的叶子节点的data域则是最终的数据；MyISAM中的key值是主键值，但是data域则是地址值，根据data域的值去读取相应数据记录。

查找过程

以磁盘块1为例，指针 P1 表示小于17的磁盘块，P2 表示在 17~35 之间的磁盘块，P3 则表示大于35的磁盘块。

比如要查找数据项99，首先将磁盘块1 load 到内存中，发生 1 次 IO。接着通过二分查找发现 99 大于 35，所以找到了 P3 指针。通过P3 指针发生第二次 IO 将磁盘块4加载到内存。再通过二分查找发现大于87，通过 P3 指针发生了第三次 IO 将磁盘块11 加载到内存。最后再通过一次二分查找找到了数据项99。

由此可见，如果一个几百万的数据查询只需要进行三次 IO 即可找到数据，那么整个效率将是非常高的。

观察树的结构，发现查询需要经历几次 IO 是由树的高度来决定的，而树的高度又由磁盘块，数据项的大小决定的。

磁盘块越大，数据项越小那么树的高度就越低。这也就是为什么索引字段要尽可能小的原因。

MySQL索引实现

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

MyISAM索引实现

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

MyISAM主索引图

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

MyISAM辅助索引图

同样也是一颗B+Tree，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。

InnoDB索引实现

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

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

图10

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

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

InnoDB辅助索引图

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

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

InnoDB的主键选择与插入优化

在使用InnoDB存储引擎时，如果没有特别的需要，请永远使用一个与业务无关的自增字段作为主键。

经常看到有帖子或博客讨论主键选择问题，有人建议使用业务无关的自增主键，有人觉得没有必要，完全可以使用如学号或身份证号这种唯一字段作为主键。不论支持哪种论点，大多数论据都是业务层面的。如果从数据库索引优化角度看，使用InnoDB引擎而不使用自增主键绝对是一个糟糕的主意。

上文讨论过InnoDB的索引实现，InnoDB使用聚集索引，数据记录本身被存于主索引（一颗B+Tree）的叶子节点上。这就要求同一个叶子节点内（大小为一个内存页或磁盘页）的各条数据记录按主键顺序存放，因此每当有一条新的记录插入时，MySQL会根据其主键将其插入适当的节点和位置，如果页面达到装载因子（InnoDB默认为15/16），则开辟一个新的页（节点）。

如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。如下图所示：

图13

这样就会形成一个紧凑的索引结构，近似顺序填满。由于每次插入时也不需要移动已有数据，因此效率很高，也不会增加很多开销在维护索引上。

如果使用非自增主键（如果身份证号或学号等），由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置：

图14

此时MySQL不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。

因此，只要可以，请尽量在InnoDB上采用自增字段做主键。

Explain 解析SQL

我们平时在执行sql后，mysql数据库其实在执行过程中会自动的将我们执行的sql语句进行自动优化和解析，执行过程往往不会是我们写的sql语句的执行顺序。

我们可以通过explain+sql语句来解析该sql语句，并且通过查看执行后的信息，分析mysql是如何解释执行该sql语句的加载过程，从而可以进行有针对的能优化。

索引使用策略及SQL优化

先来一些结论：

单表索引优化

当一个表中建立复合索引时，sql的查询条件中有一个索引字段的查询条件是范围，不是精确值时，应避免建立该字段的索引，会导致索引失效。复合索引建立时，索引字段的顺序很关键，因为复合索引的建立有前后顺序，往往后面的索引是建立在前面索引的基础之上的。如果此时SQL语句中的查询字段的条件是一个范围值，不精确，那么它后面的索引列就会发生索引失效。例如：建立复合索引 abc(a,b,c) ，sql语句where条件是：where a="1" and b > 1 and c="2" ，此时字段b和c的索引就会失效，应该修改索引为： ac(a,c)。

两表索引优化

left join 左连接，用于确定如何从右表搜索行，左边记录一定都有，所以右边表的优化是我们的重点，给右表连接字段建立索引比给左边连接字段建立索引要优化很多。结论：左连接给右表连接字段建立索引，相反，右连接给左表连接字段建立索引。

join语句索引优化

尽可能减少join语句中的循环次数：永远用小结果集驱动大的结果集 相当于外层循环尽量减小，小于内层循环。
优先优化内层循环
保证join语句中被驱动表上join条件字段已经被索引
当无法保证被驱动表的join条件字段被索引且内存资源充足的前提下，考虑JoinBuffer的设置调大

索引失效的情況

最左前缀法则

如果建立了多列索引，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。带头大哥不能丢！中间兄弟不能挂！如果中间列没有使用，同样也能命中索引，但是只会使用到其前面的索引，其后面的索引列不起作用，效果减弱

如果给 user 表中的 (name,age,home) 字段创建了复合索引那么使用以下SQL 都是可以命中索引，只要查询条件中包含了复合索引的最左列字段条件就会命中索引:

select name from user where name  ='hh' and age = 111 and home = 'jhjj';

select name from user where age =11 and name='hh' and home = 'jhjj'；

select name from user where name='affa' and home = 'jhjj'；

但是，其中最后一条sql，中间的索引列age没有用到，虽然也会命中索引，但只会命中name的索引，不会命中home的索引，效果会减弱。使用explain可以进行分析。

但是使用

1	select name from user where age =11

是不能命中索引的。

不在索引列上做任何操作（计算、函数、自动或者手动的类型转换），会导致失效从而转向了全表扫描。
mysql在使用不等于（!= 或者 <>）的时候无法使用索引会导致全表扫描
is null ， is not null 无法使用索引
like 模糊查询时，以 ‘%abc..’ ，前模糊查询，索引失效，从而变成了全表扫描，非前导模糊查询则不失效。只有百分号放在右边会命中索引，不过只是range查找，范围查找。

实际中必须使用两边模糊查询时，并且索引不失效，该怎么解决？

使用覆盖索引的方式，从另外一方面使用索引
1
select name from user where name like '%aa%'；
此时，select name 代替了 select * ，虽然模糊查询条件不能命中name索引，但是使用了覆盖索引的方式查询条件，也会命中了name索引。
字符串不加单引号查询会导致索引失效
少用 or ，用它连接时会导致索引失效

尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少 select * 的使用

1
2
3

select * from user where age =11；- 减少偷懒使用，下面性能更好

select  name  from user where age =11

索引列中其中一个索引使用了范围条件，那么该索引列之后的列的索引会失效。范围之后全失效。

负向查询不能使用索引

1	select name from user where id not in (1,3,4);

应该修改为:

1	select name from user where id in (2,5,6);

如果需要进行 join 的字段两表的字段类型要相同 不然也不会命中索引

exists和in的比较

优化原则：小表驱动大表，小的数据集驱动大的数据集

1	select * from A where id in (select id from B)

等价于：

1 2	for select id from B for select * from A where A.id = B.id

外层循环是B表。如果当B表的数据集小于A表的数据时，此时用in优于exists。

1	select * from A where exists (select 1 from B where B.id = A.id)

此时则等价于：

1 2	for select * from A for select * from B where B.id = A.id

注意此时的外层循环则是A表，当A表的数据集小于B表的数据集时，用exists要优于in

注意： A和B表的ID字段应该建立索引。

1538832680017

existis用法

in关键字用法的变通

1
2
3

select xx from A where exists(select X from B where B.id = A.id)
- 相当于,效果一样，其中上面sql语句中的X可以为任意常量
select xx from A where id in (select id from B)

可以理解为：将主查询的数据，放到子查询中做条件验证，根据验证结果，结果为 true或者false，来决定主查询的数据结果是否得以保留。