在我们使用MySQL中可能压根不会注意这个问题,因为大多数情况下他们展示出来的效果都差不多,但是还是不能将他们划等号(至少理论上是这样)
索引(index)和约束(key)的区别主要在于二者的出发点不同,索引(index)负责维护表的查找和操作速度,约束(key)负责维护表的完整性。
而有这个困惑的话,很可能是由于MySQL中有一个奇怪现象:
MySQL中的索引是约束性索引(即创建索引自动也会创建约束)
并且MySQL中创建约束也会自动附带索引。
很奇怪吧,两者不同的东西但是创建的时候又会附带给对方,为什么这样做呢?因为原因是:
MySQL中的约束效果是通过索引来实现的,MySQL数据库判断是否当前列是否unique就是通过unique索引判断的。
最后总结下:
约束有主键约束,唯一约束,外键约束,非空约束,检查约束(在MySQL中压根无法使用检查约束,但可以正常建立),等
索引有普通索引,主键索引,唯一索引,联合索引,全文索引,等。
两者的实现都可以通过建表时实现,建表后的话通过alter语句来实现建立与删除,具体语句直接百度以上二点我经过测试在MySQL5.5 ,innoDB存储引擎下。
在理论上是不能将MySQL的key和index划等号的,他们不是一回事,但在实际使用中,他们基本没有区别。
Mysql数据库提供两种类型的索引,如果没正确设置,索引的利用效率会大打折扣却完全不知问题出在这。
CREATE TABLE test (
id INT NOT NULL,
last_name CHAR(30) NOT NULL,
first_name CHAR(30) NOT NULL,
PRIMARY KEY (id),
INDEX name (last_name,first_name)
);
以上创建的其实是一个多列索引,创建列索引的代码如下:
CREATE TABLE test (
id INT NOT NULL,
last_name CHAR(30) NOT NULL,
first_name CHAR(30) NOT NULL,
PRIMARY KEY (id),
INDEX name (last_name),
INDEX_2 name (first_name)
);
一个多列索引可以认为是包含通过合并(concatenate)索引列值创建的值的一个排序数组。 当查询语句的条件中包含last_name 和 first_name时,例如:
SELECT * FROM test WHERE last_name='Kun' AND first_name='Li';
sql会先过滤出last_name符合条件的记录,在其基础上在过滤first_name符合条件的记录。那如果我们分别在last_name和first_name上创建两个列索引,mysql的处理方式就不一样了,它会选择一个最严格的索引来进行检索,可以理解为检索能力最强的那个索引来检索,另外一个利用不上了,这样效果就不如多列索引了。
但是多列索引的利用也是需要条件的,以下形式的查询语句能够利用上多列索引:
SELECT * FROM test WHERE last_name='Widenius';
SELECT * FROM test WHERE last_name='Widenius' AND first_name='Michael';
SELECT * FROM test WHERE last_name='Widenius' AND (first_name='Michael' OR first_name='Monty');
SELECT * FROM test WHERE last_name='Widenius' AND first_name >='M' AND first_name < 'N';
以下形式的查询语句利用不上多列索引:
SELECT * FROM test WHERE first_name='Michael';
SELECT * FROM test WHERE last_name='Widenius' OR first_name='Michael';
多列建索引比对每个列分别建索引更有优势,因为索引建立得越多就越占磁盘空间,在更新数据的时候速度会更慢。
另外建立多列索引时,顺序也是需要注意的,应该将严格的索引放在前面,这样筛选的力度会更大,效率更高。
1、主键:
若某一个属性组(注意是组)能唯一标识一条记录,该属性组就是一个主键。主键不能重复,且只能有一个,也不允许为空。定义主键主要是为了维护关系数据库的完整性。
2、外键:
外键用于与另一张表的关联,是能确定另一张表记录的字段。外键是另一个表的主键,可以重复,可以有多个,也可以是空值。定义外键主要是为了保持数据的一致性。
3、索引:
索引是对表中一个或多个列的值进行排序的结构。
1) 应该创建索引的列的特点:
① 在经常需要搜索的列上创建索引,可以加快搜索的速度;
② 在作为主键的列上创建索引,强制该列的唯一性;
③ 在经常用在连接的列上创建索引,主要是一些外键,可以加快连接的速度;
④ 在经常需要根据范围进行搜索的列上创建索引,因为索引已经排序,其指定的范围是连续的;在经常需要排序的列上创建索引,因为索引已经排序,可以利用索引的排序加快查询;
⑤ 在经常使用在WHERE子句中的列上创建索引,加快条件的判断速度。
2) 不应该创建索引的列的特点:
① 在查询中很少使用的列上不应该创建索引,因为这些列很少使用到,因此有索引或无索引,并不能提高查询速度,相反由于增加了索引,反而降低了系统维护速度,增大了空间需求;
② 在只有很少数据值的列上不应该创建索引,很少数据值的列如性别等,在查询的结果中,结果集的数据行占了表中数据行的很大比例,即需要在表中搜索的数据行的比例很大,增加索引,并不能明显加快检索速度;
③ 当修改性能远远大于检索性能时,不应该创建索引,因为改性能和检索性能是互相矛盾的,当增加索引时,会提高检索性能,但会降低修改性能,当减少索引时,会提高修改性能,但会降低检索性能。因此,当修改性能远大于检索性能时,不应该创建索引。
3) 可以在数据库设计器中创建三种索引:
① 唯一索引:
不允许其中任何两行具有相同索引值的索引。
② 主键索引:
表的某一列或列组合,其值唯一标识表中的每一行,该列或列组合称为表的主键。为表定义主键将自动创建主键索引,主键索引是唯一索引的特定类型。该索引要求主键中的每个值都唯一。
③ 聚集索引:
聚集索引:聚集索引表示表中存储的数据按照索引的顺序存储。由于聚集索引规定数据在表中的物理存储顺序,因此一个表只能包含一个聚集索引。
聚集索引实例:字典默认按字母顺序排序,如知道某个字的读音可根据字母顺序快速定位。
非聚集索引:非聚集索引表示数据存储在一个地方,索引存储在另一个地方,索引带有指针指向数据的存储位置,需要查询两个地方才能查找到数据。一个表可以包含多个非聚集索引,可以为查找数据时常用的每个列创建一个非聚集索引。
非聚集索引实例:如需查询某个生僻字,则需按字典前面的索引,如按偏旁进行定位,找到该字对应的页数,再打开对应页数找到该字。
与非聚集索引相比,聚集索引通常提供更快的数据访问速度,但对数据更新影响较大。
4)索引的优点:
加快对数据的检索。
5)索引的缺点:
① 减慢数据录入的速度;
② 增加了数据库的尺寸大小。
4、主键和外键的关系:
外键是另一个表的主键,主键是可以被外键有效引用的对象。若A表中的一个字段,是B表的主键,则它可以是A表的外键。
5、主键和外键以及索引的区别:
| 定义 | 作用 | 个数 |
主键 | 唯一标识一条记录,不能有重复,不允许为空 | 保证数据完整性 | 只能有一个主键 |
外键 | 另一表的主键,可以重复,允许为空 | 和其他表建立联系 | 可以有多个外键 |
索引 | 没有重复值,但可以有一个空值 | 提高查询排序的速度 | 可以有多个唯一索引 |