《Mysql入門快速學(xué)習(xí)MySQL索引的入門超級教程》要點(diǎn)：
本文介紹了Mysql入門快速學(xué)習(xí)MySQL索引的入門超級教程，希望對您有用。如果有疑問，可以聯(lián)系我們。

MYSQL教程所謂索引就是為特定的mysql字段進(jìn)行一些特定的算法排序,比如二叉樹的算法和哈希算法,哈希算法是通過建立特征值,然后根據(jù)特征值來快速查找.而用的最多,并且是mysql默認(rèn)的就是二叉樹算法 BTREE,通過BTREE算法建立索引的字段,比如掃描20行就能得到未使用BTREE前掃描了2^20行的結(jié)果,具體的實(shí)現(xiàn)方式后續(xù)本博客會出一個算法專題里面會有具體的分析討論;

MYSQL教程Explain優(yōu)化查詢檢測

MYSQL教程EXPLAIN可以幫助開發(fā)人員分析SQL問題,explain顯示了mysql如何使用索引來處理select語句以及連接表,可以幫助選擇更好的索引和寫出更優(yōu)化的查詢語句.

MYSQL教程使用方法,在select語句前加上Explain就可以了：

MYSQL教程
Explain select * from blog where false;

MYSQL教程mysql在執(zhí)行一條查詢之前,會對發(fā)出的每條SQL進(jìn)行分析,決定是否使用索引或全表掃描如果發(fā)送一條select * from blog where falseMysql是不會執(zhí)行查詢操作的,因?yàn)榻?jīng)過SQL分析器的分析后MySQL已經(jīng)清楚不會有任何語句符合操作;

MYSQL教程Example

MYSQL教程
mysql> EXPLAIN SELECT `birday` FROM `user` WHERE `birthday` < "1990/2/2"; 

MYSQL教程-- 結(jié)果：?

MYSQL教程
id: 1 

MYSQL教程?
select_type: SIMPLE -- 查詢類型(簡單查詢,聯(lián)合查詢,子查詢)?
?

• table: user -- 顯示這一行的數(shù)據(jù)是關(guān)于哪張表的
• ?
• type: range -- 區(qū)間索引(在小于1990/2/2區(qū)間的數(shù)據(jù)),這是重要的列,顯示連接使用了何種類型.從最好到最差的連接類型為system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL,const代表一次就命中,ALL代表掃描了全表才確定結(jié)果.一般來說,得保證查詢至少達(dá)到range級別,最好能達(dá)到ref.
• ?
• possible_keys: birthday? -- 指出MySQL能使用哪個索引在該表中找到行.如果是空的,沒有相關(guān)的索引.這時要提高性能,可通過檢驗(yàn)WHERE子句,看是否引用某些字段,或者檢查字段不是適合索引.?
• ?
• key: birthday -- 實(shí)際使用到的索引.如果為NULL,則沒有使用索引.如果為primary的話,表示使用了主鍵.
• ?
• key_len: 4 -- 最長的索引寬度.如果鍵是NULL,長度就是NULL.在不損失精確性的情況下,長度越短越好
• ?
• ref: const -- 顯示哪個字段或常數(shù)與key一起被使用.?
• ?
• rows: 1 -- 這個數(shù)表示mysql要遍歷多少數(shù)據(jù)才能找到,在innodb上是不準(zhǔn)確的.
• ?
• Extra: Using where; Using index -- 執(zhí)行狀態(tài)說明,這里可以看到的壞的例子是Using temporary和Using

MYSQL教程select_type

• simple 簡單select(不使用union或子查詢)
• primary 最外面的select
• union union中的第二個或后面的select語句
• dependent union union中的第二個或后面的select語句,取決于外面的查詢
• union result union的結(jié)果.
• subquery 子查詢中的第一個select
• dependent subquery 子查詢中的第一個select,取決于外面的查詢
• derived 導(dǎo)出表的select(from子句的子查詢)

MYSQL教程Extra與type詳細(xì)說明

