mysql - MySQL/MariaDB查询在某些表条件下慢的可能原因

coder 2023-10-10 原文

在某些情况下，我遇到了一个缓慢查询的奇怪问题: 我做了一些测试，并能够将问题隔离到一个名为 products_description 的表(所有表都是 MyISAM)。起初我注意到当这个表是新的(即刚刚导入的)时，查询总是执行得很快(~0.3s)。但是，如果我在这个特定的表上执行任何这些操作，在任何时候(即使是在导入它之后): CHECK、OPTIMIZE、ANALYZE 或 REPAIR，查询突然减慢 x10 倍(大约 4.5 秒)并且一直保持缓慢。

请注意，我在运行查询时强制不缓存以确保结果正确。

只有当我在该表上执行以下任何操作时，我才能恢复性能:

1) DROP 表并再次导入。

或

2) ALTER 该表的以下任何一项:Collation 或 CHECKSUM 或 DELAY_KEY_WRITE。然后它以更改后的值快速运行，当恢复到旧值时，性能仍然很快。或者，可以执行 ALTER products_description FORCE 来恢复性能。

即便如此，如果我对它执行任何 CHECK、OPTIMIZE、ANALYZE 或 REPAIR 操作表，查询速度会下降，直到我执行 1) 或 2)

我测试的另一件事: 在对表执行任何操作之前，我备份了表的文件(products_description.frm、products_description.MYD、products_description.MYI)，运行查询，它运行得很快。然后我在表上执行CHECK，运行查询，速度慢了10倍，我复制了备份文件并覆盖了3个文件，再次运行查询，速度再次变慢。

我已将数据库压缩到一个 ~5mb 的 zip 文件中(解压后约 80mb)。如果有人想在您自己的环境中测试数据库，请告诉我，我会向您发送下载链接。我可以在 MariaDB 10.1+ 和 MySQL 5.6+ 的几个不同服务器上重现它。

这是我正在运行的 SQL 查询，你应该用它来测试:

SELECT DISTINCT pav.products_options_values_id, 
            pav.products_options_values_name, 
            pav.products_options_values_sort_order 
FROM   products_stock ps, 
       products_options_values pav, 
       (SELECT DISTINCT pa.products_id 
        FROM   products_attributes pa, 
               products_options_values pov, 
               (SELECT p.products_id, 
                       p.products_image, 
                       p.products_subimage1, 
                       pd.products_name, 
                       p.products_quantity, 
                       p.products_model, 
                       p.products_ordered, 
                       p.products_price, 
                       p.products_date_added, 
                       p.products_weight, 
                       p.products_length, 
                       p.products_width, 
                       p.products_height, 
                       p.products_tax_class_id, 
                       p.products_status, 
                       IF(s.status, s.specials_new_products_price, NULL) 
                       AS 
                       specials_new_products_price, 
                       IF(s.status, s.specials_new_products_price, 
                       p.products_price) AS 
                       final_price, 
                       IF(p.clearance_price < p.products_cost * 2.25, 
                       p.clearance_price, 
                       p.products_cost * 2.25) 
                       AS 
                       sorting_price 
                FROM   products p 
                       LEFT JOIN specials s 
                              ON p.products_id = s.products_id 
                       LEFT JOIN products_description pd 
                              ON p.products_id = pd.products_id 
                WHERE 
                 /*FASTIDS*/ 
                 p.products_status = '1' 
                 AND Date_sub('2016-04-19', INTERVAL 7000 day) <= 
                     p.products_date_added 
               ) m 
        WHERE  m.products_id = pa.products_id 
               AND pa.options_id = 1 
               AND pa.options_values_id = pov.products_options_values_id 
               AND pov.language_id = '1') q 
WHERE  q.products_id = ps.products_id 
       AND ps.products_stock_attributes = 
           Concat('1-', pav.products_options_values_id) 
       AND ps.products_stock_quantity > 0 
ORDER  BY pav.products_options_values_sort_order ASC

这是 EXPLAIN EXTENDED 结果。似乎优化器在两个表上的工作方式不同，但这并不能真正解释为什么会发生这种情况，因为复制的数据库应该是相同的。

这些是对慢速和快速数据库的查询分析的屏幕截图:

造成这种巨大差异的可能原因是什么？如何验证和解决这些问题？

