MySQL的索引操作

索引概述

• 定义

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。

• 优点

加快数据检索速度,提高查找效率

• 缺点

占用数据库物理存储空间
当对表中数据更新时,索引需要动态维护,降低数据写入效率

索引分类

• 普通(MUL)

普通索引 ：字段值无约束,KEY标志为 MUL

• 唯一索引(UNI)

唯一索引(unique) ：字段值不允许重复,但可为 NULL,KEY标志为 UNI

• 主键索引（PRI）

一个表中只能有一个主键字段, 主键字段不允许重复 ,且不能为NULL，KEY标志为PRI。通常设置记录编号字段id,能唯一锁定一条记录

索引创建

主键索引的创建方法已经在数据表中介绍过了，下面是普通索引和唯一索引的创建方法：

• 创建表时直接创建索引

create table 表名(
字段名 数据类型，
字段名 数据类型，
index(字段名),
index(字段名),
unique(字段名)
);

• 在已有表中创建索引：

create [unique] index 索引名 on 表名(字段名);

• 查看索引

1、desc 表名;  --> KEY标志为：MUL 、UNI。
2、show index from 表名;

• 扩展： 借助性能查看选项去查看索引性能

show variables like 'profiling'；

set  profiling = 1； 打开功能 （项目上线一般不打开）

show profiles  查看语句执行信息

• 删除索引

drop index 索引名 on 表名;

• 已有表添加主键索引或自增长属性

alter table 表名 add primary key(id);
alter table 表名 modify id int auto_increment;

• 创建复合主键

primary key（uid,pid）
此时两个字段只要不都相同即可。

• 删除主键索引或自增长属性

alter table 表名 modify id int;  要先删除自增长，因为它只作用于主键字段
alter table 表名 drop primary key;

扩展：索引结构

mysql最为常用的就是使用B-Tree结构构建索引表。那么为什么树形结构能够加快查找速度呢？

• 二叉查找树

B 树是对查找结构的进一步优化，加快了区间查找效率：