Shulou(Shulou.com)06/01 Report--

This article is to share with you what query techniques are available in mysql statements. The editor thought it was very practical, so I shared it with you as a reference. Let's follow the editor and have a look.

Mysql statement query skills: 1, the comparison operator can be used "=" not ""; 2, knowing that there is only one query result, then please use "LIMIT 1"; 3, choose the appropriate data type for the column; 4, try to avoid using "SELECT *"; 5, use LIMIT to achieve paging logic.

Query skills of mysql statement:

Tip 1 comparison operator can use "=" not ""

"=" increases the probability of using the index.

Tip 2 knows that there is only one query result, so please use "LIMIT 1"

"LIMIT 1" avoids full table scanning, and will not continue scanning if you find the corresponding result.

Tip 3 choose the appropriate data type for the column

You don't need SMALLINT if you can use TINYINT, and you don't need INT if you can use SMALLINT. You know, the smaller the disk and memory consumption, the better.

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

Isn't it high to be able to write dozens or hundreds of lines of SQL statements? However, for better performance and better data control, you can turn them into multiple small queries.

Tip 5 use UNION ALL instead of UNION if the result set allows repetition

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

Tip 6 to get multiple executions of the same result set, keep the SQL statement consistent

The purpose of this is to take full advantage of query buffering.

Tip 7 avoid using "SELECT *" as much as possible

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

Tip 8 the columns in the WHERE clause are indexed as much as possible

Just "try" Oh, not all the columns. Adjust to local conditions and adjust to the actual situation, because sometimes too many indexes will degrade performance.

Tip 9 columns in the JOIN clause are indexed as much as possible

Again, just "try" Oh, not all the columns.

Tip 10 ORDER BY columns are indexed as much as possible

ORDER BY columns will also perform better if they are indexed.

Tip 11 using LIMIT to implement paging logic

It 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.

Technique 13 rounding, rounding, and preserving n decimal places of MySQL numbers

Thank you for reading! On the mysql sentence which query skills are shared here, I hope the above content can be of some help to you, so that you can learn more knowledge. If you think the article is good, you can share it and let more people see it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.