MySQL Architecture:
The MySQL architecture consists of five primary sub systems that work together to respond to a request made to the MySQL database engine.
The Query Engine
The Storage Manager
The Buffer Manager
The Transaction Manager
The Recovery Manager
The Query Engine:
The query engine optimizer sub system contains three interrelated components.
· The Syntax Parser
· The Query Optimizer
· The Execution Component
The Syntax Parser checks for the proper syntax of the commands issued that are understood by the MySQL engine. It also checks the objects referred in the query to resolve whether the user issuing the query has minimum privileges to do the operation.
The query optimizer resolves a best execution plan for any query that is issued taking probability of its usage and indexes that are created on the object. It also allows the users to specify the execution plan if required.
The Execution Component interprets the execution plan and based on the information received, makes the requests of the other components to retrieve the records.
The Storage Manager:
The Storage Manager interfaces with the operating system (OS) to write data to the disk efficiently. The storage manager writes to disk all the data in the user tables, indexes and logs as well as the internal system data.
The Query Cache:
A mechanism that stores the result set of a query on memory for a stipulated amount of time that depends on the frequency at which any query is requested from the database which drastically decreases the amount of time that is required for the Database engine to provide the results.
The Buffer Manager:
This subsystem handles all the memory management issues between the Query Engine and the Storage Manager. The Query Cache resides on the Buffer manager that actually manages to store the most recent results on it for faster access.
The Transaction Manager:
The function of the transaction manager is to facilitate concurrency in table access. This subsystem provides a locking facility to ensure that multiple simultaneous users access the data in a consistent way without corrupting or damaging the data in any way. Transaction control takes place via the lock manager subcomponent, which places and releases locks on the various objects being used in transactions. Each transactional table handler implements its own transaction manager to handle all the locking and concurrency needs.
The Recovery Manager
The Recovery Manager’s job is to keep copies of data for retrieval later, in case of a loss of data. It also logs commands that modify the data and other significant events inside the database.
So far, only the InnoDB and BDB table handlers provide recovery management. The MyISAM handler doesn’t have transactional recovery procedures, but it does provide mechanisms that apply certain recovery features in case of a server outage; these features “fix” any internal inconsistencies that might occur as the result of such a crash. Such inconsistencies are usually related to indexes not being properly updated to reflect the contents of a table or records being incompletely written to a database.
MySQL Extensibility:
(MySQL doesn’t support Stored Procedures)
In most RDBMS products, you can extend the capabilities of the database by using stored procedures. The programmability is usually further extended by enhancements to SQL that contains control-of-flow statements and conditional logic, as SQL Server does with T-SQL and Oracle with PL/SQL.
As of yet, MySQL includes no support for stored procedures, but one of the great benefits of this RDBMS is its extensibility. In keeping with its open-source roots, MySQL makes the original source code available as part of the distribution, which permits developers to add new functions and features that are compiled into the engine as part of the core product. MySQL also allows separate C and C++ libraries to be loaded in the same memory space as the engine when MySQL starts up.
Either of these methods will allow users to interact with your functions in the same way as they would with any of the already built-in functions available in MySQL, such as SUM() or AVG(). Because these functions run in the same memory space as MySQL, and because they execute on the server where MySQL is located, using them minimizes network traffic between the calling program and the server, thereby vastly increasing performance.
You can add functions to MySQL through a special user-defined function interface. User-defined functions are created initially as special C/C++ libraries and are then added and removed dynamically by means of the CREATE FUNCTION and DROP FUNCTION statements. User-defined functions come with the added burden of having to install all your libraries with every installation of MySQL; however, this does make deployment faster, since you can load these functions into the binary distribution rather than having to go through the trouble of compiling MySQL all over again to incorporate the new functions. In addition, there’s generally a good chance that your libraries will continue to work unchanged as new versions of MySQL are released.
You can also add functions as native (built-in) MySQL functions. Native functions are compiled into the MySQL server engine and become a permanent part of the MySQL installation itself. This makes new installations much easier than using libraries, because once you have defined your distribution, you can be sure that all of your subsequent installations will contain the functions that you have added. On the other hand, you must recompile and re-create your distribution for every new release of MySQL.
MySQL Security:
Two levels of security.
At the Login
At the usage of the Objects of the database
Remote access of the MySQL database can happen over TCP/IP or Named Pipes that can be setup for access using either of SSH (Secure Shell) or SSL( Secure Socket Layer) encryption protocol.
MySQL Storage Engine Architecture:
When you create a new table in MySQL then there is the possibility to define the table type or storage engine at the end of your table definition. This is not a mandatory part of your table definition. If you don't define an engine then the default one will be used. Later you can change the storage engine by ALTER TABLE command.
Here is an example how to create a table specifying a storage engine:
Code:
1. CREATE TABLE demo
2. (
3. username VARCHAR(50) NOT NULL,
4. age SMALLINT NOT NULL
5. ) ENGINE=MyISAM;
Instead of ENGINE keyword you can also use TYPE, however the preferred one is the ENGINE as the TYPE is only for backward compatibility.
MySQL Storage engines
In MySQL you have the possibility to select from the following storage engines:
- MyISAM
- InnoDB
- MERGE
- MEMORY
In older MySQL version the BDB and ISAM table types were also available but in the new versions they are not more supported. Besides this in MySQL 6 there will be a new table type called Falcon.
In this article I will focus only on the most commonly used types, listed above
MyISAM
MyISAM is the default storage engine in MySQL. It is the improved replacement of the old ISAM table type. Using MyISAM storage engine every table is stored in 3 different files:
- .frm file which stores the table structure
- .MYD file, which stores the table data
- .MYI file which is the index file
MyISAM storage engine is optimized for speed and supports extensive indexing. You can index BLOB and TEXT and also supports FULLTEXT indexes.
However MyISAM tables do not supports foreign key constraint and row level locking.
InnoDB
InnoDB is a transaction-safe storage engine in MySQL. Table data are managed by the InnoDB table space. This table type supports foreign key constraints and row level locking as well. However FULLTEXT indexes are not supported.
MERGE
The MERGE storage engine is a bit special. In this case the data are not stored in the MERGE table, but in the MyISAM tables from which the virtual MERGE table was made up.
MEMORY
In case of MEMORY storage engine the data are stored in the memory and are available only as long as the MySQL server is available. The MEMORY tables are very fast and so they are ideal for temporary tables.
No comments:
Post a Comment