MySQL普通索引与唯一索引__mysql中唯一索引和普通索引的用途及区别

MySQL普通索引与唯一索引

索引作用：

提高查询效率，一般加在经常查询或者排序的字段上。

普通索引：

允许字段值重复

唯一索引：

保证数据记录唯一性

如何选择：

查询过程：

• 对普通索引来说，找到满足条件的第一个记录之后，需要继续查找下一条记录，直到下一条记录不满足条件为止。
• 对唯一索引来说，查找到第一条满足条件的数据之后，就会停止检索（唯一性）

查询效率：

相差不大

• • • InnoDB的数据是以页（16KB大小）为单位进行读写
    • 一个数据页可以存放数千个Key
    • 普通索引通常情况下多了一次指针寻找和判断，较低概率跨数据页查找

更新过程：

change buffer：

• 更新一组数据时，若数据页在内存中就直接更新；

• 若不在内存中，更新操作缓存进change buffer中，待下一次查询访问此数据页时，读入内存，执行操作；

• 可持久化，在内存中有拷贝，会被写入磁盘；

• merge（将change buffer中的数据写入原数据页）的时机：

  1. 访问这个数据页
  2. 系统后台线程定期merge
  3. 数据库正常关闭（shut down）

• 可以减少读磁盘，提高执行效率。

若更新的目标页在内存中：

唯一索引：

找到插入的位置，判断有无冲突，执行更新操作

普通索引：

直接进行更新操作

若数据页不在内存中：

• 唯一索引：将数据页读入内存，判断有无冲突，插入这个值，
• 普通索引：将更新记录在change buffer中，适合写多读少的情景，如果写完立刻又进行读操作，触发merge，增加了changebuffer的维护成本。

结论：

• 二者在查询效率上差别不大
• 更新效率上有差别，普通索引可以和change buffer两两配合
• 业务正确性优先，如果业务无法保证不会插入重复数据，并且要求数据库做约束，必须创建唯一索引

mysql中唯一索引和普通索引的用途及区别

唯一索引和普通索引使用的结构都是B-tree,执行时间复杂度都是O(log n)。

1、普通索引

普通索引(由关键字KEY或INDEX定义的索引)的唯一任务是加快对数据的访问速度。因此，应该只为那些最经常出现在查询条件(WHEREcolumn=)或排序条件(ORDERBYcolumn)中的数据列创建索引。只要有可能，就应该选择一个数据最整齐、最紧凑的数据列(如一个整数类型的数据列)来创建索引。索引名称习惯用idx_…命名。

2、唯一索引

普通索引允许被索引的数据列包含重复的值。比如说，因为人有可能同名，所以同一个姓名在同一个“员工个人资料”数据表里可能出现两次或更多次。 如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该用关键字UNIQUE把它定义为一个唯一索引。这么做的好处：一是简化了MySQL对这个索引的管理工作，这个索引也因此而变得更有效率；二是MySQL会在有新记录插入数据表时，自动检查新记录的这个字段的值是否已经在某个记录的这个字段里出现过了；如果是，MySQL将拒绝插入那条新记录。也就是说，唯一索引可以保证数据记录的唯一性。事实上，在许多场合，人们创建唯一索引的目的往往不是为了提高访问速度，而只是为了避免数据出现重复。索引名称习惯用uk_…命名。