仓酷云

标题: 绝无经由的MySQL索引剖析和优化 [打印本页]
作者: 不帅    时间: 2015-1-16 20:14
标题: 绝无经由的MySQL索引剖析和优化
由于MySQL数据库已经如此普及，对企业来说它无疑是一个更好的选择。MySQL索引剖析和优化
索援用来疾速地寻觅那有特定值的纪录，一切MySQL索引都以B-树的情势保留。假如没有索引，实行查询时MySQL必需从第一个纪录入手下手扫描全部表的一切纪录，直至找到切合请求的纪录。内外面的纪录数目越多，这个操纵的价值就越高。假如作为搜刮前提的列上已创立了索引，MySQL无需扫描任何纪录便可敏捷失掉方针纪录地点的地位。假如表有1000个纪录，经由过程索引查找纪录最少要比按次扫描纪录快100倍。
假定我们创立了一个名为people的表：

  1. CREATETABLEpeople(peopleidSMALLINTNOTNULL,nameCHAR(50)NOTNULL);
复制代码

然后，我们完整随机把1000个分歧name值拔出到people表。下图显现了people表地点数据文件的一小部分：



能够看到，在数据文件中name列没有任何明白的序次。假如我们创立了name列的索引，MySQL将在索引中排序name列：



关于索引中的每项，MySQL在外部为它保留一个数据文件中实践纪录地点地位的“指针”。因而，假如我们要查找name即是“Mike”纪录的peopleid（SQL命令为“SELECTpeopleidFROMpeopleWHEREname=Mike;”），MySQL可以在name的索引中查找“Mike”值，然后间接转到数据文件中响应的行，正确地前往该行的peopleid（999）。在这个过程当中，MySQL只需处置一个行就能够前往了局。假如没有“name”列的索引，MySQL要扫描数据文件中的一切纪录，即1000个纪录！明显，必要MySQL处置的纪录数目越少，则它完成义务的速率就越快。

索引的范例

MySQL供应多种索引范例供选择：

一般索引

这是最基础的索引范例，并且它没有独一性之类的限定。一般索引能够经由过程以下几种体例创立：
  1. 创立索引，比方CREATEINDEX<索引的名字>ONtablename(列的列表);修正表，比方ALTERTABLEtablenameADDINDEX[索引的名字](列的列表);创立表的时分指定索引，比方CREATETABLEtablename([...],INDEX[索引的名字](列的列表));
复制代码

独一性索引

这类索引和后面的“一般索引”基础不异，但有一个区分：索引列的一切值都只能呈现一次，即必需独一。独一性索引能够用以下几种体例创立：
  1. 创立索引，比方CREATEUNIQUEINDEX<索引的名字>ONtablename(列的列表);修正表，比方ALTERTABLEtablenameADDUNIQUE[索引的名字](列的列表);创立表的时分指定索引，比方CREATETABLEtablename([...],UNIQUE[索引的名字](列的列表));
复制代码

主键

主键是一种独一性索引，但它必需指定为“PRIMARYKEY”。假如你已经用过AUTO_INCREMENT范例的列，你大概已熟习主键之类的观点了。主键一样平常在创立表的时分指定，比方“CREATETABLEtablename([...],PRIMARYKEY(列的列表));”。可是，我们也能够经由过程修正表的体例到场主键，比方“ALTERTABLEtablenameADDPRIMARYKEY(列的列表);”。每一个表只能有一个主键。

全文索引

MySQL从3.23.23版入手下手撑持全文索引和全文检索。在MySQL中，全文索引的索引范例为FULLTEXT。全文索引能够在VARCHAR大概TEXT范例的列上创立。它能够经由过程CREATETABLE命令创立，也能够经由过程ALTERTABLE或CREATEINDEX命令创立。关于年夜范围的数据集，经由过程ALTERTABLE（大概CREATEINDEX）命令创立全文索引要比把纪录拔出带有全文索引的空表更快。本文上面的会商不再触及全文索引，要懂得更多信息，请拜见MySQLdocumentation。

