mysql索引覆盖实例分析

本文实例讲述了mysql索引覆盖。分享给大家供大家参考，具体如下：

索引覆盖

如果查询的列恰好是索引的一部分,那么查询只需要在索引文件上进行,不需要回行到磁盘再找数据。这种查询速度非常快,称为“索引覆盖”。

假设有一张t15表，在表中建立了一个联合索引：cp（cat_id,price）

当我们使用下面的sql语句，会出现索引覆盖的情况。不信我们可以来查看一下，这里的Extra中显示了Using index，表示这条sql语句刚好用到了索引覆盖。

select price from t15 where cat_id = 1;

在来看一题，创建一张t11表，在email列有一个索引

假设我们用这样的查询语句：

select eamil from  t11 where right(email,4)='.com'

查询分析：

首先看一下Extra，这里有Using index，说明这里使用到了索引覆盖，而possible_keys为NULL的原因是因为，使用到了mysql中的函数，所以在查询的时候并没有使用到email索引，但是key却为email，表示了使用到了索引进行排序，不信我把数据打印看看。

这里的数据是经过排序的。原本的数据是这样的。

索引覆盖的问题

create table A (
id varchar(64) primary key,
ver int,
…
)

表中有几个很长的字段varbinary(3000) ，在id、ver上有联合索引 ，共10000条数据

为什么select id from A order by id特别慢？
而select id from A order by id,ver非常快

疑问: id, (id,ver)都有索引, select id 应该都产生”索引覆盖”的效果,为什么前者慢,而后快？

思路：innodb聚簇与myisam索引的不同 , 索引覆盖 这2个角度来考虑

（1）假设此表使用的是Myisam的索引，那么这两条sql语句都不需要回行查找数据，那么他们的速度应该差不多。

（2）假设此表使用的是InnoDB的索引，那么select id from A order by id这句sql使用到了主键索引，因为InnoDB的每个主键都挂载这每行的数据，并且本题中还有几个特别大的字段，所以会在查找id的时候需要走的相对慢；而select id from A order by id,ver这句sql使用到了id,ver联合索引，在InnoDB存储引擎中，次索引保存的是对主键索引的应用，所以次索引不挂载该行的数据，那么在(id,ver)索引中查找id会快，当找到对应的节点树时，只需要再次查找到主键索引的位置，即可拿到该行的数据，这样比较快。

推断:

（1）表如果是myisam引擎,2个语句,速度不会有明显差异.
（2）innodb表因为聚簇索引,id索引要在磁盘上跨N多块,导致速度慢.
（3）即使innodb引擎,如果没有那几个varbinay长列, 2个语句的速度也不会有明显差异.

t12表，存储引擎为MyISAM，有主键索引和（id,ver）符合索引，还有几个大的变长字段，测试推论1

t13表，存储引擎为InnoDB，有主键索引和（id,ver）符合索引，还有几个大的变长字段推论2

t14表，存储引擎为InnoDB，有主键索引和（id,ver）符合索引，没有几个大的变长字段推论3

t12,t13,t14每张表中都有1W条数据，然后进行测试，测试结果如下，我们的推论是正确的。

更多关于MySQL相关内容感兴趣的读者可查看本站专题：《MySQL索引操作技巧汇总》、《MySQL常用函数大汇总》、《MySQL日志操作技巧大全》、《MySQL事务操作技巧汇总》、《MySQL存储过程技巧大全》及《MySQL数据库锁相关技巧汇总》

希望本文所述对大家MySQL数据库计有所帮助。

赞 (0)