编辑: 根据评论中的要求，这些是显示表:

CREATE TABLE IF NOT EXISTS `products` (
  `products_id` int(11) NOT NULL AUTO_INCREMENT,
  `products_quantity` int(4) NOT NULL DEFAULT '0',
  `products_model` bigint(20) DEFAULT NULL,
  `products_image` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `products_image_med` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `products_image_lrg` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `products_image_sm_1` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `products_image_xl_1` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `products_image_sm_2` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `products_image_xl_2` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `products_image_sm_3` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `products_image_xl_3` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `products_image_sm_4` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `products_image_xl_4` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `products_image_sm_5` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `products_image_xl_5` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `products_image_sm_6` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `products_image_xl_6` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `products_bimage` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `products_subimage1` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `products_bsubimage1` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `products_subimage2` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `products_bsubimage2` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `products_subimage3` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `products_bsubimage3` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `products_subimage4` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `products_bsubimage4` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `products_subimage5` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `products_bsubimage5` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `products_subimage6` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `products_bsubimage6` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `products_price` decimal(15,4) NOT NULL DEFAULT '0.0000',
  `clearance_price` decimal(15,4) NOT NULL DEFAULT '0.0000',
  `products_cost` decimal(15,4) NOT NULL DEFAULT '0.0000',
  `products_rmb_cost` decimal(7,2) DEFAULT NULL,
  `products_best_rmb` decimal(7,2) DEFAULT NULL,
  `products_thb_flag` tinyint(1) NOT NULL DEFAULT '0',
  `products_date_added` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `products_last_modified` datetime DEFAULT NULL,
  `products_date_available` datetime DEFAULT NULL,
  `products_weight` decimal(5,2) NOT NULL DEFAULT '0.00',
  `products_length` decimal(5,2) NOT NULL DEFAULT '0.00',
  `products_width` decimal(5,2) DEFAULT '0.00',
  `products_height` decimal(5,2) DEFAULT '0.00',
  `products_status` tinyint(1) NOT NULL DEFAULT '0',
  `products_tax_class_id` int(11) NOT NULL DEFAULT '0',
  `manufacturers_id` int(11) DEFAULT NULL,
  `products_ordered` int(11) NOT NULL DEFAULT '0',
  `products_parent_id` int(11) NOT NULL DEFAULT '0',
  `products_price1` decimal(15,4) NOT NULL DEFAULT '0.0000',
  `products_price2` decimal(15,4) NOT NULL DEFAULT '0.0000',
  `products_price3` decimal(15,4) NOT NULL DEFAULT '0.0000',
  `products_price4` decimal(15,4) NOT NULL DEFAULT '0.0000',
  `products_price5` decimal(15,4) NOT NULL DEFAULT '0.0000',
  `products_price6` decimal(15,4) NOT NULL DEFAULT '0.0000',
  `products_price7` decimal(15,4) NOT NULL DEFAULT '0.0000',
  `products_price8` decimal(15,4) NOT NULL DEFAULT '0.0000',
  `products_price9` decimal(15,4) NOT NULL DEFAULT '0.0000',
  `products_price10` decimal(15,4) NOT NULL DEFAULT '0.0000',
  `products_price11` decimal(15,4) NOT NULL DEFAULT '0.0000',
  `products_price1_qty` int(11) NOT NULL DEFAULT '0',
  `products_price2_qty` int(11) NOT NULL DEFAULT '0',
  `products_price3_qty` int(11) NOT NULL DEFAULT '0',
  `products_price4_qty` int(11) NOT NULL DEFAULT '0',
  `products_price5_qty` int(11) NOT NULL DEFAULT '0',
  `products_price6_qty` int(11) NOT NULL DEFAULT '0',
  `products_price7_qty` int(11) NOT NULL DEFAULT '0',
  `products_price8_qty` int(11) NOT NULL DEFAULT '0',
  `products_price9_qty` int(11) NOT NULL DEFAULT '0',
  `products_price10_qty` int(11) NOT NULL DEFAULT '0',
  `products_price11_qty` int(11) NOT NULL DEFAULT '0',
  `products_qty_blocks` int(11) NOT NULL DEFAULT '1',
  `products_group_access` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'G,0',
  `products_nav_access` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'G,0',
  `sort_order` smallint(3) NOT NULL DEFAULT '0',
  `vendors_id` int(11) DEFAULT '1',
  `vendors_product_price` decimal(15,4) NOT NULL DEFAULT '0.0000',
  `vendors_prod_id` varchar(24) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `vendors_prod_comments` mediumtext COLLATE utf8_unicode_ci,
  `products_qty_days` smallint(4) NOT NULL DEFAULT '0',
  `products_qty_years` smallint(4) NOT NULL DEFAULT '0',
  `products_quantity_order_min` int(8) NOT NULL DEFAULT '1',
  `products_quantity_order_units` int(8) NOT NULL DEFAULT '1',
  `products_price_list` decimal(15,4) NOT NULL DEFAULT '0.0000',
  `products_price_rebate` decimal(15,4) NOT NULL DEFAULT '0.0000',
  `products_discount1` decimal(15,4) NOT NULL DEFAULT '0.0000',
  `products_discount2` decimal(15,4) NOT NULL DEFAULT '0.0000',
  `products_discount3` decimal(15,4) NOT NULL DEFAULT '0.0000',
  `products_discount4` decimal(15,4) NOT NULL DEFAULT '0.0000',
  `products_discount1_qty` int(6) NOT NULL DEFAULT '0',
  `products_discount2_qty` int(6) NOT NULL DEFAULT '0',
  `products_discount3_qty` int(6) NOT NULL DEFAULT '0',
  `products_discount4_qty` int(6) NOT NULL DEFAULT '0',
  `products_discounts_id` int(11) NOT NULL DEFAULT '0',
  `products_priced_by_attribute` tinyint(1) NOT NULL DEFAULT '0',
  `product_is_free` tinyint(1) NOT NULL DEFAULT '0',
  `product_is_call` tinyint(1) NOT NULL DEFAULT '0',
  `products_quantity_mixed` tinyint(1) NOT NULL DEFAULT '0',
  `product_is_showroom_only` tinyint(1) NOT NULL DEFAULT '0',
  `products_discount_percentage` tinyint(1) NOT NULL DEFAULT '0',
  `products_price_excluded` tinyint(1) NOT NULL DEFAULT '0',
  `products_showhide` tinyint(1) NOT NULL DEFAULT '1',
  `products_group` varchar(11) COLLATE utf8_unicode_ci DEFAULT NULL,
  `products_vendor_code` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `products_comments` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `products_customers_approved` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`products_id`),
  KEY `idx_products_date_added` (`products_date_added`),
  KEY `products_model` (`products_model`),
  KEY `idx_products_customers_approved` (`products_customers_approved`),
  KEY `idx_products_status` (`products_status`),
  KEY `idx_products_price` (`products_price`),
  KEY `products_thb_flag` (`products_thb_flag`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=97489 ;

CREATE TABLE IF NOT EXISTS `products_attributes` (
  `products_attributes_id` int(11) NOT NULL AUTO_INCREMENT,
  `products_id` int(11) NOT NULL DEFAULT '0',
  `options_id` int(11) NOT NULL DEFAULT '0',
  `options_values_id` int(11) NOT NULL DEFAULT '0',
  `options_values_price` decimal(15,4) NOT NULL DEFAULT '0.0000',
  `price_prefix` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `products_options_sort_order` smallint(3) unsigned NOT NULL DEFAULT '9999',
  `product_attribute_is_free` tinyint(1) NOT NULL DEFAULT '0',
  `products_attributes_weight` decimal(8,4) NOT NULL DEFAULT '0.0000',
  `products_attributes_weight_prefix` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `attributes_price_onetime` decimal(15,4) NOT NULL DEFAULT '0.0000',
  `attributes_display_only` tinyint(1) NOT NULL DEFAULT '0',
  `attributes_default` tinyint(1) NOT NULL DEFAULT '0',
  `attributes_qty_prices_onetime` mediumtext COLLATE utf8_unicode_ci,
  `attributes_discounted` tinyint(1) NOT NULL DEFAULT '1',
  `attributes_price_factor` decimal(8,2) NOT NULL DEFAULT '0.00',
  `attributes_price_factor_offset` decimal(8,2) NOT NULL DEFAULT '0.00',
  PRIMARY KEY (`products_attributes_id`),
  KEY `idx_products_attributes_products_id` (`products_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=57106 ;

CREATE TABLE IF NOT EXISTS `products_description` (
  `products_id` int(11) NOT NULL AUTO_INCREMENT,
  `language_id` int(11) NOT NULL DEFAULT '1',
  `products_name` varchar(160) COLLATE utf8_unicode_ci NOT NULL,
  `products_blurb` mediumtext COLLATE utf8_unicode_ci,
  `products_description` mediumtext COLLATE utf8_unicode_ci,
  `products_url` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `products_viewed` int(5) DEFAULT '0',
  `products_head_title_tag` varchar(80) COLLATE utf8_unicode_ci DEFAULT NULL,
  `products_head_desc_tag` longtext COLLATE utf8_unicode_ci,
  `products_head_keywords_tag` longtext COLLATE utf8_unicode_ci,
  `products_seo_url` varchar(100) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`products_id`,`language_id`),
  KEY `products_name` (`products_name`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=97489 ;

CREATE TABLE IF NOT EXISTS `products_options_values` (
  `products_options_values_id` int(11) NOT NULL DEFAULT '0',
  `language_id` int(11) NOT NULL DEFAULT '1',
  `products_options_values_name` varchar(64) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `products_options_values_sort_order` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`products_options_values_id`,`language_id`),
  KEY `products_options_values` (`products_options_values_sort_order`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `products_stock` (
  `products_stock_id` int(11) NOT NULL AUTO_INCREMENT,
  `products_id` int(11) NOT NULL DEFAULT '0',
  `products_stock_attributes` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `products_stock_quantity` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`products_stock_id`),
  UNIQUE KEY `idx_products_stock_attributes` (`products_id`,`products_stock_attributes`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=57375 ;

最佳答案

显然，您在 MySQL/MariaDB 引擎和/或优化器中发现了一个相当严重的错误。

MariaDB 工作人员 (Elena Stepanova) 已报告(向 Oracle 和 Maria)并复制(确认)此错误，他们已将其分配给他们的一位首席开发人员(Sergei Petrunia)。我相信它可能很快就会得到修复，因为 MariaDB 人员的工作效率似乎很高。

对于 Oracle，嗯，完全不同的故事......

正如您发布的那样，您已经找到了临时解决方法，例如运行 ALTER table FORCE。干得好!

关于mysql - MySQL/MariaDB查询在某些表条件下慢的可能原因，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/36728093/

有关mysql - MySQL/MariaDB查询在某些表条件下慢的可能原因的更多相关文章

ruby - ECONNRESET (Whois::ConnectionError) - 尝试在 Ruby 中查询 Whois 时出错 - 2
我正在用Ruby编写一个简单的程序来检查域列表是否被占用。基本上它循环遍历列表，并使用以下函数进行检查。require'rubygems'require'whois'defcheck_domain(domain)c=Whois::Client.newc.query("google.com").available?end程序不断出错(即使我在google.com中进行硬编码)，并打印以下消息。鉴于该程序非常简单，我已经没有什么想法了-有什么建议吗？/Library/Ruby/Gems/1.8/gems/whois-2.0.2/lib/whois/server/adapters/base.
ruby - 如何以所有可能的方式将字符串拆分为长度最多为 3 的连续子字符串？ - 2
我试图获取一个长度在1到10之间的字符串，并输出将字符串分解为大小为1、2或3的连续子字符串的所有可能方式。例如:输入:123456将整数分割成单个字符，然后继续查找组合。该代码将返回以下所有数组。[1,2,3,4,5,6][12,3,4,5,6][1,23,4,5,6][1,2,34,5,6][1,2,3,45,6][1,2,3,4,56][12,34,5,6][12,3,45,6][12,3,4,56][1,23,45,6][1,2,34,56][1,23,4,56][12,34,56][123,4,5,6][1,234,5,6][1,2,345,6][1,2,3,456][123
ruby - 如何根据特征实现 FactoryGirl 的条件行为 - 2
我有一个用户工厂。我希望默认情况下确认用户。但是鉴于unconfirmed特征，我不希望它们被确认。虽然我有一个基于实现细节而不是抽象的工作实现，但我想知道如何正确地做到这一点。factory:userdoafter(:create)do|user,evaluator|#unwantedimplementationdetailshereunlessFactoryGirl.factories[:user].defined_traits.map(&:name).include?(:unconfirmed)user.confirm!endendtrait:unconfirmeddoenden
ruby-on-rails - 在 Rails 和 ActiveRecord 中查询时忽略某些字段 - 2
我知道我可以指定某些字段来使用pluck查询数据库。ids=Item.where('due_at但是我想知道，是否有一种方法可以指定我想避免从数据库查询的某些字段。某种反拔？posts=Post.where(published:true).do_not_lookup(:enormous_field) 最佳答案 Model#attribute_names应该返回列/属性数组。您可以排除其中一些并传递给pluck或select方法。像这样:posts=Post.where(published:true).select(Post.attr
ruby - 在 Ruby 中有条件地定义函数 - 2
我有一些代码在几个不同的位置之一运行:作为具有调试输出的命令行工具，作为不接受任何输出的更大程序的一部分，以及在Rails环境中。有时我需要根据代码的位置对代码进行细微的更改，我意识到以下样式似乎可行:print"Testingnestedfunctionsdefined\n"CLI=trueifCLIdeftest_printprint"CommandLineVersion\n"endelsedeftest_printprint"ReleaseVersion\n"endendtest_print()这导致:TestingnestedfunctionsdefinedCommandLin
ruby - 定义方法参数的条件 - 2
我有一个只接受一个参数的方法:defmy_method(number)end如果使用number调用方法，我该如何引发错误？？通常，我如何定义方法参数的条件？比如我想在调用的时候报错:my_method(1) 最佳答案您可以添加guard在函数的开头，如果参数无效则引发异常。例如:defmy_method(number)failArgumentError,"Inputshouldbegreaterthanorequalto2"ifnumbereputse.messageend#=>Inputshouldbegreaterthano
使用canal同步MySQL数据到ES - 2
文章目录一、概述简介原理模块二、配置Mysql使用版本环境要求1.操作系统2.mysql要求三、配置canal-server离线下载在线下载上传解压修改配置单机配置集群配置分库分表配置1.修改全局配置2.实例配置垂直分库水平分库3.修改group-instance.xml4.启动监听四、配置canal-adapter1修改启动配置2配置映射文件3启动ES数据同步查询所有订阅同步数据同步开关启动4.验证五、配置canal-admin一、概述简介canal是Alibaba旗下的一款开源项目，Java开发。基于数据库增量日志解析，提供增量数据订阅&消费。Git地址：https://github.co
sql - 查询忽略时间戳日期的时间范围 - 2
我正在尝试查询我的Rails数据库(Postgres)中的购买表，我想查询时间范围。例如，我想知道在所有日期的下午2点到3点之间进行了多少次购买。此表中有一个created_at列，但我不知道如何在不搜索特定日期的情况下完成此操作。我试过:Purchases.where("created_atBETWEEN?and?",Time.now-1.hour,Time.now)但这最终只会搜索今天与那些时间的日期。最佳答案您需要使用PostgreSQL'sdate_part/extractfunction从created_at中提取小时
ruby-on-rails - 使用包含多个关联和单独的条件 - 2
我的Gallery模型中有以下查询:media_items.includes(:photo,:video).rank(:position_in_gallery)我的图库模型有_许多媒体项，每个都有一个照片或视频关联。到目前为止，一切正常。它返回所有media_items包括它们的photo或video关联，由media_item的position_in_gallery属性排序。但是我现在需要将此查询返回的照片限制为仅具有is_processing属性的照片，即nil。是否可以进行相同的查询，但条件是返回的照片等同于:.where(photo:'photo.is_processingIS
ruby-on-rails - 在 haml View 中重构条件 - 2
除了可访问性标准不鼓励使用这一事实指向当前页面的链接，我应该怎么做重构以下View代码？#navigation%ul.tabbed-ifcurrent_page?(new_profile_path)%li{:class=>"current_page_item"}=link_tot("new_profile"),new_profile_path-else%li=link_tot("new_profile"),new_profile_path-ifcurrent_page?(profiles_path)%li{:class=>"current_page_item"}=link_tot("p

mysql - MySQL/MariaDB查询在某些表条件下慢的可能原因

有关mysql - MySQL/MariaDB查询在某些表条件下慢的可能原因的更多相关文章

随机推荐