MySQL – 索引

---

一、索引簡(jiǎn)介

1.簡(jiǎn)介

索引：提高數(shù)據(jù)庫(kù)的性能，索引是物美價(jià)廉的東西了。不用加內(nèi)存，不用改程序，不用調(diào)sql，只要執(zhí)行
正確的create index ，查詢速度就可能提高成百上千倍。但是天下沒(méi)有免費(fèi)的午餐，查詢速度的提高是以插入、更新、刪除的速度為代價(jià)的，這些寫(xiě)操作，增加了大量的IO。所以它的價(jià)值，在于提高一個(gè)海量數(shù)據(jù)的檢索速度。

常見(jiàn)索引分為：

• 主鍵索引(primary key)
• 唯一索引(unique)
• 普通索引(index)
• 全文索引(fulltext)–解決中子文索引問(wèn)題

2.索引效率的案例

• 創(chuàng)建海量數(shù)據(jù)表：

--構(gòu)建一個(gè)8000000條記錄的數(shù)據(jù)
--構(gòu)建的海量表數(shù)據(jù)需要有差異性，所以使用存儲(chǔ)過(guò)程來(lái)創(chuàng)建， 拷貝下面代碼就可以了，暫時(shí)不用理解
-- 產(chǎn)生隨機(jī)字符串
delimiter $$
create function rand_string(n INT)
returns varchar(255)
begin
declare chars_str varchar(100) default
'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
declare return_str varchar(255) default '';
declare i int default 0;
while i < n do
set return_str =concat(return_str,substring(chars_str,floor(1+rand()*52),1));
set i = i + 1;
end while;
return return_str;
end $$
delimiter ;
--產(chǎn)生隨機(jī)數(shù)字
delimiter $$
create function rand_num()
returns int(5)
begin
declare i int default 0;
set i = floor(10+rand()*500);
return i;
end $$
delimiter ;
--創(chuàng)建存儲(chǔ)過(guò)程，向雇員表添加海量數(shù)據(jù)
delimiter $$
create procedure insert_emp(in start int(10),in max_num int(10))
begin
declare i int default 0;
set autocommit = 0;
repeat
set i = i + 1;
insert into EMP values ((start+i)
,rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num());
until i = max_num
end repeat;
commit;
end $$
delimiter ;
-- 執(zhí)行存儲(chǔ)過(guò)程，添加8000000條記錄
call insert_emp(100001, 8000000);

• 查詢員工編號(hào)為998877的員工：

select * from EMP where empno=998877;

可以看到耗時(shí)4.93秒，這還是在本機(jī)一個(gè)人來(lái)操作，在實(shí)際項(xiàng)目中，如果放在公網(wǎng)中，假如同時(shí)有1000個(gè)人并發(fā)查詢，那很可能就死機(jī)。

• 解決方法，創(chuàng)建索引：

alter table EMP add index(empno);

• 換一個(gè)員工編號(hào)，測(cè)試看看查詢時(shí)間：

select * from EMP where empno=123456;

二、認(rèn)識(shí)磁盤(pán)

MySQL 給用戶提供存儲(chǔ)服務(wù)，而存儲(chǔ)的都是數(shù)據(jù)，數(shù)據(jù)在磁盤(pán)這個(gè)外設(shè)當(dāng)中。磁盤(pán)是計(jì)算機(jī)中的一個(gè)機(jī)械設(shè)備，相比于計(jì)算機(jī)其他電子元件，磁盤(pán)效率是比較低的，在加上IO本身的特征，可以知道，如何提高效率，是MySQL 的一個(gè)重要話題。

1.磁盤(pán)

• 扇區(qū)：
  數(shù)據(jù)庫(kù)文件，本質(zhì)其實(shí)就是保存在磁盤(pán)的盤(pán)片當(dāng)中。也就是上面的一個(gè)個(gè)小格子中，就是我們經(jīng)常所說(shuō)的扇區(qū)。當(dāng)然，數(shù)據(jù)庫(kù)文件很大，也很多，一定需要占據(jù)多個(gè)扇區(qū)。

數(shù)據(jù)庫(kù)文件，本質(zhì)其實(shí)就是保存在磁盤(pán)的盤(pán)片當(dāng)中，就是一個(gè)一個(gè)的文件，通過(guò)/var/lib/mysql這個(gè)目錄可以看到創(chuàng)建的數(shù)據(jù)庫(kù)文件；

