Storage of the front-end learning Database

xCatalog [1] stored procedure [2] in front of the storage engine

We often work on inserting, deleting, updating, and finding data sheets, which is what we often call curd. In fact, when we enter the command, the MySQL engine will follow the action

If we omit the analysis and compilation process, then the execution efficiency will be greatly improved. This requires the storage described below to implement

Stored Procedures

Stored procedures are precompiled collections of SQL statements and control statements that are stored as a single name and processed as a unit. Stored procedures are stored in the database and can be executed by an application invocation, allowing the user to declare variables and process control. Stored procedures can receive parameters (input type parameters, output type parameters), and multiple return values can exist. Therefore, the execution efficiency of a stored procedure is higher than that of a single SQL command.

Advantages

1. Enhance the function and flexibility of SQL statement

2, achieve a faster execution speed. When the client calls the stored procedure for the first time, the MySQL engine parses it, compiles it, and then stores the result in memory, so the first time is the same as the previous one, but the result is directly called in the memory, and the efficiency is improved.

3, reduce network traffic. A single SQL statement has a large number of characters, and by calling a stored procedure you only need to pass the stored procedure name and related parameters, and the amount of data submitted to the server is relatively small

Grammatical structure

= {User | Current_User}]  PROCEDURE sp_name ([proc_parameter[,...]])  [Characteristic ...] Routine_body

In indicates that the value of the parameter must be specified when the stored procedure is called and cannot be returned

Out indicates that the value of this parameter can be changed by the stored procedure and can be returned

InOut indicates that the parameter is specified at call time and can be changed and returned

Characteristic (properties)

' string '| {CONTAINS SQL | NO SQL | READS SQL DATA | modifies SQL DATA}| SQL SECURITY {definer | INVOKER}

COMMENT: Notes

CONTAINS sql: Statements that contain SQL statements but do not contain read or write data

No sql: does not contain SQL statements

READS SQL Data: Statements that contain read data

Modifies SQL data: Statements that contain write data

SQL SECURITY {definer | INVOKER}: Indicates who has permission to execute

Process Body

1. The process body is composed of legitimate SQL statements;

2. The process body can be "any" SQL statement (here, any of the main refers to the record additions and deletions to change, multi-table connection);

3. If the process body is a composite structure, use begin ... End statement;

4. Composite structures can contain declarations, loops, control structures

Create a stored procedure with no parameters

CREATE PROCEDURE SP1 () SELECT VERSION ();

Call a stored procedure

Mode one: Call Sp_name ([parameter[,...]]) If the stored procedure contains parameters, you must have parentheses

Mode two: Call sp_name[()] If the stored procedure does not contain parameters, the parentheses are optional

modifying stored procedures

' string '| {CONTAINS SQL | NO SQL | READS SQL DATA | modifies SQL DATA}| SQL SECURITY {definer | INVOKER}

You can only modify the annotations in a stored procedure, the type of the current content, and you cannot modify the procedure body. To modify the process body, you need to delete the stored procedure and then rebuild

To delete a stored procedure

DROP PROCEDURE [IF EXISTS] Sp_name

Create a stored procedure with an in type

  = p_id; END//DELIMITER;

The following call to the stored procedure

To create a stored procedure with an in and out type parameter

  = p_id; SELECT count (ID) from the users into Usernums; END//DELIMITER;

MySQL variable classification

1. User variables: Start with "@", in the form "@ Variable name"

The user variable is bound to the MySQL client, and the set variable is only valid for the client used by the current user

7;

2. Global variables: When defined, appears in the following two forms, set global variable name or SET @ @global. Variable Name

Effective for all clients. You can set global variables only if you have super permissions

3. Session variables: Valid only for connected clients

4. Local variables: The action range is between the begin and end statement blocks. The variable that is set in the statement block

The Declare statement is specifically used to define local variables. Set statements are variables of different types, including session variables and global variables

The following call to the stored procedure

Call Removeandreturnusersnums (1, @nums);

To create a stored procedure with multiple out type parameters

  = p_age; SELECT Row_count () into deleteusers; SELECT COUNT (ID) from the users into usercounts; END//DELIMITER;

Note The Row_count () function is used to get the total number of affected records inserted, deleted, and updated

The following call to the stored procedure

Call Removeuserbyageandreturninfos (@a,@b);

[Note]@a indicates the number of records deleted, @b indicates the number of records remaining

SELECT @a,@b;

The difference between a stored procedure and a custom function

1. The functions of the stored procedure implementation are more complex, and the functions are more targeted

2. Stored procedures can return multiple values; function can have only one return value

3. Stored procedures are generally performed independently, whereas functions are primarily used as part of other SQL statements

Storage Engine

MySQL can store data in files (memory) in different technologies, a technique known as the storage engine. Each storage engine uses different storage mechanisms, indexing techniques, locking levels, and ultimately offers a wide range of different functions

In a relational database, the storage of data is implemented in the form of a table. Therefore, the storage engine can also be called a table type. So, in fact, the storage engine is a technique for storing data and querying data.

MySQL-supported storage engines include MyISAM, InnoDB, Memory, CSV, Archive

concurrency control　

Concurrency control is the consistency and integrity of data that is guaranteed when multiple connections modify records

For example: two users log in and manipulate the database at the same time, where one user deletes a record and another user reads the record, which requires concurrency control, otherwise an error or invalid message is returned

When dealing with concurrent ' read ' or ' write ' operations, MySQL implements concurrency control through the lock system, including shared and exclusive locks

-Shared lock (read lock): In the same time period, multiple users can read the same resource, the data will not be changed during the reading process

-Exclusive Lock (write lock): Only one user can write to the resource at any time, while the write lock will block other read or write lock operations

The lock grain (also known as Lock Force) is the unit at which the lock is locked. You just need to lock the modified data exactly, without having to lock all the resources.

Locking increases system overhead, so you need to find a balance between lock overhead and system security through a lock policy. MySQL lock policy includes table lock and row lock policy

-table lock, which is a locking strategy with minimal overhead

-row lock, which is the most expensive lock policy

Transaction processing

Transactions are one of the important characteristics of a database that differs from a file system, and transactions are primarily used to ensure database integrity

Transactional features include: atomicity (atomicity), consistency (consistency), isolation (isolation), persistence (durability), acid

Index

An index is a structure that sorts the values of one or more columns in a data table and uses the index to quickly access specific information about the data table. Indexing is a way to record quick positioning, similar to a book's Directory

Indexes include normal index, unique index, full-text index, index of btree, hash index, etc.

Features of various storage engines

In addition to the above storage engines, there are several less common engines

The CSV storage engine does not support indexes, and comma-separated values (comma-separated values,csv, sometimes referred to as character-delimited values, because delimited characters can also be not commas), whose files store tabular data (numbers and text) in plain text. Plain text means that the file is a sequence of characters and does not contain data that must be interpreted like a binary number. A CSV file is made up of any number of records, separated by some sort of line break

Blackhole is also called the Black hole engine, the data written will disappear, usually used to do data replication of the relay

The MyISAM engine is suitable for situations where things are not handled much.

Modifying the storage Engine

1, by modifying the MySQL configuration file implementation

default-storage-engine = Engine

2. By creating a data Table command

CREATE TABLE table_name (  = engine;

3, by modifying the data Table command to achieve

ALTER TABLE table_name ENGINE [=] engine_name;

Storage of the front-end learning Database