至于Mysql索引那一点事,MySQL优化之索引的应用

关系数据库的世界是一个表与集合、表与集合上的运算占统治地位的世界。数据库是一个表的集合,而表又是行和列的集合。在发布一条SELECT 查询从表中进行检索行时,得到另一个行和列的集合。这些都是一些抽象的概念,对于数据库系统用来操纵表中数据的基本表示没有多少参考价值。另一个抽象概念是,表上的运算都同时进行;查询是一种概念性的集合运算,并且集合论中没有时间概念。当然,现实世界是相当不同的。数据库管理系统实现了抽象的概念,但是在实际的硬件  范围内要受到实际的物理约束。结果是,查询要花时间,有时要花很长的时间。而人类很容易不耐烦,不喜欢等待,因此我们丢下了集合上的那些瞬间的数学运算的抽象世界去寻求加速查询的方法。幸运的是,有几种加速运算的技术,可对表进行索引使数据库服务器查找行更快。可考虑怎样充分利用这些索引来编写查询。可编写影响服务器调度机制的查询,使来自多个客户机的查询协作得更好。我们思考基本硬件怎样运行,以便想出怎样克服其物理约束对性能进行改善的方法。

MySQL索引的使用

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

 这些正是本文所要讨论的问题,其目标是优化数据库系统的性能,使其尽可能快地处理各种查询。MySQL已经相当快了,但即使是最快的数据库,在人的设计下还能运行得更快。

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

MySQL索引的使用

1 使用索引

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

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

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

索引对单个表查询的影响

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

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

索引被用来快速找出在一个列上用一特定值的行。没有索引,MySQL不得不首先以第一条记录开始并然后读完整个表直到它找出相关的行。表越大,花费时间越多。如果表对于查询的列有一个索引,MySQL能快速到达一个位置去搜寻到数据文件的中间,没有必要考虑所有数据。如果一个表有1000行,这比顺序读取至少快100倍。注意你需要存取几乎所有1000行,它较快的顺序读取,因为此时我们避免磁盘寻道。

索引对单个表查询的影响

1.1 索引的益处

例如对下面这样的一个student表:

索引被用来快速找出在一个列上用一特定值的行。没有索引,MySQL不得不首先以第一条记录开始并然后读完整个表直到它找出相关的行。表越大,花费时间越多。如果表对于查询的列有一个索引,MySQL能快速到达一个位置去搜寻到数据文件的中间,没有必要考虑所有数据。如果一个表有1000 行,这比顺序读取至少快100倍。注意你需要存取几乎所有1000行,它较快的顺序读取,因为此时我们避免磁盘寻道。

让我们从一个无索引的表着手来考察索引是怎样起作用的。无索引的表就是一个无序的行集。例如,图4

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

例如对下面这样的一个student表:

  • 1给出了我们在第1章“MySQL与SQL 介绍” 中首先看到的ad 表。这个表上没有索引,因此如果我们查找某个特定公司的行时,必须查看表中的每一行,看它是否与所需的值匹配。这是一个全表扫描,很慢,如果表中只有少数几个记录与搜索条件相匹配,则其效率是相当低的。

这样,我们试图对它进行一个特定查询时,就不得不做一个全表的扫描,速度很慢。例如,我们查找出所有english成绩不及格的学生:

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

图片 1

mysql>SELECT name,english FROM student WHERE english<60;
--------- ---------
| name    | english |
--------- ---------
| Marry   |      54 |
| William |      43 |
| Stone   |      42 |
| Smith   |      49 |
| Black   |      49 |
--------- ---------

这样,我们试图对它进行一个特定查询时,就不得不做一个全表的扫描,速度很慢。例如,我们查找出所有english成绩不及格的学生:

图4 - 2给出了相同的表,但在表的company_num 列上增加了一个索引。此索引包含表中每行的一项,但此索引是在company_num 上排序的。现在,不需要逐行搜索全表查找匹配的条款,而是可以利用索引进行查找。假如我们要查找公司13的所有行,那么可以扫描索引,结果得出3行。然后到达公司14的行,这是一个比我们正在查找的要大的号码。索引值是排序的,因此在读到包含14的记录时,我们知道不会再有匹配的记录,可以退出了。如果查找一个值,它在索引表中某个中间点以前不会出现,那么也有找到其第一个匹配索引项的定位算法,而不用进行表的顺序扫描(如二分查找法)。这样,可以快速定位到第一个匹配的值,以节省大量搜索时间。数据库利用了各种各样的快速定位索引值的技术,这些技术是什么并不重要,重要的是它们工作正常,索引技术是个好东西。

