`

【转】一个例子与InnoDB索引的几个概念

阅读更多

 

1、一个简单的sql语句问题

    假设当前我们有一个表记录用户信息,结构如下:

    a)      表结构

CREATE TABLE `u` (

  `id` int(11) NOT NULL DEFAULT ’0′,

  `regdate` int(1) unsigned,

  …..

  PRIMARY KEY (`id`),

  KEY `regdate` (`regdate`)

) ENGINE=InnoDB DEFAULT CHARSET=gbk

说明:1) 由于需要按照注册时间单独查询,建了一个regdate的索引

            2) 其他信息未列出, 一行长度100字节左右,表行数百万级。 

b)      需求:需要一个语句查出表中id为10000整数倍的记录总数。

 

2、常规答案

    一个正常想到的语句是 select sum(id % 10000 = 0) from u; —— (SQL1)

    我们来看这个语句的执行流程:

a)      遍历所有数据,取出id字段

b)      计算id%10000=0的值并通过sum累计。

           在构造的环境中这个语句的执行时间为2.6s.

 

3、查的多,查得快

    假设我们同时要查出注册时间在2007年之前的用户总数,我们自然得到这个语句

     select sum(id % 10000 = 0), sum(regdate<1167667200) from sbtest;—-(SQL2)

    执行结果发现这个语句执行时间约0.5s 。 这个语句查的数据结果比SQL1多,但执行时间却降为1/5.

 

4、分析 

    可以直接从执行期间的磁盘参数,或者在os/os0file.c中将程序读取的数据量输出结果查看,直观结果是SQL1读取了更多的磁盘数据。

问题1:在SQL1执行过程中,遍历所有数据,InnoDB只从磁盘读取了id这个字段,还是全部读入?

    实际上由于id是聚簇索引,并没有一个单独的索引树存id,因此在磁盘上,id索引树的叶节点上就是数据。 InnoDB以page为单位读取,在取id的过程中,必须将所有的数据读入。

    于是我们发现,在SQL1中,我们只需要id字段,而每行额外读入了几百字节的数据。

问题2:SQL2避免了读全数据?

    确实如此。

    我们对比两个语句的explain结果, 发现仅有的不同是选用的key结果不同。

SQL1 SQL2
key: PRIMARY key: regdate

    由于regdate是非聚簇(secondary index)索引,单独存于另一棵树。 我们知道使用非聚簇索引时,需要读行数据的时候,需要再到聚簇索引中取得。显然SQL2不会再读一遍全数据(否则性能必然低于SQL1)。

    而其原因是覆盖索引(covering index)。 非聚簇索引的叶节点上是聚簇索引的字段值,需要取数据时,根据这个值再去聚簇索引上取。而这时InnoDB变“聪明”了, 需要取的值只是id,而id作为聚簇索引的key信息,已经得到,不需要再到聚簇索引中读取数据。

    由于regdate索引树上只有regdate和主键(id)的信息,因此数据量远小于全表数据,因此SQL2的读盘量小于SQL1,执行速度快。

 

5、其他 

    这个例子涉及到几个概念, 聚簇索引(cluster index)、非聚簇索引(secondary index), 覆盖索引(covering index),还有磁盘的数据存放。都算是一些基本的内容,却是平时见到的一些优化的理论基础。举几个例子如下:

1)      我们经常被告诫select之后只填最必须的字段

    其中的一个原因是减少网络传输。但不一定能够提升服务器执行性能。比如例子中的表,select  * from u where id = n; 与select user_name from u where id =n一样。

    当然有些时候效果会很理想,比如 select id from u where regdate=xxx 就比select * from u where regdate=xxx快很多,原因已说明。

2)      查询符合条件的第10w个记录开始的10个记录。

    这个例子在其他博文上被多次提及,

select * from t order by a limit 100000, 10; 可以改进为

select * from t where a>=(select a from t order by a limit 100000,1) limit 10;

    在笔者环境中性能提升约1000倍。

    原因即在于, 改进语句中,子查询中的排序只在非聚餐索引a上执行,由于覆盖索引,排序过程不需要访问聚簇索引。实际读读取全数据的只有10条记录,而原语句则需要读所有记录的全数据。

    当然执行排序的过程消耗是一样的。 

6、结束

    回到开头,如果只需要查id满足特定条件的记录总数,可以使用select sum(id % 10000 = 0) from u force index (`regdate`);  

    把sum(id %10000=0)换成其他操作对执行效率均没有影响。 

    但若查询内容中出现除id和regdate外的其他字段,则force index优化无效,可自行分析。

转载地址:http://rdc.taobao.com/blog/cs/?p=406

------------------------------------------------------------------------------------------------

 

附:什么叫覆盖索引?

 

覆盖索引是在选择条件和 WHERE 谓词上均满足 SQL 查询的所有列的基础上建立的非聚集索引。覆盖索引可以节省大量的 I/O,因此可极大地改善查询的性能。但是有必要在新建索引(以及与它相关的 B 树索引结构维护,如:数据量增多,更新速度较慢)所需要的代价和覆盖索引所带来的 I/O 性能增益之间进行权衡。如果覆盖索引对于 MySQL上经常运行的查询极其有利,那么创建覆盖索引是值得的。 

覆盖索引的示例 
  
  Select col1,col3 from table1 where col2 = 'value'. 
  Create index index_name1 on table1(col2,col1,col3).

 

index_name1 就是一个覆盖性索引。

覆盖索引(covering index),MySQL只需要通过索引就可以返回查询所需要的数据,而不必在查到索引之后再去查询数据,所以那是相当的快!!但是同时也要求所查询的字 段必须被索引所覆盖到,在Explain的时候,输出的Extra信息中如果有“Using Index”,就表示这条查询使用了覆盖索引。


 

  • 大小: 26 KB
分享到:
评论

相关推荐

    MySQL索引检查.docx

    以下将详细介绍标题和描述中涉及的几个关键知识点。 首先,环境建设是进行索引检查的基础。在例子中,我们创建了一个名为`admin`的表,包含四个字段:`id`(主键)、`name`(帐号)、`pwd`(密码)和`createtime`...

    VIP-mysql索引优化实战一.pdf

    接下来,我们通过几个示例查询来深入理解索引的工作原理及其优化方法: 1. **普通查询**: ```sql EXPLAIN SELECT * FROM employees WHERE name &gt; 'a'; ``` 这个查询会尝试查找所有`name`字段大于'a'的记录。...

    Mysql数据库索引(2)- 为select设计索引- 自学笔记

    基本问题法是一种评估索引是否有效的简单方法,主要包括以下几个步骤: 1. **分析查询需求**:明确查询中涉及到的字段和操作类型。 2. **确定索引字段**:根据查询需求选择合适的字段创建索引。 3. **评估索引效果*...

    MySQL全文索引应用简明教程.pdf

    全文索引的几个关键特性包括: 1. **词长度阈值**:默认情况下,MySQL全文索引会忽略少于4个字符的单词,可通过`ft_min_word_len`配置改变这一设置。 2. **屏蔽词表**:MySQL有一个内置的屏蔽词列表,包含常见的无...

    MYSQL查询调优实战

    文章中还提供了一个具体的例子,即创建UserInfo表的SQL语句,展示了如何为表创建聚集索引和辅助索引。在UserInfo表中,userid字段作为主键,使用了聚集索引;而username和registdate字段则创建了辅助索引,这有助于...

    mysql索引覆盖实例分析

    有一个名为`A`的表,包含一个联合索引`id, ver`,并且有几个很长的`varbinary(3000)`字段。对于以下两个查询: 1. `SELECT id FROM A ORDER BY id;` 2. `SELECT id FROM A ORDER BY id, ver;` 第一个查询比第二个...

    MySQL查询条件中in会用到索引吗

    在上述例子中,我们创建了一个名为`pre_request_logs_20180524`的表,并为`port`字段建立了名为`idx_port`的索引。这是个B树索引,常见于MySQL中的InnoDB存储引擎。现在,如果我们对`port`字段使用`IN`操作符进行...

    MySQL中InnoDB的间隙锁问题

    2. 死锁检测与回滚:InnoDB存储引擎具有死锁检测机制,当发现死锁时,自动回滚其中一个或多个事务以解决死锁。 3. 事务隔离级别调整:将事务隔离级别调整为RC(Read Committed)可以避免间隙锁,但会牺牲一些数据...

    mysqlDML命令例子

    根据提供的文件信息,我们可以归纳出以下几个关键的知识点: ### 1. 数据库创建与表结构定义 #### 创建数据库 `brickwork` ```sql CREATE DATABASE /*!32312 IF NOT EXISTS*/ `brickwork` /*!40100 DEFAULT ...

    索引优化实践

    在这些例子中,`ref`表示使用了索引的非唯一部分来查找行,`const`表示由于主键或唯一键的存在,MySQL能确定只有一行匹配,因此可以快速定位。 优化索引时,应考虑以下几点: 1. **选择合适的索引类型**:例如,B...

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

    在MySQL中,全文索引的创建和使用有以下几个关键点: 1. **设置全文索引**: - 在MySQL的表设计中,可以为`char`、`varchar`和`text`类型的字段设置全文索引。在MySQL的管理工具中,可以通过相应的选项(如“全文...

    MySQL 5_1 中文手册

    在MySQL 5.1中,有几个关键知识点是每个开发者或管理员都需要掌握的: 1. **SQL语言基础**:MySQL支持标准的结构化查询语言(SQL),包括数据查询、数据插入、更新和删除等操作。掌握SELECT语句、JOIN操作、子查询...

    数据库常用面试题目_基础题

    - 描述SELECT语句的基本结构,并给出一个包含WHERE子句和JOIN操作的例子。 - 如何使用SQL创建视图,以及视图的作用是什么? 4. **索引与性能优化** - 什么是数据库索引,其工作原理是什么? - 如何选择合适的...

    深入解析:MySQL存储引擎的奥秘

    在这个例子中,我们创建了一个名为`Employees`的表,并指定了使用InnoDB存储引擎。InnoDB支持事务处理和行级锁定,适合需要高并发操作和数据一致性的场合。 2. **创建 MyISAM 表** ```sql CREATE TABLE Logs ( ...

    mysql面试题 92问

    - 描述一下数据库的事务,并给出一个使用事务的例子。 2. **数据类型** - 举例说明MySQL中的数值类型,如INT、FLOAT、DECIMAL等的用途。 - 何时应使用VARCHAR而不是CHAR? 3. **表操作** - 如何创建一个包含...

    mysql5.6.19下子查询为什么无法使用索引

    MySQL中的子查询与索引的使用是一个复杂的话题,特别是在不同版本之间可能会存在性能差异。这里我们探讨一下在MySQL 5.6.19版本中,为何子查询可能无法充分利用索引,以及可能导致性能下降的原因。 首先,让我们...

    mysql面试题.zip

    在MySQL面试中,以下几个主题通常是考察的重点: 1. **MySQL基础知识**:面试可能会从基本概念开始,如数据库、表、字段、索引、主键和外键。理解这些概念对于数据库设计至关重要。 2. **SQL语言**:熟练掌握SQL...

    MySQL外键详解

    #### 一、外键的基本概念与作用 外键(Foreign Key)是一种重要的数据库约束机制,主要用于确保数据库中多个表之间数据的一致性和完整性。它通过引用另一个表的主键来实现这一目标。外键的存在有助于防止在相关联的...

Global site tag (gtag.js) - Google Analytics