摘要：MySQL InnoDB数据存储是以B+树索引方式存储
存储单元、mysql InnoDB-B+树组织数据、查询数据、mysql InnoDB-B+树存储数据量、实际操作查看

mysql知识储备

参看：http://blog.bjlhx.top/categories/db-mysql-core/

InnoDB为聚集主键类型的引擎，数据会按照主键进行排序
mysql 的索引是B+tree，聚集索引使用主键，没有主键默认生成一个隐式主键rowId，辅助索引保存的是主键
innodb 存储单元是页，每页 16k，数据类型过大，会跨页，溢出等
每张表都会有主键，如没有显式设置主键，则
（1）、表中的非空唯一索引作为主键
（2）、自动创建一个6字节大小的指针

主键定义

表中经常有一个列或多列的组合，其值能唯一地标识表中的每一行。这样的一列或多列称为表的主键，通过它可强制表的实体完整性。

主键设计原则

总原则：根据数据库表的具体使用范围来决定采用不同的表主键定义。

数据库设计的三大范式

第一范式（1NF）：指在关系模型中，对于添加的一个规范要求，所有的域都应该是原子性的，即数据库表的每一列都是不可分割的原子数据项，而不能是集合，数组，记录等非原子数据项。
即实体中的某个属性有多个值时，必须拆分为不同的属性。在符合第一范式（1NF）表中的每个域值只能是实体的一个属性或一个属性的一部分。简而言之，第一范式就是无重复的域。
第二范式（2NF）：首先要满足它是1NF，另外还需要包含两部分内容：一是表必须有一个主键；二是没有包含在主键中的列必须完全依赖于主键，而不能只依赖于主键的一部分。简而言之，第二范式就是非主属性非部分依赖于主关键字
第三范式（3NF）：首先是 2NF，另外非主键列必须直接依赖于主键，不能存在传递依赖。即不能存在：非主键列 A 依赖于非主键列 B，非主键列 B 依赖于主键的情况。简而言之，第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。

反范式主键的设计原则

主键应当是对用户没有意义的。业务上的‘主键’可以通过唯一键（Unique Key）或唯一索引（Unique Index）和其它约束条件实现
主键应该是单列的，以便提高连接和筛选操作的效率
不要更新主键。实际上，因为主键除了惟一地标识一行之外再没有其他的用途了，所以也就没有理由去对它更新。另外，主键的值通常不重用，意味着记录被删除后，该主键值不再使用
主键不应包含动态变化的数据，如时间戳、创建时间列、修改时间列等
主键应当由计算机自动生成。

反范式跟范式所要求的正好相反，在反范式的设计模式，可以允许适当的数据的冗余，用这个冗余去取操作数据时间的缩短。
本质上就是用空间来换取时间，把数据冗余在多个表中，当查询时可以减少或者是避免表之间的关联（手册中也有禁止三表以上 JOIN 的条款）。

但需要注意的是，采用代理主键是为了避免业务逻辑变化导致主键变更，以及提高 JOIN 效率等。但在实际查询中，数据查询还是需要通过业务上的唯一键进行匹配的，
而不应该将代理主键作为查询条件，尤其不能将代理主键作为查询条件输入项提供给用户。

总体来说，实际应用中应当具体问题具体分析，结合范式和反范式两种设计思想。
对数据一致性和完整性较高、而对查询效率要求并不严格的地方，应当更倾向于遵从范式；而类似分布式、高并发集群的场景，则应当更考虑反范式的设计方案。

确保主键的无意义性

在开发过程中，有意义的字段例如“用户登录信息表”将“登录名”（英文名）作为主键，“订单表”中将“订单编号”作为主键，如此设计主键一般都是没什么问题，因为将这些主键基本不具有“意义更改”的可能性。

但是，也有一些例外的情况，例如“订单表”需要支持需求“订单可以作废，并重新生成订单，而且订单号要保持原订单号一致”，那将“订单编号”作为主键就满足不了要求了。

因此在使用具有实际意义的字段作为主键时，需要考虑是否存在这种可能性。

要用代理主键，不要使用业务主键。任何一张表，强烈建议不要使用有业务含义的字段充当主键。我们通常都是在表中单独添加一个整型的编号充当主键字段。

采用整型主键

主键通常都是整数，不建议使用字符串当主键。（如果主键是用于集群式服务，可以采用字符串类型）

减少主键的变动

主键的值通常都不允许修改，除非本记录被删除。

避免重复使用主键

主键的值通常不重用，意味着记录被删除后，该主键值不再使用。

主键字段定义区分

主键不要直接定义成【id】，而要加上前缀，定义成【表名id】或者【表名_id】

主键设计的常用方案

自增ID

方案：数据库设置：AUTO_INCREMENT
使用场景：不拆分库的情况下，推荐使用
优点：

数据库自动编号，速度快，而且是增量增长，聚集型主键按顺序存放，对于检索非常有利。
数字型，占用空间小，易排序，在程序中传递方便。
同样数据量的情况下，自增id主键的数据量是字符串主键的1/2，对于考虑成本的公司来说无疑是一件好事，并且数据量小对备份还原数据都有大大的好处。
Mysql会按照键值的大小进行顺序存放，如果设置自增id为主键，这个时候主键是按照一种紧凑的接近顺序写入的方式进行存储数据。
如果用其他字段作为主键的话，此时Mysql不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，
此时又要从磁盘上读回来，这增加了很多额外的开销，同时频繁的移动、分页操作造成了大量的碎片。

缺点：

