以下的文章主要介紹的是MySQL索引和查詢優(yōu)化的實(shí)際操作流程，我們大家都知道MySQL索引和查詢優(yōu)化在實(shí)際操作中出現(xiàn)的比例較高，所以對(duì)其有更深的了解會(huì)在你今后的學(xué)習(xí)中有所收獲所。

恰當(dāng)?shù)乃饕梢约涌觳樵兯俣?，可以分為四種類型：主鍵、唯一索引、全文索引、普通索引。

 

 

主鍵：唯一且沒有null值。

 

 

create table pk_test(f1 int not null,primary key(f1));  
alter table customer modify id int not null, add primary key(id); 

 

 

 

普通索引：允許重復(fù)的值出現(xiàn)。

 

 

create table tableanme (fieldname1 columntype,fieldname2 columntype,index [indexname] (fieldname1 [,fieldname2...]));  
create table tablename add index [indexname] (fieldname1 [fieldname2...]);  
alter table slaes add index(value); 

 

 

 

全文MySQL索引：用來(lái)對(duì)大表的文本域(char，varchar，text)進(jìn)行索引。

 

語(yǔ)法和普通索引一樣－fulltext。

 

使用全文索引：create table ft2 (f1 varchar(255)，fulltext(f1));

 

insert into ft2 values('wating for the bvarbariands'),('in the heart of the country'),('the master of petersburg'),('writing and being'),('heart of the beast'),('master master');

 

select * from ft2 where match(f1) against('master'); // match()－匹配域；against()匹配值。

 

MySQL會(huì)對(duì)某些字忽略，造成查詢的誤差：a. 50％以上的域出現(xiàn)的單詞；b.少于三個(gè)字的單詞；c.MySQL預(yù)定義的列表，包括the。查詢語(yǔ)句：select * from ft2 where match(f1) against('the master'); // 與希望的結(jié)果是不同的

 

相關(guān)性分?jǐn)?shù)查詢：select f1,(match(f1) against('master')) from ft2;

 

MySQL4的新功能－布爾全文查詢：select * from ft2 where match(f1) against('+master -pet' in boolean mode); // 運(yùn)算符類型 +-<>()~*"

 

 

唯一索引：除了不能有重復(fù)的記錄外，其它和普通索引一樣。

 

create table ui_test (f1 int,f2 int,unique(f1));

 

alter table ui_test add unique(f2);

 

對(duì)域(varchar，char，blob，text)的部分創(chuàng)建MySQL索引：alter table customer add index (surname(10));

 

自動(dòng)增加域：每次插入記錄時(shí)會(huì)自動(dòng)增加一個(gè)域的值,只能用于一個(gè)域，且這個(gè)域有索引。

 

create table tablename(fieldname int auto_increment,[fieldname2...,] primary key(filedname));

 

alter table tablename modify fieldname columntype auto_increment;

 

last_insert_id()函數(shù)返回***插入的自動(dòng)增加值。

 

select last_insert_id() from customer limit 1;

 

此函數(shù)在多個(gè)連接同時(shí)進(jìn)行時(shí)，會(huì)發(fā)生錯(cuò)誤。

 

重置自動(dòng)增加計(jì)數(shù)器的值：

 

create table tablename(fieldname int auto_increment,[fieldname2...,] primary key(filedname) auto_increment=50);

 

alter table tablename auto_increment=50;

 

如果重置的值比存在的值小，自動(dòng)增加計(jì)數(shù)器會(huì)從記錄中***的那個(gè)值開始增加計(jì)數(shù)，比如customer表中的id已經(jīng)有1、2、3、15、16、20，當(dāng)把自動(dòng)增加計(jì)數(shù)器的值設(shè)為1時(shí)，下次插入的記錄會(huì)從21開始。

 

自動(dòng)增加計(jì)數(shù)器的越界：有效值為1～2的127次方，即2147483647。如果超過這個(gè)值(包括負(fù)值)，MySQL會(huì)自動(dòng)把它設(shè)為***值，這樣就會(huì)產(chǎn)生一個(gè)重復(fù)鍵值的錯(cuò)誤。

 

自動(dòng)增加域在多列MySQL索引中的使用：

 

create table staff(rank enum('employee','manager','contractor') not null,position varchar(100),id int not null auto_increment,primary key(rank,id));

 

insert into staff(rank,position) values('employee','cleaner'),('cotractor','network maintenance'),('manager','sales manager');

 

在對(duì)每個(gè)級(jí)別添加一些數(shù)據(jù)，會(huì)看到熟悉的自動(dòng)增加現(xiàn)象：

 

