mysql的索引g_mysql 索引操作

一、什么是索引？为什么建立索引

索引用于快速找出在某个列中有一特定值的行，不使用索引，mysql必须从第一条记录开始往下找，直到找到符合条件的行。

表越大，查询数据所花费的时间就越多，而如果表中查询的列有一个索引，mysql就能够快速到达一个位置去搜索数据文件，而不用查看所有数据，那么将会节省很大一部分时间。

eg：一张person表，有2w条记录，记录着2w个人的信息，其中phone字段记录着每个人的电话号码，现在需求如下：想查询出电话号码为XXXX的人的信息！

如果没有索引，那么将从表中第一条记录一条条的往下遍历，指导找到匹配的为止。

如果有了索引，那么会将phone字段通过一定的方法进行存储(mysql中索引的存储类型有两种：BTREE、HASH)，好让查询该字段信息时能快速找到对应的数据，而不必遍历2w条数据。

二、索引的优缺点

优点：可以大大提高mysql的检索速度;

缺点：会降低更新表的速度;

三、索引类型

单列索引(普通索引、唯一索引、主键索引)

一个索引只包含单个列，但一个表是可以有多个单列索引的。

1)普通索引

允许在定义索引的列中插入重复值和空值。

2)唯一索引

索引列中的值必须是唯一的，但是允许为空。

3)主键索引

索引列中的值必须是唯一的，不允许为空。

组合索引

多个字段组合创建索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用。

全文索引

只能在MyISAM引擎上才能使用，只能在char、varchar、text类型字段才能使用。

空间索引

是针对空间数据类型字段建立的索引(mysql中的空间数据类型有四种：geometry、point、linestring、polygon)；

在创建空间索引时，使用spatial关键字；

创建时要求引擎为MyISAM，且列必须声明为NOT NULL。

四、创建索引

1.创建表的时候创建索引

格式：create table 表名([字段名 数据类型] [unique|fulltext|spatial|...] [index|key] [索引名] (字段名[lenggth]) [asc|desc] )

|-----------普通创建表语句---------| |-----------索引类型---------| |-索引关键字-| |-索引名字-| |-设置索引的字段-| |-对索引进行排序-|

1)创建普通索引

#创建普通索引(用index)

create table book(

book_id int not null,

book_name varchar(255) not null,

authors varchar(255) not null,

info varchar(255) null,

comment varchar(255) null,

year_publication year not null,

index (year_publication)

);

#创建普通索引(用key)

create table book(

book_id int not null,

book_name varchar(255) not null,

authors varchar(255) not null,

info varchar(255) null,

comment varchar(255) null,

year_publication year not null,

key (year_publication)

);

结果：

普通索引

如上图，当为指定索引名字时，系统自动使用字段名作为索引名。

测试：查看是否使用了索引进行查询

mysql> explain select * from book where year_publication = 1990\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: book

partitions: NULL

type: ref

possible_keys: year_publication

key: year_publication

key_len: 1

ref: const

rows: 1

filtered: 100.00

Extra: Using index condition

1 row in set, 1 warning (0.00 sec)

#id:　SELECT识别符。这是SELECT的查询序列号,也就是在一条语句中该select是第几次出现。

#select_type:所使用的SELECT查询类型，

SIMPLE表示为简单的SELECT，不实用UNION或子查询，就为简单的SELECT。也就是说在该SELECT查询时会使用索引。其他取值，

PRIMARY：最外面的SELECT.在拥有子查询时，就会出现两个以上的SELECT。

UNION：union(两张表连接)中的第二个或后面的select语句

SUBQUERY：在子查询中，第二SELECT。

#table：数据表的名字。他们按被读取的先后顺序排列，这里因为只查询一张表，所以只显示book。

#type：指定本数据表和其他数据表之间的关联关系，该表中所有符合检索值的记录都会被取出来和从上一个表中取出来的记录作联合。ref用于连接程序使用键的最左前缀或者是该键不是 primary key 或 unique索引(换句话说，就是连接程序无法根据键值只取得一条记录)的情况。当根据键值只查询到少数几条匹配的记录时，这就是一个不错的连接类型。(注意，个人这里不是很理解，百度了很多资料，全是大白话，等以后用到了这类信息时，在回过头来补充，这里不懂对后面的影响不大。)可能的取值有 system、const、eq_ref、index和All。

