MySQL server-storage engine

MySQL server system architecture

650) this.width=650; "src=" Https://s2.51cto.com/wyfs02/M02/98/9C/wKiom1k-nWaxKBCNAAIcOwbR-vs445.png "title=" Picture 1.png "alt=" Wkiom1k-nwaxkbcnaaicowbr-vs445.png "/>

The logic module consists of:

MySQL logic module can be divided into two tiers, the first layer is the SQL layer mainly includes the authority judgment,SQL parsing, Execution plan optimization,query Cache processing and so on; the second layer is the storage engine tier storageenginelayer, which is the implementation part of the underlying data access operation and consists of a variety of storage engines.

MySQL storage Engine:

the storage engine isMysqlthe installation must beMySQLhave been compiled and installed together,MySQL ABin the framework of the transformation, let the storage engine andSqllayerThey are more independent, less coupled, and can load new storage engines online without compromisingMySQLof normal operation. The architecture of the plug-in storage engine makes it easier to load and move the storage engine and make it easier to develop your own storage engine. The main plug-in storage engine includesMylsam,Lnnodb,NDB Cluster,Maria,Falcon,Memory,Archivewait

Introduction to the Mylsam storage Engine:

1,mysql5.1 before the default storage engine

2.mylsam Storage Engine table in the database, each table is stored as three table-named physical files:. frm. MYI. MYD Each table has such three files stored as a storage-type table, regardless of the number of engines that the table has, stored in the same . in MYI

Three types of indexes supported by 3.mylsam:b-tree index,r-tree index,full-text Index

4. do not support transactions and only table locks

5. mysqld is killed when the table is being written to , host down, disk hardware failure,Mylsam Storage Engine Bug will cause the table to break .

6.mylsam data storage is divided into static fixed length, dynamic variable length, compression of three formats

Creating a database creating a table estimating how much space to plan

650) this.width=650; "src=" Https://s1.51cto.com/wyfs02/M01/98/9C/wKiom1k-nXSzKDt5AAAbOTNiq78801.png "title=" Picture 2.png "alt=" Wkiom1k-nxszkdt5aaabotniq78801.png "/>

View The structure of the QQ table

650) this.width=650; "src=" Https://s4.51cto.com/wyfs02/M01/98/9C/wKioL1k-nYDxR4-ZAAAsysGTu_U172.png "title=" Picture 3.png "alt=" Wkiol1k-nydxr4-zaaasysgtu_u172.png "/>

each byte is added equal to 168 bytes, and if you add 10000 Records every day, you need to 10000X168/1024/1024=1.6MB

If you find a problem with a mylsam table , You can use the check table command online for validation

650) this.width=650; "src=" Https://s4.51cto.com/wyfs02/M00/98/9C/wKioL1k-nYqB-wpmAAAPkjTkcc8993.png "title=" Picture 4.png "alt=" Wkiol1k-nyqb-wpmaaapkjtkcc8993.png "/>

Introduction to the Lnnodb storage Engine:

Lnnodb can be so fire, mainly in the functional aspects of more features: support transactions, the improvement of locking mechanism, implementation of foreign keys, completely by the user-defined table of the separate storage and all tables are stored together.

the physical structure of LNNODB is divided into two parts:

1. Data files:

Holds data in the data table and all index data, including primary keys and other normal indexes.

2. Log files:

Lnnodb log files are similar to Oracle 's redo logs, and you can also set up multiple log groups (minimum 2 ), using a round-robin strategy to write sequentially

mylsam and lnnodb

1.mylsam does not support transactions, while lnnodb supports transactions,lnnodb autocommit is turned on by default, that is, each SQL statements are encapsulated as a single transaction by default, and autocommit can affect commit speed, preferably by displaying multiple SQL statements at the begin and commit , the constituent transaction commits.

See if the commit mode is turned on

650) this.width=650; "src=" Https://s2.51cto.com/wyfs02/M01/98/9C/wKioL1k-nZehJ0dPAAAT78IWPNU786.png "title=" Picture 5.png "alt=" Wkiol1k-nzehj0dpaaat78iwpnu786.png "/>

650) this.width=650; "src=" Https://s4.51cto.com/wyfs02/M02/98/9C/wKiom1k-naKjCSn6AAAc9sawlGE653.png "title=" Picture 6.png "alt=" Wkiom1k-nakjcsn6aaac9sawlge653.png "/>

2. InnoDB supports data row locking,MyISAM does not support row locking and only supports locking the entire table.

3. InnoDB supports foreign keys,MyISAM not supported.

4. InnoDB does not support full-text indexing, and MyISAM supports it.

Mysqlslap performance test MySQL two storage engines

Mysqlslap is the benchmark tool that comes with MySQL, and benefits: querying data, using flexible

View The default maximum number of connections for MySQL database

650) this.width=650; "src=" Https://s4.51cto.com/wyfs02/M02/98/9C/wKiom1k-neij5OmWAAANQbmTBrA209.png "title=" Picture 7.png "alt=" Wkiom1k-neij5omwaaanqbmtbra209.png "/>

View the engines used by default

650) this.width=650; "src=" Https://s3.51cto.com/wyfs02/M00/98/9C/wKiom1k-nfLSYzYQAABC-YG6DbE472.png "title=" Picture 8.png "alt=" Wkiom1k-nflsyzyqaabc-yg6dbe472.png "/>

