mysql索引回表（如何构建高性能MySQL索引）

本文目录

• 如何构建高性能MySQL索引
• mysql索引原理、主从延迟问题及如何避免
• 回表与覆盖索引，索引下推
• mysql根据索引去修改数据,会走索引吗
• MySQL索引机制（详细+原理+解析）
• 「Mysql索引原理（七）」覆盖索引
• mysql索引
• 什么是MYSQL回表查询

如何构建高性能MySQL索引

本文的重点在于如何构建一个高性能的MySQL索引，从中你可以学到如何分析一个索引是不是好索引，以及如何构建一个好的索引。
索引误区
多列索引
一个索引的常见误区是为每一列创建一个索引，如下面创建的索引：
CREATE TABLE `t` (
`c1` varchar(50) DEFAULT NULL,
`c2` varchar(50) DEFAULT NULL,
`c3` varchar(50) DEFAULT NULL,
KEY `c1` (`c1`),
KEY `c2` (`c2`),
KEY `c3` (`c3`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
t表里有三列，并且为每列创建了一个索引。创建索引的人为了能够快速访问表中的任何一列，因此为每一列添加了一个单独的索引。在多个列上创建索引通常并不能很好的提高MySQL查询性能，虽然说MySQL 5.0之后引入了索引合并策略，可以将多个单列索引合并成一个索引，但这并不总是有效的。同时创建多个索引的时候还会增加数据插入的成本，在插入数据的时候需要同时维护多个索引的写入操作。
索引的计算
看下面这条sql语句：
select name from student where id + 1 = 5
即使我们在student表的id列上建立索引，上面的这条SQL语句也无法使用索引。SQL语句中索引字段不能是表达式的一部分，也不能是函数的参数。
索引的长度以及选择性
尽量不要在一个很长的列上使用索引，否则会导致索引占用的空间很大，同时在进行数据的插入和更新的时候意味着更慢的速度。因此使用uuid列作为索引并不是一个好的选择。从上一篇文章中我们可以知道，为了加快数据的访问索引是需要常驻内存的，假如说我们把64位uuid作为索引，那么随着表中数据量的增加索引的大小也在急剧增加。同时因为uuid并没有顺序性，因此在数据插入的时候都需要从根节点找到当前索引的插入位置，如果同一个节点中的索引大小达到上限，还会导致节点分裂，更加降低了插入速度。
创建索引另外一个需要考虑的是索引的选择性，通常情况下我们会使用选择性高的列作为索引，但是也不一定一直是这样，下一节会介绍如何权衡索引的选择性。
创建高性能索引
选择正确的索引顺序
在选择索引的顺序的时候有一个原则：将索引选择性最高的列放在左侧，同时索引的顺序要与查询索引的顺序一致，并且要兼顾考虑排序和分组的需要。在一个多列B树多列中索引的顺序意味着索引首先按照最左侧的列进行排序，其次是第二列。所以无论是where语句还是order by语句都需要尽量满足这个顺序，这样才能更好的使用索引。
索引的选择性
列的选择性高的含义是通过这一列能够更多的过滤掉无用的数据，举个极端的例子，如果把自增id建成索引那么它的选择性是最高的，因为会把无用的数据都过滤掉，只会剩下一条有效数据。我们可以通过下面的方式来简单衡量某一个列的选择性：
select count(distinct columnA)/count(*) as selectivity from table
当上面的数据越大的时候意味着columnA的选择性越高。这种方式提供了一个衡量平均选择性的办法，但是也不一定是有效的，需要具体情况具体分析。
前缀索引
当遇到特别长的列，但又必须要建立索引的时候可以考虑建立前缀索引。前缀索引的含义是把某一列的前N个字符作为索引，创建前缀索引的方式如下：
alter table test add key(columnA(5));
上面这个语句就是把columnA的前5个字符创建为前缀索引。前缀索引是一种使索引更小、更快的有效办法。但是前缀所有有一个缺点：MySQL无法使用前缀索引来做order by和group by，也无法使用前缀索引做覆盖扫描。
聚簇索引和非聚簇索引
聚簇索引
聚簇索引代表一种数据的存储方式，表示同一个结构中保存了B-Tree索引和数据行。也就是说当建立聚簇索引的时候实际的数据行存放在索引的叶子节点上。这也决定了每个表只能有一个聚簇索引。
聚簇索引组织数据的方式如下图所示：
从图中可以看到索引的叶子节点和数据行是存放在一起的，这样的好处是可以直接读取到数据行。在创建表的时候如果我们不显式指定聚簇索引，那么MySQL将会按照下面的逻辑来选择聚簇索引：首先会通过主键列来聚集数据，如果没有主键列那么会选择唯一的非空索引来替代。如果还没有这样的索引那么会隐式的创建一个主键列来作为聚簇索引。
聚簇索引优点：
1、相关数据存放在一起，检索的时候降低IO的次数2、数据访问更快3、使用覆盖索引扫描的查询可以直接使用节点中的主键值
在使用上面的优点的时候聚簇索引也有一定的缺点：
1、聚簇索引将数据聚集在一起限制了插入速度，插入速度比较依赖于主键的顺序2、更新索引的时候代价会变高3、二级索引的访问的时候需要查找两次
非聚簇索引
非聚簇索引通常被称为二级索引，与聚簇索引的不同在于，非聚簇索引的叶子节点存放的是数据的行指针或者是一个主键值。这样在查找数据的时候首先定位到叶子节点上的主键值(或者行指针)，然后通过主键值再到聚簇索引中查找到对应的数据。从中我们可以看到对于非聚簇索引的查询需要走两次索引。下图是一个非聚簇索引：
这个索引是InnoDB中的耳机索引，叶子节点中存储的是索引和主键。对于MyISAM叶子节点存储的是索引和行指针。
覆盖索引
如果一个索引包含或者说覆盖所有需要查询的字段的值，那么就称为覆盖索引。覆盖索引可以极大的提高查询的效率，如果我们的查询中只查询索引，而不用去回表那应该最好不过了。
通常我们使用explain关键字来查看一个查询语句的执行计划，通过执行计划我们可以了解到查询的细节。如果是覆盖索引，我们会看到执行计划的Extra列里有”Using Index”的信息。在查询语句中一般我们希望是where条件中的语句尽量能被覆盖，并且顺序要跟索引的保持一致。还有一个需要注意的点是MySQL不能在索引中使用like操作，这样会导致后面的索引失效。
总结
本文主要讲了几种索引的原理以及如何构建一个高性能的索引。索引的优先是一个渐进的过程，随着数据量和查询语句的不同而发生变化，重要的是了解索引的原理，这样做出正确的优化。下一篇文章中将会介绍explain关键字，教你如何来看执行计划，以及如何判断一个查询语句是否需要优化的。
如何构建高性能MySQL索引
标签：header这一执行计划合并isamyisamuser创建表html

mysql索引原理、主从延迟问题及如何避免

本文讲一下mysql的整体查询过程
1、基本的框架
客户端- 》 连接器 - 》 分析器 -》 优化器 - 》执行器 - 》 存储引擎
- 》 查询缓存 - 》
这里还有一个缓存的位置，是在连接器处，如果缓存中存在要查询的结果则直接走缓存返回
但在现实中开启缓存的几率比较低
原因1、对于一个表的更新操作，这个表上的所有查询缓存都会被清空
因此除了很少更新的配置表外可以使用查询缓存来提高查询速度，一般不建议开启查询缓存
一般也不建议开启
分析器：分析语法及词法，保证sql的正确性
优化器：一条sql可以通过不同的方式获取数据，优化器需要找到最优的查询方式
查找的依赖：统计信息和代码模型
例： select * from A where a = 3 and b = 4 ;
如果表中a都是3， b 只有一条为4， 优化器会选择b的索引进行查询，因为a的区分度不高，且还需要
进行回表操作，导致代价更高
例： select * from S where ( a between 1 and 1000) and (b between 5000 and 10000) order by b limit 1 ;
mysql 会选择哪个索引？
使用a索引需要最多扫描1000行数据，然后在进行排序
使用b索引需要最多扫描50000行数据，不需要进行排序
mysql5.7之前优化器最终会选择b索引,因为受order by的影响
在5.7之后会选择a索引
优化器确实会存在一些bug，导致选择的最终索引错误，这些内容需要进行具体sql具体分析
原则：尽量使用索引的排序，因为非索引的排序都属于filesort， 一提到文件排序其实就会比较耗时
执行器：
拿到优化器的信息，去调用搜索引擎的api接口，先取b=4的数据，判断a是否=3,如果不等于跳过，否则放入结果集
调用接口再取b=4的下一条数据并返回执行器，重复，直到循环遍历结束
执行器讲结果集返回给客户端
注： mysql将结果返回客户端是一个增量、逐步返回的过程，不一定等所有结果查到才返回
好处：
1、服务器无需查询太多的结果，也不会因为返回太多的结果而消耗太多的内存
2、客户端也可以第一时间返回结果
慢sql的具体原因
磁盘io : 磁盘的访问成本大概是内存的十万倍左右，为了降低磁盘io
在每次io时，不光把磁盘地址的数据，而且把周边的数据也读到内存缓存区
每一次读取就是一page， 具体大小在8k或者16k ，所以在读取一页数据的时候才会发生io
在查找数据的时候，B+树每一层进行一次IO，所以B+树的高度决定了io的次数
索引有最左匹配的特性
哪些情况要建索引
1、主键自动建主键索引
2、频繁作为查询条件的字段应该创建索引
3、查询中与其他表关联的字段，外键关系建立索引
4、在高并发下倾向建立组合索引
5、查询中的排序字段，排序字段若通过索引去访问将大大提高排序速度
6、查询中统计或者分组的数据
哪些情况不适合建索引
1、频繁更新的字段
2、where条件用不到的字段不创建索引
3、表记录太少
4、经常增删改的表
5、数据重复太多的字段，为它建索引意义不大
进行explain
type:system》const》eq_ref》ref》fulltext》ref_or_null》index_merge》unique_subquery》index_subquery》range》index》all
3.1 索引失效_复合索引（避免）
1、应该尽量全值匹配
2、复合最佳左前缀法则（第一个索引不能掉，中间不能断开）
3、不在索引列上做任何操作（计算、函数、类型转换）会导致索引失效而转向全表扫描
4、储存引擎不能使用索引中范围条件右边的列
5、尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select*
6、mysql在使用不等于(!=或者《》)的时候无法使用索引会导致全表扫描
7、is null，is not null也会无法使用索引
8、like以统配符开头
9、字符串不加单引号
10、少用or
有些sql 中会包含force index的写法，强制去走某个索引，但条件中缺不存在这个字段，会导致全表扫描
SELECT * FROM `coupon` FORCE INDEX (`orderid`) WHERE `userid` = 1 AND `status` IN (0,1) ORDER BY `id` ASC ;
数据库的主从同步
mysql主从复制需要三个线程：master（binlog dump thread）、slave（I/O thread 、SQL thread）
binlog dump线程：主库中有数据更新时，根据设置的binlog格式，将更新的事件类型写入到主库的binlog文件中，并创建log dump线程通知slave有数据更新。当I/O线程请求日志内容时，将此时的binlog名称和当前更新的位置同时传给slave的I/O线程。
I/O线程：该线程会连接到master，向log dump线程请求一份指定binlog文件位置的副本，并将请求回来的binlog存到本地的relay log中。
SQL线程：该线程检测到relay log有更新后，会读取并在本地做redo操作，将发生在主库的事件在本地重新执行一遍，来保证主从数据同步
puma , databus :
主从延迟问题
主库 A 执行完成一个事务，写入 binlog，该时刻记为T1.
传给从库B，从库接受完这个binlog的时刻记为T2.
从库B执行完这个事务，该时刻记为T3.
是T2-T1 吗？ 不是，如果网络不延迟，T2-T1 是一个很短的时间
是T3-T2吗？ 是的，主要是从库执行的情况（relaylog）
具体原因：
1、从库的机器性能比主库差
2、从库的压力大
3、大事务的执行，如果是大事务，主库必须等事务完成之后才写入binlog，数据传输人到从库，执行容易产生延迟
尽量避免一次性的delete大量数据，尽量批次处理
4、主库的ddl, alter, drop, repair, create
1、只读节点与主库的DDL同步是串行进行，如果DDL操作在主库执行时间很长，那么从库也会消耗同样的时间，比如在主库对一张500W的表添加一个字段耗费了10分钟，那么只读节点上也会耗费10分钟。
2、只读节点上有一个执行时间非常长的的查询正在执行，那么这个查询会堵塞来自主库的DDL，读节点表被锁，直到查询结束为止，进而导致了只读节点的数据延迟。
5、锁冲突
如何避免主从延迟
降低多线程大事务并发的概率，优化业务逻辑
优化SQL，避免慢SQL，减少批量操作，建议写脚本以update-sleep这样的形式完成。
提高从库机器的配置，减少主库写binlog和从库读binlog的效率差。
尽量采用短的链路，也就是主库和从库服务器的距离尽量要短，提升端口带宽，减少binlog传输的网络延时。
实时性要求的业务读强制走主库，从库只做灾备，备份。
mysql索引原理、主从延迟问题及如何避免
标签：框架线程tab使用位置应该事件redupd

回表与覆盖索引，索引下推

通俗的讲就是，如果索引的列在 select 所需获得的列中（因为在 mysql 中索引是根据索引列的值进行排序的，所以索引节点中存在该列中的部分值）或者根据一次索引查询就能获得记录就不需要回表，如果 select 所需获得列中有大量的非索引列，索引就需要到表中找到相应的列的信息，这就叫回表。

InnoDB聚集索引的叶子节点存储行记录，因此， InnoDB必须要有，且只有一个聚集索引：

（1）如果表定义了主键，则PK就是聚集索引；
（2）如果表没有定义主键，则第一个非空唯一索引（not NULL unique）列是聚集索引；
（3）否则，InnoDB会创建一个隐藏的row-id作为聚集索引；

先创建一张表，sql 语句如下：

然后，我们再执行下面的 SQL 语句，插入几条测试数据。

假设，现在我们要查询出 id 为 2 的数据。那么执行 select * from xttblog where ID = 2; 这条 SQL 语句就不需要回表。原因是根据主键的查询方式，则只需要搜索 ID 这棵 B+ 树。主键是唯一的，根据这个唯一的索引，MySQL 就能确定搜索的记录。

但当我们使用 k 这个索引来查询 k = 2 的记录时就要用到回表。select * from xttblog where k = 2; 原因是通过 k 这个普通索引查询方式，则需要先搜索 k 索引树，然后得到主键 ID 的值为 1，再到 ID 索引树搜索一次。这个过程虽然用了索引，但实际上底层进行了两次索引查询，这个过程就称为回表。

也就是说，基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。

我这里表里的数据量比较少，如果数据量大的话，你能很明显的看出两次查询所用的时间，很明显使用主键查询效率更高。

更多如下图：

（1）先通过普通索引定位到主键值id=5；
（2）在通过聚集索引定位到行记录；
这就是所谓的回表查询，先定位主键值，再定位行记录，它的性能较扫一遍索引树更低。

使用聚集索引（主键或第一个唯一索引）就不会回表，普通索引就会回表。

只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快。

explain的输出结果Extra字段为Using index时，能够触发索引覆盖。

例子

第一个sql：
select id,name from user where name=’shenjian’;

Extra：Using index。

第二个sql：
select id,name,sex from user where name=’shenjian’;

能够命中name索引， 索引叶子节点存储了主键id，没有储存sex，sex字段必须回表查询才能获取到 ，不符合索引覆盖，需要再次通过id值扫描聚集索引获取sex字段，效率会降低。

Extra：Using index condition。

如果把(name)单列索引升级为联合索引(name, sex)就不同了。

可以看到：

select id,name ... where name=’shenjian’;
select id,name,sex ... where name=’shenjian’;
单列索升级为联合索引(name, sex)后，索引叶子节点存储了主键id，name，sex ，都能够命中索引覆盖，无需回表。

画外音，Extra：Using index。

场景1：全表count查询优化

原表为：
user(PK id, name, sex)；

直接：
select count(name) from user;
不能利用索引覆盖。

添加索引：
alter table user add key(name);
就能够利用索引覆盖提效。

场景2：列查询回表优化

这个例子不再赘述，将单列索引(name)升级为联合索引(name, sex)，即可避免回表。

场景3：分页查询

将单列索引(name)升级为联合索引(name, sex)，也可以避免回表。

假设有这么个需求，查询表中“名字第一个字是张，性别男，年龄为10岁的所有记录”。那么，查询语句是这么写的：

根据前面说的“最左前缀原则”，该语句在搜索索引树的时候，只能匹配到名字第一个字是‘张’的记录（即记录ID3），接下来是怎么处理的呢？当然就是从ID3开始，逐个回表，到主键索引上找出相应的记录，再比对age和ismale这两个字段的值是否符合。

但是！MySQL 5.6引入了索引下推优化，可以在索引遍历过程中， 对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表字数 。
下面图1、图2分别展示这两种情况。

图 1 中，在 (name,age) 索引里面我特意去掉了 age 的值， 这个过程 InnoDB 并不会去看 age 的值 ，只是按顺序把“name 第一个字是’张’”的记录一条条取出来回表。因此，需要回表 4 次。

图 2 跟图 1 的区别是，InnoDB 在 (name,age) 索引内部就判断了 age 是否等于 10，对于不等于 10 的记录，直接判断并跳过。在我们的这个例子中，只需要对 ID4、ID5 这两条记录回表取数据判断，就只需要回表 2 次。

如果没有索引下推优化（或称ICP优化），当进行索引查询时， 首先根据索引来查找记录，然后再根据where条件来过滤记录 ；在支持ICP优化后，MySQL会在取出索引的同时， 判断是否可以进行where条件过滤再进行索引查询 ，也就是说提前执行where的部分过滤操作，在某些场景下，可以大大减少回表次数，从而提升整体性能。

​

mysql根据索引去修改数据,会走索引吗

不一定，要看情况，具体是由MySQL优化器内部决定是全表扫描还是索引查找，用效率较高的一种方式。
针对索引字段的唯一性不高的情况下（索引的"区分度"低），优化器可能会选择全表扫描，而不是走索引。这可能是因为等值查询符合条件的记录太多了，导致了mysql认为全表扫描比用索引查找更快。
比如你对唯一性不高的字段（如性别：男/女）加了索引，这样通过索引去查找可能还需回表，还不如直接全表扫描！
若in中的数据量较大时，基本就不走索引了。如果你索引字段是一个unique，in可能就会用到索引。
如果你一定要用索引，可以用 force index。可能也和MySQL版本有关（5.6以后有做in的查询优化）

MySQL索引机制（详细+原理+解析）

MySQL 前缀索引能有效减小索引文件的大小，提高索引的速度。但是前缀索引也有它的坏处：MySQL 不能在 ORDER BY 或 GROUP BY 中使用前缀索引，也不能把它们用作覆盖索引(Covering Index)。

集一个索引包含多个列（最左前缀匹配原则）

索引列的值必须唯一，但允许有空值

全文索引为FUllText，在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值，全文索引可以在CHAR,VARCHAR,TEXT类型列上创建

设定主键后数据会自动建立索引，InnoDB为聚簇索引

即一个索引只包含单个列，一个表可以有多个单列索引

覆盖索引是指一个查询语句的执行只用从所有就能够得到，不必从数据表中读取，覆盖索引不是索引树，是一个结果，当一条查询语句符合覆盖索引条件时候，MySQL只需要通过索引就可以返回查询所需要的数据，这样避免了查到索引后的回表操作，减少了I/O效率

查看索引

列名解析：

删除索引

查看：

删除前:

删除后：

普通的索引，没有什么介绍

查看:（注意和前缀索引Sub_part的区别）

当索引的列是unique的时候，会生成唯一索引，唯一索引关于null有下列两种情况

SQLSERVER 下的唯一索引的列，允许null值，但最多允许有一个空值

MYSQL下的唯一索引的列，允许null值，并且允许多个空值

查看:

会建立两个索引，一个非聚簇索引，一个是唯一索引

结果:

可以插入两个空值（明人不说暗话，我喜欢MySQL）

一方面，它不会索引所有字段所有字符，会减小索引树的大小.

另外一方面，索引只是为了区别出值，对于某些列，可能前几位区别很大，我们就可以使用前缀索引。

一般情况下某个前缀的选择性也是足够高的，足以满足查询性能。对于BLOB，TEXT，或者很长的VARCHAR类型的列，必须使用前缀索引，因为MySQL不允许索引这些列的完整长度。

查看：

查看：

复合索引的最左前缀匹配原则 ：

对于复合索引，查询在一定条件才会使用该索引

减少开销。 建一个联合索引(col1,col2,col3)，实际相当于建了(col1),(col1,col2),(col1,col2,col3)三个索引。每多一个索引，都会增加写操作的开销和磁盘空间的开销。对于大量数据的表，使用联合索引会大大的减少开销！

覆盖索引。 对联合索引(col1,col2,col3)，如果有如下的sql: select col1,col2,col3 from test where col1=1 and col2=2。那么MySQL可以直接通过遍历索引取得数据，而无需回表，这减少了很多的随机io操作。减少io操作，特别的随机io其实是dba主要的优化策略。所以，在真正的实际应用中，覆盖索引是主要的提升性能的优化手段之一。

效率高。 索引列越多，通过索引筛选出的数据越少。有1000W条数据的表，有如下sql:select from table where col1=1 and col2=2 and col3=3,假设假设每个条件可以筛选出10%的数据，如果只有单值索引，那么通过该索引能筛选出1000W10%=100w条数据，然后再回表从100w条数据中找到符合col2=2 and col3= 3的数据，然后再排序，再分页；如果是联合索引，通过索引筛选出1000w10% 10% *10%=1w。

在模糊搜索中很有效，搜索全文中的某一个字段，可以参考这篇博文
***隐藏网址***

我们先进行下面一个实验看看InnoDB下的主键索引的一个现象。

查看:

我们插入进去的时候，数据的id都是乱序的，为什么这里最后select查询出来的结果都是进行了排序？

这是因为InnoDB索引底层实现的是B+tree，B+tree具有下列的特点：

所以上面的排序是为了使用B+tree的结构 ，B+tree为了范围搜索，将主键按照从小到大排序后，拆分成节点。后续还有新的节点进入的时候，和B-tree相同的操作，会进行分裂。

一般来说，聚簇索引的B+tree都是三层

InnoDB中主键索引一定是聚簇索引，聚簇索引一定是主键索引。

为什么这里辅助索引叶子结点不直接存储数据呢？

MYISAM只有非聚簇索引，索引最终指向的都是物理地址。

Q:既然有回表的存在，那么聚簇索引的优势在哪里？

Q:主键索引作为聚簇索引需要注意什么

在查询语句中使用LIke关键字进行查询时，如果匹配字符串的第一个字符为"%"，索引不会使用。如果“%”不是在第一位，索引就会使用

多列索引是在表的多个字段上创建的索引，满足最左前缀匹配原则，索引才会被使用

查询语句只有Or关键字时候，如果OR前后的两个条件都是索引，这这次查询将会使用索引，否则Or前后有一个条件的列不是索引，那么查询中将不使用索引

「Mysql索引原理（七）」覆盖索引

       通常大家都会根据查询的WHERE条件来创建合适的索引，不过这只是索引优化的一个方面。设计优秀的索引应该考虑到整个查询，而不单单是WHERE条件部分。索引确实是一种查找数据的高效方式，但是MySQL也可以使用索引来直接获取列的数据，这样就不再需要读取数据行。如果索引的叶子节点中已经包含要查询的数据，那么还有什么必要再回到表中查询呢？ 如果一个索引覆盖所有需要查询的字段的值，我们就称之为“覆盖索引”。

覆盖索引是非常有用的工具，能够极大地提高性能：

       在所有这些场景中，在索引中满足查询的成本一般比查询行要小得多。
       不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引列的值，而哈希索引、空间索引和全文索引都不存储索引列的值，所以MySQL只能使用B+Tree索引所覆盖索引。另外，不同的存储引擎实现覆盖索引的方式也不同，而且不是所有的引擎都支持覆盖索引。

       当发起一个呗索引覆盖的查询是，在EXPLAIN的Extra列可以看到“Using index”的信息。

如： explain select col1 from layout_test where col2=99

       索引覆盖查询还有很多陷阱可能会导致无法实现优化。MySQL查询优化器会在执行查询前判断是否有一个索引能进行覆盖。假设索引覆盖了wehre条件中的字段，但不是整个查询涉及的字段。mysql5.5和更早的版本也总是会回表获取数据行，尽管并不需要这一行且最终会被过滤掉。

如： EXPLAIN select * from people where last_name=’Allen’ and first_name like ’%Kim%’

这里索引无法覆盖该查询，有两个原因：

这条语句只检索1行，而之前的 like ’%Kim%’要检索3行。
也有办法解决上面所说的两个问题，需要重写查询并巧妙设计索引。

       这种方式叫做延迟关联，因为延迟了对列的访问。在查询第一个阶段MySQL可以使用覆盖索引，因为索引包含了主键id的值，不需要做二次查找。

       在FROM子句的子查询中找到匹配的id，然后根据这些id值在外层查询匹配获取需要的所有列值。虽然无法使用索引覆盖整个查询，但总算比完全无法利用索引覆盖的好吧。

数据量大了怎么办？
       这样优化的效果取决于WHERE条件匹配返回的行数。假设这个people表有100万行，我们看一下上面两个查询在三个不同的数据集上的表现，每个数据集都包含100万行。

实例1中 ，查询返回了一个很大的结果集，因此看不到优化的效果。大部分时间都花在读取和发送数据上了。

实例2中 ，经过索引过滤，尤其是第二个条件过滤后只返回了很少的结果集，优化的效果非常明显：在这个数据及上性能提高了很多，优化后的查询效率主要得益于只需读取40行完整数据行，而不是原查询中需要的30000行。

实例3中 ，子查询效率反而下降。因为索引过滤时符合第一个条件的结果集已经很小了，所以子查询带来的成本反而比从表中直接提取完整行更高。

       在大多数存储引擎中，覆盖索引只能覆盖那些只访问索引中部分列的查询。不过，可以更进一步优化InnoDB。回想一下，InnoDB的二级索引的叶子节点都包含了主键的值，这意味着InnoDB的二级索引可以有效地利用这些额外的主键列来覆盖查询。

       例如，people表中last_name字段有一个二级索引，虽然该索引的列不包括主键id，但也能够用于对id做覆盖查询：

select id,last_name from people where last_name=’hua’

mysql索引

create unique index ix_customerNo on customer(customerNo);
create unique index ix_CompanyName on customer(CompanyName);

什么是MYSQL回表查询

《迅猛定位低效SQL？》留了一个尾巴：
select id,name where name=‘shenjian’
select id,name,sex where name=‘shenjian’
多查询了一个属性，为何检索过程完全不同？
什么是回表查询？
什么是索引覆盖？
如何实现索引覆盖？
哪些场景，可以利用索引覆盖来优化SQL？
这些，这是今天要分享的内容。
画外音：本文试验基于MySQL5.6-InnoDB。
一、什么是回表查询？
这先要从InnoDB的索引实现说起，InnoDB有两大类索引：
聚集索引(clustered index)
普通索引(secondary index)
**InnoDB聚集索引和普通索引有什么差异？ **
InnoDB 聚集索引 的叶子节点存储行记录，因此， InnoDB必须要有，且只有一个聚集索引：
（1）如果表定义了PK，则PK就是聚集索引；
（2）如果表没有定义PK，则第一个not NULL unique列是聚集索引；
（3）否则，InnoDB会创建一个隐藏的row-id作为聚集索引；
画外音：所以PK查询非常快，直接定位行记录。
InnoDB 普通索引 的叶子节点存储主键值。
画外音：注意，不是存储行记录头指针，MyISAM的索引叶子节点存储记录指针。
举个栗子，不妨设有表：
t(id PK, name KEY, sex, flag);
画外音：id是聚集索引，name是普通索引。
表中有四条记录：
1, shenjian, m, A
3, zhangsan, m, A
5, lisi, m, A
9, wangwu, f, B
两个B+树索引分别如上图：
（1）id为PK，聚集索引，叶子节点存储行记录；
（2）name为KEY，普通索引，叶子节点存储PK值，即id；
既然从普通索引无法直接定位行记录，那 普通索引的查询过程是怎么样的呢？
通常情况下，需要扫码两遍索引树。
例如：
select * from t where name=‘lisi’;
是如何执行的呢？
如 粉红色 路径，需要扫码两遍索引树：
（1）先通过普通索引定位到主键值id=5；
（2）在通过聚集索引定位到行记录；
这就是所谓的 回表查询 ，先定位主键值，再定位行记录，它的性能较扫一遍索引树更低。
二、什么是索引覆盖 (Covering index) ？
额，楼主并没有在MySQL的官网找到这个概念。
画外音：治学严谨吧？
借用一下SQL-Server官网的说法。
MySQL官网，类似的说法出现在explain查询计划优化章节，即explain的输出结果Extra字段为Using index时，能够触发索引覆盖。
不管是SQL-Server官网，还是MySQL官网，都表达了：只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快。
三、如何实现索引覆盖？
常见的方法是：将被查询的字段，建立到联合索引里去。
仍是《迅猛定位低效SQL？》中的例子：
create table user (
id int primary key,
name varchar(20),
sex varchar(5),
index(name)
)engine=innodb;
第一个SQL语句：
select id,name from user where name=‘shenjian’;
能够命中name索引，索引叶子节点存储了主键id，通过name的索引树即可获取id和name，无需回表，符合索引覆盖，效率较高。
画外音，Extra：Using index。
第二个SQL语句：
select id,name,sex from user where name=‘shenjian’;
能够命中name索引，索引叶子节点存储了主键id，但sex字段必须回表查询才能获取到，不符合索引覆盖，需要再次通过id值扫码聚集索引获取sex字段，效率会降低。
画外音，Extra：Using index condition。
如果把(name)单列索引升级为联合索引(name, sex)就不同了。
create table user (
id int primary key,
name varchar(20),
sex varchar(5),
index(name, sex)
)engine=innodb;
可以看到：
select id,name … where name=‘shenjian’;
select id,name,sex … where name=‘shenjian’;
都能够命中索引覆盖，无需回表。
画外音，Extra：Using index。
四、哪些场景可以利用索引覆盖来优化SQL？
场景1：全表count查询优化
原表为：
user(PK id, name, sex)；
直接：
select count(name) from user;
不能利用索引覆盖。
添加索引：
alter table user add key(name);
就能够利用索引覆盖提效。
场景2：列查询回表优化
select id,name,sex … where name=‘shenjian’;
这个例子不再赘述，将单列索引(name)升级为联合索引(name, sex)，即可避免回表。
场景3：分页查询
select id,name,sex … order by name limit 500,100;
将单列索引(name)升级为联合索引(name, sex)，也可以避免回表。
InnoDB聚集索引普通索引 ， 回表 ， 索引覆盖 ，希望这1分钟大家有收获。
提示，如果你不清楚explain结果Extra字段为Using index的含义，请阅读前序文章：《如何利用工具，迅猛定位低效SQL？》
什么是MYSQL回表查询
标签：d3dbad查询nulluniqueexplaintablezhangsan