<b>加快动态网站之 MySQL索引解析和优化</b>[MySQL防范]

本文主要报告了若何加快动态网站的MySQL索引解析和优化.

1、什么是索引?

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

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

　 

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

然后,我们完好随机把1000个差别name值插入到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处理的记录数目越少,则它完成任务的速度就越快.

2、索引的范例

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.

3、单列索引与多列索引

索引可以是单列索引,也可以是多列索引.下面我们通过具体的例子来阐明这两种索引的辨别.假定有这样一个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这三个列上的多列索引.

4、最左前缀

多列索引还有别的一个长处,它通过称为最左前缀（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';

5、挑选索引列

在性能优化历程中,挑选在哪些列上成立索引是最重要的步骤之一.可以考虑利用索引的主要有两种范例的列:在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';”这个查询不会利用索引.