索引基础知识

索引介绍
索引是一种数据结构，是在表上创建的。是对数据库表中的一列或者是多列的值进行排序的一种结果，使用索引可以提高查询速度，避免全表扫描。

优势：提高查询效率，避免全表扫描

缺点：过多的索引会导致CPU的使用率过高

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

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

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

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

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

全文索引：使用fulltext参数设置全文索引，只支持char\varchar和text类型的字段，常用在数据量较大的字符串类型上，可以提高查询速度，只有MyISAM存储引擎支持

聚集性索引：在InNoDB引擎下建立的索引，将索引及数据存放在一起

非聚集性索引：在MyISAM引擎下建立的索引，将索引和数据分开存储在不同的文件中

索引SQL
1.在创建表时创建索引
create table 表名 (
属性名 属性类型 [完整性约束],
属性名 属性类型 [完整性约束],
......
属性名 属性类型 [完整性约束],
[unique|fulltext|spatial] index|key 别名[属性名] [asc|desc]
);
说明：

unqiue|fulltext|spatial都是可选参数

unqiue表示索引为唯一性索引

fulltext表示索引为全文索引

spatial表示索引为空间索引

index|key参数指需要创建索引指示词，属性必须是已经存在的，如果不存在，需要先创建

别名：可以给自己的所创建的属性起一个别名，可以是任意的字段

asc|desc：可选参数， 表示升序序列和降序序列

示例如下：

创建普通索引：

create table index1 (
id int ,
name varchar(20),
sex  varchar(2),
index (name) 
)

 

创建唯一性索引：

create table index2(

id int ,

name varchar(20),

sex varchar(2),

unique index idx_id (id)

);

 

2.在已经存在的表上创建索引

第一种：通过create语法创建索引

create [unique|fulltext|spatial] index 索引名 on 表名 (属性名 [asc|desc]);

示例SQL：

 

第二种：通过alter语法创建

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

 

3.删除索引

drop index 索引名 on 表名;

 

索引执行过程

查询student为例演示索引执行过程：

 

 

该表中只有主键索引

查询student表中Sname为“tu”开头的同学的信息

select * from student where Sname like 'tu%';

需用explain查看SQL语句的执行计划，

使用示例，在查询的select前面添加上explain关键字

mysql> explain select * from student where Sname like 'tu%'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 13
        Extra: Using where
1 row in set (0.00 sec)

explain关键字执行计划如上所示：

table属性：表示查询的表

possible_keys属性：可能用到的key,显示null表示没有用到索引

key属性：真实执行过程中使用到的索引

rows属性：表名当前查询操作影响数据的行数

Extra属性：备注信息，显示SQL执行过程中使用到的关键信息

从上面的执行计划可知：在student表中查询“tu”开头的学生信息，是做了整个表查询操作，把表中的所有行（rows：13）全部扫描一遍，才找到需要的“tu”开头的学生信息，加入当前表中有500万行数据，要查找“tu”开头的学生信息，要搜索的是500万行数据，这样效率就太低了

解决方案：添加索引

 

添加索引之后，在分析执行计划

mysql> explain select * from student where Sname like 'tu%'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
         type: range
possible_keys: idx_name,idx_name_age
          key: idx_name
      key_len: 27
          ref: NULL
         rows: 2
        Extra: Using index condition
1 row in set (0.01 sec)

在添加索引之后，在student表中查询”tu“开头的学生信息，发现在执行过程中会有索引选择idx_name,idx_name_age，真正执行过程使用了idx_name，整个数据查询影响的数据行数是2行数，可以发现，使用索引对SQL语句查询效率提升是很大的，所以，记住explain关键字，可以查看SQL执行计划，帮分析SQL语句能否正确使用索引