mysql索引底层原理,mysql索引

mysql索引底层原理,mysql索引详细介绍

本文目录一览： 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、数据重复太多的字段，为它建索引意义不大进行explaintype:system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>all3.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, create1、只读节点与主库的DDL同步是串行进行，如果DDL操作在主库执行时间很长，那么从库也会消耗同样的时间，比如在主库对一张500W的表添加一个字段耗费了10分钟，那么只读节点上也会耗费10分钟。2、只读节点上有一个执行时间非常长的的查询正在执行，那么这个查询会堵塞来自主库的DDL，读节点表被锁，直到查询结束为止，进而导致了只读节点的数据延迟。5、锁冲突如何避免主从延迟降低多线程大事务并发的概率，优化业务逻辑优化SQL，避免慢SQL，减少批量操作，建议写脚本以update-sleep这样的形式完成。提高从库机器的配置，减少主库写binlog和从库读binlog的效率差。尽量采用短的链路，也就是主库和从库服务器的距离尽量要短，提升端口带宽，减少binlog传输的网络延时。实时性要求的业务读强制走主库，从库只做灾备，备份。mysql索引原理、主从延迟问题及如何避免标签：框架线程tab使用位置应该事件redupd

mysql索引原理

索引的本质是一种排好序的数据结构。这个我相信其实大家并不陌生，因为谈到索引很多人自然而然的就会联想到字典中的目录。
没错，这样的类比是很形象的，但是如果再往深处说，恐怕很多小伙伴就有点张口结舌了，那既然你已经知道了索引的本质，那么您就已经有了看这篇文章的基础，相信读文本文的你，一定会对索引的原理有一个全新的了解。在数据库中，索引是分很多种类的（千万不要狭隘的认为索引只有 B+ 树，那是因为我们平时使用的基本都是 MySQL）。

MySQL 索引是怎么实现的？

你要询问原理还是怎么建立索引？
如果要建立索引，那就直接建立就可以了，借助navicate工具，连接MySQL，对你经常需要用到的某个字段，比如多表关联查询时需要用到的匹配字段，将这个字段设置成索引，可以提高查询效率。
至于索引，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。
可以理解为一本百科全书的目录，这就是索引的一种形式。
索引是满足某种特定查找算法的数据结构，而这些数据结构会以某种方式指向数据，从而实现高效查找数据。
具体来说 MySQL 中的索引，不同的数据引擎实现有所不同，但目前主流的数据库引擎的索引都是 B+ 树实现的，B+ 树的搜索效率，可以到达二分法的性能，找到数据区域之后就找到了完整的数据结构了，所有索引的性能也是更好的。

什么是索引及MySQL索引原理和慢查询优化

索引目的
索引的目的在于提高查询效率，可以类比字典，如果要查“mysql”这个单词，我们肯定需要定位到m字母，然后从下往下找到y字母，再找到剩下的sql。如果没有索引，那么你可能需要把所有单词看一遍才能找到你想要的，如果我想找到m开头的单词呢？或者ze开头的单词呢？是不是觉得如果没有索引，这个事情根本无法完成？
索引原理
除了词典，生活中随处可见索引的例子，如火车站的车次表、图书的目录等。它们的原理都是一样的，通过不断的缩小想要获得数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是我们总是通过同一种查找方式来锁定数据。
数据库也是一样，但显然要复杂许多，因为不仅面临着等值查询，还有范围查询(>、<、between、in)、模糊查询(like)、并集查询(or)等等。数据库应该选择怎么样的方式来应对所有的问题呢？我们回想字典的例子，能不能把数据分成段，然后分段查询呢？最简单的如果1000条数据，1到100分成第一段，101到200分成第二段，201到300分成第三段……这样查第250条数据，只要找第三段就可以了，一下子去除了90%的无效数据。但如果是1千万的记录呢，分成几段比较好？稍有算法基础的同学会想到搜索树，其平均复杂度是lgN，具有不错的查询性能。但这里我们忽略了一个关键的问题，复杂度模型是基于每次相同的操作成本来考虑的，数据库实现比较复杂，数据保存在磁盘上，而为了提高性能，每次又可以把部分数据读入内存来计算，因为我们知道访问磁盘的成本大概是访问内存的十万倍左右，所以简单的搜索树难以满足复杂的应用场景。
索引的数据结构
前面讲了生活中索引的例子，索引的基本原理，数据库的复杂性，又讲了操作系统的相关知识，目的就是让大家了解，任何一种数据结构都不是凭空产生的，一定会有它的背景和使用场景，我们现在总结一下，我们需要这种数据结构能够做些什么，其实很简单，那就是：每次查找数据时把磁盘IO次数控制在一个很小的数量级，最好是常数数量级。那么我们就想到如果一个高度可控的多路搜索树是否能满足需求呢？就这样，b+树应运而生。

