Shulou(Shulou.com)05/31 Report--

What are the 12 MySQL optimization techniques? I believe many inexperienced people don't know what to do about it. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

Applications are slow for a variety of reasons, including the network, the system architecture, and the database.

So how to improve the execution speed of database SQL statements? Some people would say that performance tuning is the business of database administrators (DBA), but performance tuning also has a lot to do with programmers.

The line-by-line SQL statements embedded in the program will get twice the result with half the effort if some optimization tips are used.

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

For example, query the product price based on region and product id, and use it for the first time:

So the second time the same query, please keep the above statements consistent, for example, do not change the id and region positions in the where statement.

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.

Other

There are many ways to tune SQL, and there can be many different ways to query the same query result. In fact, a better way is to test with the closest real data set and hardware environment in the development environment, and then release it to the production environment.

After reading the above, have you mastered 12 MySQL optimization techniques? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.