大家好，我是考100分的小小码 ，祝大家学习进步，加薪顺利呀。今天说一说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)
优化成功
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
转载请注明出处: https://daima100.com/7115.html
