gabtzi's questions

我的系统（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 磁盘。

任何帮助表示赞赏

我有一列带有这种模式的字符串<email> - <id>。电子邮件始终是第一个字符串。

我想只提取电子邮件地址，但这里的问题是电子邮件地址也可以包含连字符，所以我不能确定分隔符只会出现一次。

所以基本上我想匹配 .* 直到最后一个连字符并将其提取为电子邮件。

嗯，这不完全是关于管理，它是关于编写一个查询来提取数据，所以它在数据挖掘领域，但是这个论坛完全与数据库相关，所以我认为它比 stackoverflow 更合适。

我只尝试过，SUBSTRING_INDEX()但最终得到了不好的结果。

这是一个生产系统，所以我不能真正干预设计，因此需要提取信息。

我有下表

CREATE TABLE `mview_state` (
  `state_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'View State ID',
  `view_id` varchar(255) DEFAULT NULL COMMENT 'View ID',
  `mode` varchar(16) DEFAULT 'disabled' COMMENT 'View Mode',
  `status` varchar(16) DEFAULT 'idle' COMMENT 'View Status',
  `updated` datetime DEFAULT NULL COMMENT 'View updated time',
  `version_id` int(10) unsigned DEFAULT NULL COMMENT 'View Version ID',
  PRIMARY KEY (`state_id`),
  KEY `MVIEW_STATE_VIEW_ID` (`view_id`),
  KEY `MVIEW_STATE_MODE` (`mode`)
) ENGINE=InnoDB AUTO_INCREMENT=50 DEFAULT CHARSET=utf8 COMMENT='View State'

有以下数据

https://dbfiddle.uk/?rdbms=mariadb_10.5&fiddle=036fb3a69c75a92769a9262ec4af292e

我想添加一个约束，以便当view_id列值等于时catalogrule_rule，mode列必须等于enabled

我正在阅读，这看起来需要一个检查约束，但我不确定如何创建它，以便所有其他值都有效，但这个特定条件将始终得到遵守。

因此，只有当值为view_idis时才catalogrule_rule必须将 mode 设置为enabled

我有两个表（在两个数据库中）：

create table catalog_product_entity_text
(
    value_id int auto_increment comment 'Value ID'
        primary key,
    attribute_id smallint unsigned default 0 not null comment 'Attribute ID',
    store_id smallint unsigned default 0 not null comment 'Store ID',
    entity_id int unsigned default 0 not null comment 'Entity ID',
    value longtext null comment 'Value',
    constraint CATALOG_PRODUCT_ENTITY_TEXT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID
        unique (entity_id, attribute_id, store_id),
    constraint CATALOG_PRODUCT_ENTITY_TEXT_STORE_ID_STORE_STORE_ID
        foreign key (store_id) references store (store_id)
            on delete cascade,
    constraint CAT_PRD_ENTT_TEXT_ATTR_ID_EAV_ATTR_ATTR_ID
        foreign key (attribute_id) references eav_attribute (attribute_id)
            on delete cascade,
    constraint CAT_PRD_ENTT_TEXT_ENTT_ID_CAT_PRD_ENTT_ENTT_ID
        foreign key (entity_id) references catalog_product_entity (entity_id)
            on delete cascade
)

create table catalog_product_entity_varchar
(
    value_id int auto_increment comment 'Value ID'
        primary key,
    attribute_id smallint unsigned default 0 not null comment 'Attribute ID',
    store_id smallint unsigned default 0 not null comment 'Store ID',
    entity_id int unsigned default 0 not null comment 'Entity ID',
    value varchar(255) null comment 'Value',
    constraint CATALOG_PRODUCT_ENTITY_VARCHAR_ENTITY_ID_ATTRIBUTE_ID_STORE_ID
        unique (entity_id, attribute_id, store_id),
    constraint CATALOG_PRODUCT_ENTITY_VARCHAR_STORE_ID_STORE_STORE_ID
        foreign key (store_id) references store (store_id)
            on delete cascade,
    constraint CAT_PRD_ENTT_VCHR_ATTR_ID_EAV_ATTR_ATTR_ID
        foreign key (attribute_id) references eav_attribute (attribute_id)
            on delete cascade,
    constraint CAT_PRD_ENTT_VCHR_ENTT_ID_CAT_PRD_ENTT_ENTT_ID
        foreign key (entity_id) references catalog_product_entity (entity_id)
            on delete cascade
)

我的_text表包含一些我想传输到_varchar表中的数据，但与主键没有关联。主键只是自动递增。

从逻辑上讲，这些表是由集合连接的(attribute_id, store_id, entity_id)。

我想执行两个操作：

1. 从_text表中选择特定属性 ID 的所有值，例如

   select cpet.attribute_id, cpet.store_id, cpet.entity_id, cpet.value
   from catalog_product_entity_text cpet
   where cpet.attribute_id in
         (<myids>)
     and cpet.value is not null;
   

   现在对于上述结果中的每一行，我想：

2. 如果表中有对应(attribute_id, store_id, entity_id)的_varchar，则更新该行。

3. 如果表中没有对应项(attribute_id, store_id, entity_id)，请_varchar使用源行中的数据添加一个新行。

如果我有一个共同的主键，我会使用REPLACE INTO，但在这种情况下，我认为它不合适。

如何构建这样的查询？它不需要是单个查询。

我有下表

create table catalog_product_entity_varchar
(
    value_id     int auto_increment comment 'Value ID'
        primary key,
    attribute_id smallint unsigned default 0 not null comment 'Attribute ID',
    store_id     smallint unsigned default 0 not null comment 'Store ID',
    entity_id    int unsigned      default 0 not null comment 'Entity ID',
    value        varchar(255)                null comment 'Value',  
)

我想找到相同的所有 entity_idsattribute_id = 86相同store_id的位置value。

例如对于列attribute_id, store_id, entity_id,value

86, 1, 1, mypath

与

86, 1, 2, mypath

但不与

86, 2, 1, mypath

结果将是entity_id, store_id,value

任何帮助表示赞赏。

我创建了一个表，其中存储了一些具有这种结构的多语言商店数据

create table store_locator_store_lang
(
    id_store int(11) unsigned not null,
    lang_id int not null,
    partner varchar(100) null,
    address varchar(255) null,
    city varchar(100) null,
    occupation varchar(100) null,
    primary key (id_store, id_lang)
);

id_lang can be 1 or 2

在那里我有一些不完整的数据，这意味着所有行都包含空值，lang_id = 1但lang_id = 2许多行包含空值。

我想用来自行的lang_id = 2数据填充空行lang_id = 1。

商店实体由 标识，id_store因此id_store对于同一商店的两种语言来说是通用的。

即使一行有空列，也总是存储一对。没有机会缺少一行lang_id=2

是否可以通过数据库中的更新查询来完成此任务，还是我必须导出数据、编辑并重新插入？

数据库服务器是 10.1.44-MariaDB-0ubuntu0.18.04.1 Ubuntu 18.04

这是一些示例数据的转储

INSERT INTO store_locator_store_lang (id_store, lang_id, partner, address, city, occupation) VALUES (543, 1, 'TEST', 'TEST, Τ.Κ. 52100', 'TEST', 'TEST');
INSERT INTO store_locator_store_lang (id_store, lang_id, partner, address, city, occupation) VALUES (543, 2, '', '', '', '');