• Distinct:一旦MYSQL找到了與行相聯(lián)合匹配的行,就不再搜索了
• Not exists: MYSQL優(yōu)化了LEFT JOIN,一旦它找到了匹配LEFT JOIN標(biāo)準(zhǔn)的行,就不再搜索了
• Range checked for each Record(index map:#):沒有找到理想的索引,因此對于從前面表中來的每一個行組合,MYSQL檢查使用哪個索引,并用它來從表中返回行.這是使用索引的最慢的連接之一
• Using filesort: 看到這個的時候,查詢就需要優(yōu)化了 .MYSQL需要進(jìn)行額外的步驟來發(fā)現(xiàn)如何對返回的行排序.它根據(jù)連接類型以及存儲排序鍵值和匹配條件的全部行的行指針來排序全部行
• Using index: 列數(shù)據(jù)是從僅僅使用了索引中的信息而沒有讀取實(shí)際的行動的表返回的,這發(fā)生在對表的全部的請求列都是同一個索引的部分的時候
• Using temporary 看到這個的時候,查詢需要優(yōu)化了 .這里,MYSQL需要創(chuàng)建一個臨時表來存儲結(jié)果,這通常發(fā)生在對不同的列集進(jìn)行ORDER BY上,而不是GROUP BY上
• Where used 使用了WHERE從句來限制哪些行將與下一張表匹配或者是返回給用戶.如果不想返回表中的全部行,并且連接類型ALL或index,這就會發(fā)生,或者是查詢有問題不同連接類型的解釋(按照效率高低的順序排序
• system 表只有一行：system表.這是const連接類型的特殊情況
• const:表中的一個記錄的最大值能夠匹配這個查詢(索引可以是主鍵或惟一索引).因?yàn)橹挥幸恍?這個值實(shí)際就是常數(shù),因?yàn)镸YSQL先讀這個值然后把它當(dāng)做常數(shù)來對待
• eq_ref:在連接中,MYSQL在查詢時,從前面的表中,對每一個記錄的聯(lián)合都從表中讀取一個記錄,它在查詢使用了索引為主鍵或惟一鍵的全部時使用
• ref:這個連接類型只有在查詢使用了不是惟一或主鍵的鍵或者是這些類型的部分(比如,利用最左邊前綴)時發(fā)生.對于之前的表的每一個行聯(lián)合,全部記錄都將從表中讀出.這個類型嚴(yán)重依賴于根據(jù)索引匹配的記錄多少―越少越好+
• range:這個連接類型使用索引返回一個范圍中的行,比如使用>或<查找東西時發(fā)生的情況+
• index: 這個連接類型對前面的表中的每一個記錄聯(lián)合進(jìn)行完全掃描(比ALL更好,因?yàn)樗饕话阈∮诒頂?shù)據(jù))+
• ALL:這個連接類型對于前面的每一個記錄聯(lián)合進(jìn)行完全掃描,這一般比較糟糕,應(yīng)該盡量避免
• 其中type:
• 如果是Only index,這意味著信息只用索引樹中的信息檢索出的,這比掃描整個表要快.
• 如果是where used,就是使用上了where限制.
• 如果是impossible where 表示用不著where,一般就是沒查出來啥.
• 如果此信息顯示Using filesort或者Using temporary的話會很吃力,WHERE和ORDER BY的索引經(jīng)常無法兼顧,如果按照WHERE來確定索引,那么在ORDER BY時,就必然會引起Using filesort,這就要看是先過濾再排序劃算,還是先排序再過濾劃算.

MYSQL教程索引

MYSQL教程索引的類型

MYSQL教程(1)UNIQUE唯一索引

MYSQL教程不可以出現(xiàn)相同的值,可以有NULL值

MYSQL教程(2)INDEX普通索引

MYSQL教程允許出現(xiàn)相同的索引內(nèi)容

MYSQL教程(3)PRIMARY KEY主鍵索引

MYSQL教程不允許出現(xiàn)相同的值,且不能為NULL值,一個表只能有一個primary_key索引

