改进这些查询的想法（MySQL）？我的生产机器在他们身上看到10秒的时间（使用超过1000的样本量）

Question

解释了表定义和查询：

项目

CREATE TABLE `item` ( 
`item_id` int(11) NOT NULL AUTO_INCREMENT, 
`item_type_id` int(11) NOT NULL, 
`brand_id` int(11) NOT NULL, 
`site_id` int(11) NOT NULL, 
`seller_id` int(11) NOT NULL, 
`title` varchar(175) NOT NULL, 
`desc` text NOT NULL, 
`url` varchar(767) NOT NULL, 
`price` int(11) NOT NULL, 
`photo` varchar(255) NOT NULL, 
`photo_file` varchar(255) NOT NULL, 
`photo_type` varchar(32) NOT NULL, 
`has_photo` enum('yes','no','pending') NOT NULL DEFAULT 'pending', 
`added_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
`updated_at` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', 
`created_at` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', 
`normalized_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', 
`location` varchar(128) NOT NULL, 
PRIMARY KEY (`item_id`), 
KEY `item_type_id` (`item_type_id`), 
KEY `brand_id` (`brand_id`), 
KEY `site_id` (`site_id`), 
KEY `seller_id` (`seller_id`), 
KEY `created_at` (`created_at`), 
KEY `added_at` (`added_at`), 
KEY `normalized_time` (`normalized_time`), 
KEY `typephototime` (`item_type_id`,`has_photo`,`normalized_time`), 
KEY `brandidphoto` (`brand_id`,`item_type_id`,`has_photo`), 
KEY `brandidphoto2` (`brand_id`,`item_type_id`,`has_photo`), 
KEY `idphoto` (`item_type_id`,`has_photo`), 
KEY `idphototime` (`item_type_id`,`has_photo`,`normalized_time`), 
KEY `idphoto2` (`item_type_id`,`has_photo`), 
KEY `typepricebrandid` (`item_type_id`,`price`,`brand_id`,`item_id`), 
KEY `sellertypephototime` (`seller_id`,`item_type_id`,`has_photo`,`normalized_time`), 
KEY `typephoto` (`item_type_id`,`has_photo`) 
) ENGINE=MyISAM AUTO_INCREMENT=508885 DEFAULT CHARSET=latin1 | 

MySQL>解释SELECT item.*FROMitem其中item.item_type_id=“1”和item.has_photo=“yes”按normalized_time DESC限制1排序；

+----+-------------+-------+------+------------------------------------------------------------------------------------+---------------+---------+-------------+-------+-------------+ 
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | 
+----+-------------+-------+------+------------------------------------------------------------------------------------+---------------+---------+-------------+-------+-------------+ 
| 1 | SIMPLE | item | ref | item_type_id,typephototime,idphoto,idphototime,idphoto2,typepricebrandid,typephoto | typephototime | 5 | const,const | 69528 | Using where | 
+----+-------------+-------+------+------------------------------------------------------------------------------------+---------------+---------+-------------+-------+-------------+ 
1 row in set (0.02 sec) 

MySQL>解释SELECT*FROMitem其中item_type_id=“1”和（价格在“25”和“275”之间）brand_id=“10”ORDER BY item_id DESC LIMIT 1；

+----+-------------+-------+-------+------------------------------------------------------------------------------------------------------------------------+---------+---------+------+------+-------------+ 
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | 
+----+-------------+-------+-------+------------------------------------------------------------------------------------------------------------------------+---------+---------+------+------+-------------+ 
| 1 | SIMPLE | item | index | item_type_id,brand_id,typephototime,brandidphoto,brandidphoto2,idphoto,idphototime,idphoto2,typepricebrandid,typephoto | PRIMARY | 4 | NULL | 203 | Using where | 
+----+-------------+-------+-------+------------------------------------------------------------------------------------------------------------------------+---------+---------+------+------+-------------+ 
1 row in set (0.01 sec) 

