索引类型

索引类型

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外键不见了