MYSQL教程(4)fulltext index 全文索引

MYSQL教程上述三種索引都是針對列的值發(fā)揮作用,但全文索引,可以針對值中的某個單詞,比如一篇文章中的某個詞, 然而并沒有什么卵用,因?yàn)橹挥衜yisam以及英文支持,并且效率讓人不敢恭維,但是可以用coreseek和xunsearch等第三方應(yīng)用來完成這個需求

MYSQL教程MySQL支持很多數(shù)據(jù)類型,選擇合適的數(shù)據(jù)類型存儲數(shù)據(jù)對性能有很大的影響.通常來說,可以遵循以下一些指導(dǎo)原則：

MYSQL教程(1)越小的數(shù)據(jù)類型通常更好：越小的數(shù)據(jù)類型通常在磁盤、內(nèi)存和CPU緩存中都需要更少的空間,處理起來更快.
(2)簡單的數(shù)據(jù)類型更好：整型數(shù)據(jù)比起字符,處理開銷更小,因?yàn)樽址谋容^更復(fù)雜.在MySQL中,應(yīng)該用內(nèi)置的日期和時間數(shù)據(jù)類型,而不是用字符串來存儲時間；以及用整型數(shù)據(jù)類型存儲IP地址.
(3)盡量避免NULL：應(yīng)該指定列為NOT NULL,除非你想存儲NULL.在MySQL中,含有空值的列很難進(jìn)行查詢優(yōu)化,因?yàn)樗鼈兪沟盟饕⑺饕慕y(tǒng)計(jì)信息以及比較運(yùn)算更加復(fù)雜.你應(yīng)該用0、一個特殊的值或者一個空串代替空值.
對于任何DBMS,索引都是進(jìn)行優(yōu)化的最主要的因素.對于少量的數(shù)據(jù),沒有合適的索引影響不是很大,但是,當(dāng)隨著數(shù)據(jù)量的增加,性能會急劇下降.
如果對多列進(jìn)行索引(組合索引),列的順序非常重要,MySQL僅能對索引最左邊的前綴進(jìn)行有效的查找.例如：
假設(shè)存在組合索引it1c1c2(c1,c2),查詢語句select * from t1 where c1=1 and c2=2能夠使用該索引.查詢語句select * from t1 where c1=1也能夠使用該索引.但是,查詢語句select * from t1 where c2=2不能夠使用該索引,因?yàn)闆]有組合索引的引導(dǎo)列,即,要想使用c2列進(jìn)行查找,必需出現(xiàn)c1等于某值.

MYSQL教程?索引是快速搜索的關(guān)鍵.MySQL索引的建立對于MySQL的高效運(yùn)行是很重要的.

MYSQL教程在數(shù)據(jù)庫表中,對字段建立索引可以大大提高查詢速度.假如我們創(chuàng)建了一個 mytable表：

MYSQL教程
CREATE TABLE mytable(
ID INT NOT NULL, 
username VARCHAR(16) NOT NULL
);

MYSQL教程我們隨機(jī)向里面插入了10000條記錄,其中有一條：5555, admin.

MYSQL教程在查找username="admin"的記錄 SELECT * FROM mytable WHERE username='admin';時,如果在username上已經(jīng)建立了索引,MySQL無須任何掃描,即準(zhǔn)確可找到該記錄.相反,MySQL會掃描所有記錄,即要查詢10000條記錄.

MYSQL教程索引的創(chuàng)建

MYSQL教程
ALTER TABLE

MYSQL教程適用于表創(chuàng)建完畢之后再添加

MYSQL教程ALTER TABLE 表名 ADD 索引類型 (unique,primary key,fulltext,index)[索引名](字段名)

MYSQL教程
ALTER TABLE `table_name` ADD INDEX `index_name` (`column_list`) -- 索引名,可要可不要;如果不要,當(dāng)前的索引名就是該字段名; 
ALTER TABLE `table_name` ADD UNIQUE (`column_list`) 
ALTER TABLE `table_name` ADD PRIMARY KEY (`column_list`) 
ALTER TABLE `table_name` ADD FULLTEXT KEY (`column_list`) 
CREATE INDEX

