MySQL：索引详解

原文地址

1. MySQL索引原理及慢查询优化: tech.meituan.com/2014/06/30/…
2. Explain详解: blog.csdn.net/qq_38975553…
3. BTree和B+Tree详解: blog.csdn.net/weixin_4194…

索引概述

定义：索引是存储引擎用于快速找到记载的一种数据结构。举例说明：假如查找一本书中的某个特定主题，一般会先看书的目录（相似索引），找到对应页面。在MySQL，存储引擎选用相似的办法运用索引，高效获取查找的数据。

索引的分类

1）从存储结构上来区分

• Btree 索引（B+tree，B-tree)
• 哈希索引
• full-index 全文索f g i引

2）从运用层次上来区分

• 一般索引：即一个索引只包含单个列，一个表能够有多个单列索引。
• 唯一索引：索引列的值有必要唯一，但答应有空值。
• 复合索引：一个索引包含多个列。

3）从表记载的摆放次序和索引的摆放次序是否共同来区分

• 聚集索引：表记载的摆放次序和索引的摆放次序共同。
• 非聚集索引：表记载的摆放次序和索引的摆放次序不共同。

索引底层数据结构

磁盘IO与预读

数据库保存的数据是存储l G J F在磁盘上，查找数据时需求将磁盘中的数据加载到内存中，在介绍索引的完结之前，先了解下磁盘IO与预读。

磁盘读取数据靠的是机械运动，每次读取数据花费的时刻能够分为寻道时刻、旋转推迟、传输时刻三个部分，寻道时刻指的是磁臂移动到指定磁道所需求的时刻，主流磁盘一般在5ms以下；旋转推迟便是咱们经常听说的磁盘转速，比方一个磁盘7200转，表明每分钟能转7200次，也便是说1秒钟能转120次，旋转推迟便d 6 * 7 ; d / 是1/12c N i Z0/2 = 4. 17ms；传输时刻指的是从磁盘读出M z b f % c o , B或将数据写入磁盘6 f 7 7的时刻，一般在零点几毫秒，相关于前两R | & 3 1 | } #个时刻能a * Q K够忽略不计。那么拜访一次磁盘的时刻，即一次磁盘IO的时刻约等于5+4. 17 = 9ms左右，听起来还挺不错的，但要知道一台500 -MIPS的机器每秒能够履行5亿条指令，由于指令m = V依托的是电的H N $ r Q ( $性质，换句话说履行一次IO的时刻能够履行D ( b 2 $40万条指令，数据库动辄十万百万甚至千万级数据，每次9毫秒的时刻，显然是个c N V I : { |灾祸。

下图是核算机硬件推迟的对比图，供大家参阅：

考虑t c ! G Z 8 Z f [到磁盘IO是十分昂扬的操作，核算机操作体系做了一些优化，当一次IO时，不光把当时磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内，由于局部预读性原z ^ N g 0 u 0 U理告诉咱们，当核算机拜访D | O ~ M + t D一个地址的数据的时分，与其相邻的数据也会很快被拜访到。每e s D一次IO读) % N取的数据咱们称之j e R ; J K为一页(page)。具体一页有多大数据跟操作体系有关，一般为4k或8k，也便是咱们读取一页内的数据时分，实践上才发生了一次IO，这个理论关于索引的数据结构设计十分有协助。L f g

B-Tree和B+Tree

B-tree

B-Tree是为磁盘等外存储设备设计的一种平衡查找树。

B-Tree结构的数据能够让体系高效的找到数据地点的磁盘块。为了描述B-Tree，首先定义一条记载为一个二元组[key, dat( ] ba] ，key为记载的键值，对应表中的主键值，data为一行记载中除主键外的数据。关于不同的记载，key值互不相同。v b W W c

一棵m阶的B-Tree有如下特性：

1. 每一个节点M h S最多有 m 个子节点
2. 每一个非叶子节点（除根节点）最少有 ⌈m/2⌉ 个子_ _ x I Q d 5 j 4节点
3. 假如根节点不是叶子节点，那么它至少有两个子节点
4. 有 k 个子节点的非q 2 x 3叶子节点具有 k − 1 个键
5. 一切的叶子节点都在同一层

