PasseRR

xie__hai@sina.com

Postgresql联合主键left join查询

03 Jul 2017 » db

问题及环境

1.有两张数据库表exception_invoice_status_log和invoice_exception_status,其中exception_invoice_status_log数据量10w左右,invoice_exception_status数据量可以忽略
2.两张表数据库结构如下

CREATE TABLE IF NOT EXISTS exception_invoice_status_log(
  invoice_status_log_id BIGINT,
  exception_status_id BIGINT,
  CONSTRAINT pk_eisl_invoice_status_id_exception_status_id PRIMARY KEY(invoice_status_log_id, exception_status_id),
  CONSTRAINT fk_eisl_exception_status_id FOREIGN KEY(exception_status_id) REFERENCES invoice_exception_status(id)
);

CREATE TABLE IF NOT EXISTS invoice_exception_status(
  id INT NOT NULL,
  name VARCHAR(20) NOT NULL,
  CONSTRAINT pk_invoice_exception_status PRIMARY KEY (id)
);

3.现有数据库查询如下,查询效率非常psql低下

SELECT ies.name
  FROM exception_invoice_status_log eisl LEFT JOIN invoice_exception_status ies
    ON (eisl.invoice_status_log_id = 1000 AND eisl.exception_status_id = ies.id)
ORDER BY eisl.exception_status_id;

原因分析

1.先分析查询语句
EXPLAIN_LEFT_JOIN
发现LEFT JOIN的条件并没有走索引 而是过滤条件
2.将LEFT JOIN修改为JOIN分析

SELECT ies.name
  FROM exception_invoice_status_log eisl, invoice_exception_status ies
 WHERE eisl.invoice_status_log_id = 1000 
   AND eisl.exception_status_id = ies.id
ORDER BY eisl.exception_status_id;

EXPLAIN_JOIN
发现使用JOIN后查询条件走的是主键索引

结论

在postgresql中,若非联合主键,使用LEFT JOIN且条件为主键关联时,会使用主键索引。
若是联合主键,LEFT JOIN用主键关联不会走主键索引,使用JOIN会。