概述:本小结讲述了索引在优化查询中的作用,包括了索引优化查询的原理,索引在各种情况的检索中的益处,也包括索引的的弊端:增加了存储的空间,使装载数据变慢。
索引是优化查询的最常用也是最有效的的方法,一个数据表,尤其是容量很大的表,建立合适的索引,会使查询的速度提高很大.
一:索引对单个表查询的影响
索引被用来快速找出在一个列上用一特定值的行。没有索引,MySQL不得不首先以第一条记录开始并然后读完整个表直到它找出相关的行。表越大,花费时间越多。如果表对于查询的列有一个索引,MySQL能快速到达一个位置去搜寻到数据文件的中间,没有必要考虑所有数据。如果一个表有1000行,这比顺序读取至少快100倍。注意你需要存取几乎所有1000行,它较快的顺序读取,因为此时我们避免磁盘寻道。
例如对下面这样的一个student表:
mysql>SELECT * FROM student
+------+---------+---------+---------+---------+
| id | name | english | chinese | history |
+------+---------+---------+---------+---------+
| 12 | Tom | 66 | 93 | 67 |
| 56 | Paul | 78 | 52 | 75 |
| 10 | Marry | 54 | 89 | 74 |
| 4 | Tina | 99 | 83 | 48 |
| 39 | William | 43 | 96 | 52 |
| 74 | Stone | 42 | 40 | 61 |
| 86 | Smith | 49 | 85 | 78 |
| 37 | Black | 49 | 63 | 47 |
| 89 | White | 94 | 31 | 52 |
+------+---------+---------+---------+---------+
这样,我们试图对它进行一个特定查询时,就不得不做一个全表的扫描,速度很慢。例如,我们查找出所有english成绩不及格的学生:
mysql>SELECT name,english FROM student WHERE english<60;
+---------+---------+
| name | english |
+---------+---------+
| Marry | 54 |
| William | 43 |
| Stone | 42 |
| Smith | 49 |
| Black | 49 |
+---------+---------+
其中,WHERE从句不得不匹配每个记录,以检查是否符合条件。对于这个较小的表也许感觉不到太多的影响。但是对于一个较大的表,例如一个非常大的学校,我们可能需要存储成千上万的记录,这样一个检索的所花的时间是十分可观的。
如果,我们为english列创建一个索引:
mysql>ALTER TABLE student ADD INDEX (english) ;
+-------------------+
| index for english |
+-------------------+
| 42 |
| 43 |
| 49 |
| 49 |
| 54 |
| 66 |
| 78 |
| 94 |
| 99 |
+-------------------+
如上表,此索引存储在索引文件中,包含表中每行的english列值,但此索引是在 english的基础上排序的。现在,不需要逐行搜索全表查找匹配的条款,而是可以利用索引进行查找。假如我们要查找分数小于60的所有行,那么可以扫描索引,结果得出5
行。然后到达分数为66的行,及Tom的记录,这是一个比我们正在查找的要大的值。索引值是排序的,因此在读到包含Tom的记录时,我们知道不会再有匹配的记录,可以退出了。如果查找一个值,它在索引表中某个中间点以前不会出现,那么也有找到其第一个匹配索引项的定位算法,而不用进行表的顺序扫描(如二分查找法)。这样,可以快速定位到第一个匹配的值,以节省大量搜索时间。数据库利用了各种各样的快速定位索引值的技术,这些技术是什么并不重要,重要的是它们工作正常,索引技术是个好东西。
因此在执行下述查询
mysql>SELECT name,english FROM user WHERE english<60;
其结果为:
+---------+---------+
| name | english |
+---------+---------+
| Stone | 42 |
| William | 43 |
| Smith | 49 |
| Black | 49 |
| Marry | 54 |
+---------+---------+
你应该可以发现,这个结果与未索引english列之前的不同,它是排序的,原因正式如上所述。
二:索引对多个表查询的影响
假如有三个未索引的表 t1、t2、t3,分别只包含列 c1、c2、c3,每个表分别由含有数值 1 到 1000 的 1000 行组成。查找对应值相等的表行组合的查询如下所示:
此查询的结果应该为 1000 行,每个组合包含 3 个相等的值。如果我们在无索引的情况下处理此查询,则不可能知道哪些行包含那些值。因此,必须寻找出所有组合以便得出与 WHERE 子句相配的那些组合。可能的组合数目为 1000×1000×1000(十亿),比匹配数目多一百万倍。很多工作都浪费了,并且这个查询将会非常慢,即使在如像 MySQL 这样快的数据库中执行也会很慢。而这还是每个表中只有 1000 行的情形。如果每个表中有一百万行时,将会怎样?很显然,这样将会产生性能极为低下的结果。如果对每个表进行索引,就能极大地加速查询进程,因为利用索引的查询处理如下:
1) 如下从表 t1 中选择第一行,查看此行所包含的值。
2) 使用表 t2 上的索引,直接跳到 t2 中与来自 t1 的值匹配的行。类似,利用表 t3 上的索引,直接跳到 t3 中与来自 t1 的值匹配的行。
3) 进到表 t1 的下一行并重复前面的过程直到 t1 中所有的行已经查过。
在此情形下,我们仍然对表 t1 执行了一个完全扫描,但能够在表 t2 和 t3 上进行索引查找直接取出这些表中的行。从道理上说,这时的查询比未用索引时要快一百万倍。
如上所述,MySQL 利用索引加速了 WHERE 子句中与条件相配的行的搜索,或者说在执行连接时加快了与其他表中的行匹配的行的搜索。
分享到:
相关推荐
索引优化优化优化优化
Oracle索引优化。Oracle索引优化。Oracle索引优化
在一些情况下,MySQL可以使用索引优化DISTINCT操作,但需要活学活用.本文涉及一个不能利用索引完成DISTINCT操作的实例. 实例1 使用索引优化DISTINCT操作 create table m11 (a int, b int, c int, d int, primary ...
04-VIP-Mysql索引优化实战一.pdf
MySQl索引优化课件,详细得介绍了MySQl索引优化数据库
数据库 索引 优化 数据库索引好比是一本书前面的目录,能加快数据库的查询速度。 例如这样一个查询:select * from table1 where id=44。如果没有索引,必须遍历整个表,直到ID等于44的这一行被找到为止;有了索引...
有关mysql索引优化方面的文档,比较实用,希望能够对大家有帮助。
05-VIP-Mysql索引优化实战二.pdf
Oracle数据库经典优化之索引原理篇 Oracle中建立索引并强制优化器 基于索引的SQL语句优化之降龙十八掌 30个Oracle语句优化规则详解-性能调优
数据库优化工具,连接后选择数据库会自动在大的表添加索引
数据库面试题索引sql优化数据库面试题索引sql优化数据库面试题索引sql优化数据库面试题索引sql优化数据库面试题索引sql优化数据库面试题索引sql优化数据库面试题索引sql优化数据库面试题索引sql优化数据库面试题索引...
JAVA面试题MySQL索引原理及索引优化校招面试找工作笔试 目录: 基本概念 MySQL索引结构的分类 Hash索引、B+树索引、全文索引、RTree索引。 B+树索引 B+树介绍,为什么选择B+树,非聚集索引 聚集索引 第一点、第二点...
关于mysql索引一些优化介绍与创建原则,还有对order by排序的算法的介绍等等
sql数据库优化--使用索引优化存储过程,相信你对数据库的理解一定不会少!对于你开发和设计数据库是不可少的哟!
数据库索引设计和优化 经典的数据库索引的书籍......................
在进行索引优化时,有几个关键点需要注意。首先是选择合适的索引列,通常选择经常用于查询的列作为索引列,尽量避免使用长文本或二进制类型的列作为索引列。其次是合理设置索引的顺序,根据查询的频率和字段选择合适...
Mysql索引优化案例.pdf
Oracle数据库索引优化方法探析.pdf
以及表和索引的扫描方式,详尽地讲解了如何快速地估算SQL 运行的CPU 时间及执行时间,帮助读者从原理上理解SQL、表及索引结构、访问方式等对关系型数据库造成的影响,并能够运用量化的方法进行判断和优化,指导关系...
mysql索引的优化方案技术分享,珍贵资料收藏下