本文正在参与 “性能优化实战记录”话题征文活动
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)
优化成功
