1004
技術社區[雲棲]
mysql索引的詳細介紹
/*
所有MySQL列類型可以被索引。根據存儲引擎定義每個表的最大索引數和最大索引長度。
所有存儲引擎支持每個表至少16個索引，總索引長度至少為256字節。大多數存儲引擎有更高的限製。
索引的存儲類型目前隻有兩種（btree和hash），具體和存儲引擎模式相關：
MyISAM btree
InnoDB btree
MEMORY/Heap hash，btree
默認情況MEMORY/Heap存儲引擎使用hash索引
MySQL的btree索引和hash索引的區別
hash 索引結構的特殊性，其檢索效率非常高，索引的檢索可以一次定位，不像btree(B-Tree)索引需要從根節點到枝節點，最後才能訪問到頁節點這樣多次的IO訪問，所以 hash 索引的查詢效率要遠高於 btree(B-Tree) 索引。
雖然 hash 索引效率高，但是 hash 索引本身由於其特殊性也帶來了很多限製和弊端，主要有以下這些。
（1）hash 索引僅僅能滿足=，<=>，IN，IS NULL或者IS NOT NULL查詢，不能使用範圍查詢。
由於 hash 索引比較的是進行 hash 運算之後的 hash 值，所以它隻能用於等值的過濾，不能用於基於範圍的過濾，因為經過相應的 hash 算法處理之後的 hash 值的大小關係，並不能保證和hash運算前完全一樣。
（2）hash 索引無法被用來避免數據的排序操作。
由於 hash 索引中存放的是經過 hash 計算之後的 hash 值，而且hash值的大小關係並不一定和 hash 運算前的鍵值完全一樣，所以數據庫無法利用索引的數據來避免任何排序運算；
（3）hash 索引不能利用部分索引鍵查詢。
對於組合索引，hash 索引在計算 hash 值的時候是組合索引鍵合並後再一起計算 hash 值，而不是單獨計算 hash 值，所以通過組合索引的前麵一個或幾個索引鍵進行查詢的時候，hash 索引也無法被利用。
（4）hash 索引在任何時候都不能避免表掃描。
前麵已經知道，hash 索引是將索引鍵通過 hash 運算之後，將 hash運算結果的 hash 值和所對應的行指針信息存放於一個 hash 表中，由於不同索引鍵存在相同 hash 值，所以即使取滿足某個 hash 鍵值的數據的記錄條數，也無法從 hash 索引中直接完成查詢，還是要通過訪問表中的實際數據進行相應的比較，並得到相應的結果。
（5）hash 索引遇到大量hash值相等的情況後性能並不一定就會比B-Tree索引高。
對於選擇性比較低的索引鍵，如果創建 hash 索引，那麼將會存在大量記錄指針信息存於同一個 hash 值相關聯。這樣要定位某一條記錄時就會非常麻煩，會浪費多次表數據的訪問，而造成整體性能低下
B-Tree 索引是 MySQL 數據庫中使用最為頻繁的索引類型，除了 Archive 存儲引擎之外的其他所有的存儲引擎都支持 B-Tree 索引。不僅僅在 MySQL 中是如此，實際上在其他的很多數據庫管理係統中B-Tree 索引也同樣是作為最主要的索引類型，這主要是因為 B-Tree 索引的存儲結構在數據庫的數據檢 索中有非常優異的表現。
一般來說， MySQL 中的 B-Tree 索引的物理文件大多都是以 Balance Tree 的結構來存儲的，也就是所有實際需要的數據都存放於 Tree 的 Leaf Node ，而且到任何一個 Leaf Node 的最短路徑的長度都是完全相同的，所以我們大家都稱之為 B-Tree 索引當然，可能各種數據庫（或 MySQL 的各種存儲引擎）在存放自己的 B-Tree 索引的時候會對存儲結構稍作改造。
如 Innodb 存儲引擎的 B-Tree 索引實際使用的存儲結構實際上是 B+Tree ，也就是在 B-Tree 數據結構的基礎上做了很小的改造，在每一個Leaf Node 上麵出了存放索引鍵的相關信息之外，還存儲了指向與該 Leaf Node 相鄰的後一個 LeafNode 的指針信息，這主要是為了加快檢索多個相鄰 Leaf Node 的效率考慮。
在 Innodb 存儲引擎中，存在兩種不同形式的索引，一種是 Cluster 形式的主鍵索引（ Primary Key ），另外一種則是和其他存儲引擎（如 MyISAM 存儲引擎）存放形式基本相同的普通 B-Tree 索引，這種索引在 Innodb 存儲引擎中被稱為 Secondary Index 。
在 Innodb 中如果通過主鍵來訪問數據效率是非常高的，而如果是通過 Secondary Index 來訪問數據的話， Innodb 首先通過 Secondary Index 的相關信息，通過相應的索引鍵檢索到 Leaf Node之後，需要再通過 Leaf Node 中存放的主鍵值再通過主鍵索引來獲取相應的數據行。
MyISAM 存儲引擎的主鍵索引和非主鍵索引差別很小，隻不過是主鍵索引的索引鍵是一個唯一且非空 的鍵而已。而且 MyISAM 存儲引擎的索引和 Innodb 的 Secondary Index 的存儲結構也基本相同，主要的區別隻是 MyISAM 存儲引擎在 Leaf Nodes 上麵出了存放索引鍵信息之外，
再存放能直接定位到 MyISAM 數據文件中相應的數據行的信息（如 Row Number ），但並不會存放主鍵的鍵值信息。
我們這裏建表
*/
CREATE TABLE mytable(
id INT,
username VARCHAR(16),
city VARCHAR(16),
age INT
);
/*
索引分單列索引和組合索引。單列索引，即一個索引隻包含單個列，一個表可以有多個單列索引，但這不是組合索引。組合索引，即一個索包含多個列。
MySQL索引類型包括：
（1）普通索引，這是最基本的索引，它沒有任何限製。它有以下幾種創建方式：
*/
-- 創建索引
CREATE INDEX indexName ON mytable(username(10)); -- 單列索引
-- CREATE INDEX indexName ON mytable(username(10),city(10)); -- 組合索引
-- indexName為索引名，mytable表名，username和city為列名，10為前綴長度，即索引在該列從最左字符開始存儲的信息長度，單位字節
-- 如果是CHAR，VARCHAR類型，前綴長度可以小於字段實際長度；如果是BLOB和TEXT類型，必須指定 前綴長度，下同。
-- 修改表結構來創建索引
ALTER TABLE mytable ADD INDEX indexName (username(10));
-- ALTER TABLE mytable ADD INDEX indexName (username(10),city(10));
-- 此處 indexName 索引名可不寫，係統自動賦名 username ，username_2 ，username_3，...
-- 創建表的時候直接指定
CREATE TABLE mytable(
id INT,
username VARCHAR(16),
city VARCHAR(16),
age INT,
INDEX indexName (username(10))-- INDEX indexName (username(10),city(10))
);
-- 此處 indexName 索引名同樣可以省略
/*
（2）唯一索引，它與前麵的普通索引類似，不同的就是：索引列的值必須唯一，但允許有空值。如果是組合索引，則列值的組合必須唯一。它有以下幾種創建方式（僅僅在創建普通索引時關鍵字 INDEX 前加 UNIQUE）：
*/
-- 創建索引
CREATE UNIQUE INDEX indexName ON mytable(username(10));
-- 修改表結構來創建索引
ALTER TABLE mytable ADD UNIQUE INDEX indexName (username(10));-- 也可簡寫成 ALTER TABLE mytable ADD UNIQUE indexName (username(10));
-- 創建表的時候直接指定
CREATE TABLE mytable(
id INT,
username VARCHAR(16),
city VARCHAR(16),
age INT,
UNIQUE INDEX indexName (username(10)) -- 也可簡寫成 UNIQUE indexName (username(10))
);
/*
（3）主鍵索引，它是一種特殊的唯一索引，不允許有空值。在建表的時候同時創建的主鍵即為主鍵索引
主鍵索引無需命名，一個表隻能有一個主鍵。主鍵索引同時可是唯一索引或者全文索引，但唯一索引或全文索引不能共存在同一索引
*/
-- 修改表結構來創建索引
ALTER TABLE mytable ADD PRIMARY KEY (id);
-- 創建表的時候直接指定
CREATE TABLE mytable(
id INT,
username VARCHAR(16),
city VARCHAR(16),
age INT,
PRIMARY KEY(id)
);
/*
（4）全文索引，InnoDB存儲引擎不支持全文索引
*/
-- 創建索引
CREATE FULLTEXT INDEX indexName ON mytable(username(10));
-- 修改表結構來創建索引
ALTER TABLE mytable ADD FULLTEXT INDEX indexName (username(10));-- 也可簡寫成 ALTER TABLE mytable ADD FULLTEXT indexName (username(10));
-- 創建表的時候直接指定
CREATE TABLE mytable(
id INT,
username VARCHAR(16),
city VARCHAR(16),
age INT,
FULLTEXT INDEX indexName (username(10)) -- 也可簡寫成 FULLTEXT indexName (username(10))
)ENGINE=MYISAM;
-- 建表時創建全文索引，要設置該表的存儲引擎為MYISAM,新版mysql默認InnoDB存儲引擎不支持全文索引
-- 刪除索引
DROP INDEX indexName ON mytable;
/*
Mysql自動使用索引規則：
btree索引
當使用 <，<=，=，>=，>，BETWEEN，IN，!=或者<>，以及某些時候的LIKE才會使用btree索引，因為在以通配符%和_開頭作查詢時，MySQL不會使用索引。btree索引能用於加速ORDER BY操作
hash索引
當使用=，<=>，IN，IS NULL或者IS NOT NULL操作符時才會使用hash索引，並且不能用於加速ORDER BY操作，並且條件值必須是索引列查找某行該列的整個值
對where後邊條件為字符串的一定要加引號，字符串如果為數字mysql會自動轉為字符串，但是不使用索引。
mysql目前不支持函數索引，隻能對列的前一部分（指定長度前綴）進行索引，對於char和varchar列，使用前綴索引（該列從起始字符到指定長度字符位置建立索引）將大大節省空間。
mysql列建議列是非null的。說是如果是允許null的列，對索引會有影響（索引不會包括有NULL值）。因為它們使得索引、索引的統計信息以及比較運算更加複雜。應該用0、一個特殊的值或者一個空串代替空值。
盡量不使用NOT IN和<>操作
username,city,age建立這三列的組合索引，其實是相當於分別建立了下麵三組組合索引：
username,city,age
username,city
username
使用組合索引，比如where等條件，列名必須從組合索引最左列至右連續的列名做條件才可以，組合索引類似單一索引前綴
*/
-- 調用組合索引
SELECT * FROM mytable WHERE username = 'admin' AND city = 'DaLian';
-- 多表聯查中的條件也可運用索引
-- 全文索引的使用
SELECT * , MATCH (username,city) AGAINST ('name100 name200 city500 thisisname') FROM mytable WHERE MATCH (username,city) AGAINST ('name100 name200 city500 thisisname');
-- 返回 mytable 表在 MATCH 的參數中的任意列中包含 AGAINST 字符串參數中被 "空格" "," 和 "." 分割的任意單詞的行(斷字的字符： "空格" "," 和 "." 但是不用這些符號斷字的語言，如中文，就得自行手動斷字。)
-- 表列中的單詞也是以空格分割來區分
-- 這裏指定 MATCH...AGAINST 兩次。這不會引起附加的開銷，因為 MySQL 優化器會注意到兩次是同樣的 MATCH...AGAINST 調用，並隻調用一次全文搜索代碼。
/*
函數 MATCH() 對照一個列名集(一個 FULLTEXT 索引中的一個或多個列的列名)。搜索字符串做為 AGAINST() 的參數給定。搜索以忽略字母大小寫的方式執行，預設搜尋是不分大小寫，若要分大小寫，列的字符集設置要從utf8改成utf8_bin。
雖然同一個表格可以有不同字符集的字段，但是同一個FULLTEXT 索引裏的字段必須是同一個字符集與collation。
任何在 stopword 列表上出現的，或太短的(3 個字符或更少的)的單詞將被忽略。(可以覆寫內建的 stopword 列表。可以修改最少四個字符的設定。 )
搜索的詞有一個權重性，如果搜索的詞在表中包含它的行太多，則這個搜索詞將有較低的權重(可能甚至有一個零權重)，否則，它將得到一個較高的權重。然後，權重將被結合用於計算行的相似性。
如果搜索詞在表中超過一半的行中出現。則它被有效地處理為一個 stopword (即，一個零語義值的詞)，不搜索該詞。
MATCH...AGAINST可以跟所有MySQL語法搭配使用，像是JOIN或是加上其他過濾條件。
對於表中的每行記錄，MATCH...AGAINST 返回一個相關性值。即，返回的每行與搜索條件之間的相似性尺度。
當 MATCH() 被使用在一個 WHERE 子句中時，返回的結果被自動地以相關性從高到底的次序排序。如果即沒有 WHERE 也沒有 ORDER BY 子句，返回行是不排序的。
相關性值是正值的浮點數字。零相關性意味著不相似。
相關性的計算是基於：查找單詞在表行中的數目、在行中唯一詞的數目、在集中詞的全部數目和包含一個特殊詞的行的數目。
到 4.0.1 時，MySQL 也可以使用 IN BOOLEAN MODE 修飾語來執行一個邏輯全文搜索。
IN BOOLEAN MODE的特色：
不剔除50%以上符合的row。
不自動以相關性反向排序。
可以對沒有FULLTEXT 索引的字段進行搜尋，但會非常慢。
限製最長與最短的字符串。
套用stopwords。
邏輯全文搜索支持下麵的操作符：
- 一個領頭的加號表示，返回的結果的每行都必須包含有該單詞。
- 一個領頭的減號表示，包含該單詞的行不能出現在返回的結果中。 > 操作符增加包含該單詞返回行相似性值的基值。 < 操作符減少包含該單詞返回行相似性值的基值。 () 被括號包含的多個詞隻相當一個詞，即在查詢時括號裏隻有一個詞可代表該括號與括號外的詞相結合做查詢，但括號中每個詞都會依次被輪到代表該括號，所以與括號外單詞會產生多種結合形式，依次做查詢條件。 ~ 將其相關性由正轉負，表示擁有該字會降低相關性，但不像 - 將之排除，隻是排在較後麵。
- 一個星號是截斷操作符，它應該被追加到一個詞後，不加在前麵。作用類似 LIKE 語句中的 % "" 把被雙引號包含的多個詞作為一個詞
這裏是一些示例，在返回結果中：
1.+apple +juice ... 兩個詞均在被包含
2.+apple macintosh ... 包含詞 “apple”，但是如果同時包含 “macintosh”，它的排列將更高一些
3.+apple -macintosh ... 包含 “apple” 但不包含 “macintosh”
4.+apple +(>pie
5.apple* ... 包含 “apple”，“apples”，“applesauce” 和 “applet”
6."some words" ... 可以包含 “some words of wisdom”，但不是 “some noise words”
*/
SELECT *,MATCH (username,city) AGAINST ('>>name300 +thisisname -city100' IN BOOLEAN MODE) FROM mytable WHERE MATCH (username,city) AGAINST ('>>name300 +thisisname -city100' IN BOOLEAN MODE);
/*
全文索引的限製
MATCH() 函數的所有參數必須是從來自於同一張表的列，同時必須是同一個FULLTEXT 索引中的一部分，除非 MATCH() 是 IN BOOLEAN MODE 的。
MATCH() 列必須確切地匹配表的某一 FULLTEXT 索引中定義的列，除非 MATCH() 是 IN BOOLEAN MODE 的。
AGAINST() 的參數必須是一個常量字符串。
MySQL全文搜尋設定：
大部分的參數都是啟動參數，也就是修改後必須重新啟動MySQL。
有些參數修改必須重新產生索引文件。
mysql> SHOW VARIABLES LIKE 'ft%';
ft_boolean_syntax + -><()~*:""&|
ft_min_word_len 4
ft_max_word_len 84
ft_query_expansion_limit 20 ft_stopword_file (built-in)
ft_min_word_len：最短的索引字符串，默認值為4，修改後必須重建索引文件。
ft_max_word_len：最長的索引字符串，默認值因版本而不同，餘同上一點。
[mysqld]
ft_min_word_len=1
ft_stopword_file：stopword檔案路徑，若留空白不設定表示要停用stopword過濾，修改後必須重新啟動MySQL和重建索引；stopword檔案內容可以用分行空白與逗號區隔stopword，但底線和單引號視為合法的字符串字符。
50%的門坎限製：配置文件在storage/myisam/ftdefs.h，將 #define GWS_IN_USE GWS_PROB 改為 #define GWS_IN_USE GWS_FREQ，然後重新編譯MySQL，因為近低門坎會影響數據的精準度，所以不建議如此，可用IN BOOLEAN MODE即可以避開50%的限製。
ft_boolean_syntax：改變IN BOOLEAN MODE的查詢字符，不用重新啟動MySQL也不用重建索引。
修改字符串字符的認定，譬如說將「-」認定為字符串的合法字符：
方法一：修改storage/myisam/ftdefs.h的true_word_char()與misc_word_char()，然後重新編譯MySQL，最後重建索引。
方法二：修改字符集檔，然後在FULLTEXT index的字段使用該字符集，最後重建索引。
重建索引：
每個有FULLTEXT index的表格都要這麼做。
mysql> REPAIR TABLE tbl_name QUICK;
要注意如果用過myisamchk，會導致上述的設定值回複成默認值，因為myisamchk不是用MySQL的設定值。
解法一：將修改過得設定值加到myisamchk的參數裏。
shell> myisamchk --recover --ft_min_word_len=1 tbl_name.MYI
解法二：兩邊都要設定。
[mysqld]
ft_min_word_len=1
[myisamchk]
ft_min_word_len=1
解法三：用REPAIR TABLE、ANALYZE TABLE、OPTIMIZE TABLE與ALTER TABLE取代myisamchk語法，因為這些語法是由MySQL執行的。
中文全文索引可以建兩個表，一個表字段裏存中文，一個表對應字段存漢語拚音，兩表行必須對應，數據一致，插入時中文轉化下漢語拚音，兩表都插入
查詢時也轉化下，全文索引查漢語拚音，然後找到中文表對應行
或者使用mysqlcft中文全文索引插件
查看索引使用情況
如果索引正在工作，Handler_read_key的值將很高，這個值代表了一個行被索引值讀的次數，很低的值表明增加索引得到的性能改善不高，因為索引並不經常使用。
Handler_read_rnd_next的值高則意味著查詢運行低效，並且應該建立索引補救。這個值的含義是在數據文件中讀下一行的請求數。如果你正進行大量的表掃描，該值較高。通常說明表索引不正確或寫入的查詢沒有利用索引。
語法：SHOW STATUS LIKE 'Handler_read%';
MyISAM表的數據文件和索引文件是自動分開的
InnoDB的數據和索引是存儲在同一個表空間裏麵，但可以有多個文件組成
雖然索引大大提高了查詢速度，同時卻會降低更新表的速度，如對表進行INSERT、UPDATE和DELETE。因為更新表時，MySQL不僅要保存數據，還要保存一下索引文件。
建立索引會占用磁盤空間的索引文件。一般情況這個問題不太嚴重，但如果你在一個大表上創建了多種組合索引，索引文件的會膨脹很快。
*/
最後更新：2017-04-27 11:31:02