mysql join查询及原理

CREATE DATABASE mysql_join_demo;

CREATE TABLE users(
 id INT NOT NULL AUTO_INCREMENT,
 name VARCHAR(100) NOT NULL DEFAULT '',
 pass VARCHAR(100) NOT NULL DEFAULT '',
 age INT NOT NULL DEFAULT 0,
 address VARCHAR(100) NOT NULL DEFAULT '',
 PRIMARY KEY(id)
);

CREATE TABLE orders(
 id INT NOT NULL AUTO_INCREMENT,
 name VARCHAR(100) NOT NULL DEFAULT '',
 user_id INT NOT NULL DEFAULT 0,
 price FLOAT NOT NULL DEFAULT 0,
 PRIMARY KEY(id)
);

INSERT INTO users(name,pass,age,address)
VALUES
('neo','123',23,'Beijing'),
('neo','123',23,'Beijing'),
('neo','123',23,'Beijing'),
('neo','123',23,'Beijing');

INSERT INTO orders(name,user_id,price)
VALUES('order-1',1,12.3),('order-2',1,12.4),('order-3',1,12.4),('order-4',2,12.5);

1.交叉查询
交叉查询就是将多个表做笛卡尔积,也就是一个交叉乘积

 

mysql> SELECT * FROM users,orders;
+----+------+------+-----+---------+----+---------+---------+-------+

| id | name | pass | age | address | id | name | user_id | price |
+----+------+------+-----+---------+----+---------+---------+-------+

| 1 | neo | 123 | 22 | Beijing | 1 | order-1 | 1 | 12.3 |
| 1 | neo | 123 | 22 | Beijing | 2 | order-2 | 1 | 12.4 |
| 1 | neo | 123 | 22 | Beijing | 3 | order-3 | 1 | 12.4 |
| 1 | neo | 123 | 22 | Beijing | 4 | order-4 | 2 | 12.5 |
| 2 | neo | 123 | 23 | Beijing | 1 | order-1 | 1 | 12.3 |
| 2 | neo | 123 | 23 | Beijing | 2 | order-2 | 1 | 12.4 |
| 2 | neo | 123 | 23 | Beijing | 3 | order-3 | 1 | 12.4 |
| 2 | neo | 123 | 23 | Beijing | 4 | order-4 | 2 | 12.5 |
| 3 | neo | 123 | 23 | Beijing | 1 | order-1 | 1 | 12.3 |
| 3 | neo | 123 | 23 | Beijing | 2 | order-2 | 1 | 12.4 |
| 3 | neo | 123 | 23 | Beijing | 3 | order-3 | 1 | 12.4 |
| 3 | neo | 123 | 23 | Beijing | 4 | order-4 | 2 | 12.5 |
| 4 | neo | 123 | 23 | Beijing | 1 | order-1 | 1 | 12.3 |
| 4 | neo | 123 | 23 | Beijing | 2 | order-2 | 1 | 12.4 |
| 4 | neo | 123 | 23 | Beijing | 3 | order-3 | 1 | 12.4 |
| 4 | neo | 123 | 23 | Beijing | 4 | order-4 | 2 | 12.5 |
| 5 | neo | 123 | 23 | Beijing | 1 | order-1 | 1 | 12.3 |
| 5 | neo | 123 | 23 | Beijing | 2 | order-2 | 1 | 12.4 |
| 5 | neo | 123 | 23 | Beijing | 3 | order-3 | 1 | 12.4 |
| 5 | neo | 123 | 23 | Beijing | 4 | order-4 | 2 | 12.5 |
+

交叉查询+两个表的对等条件＝inner join

 

mysql> SELECT * FROM users u,orders o WHERE u.id=o.user_id;
+----+------+------+-----+---------+----+---------+---------+-------+

| id | name | pass | age | address | id | name | user_id | price |
+----+------+------+-----+---------+----+---------+---------+-------+

| 1 | neo | 123 | 22 | Beijing | 1 | order-1 | 1 | 12.3 |
| 1 | neo | 123 | 22 | Beijing | 2 | order-2 | 1 | 12.4 |
| 1 | neo | 123 | 22 | Beijing | 3 | order-3 | 1 | 12.4 |
| 2 | neo | 123 | 23 | Beijing | 4 | order-4 | 2 | 12.5 |
+----+------+------+-----+---------+----+---------+---------+-------+


mysql> SELECT * FROM users u INNER JOIN orders o ON u.id=o.user_id;
+----+------+------+-----+---------+----+---------+---------+-------+

| id | name | pass | age | address | id | name | user_id | price |
+----+------+------+-----+---------+----+---------+---------+-------+

| 1 | neo | 123 | 22 | Beijing | 1 | order-1 | 1 | 12.3 |
| 1 | neo | 123 | 22 | Beijing | 2 | order-2 | 1 | 12.4 |
| 1 | neo | 123 | 22 | Beijing | 3 | order-3 | 1 | 12.4 |
| 2 | neo | 123 | 23 | Beijing | 4 | order-4 | 2 | 12.5 |
+

