MySQL Logic architecture (i)

Source: Internet
Author: User

MySQL Logic System Architecture: can be divided into connectors, connection pool, SQL interface, interpreter, optimization library, buffer, cache, engine
The MySQL logical structure is deliberately viewed as a two-tier architecture,
The first layer is usually called the SQL layer in the database system to process the underlying data including the permission to judge, SQL Resolution Execution Plan optimization, query Chche and so on
The second layer is the storage engine, often called the Storageengine layer, which is the implementation part of the underlying data access operation.
A variety of storage engines together
Description of the MySQL storage engine:
MySQL in versions prior to 5.1, the storage engine needed to be compiled with MySQL and installed at the same time when MySQL was installed,
In 5.1. Start MySQL AB has made a big change in its architecture, and introduced a new concept, "plug-in storage engine architecture", so that the storage engine layer and the SQL layer are more independent, the coupling is smaller, even bigger online load new storage engine, Without affecting MySQL's normal operation
MySQL's plug-in storage engine includes myisam,innodb,ndb,cluster,maria,falcon,memory,archive and so on, of which MYISAM,INNODB is most widely used.
MyISAM Storage Engine Introduction
1.mysql5.1. Previous default storage engine
2 MyISAM Storage Engine table in the database, each table is stored in three physical files named after the table name
. frm file that holds table structure definition information
The data that holds the table. MYD
The index of the storage table. MYI (regardless of how many indexes the table has, it is stored in the same. MYI file)
3 MyISAM supports the following three types of indexes:
B-tree index is all the index nodes are stored according to balance tree data structure, all the index data nodes are on the leaf node
R-tree indexed indexes are stored differently than b-tree, and are primarily designed to index fields of storage space and multidimensional data
Full_text Full-text index his storage structure is also b-tree, primarily to address inefficient issues that we need to use like queries
4 Transaction not supported
5 table lock only
6 The following conditions can cause table corruption:
Mysqld is writing to the table and is killed.
Mainframe downtime
Disk hardware failure
MyISAM the storage engine bug
Innodb Storage Engine Description:
1 Support for transactions
2 Locking mechanism change: implementation of row lock
3 Implementing foreign keys
4 Innodb Storage engine also has a. frm file, but table data and index data are stored together, each table alone release vessel is all tables stored by the user.
MySQL comes with tool use introduction
mysql command syntax format: mysql [options] [Database]
1 mysql-e (--execute=name) This parameter can be useful for writing basic scripts without logging into MySQLServer with MySQL and executing commands
Mysql-u root-p123456-e "Show status Like '%binlog_cache% '"
2 mysql-e (--vertical) mysql-e-u root-p all query results are displayed in a vertical column
3 mysql-h or mysql-x The result of select will be output as HTML in XML format.
4--prompt=name main function is to set its own MySQL prompt display content can be configured to display the login host address, user name, current time, current database
PS: Highly recommended prompt definition "\[email protected]\h:\d,, r:\m:\s>"
5--tee=name is used to tell MySQL to record all inputs and outputs in the file contents can be written in my.cnf
[Client]
Tee =/tmp/client_mysql.log

MySQL Logic architecture (i)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.