Principles, installation, and basic use of MySQL

MysqlExecution Process:

MysqlIt is a single-process service, and every request is responded by a thread. This requires a connector to handle new user requests, responses, and destruction.

1.Client request, server (connector) opens thread to respond to user

2.User InitiationSQLStatement query database

3.Query cache: records users'SQLQuery statement. If the same content is queried again, the cache is returned.

4.If the cache does not enter the analyzer (the analyzer may also use the signature cache)

Syntax analyzer: whether the USER command syntax is correct

Lexical analyzer: slices USER commands. A word is separated by spaces.,Obtain the table, content, and permissions that the user wants to query.

5.Optimization: select the execution path to generate the execution tree

EachSQLStatements have many execution paths. The purpose of optimization is to select an optimal execution path among these execution paths.

6.Storage engine: used to manage data management programs stored in the file system or even on bare devices. It provides different management services for upper-layer applications, some support transactions, and some do not support transactions.

#The query cache can greatly accelerate this process, but the cache itself may bring disadvantages. If a user wants to query a piece of data, there is exactly in the cache, and before, if this data item has just been modified, the returned cache result is out of date.

MYSQLStructure:

Client:Mysql

Connector:NativeC APILocalCLanguage Interface

JDBCJAVADatabase Connector

ODBCOpen Database interconnection, very underlyingOracle,SOLServerAll supported

Connection Pool (Conetcionpool): The back of it isMYSQLTrue Functions

SQLInterface: the user passesSQLCommands sent from the clientSQLInterface receiving

DMLData operation language: Query, modify, and upgrade data.

DDLData Definition Language: Creates a new database, indexes, and deletes a user.

Stored Procedure view triggers

Parser:Analyzer

Query transaction object permissions

Optimizer: Access path

Generate execution tree

Caches & buffers: Cache and Cache

The most important thing in optimization is Cache Optimization.

Pluggablestroage enginesStorage engine (plug-in type): a program that converts a logical structure to a physical structure

ManagementServices & UtilitesTools: backup, recovery, security, transplantation, clusters, etc. These tools generally deal with file systems and do not needMysql-serverThey all correspond to commands.

Database Object: (logic)

Table, Two-Dimensional Relationship

Index

View

Trigger

Stored Procedure

Storage Functions

Event iterator Reader

Cursor

User

Physical

File

The storage engine maps database objects to physical objects.

Mysql

*Plug-in storage engine

MysqlAndMysqlConnection method:Tcp, socket, pipe, memory

MysqlInteraction method:Interact batch

SQL

Structured Query Language

Bash

Select, update, create, drop

MysqlInstall

Version selection:

GA

RC

Beta

Alpha

In future use,RPMThe package is very convenient, but it may cause various problems. Therefore, use the compilation and installation methods as much as possible.LFS

Mysql-client

Mysql-devel:Compile and install the software package.MysqlTo install the software package.

Mysql-embedded: mysqEmbedded

Mysql-ndb-management

Mysql-server

Mysql-shared:It may be used by some tools on the client or server.

Mysql-shared-compat

Mysql-test:TestMysql, Provides test components

GenerallyMYSQLYou only need to installClientdevel share server

Role of the installation directory

Bin

DataDirectory where data and indexes are stored by default

Received

Lib

Man

Mysql-test

ScriptsInitialization Script during installation

ShareFor error messages in a specific language, the original information file is stored.

SQL-ExampleStress testing tools

Support-fileProvides some Initialization Files and scripts

1 2 3 Next Page