mysql索引最左原则原理

mysql 索引最左原则原理
索引本质是一棵B+Tree，联合索引(col1, col2,col3)也是。
其非叶子节点存储的是第一个关键字的索引，而叶节点存储的则是三个关键字col1、col2、col3三个关键字的数据，且按照col1、col2、col3的顺序进行排序。
（图以innodb引擎为例，对应地址指的是数据记录的地址）
联合索引(年龄, 姓氏,名字)，叶节点上data域存储的是三个关键字的数据。且是按照年龄、姓氏、名字的顺序排列的。
而最左原则的原理就是，因为联合索引的B+Tree是按照第一个关键字进行索引排列的。

数据库基础：讲解MySQL索引的概念及数据库索引的应用[1]

　　 数据库引入了索引
　　用户对数据库最频繁的操作是进行数据查询 一般情况下 数据库在进行查询操作时需要对整个表进行数据搜索 当表中的数据很多时 搜索数据就需要很长的时间 这就造成了服务器的资源浪费 为了提高检索数据的能力 数据库引入了索引机制
　　 有关 索引 的比喻
　　从某种程度上 可以把数据库看作一本书 把索引看作书的目录 通过目录查找书中的信息 显然较没有目录的书方便 快捷
　　 数据库索引实际是什么?(两部分组成)

　　索引是一个单独的 物理的数据库结构 它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单
　　 索引在表中的角色
　　一个表的存储是由两部分组成的 一部分用来存放表的数据页面 另一部分存放索引页面 索引就存放在索引页面上
　　 索引高效原理
　　通常 索引页面相对于数据页面来说小得多 当进行数据检索时 系统先搜索索引页面 从中找到所需数据的指针 再直接通过指针从数据页面中读取数据
　　 索引的分类
　　在SQL Server 的数据库中按存储结构的不同将索引分为两类 簇索引(Clustered Index)和非簇索引(Nonclustered Index)
　　( )簇索引对表的物理数据页中的数据按列进行排序 然后再重新存储到磁盘上 即簇索引与数据是混为一体 的它的叶节点中存储的是实际的数据 由于簇索引对表中的数据一一进行了排序 因此用簇索引查找数据很快 但由于簇索引将表的所有数据完全重新排列了 它所需要的空间也就特别大 大概相当于表中数据所占空间的 % 表的数据行只能以一种排序方式存储在磁盘上 所以一个表只能有一个簇索引
　　( )非簇索引具有与表的数据完全分离的结构 使用非簇索引不用将物理数据页中的数据按列排序 非簇索引的叶节点中存储了组成非簇索引的关键字的值和行定位器 行定位器的结构和存储内容取决于数据的存储方式 如果数据是以簇索引方式存储的 则行定位器中存储的是簇索引的索引键;如果数据不是以簇索引方式存储的 这种方式又称为堆存储方式(Heap Structure) 则行定位器存储的是指向数据行的指针 非簇索引将行定位器按关键字的值用一定的方式排序 这个顺序与表的行在数据页中的排序是不匹配的 由于非簇索引使用索引页存储因此它比簇索引需要更多的存储空间且检索效率较低但一个表只能建一个簇索引 当用户需要建立多个索引时就需要使用非簇索引了
　　小结 Clustered Index 是与物理数据混在一起并对物理数据进重排 就像使用拼音查字典;Unclustered Index 是与物理数据完全分离的 利用额外空间对关键字进行重排 就像使用部首查字典
　　数据库索引应用
　　一 索引的概念
　　索引就是加快检索表中数据的方法 数据库的索引类似于书籍的索引 在书籍中 索引允许用户不必翻阅完整个书就能迅速地找到所需要的信息 在数据库中 索引也允许数据库程序迅速地找到表中的数据 而不必扫描整个数据库
　　二 索引的特点
　　 索引可以加快数据库的检索速度
　　 索引降低了数据库插入 修改 删除等维护任务的速度
　　 索引创建在表上 不能创建在视图上
　　 索引既可以直接创建 也可以间接创建
　　 可以在优化隐藏中 使用索引
　　 使用查询处理器执行SQL语句 在一个表上 一次只能使用一个索引
　　 其他
　　三 索引的优点
　　 创建唯一性索引 保证数据库表中每一行数据的唯一性
　　 大大加快数据的检索速度 这也是创建索引的最主要的原因
　　 加速表和表之间的连接 特别是在实现数据的参考完整性方面特别有意义
　　 在使用分组和排序子句进行数据检索时 同样可以显著减少查询中分组和排序的时间
　　 通过使用索引 可以在查询的过程中使用优化隐藏器 提高系统的性能
　　四 索引的缺点
　　 创建索引和维护索引要耗费时间 这种时间随着数据量的增加而增加
　　 索引需要占物理空间 除了数据表占数据空间之外 每一个索引还要占一定的物理空间 如果要建立聚簇索引 那么需要的空间就会更大
　　 当对表中的数据进行增加 删除和修改的时候 索引也要动态的维护 降低了数据的维护速度
lishixinzhi/Article/program/MySQL/201311/29604

