MySQL, Oracle, Linux, 软件架构及大数据技术知识分享平台

网站首页 > 精选文章 / 正文

MySQL索引从基础到原理，看这一篇就够了

2025-02-14 23:27 huorong 精选文章 2 ℃ 0 评论

?

索引是SQL优化中最重要的手段之一，本文从基础到原理，带你深度掌握索引。

?

索引思维导图

一、索引基础

1、什么是索引

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构，索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时，索引对于性能的影响愈发重要。索引优化应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高好几个数量级。

通俗来讲，索引类似文章的目录，用来提高查询的效率。

目录

2、索引分类

「常见的索引类型有：主键索引、唯一索引、普通索引、全文索引、组合索引」

2.1、主键索引

当一张表，把某个列设为主键的时候，则该列就是主键索引

create?table?a?(??
?id?int?primary?key?auto_increment,??
?name?varchar(20)?not?null?default?''??
);??

这里id就是表的主键，如果当创建表时没有指定主键索引，也可以在创建表之后添加：

alter?table?table_name?add?primary?key?(column_name);

1.2、普通索引

用表中的普通列构建的索引，没有任何限制

create?index?索引名?on?table_name(column1);
alter?table?table_name?add?index?索引名(column1);

1.3、全文索引

全文索引主要针对文本文件，比如文章，标题。在MySQL5.6之前，只有MyISAM存储引擎支持全文索引，MySQL5.6之后InnoDB存储引擎也支持全文索引。

create?table?c(??
?id?int?primary?key?auto_increment?,??
?title?varchar(20),??
?content?text,??
?fulltext(title,content)??
)?engine=myisam?charset?utf8;?

insert?into?c(title,content)?values??
????('MySQL?Tutorial','DBMS?stands?for?DataBase?...'),??
????('How?To?Use?MySQL?Well','After?you?went?through?a?...'),??
????('Optimizing?MySQL','In?this?tutorial?we?will?show?...'),??
????('1001?MySQL?Tricks','1.?Never?run?mysqld?as?root.?2.?...'),??
????('MySQL?vs.?YourSQL','In?the?following?database?comparison?...'),??
????('MySQL?Security','When?configured?properly,?MySQL?...');?

1.4、唯一索引

见名知义，索引列中的值必须是唯一的，但是允许为空值。d表中name就是唯一索引，相比主键索引，主键字段不能为null，也不能重复

create?table?d(
?id?int?primary?key?auto_increment?,?
?name?varchar(32)?unique
????)?

1.5、组合索引

用多个列组合构建的索引，这多个列中的值不允许有空值。

ALTER?TABLE?'table_name'?ADD?INDEX?index_name('col1','col2','col3')；

?

组合索引遵循“最左前缀”原则，使用时最好把最常用作为检索或排序的列放在最左，依次递减。组合索引相当于建立了col1，col1col2，col1col2col3 三个索引，而col2或者col3是不能使用索引的。在使用组合索引的时候可能因为列名长度过长而导致索引的key太大，导致效率降低，在允许的情况下，可以只取col1和col2的前几个字符作为索引。
ALTER TABLE 'table_name' ADD INDEX index_name(col1(4),col2（3))；

表示使用col1的前4个字符和col2的前3个字符作为索引

?

3、索引机制浅析

我们这里先简单剖析一下索引的机制，为接下来的深入做一些铺垫。

3.1、索引加快查询的原理

传统的查询方法，是按照表的顺序遍历的，不论查询几条数据，MySQL需要将表的数据从头到尾遍历一遍。

在我们添加完索引之后，MySQL一般通过BTREE算法生成一个索引文件，在查询数据库时，找到索引文件进行遍历，「使用能够大幅地查询的效率的折半查找的方式」，找到相应的键从而获取数据。

image-20210302210641120

3.1、索引的代价

