PRIMARY KEY & UNIQUE（主键、唯一索引）

这里是修真院后端小课堂，每篇分享文从

【背景介绍】【知识剖析】【常见问题】【解决方案】【编码实战】【扩展思考】【更多讨论】【参考文献】

八个方面深度解析后端知识/技能，本篇分享的是：

1.背景介绍

概念

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

不建立索引的话，MySQL执行查询会从数据表的第一行开始查直到查询想要的数据结果。
2.知识剖析

索引的分类–单列来区分

1).普通索引–这是最基本的索引，它没有任何限制

2).唯一索引–索引列的值必须唯一，但允许有空值;如果是组合索引，则列值的组合必须唯一

3).主键索引–列值唯一，非空，一个数据表只能有一个主键

4).组合索引：将数据表中多个列建到一个索引中，MySQL组合索引遵循“最左前缀”原则，

就是说你的查询条件语句里必选包含组合索引第列的值才能使用

改组合索引，例如在某个数据表中为user,name,age三个列建立组合索引，

查询语句里必须有where user*才会调用这个索引。

索引的分类（存储结构）（InnoDB）

B-tree

mysql数据库中主键、唯一、普通、全文都是采用b-tree数据结构

在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，

这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，

因此InnoDB表数据文件本身就是主索引，InooDB必须要求主键，没有显示声明，系统会默认添加

除了主键索，其它都是辅助索引，辅助索引也会包含主键列，因此主键列最好不要设置太大的列值

因为是聚簇索引方式，聚簇索引的顺序就是数据的物理存储顺序，叶节点就是数据节点。主键最好是单调的。

hash索引

简单地说，哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快

1.可以通过建立唯一索引或者主键索引,保证数据库表中每一行数据的唯一性.

2.建立索引可以大大提高检索的数据,以及减少表的检索行数

3.在表连接的连接条件 可以加速表与表直接的相连

4.在分组和排序字句进行数据检索,可以减少查询时间中 分组 和 排序时所消耗的时间(数据库的记录会重新排序)

5.建立索引,在查询中使用索引 可以提高性能
3.常见问题

1.索引可以提高查询效率，数据表每个列都建索引,这样可行吗？

1).在创建索引和维护索引 会耗费时间,随着数据量的增加而增加

2).索引文件会占用物理空间,除了数据表需要占用物理空间之外,每一个索引还会占用一定的物理空间

3).当对表的数据进行INSERT,UPDATE,DELETE的时候,索引也要动态的维护,这样就会降低数据的维护速度,(建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快)。
4.解决方案

1.在经常需要搜索的列上,可以加快索引的速度

2.主键列上可以确保列的唯一

3.在经常需要排序(order by),分组(group by)和的distinct列上加索引 可以加快排序查询的时间

4.在一些where之后的< <= > >= BETWEEN IN以及某个情况下的like建立字段的索引(B-TREE)

5.选择越小的数据类型越好,因为通常越小的数据类型通常在磁盘,内存,cpu,缓存中 占用的空间很少,处理起来更快
5.什么情况不要建索引？

1.查询中很少使用到的列 不应该创建索引,如果建立了索引然而还会降低mysql的性能和增大了空间需求.

2.很少数据的列也不应该建立索引,比如 一个性别字段0或者1,在查询中,结果集的数据占了表中数据行的比例比较大,mysql需要扫描的行数很多,增加索引,并不能提高效率

3.当表的修改(UPDATE,INSERT,DELETE)操作远远大于检索(SELECT)操作时不应该创建索引,这两个操作是互斥的关系