所以，最基本的，找到一個(gè)文件的全部，本質(zhì)就是在磁盤(pán)找到所有保存文件的扇區(qū)。
而我們能夠定位任何一個(gè)扇區(qū)，那么便能找到所有扇區(qū)，因?yàn)椴檎曳绞绞且粯拥摹?/p>

• 定位扇區(qū)：
  
  • 柱面(磁道): 多盤(pán)磁盤(pán)，每盤(pán)都是雙面，大小完全相等。那么同半徑的磁道，整體上便構(gòu)成了一個(gè)柱面；
  • 每個(gè)盤(pán)面都有一個(gè)磁頭，那么磁頭和盤(pán)面的對(duì)應(yīng)關(guān)系便是1對(duì)1的；
  • 所以，我們只需要知道，磁頭（Heads）、柱面(Cylinder)(等價(jià)于磁道)、扇區(qū)(Sector)對(duì)應(yīng)的編號(hào)。即可在磁盤(pán)上定位所要訪問(wèn)的扇區(qū)。這種磁盤(pán)數(shù)據(jù)定位方式叫做CHS 。不過(guò)實(shí)際系統(tǒng)軟件使用的并不是CHS （但是硬件是），而是LBA ，一種線性地址，可以想象成虛擬地址與物理地址。系統(tǒng)將LBA 地址最后會(huì)轉(zhuǎn)化成為CHS ，交給磁盤(pán)去進(jìn)行數(shù)據(jù)讀取。

2.結(jié)論

我們現(xiàn)在已經(jīng)能夠在硬件層面定位，任何一個(gè)基本數(shù)據(jù)塊了(扇區(qū))。那么在系統(tǒng)軟件上，就直接按照扇區(qū)(512字節(jié)，部分4096字節(jié))，進(jìn)行IO交互嗎？不是

• 如果操作系統(tǒng)直接使用硬件提供的數(shù)據(jù)大小進(jìn)行交互，那么系統(tǒng)的IO代碼，就和硬件強(qiáng)相關(guān)，換言之，如果硬件發(fā)生變化，系統(tǒng)必須跟著變化；
• 從目前來(lái)看，單次IO 512字節(jié)，還是太小了；IO單位小，意味著讀取同樣的數(shù)據(jù)內(nèi)容，需要進(jìn)行多次磁盤(pán)訪問(wèn)，會(huì)帶來(lái)效率的降低；
• 之前學(xué)習(xí)文件系統(tǒng)，就是在磁盤(pán)的基本結(jié)構(gòu)下建立的，文件系統(tǒng)讀取基本單位，就不是扇區(qū)，而是數(shù)據(jù)塊。

3.磁盤(pán)隨機(jī)訪問(wèn)(Random Access)與連續(xù)訪問(wèn)(Sequential Access)

• 隨機(jī)訪問(wèn)：本次IO所給出的扇區(qū)地址和上次IO給出扇區(qū)地址不連續(xù)，這樣的話磁頭在兩次IO操作之間需要作比較大的移動(dòng)動(dòng)作才能重新開(kāi)始讀/寫(xiě)數(shù)據(jù)。
• 連續(xù)訪問(wèn)：如果當(dāng)次IO給出的扇區(qū)地址與上次IO結(jié)束的扇區(qū)地址是連續(xù)的，那磁頭就能很快的開(kāi)始這次IO操作，這樣的多個(gè)IO操作稱為連續(xù)訪問(wèn)。

因此盡管相鄰的兩次IO操作在同一時(shí)刻發(fā)出，但如果它們的請(qǐng)求的扇區(qū)地址相差很大的話也只能稱為隨機(jī)訪問(wèn)，而非連續(xù)訪問(wèn)。
磁盤(pán)是通過(guò)機(jī)械運(yùn)動(dòng)進(jìn)行尋址的，隨機(jī)訪問(wèn)不需要過(guò)多的定位，故效率比較高。

三、MySQL 與磁盤(pán)交互基本單位

1.基本單位

MySQL 作為一款應(yīng)用軟件，可以想象成一種特殊的文件系統(tǒng)。它有著更高的IO場(chǎng)景，所以，為了提高基本的IO效率， MySQL 進(jìn)行IO的基本單位是16KB；