创建索引是为产生索引文件的，占用磁盘空间。索引文件是一个二叉树类型的文件，可想而知我们的DML操作((数据操作语言,对表记录的(增、删、改)操作)同样也会对索引文件进行修改，所以性能会相应的有所下降。

二、索引存储数据结构

上面已经说到，索引实际上是数据库中满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法 。

可能我们都知道，MySQL索引是B+树数据结构，当然，实际上索引还有哈希表、有序数组 等常见的数据结构。

1、哈希表

哈希表是一种以键-值（key-value）存储数据的结构，我们只要输入待查找的值即key，就可以找到其对应的值即Value。哈希的思路很简单，把值放在数组里，用一个哈希函数把key换算成一个确定的位置，然后把value放在数组的这个位置。

不可避免地，多个key值经过哈希函数的换算，会出现同一个值的情况。处理这种情况的一种方法是，拉出一个链表。

哈希索引

所以，需要注意，哈希表后的链表并不是有序的，区间查询的话需要扫描链表，所以「哈希表这种结构适用于只有等值查询的场景」，比如Memcached及其他一些NoSQL引擎。

2、有序数组

另外一个大家比较熟悉的数组结构，「有序数组在等值查询和范围查询场景中的性能都非常优秀」。

有序数组链表

如果仅仅看查询效率，有序数组是非常棒的数据结构。但是，在需要更新数据的时候就麻烦了，你往中间插入一个记录就必须得挪动后面所有的记录，成本太高。

所以，「有序数组索引只适用于静态存储引擎」，比如你要保存的是2017年某个城市的所有人口信息，这类不会再修改的数据。

这两种都不是最主要的索引，常见的索引使用的数据结构是树结构，树是数据结构里相对复杂一些的数据结构，我们来一步步认识索引的树结构。

3、二分查找

二分查找也称折半查找（Binary Search），它是一种效率较高的查找方法。但是，折半查找要求线性表必须采用顺序存储结构，而且表中元素按关键字有序排列。

?

查找方法：首先，假设表中元素是按升序排列，将表中间位置记录的关键字与查找关键字比较，如果两者相等，则查找成功；否则利用中间位置记录将表分成前、后两个子表，如果中间位置记录的关键字大于查找关键字，则进一步查找前一子表，否则进一步查找后一子表。重复以上过程，直到找到满足条件的记录，使查找成功，或直到子表不存在为止，此时查找不成功。

?

上面提到的有序数组的等值查询和比较查询效率非常高，但是更新数据存在问题。

为了支持频繁的修改，比如插入数据，我们需要采用链表。链表的话，如果是单链表，它的查找效率还是不够高。

所以，有没有可以使用二分查找的链表呢?

为了解决这个问题，BST(Binary Search Tree)也就是我们所说的二叉查找树诞生了。

4、二叉查找树

「二叉树具有以下性质：左子树的键值小于根的键值，右子树的键值大于根的键值。」

如下图所示就是一棵二叉查找树，

二叉查找树

在这种比较平衡的状态下查找时间复杂度是O(log(n))。

但是二叉查找树存在一个问题：在某些极端情况下会退化成链表。

同样是2,3,4,6,7,8这六个数字，如果我们插入的数据刚好是有序的，那它就变成这样

二叉查找树退化

这个时候，二叉查找树查找的时间复杂度就和链表一样，是O(n)。

造成它“叉劈”的原因是什么呢? 因为左右子树深度差太大，这棵树的左子树根本没有节点——也就是它不够平衡。

所以，我们有没有左右子树深度相差不是那么大，更加平衡的树呢? ——那就就是平衡二叉树，叫做 Balanced binary search trees，或者 AVL 树。

5、AVL 树

AVL Trees (Balanced binary search trees) 平衡二叉树的定义:左右子树深度差绝对值不能超过 1。

例如左子树的深度是 2，右子树的深度只能是 1 或者 3。这个时候我们再按顺序插入 2,3,4,6,7,8，就不会“叉劈”

AVL树

AVL树的平衡是怎么做到的呢？主要用到了两个操作左旋、右旋。

  • 插入 1、2、3。当我们插入了 1、2 之后，如果按照二叉查找树的定义，3 肯定是要在 2 的右边的，这个时候根节点 1 的右节点深度会变成 2，但是左节点的深度是 0，因为它没有子节点，所以就会违反平衡二叉树的定义。那应该怎么办呢?因为它是右节点下面接一个右节点，右–右型，所以这个时候我们要把 2 提上去，这个操作叫做左旋。

左旋

  • 同样的，如果我们插入3、2、1，这个时候会变成左左型，就会发生右旋操作，把 2提上去。

右旋

既然平衡二叉树能保持平衡，不会退化，那么我们用平衡二叉树存储索引可以吗？——可以的。

二叉树存储索引

当我们用树的结构来存储索引的时候，访问一个节点就要跟磁盘之间发生一次 IO。InnoDB 操作磁盘的最小的单位是一页(或者叫一个磁盘块)。与主存不同，磁盘I/O存在机械运动耗费，因此磁盘I/O的时间消耗是巨大的。

IO

所以如果每个节点存储的数据太少，从索引中找到我们需要的数据，就要访问更多的节点，意味着跟磁盘交互次数就会过多。

那么解决方案是什么？

  • 让每个节点存储更多的数据。
  • 让节点上有更多的关键字。

节点上的关键字的数量越多，我们的指针数也越多，也就是意味着可以有更多的分叉(我们把它叫做“路数”)。

因为分叉数越多，树的深度就会减少(根节点是 0)。这样，树就从瘦高变成了矮胖。

这个时候，我们的树就不再是二叉了，而是多叉，或者叫做多路。

6、多路平衡查找树（B-Tree）

接下来看一下多路平衡查找树，也就是B树。

B树是一种多叉平衡查找树，如下图主要特点：

  • B树的节点中存储着多个元素，每个内节点有多个分叉。
  • 节点中的元素包含键值和数据，节点中的键值从大到小排列。也就是说，在所有的节点都储存数据。
  • 父节点当中的元素不会出现在子节点中。
  • 所有的叶子结点都位于同一层，叶节点具有相同的深度，叶节点之间没有指针连接。

B-树索引结构

以上图为例，我们来简单看几个查询：

  • 如果查找key<17，就走左边子节点；
  • 如果查找17
  • 如果查找key>35，就走右边子节点；
  • 如果查找key=17，直接命中；
  • 如果查找key=35，直接命中；

B树看起来很完美，到这就结束了吗？并没有。

?

B树不支持范围查询的快速查找，你想想这么一个情况如果我们想要查找10和35之间的数据，查找到15之后，需要回到根节点重新遍历查找，需要从根节点进行多次遍历，查询效率有待提高。

如果data存储的是行记录，行的大小随着列数的增多，所占空间会变大。这时，一个页中可存储的数据量就会变少，树相应就会变高，磁盘IO次数就会变大

?

所以接下来就引入我们的终极数据结构——B+树。

7、加强版多路平衡查找树(B+Tree)

B+树，作为B树的升级版，在B树基础上，MySQL在B树的基础上继续改造，使用B+树构建索引。B+树和B树最主要的区别在于「非叶子节点是否存储数据」的问题

?

B树：非叶子节点和叶子节点都会存储数据。

B+树：只有叶子节点才会存储数据，非叶子节点至存储键值。叶子节点之间使用双向指针连接，最底层的叶子节点形成了一个双向有序链表。

?

来看一下InnoDB里的B+树的具体存储结构：

B+树索引

来说一下这张图的重点：

  • 最外面的方块，的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（粉色所示）和指针（黄色/灰色所示），如根节点磁盘包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。真实的数据存在于叶子节点即3、4、5……、65。非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。
  • 叶子节点之间使用双向指针连接，最底层的叶子节点形成了一个双向有序链表。

7.1、存储容量

举个例子:假设一条记录是 1K，一个叶子节点(一页)可以存储 16 条记录。非叶子节点可以存储多少个指针?

B+树存储容量

假设索引字段是 bigint 类型，长度为 8 字节。指针大小在 InnoDB 源码中设置为 6 字节，这样一共 14 字节。非叶子节点(一页)可以存储 16384/14=1170 个这样的 单元(键值+指针)，代表有 1170 个指针。

树深度为 2 的时候，有 1170^2 个叶子节点，可以存储的数据为 1170*1170*16=21902400。

在查找数据时一次页的查找代表一次 IO，也就是说，一张 2000 万左右的表，查询数据最多需要访问 3 次磁盘。

所以在 InnoDB 中 B+ 树深度一般为 1-3 层，它就能满足千万级的数据存储。

7.2、查询效率

我们来看一下 B+Tree 的数据搜寻过程:


  • 例如我们要查找 35，在根节点就找到了键值，但是因为它不是页子节点，所以会继续往下搜寻，25 是[17,35)的左闭右开的区间的临界值，所以会走中间的子节点，然 后继续搜索，它又是[28,34)的左闭右开的区间的临界值，所以会走左边的子节点，最后在叶子节点上找到了需要的数据。

  • 如果是范围查询，比如要查询从 22 到 60 的数据，当找到 22 之后，只需要顺着节点和指针顺序遍历就可以一次性访问到所有的数据节点，这样就极大地提高 了区间查询效率(不需要返回上层父节点重复遍历查找)。
  • 3）添加了指向相邻叶节点的指针**，形成了带有顺序访问指针的B+Tree，这样做是为了**提高区间查找的效率，只要找到第一个值那么就可以顺序的查找后面的值。

7.3、B+树特点总结

总结一下，InnoDB 中的 B+Tree 的特点:


  • 它是 B Tree 的变种，B Tree 能解决的问题，它都能解决。B Tree 解决的两大问题是什么?(每个节点存储更多关键字；路数更多)
  • 2)扫库、扫表能力更强(如果我们要对表进行全表扫描，只需要遍历叶子节点就可以 了，不需要遍历整棵 B+Tree 拿到所有的数据)

  • B+Tree 的磁盘读写能力相对于 B Tree 来说更强(根节点和枝节点不保存数据区， 所以一个节点可以保存更多的关键字，一次磁盘加载的关键字更多)

  • 排序能力更强(因为叶子节点上有下一个数据区的指针，数据形成了链表)

  • 效率更加稳定(B+Tree 永远是在叶子节点拿到数据，所以 IO 次数是稳定的)