mysql索引

在mysql中，索引是一种特殊的数据库结构，由数据表中的一列或多列组合而成，可以用来快速查询数据表中有某一特定值的记录。
通过索引，查询数据时不用读完记录的所有信息，而只是查询索引列即可。
通过索引，查询数据时不用读完记录的所有信息，而只是查询索引列。否则，数据库系统将读取每条记录的所有信息进行匹配。
可以把索引比作新华字典的音序表。例如，要查“库”字，如果不使用音序，就需要从字典的 400 页中逐页来找。但是，如果提取拼音出来，构成音序表，就只需要从 10 多页的音序表中直接查找。这样就可以大大节省时间。
因此，使用索引可以很大程度上提高数据库的查询速度，还有效的提高了数据库系统的性能。
索引的优缺点
索引有其明显的优势，也有其不可避免的缺点。
优点
索引的优点如下：
1、通过创建唯一索引可以保证数据库表中每一行数据的唯一性。
2、可以给所有的 MySQL 列类型设置索引。
3、可以大大加快数据的查询速度，这是使用索引最主要的原因。
4、在实现数据的参考完整性方面可以加速表与表之间的连接。
5、在使用分组和排序子句进行数据查询时也可以显著减少查询中分组和排序的时间
缺点
增加索引也有许多不利的方面，主要如下：
1、创建和维护索引组要耗费时间，并且随着数据量的增加所耗费的时间也会增加。
2、索引需要占磁盘空间，除了数据表占数据空间以外，每一个索引还要占一定的物理空间。如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。
3、当对表中的数据进行增加、删除和修改的时候，索引也要动态维护，这样就降低了数据的维护速度。
使用索引时，需要综合考虑索引的优点和缺点。

「Mysql索引原理（六）」聚簇索引