mysql> SHOW GLOBAL STATUS LIKE 'innodb_page_size';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| Innodb_page_size | 16384 | -- 16*1024=16384
+------------------+-------+
1 row in set (0.01 sec)

也就是說(shuō)，磁盤(pán)這個(gè)硬件設(shè)備的基本單位是512 字節(jié)，而MySQL InnoDB引擎使用16KB 進(jìn)行IO交互。
即MySQL 和磁盤(pán)進(jìn)行數(shù)據(jù)交互的基本單位是16KB。這個(gè)基本數(shù)據(jù)單元，在MySQL 這里叫做page（注意和系統(tǒng)的page區(qū)分）

• 實(shí)際上mysqld服務(wù)端是運(yùn)行在內(nèi)存中的一個(gè)進(jìn)程，其對(duì)數(shù)據(jù)的所有增刪查改（CURD），都是在內(nèi)存中進(jìn)行的，后期mysqld會(huì)進(jìn)行持久化；
• mysql處于應(yīng)用層，通過(guò)系統(tǒng)調(diào)用接口向操作系統(tǒng)發(fā)送文件操作申請(qǐng)，由操作系統(tǒng)讀取磁盤(pán)中的數(shù)據(jù)；
  
• 文件必須提前被打開(kāi)，也就是由SO讀取到內(nèi)存中，對(duì)文件內(nèi)容做任何操作，都不是在磁盤(pán)設(shè)備上操作的；
  任何磁盤(pán)數(shù)據(jù)，在進(jìn)程中要進(jìn)行操作，本質(zhì)都必須在內(nèi)存中進(jìn)行；
• 如果需要訪問(wèn)的數(shù)據(jù)此時(shí)不在內(nèi)存中，MySQL內(nèi)部有自己的內(nèi)存管理，會(huì)進(jìn)行換入、換出操作；
• MySQL在啟動(dòng)的時(shí)候，一定會(huì)預(yù)先申請(qǐng)一批內(nèi)存空間；

2.MySQL中的數(shù)據(jù)管理

• mysql page單位為16KB，Linux kernel page單位為4KB，磁盤(pán)扇區(qū)單位為512B，他們之間的數(shù)據(jù)讀取關(guān)系如下圖所示：
  
• MySQL 中的數(shù)據(jù)文件，是以page為單位保存在磁盤(pán)當(dāng)中的。
• MySQL 的CURD 操作，都需要通過(guò)計(jì)算，找到對(duì)應(yīng)的插入位置，或者找到對(duì)應(yīng)要修改或者查詢的數(shù)據(jù)。
• 而只要涉及計(jì)算，就需要CPU參與，而為了便于CPU參與，一定要能夠先將數(shù)據(jù)移動(dòng)到內(nèi)存當(dāng)中。
• 所以在特定時(shí)間內(nèi)，數(shù)據(jù)一定是磁盤(pán)中有，內(nèi)存中也有。后續(xù)操作完內(nèi)存數(shù)據(jù)之后，以特定的刷新策略，刷新到磁盤(pán)。而這時(shí)，就涉及到磁盤(pán)和內(nèi)存的數(shù)據(jù)交互，也就是IO了。而此時(shí)IO的基本單位就是Page。
• 為了更好的進(jìn)行上面的操作MySQL 服務(wù)器在內(nèi)存中運(yùn)行的時(shí)候，在服務(wù)器內(nèi)部，就申請(qǐng)了被稱為Buffer Pool的大內(nèi)存空間，來(lái)進(jìn)行各種緩存；其實(shí)就是很大的內(nèi)存空間，來(lái)和磁盤(pán)數(shù)據(jù)進(jìn)行IO交互。
• IO效率低下的最主要矛盾不是IO單次數(shù)據(jù)量的大小，而是IO的次數(shù)，因此為了更高的效率，一定要盡可能的減少系統(tǒng)和磁盤(pán)IO的次數(shù)；

五、索引的理解

1.索引案例

建立測(cè)試表：

