MariaDB 标头错误

Question

gabtzi

Asked: 2024-04-05 13:06:00 +0800 CST2024-04-05 13:06:00 +0800 CST 2024-04-05 13:06:00 +0800 CST

为什么这个查询运行得这么慢？

772

我的系统（OpenMage / Magento 1）正在运行一些如下所示的查询，尽管它没有使用任何大型表，但它们似乎需要异常长的执行时间。有时他们需要几分钟，有时他们会在一天后超时。

例如

# Time: 240405  7:37:19
# User@Host: db_example3[db_example3] @ localhost [127.0.0.1]
# Thread_id: 8345298  Schema: db_example3  QC_hit: No
# Query_time: 140.075668  Lock_time: 0.000176  Rows_sent: 1  Rows_examined: 15
# Rows_affected: 0  Bytes_sent: 148
# Tmp_tables: 1  Tmp_disk_tables: 0  Tmp_table_sizes: 253976
# Full_scan: No  Full_join: No  Tmp_table: Yes  Tmp_table_on_disk: No
# Filesort: Yes  Filesort_on_disk: No  Merge_passes: 0  Priority_queue: No
#
# explain: id   select_type     table   type    possible_keys   key     key_len ref     rows    r_rows  filtered        r_filtered      Extra
# explain: 1    SIMPLE  ram_idx ref     PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE,idx_attribute_store_value_entity   IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE     4       const   2       2.00    100.00  100.00  Using where; Using index; Using temporary; Using filesort
# explain: 1    SIMPLE  gdiktia_idx     ref     PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,idx_attribute_store_value_entity       IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID 8       db_example3.ram_idx.entity_id,const,const      1       1.00    100.00  100.00  Using index
# explain: 1    SIMPLE  primary_camera_idx      range   PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE,idx_attribute_store_value_entity   IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE     4       NULL    2       2.00    75.00   25.00   Using where; Using index; Using join buffer (flat, BNL join)
# explain: 1    SIMPLE  screen_resolution_idx   eq_ref  PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE,idx_attribute_store_value_entity   PRIMARY 12      db_example3.ram_idx.entity_id,const,const,const        1       1.00    100.00  100.00  Using index
# explain: 1    SIMPLE  memory_idx      eq_ref  PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE,idx_attribute_store_value_entity   PRIMARY 12      db_example3.ram_idx.entity_id,const,const,const        1       1.00    100.00  100.00  Using index
# explain: 1    SIMPLE  second_camera_idx       eq_ref  PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE,idx_attribute_store_value_entity   PRIMARY 12      db_example3.ram_idx.entity_id,const,const,const        1       1.00    100.00  100.00  Using index
# explain: 1    SIMPLE  nfcsosto_idx    eq_ref  PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE,idx_attribute_store_value_entity   PRIMARY 12      db_example3.ram_idx.entity_id,const,const,const        1       1.00    100.00  100.00  Using index
# explain: 1    SIMPLE  price_index     eq_ref  PRIMARY,IDX_CATALOG_PRODUCT_INDEX_PRICE_CUSTOMER_GROUP_ID,IDX_CATALOG_PRODUCT_INDEX_PRICE_WEBSITE_ID,IDX_CAT_PRD_IDX_PRICE_WS_ID_CSTR_GROUP_ID_MIN_PRICE        PRIMARY  8       db_example3.ram_idx.entity_id,const,const      1       1.00    100.00  100.00  Using index
# explain: 1    SIMPLE  core_count_idx  eq_ref  PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE,idx_attribute_store_value_entity   PRIMARY 12      db_example3.ram_idx.entity_id,const,const,const        1       1.00    100.00  100.00  Using index
# explain: 1    SIMPLE  megethossim_idx eq_ref  PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE,idx_attribute_store_value_entity   PRIMARY 12      db_example3.ram_idx.entity_id,const,const,const        1       1.00    100.00  100.00  Using index
# explain: 1    SIMPLE  cat_index       eq_ref  PRIMARY,IDX_CAT_CTGR_PRD_IDX_PRD_ID_STORE_ID_CTGR_ID_VISIBILITY,15D3C269665C74C2219037D534F4B0DC        PRIMARY 10      const,db_example3.ram_idx.entity_id,const       1       1.00    100.00  100.00  Using where
# explain: 1    SIMPLE  e       eq_ref  PRIMARY PRIMARY 4       db_example3.ram_idx.entity_id  1       1.00    100.00  100.00  Using index
#
SET timestamp=1712291839;
SELECT `gdiktia_idx`.`value`, COUNT(gdiktia_idx.entity_id) AS `count` FROM `catalog_product_entity` AS `e`
 INNER JOIN `catalog_category_product_index` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id=1 AND cat_index.visibility IN(2, 4) AND cat_index.category_id = '17'
 INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND price_index.customer_group_id = 0
 INNER JOIN `catalog_product_index_eav` AS `screen_resolution_idx` ON screen_resolution_idx.entity_id = e.entity_id AND screen_resolution_idx.attribute_id = 188 AND screen_resolution_idx.store_id = 1 AND screen_resolution_idx.value = '234'
 INNER JOIN `catalog_product_index_eav` AS `core_count_idx` ON core_count_idx.entity_id = e.entity_id AND core_count_idx.attribute_id = 193 AND core_count_idx.store_id = 1 AND core_count_idx.value = '41'
 INNER JOIN `catalog_product_index_eav` AS `ram_idx` ON ram_idx.entity_id = e.entity_id AND ram_idx.attribute_id = 196 AND ram_idx.store_id = 1 AND ram_idx.value = '54'
 INNER JOIN `catalog_product_index_eav` AS `memory_idx` ON memory_idx.entity_id = e.entity_id AND memory_idx.attribute_id = 197 AND memory_idx.store_id = 1 AND memory_idx.value = '62'
 INNER JOIN `catalog_product_index_eav` AS `primary_camera_idx` ON primary_camera_idx.entity_id = e.entity_id AND primary_camera_idx.attribute_id = 198 AND primary_camera_idx.store_id = 1 AND primary_camera_idx.value = '315'
 INNER JOIN `catalog_product_index_eav` AS `second_camera_idx` ON second_camera_idx.entity_id = e.entity_id AND second_camera_idx.attribute_id = 200 AND second_camera_idx.store_id = 1 AND second_camera_idx.value = '90'
 INNER JOIN `catalog_product_index_eav` AS `megethossim_idx` ON megethossim_idx.entity_id = e.entity_id AND megethossim_idx.attribute_id = 229 AND megethossim_idx.store_id = 1 AND megethossim_idx.value = '164'
 INNER JOIN `catalog_product_index_eav` AS `nfcsosto_idx` ON nfcsosto_idx.entity_id = e.entity_id AND nfcsosto_idx.attribute_id = 258 AND nfcsosto_idx.store_id = 1 AND nfcsosto_idx.value = '377'
 INNER JOIN `catalog_product_index_eav` AS `gdiktia_idx` ON gdiktia_idx.entity_id = e.entity_id AND gdiktia_idx.attribute_id = 232 AND gdiktia_idx.store_id = '1' GROUP BY `gdiktia_idx`.`value`;

