Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

How mysql's server layer and Storage engine layer intersect each other

2025-02-25 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

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

The following will mainly show you how mysql's server layer and storage engine layer intersect with each other. I hope these contents can bring you practical use, which is also the main purpose of my article on how mysql's server layer and storage engine layer intersect each other. All right, don't talk too much nonsense, let's just read the following.

Mysql server system architecture

The logic module is composed of:

The mysql logic module can be divided into two layers: the first layer is sql layer, which mainly includes authority judgment, sql parsing, execution plan optimization, query cache processing and so on; the second layer is the storage engine layer storageengine layer, which is the underlying data access operation implementation part, which is composed of a variety of storage engines.

Mysql Storage engine:

The storage engine must be compiled and installed together with mysql when mysql is installed. Mysql ab makes the storage engine and sqllayer more independent and less coupled, and can load new storage engine online without affecting the normal operation of mysql. The architecture of the plug-in storage engine makes it more flexible and convenient to load and move the storage engine, and also makes it more convenient and simple to develop the storage engine. The main plug-in storage engines include mylsam, lnnodb, ndb cluster, maria, falcon, memory, archive, etc.

Introduction to the mylsam storage engine:

1. Default storage engine before mysql5.1

2. The tables of the mylsam storage engine are stored in the database, and each table is stored as three physical files named after the table: .frm .MYI. MYD each table has three such files as the storage type of the table, no matter how many engines the table has, they are all stored in the same .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. Table corruption can be caused by kill drop when mysqld is writing to the table, host downtime, disk hardware failure, and bug of mylsam storage engine.

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

Create a database, create a table, estimate how much space is planned.

View the structure of the qq table

Each byte adds up to 168bytes. If you add 10000 records every day, you will need about 10000x168/1024/1024=1.6MB.

If you find a problem with a mylsam table, you can verify it online through the check table command.

Introduction to the lnnodb storage engine:

The main reason why lnnodb is so popular is that it has many functional features: supporting transactions, improving the locking mechanism, implementing foreign keys, storing completely user-defined tables separately and storing all tables together.

The physical structure of lnnodb is divided into two parts:

1. Data file:

Stores the data in the data table and all index data, including primary keys and other general indexes.

two。 Log file:

The log file of lnnodb is similar to the redo log of oracle. You can also set multiple log groups (at least 2) and use a round-robin policy to write sequentially.

The difference between mylsam and lnnodb:

1.mylsam does not support transactions, but lnnodb supports transactions. Autocommit of lnnodb is turned on by default, that is, each sql statement will be encapsulated into a transaction by default. Auto-commit will affect the commit speed. It is best to display multiple sql statements between begin and commit to form a transaction commit.

Check to see if submit mode is on

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

3. InnoDB supports foreign keys, but MyISAM does not.

4. InnoDB does not support full-text indexing, but MyISAM does.

Mysqlslap performance test MySQL two storage engines

Mysqlslap is a benchmark tool that comes with mysql. Its advantages include querying data and being flexible to use.

View the default maximum number of connections to the mysql database

View the engine used by default

Using the sql script to test the following stress tests exceeds the default number, so an error is reported

Modify the number of stress tests and the maximum test value is 30. 60 will lead to errors. Note: multiple tests.

Introduction to the use of tools that come with MySQL

Mysql naming: one of the most frequently used naming tools to provide users with a command line interface to manage mysql CVM

Syntax format: mysql [options] [database] you can use mysql-- help to view usage help information

Parameters related to mysql commands commonly used in operation and maintenance

Use the-e parameter to view the database

Create databases and tables by using scripts to add, modify, delete and query tables

Use root users to enter mysql to authorize test users

Then grant execution permission to the script

Startup script

Use the prompt to log in to mysql to see the effect

Use tee to generate a file; this file contains all the operations in this database are saved to this directory

Use the ping command to test whether the service can be provided properly

Use the status command to get several basic status values for the current mysql server

Use processlist to get the connection thread information of the current database

Write mysql monitoring script

View the effect of execution and give the script execution permission chmod + x script name

View the data dictionary, which contains metadata for all other databases, including data types and access permissions

Do you find it very helpful to talk about how the server layer and storage engine layer of mysql intersect each other? If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

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.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report