三、聚簇索引和非聚簇索引

MySQL中最常见的两种存储引擎分别是MyISAM和InnoDB，分别实现了非聚簇索引和聚簇索引。

首先要介绍几个概念，在索引的分类中，我们可以按照索引的键是否为主键来分为“主键索引”和“辅助索引”，使用主键键值建立的索引称为“主键索引”，其它的称为“辅助索引”。因此主键索引只能有一个，辅助索引可以有很多个。

1、MyISAM——非聚簇索引

MyISAM存储引擎采用的是非聚簇索引，非聚簇索引的主键索引和辅助索引基本上是相同的，只是主键索引不允许重复，不允许空值，他们的叶子结点的key都存储指向键值对应的数据的物理地址。

非聚簇索引的数据表和索引表是分开存储的。

非聚簇索引中的数据是根据数据的插入顺序保存。因此非聚簇索引更适合单个数据的查询。插入顺序不受键值影响。

?

思考：既然非聚簇索引的主键索引索引和辅助索引指向相同的内容，为什么还要辅助索引呢？索引不就是用来查询的吗，用在哪些地方呢？不就是WHERE和ORDER BY 语句后面吗，那么如果查询的条件不是主键怎么办呢，这个时候就需要辅助索引了。

?

聚簇索引

2、InnoDB——聚簇索引