B-Tree中的每个节点依据实践状况能够包含许多的关键字信息和分支，如下图所示为一个3阶的B-Tree：

每个节点 } ~ K占用一个盘块的磁盘空间，一个节点上有两个升序排$ $ V序的关键字和三个指向子树根节点的指针，指针存储的是子节点地点磁盘块m { F f | – @ 4的地址。两个关键词区分成的三个规= Q d G x ? e模域对应三个指针指向的子树的数据的规模域。以根节点为例，关键字为17和35，P1指L : G X w m 针指向的子树的数据规模为小于17，P2指针指向的子树的数据规模为17~35，P3指针指向的子树的数据{ _ / a C %规模为大于35。

模拟查找关键字29的进程：

1. 依据根节点找到磁盘块1，读入内存。【磁盘I/O操作第1次】

   比较关键字29在区间（17, 35），找到磁盘块1的指针P2。

2. 依据P2指针找到磁盘块3，读入内存。【磁盘I/O操作第2次】

   比较关键字29在区间（26, 30），找到磁盘块3的指针P2。

3. 依据P2指针找到磁盘块8，读入内存。【磁盘I/O操作第3次】

   在磁盘块8中的关键字列表中9 a K : h找到关键字29。

剖析上面进程，发现需求3次磁盘I/O操作，和3次内存查找操作。由于内存中的关键字是一个有序表结构，能够利用二分法查找进步功率。而3次磁盘I/O操作是影响整个B-Tree5 } ~查找功率的决定因素。B-Tree相关于AVLTree缩减了节点个数，使每次磁盘I/O取到Q M J _ 1 Z内存的数据都发挥了作用，从而进步了查询功率。

B+Tree

B+Tree是在B-Tree基础上的一种优化，InnoDB存储引擎便是用B+Tree完结其索引结构。

在B+Tree中，一切数据记载节点都是按照键值巨细次序存放在同一层的叶子节点上，而非v } (叶子节点上只存储key值信息，这样能够大大加大每个节点存储的key值数量，降低B+Tree的高度。

由于B+Tree的非H ? T F叶子节点只存储键值信息，假定每个磁盘块能存储4个键值及指针信息，则变成B+Tz 1 8 I X $ ree后其结构如下图所示：

树立索引的几大准则

1. 最左前缀匹配准则，十分重要的准则，mysql会一向向右匹配直到遇到规模查询(>、<、between% # $ s g、like)就中止匹配，比方a = 1 and b = 2 and c > 3 and d = 4 假如树立(a,b,c,d)次序的索引，d是用不到索引的7 9 x d，假如树立(a,b,d,c)的索引则都能够用到，a,b,d的次序能够恣意调整。

2. =和in能够乱序，比方a = 1 and b = 2 and c = 3 树立(a,b,c)索引能够恣意次序，mysql的查% p 0 D p询优化器会帮你优化成索引能够辨认的形式。

3. 尽量挑选区分度高的列作为索引，区分度的公式是count(distinct ch j 5 H . xol)/count(*)，表明字段不重复的份额，份额越大咱们扫描的记载数越少，唯一键的区分度是1，而一些状态、性别字段或许在大数据面前区分度便g ^ C是0，那或许有人会问，这个份额有什么经历值吗! n 9 r +？运用场景不同，这个值也很难确定，一般需求join的字段咱们都要求是0.1以上，即均匀1条扫描10条记载。

4. 索引列不能参与核算，坚持列“洁净”，比方from_unixtime(create_time)8 Y e Z h Y M f = ’2014-05-29’就不能运用到索引，原因很简略，b+树中存的都是数据% a U s l表中的字段值，但进行检索时，需求把一切元素都运用函数4 4 l ) / V |才干比较，显然成本太大。所以句a i | 4 = 2子应该写成create_time = unin ! ~ R 7 . ; / Ex_timestamp(’2014-05-29’)。

5. 尽量的扩展索引，不要P D n g d新建索引。比方表中现已有a的索引，现在要加(a,b)的索引，那么只需求修正本来的索引即可。

慢查询优化根本进程

1. 先运转看看是否真的很慢，留意设置SQL_NO_CACHE
2. where条件单表查，确定最小回来记载表。这句话的意思是把查询句子的where都运用到表中回来的记载数最小的表开端查起，单表j B I ] `每个字段分别查询，看哪个字段的区) B 2 C a 4分度最高K k @ * ] q K 9 r
3. explain检查履行方案，是否与2预期共同（从F S R z确定记载较少的表开端查询）
4. oI S ! ]rder by li? [ 2 n d G (mit 形式的sql句子让排序的表优先查
5. 了解事务方运用场景| Y H 2
6. 加索引时参照建索引的几大准则
7. 观察成果，不契合预期持续从0剖析

explain详解

explain为mysql供给句子的履行方案信息。能够运用在select、delete、insert、update和place句子上。explain的履行方案，仅仅作为句子履行进程的一个参阅，实践履行的进程不一定和方案彻底共同，可! ) ] – Q y ?是履行方案中透露出的信息却能够协助挑选更好的索引# x Z z # 7 n )和写出更优化的查询句子。

expl( O I ` L R Z Z pain输出项

