MySQL(5.6)数据库索引

1.索引的概念
索引是一个单独的，存储在磁盘上的数据库结构，它们包含着对数据表里所有记录的引用指针。使用索引用于快速找出在某个或多个列中有一特定值的行，所有的mysql列类型都可以被索引，对相关的列使用索引是提高查询操作速度的最佳途径
2.索引的优点和缺点
索引的在存储引擎中实现的，每种存储引擎的索引不一定完全相同，并且每种存储引擎也不一定支持所有的索引类型。根据存储引擎定义每个表的最大索引数和最大索引长度，所有存储引擎支持每个表至少16个索引，总索引长度至少为256个字节。大多数存储引擎有更高的限制。
MySQL中索引的存储类型有两种;BTREE and HASH,具体和表的存储引擎相关，MyISAM and InnoDB存储引擎只支持BTREE索引；MEMORY/HEAP存储引擎可以支持HASH和BTREE索引
（1）创建唯一索引，可以保证数据库表中每一行数据的唯一性。
（2）可以加快查询数据的查询速度。
（3）实现数据的参考完整性方面，可以加快表和表之间的连接。
（4）在使用分组和排序子句进行数据查询的时候，也可以显著减少查询中分组和排序时间。
缺点：
（1）创建和维护索引需要时间，如果数据量增多，那么创建索引和维护索引的时间也就会消耗很多
（2）当对于很多数据进行更新的时候，索引也是需要进行相应的更新的，其中删除和增加都是同样的道理，所以也是特别耗时
（3）创建索引也需要占用磁盘空间，除了数据表占数据空间之外，每个索引还要占据一定的物理空间，如果有大量的索引，索引文件可能比数据文件更快到达最大文件尺寸。
3.索引分类
（1）普通索引，唯一索引，主键索引，单列索引，组合索引，全文索引和空间索引
a）普通索引：允许在定义索引的列中插入重复值和空值。
b）唯一索引：索引咧的值必须唯一，但是允许有空值。
c）主键索引：索引咧的值必须唯一，且不允许有空值存在
d）单列索引：一个索引只包含单个列，一个表可以有多个单列索引。
e）组合索引：在表的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的额左边字段时，索引才会被使用，使用组合索引时遵循最左前缀索引。
f）全文索引：类型为FULLTEXT，在定义索引的裂伤支持值的全文查找，允许在这些索引列中插入空值和重复值。全文索引可以在CHAR,VARCHAR,TEXT类型的列上创建，MySQL中只有MyISAM存储引擎支持全文索引。
g）空间索引：对于空间数据类型的字段建立的索引，MySQL中的空间数据类型有4种，分别是：GEOMETRY,POINT,LINESTRING,POLYGON，MySQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类似的语法创建空间索引，创建空间索引的列，必须将其声明为NOT NULL，空间索引只能在存储引擎为MyISAM的表中创建。
4.索引的设计原则
（1）索引并不是越多越好，可以从占据磁盘空间和更新删除时索引维护方面考虑。
（2）对于经常更新的表进行过多索引，并且索引中列尽可能少，而对于经常查询的字段建立索引，避免添加不必要的字段。
（3）数据量小的表建议不要使用索引，起不到优化的作用。
（4）在条件表达式中经常用到的不同值较多的列上建立索引，在不同值少的列上不要建立索引。
（5）当唯一性是某种数据本身的特征的时候，指定唯一索引，使用唯一索引需要能确保定义的列的数据的完整性，以提高查询速度。
（6）在频繁排序和分组的列上建立索引，如果带排序的列有多个，可以在这些列上建立组合索引。
5.索引的创建
（1）创建表时创建索引的语法：

create table table_name [col_name data_type]
[unique|fulltext|spatial][index|key][index_name](col_name [length])
[ASC|DESC]

unique|fulltext|spatial:可选参数，表示唯一索引，全文索引，空间索引；
index|key：作用相同，用来创建索引
col_name：为需要创建索引的字段列，该列必须从数据表中定义的多列中选择。
index_name:指定索引的名称，可选参数，如果不指定，mysql默认col_name为索引值，length为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度
ASC|DESC：指定升序或者降序的索引值存储。
创建索引：

查看创建的索引：

可以看出在book表的year_publication字段上面成功创建了索引，索引名称是mysql自动添加的year_publication
使用explain来勘察索引是否正在使用

加上extended关键字

