mariadb - mariadb 5.5 上的异常“flat, BNL join”

Question

我有两个 mariadb 5.5 实例，每个实例都有完全相同的数据和 my.cnf 参数。（实际上，我已将旧服务器上的所有文件复制到新服务器上。）

当我运行查询时，查询速度很快（< 1 秒）并向我显示查询解释如下...（主机#1）

+------+-------------+-----------------+-------+---------------+---------+---------+------------------------------------------------------------------------------------------------------------------------------------------------------+--------+----------------------------------------------+
| id   | select_type | table           | type  | possible_keys | key     | key_len | ref                                                                                                                                                  | rows   | Extra                                        |
+------+-------------+-----------------+-------+---------------+---------+---------+------------------------------------------------------------------------------------------------------------------------------------------------------+--------+----------------------------------------------+
|    1 | PRIMARY     | TABLE1          | index | NULL          | PRIMARY | 130     | NULL                                                                                                                                                 |   9792 | Using where; Using index                     |
|    1 | PRIMARY     | <derived2>      | ref   | key0          | key0    | 135     | testdb.TABLE1.COL1,testdb.TABLE1.COL2,testdb.TABLE1.COL3,testdb.TABLE1.COL4,testdb.TABLE1.COL5                                                       |     30 |                                              |
|    2 | DERIVED     | TABLE2          | range | PRIMARY       | PRIMARY | 8       | NULL                                                                                                                                                 | 299012 | Using where; Using temporary; Using filesort |
+------+-------------+-----------------+-------+---------------+---------+---------+------------------------------------------------------------------------------------------------------------------------------------------------------+--------+----------------------------------------------+

另一个长时间运行（超过 15 秒）并向我展示了不同的解释，如下所示......（主机＃2）

+------+-------------+-----------------+-------+---------------+-------------------+---------+------+--------+-------------------------------------------------+
| id   | select_type | table           | type  | possible_keys | key               | key_len | ref  | rows   | Extra                                           |
+------+-------------+-----------------+-------+---------------+-------------------+---------+------+--------+-------------------------------------------------+
|    1 | PRIMARY     | TABLE1          | index | NULL          | PRIMARY           | 130     | NULL |   8123 | Using index                                     |
|    1 | PRIMARY     | <derived2>      | ALL   | NULL          | NULL              | NULL    | NULL | 294954 | Using where; Using join buffer (flat, BNL join) |
|    2 | DERIVED     | TABLE2          | range | PRIMARY       | PRIMARY           | 8       | NULL | 294954 | Using where; Using temporary; Using filesort    |
+------+-------------+-----------------+-------+---------------+-------------------+---------+------+--------+-------------------------------------------------+

当我不小心设置derived_with_keys到off主机#1 时，结果与主机#2 相同，我认为主机#1 选择derived_with_keys了主机#2 没有的操作。

如何强制我的新服务器使用derived_with_keys？

任何意见，将不胜感激。

标签： mariadb

Answer 1

问题解决了。

有两个不同的字符集。（UTF8 和 LATIN1）

ALTER TABLE TABLE2 CONVERT TO CHARACTER SET UTF8正常工作后。

谢谢里克詹姆斯。