mysql 索引

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

2.索引使用原则：

1、对经常更新的表就避免对其进行过多的索引，对经常用于查询的字段应该创建索引，

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

3、在一同值少的列上(字段上)不要建立索引，比如在学生表的"性别"字段上只有男，女两个不同值。相反的，在一个字段上不同值较多可是建立索引。

3.索引分类

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

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

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

索引分类：

普通索引（由关键字KEY或INDEX定义的索引）的唯一任务是加快对数据的访问速度。因此，应该只为那些最经常出现在查询条件（WHEREcolumn=）或排序条件（ORDERBYcolumn）中的数据列创建索引。只要有可能，就应该选择一个数据最整齐、最紧凑的数据列（如一个整数类型的数据列）来创建索引。

创建数据表的方式创建普通索引：

create table t1(id int not null, name varchar(50) not  null, author varchar(50) not null,info varchar(255) not null,comment varchar(255) not null,publication_year year not null, key(publication_year));

另一种方式：

create table t1(id int not null,name varchar(50) not null, author varchar(50) not null, info varchar(255) not null,comment varchar(255) not null,publication_year year not null,key 'publication_year' ('publication_year'));

修改表添加索引：

ALTER TABLE `t1` ADD INDEX index_name ( `publication_year` )；

看是否使用了索引进行查询。

EXPLAIN SELECT * FROM t1 WHERE publication_year= 1990;

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

elect_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。说明使用了索引。

创建唯一索引：

CREATE TABLE t1(id INT NOT NULL,name CHAR(30) NOT NULL,UNIQUE INDEX UniqIdx(id));

解释：对id字段使用了索引，并且索引名字为UniqIdx。

修改表添加唯一索引：

ALTER TABLE `table_name` ADD UNIQUE ( `column` )

通过id查询时，会使用唯一索引。并且还实验了查询一个没有的id值，则不会使用索引，我觉得原因是所有的id应该会存储到一个const tables中，到其中并没有该id值，那么就没有查找的必要了。

创建主键索引

CREATE TABLE t2(id INT NOT NULL,name CHAR(10),PRIMARY KEY(id));

INSERT INTO t2 VALUES(1,'QQQ');

EXPLAIN SELECT * FROM t2 WHERE id = 1;

我们以前声明的主键约束，就是一个主键索引

创建组合索引

就是在多个字段上创建一个索引、创建一个表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));

SHOW CREATE t3；

解释最左前缀：组合索引就是遵从了最左前缀，利用索引中最左边的列集来匹配行，这样的列集称为最左前缀，不明白没关系，举几个例子就明白了，例如，这里由id、name和age3个字段构成的索引，索引行中就按id/name/age的顺序存放，索引可以索引下面字段组合(id，name，age)、(id，name)或者(id)。如果要查询的字段不构成索引最左面的前缀，那么就不会是用索引，比如，age或者（name，age）组合就不会使用索引查询

删除索引:

show index from table_name(表名)；   查看索引名称

DROP INDEX uniqidx ON t1;

ALTER TABLE t1 DROP INDEX t1;