MySQL索引创建使用

目录

1. 索引的定义

2. 索引分类

3. 索引SQL

3.1 创建表时创建索引

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

a、通过create语法创建

b、通过alter语法创建

3.3 删除索引

3.4 查看索引

3.5 索引的执行过程

---

1. 索引的定义

索引是提供了一种数据结构来对数据进行检索，对数据库表中的一列或者是多列的值进行排序。

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

缺点：索引并不是越多越好，过多的索引会导致CPU的使用率居高不下。

2. 索引分类

• 普通索引：没有限制条件，对任何的类型的字段都可以添加普通索引；
• 唯一性索引：使用unique修饰的字段，值是不能重复的，该字段添加索引是唯一性索引；
• 主键索引：使用primary key修饰的字段会自动创建索引；
• 单列索引：在单个属性上创建的索引；
• 多列索引：联合索引：在多个属性上创建的索引；
• 全文索引：使用fulltext参数设置的是全文索引，只支持char\varchar和text类型字段上，常使用在数据量较大的字符串类型上，方便提高查询效率 (myisam 存储引擎支持)。

3. 索引SQL

3.1 创建表时创建索引

create table table_name(

属性名1 类型[完整性约束],

属性名2 类型[完整性约束],

。。。

属性名n 类型[完整性约束],

[unique|fulltext] index [别名](属性名)

);

例如：

create table jing4(
id int primary key ,
name varchar(5),
index idx_name(name)
);

索引创建展示：

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

a、通过create语法创建

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

例如：

create index idx_id_name on jing4 (id,name);

b、通过alter语法创建

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

例如：

alter table jing4 add index idx_name_1 (name);

3.3 删除索引

drop index 索引名 on 表名；

例如：

 drop index idx_name_1 on jing4;

3.4 查看索引

show index from 表名

示例：

show index from jing4;

3.5 索引的执行过程

注意：研究索引的执行过程使用explain查看SQL的执行计划

explain的使用是直接在查询SQL前添加该关键字即可

分析字段：

• possible_keys表示SQL执行可能命中的索引;
• key属性表示在执行过程中实际使用的索引名称;
• rows:表示查询操作影响表中数据的行数.

给定需求：对jing3表中查询姓名为'静静'的用户信息

• 在name不添加索引时，查询操作的执行计划

在name未添加索引时，执行过程中没有使用索引，查询时是全表扫描(rows=4)，如果数据库表数据量特别大时，这种查询效率是比较低的.

• 添加索引idx_name后，在分析SQL执行计划：

添加索引后，通过explain分析，执行过程中命中了idx_name的索引，执行过程只影响了一行数据，在大数据量查询是使用索引的效率有大的提高。