【mysql的設計與優化專題(6)】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 false

Mysql是不會執行查詢操作的,因為經過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

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

Extra與type詳細說明

Distinct:一旦MYSQL找到了與行相聯合比對的行,就不再搜尋了
Not exists: MYSQL優化了LEFT JOIN,一旦它找到了比對LEFT JOIN标準的行,就不再搜尋了
Range checked for each Record（index map:#）:沒有找到理想的索引,是以對于從前面表中來的每一個行組合,MYSQL檢查使用哪個索引,并用它來從表中傳回行。這是使用索引的最慢的連接配接之一
Using filesort: 看到這個的時候,查詢就需要優化了。MYSQL需要進行額外的步驟來發現如何對傳回的行排序。它根據連接配接類型以及存儲排序鍵值和比對條件的全部行的行指針來排序全部行
Using index: 列資料是從僅僅使用了索引中的資訊而沒有讀取實際的行動的表傳回的,這發生在對表的全部的請求列都是同一個索引的部分的時候
Using temporary 看到這個的時候,查詢需要優化了。這裡,MYSQL需要建立一個臨時表來存儲結果,這通常發生在對不同的列集進行ORDER BY上,而不是GROUP BY上
Where used 使用了WHERE從句來限制哪些行将與下一張表比對或者是傳回給使用者。如果不想傳回表中的全部行,并且連接配接類型ALL或index,這就會發生,或者是查詢有問題不同連接配接類型的解釋（按照效率高低的順序排序
system 表隻有一行：system表。這是const連接配接類型的特殊情況
const:表中的一個記錄的最大值能夠比對這個查詢（索引可以是主鍵或惟一索引）。因為隻有一行,這個值實際就是常數,因為MYSQL先讀這個值然後把它當做常數來對待
eq_ref:在連接配接中,MYSQL在查詢時,從前面的表中,對每一個記錄的聯合都從表中讀取一個記錄,它在查詢使用了索引為主鍵或惟一鍵的全部時使用
ref:這個連接配接類型隻有在查詢使用了不是惟一或主鍵的鍵或者是這些類型的部分（比如,利用最左邊字首）時發生。對于之前的表的每一個行聯合,全部記錄都将從表中讀出。這個類型嚴重依賴于根據索引比對的記錄多少—越少越好+
range:這個連接配接類型使用索引傳回一個範圍中的行,比如使用>或<查找東西時發生的情況+
index: 這個連接配接類型對前面的表中的每一個記錄聯合進行完全掃描（比ALL更好,因為索引一般小于表資料）+
ALL:這個連接配接類型對于前面的每一個記錄聯合進行完全掃描,這一般比較糟糕,應該盡量避免

其中type:

如果是Only index,這意味着資訊隻用索引樹中的資訊檢索出的,這比掃描整個表要快。
如果是where used,就是使用上了where限制。
如果是impossible where 表示用不着where,一般就是沒查出來啥。
如果此資訊顯示Using filesort或者Using temporary的話會很吃力,WHERE和ORDER BY的索引經常無法兼顧,如果按照WHERE來确定索引,那麼在ORDER BY時,就必然會引起Using filesort,這就要看是先過濾再排序劃算,還是先排序再過濾劃算。

索引

索引的類型

UNIQUE唯一索引

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

INDEX普通索引

允許出現相同的索引内容

PRIMARY KEY主鍵索引

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

fulltext index 全文索引

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

索引的CURD

索引的建立

ALTER TABLE

适用于表建立完畢之後再添加

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 -- 删除主鍵索引,注意主鍵索引隻能用這種方式删除

索引的檢視

索引的更改

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

建立索引的技巧

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：

首先考慮建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 建到一個索引裡：

建表時,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 "%後盾%" -- 不走索引

-- 正規表達式不使用索引,這應該很好了解,是以為什麼在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估計使用全表掃描要比使用索引快,則不使用索引

多表關聯時的索引效率

【mysql的設計與優化專題(6)】mysql索引攻略

從上圖可以看出,所有表的type為all,表示全表索引;也就是666,共周遊查詢了216次;

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

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

索引的弊端

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

但是,在網際網路應用中,查詢的語句遠遠大于DML的語句,甚至可以占到80%~90%,是以也不要太在意,隻是在大資料導入時,可以先删除索引,再批量插入資料,最後再添加索引;

轉載于:https://www.cnblogs.com/nixi8/p/4574709.html

【mysql的設計與優化專題(6)】mysql索引攻略

Explain優化查詢檢測

索引

索引的類型

索引的CURD

索引的建立

索引的删除

索引的檢視

索引的更改

建立索引的技巧

組合索引與字首索引

字首索引

什麼樣的sql不走索引

多表關聯時的索引效率

索引的弊端

繼續閱讀

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

PAT 1089 Insert or Merge[難]

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

Ambari介紹和架構原理

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

登入plsql 報錯 the account is locked --使用者被鎖

SequoiaDB巨杉資料庫C++驅動概述

win10本地scala和spark安裝安裝scala安裝spark