create table if not exists user (
id int primary key, --一定要添加主鍵，只有這樣才會(huì)默認(rèn)生成主鍵索引
age int not null,
name varchar(16) not null
);
mysql> show create table user \G
*************************** 1. row ***************************
Table: user
Create Table: CREATE TABLE `user` (
`id` int(11) NOT NULL,
`age` int(11) NOT NULL,
`name` varchar(16) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 --默認(rèn)就是InnoDB存儲(chǔ)引擎
1 row in set (0.00 sec)

插入多條記錄：

mysql> insert into user (id, age, name) values(3, 18, '楊過(guò)');
Query OK, 1 row affected (0.01 sec)
mysql> insert into user (id, age, name) values(4, 16, '小龍女');
Query OK, 1 row affected (0.00 sec)
mysql> insert into user (id, age, name) values(2, 26, '黃蓉');
Query OK, 1 row affected (0.01 sec)
mysql> insert into user (id, age, name) values(5, 36, '郭靖');
Query OK, 1 row affected (0.00 sec)
mysql> insert into user (id, age, name) values(1, 56, '歐陽(yáng)鋒');
Query OK, 1 row affected (0.00 sec)

• 我們并沒(méi)有按照主鍵的大小順序插入；

查看插入結(jié)果：

mysql> select * from user; 
+----+-----+-----------+
| id | age | name |
+----+-----+-----------+
| 1 | 56 | 歐陽(yáng)鋒 |
| 2 | 26 | 黃蓉 |
| 3 | 18 | 楊過(guò) |
| 4 | 16 | 小龍女 |
| 5 | 36 | 郭靖 |
+----+-----+-----------+
5 rows in set (0.00 sec)

• 可以看出查詢結(jié)果是按照主鍵排好序的；

2.單頁(yè)mysql page

MySQL 中要管理很多數(shù)據(jù)表文件，而要管理好這些文件，就需要先描述，再組織，我們目前可以簡(jiǎn)單理解成一個(gè)個(gè)獨(dú)立文件是有一個(gè)或者多個(gè)Page構(gòu)成的；

• 單個(gè)page的結(jié)構(gòu)：
  
  一個(gè)page的單位是16KB，使用prev 和next 構(gòu)成雙向鏈表；
  因?yàn)橛兄麈I的問(wèn)題，MySQL 會(huì)默認(rèn)按照主鍵給我們的數(shù)據(jù)進(jìn)行排序，從上面的Page內(nèi)數(shù)據(jù)記錄可以看出，數(shù)據(jù)是有序且彼此關(guān)聯(lián)的。
  • 排序的目的：
    插入數(shù)據(jù)時(shí)排序的目的，就是優(yōu)化查詢的效率。頁(yè)內(nèi)部存放數(shù)據(jù)的模塊，實(shí)質(zhì)上也是一個(gè)鏈表的結(jié)構(gòu)，鏈表的特點(diǎn)也就是增刪快，查詢修改慢，所以優(yōu)化查詢的效率是必須的。
    正是因?yàn)橛行?#xff0c;在查找的時(shí)候，從頭到后都是有效查找，沒(méi)有任何一個(gè)查找是浪費(fèi)的。

3.管理多個(gè)mysql page

• 上面頁(yè)模式中，只有一個(gè)功能，就是在查詢某條數(shù)據(jù)的時(shí)候直接將一整頁(yè)的數(shù)據(jù)加載到內(nèi)存中，以減少硬盤(pán)IO次數(shù)，從而提高性能。但是，我們也可以看到，現(xiàn)在的頁(yè)模式內(nèi)部，實(shí)際上是采用了鏈表的結(jié)構(gòu)，前一條數(shù)據(jù)指向后一條數(shù)據(jù)，本質(zhì)上還是通過(guò)數(shù)據(jù)的逐條比較來(lái)取出特定的數(shù)據(jù)。
• 如果有1千萬(wàn)條數(shù)據(jù)，一定需要多個(gè)Page來(lái)保存1千萬(wàn)條數(shù)據(jù)，多個(gè)Page彼此使用雙鏈表鏈接起來(lái)，而且每個(gè)Page內(nèi)部的數(shù)據(jù)也是基于鏈表的。那么，查找特定一條記錄，也一定是線性查找，效率很低。
  

頁(yè)目錄

• 看書(shū)的時(shí)候，前面會(huì)有頁(yè)目錄，來(lái)指向每一個(gè)章節(jié)的起始頁(yè)碼；本質(zhì)上，書(shū)中的目錄，是多花了紙張的，但是卻提高了效率；
  所以，目錄，是一種“空間換時(shí)間的做法”；

單頁(yè)情況：

