What are the mysql statement query techniques?

Mysql statement query skills: 1. If the comparison operator can use "=", do not use "<>"; 2. If you know that there is only one query result, please use "LIMIT 1"; 3. For columns Choose the appropriate data type; 4. Try to avoid using "SELECT *"; 5. Use LIMIT to implement paging logic.

mysql statement query skills:

Tip 1 If you can use "=" for the comparison operator, don't "<>"

　"=" increases the probability of using the index.

Tip 2 If you know that there is only one query result, please use "LIMIT 1"

"LIMIT 1" can avoid full table scan, and the corresponding result will not be found again. Scanning continues.

Tip 3 Choose the appropriate data type for the column

If you can use TINYINT, don’t use SMALLINT. If you can use SMALLINT, don’t use INT. You know the truth, disk and memory consumption The smaller the better.

Tip 4: Turn a large DELETE, UPDATE or INSERT query into multiple small queries

Can you write a SQL statement with dozens or hundreds of lines? Looks very classy? However, for better performance and better data control, you can break them into multiple small queries.

Tip 5 Use UNION ALL instead of UNION, if the result set allows duplication

Because UNION ALL does not remove duplicates, it is more efficient than UNION.

Tip 6 To obtain multiple executions of the same result set, please keep the SQL statement consistent

The purpose of this is to make full use of the query buffer.

Tip 7 Try to avoid using "SELECT *"

If you do not query all the columns in the table, try to avoid using SELECT * because it will perform a full table scan. Inability to effectively utilize indexes increases the burden on the database server and the network IO overhead between it and the application client.

Tip 8 Columns in the WHERE clause should be indexed as much as possible

It’s just “try your best”, not all columns. Adjust measures according to local conditions and actual conditions, because sometimes too many indexes will reduce performance.

Tip 9 Columns in the JOIN clause should be indexed as much as possible

It’s also just “try your best”, not all columns.

Tip 10 The columns of ORDER BY should be indexed as much as possible

If the columns of ORDER BY are indexed, the performance will be better.

Tip 11 Use LIMIT to implement paging logic

Not only improves performance, but also reduces unnecessary network transmission between databases and applications.

Tip 12 Use the EXPLAIN keyword to view the execution plan

EXPLAIN can check index usage and scanned rows.

Tip 13 MySQL numbers are rounded, rounded, and retained to n decimal places

More related free learning recommendations: mysql tutorial(Video)

The above is the detailed content of What are the mysql statement query techniques?. For more information, please follow other related articles on the PHP Chinese website!