insert into staff(rank,position) values('employee','cleaner1'),('employee','network maintenance1'),('manager','sales manager1');

 

在這種情況下是不能重置自動(dòng)增加計(jì)數(shù)器的。

 

刪除或更改索引：對(duì)索引的更改都需要先刪除再重新定義。

 

alter table tablename drop primary key;

 

alter table table drop index indexname;

 

drop index on tablename;

 

 

高效使用索引：下面討論的是用了索引會(huì)給我們帶來(lái)什么？

 

1.) 獲得域where從句中匹配的行：select * from customer where surname>'c';

 

2.) 查找max()和min()值時(shí)，MySQL只需在排序的索引中查找***個(gè)和***一個(gè)值。

 

3.) 返回的部分是MySQL索引的一部分，MySQL就不需要去查詢?nèi)淼臄?shù)據(jù)而只需看索引：select id from custo及mer;

 

4.) 對(duì)域使用order by的地方：select * from customer order by surname;

 

5.) 還可以加速表的連接：select first_name,surname,commission from sales,sales_rep where sales.sales_rep=sales_rep.employee_number and code=8;

 

6.) 在通配符的情況下：select * from sales_rep where surname like 'ser%';

 

這種情況就不能起作用：select * from sales_rep where surname like '%ser%'; #p#

 

 

選擇索引：

 

1.) 有查詢需要使用索引(比如where從句中條件的域)的時(shí)候，要?jiǎng)?chuàng)建索引；不要不使用的域(不如***個(gè)字符是通配符的)創(chuàng)建索引。

 

2.) 創(chuàng)建的索引返回的行越少越好，主鍵***，枚舉類型的索引不什么用處。

 

3.) 使用短索引(比如，名字的頭十個(gè)字符而不是全部)。

 

4.) 不要?jiǎng)?chuàng)建太多的MySQL索引，雖然加快了查詢的速度，但增加了更新的添加記錄的時(shí)間。如果索引在查詢中很少使用，而沒有索引只是輕微的影響速度，就不要?jiǎng)?chuàng)建索引。

 

 

最左邊規(guī)則：這種情況發(fā)生在多個(gè)有索引的域上，MySQL從索引列表的最左邊開始，按順序使用他們。

 

 

alter table customer add initial varchar(5);   
alter table customer add index(surname,initial,first_name);   
update customer set initial='x' where id=1;   
update customer set initial='c' where id=2;   
update customer set initial='v' where id=3;   
update customer set initial='b' where id=4;   
update customer set initial='n' where id=20;   
update customer set initial='m' where id=21; 

 

 

如果在查詢中使用了這三個(gè)域，那就***限度的利用了索引：select * from customer where surname='clegg' and initial='x' and first_name='yvonne';

 

或者是利用MySQL索引的大部分：select * from customer where surname='clegg' and initial='x';

 

或僅僅是surname：select * from customer where surname='clegg';

 

如果打破最左邊規(guī)則，下面的例子就不會(huì)用到索引：select * from customer where initial='x' and first_name='yvonne';

 

select * from customer where initial='x' ;

 

select * from customer where first_name='yvonne';

 

select * from customer where surname='clegg' and first_name='yvonne';

 

使用explain－解釋MySQL如何使用索引來(lái)處理select語(yǔ)句及連接表的。

輸入 explain select * from customer; 后，出現(xiàn)一張表，個(gè)行的意思如下：

 

table－顯示此行數(shù)據(jù)屬于哪張表；type－重要的一列，顯示使用了何種連接，從好到差依次為const、eq_ref、ref、range、index、all，下面會(huì)詳細(xì)說(shuō)明；possible_keys－可以應(yīng)用在這張表中的索引，如果為null，則表示沒有可用索引；key－實(shí)際使用的索引，如為null，表示沒有用到索引；key_len－索引的長(zhǎng)度，在不損失精確度的情況下，越短越好；ref－顯示索引的哪一列被使用了，如果可能的話，是個(gè)常數(shù)；rows－返回請(qǐng)求數(shù)據(jù)的行數(shù)；extra－關(guān)于MySQL如何解析查詢的額外信息，下面會(huì)詳細(xì)說(shuō)明。

 

extra行的描述：distinct－MySQL找到了域行聯(lián)合匹配的行，就不再搜索了；

 

not exists－MySQL優(yōu)化了left join，一旦找到了匹配left join的行，就不再搜索了；

 

range checked for each－沒找到理想的索引，一次對(duì)于從前面表中來(lái)的每一個(gè)行組合；

 

