MySQL索引类型详解

程序员文章站 2024-01-20 19:17:16

...

前言：

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

打个比方，如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。

拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。

创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

建立索引会占用磁盘空间的索引文件。

每种搜索引擎支持的索引是不同的，具体如下：

MyISAM ，InnoDB支持btree索引；

Memory 支持 btree和hash索引。

索引的优势如下：

加快查询速度

创建唯一索引来保证数据表中数据的唯一性

实现数据的完整性，加速表和表之间的连接

减少分组和排序的时间

增加索引也有很多不利，主要表现在以下几个方面：

创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加。

索引需要占磁盘空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。

当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。

一、索引的分类

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)

查看到的数据如下：

MySQL索引类型详解

注：在上面创建全文索引的方式中，可以实现英文的全文索引，每个单词以空格分隔来匹配，若想实现中文的全文索引，那么需要在创建表的同时，加上“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)

上述查到的结果如下：

MySQL索引类型详解

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;

上一篇： Photoshop轻松绘制树叶上的露珠

下一篇： “戏精少女”的pandas学习之路，你该这么学！No.5

MySQL索引类型详解

前言：

一、索引的分类

1、唯一索引和普通索引

2、单列索引和组合索引

3、全文索引（ fulltext）

4、空间索引

5、创建索引的规则

二、创建表的同时创建索引

1、创建普通索引

2、唯一索引

3、单列索引

4、组合索引

5、全文索引

6、空间索引

三、在已经存在的表上创建索引

1、添加唯一索引

2、添加单列索引

3、添加全文索引

4、添加组合索引

5、添加空间索引

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

1、创建普通索引

2、创建单列索引

3、创建组合索引

4、创建全文索引

5、创建唯一性索引

6、创建空间索引

五、删除索引

1、查看某些表上有哪些索引

2、用alter table删除索引

3、用drop index删除

MySQL索引类型详解

MySQL之索引(index)

MySQL索引失效问题

记一次MySql单列索引和联合索引的使用区别

Mysql索引原理以及索引优化

Mysql设置索引的注意事项

详解MySQL中的缓冲池（buffer pool）

MySQL表结构为InnoDB类型从ibd文件恢复数据

MySQL索引工作原理

mysql 使用B+树索引有哪些优势