• 針對(duì)上面的單頁(yè)P(yáng)age，我們能否也可以引入目錄；
  
  當(dāng)前，在一個(gè)Page內(nèi)部，我們引入了目錄。比如，我們要查找id=4記錄，之前必須線性遍歷4次，才能拿到結(jié)果?，F(xiàn)在直接通過(guò)目錄2[3]，直接進(jìn)行定位新的起始位置，提高了效率。
  因此，MySQL 會(huì)通過(guò)鍵值自動(dòng)排序的原因就是：可以很方便引入目錄

多頁(yè)情況：
當(dāng)MySQL 中有多個(gè)頁(yè)來(lái)存儲(chǔ)數(shù)據(jù)，使用鏈表結(jié)構(gòu)管理這些page，在上面說(shuō)過(guò)效率是很低的，對(duì)于多頁(yè)結(jié)構(gòu)，同樣可以引入目錄對(duì)頁(yè)進(jìn)行管理：

• 使用一個(gè)目錄項(xiàng)來(lái)指向某一頁(yè)，而這個(gè)目錄項(xiàng)存放的就是將要指向的頁(yè)中存放的最小數(shù)據(jù)的鍵值。
• 和頁(yè)內(nèi)目錄不同的地方在于，這種目錄管理的級(jí)別是頁(yè)，而頁(yè)內(nèi)目錄管理的級(jí)別是行。
• 其中，每個(gè)目錄項(xiàng)的構(gòu)成是：鍵值+指針。
  
• 存在一個(gè)目錄頁(yè)來(lái)管理頁(yè)目錄，目錄頁(yè)中的數(shù)據(jù)存放的就是指向的那一頁(yè)中最小的數(shù)據(jù)。有數(shù)據(jù)，就可通過(guò)比較，找到該訪問(wèn)那個(gè)Page，進(jìn)而通過(guò)指針，找到下一個(gè)Page。
• 其實(shí)目錄頁(yè)的本質(zhì)也是頁(yè)，普通頁(yè)中存的數(shù)據(jù)是用戶數(shù)據(jù)，而目錄頁(yè)中存的數(shù)據(jù)是普通頁(yè)的地址。

但是每次檢索數(shù)據(jù)的時(shí)候，該從哪里開(kāi)始呢？雖然頂層的目錄頁(yè)少了，但是還要遍歷啊；

• 可以再增加目錄頁(yè)，對(duì)這些目錄頁(yè)進(jìn)行管理：
  
• 這種數(shù)據(jù)結(jié)構(gòu)就是B+樹(shù)；
• 選擇B+樹(shù)的原因：
  • 節(jié)點(diǎn)不存儲(chǔ)data，這樣一個(gè)節(jié)點(diǎn)就可以存儲(chǔ)更多的key?？梢允沟脴?shù)更矮，所以IO操作次數(shù)更少。
  • 葉子節(jié)點(diǎn)相連，更便于進(jìn)行范圍查找
• 具有主鍵的表，一表就是一顆B+樹(shù)；
• 沒(méi)有主鍵的表，目前我們認(rèn)為是所有的數(shù)據(jù)是線性組織的。(但是，如果表中沒(méi)有主鍵，mysql 會(huì)自動(dòng)形成隱藏主鍵)
• B+中所有的葉子節(jié)點(diǎn)路上節(jié)點(diǎn)，不需要全部加載到內(nèi)存，可以按需進(jìn)行l(wèi)oad mysql page；

Page分為目錄頁(yè)和數(shù)據(jù)頁(yè)。目錄頁(yè)只放各個(gè)下級(jí)Page的最小鍵值。
查找的時(shí)候，自定向下找，只需要加載部分目錄頁(yè)到內(nèi)存，即可完成算法的整個(gè)查找過(guò)程;
頁(yè)目錄里面，不存數(shù)據(jù)，16KB的空間全都可以保存目錄的映射，可以管理很多的下級(jí)page，會(huì)使整棵樹(shù)的結(jié)構(gòu)變得矮胖，有效減少了IO的次數(shù)；

4.B+樹(shù)和B樹(shù)

• B樹(shù)：
  

• B+樹(shù)：
  

B樹(shù)節(jié)點(diǎn)，既有數(shù)據(jù)，又有Page指針，而B(niǎo)+，只有葉子節(jié)點(diǎn)有數(shù)據(jù)，其他目錄頁(yè)，只有鍵值和Page指針；
B+葉子節(jié)點(diǎn)，全部相連，而B(niǎo)沒(méi)有；

