MySQL索引類型分類有哪些

下面講講關于MySQL索引類型分類有哪些，文字的奧妙在于貼近主題相關。所以，閑話就不談了，我們直接看下文吧，相信看完MySQL索引類型分類有哪些這篇文章你一定會有所受益。 

一、索引的分類

1、唯一索引和普通索引

普通索引：是MySQL中的基本索引類型，允許在定義索引的列中插入重復值和空值。
唯一索引：索引列的值必須唯一，但允許有空值。如果是組合索引，則列值的組合必須唯一。
主鍵索引：是一種特殊的唯一索引，不允許有空值。

2、單列索引和組合索引

單列索引：即一個索引只包含單個列，一個表可以有多個單列索引；
組合索引：指在表的多個字段組合上創建的索引。只有在查詢條件中使用了這些字段的左邊字段時，索引才會被使用。使用組合索引時遵循最左前綴集合。

3、全文索引（ fulltext）

全文索引類型為FULLTEXT，在定義索引的列上支持值得全文查找，允許在這些索引列
中插入重復值和空值。全文索引可以在CHAR、VARCHAR或者TEXT類型的列上創建。MySQL 5.7.xx之前只有MyISAM存儲引擎支持全文索引。

4、空間索引

空間索引是對空間數據類型的字段建立的索引，MySQL中的空間數據類型有4中，分別是：
geometry、point、linstring和polygon 。MySQL使用SPATIAL關鍵字進行擴展，使得能夠用于創建空間索引的列，必須將其聲明為NOT NULL，同樣，在MySQL 5.7.xx之前，空間索引只能在存儲引擎為MyISAM的表中創建。

5、創建索引的規則

• 創建索引并非是越多越好，一個表中如果有大量的索引，不僅占用磁盤空間，而且會影響
• insert、delete、update等語句的性能。因為當表中的數據更改時，索引也會進行調整和更新；
• 數據量小得表最好不要創建索引，由于數據較少，查詢花費的時間可能比遍歷索引的時間還要長；
• 避免對經常更新的數據創建索引。而對經常用于查詢的字段應該創建索引；
• 在條件表達式中經常用到的不同值較多的列創建索引；
• 當唯一性是某種數據本身的特征時，我們創建唯一性索引；
• 在頻繁進行排序或分組的列上建立索引，如果排序的列有多個，可以創建組合索引；

二、創建表的同時創建索引

1、創建普通索引

mysql> create table book
    -> (
    -> bookid int,
    -> bookname varchar(255),
    -> authors varchar(255),
    -> info varchar(255),
    -> comment varchar(255),
    -> year_publication year,
    -> index(year_publication)        <!--創建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”關鍵字即可-->
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、唯一索引

唯一索引主要原因是減少查詢索引列操作的執行時間。尤其是對比比較龐大的數據表。與普通索引類似，不同點在于：索引列的值必須唯一，但允許有空值。如果是組合索引，則列值的組合必須唯一。

<!--創建帶唯一索引的表-->
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、單列索引

單列索引：是在數據表中的某一字段上創建的索引，一個表中可以創建多個單列索引。

<!--創建單列索引-->
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、組合索引

組合索引：是在多個字段上創建一個索引。遵循最左前綴原則。最左前綴 索引最左邊的列來匹配行。

<!--創建組合索引-->
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)

注：組合索引可以起幾個索引的作用，但是使用時并不是隨意查詢哪個字段都是可以使用索引。而是遵循最左前綴：利用索引中最左邊的列集來匹配行。這樣的列集稱為最左前綴。

5、全文索引

全文索引：FULLTEXT，可以用于全文搜索，支持為CHAR\VARCHAR和TEXT 列。索引總是對整個列進行，不支持局部索引，適合大型數據的表創建。

<!--創建帶有全文索引的表-->
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)
<!--全文索引的使用方法和其他索引不一樣，具體使用方法如下（先插入測試數據）：-->
mysql> insert into test8 values(2,'數據庫','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,'數據庫','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)
<!--測試是否用到了索引，并且可以搜索到數據-->
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)

查看到的數據如下：

注：在上面創建全文索引的方式中，可以實現英文的全文索引，每個單詞以空格分隔來匹配，若想實現中文的全文索引，那么需要在創建表的同時，加上“with parser ngram”來帶上中文解析器。

中文的全文索引創建如下：

mysql> create table test10
    -> (
    -> id int not null,
    -> title varchar(50),
    -> info text,
    -> fulltext index(title,info) with parser ngram);
<!--創建中文索引時，指定“with parser ngram”，以便支持中文分析器，否則無法使用索引搜索-->
<!--插入測試數據-->
mysql> insert into test10 values(1,'測試','主辦方介紹，本次大展將更加突出互動性強，大眾參與度高的特點，讓觀展者不僅享受視 覺盛宴，更能體驗到冰雪運動的快樂。');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test10 values(2,'測試2','中國攝影家協會分 黨組書記、駐會副主席鄭更生，中國攝影家協會副主席線云強，中國 攝影家協會顧問王玉文');
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)

上述查到的結果如下：

6、空間索引

空間索引：必須在MyISAM類型的表中創建，且空間類型的字段必須為非空。

<!--創建空間索引-->
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)

三、在已經存在的表上創建索引

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

四、在已存在的表上創建索引

<!--創建一個表用于測試-->
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、創建普通索引

mysql> create index bknameidex on book1(bookname);     <!--book1為表明，bookname為列名-->

2、創建單列索引

mysql> create index bkcmtidex on book1(comment(50));

3、創建組合索引

mysql> create index bkauthandinfoidex on book1(authors,info);

4、創建全文索引

mysql> create fulltext index fullidx on book(info);

5、創建唯一性索引

mysql> create unique index uniqidx on book1(bookid);

6、創建空間索引

mysql> create table t7 (g geometry not null);
mysql> create spatial index spaidx on t7(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;

對于以上MySQL索引類型分類有哪些相關內容，大家還有什么不明白的地方嗎？或者想要了解更多相關，可以繼續關注我們的行業資訊板塊。