There are two tables in the database, one customer and one order.

There are more than 3K customer table data and about 100W order table data.

When you need to query the customers whose order status is refund, payment, partial payment and payment, there is a slow page loading problem, which takes about 6s to load.

The table structure is as follows

--Client table--
CREATE TABLE `customer` (
  `customer_id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT 'Student ID',
  `customer_name` varchar(64) NOT NULL DEFAULT '' COMMENT 'Full name',
  `gender` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Gender,1 For men, 2 for women',
  `birthday` date DEFAULT NULL COMMENT 'Birthday',
  `email` varchar(30) NOT NULL DEFAULT '' COMMENT 'mailbox',
  PRIMARY KEY (`customer_id`)
) ENGINE=InnoDB AUTO_INCREMENT=20374 DEFAULT CHARSET=utf8 COMMENT='Background student information table'

--Order form--
CREATE TABLE `erp_order` (
  `order_id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT 'Order ID',
  `product_id` bigint(11) DEFAULT NULL COMMENT 'product ID',
  `order_num` varchar(50) DEFAULT NULL COMMENT 'Order number',
  `policy_id` bigint(11) DEFAULT NULL COMMENT 'Favoured policy',
  `policy_amount` bigint(11) NOT NULL DEFAULT '0' COMMENT 'Preferential amount',
  `sale_user_id` bigint(11) DEFAULT NULL COMMENT 'Sales Consultant',
  `customer_id` bigint(11) DEFAULT NULL COMMENT 'Student ID',
  `order_status` int(11) DEFAULT NULL COMMENT 'Order status 1 to be activated by customer 2 not paid 3 partially paid 4 paid 5 cancelled 6 refunded 7 refunded',
  `order_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT 'Order time',
  PRIMARY KEY (`order_id`) USING BTREE,
  KEY `index_order_time` (`order_time`) USING BTREE,
  KEY `index_product_id` (`product_id`) USING BTREE,
  KEY `index_order_num` (`order_num`) USING HASH,
  KEY `index_order_service_status` (`service_status`) USING BTREE,
  KEY `index_order_status` (`order_status`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=998096 DEFAULT CHARSET=utf8 COMMENT='Order information form'

The original query sql is as follows

SELECT 
 COUNT(0)
FROM
  erp_customer ec 
WHERE ec.`customer_id` IN  
  (SELECT DISTINCT 
    eo.`customer_id` 
  FROM
    erp_order eo 
  WHERE eo.order_status IN (3, 4, 6, 7)) 

The above sql execution time and explain are as follows

Optimized sql

SELECT 
  COUNT(0) 
FROM
  erp_customer ec 
WHERE EXISTS 
  (SELECT DISTINCT 
    eo.`customer_id` 
  FROM
    erp_order eo 
  WHERE eo.order_status IN (3, 4, 6, 7) 
    AND eo.`customer_id` = ec.`customer_id`)

The optimized sql execution time and explain are as follows

It can be seen that the execution time is shortened from 3.1s to 0.137s, indicating that it is obvious.