本文正在参与 “性能优化实战记录”话题征文活动
company_id和odds_typeid的值并不够散列(company_id表少于1k条就表示不够散列)，不必要创建索引。
出现慢sql警告，排查语句是否有问题。
mysql> SELECT * FROM `basketball_ls_match_odds` WHERE `match_id` = 3554260 AND `company_id` = 15 order by id desc limit 1\G *************************** 1. row *************************** id: 27071647 match_id: 3554260 company_id: 15 odds_typeid: 1 rightteam_score: 0.8600 handicap_score: -20.5000 leftteam_score: 0.9000 handicap_time: 1595497414 create_time: 2020-07-23 17:43:37 update_time: 1970-12-31 08:00:00 1 row in set (0.50 sec)
sql速度太慢。开始分析。
mysql> explain SELECT * FROM `basketball_ls_match_odds` WHERE `match_id` = 3554260 AND `company_id` = 15 order by id desc limit 1\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: basketball_ls_match_odds partitions: NULL type: index_merge possible_keys: idx_match_id,idx_company_id key: idx_match_id,idx_company_id key_len: 4,4 ref: NULL rows: 910 filtered: 100.00 Extra: Using intersect(idx_match_id,idx_company_id); Using where; Using filesort 1 row in set, 1 warning (0.00 sec)
使用到了聚合索引。
猜想此处索引应该有问题。
使用强制索引
mysql> SELECT * FROM `basketball_ls_match_odds` force index(idx_match_id) WHERE `match_id` = 3554260 AND `company_id` = 15 order by id desc limit 1\G *************************** 1. row *************************** id: 27071647 match_id: 3554260 company_id: 15 odds_typeid: 1 rightteam_score: 0.8600 handicap_score: -20.5000 leftteam_score: 0.9000 handicap_time: 1595497414 create_time: 2020-07-23 17:43:37 update_time: 1970-12-31 08:00:00 1 row in set (0.01 sec)
使用索引时，查询速度正常
查看表结构
mysql> desc basketball_ls_match_odds; +-----------------+------------------+------+-----+---------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------------+------------------+------+-----+---------------------+----------------+ | id | int(11) unsigned | NO | PRI | NULL | auto_increment | | match_id | int(11) unsigned | NO | MUL | 0 | | | company_id | int(11) | NO | MUL | 0 | | | odds_typeid | tinyint(2) | NO | MUL | 0 | | | rightteam_score | decimal(8,4) | NO | | 0.0000 | | | handicap_score | decimal(8,4) | NO | | 0.0000 | | | leftteam_score | decimal(8,4) | NO | | 0.0000 | | | handicap_time | int(11) | NO | | 0 | | | create_time | timestamp | NO | | 1970-12-31 08:00:00 | | | update_time | timestamp | NO | | 1970-12-31 08:00:00 | | +-----------------+------------------+------+-----+---------------------+----------------+ 10 rows in set (0.00 sec)
得出结论，索引创建的有问题。company_id和odds_typeid的值并不够散列(company_id表少于1k条就表示不够散列)，不必要创建索引。
删除索引
mysql> ALTER TABLE `basketball_ls_match_odds` DROP INDEX `idx_odds_typeid`, DROP INDEX `idx_company_id`; Query OK, 0 rows affected (0.13 sec) Records: 0 Duplicates: 0 Warnings: 0
再次查询
mysql> SELECT * FROM `basketball_ls_match_odds` WHERE `match_id` = 3554260 AND `company_id` = 15 order by id desc limit 1\G *************************** 1. row *************************** id: 27071647 match_id: 3554260 company_id: 15 odds_typeid: 1 rightteam_score: 0.8600 handicap_score: -20.5000 leftteam_score: 0.9000 handicap_time: 1595497414 create_time: 2020-07-23 17:43:37 update_time: 1970-12-31 08:00:00 1 row in set (0.00 sec)
优化成功
