Shulou(Shulou.com)06/01 Report--

MySQL logical system architecture: can be divided into connectors, connection pools, Sql interfaces, interpreters, optimization libraries, buffers, caches, engines

MySQL logical structure is deliberately regarded as a two-tier architecture.

The first layer is usually called SQL Layer to deal with the underlying data in the database system, including authority judgment, SQL parsing and execution plan optimization, query chche reasoning, and so on.

The second layer is the storage engine, which is often called storageEngine Layer, which is the implementation of the underlying data access operation.

It is composed of multiple storage engines.

Introduction to the MySQL storage engine:

In MySQL versions prior to 5.1, the storage engine had to be compiled and installed at the same time as MySQL when MySQL was installed.

At 5.1. At first, MySQL AB made great changes to its architecture, and introduced a new concept, "plug-in storage engine architecture", which makes the storage engine layer and SQL layer more independent, less coupled, and even larger to load new storage engines online without affecting the normal operation of MySQL.

As long as the plug-in storage engine of MySQL includes MyISAM,Innodb,NDB,Cluster,Maria,Falcon,Memory,Archive, MyISAM,Innodb is the most widely used.

Introduction to MyISAM Storage engine

1.MySQL5.1. Previously, the default storage engine

2 the tables of the MyIsam storage engine are in the database, and each table is stored in three physical files named after the table

A .frm file that stores table structure definition information

Store table data. MYD

Store the index .MYI of the table (no matter 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 means that all index nodes are stored according to the data structure of balance tree, and all index data nodes are on the leaf node.

The storage mode of R-Tree index is different from that of B-Tree. It is mainly designed to index fields of storage space and multidimensional data.

Full_text full-text index its storage structure is also B-Tree, mainly to solve the inefficient problem that we need to query with LIKE.

4 transactions are not supported

5 only watch locks

6 the table can be damaged under the following conditions:

Mysqld is writing to the table and is dropped by KILL

Host downtime

Disk hardware failure

Bug of the MyIsam storage engine

Introduction to the Innodb storage engine:

1 support for transactions

2 change of locking mechanism: row lock has been implemented

3 implement foreign keys

4 the Innodb storage engine also has .frm files, but whether the table data and index data are stored together, it is up to the user to decide whether each table is placed separately or all tables are stored.

Introduction to the use of tools that come with MySQL

MySQL command syntax format: MySQL [options] [database]

1 mysql-e (--execute=name) this parameter allows you to execute commands without logging in to MySQLserver through MySQL, which is very useful for writing some basic scripts

Liezi: MySQL-u root-p123456-e "show status like'% binlog_cache%'"

2 MySQL-E (--vertical) MySQL-E-u root-p after login, all query results will be displayed in a column

3 after MySQL-H or MySQL-X is enabled, the select results will be output according to the format of HTML in xml.

4-- the main function of prompt=name is to define the display content of your own MySQL prompt. You can configure to display the login host address, user name, current time, and current database.

PS: highly recommended prompt definition "\ u @\ h:\ d, r:\ m:\ s >"

5-- tee=name is used to tell MySQL to record all inputs and outputs in a file that can be written in my.cnf.

[client]

Tee = / tmp/client_mysql.log

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.