mysql与pg的主键索引说明
mysql与pg的主键说明
- mysql
- 插入顺序与插入随机测试案例
- B+Tree
- 原理
- id顺序的方式插入
- id随机的方式插入
- Postgresql
- 堆组织表
- CTID
- Pg的元组及索引原理
- Pg与mysql与oracel
- 问题
mysql
插入顺序与插入随机测试案例
创建了两张表 一张表是按顺序来插入的 一张表是按随机的方式来插入的,他们都只有两个字段id与name 插入的数据量都是一样的单线程插入一百万务数据,他们的耗时差异是不大的 都是400多秒
但是这两张表占用的空间差异是非常大的
而顺序方式插入的表，则只占用了27兆的空间 ,随机方式插入的是43M
B+Tree
原理
为何差跑这么巨大,Mysql的主键是按照因为B+树来存储的正常的存储
id顺序的方式插入
当我们按照顺序的方式来插入数据,这些数据都会落入最后一个节点上**,因为他前面的所有节点都是满的(按顺序嘛)**即下图是每个节点都存储5条数据
id随机的方式插入
如果按照随机的方式插入,那么他就有可能插入到一个已经满的节点上,这时节点就会分裂,分裂成两个节点 就有可能每个节点上存储了三条数据
缺点:随机插入就会产生了很多不满的节点,即在数据量一样的情况下随机插入产生了更多不满的节点 ,空间差很大 效率也不行 本地的,远程调用
Postgresql
数据库关于索引的系统表
Pg_index记录索引的具体属性
Pg_opclass
堆组织表
- pg默认采用的是堆组织表的方式来存储
- 堆表（heap table）数据插入时存储位置是随机的，主要是数据库内部块的空闲情况决定，获取数据是按照命中率计算，全表扫表时不见得先插入的数据先查到。
- 数据存储与索引存储分开, 数据存放在数据里面,索引存放在索引里
堆的图
堆就是无序数据的集合,索引就是将数据变得有序,在索引中键值有序,数据还是无序的
堆表中,主键索引和普通索引一样的,叶子节点存放的是指向堆表中数据的指针（可以是一个页编号加偏移量）,指向物理地址,没有回表的说法
堆表中,主键和普通索引基本上没区别,和非空的唯一索引没区别
CTID
PG数据索引的存储顺序并不以某一列的排序顺序来存储,而是以行CTID号来存储(每一行有一个行ID号,并且这个行ID号是一个坐标)
即新增数据永远是在索引存储的最后一个叶子,即不会存在B+Tree的页分列
ctid坐标(0,1)在第0个文件page页上递增
Pg的元组及索引原理
http://www.postgres.cn/v2/news/viewone/1/539
Pg与mysql与oracel
Oracle支持堆表，也支持索引组织表
PostgreSQL只支持堆表，不支持索引组织表
Innodb只支持索引组织表
问题
postgresql得看看主键是否一定有序的?
答: 和主键没关系,所以主键无序也可以,PG索引存储的是通过系统内部的ctid实现的对标oracel的rowId
- PG本身是使用堆表的方式进行数据存储与索引存储,即数据的存储根据内部块的空闲情部随机存储,而索引的存储根据不以某一列的排序顺序来存储,而是以行ctid号来存储(每一行有一个行ID号,并且这个行ID号是以坐标方式自增的系统内部的)
- 下图是阿里巴巴关于这块儿的阐述
- 我们的想法,即始PG主键可以不用有序递增,其实我们还是推荐业务尽量保持分布式全局唯一并且有序递增,这样在业务上很方便,比如在sharding时进行分页查询也能用上哦
|
1
2 3 4 5 |
分页的性能优化
select * from t_order order by id limit 1000000,10 select * from t_order order by id limit 0,1000010 (改写后) 建议的性能优化: select * from t_order where id>1000000 and id<1000010 order by id 或者 select * from t_order where id>1000000 limit 10 |
即id要是有序自增有时还能用上的啊
