Basic tutorial on using join statements in MySQL and the impact of fields on performance _ MySQL

This article mainly introduces the basic use of join statements in MySQL and the impact of fields on performance. It provides an example to observe the performance differences caused by the encoding of different field character sets used by join, for more information, see Basic use of join statements

SQL (MySQL) JOIN is used to obtain data from these tables based on the relationship between fields in two or more tables.

JOIN is usually used with the ON keyword. the basic syntax is as follows:

... FROM table1 INNER | LEFT | right join table2 ON conditiona
Table1 is usually called the left table, and table2 is called the right table. The ON keyword is used to set matching conditions and specify the rows in the result set. If you need to specify other conditions, you can add the WHERE condition or LIMIT to LIMIT the number of records returned.

The following describes the usage of MySQL JOIN using the most common two-table JOIN. For more information about multi-table JOIN, see MySQL JOIN multi-table.

MySQL JOIN classification

JOIN is divided into the following three categories by function:

1. Inner join: Records of the two tables with connection matching relationships are obtained.

2. Left join: The full record of the LEFT table (table1) is obtained, that is, the right table (table2) does not have a matched record.

3. Right join (right join): opposite to left join, the full record of the RIGHT table (table2) is obtained, that is, the LEFT table (table1) does not match the corresponding record.

About MySQL FULL JOIN

MySQL does not provide full join (full join) in the SQL standard: both table records are retrieved, regardless of whether there are corresponding records for each other. To solve this problem, you can use the UNION keyword to merge left join and right join to simulate full join.

MySQL INNER JOIN

Inner join is used to obtain records with connection matching relationships between two tables. The following are two original data tables:

The user of the document in the article table is associated with the user table through the uid field. It is not difficult to find that users with uid = 3 have not published any articles; in the article, aid = 4 cannot find the corresponding record in the uid table (it may be that the user is deleted but the article to which the user belongs is retained ).

We will list the data of the articles used that correspond to the users one by one.

SELECT... Inner join... The ON statement is as follows:

SELECT article.aid,article.title,user.username FROM article INNER JOIN user ON article.uid = user.uid

The returned query result is as follows:

For inner join, it is equivalent to the following SQL statement:

SELECT article.aid,article.title,user.username FROM article,user WHERE article.uid = user.uid

CROSS JOIN

Cross join is a cross join. if ON is not specified:

SELECT article.aid,article.title,user.username FROM article CROSS JOIN user

The result is the product of the two joined data tables, that is, Cartesian product.

In fact, in MySQL (only MySQL), cross join and inner join have the same performance. The results obtained without specifying the ON condition are Cartesian products, otherwise, the system returns the exact matching results for the two tables.

Inner join and cross join can omit the INNER or CROSS keyword. Therefore, the following SQL statements have the same effect:

Flat view printing?

... FROM table1 INNER JOIN table2... FROM table1 CROSS JOIN table2... FROM table1 JOIN table2

Effect of join Field character set encoding on performance

Let's take a look at the sample code:

Create a UTF-8 encoded table t1:

CREATE TABLE IF NOT EXISTS `t1` ( `name` varchar(50) NOT NULL DEFAULT '', KEY `name` (`name`)) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Insert some data at will, and the number is relatively large. the experiment results will be clearer later, and you will be lazy to construct a random string insertion statement.

insert into t1(name) select concat(char(round((rand())*25)+97),char(round((rand())*25)+65),char(round((rand())*25)+65),char(round((rand())*25)+97),char(round((rand())*25)+65),char(round((rand())*25)+65),char(round((rand())*25)+97),char(round((rand())*25)+65))

Insert a record every time, and write a loop with the script you are familiar with (python, php, shell, etc.), and execute more than 10 thousand times.

Copy the table into a new table T2. delete a part of the data. about 1000 data records are allowed. (PhpMyAdmin is recommended)

Copy t2 to t3 and change the field to gb2312 encoding.

Use a left join statement to write a statement to find out which records are missing from T2.

The statement is as follows:

SELECT SQL_NO_CACHE t1.name, t2.nameFROM t1LEFT JOIN t2 ON t1.name = t2.nameWHERE t2.name IS NULL LIMIT 0 , 30

Note: add SQL _NO_CACHE to disable mysql cache.

First look at the encoded t2 table. the execution result in phpMyAdmin is as follows:

Display row 0-29 (1,129 total, query takes 0.0010 seconds)
The average time consumption is about 0.0010 seconds.

SELECT SQL_NO_CACHE t1.name, t3.nameFROM t1LEFT JOIN t3 ON t1.name = t3.nameWHERE t2.name IS NULL LIMIT 0 , 30

PhpMyAdmin execution result:

Display row 0-29 (30 total, query takes 0.1871 seconds)
The difference is two orders of magnitude!

Query statement explanation:

The above is the basic tutorial for using the join statement in MySQL and the impact of its fields on the performance _ MySQL content. For more information, see The PHP Chinese website (www.php1.cn )!