mysql索引

『壹』 mysql加索引怎麼加

mysql 如何給SQL添加索引
1.添加PRIMARY KEY(主鍵索引)
alter table `table_name` add primary key(`column`);
2.添加UNIQUE(唯一索引)
alter table `table_name` add unique(`column`);
3.添加普通索引
alter table `table_name` add index index_name(`column`);
4.添加全文索引
alter table `table_name` add fulltext(`column`);
5.添加多列索引
alter table `table_name` add index index_name(`column1`,`column2`,`column3`);

『貳』 mysql創建索引的三種辦法

一、CREATE INDEX方法
CREATE INDEX <索引名> ON <表名> (<列名> [<長度>] [ ASC | DESC])
限制：只能增加普通索引INDEX和UNIQUE INDEX索引這兩種；不能創建PRIMARY KEY索引
二、ALTER TABLE方法
ALTER TABLE <表名> ADD INDEX [<索引名>] (<列名>,…)
ALTER TABLE <表名> ADD UNIQUE [ INDEX | KEY] [<索引名>] (<列名>,…)
ALTER TABLE <表名> ADD PRIMARY KEY (<列名>,…)
ALTER TABLE <表名> ADD FOREIGN KEY [<索引名>] (<列名>,…)
三、CREATE TABLE時候指定
創建一般索引
CREATE TABLE tb_stu_info
(
id INT NOT NULL,
name CHAR(45) DEFAULT NULL,
dept_id INT DEFAULT NULL,
age INT DEFAULT NULL,
height INT DEFAULT NULL,
INDEX(height)
);
創建唯一索引
mysql> CREATE TABLE tb_stu_info2
(
id INT NOT NULL,
name CHAR(45) DEFAULT NULL,
dept_id INT DEFAULT NULL,
age INT DEFAULT NULL,
height INT DEFAULT NULL,
UNIQUE INDEX(height)
);
創建主鍵（雖然ALTER TABLE也能創建，但主鍵一般都在創表時建立）
CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
PRIMARY KEY(ID)
);

『叄』 MYSQL資料庫索引類型都有哪些

聚集索引：也稱 Clustered Index。是指關系表記錄的物理順序與索引的邏輯順序相同。由於一張表只能按照一種物理順序存放，一張表最多也只能存在一個聚集索引。與非聚集索引相比，聚集索引有著更快的檢索速度。
MySQL 里只有 INNODB 表支持聚集索引，INNODB 表數據本身就是聚集索引，也就是常說 IOT，索引組織表。非葉子節點按照主鍵順序存放，葉子節點存放主鍵以及對應的行記錄。所以對 INNODB 表進行全表順序掃描會非常快。
非聚集索引：也叫 Secondary Index。指的是非葉子節點按照索引的鍵值順序存放，葉子節點存放索引鍵值以及對應的主鍵鍵值。MySQL 里除了 INNODB 表主鍵外，其他的都是二級索引。MYISAM，memory 等引擎的表索引都是非聚集索引。簡單點說，就是索引與行數據分開存儲。一張表可以有多個二級索引。

『肆』 MySQL前綴索引

前綴索引顧名思義，定義字元串的一部分當做索引，而不是把整個字元串當做索引。默認地，如果你創建索引的語句不指定前綴長度，那麼索引就會包含整個字元串。

