Post Jobs

澳门新葡亰平台游戏MySQL索引之主键索引,对于自增列做物理主键使用的一点构思

澳门新葡亰平台游戏 2

 

在MySQL里,主键索引和辅助索引分别是什么意思,有什么区别?
上次的分享我们介绍了聚集索引和非聚集索引的区别,本次我们继续介绍主键索引和辅助索引的区别。

本文出处: 
(保留出处并非什么原创作品权利,本人拙作还远远达不到,仅仅是为了链接到原文,因为后续对可能存在的一些错误进行修正或补充,无他)

1、主键索引

 

主键索引,简称主键,原文是PRIMARY
KEY,由一个或多个列组成,用于唯一性标识数据表中的某一条记录。一个表可以没有主键,但最多只能有一个主键,并且主键值不能包含NULL。

 

在MySQL中,InnoDB数据表的主键设计我们通常遵循几个原则:

关于自增列

1、采用一个没有业务用途的自增属性列作为主键;
2、主键字段值总是不更新,只有新增或者删除两种操作;
3、不选择会动态更新的类型,比如当前时间戳等。

自增列作为数据库的一个特性之一,在MSSQL和MySQL以及Oracle中都被支持。
之前在网上发现一个类似的问题,是关于MySQL的:“为什么InnoDB表最好要有自增列做主键?”
自增列作为一项特性,(可能)会应用到表的设计方面,不管是在那种数据库平台下。
抛开具体的数据库平台,这个问题可以从更泛的层次去思考,为什么在设计表的时候,需要增加一个自增列做主键?
或者反过来问,设计表的时候,增加一个自增列做主键有什么好处?
多数时候,自增列作为一个冗余字段,在设计表的时候是非必须的,也就是说,一个表中,要不要自增列(甚至要不要自增列作为主键)都是可选的,不是说没有自增列某些表就无法满足业务需求的。
 
当设计表的时候增加一个自增列已经成为一个非强制的要求的时候,做过数据库设计的人对此问题会习以为常。
但是突然有人问上一句“为什么”,还是很难一句话解释清楚的,
比如刚工作不久的人问的问题就很尖锐:“为什么表中要加一个自增列?有什么好处?看起来这个自增列是可有可无的?”
这是一个DB Designer必须要考虑清楚且要随时去回答一些人的质疑的问题。 

这么做的好处有几点:

 

1、新增数据时,由于主键值是顺序增长的,innodb
page发生分裂的概率降低了;可以参考以往的分享“[MySQL FAQ]系列 —
为什么InnoDB表要建议用自增列做主键”;
2、业务数据有变更时,不修改主键值,物理存储位置发生变化的概率降低了,innodb
page中产生碎片的概率也降低了。
MyISAM表因为是堆组织表,主键类型设计方面就可以不用这么讲究了。

业务主键与逻辑主键的选择

2、辅助索引

这之前首先解释两个概念:业务主键与逻辑主键,以设计一个User表为例,有UserId,UserName,CreateDate等等一些属性

辅助索引,就是我们常规所指的索引,原文是SECONDARY
KEY。辅助索引里还可以再分为唯一索引,非唯一索引。

业务主键:把具有实际含义的字段作为主键
     通常情况下可以把UserId作为业务主键,主键就暗含了非空+唯一性,一个表中每个UserId是不能为空且唯一的,UserId作为主键并没有什么不妥当的。
     通常来说,业务主键在一个表中的地位很明显,也很直观,比如UserId,订单ID,CustomerId,登录名等等。
逻辑主键:采用与业务无关的唯一性的字段做主键,或称之为“伪主键”
     自增列或者GUID都可以实现所谓的逻辑主键,这里暂且不不讨论GUID(自增与GUID也是一个很大的话题),在User表中增加一个自增列作为主键,因为自增列是也符合主键的特性,也即非空且唯一。
       
这个字段的第一个特点是与任何业务逻辑无关,第二个特点是对业务来说是不可见的,
     比如设计一张用户表,在满足存储所有用户信息的条件下,该字段作为一个独立的列存在,而不描述任何业务含义。

唯一索引其实应该叫做唯一性约束,它的作用是避免一列或多列值存在重复,是一种约束性索引。