聚簇索引的「主键索引的叶子结点存储的是键值对应的数据本身，辅助索引的叶子结点存储的是键值对应的数据的主键键值」。因此主键的值长度越小越好，类型越简单越好。

聚簇索引的数据和主键索引存储在一起。

聚簇索引

从上图中可以看到辅助索引的叶子节点的data存储的是主键的值，主键索引的叶子节点的data存储的是数据本身，也就是说数据和索引存储在一起，并且索引查询到的地方就是数据（data）本身，那么索引的顺序和数据本身的顺序就是相同的。

因为聚簇辅助索引存储的是主键的键值，因此可以在数据行移动或者页分裂的时候降低成本，因为这时不用维护辅助索引。但是由于主键索引存储的是数据本身，因此聚簇索引会占用更多的空间。

聚簇索引在插入新数据的时候比非聚簇索引慢很多，因为插入新数据时需要检测主键是否重复，这需要遍历主索引的所有叶节点，而非聚簇索引的叶节点保存的是数据地址，占用空间少，因此分布集中，查询的时候I/O更少，但聚簇索引的主索引中存储的是数据本身，数据占用空间大，分布范围更大，可能占用好多的扇区，因此需要更多次I/O才能遍历完毕。

四、索引使用原则

1、列的离散度

第一个叫做列的离散度，我们先来看一下列的离散度的公式:

count(distinct(column_name)) : count(*)

列的全部不同值和所有数据行的比例。数据行数相同的情况下，分子越大，列的离散度就越高。

mysql>?SELECT?*?FROM?`test`.`user`?ORDER?BY?`id`?LIMIT?10?OFFSET?0;
+----+-----------+--------+-------------+
|?id?|?name??????|?gender?|?phone???????|
+----+-----------+--------+-------------+
|??1?|?秦啭??????|??????0?|?13601722591?|
|??2?|?李镒榘????|??????0?|?15204160836?|
|??3?|?陈艮??????|??????0?|?13601994087?|
|??4?|?沈夷旌????|??????0?|?15507785988?|
|??5?|?朱桐泰????|??????1?|?13201268193?|
|??6?|?周韬蕊????|??????1?|?15705478612?|
|??7?|?冯叻加????|??????0?|?13705834063?|
|??8?|?王焓??????|??????1?|?15006956358?|
|??9?|?黄芪??????|??????0?|?15108012536?|
|?10?|?吴笄游????|??????0?|?15301860708?|
+----+-----------+--------+-------------+
10?rows?in?set?(0.00?sec)

