前往顾页
以后地位: 主页 > 收集编程 > Php实例教程 >

MySQL优化之索引的利用根本教程

时候:2012-07-16 00:27来源:知行网www.zhixing123.cn 编辑:麦田守望者

择要:若何优化数据库体系的机能,使其尽可能快地措置各种查询,是本文将要介绍的。索引在优化查询中的感化,包含了索引优化查询的道理,索引是优化查询的最常常利用也是最有效的的体例,一个数据表,特别是容量很年夜的表,建立适合的索引,会使查询的速率进步很年夜。本文将试图解释并给出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%";


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

以下SELECT语句将不利用索引:


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


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

MySQL凡是利用找出起码数量的行的索引。一个索引被用于你与以下操纵符作比较的列:=、>、>=、<、<=、BETWEEN和一个有一个非通配符前缀象'something%'的LIKE的列。

对一个多列索引,如果在WHERE子句的所有AND层次利用索引,将不利用来索引优化查询。为了可以或许利用索引优化查询,必须把一个多列索引的前缀利用在一个AND前提组中。

顶一下
(0)
0%
踩一下
(0)
0%
------分开线----------------------------
标签(Tag):数据库 MYSQL mysql数据库
------分开线----------------------------
颁发评论
请自发遵循互联网相关的政策法规,严禁公布色情、暴力、革命的谈吐。
评价:
神色:
考证码:点击我更换图片
猜你感兴趣