或者

# Time: 240405  7:34:29
# User@Host: db_example3[db_example3] @ localhost [127.0.0.1]
# Thread_id: 8344334  Schema: db_example3  QC_hit: No
# Query_time: 74.418149  Lock_time: 0.000100  Rows_sent: 0  Rows_examined: 0
# Rows_affected: 0  Bytes_sent: 142
# Tmp_tables: 1  Tmp_disk_tables: 0  Tmp_table_sizes: 0
# Full_scan: No  Full_join: No  Tmp_table: Yes  Tmp_table_on_disk: No
# Filesort: Yes  Filesort_on_disk: No  Merge_passes: 0  Priority_queue: No
#
# explain: id   select_type     table   type    possible_keys   key     key_len ref     rows    r_rows  filtered        r_filtered      Extra
# explain: 1    SIMPLE  extra_specs_idx ref     PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,idx_attribute_store_value_entity       IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID      2       const   1       0.00    100.00  100.00  Using where; Using index; Using temporary; Using filesort
# explain: 1    SIMPLE  manufacturer2_idx       eq_ref  PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE,idx_attribute_store_value_entity   PRIMARY 12      db_example3.extra_specs_idx.entity_id,const,const,const        1       NULL    100.00  NULL    Using index
# explain: 1    SIMPLE  primary_camera_idx      eq_ref  PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE,idx_attribute_store_value_entity   PRIMARY 12      db_example3.extra_specs_idx.entity_id,const,const,const        1       NULL    100.00  NULL    Using index
# explain: 1    SIMPLE  second_camera_idx       eq_ref  PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE,idx_attribute_store_value_entity   PRIMARY 12      db_example3.extra_specs_idx.entity_id,const,const,const        1       NULL    100.00  NULL    Using index
# explain: 1    SIMPLE  ram_idx eq_ref  PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE,idx_attribute_store_value_entity   PRIMARY 12      db_example3.extra_specs_idx.entity_id,const,const,const        1       NULL    100.00  NULL    Using index
# explain: 1    SIMPLE  nfcsosto_idx    eq_ref  PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE,idx_attribute_store_value_entity   PRIMARY 12      db_example3.extra_specs_idx.entity_id,const,const,const        1       NULL    100.00  NULL    Using index
# explain: 1    SIMPLE  operating_system_idx    eq_ref  PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE,idx_attribute_store_value_entity   PRIMARY 12      db_example3.extra_specs_idx.entity_id,const,const,const        1       NULL    100.00  NULL    Using index
# explain: 1    SIMPLE  megethossim_idx eq_ref  PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE,idx_attribute_store_value_entity   PRIMARY 12      db_example3.extra_specs_idx.entity_id,const,const,const        1       NULL    100.00  NULL    Using index
# explain: 1    SIMPLE  price_index     eq_ref  PRIMARY,IDX_CATALOG_PRODUCT_INDEX_PRICE_CUSTOMER_GROUP_ID,IDX_CATALOG_PRODUCT_INDEX_PRICE_WEBSITE_ID,IDX_CAT_PRD_IDX_PRICE_WS_ID_CSTR_GROUP_ID_MIN_PRICE        PRIMARY  8       db_example3.extra_specs_idx.entity_id,const,const      1       NULL    100.00  NULL    Using where
# explain: 1    SIMPLE  cat_index       eq_ref  PRIMARY,IDX_CAT_CTGR_PRD_IDX_PRD_ID_STORE_ID_CTGR_ID_VISIBILITY,15D3C269665C74C2219037D534F4B0DC        PRIMARY 10      const,db_example3.extra_specs_idx.entity_id,const       1       NULL    100.00  NULL    Using where
# explain: 1    SIMPLE  e       eq_ref  PRIMARY PRIMARY 4       db_example3.extra_specs_idx.entity_id  1       NULL    100.00  NULL    Using index
# explain: 1    SIMPLE  screen_type_idx range   PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE,idx_attribute_store_value_entity   IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE     4       NULL    2       NULL    75.00   NULL    Using where; Using index; Using join buffer (flat, BNL join)
#
SET timestamp=1712291669;
SELECT `extra_specs_idx`.`value`, COUNT(extra_specs_idx.entity_id) AS `count` FROM `catalog_product_entity` AS `e`
 INNER JOIN `catalog_category_product_index` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id=1 AND cat_index.visibility IN(2, 4) AND cat_index.category_id = '3'
 INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND price_index.customer_group_id = 0
 INNER JOIN `catalog_product_index_eav` AS `manufacturer2_idx` ON manufacturer2_idx.entity_id = e.entity_id AND manufacturer2_idx.attribute_id = 186 AND manufacturer2_idx.store_id = 1 AND manufacturer2_idx.value = '6'
 INNER JOIN `catalog_product_index_eav` AS `screen_type_idx` ON screen_type_idx.entity_id = e.entity_id AND screen_type_idx.attribute_id = 189 AND screen_type_idx.store_id = 1 AND screen_type_idx.value = '37'
 INNER JOIN `catalog_product_index_eav` AS `operating_system_idx` ON operating_system_idx.entity_id = e.entity_id AND operating_system_idx.attribute_id = 195 AND operating_system_idx.store_id = 1 AND operating_system_idx.value = '48'
 INNER JOIN `catalog_product_index_eav` AS `primary_camera_idx` ON primary_camera_idx.entity_id = e.entity_id AND primary_camera_idx.attribute_id = 198 AND primary_camera_idx.store_id = 1 AND primary_camera_idx.value = '309'
 INNER JOIN `catalog_product_index_eav` AS `second_camera_idx` ON second_camera_idx.entity_id = e.entity_id AND second_camera_idx.attribute_id = 200 AND second_camera_idx.store_id = 1 AND second_camera_idx.value = '87'
 INNER JOIN `catalog_product_index_eav` AS `megethossim_idx` ON megethossim_idx.entity_id = e.entity_id AND megethossim_idx.attribute_id = 229 AND megethossim_idx.store_id = 1 AND megethossim_idx.value = '164'
 INNER JOIN `catalog_product_index_eav` AS `ram_idx` ON ram_idx.entity_id = e.entity_id AND ram_idx.attribute_id = 196 AND ram_idx.store_id = 1 AND ram_idx.value = '52'
 INNER JOIN `catalog_product_index_eav` AS `nfcsosto_idx` ON nfcsosto_idx.entity_id = e.entity_id AND nfcsosto_idx.attribute_id = 258 AND nfcsosto_idx.store_id = 1 AND nfcsosto_idx.value = '377'
 INNER JOIN `catalog_product_index_eav` AS `extra_specs_idx` ON extra_specs_idx.entity_id = e.entity_id AND extra_specs_idx.attribute_id = 213 AND extra_specs_idx.store_id = '1' WHERE ((price_index.min_price -(price_index.min_price/(1+(CASE price_index.tax_class_id WHEN 2 THEN       0.2400  ELSE 0 END))*CASE price_index.tax_class_id WHEN 2 THEN       0.2400  ELSE 0 END)+((price_index.min_price-(price_index.min_price/(1+(CASE price_index.tax_class_id WHEN 2 THEN       0.2400  ELSE 0 END))*CASE price_index.tax_class_id WHEN 2 THEN       0.2400  ELSE 0 END))*CASE price_index.tax_class_id WHEN 2 THEN       0.2400  ELSE 0 END)) >= 99.995000) AND ((price_index.min_price -(price_index.min_price/(1+(CASE price_index.tax_class_id WHEN 2 THEN       0.2400  ELSE 0 END))*CASE price_index.tax_class_id WHEN 2 THEN       0.2400  ELSE 0 END)+((price_index.min_price-(price_index.min_price/(1+(CASE price_index.tax_class_id WHEN 2 THEN       0.2400  ELSE 0 END))*CASE price_index.tax_class_id WHEN 2 THEN       0.2400  ELSE 0 END))*CASE price_index.tax_class_id WHEN 2 THEN       0.2400  ELSE 0 END)) < 199.995000) GROUP BY `extra_specs_idx`.`value`;

