0.主键与索引的不同
主键在物理层面上只有两个用途： 惟一地标识一行；作为一个可以被外键有效引用的对象。
索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。下面是主键和索引的一些区别与联系。
1. 主键一定是唯一性索引，唯一性索引并不一定就是主键。
2. 一个表中可以有多个唯一性索引，但只能有一个主键。
3. 主键列不允许空值，而唯一性索引列允许空值。
4. 索引可以提高查询的速度，不会约束字段的唯一非空性;而键主要约束记录的唯一非空性。
5. 不管是主键还是外键都必须建立在索引字段上
一、索引
http://blog.csdn.net/xluren/article/details/32746183
索引是对整张表的统计，可以提高查询效率;但可能降低增删改的效率;索引是用来查找记录的，而键是用来约束记录的。当然unique也对记录有一定的约束作用。
1.索引带来的查询效率
如果正确合理设计并且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。对于没有索引的表，单表查询可能几十万数据就是瓶颈，而通常大型网站单日就可能会产生几十万甚至几百万的数据，没有索引查询会变的非常缓慢。还是以WordPress来说，其多个数据表都会对经常被查询的字段添加索引。建立了索引的表只能在where后使用了该索引才能提高效率。
2.索引定义
索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。数据库查索引远比直接查数据表(遍历整个表)快。
索引分为聚簇索引和非聚簇索引两种，聚簇索引是按照数据存放的物理位置为顺序的，而非聚簇索引就不一样了；聚簇索引能提高多行检索的速度，而非聚簇索引对于单行的检索很快。
3.索引的种类
a.普通索引INDEX
这是最基本的索引，它没有任何限制，比如上文中为title字段创建的索引就是一个普通索引，MyIASM中默认的BTREE类型的索引，也是我们大多数情况下用到的索引。
CREATE INDEX index_name ON table(column(length))
DROP INDEX index_name ON table
b.唯一索引UNIQUE
与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值（注意和主键不同）。如果是组合索引，则列值的组合必须唯一，创建方法和普通索引类似。
CREATE UNIQUE INDEX indexName ON table(column(length))
c.全文索引（FULLTEXT）
MySQL从3.23.23版开始支持全文索引和全文检索，FULLTEXT索引仅可用于 MyISAM 表；不过切记对于大容量的数据表，生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。
4.在列上构造索引
在需要经常使用在where子句后的字段上创建索引
a.单列索引、多列索引
多个单列索引与单个多列索引的查询效果不同，因为执行查询时，MySQL只能使用一个索引，会从多个索引中选择一个限制最为严格的索引。
b.组合索引（最左前缀）(聚集索引)
平时用的SQL查询语句一般都有比较多的限制条件，所以为了进一步榨取MySQL的效率，就要考虑建立组合索引.
例如上表中针对title和time建立一个组合索引：ALTER TABLE article ADD INDEX index_titme_time (title(50),time(10))。
建立这样的组合索引，其实是相当于分别建立了下面两组组合索引：
–title,time
–title
为什么没有time这样的组合索引呢？这是因为MySQL组合索引“最左前缀”的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这两列的查询都会用到该组合索引，如下面的几个SQL所示：
–使用到上面的索引
SELECT * FROM article WHREE title='测试' AND time=1234567890;
SELECT * FROM article WHREE title='测试';
–不使用上面的索引(这就是组合索引的特点)
SELECT * FROM article WHREE time=1234567890;
5.如何合理的创建索引
a. 何时使用聚集索引或非聚集索引？
动作描述使用聚集索引使用非聚集索引
列经常被分组排序使用使用
返回某范围内的数据使用不使用
一个或极少不同值不使用不使用
小数目的不同值使用不使用
大数目的不同值不使用使用
频繁更新的列不使用使用
外键列使用使用
主键列使用使用
频繁修改索引列不使用使用
b.索引不会包含有NULL值的列
只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。
c.使用短索引
对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
d.索引列排序
MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。
e.like语句操作
一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
f.不要在列上进行运算
例如：select * from users where YEAR(adddate)<2007，将在每个行上进行运算， 这将导致索引失效而进行全表 扫描，因此我们可以改成：select * from users where adddate<’2007-01-01′。关于这一点可以围观： 一个单引号引发的MYSQL性能损失。
g.索引的适用条件
MySQL只对一下操作符才使用索引：<,<=,=,>,>=,between,in,以及某些时候的like(不以通配符%或_开头的情形)。而理论上每张表里面最多可创建16个索引，不过除非是数据量真的很多，否则过多的使用索引也不是那么好玩的，比如我刚才针对text类型的字段创建索引的时候，系统差点就卡死了。
二、键约束
键是比索引更高一个层次的约束，通过创建key时，数据库会自动在该字段上创建索引。所以可以说键是在索引的基础上实现的。键有2种：主键和外键。
1.主键PRIMARY KEY
a.主键的作用:（唯一非空约束）
主键是对该字段上的记录的约束：使得该字段不能重复也不能为空，所以主键被认为是NOT NULL和UNIQUE约束最好的结合，就像记录的身份证一样，因此一个表只能有一个主键 。如果这些列没有被明确地定义为NOT NULL，MySQL会隐含地定义这些列。
（插入记录时，自动检查该字段是否唯一非空）
b.声明主键的方法：
CREATE TABLE tbl_name ([字段描述省略...], PRIMARY KEY(index_col_name));
LTER TABLE tbl_name ADD PRIMARY KEY (index_col_name,…);
c.组合主键（联合主键）
主键分为单主键和联合主键。键生于索引而高于索引。主键其实也是索引，甚至在MySQL的术语里面“键”就等于“索引”，所以“外键”一定要先设为“索引”，这个咱们下篇日志再来讨论。所以主键也应该和索引一样，既可以作用于单独的字段，又可以作用于多个字段。组合的主键，每个列都会隐含定义NOT NULL约束，且其二者加在一起被定义了UNIQUE 惟一约束。将会把多字段的组合约束为唯一非空。
create table firewall(
host varchar(11) not null,
port smallint(4),
access enum('deny', 'allow') not null,
primary key (host,port)
)
INSERT INTO firewall (host ,port ,access)
VALUES ('202.65.3.87', '21', 'deny');
/*插入一条新的记录，没有啥问题1 row(s) inserted.*/
INSERT INTO firewall (host ,port ,access)
VALUES ('202.65.3.87', '21', 'deny');
/*插入失败，因为host 加port的主键值202.65.3.87-21已经存在了
#1062 - Duplicate entry '202.65.3.87-21' for key 'PRIMARY'
*/
INSERT INTO firewall( host, port, access )
VALUES ('192.168.0.1', NULL , 'deny')
/*
没声明NOT NULl port也不能为NULL
#1048 - Column 'port' cannot be null
*/
组合主键约束：组合后的字段不能为空，也不能重复。
2.外键foreign key（子表约束）
只有InnoDB类型的表才可以使用外键，mysql默认是MyISAM，这种类型不支持外键约束.
建立外键的表，被称为子表；被引用的表叫做主表或外表。一个表可以有多个外键，表的外键必须建立在另一个表的主键上。
外键：它是表中的一个已经存在的字段，可以不是本表的主键，但是必须对应主表中的主键。如果没有delete或者update联动约束，不能删除主表中与该表具有关联关系的行，但可以删除还没有映射关系的行。
a.外键的作用：（子集约束：对主表和子表都有一定的约束）
外键用来在2个表的数据之间建立参照完整性，即同时增删改。若B表字段有以A表字段作为参照的外键，则B表中的此字段的取值只能是A表中存在的值（该字段上，子表是主表的子集），从表B会实时受到主表A的约束，同时若关联on delete on update等操作则当A中的被参照的字段发生delete或update时，B中的对应的记录也会发生 delete 或 update操作，完整性；如果没有设置delete或者update联动，则当删除A表中的记录时，如果B表中存 在对应的记录是不能删除成功的，此时会报错（因为那样将不满足子集约束）。（总结为：以某一个表A的某一个或组合字段约束另一个表B的某一个或组合字段，要求B表该字段的记录是A表该字段记录的子集，只要满足子集约束的操作都可以，反正则不可以）
b.创建的步骤
指定主键关键字： foreign key(列名)
引用外键关键字： references <外键表名>(外键列名)
c.外键关联事件触发限制:on delete和on update , 可设参数cascade(跟随外键改动), restrict(限制外表中的外键改动),set Null(设空值）,set Default（设默认值）,[默认]no action
create table temp(
id int,
name char(20),
foreign key(id) references outTable(id) on delete cascade on update cascade);
把id列设为外键，参照外表outTable的id列，当外表中外键的值删除，本表中对应的id记录删除，当外键的值改变 本表中对应的列值改变。
d.组合外键
只能有一个外键，但可以使用多表的字段组合成一个复合外键。不推荐
e.完全允许多对对的关系
主表中的一记录可以对应子表中的多个记录；而主表中的多个记录也可以对应子表中的一个记录
mysql> show create table parent; //主表
CREATE TABLE `parent` (
`id` int(11) NOT NULL,
`name` char(4) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
mysql> show create table child; //子表
CREATE TABLE `child` (
`id` int(11) NOT NULL,
`parent_id` int(11) NOT NULL,
KEY `parent_id` (`parent_id`),
CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
mysql> select * from child;
+----+-----------+
| id | parent_id |
+----+-----------+
| 1 | 1 | //多个子表的id对应同一主表的parent_id，
| 3 | 1 |
| 1 | 2 | //同时同一子表的id对应多个主表的parent_id
| 1 | 3 |
+----+-----------+
4 rows in set (0.00 sec)
mysql> select * from parent;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
+----+------+
3 rows in set (0.00 sec)
本文转自 a_liujin 51CTO博客，原文链接：http://blog.51cto.com/a1liujin/1794691，如需转载请自行联系原作者