其中,WHERE从句不得不匹配每个记录,以检查是否符合条件。对于这个较小的表也许感觉不到太多的影响。但是对于一个较大的表,例如一个非常大的学校,我们可能需要存储成千上万的记录,这样一个检索的所花的时间是十分可观的。

mysql>SELECT name,english FROM student WHERE english<60;
--------- ---------
| name    | english |
--------- ---------
| Marry   |      54 |
| William |      43 |
| Stone   |      42 |
| Smith   |      49 |
| Black   |      49 |
--------- ---------

有人会问,为什么不只对数据文件进行排序,省掉索引文件?这样不也在搜索时产生相同的效果吗?问得好,如果只有单个索引时,是这样的。不过有可能会用到第二个索引,但同时以两种不同的方法对同一个数据文件进行排序是不可能的。(如,想要一个顾客名的索引,同时又要一个顾客ID 号或电话号码的索引。)将索引文件作为一个与数据文件独立的实体就解决了这个问题,而且允许创建多个索引。此外,索引中的行一般要比数据文件中的行短。在插入或删除值时,为保持排序顺序而移动较短的索引值与移动较长的数据行相比更为容易。

如果,我们为english列创建一个索引:

其中,WHERE从句不得不匹配每个记录,以检查是否符合条件。对于这个较小的表也许感觉不到太多的影响。但是对于一个较大的表,例如一个非常大的学校,我们可能需要存储成千上万的记录,这样一个检索的所花的时间是十分可观的。

图片 2

mysql>ALTER TABLE student ADD INDEX (english) ;
-------------------
| index for english |
-------------------
|                42 |
|                43 |
|                49 |
|                49 |
|                54 |
|                66 |
|                78 |
|                94 |
|                99 |
-------------------

如果,我们为english列创建一个索引:

这个例子与MySQL索引表的方法相符。表的数据行保存在数据文件中,而索引值保存在索引文件中。一个表上可有不止一个索引;如果确实有不止一个索引,它们都保存在同一个索引文件中。索引文件中的每个索引由排过序的用来快速访问数据文件的键记录数组构成。

如上表,此索引存储在索引文件中,包含表中每行的english列值,但此索引是在 english的基础上排序的。现在,不需要逐行搜索全表查找匹配的条款,而是可以利用索引进行查找。假如我们要查找分数小于60的所有行,那么可以扫描索引,结果得出5行。然后到达分数为66的行,及Tom的记录,这是一个比我们正在查找的要大的值。索引值是排序的,因此在读到包含Tom的记录时,我们知道不会再有匹配的记录,可以退出了。如果查找一个值,它在索引表中某个中间点以前不会出现,那么也有找到其第一个匹配索引项的定位算法,而不用进行表的顺序扫描如二分查找法)。这样,可以快速定位到第一个匹配的值,以节省大量搜索时间。数据库利用了各种各样的快速定位索引值的技术,这些技术是什么并不重要,重要的是它们工作正常,索引技术是个好东西。

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的记录时,我们知道不会再有匹配的记录,可以退出了。如果查找一个值,它在索引表中某个中间点以前不会出现,那么也有找到其第一个匹配索引项的定位算法,而不用进行表的顺序扫描(如二分查找法)。这样,可以快速定位到第一个匹配的值,以节省大量搜索时间。数据库利用了各种各样的快速定位索引值的技术,这些技术是什么并不重要,重要的是它们工作正常,索引技术是个好东西。

假如有三个未索引的表t 1、t 2、t 3,分别只包含列c 1、c 2、c 3,每个表分别由含有数值1到1000 的1000 行组成。查找对应值相等的表行组合的查询如下所示:
    SELECT c1,c2,c3
    FROM t1,t2,t3
    WHERE c1=c2 AND c1=c3

mysql>SELECT name,english FROM user WHERE english<60;