#possible_keys：MySQL在搜索数据记录时可以选用的各个索引，该表中就只有一个索引，year_publication。

#key：实际选用的索引。

#key_len：显示了mysql使用索引的长度(也就是使用的索引个数)，当 key 字段的值为 null时，索引的长度就是 null。注意，key_len的值可以告诉你在联合索引中mysql会真正使用了哪些索引。这里就使用了1个索引，所以为1。

#ref:给出关联关系中另一个数据表中数据列的名字。常量(const)，这里使用的是1990，就是常量。

#rows：MySQL在执行这个查询时预计会从这个数据表里读出的数据行的个数。

#extra：提供了与关联操作有关的信息，没有则什么都不写。

上面的一大堆东西能看懂多少看多少，我们最主要的是看possible_keys和key 这两个属性，上面显示了key为year_publication。说明使用了索引。

2)创建唯一索引

#创建唯一索引

create table t1(

id int not null,

name char(30) not null,

unique key UniqIdx (id)

);

结果：

唯一索引

测试：查看是否使用了索引进行查询

mysql> # 此时t1表内无数据

mysql> explain select * from t1 where id = 1\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: NULL

partitions: NULL

type: NULL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: NULL

filtered: NULL

Extra: no matching row in const table

1 row in set, 1 warning (0.00 sec)

mysql> # 插入一条数据(t1只有一条数据)

mysql> insert into t1 values(1,'xxx');

Query OK, 1 row affected (0.01 sec)

mysql> explain select * from t1 where id = 1\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: t1

partitions: NULL

type: const

possible_keys: UniqIdx

key: UniqIdx

key_len: 4

ref: const

rows: 1

filtered: 100.00

Extra: NULL

1 row in set, 1 warning (0.00 sec)

从上述测试结果来看，

没有数据或者查询一个没有id的值，不会使用索引；

通过id查询时，当id存在时，会使用唯一索引；

3)创建主键索引

#创建主键索引

create table t2(

id int not null,

name char(10),

primary key (id)

);

结果：

主键索引

测试：查看是否使用了索引进行查询

mysql> # 插入一条数据(t2表内只有一条数据)

mysql> insert into t2 values(1,'qqq');

Query OK, 1 row affected (0.02 sec)

mysql> explain select * from t2 where id = 1\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: t2

partitions: NULL

type: const

possible_keys: PRIMARY

key: PRIMARY

key_len: 4

ref: const

rows: 1

filtered: 100.00

Extra: NULL

1 row in set, 1 warning (0.00 sec)

mysql> explain select * from t2 where id = 2\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: NULL

partitions: NULL

type: NULL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: NULL

filtered: NULL

Extra: no matching row in const table

1 row in set, 1 warning (0.00 sec)

从上述测试可以看出，

设置主键时，即是创建了主键索引；

4)创建组合索引

组合索引就是在多个字段上建立一个索引，且遵循最左前缀原则。

最左前缀：即最左优先。例如：组合索引是由id、name和age按照顺序组成的，可以索引的组合(遵循最左前缀)有：(id,name,age)、(id,name)、(id)；不能是索引的组合(不遵循最左前缀)的组合：(name,age)、(name)、(age)。

#创建组合索引

#创建表t3，在表中的id、name和age字段上建立组合索引

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> #查询id和name字段(可以正常使用索引)

mysql> explain select * from t3 where id = 1 and name = 'xxx'\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: t3

partitions: NULL

type: ref

possible_keys: MultiIdx

key: MultiIdx

key_len: 94

ref: const,const

rows: 1

filtered: 100.00

Extra: NULL

1 row in set, 1 warning (0.00 sec)

mysql> #查询name字段(无法使用索引)

mysql> explain select * from t3 where name = 'xxx'\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: t3

partitions: NULL

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 1

filtered: 100.00

Extra: Using where

1 row in set, 1 warning (0.00 sec)

5)创建全文索引

全文索引是用于进行全文搜索(即在很多文字中，通过关键字查找)的，但是只有MyISAM存储引擎支持FullText索引，且只能为char、varchar、text类型的字段服务。

