云数据库 GaussDB-案例：建立合适的索引:现象描述

时间：2025-01-26 10:39:26

云数据库 GaussDB 实际调优案例

现象描述

查询与销售部所有员工的信息：

  
       --建表CREATE TABLE staffs (staff_id NUMBER(6) NOT NULL, first_name VARCHAR2(20), last_name VARCHAR2(25), employment_id VARCHAR2(10), section_id NUMBER(4), state_name VARCHAR2(10), city VARCHAR2(10));CREATE TABLE sections(section_id NUMBER(4), place_id NUMBER(4), section_name VARCHAR2(20));CREATE TABLE states(state_id NUMBER(4));CREATE TABLE places(place_id NUMBER(4), state_id  NUMBER(4));--优化前查询EXPLAIN SELECT staff_id,first_name,last_name,employment_id,state_name,city FROM staffs,sections,states,places WHERE sections.section_name='Sales' AND staffs.section_id = sections.section_id AND sections.place_id = places.place_id AND places.state_id = states.state_id ORDER BY staff_id;--创建索引CREATE INDEX loc_id_pk ON places(place_id);CREATE INDEX state_c_id_pk ON states(state_id);--优化后查询EXPLAIN SELECT staff_id,first_name,last_name,employment_id,state_name,city FROM staffs,sections,states,places WHERE sections.section_name='Sales' AND staffs.section_id = sections.section_id AND sections.place_id = places.place_id AND places.state_id = states.state_id ORDER BY staff_id;