MYSQL教程CREATE INDEX可對表增加普通索引或UNIQUE索引

MYSQL教程--例,只能添加這兩種索引;

MYSQL教程
CREATE INDEX index_name ON table_name (column_list) 
CREATE UNIQUE INDEX index_name ON table_name (column_list) 

MYSQL教程另外,還可以在建表時添加

MYSQL教程
CREATE TABLE `test1` ( 
 `id` smallint(5) UNSIGNED AUTO_INCREMENT NOT NULL, -- 注意,下面創(chuàng)建了主鍵索引,這里就不用創(chuàng)建了 
 `username` varchar(64) NOT NULL COMMENT '用戶名', 
 `nickname` varchar(50) NOT NULL COMMENT '昵稱/姓名', 
 `intro` text, 
 PRIMARY KEY (`id`), 
 UNIQUE KEY `unique1` (`username`), -- 索引名稱,可要可不要,不要就是和列名一樣 
 KEY `index1` (`nickname`), 
 FULLTEXT KEY `intro` (`intro`) 
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='后臺用戶表'; 

MYSQL教程索引的刪除

MYSQL教程
DROP INDEX `index_name` ON `talbe_name` 
ALTER TABLE `table_name` DROP INDEX `index_name` 
-- 這兩句都是等價(jià)的,都是刪除掉table_name中的索引index_name; 
 
ALTER TABLE `table_name` DROP PRIMARY KEY -- 刪除主鍵索引,注意主鍵索引只能用這種方式刪除 

MYSQL教程索引的查看

MYSQL教程
show index from tablename \G;

MYSQL教程索引的更改

MYSQL教程更改個毛線,刪掉重建一個既可--

MYSQL教程創(chuàng)建索引的技巧

MYSQL教程1.維度高的列創(chuàng)建索引

MYSQL教程數(shù)據(jù)列中 不重復(fù)值 出現(xiàn)的個數(shù),這個數(shù)量越高,維度就越高

MYSQL教程如數(shù)據(jù)表中存在8行數(shù)據(jù)a ,b ,c,d,a,b,c,d這個表的維度為4

MYSQL教程要為維度高的列創(chuàng)建索引,如性別和年齡,那年齡的維度就高于性別

MYSQL教程性別這樣的列不適合創(chuàng)建索引,因?yàn)榫S度過低

MYSQL教程2.對 where,on,group by,order by 中出現(xiàn)的列使用索引

MYSQL教程3.對較小的數(shù)據(jù)列使用索引,這樣會使索引文件更小,同時內(nèi)存中也可以裝載更多的索引鍵

MYSQL教程4.為較長的字符串使用前綴索引

MYSQL教程5.不要過多創(chuàng)建索引,除了增加額外的磁盤空間外,對于DML操作的速度影響很大,因?yàn)槠涿吭鰟h改一次就得從新建立索引

MYSQL教程6.使用組合索引,可以減少文件索引大小,在使用時速度要優(yōu)于多個單列索引

MYSQL教程組合索引與前綴索引

MYSQL教程注意,這兩種稱呼是對建立索引技巧的一種稱呼,并非索引的類型;

MYSQL教程組合索引

MYSQL教程MySQL單列索引和組合索引究竟有何區(qū)別呢?

MYSQL教程為了形象地對比兩者,先建一個表：

MYSQL教程
CREATE TABLE `myIndex` ( 
 `i_testID` INT NOT NULL AUTO_INCREMENT, 
 `vc_Name` VARCHAR(50) NOT NULL, 
 `vc_City` VARCHAR(50) NOT NULL, 
 `i_Age` INT NOT NULL, 
 `i_SchoolID` INT NOT NULL, 
 PRIMARY KEY (`i_testID`) 
); 