简单来说，如果列的重复值越多，离散度就越低，重复值越少，离散度就越高。

了解了离散度的概念之后，我们再来思考一个问题，我们在 name 上面建立索引和 在 gender 上面建立索引有什么区别。

当我们用在 gender 上建立的索引去检索数据的时候，由于重复值太多，需要扫描的行数就更多。例如，我们现在在 gender 列上面创建一个索引，然后看一下执行计划。

ALTER?TABLE?user?ADD?INDEX?idx_user_gender?(gender);?--?耗时比较久?
EXPLAIN?SELECT?*?FROM?`user`?WHERE?gender?=?0;

+----+-------------+-------------+------------+------+-----------------+-----------------+---------+-------+---------+----------+-------+
|?id?|?select_type?|?table?????|?partitions?|?type?|?possible_keys???|?key????????|?key_len?|?ref???|?rows????|?filtered?|?Extra?|
+----+-------------+-------------+------------+------+-----------------+-----------------+---------+-------+---------+----------+-------+
|??1?|?SIMPLE??????|?user??????|?NULL???????|?ref??|?idx_user_gender?|?idx_user_gender?|?2???????|?const?|?2492574?|???100.00?|?NULL??|
+----+-------------+-------------+------------+------+-----------------+-----------------+---------+-------+---------+----------+-------+
1?row?in?set,?1?warning?(0.00?sec)

而 name 的离散度更高，比如“陈艮”的这名字，只需要扫描一行。

ALTER?TABLE?user?ADD?INDEX?idx_user_name?(name);?

EXPLAIN?SELECT?*?FROM?`user`?WHERE?name?=?'陈艮';

+----+-------------+-------------+------------+------+----------------------------+----------+---------+-------+------+----------+-------+
|?id?|?select_type?|?table?????|?partitions?|?type?|?possible_keys?|?key??????|?key_len?|?ref???|?rows?|?filtered?|?Extra?|
+----+-------------+-------------+------------+------+----------------------------+----------+---------+-------+------+----------+-------+
|??1?|?SIMPLE??????|?user??????|?NULL???????|?ref??|?idx_name?|?idx_name?|?1023????|?const?|????1?|???100.00?|?NULL??|
+----+-------------+-------------+------------+------+----------------------------+----------+---------+-------+------+----------+-------+
1?row?in?set,?1?warning?(0.00?sec)

查看表上的索引，Cardinality [kɑ:d?’n?l?t?]代表基数，代表预估的不重复的值的数量。索引的基数与表总行数越接近，列的离散度就越高。

mysql>?show?indexes?from?user;
+-------------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|?Table?|?Non_unique?|?Key_name??????????|?Seq_in_index?|?Column_name?|?Collation?|?Cardinality?|?Sub_part?|?Packed?|?Null?|?Index_type?|?Comment?|?Index_comment?|
+-------------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|?user??|??????????0?|?PRIMARY???????????|????????????1?|?id??????????|?A?????????|?????4985145?|?????NULL?|?NULL???|??????|?BTREE??????|?????????|???????????????|
|?user??|??????????1?|?idx_name??????????|????????????1?|?name????????|?A?????????|?????2605146?|?????NULL?|?NULL???|?YES??|?BTREE??????|?????????|???????????????|
|?user??|??????????1?|?idx_user_gender???|????????????1?|?gender??????|?A?????????|???????????1?|?????NULL?|?NULL???|?YES??|?BTREE??????|?????????|???????????????|
|?user??|??????????1?|?comidx_name_phone?|????????????1?|?name????????|?A?????????|?????2595718?|?????NULL?|?NULL???|?YES??|?BTREE??????|?????????|???????????????|
|?user??|??????????1?|?comidx_name_phone?|????????????2?|?phone???????|?A?????????|?????4972647?|?????NULL?|?NULL???|?YES??|?BTREE??????|?????????|???????????????|
+-------------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5?rows?in?set?(0.00?sec)

如果在索引 B+Tree 结构里面的重复值太多，MySQL 的优化器发现走索引跟使用全表扫描差不了多少的时候，就算建了索引，也不一定会走索引。

