MySql之索引底层深入解析

最新推荐文章于 2021-03-07 21:54:51 发布

东山富哥

最新推荐文章于 2021-03-07 21:54:51 发布

阅读量555

点赞数

分类专栏： mysql 文章标签： mysql索引底层解析

本文链接：https://blog.csdn.net/u014636209/article/details/104061838

版权

mysql 专栏收录该内容

26 篇文章 4 订阅

订阅专栏

文章目录

1.索引概念
2.索引类型与创建
3.索引数据结构存储模型推演
4. 索引使用原则
5.索引的创建与使用

DROP TABLE IF EXISTS `user_innodb`;
CREATE TABLE `user_innodb` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `gender` tinyint(1) DEFAULT NULL,
  `phone` varchar(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_user_gender` (`gender`),
  KEY `idx_user_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;

在这张表里，我插入了100万的数据

1.索引概念

在这里插入图片描述

1.数据库索引，是数据库的一个排序的数据结构，以协助快速查询、 更新数据库表中数据。

在这里插入图片描述

1.首先数据是以文件的形式存放在磁盘上面的，每一行数据都有它的磁盘地址。如果没有索引的话，
  要从 500 万行数据里面检索一条数据，只能依次遍历这张表的全部数据， 直到找到这条数据。 
2.但是有了索引之后，只需要在索引里面去检索这条数据就行了，因为它是一种特殊的专门用来快速检索的数据结构，
  我们找到数据存放的磁盘地址以后，就可以拿到数据 了。就像我们从一本 500 页的书里面去找特定的一小节的内容，
  肯定不可能从第一页开 始翻。那么这本书有专门的目录，它可能只有几页的内容，它是按页码来组织的，
  可以根据拼音或者偏旁部首来查找，只要确定内容对应的页码，就能很快地找到我们想要的内容。

2.索引类型与创建

在这里插入图片描述

2.1.普通索引（Normal）又称非唯一索引（None-Unique）

1.普通索引，就是最常规的索引，对于某一列的值来讲，可以重复，对于数据值没有限制，又称非唯一索引
2.例如我们在user表中对name字段进行创建索引idx_user_name(Nonoe-Unique索引)，也就是说name这一列的值是可以重复的
  因为对于用户来讲，名字是可能重复的；

在这里插入图片描述

2.2.唯一索引（Unique）

1.唯一索引实际上跟普通索引是相对应的，意味着这一列的值是不能重复的；
2.例如user表中的身份证号字段idno是不能重复的，因此我们创建唯一索引；

在这里插入图片描述

2.3.全文索引（Fulltext）

针对比较大的数据，比如我们存放的是消息内容，有几 KB 的数 据的这种情况，如果要解决 like 查询效率低的问题，可以创建全文索引。
只有文本类型 的字段才可以创建全文索引，比如 char、varchar、text。

在这里插入图片描述

select * from gpmysql.user u where match(u.remark) against('胡天钰' IN NATURAL LANGUAGE MODE);

在这里插入图片描述

3.索引数据结构存储模型推演

3.1.二分查找

1.比如我买了一部手机3000块钱，现在找一个人来猜一下，看看几次能猜对？
2.1000低了，猜10000，高了猜5000还高，猜2000低了，猜2500，如此循环N次

在这里插入图片描述

1.二分查找也叫折半查找，这种方式对于应排好序的数据是比较好的一种方式，如同我们的书本一样
  考虑用有序数组作为索引的数据结构，可以考虑这种方式；
2.有序数组的等值查询和比较查询效率非常高，但是更新数据的时候会出现一个问题， 可能要挪动大量的数据（改变 index）
  所以只适合存储静态的数据  
3.为了支持频繁的修改，比如插入数据，我们需要采用链表。链表的话，如果是单链 表，它的查找效率还是不够高。 
  所以，有没有可以使用二分查找的链表呢？ 
  为了解决这个问题，BST（Binary Search Tree）也就是我们所说的二叉查找树诞生了。

3.2.二叉查找树（BST Binary Search Tree）

1.二叉查找树的特点:
  左子树所有的节点都小于父节点，右子树所有的节点都大于父节点。投影到平面以后，就是一个有序的线性表。
  关于二叉树的一些知识大家可以去了解一下数据结构这块；

在线模拟二叉查找树

https://www.cs.usfca.edu/~galles/visualization/BST.html

1.我们现在以此插入下面的数字：19，12，17，15，13，1，4，6，2，23，21，27

在这里插入图片描述

1.二叉查找树既能够实现快速查找，又能够实现快速插入。 但是二叉查找树有一个问题： 就是它的查找耗时是和这棵树的深度相关的，
  在最坏的情况下时间复杂度会退化成 O(n)。
2.比如我们插入1-7，这时候会出现下面的情况  
3.因为左右子树深度差太大，这棵树的左子树根本没有节点——也就是它不够平衡。 所以，我们有没有左右子树深度相差不是那么大，
  更加平衡的树呢？ 这个就是平衡二叉树，叫做 Balanced binary search trees，或者 AVL 树（AVL 是 发明这个数据结构的人的名字）。

在这里插入图片描述

3.3. 平衡二叉树（AVL Tree）（左旋、右旋）

https://www.cs.usfca.edu/~galles/visualization/AVLtree.html

3.3.1 概念

1.AVL Trees (Balanced binary search trees) 平衡二叉树的定义：左右子树深度差绝对值不能超过1
 (比如左子树的深度是 2，右子树的深度只能是 1 或者 3)。

插入1，2，3，4，5，6，7，8
在这里插入图片描述

案例说明

1.我们在https://www.cs.usfca.edu/~galles/visualization/AVLtree.html这里进行模拟
2.我们插入1，2，3三个数字，然后看一下这个过程中平衡二叉树的变化

插入1
在这里插入图片描述
插入2，由于2比1大，根据二叉树的定义，右边节点都比左边的节点大，结果如下

插入3，根据二叉树的定义，3比2大，所以肯定在2的右边

1.这个时候根节点 1 的右节点深度会变成 2，但是左节点的深度是 0，因为它没有子节点，所以就会违反平衡二叉树的定义。 
2.那应该怎么办呢？因为它是右节点下面接一个右节点，右-右型，所以这个时候我们 要把2提上去，这个操作叫做左旋。
3.所以为了保持平衡，AVL 树在插入和更新数据的时候执行了一系列的计算和调整的 操作。

在这里插入图片描述

3.3.2.概念延伸-MySql

在平衡二叉树中，一个节点，它的大小是一个固定的单位，作为索引应该存储什么内容？

它应该存储三块的内容： 
1.第一个是索引的键值。比如我们在 id 上面创建了一个索引，我在用 where id =1 的条件查询的时候就会找到索引里面的id的这个键值。 
2.第二个是数据的磁盘地址，因为索引的作用就是去查找数据的存放的地址。 
3.第三个，因为是二叉树，它必须还要有左子节点和右子节点的引用，这样我们才能找到下一个节点。

在这里插入图片描述

3.3.3.AVL 树用于存储索引数据

1.当我们用树的结构来存储索引的时候，访问一个节点就要跟磁盘之间发生一次 IO。 
2.InnoDB 操作磁盘的最小的单位是一页（或者叫一个磁盘块），大小是 16K(16384 字节)。 那么，一个树的节点就是 16K 的大小。 
  如果我们一个节点只存一个键值+数据+引用，例如整形的字段，可能只用了十几个 或者几十个字节，它远远达不到 16K 的容量，
  所以访问一个树节点，进行一次 IO 的时候,浪费了大量的空间。 所以如果每个节点存储的数据太少，从索引中找到我们需要的数据，
  就要访问更多的节点，意味着跟磁盘交互次数就会过多;
3.如果是机械硬盘时代，每次从磁盘读取数据需要 10ms 左右的寻址时间，交互次数越多，消耗的时间就越多。
4.实际上说的通俗点，我们如何把平衡二叉树的深度给压缩一下，减少一下磁盘交互的次数是比较关键的；

3.4.多路平衡查找树（B Tree）（分裂、合并）

3.4.1.演示地址

https://www.cs.usfca.edu/~galles/visualization/BTree.html
在这里插入图片描述

3.4.2.概述

1.B树拥有平衡二叉树的所有特点，即：B树在枝节点和叶子节点存储键值、数据地址、节点引用；
2.另外B树有一个特殊的点：分叉数（路数）永远比关键字数多 1。
  比如我们画的这棵树，每个节点存储两个关键字，那么就会有三个指针指向三个子节；

在这里插入图片描述

比如我们要在这张表里面查找 15。 
因为 15 小于 17，走左边。 
因为 15 大于 12，走右边。
在磁盘块 7 里面就找到了 15，只用了 3 次 IO。

3.4.3.B Tree 又是怎么实现一个节点存储多个关键字，还保持平衡的呢？跟 AVL 树有什么区别？

比如 Max Degree（路数）是 3 的时候，我们插入数据 1、2、3，在插入3的时候，本来应该在第一个磁盘块，
但是如果一个节点有2个关键字的时候，意味着有3个指针,子节点会变成3路，所以这个时候必须进行分裂。
把中间的数据2提上去，把1和3变成2的子节点。

在这里插入图片描述

我们继续进行插入4，5，6，7，大家可以看到其索引的逻辑存储结构是不断地变化的
在这里插入图片描述

同样的，如果我们删除某一个节点，其存储结构又会发生很大的变化

1.从这个里面我们也能看到，在更新索引的时候会有大量的索引的结构的调整，
  所以解释了为什么我们不要在频繁更新的列上建索引，或者为什么不要更新主键。 
2.节点的分裂和合并，其实就是InnoDB页的分裂和合并(因为不同的节点的存储的数据大小不一样，会导致在不同的页上)。

关于InnoDB的逻辑存储结构，大家可以参考下面的连接
https://blog.csdn.net/u014636209/article/details/104090886

3.5.B+树（加强版多路平衡查找树）

3.5.1.演示地址

https://www.cs.usfca.edu/~galles/visualization/Algorithms.html
https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html
在这里插入图片描述

3.5.2.概述

1.实际上B+树是MySql InnoDB存储引擎对B Tree的一个优化;
2.它的关键字的数量是跟路数相等的； 
3.B+Tree 的根节点和枝节点中都不会存储数据，只有叶子节点才存储数据。搜索到关键字不会直接返回,会到最后一层的叶子节点。
  比如我们搜索 id=28，虽然在第一层直接命中了，但是全部的数据在叶子节点上面，所以我还要继续往下搜索，一直到叶子节点。
4.B+Tree 的每个叶子节点增加了一个指向相邻叶子节点的指针，它的最后一个数据会指向下一个叶子节点的第一个数据，形成了一个有序链表的结构。 
5.它是根据左闭右开的区间[)来检索数据。

在这里插入图片描述

**举个例子：假设一条记录是 1K，一个叶子节点（一页）可以存储 16 条记录。非叶子节点可以存储多少个指针？ **

假设索引字段是bigint 类型，长度为8字节。指针大小在InnoDB源码中设置为6字节，这样一共14字节。
非叶子节点（一页）可以存储 16384/14=1170个这样的单元（键值+指针），代表有1170个指针。 
树深度为2的时候，有 1170^2个叶子节点 ，可以存储的数据为 1170*1170*16=21902400。

在查找数据时一次页的查找代表一次IO，也就是说，一张2000万左右的表，查询数据最多需要访问3次磁盘。 
所以在 InnoDB 中 B+ 树深度一般为 1-3 层，它就能满足千万级的数据存储。

在这里插入图片描述
我们来看一下 B+Tree 的数据搜寻过程：

 1）比如我们要查找 28，在根节点就找到了键值，但是因为它不是页子节点，所以 会继续往下搜寻，28 是[28,66)的左闭右开的区间的临界值，
   所以会走中间的子节点，然 后继续搜索，它又是[28,34)的左闭右开的区间的临界值，所以会走左边的子节点，最后 在叶子节点上找到了需要的数据。 
 2）第二个，如果是范围查询，比如要查询从 22 到 60 的数据，当找到 22 之后，只 需要顺着节点和指针顺序遍历就可以一次性访问到所有的数据节点，
   这样就极大地提高了区间查询效率（不需要返回上层父节点重复遍历查找）。

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

1)它是 B Tree 的变种，B Tree 能解决的问题，它都能解决。B Tree 解决的两大问题 是什么？（每个节点存储更多关键字；路数更多） 
2)扫库、扫表能力更强（如果我们要对表进行全表扫描，只需要遍历叶子节点就可以 了，不需要遍历整棵 B+Tree 拿到所有的数据） 
3) B+Tree 的磁盘读写能力相对于 B Tree 来说更强（根节点和枝节点不保存数据区， 所以一个节点可以保存更多的关键字，一次磁盘加载的关键字更多） 
4)排序能力更强（因为叶子节点上有下一个数据区的指针，数据形成了链表） 
5)效率更加稳定（B+Tree 永远是在叶子节点拿到数据，所以 IO 次数是稳定的）

