postgresql和mysql查詢效率_比較PostgreSQL和MySQL上的查詢

祝你今天愉快！

在使用Debian 7.1的小型VM上的Windows Azure上,我安裝了MySQL 5.5.31和PostgreSQL 9.2.4.

插入和選擇查詢将通過pdo從php進行.

建立表：

MySQL的：

CREATE TABLE `test` (

`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

`fdate` datetime NOT NULL,

`ftext` varchar(1000) COLLATE utf8_unicode_ci DEFAULT '',

PRIMARY KEY (`id`),

KEY `ix_date` (`fdate`),

KEY `ix_text` (`ftext`(255))

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

PGSQL：

CREATE TABLE test

(

fdate timestamp without time zone,

ftext character varying(1000),

id bigserial NOT NULL,

CONSTRAINT test_pkey PRIMARY KEY (id)

)

WITH (

OIDS=FALSE

);

ALTER TABLE test

OWNER TO postgres;

CREATE INDEX ix_date

ON test

USING btree

(fdate);

CREATE INDEX ix_text

ON test

USING btree

(ftext COLLATE pg_catalog."default");

将插入内容放入表格中.

資料是這樣的：

152 2013-07-25 00:01:47 51e811712cfd6

100151 2013-07-25 00:28:25 51e825bfea275

101151 2013-07-25 00:29:26 51e825fcc5d94

153 2013-07-25 01:01:47 51e8117134c14

100152 2013-07-25 01:28:25 51e825bff1eb7

101152 2013-07-25 01:29:26 51e825fccd9e7

154 2013-07-25 02:01:47 51e811713d80d

100153 2013-07-25 02:28:25 51e825c0077c7

101153 2013-07-25 02:29:26 51e825fcd561a

155 2013-07-25 03:01:47 51e811716ffb2

100154 2013-07-25 03:28:25 51e825c013225

101154 2013-07-25 03:29:26 51e825fcdd243

156 2013-07-25 04:01:47 51e8117179af0

100155 2013-07-25 04:28:25 51e825c01cd74

101155 2013-07-25 04:29:26 51e825fce3f1c

在每個表中插入102 000行.

平均插入時間：

MySQL: 0.0328167504 сек.

PgSQL: 0.0183281872 сек.

- PgSQL is ~twice faster.

然後我選擇：

select * from test

where `fdate` > "2013-07-25" and `fdate` < "2013-08-21"

order by `fdate`

(在FOR循環(1000)中選擇,然後計算平均時間.)

MySQL: 0.0004650463 сек., 1944 rows

PgSQL: 0.0139540959 сек., 1944 rows

- PgSQL by 30! times more slowly.

為什麼？

PgSQL EXPLAIN(ANALYZE,BUFFERS)：

"Index Scan using ix_date on test (cost=0.00..36.86 rows=780 width=30) (actual time=0.018..4.672 rows=1944 loops=1)"

" Index Cond: ((fdate > '2013-07-25 00:00:00'::timestamp without time zone) AND (fdate < '2013-08-21 00:00:00'::timestamp without time zone))"

" Buffers: shared hit=1954"

"Total runtime: 7.594 ms"

MySQL EXPLAIN：

1 SIMPLE test range ix_date ix_date 8 1942 Using where

分析VERBOSE測試(PgSQL)：

INFO: analyzing "public.test"

INFO: "test": scanned 750 of 750 pages, containing 102000 live rows and 0 dead rows; 30000 rows in sample, 102000 estimated total rows

最佳答案 重複的查詢命中了MySQL

query cache,我認為這個功能在PostgreSQL中不存在.

在disabling the query cache之後重複測試,或者将SQL_NO_CACHE指令添加到MySQL的查詢中(SELECT SQL_NO_CACHE * FROM test …).