单列索引与多列索引

索引能够是单列索引，也能够是多列索引。上面我们经由过程详细的例子来讲明这两种索引的区分。假定有如许一个people表：
  1. CREATETABLEpeople(peopleidSMALLINTNOTNULLAUTO_INCREMENT,firstnameCHAR(50)NOTNULL,lastnameCHAR(50)NOTNULL,ageSMALLINTNOTNULL,townidSMALLINTNOTNULL,PRIMARYKEY(peopleid));
复制代码

上面是我们拔出到这个people表的数据：



这个数据片断中有四个名字为“Mikes”的人（个中两个姓Sullivans，两个姓McConnells），有两个岁数为17岁的人，另有一个名字不同凡响的JoeSmith。

这个表的次要用处是依据指定的用户姓、名和岁数前往响应的peopleid。比方，我们大概必要查找姓名为MikeSullivan、岁数17岁用户的peopleid（SQL命令为SELECTpeopleidFROMpeopleWHEREfirstname=MikeANDlastname=SullivanANDage=17;）。因为我们不想让MySQL每次实行查询就往扫描全部表，这里必要思索使用索引。

起首，我们能够思索在单个列上创立索引，好比firstname、lastname大概age列。假如我们创立firstname列的索引（ALTERTABLEpeopleADDINDEXfirstname(firstname);），MySQL将经由过程这个索引敏捷把搜刮局限限定到那些firstname=Mike的纪录，然后再在这个“两头了局集”长进行其他前提的搜刮：它起首扫除那些lastname不即是“Sullivan”的纪录，然后扫除那些age不即是17的纪录。当纪录满意一切搜刮前提以后，MySQL就前往终极的搜刮了局。

因为创建了firstname列的索引，与实行表的完整扫描比拟，MySQL的效力进步了良多，但我们请求MySQL扫描的纪录数目仍然远远凌驾了实践所必要的。固然我们能够删除firstname列上的索引，再创立lastname大概age列的索引，但总地看来，不管在哪一个列上创立索引搜刮效力仍然类似。

为了进步搜刮效力，我们必要思索使用多列索引。假如为firstname、lastname和age这三个列创立一个多列索引，MySQL只需一次检索就可以够找出准确的了局！上面是创立这个多列索引的SQL命令：
  1. ALTERTABLEpeopleADDINDEXfname_lname_age(firstname,lastname,age);
复制代码

因为索引文件以B-树格局保留，MySQL可以当即转到符合的firstname，然后再转到符合的lastname，最初转到符合的age。在没有扫描数据文件任何一个纪录的情形下，MySQL就准确地找出了搜刮的方针纪录！

那末，假如在firstname、lastname、age这三个列上分离创立单列索引，效果是不是和创立一个firstname、lastname、age的多列索引一样呢？谜底是不是定的，二者完整分歧。当我们实行查询的时分，MySQL只能利用一个索引。假如你有三个单列的索引，MySQL会试图选择一个限定最严厉的索引。可是，即便是限定最严厉的单列索引，它的限定才能也一定远远低于firstname、lastname、age这三个列上的多列索引。

最左前缀

多列索引另有别的一个长处，它经由过程称为最左前缀（LeftmostPrefixing）的观点表现出来。持续思索后面的例子，如今我们有一个firstname、lastname、age列上的多列索引，我们称这个索引为fname_lname_age。当搜刮前提是以下各类列的组应时，MySQL将利用fname_lname_age索引：
  1. firstname，lastname，agefirstname，lastnamefirstname
复制代码

