MySQL索引篇

索引定义

索引是对数据库表中一列或者多列的值进行排序的一种结构，索引用于快速定位某列中有一特定值的行。不使用索引，MySQL必须从第一条记录遍历整个表，直到找出相关的行。表越大，查询数据所花费的时间越多，如果表中查询的列被创建索引，MySQL能快速到达一个位置去搜索数据文件，而不必查看所有数据。

索引是一个单独的、存储在磁盘上的数据库结构，本身也是表，它们包含着对数据表里所有记录的引用指针。
索引是在存储引擎中实现的，因此，每种存储引擎的索引都不一定完全相同，并且每种存储引擎也不一定支持所有索引类型。所有存储引擎支持每个表至少16个索引，总索引长度至少为256字节。MySQL中索引的存储类型有两种：BTREE和HASH，具体和表的存储引擎相关；MyISAM和InnoDB存储引擎只支持BTREE索引，MEMORY/HEAP存储引擎可以支持HASH和BTREE缩影。
优点：
1、可以快速检索，减少I/O次数，加快检索速度；
2、可以显著减少查询中分组(group by)和排序(order by)的时间；
缺点：
1、创建和维护索引都要耗时，随着数据量的增加所耗费的时间也会增加；
2、索引也需要磁盘空间，索引表占用的空间是数据表的1.5倍；
3、对表中数据进行增加、删除和修改使，索引也要动态地维护，这样就降低了数据的增删改的效率；

索引的分类

常见的索引类型有：主键索引、唯一索引、普通索引、全文索引、组合索引
1、主键索引：即主索引，根据主键pk_clolum（length）建立索引，不允许重复，不允许空值；
2、唯一索引：用来建立索引的列的值必须是唯一的，允许空值
3、普通索引：用表中的普通列构建的索引，没有任何限制
4、全文索引：用大文本对象的列构建的索引
5、组合索引：用多个列组合构建的索引，这多个列中的值不允许有空值

索引的设计原则

索引设计不合理或者缺少索引都会对数据库和应用程序的性能造成障碍，高效的索引对于获得良好的性能非常重要。以下原则非常重要：
1、索引并非越多越好，一个表中如有大量的索引，不仅占用磁盘空间，而且会影响INSERT、DELETE、UPDATE等语句的性能，因为当表中的数据更改的同时，索引也会进行调整和更新
2、避免对经常更新的表设计过多的索引，并且索引中的列尽可能要少，而对经常用于查询的字段应该创建索引，但要避免添加不必要的字段
3、数据量小的表最好不要使用索引，由于数据较少，查询花费的时间可能比遍历索引时间还要短，索引可能不会产生优化效果
4、在条件表达式中经常用到的不同值较多的列上建立索引，在不同值较少的列上不要建立索引，比如性别字段只有男和女，就没必要建立索引。如果建立索引不但不会提高查询效率，反而会严重降低更新速度
5、当唯一性是某种数据本身的特征时，指定唯一索引。使用唯一索引需能确保定义的列的数据完整性，以提高查询速度
6、在频繁排序或分组（即group by或order by操作）的列上建立索引，如果待排序的列有多个，可以在这些列上建立组合索引

创建索引

创建表时创建索引：

CREATE TABLE table_name[col_name data_type]
[UNIQUE|FULLTEXT|SPATIAL]
[INDEX|KEY]
[index_name](col_name[length])
[ASC|DESC]

1、UNIQUE、FULLTEXT和SPATIAL为可选参数，分别表示唯一索引、全文索引和空间索引
2、INDEX和KEY为同义词，二者作用相同，用来指定创建索引
3、col_name为需要创建索引的字段列，该列必须从数据表中该定义的多个列中选择
4、index_name为指定索引的名称，为可选参数，如果不指定则MySQL默认col_name为索引值
5、length为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度
6、ASC或DESC指定升序或者降序的索引值存储

在已创建好的表中创建索引：
在已经存在的表上创建索引，可以使用ALTER TABLE语句或者CREATE INDEX语句。
1、使用ALTER TABLE语句创建索引

ALTER TABLE table_name ADD [UNIQUE|FUUTEXT|SPATIAL]
[INDEX|KEY] [index_name] (col_name[length],...) [ASC|DESC]

如：ALTER TABLE book ADD INDEX BkauthorIdx(author(50));
给book表中的author添加了普通索引，索引名为BkauthorIdx，索引长度为50：查询的时候，只需要检索前面50个字符；对字符串类型的字段进行索引，如果可能应该指定一个前缀长度，例如，一个CHAR(255)的列，如果在前10个或者前30个字符内，多数值是唯一的，则不需要对整个列进行索引，短索引不仅可以提高查询速度而且可以节省磁盘空间、减少I/O操作
2、使用CREATE INDEX语句创建索引

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name ON table_name(col_name[length],...)[ASC|DESC]

确认索引是否被创建及查询时是否生效

是否被创建：

SHOW INDEX FROM book

1、table表示创建索引的表
2、Non_unique表示索引不是一个唯一索引，1表示非唯一索引，0表示唯一索引
3、Key_name表示索引的名称
4、Seq_in_index表示该字段在索引中的位置，单列索引改值该值为1，组合索引为每个字段在索引中定义的顺序
5、Column_name表示定义索引的列字段
6、Sub_part表示索引的长度
7、Null表示该字段是否能为空值
8、Index_type表示索引类型

查询是否生效：

EXPLAIN select * from book where yead_publication = 1990

1、select_type行指定所使用的SELECT查询类型，这里值为SIMPLE，表示简单的SELECT，不使用UNION或者子查询。其他可能的取值有：PRIMARY、UNION、SUBQUERY等
2、table行指定数据库读取的数据表的名字，它们按照被读取的先后顺序排列
3、type行指定了本数据表与其他数据表之间的关联关系，可能的去只有system、const、eq_ref、ref、range、index和All
4、possible_keys行给出了MySQL在搜索数据记录时可选用的各个索引
5、key行是MySQL使用的实际索引
6、key_len行给出了索引按字节计算的长度，key_len数值越小，表示越快
7、ref行给出了关联关系中另外一个数据表里的数据列的名字
8、rows行是MySQL在执行这个查询时预计会从这个数据表里读出的数据行的个数
9、extra行提供了与关联操作有关的信息
看到，possible_keys和key的值都为year_publication，查询时使用了索引；

删除索引

两种二选一，删除表中的列时，如果要删除的列为整个索引的组成部分，则该列也会从索引中删除；如果组成索引的所有列都被删除，则整个索引将被删除；
ALTER TABLE table_name DROP INDEX index_name
DROP INDEX index_name ON table_name

参考：https://www.cnblogs.com/xrq730/p/4940747.html