MySQL multi-table stochastic query optimization scheme

I have previously introduced the most is a single table random query optimization, today to see a webmaster share a multiple table random query optimization of some methods and programs, we will take a look at the next.

This article mainly discusses how to implement the random query of MySQL, multiple table random query. The implementation method of randomly fetching a record in MySQL.

Our usual query is that there is no where or where fields>2, so that only one or more of the conditions can be removed, and if the condition is unchanged (for example, 2), the result will remain unchanged.

So how to implement the random query? I have two ways of doing it.

　　Method one, the data table is not recorded in the case:

SELECT * from ' table '

Find out all the list of records and then Array_rand () The key of an array of results randomly. Consecutive keys can use Mt_rand (1, Count ($list)); Why not use Rand instead of Mt_rand? Because Mt_rand is 4 times times faster than Rand.

In this case, the entire list is found, stored in the memcache cache or Redis NoSQL, and the next time the result set is taken out without the need to check the table. But when the volume of data exceeds the million levels, it is difficult to take out the list.

　　Method Two: Using SQL statements randomly

The MySQL function, rand (), produces a decimal number between 0-1, and Max (' ID ') can get the largest ID in the table. Then MAX (' ID ') * RAND () can fetch all the IDs in the table. OK, look at the statement.

SELECT * from ' table ' WHERE ' id ' > (select RAND () * (select MAX (' id ') from ' table ') LIMIT 0, 1

Since Max (' ID ') * MAX (' ID ') can fetch all the values in the table, the where in this statement can take all the conditions in this list, then this is a random SQL statement that all records are likely to be fetched.

　　Add another article

1. Multi-table connection type

1. Cartesian product (cross connection) in MySQL can be cross join or omit cross that join, or use ', ' such as:

SELECT * FROM table1 CROSS JOIN table2

SELECT * FROM table1 JOIN table2

SELECT * from Table1,table2

Because the result it returns is the product of the two data tables that are connected, it is generally not recommended when there is a where, on, or using conditions, because it is very slow when there are too many datasheet items. Generally use left [OUTER] Join or right [OUTER] Join

2. The INNER join INNER join inner join in MySQL is called equivalent connection, that is, you need to specify the equivalent join condition in MySQL cross and inner join are divided together. join_table:table_reference [INNER | CROSS] JOIN table_factor [join_condition]

3. The external connection in MySQL is divided into left outer and right connections, that is, in addition to returning the results of the join conditions, return to the left table (left) or the right table (right connection) does not conform to the conditions of the connection, the corresponding use of NULL correspondence.

Example:

User table:

ID | Name

———

1 | Libk

2 | Zyfon

3 | Daodao

User_action table:

user_id | Action

—————

1 | Jump

1 | Kick

1 | Jump

2 | Run

4 | Swim

Sql:

Select ID, name, action from user as U

Left join user_action A on u.id = a.user_id

Result

ID | name | Action

——————————–

1 | LIBK | Jump①

1 | LIBK | Kick②

1 | LIBK | Jump③

2 | Zyfon | Run④

3 | Daodao | Null⑤

Analysis:

Note that there is also a user_id=4, Action=swim record in the user_action, but not in the results,

Id=3 in the user table, Name=daodao users do not have a corresponding record in user_action, but they appear in the result set

Because now is the left join, all work is left.

Results 1,2,3,4 are both in the left table and on the right table record, 5 is only on the left table, not on the right table record

Working principle:

Read one from the left table and select all the right table records (n bars) to match on. Forms N Records (including duplicate rows, such as: result 1 and result 3), and if the right side does not have a table matching the on condition, the connected field is null. Then continue reading the next one.

Extended:

We can use the right table without on match to show the law of NULL, to find all the records in the left table, not the right table, note that the column to be judged must be declared not NULL.

Such as:

Sql:

Select ID, name, action from user as U

Left join user_action A on u.id = a.user_id

where a.user_id is NULL

Attention

1. A column value of NULL should be null and cannot be used with =null

2. Here the a.user_id column must be declared to not NULL.

)

Result of SQL above:

ID | name | Action

————————–

3 | Daodao | Null

——————————————————————————–

General usage:

A. Left [OUTER] JOIN:

In addition to returning results that match the join condition, you need to display data columns in the left table that do not meet the join criteria, and should use a null corresponding

SELECT column_name from table1 left [OUTER] JOIN table2 on Table1.column=table2.column

B. Right [OUTER] JOIN:

Right is different from the left join simply by displaying data columns that do not conform to the join condition in the right-hand table in addition to the results that match the join condition, using the null corresponding

SELECT column_name from table1 right [OUTER] JOIN table2 on Table1.column=table2.column

Tips:

1. On a.c1 = B.C1 equivalent to using (C1)

2. INNER JOIN and, (comma) are semantically equivalent

3. When MySQL retrieves information from a table, you can indicate which index it chooses.

This feature is useful if EXPLAIN shows that MySQL uses the wrong index in the list of possible indexes.

By specifying use index (key_list), you can tell MySQL to find a record row in a table using the most appropriate index in a possible index.

An optional two-choice syntax IGNORE index (key_list) can be used to tell MySQL not to use a specific index. Such as:

Mysql> SELECT * FROM table1 use INDEX (key1,key2)

