数据库主键/索引 必要性

数据库主键/索引 必要性

Author: 严唯嘉
Date: 2018/08/28
@toc

背景摘要:

这篇博文起因是源于一个系统设计缺陷, 索性及时发现,没有引起事故.
主要问题定位在分页查询数据发现查询多次重复数据.
以下是项目流程,问题出在了第三步:
应监管需要,线上生产数据需要经过一个数据平台进行初步的处理后才能交给业务方.本项目属于其中一个环境.
1. 项目需要从别的数据库中导出数据,将数据以一定的方式,推送给业务方.
2. 项目只知道待导出的数据在哪个 schema 下,并不知道有哪些 table ,有哪些字段, 所以主键也无从谈起.
3. 每次启动前都先从给定的数据库中查询 schema 下有哪些 table, 获取到 table 后再进一步获取 table 的字段有哪些, 分页获取
4. 将所有数据以一定的形式推送给业务方,完成一个推送任务.

结论:

  1. 分页查询必须进行 order by, 保证数据完整性,无论有无主键,是否有重复数据,都需要 order by
    • 若存在无主键无索引且存在重复记录的情况下, postgreSQL 数据库可以使用ctid作为排序字段, oracle 数据库可以使用rownumrowid作为排序字段 . 其中 ctidrowid都代表了数据表中的物理地址. 所以可以保证唯一性.
  2. 规范表,每个表都设置一个主键
  3. 经常作为查询条件的字段增加索引, 若能保证唯一性,则增加唯一索引.

sql 语句验证

实现说明

测试使用的数据库安装在本机, 无网络耗时, 所有测试结果均取平均值进行说明

系统环境/配置信息

字段 说明
数据库 PostgreSQL 9.5
系统 OSX
配置 i7低压,512GB SSD, 16GB ram

建表语句

create table public.test
(
  id character  varying(100),
  random_md5 character varying(100),
  create_date timestamp  without  time  zone,
  generate_key integer
)

初始化插入200w 条数据

insert into public.test select md5(generate_series(1,2000000)::text),md5((random()*generate_series(1,2000000))::text),clock_timestamp(),generate_series(1,2000000);

不设置索引和主键

select * from test limit 50 offset 50000; --5ms 每次 select 出来的内容不一致

select * from test order  by id asc  limit 50 offset 50000; --14.7s 无主键, 用 id 排序

select * from test order  by ctid asc  limit 50 offset 50000; --900ms 使用 pg 自带的一个类似 oracle 的字段

select row_number() over() as rownum,* from test limit 50 offset 50000; --22ms 每次 select 出来的结果不一致

设置 id 为主键

alter table public.test add constraint primary_id primary key(id);

select * from test limit 50 offset 50000; --5ms 每次 select 出来的内容不一致

select * from test order  by id asc  limit 50 offset 50000; --132ms

给 random_md5 字段增加唯一索引测试

alter table public.test add constraint index_random_md5 unique(random_md5);

select * from test order by random_md5 asc limit 50 offset 50000; --148ms 用索引排序查找.

发表评论

电子邮件地址不会被公开。 必填项已用*标注