sql學習筆記（20）----------MySQL 索引最佳化全攻略，mysql索引最佳化

sql學習筆記（20）----------MySQL 索引最佳化全攻略，mysql索引最佳化

所謂索引就是為特定的mysql欄位進行一些特定的演算法排序,比如二叉樹的演算法和雜湊演算法,雜湊演算法是通過建立特徵值,然後根據特徵值來快速尋找。而用的最多,並且是mysql預設的就是二叉樹演算法 BTREE,通過BTREE演算法建立索引的欄位,比如掃描20行就能得到未使用BTREE前掃描了2^20行的結果,具體的實現方式後續本部落格會出一個演算法專題裡面會有具體的分析討論;

Explain最佳化查詢檢測

EXPLAIN可以協助開發人員分析SQL問題,explain顯示了mysql如何使用索引來處理select語句以及串連表,可以協助選擇更好的索引和寫出更最佳化的查詢語句.

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

Explain select * from blog where false;

mysql在執行一條查詢之前,會對發出的每條SQL進行分析,決定是否使用索引或全表掃描如果發送一條select * from blog where falseMysql是不會執行查詢操作的,因為經過SQL分析器的分析後MySQL已經清楚不會有任何語句符合操作;

Example

mysql> EXPLAIN SELECT `birday` FROM `user` WHERE `birthday` < "1990/2/2"; -- 結果： id: 1 select_type: SIMPLE -- 查詢類型（簡單查詢,聯集查詢,子查詢） table: user -- 顯示這一行的資料是關於哪張表的 type: range -- 區間索引（在小於1990/2/2區間的資料),這是重要的列,顯示串連使用了何種類型。從最好到最差的連線類型為system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL,const代表一次就命中,ALL代表掃描了全表才確定結果。一般來說,得保證查詢至少達到range層級,最好能達到ref。 possible_keys: birthday  -- 指出MySQL能使用哪個索引在該表中找到行。如果是空的,沒有相關的索引。這時要提高效能,可通過檢驗WHERE子句,看是否引用某些欄位,或者檢查欄位不是適合索引。  key: birthday -- 實際使用到的索引。如果為NULL,則沒有使用索引。如果為primary的話,表示使用了主鍵。 key_len: 4 -- 最長的索引寬度。如果鍵是NULL,長度就是NULL。在不損失精確性的情況下,長度越短越好 ref: const -- 顯示哪個欄位或常數與key一起被使用。  rows: 1 -- 這個數表示mysql要遍曆多少資料才能找到,在innodb上是不準確的。 Extra: Using where; Using index -- 執行狀態說明,這裡可以看到的壞的例子是Using temporary和Using

select_type

Extra與type詳細說明

其中type:

索引

索引的類型

UNIQUE唯一索引

不可以出現相同的值,可以有NULL值

INDEX普通索引

允許出現相同的索引內容

PRIMARY KEY主鍵索引

不允許出現相同的值,且不能為NULL值,一個表只能有一個primary_key索引

fulltext index 全文索引

上述三種索引都是針對列的值發揮作用,但全文索引,可以針對值中的某個單詞,比如一篇文章中的某個詞, 然而並沒有什麼卵用,因為只有myisam以及英文支援,並且效率讓人不敢恭維,但是可以用coreseek和xunsearch等第三方應用來完成這個需求

索引的CURD

索引的建立

ALTER TABLE

適用於表建立完畢之後再添加

ALTER TABLE 表名 ADD 索引類型 (unique,primary key,fulltext,index)[索引名](欄位名)

ALTER TABLE `table_name` ADD INDEX `index_name` (`column_list`) -- 索引名,可要可不要;如果不要,當前的索引名就是該欄位名; 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

CREATE INDEX可對錶增加普通索引或UNIQUE索引

--例,只能添加這兩種索引; CREATE INDEX index_name ON table_name (column_list) CREATE UNIQUE INDEX index_name ON table_name (column_list)

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

CREATE TABLE `test1` (   `id` smallint(5) UNSIGNED AUTO_INCREMENT NOT NULL, -- 注意,下面建立了主鍵索引,這裡就不用建立了   `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='後台使用者表';

索引的刪除

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

索引的查看

show index from tablename \G;

索引的更改

更改個毛線,刪掉重建一個既可

建立索引的技巧

1.維度高的列建立索引

資料列中 不重複值 出現的個數,這個數量越高,維度就越高

如資料表中存在8行資料a ,b ,c,d,a,b,c,d這個表的維度為4

要為維度高的列建立索引,如性別和年齡,那年齡的維度就高於性別

性別這樣的列不適合建立索引,因為維度過低

2.對 where,on,group by,order by 中出現的列使用索引

3.對較小的資料列使用索引,這樣會使索引檔案更小,同時記憶體中也可以裝載更多的索引鍵

4.為較長的字串使用首碼索引

5.不要過多建立索引,除了增加額外的磁碟空間外,對於DML操作的速度影響很大,因為其每增刪改一次就得從建立立索引

6.使用複合式索引,可以減少檔案索引大小,在使用時速度要優於多個單列索引

複合式索引與首碼索引

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

複合式索引

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`)  );