假設一張表有 id,name,email 2個欄位
1.創建email列的普通索引應該是： alter table T add index idx_email1( email )
2.前綴索引的創建規則為： alter table table T add index idx_email2( email(6) )
當然第一索引包含是的整個字元串，第二個是該欄位前6個位元組（注意是位元組）
對於這2中索引，B+樹怎麼存儲呢？
INSERT INTO T (email) VALUES ('瞎子',&#[email protected]'), ('劍聖',&#[email protected]'), ('露娜',&#[email protected]'), ('李白',&#[email protected]'), ('韓信',&#[email protected]'), ('百里玄策',&#[email protected]');
【誰還不是個野王啊】
普通索引存儲為：

是的你沒看錯，前綴索引那顆樹上的存儲的是email的前6位位元組，也就是你創建前綴索引時指定的前綴位元組長度。2種樹相比，前綴索引存儲了更少的數據，那麼他所耗費的空間也就相比較少，這正是他的一個優點。同樣的也就相對的增加了掃描行數。
什麼增加了掃描行數？？？？ 這是為什麼呢？

那麼小朋友咱們一起來看下吧。
假設SQL如此這般： select id,name,email from T where email = &#[email protected]'
那麼這2個SQL，應該怎麼操作呢。
idx_email1:

2.到主鍵上查到主鍵為ID1的，判斷email值是否正確【為什麼判斷呢，其實我理解是為了二次判斷保證數據一致性吧，比較官方的解釋尚未找到】，正確放入結果集

3.取 idx_email1 索引樹上剛剛查到的位置的下一條記錄，如此往復。
循環過程中，需要回主鍵取1次數據，所以系統可以認為只掃描了一行【1次是數第一棵樹數出來的】

idx_email2:
1.從 索引數上找到滿足索引值為 'zhangs'的該記錄，取得 ID1的值

2.到主鍵上查到主鍵值是 ID1 的行，判斷出 email 的值是』 [email protected] 』，這行記錄放入結果集【不是要的值，丟棄，進行下一步】

3.取 idx_email2 上剛剛查到的位置的下一條記錄,重復以上步驟

在這個過程中，要回主鍵索引取 3 次數據，也就是掃描了 3 行。通過這個對比，你很容易就可以發現，使用前綴索引後，可能會導致查詢語句讀數據的次數變多。

但是，對於這個查詢語句來說，如果你定義的 idx_email2 不是 email(6) 而是 email(8），也就是說取 email 欄位的前 8 個位元組來構建索引的話，即滿足前綴』zhangsh』的記錄只有一個，也能夠直接查到 ID1，只掃描一行就結束了。也就是說使用前綴索引，定義好長度，就可以做到既節省空間，又不用額外增加太多的查詢成本。

那麼問題來了，到底定義多長才算是合理呢？
一般的定義原則是 count(distinct(columnName))/count(*) ,當前綴索引【count(distinct(columnName(length)))，length是你想要創建列的前綴位元組長度】越接近此值越好，當有多個前綴位元組都一樣且都等於這個值時怎麼選擇呢，當然是 位元組越少越好了哈，位元組越少越省空間。索引選取的越長，佔用的磁碟空間就越大，相同的數據頁能放下的索引值就越少，搜索的效率也就會越低。

count(distinct(columnName(length))) 翻譯到SQL 為： count(dictinct(left(colunmName, length)))

前面我們說了使用前綴索引可能會增加掃描行數，這會影響到性能。其實，前綴索引的影響不止如此，我們再看一下另外一個場景。
來呀，上SQL： select id,email from T where email=&#[email protected]'
如果按照email全欄位索引，那麼此SQL 是不需要回表的【為什麼不需要回表？兄嘚，這個相當於覆蓋索引了哈】
那麼如果按照前綴索引是否需要回表呢？答案是的。
因為當判斷前6個位元組相等後，需要拿到id 回表拿到email的全部內容進行比較，如果不相同，丟棄這行，否則加入結果集。

那麼有人會問了，我把長度放大點，包含所有位元組不就好了嗎？
那麼此時會有如下問題。
1.當你此時的長度是囊括了全欄位，但是系統是不知道的，他還是需要回表再次判斷的，去確定前綴索引的定義是否截斷了完整信息。
2.此時長度是夠了，那麼能肯定因為業務日後不會增加長度嗎？
3.盡可能的加長長度，還不如直接建立全欄位索引呢

綜上，使用前綴索引就用不上覆蓋索引對查詢性能的優化了，這也是你在選擇是否使用前綴索引時需要考慮的一個因素。

前面說到的是，可以根據欄位前面幾個位元組進行查詢的，那麼對於身份證這種，一共 18 位，其中前 6 位是地址碼，所以同一個縣的人的身份證號前 6 位一般會是相同的。
或許你會說，多弄幾個位元組不就好嗎？那麼請問下自己為什麼使用前綴索引呢，不就是為了節省空間嗎？
那麼這么做合適嗎？ 不合適對嗎？ 乖~，快去反省下吧
那麼採用前綴索引顯示是不行的，那麼如果用前綴索引怎麼辦呢，聰明的你應該已經猜到了，採用倒敘存儲，然後建立前綴索引。
放到SQL 中就應該是這樣的： select field_list from t where id_card = reverse('id_card_string');
當然了，這種邏輯建議放到業務邏輯中實現，而不是放到SQL 中。

按照上述第4節的內容，有人或許會有另一個想法，還倒敘建立前綴索引復雜不，hash索引或者hash欄位不香嗎？

有人會問了，為什麼要在創建一個值來存儲hash值呢，如果不存儲你知道原值是什麼嗎？ 同時hash演算法是有一定重復可能的（hash值碰撞）
【可以了解下partition演算法哦:[ https://selfboot.cn/2016/09/01/lost_partition 】。如果重復了，不存儲原值，你是無法判斷出正確數據的。

註：【hash欄位不代表hash索引，hash索引原理正在快馬加鞭】，簡單說下hash索引，hash索引不需要創建一個值來存儲hash值，而是有hasn表來存儲【hash值碰撞時，由一個鏈表來搞定了】，存儲的內容為 hash值和每行的行指針 。

說回來啊，跑題了
查詢時： select field_list from t where id_card_crc=crc32('id_card_string') and id_card='id_card_string'
不過有個問題相信你也想到了，不管是hash存儲值還是hash索引都是不支持范圍查詢的。

來總結下這2個優缺點吧
1.從佔用空間來看呢，倒敘索引不需要額外開辟存儲空間，而hash欄位需要額外的一個欄位，所以從這點上看倒敘索引更勝一籌，NO!並不準確,如果前綴長度過長，那麼這2個情況額外的空間也就相差無幾了

3.從查詢效率上看，使用 hash 欄位方式的查詢性能相對更穩定一些。因為 crc32 算出來的值雖然有沖突的概率，但是概率非常小，可以認為每次查詢的平均掃描行數接近 1。而倒序存儲方式畢竟還是用的前綴索引的方式，也就是說還是會增加掃描行數

1.全欄位完整索引比較占空間，但是而走覆蓋索引

2.前綴索引，節省空間，但會增加掃描 次數 並且不能使用覆蓋索引【每次都需回表校驗】

3.倒序存儲，再創建前綴索引，用於繞過字元串本身前綴的區分度不夠的問題。【倒敘方法建立放到業務邏輯中】

4.hash欄位索引，相比前綴索引性能較為穩定，但是有額外的存儲空間和計算消耗，同時也 不 支持范圍查詢

『伍』 mysql索引有哪些

如大家所知道的，Mysql目前主要有以下幾種索引類型：FULLTEXT，HASH，BTREE，RTREE。
那麼，這幾種索引有什麼功能和性能上的不同呢？
FULLTEXT
即為全文索引，目前只有MyISAM引擎支持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不過目前只有 CHAR、VARCHAR ，TEXT 列上可以創建全文索引。值得一提的是，在數據量較大時候，現將數據放入一個沒有全局索引的表中，然後再用CREATE INDEX創建FULLTEXT索引，要比先為一張表建立FULLTEXT然後再將數據寫入的速度快很多。
全文索引並不是和MyISAM一起誕生的，它的出現是為了解決WHERE name LIKE 「%word%"這類針對文本的模糊查詢效率較低的問題。在沒有全文索引之前，這樣一個查詢語句是要進行遍歷數據表操作的，可見，在數據量較大時是極其的耗時的，如果沒有非同步IO處理，進程將被挾持，很浪費時間，當然這里不對非同步IO作進一步講解，想了解的童鞋，自行谷哥。

『陸』 mysql創建索引的原則

1．選擇唯一性索引

唯一性索引的值是唯一的，可以更快速的通過該索引來確定某條記錄。例如，學生表中學號是具有唯一性的欄位。為該欄位建立唯一性索引可以很快的確定某個學生的信息。如果使用姓名的話，可能存在同名現象，從而降低查詢速度。

2．為經常需要排序、分組和聯合操作的欄位建立索引

經常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的欄位，排序操作會浪費很多時間。如果為其建立索引，可以有效地避免排序操作。

3．為常作為查詢條件的欄位建立索引

如果某個欄位經常用來做查詢條件，那麼該欄位的查詢速度會影響整個表的查詢速度。因此，為這樣的欄位建立索引，可以提高整個表的查詢速度。

4．限制索引的數目

索引的數目不是越多越好。每個索引都需要佔用磁碟空間，索引越多，需要的磁碟空間就越大。修改表時，對索引的重構和更新很麻煩。越多的索引，會使更新表變得很浪費時間。

5．盡量使用數據量少的索引

如果索引的值很長，那麼查詢的速度會受到影響。例如，對一個CHAR(100)類型的欄位進行全文檢索需要的時間肯定要比對CHAR(10)類型的欄位需要的時間要多。

6．盡量使用前綴來索引

如果索引欄位的值很長，最好使用值的前綴來索引。例如，TEXT和BLOG類型的欄位，進行全文檢索會很浪費時間。如果只檢索欄位的前面的若干個字元，這樣可以提高檢索速度。

7．刪除不再使用或者很少使用的索引

表中的數據被大量更新，或者數據的使用方式被改變後，原有的一些索引可能不再需要。資料庫管理員應當定期找出這些索引，將它們刪除，從而減少索引對更新操作的影響。

8 . 最左前綴匹配原則，非常重要的原則。

mysql會一直向右匹配直到遇到范圍查詢(>、<、between、like)就停止匹配，比如a 1=」」 and=」」 b=」2」 c=」「> 3 and d = 4 如果建立(a,b,c,d)順序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引則都可以用到，a,b,d的順序可以任意調整。

9 .=和in可以亂序。

比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意順序，mysql的查詢優化器會幫你優化成索引可以識別的形式

。
10 . 盡量選擇區分度高的列作為索引。

區分度的公式是count(distinct col)/count(*)，表示欄位不重復的比例，比例越大我們掃描的記錄數越少，唯一鍵的區分度是1，而一些狀態、性別欄位可能在大數據面前區分度就 是0，那可能有人會問，這個比例有什麼經驗值嗎？使用場景不同，這個值也很難確定，一般需要join的欄位我們都要求是0.1以上，即平均1條掃描10條 記錄

11 .索引列不能參與計算，保持列「干凈」。

比如from_unixtime(create_time) = 』2014-05-29』就不能使用到索引，原因很簡單，b+樹中存的都是數據表中的欄位值，但進行檢索時，需要把所有元素都應用函數才能比較，顯然成本 太大。所以語句應該寫成create_time = unix_timestamp(』2014-05-29』);

12 .盡量的擴展索引，不要新建索引。
比如表中已經有a的索引，現在要加(a,b)的索引，那麼只需要修改原來的索引即可

注意：選擇索引的最終目的是為了使查詢的速度變快。上面給出的原則是最基本的准則，但不能拘泥於上面的准則。讀者要在以後的學習和工作中進行不斷的實踐。根據應用的實際情況進行分析和判斷，選擇最合適的索引方式。

『柒』 mysql如何建立索引

我們可以通過查看索引的屬性來判斷創建索引的方法。
查看索引的語法格式如下：
SHOW INDEX FROM <表名> [ FROM <資料庫名>]
語法說明如下：
<表名>：指定需要查看索引的數據表名。
<資料庫名>：指定需要查看索引的數據表所在的資料庫，可省略。比如，SHOW INDEX FROM student FROM test; 語句表示查看 test 資料庫中 student 數據表的索引。
示例
使用 SHOW INDEX 語句查看《MySQL創建索引》一節中 tb_stu_info2 數據表的索引信息，SQL 語句和運行結果如下所示。
mysql> SHOW INDEX FROM tb_stu_info2\G
1. row
Table: tb_stu_info2
Non_unique: 0
Key_name: height
Seq_in_index: 1
Column_name: height
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
1 row in set (0.03 sec)
其中各主要參數說明如下：
參數 說明
Table 表示創建索引的數據表名，這里是 tb_stu_info2 數據表。
Non_unique 表示該索引是否是唯一索引。若不是唯一索引，則該列的值為 1；若是唯一索引，則該列的值為 0。
Key_name 表示索引的名稱。
Seq_in_index 表示該列在索引中的位置，如果索引是單列的，則該列的值為 1；如果索引是組合索引，則該列的值為每列在索引定義中的順序。
Column_name 表示定義索引的列欄位。
Collation 表示列以何種順序存儲在索引中。在 MySQL 中，升序顯示值「A」（升序），若顯示為 NULL，則表示無分類。
Cardinality 索引中唯一值數目的估計值。基數根據被存儲為整數的統計數據計數，所以即使對於小型表，該值也沒有必要是精確的。基數越大，當進行聯合時，MySQL 使用該索引的機會就越大。
Sub_part 表示列中被編入索引的字元的數量。若列只是部分被編入索引，則該列的值為被編入索引的字元的數目；若整列被編入索引，則該列的值為 NULL。
Packed 指示關鍵字如何被壓縮。若沒有被壓縮，值為 NULL。
Null 用於顯示索引列中是否包含 NULL。若列含有 NULL，該列的值為 YES。若沒有，則該列的值為 NO。
Index_type 顯示索引使用的類型和方法（BTREE、FULLTEXT、HASH、RTREE）。
Comment 顯示評注。

『捌』 MySQL 索引是怎麼實現的

索引是滿足某種特定查找演算法的數據結構，而這些數據結構會以某種方式指向數據，從而實現高效查找數據。
具體來說 MySQL 中的索引，不同的數據引擎實現有所不同，但目前主流的資料庫引擎的索引都是 B+ 樹實現的，B+ 樹的搜索效率，可以到達二分法的性能，找到數據區域之後就找到了完整的數據結構了，所有索引的性能也是更好的。