什么是索引

我們需要知道索引其實(shí)是一種數(shù)據(jù)結(jié)構(gòu)，其功能是幫助我們快速匹配查找到需要的數(shù)據(jù)行，是數(shù)據(jù)庫(kù)性能優(yōu)化最常用的工具之一。其作用相當(dāng)于超市里的導(dǎo)購(gòu)員、書(shū)本里的目錄。

索引類(lèi)型

主鍵索引:
主鍵是一種唯一性索引，但它必須指定為PRIMARY KEY，每個(gè)表只能有一個(gè)主鍵。

alert table tablename add primary key (字段名)

唯一索引:
索引列的所有值都只能出現(xiàn)一次，即必須唯一，值可以為空。

alter table table_name add primary key (字段名);

普通索引 :
基本的索引類(lèi)型，值可以為空，沒(méi)有唯一性的限制。

alter table table_name add index (字段名);

全文索引:
全文索引的索引類(lèi)型為FULLTEXT。全文索引可以在varchar、char、text類(lèi)型的列上創(chuàng)建?？梢酝ㄟ^(guò)ALTER TABLE或CREATE INDEX命令創(chuàng)建。對(duì)于大規(guī)模的數(shù)據(jù)集，通過(guò)ALTER TABLE（或者CREATE INDEX）命令創(chuàng)建全文索引要比把記錄插入帶有全文索引的空表更快。MyISAM支持全文索引，InnoDB在mysql5.6之后支持了全文索引。

alter table 表名 add FULLTEXT(字段名);

查看表的所有索引和刪除

 #查看:
show indexes from `表名`;
或
show keys from `表名`;
#刪除
alter table `表名` drop index 索引名;

索引的機(jī)制

1.為什么我們添加完索引后查詢(xún)速度為變快？

• 傳統(tǒng)的查詢(xún)方法，是按照表的順序遍歷的，不論查詢(xún)幾條數(shù)據(jù)，mysql需要將表的數(shù)據(jù)從頭到尾遍歷一遍
• 在我們添加完索引之后，mysql一般通過(guò)BTREE算法生成一個(gè)索引文件，在查詢(xún)數(shù)據(jù)庫(kù)時(shí)，找到索引文件進(jìn)行遍歷(折半查找大幅查詢(xún)效率)，找到相應(yīng)的鍵從而獲取數(shù)據(jù)

2.索引的代價(jià)

• 創(chuàng)建索引是為產(chǎn)生索引文件的，占用磁盤(pán)空間
• 索引文件是一個(gè)二叉樹(shù)類(lèi)型的文件，可想而知我們的dml操作同樣也會(huì)對(duì)索引文件進(jìn)行修改，所以性能會(huì)下降

3.在哪些column上使用索引？

• 較頻繁的作為查詢(xún)條件字段應(yīng)該創(chuàng)建索引
• 唯一性太差的字段不適合創(chuàng)建索引，盡管頻繁作為查詢(xún)條件，例如gender性別字段
• 新非常頻繁的字段不適合作為索引
• 不會(huì)出現(xiàn)在where子句中的字段不該創(chuàng)建索引

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

MySQL中索引的存儲(chǔ)類(lèi)型有兩種：BTREE和HASH，具體和表的存儲(chǔ)引擎相關(guān)；
MyISAM和InnoDB存儲(chǔ)引擎只支持BTREE索引，MEMORY/HEAP存儲(chǔ)引擎可以支持HASH和BTREE索引。

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

• 加快數(shù)據(jù)的查詢(xún)速度
• 唯一索引，可以保證數(shù)據(jù)庫(kù)表中每一行數(shù)據(jù)的唯一性
• 在實(shí)現(xiàn)數(shù)據(jù)的參考完整性方面，可以加速表和表之間的連接
• 在使用分組和排序子句進(jìn)行數(shù)據(jù)查詢(xún)時(shí)，也可以顯著減少查詢(xún)中分組和排序的時(shí)間

缺點(diǎn)

• 占用磁盤(pán)空間，除了數(shù)據(jù)表占數(shù)據(jù)空間之外，每一個(gè)索引還要占一定的物理空間，如果有大量的索引，索引文件可能比數(shù)據(jù)文件更快達(dá)到最大文件尺寸（合理運(yùn)用，問(wèn)題不大）
• 損耗性能（添加、修改、刪除） 索引需要?jiǎng)討B(tài)地維護(hù)

注意事項(xiàng)

