mysql - 没有 WHERE 子句的 SQL 查询优化

Question

我有一个包含 1000 万行的简单 MYSQL 表。它需要优化，但我不确定为什么这个简单的选择需要这么长时间。似乎是查询时间，在流式传输结果之前。

关于设置性能调整的任何好的链接？非常过时的示例:http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation/

##QUERY: 
SELECT * FROM scga_results;

mysql-slow.log
# Time: 120126 15:05:03
# User@Host: abc[abc] @ localhost []
# Query_time: 25  Lock_time: 0  Rows_sent: 11896464  Rows_examined: 11896464
select * from scga_results;

##REF
mysql> describe scga_results;
+--------------+------------------+------+-----+-------------------+----------------+
| Field        | Type             | Null | Key | Default           | Extra          |
+--------------+------------------+------+-----+-------------------+----------------+
| id           | int(10) unsigned | NO   | PRI | NULL              | auto_increment |
| application  | varchar(255)     | NO   | MUL |                   |                |
| field        | varchar(255)     | YES  |     | NULL              |                |
| value        | varchar(255)     | YES  |     | NULL              |                |
| period       | varchar(255)     | YES  | MUL | NULL              |                |
| end_time     | varchar(255)     | YES  |     | NULL              |                |
| date_updated | timestamp        | NO   |     | CURRENT_TIMESTAMP |                |
+--------------+------------------+------+-----+-------------------+----------------+
7 rows in set (0.00 sec)

mysql> show table status like 'scga_results'\G
*************************** 1. row ***************************
           Name: scga_results
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 11897025
 Avg_row_length: 96
    Data_length: 1152314616
Max_data_length: 281474976710655
   Index_length: 180028416
      Data_free: 0
 Auto_increment: 11971193
    Create_time: 2012-01-26 13:32:57
    Update_time: 2012-01-27 00:20:04
     Check_time: 2012-01-26 13:39:19
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

添加查询详细信息

USES KEY:
mysql> explain select * from scga_results where application="20000";
+----+-------------+--------------+------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table        | type | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+--------------+------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | scga_results | ref  | index_2       | index_2 | 257     | const |    1 | Using where |
+----+-------------+--------------+------+---------------+---------+---------+-------+------+-------------+
1 row in set (0.26 sec)

NO KEY USED: 
mysql> explain select * from scga_results where id="20";
mysql> explain select * from scga_results where period="day";
mysql> explain select * from scga_results where application>0;

添加个人资料详细信息

mysql> SET PROFILING=1;
mysql> SELECT * FROM scga_results;

------------------+---------------------+
11897787 rows in set (23.05 sec)

mysql> show profiles;
+----------+-------------+-----------------------------+
| Query_ID | Duration    | Query                       |
+----------+-------------+-----------------------------+
|        1 |  6.72981800 | select id from scga_results |
|        2 | 23.06871000 | select * from scga_results  |
+----------+-------------+-----------------------------+
2 rows in set (0.00 sec)

mysql> SHOW PROFILE FOR QUERY 2;
+--------------------------------+-----------+
| Status                         | Duration  |
+--------------------------------+-----------+
| (initialization)               | 0.000008  |
| checking query cache for query | 0.000047  |
| checking permissions           | 0.00001   |
| Opening tables                 | 0.000013  |
| System lock                    | 0.000007  |
| Table lock                     | 0.000026  |
| init                           | 0.000023  |
| optimizing                     | 0.000006  |
| statistics                     | 0.000015  |
| preparing                      | 0.000013  |
| executing                      | 0.000004  |
| Sending data                   | 23.055196 |
| end                            | 0.000018  |
| query end                      | 0.000007  |
| freeing items                  | 0.000014  |
| closing tables                 | 0.000009  |
| logging slow query             | 0.013294  |
+--------------------------------+-----------+
17 rows in set (0.01 sec)

Answer 1

看起来您正在检索超过 1GB 的数据。如果您真的需要更快地获取所有这些数据，我认为您将需要更改硬件。

由于服务器上的其他负载，您的查询运行速度可能比您预期的要慢，但您很可能已达到硬件所能提供的极限。

您可以尝试切换到更快的硬盘驱动器、某些类型的 RAID 硬盘驱动器或某种固态内存 - 甚至将表转换为 MEMORY 表以便将其保存在 RAM 中。请记住，这仍然不会使其立即生效，因为数据必须通过内存总线等。