MySQL索引

目录

• 一、索引概述
  • 1.1 索引的优势
  • 1.2 索引的劣势
• 二、索引的分类
  • 2.1 唯一索引和普通索引
  • 2.2 单列索引和组合索引
  • 2.3 全文索引
  • 2.4 空间索引
  • 2.5 创建索引的规则
• 三、创建表的同时创建索引
  • 3.1 创建普通索引
  • 3.2 创建唯一索引
  • 3.3 创建单列索引
  • 3.4 创建组合索引
• 四、在已经存在的表上创建索引
  • 4.1 添加唯一索引
  • 4.2 添加单列（前缀）索引
  • 4.3 添加全文索引
  • 4.4 添加组合索引
  • 4.5 添加空间索引
  • 4.6 查看索引
  • 4.7 删除索引

一、索引概述

Mysql索引的简历对于Mysql的高效运行是很重要的，索引可以大大提高Mysql的检索速度！

创建索引时，需要确保索引是应用在SQL查询语句的条件（一般作为WHERE子句的条件）！

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

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

• MylSAM、InnoDB支持btree索引；
• Memory支持btree和hash索引；

1.1 索引的优势

• 加快查询速度；
• 创建唯一索引可以保证数据表中数据的唯一性；
• 实现数据的完整性，加速表和表之间的链接；
• 减少分组和排序的时间

1.2 索引的劣势

• 创建索引和维护索引需要耗费大量的时间，并且随着数据量的增加所耗费的时间也会有所增加；
• 索引需要占用磁盘空间，除了数据表占用数据空间之外，每一个索引还要占一定的物理空间，如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸；
• 当对表中的数据进行增加、删除和修改时，索引也要动态地维护，这样就降低了数据的维护速度；

二、索引的分类

2.1 唯一索引和普通索引

• 普通索引：是MySQL中的基本索引类型，允许在定义索引的列中插入重复值和空值；
• 唯一索引：索引列的值必须唯一，但允许是空值。如果是组合索引，则列值的组合必须唯一；
• 主键索引：是一种特殊的唯一索引，不允许有空格；

2.2 单列索引和组合索引

• 单列索引：即一个索引只包含单个列，一个表中可以有多个单列索引；
• 组合索引：指在表的多个字段组合上创建的索引。只有在查询条件中使用了这些字段的左边字段时，索引才会被使用。使用组合索引时遵循最左前缀集合；

2.3 全文索引

全文索引类型为FULLTEXT，在定义索引的列上支持全文查找，允许在这些索引列中插入重复值和空值。全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建。Mysql5.7之前只有MylSAM存储引擎支持全文索引。

2.4 空间索引

空间索引是对空间数据类型的字段简历的索引，Mysql中的空间数据类型有4种，分别是：geometry、point、linstring和polygon。MySQL使用SPATIAL关键字进行扩展，使得能够用于创建空间索引的列，必须将其声明为NOT NULL，同样，在MySQL5.7之前，空间索引只能在存储引擎为MyISAM的表中创建。

2.5 创建索引的规则

• 创建索引并非是越多越好，一个表中如果有大量的索引，不仅占用磁盘空间，而且会影响insert、delete、update等语句的性能，因为当表中的数据更改是，索引也会进行调整和更新；
• 数据量小的表最好不要创建索引，由于数据较少，查询花费的时间可能比遍历索引的数据还要长；
• 避免对经常更新的数据创建索引。而经常用于查询的字段应该创建索引；
• 在条件表达式中经常用到的不同值较多的列创建索引；
• 当唯一性是某种数据本地的特征时，我们创建唯一索引；
• 在频繁进行排序或分则的列上建立索引，如果排序的列有多个，可以创建组合索引；

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

3.1 创建普通索引

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列为索引列
show create table book\G;
#查看索引
*************************** 1. row ***************************
       Table: book
