问题2：Inodb自增主键和业务主键优缺点

最新推荐文章于 2024-11-14 06:36:31 发布

原创最新推荐文章于 2024-11-14 06:36:31 发布 · 1.5k 阅读

0 ·

CC 4.0 BY-SA版权

MySql 专栏收录该内容

11 篇文章

订阅专栏

本文对比了两种不同的客户信息表设计方案，一种采用自增ID作为主键，适合写操作频繁的场景；另一种以手机号为主键，适用于读取操作为主的场景。通过对比两者在存储空间、查询效率及写入性能上的差异，帮助读者理解不同表设计的特点。

创建表

CREATE TABLE t_cic_customer1 (
id bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT ‘客户ID, 可以用客户ID或者source加mobile唯一表示用户’,
mobile varchar(20) NOT NULL COMMENT ‘客户手机号码’,
device_id varchar(64) DEFAULT ‘0’ COMMENT ‘设备号’,
cc_id int(11) DEFAULT NULL COMMENT ‘BC生成的客户ID’,
create_time datetime DEFAULT ‘0000-00-00 00:00:00’ COMMENT ‘创建时间’,
PRIMARY KEY (id),
UNIQUE KEY i_mobile_device (mobile,device_id),
KEY i_name (create_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=’客户信息表’;

CREATE TABLE t_cic_customer2 (
mobile varchar(20) NOT NULL COMMENT ‘客户手机号码’,
device_id varchar(64) DEFAULT ‘0’ COMMENT ‘设备号’,
cc_id int(11) DEFAULT NULL COMMENT ‘BC生成的客户ID’,
create_time datetime DEFAULT ‘0000-00-00 00:00:00’ COMMENT ‘创建时间’,
PRIMARY KEY (mobile,device_id),
KEY i_name (create_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=’客户信息表’;

插入测试数据：
insert into t_cic_customer1 select pid,mobile,device_id,cc_id,create_time from t_cic_customer;
insert into t_cic_customer2 select mobile,device_id,cc_id,create_time from t_cic_customer;

用户可以使用表t_cic_customer1和t_cic_customer2进行表设计。表1和表2有什么区别呢？
表1表3796237条记录，记录是按照顺序存储，占用空间537M，
优点：主键ID自增,在写入数据的时候,Btree分裂成本低,写性能高。
缺点：物理空间相对较多，如果根据mobile来查记录,需要走两次IO
适用场景：写操作较多的场景
表2表3796237条记录，记录是按照顺序存储，占用空间497M，
优点：物理空间相对减少，根据mobile查数据,直接走主键拿到数据，无需回表
缺点：(mobile,device_id)为随机写入,Btree分裂成本高,写性能低
适用场景：写少读多的场景,例如从hadoop回流到MySQL的统计结果表，这种统计结果一般数据较多，但主要是读。

总结：客户信息是属于读写比较多的操作，使用表1方案，设置id作为自增主键。