MySQL>解释SELECT item.*FROMitem其中item.brand_id=“10”，item.item_type_id=“1”，item.has_photo=“yes”按normalized_time DESC限制1排序；

+----+-------------+-------+-------+------------------------------------------------------------------------------------------------------------------------+-----------------+---------+------+--------+-------------+ 
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | 
+----+-------------+-------+-------+------------------------------------------------------------------------------------------------------------------------+-----------------+---------+------+--------+-------------+ 
| 1 | SIMPLE | item | index | item_type_id,brand_id,typephototime,brandidphoto,brandidphoto2,idphoto,idphototime,idphoto2,typepricebrandid,typephoto | normalized_time | 8 | NULL | 502397 | Using where | 
+----+-------------+-------+-------+------------------------------------------------------------------------------------------------------------------------+-----------------+---------+------+--------+-------------+ 
1 row in set (2.15 sec) 

MySQL>解释从item中选择计数(*)，其中item.item_type_id=“1”，item.has_photo=“yes”；

+----+-------------+-------+------+------------------------------------------------------------------------------------+-----------+---------+-------------+-------+--------------------------+ 
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | 
+----+-------------+-------+------+------------------------------------------------------------------------------------+-----------+---------+-------------+-------+--------------------------+ 
| 1 | SIMPLE | item | ref | item_type_id,typephototime,idphoto,idphototime,idphoto2,typepricebrandid,typephoto | typephoto | 5 | const,const | 71135 | Using where; Using index | 
+----+-------------+-------+------+------------------------------------------------------------------------------------+-----------+---------+-------------+-------+--------------------------+ 
1 row in set (0.01 sec)

Answer 1

我建议改变以下几点：

• 您不需要所有这些索引。您实际上只需要对被大量访问的字段（如外键字段）进行索引。删除除ID字段上的索引以外的所有索引。
• 除非有实际数据，否则应将日期存储为空。
• 远离枚举数据类型，使用带有代表每个值的标志的smallint。例如，0挂起，1是，2否。

在减少数据库大小的同时，它使事情变得更干净。您的新表结构如下所示：