3.5.4.B +树的落地形式

3.5.4.1.MySQL 架构

1.MySQL是一个支持插件式存储引擎的数据库。在MySQL里面，每个表在创建的时候都可以指定它所使用的存储引擎。 
2.这里我们主要关注一下最常用的两个存储引擎，MyISAM和InnoDB的索引的实现;

3.5.4.2.MySQL 数据存储文件

查看数据的存储目录位置

show VARIABLES LIKE 'datadir';

在这里插入图片描述

1.我们创建的每一个数据库都会在上面的目录上创建一个以这个数据库的名字为名的目录，比如我们创建一个gp-myslq数据库，目录如下
/usr/local/mysql/data/gp-dmysql （这里注意下由于是特殊字符“-”变为了@002d）

在这里插入图片描述

3.5.2.1.MyISAM

1.每张MyISAM的表有三个文件（.frm、.MYD、.MYI）

在这里插入图片描述

3.5.2.1.1.那我们怎么根据索引找到数据呢？

1.MyISAM的B+Tree里面，叶子节点存储的是数据文件对应的磁盘地址。
  所以从索引文件.MYI中找到键值后，会到数据文件.MYD中获取相应的数据记录。
  (在MyISAM存储引擎的主键索引和辅助索引一样，都是这种查找)

在这里插入图片描述