2、组合索引最左匹配

前面我们说的都是针对单列创建的索引，但有的时候我们的多条件查询的时候，也会建立组合索引。单列索引可以看成是特殊的组合索引。

比如我们在 user 表上面，给 name 和 phone 建立了一个组合索引。

ALTER?TABLE?user?add?INDEX?comidx_name_phone?(name,phone);

联合索引

组合索引在 B+Tree 中是复合的数据结构，它是按照从左到右的顺序来建立搜索树的 (name 在左边，phone 在右边)。

从这张图可以看出来，name 是有序的，phone 是无序的。当 name 相等的时候， phone 才是有序的。

这个时候我们使用 where name= ‘wangwu‘ and phone = ‘139xx ‘去查询数据的时候， B+Tree 会优先比较 name 来确定下一步应该搜索的方向，往左还是往右。如果 name 相同的时候再比较 phone。但是如果查询条件没有 name，就不知道第一步应该查哪个 节点，因为建立搜索树的时候 name 是第一个比较因子，所以用不到索引。

2.1、什么时候用到组合索引

所以，我们在建立组合索引的时候，一定要把最常用的列放在最左边。比如下面的三条语句，能用到组合索引吗?

  • 1)使用两个字段，可以用到组合索引:
mysql>?EXPLAIN?SELECT?*?FROM?user?WHERE?name=?'陈艮'?AND?phone?=?'13601994087';
+----+-------------+-------------+------------+------+----------------------------+-------------------+---------+-------------+------+----------+-------+
|?id?|?select_type?|?table???????|?partitions?|?type?|?possible_keys????|?key???????????????|?key_len?|?ref?????????|?rows?|?filtered?|?Extra?|
+----+-------------+-------------+------------+------+----------------------------+-------------------+---------+-------------+------+----------+-------+
|??1?|?SIMPLE??????|?user_innodb?|?NULL???????|?ref??|?comidx_name_phone?|?comidx_name_phone?|?1070????|?const,const?|????1?|???100.00?|?NULL??|
+----+-------------+-------------+------------+------+----------------------------+-------------------+---------+-------------+------+----------+-------+
1?row?in?set,?1?warning?(0.00?sec)
  • 2)使用左边的 name 字段，可以用到组合索引:
mysql>?EXPLAIN?SELECT?*?FROM?user?WHERE?name=?'陈艮';
+----+-------------+-------------+------------+------+----------------------------+----------+---------+-------+------+----------+-------+
|?id?|?select_type?|?table???????|?partitions?|?type?|?possible_keys?????|?key??????|?key_len?|?ref???|?rows?|?filtered?|?Extra?|
+----+-------------+-------------+------------+------+----------------------------+----------+---------+-------+------+----------+-------+
|??1?|?SIMPLE??????|?user_innodb?|?NULL???????|?ref??|?comidx_name_phone?|?idx_name?|?1023????|?const?|???19?|???100.00?|?NULL??|
+----+-------------+-------------+------------+------+----------------------------+----------+---------+-------+------+----------+-------+
1?row?in?set,?1?warning?(0.00?sec)
  • 3)使用右边的 phone 字段，无法使用索引，全表扫描:
mysql>?EXPLAIN?SELECT?*?FROM?user?WHERE??phone?=?'13601994087';
+----+-------------+-------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|?id?|?select_type?|?table?|?partitions?|?type?|?possible_keys?|?key??|?key_len?|?ref??|?rows????|?filtered?|?Extra???????|
+----+-------------+-------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|??1?|?SIMPLE??????|?user?|?NULL???????|?ALL??|?NULL??????????|?NULL?|?NULL????|?NULL?|?4985148?|????10.00?|?Using?where?|
+----+-------------+-------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1?row?in?set,?1?warning?(0.00?sec)

2.2、如何创建组合索引

当创建(a,b,c)联合索引时，相当于创建了(a)单列索引，(a,b)组合索引以及(a,b,c)组合索引，想要索引生效的话,只能使用 a和a,b和a,b,c三种组合；当然，b,a也是好使的，因为sql会对它优化。

用 where b=? 和 where b=? and c=? 和 where a=? and c=?是不能使用到索引。不能不用第一个字段，不能中断。

这里就是 MySQL 组合索引的最左匹配原则。

3、覆盖索引

3.1、回表

