mysql 键、索引与联合索引 (key & index)

mysql中key的概念

  mysql中key为数据库中的存储结构，一种用法是通过添加其他关键字进行约束，一种作用是进行查询优化（会同步创建索引）；关键字约束主要有三种

1. primary key（主键，该列元素值唯一，不可以为空，一张表只可以存在一个主键）
2. unique key（唯一键，该列元素值唯一，可以为空，一张表可以存在多个唯一键）
3. foreign key（外键，关联另一张表的主键，可以有多个外键）

mysql中联合索引的概念

  mysql中索引为底层的数据结构，用于优化、加速查询，索引也是一张表，对应底层索引文件，索引类型一般包括B+及hash索引。索引分为单列索引及组合索引，组合索引为由多个列组建的索引，创建命令如下：

KEY index_single (id);
KEY index_combine (name, age, sex);

第一个语句表示在id字段上建立单值索引，第一个语句表示用name、age、sex建立联合索引；联合索引配置后，根据最左匹配原则，对于(name)、(name, age)及(name, age, sex)查询可以使用索引，而对于(age, sex)的查询则不可以使用，例如下列语句：

# 使用索引
select * from Table where name="test"; 
# 使用索引
select * from Table where name="test" and age=10;
# 使用索引
select * from Table where name="test" and age=10 and sex=1;
# 不使用索引
select * from Table where age=10 and sex=1;

mysql中外键概念

  mysql中外键用于建立两个表之间的关联关系，一张表可以包含一个或多个外键，外键通常指向另一张表的主键，设置外键的表一般称为子表，被指向的表一般称为父表。其配置语法如下：

foreign key (pid) references parent(id) on delete cascade on update cascade

pid为当前表中的一个字段，表示父id，parent为其指向的表，id为parent表中的主键。关键字foreign key为外键定义关键字，references表示指向，on delete cascade表示父表删除时字表级联更新，即父表删除对应记录，字表同步删除；on update cascade即父表更新时字表同步更新。
示例：

# create parent table -- Country
CREATE TABLE `Country` (
  `nationid` int(10) NOT NULL,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`nationid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

# create child table -- Province
CREATE TABLE `Province` (
  `provinceid` int(10) NOT NULL,
  `name` varchar(10) DEFAULT NULL,
  `nationid` int(10) DEFAULT NULL,
  PRIMARY KEY (`provinceid`),
  KEY `nationid` (`nationid`),
  CONSTRAINT `province_ibfk_1` FOREIGN KEY (`nationid`) REFERENCES `Country` (`nationid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1

# insert test data
insert into Country values(1, "China");
insert into Province values(1, "Zhejiang", 1);
insert into Province values(2, "Shanghai", 1);

分别执行更新语句及删除语句：

update Country set nationid=2 where nationid=1;
delete from Country where nationid=1; 
insert into Province values (1, "Shandong", 3)

第一条语句更新后，则Province表中所有nationid也被更新为2；第二条删除语句执行后，Province表中所有nationid为2
的也被删除；第三语句执行会报错，因为父表Country中无相应的数据。