• 索引并非越多越好，一個(gè)表中如有大量的索引，不僅占用磁盤(pán)空間，而且會(huì)影響INSERT、DELETE、UPDATE等語(yǔ)句的性能，因?yàn)楫?dāng)表中的數(shù)據(jù)更改的同時(shí)，索引也會(huì)進(jìn)行調(diào)整和更新
• 避免對(duì)經(jīng)常更新的表設(shè)計(jì)過(guò)多的索引，并且索引中的列盡可能要少，而對(duì)經(jīng)常用于查詢(xún)的字 段應(yīng)該創(chuàng)建索引，但要避免添加不必要的字段
• 數(shù)據(jù)量小的表最好不要使用索引，由于數(shù)據(jù)較少，查詢(xún)花費(fèi)的時(shí)間可能比遍歷索引時(shí)間還要短，索引可能不會(huì)產(chǎn)生優(yōu)化效果
• 在條件表達(dá)式中經(jīng)常用到的不同值較多的列上建立索引，在不同值較少的列上不要建立索引，比如性別字段只有男和女，就沒(méi)必要建立索引。如果建立索引不但不會(huì)提高查詢(xún)效率，反而會(huì)嚴(yán)重降低更新速度
• 當(dāng)唯一性是某種數(shù)據(jù)本身的特征時(shí)，指定唯一索引。使用唯一索引需能確保定義的列的數(shù)據(jù)完整性，以提高查詢(xún)速度
• 在頻繁排序或分組（即group by或order by操作）的列上建立索引，如果待排序的列有多個(gè)，可以在這些列上建立組合索引

實(shí)例

使用 CREATE TABLE 創(chuàng)建表的時(shí)候，除了可以定義列的數(shù)據(jù)類(lèi)型，還可以定義主鍵約束、外鍵約束或者唯一性約束，而不論創(chuàng)建哪種約束，在定義約束的同時(shí)相當(dāng)于在指定列上創(chuàng)建了一個(gè)索引。
創(chuàng)建表時(shí)創(chuàng)建索引的基本語(yǔ)法如下：

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為同義詞，二者作用相同，用來(lái)指定創(chuàng)建索引
• col_name為需要?jiǎng)?chuàng)建索引的字段列，該列必須從數(shù)據(jù)表中該定義的多個(gè)列中選擇
• index_name為指定索引的名稱(chēng)，為可選參數(shù)，如果不指定則MySQL默認(rèn)col_name為索引值
• length為可選參數(shù)，表示索引的長(zhǎng)度，只有字符串類(lèi)型的字段才能指定索引長(zhǎng)度
• ASC或DESC指定升序或者降序的索引值存儲(chǔ)

普通索引

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

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

DROP TABLE
IF EXISTS customer1;

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

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

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

DROP TABLE
IF EXISTS customer1;

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

SHOW INDEX FROM customer1;

這就為customer_id、customer_name兩個(gè)字段成功創(chuàng)建了一個(gè)名為idx_group_customer的組合索引，通過(guò)SHOW INDEX FROM customer1; 將會(huì)看到兩條記錄

全文索引
全文索引可以對(duì)全文進(jìn)行搜索，只有MyISAM存儲(chǔ)引擎支持全文索引，并且只為CHAR、VARCHAR和TEXT列，索引總是對(duì)整個(gè)列進(jìn)行，不支持局部索引，比如：

DROP TABLE
IF EXISTS customer1;

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

SHOW INDEX FROM customer1;

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

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

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

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

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));

意思是查詢(xún)的時(shí)候，只需要檢索前面50個(gè)字符。這里專(zhuān)門(mén)提一下，對(duì)字符串類(lèi)型的字段進(jìn)行索引，如果可以盡可能的指定一個(gè)前綴長(zhǎng)度，例如，一個(gè)CHAR(255)的列，如果在前10個(gè)或者前30個(gè)字符內(nèi)，多數(shù)值是唯一的，則不需要對(duì)整個(gè)列進(jìn)行索引，短索引不僅可以提高查詢(xún)速度而且可以節(jié)省磁盤(pán)空間、減少I(mǎ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`);

刪除索引
最后一項(xiàng)工作就是刪除索引了，可以使用ALTER TABLE和DROP INDEX刪除索引。

ALTER TABLE 語(yǔ)法
ALTER TABLE的基本語(yǔ)法為：

ALTER TABLE table_name DROP EXISTS index_name;

ALTER TABLE table_name DROP INDEX IF EXISTS index_name;

DROP INDEX 語(yǔ)法
DROP INDEX的基本語(yǔ)法為：

DROP INDEX index_name ON table_name

DROP INDEX IF EXISTS  index_name ON table_name