欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  后端开发

Select多行SQL语句慢查询优化

程序员文章站 2024-02-03 19:34:16
...
环境:MySQL5.5 InnoDB表 post ,主键为 id

需求非常简单,根据id返回 post.name ,需要一次返回指定的多个,所以使用了where id in

select `name` from `post` where `id` in(2142324,2106574,2106564,2075699,2065402,2050790,2038346,2038345,2035588,2031765,2022035,2022034,2020745,2020737,2020718,1987558,1970241,1962232,1911342,1891481,1889641,1877438,1877434,1867217,1866057,1866013,1847315);

Explain结果:

+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | post  | range | PRIMARY       | PRIMARY | 4       | NULL |   27 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+

现在这句SQL一小时产生几千条慢查询记录,我直接在console执行每次也需要7,8秒

能怎么优化呢?

回复内容:

环境:MySQL5.5 InnoDB表 post ,主键为 id

需求非常简单,根据id返回 post.name ,需要一次返回指定的多个,所以使用了where id in

select `name` from `post` where `id` in(2142324,2106574,2106564,2075699,2065402,2050790,2038346,2038345,2035588,2031765,2022035,2022034,2020745,2020737,2020718,1987558,1970241,1962232,1911342,1891481,1889641,1877438,1877434,1867217,1866057,1866013,1847315);

Explain结果:

+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | post  | range | PRIMARY       | PRIMARY | 4       | NULL |   27 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+

现在这句SQL一小时产生几千条慢查询记录,我直接在console执行每次也需要7,8秒

能怎么优化呢?

从纯sql的角度看是没问题的,走了索引,rows值也不大,如果速度很慢,可以从3个方面看 1:innodb参数是否已经优化,比如innodbbufferpoor_size等 2:qcache命中率。根据你上诉问题,一小时该语句产生几千条慢查询,命中率明显不高,是否该表写入频率过高导致qcache失效,是否考虑读写分离 3:数据量大的话考虑分区,或者水平分表

相关标签: mysql sql php