首先声明,下面的分析十分以及相当浅薄.

在mysql中:

v4.0, 4.1, 5.0, v5.1 到 5.1.20(包括):不支持毫秒级别的慢查询分析(支持精度为1-10秒);
5.1.21+ :支持毫秒级别的慢查询分析;
6.0 到 6.0.3: 不支持毫秒级别的慢查询分析(支持精度为1-10秒);
6.0.4+:支持毫秒级别的慢查询分析;

总而言之,如果不支持毫秒级别的慢查询分析则 patch it to allow microsecond support

首先确认mysql的版本: 5.1.44-log
打开慢查询功能

#vim /etc/my.cnf
long_query_time=0.5
log-slow-queries= /usr/var/slowquery.log

超过0.5秒的查询基本是无法忍受的了.

下面来看一个比较慢的查询:

mysql> select temp_value from temperatures where temp_value > 15;
844827 rows in set (2.01 sec)
mysql> explain select temp_value from temperatures where temp_value > 15;
+----+-------------+--------------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table        | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+--------------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | temperatures | ALL  | NULL          | NULL | NULL    | NULL | 1273287 | Using where |
+----+-------------+--------------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

这个语句很简单阿，怎么会这么慢呢?:

mysql> select count(*) from temperatures;
+----------+
| count(*) |
+----------+
|  1273062 |
+----------+
1 row in set (1.01 sec)

毫无怀疑是记录数太多了,超过了百万条.加个索引来看看:

mysql> create index idx_value on temperatures (temp_value);
mysql> alter table temperatures drop index idx_value; #这个是删除索引的一种方法.
mysql> show index from temperatures; #这个是查看索引的一种方法.
+--------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table        | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| temperatures |          0 | PRIMARY   |            1 | id          | A         |     1273561 |     NULL | NULL   |      | BTREE      |         |
| temperatures |          1 | idx_value |            1 | temp_value  | A         |         314 |     NULL | NULL   | YES  | BTREE      |         |
+--------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+


然后再来看看查询速度:

mysql> select temp_value from temperatures where temp_value > 15;
844895 rows in set (0.86 sec)
mysql> explain select temp_value from temperatures where temp_value > 15;
+----+-------------+--------------+-------+---------------+-----------+---------+------+--------+--------------------------+
| id | select_type | table        | type  | possible_keys | key       | key_len | ref  | rows   | Extra                    |
+----+-------------+--------------+-------+---------------+-----------+---------+------+--------+--------------------------+
|  1 | SIMPLE      | temperatures | range | idx_value     | idx_value | 4       | NULL | 636591 | Using where; Using index |
+----+-------------+--------------+-------+---------------+-----------+---------+------+--------+--------------------------+
1 row in set (0.00 sec)

可以看到使用了索引,查询时间降到了1秒内,但是说实话速度没有快多少,不解.

再看一个慢查询:

mysql> SELECT * FROM `temperatures` WHERE (device_id in (91,92,93,94,95,96,97,98,99) and created_at >= '2010-05-06 03:26:06');
70182 rows in set (1.61 sec)
mysql> explain SELECT * FROM `temperatures` WHERE (device_id in (91,92,93,94,95,96,97,98,99) and created_at >= '2010-05-06 03:26:06');
+----+-------------+--------------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table        | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+--------------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | temperatures | ALL  | NULL          | NULL | NULL    | NULL | 1273561 | Using where |
+----+-------------+--------------+------+---------------+------+---------+------+---------+-------------+

该查询检查了所有的100万条数据,慢是理所当然的.
我们尝试加入索引:

mysql> create index idx_device_created_at on temperatures (device_id,created_at);

再来看看效果:

mysql> SELECT * FROM `temperatures` WHERE (device_id in (91,92,93,94,95,96,97,98,99) and created_at >= '2010-05-06 03:26:06');
70191 rows in set (1.67 sec)
mysql> explain SELECT * FROM `temperatures` WHERE (device_id in (91,92,93,94,95,96,97,98,99) and created_at >= '2010-05-06 03:26:06');
+----+-------------+--------------+-------+-----------------------+-----------------------+---------+------+-------+-------------+
| id | select_type | table        | type  | possible_keys         | key                   | key_len | ref  | rows  | Extra       |
+----+-------------+--------------+-------+-----------------------+-----------------------+---------+------+-------+-------------+
|  1 | SIMPLE      | temperatures | range | idx_device_created_at | idx_device_created_at | 14      | NULL | 89250 | Using where |
+----+-------------+--------------+-------+-----------------------+-----------------------+---------+------+-------+-------------+

就一个字：没有使用索引,崩溃阿！
比刚才还慢.
避免使用in 或者 or (or会导致扫表)

看下面一个查询:

mysql> SELECT * FROM `temperatures` WHERE (device_id = 182 and current = 1)  LIMIT 1;
+---------+-----------+------------+---------------------+---------------------+---------+---------------------+
| id      | device_id | temp_value | created_at          | updated_at          | current | time                |
+---------+-----------+------------+---------------------+---------------------+---------+---------------------+
| 1423040 |       182 |       16.5 | 2010-05-18 09:10:03 | 2010-05-18 09:10:03 |       1 | 2010-05-18 09:10:03 |
+---------+-----------+------------+---------------------+---------------------+---------+---------------------+
1 row in set (0.25 sec)
mysql> explain SELECT * FROM `temperatures` WHERE (device_id = 182 and current = 1)  LIMIT 1;
+----+-------------+--------------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table        | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+--------------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | temperatures | ALL  | NULL          | NULL | NULL    | NULL | 423269 | Using where |
+----+-------------+--------------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)

竟然检查了23269行,不慢才怪.
加上索引试试看:

mysql> create index idx_device_id_current on temperatures(device_id,current);
Query OK, 423051 rows affected (1 min 0.62 sec)
Records: 423051  Duplicates: 0  Warnings: 0

再看看查询速度:

mysql> SELECT * FROM `temperatures` WHERE (device_id = 182 and current = 1)  LIMIT 1;
+---------+-----------+------------+---------------------+---------------------+---------+---------------------+
| id      | device_id | temp_value | created_at          | updated_at          | current | time                |
+---------+-----------+------------+---------------------+---------------------+---------+---------------------+
| 1423040 |       182 |       16.5 | 2010-05-18 09:10:03 | 2010-05-18 09:10:03 |       1 | 2010-05-18 09:10:03 |
+---------+-----------+------------+---------------------+---------------------+---------+---------------------+
1 row in set (0.00 sec)
mysql> explain SELECT * FROM `temperatures` WHERE (device_id = 182 and current = 1)  LIMIT 1;
+----+-------------+--------------+------+-----------------------+-----------------------+---------+-------------+------+-------------+
| id | select_type | table        | type | possible_keys         | key                   | key_len | ref         | rows | Extra       |
+----+-------------+--------------+------+-----------------------+-----------------------+---------+-------------+------+-------------+
|  1 | SIMPLE      | temperatures | ref  | idx_device_id_current | idx_device_id_current | 7       | const,const |    1 | Using where |
+----+-------------+--------------+------+-----------------------+-----------------------+---------+-------------+------+-------------+
1 row in set (0.00 sec)

可以看到只检查了一行,速度当然快了