索引是用來快速檢索出具有特定值的記錄腋逆。如果沒有索引，數(shù)據(jù)庫就必須從第一條記錄開始進(jìn)行全表掃描侈贷，直到找出相關(guān)的行惩歉。數(shù)據(jù)越多，檢索的代價就越高俏蛮，檢索時如果表的列存在索引撑蚌，那么MySQL就能快速到達(dá)指定位置去搜索數(shù)據(jù)文件，而不必查看所有數(shù)據(jù)嫁蛇。

概述

索引依托于存儲引擎的實(shí)現(xiàn)锨并，因此，每種存儲引擎的索引都不一定完全相同睬棚，并且每種存儲引擎也不一定支持所有索引類型第煮。所有存儲引擎支持每個表至少16個索引，總索引長度至少為256字節(jié)抑党。大多數(shù)存儲引擎有更高的額限制包警。

MySQL中索引的存儲類型有兩種：BTREE和HASH，具體和表的存儲引擎相關(guān)底靠；

MyISAM和InnoDB存儲引擎只支持BTREE索引害晦，MEMORY/HEAP存儲引擎可以支持HASH和BTREE索引。

優(yōu)點(diǎn)

加快數(shù)據(jù)的查詢速度

唯一索引，可以保證數(shù)據(jù)庫表中每一行數(shù)據(jù)的唯一性

在實(shí)現(xiàn)數(shù)據(jù)的參考完整性方面壹瘟，可以加速表和表之間的連接

在使用分組和排序子句進(jìn)行數(shù)據(jù)查詢時鲫剿，也可以顯著減少查詢中分組和排序的時間

缺點(diǎn)

占用磁盤空間，除了數(shù)據(jù)表占數(shù)據(jù)空間之外稻轨，每一個索引還要占一定的物理空間灵莲，如果有大量的索引，索引文件可能比數(shù)據(jù)文件更快達(dá)到最大文件尺寸（合理運(yùn)用殴俱，問題不大）

損耗性能（添加政冻、修改、刪除） 索引需要動態(tài)地維護(hù)

分類

普通索引和唯一索引

普通索引： 數(shù)據(jù)庫中的基本索引類型线欲，允許在定義索引的列中插入重復(fù)值和空值

唯一索引：索引列的值必須唯一明场，但允許有空值，主鍵索引是一種特殊的唯一索引李丰，不允許有空值（比如自增ID）

單列索引和組合索引

單列索引： 即一個索引只包含單個列苦锨，一個表可以有多個單列索引

組合索引： 指在表的多個字段組合上創(chuàng)建的索引，只有在查詢條件中使用了這些字段的左邊字段時嫌套，索引才會被使用

全文索引

全文索引： 類型為FULLTEXT逆屡，在定義索引的列上支持值的全文查找圾旨，允許在這些索引列中插入重復(fù)值和空值踱讨。全文索引可以在CHAR、VARCHAR或者TEXT類型的列上創(chuàng)建砍的，MySQL中只有MyISAM存儲引擎支持全文索引

設(shè)計原則

索引設(shè)計不合理或者缺少索引都會對數(shù)據(jù)庫和應(yīng)用程序的性能造成障礙痹筛，高效的索引對于獲得良好的性能非常重要。

注意事項

索引并非越多越好廓鞠，一個表中如有大量的索引帚稠，不僅占用磁盤空間，而且會影響INSERT床佳、DELETE滋早、UPDATE等語句的性能，因?yàn)楫?dāng)表中的數(shù)據(jù)更改的同時砌们，索引也會進(jìn)行調(diào)整和更新

避免對經(jīng)常更新的表設(shè)計過多的索引杆麸，并且索引中的列盡可能要少，而對經(jīng)常用于查詢的字段應(yīng)該創(chuàng)建索引浪感，但要避免添加不必要的字段

數(shù)據(jù)量小的表最好不要使用索引昔头，由于數(shù)據(jù)較少，查詢花費(fèi)的時間可能比遍歷索引時間還要短影兽，索引可能不會產(chǎn)生優(yōu)化效果

在條件表達(dá)式中經(jīng)常用到的不同值較多的列上建立索引揭斧，在不同值較少的列上不要建立索引，比如性別字段只有男和女峻堰，就沒必要建立索引讹开。如果建立索引不但不會提高查詢效率盅视，反而會嚴(yán)重降低更新速度

當(dāng)唯一性是某種數(shù)據(jù)本身的特征時，指定唯一索引旦万。使用唯一索引需能確保定義的列的數(shù)據(jù)完整性左冬，以提高查詢速度

在頻繁排序或分組（即group by或order by操作）的列上建立索引，如果待排序的列有多個纸型，可以在這些列上建立組合索引

使用