在聚簇索引里，通过辅助索引查找数据，先通过索引找到主键索引的键值，再通过主键值查出索引里面没有的数据，它比基于主键索引的查询多扫描了一棵索引树，这个过程就叫回表。

例如:select * from user where name = ‘lisi’;

回表

3.2、覆盖索引

在辅助索引里面，不管是单列索引还是联合索引，如果 select 的数据列只用从索引中就能够取得，不必从数据区中读取，这时候使用的索引就叫做覆盖索引，这样就避免了回表。

我们先来创建一个联合索引:

--?创建联合索引
ALTER?TABLE?user?add?INDEX?'comixd_name_phone'?('name','phone');

这三个查询语句都用到了覆盖索引:

EXPLAIN?SELECT?name,phone?FROM?user?WHERE?name=?'陈艮'?AND?phone?=?'13601994087';
EXPLAIN?SELECT?name?FROM?user?WHERE?name=?'陈艮'?AND?phone?=?'13601994087';
EXPLAIN?SELECT?phone?FROM?user?WHERE?name=?'陈艮'?AND?phone?=?'13601994087';

Extra 里面值为“Using index”代表使用了覆盖索引。

mysql>?EXPLAIN?SELECT?name?FROM?user_innodb?WHERE?name=?'陈艮'?AND?phone?=?'13601994087';
+----+-------------+-------------+------------+------+----------------------------+-------------------+---------+-------------+------+----------+-------------+
|?id?|?select_type?|?table???????|?partitions?|?type?|?possible_keys??????????????|?key???????????????|?key_len?|?ref?????????|?rows?|?filtered?|?Extra???????|
+----+-------------+-------------+------------+------+----------------------------+-------------------+---------+-------------+------+----------+-------------+
|??1?|?SIMPLE??????|?user_innodb?|?NULL???????|?ref??|?idx_name,comidx_name_phone?|?comidx_name_phone?|?1070????|?const,const?|????1?|???100.00?|?Using?index?|
+----+-------------+-------------+------------+------+----------------------------+-------------------+---------+-------------+------+----------+-------------+
1?row?in?set,?1?warning?(0.00?sec)

select * ，用不到覆盖索引。

很明显，因为覆盖索引减少了 IO 次数，减少了数据的访问量，可以大大地提升查询效率。

4、索引条件下推(ICP)

「“索引条件下推”，称为」 Index Condition Pushdown (ICP)，这是MySQL提供的用某「一个」索引对「一个」特定的表从表中获取元组”，注意我们这里特意强调了“一个”，这是因为这样的索引优化不是用于多表连接而是用于单表扫描，确切地说，是单表利用索引进行扫描以获取数据的一种方式。 它的作用如下

  • 一是说明减少完整记录（一条完整元组）读取的个数；
  • 二是说明对于InnoDB聚集索引无效，只能是对SECOND INDEX这样的非聚簇索引有效。

关闭 ICP:

set?optimizer_switch='index_condition_pushdown=off';

查看参数:

show?variables?like?'optimizer_switch';

现在我们要查询所有名字为陈艮，并且手机号码后四位为4087这个人。查询的 SQL:

SELECT?*?FROM?user?WHERE?name=?'陈艮'??and?phone?LIKE?'%4087'?;

这条 SQL 有两种执行方式:

  • 1、根据组合索引查出所有名字是’陈艮’的二级索引数据，然后回表，到主键索引上查询全部符合条件的数据(19 条数据)。然后返回给 Server 层，在 Server 层过滤出手机号码后四位为4087这个人。
  • 2、根据组合索引查出所有名字是’陈艮’的二级索引数据(19 个索引)，然后从二级索引 中筛选出手机号码后四位为4087的索引(1 个索引)，然后再回表，到主键索引上查询全部符合条件的数据(1 条数据)，返回给 Server 层。

很明显，第二种方式到主键索引上查询的数据更少。

注意，索引的比较是在存储引擎进行的，数据记录的比较，是在 Server 层进行的。而当 phone 的条件不能用于索引过滤时，Server 层不会把 phone 的条件传递 给存储引擎，所以读取了两条没有必要的记录。

这时候，如果满足 name=’陈艮’的记录有 100000 条，就会有 99999 条没有 必要读取的记录。

执行以下 SQL，Using where:

