关于auto_increment列的问题

zfm1988 · 发表于 2011-11-19 12:33

这个也是坑爹吗？现在有很多设计呢？不知道有什么好办法，让XX修改！

legsantking · 发表于 2011-11-19 13:05

本帖最后由 legsantking 于 2011-11-19 13:18 编辑

icer_repls 发表于 2011-11-18 18:43
应该还是有一些意义的，如果太疏松了的话，对索引有一定的影响，你想太疏松必然导致这个AUTO_INCREMENT列 ...

没有任何意义，索引的组织结构跟稀疏没什么关系，索引深度是由出度决定的，出度由单页键值数决定，单页键值数由键值大小决定，在单页键值不增大的情况下，索引分支是不会增大的。
索引树出度是由页数决定，那在页数没有增加的情况下，索引树也不会增大。
可以测试，相同键值数量，一个紧凑的Int B+树的大小和松散索引树的大小是一致的
且扫描也要分为是基于Secondary Index（SI）和 Clustered Primary Key（PK）扫描，对于任何SI都是随机PK访问，对于PK访问，在范围访问时，松散数据，实际也是顺序访问。
不过PK的键值是把整行数据算上（除掉Uncompressed BLOB Page，并算上Uncompressed BLOB Page前缀）而已，所以PK相对SK要深，要大，效率也要比SK慢很多，所以能在SK访问的数据，不要访问PK。

icer_repls · 发表于 2011-11-19 14:13

legsantking 发表于 2011-11-19 13:05
没有任何意义，索引的组织结构跟稀疏没什么关系，索引深度是由出度决定的，出度由单页键值数决定，单页键 ...

1."单页键值数由键值大小决定，在单页键值不增大的情况下，索引分支是不会增大的"
恩，确实是这样的，但是，如果auto_increment列中间空缺很多（由于delete操作），以后的insert势必就会以当前 increment count的最大值+1来使用对吧，这不就会导致键值变大吗？我是从这个角度说的。

2."所以PK相对SK要深，要大，效率也要比SK慢很多，所以能在SK访问的数据，不要访问PK。"
这句话不恰当，因为很多情况访问了SK以后还得继续访问PK(当使用SK的query所需要的字段不是全部属于SK索引字段，也就是说不能通过仅仅扫描SK就能完成任务)。
当然如果单纯的说SK,PK，那SK确实比Pk快，因为一般情况SK这棵树比PK小得多。但现实中是单纯依靠SK不能完成任务，而是必须依靠第二次去扫描PK。

legsantking · 发表于 2011-11-19 14:31

本帖最后由 legsantking 于 2011-11-19 14:44 编辑

icer_repls 发表于 2011-11-19 14:13
1."单页键值数由键值大小决定，在单页键值不增大的情况下，索引分支是不会增大的"
恩，确实是这样的，但 ...

首先抱歉，是SI，不是SK，笔误
首先，键值大小总是在一个长度范围内，如int ，你1和1亿又或增大到多少还是4byte，你datetime不会因为是1990年变成2byte，也不会因为是9999年变成12byte，键值本身不会变大。当然在可变长度索引中，会导致索引slot row 分布不均，这也是需要设计字段的时候要注意的
其次索引是具有自动维护功能，被更新(UIDR)的索引是会被meger掉的（拆分和合并，部分重构）等方式
其次，任何SI访问都会导致随机访问(除非你的SI=PK)，在PK大小一致的时候，是不会导致你说的这个问题，任何SI访问都是如此，使用SI来访问数据，就是考验DBA同学们的设计能力，优化能力的地方，当然访问PK也同样重要。
至于PK为什么会比SI要深，可以参考http://www.itpub.net/thread-1513014-1-1.html里的详细页，有解释

icer_repls · 发表于 2011-11-19 15:00

legsantking 发表于 2011-11-19 14:31
首先抱歉，是SI，不是SK，笔误
首先，键值大小总是在一个长度范围内，如int ，你1和1亿又或增大到多少还 ...

恩，你说的这儿键值大小所占用的存储空间，这个是对的。
我也没深入去了解过b-tree这棵树到底是怎样维护的，我的猜想是这样的：
假如首先由于1,2,3,4,5,6,7这几个键值，同样的由于delete操作，键值的分布不均匀了，变成了类似1,5,233,5555, 54555, 124566,99333333, 99999999999这种键值。
后来的这种键值可能导致分支节点增加，这就是我想表达的意思（我自己也不确定，猜想！！！）

然后对于SI, pk。
PK大小一致的时候，是不会导致什么问题？你这个PK大小具体是指哪个方面的大小？
只访问SI就能完成query只有一种情况：query所需要的字段全部在SI叶子节点里面，而SI的叶子节点并不像PK一样，保存了全部的字段。这里确实需要很好的设计

icer_repls · 发表于 2011-11-19 20:08

@legsantking:
谢谢你的指正.
查了一下原理方面的书籍，关于插入、删除行导致索引树节点的变化，虽然没看很懂，不过大概意思是分支节点增多只与键值的个数有关，而我之前的理解是键值的范围比较大，索引的分支节点就会比较多。现在想想也不应该是这样的。实际上导致节点的裂变、合并是看这个节点上的存放了多少个键值，超过了每个节点上所规定的，那么这个索引节点无法更多的键值，必然就要新增加一个索引节点（也就是分支节点会增加）。