left join
left join主要以from后面的表为基础表,对于join的表没有与其对应的补NULL

 

mysql> SELECT * FROM users u LEFT JOIN orders o ON u.id=o.user_id;
+----+------+------+-----+---------+------+---------+---------+-------+

| id | name | pass | age | address | id | name | user_id | price |
+----+------+------+-----+---------+------+---------+---------+-------+

| 1 | neo | 123 | 22 | Beijing | 1 | order-1 | 1 | 12.3 |
| 1 | neo | 123 | 22 | Beijing | 2 | order-2 | 1 | 12.4 |
| 1 | neo | 123 | 22 | Beijing | 3 | order-3 | 1 | 12.4 |
| 2 | neo | 123 | 23 | Beijing | 4 | order-4 | 2 | 12.5 |
| 3 | neo | 123 | 23 | Beijing | NULL | NULL | NULL | NULL |
| 4 | neo | 123 | 23 | Beijing | NULL | NULL | NULL | NULL |
| 5 | neo | 123 | 23 | Beijing | NULL | NULL | NULL | NULL |
+

right join不会补齐，而是将不存在的NULL值去掉

 

mysql> SELECT * FROM users u RIGHT JOIN orders o ON u.id=o.user_id;
+------+------+------+------+---------+----+---------+---------+-------+

| id | name | pass | age | address | id | name | user_id | price |
+------+------+------+------+---------+----+---------+---------+-------+

| 1 | neo | 123 | 22 | Beijing | 1 | order-1 | 1 | 12.3 |
| 1 | neo | 123 | 22 | Beijing | 2 | order-2 | 1 | 12.4 |
| 1 | neo | 123 | 22 | Beijing | 3 | order-3 | 1 | 12.4 |
| 2 | neo | 123 | 23 | Beijing | 4 | order-4 | 2 | 12.5 |
+

mysql的join在添加条件的时候可以使用using关键字，前提是两个关联字段必须一样-_-!

mysql的join原理

mysql join采用的算法就是Nested-Loop Join 传送门(http://dev.mysql.com/doc/refman/5.0/en/nested-loop-joins.html),

例如一个SQL是这样的select * from users join orders,这个东西在mysql内部的实现的伪代码

FOR ORDER IN orders{
 FOR USER IN users{
    send USER+ORDER
 }
}

这里还需要描述一个join_buffer_size,这个值定义当执行join操作的时候系统分配的内存

上面的查询其实已经使用了join_buffer,看执行计划里面Extra这个值Using join buffer

 

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM users JOIN orders;
+----+-------------+--------+------+---------------+------+---------+------+------+-------------------+

| id | select_type | TABLE | TYPE | possible_keys | KEY | key_len | REF | ROWS | Extra |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------------+

| 1 | SIMPLE | orders | ALL | NULL | NULL | NULL | NULL | 4 | |
| 1 | SIMPLE | users | ALL | NULL | NULL | NULL | NULL | 5 | USING JOIN buffer |
+

(这里预留一个问题，为什么以orders表做了基表?)

join buffer使用就是将外层的查询结果放置在一片内存里，这块内存就是join buffer,大小就是系统启动时候设置的join_buffer_size

1.join buffer在join的查询为ALL,index,range的时候
2.一个语句可能用到多个join buffer
3.join buffer在语句执行完的时候被释放

 

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM users JOIN orders JOIN users u;
+----+-------------+--------+------+---------------+------+---------+------+------+-------------------+

| id | select_type | TABLE | TYPE | possible_keys | KEY | key_len | REF | ROWS | Extra |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------------+

| 1 | SIMPLE | users | ALL | NULL | NULL | NULL | NULL | 5 | |
| 1 | SIMPLE | orders | ALL | NULL | NULL | NULL | NULL | 5 | USING JOIN buffer |
| 1 | SIMPLE | u | ALL | NULL | NULL | NULL | NULL | 5 | USING JOIN buffer |
+

为什么用orders做基表，因为orders表里面只有4条记录，而users表里面有5条记录，mysql的优化器在优化的时候，会以扫描记录数量的多少来决定以哪个表作为基表

mysql> INSERT INTO orders(name,user_id) VALUES('order-5',2);
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM users JOIN orders;
+----+-------------+--------+------+---------------+------+---------+------+------+-------------------+

| id | select_type | TABLE | TYPE | possible_keys | KEY | key_len | REF | ROWS | Extra |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------------+

| 1 | SIMPLE | users | ALL | NULL | NULL | NULL | NULL | 5 | |
| 1 | SIMPLE | orders | ALL | NULL | NULL | NULL | NULL | 5 | USING JOIN buffer |
+

参考:
http://dev.mysql.com/doc/refman/5.0/en/nested-loop-joins.html

http://www.mysqlperformanceblog.com/2010/07/05/how-is-join_buffer_size-allocated/