数据库索引和主键

1、什么是索引？

面试时候，常常被问到自己熟知的Mysql优化技巧，索引一定会被提及。那么，什么是索引呢？索引是一种加快海量数据查询的技术。索引就像是小时候的新华字典，有了索引，你可以快速地找到自己想找的东西。MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。

创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。过多的使用索引将会造成滥用。

因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

建立索引会占用磁盘空间的索引文件。

2、索引的优缺点

1）优点
A.加快数据检索速度和表与表之间的连接；
B.可以显著减少查询中分组和排序的时间（使用分组和排序子句进行数据检索时）。

2）缺点
A.占物理空间。
B.需要动态维护，降低数据维护速度（update、delete、insert）。

3、索引的分类

一般索引：这是最基本的索引，它没有任何限制。值可以重复，可以为空。

唯一索引：它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

聚集索引类似于电话簿，后者按姓氏排列数据。由于聚集索引规定数据在表中的物理存储顺序，因此一个表只能包含一个聚集索引。

但该索引可以包含多个列（组合索引），就像电话簿按姓氏和名字进行组织一样。

如果在表中未创建聚集索引，则无法保证这些行具有任何特定的顺序。聚集索引..就像我们新华字典中的按拼音排序..即你查.."爱"字..可以在前面看到"
癌"字...但不会看到"受"字..而非聚集索引..就是新华字典中的按部首..笔划排序...

聚集索引相当于我们书本上前面的目录的一样，它可以方便快速的找到你想找的内容，而非聚集索引就相当于书最后几页的解释，它是对书中某个语句或者是生词的解释
，就像我们上学时候的地理说一样，书后面都有各种地理名称的英文解释；

《数据库原理》里面的解释：聚集索引的顺序就是数据的物理存储顺序，而非聚集索
引的顺序和数据物理排列无关。因为数据在物理存放时只能有一种排列方式，所以一
个表只能有一个聚集索引。

主要分为聚集索引和非聚集索引两类。

什么是聚集索引？
正文内容本身是一种按照一定规则排列的目录称为“聚集索引”，就如新华字典的正文本身。因为数据只能按照一种规则排序，所以一张表至多有一个聚集索引，但可以有多个非聚集索引。

非聚集索引最常见的就是联合索引，那什么是联合索引呢？
比如 INDEX idx_test(col_a,col_b)，这种包含多个字段的索引就被称为“联合索引”。

何时使用聚集索引或非聚集索引，参照：

 

 

聚集索引或非聚集索引.png

在MySQL数据库的 InnoDB存储引擎中，主键索引就是聚集索引，所有数据都会按照主键索引进行组织；而在 MyISAM存储引擎中，就没有聚集索引了，因为MyISAM存储引擎中的数据不是按索引顺序进行存储的。

4、什么是主键？

表中经常有一个列或列的组合，其值能唯一地标识表中的每一行。这样的一列或多列称为表的主键.(默认为聚集索引)聚集索引确定表中数据的物理顺序。

主键：唯一标识记录（常见的什么什么ID），不允许重复，不允许为空。
主键默认建立唯一索引，所以创建表时，不能在同一个字段上建立两个索引，如：deal_id已经是主键，不能再次执行：
create index table_fei on tmp_table(deal_id) 会报错！

 

 

比对.png

1. 主键一定是唯一性索引，唯一性索引并不一定就是主键。

2. 一个表中可以有多个唯一性索引，但只能有一个主键（图中多个主键指复合主键）

3. 主键列不允许空值，而唯一性索引列允许空值。

4. 索引可以提高查询的速度。

主键和索引都是键，不过主键是逻辑键，索引是物理键（也是表，存在物理磁盘），意思就是主键不实际存在，而索引实际存在在数据库中。

DROP INDEX [indexName] ON mytable; //删除索引的语法

SHOW INDEX FROM table_name; //显示索引信息

 索引说明：https://www.runoob.com/mysql/mysql-index.html