据我所见，表正在使用索引，并且通常没有任何内容写入磁盘上，一切都发生在内存中，因此我不确定如何优化并使它们运行得更快。

我的 mariadb 版本是

Server version: 10.6.17-MariaDB-1:10.6.17+maria~ubu2004-log mariadb.org binary distribution

这是表定义

create table db_example3.catalog_category_product_index
(
    category_id int unsigned      default 0 not null comment 'Category ID',
    product_id  int unsigned      default 0 not null comment 'Product ID',
    position    int                         null comment 'Position',
    is_parent   smallint unsigned default 0 not null comment 'Is Parent',
    store_id    smallint unsigned default 0 not null comment 'Store ID',
    visibility  smallint unsigned           not null comment 'Visibility',
    primary key (category_id, product_id, store_id),
    constraint FK_CATALOG_CATEGORY_PRODUCT_INDEX_STORE_ID_CORE_STORE_STORE_ID
        foreign key (store_id) references db_example3.core_store (store_id)
            on update cascade on delete cascade,
    constraint FK_CAT_CTGR_PRD_IDX_CTGR_ID_CAT_CTGR_ENTT_ENTT_ID
        foreign key (category_id) references db_example3.catalog_category_entity (entity_id)
            on update cascade on delete cascade,
    constraint FK_CAT_CTGR_PRD_IDX_PRD_ID_CAT_PRD_ENTT_ENTT_ID
        foreign key (product_id) references db_example3.catalog_product_entity (entity_id)
            on update cascade on delete cascade
)
    comment 'Catalog Category Product Index' row_format = COMPRESSED;