从另外一方面了解，它相称于我们创立了(firstname，lastname，age)、(firstname，lastname)和(firstname)这些列组合上的索引。上面这些查询都可以利用这个fname_lname_age索引：
  1. SELECTpeopleidFROMpeopleWHEREfirstname=MikeANDlastname=SullivanANDage=17;SELECTpeopleidFROMpeopleWHEREfirstname=MikeANDlastname=Sullivan;SELECTpeopleidFROMpeopleWHEREfirstname=Mike;Thefollowingqueriescannotusetheindexatall:SELECTpeopleidFROMpeopleWHERElastname=Sullivan;SELECTpeopleidFROMpeopleWHEREage=17;SELECTpeopleidFROMpeopleWHERElastname=SullivanANDage=17;
复制代码

选择索引列

在功能优化过程当中，选择在哪些列上创立索引是最主要的步骤之一。能够思索利用索引的次要有两品种型的列：在WHERE子句中呈现的列，在join子句中呈现的列。请看上面这个查询：
  1. SELECTage##不利用索引FROMpeopleWHEREfirstname=Mike##思索利用索引ANDlastname=Sullivan##思索利用索引
复制代码

这个查询与后面的查询略有分歧，但仍属于复杂查询。因为age是在SELECT部分被援用，MySQL不会用它来限定列选择操纵。因而，关于这个查询来讲，创立age列的索引没有甚么需要。上面是一个更庞大的例子：
  1. SELECTpeople.age,##不利用索引town.name##不利用索引FROMpeopleLEFTJOINtownONpeople.townid=town.townid##思索利用索引WHEREfirstname=Mike##思索利用索引ANDlastname=Sullivan##思索利用索引
复制代码

与后面的例子一样，因为firstname和lastname呈现在WHERE子句中，因而这两个列仍然有创立索引的需要。除此以外，因为town表的townid列呈现在join子句中，因而我们必要思索创立该列的索引。

那末，我们是不是能够复杂地以为应当索引WHERE子句和join子句中呈现的每个列呢？差未几云云，但其实不完整。我们还必需思索到对列举行对照的操纵符范例。MySQL只要对以下操纵符才利用索引：<，<=，=，>，>=，BETWEEN，IN，和某些时分的LIKE。能够在LIKE操纵中利用索引的情况是指另外一个操纵数不是以通配符（%大概_）开首的情况。比方，“SELECTpeopleidFROMpeopleWHEREfirstnameLIKEMich%;”这个查询将利用索引，但“SELECTpeopleidFROMpeopleWHEREfirstnameLIKE%ike;”这个查询不会利用索引。

剖析索引效力

如今我们已晓得了一些怎样选择索引列的常识，但还没法判别哪个最无效。MySQL供应了一个内建的SQL命令匡助我们完成这个义务，这就是EXPLAIN命令。EXPLAIN命令的一样平常语法是：EXPLAIN<SQL命令>。你能够在MySQL文档找到有关该命令的更多申明。上面是一个例子：
  1. EXPLAINSELECTpeopleidFROMpeopleWHEREfirstname=MikeANDlastname=SullivanANDage=17;
复制代码

这个命令将前往上面这类剖析了局：

tabletypepossible_keyskeykey_lenrefrowsExtrapeoplereffname_lname_agefname_lname_age102const,const,const1Whereused

上面我们就来看看这个EXPLAIN剖析了局的寄义。

table：这是表的名字。

type：毗连操纵的范例。上面是MySQL文档关于ref毗连范例的申明：

“关于每种与另外一个表中纪录的组合，MySQL将从以后的表读取一切带有婚配索引值的纪录。假如毗连操纵只利用键的最左前缀，大概假如键不是UNIQUE或PRIMARYKEY范例（换句话说，假如毗连操纵不克不及依据键值选择出独一行），则MySQL利用ref毗连范例。假如毗连操纵所用的键只婚配大批的纪录，则ref是一种好的毗连范例。”

在本例中，因为索引不是UNIQUE范例，ref是我们可以失掉的最好毗连范例。

假如EXPLAIN显现毗连范例是“ALL”，并且你其实不想从内外面选择出年夜多半纪录，那末MySQL的操纵效力将十分低，由于它要扫描全部表。你能够到场更多的索引来办理这个成绩。预知更多信息，请拜见MySQL的手册申明。