使用 CREATE TABLE 創(chuàng)建表的時候拇砰，除了可以定義列的數(shù)據(jù)類型，還可以定義主鍵約束狰腌、外鍵約束或者唯一性約束除破，而不論創(chuàng)建哪種約束，在定義約束的同時相當(dāng)于在指定列上創(chuàng)建了一個索引琼腔。

創(chuàng)建表時創(chuàng)建索引的基本語法如下：

CREATE TABLE table_name[col_name data_type][UNIQUE|FULLTEXT|SPATIAL][INDEX|KEY][index_name](col_name[length])[ASC|DESC]

釋義

UNIQUE瑰枫、FULLTEXT和SPATIAL為可選參數(shù)，分別表示唯一索引丹莲、全文索引和空間索引

INDEX和KEY為同義詞光坝，二者作用相同，用來指定創(chuàng)建索引

col_name為需要創(chuàng)建索引的字段列甥材，該列必須從數(shù)據(jù)表中該定義的多個列中選擇

index_name為指定索引的名稱盯另，為可選參數(shù)，如果不指定則MySQL默認(rèn)col_name為索引值

length為可選參數(shù)洲赵，表示索引的長度鸳惯，只有字符串類型的字段才能指定索引長度

ASC或DESC指定升序或者降序的索引值存儲

普通索引

-- 這句作用是,如果 customer1 存在就刪除DROP TABLE IF EXISTS customer1;CREATE TABLE `customer1` ( `customer_id` bigint(20) NOT NULL COMMENT '客戶ID', `customer_name` varchar(30) DEFAULT NULL COMMENT '客戶姓名', INDEX `idx_customer_id` (`customer_id`) USING BTREE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='客戶表';

測試

-- 查看當(dāng)前表的索引情況SHOW INDEX FROM customer1;-- 使用 EXPLAIN 分析 SQL語句 是否使用了索引EXPLAIN SELECT * FROM customer1 WHERE customer_id = 1;

釋義

EXPLAIN 語法下章會詳細(xì)講解,本章重心是索引

select_type： 指定所使用的SELECT查詢類型，這里值為SIMPLE叠萍，表示簡單的SELECT芝发，不使用UNION或者子查詢。其他取值有PRIMARY苛谷、UNION辅鲸、SUBQUERY、等

table： 指定數(shù)據(jù)庫讀取的數(shù)據(jù)表的名字腹殿，它們按照被讀取的先后順序排列

type： 指定了本數(shù)據(jù)表與其他數(shù)據(jù)表之間的關(guān)聯(lián)關(guān)系独悴，其它取值有system、const赫蛇、eq_ref绵患、ref、range悟耘、index和All

possible_keys： MySQL在搜索數(shù)據(jù)記錄時可選用的各個索引

key： MySQL使用的實(shí)際索引

key_len： 給出了索引按字節(jié)計算的長度落蝙，key_len數(shù)值越小，表示越快

ref： 提供了關(guān)聯(lián)關(guān)系中另外一個數(shù)據(jù)表里的數(shù)據(jù)列的名字

rows： 指MySQL執(zhí)行查詢時預(yù)計從當(dāng)前數(shù)據(jù)表中讀出的數(shù)據(jù)行數(shù)

Extra： 提供了與關(guān)聯(lián)操作有關(guān)的信息

SHOW INDEX FROM 語法

table： 表示創(chuàng)建索引的表

Non_unique： 表示索引不是一個唯一索引，1表示非唯一索引筏勒，0表示唯一索引

Key_name： 表示索引的名稱

Seq_in_index： 表示該字段在索引中的位置移迫，單列索引改值該值為1，組合索引為每個字段在索引中定義的順序

Column_name： 表示定義索引的列字段

Sub_part： 表示索引的長度

Null： 表示該字段是否能為空值

Index_type： 表示索引類型

當(dāng)?possible_keys?與?key?都為?idx_customer_id管行，說明查詢時使用了索引

唯一索引

單列索引是在數(shù)據(jù)表中的某一個字段上創(chuàng)建的索引厨埋，一個表中可以創(chuàng)建多個單列索引，前面兩個例子中創(chuàng)建的索引都是單列索引捐顷，比如：

DROP TABLEIF EXISTS customer1;CREATE TABLE `customer1` ( `customer_id` BIGINT (20) NOT NULL COMMENT '客戶ID', `customer_name` VARCHAR (30) DEFAULT NULL COMMENT '客戶姓名', UNIQUE INDEX `idx_customer_id` (`customer_id`) USING BTREE) ENGINE = INNODB DEFAULT CHARSET = utf8mb4 COMMENT = '客戶表';

這樣就代表在表的customer_id字段上創(chuàng)建了一個名為idx_customer_id的唯一索引

組合索引

組合索引是在多個字段上創(chuàng)建一個索引荡陷，比如：

DROP TABLEIF EXISTS customer1;CREATE TABLE `customer1` ( `customer_id` BIGINT (20) NOT NULL COMMENT '客戶ID', `customer_name` VARCHAR (30) DEFAULT NULL COMMENT '客戶姓名', INDEX `idx_group_customer` (`customer_id`,`customer_name`) USING BTREE) ENGINE = INNODB DEFAULT CHARSET = utf8mb4 COMMENT = '客戶表';SHOW INDEX FROM customer1;

這就為customer_id、customer_name兩個字段成功創(chuàng)建了一個名為idx_group_customer的組合索引迅涮，通過SHOW INDEX FROM customer1;?將會看到兩條記錄（附圖）

全文索引

全文索引可以對全文進(jìn)行搜索废赞，只有MyISAM存儲引擎支持全文索引，并且只為CHAR叮姑、VARCHAR和TEXT列唉地，索引總是對整個列進(jìn)行，不支持局部索引传透，比如：

DROP TABLEIF EXISTS customer1;CREATE TABLE `customer1` ( `customer_id` BIGINT (20) NOT NULL COMMENT '客戶ID', `customer_name` VARCHAR (255) DEFAULT NULL COMMENT '客戶姓名', FULLTEXT INDEX `idx_fulltext_customer_name` (`customer_name`)) ENGINE = MyISAM DEFAULT CHARSET = utf8mb4 COMMENT = '客戶表';SHOW INDEX FROM customer1;

因?yàn)槟J(rèn)的存儲引擎為InnoDB耘沼，而全文索引只支持MyISAM，所以這里創(chuàng)建表的時候要手動指定一下引擎朱盐。

