日期:2014-05-16  浏览次数:20653 次

请问我的设置的索引有问题吗
索引设置有
名 栏位名 索引类型
indexs1 number, stattuima, statsizi Normal
indexs1 uid, printstat, id Normal
indexs1 stattuima, reportstat Normal
indexs1 stattuima, pid5, classid Normal
indexs1 pid5, dingmoney Normal
indexs1 uid Normal

dingdanbiao.MYD 这个文件到240M
dingdanbiao.MYI 这个文件到130M

因为经常查询 WHERE number='1212' and stattuima=0 and statsizi='';组合,一旦量大(都是100万笔数据)都会有超出三秒的。
请教大家我的索引设置是否合理或有什么方法优化的呢。

explain SELECT uid,pid2,pid3,pid4,pid5,money,om1,om2,om3,om4,om5 FROM dingdanbiao WHERE number='1212' and stattuima=0 and statsizi='';

+----+-------------+------------------+------+-------------------------+---------+---------+-------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------------+------+-------------------------+---------+---------+-------------------+------+-------------+
| 1 | SIMPLE | dingdanbiao | ref | indexs1,indexs3,indexs4 | indexs1 | 16 | const,const,const | 2 | Using where |
+----+-------------+------------------+------+-------------------------+---------+---------+-------------------+------+-------------+
1 row in set

mysql> show index from dingdanbiao;
+------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| dingdanbiao | 0 | PRIMARY | 1 | id | A | 92 | NULL | NULL | | BTREE | |
| dingdanbiao | 1 | indexs1 | 1 | number | A | NULL | NULL | NULL | | BTREE | |
| dingdanbiao | 1 | indexs1 | 2 | stattuima | A | NULL | NULL | NULL | YES | BTREE | |
| dingdanbiao | 1 | indexs1 | 3 | statsizi | A | NULL | NULL | NULL | YES | BTREE | |
| dingdanbiao | 1 | indexs2 | 1 | uid | A | NULL | NULL | NULL | YES | BTREE | |
| dingdanbiao | 1 | indexs2 | 2 | printstat | A | NULL | NULL | NULL | YES | BTREE | |
| dingdanbiao | 1 | indexs2 | 3 | id | A | NULL | NULL | NULL | | BTREE | |
| dingdanbiao | 1 | indexs3 | 1 | stattuima | A | NULL | NULL | NULL | YES | BTREE | |
| dingdanbiao | 1 | indexs3 | 2 | reportstat | A | NULL | NULL | NULL | YES | BTREE | |
| dingdanbiao | 1 | indexs4 | 1 | stattuima | A | NULL | NULL | NULL | YES | BTREE | |
| dingdanbiao | 1 | indexs4 | 2 | pid5 | A | NULL | NULL | NULL | YES | BTREE | |
| dingdanbiao | 1 | indexs4 | 3 | classid | A | NULL | NULL | NULL | YES | BTREE | |
| dingdanbiao | 1 | indexs5 | 1 | pid5 | A | NULL | NULL | NULL | YES | BTREE | |
| dingdanbiao