建立测试表结构
做网站、成都做网站的开发，更需要了解用户，从用户角度来建设网站，获得较好的用户体验。成都创新互联公司多年互联网经验，见的多，沟通容易、能帮助客户提出的运营建议。作为成都一家网络公司，打造的就是网站建设产品直销的概念。选择成都创新互联公司，不只是建站，我们把建站作为产品，不断的更新、完善，让每位来访用户感受到浩方产品的价值服务。
CREATE TABLE t_test
(
id integer,
name text COLLATE pg_catalog."default",
address character varying(500) COLLATE pg_catalog."default"
);
插入测试数据
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语句，与条件次序无关
一般3毫秒多出结果
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);
以下语句会使用索引
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';
下面语句不会使用索引
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'