久久精品人人爽,华人av在线,亚洲性视频网站,欧美专区一二三

MySQL中索引類型的作用是什么

140次閱讀
沒有評論

共計(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ò),可以把它分享出去讓更多的人看到。

正文完
 
丸趣
版權(quán)聲明:本站原創(chuàng)文章,由 丸趣 2023-08-03發(fā)表,共計(jì)8156字。
轉(zhuǎn)載說明:除特殊說明外本站除技術(shù)相關(guān)以外文章皆由網(wǎng)絡(luò)搜集發(fā)布,轉(zhuǎn)載請注明出處。
評論(沒有評論)
主站蜘蛛池模板: 临猗县| 佛山市| 新晃| 清水县| 五河县| 青海省| 岚皋县| 穆棱市| 皋兰县| 花莲市| 揭东县| 泸溪县| 都江堰市| 探索| 铜川市| 蒲城县| 双鸭山市| 历史| 东兰县| 土默特左旗| 筠连县| 丰城市| 鹤山市| 盐亭县| 汪清县| 余干县| 潢川县| 云南省| 子长县| 汶川县| 察隅县| 墨玉县| 澎湖县| 永登县| 陆河县| 绥化市| 平山县| 丽江市| 洪泽县| 莫力| 德安县|