Optimization is not just an index, but an index optimization.

Optimization is not just an index, but an index optimization.

After graduation, I got into contact with optimization problems. I have been doing professional Optimization for at least five years. But now I still often hear many people think that optimization is very simple, that is, index building, this really cannot blame everyone. During the past 20 years, almost everyone can hear this sound at every stage of his career. I have seen this in many books, but here I want to tell you: optimization is not just about index building, but also about optimization. This job requires a comprehensive basic database knowledge and a profound understanding of concepts, we also need rich practical experience.

Database optimization can be divided into OS, DB, and SQL optimization. Aside from the OS and DB, let's talk about SQL statement optimization (SQL tuning). When it comes to SQL optimization, we have to mention the explain plan ), all relational databases (oracle, db2, sqlserver, mysql, postgresql, gp, and so on) have corresponding execution plans for SQL statements, but their expressions are different. The execution plan contains multiple nodes or steps. Depending on the SQL complexity, the nodes are more or less. There are multiple data access methods in these nodes, there are also a variety of data computing methods between nodes, and indexing is only one of the multiple data access methods. Aside from those computing nodes and other data access nodes, there are many ways to access data by indexing. Let's take a look at it. Aside from so many aspects and content, there is only so much content learning and research for indexing.

Speaking of the data access method, the most common is full table scan and index access. Now many people, even many IT people, think that there is a problem with the execution plan when they see full table scan, I even shouted, as if I had discovered the new world. In fact, full table scanning has its own application scenarios, while index access also has its own application scenarios, it is not always optimal to access data through indexes. The most simple way is to access most of the data in the table. Full table scan may be better than index access, it is the cluster factor of the index. When the value is very high (some friends may note that sometimes the logical read of an SQL statement is much larger than that of the entire table ), even if the proportion of data you access is small, you may scan the entire table. The cluster factor problem is almost inevitable when indexes are created on multiple different fields, therefore, in order to truly master the optimization, we must understand and understand the basic knowledge and concepts involved in the database. Only in this way can we find out under what circumstances, which access methods and algorithms are most suitable.

SQL tuning does have high requirements for professional personnel, but it only solves the problems at the application layer. It is undeniable that in many cases, the system performance and even the failure are caused by SQL, however, in many cases, even if the SQL tuning is good, it cannot solve the performance problem. This requires us to perform overall Analysis and Optimization on the OS and DB layers.

So far, this article only tells you that tuning is not only about indexing, but also requires us to study and study many other aspects, for more information about how to optimize the OS, DB, and SQL TUNING, see this blog.