Note: This document is written in May 2000. Therefore, it does not explain the latest features of MySQL. But we can still understand some of RDBMS's basic concepts, principles, thus better applying databases in practice, and can also stay necessary for some false hype.
Why don't you use mysql?
Author: Ben Adida Translator: Mahuida
Almost every week, sometimes even more frequent, there is always someone asking us why we do not use MySQL as the RDBMS of OpenACS (Relational Database Management System). ACS Classic Team (Arsdigita) has also encountered the same question on their forum. If mysql is good enough for SlashDot, it will be able to use OpenAcs, isn't it?
wrong. This short papers will try to explain why MySQL is not only wrong to OpenACS, but it should not be used for any system that handles critical data.
RDBMS purpose
The purpose of RDBMS is to provide a reliable permanent storage mechanism that specifically shows very stringent characteristics of this mechanism in the ACID test. I will directly reference the wonderful explanation of Philip Greenspun (represented by Oracle as RDBMS):
Atomicity
The execution result of the transaction is or is all submitted, or it is rolled back (ROLL BACK). Either all changes take effect, or there is no change to take effect. Suppose a user is editing a note, the web script tells the database "Copy the old annotation value to the audit table, and update the active table with the new text." If after copying, the update is full of hard drives, the audit table will be rolled back.
Consistency
The database is converted from an active state to another. A transaction is legal only when obeying the user-defined integrity constraint. Illegal transactions are not allowed, and if you can't meet a integrity constraint, the transaction will be rolled back. For example, assuming that you define a rule: The post in the forum table must be associated with a valid user ID. Then you hire Joe Novice to write a management page. Joe has written a deleted user page, which does not check if it will generate some noord forum posts. However, RDBMs like Oracle will check and abort any transactions. If it produces the forum post for the user who has been deleted.
Isolation
The result of a transaction is invisible for other transactions until the transaction is completed. For example, assume you have a page that displays new users and their photos. According to the requirements of the publisher, each user has a facial photo in the page. If the user does not have a photo, it will display an image indicating no photos. While the new user Jane is registered at your site, the old user BILL is viewing the new user page. Handling Jane's registered scripts inserts several tables: Users, Mugshots, Users_Demographics. If the face of Jane's facial photo is large, the above insert may take some time. If BILL is started before Jane's transaction, Bill will not see Jane at his new user page, even in Jane's transaction, insertion of some tables has been completed.
Durability
Once submit (completed), the results of the transaction will be permanent and free from future systems and media failures. Suppose your e-commerce system is inserted into the database table from a consumer, and indicates that Cybercash charges the consumer's $ 500. Suddenly, someone slammed the power cord of the machine before your server received Cybercash. In this case, Oracle will not forget the order. Moreover, if some programmers sprinkle the coffee into the disk drive, it is possible to install a new disk and return the transaction to the coffee and sprinkle; the data will show you that you have tried to charge someone $ 500 and not clear What happened to Cybercash. If you want is a fast bare storage, go to the file system. If you want to share between multiple machines, use NFS. If you want simple reliability to deal with too simple faults, use mirroring. Want to add SQL interface? Use mysql.
Now, if you want something such a data store, it can keep your data set constant, which can perform complex operations for these data, and never violate the above constraints, you can simultaneously The local work is separated from each other and can make a smooth recovery from any type of failure, then find a real RDBMS yourself. Yes, it will be slower than the MySQL file system, just like TCP is slower than UDP, but they provide a better service guarantee.
The status quo and future of mysql
Building a real RDBMS is a daunting task, perhaps arduous than any other system problem. Most of the products in the market have been developed for many years, some have more than 10 or 15 years.
MySQL developers claim that they sacrificed certain features to ensure better performance. Although this is perhaps a fun way to track non-critical data (such as click rate tracking), when processing critical data, sacrifice complete data integrity is unacceptable, even for speeds.
When MySQL is ripe, the OpenACS team is very happy to take a closer approach. However, the MySQL team does not seem to understand the concept and importance of true ACID capabilities: MySQL TODO mentioned "transaction" in a long list, including problems such as "sleep process occupied CPU". In addition, the MySQL manual claiming that mysql will quickly achieve "atomic operation" through the use of the latch, but "no rollback". This is the misuse of the term "atom" clear position: "Atomic operation" means that all operations are completed, or no operation is completed. If there is no rollback capability, hardware or power failures that occur in the middle of a group of statements will destroy the atomicity of the block.
Rollback is not just a convenient feature, it is a critical basis for reliable data storage.
There are a lot of good reasons to use mysql, but the demand for data storage for reliable, complimentary to Acid is not one of them.
More details
l MySQL has no subquery.
For complex queries, MySQL users must perform two or more series queries, each time you need to communicate or network communication between applications and databases. This significantly reduces the speed advantage of MySQL.
l MySQL has no stored procedure.
l MySQL has no trigger or foreign key constraint.
l MySQL is only a table-level lock.
Conclusion
Enterprise-level systems will not sacrifice specific features for speeds. RDBMS's ACID attribute is an absolute necessities for any critical data. Key websites running on non-ACID obedience systems are in trouble.
