快捷導(dǎo)航

MySQL中索引的創(chuàng)建及刪除方法

更新時(shí)間：2024年08月07日 08:50:03 作者：一只特立獨(dú)行的豬611

MySQL中的索引是一種特殊的數(shù)據(jù)結(jié)構(gòu),它的主要目的是為了加快數(shù)據(jù)的檢索速度,下面這篇文章主要給大家介紹了關(guān)于MySQL中索引的創(chuàng)建及刪除的相關(guān)資料,文中通過代碼介紹的非常詳細(xì),需要的朋友可以參考下

1.1 索引的分類

MySQL的索引包括普通索引、唯一性索引、全文索引、單列索引、多列索引和空間索引等。

從功能邏輯按照上說，索引主要有 4 種：普通索引、唯一索引、主鍵索引、全文索引。
按照物理實(shí)現(xiàn)方式，索引可以分為 2 種：聚簇索引和非聚簇索引。
按照作用字段個(gè)數(shù) 進(jìn)行劃分，分成單列索引和聯(lián)合索引。

注意：不同的存儲(chǔ)引擎支持的索引類型也不一樣

InnoDB：支持 B-tree、Full-text 等索引，不支持 Hash 索引；
MyISAM：支持 B-tree、Full-text 等索引，不支持 Hash 索引；
Memory：支持 B-tree、Hash 等索引，不支持 Full-text 索引；
NDB ：支持 Hash 索引，不支持 B-tree、Full-text 等索引；
Archive：不支持 B-tree、Hash、Full-text 等索引;

1.2 創(chuàng)建索引

1、創(chuàng)建表的時(shí)候創(chuàng)建索引

隱式的索引創(chuàng)建：

# 1.隱式的添加索引(在添加有主鍵約束、唯一性約束或者外鍵約束的字段會(huì)自動(dòng)的創(chuàng)建索引)
CREATE TABLE dept(
    dept_id INT PRIMARY KEY AUTO_INCREMENT,# 創(chuàng)建主鍵索引
    dept_name VARCHAR(20)
);
CREATE TABLE emp(
    emp_id INT PRIMARY KEY AUTO_INCREMENT,# 主鍵索引
    emp_name VARCHAR(20) UNIQUE,# 唯一索引
    dept_id INT,
    CONSTRAINT emp_dept_id_fk FOREIGN KEY(dept_id) REFERENCES dept(dept_id)
); # 外鍵索引

顯式的索引創(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)建索引;
index_name 指定索引的名稱，為可選參數(shù)，如果不指定，那么 MySQL 默認(rèn) col_name 為索引名;
col_name為需要?jiǎng)?chuàng)建索引的字段列，該列必須從數(shù)據(jù)表中定義的多個(gè)列中選擇;
length 為可選參數(shù)，表示索引的長度，只有字符串類型的字段才能指定索引長度;
ASC 或 DESC 指定升序或者降序的索引值存儲(chǔ)。
特例：主鍵索引使用主鍵約束的方式來創(chuàng)建。

1、創(chuàng)建普通索引

在book表中的year_publication字段上建立普通索引，SQL語句如下:

# ①創(chuàng)建普通的索引
CREATE TABLE book(
    book_id INT ,
    book_name VARCHAR(100),
    AUTHORS VARCHAR(100),
    info VARCHAR(100) ,
    COMMENT VARCHAR(100),
    year_publication YEAR,
    # 聲明索引
    INDEX idx_bname(book_name)
);

通過命令查看索引有沒有創(chuàng)建成功

# 方式1：
SHOW CREATE TABLE book; # Linux下添加\G參數(shù)

# 方式2：
SHOW INDEX FROM book;

#性能分析工具：EXPLAIN，查看索引是否正在使用
EXPLAIN SELECT * from book where book_name = 'mysql高級(jí)';

EXPLAIN語句輸出結(jié)果的各個(gè)行我們?cè)谙乱徽轮v解，這里主要關(guān)注兩個(gè)字段

possible_keys行給出了MySQL在搜索數(shù)據(jù)記錄時(shí)可選用的各個(gè)索引
key行時(shí)MySQL實(shí)際選用的索引

可以看到，possible_keys和key值都為idx_bname，查詢時(shí)使用了索引

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

# ②創(chuàng)建唯一索引
CREATE TABLE book1 (
  book_id INT,
  book_name VARCHAR (100),
  AUTHORS VARCHAR (100),
  info VARCHAR (100),
  COMMENT VARCHAR (100),
  year_publication YEAR,
  #聲明索引
  UNIQUE INDEX uk_idx_cmt (COMMENT)
);

