mysql 索引及索引建立原則

來源:互聯網
上載者:User
是什麼

  索引用於快速的查詢某些特殊列的某些行。如果沒有索引, MySQL 必須從第一行開始,然後通過搜尋整個表來查詢有關的行。表越大,查詢的成本越大。如果表有了索引的話,那麼 MySQL 可以很快的確定資料的位置,而不用查詢整個表格。這比順序的讀取每一行要快的多。索引就像我們查字典時的目錄一樣,我們通過查詢字典的目錄,可以定位到某一行資料。

  大多數的 MySQL 的索引(主鍵索引,唯一索引,普通索引,全文索引)都是 B-trees  結構。例外的情況有:在空間資料類型使用 R-trees 結構。儲存引擎為 MEMORY 的資料庫,也可以支援雜湊索引。InnoDB 儲存引擎的全文索引使用反向列表結構。

使用情境

  MySQL 會使用到索引的情境如下:

  1.根據一個條件快速的匹配到對應的行。

  2.縮小查詢影響行數。如果一個查詢欄位有多個索引,MySQL 通常選擇使用影響行數最小的索引(選擇性最高的索引)。索引的選擇性的計算 select count(distinct name) / count(*) from table; 

  3.對於複合式索引,索引左邊的列可以用索引首碼最佳化器來查詢資料。例如,你有個三列的複合式索引(col1,col2,col3) ,那麼你可以使用索引查詢(col1),(col1,col2),(col1,col2,col3)這三種組合的資料。有關於複合式索引,詳細請看另外一篇部落格 MySQL 複合式索引

  4.當和其他表進行連表查詢的時候,如果進行判斷的列的資料類型和大小相同,那麼再這兩個列上使用索引,可以讓判斷更加效率。例如:在如下查詢中,給tb1.name和tb2.name添加索引會提升查詢效率。 SELECT * FROM tb1, tb2 WHERE tb1. name = tb2. name 

  在這裡,VARCHAR 與 CHAR 被認為是相同的類型。需要注意的是,如果要讓索引生效,不僅需要類型一致,大小也必須一致。例如,VARCHAR(10) 和 CHAR(10) 大小相同可以使用索引,但 VARCHAR(10) 與 CHAR(15)就無法使用索引

  5.尋找索引列的 MIN() 或 MAX()值。

  6.通過索引列進行排序或分組,或者複合式索引的左首碼進行排序或分組。

  7.查詢索引列的內容。(如果只需要返回索引列的值,那麼不需要查詢資料行,直接從記憶體中讀取檢索值。這種情況稱為覆蓋索引)例如: SELECT key_part FROM table WHERE key_part=1 

  對於小型表或報表查詢處理大多數或所有行的大型表的查詢,索引不太重要。當查詢需要訪問大多數行時,順序讀取比通過索引更快。順序讀取可以最大限度地減少磁碟搜尋,即使查詢不需要所有行也是如此。只有資料較大,並且需要訪問其中一部分資料的時候,索引才會顯得比較重要。

 

怎麼用查看索引

   SHOW INDEX FROM table 

  運行後,顯示結果如下:

  

  其中,各個欄位的含義:

  table: 表的名稱

  Non_unique: 索引是否可以重複。不可以重複則為0;可以重複則為1。

  Key_name: 索引名稱。建立的時候,可以選擇輸入,不輸入 MySQL 自動產生。如果索引是主鍵,則名稱始終為 PRIMARY。

  Seq_in_index: 索引中的列序號,從1開始。

  Column_name: 索引涉及到的列的名稱。

  Collation:列如何在索引中排序。這可以具有值 A(ascending 升序),D ( descending 降序)或NULL(未排序)。

  Cardinality: 索引中唯一值的數量(不是即時更新的準確資料)。

  Sub_part: 索引前置長度。如果使用欄位的部分字元作為索引,那麼顯示索引字元數量。如果使用整個欄位都被索引,那麼為 NULL。

  Packed: key的打包方式,NULL 表示不打包。

  Null: 索引列包含 NULL 或者 ‘’ 的時候,會是 YES。

  Index_type: 索引類型。(BTREE, FULLTEXT,HASH, RTREE)之一。

  Comment: 未在當前列中描述的索引資訊,例如 disabled 索引是否已禁用。

  Index_comment: 在建立索引時提供的注釋。

  Visible: 索引是否對最佳化程式可見(有的版本會出現該資訊)。

