php页面查询需要15分钟，phpmyadmin上同样的查询需要3秒

Question

php 页面查询需要 15 分钟，phpmyadmin 上的相同查询需要 3 秒，没有意义这同一台服务器。涉及的表在20k行以下，但最终结果只有144行。这是查询:

SELECT a.cod_empresa, a.dat_alt_sit, a.cod_cliente, a.num_pedido, 
        b.qtd_pecas_solic, b.qtd_pecas_cancel, b.qtd_pecas_atend, b.cod_item, b.num_pedido, 
        c.nom_cliente, 
        d.qtd_liberada, d.qtd_reservada 
FROM lx_pedidos a, 
     lx_ped_itens b, 
     lx_clientes c, 
     lx_estoque d 
WHERE 
    a.num_pedido = b.num_pedido 
AND a.cod_empresa = b.cod_empresa 
AND c.cod_cliente = a.cod_cliente 
AND b.cod_item = d.cod_item 
AND b.cod_empresa = d.cod_empresa 
AND a.ies_sit_pedido = 'N' 
AND a.cod_nat_oper <> 9001 
AND d.qtd_liberada > 0 
AND (b.qtd_pecas_solic - b.qtd_pecas_cancel - b.qtd_pecas_atend) > 0 
AND (a.cod_empresa = 2 or a.cod_empresa = 3 or a.cod_empresa = 5) 
AND a.dat_alt_sit > '2018-07-01' 
AND a.dat_alt_sit < '2018-07-31'                                      
ORDER BY b.cod_item

