MySQL索引:回表、索引覆盖，最左匹配原则、索引下推

MySQL索引:回表、索引覆盖，最左匹配原则、索引下推

MySQL索引类型

1.普通索引：最基本的索引，没有任何限制
2.唯一索引(unique index)：索引列的值必须唯一，但是允许为空
3.主键索引：特殊的唯一索引，但是不允许为空，一般在建表的时候同时创建主键索引
4.组合索引：指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组个索引时遵循最左前缀集合
5.全文索引：主要用来查找文本中的关键字，而不是直接与索引中的值相比较

 

sql语句是否使用索引

在查询语句前面加上explain

解释
我们只需要注意一个最重要的type 的信息很明显的提现是否用到索引：

type结果值从好到坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

 

一般来说，得保证查询至少达到range级别，最好能达到ref，否则就可能会出现性能问题。

possible_keys：sql所用到的索引

key：显示MySQL实际决定使用的键（索引）。如果没有选择索引，键是NULL

rows: 显示MySQL认为它执行查询时必须检查的行数

 

profiling分析性能瓶颈

想要优化一条query sql ，就要清楚这条query的性能瓶颈在哪里，mysql的profiler是一个非常方便的查询诊断分析工具，通过该工具可以获取一条查询在整个执行过程中多种资源的消耗情况，例如内存消耗、I/O消耗和CPU消耗

一、回表

什么是回表查询？

首先，从InnoDB说起，InnoDB的索引有两种，聚集索引（clustered index）和普通索引（secondary index），聚集索引的叶子结点存储的是行记录，若表定义了PK，那么PK就是聚集索引（因此PK查询非常快，直接定位行记录），若表没有定义PK，那么第一个not null unique列就是聚集索引。普通索引的叶子结点存储的是主键值。

如：t(id PK, name KEY, sex, flag);id为聚集索引，name为普通索引

查询

select * from t where name='lisi';　

执行过程：

对于聚集索引，直接定位行记录
对于普通索引，需要遍历两遍索引树，如下图所示，先通过普通索引定位到id，再通过聚集索引定位到行记录。

这就是所谓的回表查询，先定位主键值，再定位行记录，性能比聚集索引低。

所以，避免回表查询？

直接使用聚集索引（主键或者第一个唯一索引）即可。

二、索引覆盖

在了解覆盖索引之前我们先大概了解一下什么是聚集索引(主键索引)和辅助索引(二级索引)

   聚集索引（主键索引）：

       聚集索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的即为整张表的记录数据。

       聚集索引的叶子节点称为数据页，聚集索引的这个特性决定了索引组织表中的数据也是索引的一部分。

   辅助索引（二级索引）：

       非主键索引，叶子节点=键值+书签。Innodb存储引擎的书签就是相应行数据的主键索引值。

 

再来看看什么是覆盖索引，有下面三种理解：
解释一： 就是select的数据列只用从索引中就能够取得，不必从数据表中读取，换句话说查询列要被所使用的索引覆盖。
解释二： 索引是高效找到行的一个方法，当能通过检索索引就可以读取想要的数据，那就不需要再到数据表中读取行了。如果一个索引包含了（或覆盖了）满足查询语句中字段与条件的数据就叫 做覆盖索引。
解释三：是非聚集组合索引的一种形式，它包括在查询里的Select、Join和Where子句用到的所有列（即建立索引的字段正好是覆盖查询语句[select子句]与查询条件[Where子句]中所涉及的字段，也即，索引包含了查询正在查找的所有数据）。
如果一个索引包含(或覆盖)所有需要查询的字段的值，称为‘覆盖索引’。即只需扫描索引而无须回表。

什么是“索引覆盖”？
简单来的说，就是让查询的字段（包括where子句中的字段），都是索引字段。索引覆盖的好处是什么？好处是极大的、极大的、极大的提高查询的效率！
特别说明：
1、where条件中如果使用like关键词，即使字段是索引中的，也不构成索引覆盖。
2、单个字段的索引，对于搜索单个where条件有较好的效果。但索引覆盖需要使用多字列索引。