添加索引

   CREATE INDEX index_name ON table_name (key_part,...) 

   ALTER TABLE t1 ADD INDEX index_name (key_part) 

  通常,在建立表時建立索引。對於InnoDB儲存引擎的表。其中主鍵確定資料的物理布局,可以向現有表中添加索引。key_part 表示組成索引的列的列名,如果是多個列名,那麼將產生一個複合式索引。在 key_part 參數後可以添加 ASC 或者 DESC 去指定索引按照正序排列還是倒序排列。

  關於建立索引需要注意的是:

複合式索引

  複合式索引是一個由多個列組成的索引。舉例說明:例如在表 address 中有三個欄位,分別為 Provincial 省 city 市 county 縣 在建表的時候,用這三個欄位組成一個複合式索引。代碼如下:

1 CREATE TABLE address (2     provincial VARCHAR (10),3     city VARCHAR (10),4     county VARCHAR (10),5     INDEX (provincial, city, county)6 )
CREATE TABLE address

 

  這裡的索引是這樣建立的:首先按照省排序,然後,再根據同一個省的內容,按照市進行排序,最後,按照縣去排序。即,首先按照第一列進行索引排序,如果第一列內容一致,那麼按照第二列進行排序,以此類推。

 

首碼索引

  如果將字串的列作為索引,可以建立首碼索引。一般情況下某個首碼的選擇性也是足夠高的,足以滿足查詢效能。對於BLOB,TEXT,或者很長的VARCHAR類型的列,必須使用首碼索引。首碼索引以位元組為單位。首碼索引支援的長度取決於儲存引擎。例如,對於InnoDB 使用 REDUNDANT 或 COMPACT 行格式的表, 首碼最長可達767位元組。對於InnoDB使用DYNAMIC 或 COMPRESSED 行格式的表, 前置長度限制為3072位元組 。對於MyISAM表,前置長度限制為1000個位元組。

  如果指定的索引首碼超過最大列資料類型大小,對於非唯一索引,如果啟用了嚴格的SQL模式,建立會發生錯誤。如果未啟用嚴格SQL模式,索引長度減少到最大列資料類型大小,併產生警告。

  建立首碼索引的長度,取決於索引的選擇性。詳見另外一篇部落格:索引選擇性

  建立首碼索引文法如下(這裡的10 表示截取前10個字元):

   CREATE INDEX key_part_name ON table_name (key_part(10)); 

   ALTER TABLE table_name ADD INDEX index_name (key_part(10)) 

  首碼索引可以兼顧索引大小和查詢速度。可以利用相對更小的空間,用更快的速度,查出資料。但是它也有缺點:首碼索引無法用於 ORDER BY 和 GROUP BY 操作,也不能用於索引覆蓋。

方法索引

  這裡的索引類型英文名稱為:Functional Key Parts 這裡作者並不清楚官方的翻譯名稱為啥,只是根據索引的方式進行翻譯。如果不對,歡迎大神指正。

  這個索引類似於兩個首碼索引的拼接。直接舉個例子就明白了:在 t1 表中有兩個列,col1 和 col2 我要建立一個包含完整的 col1 列和 col2 列的前10個位元組組成一個複合式索引。代碼如下:

CREATE TABLE t1 (  col1 VARCHAR(10),  col2 VARCHAR(20),  INDEX (col1, col2(10)));
Functional Key Parts

 

  在 MySQL 8.0.13版本及更高版本中,MySQL 支援運算式進行索引。這裡,需要將運算運算式寫在括弧內進行縮印的聲明。例如:

 1 -- 方法索引 2 CREATE TABLE t1 ( 3     col1 INT, 4     col2 INT, 5     INDEX func_index ((ABS(col1))) 6 ); 7  8 CREATE INDEX idx1 ON t1 ((col1 + col2)); 9 10 CREATE INDEX idx2 ON t1 (11     (col1 + col2),12     (col1 - col2),13     col114 );15 16 ALTER TABLE t1 ADD INDEX ((col1 * 40) DESC);
Functional Key Parts  唯一索引

  通過 UNIQUE 建立的索引。索引列的內容非null值的時候必須是唯一的,null值可以不唯一。如果添加重複值,則會發生錯誤。如果在建立唯一索引的時候指定首碼值,那麼首碼必須是唯一的。建立文法: CREATE UNIQUE INDEX unique_index_name ON table_name (key_part) 

  全文索引

  全文索引,顧名思義,支援全文檢索索引的索引。僅支援 Innodb 和 MyISAM 兩種儲存引擎。並且只能包括 CHAR, VARCHAR 和 TEXT 列,索引始終發生在整個列上,不支援首碼索引。(即使寫了也沒用)可以對欄位進行全文檢索索引。對於資料量比較大的資料集,先將資料載入到沒有資料的表中,然後再添加索引,效率要比把資料直接向有索引的表中添加高。

  空間索引

  空間索引是為空白間搜尋提供一種合適的資料結構,以提高搜尋速度。對於空間索引,作者研究並不多,這裡大概介紹下空間索引的用處。等以後研究深刻後,再補上這部分內容。首先,空間索引是幹什麼用的?舉例:當我們需要按照某個點,查詢附近的50米內都有哪些客戶存在。對於這個需求,可能的解法如下:

  1.我們可以根據使用者的經緯度,去計算每個人跟我們的當前點的距離,然後跟50米去作對比。這在客戶資料量少的時候,還可以這麼做,資料量大的時候,將會特別的浪費效能。

  2.先畫一個方框,把50米範圍的資料的經緯度畫出來,通過經緯度的值進行篩選後,得到一個正方形的地區,然後再進行計算。這時候,會少很多計算,但依然不是最優方案。

  3.使用空間索引。將空間按照一定規則劃分為不同的地區,在檢索的時候,根據設計的地區,取出相應的資料。空間索引結構圖如下(圖片來自知乎):

  

 

刪除索引

  刪除索引沒啥好說的,語句如下:

1 DROP INDEX index_name ON talbe_name2 ALTER TABLE table_name DROP INDEX index_name
DROP INDEX

 

  如果索引所在的列刪除,那麼該列對應的索引也會自動刪除。

 

索引最佳化  主鍵最佳化

  表的主鍵是唯一且非空的索引,在使用InnoDB儲存引擎的時候,表資料直接掛載在主鍵的葉子節點上,是查詢速度最快的索引。

  如果表的內容很多,並且很重要。但是沒有明顯的列和列的集合作為主鍵的話,可以單獨建立一個自動成長的值作為主鍵。當使用外鏈查詢的時候,這個id可以作為指向內容的指標。

  外鍵最佳化

  如果你的表有很多列,你可以將查詢頻率比較低的列拆分到其他表格,並通過複製id的方式讓它們與主表關聯。這樣,每個小表都會有個主鍵來快速尋找其他資料。在查詢的時候,就可以僅查詢自己需要的列集。這時,查詢會執行較少的 I/O 並且佔用較少的記憶體。整體原則是:為了提高效能,儘可能少的從磁碟讀取資料。這就是拆表的原則。

  

 

 

相關文章

Alibaba Cloud 10 Year Anniversary

With You, We are Shaping a Digital World, 2009-2019

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。