MySQL 索引

索引

1、定义：对数据库表的一列（字段）或多列的值进行排序的一种结构(Btree方式)
2、优点：加快数据检索速度
3、缺点：占用物理存储空间；当对表中数据更新时,索引需要动态维护,降低数据维护速度
4、索引示例
　　1、开启运行时间检测 ：set profiling=1;
　　2、执行查询语句
　　　　select name from t1 where name="lucy99999";
　　3、查看执行时间
　　　　show profiles;
　　4、在name字段创建索引
　　　　create index name on t1(name);
　　5、再执行查询语句
　　　　select name from t1 where name="lucy88888";
　　6、查看执行时间
　　　　show profiles;

索引分类

1、普通索引(index)
　　1、使用规则:可设置多个字段
　　2、字段值无约束
　　3、key标志 ：MUL
　　　　2、创建index
　　　　　　1、创建表时
　　　　　　　　create table 表名(...
　　　　　　　　index(字段名),index(字段名));
　　　　　　2、已有表
　　　　　　　　create index 索引名 on 表名(字段名);
　　　　　　　　create index name on t3(name);
　　　　　　3、查看索引
　　　　　　　　1、desc 表名; --> KEY标志为：MUL
　　2、show index from 表名G;
　　　　4、删除索引
　　　　　　drop index 索引名 on 表名;
2、唯一索引(unique)
　　1、使用规则:可设置多个字段
　　2、约束 ：字段值不允许重复,但可为 NULL
　　3、KEY标志 ：UNI
　　　　2、创建
　　　　　　1、创建表时创建
　　　　　　　　unique(字段名),
　　　　　　　　unique(字段名)
　　2、已有表
　　　　create unique index 索引名 on 表名(字段名);
　　3、查看、删除 同 普通索引
3、主键索引(primary key)
　　　　自增长属性(auto_increment,配合主键一起使用)
　　1、使用规则:只能有一个主键字段
　　2、约束 ：不允许重复,且不能为NULL
　　3、KEY标志 ：PRI
　　4、通常设置记录编号字段id,能唯一锁定一条记录
　　　　2、创建
　　　　　　1、创建表时
　　　　　　　　(id int primary key auto_increment,
　　　　　　　　)auto_increment=10000;##设置自增长起始值
　　　　　　　　已有表添加自增长属性：
　　　　　　　　alter table 表名 modify id int auto_increment;
　　　　　　　　已有表重新指定起始值：
　　　　　　　　alter table 表名 auto_increment=20000;
　　　　　　2、已有表
　　　　　　　　alter table 表名 add primary key(id);
　　　　　　3、删除
　　　　　　　　1、删除自增长属性(modify)
　　　　　　　　　　alter table 表名 modify id int;
　　　　　　　　2、删除主键索引
　　　　　　　　　　alter table 表名 drop primary key;
4、外键索引

　　1、定义：让当前表字段的值在另一个表的范围内选择

　　2、语法：

　　　　foreign key(参考字段名)
　　　　references 主表(被参考字段名)
　　　　on delete 级联动作
　　　　on update 级联动作
　　3、使用规则
　　　　1、主表、从表字段数据类型要一致
　　　　2、主表被参考字段必须是主键
　　4、示例
　　　　表1、缴费信息表(财务)
　　　　id  姓名   班级  缴费金额
　　　　1  唐伯虎  AID06   300
　　　　2  点秋香  AID06   260
　　　　3  祝枝山  AID06   250

　　　　表2、学生信息表(班主任)
　　　　id  姓名  缴费金额
　　　　1  唐伯虎  300
　　　　2  点秋香  260
　　　　3 XXXXXXXXXXXXXXXXX
　　1、创建表
　　　　表1(主表)：
　　　　create table jftab(
　　　　id int primary key,
　　　　name varchar(15),
　　　　class char(5),
　　　　money int
　　　　);

　　　　insert into jftab values
　　　　(1,"唐伯虎","AID06",300),
　　　　(2,"点秋香","AID06",260),
　　　　(3,"祝枝山","AID06",250);
　　　　表2(从表)、
　　　　create table bjtab(
　　　　stu_id int,
　　　　name varchar(15),
　　　　money int,
　　　　foreign key(stu_id) references jftab(id)
　　　　on delete cascade
　　　　on update cascade
　　　　);
　　　　insert into bjtab values
　　　　(1,"唐伯虎",300),(2,"点秋香",260);
　　5、删除外键
　　　　alter table 表名 drop foreign key 外键名;
　　　　外键名 ：show create table 表名;

　　6、级联动作
　　　　1、cascade
　　　　　　数据级联删除、更新(参考字段)
　　　　2、restrict(默认)
　　　　　　从表有相关联记录,不允许主表操作
　　　　3、set null
　　　　　　主表删除、更新,从表相关联记录字段值为NULL
　　7、已有表添加外键
　　　　alter table 表名 add
　　　　foreign key(参考字段) references 主表(被参考字段)
　　　　on delete ...
　　　　on update ...

注意：在已有表中添加外键时，会受到表中原有数据的限制