慢MySQL查询

Question

我有三张桌子：

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

Answer 1

切换到InnoDB以避免表锁。表锁可以将MyISAM表绑定很长时间。

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

number和number_cleaned听起来像数字值，但您却声明了它们为VARCHAR。一定要经常引用你比较过的数字。

Answer 2

我的目标是建立一个指数：

(number_cleaned, source_id)

即

KEY ... (number_cleaned, source_id)

我从来不明白为什么MySQL会用键这个词来形容不是的东西，但那是另一回事。正如@mysql_user所指出的，研究是否有可能将表迁移到innodb。

如果您想进一步扩展，可以创建一个覆盖索引，如下所示：

KEY ... (number_cleaned, source_id, id)