create index `15D3C269665C74C2219037D534F4B0DC`
    on db_example3.catalog_category_product_index (store_id, category_id, visibility, is_parent, position);

create index IDX_CAT_CTGR_PRD_IDX_PRD_ID_STORE_ID_CTGR_ID_VISIBILITY
    on db_example3.catalog_category_product_index (product_id, store_id, category_id, visibility);

create table db_example3.catalog_product_index_eav
(
    entity_id    int unsigned      not null comment 'Entity ID',
    attribute_id smallint unsigned not null comment 'Attribute ID',
    store_id     smallint unsigned not null comment 'Store ID',
    value        int unsigned      not null comment 'Value',
    primary key (entity_id, attribute_id, store_id, value),
    constraint FK_CATALOG_PRODUCT_INDEX_EAV_STORE_ID_CORE_STORE_STORE_ID
        foreign key (store_id) references db_example3.core_store (store_id)
            on update cascade on delete cascade,
    constraint FK_CAT_PRD_IDX_EAV_ATTR_ID_EAV_ATTR_ATTR_ID
        foreign key (attribute_id) references db_example3.eav_attribute (attribute_id)
            on update cascade on delete cascade,
    constraint FK_CAT_PRD_IDX_EAV_ENTT_ID_CAT_PRD_ENTT_ENTT_ID
        foreign key (entity_id) references db_example3.catalog_product_entity (entity_id)
            on update cascade on delete cascade
)
    comment 'Catalog Product EAV Index Table' row_format = COMPRESSED;

