MYSQL--索引

1.索引的优缺点

优点：

        当一张表中的数据量非常大的时候，如有一张User表记录是百万级别的，要是通过电话号码查询出某个人的信息。若这个电话号码字段没有添加索引的话，将会遍历整张表进行查询，直到查询到这条信息为止，若是添加了索引mysql将会通过（BTREE、HASH、full-text全文索引、R-Tree）使用的存储方式进行查询，而不必去遍历整张表数据。

缺点：

• 创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加。
• 索引也需要占空间，我们知道数据表中的数据也会有最大上限设置，要是设置大量索引可能会比数据文件更快达到上限值。
• 对表中的数据进行增加、删除、修改时，索引也需要动态维护，降低了数据的维护速度。

使用原则

• 对经常增加、删除、修改的表应该避免对其创建过的索引，对经常用于查询的字段应该创建索引。
• 数据量小的表不要使用索引，因为由于数据较少，可能查询全部数据花费的时间比遍历索引的时间还要短，索引就不会产生优化效果。
• 在一同值少的列上不要创建索引，比如性别字段，相反在一个字段上不同值较多的可以建立索引。

3.索引的分类

说明：索引是在存储引擎中实现的，也就是说不同的存储引擎，会使用不同的索引。如：MyISAM和InnoDB存储引擎只支持BTREE索引，也就是说默认使用BTREE，不能更换。MEMORY/HEAP存储引擎支持HASH和BTREE索引。

单列索引：（普通索引，唯一索引，主键索引）

• 普通索引：mysql 中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点。
• 唯一索引：索引列中的值必须是唯一的，但是允许为空值
• 主键索引：是一种特殊的唯一索引，不允许有空值

组合索引：在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。（最左前缀解释：利用索引中最左边的列集来匹配行，例如有id,name,age，3个字段构成的索引，索引行中就按id/name/age的顺序存放，索引生效的组合有（id,name,age）、(id,name)、(id)。如果要查寻的字段不构成最左面的前缀，那么就不会使用索引。比如age或者(name,age)就不会使用索引）。

全文索引：全文索引，只有在MyISAM引擎上才能使用，只能在CHAR，VARCHAR，TEXT类型字段上使用全文索引（全文索引就是在一堆文字中，通过其中的某个关键字等，就能找到该字段的所属的记录行，但是会忽略过于短的词默认能找到最小长度为4个字符。停止字中的词会被忽略。具体细节翻看相关文章）

空间索引：空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有4种，GEOMETRY、POINT、LINESTRING、 POLYGON。在创建空间索引时，使用SPATIAL关键字。要求引擎为MyISAM，创建空间索引的列，必须将其声明为NOT NULL。

4.DEMO

单列索引：

4.1创建普通索引

create table t1(
	id int not null,
    name varchar(16),
    age int ,
    index(name)
);
insert into t1 values(1,"zhangsan",10),(2,"lisi",23),(3,"wangwu",34);
select * from t1;

显示创建表：

show create table t1;

结果：

测试查询的时候索引是否起作用：

explain select * from t1 where name ="zhangsan";

结果：

字段解释：

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

select_type:所使用的select查询类型，simple表示为简单的select，不使用union或子查询，就为简单的select。也就是说在该select查询时会使用索引。其他取值，primary:最外面的select。在拥有子查询时，就会出现两个以上的select。

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

type:参考博客

possible_keys:MySQL 在搜索表数据记录时可以选用的各个索引，该表中就有一个索引name

key:实际选用的索引

key_len:参考博客

ref:给出关联关系中另一个数据表中数据列的名字。常量（const），这里使用的是“zhangsan”

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

extra:extra列中出现的信息一般不是太重要，但是还是有很多信息我们可以从这里面获取

• using index：出现这个说明mysql使用了覆盖索引，避免访问了表的数据行，效率不错！
• using where：这说明服务器在存储引擎收到行后将进行过滤。有些where中的条件会有属于索引的列，当它读取使用索引的时候，就会被过滤，所以会出现有些where语句并没有在extra列中出现using where这么一个说明。
• using temporary：这意味着mysql对查询结果进行排序的时候使用了一张临时表。
• using filesort：这个说明mysql会对数据使用一个外部索引排序，而不是按照表内的索引顺序进行读取。
  
  

4.2创建唯一索引

create table t2(
	id int not null,
    name char(30) not null,
    unique index nameIndex(name)  ##对名字创建了唯一索引  索引名字为nameIndex
);

show create table t2;

插入数据

insert into t2 values(1,"zhangsan"),(2,"lisi"),(3,"wangwu");
select * from t2;

分析数据

explain select * from t2 where name="zhangsan";//存在zhangsan数据
explain select * from t2 where name="zhangsan1";//不存在zhangsan1数据

4.3创建主键索引

create table t3(
	id int not null,
    name char(30) not null,
    primary key(id)
);
show create table t3;

insert into t3 values(1,"zhangsan"),(2,"lisi"),(3,"wangwu");

explain select * from t3 where id="3";

4.4创建组合索引

create table t4(
	id int not null,
    name char(30) not null,
    age int not null,
    info varchar(255),
    index multiIdx(id,name,age)
);

insert into t4 values(1,"zhangsan",23,"hahah"),(2,"lisi",24,"lalal"),(3,"wangwu",34,"halal");
explain select * from t4 where id="1" and name="zhangsan";

explain select * from t4 where name="zhangsan" and age=23;

4.5创建全文搜索索引

create table t6(
	id int not null,
    name char(30) not null,
    age int not null,
    info varchar(255),
    fulltext index fullTxt(info)
) engine=MyISAM;
insert into t6 values(1,"zhangsan",23,"hahah"),(2,"lisi",24,"lalal"),(3,"wangwu",34,"halal");
explain select * from t6 where match(info) against("lalal");

注意：使用全文搜索时，需要借助match函数，并且其全文搜索的限制比较多，比如只能通过MyISAM引擎，比如只能在CHAR,VARCHAR,TEXT上设置全文搜索。比如搜索的关键字默认至少要有4个字符，比如搜索的关键字太短就会被忽略掉。

4.6创建空间索引

参考博文