-> WHERE key1=1 and key2=2 and key3=3;

Mysql> SELECT * FROM table1 IGNORE INDEX (Key3)

-> WHERE key1=1 and key2=2 and key3=3;

2. Constraints on table joins

Add display criteria where, ON, USING

1. WHERE clause

Mysql>

SELECT * from Table1,table2 WHERE table1.id=table2.id;

2. On

Mysql>

SELECT * FROM table1 left JOIN table2 on table1.id=table2.id;

SELECT * FROM table1 left JOIN table2 on table1.id=table2.id

Left JOIN table3 on table2.id=table3.id;

3. Using clause, if the two columns of a connected two table join condition have the same name, you can use the using

For example:

SELECT from left JOIN USING ()

Example of a connection with more than two tables:

Mysql>

SELECT artists. Artist, Cds.title, genres.genre

From CDs

Left JOIN genres N Cds.genreid = Genres.genreid

Left JOIN artists on cds.artistid = Artists.artistid;

or mysql>.

SELECT artists. Artist, Cds.title, genres.genre

From CDs

Left JOIN genres on cds.genreid = Genres.genreid

Left JOIN artists-> on cds.artistid = Artists.artistid

WHERE (genres.genre = ' Pop ');

--------------------------------------------

Filter the criteria and then set up the index of the related query fields in the table based on the table connection, which is very fast in the case of large data multiple table joint queries

SELECT M.*,ss. Sensorcode,ss. Sensorstatus,ss. Manufacturerid,ss. Electricity,

Ss. Voltage,ss. Minelectricity,ss. Minvoltage,ss. Temperature,ss. Statusupdtedate,ss. UpdateStatus, TP. Pricingstrategyid,tps. Freeduration,bat. Berthtypeid

From

(SELECT t.*, BS.) Parkstatus,bs. Changetime, CA. Cantonname, SE. SectionName

From

(SELECT a.*, B.berthid,b.berthcode,b.berthaddress,b.berthstatus,b.linedirection,b.cantonid,b.sectionid

From

(SELECT AR. Areaid,ar. Areacode,ar. AreaName from Sys_area as Ar WHERE 1=1 and AR. Areacode= ' A ')

Left JOIN Sys_berth as B on B.areaid=a.areaid) T

JOIN Sys_berthstatus as BS on T.berthcode=bs. Berthcode

JOIN Sys_canton as CA on T.cantonid=ca. Cantonid

JOIN sys_section as SE on T.sectionid=se. SectionID) M

The left JOIN sys_sensor the SS on M.berthcode=ss. Berthcode

The left JOIN is tra_pricingberth as TP on TP. Berthcode=m.berthcode

Left JOIN Tra_pricingstrategy as TPS on TPS. PRICINGSTRATEGYID=TP. Pricingstrategyid

Left JOIN Sys_berthandtype as bat on bat. Berthcode=m.berthcode

ORDER BY Berthcode ASC

Also need to pay attention to the place in MySQL involved in multiple table query, need to query according to the situation, want to use which connection way more efficient.

1. Cross-linking (cartesian product) or inner join [INNER | CROSS] JOIN

2. Left outer join or right [OUTER] Join note Specifies the join condition where, on,using, the OUTER.

3. How MySQL optimizes left join and right join

In MySQL, A left JOIN B join_condition is executed as follows:

1) · Set Table B based on all tables that are dependent on tables A and a.

2) · Set Table A for all tables (except B) that are used in the left join condition.

3) · The LEFT join condition is used to determine how to search for rows from table B. (In other words, do not use any of the conditions in the WHERE clause).

8); All standard joins can be optimized, except for tables that are read from all tables on which it relies. If there is a circular dependency, MySQL prompts for an error.

5) · Perform all standard where optimizations.

6) · If a row in a matches a WHERE clause, but no row in B matches the on condition, then another B row is generated, where all the columns are set to NULL.

7) · If you use a LEFT join to find a row that does not exist in some tables, and the following test: the where part of the col_name is NULL, where the col_name is a column declared not NULL, MySQL finds a match Stop after one line of the join condition (for a specific keyword combination) to search for additional rows.

The execution of the right join is similar to a left join, but the role of the table is reversed.

Joins the optimizer in the order in which the tables should be joined. Left JOIN and Straight_join forced table read Order can help the Join optimizer work faster because fewer table exchanges are checked. Note that this means that if you perform the following type of query, MySQL performs a full scan of B because the left join forces it to read before D:

SELECT *

From A,b left join C on (C.key=a.key) left join D on (D.key=a.key)

WHERE B.key=d.key;

In this case fix is in the reverse order of a, and B is listed in the FROM clause:

SELECT *

From B,a left join C on (C.key=a.key) left join D on (D.key=a.key)

WHERE B.key=d.key;

MySQL can perform the following left join optimization: If for the resulting null row, the Where condition is always false, and the left join becomes a normal join.

For example, in the following query, if T2.COLUMN1 is the Null,where clause, it will be false:

SELECT * from T1 left JOIN T2 on (column1) WHERE t2.column2=5;

Therefore, you can safely convert a query to a normal join:

SELECT * from T1, T2 where t2.column2=5 and t1.column1=t2.column1;

This can be faster because MySQL can use table t2 before table T1 If you can make the query better. To enforce the use of table order, use Straight_join.