show INDEX from book1;# 查看索引

3、主鍵索引

設(shè)定為主鍵后數(shù)據(jù)庫會(huì)自動(dòng)建立索引，innodb為聚簇索引，語法:

隨表一起建索引:

# ③主鍵索引
# 通過定義主鍵約束的方式定義主鍵索引
create table book2(
    book_id int primary key,
    book_name varchar(100),
    AUTHORS VARCHAR (100),
    info VARCHAR (100),
    COMMENT VARCHAR (100),
    year_publication YEAR
) ;

刪除主鍵索引:

# 通過刪除主鍵約束的方式刪除主鍵索引
alter table book2
drop primary key;

修改主鍵索引：必須先刪除掉（drop）原索引，再新建（add）索引

4、創(chuàng)建單列索引

#④ 創(chuàng)建單列索引
CREATE TABLE book3(
    book_id INT,
    book_name VARCHAR(100),
    AUTHORS VARCHAR (100),
    info VARCHAR (100),
    COMMENT VARCHAR (100),
    year_publication YEAR,
    UNIQUE INDEX idx_bname(book_name)
);

show index from book3;

5、創(chuàng)建組合索引

舉例：創(chuàng)建表 book4，在表中的 book_id、book_name和 info字段上建立組合索引，SQL 語句如下：

# ⑤ 創(chuàng)建聯(lián)合索引
create table book4(
    book_id INT,
    book_name VARCHAR(100),
    AUTHORS VARCHAR (100),
    info VARCHAR (100),
    COMMENT VARCHAR (100),
    year_publication YEAR,
    index mul_bid_bname_info(book_id,book_name,info)	
)

SHOW INDEX FROM book4;

注意上面三行依次是book_id,book_name,info，與我們創(chuàng)建索引時(shí)指定的順序是嚴(yán)格對(duì)應(yīng)的。在查詢時(shí)會(huì)遵守最左索引原則，先進(jìn)行book_id條件的比較，然后再進(jìn)行book_name比較，最后才是info。因此注意把最常用的查詢字段放在索引的最左邊。

# 分析
explain select * from book4 where book_id = 1001 and book_name = 'mysql'; # 會(huì)使用到mul_bid_bname_info索引

explain select * from book4 where book_name = 'mysql';# 不會(huì)使用到mul_bid_bname_info索引

6、創(chuàng)建全文索引

FULLTEXT全文索引可以用于全文搜索，并且只為CHAR、VARCHAR和TEXT列創(chuàng)建索引。索引總是對(duì)整個(gè)列進(jìn)行，不支持局部（前綴）索引。

舉例1：創(chuàng)建表test4，在表中的 info 字段上建立全文索引，SQL 語句如下:

CREATE TABLE test4(
    id INT NOT NULL,
    name CHAR(30) NOT NULL,
    age INT NOT NULL,
    info VARCHAR(255),
    FULLTEXT INDEX futxt_idx_info(info)
) ENGINE=MyISAM;

在 MySQL 5.7 及之后版本中可以不指定最后的 ENGINE 了，因?yàn)樵诖税姹局?InnoDB 支持全文索引。

語句執(zhí)行完畢后，用SHOW CREATE TABLE查看表結(jié)構(gòu)：

SHOW INDEX FROM test4\G;

由結(jié)果可以看到，info字段上已經(jīng)成功建立了一個(gè)名為futxt_idx_info的FULLTEXT索引。

舉例2：

CREATE TABLE articles (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR (200),
    body TEXT,
    FULLTEXT index (title, body)
) ENGINE = INNODB ;

創(chuàng)建了一個(gè)給 title 和 body 字段添加全文索引的表。

舉例3：

