PostgreSQL的hash索引是否有用?

浏览数：23 / 时间：2015年06月12日
根据PostgreSQL的手册，PostgreSQL中hash索引有很大的缺陷，不推荐使用。

http://58.58.27.50:8079/doc/html/9.3.1_zh/indexes-types.html
-----------------------------------------------------------------------------
Hash 索引操作目前没有记录 WAL 日志，因此如果数据库崩溃有未写入的改变， 我们可能需要用REINDEX重建 Hash 索引。另外， 对hash索引的改变在初始的基础备份后不是基于流复制或者基于文件复制的， 所以对于随后使用它们的查询会给出错误的回复。因为这些原因， 我们并不鼓励使用 Hash 索引。
-----------------------------------------------------------------------------

并且hash索引只支持"等于"查询，而PostgreSQL中作为默认索引类型的btree能支持包括“等于”在内的各种比较操作，功能上覆盖了hash索引。因此，在PostgreSQL中，对常规数据类型，btree索引是不二之选。（相比Oracle中有过于丰富的索引类型可选择，在PostgreSQL中建索引是不是更轻松一点？）

那么，既然不推荐使用hash索引，PostgreSQL为什么还要实现hash索引呢？
PostgreSQL的hash索引使用的线性hash算法，可以动态扩展桶数，理论上的查询复杂度接近O(1)，这是btree无法比拟的。下面做个实测，也证明了在大数量的情况下，hash索引的性能确实明显优于btree。所以在某些极少的特殊场景下，hash索引也还是可以考虑的。

环境

测试环境在Linux虚拟机上
宿主机
OS:Win7 64bit
CPU:AMD Athlon II X4 640 3.0G
内存:6G
硬盘:Apacer A S510S 128G SATA

虚拟机
OS:CentOS 6.5
CPU:x4核
MEM:2G
PostgreSQL:9.3(shared_buffers=128M)

测试方法

使用pgbench的TCP-B和Select Only测试对比性能。
分别建2个测试数据库db1和db2。db1是原始的pgbench测试数据库，即btree索引。db2则通过下面的方法修改为hash索引。

  1. [chenhj@hanode1 ~]$ psql db2
  2. psql (9.3.4)
  3. Type "help" for help.

  5. db2=# alter table pgbench_accounts DROP CONSTRAINT pgbench_accounts_pkey;
  6. ALTER TABLE
  7. db2=# \d pgbench_accounts
  8.    Table "public.pgbench_accounts"
  9.   Column | Type | Modifiers
  10. ----------+---------------+-----------
  11.  aid | integer | not null
  12.  bid | integer |
  13.  abalance | integer |
  14.  filler | character(84) |

  16. db2=# create index on pgbench_accounts using hash(aid);
  17. CREATE INDEX

测试结果

sclae=1时，差异不大；sclae=100时，hash几乎是btree的两倍性能。可见数据量越大，hash的性能优势越明显。


sclae=1时，无太大差异
数据略


sclae=10时，大概快了20%

点击(此处)折叠或打开

  1. [chenhj@hanode1 ~]$ pgbench -n -l -c 1 -T 3 db1
  2. transaction type: TPC-B (sort of)
  3. scaling factor: 10
  4. query mode: simple
  5. number of clients: 1
  6. number of threads: 1
  7. duration: 3 s
  8. number of transactions actually processed: 641
  9. tps = 213.418248 (including connections establishing)
  10. tps = 213.716676 (excluding connections establishing)
  11. [chenhj@hanode1 ~]$ pgbench -n -l -c 1 -T 3 db2
  12. transaction type: TPC-B (sort of)
  13. scaling factor: 10
  14. query mode: simple
  15. number of clients: 1
  16. number of threads: 1
  17. duration: 3 s
  18. number of transactions actually processed: 653
  19. tps = 217.440022 (including connections establishing)
  20. tps = 217.770471 (excluding connections establishing)

  22. [chenhj@hanode1 ~]$ pgbench -n -l -c 16 -T 3 db1
  23. transaction type: TPC-B (sort of)
  24. scaling factor: 10
  25. query mode: simple
  26. number of clients: 16
  27. number of threads: 1
  28. duration: 3 s
  29. number of transactions actually processed: 2064
  30. tps = 683.721499 (including connections establishing)
  31. tps = 701.078622 (excluding connections establishing)
  32. [chenhj@hanode1 ~]$ pgbench -n -l -c 16 -T 3 db2
  33. transaction type: TPC-B (sort of)
  34. scaling factor: 10
  35. query mode: simple
  36. number of clients: 16
  37. number of threads: 1
  38. duration: 3 s
  39. number of transactions actually processed: 3278
  40. tps = 1085.589348 (including connections establishing)
  41. tps = 1110.521633 (excluding connections establishing)

  43. [chenhj@hanode1 ~]$ pgbench -n -l -c 1 -T 3 -S db1
  44. transaction type: SELECT only
  45. scaling factor: 10
  46. query mode: simple
  47. number of clients: 1
  48. number of threads: 1
  49. duration: 3 s
  50. number of transactions actually processed: 4922
  51. tps = 1640.242391 (including connections establishing)
  52. tps = 1642.450916 (excluding connections establishing)
  53. [chenhj@hanode1 ~]$ pgbench -n -l -c 1 -T 3 -S db2
  54. transaction type: SELECT only
  55. scaling factor: 10
  56. query mode: simple
  57. number of clients: 1
  58. number of threads: 1
  59. duration: 3 s
  60. number of transactions actually processed: 4771
  61. tps = 1590.028578 (including connections establishing)
  62. tps = 1592.541658 (excluding connections establishing)

  64. [chenhj@hanode1 ~]$ pgbench -n -l -c 16 -T 3 -S db1
  65. transaction type: SELECT only
  66. scaling factor: 10
  67. query mode: simple
  68. number of clients: 16
  69. number of threads: 1
  70. duration: 3 s
  71. number of transactions actually processed: 32188
  72. tps = 10724.978992 (including connections establishing)
  73. tps = 10969.036021 (excluding connections establishing)

  75. [chenhj@hanode1 ~]$ pgbench -n -l -c 16 -T 3 -S db2
  76. transaction type: SELECT only
  77. scaling factor: 10
  78. query mode: simple
  79. number of clients: 16
  80. number of threads: 1
  81. duration: 3 s
  82. number of transactions actually processed: 33367
  83. tps = 11092.571963 (including connections establishing)
  84. tps = 11339.707975 (excluding connections establishing)