id

id列的编号是 select 的序列号，有几个 select 就有几{ n X i T ~ :个id，而且id的次序是按 select 呈现的次序增加的。

MySQL将 select 查询分为简略查询(SIMPLE)和杂乱查询(PRIMARY)。杂乱查询分为三类：简略子查询、派生表（froO # k L 0 } j 6m句子中的子查询）、union 查询。

id列越大履行优先级越高，id相同则从上往下履行，id为NULL最后履行。

select_type

select_type 表明对应行是简略仍是杂乱的查询。

tabl N ] 5 ? d ;e

这一列表明 explain 的一行正在拜访哪个表。

当 from 子句中有子查1 I T , )询时，t/ y 7 : O H {able列是 格式，表明当时查询依V v ) s W $ A *靠 id=N 的查询，所以先履行 id=N 的查询。

当有 union 时，UNION RESULT 的 table 列的值为<union1, 2>，1和2表] T L Z明参与 union 的 select 行id{ p ) : / 0 ^。

partitions

type

这一列表明相关类型或拜访类型，即MySQL决定怎么查找表中的行，查找数I f m i 1 p X据行记载的大约规模。

依次从最优到最差分别为：system > const > eq_ref > refA T 2 ~ L ; ; r y > range > index > ALL

• NULL：mysql能够在优化阶段分解查询句子，在履行阶段用不w + p s , A着再拜访表或索引。例如：在索引列中选取最小值，能够单独查找索引来完结，不需求在履行时拜访表。
• const, system：mysql能对查询的某部分进行优化并将其转化成) T / ! s C )一个常量（能够看show warnings 的成果）。用于 primary key 或 unique key 的一切列与常数比较时，E f 3所以表最多有一个匹配行，读取1次，速度比较快。system是const的特例，表Z * F h T 里只要一条元组匹配时为system
• eq_ref：primary key 或 unique key 索引的一切部分被连接运用 ，最多只会回来一条契合条= a o / [ N | b T件的记载。这或许是在 const 之外最好的联接类型了，简略的 select 查询不会呈现这种 type。
• ref：比较 eq_ref，不运用唯一索引，而是运用一般索引或者唯一性索引的部分前缀，索引要和某个值比较较，或许会找到多Y Q $ }个契合Q / U 8 .条件的行。
• range：规模扫描一般呈现在 in(), between , > , <, >= 等操作中。运用一个索引来检索给定规模的行。
• index：扫描全表索引，这一? L ]般比ALL快一些。（index是从索引中读取的，而all是从硬盘中读取）
• AL} X E J i JL：即全表扫描，意味着mysql需求自始至终去查找所需求的行。一般状况下这需求增加索引来进行优化了

possibR r Dle_keys

这一列显现查询或许运用哪些索引来查找。

explain7 ] _ x 时或许呈现 p– B y Iossible_keys 有0 7 s s d T列，而 key 显现 NULL 的状况，这种状况是由于表中数据不多, { ,，mysql认为索引对此查询协助不大，挑选了全表查询。

假如该列是NULL，则没有相关的索引。在这种状况下，能够经过检查 where 子句看是否能够发明一个恰当的索引来进步查询功能，然X c : | 6后用 explain 检查作用。

key

这一列显现mB ~ Aysql实践选用哪个索引来优化对该表的拜访。

假如没有运用索p f 3 g V . q g引，则该列是 NULL。假如想强制mysql运用或忽视poL x ; ] 3 g p : *ssible_keys列中的索引，在查询中运用 force indeQ W n z q q 6x、ignor~ y x : 2e index。