因此在执行下述查询

此查询的结果应该为1000 行,每个组合包含3 个相等的值。如果我们在无索引的情况下处理此查询,则不可能知道哪些行包含那些值。因此,必须寻找出所有组合以便得出与WHERE 子句相配的那些组合。可能的组合数目为10 0 0×10 0 0×10 0 0(十亿),比匹配数目多一百万倍。很多工作都浪费了,并且这个查询将会非常慢,即使在如像MySQL这样快的数据库中执行也会很慢。而这还是每个表中只有1000 行的情形。如果每个表中有一百万行时,将会怎样?很显然,这样将会产生性能极为低下的结果。如果对每个表进行索引,就能极大地加速查询进程,因为利用索引的查询处理如下:

其结果为:
--------- ---------
| name    | english |
--------- ---------
| Stone   |      42 |
| William |      43 |
| Smith   |      49 |
| Black   |      49 |
| Marry   |      54 |
--------- ---------

mysql>SELECT name,english FROM user WHERE english<60;

    1) 如下从表t1中选择第一行,查看此行所包含的值。
    2) 使用表t2 上的索引,直接跳到t2 中与来自t1的值匹配的行。类似,利用表t3 上的索引,直接跳到t3 中与来自t1的值匹配的行。
    3) 进到表t1的下一行并重复前面的过程直到t1中所有的行已经查过。在此情形下,我们仍然对表t1执行了一个完全扫描,但能够在表t2 和t3 上进行索引查找直接取出这些表中的行。从道理上说,这时的查询比未用索引时要快一百万倍。如上所述,MySQL利用索引加速了WHERE 子句中与条件相配的行的搜索,或者说在执行连接时加快了与其他表中的行匹配的行的搜索。它也利用索引来改进其他操作的性能:

你应该可以发现,这个结果与未索引english列之前的不同,它是排序的,原因正式如上所述。

其结果为:
--------- ---------
| name    | english |
--------- ---------
| Stone   |      42 |
| William |      43 |
| Smith   |      49 |
| Black   |      49 |
| Marry   |      54 |
--------- ---------

    ■ 在使用MIN( ) 和MAX( ) 函数时,能够快速找到索引列的最小或最大值。
    ■ MySQL常常能够利用索引来完成ORDER BY 子句的排序操作。
    ■ 有时,MySQL可避免对整个数据文件的读取。假如从一个索引数值列中选择值,而且不选择表中其他列。这时,通过对索引值的读取,就已经得到了读取数据文件所要得到的值。没有对相同的值进行两次读取的必要,因此,甚至无需涉及数据文件。

索引对多个表查询的影响

你应该可以发现,这个结果与未索引english列之前的不同,它是排序的,原因正式如上所述。

 

前面的讨论描述了单表查询中索引的好处,其中使用索引消除了全表扫描,极大地加快了搜索的速度。在执行涉及多个表的连接查询时,索引甚至会更有价值。在单个表的查询中,每列需要查看的值的数目就是表中行的数目。而在多个表的查询中,可能的组合数目极大,因为这个数目为各表中行数之积。

索引对多个表查询的影响

假如有三个未索引的表 t1、t2、t3,分别只包含列 c1、c2、c3,每个表分别由含有数值 1 到 1000 的 1000 行组成。查找对应值相等的表行组合的查询如下所示:

前面的讨论描述了单表查询中索引的好处,其中使用索引消除了全表扫描,极大地加快了搜索的速度。在执行涉及多个表的连接查询时,索引甚至会更有价值。在单个表的查询中,每列需要查看的值的数目就是表中行的数目。而在多个表的查询中,可能的组合数目极大,因为这个数目为各表中行数之积。

此查询的结果应该为 1000 行,每个组合包含 3 个相等的值。如果我们在无索引的情况下处理此查询,则不可能知道哪些行包含那些值。因此,必须寻找出所有组合以便得出与 WHERE 子句相配的那些组合。可能的组合数目为 1000×1000×1000十亿),比匹配数目多一百万倍。很多工作都浪费了,并且这个查询将会非常慢,即使在如像 MySQL 这样快的数据库中执行也会很慢。而这还是每个表中只有 1000 行的情形。如果每个表中有一百万行时,将会怎样?很显然,这样将会产生性能极为低下的结果。如果对每个表进行索引,就能极大地加速查询进程,因为利用索引的查询处理如下:

