关于绑定变量的问题

Question

  各位大侠，小弟mysql的slow_log里的数据量很大，全是类似
     SELECT JobId,JobClass,sns_network_id,JobContent,Status,Info from async_job_queue WHERE JobClass in (32,1,2,43,33,41,42,11,12,44,21,31) and Status in (1) and ScheTime<=1307011812 order by  JobId   LIMIT 50;
我修改sql语句和表结构后，没啥改善，最后加了组合索引能减少点，主要原因还是并发性很高，五万用户并发执行上面语句，每个五万次，想通过绑定变量改善，不知道怎么来写帮定变量？帮帮忙！不知道还有其他方法没有？

Answer 1

1 看执行计划是否用到索引
2 是在不行就在这四个列上加组合做引JobClass ，Status ，ScheTime，JobId 

Answer 2

执行计划里面只走主键索引，我加了JobClass ，Status ，ScheTime三个字段的组合索引，然后去掉order by，查看执行计划，这时走组合索引，这时执行速度很快，内存很大很大，io也很大，但是还有少量的语句进入slow_log里面了！现在不知道什么问题，想通过加绑定变量来解决

Answer 3

执行计划里面只走主键索引，我加了JobClass ，Status ，ScheTime三个字段的组合索引，然后去掉order by，查看执行计划，这时走组合索引，这时执行速度很快，内存很大很大，io也很大，但是还有少量的语句进入slow_log里面了！现在不知道什么问题，想通过加绑定变量来解决，不知道怎么加

Answer 4

http://www.ixpub.net/thread-849958-1-1.html
http://linuxguest.blog.51cto.com/195664/496888
你可以参考下上面的两篇文章.
mysql体系结构跟oracle不太一样,绑定变量的作用看来不大.

Answer 5

这是我slow_log里面的语句：
# Time: 110608 10:57:32
# User@Host: root[root] @ localhost []
# Query_time: 38.339345  Lock_time: 2.825681 Rows_sent: 3  Rows_examined: 439
SET timestamp=1307501852;
SELECT JobId,JobClass,sns_network_id,JobContent,Status,Info from async_job_queue WHERE JobClass in (32,1,2,43,33,41,42,11,12,44,21,31) and Status in (1) and ScheTime<=1307028624   LIMIT 50;

Answer 6

贴出来你的 
explain     SELECT JobId,JobClass,sns_network_id,JobContent,Status,Info from async_job_queue WHERE JobClass in (32,1,2,43,33,41,42,11,12,44,21,31) and Status in (1) and ScheTime<=1307011812 order by  JobId   LIMIT 50;还有 show index from async_job_queue 等，以供分析。

Answer 7

mysql> 
mysql> explain  SELECT JobId,JobClass,sns_network_id,JobContent,Status,Info from async_job_queue WHERE JobClass in (32,1,2,43,33,41,42,11,12,44,21,31) and Status in (1) and ScheTime<= 1307072511  LIMIT 50;
+----+-------------+-----------------+-------+---------------+--------+---------+------+------+-------------+
| id | select_type | table           | type  | possible_keys | key    | key_len | ref  | rows | Extra       |
+----+-------------+-----------------+-------+---------------+--------+---------+------+------+-------------+
|  1 | SIMPLE      | async_job_queue | range | cccccc        | cccccc | 6       | NULL |  437 | Using where | 
+----+-------------+-----------------+-------+---------------+--------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> show index from async_job_queue;                                                                                                                                +-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table           | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| async_job_queue |          0 | PRIMARY  |            1 | JobId       | A         |         439 |     NULL | NULL   |      | BTREE      |         | 
| async_job_queue |          1 | cccccc   |            1 | ScheTime    | A         |         439 |     NULL | NULL   |      | BTREE      |         | 
| async_job_queue |          1 | cccccc   |            2 | JobClass    | A         |         439 |     NULL | NULL   |      | BTREE      |         | 
| async_job_queue |          1 | cccccc   |            3 | Status      | A         |         439 |     NULL | NULL   |      | BTREE      |         | 
+-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
4 rows in set (0.09 sec)mysql> explain  SELECT JobId,JobClass,sns_network_id,JobContent,Status,Info from async_job_queue WHERE JobClass in (32,1,2,43,33,41,42,11,12,44,21,31) and Status in (1) and ScheTime<= 1307072522 ORDER BY JobId LIMIT 50;
+----+-------------+-----------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table           | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-----------------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | async_job_queue | index | cccccc        | PRIMARY | 4       | NULL |   50 | Using where | 
+----+-------------+-----------------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

Answer 8

能不能修改my.cnf中那些参数来 优化 大量并发select语句？？