MySQL中的InnoDB索引优化方法是什么

本文主要介绍“MySQL中的InnoDB索引优化方法是什么”。在日常操作中，相信很多人对于MySQL中的InnoDB索引优化方法是什么都有疑问。边肖查阅了各种资料，整理出简单易用的操作方法，希望能帮助大家解答“MySQL中InnoDB索引优化方法是什么”的疑惑！接下来，请和边肖一起学习！

半双工通信: MySQL数据传输采用半双工通信。同时，要么客户端向服务器发送数据，要么服务器向客户端发送数据。这两个动作不能同时发生。MySQL还要求客户端发送数据。一次性发送所有数据，等待服务器响应后再发送下一个数据。

顺序读写与随机读写:所有的数据库数据都需要删除。由于磁盘的物理结构和较长的寻道时间，顺序读写比随机读写高效得多。如果不太了解的话，平时可以考虑坐公交车。你愿意直接坐公交车吗(按顺序读写)？是不是转学比较好(随机读写)？

结果缓存: MySQL支持查询结果的缓存，默认情况下是关闭的。(提示，对于频繁更新的数据尽量不要使用MySQL自带的缓存，缓存失效会造成更多的性能浪费)

SQL查询流程:客户端发送查询SQL，查询SQL通过数据传输到服务器，查询结果首先被缓存。如果错过，将依次经过解析器、预处理器、优化器、执行计划、执行引擎和存储引擎，结果将被放入内存并返回给客户端。(以后写篇文章介绍一下)

索引（Index）:一种帮助MySQL高效获取数据的数据结构。MySQL中的大多数索引都使用多路径平衡查找树。

在优化索引之前，你需要知道索引的具体结构。根据不同的存储引擎，数据的存储结构是不同的。存储引擎主要使用InnoDB和MyISAM。

00-1010

InnoDB引擎索引说明

每个表都有一个聚集索引：

当主键存在时，主键被用作聚类索引，

当主键不存在时，第一个没有空值的唯一索引将用作聚集索引。

当上述索引都不存在时，MySQL将创建一个带有隐藏字段rowid的聚集索引。

每个表的数据根据聚类索引聚集在一起，形成B树。其中非索引数据装载在最后一个叶节点上，并且在叶节点之间有有序的指针。

群集索引图标1

聚簇索引

表中，除了聚集索引外，其他非聚集索引都变成了二级索引或辅助索引，辅助索引中的叶节点不再挂载非索引数据，而是存储聚集索引的索引值。

辅助索引图标2

辅助索引

特殊辅助索引：联合索引，B树的节点存储的不是一列数据，而是多列数据，按照定义的顺序组成一个节点。

联合索引图标3

结合B树存储结构的一些知识，从实践的角度分析了如何优化SQL。这也是SQL优化器的功能。

00-1010

联合索引

首先我们要明白B-tree是一个有序的多路径平衡搜索树，也就是在插入之前需要排序，为了平衡还需要分页、旋转等操作。

先说序列本身。序列是比较的结果。如何比较？MySQL在建立数据的时候一定要指定编码格式和排序方式，然后才有办法比较顺序。不管主键是什么类型，数字和字符串都会被编码和排序。主键的可比性决定了主键的效率。

我们来谈谈顺序意义。仔细看簇索引图1的叶子节点，也就是最后一层，它是页面的有序列表(图中放在一起的数据称为页面)。每次插入都是为了在记录数据之前确定主键的位置。叶节点是否有序插入决定了主键的效率。主键的顺序决定了磁盘读写的顺序(顺序写入比随机写入高效得多)。

以上两点足以说明MySQL中主键有序性的重要性。所以选择主键优先选择有序主键，自增主键就是有序主键。当然，不要那么绝对。当数据量过少时，效率差距基本看不到。