3.5.2.2.InnoDB

1.每张InnoDB的表有两个文件（.frm和.ibd）

在这里插入图片描述

InnoDB 只有一个文件（.ibd 文件），那索引放在哪里呢？ 
1.在InnoDB里面，它是以主键为索引来组织数据的存储的，所以索引文件和数据文件是同一个文件，都在.ibd 文件里面。 
2.在InnoDB的主键索引的叶子节点上，它直接存储了我们的数据。

在这里插入图片描述

3.5.2.2.1.聚集索引（聚簇索引）

概念特点

1.索引键值的逻辑顺序跟表数据行的物理存储顺序是一致的；
2.在 InnoDB 里面，它组织数据的方式叫做叫做（聚集）索引组织表（clustered index organize table），
  所以主键索引是聚集索引，非主键都是非聚集索引。

3.5.2.2.2.主键索引

1.主键索引，实际上就是我们的二叉树的索引逻辑的先后存储的存储结构，跟磁盘里的数据的存储结构是一致的，
  就跟字典的那个目录是的，字典的前面的那页目录是按拼音(a-z)排序的，内容也是按拼音排序(a-z)的;

3.5.2.2.3.辅助索引

1.辅助索引存储的是辅助索引和主键值。如果使用辅助索引查询，会根据主键值在主键索引中查询，最终取得数据。