php代码:

    echo 'point 2 '.hojeDataHoraHuman();
    $result = mysqli_query($conn, "QUERY^^^");

    echo 'point 3 '.hojeDataHoraHuman();


    $cont1 = 0;
    $totGeral = 0;
    while($row = mysqli_fetch_assoc($result)){
        $cont1++;
(...)

创建表

CREATE TABLE lx_pedidos ( 
    id                  int(11) AUTO_INCREMENT NOT NULL,
    cod_empresa         int(2) NULL,
    num_pedido          int(6) NULL,
    cod_cliente         bigint(15) NULL,
    pct_comissao        varchar(5) NULL,
    cod_nat_oper        int(4) NULL,
    cod_transpor        varchar(15) NULL,
    ies_finalidade      int(1) NULL,
    ies_frete           int(1) NULL,
    ies_preco           varchar(1) NULL,
    cod_cnd_pgto        int(3) NULL,
    ies_embal_padrao    int(1) NULL,
    ies_tip_entrega     int(1) NULL,
    ies_aceite          varchar(1) NULL,
    ies_sit_pedido      varchar(1) NULL,
    dat_pedido          date NULL,
    num_pedido_cli      varchar(25) NULL,
    num_list_preco      int(4) NULL,
    cod_repres          int(4) NULL,
    dat_alt_sit         date NULL,
    dat_cancel          date NULL,
    cod_motivo_can      varchar(4) NULL,
    dat_ult_fatur       date NULL,
    cod_moeda           int(1) NULL,
    ies_comissao        varchar(1) NULL,
    cod_tip_carteira    int(2) NULL,
    data_ult_sync       datetime NOT NULL,
    id_sync             int(11) NOT NULL,
    info                varchar(255) NOT NULL,
    PRIMARY KEY(id)
)

-

CREATE TABLE lx_clientes ( 
    id                  int(11) AUTO_INCREMENT NOT NULL,
    cod_cliente         varchar(15) NULL,
    cod_class           varchar(1) NULL,
    nom_cliente         varchar(36) NULL,
    dat_cadastro        varchar(10) NULL,
    end_cliente         varchar(36) NULL,
    den_bairro          varchar(23) NULL,
    cod_cidade          varchar(9) NULL,
    cod_cep             varchar(9) NULL,
    num_caixa_postal    varchar(15) NULL,
    num_telefone        varchar(15) NULL,
    num_suframa         varchar(9) NULL,
    cod_tip_cli         varchar(2) NULL,
    den_marca           varchar(15) NULL,
    nom_reduzido        varchar(29) NULL,
    den_frete_posto     varchar(19) NULL,
    num_cgc_cpf         varchar(19) NULL,
    ins_estadual        varchar(16) NULL,
    cod_portador        varchar(3) NULL,
    ies_tip_portador    varchar(15) NULL,
    cod_consig          varchar(15) NULL,
    ies_cli_forn        varchar(1) NULL,
    ies_zona_franca     varchar(1) NULL,
    ies_situacao        varchar(1) NULL,
    cod_rota            int(5) NULL,
    cod_praca           varchar(10) NULL,
    dat_atualiz         varchar(20) NULL,
    nom_contato         varchar(20) NULL,
    dat_fundacao        varchar(9) NULL,
    cod_local           int(1) NULL,
    PRIMARY KEY(id)
)

-

CREATE TABLE lx_estoque ( 
id              int(11) AUTO_INCREMENT NOT NULL,
cod_empresa     int(2) NULL,
cod_item        varchar(15) NULL,
qtd_liberada    int(9) NULL,
qtd_reservada   int(3) NULL,
dat_ult_entrada date NULL,
dat_ult_saida   date NULL,
PRIMARY KEY(id)
)

-

CREATE TABLE lx_ped_itens ( 
    id                  int(11) AUTO_INCREMENT NOT NULL,
    cod_empresa         int(2) NULL,
    num_pedido          int(6) NULL,
    num_sequencia       int(2) NULL,
    cod_item            varchar(30) NULL,
    pct_desc_adic       decimal(4,2) NULL,
    pre_unit            decimal(12,3) NULL,
    qtd_pecas_solic     int(4) NULL,
    qtd_pecas_atend     int(3) NULL,
    qtd_pecas_cancel    int(4) NULL,
    qtd_pecas_reserv    int(1) NULL,
    prz_entrega         date NULL,
    qtd_pecas_romaneio  int(1) NULL,
    pct_desc_bruto      int(2) NULL,
    data_ult_sync       datetime NOT NULL,
    id_sync             int(2) NOT NULL,
    info                varchar(255) NOT NULL,
    PRIMARY KEY(id)
)

phpmyadmin 中的结果:results

mysqli 语句:php code query

添加:我现在用 join 做了一个查询，它在 phpmyadmin 和我的 php 页面中一直运行，但是这个查询在 informix DB 中有效，我有相同的表:join query (为什么？？)

15分钟的时间在点2和点3之间。拜托，任何建议都是有用的

Answer 1

向您的模式添加一些索引:

ALTER TABLE lx_pedidos ADD INDEX dat_alt_sit_idx (dat_alt_sit, cod_empresa, cod_nat_oper);
ALTER TABLE lx_pedidos ADD INDEX num_pedido_idx (num_pedido);
ALTER TABLE lx_pedidos ADD INDEX cod_cliente_idx (cod_cliente);
ALTER TABLE lx_clientes ADD INDEX cod_cliente_idx (cod_cliente);
ALTER TABLE lx_ped_itens ADD INDEX num_pedido_idx (num_pedido);
ALTER TABLE lx_ped_itens ADD INDEX cod_item_idx (cod_item, cod_empresa);
ALTER TABLE lx_estoque ADD INDEX cod_item_idx (cod_item, cod_empresa);

并更改您的查询以使用JOIN:

SELECT a.cod_empresa, a.dat_alt_sit, a.cod_cliente, a.num_pedido, 
        b.qtd_pecas_solic, b.qtd_pecas_cancel, b.qtd_pecas_atend, b.cod_item, b.num_pedido, 
        c.nom_cliente, 
        d.qtd_liberada, d.qtd_reservada 
FROM lx_pedidos a
JOIN lx_ped_itens b
ON a.num_pedido = b.num_pedido
    AND a.cod_empresa = b.cod_empresa
    AND (b.qtd_pecas_solic - b.qtd_pecas_cancel - b.qtd_pecas_atend) > 0
JOIN lx_clientes c
ON c.cod_cliente = a.cod_cliente
JOIN lx_estoque d
ON b.cod_item = d.cod_item 
    AND b.cod_empresa = d.cod_empresa
    AND d.qtd_liberada > 0
WHERE a.ies_sit_pedido = 'N' 
AND a.cod_nat_oper <> 9001 
AND (a.cod_empresa = 2 or a.cod_empresa = 3 or a.cod_empresa = 5) 
AND a.dat_alt_sit > '2018-07-01' 
AND a.dat_alt_sit < '2018-07-31'  

应用此数据库更改后:查询耗时 0.2310 秒。

注意 你有一个大问题。表 lx_pedidos a 的列 cod_cliente bigint(15) NULL， 但表 lx_clientes c 的列 cod_cliente varchar(15) NULL， 这就是为什么 JOIN lx_clientes c 非常慢的原因。如果您将 lx_clientes 切换为使用相同类型的 bigint(15)

，那将会更快

ALTER TABLE lx_clientes MODIFY COLUMN cod_cliente bigint(15);

此查询在您的数据集上失败。检查您的记录 select * from lx_clientes where id = 9290 。它有 - 符号。当您更改该值时 - ALTER TABLE 应该可以工作。

修改后:查询耗时 0.0041 秒 ;-)