顺便说一句，经常被问到的UUID主键和自增主键的选择，在数据量太小或者业务僵化的时候可以使用。当数据量太大时，建议自动增加主键，不仅是因为顺序，还因为字符串的存储空间比整数大。

00-1010表示之前的顺序，这里使用的是较低的顺序。索引树的叶节点是有序的。按顺序匹配的顺序越多，查询效率越高。所以在排序的时候，尽量遵循

所使用的索引进行排序，也因此全表查询时默认是主键排序。如果查询条件中涉及到了其他索引则默认以首个索引的顺序为主。如果不确定使用了什么索引，则应该主动指定排序列

同样基于以上，推荐在频繁排序或者分组的列上建立索引

索引树中数据如何获取

首先先明确一点，索引树中数据分为2种，1：索引树非叶子节点存储的是索引数据，2：索引叶子节点存储的是索引数据和表非索引数据。

其次也要明确：聚簇索引是一颗存有全表数据的索引树，每个表都是必有的。其他辅助索引每建立一个就会多一颗索引树，只是和图示一样叶子节点不存储数据

因此获取SQL查询数据应该从2个角度分析

• 从不同索引树角度

• 查询聚簇索引树

• 查询非聚簇索引树

• 从查询数据所在位置角度

• 查询索引树中非叶子节点数据（即索引数据），不查其他数据

• 查询叶子节点中的数据（包含索引和非索引数据）。

SQL索引优化注重点之一在数据所处位置

如果查询的数据全部在索引树非叶子节点（即查询索引列）时，此时效率是最高的，因为节点的有序性，通过高效算法能很快找到数据完成查询，这种查询称为覆盖索引查询。这点告诉使用者：尽量不要使用select *,同时也应该知道，如果一个表列全是索引，那一定会走索引。（别再说什么 not null 、!= 一定不走索引的问题了）

如果查询的数据不在索引树非叶子节点（即查询非索引列）时，注意此时SQL优化器很有可能会优化书写的SQL，导致最终执行的SQL和客户端传输的SQL不一致。

先说下此时正规的数据查找流程：

• 如果查询条件存在索引，则使用第一个索引条件列（优化后的）去首次加载数据行

• 索引为聚簇索引，则在聚簇索引树上，根据算法查询到索引所处的叶子节点位置，把该位置的对应数据获取即可

• 索引为非聚簇索引，则在非聚簇索引树上，根据算法查询引所处的叶子节点位置，获取到该位置上的聚簇索引值，然后拿到该值在聚簇索引树上定位其位置，再把聚簇索引树叶子节点上对应的数据获取即可。从非聚簇索引树再到聚簇索引树的过程称为回表。

• 如果查询条件不存在索引

• 由于没有索引，所以会去聚簇索引树的非叶子节点数据处进行全表扫描，逐个匹配，直至扫描完毕获取到数据返回

从聚簇索引中获取到的数据行，会加载到内存中，然后在进行

where其他条件的过滤，最后才返回过滤后的数据，

这点告诉使用者：where条件中首个条件应尽量精确匹配（例如主键、高离散度索引列）数据。

索引树的分裂、节点移除

索引树中每个页存储的数据个数是固定的，例如4个，当该页新增数据时，如果数据已满4个，则需要分裂为2个页，每页还是4个来保证。

节点移除时，索引树会进行旋转来达到平衡。具体流程可自行查询平衡树。这里只需要知道：索引树调整很浪费时间，开销很大。

因此频繁更新的列，不适合作为主键或者索引

最左匹配原则

问个索引优化，都说最左匹配原则，可是否知道为什么是最左匹配，如何匹配？

在上面说顺序时提到了如何排序，这里如何匹配也是类似，例如abc和abd如何匹配，这里说下通俗理解（不一定是实现），把这两个字符逐个通过编码、排序获取排序值，假设a编码后排序值为 32，b 编码后排序值为33，c 编码后排序值为 34，设d编码后排序值为35;匹配时先对a比较==，如果不等则不必再进行匹配，如果相等则比较b、然后c，最终发现35>34于是结果就是不匹配。第一步的a的匹配就是最左开始匹配原则。