相关推荐

  • 解决MySQL中IN子查询会导致无法使用索引问题

    解决MySQL中IN子查询会导致无法使用索引问题

    今天看到一篇关于MySQL的IN子查询优化的案例, 一开始感觉有点半信半疑(如果是换做在SQL Server中,这种情况是绝对不可能的,后面会做一个简单的测试.) 随后动手按照他说的做了一个表来测试验证,发现MySQL的IN子查询做的不好,确实会导致无法使用索引的情况(IN子查询无法使用所以,场景是MySQL,截止的版本是5.7.18) MySQL的测试环境 测试表如下 create table test_table2 ( id int auto_increment primary key, p

  • MYSQL中常用的强制性操作（例如强制索引）

    其他强制操作,优先操作如下: mysql常用的hint 对于经常使用oracle的朋友可能知道,oracle的hint功能种类很多,对于优化sql语句提供了很多方法.同样,在mysql里,也有类似的hint功能.下面介绍一些常用的. 强制索引 FORCE INDEX 复制代码 代码如下: SELECT * FROM TABLE1 FORCE INDEX (FIELD1) - 以上的SQL语句只使用建立在FIELD1上的索引,而不使用其它字段上的索引. 忽略索引 IGNORE INDEX 复制代码

  • mysql索引对排序的影响实例分析

    本文实例讲述了mysql索引对排序的影响.分享给大家供大家参考,具体如下: 索引不仅能提高查询速度,还可以添加排序速度,如果order by 后面的语句用到了索引,那么将会提高排序的速度. 测试 1.创建测试表:t15表 CREATE TABLE `t15` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `cat_id` int(10) unsigned NOT NULL DEFAULT '0', `price` decimal(10,2) N

  • MySQL 主键与索引的联系与区别分析

    关系数据库依赖于主键,它是数据库物理模式的基石.主键在物理层面上只有两个用途: 惟一地标识一行. 作为一个可以被外键有效引用的对象. 索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分),它们包含着对数据表里所有记录的引用指针.下面是主键和索引的一些区别与联系. 1. 主键一定是唯一性索引,唯一性索引并不一定就是主键. 所谓主键就是能够唯一标识表中某一行的属性或属性组,一个表只能有一个主键,但可以有多个候选索引.因为主键可以唯一标识某一行记录,所以可以确保执行数据更新.删除的

  • MySQL索引类型总结和使用技巧以及注意事项

    在数据库表中,对字段建立索引可以大大提高查询速度.假如我们创建了一个 mytable表: 复制代码 代码如下: CREATE TABLE mytable(   ID INT NOT NULL,    username VARCHAR(16) NOT NULL  ); 我们随机向里面插入了10000条记录,其中有一条:5555, admin. 在查找username="admin"的记录 SELECT * FROM mytable WHERE username='admin';时,如果在

  • MySQL全文索引实现简单版搜索引擎实例代码

    前言 只有Innodb和myisam存储引擎能用全文索引(innodb支持全文索引是从mysql5.6开始的) char.varchar.text类型字段能创建全文索引(fulltext index type) 全文索引的基于关键词的,如何区分不同的关键词了,就要用到分词(stopword) 英文单词用空格,逗号进行分词:中文分词不方便(一个句子不知道怎样区分不同的关键词) 内置分词解析器ngram支持中文,日文,韩文(将句子分成固定数字的短语) 当对表写入大量数据时,写入数据后再创建全文索引的

  • MySQL查看、创建和删除索引的方法

    本文实例讲述了MySQL查看.创建和删除索引的方法.分享给大家供大家参考.具体如下: 1．索引作用 在索引列上,除了上面提到的有序查找之外,数据库利用各种各样的快速定位技术,能够大大提高查询效率.特别是当数据量非常大,查询涉及多个表时,使用索引往往能使查询速度加快成千上万倍. 例如,有3个未索引的表t1.t2.t3,分别只包含列c1.c2.c3,每个表分别含有1000行数据组成,指为1-1000的数值,查找对应值相等行的查询如下所示. SELECT c1,c2,c3 FROM t1,t2,t3

  • Mysql性能优化案例研究-覆盖索引和SQL_NO_CACHE

    Mysql性能优化案例研究-覆盖索引和SQL_NO_CACHE

    场景 产品中有一张图片表pics,数据量将近100万条,有一条相关的查询语句,由于执行频次较高,想针对此语句进行优化 表结构很简单,主要字段: 复制代码 代码如下: user_id 用户ID picname 图片名称 smallimg 小图名称 一个用户会有多条图片记录,现在有一个根据user_id建立的索引:uid,查询语句也很简单:取得某用户的图片集合: 复制代码 代码如下: select picname, smallimg from pics where user_id = xxx; 优化

  • Mysql覆盖索引详解

    Mysql覆盖索引详解

    概念 如果索引包含所有满足查询需要的数据的索引成为覆盖索引(Covering Index),也就是平时所说的不需要回表操作 判断标准 使用explain,可以通过输出的extra列来判断,对于一个索引覆盖查询,显示为using index,MySQL查询优化器在执行查询前会决定是否有索引覆盖查询 注意 1.覆盖索引也并不适用于任意的索引类型,索引必须存储列的值 2.Hash 和full-text索引不存储值,因此MySQL只能使用B-TREE 3.并且不同的存储引擎实现覆盖索引都是不同的 4.并

  • Mysql性能优化案例 - 覆盖索引分享

    Mysql性能优化案例 - 覆盖索引分享

    场景 产品中有一张图片表,数据量将近100万条,有一条相关的查询语句,由于执行频次较高,想针对此语句进行优化 表结构很简单,主要字段: 复制代码 代码如下: user_id 用户ID picname 图片名称 smallimg 小图名称 一个用户会有多条图片记录 现在有一个根据user_id建立的索引:uid 查询语句也很简单:取得某用户的图片集合 复制代码 代码如下: select picname, smallimg from pics where user_id = xxx; 优化前 执行查

  • mysql 添加索引 mysql 如何创建索引

    1.添加PRIMARY KEY(主键索引) mysql>ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) 2.添加UNIQUE(唯一索引) mysql>ALTER TABLE `table_name` ADD UNIQUE ( `column` ) 3.添加INDEX(普通索引) mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column` ) 4.添加FULLTEX

  • 基于mysql全文索引的深入理解

    基于mysql全文索引的深入理解

    前言:本文简单讲述全文索引的应用实例,MYSQL演示版本5.5.24. Q:全文索引适用于什么场合? A:全文索引是目前实现大数据搜索的关键技术. 至于更详细的介绍请自行百度,本文不再阐述. -------------------------------------------------------------------------------- 一.如何设置? 如图点击结尾处的{全文搜索}即可设置全文索引,不同MYSQL版本名字可能不同. 二.设置条件 1.表的存储引擎是MyISAM,默认

随机推荐