❝开头还是介绍一下群，如果感兴趣PolarDB ,MongoDB ,MySQL ,PostgreSQL ,Redis, OceanBase, Sql Server等有问题，有需求都可以加群群内有各大数据库行业大咖，可以解决你的问题。加群请联系 liuaustin3 ，（共3000人左右 1 + 2 + 3 + 4 +5 + 6 + 7 + 8 +9）(1 2 3 4 5 6 7群均已爆满，开8群350+ 9群 100+)
PostgreSQL字段类型与创建索引和查询之间的关系，通过这篇文章，我们希望数据库管理员以及开发人员能认识到，在PostgreSQL选择正确的数据类型对于数据处理和查询的优势，更进一步的论证，各个POSTGRESQL数据类型在数据处理中的损耗。
这里我们通过 int,bigint,float,numeric,text等字段来进行相关的测试。
通过下面我们可以看到各个字段在建立索引时的时间。
db1=#
db1=# \d+ t_demoTable "public.t_demo"Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+------------------+-----------+----------+---------+----------+-------------+--------------+-------------v1 | integer | | | | plain | | | v2 | bigint | | | | plain | | | v3 | double precision | | | | plain | | | v4 | numeric | | | | main | | | v5 | text | | | | extended | | |
Access method: heapdb1=# \timing
Timing is on.
db1=# CREATE INDEX ON t_demo (v1); -- int
CREATE INDEX
Time: 24008.908 ms (00:24.009)
db1=#
db1=# CREATE INDEX ON t_demo (v2);
CREATE INDEX
Time: 25224.961 ms (00:25.225)
db1=#
db1=#
db1=# CREATE INDEX ON t_demo (v3);
CREATE INDEX
Time: 40735.718 ms (00:40.736)
db1=#
db1=# CREATE INDEX ON t_demo (v4);
CREATE INDEX
Time: 56503.447 ms (00:56.503)
db1=#
db1=#
db1=#
db1=# CREATE INDEX ON t_demo (v5);
CREATE INDEX
Time: 156576.956 ms (02:36.577)
db1=#表的列的释义
列名 | 数据类型 | 存储方式 | 描述 |
|---|---|---|---|
v1 | integer | plain | 整数类型 |
v2 | bigint | plain | 大整数类型 |
v3 | double precision | plain | 浮点数 |
v4 | numeric | main | 高精度数值 |
v5 | text | extended | 文本 |
列名 | 数据类型 | 索引创建耗时（ms） | 索引创建耗时（格式化） |
|---|---|---|---|
v1 | integer | 24008.908 | 00:24.009 |
v2 | bigint | 25224.961 | 00:25.225 |
v3 | double precision | 40735.718 | 00:40.736 |
v4 | numeric | 56503.447 | 00:56.503 |
v5 | text | 156576.956 | 02:36.577 |
通过上图我们创建索引的时间可以看出，随着数据的精度提高，创建索引的时间逐步提高。原因是什么，这就要说到CPU的指令集。CPU 在处理数据中使用的是指令集，这就牵扯到CPU硬件本身支持的原始数据类型，如整形，浮点等都是CPU直接支持的原始类型。这与CPU内部的寄存器的宽度对应。
而如POSTGRESQL这样的数据库产品，使用C语言进行编写，C语言针对原始的数据类型设计为直接映射到CPU的原生数据类型。原始的数据类型可以产生编译器高度优化的机器代码。从而利用CPU固有的方式来处理这些数据类型。效率来自于直接性，任何无法直接进行处理的数据类型，都需要进行转换，这就是上面bigint 比 int大一倍，处理的速度并没有慢一倍的原因。
类型类别 | 典型名称 | 常见大小（位/字节） | 有符号范围 | 无符号范围 | 相关 CPU/架构/标准 |
|---|---|---|---|---|---|
整数 | Byte, octet, char (C99) | 8 位 / 1 字节 | -128 到 +127 | 0 到 255 | x86, C99 |
整数 | x86 word, short, int (C) | 16 位 / 2 字节 | -32,768 到 +32,767 | 0 到 65,535 | x86, C |
整数 | x86 double word, int, long (C) | 32 位 / 4 字节 | -2,147,483,648 到 +2,147,483,647 | 0 到 4,294,967,295 | x86, C |
整数 | x86 quadruple word, long long, long (C) | 64 位 / 8 字节 | -9,223,372,036,854,775,808 到 +9,223,372,036,854,775,807 | 0 到 18,446,744,073,709,551,615 | x86, C |
浮点数 | float | 32 位 / 4 字节 | IEEE 754 单精度 | N/A | IEEE 754 |
浮点数 | double | 64 位 / 8 字节 | IEEE 754 双精度 | N/A | IEEE 754 |
布尔值 | Boolean, bool | 1 位（通常为 1 字节） | N/A | True / False | 多数语言 / 硬件 |
除此以外，在内存中的内存对齐对于建立索引的速度也有影响，如B树的构建是需要在内存中进行建立的，然后在持久化到磁盘中，对数据在内存访问的方式是影响索引建立速度的第二个点，那么什么是内存对齐。
我们画一个图来举一个例子：
上图中很明显，在内存中我们的 char和int结构在内存中基于a是一个字节，则会匹配3个pad来进行地址的对齐。上面的案例就是不对齐的情况，而编译器在a 和 b之间进行了对齐。
说完这些，很多人看到这里很可能不知道这篇文章要说什么，按照我一贯的风格，马上就要突变。因为要说到开发，表设计，PostgreSQL的表设计。
在PostgreSQL中我们都明白，基于PG的原理
1 索引越少越少越好
2 索引越小越好
（如果你还不明白为什么，说明你对PG的原理不明晰，不清楚）
那么在明白了原理上面的比较后，我们要做的就是下一步，如何让索引变少，如何让索引变小。
方案 1:
替代法：在很多商业数据库中都这样设计，原因有几个其中最初的目的并不是为了要节省索引的大小，而是杜绝商业的剽窃，提高商业剽窃的难度。所以如果你们注意一些大型的国外的软件厂商特别愿意中这样的方式设计数据库表。
其原理是，设计两张表，一张为真实的metadata表也就是第二张表的信息解释，这里做一个举例。
CREATE TABLE order_status_dict (status_id SMALLINT PRIMARY KEY,status_text TEXT NOT NULL,description TEXT,is_active BOOLEAN DEFAULT TRUE
);-- 示例插入数据：
INSERT INTO order_status_dict (status_id, status_text, description) VALUES(1, '待支付', '用户已下单，尚未付款'),(2, '已支付', '用户完成支付'),(3, '已取消', '订单取消'),(4, '退款中', '退款流程已启动'),(5, '已退款', '退款完成'),(6, '已完成', '订单流程已完结');CREATE TABLE orders (
order_id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL,
status_id SMALLINT NOT NULL REFERENCES order_status_dict(status_id),
order_created_at TIMESTAMP NOT NULL
);CREATE INDEX idx_status_id ON orders(status_id);在一个成熟商业软件公司，这样的设计是一个常规的设计，但我知道大部分的甲方公司的开发并没有这样的意识和开发方式。
2 字段类型缩减法
在一些大量使用float，numeric的表中，可以考虑通过附属列来进行表达比如
12.35，可以变成 列1 1235 列2 2，这个意思就是第一列的数据需要小数位2位。当然我非常理解开发同学对这样的优化的不理解，但大部分情况这样开发系统还是为了防止数据丢失，如果数据被窃取，大部分不明白其中表设计的人是无法看懂数据的真实含义。
这也是我年轻在某个公司见过的开发人员使用的一些方案来避免数据被拿走后就明白其中的含义的方案。
至于其他，建议经常去扫描POSTGRESQL中的无用索引，合并一些索引，能复合的索引就别单独建立了。
置顶
写了3750万字的我，在2000字的OB白皮书上了一课--记 《OceanBase 社区版在泛互场景的应用案例研究》
SQLSHIFT 是爱可生对OB的雪中送炭！
青春的记忆，MySQL 30年感谢有你，再见！（译）
老实人做的数据库产品，好像也不“老实” ！
疯狂老DBA 和 年轻“网红” 程序员 --火星撞地球-- 谁也不是怂货
哈呀站，OB广州开发者大会 之 “五” 眼联盟
和架构师沟通那种“一坨”的系统，推荐只能是OceanBase，Why ?
DBA 怎么变得更强-应对架构师提出高并发问题？
鸿门宴讲PostgreSQL -- 被拉去央企救场一天
美国知名大学开授China数据库理论，你没看错！
DBA 会架构，实际案例分析--“IT流氓”得下跪
搞 PostgreSQL多才多艺的人--赵渝强 《PG数据库实战派》
追逐太阳的男人--林春 《金融数据库转型实战》
打破DBA局限：像架构师一样思考，提升你的技术价值-- 访蚂蚁金服P9 朱春茂
OceanBase 相关文章写了3750万字的我，在2000字的OB白皮书上了一课--记 《OceanBase 社区版在泛互场景的应用案例研究》
哈呀站，OB广州开发者大会 之 “五” 眼联盟
OceanBase 单机版可以大批量快速部署吗？ YES
OceanBase 6大学习法--OBCA视频学习总结第六章
OceanBase 6大学习法--OBCA视频学习总结第五章--索引与表设计
OceanBase 6大学习法--OBCA视频学习总结第五章--开发与库表设计
OceanBase 6大学习法--OBCA视频学习总结第四章 --数据库安装
OceanBase 6大学习法--OBCA视频学习总结第三章--数据库引擎
OceanBase 架构学习--OB上手视频学习总结第二章 （OBCA）
OceanBase 6大学习法--OB上手视频学习总结第一章
没有谁是垮掉的一代--记 第四届 OceanBase 数据库大赛
OceanBase 送祝福活动，礼物和幸运带给您
跟我学OceanBase4.0 --阅读白皮书 (OB分布式优化哪里了提高了速度)
跟我学OceanBase4.0 --阅读白皮书 （4.0优化的核心点是什么）
跟我学OceanBase4.0 --阅读白皮书 （0.5-4.0的架构与之前架构特点）
跟我学OceanBase4.0 --阅读白皮书 （旧的概念害死人呀，更新知识和理念）
聚焦SaaS类企业数据库选型（技术、成本、合规、地缘政治）
OceanBase 学习记录-- 建立MySQL租户,像用MySQL一样使用OB
MongoDB 相关文章
MongoDB “升级项目” 大型连续剧(4)-- 与开发和架构沟通与扫尾
MongoDB “升级项目” 大型连续剧(3)-- 自动校对代码与注意事项
MongoDB “升级项目” 大型连续剧(2)-- 到底谁是"der"
MongoDB “升级项目” 大型连续剧(1)-- 可“生”可不升
MongoDB 大俗大雅，上来问分片真三俗 -- 4 分什么分
MongoDB 大俗大雅，高端知识讲“庸俗” --3 奇葩数据更新方法
MongoDB 学习建模与设计思路--统计数据更新案例
MongoDB 大俗大雅，高端的知识讲“通俗” -- 2 嵌套和引用
MongoDB 大俗大雅，高端的知识讲“低俗” -- 1 什么叫多模
MongoDB 合作考试报销活动 贴附属，MongoDB基础知识速通
MongoDB 年底活动，免费考试名额 7个公众号获得
MongoDB 使用网上妙招，直接DOWN机---清理表碎片导致的灾祸 (送书活动结束)
MongoDB 2023年度纽约 MongoDB 年度大会话题 -- MongoDB 数据模式与建模
MongoDB 双机热备那篇文章是 “毒”
MongoDB 会丢数据吗？在次补刀MongoDB 双机热备
MONGODB ---- Austindatabases 历年文章合集
PolarDB 相关文章
MySQL 和 PostgreSQL 可以一起快速发展，提供更多的功能？
这个MySQL说“云上自建的MySQL”都是”小垃圾“
PolarDB MySQL 加索引卡主的整体解决方案
“PostgreSQL” 高性能主从强一致读写分离，我行，你没戏！
PostgreSQL 的搅局者问世了，杀过来了！
在被厂商围剿的DBA 求生之路 --我是老油条
POLARDB 添加字段 “卡” 住---这锅Polar不背
PolarDB 版本差异分析--外人不知道的秘密（谁是绵羊，谁是怪兽）
在被厂商围剿的DBA 求生之路 --我是老油条
PolarDB 答题拿-- 飞刀总的书、同款卫衣、T恤，来自杭州的Package（活动结束了）
PolarDB for MySQL 三大核心之一POLARFS 今天扒开它--- 嘛是火
PostgreSQL 相关文章
PostgreSQL 无服务 Neon and Aurora 新技术下的新经济模式 （翻译）
PostgreSQL的"犄角旮旯"的参数捋一捋
PostgreSQL逻辑复制槽功能
PostgreSQL 扫盲贴 常用的监控分析脚本
“PostgreSQL” 高性能主从强一致读写分离，我行，你没戏！
PostgreSQL 添加索引导致崩溃，参数调整需谨慎--文档未必完全覆盖场景
PostgreSQL 的搅局者问世了，杀过来了！
PostgreSQL SQL优化用兵法，优化后提高 140倍速度
PostgreSQL 运维的难与“难” --上海PG大会主题记录
PostgreSQL 什么都能存，什么都能塞 --- 你能成熟一点吗？
PostgreSQL 迁移用户很简单 --- 我看你的好戏
PostgreSQL 用户胡作非为只能受着 --- 警告他
全世界都在“搞” PostgreSQL ，从Oracle 得到一个“馊主意”开始
PostgreSQL 加索引系统OOM 怨我了--- 不怨你怨谁
PostgreSQL “我怎么就连个数据库都不会建？” --- 你还真不会！
病毒攻击PostgreSQL暴力破解系统，防范加固系统方案（内附分析日志脚本）
PostgreSQL 远程管理越来越简单，6个自动化脚本开胃菜
PostgreSQL 稳定性平台 PG中文社区大会--杭州来去匆匆
PostgreSQL 如何通过工具来分析PG 内存泄露
PostgreSQL 分组查询可以不进行全表扫描吗？速度提高上千倍？
POSTGRESQL --Austindatabaes 历年文章整理
PostgreSQL 查询语句开发写不好是必然，不是PG的锅
PostgreSQL 字符集乌龙导致数据查询排序的问题，与 MySQL 稳定 "PG不稳定"
PostgreSQL Patroni 3.0 新功能规划 2023年 纽约PG 大会 （音译）
PostgreSQL 玩PG我们是认真的，vacuum 稳定性平台我们有了
PostgreSQL DBA硬扛 垃圾 “开发”，“架构师”，滥用PG 你们滚出 ！（附送定期清理连接脚本）
DBA 失职导致 PostgreSQL 日志疯涨
MySQL相关文章
青春的记忆，MySQL 30年感谢有你，再见！（译）
MySQL 8 SQL 优化两则 ---常见问题
MySQL SQL优化快速定位案例 与 优化思维导图
"DBA 是个der" 吵出MySQL主键问题多种解决方案
MySQL 怎么让自己更高级---从内存表说到了开发方式
MySQL timeout 参数可以让事务不完全回滚
MySQL 让你还用5.7 出事了吧，用着用着5.7崩了
MySQL 的SQL引擎很差吗？由一个同学提出问题引出的实验
用MySql不是MySQL, 不用MySQL都是MySQL 横批 哼哼哈哈啊啊
MYSQL --Austindatabases 历年文章合集
临时工访谈系列
没有谁是垮掉的一代--记 第四届 OceanBase 数据库大赛
ETL 行业也够卷，云化ETL，ETL 软件不过了
SQL SERVER 系列
SQL SERVER维保AI化，从一段小故事开始
SQL SERVER 如何实现UNDO REDO 和PostgreSQL 有近亲关系吗
SQL SERVER 危险中，标题不让发，进入看详情（译）