MYSQL教程假設(shè)表內(nèi)已有1000條數(shù)據(jù),在這 10000 條記錄里面 7 上 8 下地分布了 5 條 vc_Name="erquan" 的記錄,只不過 city,age,school 的組合各不相同.來看這條 T-SQL：

MYSQL教程SELECT `i_testID` FROM `myIndex` WHERE `vc_Name`='erquan' AND `vc_City`='鄭州' AND `i_Age`=25; -- 關(guān)聯(lián)搜索;
首先考慮建MySQL單列索引：

MYSQL教程在 vc_Name 列上建立了索引.執(zhí)行 T-SQL 時,MYSQL 很快將目標(biāo)鎖定在了 vc_Name=erquan 的 5 條記錄上,取出來放到一中間結(jié)果集.在這個結(jié)果集里,先排除掉 vc_City 不等于"鄭州"的記錄,再排除 i_Age 不等于 25 的記錄,最后篩選出唯一的符合條件的記錄.雖然在 vc_Name 上建立了索引,查詢時MYSQL不用掃描整張表,效率有所提高,但離我們的要求還有一定的距離.同樣的,在 vc_City 和 i_Age 分別建立的MySQL單列索引的效率相似.

MYSQL教程為了進(jìn)一步榨取 MySQL 的效率,就要考慮建立組合索引.就是將 vc_Name,vc_City,i_Age 建到一個索引里：

MYSQL教程ALTER TABLE `myIndex` ADD INDEX `name_city_age` (vc_Name(10),vc_City,i_Age);

MYSQL教程建表時,vc_Name 長度為 50,這里為什么用 10 呢?這就是下文要說到的前綴索引,因?yàn)橐话闱闆r下名字的長度不會超過 10,這樣會加速索引查詢速度,還會減少索引文件的大小,提高 INSERT 的更新速度.

MYSQL教程執(zhí)行 T-SQL 時,MySQL 無須掃描任何記錄就到找到唯一的記錄!!

MYSQL教程如果分別在 vc_Name,vc_City,i_Age 上建立單列索引,讓該表有 3 個單列索引,查詢時和上述的組合索引效率一樣嗎?答案是大不一樣,遠(yuǎn)遠(yuǎn)低于我們的組合索引.雖然此時有了三個索引, 但 MySQL 只能用到其中的那個它認(rèn)為似乎是最有效率的單列索引,另外兩個是用不到的,也就是說還是一個全表掃描的過程 .

MYSQL教程建立這樣的組合索引,其實(shí)是相當(dāng)于分別建立了

• vc_Name,vc_City,i_Age
• vc_Name,vc_City
• vc_Name

MYSQL教程這樣的三個組合索引!為什么沒有 vc_City,i_Age 等這樣的組合索引呢?這是因?yàn)?mysql 組合索引 "最左前綴" 的結(jié)果.簡單的理解就是只從最左面的開始組合.并不是只要包含這三列的查詢都會用到該組合索引,下面的幾個 T-SQL 會用到：

MYSQL教程
SELECT * FROM myIndex WHREE vc_Name="erquan" AND vc_City="鄭州" SELECT * FROM myIndex WHREE vc_Name="erquan"

MYSQL教程而下面幾個則不會用到：

MYSQL教程
SELECT * FROM myIndex WHREE i_Age=20 AND vc_City="鄭州" SELECT * FROM myIndex WHREE vc_City="鄭州"

MYSQL教程也就是,name_city_age(vc_Name(10),vc_City,i_Age) 從左到右進(jìn)行索引,如果沒有左前索引Mysql不執(zhí)行索引查詢

MYSQL教程前綴索引

MYSQL教程如果索引列長度過長,這種列索引時將會產(chǎn)生很大的索引文件,不便于操作,可以使用前綴索引方式進(jìn)行索引前綴索引應(yīng)該控制在一個合適的點(diǎn),控制在0.31黃金值即可(大于這個值就可以創(chuàng)建)

