MYSQL索引~

一、什么是索引

索引是创建在表上，是对数据库表中的一列或者多列的值进行排序的一种结果。

优点在于可以提高查询的速度；

缺点在于并不是索引越多越好，过多的索引会引起CPU的使用高居不下（因为由于数据的改动引起索引文件的改动，过多的磁盘IO造成CPU负载过高）

二、索引的分类

普通索引：没有任何的限制条件，可以给任何类型的字段创建普通索引。

唯一性索引：使用unique修饰的字段，值不会重复，主键索引隶属于唯一性索引。

主键索引：使用primary key修饰的字段会自动创建索引。

单列索引：在一个字段上创建的索引。

多列索引：在表的多个字段上创建索引。

全文索引：使用FULLTEXT参数可以设置全文索引，只支持char、varchar、text类型的字段上。

三、索引的创建与删除

1、索引的创建

在创建表的时候指定索引字段

create table table_name{

id INT,

name varchar(12),

index(id)

};

index（属性名）：指定当前的属性名为索引

在已经创建的表上添加索引

create [unique|fulltext|spatial] index 索引名 (idx_id) on 表名(属性名)；

alter table 表名 add[unique|fulltext|spatial] index 索引名(属性名);

2、删除索引：drop index 索引名 on 表名；

四、索引的执行过程分析

首先创建一个表------>在表中插入基础的数据----->在某一条件下进行查询通过explain分析SQL执行过程----->将表中的某一个字段设置为索引------>重复查询的过程（在某一条件下进行查询通过explain分析SQL执行过程）

创建一个Student表，表里字段分别为：
Student(SID,Sname,Sage,Ssex) 
--SID 学生编号,Sname 学生姓名,Sage 年龄,Ssex 学生性别

插入基础数据：
select * from Student;
+-----+----------+------+------+
| SID | Sname    | Ssex | Sage |
+-----+----------+------+------+
|   1 | zhaolei  | nan  |   20 |
|   2 | qiandian | nan  |   20 |
|   3 | sunfen   | nan  |   21 |
|   4 | wulan    | nv   |   18 |
+-----+----------+------+------+
数据库里插入了四条数据：

查询姓名为"zhaolei"的数据记录，通过explain分析SQL执行过程
mysql> explain select * from Student where Sname = 'zhaolei'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: Student
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
        Extra: Using where
1 row in set (0.01 sec)

id 列是在QEP 中展示的表的连续引用。

 ref 列可以被用来标识那些用来进行索引比较的列或者常量。

 type：type 列代表QEP 中指定的表使用的连接方式。

select_type 列提供了各种表示table 列引用的使用方式的类型。最常见的值包括SIMPLE、PRIMARY、DERIVED 和UNION。其他可能的值还有UNION RESULT、DEPENDENT SUBQUERY、DEPENDENT UNION、UNCACHEABLE UNION 以及UNCACHEABLE QUERY。

SIMPLE对于不包含子查询和其他复杂语法的简单查询，这是一个常 见的类型。

PRIMARY这是为更复杂的查询而创建的首要表(也就是最外层的表)。这个类型通常可以在DERIVED 和UNION 类型混合使用时见到。

DERIVED当一个表不是一个物理表时，那么就被叫做DERIVED。

DEPENDENT SUBQUERY这个select-type 值是为使用子查询而定义的。

UNION这是UNION 语句其中的一个SQL 元素。

table 列是EXPLAIN 命令输出结果中的一个单独行的唯一标识符。这个值可能是表名、表的别名或者一个为查询产生临时表
 的标识符，如派生表、子查询或集合。

possible_keys 列指出优化器为查询选定的索引。

key 列指出优化器选择使用的索引。

key_len 列定义了用于SQL 语句的连接条件的键的长度。

 rows 列提供了试图分析所有存在于累计结果集中的行数目的MySQL 优化器估计值。

partitions：partitions 列代表给定表所使用的分区。这一列只会在EXPLAIN PARTITIONS 语句中出现。

Extra：Extra 列提供了有关不同种类的MySQL 优化器路径的一系列额外信息。Extra 列可以包含多个值，可以有很多不同的取值，并且这些值还在随着MySQL 新版本的发布而进一步增加。

可以通过possible_keys、key没有用到索引，并且通过rows: 4可以看出直接全表扫描一遍才找到"zhaolei"的信息，
假设表中4百万数据，那么查找"zhaolei"同学就需要扫描4百万的数据，效率比较低

以名字创建索引：
create index idx_name on Student (Sname);

mysql> explain select * from Student where Sname = 'zhaolei'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: Student
         type: ref
possible_keys: idx_name
          key: idx_name
      key_len: 27
          ref: const
         rows: 1
        Extra: Using index condition
1 row in set (0.01 sec)
注意：这次查询"zhaolei"同学的信息，只在表中查询一行数据就可以得到（rows: 1）

记住：explain命令，可以查看SQL执行计划，分析SQL语句是否正确使用索引