索引类型

索引类型

1、普通索引
2、唯一性索引
3、主键索引
4、复合索引

普通索引

最基本的索引,不具备唯一性,就是加快查询速度
创建普通索引:
方法一:创建表时添加索引
create table 表名(列定义…… ,index 索引名称 (字段),index 索引名称 (字段));
注:可以使用key,也可以使用index。index 索引名称(字段),索引名称,可以加也可以不加,不加索引名默认使用字段名作为索引名。
mysql> create table demo( id int(4), name varchar(20), pwd varchar(20), index(pwd) );
注意:index和 key 是相同的
mysql> create table demo1( id int(4), name varchar(20), pwd varchar(20), key(pwd) );
mysql> create table demo2( id int(4), name varchar(20), pwd varchar(20), key index_pwd(pwd) ); #加上名称
查看索引
mysql> desc demo;
在这里插入图片描述
注:如果Key是MUL, 就是一般性索引,该列的值可以重复, 该列是一个非唯一索引的前导列(第一列)或者是一个唯一性索引的组成部分但是可以含有空值NULL。就是表示是一个普通索引
方法二: 当表创建完成后,使用alter为表添加索引:
alter table 表名 add index 索引名称 (字段1,字段2…);
我们先删除索引
mysql> alter table demo drop key pwd; 注意此处的pwd指的是索引的名称,而不是表中pwd的那个字段
再用alter添加
mysql> alter table demo add key(pwd);
查看索引:
mysql> desc demo;
在这里插入图片描述
也可以执行下面的操作查看索引
mysql> show index from tablename;

mysql> show keys from tablename;
测试:从book库中的books表查询bname=’ Linux傻瓜书’的图书信息
创建索引前:
在这里插入图片描述
通过explain模拟执行sql查询语句:
在这里插入图片描述
在bname列上创建索引
mysql> alter table books add index index_bname(bname);
在这里插入图片描述
创建索引后再次执行查询bname=’ Linux傻瓜书’的图书信息
在这里插入图片描述
注:使用EXPLAIN关键字可以模拟优化器执行SQL查询语句,从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。

唯一索引

与普通索引基本相同,但有一个区别:索引列的所有值都只能出现一次,即必须唯一,不能有重复的值,用来约束内容。唯一性允许有NULL值。
方法一:创建表时加唯一索引
注意:常用在值不能重复的字段上,比如说用户名,电话号码,身份证号。
create table 表名(列定义,unique key 索引名1(字段),unique key 索引名2(字段));
mysql> create table demo3(id int(4) auto_increment primary key, uName varchar(20), uPwd varchar(20), unique index (uName));
在这里插入图片描述
方法二:修改表时加唯一索引
alter table 表名 add unique 索引名 (字段);
mysql> alter table demo3 drop key uName; #删除之前创建的唯一索引
mysql> desc demo3;
在这里插入图片描述
mysql> alter table demo3 add unique(uName); #添加唯一索引
在这里插入图片描述

主键索引

设置主键就会在该列上自动创建主键索引,每个表最多只能有一个主键。主键列要求列的所有内容必须唯一。主键索引是一种特殊的唯一索引, 但不允许有空值。
创建主键索引
创建表创建主键索引
mysql> create table demo5( id int(4) not null auto_increment primary key, name varchar(20) default null);

mysql> create table demo5( id int(4) not null auto_increment, name varchar(20) default null,primary key(id));
mysql> desc demo5;
在这里插入图片描述
mysql> show index from demo5 \G;
在这里插入图片描述
方法二:创建表后添加
不推荐这种方法,如果生产的数据无法保证唯一,创建主键会报错,主键索引一般是在设计数据库时,创建表来添加。
先删除主键索引,再创建
mysql> alter table demo5 drop primary key;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
删除遇到这种报错,是auto_increment的原因。先取消自增长
mysql> alter table demo5 change id id int(4) not null; #取消自增长
mysql> alter table demo5 drop primary key; #删除主键索引
mysql> alter table demo5 change id id int(4) not null primary key auto_increment;
#添加主键索引
mysql> desc demo5;
在这里插入图片描述
主键索引,唯一性索引区别:主键索引不能有NULL,唯一性索引可以为Null

复合索引