CREATE TABLE `item` ( 
    `item_id` int(11) NOT NULL AUTO_INCREMENT, 
    `item_type_id` int(11) NOT NULL, 
    `brand_id` int(11) NOT NULL, 
    `site_id` int(11) NOT NULL, 
    `seller_id` int(11) NOT NULL, 
    `title` varchar(175) NOT NULL, 
    `desc` text NOT NULL, 
    `url` varchar(767) NOT NULL, 
    `price` int(11) NOT NULL, 
    `photo` varchar(255) NOT NULL, 
    `photo_file` varchar(255) NOT NULL, 
    `photo_type` varchar(32) NOT NULL, 
    `has_photo` smallint NOT NULL DEFAULT 0, 
    `added_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
    `updated_at` datetime NULL DEFAULT NULL, 
    `created_at` datetime NULL DEFAULT NULL, 
    `normalized_time` datetime NULL DEFAULT NULL, 
    `location` varchar(128) NULL, 
    PRIMARY KEY (`item_id`), 
    KEY `item_type_id` (`item_type_id`), 
    KEY `brand_id` (`brand_id`), 
    KEY `site_id` (`site_id`), 
    KEY `seller_id` (`seller_id`)
) ENGINE=MyISAM AUTO_INCREMENT=508885 DEFAULT CHARSET=latin1;

我还建议使用utf8_unicode_ci作为排序规则，utf8作为字符集，innodb作为引擎。

但首先，删除所有这些键，然后再试一次。还要删除第3个查询上的别名。

从brand_id=“10”、item_type_id=“1”、has_photo=“yes”按normalized_time DESC LIMIT 1排序的项目中选择*；

Answer 2

下列索引是多余的，因为它们与另一个索引的左列相匹配。您几乎可以肯定地删除这些索引并节省一些空间和开销。

KEY `item_type_id` (`item_type_id`), /* redundant */
KEY `brand_id` (`brand_id`), /* redundant */
KEY `seller_id` (`seller_id`), /* redundant */
KEY `idphototime` (`item_type_id`,`has_photo`,`normalized_time`),  /* redundant */
KEY `brandidphoto2` (`brand_id`,`item_type_id`,`has_photo`), /* redundant */
KEY `idphoto` (`item_type_id`,`has_photo`), /* redundant  */
KEY `idphoto2` (`item_type_id`,`has_photo`), /* redundant */
KEY `typephoto` (`item_type_id`,`has_photo`) /* redundant */

剩下以下索引：

KEY `site_id` (`site_id`), 
KEY `created_at` (`created_at`), 
KEY `added_at` (`added_at`), 
KEY `normalized_time` (`normalized_time`), 
KEY `brandidphoto` (`brand_id`,`item_type_id`,`has_photo`), 
KEY `typephototime` (`item_type_id`,`has_photo`,`normalized_time`),
KEY `typepricebrandid` (`item_type_id`,`price`,`brand_id`,`item_id`), 
KEY `sellertypephototime` (`seller_id`,`item_type_id`,`has_photo`,`normalized_time`), 

您还可以使用类似pt-duplicate-key-checker的工具来查找冗余索引。

接下来考虑存储引擎：

) ENGINE=MyISAM AUTO_INCREMENT=508885 DEFAULT CHARSET=latin1;

几乎总是，InnoDB是比Myisam更好的选择。不仅是为了性能，还为了数据完整性和崩溃安全。InnoDB自2010年以来一直是默认的存储引擎，它是唯一一个正在积极改进的html" target="_blank">存储引擎。我建议制作这个表的副本，将存储引擎更改为InnoDB，并将其性能与您的查询进行比较。

接下来让我们考虑查询的索引：

SELECT item.* FROM `item` WHERE item.item_type_id = "1" AND item.has_photo = "yes" 
ORDER BY normalized_time DESC LIMIT 1; 

我将选择(item_type_id,has_photo,normalized_time)上的索引，这就是它当前使用的索引，即typephototime。

进一步优化的一种方法是只获取索引中的列。当你在解释计划中看到“使用索引”的时候，这对于性能来说是一个巨大的改进。

另一个重要因素是确保您的索引缓存在内存中：如果您使用MyISAM，则增加key_buffer_size；如果您使用InnoDB，则增加innodb_buffer_pool_size使其与您希望保留在内存中的所有索引一样大。因为您不希望运行需要扫描比缓冲区大的索引的查询；它会引起很多交换。

SELECT * FROM `item` WHERE item_type_id = "1" AND (price BETWEEN "25" AND "275") AND brand_id = "10" 
ORDER BY item_id DESC LIMIT 1; 

我会选择(item_type_id,brand_id,price)上的索引，但此查询当前使用的是主索引。您应该创建一个新的索引。

SELECT item.* FROM `item` WHERE item.brand_id = "10" AND item.item_type_id = "1" AND item.has_photo = "yes" 
ORDER BY normalized_time DESC LIMIT 1; 

我将选择(item_type_id,brand_id,has_photo,normalized_time)上的索引。您应该创建一个新的索引。

SELECT COUNT(*) FROM `item` WHERE item.item_type_id = "1" AND item.has_photo = "yes" ; 

我将选择(item_type_id,has_photo)上的索引，这就是它当前使用的索引，即typephoto。它还进行了“使用索引”优化，因此唯一的其他改进是确保内存中有足够的缓冲区保存索引。

很难优化select COUNT(*)查询，因为它们自然要扫描大量行。

优化COUNT(*)的另一个策略是离线计算计数，并将它们存储在汇总表或内存缓存（如memcached）中，这样就不必在每次有人加载页面时重新计算它们。但这意味着您必须在每次有人在item表中添加或删除一行时更新计数，这可能会花费更多，具体取决于发生的频率。