请问我的设置的索引有问题吗

Question

索引设置有
名             栏位名                             索引类型
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                                Normaldingdanbiao.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 setmysql> 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 |          1 | indexs5  |            2 | dingmoney   | A         | NULL        | NULL     | NULL   | YES  | BTREE      |         |
| dingdanbiao |          1 | indexs6  |            1 | uid         | A         | NULL        | NULL     | NULL   | YES  | BTREE      |         |
+------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
15 rows in set

Answer 1

number='1212' and stattuima=0 and statsizi
这三个字段加组合索引

Answer 2

ACMAIN 您好，请看下面是139万条的结果
explain SELECT uid,pid2,pid3,pid4,pid5,money,om2,om3,om4,om5 FROM dingdanbiao WHERE number='3067' 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 |  118 | Using where | 
+----+-------------+----------------+------+-------------------------+---------+---------+-------------------+------+-------------+
1 row in set (0.09 sec)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         |     1397272 |     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 |          1 | indexs5  |            2 | dingmoney  | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         | 
| dingdanbiao |          1 | indexs6  |            1 | uid         | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         | 
+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
15 rows in set (0.01 sec)

Answer 3

| 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 | |  
怎么都是空？ANALYZE TABLE 一下然后看一下统计更新结果。

Answer 4

出来的结果是
ANALYZE TABLE dingdanbiao;
+------------------------------------+---------+----------+----------+
| Table                              | Op      | Msg_type | Msg_text |
+------------------------------------+---------+----------+----------+
| carshujuku.dingdanbiao             | analyze | status   | OK       | 
+------------------------------------+---------+----------+----------+
1 row in set (1 min 21.64 sec)

Answer 5

在执行show index from dingdanbiao;只有Cardinality有值
+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| 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         |     1397272 |     NULL | NULL   |      | BTREE      |         | 
|      dingdanbiao |          1 | indexs1  |            1 | number      | A         |       14864 |     NULL | NULL   |      | BTREE      |         | 
|      dingdanbiao |          1 | indexs1  |            2 | stattuima   | A         |       26870 |     NULL | NULL   | YES  | BTREE      |         | 
|      dingdanbiao |          1 | indexs1  |            3 | statsizi    | A         |       27397 |     NULL | NULL   | YES  | BTREE      |         | 
|      dingdanbiao |          1 | indexs2  |            1 | uid         | A         |        1731 |     NULL | NULL   | YES  | BTREE      |         | 
|      dingdanbiao |          1 | indexs2  |            2 | printstat   | A         |        2941 |     NULL | NULL   | YES  | BTREE      |         | 
|      dingdanbiao |          1 | indexs2  |            3 | id          | A         |     1397272 |     NULL | NULL   |      | BTREE      |         | 
|      dingdanbiao |          1 | indexs3  |            1 | stattuima   | A         |           2 |     NULL | NULL   | YES  | BTREE      |         | 
|      dingdanbiao |          1 | indexs3  |            2 | reportstat  | A         |           2 |     NULL | NULL   | YES  | BTREE      |         | 
|      dingdanbiao |          1 | indexs4  |            1 | stattuima   | A         |           2 |     NULL | NULL   | YES  | BTREE      |         | 
|      dingdanbiao |          1 | indexs4  |            2 | pid5        | A         |         799 |     NULL | NULL   | YES  | BTREE      |         | 
|      dingdanbiao |          1 | indexs4  |            3 | classid     | A         |        6409 |     NULL | NULL   | YES  | BTREE      |         | 
|      dingdanbiao |          1 | indexs5  |            1 | pid5        | A         |         453 |     NULL | NULL   | YES  | BTREE      |         | 
|      dingdanbiao |          1 | indexs5  |            2 | awardmoney  | A         |        2193 |     NULL | NULL   | YES  | BTREE      |         | 
|      dingdanbiao |          1 | indexs6  |            1 | uid         | A         |        1731 |     NULL | NULL   | YES  | BTREE      |         | 
+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
15 rows in set (0.00 sec)

Answer 6

现在查询速度如何？ 如果慢，再贴一下EXPLAIN

Answer 7

现在没有人，测不出查询速度。
如果高峰期执行ANALYZE TABLE dingdanbiao;对索引有优化的效果吗？还有，Cardinality 很多null是什么影响到的呢

Answer 8

ANYLYZE 后，数据好像没有那么多超过3秒的了。因为我dingdanbiao每天都创建一个的。但是创建的时候Cardinality 还是有null呢。必须要用ANYLYZE 后才可以。