key_len

这一列显现了mysql在索引里运用的字节数，经过这个值能够算出具体运用了索引中的哪些列。

ref

这一列显现了在key列记载的索引中，表查找值所用到0 X p b 5 9 j的列或常量，常见的有：const（常量），字段名（例：film. id）

rows

这一列是mysqlA = ] A估量要读取并检测的行数，留意这个不是成果集里的行数。

filtered

Extra

• Using index：查询的列被索引掩盖，而且where挑选条件是索引的前导列(最左侧索引)，是功能高的体现。一般是运用了掩盖索引(索M g / , B D B o b引包含了一切查询的字段)。关于innodb来说，假如是辅助索引功能会有不少进步。

• Using where：查询的k q 6 * d J B列未被索引掩盖，where挑选条件非索引的前导列。

• Using where Using index：查询的列被索引掩盖，而且where挑选条件是索引列之一但不是索引的前导列，意味着无法直接经过索引查找来查询到契合条件的数据, Using index代表select用到了掩盖索引。

• NULL：查询的列未被索引掩盖，而且V X F Z G P !where挑选条件是索引的前导列，意味着用到了索引，可是部分字段未被a W a索引掩盖，有必要经过“回表”来完结，不是朴实地用到了索1 D w引，也不是彻底没用到索引。

• Using index condition：与Using where相似，查询的W O : 3 V k列不彻底被索引掩盖，where条件中是一k W 5 , 6 W个前导列的规模；. B w N ^ |

• Using tempor% X b l z uary：mys9 C – k V & ~ql需求创立一张暂时表来处理查询。呈现这种状况一般是要进行优化的，首先C [ o是想到用索引来J } @ $ 5 & G优化。

• Using filesort：mysql 会对成果运用一个外部索引排序，而不是按索引次序从表里读取行。此刻mysql会依据联接类型阅读一切契合条件的记载，并保存排序关键字和行指针，然后排序关键字并按次序检索行信息。这种状况下一般也是要考虑运用索引来优化的。

案列剖析

1. 杂乱句子写法