create index IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID
    on db_example3.catalog_product_index_eav (attribute_id);

create index IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID
    on db_example3.catalog_product_index_eav (entity_id);

create index IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID
    on db_example3.catalog_product_index_eav (store_id);

create index IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE
    on db_example3.catalog_product_index_eav (value);

create index idx_attribute_store_value_entity
    on db_example3.catalog_product_index_eav (attribute_id, store_id, value, entity_id);

create table db_example3.catalog_product_index_price
(
    entity_id         int unsigned                not null comment 'Entity ID',
    customer_group_id smallint unsigned           not null comment 'Customer Group ID',
    website_id        smallint unsigned           not null comment 'Website ID',
    tax_class_id      smallint unsigned default 0 null comment 'Tax Class ID',
    price             decimal(12, 4)              null comment 'Price',
    final_price       decimal(12, 4)              null comment 'Final Price',
    min_price         decimal(12, 4)              null comment 'Min Price',
    max_price         decimal(12, 4)              null comment 'Max Price',
    tier_price        decimal(12, 4)              null comment 'Tier Price',
    group_price       decimal(12, 4)              null comment 'Group price',
    primary key (entity_id, customer_group_id, website_id),
    constraint FK_CAT_PRD_IDX_PRICE_CSTR_GROUP_ID_CSTR_GROUP_CSTR_GROUP_ID
        foreign key (customer_group_id) references db_example3.customer_group (customer_group_id)
            on update cascade on delete cascade,
    constraint FK_CAT_PRD_IDX_PRICE_ENTT_ID_CAT_PRD_ENTT_ENTT_ID
        foreign key (entity_id) references db_example3.catalog_product_entity (entity_id)
            on update cascade on delete cascade,
    constraint FK_CAT_PRD_IDX_PRICE_WS_ID_CORE_WS_WS_ID
        foreign key (website_id) references db_example3.core_website (website_id)
            on update cascade on delete cascade
)
    comment 'Catalog Product Price Index Table' row_format = COMPRESSED;

create index IDX_CATALOG_PRODUCT_INDEX_PRICE_CUSTOMER_GROUP_ID
    on db_example3.catalog_product_index_price (customer_group_id);

