关于MariaDB5.5.27数据库中的hashjoin

D:\mariadb-5.5.27\sql\Debug>more my.ini
[mysqld]
#datadir=D:\mariadb-5.5.27\sql\data
innodb_file_per_table
optimizer_switch='index_condition_pushdown=on'
optimizer_switch='mrr=on'
optimizer_switch='mrr_sort_keys=on'
optimizer_switch='mrr_cost_based=off'
mrr_buffer_size=32M
optimizer_switch='join_cache_incremental=on'
optimizer_switch='join_cache_hashed=on'
optimizer_switch='join_cache_bka=on'
join_cache_level=4
#join_buffer_size=32M
#join_buffer_space_limit=32M

上面参数只试了join_cache_level=4是一定要有的，最初配置没加这个，就没试出来hash join。

MariaDB [tm]> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `name_1` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
MariaDB [tm]> show create table t3\G
*************************** 1. row ***************************
       Table: t3
Create Table: CREATE TABLE `t3` (
  `t3id` int(11) NOT NULL AUTO_INCREMENT,
  `t3name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`t3id`)
) ENGINE=InnoDB AUTO_INCREMENT=391152 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
MariaDB [tm]> select count(*) from t1;
+----------+
| count(*) |
+----------+
|        6 |
+----------+
1 row in set (0.00 sec)
MariaDB [tm]> select count(*) from t3;
+----------+
| count(*) |
+----------+
|   262144 |
+----------+
1 row in set (0.94 sec)

MariaDB [tm]> explain select t3.t3name from t1,t3 where t1.name=t3.t3name;
+------+-------------+-------+----------+---------------+-----------+---------+------------+--------+--------------------------------------------------+
| id   | select_type | table | type     | possible_keys | key       | key_len | ref        | rows   | Extra                                            |
+------+-------------+-------+----------+---------------+-----------+---------+------------+--------+--------------------------------------------------+
|    1 | SIMPLE      | t1    | ALL      | NULL          | NULL      | NULL    | NULL       |      6 | Using where                                      |
|    1 | SIMPLE      | t3    | hash_ALL | NULL          | #hash#$hj | 104     | tm.t1.name | 262178 | Using where; Using join buffer (flat, BNLH join) |
+------+-------------+-------+----------+---------------+-----------+---------+------------+--------+--------------------------------------------------+
2 rows in set (0.00 sec)

BNLH就是Block Nested Loop Hash