不支持水平分片架构，水平分片的设计当中，这种方法显然不能保证全局唯一。
表锁
在MySQL5.1.22之前，InnoDB自增值是通过其本身的自增长计数器来获取值，该实现方式是通过表锁机制来完成的（AUTO-INC LOCKING）。
锁不是在每次事务完成后释放，而是在完成对自增长值插入的SQL语句后释放，要等待其释放才能进行后续操作。
比如说当表里有一个auto_increment字段的时候，innoDB会在内存里保存一个计数器用来记录auto_increment的值，当插入一个新行数据时，
就会用一个表锁来锁住这个计数器，直到插入结束。如果大量的并发插入，表锁会引起SQL堵塞。
在5.1.22之后，InnoDB为了解决自增主键锁表的问题，引入了参数innodb_autoinc_lock_mode：
0：通过表锁的方式进行，也就是所有类型的insert都用AUTO-inc locking（表锁机制）。
1：默认值，对于simple insert 自增长值的产生使用互斥量对内存中的计数器进行累加操作，对于bulk insert 则还是使用表锁的方式进行。
2：对所有的insert-like 自增长值的产生使用互斥量机制完成，性能最高，并发插入可能导致自增值不连续，可能会导致Statement 的 Replication 出现不一致，使用该模式，需要用 Row Replication的模式。
可能不连续
当系统与其他系统集成时，需要数据导入时，很难保证原系统的ID不发生主键冲突。
如果其他系统主键不是数字型，会导致修改主键数据类型，导致其他相关表的修改。

UUID

UUID是指在一台机器上生成的数字，它保证对在同一时空中的所有机器都是唯一的。在UUID的算法中，可能会用到诸如网卡MAC地址，IP，主机名，进程ID等信息以保证其独立性。

方案：mysql uuid()函数，应用程序UUID函数
使用场景：不推荐使用，针对mysql缺点过于严重
优点：

全局唯一性、安全性、可移植性。
能够保证独立性，程序可以在不同的数据库间迁移，效果不受影响。
保证生成的ID不仅是表独立的，而且是库独立的，在你切分数据库的时候尤为重要

缺点：

针对InnoDB引擎会徒增IO压力，InnoDB为聚集主键类型的引擎，数据会按照主键进行排序，由于UUID的无序性，InnoDB会产生巨大的IO压力。InnoDB主键索引和数据存储位置相关（簇类索引），uuid 主键可能会引起数据位置频繁变动，严重影响性能。
UUID长度过长，一个UUID占用128个比特（16个字节）。主键索引KeyLength长度过大，而影响能够基于内存的索引记录数量，进而影响基于内存的索引命中率，而基于硬盘进行索引查询性能很差。严重影响数据库服务器整体的性能表现。

自建的id生成器

当数据量比较大，又是分布式架构的时候，可能需要考虑各种分库分表方案了。
自建id生成器，可以保证全局唯一，可以参考snowflake的算法（18位）方案，具体实施也可以根据自身业务进行调整算法。其次需要考虑的就是服务的高可用。

业务编号做主键

优点
此方法就是采用实际业务中的唯一字段的“编号”作为主键设计，这在小型的项目中是推荐这样做的，因为这可以使项目比较简单化，
缺点：
但在使用中却可能带来一些麻烦，比如要进行编号修改”时，可能要涉及到很多相关联的其他表，后果很严重”;或“业务要求允许编号重复时”

max加1

优点：
由于自动编号存在那些问题，采用自己生成，同样是数字型的，只是把自动增长去掉了，采用在Insert时，读取Max值后加一，这种方法可以避免自动编号的问题，
缺点：
但也存在一个效率问题，如果记录非常大的话，那么Max()也会影响效率的;更严重的是并发性问题，如果同时有两人读到相同的Max后，加一后插入的ID值会重复。

自制加一

优点：
考虑Max加一的效率后，采用自制加一，也就是建一个特别的表，字段为：表名，当前序列值。这样在往表中插入值时，先从此表中找到相应表的最大值后加一，进行插入，
也可能会存在并发处理，这个并发处理，我们可以采用lock线程的方式来避免，在生成此值的时，先Lock，取到值以后，再unLock出来，这样不会有两人同时生成了。
这比Max加一的速度要快多了。
缺点：
但同样存在一个问题：在与其他系统集成时，脱离了系统中的生成方法后，很麻烦保证自制表中的最大值与导入后的保持一致，而且数字型都存在老数据的导入问题。
因此在“自制加一”中可以把主键设为字符型的。字符型的自制加一我倒是蛮推荐的，应该字符型主键可以应付很多我们意想不到的情况。

主键的选取建议

此处的分布式主要指分库分表。非应用分布式

非分布式架构直接套用自增id做主键
小规模分布式架构用uuid或者自增id+步长做主键
大规模分布式架构用自建的id生成器做主键，参考twitter的[snowflake算法][2]

（1）单实例或者单节点组：
经过500W、1000W的单机表测试，自增ID相对UUID来说，自增ID主键性能高于UUID，磁盘存储费用比UUID节省一半的钱。所以在单实例上或者单节点组上，使用自增ID作为首选主键。

（2）分布式架构场景：
20个节点组下的小型规模的分布式场景，为了快速实现部署，可以采用多花存储费用、牺牲部分性能而使用UUID主键快速部署；

20到200个节点组的中等规模的分布式场景，可以采用自增ID+步长的较快速方案。

200以上节点组的大数据下的分布式场景，可以借鉴类似twitter雪花算法构造的全局自增ID作为主键。