假如有三个未索引的表 t1、t2、t3,分别只包含列 c1、c2、c3,每个表分别由含有数值 1 到 1000 的 1000 行组成。查找对应值相等的表行组合的查询如下所示:

1) 如下从表 t1 中选择第一行,查看此行所包含的值。

此查询的结果应该为 1000 行,每个组合包含 3 个相等的值。如果我们在无索引的情况下处理此查询,则不可能知道哪些行包含那些值。因此,必须寻找出所有组合以便得出与 WHERE 子句相配的那些组合。可能的组合数目为 1000×1000×1000(十亿),比匹配数目多一百万倍。很多工作都浪费了,并且这个查询将会非常慢,即使在如像 MySQL 这样快的数据库中执行也会很慢。而这还是每个表中只有 1000 行的情形。如果每个表中有一百万行时,将会怎样?很显然,这样将会产生性能极为低下的结果。如果对每个表进行索引,就能极大地加速查询进程,因为利用索引的查询处理如下:

2) 使用表 t2 上的索引,直接跳到 t2 中与来自 t1 的值匹配的行。类似,利用表 t3 上的索引,直接跳到 t3 中与来自 t1 的值匹配的行。

1) 如下从表 t1 中选择第一行,查看此行所包含的值。

3) 进到表 t1 的下一行并重复前面的过程直到 t1 中所有的行已经查过。

2) 使用表 t2 上的索引,直接跳到 t2 中与来自 t1 的值匹配的行。类似,利用表 t3 上的索引,直接跳到 t3 中与来自 t1 的值匹配的行。

在此情形下,我们仍然对表 t1 执行了一个完全扫描,但能够在表 t2 和 t3 上进行索引查找直接取出这些表中的行。从道理上说,这时的查询比未用索引时要快一百万倍。

3) 进到表 t1 的下一行并重复前面的过程直到 t1 中所有的行已经查过。

如上所述,MySQL 利用索引加速了 WHERE 子句中与条件相配的行的搜索,或者说在执行连接时加快了与其他表中的行匹配的行的搜索。

在此情形下,我们仍然对表 t1 执行了一个完全扫描,但能够在表 t2 和 t3 上进行索引查找直接取出这些表中的行。从道理上说,这时的查询比未用索引时要快一百万倍。

多列索引对查询的影响

如上所述,MySQL 利用索引加速了 WHERE 子句中与条件相配的行的搜索,或者说在执行连接时加快了与其他表中的行匹配的行的搜索。

假定你发出下列SELECT语句:

多列索引对查询的影响

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

假定你发出下列SELECT语句:

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

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

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

如果一个多列索引存在于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>ALTER TABLE tble_name ADD INDEX(col1);
mysql>ALTER TABLE tble_name ADD INDEX(col1);

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

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

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;

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

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

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;

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

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

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

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

在第一条语句中,只考虑有"Patrick" <= key_col < "Patricl"的行。在第二条语句中,只考虑有"Pat" <= key_col < "Pau"的行。

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

下列SELECT语句将不使用索引:

在第一条语句中,只考虑有"Patrick" <= key_col < "Patricl"的行。在第二条语句中,只考虑有"Pat" <= key_col < "Pau"的行。

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

下列SELECT语句将不使用索引:

在第一条语句中,LIKE值以一个通配符字符开始。在第二条语句中,LIKE值不是一个常数。
如果 column_name 是一个索引,使用column_name IS NULL的搜索将使用索引。

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

MySQL通常使用找出最少数量的行的索引。一个索引被用于你与下列操作符作比较的列:=、>、>=、<、<=、BETWEEN和一个有一个非通配符前缀象'something%'的LIKE的列。

在第一条语句中,LIKE值以一个通配符字符开始。在第二条语句中,LIKE值不是一个常数。
如果 column_name 是一个索引,使用column_name IS NULL的搜索将使用索引。

对于一个多列索引,如果在WHERE子句的所有AND层次使用索引,将不使用来索引优化查询。为了能够使用索引优化查询,必须把一个多列索引的前缀使用在一个AND条件组中。
下列WHERE子句使用索引:

MySQL通常使用找出最少数量的行的索引。一个索引被用于你与下列操作符作比较的列:=、>、>=、<、<=、BETWEEN和一个有一个非通配符前缀象'something%'的LIKE的列。

... WHERE index_part1=1 AND index_part2=2
... WHERE index=1 OR A=10 AND index=2      /* index = 1 OR index = 2 */
... WHERE index_part1='hello' AND index_part_3=5
          /* optimized like "index_part1='hello'" */
这些WHERE子句不使用索引: 
... WHERE index_part2=1 AND index_part3=2  /* index_part_1 is not used */
... WHERE index=1 OR A=10                  /* No index */
... WHERE index_part1=1 OR index_part2=10  /* No index spans all rows */

对于一个多列索引,如果在WHERE子句的所有AND层次使用索引,将不使用来索引优化查询。为了能够使用索引优化查询,必须把一个多列索引的前缀使用在一个AND条件组中。
下列WHERE子句使用索引:

我们首先讨论索引,因为它是加快查询的最重要的工具。还有其他加快查询的技术,但是最有效的莫过于恰当地使用索引了...

... WHERE index_part1=1 AND index_part2=2
... WHERE index=1 OR A=10 AND index=2      /* index = 1 OR index = 2 */
... WHERE index_part1='hello' AND index_part_3=5
          /* optimized like "index_part1='hello'" */
这些WHERE子句不使用索引: 
... WHERE index_part2=1 AND index_part3=2  /* index_part_1 is not used */
... WHERE index=1 OR A=10                  /* No index */
... WHERE index_part1=1 OR index_part2=10  /* No index spans all rows */

MySQL索引的作用

所有的MySQL索引(PRIMARY、UNIQUE和INDEX)在B树中存储。字符串是自动地压缩前缀和结尾空间。CREATE INDEX句法。

索引用于:

快速找出匹配一个WHERE子句的行。

在多个表的查询时,执行连接时加快了与其他表中的行匹配的行的搜索。

对特定的索引列找出MAX()或MIN()值。

如果排序或分组在一个可用索引的最左面前缀上进行(例如,ORDER BY key_part_1,key_part_2),排序或分组一个表。如果所有键值部分跟随DESC,键以倒序被读取。

在一些情况中,一个查询能被优化来检索值,不用咨询数据文件。如果对某些表的所有使用的列是数字型的并且构成某些键的最左面前缀,为了更快,值可以从索引树被检索出来。 

索引的弊端
一般情况下,如果 MySQL 能够知道怎样用索引来更快地处理查询,它就会这样做。这表示,在大多数情况下,如果您不对表进行索引,则损害的是您自己的利益。可以看出,作者描绘了索引的诸多好处。但有不利之处吗?是的,有。实际上,这些缺点被优点所掩盖了,但应该对它们有所了解。

首先,索引文件要占磁盘空间。如果有大量的索引,索引文件可能会比数据文件更快地达到最大的文件尺寸。其次,索引文件加快了检索,但增加了插入和删除,以及更新索引列中的值的时间(即,降低了大多数涉及写入的操作的时间),因为写操作不仅涉及数据行,而且还常常涉及索引。一个表拥有的索引越多,则写操作的平均性能下降就越大。在8.4.4节记录装载和修改的速度中,我们将更为详细地介绍这些性能问题,并讨论怎样解决。

选择索引的准则

创建索引的语法已经在4.5索引属性中进行了介绍。这里,我们假定您已经阅读过该节。但是知道语法并不能帮助确定表怎样进行索引。要决定表怎样进行索引需要考虑表的使用方式。本节介绍一些关于怎样确定和挑选索引列的准则:

1、搜索的索引列,不一定是所要选择的列

换句话说,最适合索引的列是出现在 WHERE 子句中的列,或连接子句中指定的列,而不是出现在 SELECT 关键字后的选择列表中的列,例如:

SELECT
col_a                           ←不适合作索引列
FROM
Tbl1 LEFT JOIN tbl2
ON tbl1.col_b = tbl2.col_c      ←适合作索引列
WHERE
col_d = expr                    ←适合作索引列

