1. 最近在自己的小项目线上一张表增加到了10000条记录，然后页面打开的速度感觉明显下降，于是打算看下是不是索引的问题
表结构：
CREATE TABLE `crawl_items` ( `id` bigint NOT NULL AUTO_INCREMENT, `goods_source_sn` varchar(55) NOT NULL DEFAULT '' COMMENT '来源商品编号', `goods_info_url` varchar(127) NOT NULL DEFAULT '' COMMENT '来源商品url', `source` varchar(64) NOT NULL DEFAULT '' COMMENT '信息来源', `url_code` varchar(64) NOT NULL DEFAULT '' COMMENT 'url识别码', `thumb_url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT '商品图标', `zhi_count` smallint NOT NULL DEFAULT '0' COMMENT '值的数量', `buzhi_count` smallint NOT NULL DEFAULT '0' COMMENT '不值的数量', `star_count` smallint NOT NULL DEFAULT '0' COMMENT '值的数量', `comments_count` smallint NOT NULL DEFAULT '0' COMMENT '评论的数量', `mall` varchar(64) NOT NULL DEFAULT '' COMMENT '商城', `title` varchar(256) NOT NULL DEFAULT '' COMMENT '商品标题', `emphsis` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '详情页第一句描述', `detail` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '商品详情', `detail_brief` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT '商品详情简介(取商品详情前32个字符)', `label` varchar(256) NOT NULL DEFAULT '' COMMENT '商品标签，json形式保存', `category_text` varchar(256) NOT NULL DEFAULT '' COMMENT '商品类目', `item_create_time` datetime NOT NULL COMMENT '值得买商品条目创建时间', `item_update_time` datetime NOT NULL COMMENT '值得买商品条目更新时间', `main_image_url` varchar(256) NOT NULL DEFAULT '' COMMENT '商品主图', `big_image_urls` varchar(1024) NOT NULL DEFAULT '' COMMENT '商品大图列表', `small_image_urls` varchar(1024) NOT NULL DEFAULT '' COMMENT '商品小图列表', `price_text` varchar(256) NOT NULL DEFAULT '' COMMENT '价格文字', `price` decimal(10,2) DEFAULT '0.00' COMMENT '价格', `unit_price` decimal(10,2) DEFAULT NULL COMMENT '单价', `actual_buy_link` varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT '实际购买链接', `transfer_link` varchar(1024) DEFAULT NULL COMMENT '转链后的链接[只适用于无需跟单的场景]', `transfer_result` tinyint NOT NULL DEFAULT '0' COMMENT '转链结果 1 成功 -1：失败 0 未转[只适用于无需跟单的场景]', `transfer_remark` varchar(2048) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT '转链失败后的转链备注，转链成功时是京东会标明用哪张优惠券来转链[只适用于无需跟单的场景]', `taobao_pwd` varchar(56) DEFAULT '', `coupon_info` json DEFAULT NULL COMMENT '优惠券信息，json形式保存', `score` int NOT NULL DEFAULT '0' COMMENT '商品得分', `score_minute` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '每分钟得分', `keywords` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT '' COMMENT '商品关键字,json形式保存', `proxy` varchar(56) DEFAULT NULL COMMENT '爬虫代理', `status` tinyint DEFAULT '0' COMMENT '0:未爬取 1：正常 -1：过期 -2: 无效数据', `remark` varchar(256) NOT NULL DEFAULT '' COMMENT '备注', `creator` varchar(50) DEFAULT NULL COMMENT '创建人', `creator_id` bigint DEFAULT NULL COMMENT '创建人ID', `last_operator` varchar(50) DEFAULT NULL COMMENT '最后操作人', `last_operator_id` bigint DEFAULT '0' COMMENT '最后操作人ID', `created_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建日期', `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新日期', PRIMARY KEY (`id`), UNIQUE KEY `uk_source_good_sn` (`goods_source_sn`,`source`) ) ENGINE=InnoDB AUTO_INCREMENT=11090 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='爬虫商品条目信息表';
sql语句：
SELECT id,goods_source_sn,goods_info_url,source,url_code,thumb_url,zhi_count,buzhi_count,star_count,comments_count,mall,title,emphsis,detail_brief,label,category_text,item_create_time,item_update_time,main_image_url,big_image_urls,small_image_urls,price_text,price,unit_price,actual_buy_link,transfer_link,transfer_result,transfer_remark,taobao_pwd,coupon_info,score,score_minute,keywords,status,remark,creator,creator_id,last_operator,last_operator_id,created_time,update_time,detail FROM crawl_items WHERE (status =1) ORDER BY score_minute desc, update_time DESC LIMIT 20;
explain：
mysql> explain SELECT id,goods_source_sn,goods_info_url,source,url_code,thumb_url,zhi_count,buzhi_count,star_count,comments_count,mall,title,emphsis,detail_brief,label,category_text,item_create_time,item_update_time,main_image_url,big_image_urls,small_image_urls,price_text,price,unit_price,actual_buy_link,transfer_link,transfer_result,transfer_remark,taobao_pwd,coupon_info,score,score_minute,keywords,status,remark,creator,creator_id,last_operator,last_operator_id,created_time,update_time,detail FROM crawl_items WHERE (status =1) ORDER BY score_minute desc, update_time DESC LIMIT 20; +----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+ | 1 | SIMPLE | crawl_items | NULL | ALL | NULL | NULL | NULL | NULL | 9182 | 10.00 | Using where; Using filesort | +----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
看type是ALL；
根据mysql文档（https://dev.mysql.com/doc/refman/8.0/en/explain-output.html#explain-join-types），type其实是join type,表述的是“how tables are joined”，从好坏依次是：
system The table has only one row (= system table). This is a special case of the const join type. const The table has at most one matching row, which is read at the start of the query. Because there is only one row, values from the column in this row can be regarded as constants by the rest of the optimizer. const tables are very fast because they are read only once. const is used when you compare all parts of a PRIMARY KEY or UNIQUE index to constant values. In the following queries, tbl_name can be used as a const table:
SELECT * FROM tbl_name WHERE primary_key=1; SELECT * FROM tbl_name WHERE primary_key_part1=1 AND primary_key_part2=2;
eq_ref One row is read from this table for each combination of rows from the previous tables. Other than the system and const types, this is the best possible join type. It is used when all parts of an index are used by the join and the index is a PRIMARY KEY or UNIQUE NOT NULL index. eq_ref can be used for indexed columns that are compared using the = operator. The comparison value can be a constant or an expression that uses columns from tables that are read before this table. In the following examples, MySQL can use an eq_ref join to process ref_table:
SELECT * FROM ref_table,other_table WHERE ref_table.key_column=other_table.column; SELECT * FROM ref_table,other_table WHERE ref_table.key_column_part1=other_table.column AND ref_table.key_column_part2=1;
ref All rows with matching index values are read from this table for each combination of rows from the previous tables. ref is used if the join uses only a leftmost prefix of the key or if the key is not a PRIMARY KEY or UNIQUE index (in other words, if the join cannot select a single row based on the key value). If the key that is used matches only a few rows, this is a good join type. ref can be used for indexed columns that are compared using the = or <=> operator. In the following examples, MySQL can use a ref join to process ref_table:
SELECT * FROM ref_table WHERE key_column=expr; SELECT * FROM ref_table,other_table WHERE ref_table.key_column=other_table.column; SELECT * FROM ref_table,other_table WHERE ref_table.key_column_part1=other_table.column AND ref_table.key_column_part2=1;
fulltext The join is performed using a FULLTEXT index. ref_or_null This join type is like ref, but with the addition that MySQL does an extra search for rows that contain NULL values. This join type optimization is used most often in resolving subqueries. In the following examples, MySQL can use a ref_or_null join to process ref_table:
SELECT * FROM ref_table WHERE key_column=expr OR key_column IS NULL;
See Section 8.2.1.15, “IS NULL Optimization”. index_merge This join type indicates that the Index Merge optimization is used. In this case, the key column in the output row contains a list of indexes used, and key_len contains a list of the longest key parts for the indexes used. For more information, see Section 8.2.1.3, “Index Merge Optimization”. unique_subquery This type replaces eq_ref for some IN subqueries of the following form: value IN (SELECT primary_key FROM single_table WHERE some_expr) unique_subquery is just an index lookup function that replaces the subquery completely for better efficiency. index_subquery This join type is similar to unique_subquery. It replaces IN subqueries, but it works for nonunique indexes in subqueries of the following form: value IN (SELECT key_column FROM single_table WHERE some_expr)
range Only rows that are in a given range are retrieved, using an index to select the rows. The key column in the output row indicates which index is used. The key_len contains the longest key part that was used. The ref column is NULL for this type. range can be used when a key column is compared to a constant using any of the =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, LIKE, or IN() operators:
SELECT * FROM tbl_name WHERE key_column = 10; SELECT * FROM tbl_name WHERE key_column BETWEEN 10 and 20; SELECT * FROM tbl_name WHERE key_column IN (10,20,30); SELECT * FROM tbl_name WHERE key_part1 = 10 AND key_part2 IN (10,20,30);
index The index join type is the same as ALL, except that the index tree is scanned. This occurs two ways: If the index is a covering index for the queries and can be used to satisfy all data required from the table, only the index tree is scanned. In this case, the Extra column says Using index. An index-only scan usually is faster than ALL because the size of the index usually is smaller than the table data. A full table scan is performed using reads from the index to look up data rows in index order. Uses index does not appear in the Extra column. MySQL can use this join type when the query uses only columns that are part of a single index. ALL A full table scan is done for each combination of rows from the previous tables. This is normally not good if the table is the first table not marked const, and usually very bad in all other cases. Normally, you can avoid ALL by adding indexes that enable row retrieval from the table based on constant values or column values from earlier tables.
所以All是最差的，因为用到了 ORDER BY score_minute desc, update_time，所以添加了两个index
alter table crawl_items add key idx_update_time(update_time)； alter table crawl_items add key idx_score_minute(score_minute)；
然后执行explain，发现还是ALL，也就是索引根本没用。
把索引删除，改成一个联合索引
alter table crawl_items add key idx_score_minute(score_minute,update_time);
这次explain执行的结果，type变成了“index”，稍微好了一丢丢。
-----
那这种selec ... from ... order by ... limit 20 这种列表页的sql该怎么写效率会高一点？为了不全表扫描，可以限制一个范围，如：
selec ... from ... where update_time > ? order by ... limit 20, 这样type会变成range，当然，update_time 是一个索引。