use SQL script to test the number of stress tests below the default number so the error

650) this.width=650; "src=" Https://s5.51cto.com/wyfs02/M01/98/9C/wKiom1k-nf7DQvfCAAAcpyTRJe4188.png "title=" Picture 9.png "alt=" Wkiom1k-nf7dqvfcaaacpytrje4188.png "/>

Modify the number of stress tests and test the maximum value of 30,60 more than it will be an error note: Multiple tests

650) this.width=650; "src=" Https://s4.51cto.com/wyfs02/M00/98/9C/wKioL1k-ngvT68SgAADED9ZVwRE010.png "title=" Picture 10.png "alt=" Wkiol1k-ngvt68sgaaded9zvwre010.png "/>

MySQL comes with tool use introduction

MySQL naming: Use most of a naming tool to provide the user with a command line interface to manage the mysql server

syntax format:mysql [options] [database] can use mysql--help to View the use of Help information

MySQL command-related parameters commonly used in operation and maintenance

To view a database using the-e parameter

650) this.width=650; "src=" Https://s2.51cto.com/wyfs02/M01/98/9C/wKioL1k-nhWStNzIAAAVY-1rAtw295.png "title=" Picture 11.png "alt=" Wkiol1k-nhwstnziaaavy-1ratw295.png "/>

Add, change, delete, and check tables by using scripts to create databases and tables

650) this.width=650; "src=" Https://s1.51cto.com/wyfs02/M01/98/9C/wKiom1k-niHzb1r7AACVgeZLuG4858.png "title=" Picture 12.png "alt=" Wkiom1k-nihzb1r7aacvgezlug4858.png "/>

use root user to enter MySQL to Grant test User Authorization

650) this.width=650; "src=" Https://s5.51cto.com/wyfs02/M01/98/9C/wKioL1k-ni7wkzYWAAAKuMjMohw289.png "title=" Picture 13.png "alt=" Wkiol1k-ni7wkzywaaakumjmohw289.png "/>

Then grant execute permission to the script

650) this.width=650; "src=" Https://s4.51cto.com/wyfs02/M00/98/9C/wKiom1k-njmxxQ7aAAAEBFZfXTY438.png "title=" Picture 14.png "alt=" Wkiom1k-njmxxq7aaaaebfzfxty438.png "/>

Startup scripts

Use the prompt to log in to MySQL to see effects

650) this.width=650; "src=" Https://s5.51cto.com/wyfs02/M01/98/9C/wKioL1k-nmrgLpmYAABTyN2LRUU028.png "title=" Picture 15.png "alt=" Wkiol1k-nmrglpmyaabtyn2lruu028.png "/>

using tee to generate a file, this file contains all the operations in this database are saved in this directory

650) this.width=650; "src=" Https://s4.51cto.com/wyfs02/M00/98/9C/wKioL1k-nnrSVmS4AAAFNxwQYXU745.png "title=" Picture 16.png "alt=" Wkiol1k-nnrsvms4aaafnxwqyxu745.png "/>

650) this.width=650; "Src=" Https://s3.51cto.com/wyfs02/M01/98/9C/wKioL1k-noSC2-MeAAAztED0mMI677.png " title= "Picture 17.png" alt= "Wkiol1k-nosc2-meaaazted0mmi677.png"/>

use the ping command to test for service availability

650) this.width=650; "src=" Https://s3.51cto.com/wyfs02/M01/98/9C/wKioL1k-n-iQfllHAAAgcY60JBo107.png "title=" Picture 18.png "alt=" Wkiol1k-n-iqfllhaaagcy60jbo107.png "/>

use the status command to get several basic status values for the current MySQL server

650) this.width=650; "src=" Https://s5.51cto.com/wyfs02/M00/98/9C/wKiom1k-n9vg6koIAAAVj2UaHCU914.png "title=" Picture 19.png "alt=" Wkiom1k-n9vg6koiaaavj2uahcu914.png "/>

use processlist to get connection thread information for the current database

650) this.width=650; "src=" Https://s2.51cto.com/wyfs02/M00/98/9C/wKiom1k-n8_zR-9wAAAoBhOmxzo887.png "title=" Picture 20.png "alt=" Wkiom1k-n8_zr-9waaaobhomxzo887.png "/>

writing mysql monitoring scripts

650) this.width=650; "src=" Https://s1.51cto.com/wyfs02/M02/98/9C/wKioL1k-n8XiyhkHAAA4TjuimMc246.png "title=" Picture 21.png "alt=" Wkiol1k-n8xiyhkhaaa4tjuimmc246.png "/>

View the effect of execution and Execute permissions for script chmod +x script name

650) this.width=650; "src=" Https://s1.51cto.com/wyfs02/M02/98/9C/wKiom1k-n7fCI1akAAB5qhnR1fo284.png "title=" Picture 22.png "alt=" Wkiom1k-n7fci1akaab5qhnr1fo284.png "/>

View a data dictionary that contains metadata for all other databases, including data types, access rights

650) this.width=650; "src=" Https://s3.51cto.com/wyfs02/M01/98/9C/wKiom1k-n6zzNmkJAABMZIz8Cm0044.png "title=" Picture 23.png "alt=" Wkiom1k-n6zznmkjaabmziz8cm0044.png "/>

This article is from the "Centos7" blog, make sure to keep this source http://12832314.blog.51cto.com/12822314/1934710

MySQL server-storage engine