mysql查看索引

1、mysql查看索引mysql> show index from tblname;mysql> show keys from tblname;· Table表的名稱。· Non_unique如果索引不能包括重復(fù)詞，則為0。如果可以，則為1。· Key_name索引的名稱。· Seq_in_index索引中的列序列號，從1開始。· Column_name列名稱。· Collation列以什么方式存儲在索引中。在MySQL中，有值A(chǔ)（升序）或NULL（無分類）。· Cardinality索引中唯一值的數(shù)目的估計值。通過

2、運行ANALYZE TABLE或myisamchk -a可以更新?；鶖?shù)根據(jù)被存儲為整數(shù)的統(tǒng)計數(shù)據(jù)來計數(shù)，所以即使對于小型表，該值也沒有必要是精確的?；鶖?shù)越大，當(dāng)進(jìn)行聯(lián)合時，MySQL使用該索引的機 會就越大。· Sub_part如果列只是被部分地編入索引，則為被編入索引的字符的數(shù)目。如果整列被編入索引，則為NULL。· Packed指示關(guān)鍵字如何被壓縮。如果沒有被壓縮，則為NULL。· Null如果列含有NULL，則含有YES。如果沒有，則該列含有NO。· Index_type用過的索引方法（BTREE, FULLTEXT, HASH, RTREE）。&

3、#183; Comment 這是以讀為主的線上庫rootread 02:28:07>show status like Handler_read%;  +-+  | Variable_name         | Value |  +-+  | Handler_read_first    | 0     |  | Handl

4、er_read_key      | 0     |  | Handler_read_next     | 0     |  | Handler_read_prev     | 0     |  | Handler_read_rnd     

5、; | 0     |  | Handler_read_rnd_next | 61    |  +-+  6 rows in set (0.41 sec)Handler_read_first 代表讀取索引頭的次數(shù)，如果這個值很高，說明全索引掃描很多。Handler_read_key代表一個索引被使用的次數(shù)，如果我們新增加一個索引，可以查看Handler_read_key是否有增加，如果有增加，說明sql用到索引。Handler_read_next 代表讀取索引的下列

6、，一般發(fā)生range scan。Handler_read_prev 代表讀取索引的上列，一般發(fā)生在ORDER BY DESC。Handler_read_rnd 代表在固定位置讀取行，如果這個值很高，說明對大量結(jié)果集進(jìn)行了排序、進(jìn)行了全表掃描、關(guān)聯(lián)查詢沒有用到合適的KEY。Handler_read_rnd_next 代表進(jìn)行了很多表掃描，查詢性能低下。寫在前面：索引對查詢的速度有著至關(guān)重要的影響，理解索引也是進(jìn)行數(shù)據(jù)庫性能調(diào)優(yōu)的起點。考慮如下情況，假設(shè)數(shù)據(jù)庫中一個表有106條記錄，DBMS的頁面大小為4K，并存儲100條記錄。如果沒有索引，查詢將對整個表進(jìn)行掃描，最壞的情況下，如果所有數(shù)據(jù)頁都不

7、在內(nèi)存，需要讀取104個頁面，如果這104個頁面在磁盤上隨機分布，需要進(jìn)行104次I/O，假設(shè)磁盤每次I/O時間為10ms(忽略數(shù)據(jù)傳輸時間)，則總共需要100s(但實際上要好很多很多)。如果對之建立B-Tree索引，則只需要進(jìn)行l(wèi)og100(106)=3次頁面讀取，最壞情況下耗時30ms。這就是索引帶來的效果，很多時候，當(dāng)你的應(yīng)用程序進(jìn)行SQL查詢速度很慢時，應(yīng)該想想是否可以建索引。進(jìn)入正題：第二章、索引與優(yōu)化1、選擇索引的數(shù)據(jù)類型MySQL支持很多數(shù)據(jù)類型，選擇合適的數(shù)據(jù)類型存儲數(shù)據(jù)對性能有很大的影響。通常來說，可以遵循以下一些指導(dǎo)原則：(1)越小的數(shù)據(jù)類型通常更好：越小的數(shù)據(jù)類型通常在磁

