日期：2011-05-02 15:44:00 来源：本站整理

MySQL索引解析和优化[MySQL防范]

赞助商链接

分享到： QQ空间新浪微博腾讯微博人人网

　　本文“MySQL索引解析和优化[MySQL防范]”是由七道奇为您精心收集，来源于网络转载，文章版权归文章作者所有，本站不对其观点以及内容做任何评价，请读者自行判断，以下是其具体内容：
什么是索引?

索引用来快速地探求那些具有特定值的记录,全部MySQL索引都以B-树的情势保存.假如没有索引,履行查询时MySQL必须从第一个记录开始扫描整个表的全部记录,直至找到符合要求的记录.表里面的记录数目越多,这个操作的代价就越高.假如作为搜索条件的列上已经成立了索引,MySQL无需扫描任何记录便可疾速得到目标志录所在的位置.假如表有1000个记录,通过索引查找记录至少要比次序扫描记录快100倍.

假定我们成立了一个名为people的表:

CREATE TABLE people ( peopleid SMALLINT NOT NULL, name CHAR(50) NOT NULL );

然后,我们完好随机把1000个差别name值插入到people表.下图显示了people表所在数据文件的一小部份:

可以看到,在数据文件中name列没有任何明确的次序.假如我们成立了name列的索引,MySQL将在索引中排序name列:

关于索引中的每一项,MySQL在内部为它保存一个数据文件中实际记录所在位置的"指针".因此,假如我们要查找name等于"Mike"记录的peopleid（SQL号令为"SELECT peopleid FROM people WHERE name='Mike';"）,MySQL可以在name的索引中查找"Mike"值,然后直接转到数据文件中呼应的行,精确地返回该行的peopleid（999）.在这个历程中,MySQL只需处理一个行便可以返回后果.假如没有"name"列的索引,MySQL要扫描数据文件中的全部记录,即1000个记录!明显,需求MySQL处理的记录数目越少,则它完成任务的速度就越快.

索引的范例

MySQL供应多种索引范例供挑选:

普通索引

这是最基本的索引范例,并且它没有唯一性之类的限制.普通索引可以通过以下几种方法成立:

成立索引,比方CREATE INDEX <索引的名字> ON tablename (列的列表);
改正表,比方ALTER TABLE tablename ADD INDEX [索引的名字] (列的列表);
成立表的时刻指定索引,比方CREATE TABLE tablename ( [...], INDEX [索引的名字] (列的列表) );

唯一性索引

这种索引和前面的"普通索引"基本相同,但有一个辨别:索引列的全部值都只能呈现一次,即必须唯一.唯一性索引可以用以下几种方法成立:

成立索引,比方CREATE UNIQUE INDEX <索引的名字> ON tablename (列的列表);
改正表,比方ALTER TABLE tablename ADD UNIQUE [索引的名字] (列的列表);
成立表的时刻指定索引,比方CREATE TABLE tablename ( [...], UNIQUE [索引的名字] (列的列表) 
);

主键

主键是一种唯一性索引,但它必须指定为"PRIMARY KEY".假如你曾经用过AUTO_INCREMENT范例的列,你大概已经熟习主键之类的概念了.主键普通在成立表的时刻指定,比方"CREATE TABLE tablename ( [...], PRIMARY KEY (列的列表) ); ".但是,我们也可以通过改正表的方法加入主键,比方"ALTER TABLE tablename ADD PRIMARY KEY (列的列表); ".每个表只能有一个主键.

全文索引

MySQL从3.23.23版开始支持全文索引和全文检索.在MySQL中,全文索引的索引范例为FULLTEXT.全文索引可以在VARCHAR大概TEXT范例的列上成立.它可以通过CREATE TABLE号令成立,也可以通过ALTER TABLE或CREATE INDEX号令成立.关于大规模的数据集,通过ALTER TABLE（大概CREATE INDEX）号令成立全文索引要比把记录插入带有全文索引的空表更快.本文下面的谈论不再触及全文索引,要理解更多信息,请拜见MySQL documentation.