? ? ? ?本节课主要关注InnoDB，但是这里讨论的原理对于任何支持聚簇索引的存储引擎都是适用的。
? ? ? ?叶子节点包含了全部数据，其他节点只包含索引列。InnoDB将通过主键聚集数据，也就是说上图中的“被索引的列”就是主键列。如果没有定义主键，InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引InnoDB会隐式定义一个主键来作为聚簇索引。
? ? ? ?如果主键比较大的话，那辅助索引将会变的更大，因为 辅助索引的叶子存储的是主键值；过长的主键值，会导致非叶子节点占用占用更多的物理空间
所以建议使用int的auto_increment作为主键
? ? ? ?主键的值是顺序的，所以 InnoDB 把每一条记录都存储在上一条记录的后面。当达到页的最大值时，下一条记录就会写入新的页中。一旦数据按照这种顺序的方式加载，主键页就会近似于被顺序的记录填满。 ? ? ? ?聚簇索引的数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。如果主键不是自增id，那么可以想 象，它会干些什么，不断地调整数据的物理地址、分页，当然也有其他一些措施来减少这些操作，但却无法彻底避免。但，如果是自增的，那就简单了，它只需要一 页一页地写，索引结构相对紧凑，磁盘碎片少，效率也高。 ? ? ? ?因为MyISAM的主索引并非聚簇索引，那么他的数据的物理地址必然是凌乱的，拿到这些物理地址，按照合适的算法进行I/O读取，于是开始不停的寻道不停的旋转。聚簇索引则只需一次I/O。（强烈的对比） ? ? ? ?不过，如果涉及到大数据量的排序、全表扫描、count之类的操作的话，还是MyISAM占优势些，因为索引所占空间小，这些操作是需要在内存中完成的。
? ? ? ?MyISM使用的是非聚簇索引， 非聚簇索引的两棵B+树看上去没什么不同 ，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于 索引树是独立的，通过辅助键检索无需访问主键的索引树 。 ? ? ? ?所以说，聚簇索引性能最好而且具有唯一性，所以非常珍贵，必须慎重设置。 一般要根据这个表最常用的SQL查询方式来进行选择，某个字段作为聚簇索引，或组合聚簇索引 ，这个要看实际情况。
? ? ? ?聚簇索引和非聚簇索引的数据分布有区别，主键索引和二级索引的数据分布也有区别，通常会让人感到困扰和以外，下面通过一个列子来讲解InnoDB和MyISAM是如何存储数据的：
? ? ? ?该表的主键取值1~10000，按照随机顺序插入并使用optimize table命令做了优化。换句话说，数据在磁盘上的存储方式已是最优，但行的顺序是随机的。列col2的值是从1~100之间随机赋值，所以有很多重复的值。
? ? ? ?MyISAM的数据分布很简单，所以先介绍它。MyISAM按照数据插入的顺序存储在磁盘上，如下图所示：
在行的旁边显示行号，从0开始递增。因为行是定长的，所以MyISAM可以从表的开头跳过所需的字节找到需要的行。
col2上的索引
? ? ? ?事实上，MyISAM中主键索引和其他索引在结构上没有什么不同。主键索引就是一个名为PRIMARY的唯一非空索引。
? ? ? ?InnoDB支持聚簇索引，所以使用不同的方式存储同样的数据。
? ? ? ?第一眼看上去，感觉和前面的没什么区别，但是该图显示了整个表，而不是只有索引。因为在InnoDB中，聚簇索引就是表，所以不像MyISAM那样需要独立的行存储，这也是为什么MyISAM索引和数据结构是分开的。 ? ? ? ?聚簇索引的每一个叶子节点都包含了主键值。事务ID、用于事务和MVCC的回滚指针以及所有的剩余列。如果主键是一个列前缀索引，InnoDB也会包含完整的主键列和剩下的其他列。 ? ? ? ?还有一点和MyISAM不同的是，InnoDB的二级索引和聚簇索引很不相同。InnoDB的二级索引的叶子节点中存储的不是“行指针”，而是主键值，并以此作为指向行的“指针”。这样的策略减少了当出现行移动或者数据页分裂时二级索引的维护工作。使用主键值当作指针会让二级索引占用更多的空间，换来的好处是，InnoDB在移动时无需更新二级索引中的这个“指针”。
? ? ? ?我们在来看一下 col2索引 。
? ? ? ?每一个叶子节点包含了索引列（这里是col2），紧接着是主键值（col1），上图我们省略了非叶子节点这样的细节。InnoDB非叶子节点包含了索引列和一个指向下一级节点的指针。 ? ? ? ?最后，以一张图表示InnoDB和MyISAM保存数据和索引的区别。
? ? ? ?前面讲过，最好使用AUTO_INCREMENT自增列来聚集数据，避免随机的、不连续的、值分布范围大的列做聚簇索引，特别是对于I/O密集型的应用。例如，从性能角度考虑，使用UUID来作为聚簇索引则会很糟糕：他使得聚簇索引的插入变得完全随机，这是最坏的情况，使得数据没有任何聚集特性。
? ? ? ?为了演示这一点，我们做两个基准测试：
1、使用证书ID插入userinfo表，和uuid作为主键的userinfo_uuid表
? ? ? ?userinfo_uuid表跟userinfo表除了主键给为UUID，其他字段都一样
? ? ? ?测试这两个表的设计，首先在一个有足够内存容纳索引的服务器上向这两个表各插入100万条记录。然后向两个表继续插入300万数据，使索引的大小超过服务器的内存容量。测试结果如下：
? ? ? ?向UUID主键插入行不仅花费的时间更长，而且索引占用的空间也更大。这一方面是由于主键字段更长，另一方面毫无疑问是由于页分裂和碎片导致的。
? ? ? ?为了明白为什么会这样，来看看往第一个表中插入数据时，索引发生了什么变化。
自整型主键插入
? ? ? ?因为主键是顺序的，所以InnoDB把每一条记录都存在上一条记录的后面。当达到页的最大容量后，下一条记录就会写入到新的页中。一旦数据按照这种顺序的方式加载，主键页就会近似于被顺序的记录填满，这也正是所期望的结果。
UUID插入
? ? ? ?因为新行的主键值不一定比之前插入的大，所以InnoDB无法简单的总是把新行插入到索引的最后，而是需要为新的行寻找合适的位置，通常是已有数据的中间位置，并且分配空间。这会正价很多的额外工作，并导致数据分布不够优化。
缺点：
把这些随机值载入到聚簇索引后，也许需要做一次OPTIMIZE TABLE来重建表并优化页的填充。
结论 ：使用InnoDB时应尽可能地按主键顺序插入数据，并且尽可能地单调增加聚簇键的值来插入新行。

「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'