玉面飞龙的BLOG

mysql的SQL优化器是CBO的，所以要依赖表上的统计信息，但统计信息内容又不如Oracle丰富，只有table.Rows,index.Cardinality等。

且对一些存储引擎，mysql能够自动分析某些信息，虽然有时候不够准确。怀疑MYSQL能否高效应付复杂的查询。

bench1为memory类型的表，有2个HASH类型的索引，PRIMARY和ix_id3

刚建立表,2个索引的Cardinality 都为0

mysql> insert into bench1 values(1,1,1,'a');
Query OK, 1 row affected (0.00 sec)

插入一行后,PRIMARY 显示Cardinality为1，ix_id3仍然是0;table.Rows 记录为1。

mysql> show indexes from bench1;
+--------+------------+----------+--------------+-------------+-----------+-------------+-------
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Cardinality | Null | Index_type |
+--------+------------+----------+--------------+-------------+-------------+------+------------+
| bench1 | 0 | PRIMARY | 1 | id | NULL | | HASH |
| bench1 | 0 | PRIMARY | 2 | id2 | 1 | | HASH |
| bench1 | 1 | ix_id3 | 1 | id3 | 0 | | HASH |
+--------+------------+----------+--------------+-------------+-------------+------+------------+
3 rows in set (0.00 sec)

mysql> analyze table bench1;
+-------------+---------+----------+----------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+-------------+---------+----------+----------------------------------------------------------+
| test.bench1 | analyze | note | The storage engine for the table doesn't support analyze |
+-------------+---------+----------+----------------------------------------------------------+
1 row in set (0.00 sec)

似乎对于memory DB无法得到ix_id3的Cardinality信息？

参考

analyze-this

analyze table

myisam-index-statistics