MySQL 简单 COUNT 花费很长时间

Question

我有一个包含超过 870.000 行的数据库。每次收到请求时，我都必须执行 COUNT 来检查行是否存在。这些 COUNT 请求最多需要 180 秒才能执行。这是相当长的。

这是我的查询:

SELECT COUNT(0) AS aantal FROM milk_quotes WHERE shopId = '438' AND quoteId = '17424765'

解释扩展显示:

id  select_type table   type    possible_keys   key key_len ref rows    filtered    Extra
1   SIMPLE  milk_quotes ref shopId  shopId  4   const   87648   100.00  Using where

表结构为:

CREATE TABLE `milk_quotes ` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `shopId` mediumint(11) DEFAULT NULL,
  `quoteId` bigint(11) DEFAULT NULL,
  `customerId` bigint(11) DEFAULT NULL,
  `customerIP` int(11) unsigned DEFAULT NULL,
  `recoveryHash` char(32) CHARACTER SET latin1 DEFAULT NULL,
  `language` varchar(2) CHARACTER SET latin1 DEFAULT 'nl',
  `channel` varchar(11) CHARACTER SET latin1 DEFAULT NULL,
  `productsCount` int(11) DEFAULT NULL,
  `isPaid` tinyint(1) DEFAULT '0',
  `isNotified` enum('0','1') CHARACTER SET latin1 DEFAULT '0',
  `paymentId` varchar(50) CHARACTER SET latin1 DEFAULT NULL,
  `paymentCountry` varchar(2) CHARACTER SET latin1 DEFAULT NULL,
  `priceIncl` decimal(10,2) DEFAULT NULL,
  `createdAt` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedAt` timestamp NULL DEFAULT NULL,
  `shopUpdatedAt` timestamp NULL DEFAULT NULL,
  `recalculatedAt` timestamp NULL DEFAULT NULL,
  `shopCreatedAt` timestamp NULL DEFAULT NULL,
  `firstReminder` mediumint(9) DEFAULT NULL,
  `secondReminder` mediumint(9) DEFAULT NULL,
  `thirdReminder` mediumint(9) DEFAULT NULL,
  `skipQuote` enum('1','0') DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `isPaid` (`isPaid`),
  KEY `shopId` (`shopId`),
  KEY `firstReminder` (`firstReminder`)
) ENGINE=InnoDB AUTO_INCREMENT=930952 DEFAULT CHARSET=utf8;

Inno DB 变量:

have_innodb YES
ignore_builtin_innodb   OFF
innodb_adaptive_hash_index  ON
innodb_additional_mem_pool_size 1048576
innodb_autoextend_increment 8
innodb_autoinc_lock_mode    1
innodb_buffer_pool_size 8388608
innodb_checksums    ON
innodb_commit_concurrency   0
innodb_concurrency_tickets  500
innodb_data_file_path   ibdata1:10M:autoextend
innodb_data_home_dir    
innodb_doublewrite  ON
innodb_fast_shutdown    1
innodb_file_io_threads  4
innodb_file_per_table   OFF
innodb_flush_log_at_trx_commit  1
innodb_flush_method 
innodb_force_recovery   0
innodb_lock_wait_timeout    50
innodb_locks_unsafe_for_binlog  OFF
innodb_log_buffer_size  1048576
innodb_log_file_size    5242880
innodb_log_files_in_group   2
innodb_log_group_home_dir   ./
innodb_max_dirty_pages_pct  90
innodb_max_purge_lag    0
innodb_mirrored_log_groups  1
innodb_open_files   300
innodb_rollback_on_timeout  OFF
innodb_stats_on_metadata    ON
innodb_support_xa   ON
innodb_sync_spin_loops  20
innodb_table_locks  ON
innodb_thread_concurrency   8
innodb_thread_sleep_delay   10000
innodb_use_legacy_cardinality_algorithm ON

Answer 1

一个查询在每个查询中每个表只能使用一个索引；因此，如果您在单个查询中查找多个字段，则需要有一个涵盖所有字段的索引。在您的查询中，使用了 shopId 索引，但在该 quoteId 中按顺序扫描，这不太好(我假设有一堆 quoteId每个商店都有几行，是吗？)。尝试将架构中的 shopId 索引更改为

KEY `shopAndQuoteIds` (`shopId`, `quoteId`),

(如果您想在现有表上执行此操作，我相信 ALTER TABLE aantal DROP INDEX shopId 后跟 ALTER TABLE aantal ADD KEY shopAndQuoteIds (shopId, quoteId)应该这样做。在执行此操作之前，请备份您的数据库!或者，您可以转储表，将架构更改为上述内容，然后将其重新导入回来。)

请注意，可以使用键的任何前缀；因此 (shopId, quoteId) 索引可用于 shopId 查询和 shopId + quoteId 查询，但是不适用于 quoteId 查询。事实上，它仍然可以用于普通的 shopId 查询，这意味着通过创建这个新索引并删除旧的 shopId 索引，您不会有任何损失。