MySQL 被 seen as a most widely used open source database, one of its pluggable storage engine is one of the most important features to understand and apply the storage engine will be able to more easily use the MySQL database, enterprise-class features.
MySQL database architecture diagram
Open-source database MySQL database is the most important database product is one, divided into the MySQL Community Server Edition and MySQL Enterprise Server Edition. Enterprise Server version of its mainly small and medium broad application, but in many large enterprises departmental or business-critical database applications also began to be used. MySQL is known as the most widely used database product, mainly because it has a lot of good momentum, one of the most distinctive is its pluggable storage engine.
When the user applied to the database when the different areas, each application submitted to the database, the performance characteristics meet their own needs, while the MySQL pluggable storage engine was introduced for this and. Using MySQL pluggable storage engine architecture allows database professionals to choose for a particular application requirements specific storage engine management completely without any special application coding requirements. MySQL server architecture as the storage level to provide a consistent and simple application model and API, application programming and DBA can not consider all of the underlying implementation details. Therefore, different storage engines have different capabilities, but with separate application is.
What is a MySQL storage engine
So what is the pluggable storage engine? MySQL using different technologies, the data on the different file system to store, for example, data for the memory database will be directly stored in the memory. Each of these technologies technology uses a different storage mechanism, indexing techniques, 最终 lock level and provides a wide range of features and capabilities by choosing different technology, users get an extra Suduhuozhe features to improve application of the overall function of . For example, if a large number of temporary data to study, may need to use memory storage engine, memory storage engine is stored in memory all the form data, helping users to complete the application. Or, the user may need a database to support transaction processing to ensure that the transaction rollback not successful data capacity, which need to use storage engines support transactions. These different technologies and supporting related functions were called in the MySQL storage engine (also called table types). MySQL default configuration of many different storage engines, you can pre-set or enabled in the MySQL server. Can choose to apply to servers, databases, and forms of storage engines to choose how to store information, how to retrieve this information and the need to combine what data, when performance and functionality for maximum flexibility.
Can choose how to store and retrieve data, such flexibility is the MySQL main reason why it is so popular. Other database systems (including most commercial databases) supports only one type of data storage. MySQL database architecture, as shown above.
Configured to use storage engines
Here, I highlight some often you use MySQL storage engine, and also introduce them to Pluggable storage engine how to use the issue. First of all, the most commonly used is the MyISAM storage engine, which is the default MySQL pluggable storage engine, which is in the Web, data warehousing and other application environments one of the most frequently used storage engine. It supports table-level locking, full-text indexing functions. Then the InnoDB storage engine for transaction processing applications, has many features, by MVCC support services, allowing COMMIT, ROLLBACK, and save points, but also support the ACID transaction. InnoDB storage engine, developed by Innobase, but in October 2005, Oracle acquired the company, so, although currently the engine still would still be used, however, MySQL has developed a new engine to replace InnoDB engine , code-named Falcon. The new engine will be released in the near future.
Followed by Cluster / NDB engine, which is the cluster type MySQL database engine, in particular, find the requirements for high performance applications, such demands also asked to find the highest normal working hours and availability. The Archive engine, a lot of little references to the history, archiving, or security audit information storage and retrieval provides a perfect solution. Federated engine is able to separate multiple link the MySQL server, from multiple physical servers to create a logical database, are very suitable for distributed environment or data mart environment. Merge engine allows MySQL DBA or developer will be a series of identical MyISAM tables together in a logical manner, and as an object reference to them, such as data warehousing for the VLDB environment is very suitable. Memory engine, all data stored in RAM, in the need to quickly find references, and other similar data environment, providing fast access speed.
Other storage engines, including CSV (comma-separated reference used by the database table file), Blackhole (the database used for the temporary ban on the application input), and Example engine (can quickly create custom plug-in storage engine to help) .
Want to change the storage engine structure, the database system as long as the changes to the database system STORAGE_ENGINE configuration variables, we can easily change the default storage engine, MySQL server. To easily install the plug-in storage engine, the storage engine plugin file must be in the proper MySQL database directory, and issue INSTALL PLUGIN statement must have super-user administrative privileges. As the convenience of the engine can be inserted, for all applications, not necessarily use the same storage engine for the same applications on each table using a different storage engine, just create a new table in the designated storage engine, can use the ENGINE parameters, or by using the ALTER TABLE statement to specify the storage engine. If you want to unplug the storage engine, just use the UNINSTALL PLUGIN statement on it. (The unit of the General Armament Department of Aerospace Command and Control Center)
Term analysis: the open source database
After years of open-source database market development, enterprise applications have also matured. Among them, most people understand that the MySQL, PostgreSQL, Firebird, Derby, Orade so. MySQL is known as "the world's most popular open source database", in many enterprises has been a lot of applications, and in many key business has also been applied, there are a lot of success stories, has very good reliability and technical superiority. It started from the 5.0 version added a lot of the elements of enterprise applications, such as stored procedures, triggers, views, pointers and so on. MySQL enterprise applications are also widely carried out in China, China's thousands of miles with the MySQL open source partnership established in MySQL training, the Chinese developer of the MySQL for development and application, and introduced the most popular LAMP application.
PostgreSQL is known as "the world's most advanced open source database", is a complex object - relational database management system (ORDBMS), feature-rich, complex, some features do not even have commercial databases, which aims to do large object-oriented relational database system. It supports SQL89 and SQL92 standards. EnterpriseDB is based on the extension from the PostgreSql database, known as the "true enterprise-class open source database, and enough to compete with Oracle." firebird is a full-featured, powerful and efficient, lightweight, maintenance-free database, fully supports the ANSI SQL92, SQL98 and other standards, it easily from a single user, single database to upgrade to enterprise applications. firebird database server can manage multiple independent databases, each database can support multiple clients simultaneously connected.