mysql - 如何提高查询的性能？

Question

我们编写了如下所示的查询，并在表上创建了适当的索引。查询

SELECT ref_order_id, order_id, cams_ref_order_id 
FROM cart_entries_archive 
WHERE regular_price <> product_price 
AND ref_order_id >0 
AND cams_ref_order_id > 0;

但是查询正在执行全表扫描，因此我们遇到了负载峰值。

我们尝试在 where 子句列上添加索引，但仍执行完整扫描。如果可能，请重写查询。

查询解释计划

mysql>  explain select ref_order_id,order_id,cams_ref_order_id from cart_entries_archive where regular_price <> product_price and ref_order_id >0 and cams_ref_order_id > 0;
+----+-------------+----------------------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table                | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+----------------------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | cart_entries_archive | ALL  | NULL          | NULL | NULL    | NULL | 6490560 | Using where |
+----+-------------+----------------------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

表结构:

mysql> show create table cart_entries_archive\G
*************************** 1. row ***************************
       Table: cart_entries_archive
Create Table: CREATE TABLE `cart_entries_archive` (
  `row_mod` datetime DEFAULT NULL,
  `row_create` datetime DEFAULT NULL,
  `address_id` int(11) DEFAULT NULL,
  `backorder_date` datetime DEFAULT NULL,
  `cancelled_date` datetime DEFAULT NULL,
  `cart_entry_id` int(11) NOT NULL,
  `cart_id` int(11) NOT NULL,
  `delivery_date` datetime DEFAULT NULL,
  `delivery_method` varchar(100) COLLATE latin1_bin DEFAULT NULL,
  `delivery_note` varchar(255) COLLATE latin1_bin DEFAULT NULL,
  `discount_amount` decimal(8,2) DEFAULT '0.00',
  `gift_message` varchar(255) COLLATE latin1_bin DEFAULT NULL,
  `occasion` varchar(255) COLLATE latin1_bin DEFAULT NULL,
  `order_date` datetime DEFAULT NULL,
  `order_id` varchar(50) COLLATE latin1_bin DEFAULT NULL,
  `order_status` varchar(50) COLLATE latin1_bin DEFAULT NULL,
  `product_name` varchar(255) COLLATE latin1_bin DEFAULT NULL,
  `product_extra` varchar(255) COLLATE latin1_bin DEFAULT NULL,
  `product_price` decimal(8,2) DEFAULT '0.00',
  `product_count` int(11) DEFAULT NULL,
  `product_cost` decimal(8,2) DEFAULT '0.00',
  `product_sku` varchar(100) COLLATE latin1_bin DEFAULT NULL,
  `product_notes` varchar(255) COLLATE latin1_bin DEFAULT NULL,
  `parent_product_sku` varchar(100) COLLATE latin1_bin DEFAULT NULL,
  `returned_date` datetime DEFAULT NULL,
  `release_date` datetime DEFAULT NULL,
  `regular_price` decimal(8,2) DEFAULT '0.00',
  `shipping_cost` decimal(8,2) DEFAULT '0.00',
  `service_charge` decimal(8,2) DEFAULT '0.00',
  `sku_option_name` varchar(50) COLLATE latin1_bin DEFAULT NULL,
  `sku_option_value` varchar(50) COLLATE latin1_bin DEFAULT NULL,
  `shipping_company` varchar(50) COLLATE latin1_bin DEFAULT NULL,
  `shipping_fname` varchar(50) COLLATE latin1_bin DEFAULT NULL,
  `shipping_lname` varchar(50) COLLATE latin1_bin DEFAULT NULL,
  `shipping_address` varchar(100) COLLATE latin1_bin DEFAULT NULL,
  `shipping_address2` varchar(100) COLLATE latin1_bin DEFAULT NULL,
  `shipping_city` varchar(50) COLLATE latin1_bin DEFAULT NULL,
  `shipping_country` varchar(50) COLLATE latin1_bin DEFAULT NULL,
  `shipping_province` varchar(50) COLLATE latin1_bin DEFAULT NULL,
  `shipping_postal_code` varchar(10) COLLATE latin1_bin DEFAULT NULL,
  `shipping_phone` varchar(20) COLLATE latin1_bin DEFAULT NULL,
  `shipping_phone_ext` varchar(10) COLLATE latin1_bin DEFAULT NULL,
  `ship_tracking_number` varchar(50) COLLATE latin1_bin DEFAULT NULL,
  `status` varchar(20) COLLATE latin1_bin DEFAULT NULL,
  `tax` decimal(8,2) DEFAULT '0.00',
  `total_charge` decimal(8,2) DEFAULT '0.00',
  `website_id` int(11) DEFAULT NULL,
  `microsite_id` int(11) DEFAULT NULL,
  `defer_reason` varchar(255) COLLATE latin1_bin DEFAULT NULL,
  `defer_key` varchar(255) COLLATE latin1_bin DEFAULT NULL,
  `shipping_evening_phone` varchar(20) COLLATE latin1_bin DEFAULT NULL,
  `shipping_mobile_phone` varchar(20) COLLATE latin1_bin DEFAULT NULL,
  `shipping_email` varchar(100) COLLATE latin1_bin DEFAULT NULL,
  `shipping_title` varchar(10) COLLATE latin1_bin DEFAULT NULL,
  `shipping_district` varchar(30) COLLATE latin1_bin DEFAULT NULL,
  `location_type` varchar(20) COLLATE latin1_bin DEFAULT NULL,
  `occasion_id` int(11) DEFAULT NULL,
  `occasion_date` datetime DEFAULT NULL,
  `occasion_do_remind` int(11) DEFAULT NULL,
  `coupon_ref_source_id` varchar(50) COLLATE latin1_bin DEFAULT NULL,
  `delivery_date_verified` int(11) DEFAULT NULL,
  `florist_peak_charge` float DEFAULT NULL,
  `member_id` varchar(10) COLLATE latin1_bin DEFAULT NULL,
  `delivery_location_code` varchar(10) COLLATE latin1_bin DEFAULT NULL,
  `simply_iflora` int(11) DEFAULT NULL,
  `rotation_weight` int(11) DEFAULT NULL,
  `area_charge` decimal(8,2) DEFAULT NULL,
  `cf_indicator` varchar(5) COLLATE latin1_bin DEFAULT NULL,
  `category_id` varchar(10) COLLATE latin1_bin DEFAULT NULL,
  `cms_note` varchar(255) COLLATE latin1_bin DEFAULT NULL,
  `qas_queried` int(11) DEFAULT NULL,
  `shipping_verified` int(11) DEFAULT NULL,
  `occasion_event_name` varchar(50) COLLATE latin1_bin DEFAULT NULL,
  `push_date` datetime DEFAULT NULL,
  `ref_order_id` varchar(50) COLLATE latin1_bin DEFAULT NULL,
  `relationship` varchar(50) COLLATE latin1_bin DEFAULT NULL,
  `std_delivery_id` int(11) DEFAULT NULL,
  `opt_delivery_id` int(11) DEFAULT NULL,
  `service_date` datetime DEFAULT NULL,
  `parameters` varchar(1024) COLLATE latin1_bin DEFAULT NULL,
  `order_type` varchar(32) COLLATE latin1_bin DEFAULT NULL,
  `cams_ref_order_id` varchar(50) COLLATE latin1_bin DEFAULT NULL,
  `membership_discount` decimal(8,2) DEFAULT NULL,
  PRIMARY KEY (`cart_entry_id`),
  UNIQUE KEY `idx_2204` (`cart_entry_id`,`cart_id`),
  UNIQUE KEY `idx_2318` (`cart_entry_id`,`order_id`),
  KEY `idx_1049` (`order_date`),
  KEY `idx_726` (`cart_id`),
  KEY `idx_840` (`order_id`),
  KEY `idx_row_create` (`row_create`),
  KEY `idx_1035` (`push_date`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin
1 row in set (0.00 sec)

Answer 1

您的 where 子句使用“">”而不是“=”，这使得索引的有用性大大降低。该表中有多少行满足 ref_order_id>0 和 cams_ref_order_id>0 的条件？如果百分比很高，则表扫描可能是最快的方法。即使 10% 的记录满足该标准，也可能意味着 RDBMS 必须读取表的几乎每一页。

如果您希望它是“仅索引”，您可以添加以下索引:

create index TMP001 on cart_entries_archive 
(ref_order_id, cams_ref_order_id, order_id, regular_price, product_price)

where 子句中的字段引导索引，其他所有内容都跟随。如果这是您关心的唯一查询，并且维护索引的成本可以忽略不计，那么创建它就完成了。