索引可以包含一个、两个或更多个列。两个或更多个列上的索引被称作复合索引
例: 创建一个表,存放服务器允许或拒绝的IP和port,要数值中IP和port字段唯一。
mysql> create table firewall ( host varchar(15) not null ,port smallint(4) not null ,access enum(‘deny’,‘allow’) not null, primary key (host,port)); #复合主键
mysql> desc firewall;
在这里插入图片描述
mysql> insert into firewall values(‘10.96.52.46’,22,‘deny’);
mysql> insert into firewall values(‘10.96.52.46’,21,‘allow’);
mysql> insert into firewall values(‘10.96.52.46’,21,‘allow’);
ERROR 1062 (23000): Duplicate entry ‘10.96.52.46-21’ for key ‘PRIMARY’
mysql> insert into firewall values(‘10.96.52.46’,22,‘allow’);
ERROR 1062 (23000): Duplicate entry ‘10.96.52.46-22’ for key ‘PRIMARY’
经过测试,复合主键必须要有一个字段的值是不能重复的,插入一样的数据就会报错,数值要唯一。
为了进一步提升MySQL的效率,就要考虑建立组合(复合)索引。例如:创建一个表,包含如下字段
mysql>create table mytable(id int not null,username varchar(16) not null,city varchar(50)not null,age int not null);
将 username, city, age建到一个索引里
mysql>alter table mytable add index username_city_age(username,city,age);
建立这样的组合索引,其实是相当于分别建立了下面三组组合索引:
username,city,age username,city username 为什么没有 city,age这样的组合索引呢?这是因为MySQL组合索引“最左前缀”的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这三列的查询都会用到该组合索引,下面的几个SQL就会用到这个组合索引:
SELECT * FROM mytable WHREE username=“admin” AND city=“郑州”
SELECT * FROM mytable WHREE username=“admin”
而下面几个则不会用到:
SELECT * FROM mytable WHREE age=20 AND city=“郑州”
SELECT * FROM mytable WHREE city=“郑州”
如果对多列进行索引(组合索引),列的顺序非常重要,MySQL仅能对索引最左边的前缀进行有效的查找。
索引设计原则:
1、索引并非越多越好,每个额外的索引都要占用额外的磁盘空间,并降低增、删、改操作的性能,因此,索引越多,所花的时间越长。
2、数据量不大不需要建立索引
3、在where子句中出现的字段、常排序(order by 字段)和分组(group by 字段)的列需要建立索引
4、唯一性约束对应使用唯一性索引
5、MySQL只对以下操作符才使用索引:<,<=,=,>,>=,between,in, 以及某些时候的like(不以通配符%开头的情形)。
外键约束
什么是外键约束:
foreign key就是表与表之间的某种约定的关系,由于这种关系的存在,我们能够让表与表之间的数据,更加的完整,关连性更强。
关于完整性,关联性我们举个例子
有二张表,一张是用户表,一张是订单表:
1、如果我删除了用户表里的用户,那么订单表里面与这个用户有关的数据,就成了无头数据了,不完整了。
2、如果我在订单表里面,随便插入了一条数据,这个订单在用户表里面,没有与之对应的用户。这样数据也不完整了。
如果有外键的话,就方便多了,可以不让用户删除数据,或者删除用户的话,通过外键同样删除订单表里面的数据,这样也能让数据完整。
如果表A的主键字段是表B中的字段,则该字段称为表B的外键,表A称为主表,表B称为从表。外键是用来实现参照完整性的,外键约束方式将可以使两张表紧密的结合起来,特别是修改或者删除的级联操作将使得日常的维护工作更加轻松。外键主要用来保证数据的引用完整性和一致性。
创建外键约束方法一:
外键: 每次插入或更新时,都会检查数据的完整性。
语法:
create table 表名(…,[CONSTRAINT [外键名]] FOREIGN KEY [字段名,字段名2……] REFERENCES 主表名 // References [ˈrefrənsɪz][ON DELETE CASCADE |RESTRICT][ON UPDATE CASCADE|RESTRICT]);
关于参数的解释:
RESTRICT: 拒绝对父表的删除或更新操作。
CASCADE: 从父表删除或更新且自动删除或更新子表中匹配的行。ON DELETE CASCADE和ON UPDATE CASCADE都可用
注意:on update cascade是级联更新的意思,on delete cascade是级联删除的意思,意思就是说当你更新或删除主键表,那外键表也会跟随一起更新或删除。
例如:
创建主表,并将sid设为主键
mysql> create table stu(sid int primary key,name varchar(50)not null);
创建从表,并将sid设为外键。
mysql>create table scorel(score double,sid int,constraint fk_stu_scorel_sid foreign key(sid) references stu(sid));
注:创建成功,必须满足以下4个条件:
1、确保参照的表和字段存在。
2、组成外键的字段被索引。
3、必须使用ENGINE指定存储引擎为:innodb.
4、外键字段和关联字段,数据类型必须一致。
我们创建一个数据库,包含用户信息表和订单表
mysql> create database market;
mysql> use market;
mysql> create table user(id int(11) not null auto_increment, name varchar(50) not null default ‘’, sex int(1) not null default ‘0’, primary key(id))ENGINE=innodb;
#创建时,如果表名是sql关键字,使用时,需要使用反引号``
mysql> create table order(o_id int(11) auto_increment, u_id int(11) default ‘0’, username varchar(50), money int(11), primary key(o_id), index(u_id), foreign key order_f_key(u_id) references user(id) on delete cascade on update cascade) ENGINE=innodb;
order 的u_id= user 的 id
注:
1、on delete cascade on update cascade 添加级联删除和更新:
2、确保参照的表user中id字段存在。 组成外键的字段u_id被索引。
3、外键字段和关联字段,数据类型必须一致。
插入测试数据
mysql> insert into user(name,sex)values(‘HA’,1),(‘LB’,2),(‘HPC’,1);
mysql>insert into order (u_id,username,money)values(1,‘HA’,234),(2,‘LB’,146),(3,‘HPC’,256);
查看表数据
mysql> select * from order;
在这里插入图片描述
mysql> select * from user;
在这里插入图片描述
测试级联删除:
mysql> delete from user where id=1; 删除user表中id为1的数据
再查看order表
mysql> select * from order;
在这里插入图片描述
测试级联更新:
mysql> update user set id=6 where id=2;
mysql> select * from user;
在这里插入图片描述
mysql> select * from order;
在这里插入图片描述
可以看到,更新过来了
测试数据完整性
在order表插入一个不存在的用户
mysql> insert into order (u_id,username,money)values(7,‘Find’,346);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (market.order, CONSTRAINT order_ibfk_1 FOREIGN KEY (u_id) REFERENCES user (id) ON DELETE CASCADE ON UPDATE CASCADE)
报错是因为外键约束,order表受user表的约束,在order里面插入一条数据u_id为7用户,在user表里面根本没有,所以插入不进去
解决:
mysql> insert into user values(7,‘Find’,100); #先在user表插入7用户
mysql> insert into order (u_id,username,money)values(7,‘Find’,346);
这里只能在order插入u_id为7的用户
mysql> select * from order;
在这里插入图片描述
方法二:通过ALTER TABLE 创建外键和级联更新,级联删除
语法:
alter table 数据表名称 add
[constraint [约束名称] ] foreign key (外键字段,…) references 数据表(参照字段,…)
[on update cascade]
[on delete cascade]
);
创建orde1表
mysql> create table order1(o_id int(11) auto_increment, u_id int(11) default ‘0’, username varchar(50), money int(11), primary key(o_id), index(u_id)) ENGINE=innodb;
创建外键约束
mysql> alter table order1 add foreign key(u_id) references user(id) on delete cascade on update cascade, ENGINE =innodb;
或:
mysql> alter table order1 add constraint bkforeign key(u_id) references user(id) on delete cascade on update cascade, ENGINE =innodb;
查看创建表执行命令
mysql> show create table order1;
在这里插入图片描述
删除外键:
语法
alter table 数据表名称 drop foreign key 约束(外键)名称
mysql> alter table order1 drop foreign key bk; #删除名称为bk的外键
(单引号里的名称)
mysql> show create table order1;
在这里插入图片描述
可以看见,bk外键不见了

已标记关键词 清除标记
相关推荐
©️2020 CSDN 皮肤主题: 深蓝海洋 设计师:CSDN官方博客 返回首页