快捷導航

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

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

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

1.1 索引的分類

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

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

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

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)建表的時候創(chuàng)建索引

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

# 1.隱式的添加索引(在添加有主鍵約束、唯一性約束或者外鍵約束的字段會自動的創(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 默認 col_name 為索引名;
col_name為需要創(chuàng)建索引的字段列，該列必須從數(shù)據(jù)表中定義的多個列中選擇;
length 為可選參數(shù)，表示索引的長度，只有字符串類型的字段才能指定索引長度;
ASC 或 DESC 指定升序或者降序的索引值存儲。
特例：主鍵索引使用主鍵約束的方式來創(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高級';

EXPLAIN語句輸出結(jié)果的各個行我們在下一章講解，這里主要關(guān)注兩個字段

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

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

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ù)庫會自動建立索引，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)建索引時指定的順序是嚴格對應(yīng)的。在查詢時會遵守最左索引原則，先進行book_id條件的比較，然后再進行book_name比較，最后才是info。因此注意把最常用的查詢字段放在索引的最左邊。

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

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

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

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

舉例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 了，因為在此版本中 InnoDB 支持全文索引。

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

SHOW INDEX FROM test4\G;

由結(jié)果可以看到，info字段上已經(jīng)成功建立了一個名為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)建了一個給 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 (‘查詢字符串');

明顯的提高查詢效率

注意點

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

全文索引比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 被映射到一個 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語句，兩者可實現(xiàn)相同的功能，DROP INDEX語句在內(nèi)部被映射到一個ALTER TABLE語句中

1. 使用 ALTER TABLE 刪除索引：

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

ALTER TABLE table_name DROP INDEX index_name;

練習：刪除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;

練習：刪除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的組合索引，刪除索引成功。

提示刪除表中的列時，如果要刪除的列為索引的組成部分，則該列也會從索引中刪除。如果組成索引的所有列都被刪除，則整個索引將被刪除。

總結(jié)

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

您可能感興趣的文章:

mysql
索引

欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

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

目錄

1.1 索引的分類

1.2 創(chuàng)建索引

1、創(chuàng)建表的時候創(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)文章

最新評論

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

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

常用在線小工具

欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

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

目錄

1.1 索引的分類

1.2 創(chuàng)建索引

1、創(chuàng)建表的時候創(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)文章

最新評論

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

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

常用在線小工具

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

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

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

3、主鍵索引

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

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