MySQL索引和最佳化查詢

MySQL索引和最佳化查詢

恰當的索引可以加快查詢速度，可以分為四種類型：主鍵、唯一索引、全文索引、普通索引。

主鍵：唯一且沒有null值。
create table pk_test(f1 int not null,primary key(f1));
alter table customer modify id int not null, add primary key(id);

普通索引：允許重複的值出現。
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);

全文索引：用來對大表的文本域(char，varchar，text)進行索引。文法和普通索引一樣－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會對某些字忽略，造成查詢的誤差：a. 50％以上的域出現的單詞；b.少於三個字的單詞；c.mysql預定義的列表，包括the。
查詢語句：select * from ft2 where match(f1) against(‘the master‘); // 與希望的結果是不同的
相關性分數查詢：select f1,(match(f1) against(‘master‘)) from ft2;
mysql4的新功能－布爾全文檢索查詢：select * from ft2 where match(f1) against(‘+master -pet‘ in boolean mode); // 運算子類型 +-<>()~*"

唯一索引：除了不能有重複的記錄外，其它和普通索引一樣。
create table ui_test (f1 int,f2 int,unique(f1));
alter table ui_test add unique(f2);
對域(varchar，char，blob，text)的部分建立索引：alter table customer add index (surname(10));
自動增加域：每次插入記錄時會自動增加一個域的值,只能用於一個域，且這個域有索引。
create table tablename(fieldname int auto_increment,[fieldname2...,] primary key(filedname));
alter table tablename modify fieldname columntype auto_increment;
last_insert_id()函數返回最新插入的自動增加值。
select last_insert_id() from customer limit 1;
此函數在多個串連同時進行時，會發生錯誤。
重設自動增加計數器的值：
create table tablename(fieldname int auto_increment,[fieldname2...,] primary key(filedname) auto_increment=50);
alter table tablename auto_increment=50;
如果重設的值比存在的值小，自動增加計數器會從屬記錄中最大的那個值開始增加計數，比如customer表中的id已經有1、2、3、15、16、20，當把自動增加計數器的值設為1時，下次插入的記錄會從21開始。
自動增加計數器的越界：有效值為1～2的127次方，即2147483647。如果超過這個值(包括負值)，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‘);
在對每個層級添加一些資料，會看到熟悉的自動增加現象：
insert into staff(rank,position) values(‘employee‘,‘cleaner1‘),(‘employee‘,‘network maintenance1‘),(‘manager‘,‘sales manager1‘);
在這種情況下是不能重設自動增加計數器的。
刪除或更改索引：對索引的更改都需要先刪除再重新定義。
alter table tablename drop primary key;
alter table table drop index indexname;
drop index on tablename;

高效使用索引：下面討論的是用了索引會給我們帶來什嗎？
1.) 獲得域where從句中匹配的行：select * from customer where surname>‘c‘;
2.) 尋找max()和min()值時，mysql只需在排序的索引中尋找第一個和最後一個值。
3.) 返回的部分是索引的一部分，mysql就不需要去查詢全表的資料而只需看索引：select id from customer;
4.) 對域使用order by的地方：select * from customer order by surname;
5.) 還可以加速表的串連：select first_name,surname,commission from sales,sales_rep wheresales.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%‘;
選擇索引：
1.) 有查詢需要使用索引(比如where從句中條件的域)的時候，要建立索引；不要不使用的域(不如第一個字元是萬用字元的)建立索引。
2.) 建立的索引返回的行越少越好，主鍵最好，枚舉類型的索引不什麼用處。
3.) 使用短索引(比如，名字的頭十個字元而不是全部)。
4.) 不要建立太多的索引，雖然加快了查詢的速度，但增加了更新的添加記錄的時間。如果索引在查詢中很少使用，而沒有索引只是輕微的影響速度，就不要建立索引。
最左邊規則：這種情況發生在多個有索引的域上，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;
如果在查詢中使用了這三個域，那就最大限度的利用了索引：select * from customer where surname=‘clegg‘ and initial=‘x‘ and first_name=‘yvonne‘;
或者是利用索引的大部分：select * from customer where surname=‘clegg‘ and initial=‘x‘;
或僅僅是surname：select * from customer where surname=‘clegg‘;
如果打破最左邊規則，下面的例子就不會用到索引：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如何使用索引來處理select語句及串連表的。
輸入 explain select * from customer; 後，出現一張表，個行的意思如下：
table－顯示此行資料屬於哪張表；
type－重要的一列，顯示使用了何種串連，從好到差依次為const、eq_ref、ref、range、index、all，下面會詳細說明；
possible_keys－可以應用在這張表中的索引，如果為null，則表示沒有可用索引；
key－實際使用的索引，如為null，表示沒有用到索引；
key_len－索引的長度，在不損失精確度的情況下，越短越好；
ref－顯示索引的哪一列被使用了，如果可能的話，是個常數；
rows－返回請求資料的行數；
extra－關於mysql如何解析查詢的額外資訊，下面會詳細說明。

