在MySQL中，創(chuàng)建索引的方法有兩種：使用命令行工具或者使用MySQL圖形化工具。以下是兩種方法的詳細(xì)步驟：
方法一：使用命令行工具
1. 登錄到MySQL服務(wù)器。在命令行中輸入以下命令：
```css
mysql -u 用戶名 -p
```
其中，用戶名是您的MySQL用戶名。執(zhí)行此命令后，系統(tǒng)將提示您輸入密碼。
2. 選擇要?jiǎng)?chuàng)建索引的數(shù)據(jù)庫(kù)。使用以下命令選擇要?jiǎng)?chuàng)建索引的數(shù)據(jù)庫(kù)：
```perl
use 數(shù)據(jù)庫(kù)名;
```
其中，數(shù)據(jù)庫(kù)名是您要?jiǎng)?chuàng)建索引的數(shù)據(jù)庫(kù)名稱。
3. 創(chuàng)建索引。使用以下命令創(chuàng)建索引：
```sql
ALTER TABLE 表名 ADD INDEX 索引名 (列名);
```
其中，表名是要添加索引的表名稱，索引名是您為索引指定的名稱，列名是要添加索引的列名稱。
例如，如果您要在名為"users"的表的"email"列上創(chuàng)建一個(gè)名為"idx_email"的索引，可以使用以下命令：
```sql
ALTER TABLE users ADD INDEX idx_email (email);
```
方法二：使用MySQL圖形化工具
1. 啟動(dòng)MySQL圖形化工具（如phpMyAdmin或MySQL Workbench）。
2. 連接到您的MySQL服務(wù)器。輸入服務(wù)器地址、用戶名和密碼。
3. 選擇要?jiǎng)?chuàng)建索引的數(shù)據(jù)庫(kù)和表。在圖形化工具中，您可以通過(guò)單擊數(shù)據(jù)庫(kù)名稱來(lái)選擇它，然后選擇您要?jiǎng)?chuàng)建索引的表。
4. 創(chuàng)建索引。在工具欄或右鍵菜單中，選擇"Alter Table"（更改表）選項(xiàng)。在彈出的對(duì)話框中，選擇要添加索引的列，并設(shè)置索引名稱和其他選項(xiàng)。單擊"Apply"（應(yīng)用）按鈕以創(chuàng)建索引。
5. 等待圖形化工具完成操作。在操作完成后，您可以驗(yàn)證索引是否成功創(chuàng)建。您可以通過(guò)執(zhí)行以下查詢來(lái)檢查是否已成功創(chuàng)建索引：
```sql
SHOW INDEX FROM 表名;
```
其中，表名是您要檢查索引的表名稱。
索引的順序要遵循三個(gè)規(guī)則
1.要遵循最左前綴 無(wú)論是多個(gè)還是一個(gè)列的索引 都不應(yīng)該跳過(guò)最左列 如果在查詢語(yǔ)句當(dāng)中 沒有使用最左前綴的字段 就不會(huì)使用索引
2.不能跨越索引列
3.索引進(jìn)行模糊查詢 范圍查詢 ，右邊的所有列都無(wú)法使用索引優(yōu)化
MySQL建立索引最簡(jiǎn)單的目的就是對(duì)數(shù)據(jù)庫(kù)的訪問會(huì)快一點(diǎn)。一個(gè)表，如果沒有索引，數(shù)據(jù)量少點(diǎn)的時(shí)候你不會(huì)覺得數(shù)據(jù)庫(kù)本身的的性能問題，但是隨著數(shù)據(jù)量的顯著增加，比如超過(guò)一萬(wàn)條記錄之后，可能你就會(huì)遇到數(shù)據(jù)庫(kù)操作的性能問題了，這個(gè)時(shí)候，你建立索引就會(huì)顯著的改善數(shù)據(jù)庫(kù)的寫入性能。
索引查詢失效的幾個(gè)情況：
1、like 以%開頭，索引無(wú)效；當(dāng)like前綴沒有%，后綴有%時(shí)，索引有效。
2、or語(yǔ)句前后沒有同時(shí)使用索引。當(dāng)or左右查詢字段只有一個(gè)是索引，該索引失效，只有當(dāng)or左右查詢字段均為索引時(shí)，才會(huì)生效。
3、組合索引，不是使用第一列索引，索引失效。
4、數(shù)據(jù)類型出現(xiàn)隱式轉(zhuǎn)化。如varchar不加單引號(hào)的話可能會(huì)自動(dòng)轉(zhuǎn)換為int型，使索引無(wú)效，產(chǎn)生全表掃描。
5、在索引列上使用 IS NULL 或 IS NOT NULL操作。索引是不索引空值的，所以這樣的操作不能使用索引，可以用其他的辦法處理。
一、定義
索引定義：索引（Index）是幫助MySQL高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)。本質(zhì)：索引是數(shù)據(jù)結(jié)構(gòu)。
二、B-Tree
m階B-Tree滿足以下條件：1、每個(gè)節(jié)點(diǎn)至多可以擁有m棵子樹。2、根節(jié)點(diǎn)，只有至少有2個(gè)節(jié)點(diǎn)（要么極端情況，就是一棵樹就一個(gè)根節(jié)點(diǎn)，單細(xì)胞生物，即是根，也是葉，也是樹)。3、非根非葉的節(jié)點(diǎn)至少有的Ceil(m/2)個(gè)子樹(Ceil表示向上取整，如5階B樹，每個(gè)節(jié)點(diǎn)至少有3個(gè)子樹，也就是至少有3個(gè)叉)。4、非葉節(jié)點(diǎn)中的信息包括[n,A0,K1,A1,K2,A2,…,Kn,An]，，其中n表示該節(jié)點(diǎn)中保存的關(guān)鍵字個(gè)數(shù)，K為關(guān)鍵字且Ki<Ki+1，A為指向子樹根節(jié)點(diǎn)的指針。5、從根到葉子的每一條路徑都有相同的長(zhǎng)度（葉子節(jié)點(diǎn)在相同的層）
B-Tree特性：
1、關(guān)鍵字集合分布在整顆樹中；2、任何一個(gè)關(guān)鍵字出現(xiàn)且只出現(xiàn)在一個(gè)節(jié)點(diǎn)中；3、每個(gè)節(jié)點(diǎn)存儲(chǔ)date和key；4、搜索有可能在非葉子節(jié)點(diǎn)結(jié)束；5、一個(gè)節(jié)點(diǎn)中的key從左到右非遞減排列；6、所有葉節(jié)點(diǎn)具有相同的深度，等于樹高h(yuǎn)。
B-Tree上查找算法的偽代碼如下：
三、B+Tree
B+Tree與B-Tree的差異在于：1、B+Tree非葉子節(jié)點(diǎn)不存儲(chǔ)data，只存儲(chǔ)key；2、所有的關(guān)鍵字全部存儲(chǔ)在葉子節(jié)點(diǎn)上；3、每個(gè)葉子節(jié)點(diǎn)含有一個(gè)指向相鄰葉子節(jié)點(diǎn)的指針，帶順序訪問指針的B+樹提高了區(qū)間查找能力；4、非葉子節(jié)點(diǎn)可以看成索引部分，節(jié)點(diǎn)中僅含有其子樹（根節(jié)點(diǎn)）中的最大（或最小）關(guān)鍵字；
四、B/B+樹索引的性能分析
依據(jù)：使用磁盤I/O次數(shù)評(píng)價(jià)索引結(jié)構(gòu)的優(yōu)劣主存和磁盤以頁(yè)為單位交換數(shù)據(jù)，將一個(gè)節(jié)點(diǎn)的大小設(shè)為等于一個(gè)頁(yè)，因此每個(gè)節(jié)點(diǎn)只需一次I/O就可以完全載入。根據(jù)B樹的定義，可知檢索一次最多需要訪問h個(gè)節(jié)點(diǎn)漸進(jìn)復(fù)雜度:O(h)=O(logdN) dmax=floor(pagesize/(keysize+datasize+pointsize))一般實(shí)際應(yīng)用中，出度d是非常大的數(shù)字，通常超過(guò)100，因此h非常小（通常不超過(guò)3，3層可存大約一百萬(wàn)數(shù)據(jù)）B-Tree中一次檢索最多需要h-1次I/O（根節(jié)點(diǎn)常駐內(nèi)存）B+Tree內(nèi)節(jié)點(diǎn)不含data域，因此出度d更大，則h更小，I/O次數(shù)少，效率更高，故B+Tree更適合外存索引。
五、MySQL索引實(shí)現(xiàn)1、MyISAM引擎使用B+Tree作為索引結(jié)構(gòu)，葉節(jié)點(diǎn)的data域存放的是數(shù)據(jù)記錄的地址； MyISAM主索引和輔助索引在結(jié)構(gòu)上沒有任何區(qū)別，只是主索引要求key是唯一的，而輔助索引的key可以重復(fù)；
2、InnoDB的數(shù)據(jù)文件本身就是索引文件，葉節(jié)點(diǎn)包含了完整的數(shù)據(jù)記錄，這種索引叫做聚集索引。因?yàn)镮nnoDB的數(shù)據(jù)文件本身要按主鍵聚集，所以InnoDB要求表必須有主鍵（MyISAM可以沒有），如果沒有顯式指定，則MySQL系統(tǒng)會(huì)自動(dòng)選擇一個(gè)可以唯一標(biāo)識(shí)數(shù)據(jù)記錄的列作為主鍵，如果不存在這種列，則MySQL自動(dòng)為InnoDB表生成一個(gè)隱含字段作為主鍵。 InnoDB的輔助索引data域存儲(chǔ)相應(yīng)記錄主鍵的值而不是地址； 輔助索引搜索需要檢索兩遍索引：首先檢索輔助索引獲得主鍵，然后用主鍵到主索引中檢索獲得記錄；
3、頁(yè)分裂問題
如果主鍵是單調(diào)遞增的，每條新記錄會(huì)順序插入到頁(yè)，當(dāng)頁(yè)被插滿后，繼續(xù)插入到新的頁(yè)；
如果寫入是亂序的，InnoDB不得不頻繁地做頁(yè)分裂操作，以便為新的行分配空間。頁(yè)分裂會(huì)導(dǎo)致移動(dòng)大量數(shù)據(jù)，一次插入最少需要修改三個(gè)頁(yè)而不是一個(gè)頁(yè)。
如果頻繁的頁(yè)分裂，頁(yè)會(huì)變得稀疏并被不規(guī)則地填充，所以最終數(shù)據(jù)會(huì)有碎片。
六、總結(jié)
了解不同存儲(chǔ)引擎的索引實(shí)現(xiàn)方式對(duì)于正確使用和優(yōu)化索引都非常有幫助
1、為什么不建議使用過(guò)長(zhǎng)的字段作為主鍵？
2、為什么選擇自增字段作為主鍵？
3、為什么常更新是字段不建議建立索引？
4、為什么選擇區(qū)分度高的列作為索引？區(qū)分度的公式是count(distinct col)/count(*)
5、盡可能的使用覆蓋索引
七、優(yōu)化LIMIT分頁(yè)查詢
SELECT * FROM table where condition LIMIT offset , rows ;上述SQL語(yǔ)句的實(shí)現(xiàn)機(jī)制是： 1、從“table”表中讀取offset+rows行記錄。 2、 拋棄前面的offset行記錄，返回后面的rows行記錄作為最終的結(jié)果。覆蓋索引：select a.id, sid, parent_s_id from cashpool_account_relationship a join (select id from cashpool_account_relationship LIMIT 1000000,10)b on a.id = b.id;select id, sid, parent_s_id from cashpool_account_relationship where id >=(select id from cashpool_account_relationship LIMIT 1000000,1) LIMIT 10;
八、Q&A
1、InnoDB支持hash索引嗎？--馬欣InnoDB是支持hash索引的，不過(guò)其支持的hash索引是自適應(yīng)的，InnoDB存儲(chǔ)引擎會(huì)根據(jù)表的使用情況自動(dòng)為表生成hash索引，不能人為干預(yù)是否在一張表中生成hash索引。2、InnoDB主鍵索引的葉節(jié)點(diǎn)含完整的數(shù)據(jù)記錄，那主鍵索引文件要比數(shù)據(jù)文件大嗎？--徐財(cái)厚1）.在Innodb 引擎中，主鍵索引中的葉子結(jié)點(diǎn)包含記錄數(shù)據(jù)，主鍵索引文件即為數(shù)據(jù)文件。2）.在 tables 表中統(tǒng)計(jì)的data_length數(shù)據(jù)為主鍵索引大小，index_length 為統(tǒng)計(jì)的這個(gè)表中所有輔助索引（二級(jí)索引）索引的大小。
mysql建立索引是立即生效的，語(yǔ)句生效后索引也就建立成功了，可以建立索引進(jìn)行快速的查詢和檢索
Hash索引：將索引字段轉(zhuǎn)化為hashcode，在對(duì)hashcode進(jìn)行排序。僅支持Memory引擎。
1. 普通索引：最基本的索引，它沒有任何限制，用于加速查詢。
2. 唯一索引unique：索引列的值必須唯一，但允許有空值。如果是組合索引，則列值的組合必須唯一。
3. 主鍵索引： 是一種特殊的唯一索引，一個(gè)表只能有一個(gè)主鍵，不允許有空值。一般是在建表的時(shí)候同時(shí)創(chuàng)建主鍵索引。
4. 空間索引Spatial ：空間索引是對(duì)空間數(shù)據(jù)類型的字段建立的索引，MYSQL中的空間數(shù)據(jù)類型有4種，分別是GEOMETRY、POINT、LINESTRING、POLYGON。MYSQL使用SPATIAL關(guān)鍵字進(jìn)行擴(kuò)展，使得能夠用于創(chuàng)建正規(guī)索引類型的語(yǔ)法創(chuàng)建空間索引。創(chuàng)建空間索引的列，必須將其聲明為NOT NULL，空間索引只能在存儲(chǔ)引擎為MYISAM的表中創(chuàng)建
1.最左前綴匹配原則, mysql會(huì)一只向右匹配直到遇到范圍查詢(>, <, between, like)就停止匹配, 比如a=1 and b=2 and c>3 and d=4 如果建立了(a,b,c,d)順序的索引, d是用不到索引的, 如果建立(a,b,d,c)的索引, 則都可以使用到, a,b,d的順序可以任意調(diào)整.
2.= 和 in 可以亂序, 比如 a=1 and b=2 and c=3 建立(a,b,c)索引可以任意順序, mysql 的查詢優(yōu)化器會(huì)幫你優(yōu)化成索引可以識(shí)別的形式.
mysql索引有：
1、主鍵索引：主鍵索引是一種特殊的唯一索引，不允許有空值
2、普通索引或者單列索引
3、多列索引（復(fù)合索引）：復(fù)合索引指多個(gè)字段上創(chuàng)建的索引，只有在查詢條件中使用了創(chuàng)建索引時(shí)的第一個(gè)字段，索引才會(huì)被使用。使用復(fù)合索引時(shí)遵循最左前綴集合
4、唯一索引或者非唯一索引
5、空間索引：空間索引是對(duì)空間數(shù)據(jù)類型的字段建立的索引。MYSQL使用SPATIAL關(guān)鍵字進(jìn)行擴(kuò)展，使得能夠用于創(chuàng)建正規(guī)索引類型的語(yǔ)法創(chuàng)建空間索引。
索引可以加快數(shù)據(jù)檢索操作，但會(huì)使數(shù)據(jù)修改操作變慢。每修改數(shù)據(jù)記錄，索引就必須刷新一次。為了在某種程度上彌補(bǔ)這一缺陷，許多SQL命令都有一個(gè)DELAY_KEY_WRITE項(xiàng)。
這個(gè)選項(xiàng)的作用是暫時(shí)制止MySQL在該命令每插入一條新記錄和每修改一條現(xiàn)有之后立刻對(duì)索引進(jìn)行刷新，對(duì)索引的刷新將等到全部記錄插入/修改完畢之后再進(jìn)行。在需要把許多新記錄插入某個(gè)數(shù)據(jù)表的場(chǎng)合，DELAY_KEY_WRITE選項(xiàng)的作用將非常明顯。