mysql>?EXPLAIN?SELECT?*?FROM?user?WHERE?name=?'陈艮'?AND?phone?LIKE?'%4087';
+----+-------------+-------------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------------+
|?id?|?select_type?|?table???????|?partitions?|?type?|?possible_keys?????|?key???????????????|?key_len?|?ref???|?rows?|?filtered?|?Extra???????|
+----+-------------+-------------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------------+
|??1?|?SIMPLE??????|?user_innodb?|?NULL???????|?ref??|?comidx_name_phone?|?comidx_name_phone?|?1023????|?const?|???19?|????11.11?|?Using?where?|
+----+-------------+-------------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------------+
1?row?in?set,?1?warning?(0.00?sec)

Using Where 代表从存储引擎取回的数据不全部满足条件，需要在 Server 层过滤。

先用 name条件进行索引范围扫描，读取数据表记录，然后进行比较，检查是否符合 phone LIKE ‘%4087’ 的条件。此时 19 条中只有 1 条符合条件。

五、 索引创建使用总结

因为索引对于改善查询性能的作用是巨大的，所以我们的目标是尽量使用索引。

5.1. 索引的创建

根据上一节的分析，我们总结出索引创建的一些注意点：

  • 1、在用于 where 判断 order 排序和 join 的(on)字段上创建索引
  • 2、索引的个数不要过多。——浪费空间，更新变慢。
  • 3、区分度低的字段，例如性别，不要建索引。——离散度太低，导致扫描行数过多。
  • 4、频繁更新的值，不要作为主键或者索引。——页分裂
  • 5、组合索引把散列性高(区分度高)的值放在前面。——最左前缀匹配原则
  • 6、创建复合索引，而不是修改单列索引。——组合索引代替多个单列索引（由于MySQL中每次只能使用一个索引，所以经常使用多个条件查询时更适合使用组合索引）
  • 7、过长的字段，怎么建立索引?——使用短索引。
    当字段值比较长的时候，建立索引会消耗很多的空间，搜索起来也会很慢。我们可以通过截取字段的前面一部分内容建立索引，这个就叫前缀索引。
create?table?shop(address?varchar(120)?not?null);?
alter?table?shop?add?key?(address(12));
  • 8、不建议用无序的值(例如身份证、UUID )作为索引——当主键具有不确定性，会造成叶子节点频繁分裂，出现磁盘存储的碎片化

5.2. 什么时候会用不到索引

  • 1、索引列上使用函数(replace\SUBSTR\CONCAT\sum count avg)、表达式、 计算(+ – * /):
explain?SELECT?*?FROM?'t2'?where?id+1?=?4;
  • 2、字符串不加引号，出现隐式转换
explain?SELECT?*?FROM?'user'?where?name?=?136;?

explain?SELECT?*?FROM?'user'?where?name?=?'136';
  • 3、like 条件中前面带%

where 条件中 like abc%，like %2673%，like %888 都用不到索引吗?为什么?

explain?select?*?from?user?where?name?like?'wang%';?

explain?select?*?from?user?where?name?like?'%wang';

过滤的开销太大，所以无法使用索引。这个时候可以用全文索引。

  • 4、负向查询

NOT LIKE 不能:

explain?select?*from?employees?where?last_name?not?like?'wang'

!= (<>)和 NOT IN 在某些情况下可以:

explain?select?*?from?user?where?id?not?in?(1)?
explain?select?*?from?user?where?id?<>?1
  • 5.索引不会包含有NULL值的列

只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

  • 6，排序的索引问题

MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

注意一个 SQL 语句是否使用索引，跟数据库版本、数据量、数据选择度都有关系。

其实，用不用索引，最终都是优化器说了算。优化器是基于什么的优化器? 基于 cost 开销(Cost Base Optimizer)，它不是基于规则(Rule-Based Optimizer)，也不是基于语义。怎么样开销小就怎么来。

?

以上是我对索引相关知识的整理，希望你能有所收获，参考如下！

?

参考：

【1】：《高性能MySQL》

【2】：MySQL索引原理及慢查询优化

【3】：极客时间 《MySQL45讲》

【4】：MySQL索引背后的数据结构及算法原理

【5】：MySQL索引原理，一篇从头到尾讲清楚

【6】：Mysql 四种常见的索引

【7】：MySQL探秘(三)MySQL索引原理

【8】：一口气搞懂MySQL索引所有知识点

原文参考自CSDN三分恶：
https://blog.csdn.net/sinat_40770656/article/details/114339535

Tags：mysql 绝对值

猜你喜欢

控制面板
您好，欢迎到访网站！
  查看权限
网站分类
最新留言