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) );
复制代码
<P style="TEXT-INDENT: 2em"><P style="TEXT-INDENT: 2em"><P style="TEXT-INDENT: 2em">上面是咱们拔出到这个people表的数据: <P style="TEXT-INDENT: 2em"><P style="TEXT-INDENT: 2em">这个数据片断中有四个名字为“Mikes”的人(个中两个姓Sullivans,两个姓McConnells),有两个岁数为17岁的人,还有一个名字不同凡响的Joe Smith。 <P style="TEXT-INDENT: 2em"><P style="TEXT-INDENT: 2em">这个表的次要用处是依据指定的用户姓、名和岁数前往响应的peopleid。例如,咱们能够需求查找姓名为Mike Sullivan、岁数17岁用户的peopleid(SQL号令为SELECT peopleid FROM people WHERE firstname='Mike' AND lastname='Sullivan' AND age=17;)。因为咱们不想让MySQL每次履行查询就去扫描全部表,这里需求思索应用索引。 <P style="TEXT-INDENT: 2em"><P style="TEXT-INDENT: 2em">起首,咱们可以思索在单个列上创立索引,好比firstname、lastname或age列。假如咱们创立firstname列的索引(ALTER TABLE people ADD INDEX firstname (firstname);),MySQL将经由过程这个索引敏捷把搜刮局限限制到那些firstname='Mike'的纪录,然后再在这个“两头了局集”长进行其他前提的搜刮:它起首扫除那些lastname不等于“Sullivan”的纪录,然后扫除那些age不等于17的纪录。当纪录知足一切搜刮前提以后,MySQL就前往终究的搜刮了局。 <P style="TEXT-INDENT: 2em"><P style="TEXT-INDENT: 2em">因为创立了firstname列的索引,与履行表的完整扫描比拟,MySQL的效力进步了良多,但咱们请求MySQL扫描的纪录数目仍然远远超越了实践所需求的。固然咱们可以删除firstname列上的索引,再创立lastname或age列的索引,但总地看来,不管在哪一个列上创立索引搜刮效力仍然类似。 <P style="TEXT-INDENT: 2em"><P style="TEXT-INDENT: 2em">为了进步搜刮效力,咱们需求思索应用多列索引。假如为firstname、lastname和age这三个列创立一个多列索引,MySQL只需一次检索就可以够找出准确的了局!上面是创立这个多列索引的SQL号令: <P style="TEXT-INDENT: 2em"><P style="TEXT-INDENT: 2em">ALTER TABLE people ADD INDEX fname_lname_age (firstname,lastname,age); <P style="TEXT-INDENT: 2em">因为索引文件以B-树格局保留,MySQL可以当即转到适合的firstname,然后再转到适合的lastname,最初转到适合的age。在没有扫描数据文件任何一个纪录的情形下,MySQL就准确地找出了搜刮的方针纪录! <P style="TEXT-INDENT: 2em"><P style="TEXT-INDENT: 2em"><P style="TEXT-INDENT: 2em"><P style="TEXT-INDENT: 2em">那末,假如在firstname、lastname、age这三个列上分离创立单列索引,后果是不是和创立一个firstname、lastname、age的多列索引一样呢?谜底是不是定的,二者完整分歧。当咱们履行查询的时分,MySQL只能利用一个索引。假如你有三个单列的索引,MySQL会试图选择一个限制最严厉的索引。然而,即便是限制最严厉的单列索引,它的限制才能也一定远远低于firstname、lastname、age这三个列上的多列索引。 <P style="TEXT-INDENT: 2em"><P style="TEXT-INDENT: 2em">4、最左前缀 <P style="TEXT-INDENT: 2em"><P style="TEXT-INDENT: 2em">多列索引还有别的一个长处,它经由过程称为最左前缀(Leftmost Prefixing)的概念表现出来。持续思索后面的例子,如今咱们有一个firstname、lastname、age列上的多列索引,咱们称这个索引为fname_lname_age。当搜刮前提是以下各类列的组应时,MySQL将利用fname_lname_age索引: <P style="TEXT-INDENT: 2em">
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';
SELECT people.age, ##不利用索引town.name ##不利用索引FROM people LEFT JOIN town ONpeople.townid=town.townid ##思索利用索引WHERE firstname='Mike' ##思索利用索引AND lastname='Sullivan' ##思索利用索引