单列索引与多列索引

索引可以是单列索引,也可以是多列索引.下面我们通过具体的例子来阐明这两种索引的辨别.假定有这样一个people表:

CREATE TABLE people ( peopleid SMALLINT NOT NULL AUTO_INCREMENT, firstname CHAR(50) 
NOT NULL, lastname CHAR(50) NOT NULL, age SMALLINT NOT NULL, townid SMALLINT NOT 
NULL, PRIMARY KEY (peopleid) );

下面是我们插入到这个people表的数据:

这个数据片段中有四个名字为"Mikes"的人（此中两个姓Sullivans,两个姓McConnells）,有两个年纪为17岁的人,还有一个名字与众差别的Joe Smith.

这个表的主要用处是按照指定的用户姓、名以及年纪返回呼应的peopleid.比方,我们大概需求查找姓名为Mike Sullivan、年纪17岁用户的peopleid（SQL号令为SELECT peopleid FROM people WHERE firstname='Mike' AND lastname='Sullivan' AND age=17;）.由于我们不想让MySQL每次履行查询就去扫描整个表,这里需求考虑应用索引.

首先,我们可以考虑在单个列上成立索引,比方firstname、lastname大概age列.假如我们成立firstname列的索引（ALTER TABLE people ADD INDEX firstname (firstname);）,MySQL将通过这个索引疾速把搜索范围限制到那些firstname='Mike'的记录,然后再在这个"中间后果集"上举行其他条件的搜索:它首先解除那些lastname不等于"Sullivan"的记录,然后解除那些age不等于17的记录.当记录满意全部搜索条件之后,MySQL就返回终究的搜索后果.

由于成立了firstname列的索引,与履行表的完好扫描相比,MySQL的效率提高了很多,但我们要求MySQL扫描的记录数目仍旧远远超越了实际所需求的.固然我们可以删除firstname列上的索引,再成立lastname大概age列的索引,但总地看来,不管在哪个列上成立索引搜索效率仍旧类似.

为了提高搜索效率,我们需求考虑应用多列索引.假如为firstname、lastname和age这三个列成立一个多列索引,MySQL只需一次检索就可以够找出精确的后果!下面是成立这个多列索引的SQL号令:

ALTER TABLE people ADD INDEX fname_lname_age (firstname,lastname,age);

由于索引文件以B-树格局保存,MySQL可以当即转到符合的firstname,然后再转到符合的lastname,最后转到符合的age.在没有扫描数据文件任何一个记录的情形下,MySQL就精确地找出了搜索的目标志录!

那么,假如在firstname、lastname、age这三个列上辨别成立单列索引,效果能否和成立一个firstname、lastname、age的多列索引一样呢?答案能否定的,二者完好差别.当我们履行查询的时刻,MySQL只能利用一个索引.假如你有三个单列的索引,MySQL会试图挑选一个限制最严峻的索引.但是,即便是限制最严峻的单列索引,它的限制本领也必定远远低于firstname、lastname、age这三个列上的多列索引.

最左前缀

多列索引还有别的一个长处,它通过称为最左前缀（Leftmost Prefixing）的概念表现出来.持续考虑前面的例子,目前我们有一个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索引:

SELECT peopleid FROM people WHERE firstname='Mike' AND lastname='Sullivan' AND 
age='17'; SELECT peopleid FROM people WHERE firstname='Mike' AND 
lastname='Sullivan'; SELECT peopleid FROM people WHERE firstname='Mike'; The 
following queries cannot use the index at all: SELECT peopleid FROM people WHERE 
lastname='Sullivan'; SELECT peopleid FROM people WHERE age='17'; SELECT peopleid 
FROM people WHERE lastname='Sullivan' AND age='17';

挑选索引列

在性能优化历程中,挑选在哪些列上成立索引是最重要的步骤之一.可以考虑利用索引的主要有两种范例的列:在WHERE子句中呈现的列,在join子句中呈现的列.请看下面这个查询:

SELECT age ## 不利用索引 FROM people WHERE firstname='Mike' ## 考虑利用索引 AND 
lastname='Sullivan' ## 考虑利用索引

这个查询与前面的查询略有差别,但仍属于简单查询.由于age是在SELECT部份被引用,MySQL不会用它来限制列挑选操作.因此,关于这个查询来说,成立age列的索引没有什么必要.下面是一个更复杂的例子:

SELECT people.age, ##不利用索引 town.name ##不利用索引 FROM people LEFT JOIN town ON 
people.townid=town.townid ##考虑利用索引 WHERE firstname='Mike' ##考虑利用索引 AND 
lastname='Sullivan' ##考虑利用索引

与前面的例子一样,由于firstname和lastname呈目前WHERE子句中,因此这两个列仍旧有成立索引的必要.除此之外,由于town表的townid列呈目前join子句中,因此我们需求考虑成立该列的索引.

那么,我们能否可以简单地认为应当索引WHERE子句和join子句中呈现的每一个列呢?差不多如此,但并不完好.我们还必须考虑到对摆列行对比的操作符范例.MySQL只有对以下操作符才利用索引:<,<=,=,>,>=,BETWEEN,IN,以及某些时刻的LIKE.可以在LIKE操作中利用索引的情形是指另一个操作数不是以通配符（%大概_）开首的情形.比方,"SELECT peopleid FROM people WHERE firstname LIKE 'Mich%';"这个查询将利用索引,但"SELECT peopleid FROM people WHERE firstname LIKE '%ike';"这个查询不会利用索引.

解析索引效率

目前我们已经知道了一些若何挑选索引列的知识,但还无法判断哪一个最有效.MySQL供应了一个内建的SQL号令帮忙我们完成这个任务,这就是EXPLAIN号令.EXPLAIN号令的普通语法是:EXPLAIN <SQL号令>.你可以在MySQL文档找到有关该号令的更多阐明.下面是一个例子:

EXPLAIN SELECT peopleid FROM people WHERE firstname='Mike' AND lastname='Sullivan' 
AND age='17';

这个号令将返回下面这种解析后果:

table	type	possible_keys	key	key_len	ref	rows	Extra
people	ref	fname_lname_age	fname_lname_age	102	const,const,const	1	Where used

下面我们就来看看这个EXPLAIN解析后果的含义.

table:这是表的名字.

type:衔接操作的范例.下面是MySQL文档关于ref衔接范例的阐明:

"关于每一种与另一个表中记录的组合,MySQL将从当前的表读取全部带有匹配索引值的记录.假如衔接操作只利用键的最左前缀,大概假如键不是UNIQUE或PRIMARY KEY范例（换句话说,假如衔接操作不能按照键值挑选出唯一行）,则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行的表扫描操作无论若何不会快.假如你的网站上也有这种大规模的表,那么你确切应当花些时间去解析可以采取哪些索引,并考虑能否可以改写查询以优化利用.要理解更多信息,请拜见MySQL manual.别的注意,本文假定你所利用的MySQL是3.23版,部份查询不能在3.22版MySQL上履行.

　　以上是“MySQL索引解析和优化[MySQL防范]”的内容，如果你对以上该文章内容感兴趣，你可以看看七道奇为您推荐以下文章：

Windows 搭配 IIS7 PHP MySQL 环境

mysql Out of memory (Needed 16777224 bytes)的错误办理

mysql提醒[Warning] Invalid (old?) table or database name问题的办理办法

mysql启用skip-name-resolve情势时呈现Warning的处理办法

MySQL Order By语法介绍

<b>MySQL ORDER BY 的实现解析</b>

mysql数据库插入速度和读取速度的调整记录

MySQL Order By索引优化办法

MySQL Order By用法分享

mysql #1062 –Duplicate entry ''1'' for key ''PRIMARY''

MySQL Order By Rand()效率解析

本文地址：