Mysql數(shù)據(jù)庫（四）——mysql索引相關知識

• 一、索引的概念
• 二、索引的優(yōu)缺點
• • 1、優(yōu)點
  • 2、缺點
• 三、創(chuàng)建索引的原則
• 四、索引的分類和創(chuàng)建方法
• • 1、普通索引
  • • ①、直接創(chuàng)建索引
    • ②、修改表方式創(chuàng)建
    • ③、創(chuàng)建表的時候指定索引
  • 2、唯一索引
  • • ①、直接創(chuàng)建
    • ②、修改表方式創(chuàng)建索引
    • ③、創(chuàng)建表的時候指定索引
  • 3、主鍵索引
  • • ①、創(chuàng)建表的時候指定索引
    • ②、修改表方式創(chuàng)建索引
  • 4、組合索引
  • • ①、創(chuàng)建表的時候指定組合索引
    • ②、修改表方式創(chuàng)建組合索引
  • 5、全文索引
  • • ①、直接創(chuàng)建索引
    • ②、修改表方式指定全文索引
    • ③、創(chuàng)建表的時候指定全文索引
• 五、查看索引
• 六、刪除索引
• • 1、直接刪除索引
  • 2、修改表方式刪除索引
  • 3、刪除主鍵索引
• 總結
• • 索引的作用：
  • 索引的副作用：
  • 創(chuàng)建索引的原則——根據(jù)情況，創(chuàng)建合適的索引
  • 索引分類和操作
  • 面試題: mysql優(yōu)化:

一、索引的概念

• 索引是一個排序的列表，在這個列表中存儲著索引的值和包含這個值的數(shù)據(jù)所在行的物理地址（類似于C語言的鏈表通過指針指向數(shù)據(jù)記錄的內(nèi)存地址）。
• 使用索引后可以不用掃描全表來定位某行的數(shù)據(jù)，而是先通過索引表找到該行數(shù)據(jù)對應的物理地址然后訪問相應的數(shù)據(jù)，因此能加快數(shù)據(jù)庫的查詢速度。
• 索引就好比是一本書的目錄，可以根據(jù)目錄中的頁碼快速找到所需的內(nèi)容。
• 索引是表中一列或者若干列值排序的方法。
• 建立索引的目的是加快對表中記錄的查找或排序。

二、索引的優(yōu)缺點

1、優(yōu)點

• 設置了合適的索引之后，數(shù)據(jù)庫利用各種快速定位技術，能夠大大加快查詢速度，這是創(chuàng)建所有的最主要的原因。
• 當表很大或查詢涉及到多個表時，使用索引可以成千上萬倍地提高查詢速度。
• 可以降低數(shù)據(jù)庫的IO成本，并且索引還可以降低數(shù)據(jù)庫的排序成本。
• 通過創(chuàng)建唯一性索引，可以保證數(shù)據(jù)表中每一行數(shù)據(jù)的唯一性。
• 可以加快表與表之間的連接。
• 在使用分組和排序時，可大大減少分組和排序的時間。

2、缺點

• 索引需要占用額外的磁盤空間。
• 對于 MyISAM 引擎而言，索引文件和數(shù)據(jù)文件是分離的，索引文件用于保存數(shù)據(jù)記錄的地址。
• 而 InnoDB 引擎的表數(shù)據(jù)文件本身就是索引文件。
• 在插入和修改數(shù)據(jù)時要花費更多的時間，因為索引也要隨之變動

三、創(chuàng)建索引的原則

• 索引隨可以提升數(shù)據(jù)庫查詢的速度，但并不是任何情況下都適合創(chuàng)建索引。因為索引本身會消耗系統(tǒng)資源，在有索引的情況下，數(shù)據(jù)庫會先進行索引查詢，然后定位到具體的數(shù)據(jù)行，如果索引使用不當，反而會增加數(shù)據(jù)庫的負擔。
• 表的主鍵、外鍵必須有索引。因為主鍵具有唯一性，外鍵關聯(lián)的是子表的主鍵，查詢時可以快速定位。
• 記錄數(shù)超過300行的表應該有索引。如果沒有索引，需要把表遍歷一遍，會嚴重影響數(shù)據(jù)庫的性能。
• 經(jīng)常與其他表進行連接的表，在連接字段上應該建立索引。
• 唯一性太差的字段不適合建立索引。
• 更新太頻繁地字段不適合創(chuàng)建索引。
• 經(jīng)常出現(xiàn)在 where 子句中的字段，特別是大表的字段，應該建立索引。
• 索引應該建在選擇性高的字段上。
• 索引應該建在小字段上，對于大的文本字段甚至超長字段，不要建索引。

