mysql 中的索引_Mysql中的索引

一、索引是什么？为什么要有索引？

1、索引是什么？

是一种帮助mysql高效的获取数据的数据结构，这些数据结构以某种方式引用数据，这种结构就是索引。可简单理解为排好序的快速查找数据结构。如果要查“mysql”这个单词，我们肯定需要定位到m字母，然后从下往下找到y字母，再找到剩下的sql。

2、为什么要有索引？

可以快速找出在某个列中有一特定值的行，不使用索引，MySQL必须从第一条记录开始读完整个表，直到找出相关的行，表越大，查询数据所花费的时间就越多，如果表中查询的列有一个索引，MySQL能够快速到达一个位置去搜索数据文件，而不必查看所有数据，那么将会节省很大一部分时间。

二、索引建立原则：什么时候使用索引？什么时候不宜使用索引？

1、什么时候使用索引？

①对经常用来查询的字段适合建立索引

②主键自动建立唯一索引

2、什么时候不宜使用索引？

①对经常更新的表不适合建立索引

②数据量小的表最好不要使用索引，因为由于数据较少，可能查询全部数据花费的时间比遍历索引的时间还要短，索引就可能不会产生优化效果

③在值的类型较少的列上不宜建立索引

三、使用索引的优缺点

1、优点：

便于快速查询，所有的Mysql列类型都可以创建索引

2、缺点：

①创建和维护索引也需要耗费时间，随着数据量的增加所耗费的时间也会更多

②建立索引也需要占用空间，我们知道数据表中的数据也会有最大上线设置的，如果我们有大量的索引，索引文件可能会比数据文件更快达到上线值所以索引并不是越多越好

③对数据进行增删改的时候，也需要对索引进行维护

四、索引的分类及每类索引的简介：

1、索引的分类：

重点：索引是在存储引擎中实现的，也就是说不同的存储引擎，会使用不同的索引

MyISAM和InnoDB存储引擎：只支持BTREE索引， 也就是说默认使用BTREE，不能够更换

MEMORY/HEAP存储引擎：支持HASH和BTREE索引

索引简单分位四类：单列索引(又分为普通索引，唯一索引，主键索引)、组合索引、全文索引、空间索引

2、每类索引的简介：

(1)单列索引：一个索引只包含单列，但是一个表中可以有多个单列索引

①普通索引：

MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点。

②唯一索引：

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

③主键索引：

是不允许为空的唯一索引

(2)组合索引：

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

(3)全文索引：

全文索引，只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引，介绍了要求，说说什么是全文索引，就是在一堆文字中，通过其中的某个关键字等，就能找到该字段所属的记录行，比如有"喵喵喵个小可爱..." 通过小可爱，可能就可以找到该条记录。

(4)空间索引：

空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有四种，GEOMETRY、POINT、LINESTRING、POLYGON。

在创建空间索引时，使用SPATIAL关键字。

要求，引擎为MyISAM，创建空间索引的列，必须将其声明为NOT NULL。

五、索引的创建

1、在创建表时，创建索引：

索引的创建格式：

CREATE TABLE 表名[字段名 数据类型]  [UNIQUE|FULLTEXT|SPATIAL|...]  [INDEX|KEY]  [索引名字] (字段名[length])  [ASC|DESC]

|--------------------------------------|                 |-----------------------------------|        |------------|        |---------|     |---------------|  　　   |------------|

普通创建表语句　　　                   设置什么样的索引(唯一、全文等)    索引关键字      索引名字   对哪个字段设置索引   对索引进行排序

(1)单列索引：

①普通索引：不用给索引那一列添加数据都可使用explain查看是否使用了索引

create table stu(

id int not null,

name varchar(25) not null,

birth_year year default null,

key year(birth_year)

)

create table stu(

id int not null,

name varchar(25) not null,

birth_year year default null,

index year(birth_year)

)

两种均可：

我们在创建索引时没写索引名的话，会自动帮我们用字段名当作索引名，如下：

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

explain select * from stu where birth_year=1990   (此处查询的字段应该为表中的字段名，而不是索引的名字)

解释：虽然表中没数据，但是有EXPLAIN关键字，用来查看索引是否正在被使用，并且输出其使用的索引的信息。