Create Table: CREATE TABLE `book` (
  `bookid` int(11) DEFAULT NULL,
  `bookname` varchar(255) NULL,
  `authors` varchar(255) NULL,
  `info` varchar(255) NULL,
  `comment` varchar(255) 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 select * from book where year_publication=1999\G;
#使用explain判断索引是否正在被使用
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: book
   partitions: NULL
         type: ref
possible_keys: year_publication     #表示使用的索引名称，没有定义名称会使用字段名为索引名
          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)

3.2 创建唯一索引

唯一索引主要原因就是减少查询索引列操作的执行时间，尤其是对比较庞大的数据表。与普通索引类似，不同点在于：索引列的值必须唯一，但允许有空值。如果是组合索引，则该列值的组合必须唯一。

create table t1(
id int not null,
name char(30),
unique index Uniqidx(id));
#创建带唯一索引的表
show create table t1\G;
#查看索引
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `name` char(30)  NULL,
  UNIQUE KEY `Uniqidx` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

3.3 创建单列索引

单列索引：是在数据表中的某一字段上创建的索引，一个表中可以创建多个单列索引。

create table t2 (
id int not null,
name char(50) null,
index singleidx(name) );
#创建单列索引
show create table t2\G;
#查看创建的索引
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  `name` char(50) NULL,
  KEY `singleidx` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

3.4 创建组合索引

组合索引：是在多个字段上创建一个索引。遵循最左前缀原则。最左前缀 索引最左边的列来匹配行。

create table t3 (
id int not null,
name char(30) not null,
age int not null,
info varchar(255),
index multiidx(id,name,age) );
#创建组合索引
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)  NULL,
  KEY `multiidx` (`id`,`name`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

注意：组合索引可以起几个索引的作用，但是使用时并不是随意查询哪个字段都是可以使用索引。而是遵循最左前缀：利用索引中最左边的列集来匹配行。这样的列集称为最左前缀。

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

4.1 添加唯一索引

alter table book add unique index idx_book(bookid);
#针对book表添加唯一索引，索引名称为idx_book（可自定义），针对bookid列建立索引

4.2 添加单列（前缀）索引

alter table book add index idx_comment(comment(50));

4.3 添加全文索引

alter table book add fulltext index idx_info(info);

4.4 添加组合索引

alter table book add index idx_auth_info(authors(20),info);

4.5 添加空间索引

create table t7(g geometry not null);
alter table t7 add spatial index idx_spatial(g);

4.6 查看索引

desc book;
+------------------+--------------+------+-----+---------+-------+
| Field            | Type         | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+-------+
| bookid           | int(11)      | YES  | UNI | NULL    |       |
| bookname         | varchar(255) | YES  |     | NULL    |       |
| authors          | varchar(255) | YES  | MUL | NULL    |       |
| info             | varchar(255) | YES  | MUL | NULL    |       |
| comment          | varchar(255) | YES  | MUL | NULL    |       |
| year_publication | year(4)      | YES  | MUL | NULL    |       |
+------------------+--------------+------+-----+---------+-------+
# UNI为唯一索引，MUL为非唯一索引，PRI为主键索引
show index from book;
+-------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name         | Seq_in_index | Column_name      | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| book  |          0 | idx_book         |            1 | bookid           | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| book  |          1 | year_publication |            1 | year_publication | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| book  |          1 | idx_comment      |            1 | comment          | A         |           0 |       50 | NULL   | YES  | BTREE      |         |               |
| book  |          1 | idx_auth_info    |            1 | authors          | A         |           0 |       20 | NULL   | YES  | BTREE      |         |               |
| book  |          1 | idx_auth_info    |            2 | info             | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| book  |          1 | idx_info         |            1 | info             | NULL      |           0 |     NULL | NULL   | YES  | FULLTEXT   |         |               |
+-------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
#可以清楚的看到创建的索引，及索引与列的对应关系
show index from book\G;
#效果同上

4.7 删除索引

alter table book drop index idx_book;
#使用alter的方式删除索引
drop index idx_info on book;
#使用drop的方式删除