网站改版建设征求意见书,网站开发专员,wordpress ping通告,杂志社网站建设方案MySQL主键一. MySQL主键设计原则MySQL主键应当是对用户没有意义的。MySQL主键应该是单列的#xff0c;以便提高连接和筛选操作的效率(当然复合主键是可以的#xff0c;只是不建议)永远也不要更新MySQL主键MySQL主键不应包含动态变化的数据#xff0c;如时间戳、创建时间列、…MySQL主键一. MySQL主键设计原则MySQL主键应当是对用户没有意义的。MySQL主键应该是单列的以便提高连接和筛选操作的效率(当然复合主键是可以的只是不建议)永远也不要更新MySQL主键MySQL主键不应包含动态变化的数据如时间戳、创建时间列、修改时间列等MySQL主键应当有计算机自动生成。二. 主键添加方法在创建表的时候直接在字段之后添加primary key关键字create table my_pri1(name varchar(20) not null comment ‘姓名‘,number char(10) primary key comment ‘学号‘)charset utf8;在创建表的时候在所有的字段之后使用primary key(主键字段列表)来创建主键此处可用来添加复合主键(不建议)。create table my_pri2(number char(10) not null comment ‘学号‘,course char(10) not null comment ‘课程编号‘,score tinyint unsigned default 60,-- 增加主键限制学号和课程编号应该是对应的具有唯一性primary key(number) //primary key(number,course))charset utf8;当表创建完之后额外追加主键可以直接追加主键也可以通过修改表字段的属性追加主键alter table my_pri3 modify course char(10) primary key comment ‘课程编号‘; -- 不建议使用alter table my_pri3 add primary key(course); -- 推荐使用三. 主键设计的常用方案1. 自增ID优点1) 数据库自动编号速度快而且是增量增长聚集型主键按顺序存放对于检索非常有利。2) 数字型占用空间小易排序在程序中传递方便。缺点1) 不支持水平分片架构水平分片的设计当中这种方法显然不能保证全局唯一。2) 表锁在MySQL5.1.22之前InnoDB自增值是通过其本身的自增长计数器来获取值该实现方式是通过表锁机制来完成的(AUTO-INC LOCKING)。锁不是在每次事务完成后释放而是在完成对自增长值插入的SQL语句后释放要等待其释放才能进行后续操作。比如说当表里有一个auto_increment字段的时候innoDB会在内存里保存一个计数器用来记录auto_increment的值当插入一个新行数据时就会用一个表锁来锁住这个计数器直到插入结束。如果大量的并发插入表锁会引起SQL堵塞。在5.1.22之后InnoDB为了解决自增主键锁表的问题引入了参数innodb_autoinc_lock_mode0通过表锁的方式进行也就是所有类型的insert都用AUTO-inc locking(表锁机制)。1默认值对于simple insert 自增长值的产生使用互斥量对内存中的计数器进行累加操作对于bulk insert 则还是使用表锁的方式进行。2对所有的insert-like 自增长值的产生使用互斥量机制完成性能最高并发插入可能导致自增值不连续可能会导致Statement 的 Replication 出现不一致使用该模式需要用 Row Replication的模式。3) 自增主键不连续对表增加属性AUTO_INCREMENT16时主键就会从16开始自增起实际的插入数据操作中可能存在不连续的主键值。2. UUID优点1) 全局唯一性、安全性、可移植性。2) 能够保证独立性程序可以在不同的数据库间迁移效果不受影响。3) 保证生成的ID不仅是表独立的而且是库独立的在你切分数据库的时候尤为重要缺点1) 针对InnoDB引擎会徒增IO压力InnoDB为聚集主键类型的引擎数据会按照主键进行排序由于UUID的无序性InnoDB会产生巨大的IO压力。InnoDB主键索引和数据存储位置相关(簇类索引)uuid 主键可能会引起数据位置频繁变动严重影响性能。2) UUID长度过长一个UUID占用128个比特(16个字节)。主键索引KeyLength长度过大而影响能够基于内存的索引记录数量进而影响基于内存的索引命中率而基于硬盘进行索引查询性能很差。严重影响数据库服务器整体的性能表现。3.自定义序列自定义序列列表就是在库中建一张用于生成序列的表来存储序列信息序列生成的策略通过程序层面来实现。如下所示构建一张序列表CREATE TABLE sequence (name varchar(50) NOT NULL,id bigint(20) unsigned NOT NULL DEFAULT ‘0‘,PRIMARY KEY (name)) ENGINEInnoDB;注意区别id字段不是自增的也不是主键。在使用前我们需要先插入一些初始化数据INSERT INTO sequence (name) VALUES(‘users‘), (‘photos‘), (‘albums‘), (‘comments‘);接下来我们可以通过执行下面的SQL语句来获得新的照片IDUPDATE sequence SET id LAST_INSERT_ID(id 1) WHERE name ‘photos‘;SELECT LAST_INSERT_ID();我们执行了一个更新操作将id字段增加1并将增加后的值传递到LAST_INSERT_ID函数 从而指定了LAST_INSERT_ID的返回值。实际上我们不一定需要预先指定序列的名字。如果我们现在需要一种新的序列我们可以直接执行下面的SQL语句INSERT INTO sequence (name) VALUES(‘new_business‘) ON DUPLICATE KEY UPDATE id LAST_INSERT_ID(id 1);SELECT LAST_INSERT_ID();这种方案的问题在于序列生成的逻辑脱离了数据库层由应用层负责增加了开发复杂度。不过一般序列值都是通过Java的框架去自动生成的具体就不在MySQL系列文章阐述。四. 如何解决水平分片的需求UUID由于UUID出现重复的概率基本可以忽略所以对分片是天生支持的。独立的序列库单独建立一个库用来生成ID在Shard中的每张表在这个ID库中都有一个对应的表而这个对应的表只有一个字段 这个字段是自增的。当我们需要插入新的数据我们首先在ID库中的相应表中插入一条记录以此得到一个新的ID 然后将这个ID作为插入到Shard中的数据的主键。这个方法的缺点就是需要额外的插入操作如果ID库变的很大 性能也会随之降低。所以一定要保证ID库的数据集不要太大一个办法是定期清理前面的记录。复合标识符这种做法是通过联合主键的策略即通过两个字段来生成一个唯一标识前半部分是分片标识符后半部分是本地生成的标识符(比如使用AUTO_INCREMENT生成)。带分库策略的自定义序列表这种做法可以基于上面提到的自定义序列表的方法的基础上做一些技巧性的调整。即如下UPDATE sequence SET id LAST_INSERT_ID(id 1) WHERE name ‘photos‘;SELECT LAST_INSERT_ID();这里的id初始值设定上要求不同的分片取不同的值且必须连续。同时将每次递增的步长设定为服务器数目。比如有3台机器那么我们只要将初始值分别设置为123. 然后执行下面的语句即可UPDATE sequence SET id LAST_INSERT_ID(id 3) WHERE name ‘photos‘;SELECT LAST_INSERT_ID();这就可以解决主键生成冲突的问题。但是如果在运行一段时间后要进行动态扩充分片数的时候需要对序列初始值做一次调整以确保其连续性否则依然可能存在冲突的可能。当然这些逻辑可以封装在数据访问层的代码中。五. 主键的必要性表中每一行都应该有可以唯一标识自己的一列(或一组列)。虽然并不总是都需要主键但大多数数据库设计人员都应保证他们创建的每个表有一个主键以便于以后数据操纵和管理。其实即使你不建主键MySQL(InnoDB引擎)也会自己建立一个隐藏6字节的ROWID作为主键列详细可以参见[这里]因为InnoDB引擎使用聚集索引数据记录本身被存于主索引(一颗BTree)的叶子节点上。这就要求同一个叶子节点内(大小为一个内存页或磁盘页)的各条数据记录按主键顺序存放因此每当有一条新的记录插入时MySQL 会根据其主键将其插入适当的节点和位置如果页面达到装载因子(InnoDB默认为15/16)则开辟一个新的页(节点)所以在使用innoDB表时要避免随机的(不连续且值的分布范围非常大)聚簇索引特别是针对I/O密集型的应用。例如从性能角度考虑使用UUID的方案就会导致聚簇索引的插入变得完全随机。主键的数据类型选择关于主键的类型选择上最常见的争论是用整型还是字符型的问题关于这个问题《高性能MySQL》一书中有明确论断整数通常是标识列的最好选择因为它很快且可以使用AUTO_INCREAMENT,如果可能应该避免使用字符串类型作为标识列因为很消耗空间且通常比数字类型慢。如果是使用MyISAM则就更不能用字符型因为MyISAM默认会对字符型采用压缩引擎从而导致查询变得非常慢。
