On the database frame, laughed at, please correct me.

On the database frame, laughed at, please correct me.

http://weibo.com/p/1001603724746155003486

One friend said, "plug-in storage also separates the full logic of the SQL engine ... Overall, MySQL optimizer does not have much improved value under the existing framework. "

We also do a technical analysis:

1 plug-in frame, can be static/dynamic loading components, easy to switch between the same family of different modules, this design is good. Many software designs use the "micro-kernel + plug-in" approach to build a powerful application. such as the ecplise biosphere.

2 database scope, MySQL is a plug-in, there are no other database also has plug-in design? The answer is: PostgreSQL's storage layer is also plug-in. This can be seen from the F_SMGR structure (Analysis smgr layer, you can see  @ that Blue   's blog: http://blog.163.com/li_hx/blog/static/183991413201172483824324/ http://blog.163.com/li_hx/blog/static/18399141320117266474331/and http://blog.163.com/li_hx/blog/static/ 18399141320117258368583/), only the development of PG these years, has not grown independent and influential storage layer, so this is little known. Maybe this gives you the impression that MySQL has exclusive use of plug-in design (this sentence is laughed at).

3 "plug-in storage" if it is more appropriate to change to "plug-in transaction and storage Engine", the MySQL server layer only defines the interface of the transaction, the implementation of acid characteristics, or the server layer, such as the InnoDB class of plug-in implements the transaction and storage functions. Emphasizing this, in fact, is to emphasize the nature of the database system-the transaction, if only understand InnoDB as the storage engine may be great difference. This is also a growing number of people to MyISAM and so on to choose the internal reasons for InnoDB, the choice is the transaction security rather than the simple storage.

4 once the module is loaded, in the running state, they are one, the efficiency does not depend on whether the plug-in type (plug-in design in the running state, just the application of function pointer technology, no loss of efficiency). The pros and cons of plug-in design is whether the interface layer is considered properly.

for plug-in design, if the interface layer is required to perform the task/need to pass the information clearly defined, then the run-state and whether it is plug-in (dynamically loaded plug-ins, only the first use of the load at a cost, InnoDB at the start of the load so even this little impact does not exist). Plug-in design is only a design-level problem, not the problem of running state, so "plug-in storage is fragmented ..." is not appropriate, if the hard to say the separation, it can actually be considered as "interface layer" definition is not clear but "plug-in" fault. Therefore, the conclusion that "overall the MySQL optimizer does not have much improvement in the existing framework" is naturally not tenable.

5 extra talk about the future of the MySQL optimizer.

This is a very large topic, not three words and five words can be said thoroughly. Not to mention the short microblogging can be easily exhausted.

Simply put:

1) MySQL optimizer has been in progress, the industry insight from the source code of 5.6 and 5.7 has found this proof. Large-scale, continuous investment makes MySQL faster than any stage in history.

2) Any database optimizer is progressing, which is driven by social needs.

3) How does the MySQL optimizer progress with the demand push? It can be discussed from 2 aspects: technical and historical development opportunities (development opportunities are not discussed). Technically, the MySQL optimizer does not have a so-called plug-in framework to limit the existence of pseudo-propositions for its development, while MySQL's internal iterative refactoring of the optimizer (which is an improvement of the architecture) constantly employs a variety of techniques to improve the performance of the optimizer (this is the improvement of functionality).

4) More technical details: "The Art of database query optimizer" in the fourth Chapter 17th/18th Chapter/19th Chapter depth to the details, discussed the PostgreSQL and MySQL optimizer's similarities and differences and pros and cons, for reference.

6 Frames

For more discussion of the framework, more evidence-related discussions and corrections are welcome.

That's what I'm saying . Feel the plug-in type disadvantage really a lot, for example InnoDB need to maintain their own set of data dictionary, each time to the server output a record resulting in the hash join implementation difficulties. MySQL's execution plan is also very coarse, as the actuator and optimizer interface, should be more clear, fine.