a)id:SELECT识别符。这是SELECT的查询序列号
b)select_type:指定所使用的select查询类型，这里值为simple,表示是简单查询，不使用union或者子查询，其他可能取值有：primary,union,subquery等
c)table：表示数据库读取的数据表的名字，按被读取的先后顺序排列。
d)type表示本数据表与其它数据表之间的关联关系，可能的取值有system,const,eq_ref,ref,range,index,all
e)possible_keys:表示mysql在搜索数据记录时可选用的各个索引。
f)key表示mysql实际选用的索引
g)key_len:表示给出索引按照字节计算的长度，key_len数值越小，表示越快。
h)ref：表示关联关系中另一个数据表里的数据列名字
i)rows:mysql在执行查询时会预计从这个数据表里读出的数据行的个数
j)extra:表示提供了与关联操作相关的信息。
（2）select_type介绍
a)SELECT类型,可以为以下任何一种:
b)SIMPLE:简单SELECT(不使用UNION或子查询)
c)PRIMARY:最外面的SELECT
d)UNION:UNION中的第二个或后面的SELECT语句
e)DEPENDENT UNION:UNION中的第二个或后面的SELECT语句,取决于外面的查询
f)UNION RESULT:UNION 的结果
g)SUBQUERY:子查询中的第一个SELECT
h)DEPENDENT SUBQUERY:子查询中的第一个SELECT,取决于外面的查询
i)DERIVED:导出表的SELECT(FROM子句的子查询)
（3）type类型介绍
联接类型。下面给出各种联接类型,按照从最佳类型到最坏类型进行排序:
a)system:表仅有一行(=系统表)。这是const联接类型的一个特例。
b)const:表最多有一个匹配行,它将在查询开始时被读取。因为仅有一行,在这行的列值可被优化器剩余部分认为是常数。const表很快,因为它们只读取一次!
c)eq_ref:对于每个来自于前面的表的行组合,从该表中读取一行。这可能是最好的联接类型,除了const类型。
d)ref:对于每个来自于前面的表的行组合,所有有匹配索引值的行将从这张表中读取。
e)ref_or_null:该联接类型如同ref,但是添加了MySQL可以专门搜索包含NULL值的行。
f)index_merge:该联接类型表示使用了索引合并优化方法。
g)unique_subquery:该类型替换了下面形式的IN子查询的ref: value IN (SELECT primary_key FROM single_table WHERE some_expr) unique_subquery是一个索引查找函数,可以完全替换子查询,效率更高。
h)index_subquery:该联接类型类似于unique_subquery。可以替换IN子查询,但只适合下列形式的子查询中的非唯一索引: value IN (SELECT i)key_column FROM single_table WHERE some_expr)
j)range:只检索给定范围的行,使用一个索引来选择行。
k)index:该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小。
l)ALL:对于每个来自于先前的表的行组合,进行完整的表扫描。
（4）extra类型介绍
该列包含MySQL解决查询的详细信息
a)Distinct:MySQL发现第1个匹配行后,停止为当前的行组合搜索更多的行。
b)Not exists:MySQL能够对查询进行LEFT JOIN优化,发现1个匹配LEFT JOIN标准的行后,不再为前面的的行组合在该表内检查更多的行。
c)range checked for each record (index map: #):MySQL没有发现好的可以使用的索引,但发现如果来自前面的表的列值已知,可能部分索引可以使用。
d)Using filesort:MySQL需要额外的一次传递,以找出如何按排序顺序检索行。
e)Using index:从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。
f)Using temporary:为了解决查询,MySQL需要创建一个临时表来容纳结果。
g)Using where:WHERE 子句用于限制哪一个行匹配下一个表或发送到客户。
h)Using sort_union(…), Using union(…), Using intersect(…):这些函数说明如何为index_merge联接类型合并索引扫描。
i)Using index for group-by:类似于访问表的Using index方式,Using j)index for group-by表示MySQL发现了一个索引,可以用来查 询GROUP BY或DISTINCT查询的所有列,而不要额外搜索硬盘访问实际的表。
（5）创建唯一索引


（6）创建单列索引

（7）创建组合索引

（8）在已经存在的表上创建索引

alter table table_name add [unique|fulltext|spatial] [index|key][index_name](col_name[length],...)[asc|desc]

使用show index from table_name\G来查看指定表中创建的索引

参数解释：
table:创建索引的表
non_unique:索引唯一，1代表非唯一索引，0代表唯一索引
key_name:索引名称
seq_in_index:表示该字段在索引中的位置，单列索引该值为1，组合所因为每个字段在索引定义中的顺序
column_name:表示定义索引的列字段
sub_part:表示索引的长度
null表示该字段是否能为空值
index_type:表示索引的类型。
6.删除索引
mysql中删除索引使用alter table或者drop index,两者实现相同的功能，drop index语句在内部被映射到一个alter table 语句中
（1）alter table删除索引语法

alter table table_name drop index index_name;

注意：添加AUTO_INCREMENT约束字段的唯一索引不能被删除。
（2）drop index删除索引语法

drop index index_name on table_name;

7.注意：尽量使用短索引，对于字符串类型的字段进行索引，如果可能应该制定一个前缀长度，比如，如果有一个char的列，如果在前面10或者30个字符内，多数值是唯一的，则不需要对整个咧进行索引。短缩印不仅可以提高查询速度而且可以节省几盘空间，减少IO操作。