mysql-faq

0.0.1. 主键自增, 并按特定格式的

主键不支持varchar自增, 所以需要拼接英文字符串的只能在程序里控制.

假如主键仍是数字, 比如需要一个定长4为的ID, 可以使用以下方式:

CREATE TABLE `auto` (                                     
 `id` int(4) unsigned zerofill NOT NULL auto_increment,
 PRIMARY KEY  (`id`)                                     
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


insert into auto select NULL;

主键步长和偏移量可以根据需要更改.

1. InnoDb 下为什么用自增列作为主键

InnoDB 无法自定义选择哪一列作为聚簇索引, 而是默认选择主键作为聚簇索引.
如果表中没有定义了主键, 则会选择没有 NULL 值的唯一索引在作为聚簇索引; 如果没有符合条件的唯一索引, 则会选择内置的 6 字节长度的 ROWID 作为聚簇索引. ROWID 是随行写入而逐渐递增的.

在聚簇索引中, 数据记录存放在主索引 (B+Tree) 的叶子节点上. 所有叶子节点会根据 KEY 值按顺序存放.

更新聚簇索引可能面临 页分裂 的问题.
如果主键非自增, 当插入新的记录时, 加入新记录的 KEY 需要插入某个已满的页中, 存储引擎就会将该页分裂成两个页面来容纳这条记录.
如果更新主键列, 存储引擎就需要将该行移动到新的位置, 同样可能面临这个问题.
这就会导致磁盘碎片的产生.
可以使用 OPTIMIZE TABLE 命令重新组织表结构.

那么如果是自增主键, 则每次插入新的记录时, 就可以顺序添加到当前索引节点的后续位置. 所以按主键顺序插入是加载数据到 InnoDB 表中速度最快的方式.
至少保证了插入的速度. 而一般不会有更新主键的情况, 最多出现更新唯一索引的情况.

2. 既然 varchar 是可变长度的, 那么只有存得下内容, 如 ‘hello’ , 那 varchar(5) 比 varchar(500) 好在哪里?

由于 varchar 是可变长度的, 那他们的存储开销是一样的
但是在内存的中消耗不一样, 随之带来的是排序与操作时的消耗更大.