2020国产成人精品视频,性做久久久久久久久,亚洲国产成人久久综合一区,亚洲影院天堂中文av色

分享

Mysql索引和優(yōu)化查詢

 Ralf_Jones 2006-02-20
索引和優(yōu)化查詢
 
恰當(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);
全文索引:用來對(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)建索引: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)增加域在多列索引中的使用:
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ì)給我們帶來什么?
1.) 獲得域where從句中匹配的行:select * from customer where surname>‘c‘;
2.) 查找max()和min()值時(shí),mysql只需在排序的索引中查找第一個(gè)和最后一個(gè)值。
3.) 返回的部分是索引的一部分,mysql就不需要去查詢?nèi)淼臄?shù)據(jù)而只需看索引:select id from customer;
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%‘;
選擇索引:
1.) 有查詢需要使用索引(比如where從句中條件的域)的時(shí)候,要?jiǎng)?chuàng)建索引;不要不使用的域(不如第一個(gè)字符是通配符的)創(chuàng)建索引。
2.) 創(chuàng)建的索引返回的行越少越好,主鍵最好,枚舉類型的索引不什么用處。
3.) 使用短索引(比如,名字的頭十個(gè)字符而不是全部)。
4.) 不要?jiǎng)?chuàng)建太多的索引,雖然加快了查詢的速度,但增加了更新的添加記錄的時(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‘;
或者是利用索引的大部分: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如何使用索引來處理select語(yǔ)句及連接表的。
輸入 explain select * from customer; 后,出現(xiàn)一張表,個(gè)行的意思如下:
table-顯示此行數(shù)據(jù)屬于哪張表;type-重要的一列,顯示使用了何種連接,從好到差依次為const、eq_ref、ref、range、index、all,下面會(huì)詳細(xì)說明;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ì)說明。
extra行的描述:distinct-mysql找到了域行聯(lián)合匹配的行,就不再搜索了;
not exists-mysql優(yōu)化了left join,一旦找到了匹配left join的行,就不再搜索了;
range checked for each-沒找到理想的索引,一次對(duì)于從前面表中來的每一個(gè)行組合;
record(index map: #)-檢查使用哪個(gè)索引,并用它從表中返回行,這是使用索引最慢的一種;
using filesort-看到這個(gè)就需要優(yōu)化查詢了,mysql需要額外的步驟來發(fā)現(xiàn)如何對(duì)返回的行排序。他根據(jù)連接類型以及存儲(chǔ)排序鍵值和匹配條件的全部行的行指針來排序全部行。
using index-列數(shù)據(jù)是從單單使用了索引中的信息而沒有讀取實(shí)際行的表返回的,這發(fā)生在對(duì)表的全部的請(qǐng)求列都是同一個(gè)索引時(shí);
using temporary-看到這個(gè)就需要優(yōu)化查詢了,mysql需要?jiǎng)?chuàng)建一個(gè)臨時(shí)表來查詢存儲(chǔ)結(jié)果,這通常發(fā)生在多不同的列表進(jìn)行order by時(shí),而不是group by;
where used-使用了where從句來限制哪些行將與下一張表匹配或是返回給用戶。如不想返回表中用的全部行,并連接類型是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)建索引前后的變化
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è)列為索引,值為null,否則以字符表示索引的大?。籶acked-是否打包;null-如果列能包含null,則為yes;comment-各種注釋。

    本站是提供個(gè)人知識(shí)管理的網(wǎng)絡(luò)存儲(chǔ)空間,所有內(nèi)容均由用戶發(fā)布,不代表本站觀點(diǎn)。請(qǐng)注意甄別內(nèi)容中的聯(lián)系方式、誘導(dǎo)購(gòu)買等信息,謹(jǐn)防詐騙。如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容,請(qǐng)點(diǎn)擊一鍵舉報(bào)。
    轉(zhuǎn)藏 分享 獻(xiàn)花(0

    0條評(píng)論

    發(fā)表

    請(qǐng)遵守用戶 評(píng)論公約

    類似文章 更多