仓酷云

标题: 发一篇MySQL索引剖析和优化办理计划 [打印本页]

---

作者: 兰色精灵    时间: 2015-1-16 20:11
标题: 发一篇MySQL索引剖析和优化办理计划
如果你在一个遵循GPL的自由(开源)项目中使用MySQL，那么你可以遵循GPL协议使用MySQL。然而，如果你的项目不是在GPL协议下的话，你必须为使用MySQL来支付许可费用，或者你可能因为这个因素而将你的项目改为遵循GPL。　　1、甚么是索引？

　　索援用来疾速地寻觅那些具有特定值的纪录，一切MySQL索引都以B-树的情势保留。假如没有索引，实行查询时MySQL必需从第一个纪录入手下手扫描全部表的一切纪录，直至找到切合请求的纪录。内外面的纪录数目越多，这个操纵的价值就越高。假如作为搜刮前提的列上已创立了索引，MySQL无需扫描任何纪录便可敏捷失掉方针纪录地点的地位。假如表有1000个纪录，经由过程索引查找纪录最少要比按次扫描纪录快100倍。

　　假定我们创立了一个名为people的表：

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处置的纪录数目越少，则它完成义务的速率就越快。

　　2、索引的范例

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

• 一般索引
  
  这是最基础的索引范例，并且它没有独一性之类的限定。一般索引能够经由过程以下几种体例创立：
  
  <LItype=circle>创立索引，比方CREATEINDEX<索引的名字>ONtablename(列的列表);<LItype=circle>修正表，比方ALTERTABLEtablenameADDINDEX[索引的名字](列的列表);
  
  • 创立表的时分指定索引，比方CREATETABLEtablename([...],INDEX[索引的名字](列的列表));
    
  
  
  
• 独一性索引
  
  这类索引和后面的“一般索引”基础不异，但有一个区分：索引列的一切值都只能呈现一次，即必需独一。独一性索引能够用以下几种体例创立：
  
  <LItype=circle>创立索引，比方CREATEUNIQUEINDEX<索引的名字>ONtablename(列的列表);<LItype=circle>修正表，比方ALTERTABLEtablenameADDUNIQUE[索引的名字](列的列表);
  
  • 创立表的时分指定索引，比方CREATETABLEtablename([...],UNIQUE[索引的名字](列的列表));
    
  
  
  
• 主键
  
  主键是一种独一性索引，但它必需指定为“PRIMARYKEY”。假如你已经用过AUTO_INCREMENT范例的列，你大概已熟习主键之类的观点了。主键一样平常在创立表的时分指定，比方“CREATETABLEtablename([...],PRIMARYKEY(列的列表));”。可是，我们也能够经由过程修正表的体例到场主键，比方“ALTERTABLEtablenameADDPRIMARYKEY(列的列表);”。每一个表只能有一个主键。
  
  
• 全文索引
  
  MySQL从3.23.23版入手下手撑持全文索引和全文检索。在MySQL中，全文索引的索引范例为FULLTEXT。全文索引能够在VARCHAR大概TEXT范例的列上创立。它能够经由过程CREATETABLE命令创立，也能够经由过程ALTERTABLE或CREATEINDEX命令创立。关于年夜范围的数据集，经由过程ALTERTABLE（大概CREATEINDEX）命令创立全文索引要比把纪录拔出带有全文索引的空表更快。本文上面的会商不再触及全文索引，要懂得更多信息，请拜见MySQLdocumentation。
  

　　3、单列索引与多列索引

　　索引能够是单列索引，也能够是多列索引。上面我们经由过程详细的例子来讲明这两种索引的区分。假定有如许一个people表：

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

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

　　4、最左前缀

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

• firstname，lastname，age
  
• firstname，lastname
  
• firstname
  

　　从另外一方面了解，它相称于我们创立了(firstname，lastname，age)、(firstname，lastname)和(firstname)这些列组合上的索引。上面这些查询都可以利用这个fname_lname_age索引：

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子句限定搜刮了局集。
  

　　7、索引的弱点

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

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

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

　　【停止语】

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

---

作者: 爱飞    时间: 2015-1-18 18:23
从底层原理到表层引用，书籍多的很。个人认为没有什么那本书好？这样的说法。主要看和个人的学习方法是否适合。

---

作者: 莫相离    时间: 2015-1-25 08:53
然后最好有实践机会，能够把实践到的和实践结合起来，其实理论思考是个非常困扰和痛苦的事情

---

作者: 愤怒的大鸟    时间: 2015-2-2 21:29
sqlserver的痛苦之处在于有用文档的匮乏，很多只是表明的东西

---

作者: 小女巫    时间: 2015-2-8 06:23
代替了原来VB式的错误判断。比Oracle高级不少。

---

作者: 不帅    时间: 2015-3-7 13:48
其实可以做一下类比，Oracle等数据库产品老早就支持了java编程，而且提供了java池参数作为用户配置接口。但是现在有哪些系统大批使用了java存储过程？！连Oracle自己的应用都不用为什么？！

---

作者: 若相依    时间: 2015-3-15 07:33
如果是将来做数据库的开发设计，就应该详细学习T-SQL的各种细节，包括T-SQL的程序设计、存储过程、触发器以及具体使用某个开发语言来访问数据库。

---

作者: 活着的死人    时间: 2015-3-21 23:03
现在是在考虑:如果写到服务器端,我一下搞他个10个存储过程导过去,那久之服务器不就成垃圾箱了吗?即便优化了我的中间层.

---

欢迎光临 仓酷云 (http://ckuyun.com/)

Powered by Discuz! X3.2