一.MySQL索引介绍

      索引是一个排序的列表，在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址。在数据十分庞大的时候，索引可以大大加快查询的速度。这是因为使用索引后可以不用扫描全表来定位某行的数据,而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据。索引的作用类似于图书的目录，可以根据目录中的页码快速找到所需的内容。

1.索引概述

       当数据保存在磁盘类存储介质上时，它是作为数据块存放。这些数据块是被当作一个整体来访问的，这样可以保证操作的原子性。硬盘数据块存储结构类似于链表，都包含数据部分，以及一个指向下一个节点(或数据块)的指针，不需要连续存储。

       记录集只能在某个关键字段上进行排序，所以如果需要在一个无序字段上进行搜索，就要执行一个线性搜索(Linear Search)的过程,平均需要访问 N/2 的数据块，N 是表示所占据的数据块数目。如果这个字段是一个非主键字段(也就是说，不包含唯一的访问入口)，那么需要在N个数据块上搜索整个表格空间。

      但是对于一个有序字段，可以运用二分查找(BinarySearch)，这样只需要访问 1og2(N)的数据块。这就是为什么数据表使用索引后性能可以得到本质上提高的原因。

      索引是对记录集的多个字段进行排序的方法。在一张表中为一个字段创建一个索引，将创建另外一个数据结构，包含字段数值以及指向相关记录的指针，然后对这个索引结构进行排序，允许在该数据上进行二分法排序。

      使用索引的副作用是需要额外的磁盘空间。对于 MyISAM 引擎而言，这些索引是被统一保存在一张表中的。如果很多字段都建立了索引，那么会占用大量的磁盘空间，这个文件将很快到达底层文件系统所能够支持的大小限制。

2.索引作用

      在索引列上，除了上面提到的有序查找之外，数据库利用各种各样的快速定位技术，能够大大提高查询效率。特别是当数据量非常大，查询涉及多个表时，使用索引往往能使查询速度加快成千上万倍。

       此查询结果应该为1000行，每行包含3个相等的值。在无索引的情况下处理此查询，必须寻找3个表所有的组合，以便得出与WHERE子句相配的那些行。而可能的组合数目为 1000x1000x1000(十亿)，显然查询将会非常慢。
如果对每个表进行索引，就能极大地加速查询进程，利用索引的查询处理如下

      从表 t1 中选择第一行，查看此行所包含的数据。
      使用表 t2 上的索引,直接定位 t2 中与 t1 的值匹配的行。同理,利用表 t3上的索引，直接定位_t3 中与 t1的值匹配的行。
       扫描表 t1 的下一行并重复前面的过程，直到遍历 t1 中所有的行。

       在此情形下，仍然对表 t1执行了一个完全扫描，但能够在表 t2 和 t3 上进行索引查找直接取出这些表中的行，比未用索引时要快一百万倍。

        利用索引，MySQL 加速了WHERE子句满足条件行的搜索，而在多表连接查询时、在执行连接时加快了与其他表中的行匹配的速度。

3.索引的分类

       在数据库表中,对字段建立索引可以大大提高查询速度。通过善用这些索引,可以令 MySQL 的查询和运行更加高效。索引是快速搜索的关键。MySQL 索引的建立对于 MySQL, 的高效运行是非常重要的。下面介绍几种常见的 MySQL 索引类型。

       从物理存储的角度来划分，索引分为聚簇索引和非聚索引两种，聚索引是按照数据存放的物理位置为顺序的，而非聚簇索引就不一样了;聚簇索引能提高多行检索的速度，而非聚簇索引对于单行的检索更快。

3.1普通索引

      普通索引是最基本的索引，它没有任何限制，也是大多数情况下用到的索引。它有以下几种创建方式。

直接创建索引:

      column 是指定要创建索引的列名。通常可以考虑将查询语句中在JOIN 子句和WHERE子句里经常出现的列作为索引列。

       其中 length 是可选项。如果忽略 length 的值，则使用整个列的值作为索引。如果指定使用列前的 length个字符来创建索引,就是使用列的一部分来创建索引，这样有利于减小索引文件的大小，节省索引列所占的空间。在某些情况下，只能对列的前缀进行索引。索引列的长度有一个最大上限255个字节(MyISAM和 InnoDB 表的最大上限为1000个字节)，如果索引列的长度超过了这个上限，就只能用列的前缀进行索引。另外，BLOB或TEXT类型的列也必须使用前缀索引。column 和 length 的含义，在下面创建索引的操作语句中意义相同。

修改表结构的方式添加索引:

创建表结构时，同时创建索引:

3.2唯一索引

      唯一索引与普通索引类似，不同的就是:唯一索引的索引列的值必须唯一，但允许有空值(注意和主键不同)。如果是组合索引，则列值的组合必须唯一。唯一索引创建方法和普通索引类似。

创建唯一索引:

修改表结构的时候添加唯一索引:

创建表的时候同时创建唯一索引:

3.3 主键索引

      主键索引是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值一般是在建表的时候同时创建主键索引。

3.4组合索引(最左前缀)

      平时用的 SQL 查询语句一般都有比较多的限制条件，所以为了进一步榨取MySQL 的效率，就要考虑建立组合索引。在组合索引的创建中，有两种场景，即为单列索引和多列索引。下面通过一个场景来具体说明单列索引和多列索引。

       在一个 user 用户表中，有name，age，sex 三个字段，分别分三次建立了INDEX 普通索引。那么在 select *from user where name =’’AND ageANDsex=’:数据查询语句中就会分别检索三条索引，虽然扫描效率有所提升，但却还未达到最优。这个时候就需要使用到组合索引(即多列索引)，如下所示。

       在 MySQL 中，有一个知识点叫最左原则。下面的 select 语句的 where 条件是依次从左往右执行的。

5.全文索引(FULLTEXT)

       MySQL从3.23.23版开始支持全文索引和全文检索。在MySQL5.6 版本以前 FULLTEXT 索引仅可用于 MyISAM 表，在 5.6之后 innodb 引警也支持FULLTEXT索引;他们可以从CHAR、             VARCHAR或TEXT列中作为CREATE TABLE语句的一部分被创建，或是随后使用ALTERTABLE或CREATEINDEX 被添加。

       对于较大的数据集，将资料输入一个没有FULLTEXT 索引的表中，然后创建索引，其速度比把资料输入现有FULLTEXT索引的速度更快。不过切记对于大容量的数据表，生成全文索引是一个非常消耗时间、非常消耗硬盘空间的做法。

创建表的全文索引:

修改表结构添加全文索引:

mysql>ALTER TABLE article ADD FULLTEXT index_content(content);

直接创建索引:

mysqI>CREATE FULLTEXT INDEX index_content ON article(content);

3..4 查看索引

        MySQL 数据表索引已经创建好了，那么如何才能查看刚刚创建的索引?或者怎么去查看表内已经存在的索引?有以下两种查看当前索引的方式。

mysql>show index from tablename ;
mysql>show keys from tablename ;

以 renyuan 表为例，查看 renyuan 表的索引内容,

3.5删除索引

       索引在创建之后,是会占用一定的磁盘空间的,因此表内如果有不再使用的索引,从数据库性能方面考虑，最好是删除无用索引。索引的删除有如下两种方法。

DROP INDEX索引名ON 表名;

ALTER TABLE表名DROPINDEX索引名;

以 renyuan 表为例，删除renyuan 表的索引。

添加索引

mysql>create index name index on employee(name);

mysql> alter table renyuan add index age index(age)

删除索引

mysql> drop index name index on employee;

mysql> alter table renyuan drop index age index,