关于MariaDB 5.5.27数据库中的hash join
D:mariadb-5.5.27sqlDebug>more my.ini
[mysqld]
#datadir=D:mariadb-5.5.27sqldata
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 t1G
*************************** 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 t3G
*************************** 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
关于MariaDB 5.5.27数据库中的hash join