假设创建一个zaho_user表：
CREATE TABLE `zaho_user` (
  `u_id` int(11) NOT NULL AUTO_INCREMENT,
  `insert_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `u_name` varchar(20) NOT NULL DEFAULT '' COMMENT '用户名',
  `u_sex` tinyint(4) NOT NULL DEFAULT '1' COMMENT '性别',
  `u_desc` varchar(200) NOT NULL DEFAULT '' COMMENT '介绍'
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT '用户表';

这里我们创建一个索引：
alter table `zaho_user` add index (`u_name`,`insert_time`,`u_sex`);

那么，我们执行如下查询时，效率将会非常高：
select `u_name`,`insert_time` from `zaho_user` where `u_sex`=1
例子中的三个字段：`u_name`,`insert_time`,`u_sex`创建了一个多列索引，而SQL查询时形成了索引覆盖。

 

总结：覆盖索引的优化及限制

覆盖索引是一种非常强大的工具，能大大提高查询性能，只需要读取索引而不需要读取数据，有以下优点：

 1、索引项通常比记录要小，所以MySQL访问更少的数据。

 2、索引都按值得大小存储，相对于随机访问记录，需要更少的I/O。

 3、数据引擎能更好的缓存索引，比如MyISAM只缓存索引。

 4、覆盖索引对InnoDB尤其有用，因为InnoDB使用聚集索引组织数据，如果二级索引包含查询所需的数据，就不再需要在聚集索引中查找了。

 

限制：

 1、覆盖索引也并不适用于任意的索引类型，索引必须存储列的值。

 2、Hash和full-text索引不存储值，因此MySQL只能使用BTree。

 3、不同的存储引擎实现覆盖索引都是不同的，并不是所有的存储引擎都支持覆盖索引。

 4、如果要使用覆盖索引，一定要注意SELECT列表值取出需要的列，不可以SELECT * ，因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降。

 

三、最左匹配原则

最左匹配原则就是指在联合索引中，如果你的 SQL 语句中用到了联合索引中的最左边的索引，那么这条 SQL 语句就可以利用这个联合索引去进行匹配。

 

一帮情况下和组合索引一起使用，例如吧name，age共同建立索引(name,age)，假设现在有下面四条sql语句

select * from student where name=? and age=?

select * from student where name=?

select * from student where age=?

select * from student where age=? and name=?

现在问题来了，那个会走组合索引(name,age)?

答案是1,2,4，而3会进行全表扫描，看下图

听名知意，就是最左边开始匹配呗，也就是先匹配name，再来age。虽然2只有name，但是也会走索引。

你可能的疑惑就是4为啥会走索引，其实mysql中有个叫做优化器的东西，他会对这个age和name的顺序进行优化。这样就可以走索引了

优化器简单的说一下，有两种：CBO(基于成本的优化)，RBO(基于规则的优化)MySQL默认用的是CBO。

四、索引下推

什么是索引下推？
索引下推（index condition pushdown ）简称ICP，在Mysql5.6的版本上推出，用于优化查询。

在不使用ICP的情况下，在使用非主键索引（又叫普通索引或者二级索引）进行查询时，存储引擎通过索引检索到数据，然后返回给MySQL服务器，服务器然后判断数据是否符合条件 。

在使用ICP的情况下，如果存在某些被索引的列的判断条件时，MySQL服务器将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器 。

索引条件下推优化可以减少存储引擎查询基础表的次数，也可以减少MySQL服务器从存储引擎接收数据的次数。

首先：

数据是存储在磁盘的、MySQL有自己的服务，MySQL服务要跟磁盘发生交互。这样能从磁盘拿到数据

没有索引下推时：

存储引擎先从磁盘中筛选出name符合条件的数据，全部取出，MySQL server再根据age条件筛选一次。这样就得到了符合条件的值。

这样会有大量的IO操作，所以浪费时间和资源

有存索引下推时：

存储引擎先从磁盘中直接筛选出name，age同时都符合条件的数据，不需要server再去做任何的数据筛选

索引下推需要在磁盘上进行数据筛选，原来的筛选是在内存中进行，现在放到了磁盘上进行查找数据的环节，但是，虽然这样看起来成本更高了，可别忘了，索引数据是排序的，所有数据是聚集存放的没所以性能并不会有影响，而且还会减少IO次数，反而会提升性能
实践
当然上述的分析只是原理上的，我们可以实战分析一下，因此装了Mysql5.6版本的Mysql，解析了上述的语句，如下图：

根据explain解析结果可以看出Extra的值为Using index condition，表示已经使用了索引下推。

总结

索引下推在非主键索引上的优化，可以有效减少回表的次数，大大提升了查询的效率。

关闭索引下推可以使用如下命令，配置文件的修改不再讲述了，毕竟这么优秀的功能干嘛关闭呢：

set optimizer_switch='index_condition_pushdown=off';

 

 

来源：https://blog.csdn.net/weixin_43564627/article/details/121396125