Menu

Subscribe

奇怪的mysql慢查询

on mysql, 数据库

线上问题

目前还没有未知根本缘由，线上跑了一个月左右，数据库这张表，大约有80万数据
当发生order by 和 limit 同时作为条件时，查询速度下降到3秒以上

表基本信息

首先利用 

pager cat /dev/null;  
nopager;

pager 只影响单次链接

介绍一下表的情况，id 是 唯一索引， rid(下面没用到) 是主键索引，status 是普通字段
查看表索引，可见 

mysql> show index from 10000_web \G;  
*************************** 1. row ***************************
        Table: 10000_web
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: rid
    Collation: A
  Cardinality: 680538
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment:  
*************************** 2. row ***************************
        Table: 10000_web
   Non_unique: 0
     Key_name: id
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 680538
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment:  
*************************** 3. row ***************************
        Table: 10000_web
   Non_unique: 0
     Key_name: 10000_web_id_unique
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 680538
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment:  
3 rows in set (0.00 sec)
具体耗时

具体数据如下 

// 发现下面非常耗时  
mysql> select * from 10000_web where status = 2 order by id limit 10;  
6 rows in set (3.12 sec)

// 但实际上 
mysql> select * from 10000_web where status = 2 order by id;  
6 rows in set (0.59 sec)

mysql> select * from 10000_web where status = 2  limit 10;  
6 rows in set (1.20 sec)

mysql> select * from  10000_web where status = 2;  
6 rows in set (1.20 sec)

// 同样查询，除了status不同
mysql> select * from 10000_web where status = 12 order by id limit 10;  
10 rows in set (0.00 sec)  

// 并且发现，单单排序所有数据，是很耗时的 
mysql> select * from 10000_web order by id;  
794368 rows in set (11.66 sec)

mysql> select * from 10000_web order by id limit 10;  
10 rows in set (0.00 sec)
对应explain解析
mysql> explain select * from 10000_web where status = 2 order by id limit 10;  
+----+-------------+-----------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table     | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-----------+-------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | 10000_web | index | NULL          | id   | 8       | NULL |   10 | Using where |
+----+-------------+-----------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from 10000_web where status = 2 order by id;  
+----+-------------+-----------+------+---------------+------+---------+------+--------+-----------------------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows   | Extra                       |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-----------------------------+
|  1 | SIMPLE      | 10000_web | ALL  | NULL          | NULL | NULL    | NULL | 680736 | Using where; Using filesort |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-----------------------------+
1 row in set (0.00 sec)

mysql> explain select * from 10000_web where status = 2  limit 10;  
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | 10000_web | ALL  | NULL          | NULL | NULL    | NULL | 680737 | Using where |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)  

mysql> explain select * from  10000_web where status = 2;  
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | 10000_web | ALL  | NULL          | NULL | NULL    | NULL | 680751 | Using where |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from 10000_web where status = 12 order by id limit 10;  
+----+-------------+-----------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table     | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-----------+-------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | 10000_web | index | NULL          | id   | 8       | NULL |   10 | Using where |
+----+-------------+-----------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from 10000_web order by id;  
+----+-------------+-----------+------+---------------+------+---------+------+--------+----------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows   | Extra          |
+----+-------------+-----------+------+---------------+------+---------+------+--------+----------------+
|  1 | SIMPLE      | 10000_web | ALL  | NULL          | NULL | NULL    | NULL | 680748 | Using filesort |
+----+-------------+-----------+------+---------------+------+---------+------+--------+----------------+
1 row in set (0.00 sec)

mysql> explain select * from 10000_web order by id limit 10;  
+----+-------------+-----------+-------+---------------+------+---------+------+------+-------+
| id | select_type | table     | type  | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-----------+-------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | 10000_web | index | NULL          | id   | 8       | NULL |   10 | NULL  |
+----+-------------+-----------+-------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)
结论

从结果上来看，同时使用了 where、order by、limit，触发了索引优化，避免了排序
每次从索引树中取一个当前最小的id，尝试满足where条件，满足则最终结果+1，并检验是否满足limit数量
这样得出一个现象 => 比整体sort要快得多、同时比全盘扫一遍要慢(因为status = 2都在数据库靠后地方)

新的问题

mysql> select * from 10000_web where status = 2 ;  
4 rows in set (1.19 sec)

mysql> select * from 10000_web where status = 2 order by lastopertime;  
4 rows in set (0.57 sec)

mysql> select * from 10000_web where status = 2 order by rid;  
4 rows in set (1.19 sec)

mysql> select * from 10000_web where status = 2 order by id;  
4 rows in set (0.58 sec)  


mysql> explain select * from 10000_web where status = 2 ;  
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | 10000_web | ALL  | NULL          | NULL | NULL    | NULL | 680777 | Using where |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.01 sec)

mysql> explain select * from 10000_web where status = 2 order by lastopertime;  
+----+-------------+-----------+------+---------------+------+---------+------+--------+-----------------------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows   | Extra                       |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-----------------------------+
|  1 | SIMPLE      | 10000_web | ALL  | NULL          | NULL | NULL    | NULL | 680777 | Using where; Using filesort |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-----------------------------+
1 row in set (0.00 sec)

mysql> explain select * from 10000_web where status = 2 order by rid;  
+----+-------------+-----------+-------+---------------+---------+---------+------+--------+-------------+
| id | select_type | table     | type  | possible_keys | key     | key_len | ref  | rows   | Extra       |
+----+-------------+-----------+-------+---------------+---------+---------+------+--------+-------------+
|  1 | SIMPLE      | 10000_web | index | NULL          | PRIMARY | 767     | NULL | 680777 | Using where |
+----+-------------+-----------+-------+---------------+---------+---------+------+--------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from 10000_web where status = 2 order by id;  
+----+-------------+-----------+------+---------------+------+---------+------+--------+-----------------------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows   | Extra                       |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-----------------------------+
|  1 | SIMPLE      | 10000_web | ALL  | NULL          | NULL | NULL    | NULL | 680777 | Using where; Using filesort |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-----------------------------+
1 row in set (0.00 sec)

注意 覆盖索引 covering index

索引

How MySQL Uses Indexes

Indexes are less important for queries on small tables, or big tables where report queries process most or all of the rows. When a query needs to access most of the rows, reading sequentially is faster than working through an index. Sequential reads minimize disk seeks, even if not all the rows are needed for the query. See Section 8.2.1.19, “Avoiding Full Table Scans” for details.

参考

一个容易被忽略的SQL调优技巧 --- order by字段到底要不要加入索引