以上就是所谓的业务主键和逻辑主键,实话说,仅仅从表面上看,后者并没有什么明显的优势,甚至可以说,如果仅仅就这一张表来说的话,有点冗余的感觉。
但是实际应用中,一个应用是具体一系列复杂的表来支撑的,表与表之间存在着一系列的关联关系,比如1对1或者1对多的关系,此时,自增列的作用就可以提现出来了。

3、主键索引和辅助索引的区别

 

在MyISAM引擎中,唯一索引除了key值允许存在NULL外,其余的和主键索引没有本质性区别。也就是说,在MyISAM引擎中,不允许存在NULL值的唯一索引,本质上和主键索引是一回事。

业务主键存在的问题

而在InnoDB引擎中,主键索引和辅助索引的区别就很大了。主键索引会被选中作为聚集索引,而唯一索引和普通辅助索引间除了唯一性约束外,在存储上没本质区别。

首先说业务主键存在的弊端,业务主键必然要与业务逻辑挂钩,这就意味着业务主键可能是一个或者多个字段的结合,甚至对于每一个业务主键字段都有要求,
比如订单号要求以DH0000000000X开头,或者通常情况下的UserId,比如博客园的登录Id,是介于一定长度之间的字符与数字的组合
这样存在一下几个弊端:
1,当前表的主键与其他表的存在关联关系的时候,必然要存储当前这个表的业务主键信息,甚至不止一个字段,这样无疑增加了表与表之间关联的复杂性与存储空间。
 
 同理,在表之间join的时候,必然要用基于业务主键的各个条件连接,业务增加SQL的复杂性和降低SQL的执行效率。
2,既然是业务主键,也存在潜在的修改的可能性,有人说主键还会修改,不可能吧,其实这种情况还真不少,比如呢?
 
 博客园的登录名,作为一个类似于UserId的东西,可以认为是一个业务主键,其特点就是非空+唯一的
 
 但是这个用户名就是可以修改的,如果其他跟用户信息相关的表存储了用户名,用户名有修改之后,要达到数据的一致性,要修改一系列存储了用户名相关的表。

从查询性能上来说,在MyISAM表中主键索引和不允许有NULL的唯一索引的查询性能是相当的,在InnoDB表通过唯一索引查询则需要多一次从辅助索引到主键索引的转换过程。InnoDB表基于普通索引的查找代价更高,因为每次检索到结果后,还需要至少再多检索一次才能确认是否还有更多符合条件的结果,主键索引和唯一索引就不需要这么做了。

澳门新葡亰平台游戏 1

经过测试,对100万行数据的MyISAM做随机检索(整数类型),主键和唯一索引的效率基本一样,普通索引的检索效率则慢了30%以上。换成InnoDB表的话,唯一索引比主键索引效率约慢9%,普通索引比主键索引约慢了50%以上。

    再比如,如果用邮箱作为用户名进行注册,比如雅虎邮箱关闭之后,
    注册的各种宝宝账号,如果相关表中直接存储的这个业务主键,也即直接存储的这个邮箱,在需要给用户发送邮件的地方存一个邮箱信息字段,
    那么修改个人邮箱的时候,要涉及多少张表的修改?

关于MySQL的方方面面大家想了解什么,可以直接留言回复,我会从中选择一些热门话题进行分享。
同时希望大家多多转发,多一些阅读量是老叶继续努力分享的绝佳助力,谢谢大家
🙂

  澳门新葡亰平台游戏 2

您可能感兴趣的文章:

  • MySQL
    创建主键,外键和复合主键的语句
  • MySQL
    主键与索引的联系与区别分析
  • MySQL中的主键以及设置其自增的用法教程
  • sqlserver数据库主键的生成方式小结(sqlserver,mysql)
  • mysql把主键定义为自动增长标识符类型
  • 快速理解MySQL中主键与外键的实例教程
  • Oracle与Mysql主键、索引及分页的区别小结
  • Mysql主键相关的sql语句集锦
  • MySQL中主键为0与主键自排约束的关系详解(细节)

发表评论

电子邮件地址不会被公开。 必填项已用*标注

相关文章

网站地图xml地图