id:　SELECT识别符。这是SELECT的查询序列号,也就是一条语句中，该select是第几次出现。在次语句中，select就只有一个，所以是1.

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

key：实际选用的索引

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

会真正使用了哪些索引。这里就使用了1个索引，所以为1，

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

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

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

②创建一个唯一索引：

create table stu1(

id int not null,

name varchar(25) not null,

birth_year year default null,

unique key sid(id)

)

测试索引是否被使用，并输出其使用索引的信息：explain select * from stu1 where id=5;

在使用唯一索引时要注意，要查看其中使用的索引，一定要提前给里面加入数据，不然查询一个没有的id值是不会使用

索引的

若是把其他的字段设置成唯一的索引也一样，必须要先给里面添加值在查看

③创建主键索引：

create table stu2(

id int not null,

name varchar(25) not null,

primary key(id)

);

测试是否使用了主键索引：

(2)组合索引：

组合索引即在多个字段上建立一个索引

如下：

create table stu3(

id int not null,

name varchar(50) not null,

age int not null,

birth varchar(25) default null,

index multi(id,name,age)

);

组合索引就是遵从了最左前缀，利用索引中最左边的列集来匹配行，这样的列集称为最左前缀，这里由id、name和age3

个字段构成的索引，索引行中就按id/name/age的顺序存放，索引可以索引下面字段组合(id，name，age)、(id，name)或者

(id)。如果要查询的字段不构成索引最左面的前缀，那么就不会是用索引，比如，age或者(name，age)组合就不会使用索引

查询

如下示范：

Ⅰ使用id查询：

Ⅱ使用id，name id，age id，name，age查询

Ⅲ使用name age name，age查询

只有使用了最左边的字段才会使用索引，使用explain查看是否使用了组合索引时，数据库中可以不用添加数据

(3)全文索引：

全文索引可以用于全文搜索，但只有MyISAM存储引擎支持FULLTEXT索引，并且只为CHAR、VARCHAR和TEXT列服务。索引总是对整个列进行，不支持前缀索引

如下：

create table stu4(

id int not null,

name varchar(25) not null,

info varchar(255) not null,

fulltext index text(info)

);

测试是否使用索引：

explain select * from stu4 where match(info) against('lalala');

使用全文索引查询：

//先插入数据

insert into stu4(1,'lala','miaomiao is a girl');

//查询

select * from stu4 where match(info) against('miaomiao');

查询出来是有条件的：见博文--(http://blog.sina.com.cn/s/blog_ae1611930101a063.html)

我这个第一次没查出来的问题出现在：我查询的是“girl”单词长度小于四个字符，所以为空，如下：

(4)空间索引：

空间索引也必须使用MyISAM引擎， 并且空间类型的字段必须为非空，作为空间索引的字段类型必须为GEOMETRY(只能是)

使用SPATIAL，如下：

create table stu5(

id GEOMETRY not null,

spatial index(id)

)engine=myisam;

如下：

2、在已经创建的表上添加索引：

格式：ALTER TABLE 表名 ADD[UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [索引名] (索引字段名)[ASC|DESC]

(1)查看一个表中的索引：

Table:创建索引的表

Non_unique：表示索引非唯一，1代表 非唯一索引， 0代表 唯一索引，意思就是该索引是不是唯一索引

Key_name：索引名称

Seq_in_index ：表示该字段在索引中的位置，单列索引的话该值为1，组合索引为每个字段在索引定义中的顺序(这个只需要                                                     知道单列索引该值就为1，组合索引为别的)

Column_name：表示定义索引的列字段

Sub_part：表示索引的长度

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

Index_type：表示索引类型

(2)给表加索引：

使用上面的stu1表，把它的id也创建成一个索引

alter table stu1 add index(id);

查看结果：第一张图为添加前，第二张图为添加后：

五、删除索引：

两种方式：

1、ALTER TABLE 表名 DROP INDEX 索引名

还用上面的stu1表：alter table stu1 drop index id；

2、DROP INDEX 索引名 ON 表名；

drop index name on stu1;

原博：https://www.cnblogs.com/whgk/p/6179612.html