CREATE TABLE `papers` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(200) DEFAULT NULL,
  `content` text,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `title` (`title`,`content`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

不同于 like 方式的的查詢:

 SELECT * FROM papers WHERE content LIKE ‘%查詢字符串%';

全文索引用 match+against 方式查詢:

SELECT * FROM papers WHERE MATCH(title,content) AGAINST (‘查詢字符串');

明顯的提高查詢效率

注意點(diǎn)

使用全文索引前，搞清楚版本支持情況；

全文索引比like + % 快 N倍，但是可能存在精度問題

如果需要全文索引的是大量數(shù)據(jù)，建議先添加數(shù)據(jù)，再創(chuàng)建索引。

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

在已經(jīng)存在的表中創(chuàng)建索引可以使用 ALTER TABLE 語句或者 CREATE INDEX 語句。

1. 使用 ALTER TABLE 語句創(chuàng)建索引

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

2. 使用 CREATE INDEX 創(chuàng)建索引

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

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

舉例1：在book表的comment字段上建立名為的普通索引

create index idx_cmt on book(comment);

舉例2：在book表中的book_id字段上建立名為uk_idx_bid的唯一索引，SQL語句如下：

CREATE UNIQUE INDEX uk_idx_bid ON book(book_id);

舉例3：在book表的book_id、book_name、info字段上建立聯(lián)合索引，SQL語句如下：

CREATE INDEX mul_bid_bname_info ON book(book_id,book_name,info);

1.3 刪除索引

MySQL中刪除索引使用ALTER TABLE或DROP INDEX語句，兩者可實(shí)現(xiàn)相同的功能，DROP INDEX語句在內(nèi)部被映射到一個(gè)ALTER TABLE語句中

1. 使用 ALTER TABLE 刪除索引：

ALTER TABLE刪除索引的基本語法格式如下：

ALTER TABLE table_name DROP INDEX index_name;

練習(xí)：刪除book表中名稱為idx_bk_id的唯一索引

首先查看book表中是否名稱為idx_bk_id的索引，輸入SHOW語句如下：

SHOW INDEX FROM book\G;

下面刪除該索引，輸入刪除語句如下：

ALTER TABLE book DROP INDEX idx_bk_id;

提示

添加AUTO_INCREMENT約束字段的唯一索引不能被刪除（）

2. 使用 DROP INDEX 語句刪除索引：

DROP INDEX刪除索引的基本語法格式如下：

DROP INDEX index_name ON table_name;

練習(xí)：刪除book表中名稱為idx_aut_info的組合索引，SQL語句如下：

DROP INDEX idx_aut_info ON book;

語句執(zhí)行完畢，使用SHOW查看索引是否刪除：

SHOW CREATE TABLE book\G;

可以看到，book表中已經(jīng)沒有名稱為idx_aut_info的組合索引，刪除索引成功。

提示刪除表中的列時(shí)，如果要?jiǎng)h除的列為索引的組成部分，則該列也會(huì)從索引中刪除。如果組成索引的所有列都被刪除，則整個(gè)索引將被刪除。

總結(jié)

到此這篇關(guān)于MySQL中索引創(chuàng)建及刪除的文章就介紹到這了,更多相關(guān)MySQL中索引內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章:

mysql
索引

最新国产好看的视频,伊人天堂AV在线,国产Aaaaaa视频,蜜臀视频在线观看一区,人妻av色图,密臀久久久精品影片,青青视频免费观看毛片,久草在线观看视,国产三级精品色情在线

MySQL中索引的創(chuàng)建及刪除方法

目錄

1.1 索引的分類

1.2 創(chuàng)建索引

1、創(chuàng)建表的時(shí)候創(chuàng)建索引

1、創(chuàng)建普通索引

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

3、主鍵索引

4、創(chuàng)建單列索引

5、創(chuàng)建組合索引

6、創(chuàng)建全文索引

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

1. 使用 ALTER TABLE 語句創(chuàng)建索引

2. 使用 CREATE INDEX 創(chuàng)建索引

1.3 刪除索引

1. 使用 ALTER TABLE 刪除索引：

2. 使用 DROP INDEX 語句刪除索引：

總結(jié)

相關(guān)文章

最新評(píng)論

大家感興趣的內(nèi)容

最近更新的內(nèi)容

常用在線小工具

最新国产好看的视频,伊人天堂AV在线,国产Aaaaaa视频,蜜臀视频在线观看一区,人妻av色图,密臀久久久精品影片,青青视频免费观看毛片,久草在线观看视,国产三级精品色情在线

MySQL中索引的創(chuàng)建及刪除方法

目錄

1.1 索引的分類

1.2 創(chuàng)建索引

1、創(chuàng)建表的時(shí)候創(chuàng)建索引

1、創(chuàng)建普通索引

2、創(chuàng)建唯一索引

3、主鍵索引

4、創(chuàng)建單列索引

5、創(chuàng)建組合索引

6、 創(chuàng)建全文索引

2、在已經(jīng)存在的表上創(chuàng)建索引

1. 使用 ALTER TABLE 語句創(chuàng)建索引

2. 使用 CREATE INDEX 創(chuàng)建索引

1.3 刪除索引

1. 使用 ALTER TABLE 刪除索引：

2. 使用 DROP INDEX 語句刪除索引：

總結(jié)

相關(guān)文章

最新評(píng)論

大家感興趣的內(nèi)容

最近更新的內(nèi)容

常用在線小工具

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

3、主鍵索引

4、創(chuàng)建單列索引

6、創(chuàng)建全文索引

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