mysql 慢sql查询_web服务器反应慢的排查方法

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 1G
*************************** 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)

代码100分

sql速度太慢。开始分析。

代码100分
 mysql> explain SELECT * FROM `basketball_ls_match_odds` WHERE `match_id` = 3554260 AND 
  `company_id` = 15 order by id desc limit 1G
  *************************** 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 1G
*************************** 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)

使用索引时，查询速度正常
查看表结构

代码100分
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 1G
*************************** 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)

优化成功