mysql自增id原理_mysql使用自增Id为什么存储比较快

转自&＃xff1a;https://blog.csdn.net/bigtree_3721/article/details/73151028

InnoDB引擎表的特点

1、InnoDB引擎表是基于B&＃43;树的索引组织表(IOT)

关于B&＃43;树

(图片来源于网上)

B&＃43; 树的特点:

(1)所有关键字都出现在叶子结点的链表中(稠密索引)&＃xff0c;且链表中的关键字恰好是有序的;

(2)不可能在非叶子结点命中;

(3)非叶子结点相当于是叶子结点的索引(稀疏索引)&＃xff0c;叶子结点相当于是存储(关键字)数据的数据层;

2、如果我们定义了主键(PRIMARY KEY)&＃xff0c;那么InnoDB会选择主键作为聚集索引、如果没有显式定义主键&＃xff0c;则InnoDB会选择第一个不包含有NULL值的唯一索引作为主键索引、如果也没有这样的唯一索引&＃xff0c;则InnoDB会选择内置6字节长的ROWID作为隐含的聚集索引(ROWID随着行记录的写入而主键递增&＃xff0c;这个ROWID不像ORACLE的ROWID那样可引用&＃xff0c;是隐含的)。

3、数据记录本身被存于主索引(一颗B&＃43;Tree)的叶子节点上。这就要求同一个叶子节点内(大小为一个内存页或磁盘页)的各条数据记录按主键顺序存放&＃xff0c;因此每当有一条新的记录插入时&＃xff0c;MySQL会根据其主键将其插入适当的节点和位置&＃xff0c;如果页面达到装载因子(InnoDB默认为15/16)&＃xff0c;则开辟一个新的页(节点)

4、如果表使用自增主键&＃xff0c;那么每次插入新的记录&＃xff0c;记录就会顺序添加到当前索引节点的后续位置&＃xff0c;当一页写满&＃xff0c;就会自动开辟一个新的页

5、如果使用非自增主键(如果身份证号或学号等)&＃xff0c;由于每次插入主键的值近似于随机&＃xff0c;因此每次新纪录都要被插到现有索引页得中间某个位置&＃xff0c;此时MySQL不得不为了将新记录插到合适位置而移动数据&＃xff0c;甚至目标页面可能已经被回写到磁盘上而从缓存中清掉&＃xff0c;此时又要从磁盘上读回来&＃xff0c;这增加了很多开销&＃xff0c;同时频繁的移动、分页操作造成了大量的碎片&＃xff0c;得到了不够紧凑的索引结构&＃xff0c;后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。

综上总结&＃xff0c;如果InnoDB表的数据写入顺序能和B&＃43;树索引的叶子节点顺序一致的话&＃xff0c;这时候存取效率是最高的&＃xff0c;也就是下面这几种情况的存取效率最高&＃xff1a;

1、使用自增列(INT/BIGINT类型)做主键&＃xff0c;这时候写入顺序是自增的&＃xff0c;和B&＃43;数叶子节点分裂顺序一致&＃xff1b;

2、该表不指定自增列做主键&＃xff0c;同时也没有可以被选为主键的唯一索引(上面的条件)&＃xff0c;这时候InnoDB会选择内置的ROWID作为主键&＃xff0c;写入顺序和ROWID增长顺序一致&＃xff1b;

除此以外&＃xff0c;如果一个InnoDB表又没有显示主键&＃xff0c;又有可以被选择为主键的唯一索引&＃xff0c;但该唯一索引可能不是递增关系时(例如字符串、UUID、多字段联合唯一索引的情况)&＃xff0c;该表的存取效率就会比较差。

《高性能MySQL》中的原话

欢迎关注