共計(jì) 8156 個(gè)字符,預(yù)計(jì)需要花費(fèi) 21 分鐘才能閱讀完成。
這篇文章給大家介紹 MySQL 中索引類型的作用是什么,內(nèi)容非常詳細(xì),感興趣的小伙伴們可以參考借鑒,希望對大家能有所幫助。
前言:
MySQL 索引的建立對于 MySQL 的高效運(yùn)行是很重要的,索引可以大大提高 MySQL 的檢索速度。
打個(gè)比方,如果合理的設(shè)計(jì)且使用索引的 MySQL 是一輛蘭博基尼的話,那么沒有設(shè)計(jì)和使用索引的 MySQL 就是一個(gè)人力三輪車。
拿漢語字典的目錄頁(索引)打比方,我們可以按拼音、筆畫、偏旁部首等排序的目錄(索引)快速查找到需要的字。
創(chuàng)建索引時(shí),你需要確保該索引是應(yīng)用在 SQL 查詢語句的條件(一般作為 WHERE 子句的條件)。
建立索引會占用磁盤空間的索引文件。
每種搜索引擎支持的索引是不同的,具體如下:
MyISAM,InnoDB 支持 btree 索引;
Memory 支持 btree 和 hash 索引。
索引的優(yōu)勢如下:
加快查詢速度
創(chuàng)建唯一索引來保證數(shù)據(jù)表中數(shù)據(jù)的唯一性
實(shí)現(xiàn)數(shù)據(jù)的完整性,加速表和表之間的連接
減少分組和排序的時(shí)間
增加索引也有很多不利,主要表現(xiàn)在以下幾個(gè)方面:
創(chuàng)建索引和維護(hù)索引要耗費(fèi)時(shí)間,并且隨著數(shù)據(jù)量的增加所耗費(fèi)的時(shí)間也會增加。
索引需要占磁盤空間,除了數(shù)據(jù)表占數(shù)據(jù)空間之外,每一個(gè)索引還要占一定的物理空間,如果有大量的索引,索引文件可能比數(shù)據(jù)文件更快達(dá)到最大文件尺寸。
當(dāng)對表中的數(shù)據(jù)進(jìn)行增加、刪除和修改的時(shí)候,索引也要?jiǎng)討B(tài)地維護(hù),這樣就降低了數(shù)據(jù)的維護(hù)速度。
一、索引的分類 1、唯一索引和普通索引
普通索引:是 MySQL 中的基本索引類型,允許在定義索引的列中插入重復(fù)值和空值。
唯一索引:索引列的值必須唯一,但允許有空值。如果是組合索引,則列值的組合必須唯一。
主鍵索引:是一種特殊的唯一索引,不允許有空值。
2、單列索引和組合索引
單列索引:即一個(gè)索引只包含單個(gè)列,一個(gè)表可以有多個(gè)單列索引;
組合索引:指在表的多個(gè)字段組合上創(chuàng)建的索引。只有在查詢條件中使用了這些字段的左邊字段時(shí),索引才會被使用。使用組合索引時(shí)遵循最左前綴集合。
3、全文索引(fulltext)
全文索引類型為 FULLTEXT,在定義索引的列上支持值得全文查找,允許在這些索引列
中插入重復(fù)值和空值。全文索引可以在 CHAR、VARCHAR 或者 TEXT 類型的列上創(chuàng)建。MySQL 5.7.xx 之前只有 MyISAM 存儲引擎支持全文索引。
4、空間索引
空間索引是對空間數(shù)據(jù)類型的字段建立的索引,MySQL 中的空間數(shù)據(jù)類型有 4 中,分別是:
geometry、point、linstring 和 polygon。MySQL 使用 SPATIAL 關(guān)鍵字進(jìn)行擴(kuò)展,使得能夠用于創(chuàng)建空間索引的列,必須將其聲明為 NOT NULL,同樣,在 MySQL 5.7.xx 之前,空間索引只能在存儲引擎為 MyISAM 的表中創(chuàng)建。
5、創(chuàng)建索引的規(guī)則
創(chuàng)建索引并非是越多越好,一個(gè)表中如果有大量的索引,不僅占用磁盤空間,而且會影響
insert、delete、update 等語句的性能。因?yàn)楫?dāng)表中的數(shù)據(jù)更改時(shí),索引也會進(jìn)行調(diào)整和更新;
數(shù)據(jù)量小得表最好不要?jiǎng)?chuàng)建索引,由于數(shù)據(jù)較少,查詢花費(fèi)的時(shí)間可能比遍歷索引的時(shí)間還要長;
避免對經(jīng)常更新的數(shù)據(jù)創(chuàng)建索引。而對經(jīng)常用于查詢的字段應(yīng)該創(chuàng)建索引;
在條件表達(dá)式中經(jīng)常用到的不同值較多的列創(chuàng)建索引;
當(dāng)唯一性是某種數(shù)據(jù)本身的特征時(shí),我們創(chuàng)建唯一性索引;
在頻繁進(jìn)行排序或分組的列上建立索引,如果排序的列有多個(gè),可以創(chuàng)建組合索引;
二、創(chuàng)建表的同時(shí)創(chuàng)建索引 1、創(chuàng)建普通索引
mysql create table book
- (
- bookid int,
- bookname varchar(255),
- authors varchar(255),
- info varchar(255),
- comment varchar(255),
- year_publication year,
- index(year_publication) !-- 創(chuàng)建 year_publication 列為索引列 --
- );
mysql show create table book\G !-- 查看索引 --
*************************** 1. row ***************************
Table: book
Create Table: CREATE TABLE `book` ( `bookid` int(11) DEFAULT NULL,
`bookname` varchar(255) DEFAULT NULL,
`authors` varchar(255) DEFAULT NULL,
`info` varchar(255) DEFAULT NULL,
`comment` varchar(255) DEFAULT NULL,
`year_publication` year(4) DEFAULT NULL,
KEY `year_publication` (`year_publication`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
使用 explain 判斷索引是否正在被使用,如下:
!-- 在查詢命令前加“explain”關(guān)鍵字即可 --
mysql explain select * from book where year_publication=1999\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: book
partitions: NULL
type: ref
possible_keys: year_publication
!--possible_keys:后面表示使用的索引名稱,沒有定義名稱會使用字段名為索引名 --
key: year_publication
key_len: 2
ref: const
rows: 1
filtered: 100.00
Extra: Using index condition
1 row in set, 1 warning (0.00 sec)
2、唯一索引
唯一索引主要原因是減少查詢索引列操作的執(zhí)行時(shí)間。尤其是對比比較龐大的數(shù)據(jù)表。與普通索引類似,不同點(diǎn)在于:索引列的值必須唯一,但允許有空值。如果是組合索引,則列值的組合必須唯一。
!-- 創(chuàng)建帶唯一索引的表 --
mysql create table t1(
- id int not null,
- name char(30),
- unique index Uniqidx(id)
- );
!-- 查看 --
mysql show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` ( `id` int(11) NOT NULL,
`name` char(30) DEFAULT NULL,
UNIQUE KEY `Uniqidx` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
3、單列索引
單列索引:是在數(shù)據(jù)表中的某一字段上創(chuàng)建的索引,一個(gè)表中可以創(chuàng)建多個(gè)單列索引。
!-- 創(chuàng)建單列索引 --
mysql create table t2
- (
- id int not null,
- name char(50) null,
- index singleidx(name)
- );
mysql show create table t2\G !-- 查看 --
*************************** 1. row ***************************
Table: t2
Create Table: CREATE TABLE `t2` ( `id` int(11) NOT NULL,
`name` char(50) DEFAULT NULL,
KEY `singleidx` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
4、組合索引
組合索引:是在多個(gè)字段上創(chuàng)建一個(gè)索引。遵循最左前綴原則。最左前綴 索引最左邊的列來匹配行。
!-- 創(chuàng)建組合索引 --
mysql create table t3
- (
- id int not null,
- name char(30) not null,
- age int not null,
- info varchar(255),
- index multiidx(id,name,age)
- );
!-- 查看組合索引 --
mysql show create table t3\G
*************************** 1. row ***************************
Table: t3
Create Table: CREATE TABLE `t3` ( `id` int(11) NOT NULL,
`name` char(30) NOT NULL,
`age` int(11) NOT NULL,
`info` varchar(255) DEFAULT NULL,
KEY `multiidx` (`id`,`name`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
注:組合索引可以起幾個(gè)索引的作用,但是使用時(shí)并不是隨意查詢哪個(gè)字段都是可以使用索引。而是遵循最左前綴:利用索引中最左邊的列集來匹配行。這樣的列集稱為最左前綴。
5、全文索引
全文索引:FULLTEXT,可以用于全文搜索,支持為 CHAR\VARCHAR 和 TEXT 列。索引總是對整個(gè)列進(jìn)行,不支持局部索引,適合大型數(shù)據(jù)的表創(chuàng)建。
!-- 創(chuàng)建帶有全文索引的表 --
mysql create table test8 ( id int not null, title varchar(255), body varchar(255),fulltext index(body) ) ENGINE=MyISAM; !--MySQL 版本在 5.7 以后的話,可以不用指定引擎為 myisam--
!-- 查看 --
mysql show create table t4\G
*************************** 1. row ***************************
Table: t4
Create Table: CREATE TABLE `t4` ( `id` int(11) NOT NULL,
`name` char(30) NOT NULL,
`age` int(11) NOT NULL,
`info` varchar(255) DEFAULT NULL,
FULLTEXT KEY `fullidx` (`info`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
!-- 全文索引的使用方法和其他索引不一樣,具體使用方法如下(先插入測試數(shù)據(jù)):--
mysql insert into test8 values(2, 數(shù)據(jù)庫 , WORDS OF WISDOM: Like they say in Asia, nobody should use a fork. Tradition evven dictates to “chop” all your forks and “stick” to the origiinal.
Query OK, 1 row affected (0.00 sec)
mysql insert into test8 values(1, 數(shù)據(jù)庫 , In MySQL 8.0.17, we made an observation in the well-known TPC-H benchmark for one particular query
Query OK, 1 row affected (0.00 sec)
!-- 測試是否用到了索引,并且可以搜索到數(shù)據(jù) --
mysql explain select * from test8 where match(body) against(MySQL)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test8
partitions: NULL
type: fulltext
possible_keys: body
key: body
key_len: 0
ref: const
rows: 1
filtered: 100.00
Extra: Using where; Ft_hints: sorted
1 row in set, 1 warning (0.00 sec)
查看到的數(shù)據(jù)如下:
注:在上面創(chuàng)建全文索引的方式中,可以實(shí)現(xiàn)英文的全文索引,每個(gè)單詞以空格分隔來匹配,若想實(shí)現(xiàn)中文的全文索引,那么需要在創(chuàng)建表的同時(shí),加上“with parser ngram”來帶上中文解析器。
中文的全文索引創(chuàng)建如下:
mysql create table test10
- (
- id int not null,
- title varchar(50),
- info text,
- fulltext index(title,info) with parser ngram);
!-- 創(chuàng)建中文索引時(shí),指定“with parser ngram”,以便支持中文分析器,否則無法使用索引搜索 --
!-- 插入測試數(shù)據(jù) --
mysql insert into test10 values(1, 測試 , 主辦方介紹,本次大展將更加突出互動性強(qiáng),大眾參與度高的特點(diǎn),讓觀展者不僅享受視 覺盛宴,更能體驗(yàn)到冰雪運(yùn)動的快樂。Query OK, 1 row affected (0.00 sec)
mysql insert into test10 values(2, 測試 2 , 中國攝影家協(xié)會分 黨組書記、駐會副主席鄭更生,中國攝影家協(xié)會副主席線云強(qiáng),中國 攝影家協(xié)會顧問王玉文
Query OK, 1 row affected (0.00 sec)
!-- 測試是否可以使用到全文索引 --
mysql explain select * from test10 where match(title,info) against(中國)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test10
partitions: NULL
type: fulltext
possible_keys: title !-- 表示使用到了 --
key: title
key_len: 0
ref: const
rows: 1
filtered: 100.00
Extra: Using where; Ft_hints: sorted
1 row in set, 1 warning (0.00 sec)
上述查到的結(jié)果如下:
6、空間索引
空間索引:必須在 MyISAM 類型的表中創(chuàng)建,且空間類型的字段必須為非空。
!-- 創(chuàng)建空間索引 --
mysql create table t5
- (
- g geometry not null,
- spatial index spaidx(g)
- )engine=myisam;
!-- 查看 --
mysql show create table t5\G
*************************** 1. row ***************************
Table: t5
Create Table: CREATE TABLE `t5` (
`g` geometry NOT NULL,
SPATIAL KEY `spaidx` (`g`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
三、在已經(jīng)存在的表上創(chuàng)建索引 1、添加唯一索引
mysql alter table book add unique index uniqidx(bookid); !--uniqidx 為索引名,bookid 為列名 --
2、添加單列索引
mysql alter table book add index bkidex(comment(50));
3、添加全文索引
mysql alter table t6 add fulltext index infofulidx(info);
4、添加組合索引
mysql alter table book add index abc(authors(20),info);
5、添加空間索引
mysql alter table t5 add spatial index spatidx(g);
四、在已存在的表上創(chuàng)建索引
!-- 創(chuàng)建一個(gè)表用于測試 --
mysql CREATE TABLE book1 ( bookid INT NOT NULL, bookname VARCHAR(255) NOT NULL,
authors VARCHAR(255) NOT NULL, info VARCHAR(255) NULL, comment VARCHAR(255) NULL,
year_publication YEAR NOT NULL );
Query OK, 0 rows affected (0.02 sec)
1、創(chuàng)建普通索引
mysql create index bknameidex on book1(bookname); !--book1 為表明,bookname 為列名 --
2、創(chuàng)建單列索引
mysql create index bkcmtidex on book1(comment(50));
3、創(chuàng)建組合索引
mysql create index bkauthandinfoidex on book1(authors,info);
4、創(chuàng)建全文索引
mysql create fulltext index fullidx on book(info);
5、創(chuàng)建唯一性索引
mysql create unique index uniqidx on book1(bookid);
6、創(chuàng)建空間索引
mysql create table t7 (g geometry not null);
mysql create spatial index spaidx on t7(g);
五、刪除索引
刪除表中的列時(shí),如果要?jiǎng)h除的列為索引的組成部分,則該列也會從索引中刪除。如果組成索引的所有列都被刪除,那么整個(gè)索引將被刪除。
1、查看某些表上有哪些索引
mysql show index from book\G
2、用 alter table 刪除索引
mysql alter table book drop index uniqidx;
注:添加 AUTO_INCREMENT 的約束字段的唯一索引不能刪除
3、用 drop index 刪除
mysql drop index spaidx on t7;
關(guān)于 MySQL 中索引類型的作用是什么就分享到這里了,希望以上內(nèi)容可以對大家有一定的幫助,可以學(xué)到更多知識。如果覺得文章不錯(cuò),可以把它分享出去讓更多的人看到。