緩慢的 MySQL 查詢

我有 3 張桌子：

Name                  Engine  Rows    Data Size   Index Size  Total Size
product               MyISAM  11.06M  859.59M     861.20M     1.68G
product_manufacturer  MyISAM  3.09K   236.52K     367K        603.52K
product_source        MyISAM  4       88          3K          3.09K

產品DDL ：

`CREATE TABLE `product` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `slug` varchar(54) COLLATE utf8_unicode_ci NOT NULL,
 `number` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
 `number_cleaned` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
 `manufacturer_id` int(11) NOT NULL,
 `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `is_active` tinyint(1) NOT NULL,
 `group_id` int(11) DEFAULT NULL,
 `created` date NOT NULL,
 `modified` date NOT NULL,
 `source_id` int(11) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `product_product_3aac1984` (`number_cleaned`),
 KEY `product_product_4ac7f441` (`manufacturer_id`),
 KEY `product_product_425ae3c4` (`group_id`),
 KEY `product_7607617b` (`source_id`),
 KEY `product_65da3d2c` (`slug`),
 KEY `product_ec9ad377` (`modified`),
 KEY `product_4264c638` (`is_active`)
) ENGINE=MyISAM AUTO_INCREMENT=11637660 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

執行約 35 秒的 SQL。為什麼？

SELECT `product`.`id` FROM `product` 
INNER JOIN `product_source` 
   ON `product`.`source_id` = product_source`.`id` 
WHERE `product`.`number_cleaned` = '5404'

解釋結果：

state duration (summed) in sec percentage
Sending data 35.77627 99.99776 (!!!)
Opening tables 0.00037 0.00103
statistics 0.00014 0.00039
updating status 0.00009 0.00025
starting 0.00005 0.00014
preparing 0.00003 0.00008
optimizing 0.00003 0.00008
init 0.00003 0.00008
freeing items 0.00001 0.00003
Table lock 0.00001 0.00003

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  product_source  index   PRIMARY PRIMARY 4   (NULL)  4   Using index
1   SIMPLE  product ref product_product_3aac1984,product_7607617b   product_7607617b    4   product_source.id  1   Using where

但其他 SQL 執行得非常快：

SELECT `product`.`id` FROM `product` 
INNER JOIN `product_manufacturer` 
   ON `product`.`manufacturer_id` = `product_manufacturer`.`id` 
WHERE `product`.`number_cleaned` = '5404'

解釋結果：

state   duration (summed) in sec    percentage
Sending data    0.00023 41.81818
statistics  0.00009 16.36364
updating status 0.00006 10.90909
starting    0.00005 9.09091
init    0.00002 3.63636
Opening tables  0.00002 3.63636
preparing   0.00002 3.63636
optimizing  0.00002 3.63636
Table lock  0.00001 1.81818
checking permissions    0.00001 1.81818
Unlocking tables    0.00001 1.81818
System lock 0.00001 1.81818


id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  product ref product_product_3aac1984,product_product_4ac7f441   product_product_3aac1984    152 const   13  Using index condition
1   SIMPLE  product_manufacturer    eq_ref  PRIMARY PRIMARY 4   product.manufacturer_id 1   Using index

切換到 InnoDB 以避免表鎖。表鎖會長期佔用 MyISAM 表。

product: INDEX(number_cleaned, manufacturer_id, id)
product: INDEX(number_cleaned, source_id, id)

number``number_cleaned聽起來像數值，但您聲明了它們VARCHAR。請務必始終引用您與之比較的數字。

我的目標是建立以下索引：

(number_cleaned, source_id)

IE

KEY ... (number_cleaned, source_id)

我從來不明白為什麼 MySQL 使用術語 key 來表示不是的東西，但這是一個不同的故事。正如@mysql_user 指出的那樣，調查是否可以將您的表遷移到innodb。

如果您想進一步擴展內容，可以創建一個覆蓋索引，例如：

KEY ... (number_cleaned, source_id, id)

引用自：https://dba.stackexchange.com/questions/134050