record(index map: #)－檢查使用哪個(gè)MySQL索引，并用它從表中返回行，這是使用索引最慢的一種；

 

using filesort－看到這個(gè)就需要優(yōu)化查詢了，MySQL需要額外的步驟來(lái)發(fā)現(xiàn)如何對(duì)返回的行排序。他根據(jù)連接類型以及存儲(chǔ)排序鍵值和匹配條件的全部行的行指針來(lái)排序全部行。

 

using index－列數(shù)據(jù)是從單單使用了索引中的信息而沒有讀取實(shí)際行的表返回的，這發(fā)生在對(duì)表的全部的請(qǐng)求列都是同一個(gè)索引時(shí)；

 

using temporary－看到這個(gè)就需要優(yōu)化查詢了，MySQL需要?jiǎng)?chuàng)建一個(gè)臨時(shí)表來(lái)查詢存儲(chǔ)結(jié)果，這通常發(fā)生在多不同的列表進(jìn)行order by時(shí)，而不是group by；

 

where used－使用了where從句來(lái)限制哪些行將與下一張表匹配或是返回給用戶。如不想返回表中用的全部行，并連接類型是all或index，這就會(huì)發(fā)生，也可能是查詢有問題。

 

type的描述：system－表只有一行，這是const連接類型的特例；const－表中一個(gè)記錄的***值能夠匹配這個(gè)查詢(索引可以是主鍵或唯一索引)。因?yàn)橹挥幸恍?，這個(gè)值實(shí)際就是常數(shù)，因?yàn)镸ySQL先讀這個(gè)值，再把它當(dāng)作常數(shù)對(duì)待；eq_ref－從前面的表中，對(duì)每一個(gè)記錄的聯(lián)合都從表中讀取一個(gè)記錄。在查詢使用索引為主鍵或唯一索引的全部時(shí)使用；ref－只有使用了不是主鍵或唯一索引的部分時(shí)發(fā)生。

對(duì)于前面表的每一行聯(lián)合，全部記錄都將從表中讀出，這個(gè)連接類型嚴(yán)重依賴索引匹配記錄的多少－越少越好；range－使用索引返回一個(gè)范圍中的行，比如使用>或<查找時(shí)發(fā)生；index－這個(gè)連接類型對(duì)前面的表中的每一個(gè)記錄聯(lián)合進(jìn)行完全掃描(比all好，因?yàn)樗饕话阈∮诒頂?shù)據(jù))；all－這個(gè)連接類型多前面的表中的每一個(gè)記錄聯(lián)合進(jìn)行完全掃描，這個(gè)比較糟糕，應(yīng)該盡量避免。

 

舉個(gè)例子：create index sales_rep on sales(sales_rep); // 可以比較一下創(chuàng)建MySQL索引前后的變化

 

explain select * from sales_rep left join sales on sales.sales_rep = sales_rep.employee_number;

 

結(jié)果如下：

 

table type possible_keys key key_len ref rows extra   
sales_rep all null null null null 5   
sales ref sales_rep sales_rep 5 sales_rep.employee_number 2  

 

 

這個(gè)結(jié)果表示sales_rep表有個(gè)不好的連接類型－all，沒用到索引，要查詢的行數(shù)為5；sales的連接類型為ref，可用的索引是sales_rep，實(shí)際也使用sales_rep索引，這個(gè)索引的長(zhǎng)度是5，對(duì)應(yīng)的列是employee_number，要查詢的行數(shù)為2，所以這次查詢對(duì)表共進(jìn)行了5×2次查詢。

查看索引信息：show index from tablename;

列的描述：table－正在查看的表名；non_unique－1或1.0表示索引不能包含重復(fù)值(主鍵和唯一索引)，1表示可以；key_name－索引名；seq_in_index－索引中列的順序，從1開始；column_name－列名；collation－a或null，a表示索引以序升排列，null表示不排序；

cardinality－索引中唯一值的個(gè)數(shù)；sub_part－如果整個(gè)列為MySQL索引，值為null，否則以字符表示索引的大小；packed－是否打包；null－如果列能包含null，則為yes；comment－各種注釋。

 

【編輯推薦】

1. 對(duì)MySQL DELETE語(yǔ)法的詳細(xì)解析
2. MySQL show的實(shí)際操作用法
3. MySQL 游標(biāo)的具體使用方案
4. MySQL查看表結(jié)構(gòu)的實(shí)際應(yīng)用命令列舉
5. 如何實(shí)現(xiàn)MySQL Replication 優(yōu)化