create index IDX_CATALOG_PRODUCT_INDEX_PRICE_MIN_PRICE
    on db_example3.catalog_product_index_price (min_price);

create index IDX_CATALOG_PRODUCT_INDEX_PRICE_WEBSITE_ID
    on db_example3.catalog_product_index_price (website_id);

create index IDX_CAT_PRD_IDX_PRICE_WS_ID_CSTR_GROUP_ID_MIN_PRICE
    on db_example3.catalog_product_index_price (website_id, customer_group_id, min_price);

每个表的行数

select count(*) from catalog_product_index_eav; #418
select count(*) from catalog_product_index_price; #84
select count(*) from catalog_category_product_index; # 314

该服务器配备 AMD Ryzen 9 3900 12 核处理器、128GB RAM 和 Nvme 磁盘。

任何帮助表示赞赏

2 个回答

Voted

danblack · Answer 1 · 2024-04-06T19:10:19+08:00

编辑：

对于正确索引的表，设置optimizer_search_depth = 1可以减少 8-9+ 表连接时的开销。

不清楚为什么catalog_product_entity在查询中，因为entity_id另一个表中的可以用作连接条件。除非我遗漏了一些东西（很可能，它有点难以阅读），否则它不会在查询中使用。

catalog_category_product_index应该product_id（为什么不像其他表那样调用entity_id？）作为主键的第一个元素，以便其连接更简单。这应该在查询中使用该表的 PK 显示。如果该表位于处理列表的较早位置，这可能会产生影响。

从ANALYZE FORMAT=JSON 来看query，连接缓冲区的多次迭代可能会导致执行效率低下。

使用 BNL 作为示例，包括 @Akina 的变体 ( ref )。提高join_buffer并join_buffer_space_limit（作为会话变量）直到循环减少，看看是否可以提高性能。

您还可以尝试join_cache_level来查看较高的级别是否会产生更好的执行查询计划。

参考：https://mariadb.com/kb/en/block-based-join-algorithms/

Rick James · Answer 2 · 2024-04-22T07:58:38+08:00

想法 1 我怀疑它们INNER JOINs是为了提供在此特定查询中未使用的额外列，而不是用于过滤。因此，将它们更改为LEFT JOIN，看看是否EXPLAIN会变得更短，同时仍然给出正确的答案。

想法2

EAV 是一种特别丑陋的模式模式，需要对其进行优化。

让我们把查询彻底翻转过来；它可能会有所帮助，也许是显着的帮助。（是的，框架 OpenMage 会妨碍。）

在通过消除大部分内容之前，您所拥有的是大量的 JOINing GROUP BY。由内而外的方法是从“派生表”开始查找所需行的 id：

SELECT ...
    FROM ( SELECT maybe as little as one id
               FROM as few tables as possible
               GROUP BY ...   -- see note
         ) AS step1
    JOIN back to the original table(s) if you need extra columns
    JOIN to each other table ON ... = step1....;

GROUP BY注意：通常，执行此由内而外的操作可以消除对;的需要。这会带来额外的提升。

SELECT step1.value, COUNT(*) as `count`
    FROM ( SELECT gdiktia_idx.id, gdiktia_idx.value
             FROM  catalog_product_index_eav` AS `gdiktia_idx`
             WHERE  gdiktia_idx.attribute_id = 232
               AND  gdiktia_idx.store_id = '1'
             GROUP BY  `gdiktia_idx`.`value` -- see note
         ) AS step1
    JOIN ...
    JOIN ...
    JOIN ...
    JOIN ... ;

为什么这个查询运行得这么慢？

连接到 PostgreSQL 服务器：致命：主机没有 pg_hba.conf 条目

如何让sqlplus的输出出现在一行中？

选择具有最大日期或最晚日期的日期

如何列出 PostgreSQL 中的所有模式？

列出指定表的所有列

如何在不修改我自己的 tnsnames.ora 的情况下使用 sqlplus 连接到位于另一台主机上的 Oracle 数据库

你如何mysqldump特定的表？

使用 psql 列出数据库权限

如何从 PostgreSQL 中的选择查询中将值插入表中？

如何使用 psql 列出所有数据库和表？

为什么这个查询运行得这么慢？

2 个回答

相关问题