最左匹配的应用：

• like匹配，只有左边字符确定才能支持最左匹配原则，即不支持%xxx匹配。

• 联合索引匹配，联合索引中非叶子节点中数据存储是安装联合索引定义的顺序组合成一个节点的，例如

  index0,index1,index2一旦顺序不对则不能进行匹配。但是记住一点：组合后的索引节点是按照一个节点在索引中排序的，也就是哪怕匹配了一个索引也是能提高效率的。例如：聚合索引a,b,c查询条件where a=1 and c=1，此时a=1是能走聚合索引的，但是c就不行了，此时等同于%c。这里也有个坑，会问这个查询是否走索引，回答是走索引（部分走也是走）。还有查询条件中遇到范围查询（like != > < 等）则会中止后续匹配。直接理解为联合索引就是一个拼接后的字符列索引，遇到范围查询则会导致开销指数级变大。

索引条件下推ICP

在索聚簇索引树查询数据行之前，匹配的数据行越少，越精确则查询效率越高。ICP（index_condition_pushdown）技术就是优化的这部分，旨在尽量减少数据行加载到内存中。在InnoDB引擎中ICP只支持联合索引，因为聚簇索引能直接锁定要查询的数据行，无法继续再筛选（聚簇索引只有一个索引），而联合索引则是至少2个索引，在第一个索引匹配的行数和后续其他联合索引匹配的行数处理后，再回表到聚簇索引树中查询数据，这样聚簇索引树中的数据行就会缩减，从而提高效率。ICP技术是默认开启的。explain提示信息为：Using index condition，设置参数为：index_condition_pushdown

ICP应用：

• 尽量建立聚合索引而不是多个单索引，where条件后面按照聚合索引列作为条件

函数对索引条件的影响

内置函数

MySQL函数的contract，date_format，count等

函数区分为2种，1：该函数可以得到确定的结果，这种称为确定性函数，2：该函数不能得到确定的结果，具体的结果由参数决定，这种称为不确定性函数

表达式

计算表达式，1+1、2*3等

函数和表达式位置分为条件左侧和右侧，条件左侧即条件列，右侧为查询条件。

• 对于右侧：

• 确定性函数大部分可以使用索引，例如： contract、pow

• 不确定性函数基本不能使用索引，例如： rand,uuid

• 对于左侧：

• 一定导致索引失效,而且任何对左侧索引列的处理都会导致索引失效，包含编码格式、函数、表达式计算等。 例如：where age + 10 = 30 应写为where age = 30 + 10这种写法没问题，MySQL会自动优化为where age = 40

NULL的优化

MySQL支持索引列的null查询，且支持is not null和is null,属于范围查询。出现索引失效的一般都是因为回表开销过大导致的，毕竟数据为null为少数或者多数。

非空约束列的is null查询不会走索引，因为有比索引更高效的查询方式。

开销优化

MySQL的优化器是基于开销的，它对客户端的SQL会解析出多条同样效果的SQL，最终选择的是开销最小的SQL。基本所有的优化都基于此。

离散度体现的开销

例如：在性别sex列表建立索引，然而sex值只有0和1。如果表中数据全是男或者全是女，优化器会觉得全表扫描会由于索引查询，毕竟不用从索引树的根节点逐个比较。

开销大小对索引而已外观表现为索引列数据的离散度，离散度相当于count(distinct(column_name))/count(*)。对于这种离散度低的列不建议建立索引

全表扫描开销

例如：聚合索引a,b,c，在查询条件中使用where a=1 or d=1,这里d为非索引列，此时会导致匹配d时必须全表扫描，既然都全表扫描了说明索引树中的数据行都加载到了内存，因此没必要通过索引去过滤，定位聚簇索引树的位置了，于是最终采用的是全表扫描而不会走索引。注：如果表所有列都是索引则全表扫描也是走索引树扫描。覆盖索引优先级比全表扫描优先级高