MYSQL教程SELECT COUNT(DISTINCT(LEFT(`title`,10)))/COUNT(*) FROM Arctic; -- 這個值大于0.31就可以創(chuàng)建前綴索引,Distinct去重復(fù) ALTER TABLE `user` ADD INDEX `uname`(title(10)); -- 增加前綴索引SQL,將人名的索引建立在10,這樣可以減少索引文件大小,加快索引查詢速度

MYSQL教程什么樣的sql不走索引

MYSQL教程要盡量避免這些不走索引的sql

MYSQL教程
SELECT `sname` FROM `stu` WHERE `age`+10=30;-- 不會使用索引,因?yàn)樗兴饕袇⑴c了計(jì)算 
 
SELECT `sname` FROM `stu` WHERE LEFT(`date`,4) <1990; -- 不會使用索引,因?yàn)槭褂昧撕瘮?shù)運(yùn)算,原理與上面相同 
 
SELECT * FROM `houdunwang` WHERE `uname` LIKE'后盾%' -- 走索引 
 
SELECT * FROM `houdunwang` WHERE `uname` LIKE "%后盾%" -- 不走索引 
 
-- 正則表達(dá)式不使用索引,這應(yīng)該很好理解,所以為什么在SQL中很難看到regexp關(guān)鍵字的原因 
 
-- 字符串與數(shù)字比較不使用索引; 
CREATE TABLE `a` (`a` char(10)); 
EXPLAIN SELECT * FROM `a` WHERE `a`="1" -- 走索引 
EXPLAIN SELECT * FROM `a` WHERE `a`=1 -- 不走索引 
 
select * from dept where dname='xxx' or loc='xx' or deptno=45 --如果條件中有or,即使其中有條件帶索引也不會使用.換言之,就是要求使用的所有字段,都必須建立索引, 我們建議大家盡量避免使用or 關(guān)鍵字 
 
-- 如果mysql估計(jì)使用全表掃描要比使用索引快,則不使用索引 

MYSQL教程多表關(guān)聯(lián)時的索引效率

MYSQL教程
SELECT `sname` FROM `stu` WHERE LEFT(`date`,4) <1990; -- 不會使用索引,因?yàn)槭褂昧撕瘮?shù)運(yùn)算,原理與上面相同
SELECT * FROM `houdunwang` WHERE `uname` LIKE'后盾%' -- 走索引
SELECT * FROM `houdunwang` WHERE `uname` LIKE "%后盾%" -- 不走索引

MYSQL教程

MYSQL教程從上圖可以看出,所有表的type為all,表示全表索引;也就是6 6 6,共遍歷查詢了216次;

MYSQL教程除第一張表示全表索引(必須的,要以此關(guān)聯(lián)其他表),其余的為range(索引區(qū)間獲得),也就是6+1+1+1,共遍歷查詢9次即可;

MYSQL教程所以我們建議在多表join的時候盡量少join幾張表,因?yàn)橐徊恍⌒木褪且粋€笛卡爾乘積的恐怖掃描,另外,我們還建議盡量使用left join,以少關(guān)聯(lián)多.因?yàn)槭褂胘oin 的話,第一張表是必須的全掃描的,以少關(guān)聯(lián)多就可以減少這個掃描次數(shù).

MYSQL教程索引的弊端

MYSQL教程不要盲目的創(chuàng)建索引,只為查詢操作頻繁的列創(chuàng)建索引,創(chuàng)建索引會使查詢操作變得更加快速,但是會降低增加、刪除、更新操作的速度,因?yàn)閳?zhí)行這些操作的同時會對索引文件進(jìn)行重新排序或更新;

MYSQL教程但是,在互聯(lián)網(wǎng)應(yīng)用中,查詢的語句遠(yuǎn)遠(yuǎn)大于DML的語句,甚至可以占到80%~90%,所以也不要太在意,只是在大數(shù)據(jù)導(dǎo)入時,可以先刪除索引,再批量插入數(shù)據(jù),最后再添加索引;

轉(zhuǎn)載請注明本頁網(wǎng)址：
http://www.snjht.com/jiaocheng/2351.html