sclae=100时，大概快了1倍

点击(此处)折叠或打开

  1. [chenhj@hanode1 ~]$ pgbench -n -l -c 1 -T 3 -S db1
  2. transaction type: SELECT only
  3. scaling factor: 100
  4. query mode: simple
  5. number of clients: 1
  6. number of threads: 1
  7. duration: 3 s
  8. number of transactions actually processed: 1324
  9. tps = 441.307443 (including connections establishing)
  10. tps = 442.905056 (excluding connections establishing)
  11. [chenhj@hanode1 ~]$ pgbench -n -l -c 1 -T 3 -S db2
  12. transaction type: SELECT only
  13. scaling factor: 100
  14. query mode: simple
  15. number of clients: 1
  16. number of threads: 1
  17. duration: 3 s
  18. number of transactions actually processed: 3226
  19. tps = 1075.038414 (including connections establishing)
  20. tps = 1076.599774 (excluding connections establishing)

  22. [chenhj@hanode1 ~]$ pgbench -n -l -c 16 -T 3 db1
  23. transaction type: TPC-B (sort of)
  24. scaling factor: 100
  25. query mode: simple
  26. number of clients: 16
  27. number of threads: 1
  28. duration: 3 s
  29. number of transactions actually processed: 2194
  30. tps = 725.580208 (including connections establishing)
  31. tps = 741.318899 (excluding connections establishing)
  32. [chenhj@hanode1 ~]$ pgbench -n -l -c 16 -T 3 db2
  33. transaction type: TPC-B (sort of)
  34. scaling factor: 100
  35. query mode: simple
  36. number of clients: 16
  37. number of threads: 1
  38. duration: 3 s
  39. number of transactions actually processed: 3174
  40. tps = 1049.927110 (including connections establishing)
  41. tps = 1073.139131 (excluding connections establishing)

  43. [chenhj@hanode1 ~]$ pgbench -n -l -c 1 -T 3 -S db1
  44. transaction type: SELECT only
  45. scaling factor: 100
  46. query mode: simple
  47. number of clients: 1
  48. number of threads: 1
  49. duration: 3 s
  50. number of transactions actually processed: 1385
  51. tps = 461.481151 (including connections establishing)
  52. tps = 462.109984 (excluding connections establishing)
  53. [chenhj@hanode1 ~]$ pgbench -n -l -c 1 -T 3 -S db2
  54. transaction type: SELECT only
  55. scaling factor: 100
  56. query mode: simple
  57. number of clients: 1
  58. number of threads: 1
  59. duration: 3 s
  60. number of transactions actually processed: 3527
  61. tps = 1175.124543 (including connections establishing)
  62. tps = 1177.144768 (excluding connections establishing)

  64. [chenhj@hanode1 ~]$ pgbench -n -l -c 16 -T 3 -S db1
  65. transaction type: SELECT only
  66. scaling factor: 100
  67. query mode: simple
  68. number of clients: 16
  69. number of threads: 1
  70. duration: 3 s
  71. number of transactions actually processed: 6313
  72. tps = 2092.075168 (including connections establishing)
  73. tps = 2137.468368 (excluding connections establishing)
  74. [chenhj@hanode1 ~]$ pgbench -n -l -c 16 -T 3 -S db2
  75. transaction type: SELECT only
  76. scaling factor: 100
  77. query mode: simple
  78. number of clients: 16
  79. number of threads: 1
  80. duration: 3 s
  81. number of transactions actually processed: 14414
  82. tps = 4775.127727 (including connections establishing)
  83. tps = 4886.910106 (excluding connections establishing)

2种索引的大小差不多

  1. db1=# select pg_table_size(‘pgbench_accounts_pkey‘::regclass);
  2.  pg_table_size
  3. ---------------
  4.      224641024
  5. (1 row)

  7. db2=# select pg_table_size(‘pgbench_accounts_aid_idx‘::regclass);
  8.  pg_table_size
  9. ---------------
  10.      268451840
  11. (1 row)


郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。

标签： 算法 class style 使用 html 文件 数据 http si 算法 class style 使用 html 文件 数据 http si

相关文章

随机文章

您可能还喜欢

您可能还喜欢