联合索引顺序开销

例如：聚合索引a,b,c，在查询条件中书写顺序where a=1 and b=1 and c=1和书写顺序where c=1 and a=1 and b=1不影响索引使用，SQL优化器会分析出最小的开销，就是按照索引定义顺序来纠正查询条件。符合最左匹配原则才有意义。

其他索引优化

MySQL优化点很多，只是列一些常见的优化

隐式转换

字符串类型的列一定要加单引号'',否则会隐式转换为数字，导致索引失效

负向索引

负向索引（<> 、!= 、not in）有可能使用索引，但是大部分不会使用索引，这要基于SQL优化器优化了。例如对于索引列a,如果值全是1（离散度过低），此时<>1 、!=、not in（1） 都是会走索引的。注意不走索引便意味着全表扫描。

对于负向索引（not like） 一定不走索引。

强制索引

当SQL优化器优化后不是想要的SQL时，可以指定强制索引（force index(idx_name)）来让SQL使用指定的索引查询，不一定会采用，只有多个执行计划中有这个索引的执行计划时才有效（毕竟强制一个不查询的索引也没意义）。

其他优化

查询结果越少越好

前面提到MySQL是半双工通信，客户端需要等待服务端处理好结果且返回之后才能继续。如果查询结果很大，会导致后续请求阻塞。故善用limit，不要select *，也注意insert into xxx select xxx这个select结果也是越少越好

子查询越少越好，最好不存在

子查询会导致多次查询数据行，浪费IO。个人建议即使多次请求也比子查询好。不仅能看懂，效率也不一定降低。

查询SQL越精确越好

SQL越精确，在进行查找时读取的数据行越少，查询效率越高。

尽量不要随机读取

基于磁盘性能，随机读取效率差，索引树查询开销大，不建议

常量查询效率比索引查询高

能使用常量查询的尽量使用常量查询

例如:只是确认是否存在，没必要查询其他字段

select 1 from user where name='xx' limit 1

例如 非空约束列查询is null

时间字段尽量使用数据库函数

虽然说大部分数据库和线上库都会统一时间，但是防止埋坑，而且数据库自身的效率会高点，当然这点性能没什么影响。如果没必要还是建议使用数据库自身的时间函数来填充时间字段。

update user set modify_time=now()

使用IN代替OR

针对同列的IN 和 OR 如果查询字段是索引列，则二者性能基本一致，否则In的效率随着数据量增大会比OR越来越高，

针对IN，MySQL会估算in范围的条数开销，in的范围越大开销越大，特别是不是唯一列的开销更大，此时可以考虑join等方式是否可以试下，毕竟in其实也是等值比较，join连接条件也是等值比较。当然也可以考虑exists

针对不同列的OR,例如where a=1 or b=1，会被优化为union，尽量主动书写union

select a,b from  source where a=0  or  b=2

推荐写法

select a,b from  source where a=0  
union
select a,b from  source where b=2

In和Exists

使用IN时要保证IN中的总数据量小且in之后的数据量也很小才能操作其效率高。Exists则是exists语句中的数据量大，但是匹配后小则效率高。

在考虑in和exists时，思考下哪个遍历的少，哪个效率就高。

平时常见的索引优化暂时就罗列这些，一旦想起来再来补充吧！

补充

Like优化

经过数据验证，like在千万级数据时效率很差，反而没有instr函数效率高。

select xxx  from xxx  where xxx like '%abc%'

不如走索引的以下语句好

select xxx  from xxx  where xxx like 'abc%'

走索引的like不如以下语句好

select xxx  from xxx  where instr( xxx, 'abc' ) > 0

到此，关于“MySQL中的InnoDB索引优化方法是什么”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注网站，小编会继续努力为大家带来更多实用的文章！