- 论坛徽章:
- 9
|
首先说下,filesort为什么不好。原因有二:
1. filesort会创建一个使用sort_buffer_size的线程,消耗额外缓存
2. filesort会额外进行数据读取。如果data_size大于max_length_for_sort_data,则需要多次读取。
You can read the filesort algorithm
here.
如何避免?
举例!!
出现filesort!!!
mysql> explain SELECT * FROM ABCD WHERE a=1 AND b=1 ORDER BY c DESC, d ASC\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: ABCD
type: ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: const,const
rows: 2
Extra: Using where; Using index; Using filesort
1 row in set (0.00 sec)使用多个select进行避免!!!
SELECT * FROM ABCD WHERE A=? AND B=? ORDER BY C DESC
explain SELECT * FROM ABCD WHERE a=1 AND b=1 ORDER BY c DESC LIMIT 10\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: ABCD
type: ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: const,const
rows: 2
Extra: Using where; Using index
1 row in set (0.00 sec)
---------------------------------------------------------
FOREACH($C_parent as $i => $c_id) {
$C_parent[$i] = SELECT SQL_CALC_FOUND_ROWS * FROM ABCD WHERE A=? AND B=? AND C=$c_id ORDER BY D ASC LIMIT 1;
}
虽然使用了多个select,但是从效率上来看还是有所提高。因此尽量避免filesort
本文来自ChinaUnix博客,如果查看原文请点:http://blog.chinaunix.net/u3/90603/showart_1968587.html |
|