在这里插入图片描述

辅助索引存储的是辅助索引和主键值，为啥不直接存储磁盘地址呢？

1.主键的数据类型比较大，比存地址更消耗空间;
2.因为InnoDB存储殷勤有分叉和合并的操作，导致其磁盘的地址有可能是有变化的，所以如果辅助索引存储磁盘地址，
  后面由于分叉和合并，其磁盘地址时有可能会变化的，所以直接存储了主键的值；

另一个问题，如果一张表没有主键怎么办？

1、如果我们定义了主键(PRIMARY KEY)，那么InnoDB会选择主键作为聚集索引。 
2、如果没有显式定义主键，则InnoDB 会选择第一个不包含有NULL值的唯一索引作为主键索引。 
3、如果也没有这样的唯一索引，则 InnoDB 会选择内置6字节长的ROWID作为隐藏的聚集索引，它会随着行记录的写入而主键递增。

select _rowid,u.* from user_innodb u

在这里插入图片描述

4. 索引使用原则

1.并不是索引创建的越多越好；

4.1.列的离散（sàn）度

1.第一个叫做列的离散度，我们先来看一下列的离散度的公式： count(distinct(column_name)) : count(*)，
  列的全部不同值和所有数据行的比例.数据行数相同的情况下，分子越大，列的离散度就越高。

