MySQL Query optimization tips

Index optimization, query optimization, query caching, Server Setup optimization, operating system and hardware optimization, application-level optimization (Web server, caching) and more. The optimization techniques recorded here are more suitable for developers, are collected and collated from the web, mainly in query statement optimization, other aspects of the optimization techniques are not recorded here.

Cost metric for the query: execution time, number of rows checked, number of rows returned.

Several guidelines for indexing:

(1) Reasonable index can speed up the efficiency of data reading, and unreasonable indexing will slow down the response speed of the database.

(2) The more indexes, the slower the data is updated.

(3) try to use an index when using MyISAM as the engine (because MySQL stores the index btree) instead of InnoDB. But MyISAM does not support transcation.

(4) when your program and database structure/SQL statements have been optimized to the extent that they cannot be optimized, and the program bottleneck does not work, it is time to consider using a distributed cache system such as memcached.  

(5) habit and force yourself to use explain to analyze the performance of your SQL statements.

One, the optimization of the count

(1). Select COUNT (*) from world.city where ID > 5;

(2). Select (select count () from world.city) –count () from world.city where ID <= 5;  

1 Statement the number of rows that need to be scanned is more than 2 statements when the number of rows exceeds 11, and 2 statements scan 6 rows, in which case 2 statements are more efficient than 1 statements. Direct SELECT COUNT (*) from world.city is faster when there is no where statement, because MySQL always knows the number of rows in the table.

Second, the Index field on the operation will invalidate the index

try to avoid function or expression operations on the field in the Where clause, which will cause the engine to discard the use of the index full table scan. such as:

SELECT * from T1 where f1/2=100 should read: SELECT * from T1 where f1=100*2

Third , avoid using! = or ">, is null or is not NULL," in "," not "and so on operators

Because this makes the system unusable with indexes, it can only search the data in the table directly. For example: The Select ID from the employee WHERE ID! = "B%" Optimizer will not be able to determine the number of rows in the fatal row by index, so you need to search all rows of the table. In the in statement can use the EXISTS statement instead of the exists.

Four , rational use of exists,not exists clauses

Five , if you can use between, do not use in

Six , you can use distinct without GROUP by

Seven , try not to use the SELECT INTO statement. The SELECT into statement causes the table to lock and prevent other users from accessing the table

Viii. If multiple data is inserted into the same table at one time in the program

such as the following statement:  

Insert into person (name,age) VALUES (' Xboy ', 14);

Insert into person (name,age) VALUES (' Xgirl ', 15);

Insert into person (name,age) VALUES (' Nia ', 19);

It will be more efficient to put it into a statement.

Insert into person (name,age) VALUES (' Xboy ', +), (' Xgirl ', +), (' Nia ', 19);

MySQL Query optimization tips