在新 VM 上运行缓慢的查询

下面的查询花费了很多时间，一个多小时。

有什么我可以在服务器方面或查询方面对其进行调整的吗？它只有 23000 条记录。

EXPLAIN SELECT
    bc.charge
  , DATE(bq.`addeddate`) AS revenuedate
  , COUNT(*) AS 'subcount'
FROM
    billingqueue AS bq
  , billingcharge AS bc   
  , subscriptionqueue AS sq
  , keyword AS k
  , service AS ser
  , vendor AS v
  , region AS r
  , subscription AS s
WHERE
    (bq.`addeddate` BETWEEN '2012-10-12 00:00:00' AND '2012-10-18 23:59:59')
AND v.id = 1
AND bq.chargetype = 'subscription'
AND bq.status = 'success'
AND bq.transactionid = bc.transactionid
AND sq.`transactionid` = bq.transactionid
AND k.`idkeyword` = sq.`keywordid`
AND sq.`serviceid` = ser.`idservice`     
AND bq.vendorid = v.id
AND s.idregion = r.idregion
AND bc.`status` = 'success';

+----+-------------+-------+--------+----------------------------------------- ----------------------------+----------------+---------+-------------------------- --+-------+--------------------------------+
| id | select_type | table | type   | possible_keys                                                          | key            | key_len | ref                        | rows  | Extra                           |
+----+-------------+-------+--------+-----------------------------------------   ----------------------------+----------------+---------+--------------------------  --+-------+--------------------------------+
|  1 | SIMPLE      | v     | const  | PRIMARY                                                               | PRIMARY        | 4       | const                      |     1 | Using index                     |
|  1 | SIMPLE      | r     | index  | PRIMARY                                                             | PRIMARY        | 4       | NULL                       |     1 | Using index                    |
|  1 | SIMPLE      | ser   | index  | PRIMARY                                                              | PRIMARY        | 8       | NULL                       |   193 | Using index; Using join buffer |
|  1 | SIMPLE      | sq    | ref    | NewIndex1,NewIndex2,NewIndex3                                       | NewIndex2      | 8       | sm2.ser.idservice          |  3090 |                                |
|  1 | SIMPLE      | k     | eq_ref | PRIMARY                                                               | PRIMARY        | 8       | sm2.sq.keywordid           |     1 | Using index                       |
|  1 | SIMPLE      | bq    | eq_ref |  PRIMARY,NewIndex1,NewIndex2,NewIndex4,NewIndex5,NewIndex6,NewIndex7 | PRIMARY          | 103     | sm2.sq.transactionid,const |     1 | Using where                    |
|  1 | SIMPLE      | bc    | ref    | NewIndex2,NewIndex1                                                  | NewIndex2      | 103     | sm2.bq.transactionid       |     1 | Using where                      |
|  1 | SIMPLE      | s     | ref    | idx_sub_region                                                      | idx_sub_region | 5       | sm2.r.idregion             | 30802 | Using where;  Using index       |
+----+-------------+-------+--------+---------------------------------------------------------------------+----------------+---------+----------------------------+-------+--------------------------------+

下面是我my.cnf在具有 2 GB RAM 的 VM 上的情况。所有表都使用 InnoDB，版本为 5.5.28。

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql

symbolic-links=0

character-set-server         = utf8
collation-server         = utf8_general_ci
port                 = 3306
socket                 = /var/lib/mysql/mysql.sock
pid-file             = /var/lib/mysql/mysql.pid
skip-external-locking
skip-name-resolve
datadir                = /var/lib/mysql
relay_log            = mysql-relay-bin
relay_log_index            = mysql-relay-index
log_error            = mysql-error.err
log_warnings
log_bin                = mysql-bin
log_slow_queries        = mysql-slow.log
long_query_time         = 10    
max_binlog_size         = 256M    
expire_logs_days         = 4    
thread_stack             = 5M      
sort_buffer_size         = 128M      
read_buffer_size         = 128M      
read_rnd_buffer_size     = 128M      
join_buffer_size         = 1024M      
binlog_cache_size         = 128K      
query_cache_type = 0

max_connections         = 25    
max_connect_errors         = 5        
concurrent_insert        = 2        
connect_timeout            = 30    
max_allowed_packet         = 32M    
sql_mode             = NO_AUTO_CREATE_USER
max_heap_table_size         = 128M    
bulk_insert_buffer_size     = 128M    
tmp_table_size              = 128M 
thread_concurrency        = 16  
thread_cache_size        = 100 
key_buffer            = 512K    

innodb_data_home_dir            = /var/lib/mysql
innodb_data_file_path        = ibdata1:128M;ibdata2:10M:autoextend
innodb_log_file_size        = 128M
innodb_log_files_in_group    = 2
innodb_buffer_pool_size        = 768M
innodb_additional_mem_pool_size    = 4M
innodb_flush_log_at_trx_commit    = 2
innodb_table_locks        = 0
innodb_log_buffer_size         = 32M
innodb_lock_wait_timeout     = 60    
innodb_thread_concurrency    = 16
innodb_commit_concurrency    = 16
innodb_support_xa        = 0
skip-innodb-doublewrite

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid