PostgreSQL操作问题 转载.docVIP

PostgreSQL操作问题 转载 作者：无痕客发表于2010-08-01 00：40原文链接阅读：1评论：0 PostgreSQL操作问题 4.1)如何只选择一个查询结果的头几行?或是随机的一行? 如果你只是要提取几行数据，并且你在执行查询中知道确切的行数，你可以使用LIMIT功能。如果有一个索引与ORDER BY中的条件匹配，PostgreSQL可能就只处理要求的头几条记录，(否则将对整个查询进行处理直到生成需要的行)。如果在执行查询功能时不知道确切的记录数，可使用游标(cursor)和FETCH功能。 可使用以下方法提取一行随机记录的： SELECT cols FROM tab ORDER BY random() LIMIT 1； 4.2)如何查看表、索引、数据库以及用户的定义?如何查看psql里用到的查询指令并显示它们? 在psql中使用\dt命令来显示数据表的定义，要了解psql中的完整命令列表可使用\?，另外，你也可以阅读psql的源代码文件pgsql/src/bin/psql/describe.c，它包括为生成psql反斜杠命令的输出的所有SQL命令。你还可以带-E选项启动psql，这样它将打印出执行你在psql中所给出的命令的内部实际使用的SQL查询。PostgreSQL也提供了一个兼容SQL的INFORMATION SCHEMA接口，你可以从这里获取关于数据库的信息。 在系统中有一些以pg_打头的系统表也描述了表的定义。 使用psql-l指令可以列出所有的数据库。 也可以浏览一下pgsql/src/tutorial/syscat.source文件，它列举了很多可从数据库系统表中获取信息的SELECT语法。 4.3)如何更改一个字段的数据类型? 在8.0版本里更改一个字段的数据类型很容易，可使用ALTER TABLE ALTER COLUMN TYPE。 在以前的版本中，可以这样做： BEGIN； ALTER TABLE tab ADD COLUMN new_col new_data_type； UPDATE tab SET new_col=CAST(old_col AS new_data_type)； ALTER TABLE tab DROP COLUMN old_col； COMMIT； 你然后可以使用VACUUM FULL tab指令来使系统收回无效数据所占用的空间。 4.4)一行记录，一个表，一个库的最大尺寸是多少? 下面是一些限制： 一个数据库最大尺寸?无限制(已存在有32TB的数据库) 一个表的最大尺寸?32 TB 一行记录的最大尺寸?1.6 TB 一个字段的最大尺寸?1 GB 一个表里最大行数?无限制 一个表里最大列数?250-1600(与列类型有关) 一个表里的最大索引数量?无限制 当然，实际上没有真正的无限制，还是要受可用磁盘空间、可用内存/交换区的制约。事实上，当这些数值变得异常地大时，系统性能也会受很大影响。 表的最大尺寸32 TB不需要操作系统对大文件的支持。大表用多个1 GB的文件存储，因此文件系统尺寸的限制是不重要的。 如果缺省的块大小增长到32K，最大的表尺寸和最大列数还可以增加到四倍。 4.5)存储一个典型的文本文件里的数据需要多少磁盘空间? 一个Postgres数据库(存储一个文本文件)所占用的空间最多可能需要相当于这个文本文件自身大小5倍的磁盘空间。 例如，假设有一个100,000行的文件，每行有一个整数和一个文本描述。假设文本串的平均长度为20字节。文本文件占用2.8 MB。存放这些数据的PostgreSQL数据库文件大约是6.4 MB： 32字节：每行的头(估计值) 24字节：一个整数型字段和一个文本型字段 +4字节：页面内指向元组的指针 -- 60字节每行 PostgreSQL数据页的大小是8192字节(8 KB)，则： 8192字节每页 ---=136行/数据页(向下取整) 60字节每行 数据行 --=735数据页(向上取整) 128行每页 735数据页*8192字节/页=6,021,120字节(6 MB) 索引不需要这么多的额外消耗，但也确实包括被索引的数据，因此它们也可能很大。 空值NULL存放在位图中，因此占用很少的空间。 4.6)为什么我的查询很慢?为什么这些查询没有利用索引? 并非每个查询都会自动使用索引。只有在表的大小超过一个最小值，并且查询只会选中表中较小比例的记录时才会采用索引。这是因为索引扫描引起的随即磁盘存取可能比直接地读取表(顺序扫描)更慢。 为了判断是否使用索引，PostgreSQL必须获得有关表的统计值。这些统计值可以使用VACUUM ANALYZE，或ANALYZE获得。使用统计值，优化器知道表中有多少行，就能够更好地判断是否利用索引。统