Mysql慢Sql排查

Z时代
2024-01-10
分类：综合

database

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)

sql速度太慢。开始分析。


    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)

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


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)