8、盤、內(nèi)存和CPU緩存中都需要更少的空間，處理起來更快。(2)簡單的數(shù)據(jù)類型更好：整型數(shù)據(jù)比起字符，處理開銷更小，因為字符串的比較更復(fù)雜。在MySQL中，應(yīng)該用內(nèi)置的日期和時間數(shù)據(jù)類型，而不是用字符串來存儲時間；以及用整型數(shù)據(jù)類型存儲IP地址。(3)盡量避免NULL：應(yīng)該指定列為NOT NULL，除非你想存儲NULL。在MySQL中，含有空值的列很難進(jìn)行查詢優(yōu)化，因為它們使得索引、索引的統(tǒng)計信息以及比較運算更加復(fù)雜。你應(yīng)該用0、一個特殊的值或者一個空串代替空值。1.1、選擇標(biāo)識符選擇合適的標(biāo)識符是非常重要的。選擇時不僅應(yīng)該考慮存儲類型，而且應(yīng)該考慮MySQL是怎樣進(jìn)行運算和比較的。一旦選定數(shù)據(jù)類

9、型，應(yīng)該保證所有相關(guān)的表都使用相同的數(shù)據(jù)類型。(1)    整型：通常是作為標(biāo)識符的最好選擇，因為可以更快的處理，而且可以設(shè)置為AUTO_INCREMENT。(2)    字符串：盡量避免使用字符串作為標(biāo)識符，它們消耗更好的空間，處理起來也較慢。而且，通常來說，字符串都是隨機的，所以它們在索引中的位置也是隨機的，這會導(dǎo)致頁面分裂、隨機訪問磁盤，聚簇索引分裂（對于使用聚簇索引的存儲引擎）。2、索引入門對于任何DBMS，索引都是進(jìn)行優(yōu)化的最主要的因素。對于少量的數(shù)據(jù)，沒有合適的索引影響不是很大，但是，當(dāng)隨著數(shù)據(jù)量的增加，性能會急劇下降。如

