请问个SQL语句应该如何优化？

Question

我用下面语句运行的时候：desc select id,buyer_id,buyer_name,seller_id,seller_name,buyer_obj,seller_obj,buyer_money,seller_money,time from log_trade where (buyer_id=521 or seller_id=521) order by time desc limit 0 ,20;得出如下结果：+----+-------------+-----------+------+---------------+------+---------+------+------+-----------------------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows | Extra                       |
+----+-------------+-----------+------+---------------+------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | log_trade | ALL  | buy,sell      | NULL | NULL    | NULL |    4 | Using where; Using filesort |
+----+-------------+-----------+------+---------------+------+---------+------+------+-----------------------------+ 注意哦：Extra这一列有Using filesort 额外排序，显然这个SQL语句有问题我做的索引如下(做了三个索引)：
索引名       栏位名
buy          buy_id,buyer_name
sell          seller_id,seller_name
time        time请问我这样做对了吗？谢谢

Answer 1

贴出你的 show index from log_trade

Answer 2

buyer_id加索引
seller_id加索引

Answer 3

buyer_id、seller_id有多少种值
在time、buyer_id、seller_id上建立联合索引试试

Answer 4

+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table     | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| log_trade |          0 | PRIMARY  |            1 | id          | A         |           2 | NULL     | NULL   |      | BTREE      |         |
| log_trade |          1 | time     |            1 | time        | A         |           2 | NULL     | NULL   |      | BTREE      |         |
| log_trade |          1 | sell     |            1 | seller_id   | A         |           2 | NULL     | NULL   |      | BTREE      |         |
| log_trade |          1 | buy      |            1 | buyer_id    | A         |           2 | NULL     | NULL   |      | BTREE      |         |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
以上是索引

Answer 5

试过了，还是全盘扫描;mysql> desc select id,buyer_id,buyer_name,seller_id,seller_name,buyer_obj,seller_obj,buyer_money,seller_money,time from log_trade where (buyer_id=95 or seller_id=95) order by time desc limit 0 ,20;
+----+-------------+-----------+------+---------------+------+---------+------+------+-----------------------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows | Extra                       |
+----+-------------+-----------+------+---------------+------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | log_trade | ALL  | buy           | NULL | NULL    | NULL |    4 | Using where; Using filesort |
+----+-------------+-----------+------+---------------+------+---------+------+------+-----------------------------+
mysql> show index from log_trade;
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table     | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| log_trade |          0 | PRIMARY  |            1 | id          | A         |           2 | NULL     | NULL   |      | BTREE      |         |
| log_trade |          1 | buy      |            1 | buyer_id    | A         |           2 | NULL     | NULL   |      | BTREE      |         |
| log_trade |          1 | buy      |            2 | seller_id   | A         |           2 | NULL     | NULL   |      | BTREE      |         |
| log_trade |          1 | buy      |            3 | time        | A         |           2 | NULL     | NULL   |      | BTREE      |         |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

Answer 6

你的表中不过2条记录！
MYSQL认为与其花时间去走索引，不如直接全表扫描。

Answer 7

Mysql这么智能的！！
那一般要插入多少条记录Mysql会走索引？

Answer 8

select id,buyer_id,buyer_name,seller_id,seller_name,buyer_obj,seller_obj,buyer_money,seller_money,time from log_trade where (buyer_id=521 or seller_id=521) order by time desc limit 0 ,20;
1、你建的索引本身就有问题，应该建立<buyer_id,time>,<seller_id,time>
2、需要将(buyer_id=521 or seller_id=521) 条件肯定是不走索引的，可以将其拆分两条sql用union all来合并