Database performance optimization (DB tuning) performance optimization is by no means just an index

A graduation on the contact optimization problems, professional optimization also has at least 5 years of time, but now still often hear many people think that optimization is very simple, is to build index problem, it really can not blame everyone, do this line for more than 20 years, at every stage of career, almost can hear such a voice, In many books have also seen such a statement, but here I want to tell you: optimization is not just index, optimization is not very simple thing, this job requires a comprehensive database of basic knowledge, deep understanding of the concept, but also have a wealth of practical experience.

Database optimization can be broadly divided into OS, DB, and SQL-level optimizations. Let's leave the OS and db level aside, we'll start with the SQL statement optimization (SQL TUNING), and when it comes to SQL optimization, let's talk about the execution plan (explain plan), all the relational databases (oracle,db2,sqlserver,mysql , POSTGRESQL,GP, etc.), for SQL statements, there are corresponding execution plans, only the form of expression is different. The execution plan includes several nodes or steps, depending on the complexity of the SQL, the nodes are more or less, there are many methods of accessing data in these nodes, and there are many methods of calculating data between nodes, but index is just one kind of various data access methods, and then throw away those compute nodes and other data access nodes. There are many ways to access data by index, and you can look at it, leaving out so many aspects and content, just indexing and so much content learning and research.

Speaking of the method of accessing data, the most common is the full table scan and index access, and now many people, even many it people see the full table scan to think that the implementation of the plan is a problem, and even loudly exclaimed, as if discovered the new world, in fact, the full table scan has its own application scenario, and index access has its own application scenario It is not at any time to access the data through the index is the best, the most obvious, access to most of the table of data, full-scale scan may be better than the index access, and one point, is the index of the cluster factor, when this value is very high (perhaps a lot of friends notice that Sometimes a SQL logical reading is much larger than the whole table, that's all, even if the data you access is small, it's possible to go through a full table scan, and the cluster factor problem is almost inevitable in the case of many different fields, so, to really master the optimization, We must know and understand the basic knowledge and concepts involved in the database, so that we can figure out, under what circumstances, what access methods and algorithms are most appropriate.

SQL tuning is really a high demand for professionals, but this only solves the application level of the problem, admittedly, in many cases, system performance or even failure is caused by SQL, but in many cases, even if the SQL tuning better, but also can not solve the performance problem, This requires a holistic analysis and tuning of the OS and DB levels.

This article ends here, just to tell you that tuning is not just an index problem, there are many aspects that we need to learn and study, as for the specific methods and procedures for OS, DB, and SQL tuning tuning, check out other articles on this blog.

Database performance optimization (DB tuning) performance optimization is by no means just an index