10、果對多列進(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不能夠使用該索引，因為沒有組合索引的引導(dǎo)列，即，要想使用c2列進(jìn)行查找，必需出現(xiàn)c1等于某值。2.1、索引的類型索引是在存儲引擎中實現(xiàn)的，而不是在服務(wù)器層中實現(xiàn)的。所以，每種存儲引擎的索引都不一定完全相同，并

11、不是所有的存儲引擎都支持所有的索引類型。2.1.1、B-Tree索引假設(shè)有如下一個表：CREATE TABLE People (   last_name varchar(50)    not null,   first_name varchar(50)    not null,   dob        date    

12、0;      not null,   gender     enum('m', 'f') not null,   key(last_name, first_name, dob); 其索引包含表中每一行的last_name、first_name和dob列。其結(jié)構(gòu)大致如下：  索引存儲的值按索引列中的順序排列?？梢岳肂-Tree索引進(jìn)行全關(guān)鍵字、關(guān)鍵字范圍和關(guān)鍵字前綴查詢，當(dāng)然，如果想

13、使用索引，你必須保證按索引的最左邊前綴(leftmost prefix of the index)來進(jìn)行查詢。(1)匹配全值(Match the full value)：對索引中的所有列都指定具體的值。例如，上圖中索引可以幫助你查找出生于1960-01-01的Cuba Allen。(2)匹配最左前綴(Match a leftmost prefix)：你可以利用索引查找last name為Allen的人，僅僅使用索引中的第1列。(3)匹配列前綴(Match a column prefix)：例如，你可以利用索引查找last name以J開始的人，這僅僅使用索引中的第1列。(4)匹配值的范圍查詢(

14、Match a range of values)：可以利用索引查找last name在Allen和Barrymore之間的人，僅僅使用索引中第1列。(5)匹配部分精確而其它部分進(jìn)行范圍匹配(Match one part exactly and match a range on another part)：可以利用索引查找last name為Allen，而first name以字母K開始的人。(6)僅對索引進(jìn)行查詢(Index-only queries)：如果查詢的列都位于索引中，則不需要讀取元組的值。由于B-樹中的節(jié)點都是順序存儲的，所以可以利用索引進(jìn)行查找(找某些值)，也可以對查詢結(jié)果進(jìn)行O

15、RDER BY。當(dāng)然，使用B-tree索引有以下一些限制：(1) 查詢必須從索引的最左邊的列開始。關(guān)于這點已經(jīng)提了很多遍了。例如你不能利用索引查找在某一天出生的人。(2) 不能跳過某一索引列。例如，你不能利用索引查找last name為Smith且出生于某一天的人。(3) 存儲引擎不能使用索引中范圍條件右邊的列。例如，如果你的查詢語句為WHERE last_name="Smith" AND first_name LIKE 'J%' AND dob='1976-12-23'，則該查詢只會使用索引中的前兩列，因為LIKE是范圍查詢。2.1.2、H

16、ash索引MySQL中，只有Memory存儲引擎顯示支持hash索引，是Memory表的默認(rèn)索引類型，盡管Memory表也可以使用B-Tree索引。Memory存儲引擎支持非唯一hash索引，這在數(shù)據(jù)庫領(lǐng)域是罕見的，如果多個值有相同的hash code，索引把它們的行指針用鏈表保存到同一個hash表項中。假設(shè)創(chuàng)建如下一個表：CREATE TABLE testhash (   fname VARCHAR(50) NOT NULL,   lname VARCHAR(50) NOT NULL,   KEY USING HASH(fname)

17、 ENGINE=MEMORY;包含的數(shù)據(jù)如下：假設(shè)索引使用hash函數(shù)f( )，如下：f('Arjen') = 2323f('Baron') = 7437f('Peter') = 8784f('Vadim') = 2458此時，索引的結(jié)構(gòu)大概如下：  Slots是有序的，但是記錄不是有序的。當(dāng)你執(zhí)行mysql> SELECT lname FROM testhash WHERE fname='Peter'MySQL會計算Peter的hash值，然后通過它來查詢索引的行指針。因為f('

18、Peter') = 8784，MySQL會在索引中查找8784，得到指向記錄3的指針。因為索引自己僅僅存儲很短的值，所以，索引非常緊湊。Hash值不取決于列的數(shù)據(jù)類型，一個TINYINT列的索引與一個長字符串列的索引一樣大。 Hash索引有以下一些限制：(1)由于索引僅包含hash code和記錄指針，所以，MySQL不能通過使用索引避免讀取記錄。但是訪問內(nèi)存中的記錄是非常迅速的，不會對性造成太大的影響。(2)不能使用hash索引排序。(3)Hash索引不支持鍵的部分匹配，因為是通過整個索引值來計算hash值的。(4)Hash索引只支持等值比較，例如使用=，IN( )和<

19、;=>。對于WHERE price>100并不能加速查詢。2.1.3、空間(R-Tree)索引MyISAM支持空間索引，主要用于地理空間數(shù)據(jù)類型，例如GEOMETRY。2.1.4、全文(Full-text)索引全文索引是MyISAM的一個特殊索引類型，主要用于全文檢索。3、高性能的索引策略3.1、聚簇索引(Clustered Indexes)聚簇索引保證關(guān)鍵字的值相近的元組存儲的物理位置也相同（所以字符串類型不宜建立聚簇索引，特別是隨機字符串，會使得系統(tǒng)進(jìn)行大量的移動操作），且一個表只能有一個聚簇索引。因為由存儲引擎實現(xiàn)索引，所以，并不是所有的引擎都支持聚簇索引。目前，只有soli

20、dDB和InnoDB支持。聚簇索引的結(jié)構(gòu)大致如下：  注：葉子頁面包含完整的元組，而內(nèi)節(jié)點頁面僅包含索引的列(索引的列為整型)。一些DBMS允許用戶指定聚簇索引，但是MySQL的存儲引擎到目前為止都不支持。InnoDB對主鍵建立聚簇索引。如果你不指定主鍵，InnoDB會用一個具有唯一且非空值的索引來代替。如果不存在這樣的索引，InnoDB會定義一個隱藏的主鍵，然后對其建立聚簇索引。一般來說，DBMS都會以聚簇索引的形式來存儲實際的數(shù)據(jù)，它是其它二級索引的基礎(chǔ)。3.1.1、InnoDB和MyISAM的數(shù)據(jù)布局的比較為了更加理解聚簇索引和非聚簇索引，或者primary索引和se

21、cond索引(MyISAM不支持聚簇索引)，來比較一下InnoDB和MyISAM的數(shù)據(jù)布局，對于如下表：CREATE TABLE layout_test (   col1 int NOT NULL,   col2 int NOT NULL,   PRIMARY KEY(col1),   KEY(col2); 假設(shè)主鍵的值位于1-10,000之間，且按隨機順序插入，然后用OPTIMIZE TABLE進(jìn)行優(yōu)化。col2隨機賦予1-100之間的值，所以會存在許多重復(fù)的值。

22、(1)    MyISAM的數(shù)據(jù)布局其布局十分簡單，MyISAM按照插入的順序在磁盤上存儲數(shù)據(jù)，如下： 注：左邊為行號(row number)，從0開始。因為元組的大小固定，所以MyISAM可以很容易的從表的開始位置找到某一字節(jié)的位置。據(jù)些建立的primary key的索引結(jié)構(gòu)大致如下： 注：MyISAM不支持聚簇索引，索引中每一個葉子節(jié)點僅僅包含行號(row number)，且葉子節(jié)點按照col1的順序存儲。來看看col2的索引結(jié)構(gòu)： 實際上，在MyISAM中，primary key和其它索引沒有什么區(qū)別。Primary key僅僅

23、只是一個叫做PRIMARY的唯一，非空的索引而已。(2)    InnoDB的數(shù)據(jù)布局InnoDB按聚簇索引的形式存儲數(shù)據(jù)，所以它的數(shù)據(jù)布局有著很大的不同。它存儲表的結(jié)構(gòu)大致如下： 注：聚簇索引中的每個葉子節(jié)點包含primary key的值，事務(wù)ID和回滾指針(rollback pointer)用于事務(wù)和MVCC，和余下的列(如col2)。相對于MyISAM，二級索引與聚簇索引有很大的不同。InnoDB的二級索引的葉子包含primary key的值，而不是行指針(row pointers)，這減小了移動數(shù)據(jù)或者數(shù)據(jù)頁面分裂時維護(hù)二級索引的開銷，因為Inn

24、oDB不需要更新索引的行指針。其結(jié)構(gòu)大致如下： 聚簇索引和非聚簇索引表的對比：  3.1.2、按primary key的順序插入行(InnoDB)如果你用InnoDB，而且不需要特殊的聚簇索引，一個好的做法就是使用代理主鍵(surrogate key)獨立于你的應(yīng)用中的數(shù)據(jù)。最簡單的做法就是使用一個AUTO_INCREMENT的列，這會保證記錄按照順序插入，而且能提高使用primary key進(jìn)行連接的查詢的性能。應(yīng)該盡量避免隨機的聚簇主鍵，例如，字符串主鍵就是一個不好的選擇，它使得插入操作變得隨機。  3.2、覆蓋索引(Covering In

25、dexes)如果索引包含滿足查詢的所有數(shù)據(jù)，就稱為覆蓋索引。覆蓋索引是一種非常強大的工具，能大大提高查詢性能。只需要讀取索引而不用讀取數(shù)據(jù)有以下一些優(yōu)點：(1)索引項通常比記錄要小，所以MySQL訪問更少的數(shù)據(jù)；(2)索引都按值的大小順序存儲，相對于隨機訪問記錄，需要更少的I/O；(3)大多數(shù)據(jù)引擎能更好的緩存索引。比如MyISAM只緩存索引。(4)覆蓋索引對于InnoDB表尤其有用，因為InnoDB使用聚集索引組織數(shù)據(jù)，如果二級索引中包含查詢所需的數(shù)據(jù)，就不再需要在聚集索引中查找了。覆蓋索引不能是任何索引，只有B-TREE索引存儲相應(yīng)的值。而且不同的存儲引擎實現(xiàn)覆蓋索引的方式都不同，并不是所

26、有存儲引擎都支持覆蓋索引(Memory和Falcon就不支持)。對于索引覆蓋查詢(index-covered query)，使用EXPLAIN時，可以在Extra一列中看到“Using index”。例如，在sakila的inventory表中，有一個組合索引(store_id,film_id)，對于只需要訪問這兩列的查詢，MySQL就可以使用索引，如下：mysql> EXPLAIN SELECT store_id, film_id FROM sakila.inventoryG* 1. row *       

27、0;   id: 1 select_type: SIMPLE        table: inventory         type: indexpossible_keys: NULL          key: idx_store_id_film_id   

28、;   key_len: 3          ref: NULL         rows: 5007        Extra: Using index1 row in set (0.17 sec)在大多數(shù)引擎中，只有當(dāng)查詢語句所訪問的列是索引的一部分時，索引才會覆蓋。但是，InnoDB不限于此

29、，InnoDB的二級索引在葉子節(jié)點中存儲了primary key的值。因此，sakila.actor表使用InnoDB，而且對于是last_name上有索引，所以，索引能覆蓋那些訪問actor_id的查詢，如： mysql> EXPLAIN SELECT actor_id, last_name    -> FROM sakila.actor WHERE last_name = 'HOPPER'G* 1. row *        

30、0;  id: 1 select_type: SIMPLE        table: actor         type: refpossible_keys: idx_actor_last_name          key: idx_actor_last_name  

31、60;   key_len: 137          ref: const         rows: 2        Extra: Using where; Using index 3.3、利用索引進(jìn)行排序MySQL中，有兩種方式生成有序結(jié)果集：一是使用filesort，二是按索引順

32、序掃描。利用索引進(jìn)行排序操作是非?？斓模铱梢岳猛凰饕瑫r進(jìn)行查找和排序操作。當(dāng)索引的順序與ORDER BY中的列順序相同且所有的列是同一方向(全部升序或者全部降序)時，可以使用索引來排序。如果查詢是連接多個表，僅當(dāng)ORDER BY中的所有列都是第一個表的列時才會使用索引。其它情況都會使用filesort。create table actor(actor_id int unsigned NOT NULL AUTO_INCREMENT,name      varchar(16) NOT NULL DEFAULT '

33、;',password        varchar(16) NOT NULL DEFAULT '',PRIMARY KEY(actor_id), KEY     (name) ENGINE=InnoDBinsert into actor(name,password) values('cat01','1234567');insert into actor(name,password) values('cat02&

34、#39;,'1234567');insert into actor(name,password) values('ddddd','1234567');insert into actor(name,password) values('aaaaa','1234567'); mysql> explain select actor_id from actor order by actor_id G* 1. row *       

35、0;   id: 1 select_type: SIMPLE        table: actor         type: indexpossible_keys: NULL          key: PRIMARY     

36、0;key_len: 4          ref: NULL         rows: 4        Extra: Using index1 row in set (0.00 sec) mysql> explain select actor_id from actor order by pass

37、word G* 1. row *           id: 1 select_type: SIMPLE        table: actor         type: ALLpossible_keys: NULL       

38、0;  key: NULL      key_len: NULL          ref: NULL         rows: 4        Extra: Using filesort1 row in set (0.00 sec)&

39、#160;mysql> explain select actor_id from actor order by name G* 1. row *           id: 1 select_type: SIMPLE        table: actor         type: indexposs

40、ible_keys: NULL          key: name      key_len: 18          ref: NULL         rows: 4      

41、60; Extra: Using index1 row in set (0.00 sec) 當(dāng)MySQL不能使用索引進(jìn)行排序時，就會利用自己的排序算法(快速排序算法)在內(nèi)存(sort buffer)中對數(shù)據(jù)進(jìn)行排序，如果內(nèi)存裝載不下，它會將磁盤上的數(shù)據(jù)進(jìn)行分塊，再對各個數(shù)據(jù)塊進(jìn)行排序，然后將各個塊合并成有序的結(jié)果集（實際上就是外排序）。對于filesort，MySQL有兩種排序算法。(1)兩遍掃描算法(Two passes)實現(xiàn)方式是先將須要排序的字段和可以直接定位到相關(guān)行數(shù)據(jù)的指針信息取出，然后在設(shè)定的內(nèi)存（通過參數(shù)sort_buffer_size設(shè)定）中進(jìn)行排

42、序，完成排序之后再次通過行指針信息取出所需的Columns。注：該算法是4.1之前采用的算法，它需要兩次訪問數(shù)據(jù)，尤其是第二次讀取操作會導(dǎo)致大量的隨機I/O操作。另一方面，內(nèi)存開銷較小。(3)    一次掃描算法(single pass)該算法一次性將所需的Columns全部取出，在內(nèi)存中排序后直接將結(jié)果輸出。注：從 MySQL 4.1 版本開始使用該算法。它減少了I/O的次數(shù)，效率較高，但是內(nèi)存開銷也較大。如果我們將并不需要的Columns也取出來，就會極大地浪費排序過程所需要的內(nèi)存。在 MySQL 4.1 之后的版本中，可以通過設(shè)置 max_length_fo

43、r_sort_data 參數(shù)來控制 MySQL 選擇第一種排序算法還是第二種。當(dāng)取出的所有大字段總大小大于 max_length_for_sort_data 的設(shè)置時，MySQL 就會選擇使用第一種排序算法，反之，則會選擇第二種。為了盡可能地提高排序性能，我們自然更希望使用第二種排序算法，所以在 Query 中僅僅取出需要的 Columns 是非常有必要的。當(dāng)對連接操作進(jìn)行排序時，如果ORDER BY僅僅引用第一個表的列，MySQL對該表進(jìn)行filesort操作，然后進(jìn)行連接處理，此時，EXPLAIN輸出“Using filesort”；否則，MySQL必須將查詢的結(jié)果集生成一個臨時表，在連接

44、完成之后進(jìn)行filesort操作，此時，EXPLAIN輸出“Using temporary;Using filesort”。 3.4、索引與加鎖索引對于InnoDB非常重要，因為它可以讓查詢鎖更少的元組。這點十分重要，因為MySQL 5.0中，InnoDB直到事務(wù)提交時才會解鎖。有兩個方面的原因：首先，即使InnoDB行級鎖的開銷非常高效，內(nèi)存開銷也較小，但不管怎么樣，還是存在開銷。其次，對不需要的元組的加鎖，會增加鎖的開銷，降低并發(fā)性。InnoDB僅對需要訪問的元組加鎖，而索引能夠減少InnoDB訪問的元組數(shù)。但是，只有在存儲引擎層過濾掉那些不需要的數(shù)據(jù)才能達(dá)到這種目的。一旦索引不

45、允許InnoDB那樣做（即達(dá)不到過濾的目的），MySQL服務(wù)器只能對InnoDB返回的數(shù)據(jù)進(jìn)行WHERE操作，此時，已經(jīng)無法避免對那些元組加鎖了：InnoDB已經(jīng)鎖住那些元組，服務(wù)器無法解鎖了。來看個例子：create table actor(actor_id int unsigned NOT NULL AUTO_INCREMENT,name      varchar(16) NOT NULL DEFAULT '',password        varchar(16) NOT NULL DEFAULT '',PRIMARY KEY(actor_id