4.1.1.列的重复值比较高，离散度比较低

1.比如我们在user_innodb表中字段性别gender创建索引idx_user_gender，我们来测试一下；

ALTER TABLE user_innodb DROP INDEX idx_user_gender; # 如果没有创建过这个索引，可以忽略这一步
ALTER TABLE user_innodb ADD INDEX idx_user_gender (gender);
EXPLAIN SELECT * FROM `user_innodb` WHERE gender = 0;

在这里插入图片描述

概要描述：
id:选择标识符
select_type:表示查询的类型。
table:输出结果集的表
partitions:匹配的分区
type:表示表的连接类型
possible_keys:表示查询时，可能使用的索引
key:表示实际使用的索引
key_len:索引字段的长度
ref:列与索引的比较
rows:扫描出的行数(估算的行数)
filtered:按表条件过滤的行百分比
Extra:执行情况的描述和说明

1.如果在 B+Tree 里面的重复值太多，MySQL 的优化器发现走索引跟使用全表扫描差 不了多少的时候，就算建了索引，也不一定会走索引。
2.这个给我们的启发是什么？建立索引，要使用离散度（选择度）更高的字段

4.1.2.列的重复值很低，离散度比较高

1.比如我们在user_innodb表中字段姓名name创建索引idx_user_name，我们来测试一下；

ALTER TABLE user_innodb DROP INDEX idx_user_name; 
ALTER TABLE user_innodb ADD INDEX idx_user_name (name); 
EXPLAIN SELECT * FROM `user_innodb` WHERE name = '孙卵';

在这里插入图片描述

4.1.3.查看所有的索引

show indexes from user_innodb;

在这里插入图片描述

4.2.联合索引最左匹配

前面我们说的都是针对单列创建的索引，但有的时候我们的多条件查询的时候，也会建立联合索引。
单列索引可以看成是特殊的联合索引。 比如我们在user_innodb表上面，给name和phone建立了一个联合索引。

ALTER TABLE user_innodb DROP INDEX comidx_name_phone; 
ALTER TABLE user_innodb add INDEX comidx_name_phone (name,phone);
show indexes from user_innodb;

在这里插入图片描述

联合索引在B+Tree 中是复合的数据结构，它是按照从左到右的顺序来建立搜索树的（name 在左边，phone 在右边）。 
从这张图可以看出来，name 是有序的，phone 是无序的。当name相等的时候，phone才是有序的。
这个时候我们使用where name= '高新富' and phone = '136xx '去查询数据的时候， 
B+Tree 会优先比较name来确定下一步应该搜索的方向，往左还是往右。如果name相同的时候再比较 phone。
但是如果查询条件没有 name，就不知道第一步应该查哪个节点，因为建立搜索树的时候 name 是第一个比较因子，所以用不到索引。

4.2.1.什么时候使用联合索引

我们在建立联合索引的时候，一定要把最常用的列放在最左边

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

1）使用两个字段，可以用到联合索引：

EXPLAIN SELECT * FROM user_innodb WHERE name= '权亮' AND phone = '15204661800';

在这里插入图片描述

1.从上面的结果知道使用了 联合索引

2）使用左边的 name 字段，可以用到联合索引：

EXPLAIN SELECT * FROM user_innodb WHERE name= '权亮'

在这里插入图片描述

1.也使用了联合索引

3）使用右边的 phone 字段，无法使用索引，全表扫描：

在这里插入图片描述

1.未使用联合索引

4.2.2.如何创建联合索引

4.2.2.1.背景

1.如果我们发现下面的SQL执行比较慢，如何进行优化呢？
  SELECT * FROM user_innodb WHERE name= ? AND phone = ?; 
  SELECT * FROM user_innodb WHERE name= ?;

4.2.2.2.推演

1.根据上面两个SQL的问题，我们可能会创建两个索引
  CREATE INDEX idx_name_phone on user_innodb(name,phone);
  CREATE INDEX idx_name on user_innodb(name);

1.当我们创建一个联合索引的时候，按照最左匹配原则，用左边的字段name去查询的时候，也能用到索引，
所以第2个索引创建idx_name完全没必要。 
idx_name_phone相当于建立了两个联合索引(name),(name,phone)。 
如果我们创建三个字段的索引 index(a,b,c)，相当于创建三个索引： 
   index(a)
   index(a,b) 
   index(a,b,c)
