：索引
摘要：
表越大，查詢數據所需的時間越長。如果表中的查詢列有索引，MySQL可以快速到達某個位置來搜索數據文件，而無需查看所有數據。使用索引用于快速查找一列或多列中具有特定值的行。所有MySQL列類型都可以被索引。在相關列上使用索引是加快查詢操作的最佳方法。如果沒有索引，則必須遍歷整個表，直到找到num等于10000的行；如果在num列上創建索引，MySQL不需要任何掃描。您可以直接在索引中查找10000，以了解值行的位置。
什么是索引
索引是對數據庫表中一列或者多列的值進行排序的一種結構，所引用于快速找出在某個列中有一特定值的行。不使用索引，MySQL必須從第一條記錄開始讀完整個表，直到找出相關的行。表越大，查詢數據所花費的時間越多，如果表中查詢的列有一個索引，MySQL能快速到達一個位置去搜索數據文件，而不必查看所有數據。
索引的含義和特點
索引是一個單獨的、存儲在磁盤上的數據庫結構，它們包含著對數據表里所有記錄的引用指針。使用索引用于快速找出在某個或多個列中有一特定值的行，所有MySQL列類型都可以被索引，對相關列使用索引是提高查詢操作速度的最佳途徑。
例如，數據庫里面有20000條記錄，現在要執行這么一個查詢： * FROM table where num = 10000。如果沒有索引，必須遍歷整個表，直到num等于10000的這一行被找到為止；如果在num列上創建索引，MySQL不需要任何掃描，直接在索引中找10000，就可以得知值這一行的位置?？梢?，索引的建立可以提高數據庫的查詢速度。
索引是在存儲引擎中實現的，因此，每種存儲引擎的索引都不一定完全相同，并且每種存儲引擎也不一定支持所有索引類型。所有存儲引擎支持每個表至少16個索引，總索引長度至少為256字節。大多數存儲引擎有更高的額限制，MySQL中索引的存儲類型有兩種：BTREE和HASH，具體和表的存儲引擎相關；和存儲引擎只支持BTREE索引，/HEAP存儲引擎可以支持HASH和BTREE縮影。
索引的優點主要有：
1、通過創建唯一索引，可以保證數據庫表中每一行數據的唯一性
2、可以大大加快數據的查詢速度，這也是創建索引最主要的原因
3、在實現數據的參考完整性方面，可以加速表和表之間的連接
4、在使用分組和排序子句進行數據查詢時，也可以顯著減少查詢中分組和排序的時間
增加索引也有許多不利的方面，比如：
1、創建索引和維護索引要耗費時間，并且隨著數據量的增加所耗費的時間也會增加
2、索引需要占用磁盤空間，除了數據表占數據空間之外，每一個索引還要占一定的物理空間，如果有大量的索引，索引文件可能比數據文件更快達到最大文件尺寸
3、當對表中數據進行增加、刪除和修改的時候，索引也要動態地維護，這樣就降低了數據的維護速度
索引的分類
MySQL的索引可以分為以下幾類：
1、普通索引和唯一索引
（1）普通索引是MySQL中的基本索引類型，允許在定義索引的列中插入重復值和空值
（2）唯一索引，索引列的值必須唯一，但允許有空值，主鍵索引是一種特殊的唯一索引，不允許有空值
2、單列索引和組合索引
（1）單列索引即一個索引只包含單個列，一個表可以有多個單列索引
（2）組合索引指在表的多個字段組合上創建的索引，只有在查詢條件中使用了這些字段的左邊字段時，索引才會被使用
3、全文索引
全文索引類型為，在定義索引的列上支持值的全文查找，允許在這些索引列中插入重復值和空值。全文索引可以在CHAR、或者TEXT類型的列上創建，MySQL中只有存儲引擎支持全文索引
索引的設計原則
索引設計不合理或者缺少索引都會對數據庫和應用程序的性能造成障礙，高效的索引對于獲得良好的性能非常重要，設計索引時，應該考慮一下：
1、索引并非越多越好，一個表中如有大量的索引，不僅占用磁盤空間，而且會影響、、等語句的性能，因為當表中的數據更改的同時，索引也會進行調整和更新
2、避免對經常更新的表設計過多的索引，并且索引中的列盡可能要少，而對經常用于查詢的字段應該創建索引，但要避免添加不必要的字段
3、數據量小的表最好不要使用索引，由于數據較少，查詢花費的時間可能比遍歷索引時間還要短，索引可能不會產生優化效果
4、在條件表達式中經常用到的不同值較多的列上建立索引，在不同值較少的列上不要建立索引，比如性別字段只有男和女，就沒必要建立索引。如果建立索引不但不會提高查詢效率，反而會嚴重降低更新速度
5、當唯一性是某種數據本身的特征時，指定唯一索引。使用唯一索引需能確保定義的列的數據完整性，以提高查詢速度
6、在頻繁排序或分組（即group by或order by操作）的列上建立索引，如果待排序的列有多個，可以在這些列上建立組合索引
創建表的時候創建索引
使用 TABLE創建表的時候sql復制指定行數據，除了可以定義列的數據類型，還可以定義主鍵約束、外鍵約束或者唯一性約束，而不論創建哪種約束，在定義約束的同時相當于在指定列上創建了一個索引。創建表時創建索引的基本語法如下：
CREATE TABLE table_name[col_name data_type] [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [index_name](col_name[length]) [ASC|DESC]
解釋一下：
1、、和為可選參數，分別表示唯一索引、全文索引和空間索引
2、INDEX和KEY為同義詞，二者作用相同，用來指定創建索引
3、為需要創建索引的字段列，該列必須從數據表中該定義的多個列中選擇
4、為指定索引的名稱，為可選參數，如果不指定則MySQL默認為索引值
5、為可選參數，表示索引的長度，只有字符串類型的字段才能指定索引長度
6、ASC或DESC指定升序或者降序的索引值存儲
下面創建一個普通索引，沒有唯一性之類的限制，其作用只是加快對于數據的訪問速度：
CREATE TABLEbook ( bookId INT NOT NULL, bookName VARCHAR(255) NOT NULL, author VARCHAR(255) NOT NULL, info VARCHAR(255) NOT NULL, year_publication YEAR NOT NULL,INDEX(year_publication) )
確認一下索引是否正在使用，可以使用：
EXPLAIN select * from book where yead_publication = 1990
結果為：
解釋下字段的意思：
1、行指定所使用的查詢類型，這里值為，表示簡單的，不使用UNION或者子查詢。其他可能的取值有：、UNION、等
2、table行指定數據庫讀取的數據表的名字，它們按照被讀取的先后順序排列
3、type行指定了本數據表與其他數據表之間的關聯關系，可能的去只有、const、、ref、range、index和All
4、行給出了MySQL在搜索數據記錄時可選用的各個索引
5、key行是MySQL使用的實際索引
6、行給出了索引按字節計算的長度，數值越小，表示越快
7、ref行給出了關聯關系中另外一個數據表里的數據列的名字
8、rows行是MySQL在執行這個查詢時預計會從這個數據表里讀出的數據行的個數
9、extra行提供了與關聯操作有關的信息
看到，和key的值都為sql復制指定行數據，查詢時使用了索引
2、創建唯一索引
唯一索引和普通索引類似，不過唯一索引索引列的值必須唯一，但允許有空值，如果是組合索引，則列值的組合必須唯一。看一下創建唯一索引的方式：
CREATE TABLEuniquetable ( id INT NOT NULL, name CHAR(30) NOT NULL, UNIQUE INDEXUniqIdx(id) )
這就在表的id字段上創建了一個名為的唯一索引
3、創建單列索引
單列索引是在數據表中的某一個字段上創建的索引，一個表中可以創建多個單列索引，前面兩個例子中創建的索引都是單列索引，比如：
CREATE TABLEsingletable( id INT NOT NULL, name CHAR(30) NOT NULL, UNIQUE INDEX SingleIdx(name(20)) )
這就在name字段上建立了一個名為的單列索引，索引長度為20
4、創建組合索引
組合索引是在多個字段上創建一個索引，比如：
create tableuniontable ( id INT NOT NULL, name CHAR(30) NOT NULL, age INT NOT NULL, info VARCHAR(255), INDEXUnionIdx(id, name, age) )
這就為id、name和age三個字段成功創建了一個名為的組合索引
5、創建全文索引
全文索引可以對全文進行搜索，只有存儲引擎支持全文索引，并且只為CHAR、和TEXT列，索引總是對整個列進行，不支持局部索引，比如：
CREATE TABLEfulltexttable ( id INT NOT NULL, name CHAR(30) NOT NULL, age INT NOT NULL, info VARCHAR(255), FULLTEXT INDEXFullTxtIdx(info) )ENGINE=MyISAM
因為默認的存儲引擎為，而全文索引只支持，所以這里創建表的時候要手動指定一下引擎。
看到這么創建，就在info字段上成功建立了一個名為的全文索引，全文索引非常適合大型數據庫，而對于小的數據集，它的用處可能比較小
在已經存在的表上創建索引
在已經存在的表上創建索引，可以使用ALTER TABLE語句或者 INDEX語句，所以，分別講解一下如何使用ALTER TABLE和 INDEX語句在已知的表字段上創建索引。
1、使用ALTER TABLE語句創建索引
ALTER TABLE創建索引的基本語法為：
ALTER TABLE table_name ADD [UNIQUE|FUUTEXT|SPATIAL] [INDEX|KEY] [index_name] (col_name[length],...) [ASC|DESC]
與創建表時創建索引的語法不同的是，這里用了ALTER TABLE和ADD關鍵字，ADD表示向表中添加索引。以book這張表為例，先看一下這張表里面有哪些索引：
SHOW INDEX FROMbook
看下結果：
解釋一下：
1、table表示創建索引的表
2、表示索引不是一個唯一索引，1表示非唯一索引，0表示唯一索引
3、表示索引的名稱
4、表示該字段在索引中的位置，單列索引改值該值為1，組合索引為每個字段在索引中定義的順序
5、表示定義索引的列字段
6、表示索引的長度
7、Null表示該字段是否能為空值
8、表示索引類型
所以，book里面已經有一個索引了，是一個非唯一索引，現在給字段加上索引，SQL語句如下：
ALTER TABLE book ADD INDEX BoNameIdx(bookname(30));
再給字段加上唯一索引，名稱為：
ALTER TABLE book ADD UNIQUE INDEXUniqidIdx(bookId);
再給字段加上單列索引：
ALTER TABLE book ADD INDEX BkauthorIdx(author(50));
意思是查詢的時候，只需要檢索前面50個字符。這里專門提一下，對字符串類型的字段進行索引，如果可能應該指定一個前綴長度，例如，一個CHAR(255)的列，如果在前10個或者前30個字符內，多數值是唯一的，則不需要對整個列進行索引，短索引不僅可以提高查詢速度而且可以節省磁盤空間、減少I/O操作
組合索引和全文索引和創建表時建立索引的方式差不多，就不寫了，此時我們SHOW一下INDEX：
2、使用 INDEX語句創建索引
INDEX語句可以在已經存在的表上添加索引，MySQL中 INDEX被映射到一個ALTER TABLE語句上，基本語法結構為：
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name ON table_name(col_name[length],...)[ASC|DESC]
看到和ALTER INDEX語句的語法基本一樣，下面把book表刪除了再創建，所有字段都沒有索引，用 INDEX語句創建一次索引：
--為bookname字段建立名為BkNameIdx的普通索引 CREATE INDEX BkNameIdx ONbook(bookname); --為bookid字段建立名為UniqidIdx的唯一索引 CREATE INDEX UniqidIdx ONbook(bookid); --為author和info字段建立名為BkAuAndInfoIdx的組合索引 CREATE INDEX BkAuAndInfoIdx ON book(author(20), info(50)); --為year_publication字段建立名為BkyearIdx的普通索引 CREATE INDEX BkyearIdx ON book(year_publication);
此時我們SHOW一下INDEX，可以看到為5個字段建立了4個索引：
刪除索引
最后一項工作就是刪除索引了，可以使用ALTER TABLE和DROP INDEX刪除索引。
1、ALTER TABLE
ALTER TABLE的基本語法為：
ALTER TABLE table_name DROP INDEXindex_name
比如把book的給刪除了：
ALTER TABLE book DROP INDEX UniqidIdx;
這樣就刪除了book表中的這個索引，可以SHOW INDEX from book查看一下，這里就不貼圖了
2、DROP INDEX
DROP INDEX的基本語法為：
DROP INDEX index_name ONtable_name
比如我把這個組合索引給刪了：
DROP INDEX BkAuAndInfoIdx ONbook
這樣就把book表里面的這個組合索引給刪除了。
注意一個細節，刪除表中的列時，如果要刪除的列為整個索引的組成部分，則該列也會從索引中刪除；如果組成索引的所有列都被刪除，則整個索引將被刪除
標簽：#全文索引#mysql#mysql 語句#table#唯一索引#mysql創建索引#存儲引擎#索引#mysql創建數據庫#sql優化#數據表 | 瀏覽：4 | 發布日期：2022-11-13