许多状况下，咱们写SQL仅仅为了V [ ! l完结功能，这仅仅榜首步，不( e Z X R { r = o同的句子书写方法关于功率往往有实质q , v + f D 3的差别，这要求咱们对mysql的履行方案和索引准则有十分清楚的认识，请看下面的句子：

select
distinct cert.emp_id
from
cm_log cl
inner joins % s ! _ . 4 (
select
emp.id as empx | h F_id,
emp_cert.id as cert_id
from
employee emp
left join emp_certificate emp_cert on emp.id = emp_cert.emp_id
where
emp.is_deleted = 0
) cert on (
cl.ref_table = 'Employee'
and cl.ref_oid = cert.emp_id
)
orr g ? r e ; (
cl.ref_tablea e # = 'EmpCertificate'
anW o  ` ? J xd cl.ref_oid = cert.cert_id
)
where
cl.last_upd_date >= '2013-11-07 15:03:00'
and cl.last_upd_date <= '20136 N @ i v E Q N-11-08 16:00:00';

1. 先运转一下，53条记载 1.87秒，又没有用聚合句子，比较慢

   53 rows in set (1.87 sec)

2. explain

简述一下履u x t y 3 j ! p ;行方案，首先mysql依据idx_last_upd_date索引扫描cm_log表取得379条记载；然后查表扫描了63727条记载，分为两部分，deriveR l 7 =d表明构造表，也便是不存在的表，能够简略了解成是一个句子构成的成果集，后边的数字表明句子的ID。derived2表明的是ID = 2的查询构造了虚拟表，而且回来了63727条记载。/ ; M d B 8 I y咱z * _ Y ^们再来看看ID = 2的句子究竟做了写什么回来了这么许多的数据，首先全表扫描employee表13317条记载，c z r然后依据索引emp_ce| 5 V P Y rrtificate_empid相关emp_certificate表，rows = 1n z & H % J f表明，每个相关都只确定了| ` z ? z一条记载，功率比较高。取得后，再和cm_log的379条记载依据规矩相关。从履行进程上能够看出回来了太多的数据，回来的数据绝大部分cm_log都用不到，由于cm_log只确定了379条记载。

怎么优化7 1 e U g ] o ?呢？能够看到咱们在运转完后仍是要和cm_log做join, 那么咱们能不能之前和cm_lo[ r Dg做joi| 4 Fn呢？仔细剖析句子不难发现，其根本思想是假如cm_log的ref9 h h Q N . |_table是EmK K r Q i 0pCertificate就相关emp_certificate表，假l u n如ref_t2 X { –able是Employee就相关employee表，咱们彻底能够拆成两部分，并用union连接起来，留意这里用union，而不用union all是由于原句子有“distinct”来得到唯一的记载，而union刚好具备了这种功能。假如原句子中没有disc Z Wtinct不需求去重，咱们就能够直接运用union all了，由于运用union需求去重的动作，会影响SQL功能。

优化过的句子如下：

   sele? I , *ct
emp.id
from
cm_log cl
inner join employee emp on cl.ref_table = 'Employee'J { 6 d
and cl.ref_oid = emp.id
where
cl.last_upd_dateo m i U w e D >=2 r ( / 7 Z 8 e '2013-11-07 15:03:00'
and cl.last_upd_date &7 S W Slt;= '2013-11-08 16:00:G ) @ F00'
and emp.is_deleted = 0
union
select
emp.id
from
cm_log cl
inner join emp_certificate ec on cl.ref_table =Q Z I K F m + k k 'EmpCe1 + & 4 Xrtificate[ # g Y 5 B !'
and cl.ref_oid = ec.id
innN  ^er join employee emI F 4 i D 0 Np on emp.id = ec.emp_id
wherc ` { I H R - Me
cl.las= K ! z R rt_upd_date >= '2013-11-07 15:03:00'
and cl.l; V @ A f 0ast_upd_date <= '2013-11-08 16:00:00'
and emp.is_deleted = 0

4. 不需求了解事务场景，只需求改# ~ 3 B ? {造的句子和改造之前的句子坚持成果共同

5. 现有索引能够满足，不需求建索引

6. 用改造后的句子试验一下，只需求10ms 降低了近200倍！

   

2. 清晰运用场景

举这个比方的目的在于推翻咱们对列的区分度的认知，一般上咱们认为区分度越高的列，越容易确定更少的记载，但在一些特殊的状况下，这种理B 3 ! n H 2 } h {论是有局限性的。

select
*c 5 K / k d l
from
stage_poi sp
where
sp.accurate_result = 1
and (
sp.syncu * (  r q s_status = 0
or sp.sync_status = 2
or sY U A y b U i sp.sync_stV [ M 5 Uatus = 4
);

1. 先看看运转多长时刻,951条数据6.22秒，真的很慢。

951 rowsF O { P 5 ^ in set (6.22 sec)

2. 先I s 6explain，rows达到了361万，tyh i * m 0pe = ALL表明是全表扫描。

3. 一切字段都运用查询回来记载数，由所以单表查询 0现已做过了951条。

4. 让. ` N 9 b ^ u J [explain的rows 尽量逼近951。

   看一下accurate_result = 1的记载数：

select count(*),accurate_rj 2 6 aesult from stage_poi  group by accurate_result+ ! 7 ^ G t r H;
+----------+-----------------+
| count(*) | accurate_result |
+----------+-----------------+
|     1023 |              -1 |
|  2114655 |               0 |
|   972815 |               1 |
+----------+-----------------+

咱们看到accuv { e Y : d W 2 {rate_result这个字段的区分度十分低，整个表只要-1, 0, 1三个值，加上索引也无法确# F W i定特别少数的数据。

再看一下sync_sta 5 k ` p Q |atus字段的状况：

select count(*),sync_status from stage_poi  group by s@ = o m ;ync_status;
+----------+------------T F J I Z * r `-+
| coun/ ) v 7 l Ht(*) |x 2 q ` 8 I - sync_status |
+----------+---------* $ W n * P h ^ y----+
|     3080 |           0 |
|  3085413 |           3 |
+----------+ 2 Q # |+-------------+

同样的区分度也很低，依据理Q C ( 6 @ x论，也不适合树立索引。

问题剖析到这，如同得出了这个表无法优化的定论，两个列的区分度都很低，即便加上索引也只能习惯这种状况，很难做普遍性的优化，比方当syx A p C } H Y O *nc_status 0、3散布的很均匀，那么确定记载也是百万等级的。

找事务方去沟通，看看运用场景。事务方是这么来运用这个SQL句子的，每隔五分钟会扫描契合条件的数据，处理完结后把sync_status这个字段变成] r = / A1, 五分钟契合条件的记载数并不会太多，1000个左右。了解了事务方的运用场景后，优化这个SQL就变得简略了，由于事务方保证了数据的不平衡，假如加上索引能够 0 p K过滤掉绝大部% D Q O {分不需求的数据。

5. 依据树立索引规矩，运用如下句子树立索引

alter table stk T u r p ; A * 2age_poi add index idx_acc_status(a^ e t J . )ccurateX ^ G M r , B m_result,sync# ] u & z + T_sta] ` `tus);

6. 观察预期成果,发现只需求200ms，快了30多倍。

952 rows in set (0.20 sec)

咱们再来回忆一下剖析问题的进程，单[ L [ e 0 = N表查询相对来说比较好优* ? l化，大部分时分只需求把where条件里边的字段a . G O Z ] l依R 9 m照规矩加上索引就好，假如仅仅这种“无~ E e # x脑”优化的话，显然$ / ^一些区分度十分低的列，不该该加索引的列也会被加上索引，这样会对刺进、更新功能形成严重的影响，同时也有或许影响其它的查询句+ h ] G 0 R子。所以咱们第4步调差SQL的运用场景十分关键，咱们只要知道这个事务场景，才干更好地辅助咱们更好的剖析和优化查询句子。

3. 无法优化的句子

select
c.id,
c.name,
c.position,
cr : # F $ ` W.sex,
c.phone,
c.office_phone,
c.feature_info,
c.birthday,
c.creator_id,
c.is_keyperson,
c.giveup_reason,
c.status,
c.data_source,
from_unixtime(c.created_time) as created_time,
from_unixtime(c.last_modY B O b , Z a _ified) as last_modified,
c.lastD : K L w : 9 ) @_modified_user_id
from
contact c
inner join contact_branch cb on c.id = cb.contact_id
inner join branch_user bu on cb.branch_id = bu.branch_id
and bu.status in (1, 2)
inner join org_emp_infr % E ^ = B ^ so oei on oei.data_id = bu.user_id
and oei.c m Q C Ynode_left >= 2875
and oei.node_right <= 10802
and oei.org_categor% (  [ v b B +y = - 1
order by
c.created_time desc
limit
0, 10;

仍是几个进程。

1. 先看句子运转多长时刻，10条记载用[ S _ I 1 s u了13秒，现已不可忍耐。

10 rows in set (13.06 sec)

2. explain

   

   从履行方案上看，mysql先查orgn J X ~ / /_emp_in2 $ efo表扫描8849记载，再用索引idx_userid_status相关branchy ] ` o N ( _ _ 3_user表，再用索引idx_branch_id相关contact_branch表，u g 2最后主键相关contact表。

   rows回来的都十分少，看不到有什么异常状况。咱们在看一下句子，发现后边有order by + limit组合，会不会是排序量太大搞的？所以咱们^ ; :简化SQL，去掉后边的e p ] m $ Iorder by 和 limit，看看究竟用了多少记载来排序。

select
count(*)
from
contact c
inner join
contact_branch cb
on  c.id = cb.contact_id
inner join
branch_user bu
on  cb.branch_id = bu.branch_id
and bu.status in (: # 3 ^ 3 I 8 L 3
1,
2)
inner join
org_emp_info oei^ Z W I T J #
on  oei.data_id = bu.user_id
and oei.node_left >* q U 9 ]= 2875
and oei.node_right <= 10802
and oei.ory q i i J =g_category = - 1
+----------+
|/ z q ` u G ~ count(*W ~ , ! W ]) |
+----------+
|   778p i 0878 |
+----------+
1 row in set (5.19 sec)

发现排序之前居然确定了778878条记载，假如针对70万的成果集排序，将是灾祸性的，怪不得这么慢，那咱们能不能换个思路，先依据contact的created_time排序，再来join会不会比较快呢？

所以改形成下面的句子，. J k [ y d *也能够用straight_join} _ A Y Z来优化：

select
c.id,
c.name,
c.position,
c.sex,
c.phone,
c.office_phone,
c.feature_ 2 ^ V  M 5info,
c.birti 1  hday,
c.creator_id,
c.is_keyperson,
c.giveup_reason,
c.status,
c.data_soQ 7  w i Zurce,
from_unixtime(c.created_timef l = m V x T J Q) as created_time,
from_unixtime(c.last_modified) as last_modifiedq j A,
c.last_modified_user_[ 8 F fid
from
contact c
where
exists (
select
1
from
contact_branch cb
inner join branch_user bu on cb.branch_id = bu.branch_id
and bu.status in (1, 2)
inner join org_emp_info oei on oei.data_id = bu.u% C  t 9 Gser_id
and oei.node_lS # , m # F ? h beft >= 2875
and oei.node_right <= 10802
and oei.org_category = - 1
where
c.id = cb.contact_id
)
order by& : f v r t . {
c.created_time desc
limit4 c . + T L E /
0, 10;

验证一下作用 预计在1ms内，提高了e F _13000多倍！

1l k K g C @0 rows in set (0.00 sec)

本认为至此大工告成，但咱们在前面的剖析中漏了一个细节* u (，先排序再join和先joh pin再排序理) ; t A论上开支是相同的，为何提高这么多是由于有一个limit！H d N ) v Y x大致履行进程是：mysql先按索引排序得到前10条记载，) * _ m E & b S {然后再去join过滤，当发现不够10条的时分，再次去10条，再次join，这显然在内层join过滤的数据十分多的时分，将是灾祸的，极端状况，内层一条数据都找不到，mysql还傻呵呵的每次取10条，几乎遍历了这个数据表！

​ 用不同参数的SQL试验下：

select
sql_no_cache c.id,
c.name,
c.position,
c.sex,
c.phone,
c.office_phone,
c.feature_info,c r e [ ^ 5
c.birthd8 ; zay,
c.creator_id,
c.is_keyperson,
c.giveup_reason,
c.statusv p M R 4,
c.data_source,
froo - t $ [ H { G 2m_unixtime(c.created_time) as created_time,
from_unixtime(c.last_modified)I %  as last_modified,
c.last_modified_user_id
from
contact c
where
exists (
select
1
from
contact_branch cb
inner join branch_user bu on cb.branch_id = bu.branch_id
and bu.status in (1, 2)
inner join org_emp_in= A K ` T fo oei on oei.data_id = bu.user_id
and oei.node_left >= 2875
and oei.node_right <= 2875
and oei.org_category = - 1
where
c.id = cb.contact_id
)
ordes I b L A Cr by
c.createdg * 0 | Q 9 b 3 &_time desc
limit
0, 1/ 6 R 2 v 60;
EmptyW h A y t set (2 min 18.99 sec)

2 min 18. 99 sec！比之前的状况还糟糕许多。由于mysN q a + nql的nested1 ( c ! ; k % loop机制，遇到这种状况，根本是无法优化的。这条句子终究也只能交给运用体系去优化自己的逻辑了。

经过这个比方咱们能够看到，并不是一切句子都能优化，而往往咱们q ~ %优化时，由于5 ) c ) 4 t R ? eSQL用例回归时落掉一些极端状况，会形成比本来还严重的后果。所以，榜首：不要盼望一切句子都能经过SQL优化，第二：不要过于自信，只针对具体case来优化，而忽略了更杂乱的状况。1 _ –

慢查询的事例就剖析到这儿，以上仅仅一 G 6 & = h {些比较典型的事例。咱们在优化进程中遇到过超过1O 3 } 0 b x . 0000行，涉及到16个表join的“废物SQL”，也遇到过线上线下数据库差异导致运用直接被慢查询拖死，也遇到过varchar等值比较没有写单引号，还遇到过笛卡尔积查询直接把从库搞死。再多的事例其实也仅仅一些经历的堆集，假如咱们熟悉查询优化器、D $ o r 8索引的内? U . N p s j S部原理，那么剖析这些事例就变得特别简略了。