在以下几种条件下,MySQL就会做全表扫描:
1>数据表是在太小了,做一次全表扫描比做索引键的查找来得快多了。当表的记录总数小于10且记录长度比较短时通常这么做。
2>没有合适用于 ON 或 WHERE 分句的索引字段。
3>让索引字段和常量值比较,MySQL已经计算到常量覆盖了数据表的很大部分,因此做全表扫描应该会来得更快。
4>通过其他字段使用了一个基数很小的索引键。这种情况下,MySQL认为使用索引键需要大量查找,还不如全表扫描来得更快。
对于小表来说,全表扫描通常更合适。但是对大表来说,尝试使用以下技术来避免让优化程序错误地选择全表扫描:
1>执行 ANALYZE TABLE tbl_name 更新要扫描的表的索引键分布。 2>
使用 FORCE INDEX 告诉MySQL,做全表扫描的话会比利用给定的索引更浪费资源。
SELECT * FROM t1, t2 FORCE INDEX WHERE t1.col_name=t2.col_name;
3>启动 mysqld 时使用参数 –max-seeks-for-key=1000 或者执行 SET
max_seeks_for_key=1000
来告诉优化程序,所有的索引都不会导致超过1000次的索引搜索。

摘要:如何优化数据库系统的性能,使其尽可能快地处理各种查询,是本文将要介绍的。索引在优化查询中的作用,包括了索引优化查询的原理,索引是优化查询的最常用也是最有效的的方法,一个数据表,尤其是容量很大的表,建立合适的索引,会使查询的速度提高很大。本文将试图解释并给出MySQL索引的各种功能的一些例子。

MySQL索引的使用

我们首先讨论索引,因为它是加快查询的最重要的工具。还有其他加快查询的技术,但是最有效的莫过于恰当地使用索引了。在
MySQL
的邮件清单上,人们通常询问关于使查询更快的问题。在大量的案例中,都是因为表上没有索引,一般只要加上索引就可以立即解决问题。但这样也并非总是有效,因为优化并非总是那样简单。然而,如果不使用索引,在许多情形下,用其他手段改善性能只会是浪费时间。应该首先考虑使用索引取得最大的性能改善,然后再寻求其他可能有帮助的技术。

本节介绍索引是什么、它怎样改善查询性能、索引在什么情况下可能会降低性能,以及怎样为表选择索引。下一节,我们将讨论
MySQL
的查询优化程序。除了知道怎样创建索引外,了解一些优化程序的知识也是有好处的,因为这样可以更好地利用所创建的索引。某些编写查询的方法实际上会妨碍索引的效果,应该避免这种情况出现。(虽然并非总会这样。有时也会希望忽略优化程序的作用。我们也将介绍这些情况。)

索引对单个表查询的影响

索引被用来快速找出在一个列上用一特定值的行。没有索引,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
子句中与条件相配的行的搜索,或者说在执行连接时加快了与其他表中的行匹配的行的搜索。

多列索引对查询的影响

假定你发出下列SELECT语句:

mysql> SELECT FROM tbl_name WHERE col1=val1 AND col2=val2;*

如果一个多列索引存在于col1和col2上,适当的行可以直接被取出。如果分开的单行列索引存在于col1和col2上,优化器试图通过决定哪个索引将找到更少的行并来找出更具限制性的索引并且使用该索引取行。
你可以这样创建一个多列索引:

mysql>ALTER TABLE tbl_name ADD INDEX(col1,col2);

而你应该这样创建分开的单行列索引:

mysql>ALTER TABLE tble_name ADD INDEX(col1);
mysql>ALTER TABLE tble_name ADD INDEX(col1);

如果表有一个多列索引,任何最左面的索引前缀能被优化器使用以找出行。例如,如果你有一个3行列索引(col1,col2,col3),你已经索引了在(col1)、(col1,col2)和(col1,col2,col3)上的搜索能力。

如果列不构成索引的最左面前缀,MySQL不能使用一个部分的索引。假定你下面显示的SELECT语句:

mysql> SELECT * FROM tbl_name WHERE col1=val1;
mysql> SELECT * FROM tbl_name WHERE col2=val2;
mysql> SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;

如果一个索引存在于(col1、col2、col3)上,只有上面显示的第一个查询使用索引。第二个和第三个查询确实包含索引的列,但是(col2)和(col2、col3)不是(col1、col2、col3)的最左面前缀。

如果LIKE参数是一个不以一个通配符字符起始的一个常数字符串,MySQL也为LIKE比较使用索引。例如,下列SELECT语句使用索引:

mysql> select * from tbl_name where key_col LIKE "Patrick%";
mysql> select * from tbl_name where key_col LIKE "Pat%_ck%";

发表评论

电子邮件地址不会被公开。 必填项已用*标注