mysql 性能问题

dahai01

1，
slave
mysql  Ver 14.12 Distrib 5.0.45, for unknown-linux-gnu (x86_64) using readline 5.0
2cpu
4G ram



SELECT order_id FROM T_ORDER WHERE  state!=10020 and state!=10001 and pay_type=10300
  16144 rows in set (0.50 sec)
  +----+-------------+---------+------+-------------------+---------+---------+-------+--------+-------------+
| id | select_type | table   | type | possible_keys     | key     | key_len | ref   | rows   | Extra       |
+----+-------------+---------+------+-------------------+---------+---------+-------+--------+-------------+
|  1 | SIMPLE      | T_ORDER | ref  | state_idx,PAY_IDX | PAY_IDX | 4       | const | 228000 | Using where |
+----+-------------+---------+------+-------------------+---------+---------+-------+--------+-------------+




SELECT order_id FROM T_ORDER WHERE  state!=10020 and state!=10001
  101667 rows in set (0.09 sec)
  
+----+-------------+---------+-------+---------------+-----------+---------+------+--------+--------------------------+
| id | select_type | table   | type  | possible_keys | key       | key_len | ref  | rows   | Extra                    |
+----+-------------+---------+-------+---------------+-----------+---------+------+--------+--------------------------+
|  1 | SIMPLE      | T_ORDER | range | state_idx     | state_idx | 5       | NULL | 145471 | Using where; Using index |
+----+-------------+---------+-------+---------------+-----------+---------+------+--------+--------------------------+



MASTER
mysql  Ver 14.12 Distrib 5.0.51a, for redhat-linux-gnu (x86_64) using readline 5.0
4cpu
8G ram

explan 结果和上面一致

SELECT order_id FROM T_ORDER WHERE  state!=10020 and state!=10001 and pay_type=10300
16141 rows in set (0.75 sec)

SELECT order_id FROM T_ORDER WHERE  state!=10020 and state!=10001
101664 rows in set (0.13 sec)

没有负载差异。记录有50W左右。


为啥就没有slave 快呢？ 都是innodb .
但是下面情况

slave
SELECT order_id FROM T_ORDER WHERE buyer='dahai';
Empty set (0.88 sec)
+----+-------------+---------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+---------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | T_ORDER | ALL  | NULL          | NULL | NULL    | NULL | 601630 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+--------+-------------+


master
SELECT order_id FROM T_ORDER WHERE buyer='dahai';
Empty set (0.79 sec)

再不使用索引的情况下就master 快。。

还有master 用的是独享表空间。slave 共享表空间

这是为啥？

2，order_by 索引问题
还有mysql order by 的时候索引很难用到。各位有什么好办法

select * from a where a=xx order by a (a 有索引)  这样情况能用到索引。
但是 where 后面 a=xx and b=yy(b 也有索引) 这时候再order by a  就用不到索引。要建联合索引 a,b 可以。
如果 我的条件涉及很多列 几个索引要order by a 的话就要建立很多列的联合索引才行嘛？

请教下
谢谢

[ 本帖最后由 dahai01 于 2008-10-24 17:47 编辑 ]