您好,登錄后才能下訂單哦!
建立測(cè)試表結(jié)構(gòu)
CREATE TABLE t_test
(
id integer,
name text COLLATE pg_catalog."default",
address character varying(500) COLLATE pg_catalog."default"
);
插入測(cè)試數(shù)據(jù)
insert into t_test SELECT generate_series(1,10000000) as key, 'name'||(random()*(10^3))::integer, 'ChangAn Street NO'||(random()*(10^3))::integer;
建立3列索引
create index idx_t_test_id_name_address on t_test(id,name,address);
1.以下查詢語句可以使用索引且較快
索引第一列在where語句,與條件次序無關(guān)
一般3毫秒多出結(jié)果
explain analyze select * from t_test where id < 2000 and name like 'name%' and address like 'ChangAn%';
explain analyze select * from t_test where address like 'ChangAn%' and name like 'name%' and id < 2000 ;
explain analyze select * from t_test where name like 'name%' and id < 2000 and address like 'ChangAn%' ;
explain analyze select * from t_test where id < 2000
explain analyze select * from t_test where name like 'name%' and id < 2000
explain analyze select * from t_test where address like 'ChangAn%' and id < 2000 ;
explain analyze select * from t_test where address like 'ChangAn%' and name like 'name%' and id < 2000 ;
2.以下可以使用索引,但是查詢速度較慢
索引第一列在order by
explain analyze select * from t_test where address like 'ChangAn%' and name like 'name%' order by id;
17S
explain analyze select * from t_test where address like 'ChangAn%' order by id;
8s
explain analyze select * from t_test where name like 'name%' order by id;
9s
以下語句無法使用索引,索引第一列不在 where 或者 order by
explain analyze select * from t_test where address like 'ChangAn%' and name like 'name%';
explain analyze select * from t_test where address like 'ChangAn%';
explain analyze select * from t_test where name like 'name%';
建立雙列索引
create index idx_t_test_name_address on t_test(name,address);
以下語句會(huì)使用索引
explain analyze select * from t_test where name = 'name580';
explain analyze select * from t_test where address like 'ChangAn%' and name like 'name580';
explain analyze select * from t_test where address like 'ChangAn%' and name = 'name580';
下面語句不會(huì)使用索引
explain analyze select * from t_test where name like 'name%'
explain analyze select * from t_test where address like 'ChangAn%'
explain analyze select * from t_test where address = 'ChangAn Street NO416'
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場,如果涉及侵權(quán)請(qǐng)聯(lián)系站長郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。