当然,所选择的列和用于 WHERE 子句的列也可能是相同的。关键是,列出现在选择列表中不是该列应该索引的标志。

出现在连接子句中的列或出现在形如 col1 = col2 的表达式中的列是很适合索引的列。查询中的 col_b 和 col_c 就是这样的例子。如果 MySQL 能利用连接列来优化一个查询,表示它通过消除全表扫描相当可观地减少了表行的组合。

2、使用惟一索引

考虑某列中值的分布。对于惟一值的列,索引的效果最好,而具有多个重复值的列,其索引效果最差。例如,存放年龄的列具有不同值,很容易区分各行。而用来记录性别的列,只含有“M”和“F”,则对此列进行索引没有多大用处(不管搜索哪个值,都会得出大约一半的行)。

3、使用短索引

如果对串列进行索引,应该指定一个前缀长度,只要有可能就应该这样做。例如,如果有一个 CHAR(200) 列,如果在前 10 个或 20 个字符内,多数值是惟一的,那么就不要对整个列进行索引。对前 10 个或 20 个字符进行索引能够节省大量索引空间,也可能会使查询更快。较小的索引涉及的磁盘 I/O 较少,较短的值比较起来更快。更为重要的是,对于较短的键值,索引高速缓存中的块能容纳更多的键值,因此,MySQL 也可以在内存中容纳更多的值。这增加了找到行而不用读取索引中较多块的可能性。(当然,应该利用一些常识。如仅用列值的第一个字符进行索引是不可能有多大好处的,因为这个索引中不会有许多不同的值。)

4、利用最左前缀

在创建一个 n 列的索引时,实际是创建了 MySQL 可利用的 n 个索引。多列索引可起几个索引的作用,因为可利用索引中最左边的列集来匹配行。这样的列集称为最左前缀。(这与索引一个列的前缀不同,索引一个列的前缀是利用该的前 n 个字符作为索引值。)

假如一个表在分别名为 state、city 和 zip 的三个列上有一个索引。索引中的行是按 state/city/zip 的次序存放的,因此,索引中的行也会自动按 state/city 的顺序和 state 的顺序存放。这表示,即使在查询中只指定 state 值或只指定 state 和 city 的值,MySQL 也可以利用索引。因此,此索引可用来搜索下列的列组合:
MySQL 不能使用不涉及左前缀的搜索。例如,如果按 city 或 zip 进行搜索,则不能使用该索引。如果要搜索某个州以及某个 zip 代码(索引中的列1和列3),则此索引不能用于相应值的组合。但是,可利用索引来寻找与该州相符的行,以减少搜索范围。

5、不要过度索引

不要以为索引“越多越好”,什么东西都用索引是错的。每个额外的索引都要占用额外的磁盘空间,并降低写操作的性能,这一点我们前面已经介绍过。在修改表的内容时,索引必须进行更新,有时可能需要重构,因此,索引越多,所花的时间越长。如果有一个索引很少利用或从不使用,那么会不必要地减缓表的修改速度。此外,MySQL 在生成一个执行计划时,要考虑各个索引,这也要费时间。创建多余的索引给查询优化带来了更多的工作。索引太多,也可能会使 MySQL 选择不到所要使用的最好索引。只保持所需的索引有利于查询优化。

如果想给已索引的表增加索引,应该考虑所要增加的索引是否是现有多列索引的最左索引。如果是,则就不要费力去增加这个索引了,因为已经有了。

6、考虑在列上进行的比较类型

索引可用于“<”、“<=”、“=”、“>=”、“>”和 BETWEEN 运算。在模式具有一个直接量前缀时,索引也用于 LIKE 运算。如果只将某个列用于其他类型的运算时(如 STRCMP( )),对其进行索引没有价值。

总结

本节介绍了索引在优化查询中的作用,包括了索引优化查询的原理,索引在各种情况的检索中的益处,也包括索引的的弊端:增加了存储的空间,使装载数据变慢。
索引是优化查询的最常用也是最有效的的方法,一个数据表,尤其是容量很大的表,建立合适的索引,会使查询的速度提高很大。

...

本文由星彩网app下载发布于星彩彩票app下载,转载请注明出处:至于Mysql索引那一点事,MySQL优化之索引的应用

TAG标签:
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。