Mysql索引必知必會(huì)！

什么是索引

「索引用來(lái)排序數(shù)據(jù)以加快搜索和排序操作的速度」。主鍵數(shù)據(jù)總是排序的， 因此，按主鍵檢索特定行總是一種快速有效的操作。但是，搜索其他列中的值通常效率不高。這時(shí)候我們可以使用索引，索引就是根據(jù)表中的一列或若干列按照一定順序建立的列值與記錄行之間的對(duì)應(yīng)關(guān)系表，實(shí)質(zhì)上是一張描述索引列的列值與原表中記錄行之間一一對(duì)應(yīng)關(guān)系的有序表。

「索引特點(diǎn)：」

• 索引提高檢索的性能，但降低了數(shù)據(jù)增刪改的性能。在執(zhí)行這些操作時(shí)，DBMS 必須動(dòng)態(tài)地更新索引。
• 索引數(shù)據(jù)可能要占用大量的存儲(chǔ)空間。
• 并非所有數(shù)據(jù)都適合做索引。取值不多的數(shù)據(jù)（如地區(qū)）不如具有更多可能值的數(shù)據(jù)（如姓名），能夠更加體現(xiàn)索引的價(jià)值。
• 索引用于數(shù)據(jù)過(guò)濾和數(shù)據(jù)排序。如果你經(jīng)常以某種特定的順序排序數(shù)據(jù)，則該數(shù)據(jù)可能適合做索引。
• 可以在索引中定義多個(gè)列（例如，國(guó)家 + 城市）。

普通索引

在創(chuàng)建表的時(shí)創(chuàng)建普通索引。

DROP?TABLE?IF?EXISTS?student;
CREATE?TABLE?student
(????
??id?INT(11),
??stu_name?VARCHAR(10)
)

直接創(chuàng)建。

CREATE?INDEX?stu_id?ON?student(id);

修改表時(shí)創(chuàng)建。

ALTER?TABLE?student?ADD?INDEX?stu_id(id);

刪除索引。

DROP?INDEX?stu_id?ON?student;

唯一索引

唯一索引列值必須唯一，但允許有空值（注意和主鍵不同）。如果是組合索引，則列值的組合必須唯一。事實(shí)上，在許多場(chǎng)合，創(chuàng)建唯一索引的目的往往不是提高訪問(wèn)速度，而是為了避免數(shù)據(jù)出現(xiàn)重復(fù)。

CREATE?UNIQUE?INDEX?stu_id?ON?student(id);

全局索引

全文索引只能作用在 CHAR、VARCHAR、TEXT、類型的字段上。創(chuàng)建全文索引需要使用 FULLTEXT 參數(shù)進(jìn)行約束。

CREATE?FULLTEXT?INDEX?s_name?ON?student(stu_name);

多列索引

多列索引，即在數(shù)據(jù)表的多個(gè)字段上創(chuàng)建索引。

CREATE?TABLE?student
(????
??id?INT(11),
??stu_name?VARCHAR(10),
??email?VARCHAR(20),
??INDEX?info(stu_name,?email)
);

在多列索引中，只有查詢條件中使用了這些字段中的第一個(gè)字段(即上面示例中的 stu_name 字段)，索引才會(huì)被使用（「最左前綴’原則」）。如果沒(méi)有用到第一字段，則索引不起任何作用。

--?使用索引
SELECT?*?FROM?student?WHERE?stu_name?=?'張三';
SELECT?*?FROM?student?WHERE?stu_name?=?'李四'?AND?email?=?'[email protected]';
--?未使用索引
SELECT?*?FROM?student?WHERE?email?=?'[email protected]';