MySQL takes speed as its target

mysql| speed MySQL with speed as the target



--------------------------------------------------------------------------------


Both MySQL and PostgreSQL are well-known database open source projects, and there is a trend to replace the commercial database system, but the two have different design goals. Generally speaking, if you need faster speed, you can prioritize MySQL, and if you need some new features, you can choose PostgreSQL.

MySQL is currently maintained by MySQL AB, its latest stable version is 3.23, and the 3.23Max version of the support transaction is in beta testing, and its latest development version 4.0 has entered the Alpha test phase. It's a slightly unusual license fee, and it's easy to impress the user. Its price varies with the platform and installation method, and its Windows version is not free in any case, while for any unix/linux version, if the user or system administrator installs it for free, the Third-party installation must pay the license fee.

MySQL is a network database management system based on C/s system structure. It is designed to speed as the main goal, can quickly handle the query requests of multiple users, processing client connections is very fast, very suitable for use as a Web site background database. PostgreSQL is relatively slow to perform, generating a child process for each connection, while the steps to build a subprocess and establish a background service process slow down its execution speed. MySQL's server is very compact, and complex functionality processing (such as XML support) is fully placed on the client side, making it more functional without sacrificing the speed and reliability of the database server. Moreover, it has been proven by commercial memory testing programs that there is no memory leak, continuous outages and program crashes are rare and more robust than PostgreSQL.

In the initial design phase, MySQL was primarily targeted at mid-sized databases, that is, the size of the database is about tens of millions of lines, each table is about 100MB. With the development of the MySQL project, the area of its use has gradually expanded to large terabytes of databases and small handheld or embedded devices. In the short term, however, MySQL does not support real-time databases or large cluster databases. Although in its 3.23 version, it passes the MyISAM table, the system can theoretically support the 8000PB table. However, due to operating system limitations, it can only handle tables with a maximum of 2GB or 4GB in most Linux systems. In version 3.23, new modules such as replication for large server clusters, INNODB Transaction table processors, Berkerley DB Transaction table processor interface, Full-text search, MYODBC 2.5 interface, MyISAM table automatic check and repair, BULK insert are also included.

The introduction of InnoDB and BDB Transaction table processors makes the data tables stored in MySQL a good transaction-handling capability. However, for tables that are stored in other ways, maintaining integrity and consistency with atomic operations typically loses some performance. When integrity is important, you can use lock tables for table locking. Starting with 3.23.44, the InnoDB table supports the code restriction check, but its use, especially the misuse of the outer code, can lead to more serious problems.

MySQL does not support nesting queries and is a great inconvenience to developers. It supports only a subset of the SQL92 and does not support a common subset of SQL92 like PostgreSQL. So for a more complex logic query, often to establish temporary tables to save the intermediate information, sometimes this way will make some query statements are not intuitive or simply can not express.

However, there are new features added to the MySQL 4.0 system, such as how to support fail-safe replication and online backup to accommodate mission-critical and heavily-loaded users. It also uses a new Full-text index for large sections of text, enabling users to retrieve them using natural language descriptions. In addition, the connection between the server and the client can only be made through plaintext in a version prior to 4.0, and a firewall must be erected between the server and the client to provide strong security; The 4.0 version supports secure communication between the client and the server, and its MYSQLD server daemon can communicate using Secure Sockets (SSL) and clients. In addition, it adds symbolic connections to the MyISAM at the table level, making it possible to support symbolic connection processing under Windows systems.

(Computer World Newspaper, Phase 11th B24)