extra行的描述：distinct－mysql找到了域行聯合匹配的行，就不再搜尋了；
not exists－mysql最佳化了left join，一旦找到了匹配left join的行，就不再搜尋了；
range checked for each－沒找到理想的索引，一次對於從前面表中來的每一個行組合；
record(index map: #)－檢查使用哪個索引，並用它從表中返回行，這是使用索引最慢的一種；
using filesort－看到這個就需要最佳化查詢了，mysql需要額外的步驟來發現如何對返回的行排序。他根據連線類型以及儲存排序索引值和匹配條件的全部行的行指標來排序全部行。
using index－列資料是從單單使用了索引中的資訊而沒有讀取實際行的表返回的，這發生在對錶的全部的請求列都是同一個索引時；
using temporary－看到這個就需要最佳化查詢了，mysql需要建立一個暫存資料表來查詢儲存結果，這通常發生在多不同的列表進行order by時，而不是group by；
where used－使用了where從句來限制哪些行將與下一張表匹配或是返回給使用者。如不想返回表中用的全部行，並連線類型是all或index，這就會發生，也可能是查詢有問題。

type的描述：
system－表只有一行，這是const連線類型的特例；
const－表中一個記錄的最大值能夠匹配這個查詢(索引可以是主鍵或唯一索引)。因為只有一行，這個值實際就是常數，因為mysql先讀這個值，再把它當作常數對待；
eq_ref－從前面的表中，對每一個記錄的聯合都從表中讀取一個記錄。在查詢使用索引為主鍵或唯一索引的全部時使用；
ref－只有使用了不是主鍵或唯一索引的部分時發生。對於前面表的每一行聯合，全部記錄都將從表中讀出，這個連線類型嚴重依賴索引匹配記錄的多少－越少越好；
range－使用索引返回一個範圍中的行，比如使用>或<尋找時發生；
index－這個連線類型對前面的表中的每一個記錄聯合進行完全掃描(比all好，因為索引一般小於表資料)；
all－這個連線類型多前面的表中的每一個記錄聯合進行完全掃描，這個比較糟糕，應該盡量避免。
舉個例子：create index sales_rep on sales(sales_rep); // 可以比較一下建立索引前後的變化
explain select * from sales_rep left join sales on sales.sales_rep = sales_rep.employee_number;

結果如下：

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

這個結果表示sales_rep表有個不好的連線類型－all，沒用到索引，要查詢的行數為5；sales的連線類型為ref，可用的索引是sales_rep，實際也使用sales_rep索引，這個索引的長度是5，對應的列是employee_number，要查詢的行數為2，所以這次查詢對錶共進行了5×2次查詢。

查看索引資訊：show index from tablename;
列的描述：table－正在查看的表名；non_unique－1或1.0表示索引不能包含重複值(主鍵和唯一索引)，1表示可以；key_name－索引名；seq_in_index－索引中列的順序，從1開始；column_name－列名；collation－a或null，a表示索引以序升排列，null表示不排序；cardinality－索引中唯一值的個數；sub_part－如果整個列為索引，值為null，否則以字元表示索引的大小；packed－是否打包；null－如果列能包含null，則為yes；comment－各種注釋。

本文永久更新連結地址：