四、索引的分類和創(chuàng)建方法

• 索引可分為5類
  • 普通索引
  • 唯一索引
  • 主鍵索引
  • 組合索引
  • 全文索引
• 創(chuàng)建方法
  • 直接創(chuàng)建索引（主鍵索引無法使用此方式）
  • 修改表結構方式添加索引
  • 創(chuàng)建表結構時創(chuàng)建索引

1、普通索引

• 最基本的索引類型，沒有唯一性之類的限制。

①、直接創(chuàng)建索引

create index 索引名 on 表名 (列名[(length)]);(列名[(length)]：length 為可選項，如果忽略 length 的值，則使用整個列的值作為索引。如果指定使用列的前 length 個字符來創(chuàng)建索引，這樣有利于減小索引文件的大小。 索引名建議以 “index” 結尾。

②、修改表方式創(chuàng)建

alter table 表名 add index 索引名 (列名);

③、創(chuàng)建表的時候指定索引

create table 表名 (字段1 數(shù)據(jù)類型，字段2 數(shù)據(jù)類型,index 索引名 (列名));

2、唯一索引

• 與普通索引類似，但區(qū)別是唯一索引列的每個值都唯一。
• 唯一索引允許有空值（注意和主鍵不同）。如果是用組合索引創(chuàng)建，則列值的組合必須唯一。
• 添加唯一鍵將自動創(chuàng)建唯一索引。
• [ ]

①、直接創(chuàng)建

create unique index 索引名 on 表名 (列名);

②、修改表方式創(chuàng)建索引

alter table 表名 add unique 索引名 (列名);

③、創(chuàng)建表的時候指定索引

create table 表名 (字段1 數(shù)據(jù)類型，字段2 數(shù)據(jù)類型,unique 索引名 (列名));

3、主鍵索引

• 是一種特殊的唯一索引，必須指定為“PRIMARY KEY”。
• 一個表只能有一個主鍵，不允許有空值。 添加主鍵將自動創(chuàng)建主鍵索引。

①、創(chuàng)建表的時候指定索引

create table 表名 (字段1 數(shù)據(jù)類型，字段2 數(shù)據(jù)類型,primary key (列名));

②、修改表方式創(chuàng)建索引

alter table 表名 add primary key (列名);

4、組合索引

• 可以是單列上創(chuàng)建的索引，也可以是在多列上創(chuàng)建的索引。
• 需要滿足最左原則，因為select語句的 where 條件是依次從左往右執(zhí)行的，所以在使用 select 語句查詢時 where 條件使用的字段順序必須和組合索引中的排序一致，否則索引將不會生效。

①、創(chuàng)建表的時候指定組合索引

create table 表名 (字段1 數(shù)據(jù)類型，字段2 數(shù)據(jù)類型,index 索引名 (列名1，列名2)); select * from 表名 where 列名1='...' and(or) 列名2='...'

②、修改表方式創(chuàng)建組合索引

create table 表名 (字段1 數(shù)據(jù)類型，字段2 數(shù)據(jù)類型); alter table 表名 add primary key (列名);

5、全文索引

• 適合在進行模糊查詢的時候使用，可用于在一篇文章中檢索文本信息。
• 在 MySQL5.6 版本以前 fulltext 索引僅可用于 MyISAM 引擎，在 5.6 版本之后 innodb 引擎也支持 fulltext 索引。
• 全文索引可以在 char、varchar 或者 text 類型的列上創(chuàng)建。每個表只允許有一個全文索引。

①、直接創(chuàng)建索引

create fulltext index 索引名 on 表名 (列名);

②、修改表方式指定全文索引

alter table 表名 add fulltext 索引名 (列名);

③、創(chuàng)建表的時候指定全文索引

create table 表名 (字段1 數(shù)據(jù)類型，字段2 數(shù)據(jù)類型,fulltext 索引名 (列名));

五、查看索引

show index from 表名; show index from 表名\G； 豎向顯示表索引信息 show keys from 表名; show keys from 表名\G;

Table表的名稱

Non_unique	如果索引不能包括重復詞，則為 0；如果可以，則為 1。
Key_name	索引的名稱。
Seq_in_index	索引中的列序號，從 1 開始。
Column_name	列名稱。
Collation	列以什么方式存儲在索引中。在 MySQL 中，有值‘A’（升序）或 NULL（無分類）。
Cardinality	索引中唯一值數(shù)目的估計值。
Sub_part	如果列只是被部分地編入索引，則為被編入索引的字符的數(shù)目。如果整列被編入索引，則為 NULL。
Packed	指示關鍵字如何被壓縮。如果沒有被壓縮，則為 NULL。
Null	如果列含有 NULL，則含有 YES。如果沒有，則該列含有 NO。
Index_type	用過的索引方法（BTREE, FULLTEXT, HASH, RTREE）。
Comment	備注。

六、刪除索引

1、直接刪除索引

drop index 索引名 on 表名;

2、修改表方式刪除索引

alter table 表名 drop index 索引名;

3、刪除主鍵索引

alter table 表名 drop primary key;

總結

索引的作用：

• 快速定位、加快查詢速度
• 降低IO成本，降低排序成本
• 通過建立合適的索引來保證業(yè)務的快速、穩(wěn)定（例如唯一索引的建立，就能保證數(shù)據(jù)行的唯一性）
• 提升表與表之間的相互連接
  • 例如：我需要在300個表中查詢一行信息，如果沒有索引，我就得一行一行找，不僅耗時間，而且每一次查詢，都會給數(shù)據(jù)庫帶來一份壓力。
  • 但如果有了索引，我們就能直接根據(jù)條件進行查詢，不需要一個表一個表的查找，會節(jié)約辦事效率，節(jié)約時間成本
  • 而且索引會根據(jù)索引類型規(guī)則進行自動排序，就省去了我們手動排序的時間

索引的副作用：

• 索引也是表，所以會占用一定的磁盤空間
• 因為索引和表示相連的，當我們修改表的內(nèi)容時，索引也會隨之改變，索引會消耗一定的時間

創(chuàng)建索引的原則——根據(jù)情況，創(chuàng)建合適的索引

• 主鍵、唯一鍵會自動創(chuàng)建索引
• 外鍵也必須有索引，因為外鍵是子表的主鍵關連建，能更快速的定位子表內(nèi)容
• 使用唯一性較好的、更新不是很頻繁的、經(jīng)常與其他表進行連接的字段作為索引
• 經(jīng)常出現(xiàn)在 where 語句中的字段，需要做索引，會更好的方便我們處理日常事務，節(jié)約時間成本
• 選擇性較高的字段需要做索引
• 超長字段，建議做全文索引，或者不建立索引

索引分類和操作

• 索引可分為5類：
  • 普通索引
  • 唯一索引
  • 主鍵索引
  • 組合索引
  • 全文索引
• 創(chuàng)建方法：
  • 直接創(chuàng)建索引（主鍵索引無法使用此方式）
  • 修改表結構方式添加索引
  • 創(chuàng)建表結構時創(chuàng)建索引
• 刪除索引：
  • 直接刪除索引（drop index 索引名 on 表名;）
  • 修改表方式刪除索引（alter table 表名 drop index 索引名;）
  • 刪除主鍵索引（alter table 表名 drop primary key;）
• 查看索引：
  • show index from 表名\G;
  • show keys from 表名\G;

面試題: mysql優(yōu)化:

• 1、提升服務性能(提高用戶/使用者的體驗度，安全)
• 2、減少資源消耗
• 3、對合適的字段創(chuàng)建索引，來加速查詢

總結

以上是生活随笔為你收集整理的Mysql数据库（四）——mysql索引相关知识的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。