看到這么創(chuàng)建群嗤，就在info字段上成功建立了一個名為idx_fulltext_customer_name的FULLTEXT全文索引，全文索引非常適合大型數(shù)據(jù)庫托享，而對于小的數(shù)據(jù)集骚烧，它的用處可能比較小

在已經(jīng)存在的表上創(chuàng)建索引

在已經(jīng)存在的表上創(chuàng)建索引，可以使用ALTER TABLE語句或者CREATE INDEX語句闰围，所以，分別講解一下如何使用ALTER TABLE和CREATE INDEX語句在已知的表字段上創(chuàng)建索引既峡。

ALTER TABLE 語法

ALTER TABLE創(chuàng)建索引的基本語法為：

ALTER TABLE table_name ADD [UNIQUE|FUUTEXT|SPATIAL][INDEX|KEY] [index_name] (col_name[length],...) [ASC|DESC]

普通索引

ALTER TABLE customer1 ADD INDEX idx_customer_id(`customer_id`);ALTER TABLE customer1 ADD INDEX idx_customer_id(customer_name(50));

意思是查詢的時候羡榴，只需要檢索前面50個字符。這里專門提一下运敢，對字符串類型的字段進(jìn)行索引校仑，如果可以盡可能的指定一個前綴長度，例如传惠，一個CHAR(255)的列迄沫，如果在前10個或者前30個字符內(nèi)，多數(shù)值是唯一的卦方，則不需要對整個列進(jìn)行索引羊瘩，短索引不僅可以提高查詢速度而且可以節(jié)省磁盤空間、減少I/O操作。

唯一索引

ALTER TABLE customer1 ADD UNIQUE INDEX `idx_customer_id` (`customer_id`);

組合索引

ALTER TABLE customer1 ADD INDEX `idx_group_customer` (`customer_id`,`customer_name`);

CREATE TABLE 語法

CREATE INDEX語句可以在已經(jīng)存在的表上添加索引尘吗，MySQL中CREATE INDEX被映射到一個ALTER TABLE語句上逝她，基本語法結(jié)構(gòu)為：

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name ON table_name(col_name[length],...)[ASC|DESC]

看到和ALTER INDEX語句的語法基本一樣，下面把?customer1?表刪除了再創(chuàng)建睬捶，所有字段都沒有索引黔宛，用CREATE INDEX語句創(chuàng)建一次索引：

CREATE INDEX idx_customer_id ON customer1(`customer_id`);CREATE UNIQUE INDEX idx_customer_id ON customer1(`customer_id`);CREATE INDEX idx_group_customer ON customer1(`customer_id`,`customer_name`);

刪除索引

最后一項工作就是刪除索引了，可以使用ALTER TABLE和DROP INDEX刪除索引擒贸。

ALTER TABLE 語法

ALTER TABLE的基本語法為：

ALTER TABLE table_name DROP EXISTS index_name;ALTER TABLE table_name DROP INDEX IF EXISTS index_name;

建議大家使用第二條

DROP INDEX 語法

DROP INDEX的基本語法為：

DROP INDEX index_name ON table_nameDROP INDEX IF EXISTS index_name ON table_name

建議大家使用第二條

注意一個細(xì)節(jié)臀晃，刪除表中的列時，如果要刪除的列為整個索引的組成部分介劫，則該列也會從索引中刪除积仗；如果組成索引的所有列都被刪除，則整個索引將被刪除

架構(gòu)群：725633148?