在使用全文索引时借助match函数。

#创建全文索引

create table t4(

id int not null,

name char(30) not null,

age int not null,

info varchar(255),

fulltext index FullTxtIdx (info)

)engine = MyISAM;

结果：

全文索引

测试：是否可以使用索引进行查询

测试结果

上述测试结果可以看出，全文搜索使用了全文索引，但是并未查询出结果，why？

因为其全文搜索存在很多限制：

只能通过MyISAM引擎，比如char、varchar、text设置全文索引；

搜索的关键字默认至少要4个字符，否则会被忽略掉，导致查不到结果；

搜索的关键字若出现在全文50%的行中，则会别列为停止字，而停止字中的词是会被忽略掉的，最终导致查不到结果；

6)创建空间索引

关于空间索引，这里只做简单介绍。

空间索引必须使用MyISAM引擎，并且空间类型的字段必须为非空字段。

#创建空间索引

create table t5(

g geometry not null,

spatial index spatIdx(g)

)engine = MyISAM;

结果：

空间索引

2.在已经存在的表上创建索引

格式：alter table 表名 add [unique|fulltext|spatial] [index|key] [索引名] (索引字段名) [asc|desc]

命令一：

查看一张表中所创建的索引

show index from 表名\G

mysql> show index from book\G

*************************** 1. row ***************************

Table: book

Non_unique: 1

Key_name: year_publication

Seq_in_index: 1

Column_name: year_publication

Collation: A

Cardinality: 0

Sub_part: NULL

Packed: NULL

Null:

Index_type: BTREE

Comment:

Index_comment:

1 row in set (0.00 sec)

#Table:创建索引的表

#Non_unique:表示索引是否唯一，1代表非唯一索引，0表示唯一索引

#Key_name:索引的名称

#Seq_in_index:表示该字段在索引中的位置，单列索引的话该值为1，组合索引为每个字段在索引定义中的顺序。简而言之，即单列索引该值就为1，组合索引为别的值。

#Column_name:表示定义索引的列字段

#Sub_part:表示索引的长度

#Null:表示该字段是否能为空值

#Index_type:表示索引类型

1)为表添加索引

测试：就拿上面的book表来说，本来创建表时就创建了一个year_publication索引，现在为该表再添加一个普通索引

alter table book add index BkNameIdx (book_name(30));

结果：

图片.png

2)使用create index创建索引

格式：create [unique|fulltext|spatial] [index|key] 索引名称 on 表名 (创建索引的字段名[length]) [asc|desc]

测试：为book表添加一个普通索引，字段为authors。

create index BkAuthorsIdx on book (authors);

结果：

mysql> create index BkAuthorsIdx on book (authors);

Query OK, 0 rows affected (0.05 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> show index from book\G

*************************** 1. row ***************************

Table: book

Non_unique: 1

Key_name: year_publication

Seq_in_index: 1

Column_name: year_publication

Collation: A

Cardinality: 0

Sub_part: NULL

Packed: NULL

Null:

Index_type: BTREE

Comment:

Index_comment:

*************************** 2. row ***************************

Table: book

Non_unique: 1

Key_name: BkNameIdx

Seq_in_index: 1

Column_name: book_name

Collation: A

Cardinality: 0

Sub_part: 30

Packed: NULL

Null:

Index_type: BTREE

Comment:

Index_comment:

*************************** 3. row ***************************

Table: book

Non_unique: 1

Key_name: BkAuthorsIdx

Seq_in_index: 1

Column_name: authors

Collation: A

Cardinality: 0

Sub_part: NULL

Packed: NULL

Null:

Index_type: BTREE

Comment:

Index_comment:

3 rows in set (0.00 sec)

五、删除索引

格式一：alter table 表名 drop index 缩影名

测试：对book表进行删除操作

删除book表中名为BkNameIdx的索引：

alter table book drop index BkNameIdx;

图片.png

结果：此时BkNameIdx已不存在了。

图片.png

格式二：drop index 索引名 on 表名

测试：删除book表中名为BkAuthorsIdx的索引。

drop index BkAuthors on book;

图片.png

结果：此时BkAuthorsIdx已不存在了。

图片.png

以上~