1. Database table structure optimization

a. select the most applicable field attribute and index

According to the actual situation: for example, limit the length of varchar field, set fixed length char; use enum.

b. use connections instead of subqueries

According to the actual situation: try not to use subquery if it can be rewritten as join.
//Subquery / left join/left semi join

c. use explain to analyze SQL problems

Record slow query logs and analyze them one by one.

d. test

To create a test table:

CREATE TABLE `goods` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `name` varchar(128) COLLATE utf8mb4_general_ci NOT NULL,
 `price` FLOAT(10,3) UNSIGNED NOT NULL DEFAULT '0.000' COMMENT 'Price';
 `info` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Commodity list';

CREATE TABLE `goods_order_log` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `order_id` int(10) unsigned NOT NULL COMMENT 'Order id',
 `buy_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Buying time',
 `goods_id` int(10) unsigned NOT NULL COMMENT 'Order id China: Commodities id',
 `goods_num` int(10) unsigned NOT NULL COMMENT 'Order id China: Commodities id Quantity',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Commodity purchase flow chart';

CREATE TABLE `goods_fav_log` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `user_id` int(10) unsigned NOT NULL COMMENT 'Users searched id',
 `goods_id` int(10) unsigned NOT NULL COMMENT 'commodity id',
 `buy_time` `fav_value` ENUM('1','0') NOT NULL DEFAULT '1' COMMENT 'Do you search?',
 PRIMARY KEY (`id`),
 KEY `user_id` (`user_id`),
 KEY `goods_id` (`goods_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=COMPACT COMMENT='Commodity collection flow chart'

Stored procedure for filling test data:

DELIMITER $$
CREATE PROCEDURE `insert_goods`(IN `c` INT UNSIGNED)
    NO SQL
    COMMENT 'Stored procedure for generating test data'
BEGIN
declare i int default 0;
  set autocommit = 0;
  repeat
    set i = i + 1;
    insert into goods(name,price,info) values(concat(i,'_',rand_string(60)),floor(RAND()*100000)/100,rand_string(120));
  until i = c end repeat;
  set autocommit = 1;
END$$

DELIMITER $$
CREATE PROCEDURE `insert_goods_order_log`(IN `c` INT UNSIGNED)
    NO SQL
    COMMENT 'Stored procedure for generating order line'
BEGIN
declare i int default 0;
  set autocommit = 0;
  set @basetime = '2016-01-01 00:00:00';
  repeat
    set i = i + 1;
    set @orderid = concat(REPLACE(REPLACE(REPLACE(NOW(),'-',''),' ',''),':',''), '_',rand_string(6));
set @goodsnums = floor(RAND()*10) + 1;
set @buytime = from_unixtime(UNIX_TIMESTAMP(@basetime) + floor((UNIX_TIMESTAMP()-UNIX_TIMESTAMP(@basetime))*RAND()));

  while @goodsnums>0 do
    set @goodid = floor(RAND() * 10000) + 1;
    insert into goods_order_log(order_id,buy_time,goods_id,goods_num) values(@orderid,@buytime,@goodid,RAND()*50);
    set @goodsnums = @goodsnums - 1;
  end while;

  until i = c end repeat;
  set autocommit = 1;
END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE `insert_goods_fav_log`(IN `c` INT UNSIGNED)
    NO SQL
    COMMENT 'Generate the stored procedure for searching and storing goods by users'
BEGIN
declare i int default 0;
  set autocommit = 0;
  repeat
    set i = i + 1;
    set @userid = floor(RAND()*100000) + 1;
    set @goodsid = floor(RAND()*100000) + 1;
    select @userid,@goodsid,FLOOR(RAND()*2);
    insert into goods_fav_log(user_id,goods_id,fav_value) values(@userid,@goodsid,concat(FLOOR(RAND()*2),''));

  until i = c end repeat;
  set autocommit = 1;
END$$
DELIMITER ;

call insert_goods(10000); //Commodity list, 1w
call insert_goods_order_log(1000000); //Order details, 100w
call insert_goods_fav_log(100000); //Merchandise collection, 10w

//(some commodities) ranking of interval sales
select goods_id,sum(goods_num) as goods_nums from goods_order_log where goods_id between 1 and 199  group by goods_id order by goods_nums desc limit 0,20; 
##Sales ranking
//Subquery
select a.id,b.goods_nums,a.price,(b.goods_nums*a.price) as goods_sales from goods as a
    inner join (select goods_id,sum(goods_num) as goods_nums from goods_order_log where goods_id between 1 and 1999 group by goods_id ) as b on a.id=b.goods_id
order by goods_sales desc limit 0,20; 

//Merchandise collection ranking
select goods_id,count(id) as fav_nums from goods_fav_log where fav_value='1' group by goods_id order by fav_nums desc limit 0,20; 

2. Standardized use

A really good optimization is to set up a set of implementation standards and reach the optimum at the beginning. Reference resources:
<MySQL database iron law 2019.09>
<Detailed explanation of 30 military rules of MySQL>