Sun Tzu's Art of War by database developers

Sun Tzu's Art of War by database developers

Database? No.MySQLAnd so on!

Link? It's not a table!

Database Design? Just think of the interface!

Not fully considered? Change the table!

Not fast enough? Have you created an index? Add an index!

......

In my development experience, similar Q & A is not uncommon. But is this normal?

Today,DBMSIt seems that data persistence has become a choice in most applications. In a large number of development scenarios, there is no full-timeDBATherefore, database installation, design, maintenance, and operationsProgramMember. Unfortunately, in many programmers, database knowledge is very scarce-otherwise, there will be no such questions and answers.

It is no wonder that the database courses offered by schools are too old-many textbooks are still being taught.FoxBASE (Take the course as an archaeological course); too boring --1nf ,2nf ,3nf ,Bcnf It is a headache if you think about it. (anyone who has development experience knows that it cannot be fully standardized.) It is too theoretical-index retrieval ratio (Retrieve Ratio ) In10% The left and right are normal (please, in that case500,000 Rows of data are big tables. Nowadays, such tables are common )...... Besides, today'sDBMS So intelligent, so easy-to-use, simply breaking those rules and taking advantage of intuition, it seems to be a good choice.

However, the problem does not seem to be so simple, especially in today's increasingly complex applications and increasingly strict requirements on speed-the database is not well designed, complex applications or cannot be implemented, or the efficiency is extremely low;SQL Statement writing is not concise and efficient, and the speed will be greatly reduced. In daily development, such an example is not uncommon: the two results are the sameSQL Statement, a required0.05 Seconds.0.2 Seconds.50,000 Time Difference is nearly2 Hours! I have seen that the entire database is locked in a commonly used Forum program.172 SecondsSQL Statement. You have also encountered a database with an improper design.60 Seconds to complete.5 Second query.

Every time I encounter such a situation, I am thinking, if there is a book, starting from the reality, to teach us the "practical" database knowledge, how good?

O 'ReillyOf 《SQLLanguage Art (The art of SQL) Is such a book!

Just look at the name. This book is aboutSQLThe "skill" of the language. However, if you read two pages, you will find that this is actually a database practice book. Its content is far from being refined. It goes up to the overall idea of database design, and downSQLStatement tuning details. In addition, it not only provides solutions for various typical problems encountered in actual development, but also provides reasons:

L the addresses of the accounting, office, and production departments of a company may be different, what should we do? Are three fields allowed? However, in most cases, an address is enough, the other two addresses are set to null , or directly copy data? If it is set to null Should the operations for judging and reading the address be put in the database or in the programming language? How can we maintain updated consistency if we copy data?

L If You Want To determine whether appropriate data exists, modify the data, use count (*) ? However, experienced programmers seem to have another way. How did they do it?

L in textbooks, standardization is required, but sometimes normalization is required, under what circumstances should we standardize and how should we determine?

L can more indexes increase efficiency? Is it a good habit to index Foreign keys systematically? If a multi-field index has been created, is it necessary to create a separate index for one of the fields?

L as the data volume is large, table partitions (or "Table spaces") are a good solution. However, how to partition? Under what circumstances should I select a circular partition? Under what circumstances should I select a data-driven partition?

L The complex data processing logic should be placed inside the database (PL/SQL), Or to the external programming language (CLanguage )?

There are many other similar problems. It can be said that there are often no fixed and ready-made answers to such questions. We must consider and make a choice based on the specific situation. At this time, the experience introduced in the book, the guidance and judgment basis for choice, is especially precious.

In addition, this book has two areas that I like: one is to introduce good development habits, suchSQLLanguage
Add comments at the beginning of a sentence to facilitate debugging (believe me, in a complex system, this habit is very useful); another point is that the author wrote books to borrow the wisdom of Sun Tzu's Art of War, the examples are also related to the military.
-- For example, when we introduce indexes, the data we use is the name of the battle ship of Napoleon-programmers interested in military affairs. when reading this book, we should be able to see more fun.

 

Relational databases are different from many other fields in software development. They have a complete theoretical foundation and must be integrated into complicated reality.The art of SQLAnd may be more suitable for translation 《SQLBecause it is a bridge of experience between theory and reality.

Yu Sheng
Regular Expression proficiency, version 3rd Translator

Blog point of view major recommendations:

Http://www.cc2e.com.cn/indexbooks.htm

What are we going to use to create future applications?

Step into the. net3.0 development Hall-yan Xiaojun

New-generation interface programming experience

Horizontal evaluation: one book, two people