MySQL SQL optimization

Record some experience, mainly the conclusion. Build the search and so on will not write, the Earth people know.

1. Comparison of 2 joins

SELECT * FROM (SELECT * from A where is >) inner JOIN (SELECT * from B where grade > 3) TB on a.b_id = Tb.id;sel ECT * FROM (SELECT * from A where >) inner join B on a.b_id = b.ID and B.grade > 3;

When the ID field of B is the primary key or index, if the amount of data reaches tens, the second may be more than 10 times times faster than the first SQL because the first SQL terabyte is not indexed.

2. JDBC connects MySQL to insert data and uses BULK insert to increase performance by more than 8 times times. There are two points to pay attention to:

(1) the database connection needs to be added "Rewritebatchedstatements=true", otherwise JDBC will not really use BULK INSERT.

(2) If MySQL has a master-slave, note that the number of bulk inserts not too much, to prevent failure.

3. Observe the SQL executed by MySQL

With tail-f view, the SQL execution log file path is general_log_file defined in the my.cnf file.

4. Using explain

See http://database.51cto.com/art/200912/168453.htm

MySQL SQL optimization