假設表內已有1000條資料,在這 10000 條記錄裡面 7 上 8 下地分布了 5 條 vc_Name=”erquan” 的記錄,只不過 city,age,school 的組合各不相同。來看這條 T-SQL：

SELECT `i_testID` FROM `myIndex` WHERE `vc_Name`='erquan' AND `vc_City`='鄭州' AND `i_Age`=25; -- 關聯搜尋;

首先考慮建MySQL單列索引：

在 vc_Name 列上建立了索引。執行 T-SQL 時,MYSQL 很快將目標鎖定在了 vc_Name=erquan 的 5 條記錄上,取出來放到一中間結果集。在這個結果集裡,先排除掉 vc_City 不等於”鄭州”的記錄,再排除 i_Age 不等於 25 的記錄,最後篩選出唯一的合格記錄。雖然在 vc_Name 上建立了索引,查詢時MYSQL不用掃描整張表,效率有所提高,但離我們的要求還有一定的距離。同樣的,在 vc_City 和 i_Age 分別建立的MySQL單列索引的效率相似。

為了進一步榨取 MySQL 的效率,就要考慮建立複合式索引。就是將 vc_Name,vc_City,i_Age 建到一個索引裡：

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

建表時,vc_Name 長度為 50,這裡為什麼用 10 呢?這就是下文要說到的首碼索引,因為一般情況下名字的長度不會超過 10,這樣會加速索引查詢速度,還會減少索引檔案的大小,提高 INSERT 的更新速度。

執行 T-SQL 時,MySQL 無須掃描任何記錄就到找到唯一的記錄!!

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

建立這樣的複合式索引,其實是相當於分別建立了

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

這樣的三個複合式索引!為什麼沒有 vc_City,i_Age 等這樣的複合式索引呢?這是因為 mysql 複合式索引 “最左首碼” 的結果。簡單的理解就是只從最左面的開始組合。並不是只要包含這三列的查詢都會用到該複合式索引,下面的幾個 T-SQL 會用到：

SELECT * FROM myIndex WHREE vc_Name=”erquan” AND vc_City=”鄭州” SELECT * FROM myIndex WHREE vc_Name=”erquan”

而下面幾個則不會用到：

SELECT * FROM myIndex WHREE i_Age=20 AND vc_City=”鄭州” SELECT * FROM myIndex WHREE vc_City=”鄭州”

也就是,name_city_age(vc_Name(10),vc_City,i_Age) 從左至右進行索引,如果沒有左前索引Mysql不執行索引查詢

首碼索引

如果索引列長度過長,這種列索引時將會產生很大的索引檔案,不便於操作,可以使用首碼索引方式進行索引首碼索引應該控制在一個合適的點,控制在0.31黃金值即可(大於這個值就可以建立)

SELECT COUNT(DISTINCT(LEFT(`title`,10)))/COUNT(*) FROM Arctic; — 這個值大於0.31就可以建立首碼索引,Distinct去重複 ALTER TABLE `user` ADD INDEX `uname`(title(10)); — 增加首碼索引SQL,將人名的索引建立在10,這樣可以減少索引檔案大小,加快索引查詢速度

什麼樣的sql不走索引

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

SELECT `sname` FROM `stu` WHERE `age`+10=30;-- 不會使用索引,因為所有索引列參與了計算 SELECT `sname` FROM `stu` WHERE LEFT(`date`,4) <1990; -- 不會使用索引,因為使用了函數運算,原理與上面相同 SELECT * FROM `houdunwang` WHERE `uname` LIKE'後盾%' -- 走索引 SELECT * FROM `houdunwang` WHERE `uname` LIKE "%後盾%" -- 不走索引 -- Regex不使用索引,這應該很好理解,所以為什麼在SQL中很難看到regexp關鍵字的原因 -- 字串與數字比較不使用索引; 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 關鍵字 -- 如果mysql估計使用全表掃描要比使用索引快,則不使用索引

多表關聯時的索引效率

• SELECT `sname` FROM `stu` WHERE LEFT(`date`,4) <1990; — 不會使用索引,因為使用了函數運算,原理與上面相同
• SELECT * FROM `houdunwang` WHERE `uname` LIKE’後盾%’ — 走索引
• SELECT * FROM `houdunwang` WHERE `uname` LIKE “%後盾%” — 不走索引

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

除第一張表示全表索引(必須的,要以此關聯其他表),其餘的為range(索引區間獲得),也就是6+1+1+1,共遍曆查詢9次即可;

所以我們建議在多表join的時候盡量少join幾張表,因為一不小心就是一個笛卡爾乘積的恐怖掃描,另外,我們還建議盡量使用left join,以少關聯多.因為使用join 的話,第一張表是必須的全掃描的,以少關聯多就可以減少這個掃描次數.

索引的弊端

不要盲目的建立索引,只為查詢操作頻繁的列建立索引,建立索引會使查詢操作變得更加快速,但是會降低增加、刪除、更新操作的速度,因為執行這些操作的同時會對索引檔案進行重新排序或更新;

但是,在互連網應用中,查詢的語句遠遠大於DML的語句,甚至可以佔到80%~90%,所以也不要太在意,只是在大資料匯入時,可以先刪除索引,再批量插入資料,最後再添加索引。

 

 

 

本文借鑒於http://www.codeceo.com/article/mysql-index.html