2.用 where b=?和where b=? and c=? 和 where a=? and c=?是不能使用到索引 的。不能不用第一个字段，不能中断。 
  这里就是MySQL联合索引的最左匹配原则

4.3.覆盖索引

1.了解覆盖索引之前，我们先了解一个概念 “回表”

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

在这里插入图片描述

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

-- 创建联合索引 
ALTER TABLE user_innodb DROP INDEX comixd_name_phone; 
ALTER TABLE user_innodb add INDEX `comixd_name_phone` (`name`,`phone`);

EXPLAIN SELECT name,phone FROM user_innodb WHERE name= 'gaoxinfu' AND phone = ' 13666666666'; 
EXPLAIN SELECT name FROM user_innodb WHERE name= 'gaoxinfu' AND phone = ' 13666666666'; 
EXPLAIN SELECT phone FROM user_innodb WHERE name= 'gaoxinfu' AND phone = ' 13666666666';

Using where 表示使用覆盖索引
在这里插入图片描述

4.4.索引条件下推（ICP）

4.4.1.官网

https://dev.mysql.com/doc/refman/5.7/en/index-condition-pushdown-optimization.html

4.4.2.SQL准备

DROP TABLE employees;
CREATE TABLE `employees` (
	`emp_no` INT ( 11 ) NOT NULL,
	`birth_date` date NULL,
	`first_name` VARCHAR ( 14 ) NOT NULL,
	`last_name` VARCHAR ( 16 ) NOT NULL,
	`gender` enum ( 'M', 'F' ) NOT NULL,
	`hire_date` date NULL,
	PRIMARY KEY ( `emp_no` ) 
) ENGINE = INNODB DEFAULT CHARSET = latin1;
# 在 last_name 和 first_name 上面创建联合索引。
ALTER TABLE employees ADD INDEX idx_lastname_firstname ( last_name, first_name );
INSERT INTO `employees` (`emp_no`,`birth_date`,`first_name`,`last_name`,`gender`,`hire_date`) VALUES (1,NULL,'698','liu','F',NULL); 
INSERT INTO `employees` (`emp_no`,`birth_date`,`first_name`,`last_name`,`gender`,`hire_date`) VALUES (2,NULL,'d99','zheng','F',NULL); 
INSERT INTO `employees` (`emp_no`,`birth_date`,`first_name`,`last_name`,`gender`,`hire_date`) VALUES (3,NULL,'e08','huang','F',NULL); 
INSERT INTO `employees` (`emp_no`,`birth_date`,`first_name`,`last_name`,`gender`,`hire_date`) VALUES (4,NULL,'59d','lu','F',NULL); 
INSERT INTO `employees` (`emp_no`,`birth_date`,`first_name`,`last_name`,`gender`,`hire_date`) VALUES (5,NULL,'0dc','yu','F',NULL); 
INSERT INTO `employees` (`emp_no`,`birth_date`,`first_name`,`last_name`,`gender`,`hire_date`) VALUES (6,NULL,'989','wang','F',NULL); 
INSERT INTO `employees` (`emp_no`,`birth_date`,`first_name`,`last_name`,`gender`,`hire_date`) VALUES (7,NULL,'e38','wang','F',NULL); 
INSERT INTO `employees` (`emp_no`,`birth_date`,`first_name`,`last_name`,`gender`,`hire_date`) VALUES (8,NULL,'0zi','wang','F',NULL); 
INSERT INTO `employees` (`emp_no`,`birth_date`,`first_name`,`last_name`,`gender`,`hire_date`) VALUES (9,NULL,'dc9','xie','F',NULL); 
INSERT INTO `employees` (`emp_no`,`birth_date`,`first_name`,`last_name`,`gender`,`hire_date`) VALUES (10,NULL,'5ba','zhou','F',NULL);

4.4.3.查看ICP是否开启(默认是开启的状态)

# index_condition_pushdown=on
show variables like 'optimizer_switch';

在这里插入图片描述

4.4.4.案例演示

现在我们要查询所有姓 wang，并且名字最后一个字是 zi 的员工，比如王胖子，王瘦子。查询的 SQL：