possible_keys：

大概能够使用的索引的名字。这里的索引名字是创立索引时指定的索引昵称；假如索引没有昵称，则默许显现的是索引中第一个列的名字（在本例中，它是“firstname”）。默许索引名字的寄义常常不是很分明。

Key：

它显现了MySQL实践利用的索引的名字。假如它为空（或NULL），则MySQL不利用索引。

key_len：

索引中被利用部分的长度，以字节计。在本例中，key_len是102，个中firstname占50字节，lastname占50字节，age占2字节。假如MySQL只利用索引中的firstname部分，则key_len将是50。

ref：

它显现的是列的名字（或单词“const”），MySQL将依据这些列来选择行。在本例中，MySQL依据三个常量选择行。

rows：

MySQL所以为的它在找到准确的了局之前必需扫描的纪录数。明显，这里最幻想的数字就是1。

Extra：

这里大概呈现很多分歧的选项，个中年夜多半将对查询发生负面影响。在本例中，MySQL只是提示我们它将用WHERE子句限定搜刮了局集。

索引的弱点

到今朝为止，我们会商的都是索引的长处。现实上，索引也是出缺点的。

起首，索引要占用磁盘空间。一般情形下，这个成绩不是很凸起。可是，假如你创立每种大概列组合的索引，索引文件体积的增加速率将远远凌驾数据文件。假如你有一个很年夜的表，索引文件的巨细大概到达操纵体系同意的最年夜文件限定。

第二，关于必要写进数据的操纵，好比DELETE、UPDATE和INSERT操纵，索引会下降它们的速率。这是由于MySQL不但要把修改数据写进数据文件，并且它还要把这些修改写进索引文件。

【停止语】在年夜型数据库中，索引是进步速率的一个关头要素。不论表的布局是何等复杂，一次500000行的表扫描操纵不管怎样不会快。假如你的网站上也有这类年夜范围的表，那末你的确应当花些工夫往剖析能够接纳哪些索引，并思索是不是能够改写查询以优化使用。要懂得更多信息，请拜见MySQLmanual。别的注重，本文假定你所利用的MySQL是3.23版，部分查询不克不及在3.22版MySQL上实行。

DBaaS系统其实具有更大的市场机遇：像其他云服务一样，DBaaS意味着更短的销售周期，更少的启动费用，持续不断的收入，也意味着比之前更多的客户。
作者: 再现理想    时间: 2015-1-18 15:00
从项目平台的选择上讲，我们关心的，应该是一款产品能不能满足任务需求，而不是网上怎么说。
作者: 冷月葬花魂    时间: 2015-1-22 08:38
入门没那么困难，精通没那么容易
作者: 分手快乐    时间: 2015-1-31 05:18
两个月啃那本sqlserver2005技术内部-存储引擎，花了几个月啃四本书
作者: 活着的死人    时间: 2015-2-6 17:34
你觉得我的非分区索引无法对起子分区，你可以提醒我一下呀！没有任何的提醒，直接就变成了非分区表。不知道这算不算一个bug。大家也可以试试。
作者: 若相依    时间: 2015-2-17 19:54
having子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函数，使用having条件显示特定的组，也可以使用多个分组标准进行分组。
作者: 简单生活    时间: 2015-3-5 22:35
SQL语言是学习所有数据库产品的基础，无论你是做数据库管理还是做数据库开发都是这样。不过具体学习的侧重点要看你将来做哪一块，如果是做数据库管理（DBA），侧重点应该放在SQLServer的系统管理上.
作者: 变相怪杰    时间: 2015-3-12 16:31
也可谈一下你是怎么优化存储过程的?
作者: 飘灵儿    时间: 2015-3-19 23:58
从项目平台的选择上讲，我们关心的，应该是一款产品能不能满足任务需求，而不是网上怎么说。



欢迎光临 仓酷云 (http://ckuyun.com/) Powered by Discuz! X3.2