mysql 本地索引_MySQL索引

一、索引概述

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的方式删除