这条 SQL 有两种执行方式： 
1、根据联合索引查出所有姓 wang 的二级索引数据，然后回表，到主键索引上查询 全部符合条件的数据（3 条数据）。
   然后返回给 Server 层，在 Server 层过滤出名字以 zi 结尾的员工。 
2、根据联合索引查出所有姓 wang 的二级索引数据（3 个索引），然后从二级索引中筛选出first_name以zi结尾的索引（1 个索引），
   然后再回表，到主键索引上查询全 部符合条件的数据（1 条数据），返回给Server层。

在这里插入图片描述

注意，索引的比较是在存储引擎进行的，数据记录的比较，是在 Server 层进行的。 
而当 first_name 的条件不能用于索引过滤时，Server 层不会把 first_name 的条件传递 给存储引擎，
所以读取了两条没有必要的记录。 这时候，如果满足 last_name='wang'的记录有 100000 条，
就会有 99999 条没有 必要读取的记录。

4.4.4.1.关闭ICP使用

根据联合索引查出所有姓 wang 的二级索引数据，然后回表，到主键索引上查询 全部符合条件的数据（3 条数据）。
然后返回给 Server 层，在 Server 层过滤出名字以 zi 结尾的员工。

我们先暂时关闭ICP

set optimizer_switch='index_condition_pushdown=off';

explain select * from employees where last_name='wang' and first_name LIKE '%zi' ;

在这里插入图片描述

Using Where 代表从存储引擎取回的数据不全部满足条件，需要在 Server 层过滤。 先用last_name条件进行索引范围扫描，读取数据表记录，
然后进行比较，检查是否符合 first_name LIKE '%zi' 的条件。此时 3 条中只有 1 条符合条件。

4.4.4.1.开启ICP使用

set optimizer_switch='index_condition_pushdown=on';

在这里插入图片描述

把 first_name LIKE '%zi'下推给存储引擎后，只会从数据表读取所需的 1 条记录。 
索引条件下推（Index Condition Pushdown），5.6 以后完善的功能。只适用于二 级索引。
ICP 的目标是减少访问表的完整行的读数量从而减少 I/O 操作。

5.索引的创建与使用

5.1.索引的创建

1、在用于 where 判断 order 排序和 join 的（on）字段上创建索引 
2、索引的个数不要过多。——浪费空间，更新变慢。 
3、区分度低的字段，例如性别，不要建索引。 ——离散度太低，导致扫描行数过多。 
4、频繁更新的值，不要作为主键或者索引。 ——页分裂 
5、组合索引把散列性高（区分度高）的值放在前面。 
6、创建复合索引，而不是修改单列索引。 
7、过长的字段，怎么建立索引？ # 前缀索引
8、为什么不建议用无序的值（例如身份证、UUID ）作为主键索引？# 索引的分裂和合并

5.2.什么时候不用索引

1、索引列上使用函数（replace\SUBSTR\CONCAT\sum count avg）、表达式、计算（+ - * /）

explain SELECT * FROM user_innodb where id+1 = 4;

在这里插入图片描述

2、字符串不加引号，出现隐式转换

ALTER TABLE user_innodb DROP INDEX comidx_name_phone; 
ALTER TABLE user_innodb add INDEX comidx_name_phone (name,phone);

explain SELECT * FROM `user_innodb` where name = 136; # 未使用索引，因为本身类型是字符串类型，做了转换，导致没有使用索引

在这里插入图片描述

explain SELECT * FROM `user_innodb` where name = '136';# 使用了索引

在这里插入图片描述

3、like 条件中前面带%

explain select *from user_innodb where name like 'wang%';

在这里插入图片描述

where 条件中 like abc%，like %2673%，like %888 都用不到索引吗？为什么？
过滤的开销太大，所以无法使用索引。这个时候可以用全文索引。

4、负向查询（NOT LIKE 不能：）

在这里插入图片描述

!= （<>）和 NOT IN 在某些情况下可以：

注意一个 SQL 语句是否使用索引，跟数据库版本、数据量、数据选择度都有关系。 其实，用不用索引，最终都是优化器说了算。 
优化器是基于什么的优化器？ 基于 cost 开销（Cost Base Optimizer），它不是基于规则（Rule-Based Optimizer）， 
也不是基于语义。怎么样开销小就怎么来。