選擇B+樹(shù)的原因：

1. 節(jié)點(diǎn)不存儲(chǔ)data，這樣一個(gè)節(jié)點(diǎn)就可以存儲(chǔ)更多的key；可以使得樹(shù)更矮，所以IO操作次數(shù)更少；
2. 葉子節(jié)點(diǎn)相連，更便于進(jìn)行范圍查找；

5.聚簇索引 VS 非聚簇索引

MyISAM 存儲(chǔ)引擎-主鍵索引；
MyISAM 引擎同樣使用B+樹(shù)作為索引結(jié)果，葉節(jié)點(diǎn)的data域存放的是數(shù)據(jù)記錄的地址。下圖為MyISAM表的主索引， Col1 為主鍵。

其中， MyISAM 最大的特點(diǎn)是，將索引Page和數(shù)據(jù)Page分離，也就是葉子節(jié)點(diǎn)沒(méi)有數(shù)據(jù)，只有對(duì)應(yīng)數(shù)據(jù)的地址。
相較于InnoDB 索引， InnoDB 是將索引和數(shù)據(jù)放在一起的。
可以從Linux中MySQL的文件目錄中看出：

兩種引擎的數(shù)據(jù)庫(kù)創(chuàng)建的文件數(shù)量不一致；

• MyISAM 這種用戶數(shù)據(jù)與索引數(shù)據(jù)分離的索引方案，叫做非聚簇索引；
• InnoDB 這種用戶數(shù)據(jù)與索引數(shù)據(jù)在一起索引方案，叫做聚簇索引；

當(dāng)然， MySQL 除了默認(rèn)會(huì)建立主鍵索引外，我們用戶也有可能建立按照其他列信息建立的索引，一般這
種索引可以叫做輔助（普通）索引。

• 對(duì)于MyISAM，建立輔助（普通）索引和主鍵索引沒(méi)有差別，無(wú)非就是主鍵不能重復(fù)，而非主鍵可重復(fù)。
  下圖就是基于MyISAM 的Col2 建立的索引，和主鍵索引沒(méi)有差別；
  
• InnoDB 除了主鍵索引，用戶也會(huì)建立輔助（普通）索引，我們以上表中的Col3 建立對(duì)應(yīng)的輔助索引如下圖：
  
  所以通過(guò)輔助（普通）索引，找到目標(biāo)記錄，需要兩遍索引：首先檢索輔助索引獲得主鍵，然后用主鍵到主索引中檢索獲得記錄。這種過(guò)程，就叫做回表查詢；
  為何InnoDB 針對(duì)這種輔助（普通）索引的場(chǎng)景，不給葉子節(jié)點(diǎn)也附上數(shù)據(jù)呢？原因就是太浪費(fèi)空間了。

六、索引操作

1.創(chuàng)建主鍵索引

• 第一種方式：
  在創(chuàng)建表的時(shí)候，直接在字段名后指定 primary key；

create table user1(id int primary key, name varchar(30));

• 第二種方式：
  在創(chuàng)建表的最后，指定某列或某幾列為主鍵索引；

create table user2(id int, name varchar(30), primary key(id));

• 第三種方式：
  創(chuàng)建表以后再添加主鍵；

create table user3(id int, name varchar(30));
alter table user3 add primary key(id);

主鍵索引的特點(diǎn)：

1. 一個(gè)表中，最多有一個(gè)主鍵索引，當(dāng)然可以使用復(fù)合主鍵；
2. 主鍵索引的效率高（主鍵不可重復(fù)）；
3. 創(chuàng)建主鍵索引的列，它的值不能為null，且不能重復(fù)；
4. 主鍵索引的列基本上是int；

2.唯一索引的創(chuàng)建

• 第一種方式：
  在表定義時(shí)，在某列后直接指定unique唯一屬性；

create table user4(id int primary key, name varchar(30) unique);

• 第二種方式：
  創(chuàng)建表時(shí)，在表的后面指定某列或某幾列為unique；

create table user5(id int primary key, name varchar(30), unique(name));

• 第三種方式：
  創(chuàng)建表以后再添加主鍵；

create table user6(id int primary key, name varchar(30)）；
alter table user6 add unique(name);

唯一索引的特點(diǎn)：

1. 一個(gè)表中，可以有多個(gè)唯一索引；
2. 查詢效率高；
3. 如果在某一列建立唯一索引，必須保證這列不能有重復(fù)數(shù)據(jù)；
4. 如果一個(gè)唯一索引上指定not null，等價(jià)于主鍵索引；

3.普通索引的創(chuàng)建

• 第一種方式：
  在表的定義最后，指定某列為索引；

create table user8(id int primary key,
name varchar(20),
email varchar(30),
index(name) --在表的定義最后，指定某列為索引
);

• 第二種方式：
  創(chuàng)建完表以后指定某列為普通索引；

create table user9(id int primary key, name varchar(20), email
varchar(30));
alter table user9 add index(name);

• 第三種方式：
  創(chuàng)建完表以后指定某列為普通索引， 可以給索引命名；

create table user10(id int primary key, name varchar(20), email
varchar(30));
-- 創(chuàng)建一個(gè)索引名為 idx_name 的索引
create index idx_name on user10(name);

這樣會(huì)重命名索引屬性的key_name屬性，如果沒(méi)有重命名，一般key_name和列名是一致的；

普通索引的特點(diǎn)：

1. 一個(gè)表中可以有多個(gè)普通索引，普通索引在實(shí)際開(kāi)發(fā)中用的比較多；
2. 如果某列需要?jiǎng)?chuàng)建索引，但是該列有重復(fù)的值，那么我們就應(yīng)該使用普通索引；

4.全文索引的創(chuàng)建

當(dāng)對(duì)文章字段或有大量文字的字段進(jìn)行檢索時(shí)，會(huì)使用到全文索引。MySQL提供全文索引機(jī)制，但是有要求，要求表的存儲(chǔ)引擎必須是MyISAM，而且默認(rèn)的全文索引支持英文，不支持中文。如果對(duì)中文進(jìn)行全文檢索，可以使用sphinx的中文版(coreseek)。

創(chuàng)建數(shù)據(jù)庫(kù)并插入數(shù)據(jù)，設(shè)置title和body的全文索引：

CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT (title,body) -- 指定全文索引
)engine=MyISAM;INSERT INTO articles (title,body) VALUES
('MySQL Tutorial','DBMS stands for DataBase ...'),
('How To Use MySQL Well','After you went through a ...'),
('Optimizing MySQL','In this tutorial we will show ...'),
('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
('MySQL vs. YourSQL','In the following database comparison ...'),
('MySQL Security','When configured properly, MySQL ...');

查詢有沒(méi)有database數(shù)據(jù)：

• 如果使用如下查詢方式，雖然查詢出數(shù)據(jù)，但是沒(méi)有使用到全文索引：
  
  可以用explain工具看一下，是否使用到索引；
  
• 使用全文索引：
  
  通過(guò)explain來(lái)分析這個(gè)sql語(yǔ)句：
  

5.查詢索引

• ==第一種方法： ==

show keys from 表名;

• ==第二種方法: ==

show index from 表名;

• 第三種方法（信息比較簡(jiǎn)略）：

desc 表名;

6.刪除索引

• 第一種方法 – 刪除主鍵索引：

alter table 表名 drop primary key;

• 第二種方法 – 其他索引的刪除：

alter table 表名 drop index 索引名;

索引名就是show keys from 表名中的 Key_name 字段;

• ==第三種方法： ==

drop index 索引名 on 表名;

7.索引創(chuàng)建原則

• 比較頻繁作為查詢條件的字段應(yīng)該創(chuàng)建索引;
• 唯一性太差的字段不適合單獨(dú)創(chuàng)建索引，即使頻繁作為查詢條件;
• 更新非常頻繁的字段不適合作創(chuàng)建索引;
• 不會(huì)出現(xiàn)在where子句中的字段不該創(chuàng)建索引;

8.復(fù)合索引

創(chuàng)建復(fù)合索引，同時(shí)用name和qq作為索引，但是只創(chuàng)建一顆B+樹(shù)；

查看復(fù)合索引屬性：

索引名都是myindex；

復(fù)合索引的好處是：

• 查詢條件是name，select想要取出的數(shù)據(jù)是qq，查到name后就直接返回qq就可以，不用像普通索引一樣，再去主鍵的B+樹(shù)中再次查找qq，這就是索引覆蓋；

最左匹配原則：

• 復(fù)合索引查詢的時(shí)候，一般是按照從左向右匹配的；