MARIADB's query and storage engine

In a relational database, if you want to design a table mechanism to store data, you must satisfy the basic paradigm, at least the first three paradigms.

First normal form (1NF): Refers to a domain (Domain representative field) added in the relational model of a specification requirements, all domains should be atomic (non-detachable), that is, each column of the database table is an indivisible atomic data item, but not a collection, array, record, and other non-atomic data items. That is, when an attribute in an entity (each row) has multiple values, it must be split into different properties. Each domain value in a table that conforms to the first normal form (1NF) can only be a property of an entity or part of an attribute. In short, the basic requirements of the first paradigm are non-repeating domains.

The second paradigm (2NF) is established on the basis of the first paradigm (1NF), that is, satisfying the second paradigm (2NF) must first satisfy the first normal form (1NF). The second paradigm (2NF) requires that each instance or record in a database table must be able to be divided by a unique region (no two rows are the same). Select a property or group of attributes that can differentiate each entity as a unique identifier for the entity.

The third paradigm (3NF) is a subset of the second paradigm (2NF) that satisfies the third normal form (3NF) that must satisfy the second normal form (2NF). In short, the third paradigm (3NF) requires that a relationship cannot contain non-primary keyword information already contained in other relationships. In short, the third paradigm is that a property does not depend on other non-principal attributes, that is, any non-primary attribute must not be passed on the primary attribute on the basis of satisfying 2NF.

Relational operations can have complex query operations to achieve more complex table and table operations, for example, to join the table to do the Cartesian product, natural connection, outer connection, left outer connection, right outer connection, full outer connection, inner connection, self-connection, etc. are relational operations;

Databases: tables, indexes, views (virtual Tables)

View is also known as the virtual table, is actually stored down the SELECT statement, the query results of the SELECT statement is displayed as a table form, but in fact can put a statement to save, and then the statement itself to do the query operation, similar to do a sub-query;

MySQL is now primarily maintained by Oracle, and MySQL and mariadb are still compatible with the basic functionality, two different release routes;

The difference between MySQL and mariadb in many solutions is that, although each relational database management system requires its own physical layer, the storage engine will eventually convert the logical component to a physical component to be stored on the file system, but MySQL uses a very ingenious scheme Called the plug-in storage engine, also means that MySQL can support a large number of storage engines, and can still develop a proprietary storage engine, in MySQL and mariadb there are many storage engines to choose, some open source may be the additional purchase of commercial version;

In addition, different storage engines because they are the physical layer of implementation, so that the MySQL support for some features is determined by the storage engine; For example, whether MySQL supports transactions depends on whether the storage engine supports itself; processing mysql-server is a key component And the storage engine is another key core element;

The second feature is that MySQL is a single-process multithreaded model, which means that MySQL can see the process is only one, its internal multi-threaded to complete different functions, the most critical of the many threads in two categories,
The class is called the connection thread, the class is called the daemon thread, and many other threads, the connection thread is mainly responsible for maintaining the user connection, and the daemon thread consists of brushing the corresponding data from memory to disk, loading the data from disk into memory to maintain a cache and buffer, etc. are done by the daemon thread;

Single-process multithreading due to the early 5.1 version of MySQL, itself is not to face the enterprise level, especially heavyweight enterprise application; So MySQL is a single-process model, it is not particularly good for multi-CPU core applications, and later to 5.5 MySQL can support up to 64 core uncertainty? However, the entire run of a SELECT query statement, regardless of the number of CPUs supported by MySQL server, can only be run on one CPU per query statement, which makes it impossible for a complex query statement to parse quickly;

In addition, it is not possible to use a large amount of memory space, and now can support more and more memory space; MySQL continues to evolve through continuous technological improvements;

Gets the various parameters that are used by the running MySQL process

MariaDB [(None)]> show Global Variables\g

MariaDB [(None)]> Show Session Variables\g

Modifying the value of a server variable

Modifying global variables, valid only for newly connected sessions, invalid for current session

MariaDB [(None)]> set global system_var_name=value;

MariaDB [(None)]> set @ @global. system_var_name=value;

Modify the current session variable to expire the session

MariaDB [(None)]> set session System_var_name=value;

MariaDB [(None)]> set @ @session. system_var_name=value;

State variables: Used to save historical statistical variables in the mysqld run

MariaDB [(None)]> show Global Status\g

MariaDB [(None)]> Show Session Status\g

Sql_mode: Defining the response behavior of mysqld to constraints, etc.

MariaDB [(None)]> Show session variables like ' Sql_mode ' \g*************************** 1. Row ***************************variable_name:sql_mode value:1 row in Set (0.00 sec)

The usual mode:

Traditional: Traditional model, requires not to allow the insertion of illegal values;

Strict_trans_tables: Represents strict constraints on all tables that support transaction types;

Strict_all_tables: Indicates strict constraints on all tables;

The execution flow of the SELECT statement:

From Clause--WHERE Clause--and GROUP by--having Clause----ORDER by---SELECT

Distinct: Data de-weight

Field display can use aliases

GROUP BY: Groups query results based on specified criteria for aggregation operations

Common functions:

AVG (), Max (), Min (), count (), SUM ()

Having: specifying filter conditions for results after grouping aggregation operations

ORDER BY: Sort by the specified field, default to ascending asc,order by NAME desc to descending order

limit [[Offset,]row_count]: Limit the number of rows displayed for the results of a query

To apply a lock to data:
Write Lock: Exclusive lock, lock, must wait for everyone else to release the read lock before you can

Read lock: Shared lock, cannot read lock when other people write lock

MariaDB [hellodb]> Lock table students read|write;

Unlock tables;

Multi-Table query:

Cross-linking: Cartesian product (polynomial multiplication);

Internal connection:

Equivalent connection: Allows the fields between tables to establish a connection relationship with equivalence;

No equivalent connection:

Natural connections:

Self-connect:

External connection:

Left OUTER join: Each item in the left table appears, and the right table does not have a blank

From TB1 left JOIN TB2 on Tb1.col=tb2.col

Right outer connection:

From tb1 right JOIN TB2 on Tb1.col1=tb2.col

Subquery: A query that is nested within a query statement, and that is repeated based on the query result of a statement

Subquery used in Where:

1, used to compare the sub-query in the expression, sub-query can only return a single value;

Select Name,age from Students WHERE age> (select Avg. from students);

2, in the subquery, sub-query should be a single key query and return one or more values from the constituent list;

Select Name,age from Students WHERE-age-In (select-age from Teachers);

3. Use exists; in the WHERE clause;

Subquery used in from:

SELECT tb_alias.col1, ... From (SELECT clause) as Tb_alias WHERE clause; (deprecated, the tables obtained by subqueries must be represented by aliases)

Union query: Union, merge two SELECT statement query results into one;

MySQL Storage engine:

Whether transactions are supported depends on the storage engine, which is a table-level concept;

MyISAM does not support transactions; You can choose which storage engine to use when creating a database;

Transactions must follow: acid, atomicity, consistency, isolation, persistence;

In order to guarantee the four characteristics of acid, it is necessary to use redo and undo two kinds of logs to ensure that the unfinished transaction can be rolled back called undo at any time, the completed transaction is written to disk called redo;

Database storage is generally done in the transaction log, uncommitted transactions rolled back, the transaction has been committed, only the transaction log is not saved on the disk, you need to read the transaction log file, save to the data file;

InnoDB storage engine, the data stored in a file, is a black box, and varnish is the same, the data cache in varnish is stored in a file, but its internal completely self-organized results from the outside seems to be a file, the internal can have their own metadata, Data is a file system built on top of a filesystem, and InnoDB, by default, can place n tables in a table space, and externally it looks like a table with n tables and even the index of n tables, but it is difficult to put the index of n tables in the same table to achieve advanced functionality such as import and export like single table, so this is why when installing MARIADB, it is required to use innodb_file_per_table which means that each table uses a table space;

InnoDB table space in order to be able to cross the file system (partition), the InnoDB table space behind the file can be more than one, but also can automatically grow; When the table space was just created, it occupies a fixed size of space, if the wish space, then occupy a fixed size of space, use not all occupy ; it can be understood that it is based on stepping; InnoDB all tables and indexes into a single table space;

Specify the storage engine when creating the table:

CREATE TABLE ... Engine[=]storage_engine_name ...

To display status information for a table:
SHOW TABLE status[like| WHERE]

The InnoDB storage engine is a transactional storage engine that is suitable for handling a large number of short-term transactions;

Support high concurrency based on MVCC, support all four isolation levels, default level is repeatable read, support Gap lock to prevent phantom reading;

First Level: READ UNCOMMITTED READ UNCOMMITTED

Second Level: Read Committed

Third level: can reread repeatable read

level Fourth: Serializable serializable

The higher the level of the transaction, the less the conflict, the higher the transaction security, but the less concurrency;

The most important component in transaction isolation is the lock, so that the transaction is not concurrent, since the lock, it appears that the transaction started at the same time, but also waiting to be completed, so the isolation level is critical;

MyISAM internal complex mechanism is very few, especially adapted to read and write less applications; however, MySQL's own table, the internal metadata of the library is still used by the MyISAM storage engine;

The largest feature of the MyISAM storage engine is the support for full-text indexing, full-text indexing refers to the full text of each of the keywords can be searched;

The difference between a search engine and a MySQL query:

For the relational database, in MySQL, for example, the index is built in which field, you can query which field, if there is no index, can query, but the performance is very poor, even if there are indexes, the query conditions are very unique; MySQL uses a balanced tree index, the most important feature of the Balanced Tree index is the leftmost prefix mode, That is, if the amount of data in a field is large, such as using varchar or the text type, a field can hold hundreds of or even thousands of bytes of data, and in order to reduce the index volume, the index is generally indexed with only a limited number of bytes, such as the first 100 bytes; According to the condition query can only find the first 100 bytes of keywords, not be added to the index of the query is not, this is where the full-text index, the full-text index is not the leftmost of a single field of the finite byte, but the whole row of content can be indexed, can be queried according to any keyword, And then the full-text index is similar to the characteristics of the search engine, the search engine can be based on a keyword directly can be found in the document is a page, but in the storage is stored as a document; So search engine index of the inverted mechanism, you can query the document according to one or several keywords But the MySQL index is based on a table to query a row of data, which is the difference between the search engine and MySQL index;

The full-text index is somewhat similar to the concept of a search engine, while MyISAM supports full-text indexing and requires specialized full-text indexing functions, and supports spatial indexing based on spatial functions;

MyISAM after the crash can not be safe recovery, it is possible to cause some data loss, such as to enter a row of data, just half of the database crashes, only the half-line just inserted to delete, to ensure that the data is consistent;

The workaround is that MyISAM has a table repair tool that scans the whole table, a row of rows, to see which row of data is not completely deleted; So this is the recovery after the crash, if the table is large, the recovery time will be very long; A repair operation that can take a long time (a few minutes or a few 10 minutes) is allowed to use MyISAM; this longer refers to the smaller table, which is possible if the table is large enough to fix the day;

MyISAM Storage Engine Features:

Locking and Concurrency: Support table-level lock;
FIX: Can only be repaired manually or automatically (using tools), but may lose data; not safe recovery; Index: Non-clustered index used;
Supports deferred update of indexes;
Support table compression, compressed table data can not be modified;

For the storage engine, the MyISAM in MARIADB only supports table-level locks, does not support transactions, and InnoDB supports row-level locks, so the granularity is smaller; in fact, the lock is the important component of MySQL to implement concurrent access control;

Lock strategy: A balance mechanism between lock granularity and data security;

The cost of small lock granularity is large; MySQL supports two forms of lock: MYSLQ lock and storage engine lock;

A MySQL lock is a lock applied by the MySQL core component;

Locks imposed by the storage engine are called storage engine locks;

MySQL also implements locks at the server level, but only table-level locks are supported; The storage engine level uses table-level and row-level locks to be automatically managed, but cannot be requested manually;

Depending on whether the lock itself is manually imposed by the user or automatically implemented by the server, there are two types of locks:

Explicit Lock: The user manually requests the applied lock;

Implicit lock: A lock that is applied by the storage engine (or MySQL server) on demand;

MySQL transaction:

Transaction log: In the case of MySQL is another storage area outside the data file, this storage area has the function that when a transactional storage engine is running, it needs to start a transaction and complete the data modification, all the modification operations (consisting of multiple SQL statements) Each time the operation involves the data modification, this change to the operation to convert to the underlying storage engine supported by the relevant operating procedures, it will each SQL statement involved in the operation of the procedure, the specific process recorded in the transaction log file;

Note that the specific action of each step is recorded, for example, what data is inserted into a row, where it is inserted, when it is inserted, and so on, for example, if a few lines are modified, which row is modified, which row is modified, what time is changed, what is the end, and so on? , and this log to be able to reproduce the operation is called the transaction log;

A transactional storage engine its operations rely on the transaction log to ensure its corresponding transaction characteristics, so, any transactional storage engine related operations, the default is not directly written on the database file, but first written in the transaction log, and the transaction log is guaranteed enough reliable, basically rarely in memory buffer, write a file basically poor , the memory is written first and then synchronized to disk after a while;

The transaction log also has this buffer, the buffer can not put too much time can not be too long, because if the cache is allowed for 5 seconds, if the system crashes the maximum loss of 5 seconds of data, it is recommended that the time is short enough, generally 1 seconds to sync to disk once, but the more frequent synchronization performance is worse, The higher the data reliability is;

At each step, the original data content before the modification is also recorded, which is recorded to support the undo mechanism;

The transaction log supports redo and undo, and when the record is modified, the data before the modified data is recorded;

If, after half of the service crashes, the modified operation log is revoked, because it is not synchronized to disk;

If in order to ensure the stability of the data, a large transaction may be in the middle of the transaction has not completed, has logged 30 in the transaction log, and then the transaction log will be a part of the information to the disk synchronization, and start to really modify the original data, then crash, you can only synchronize the data has been synchronized to the table, need to participate ；

Another situation is that, for example, a transaction has 60 SQL statements are written and recorded in the transaction log, but only 30 of the statements are synchronized to disk, the data is inconsistent, because some transactions have been committed, but not stored on disk, so that the transaction log in the incomplete statement must be synchronized to disk , synchronous to do the submission, did not complete to do rollback; this is called the crash after the recovery;

If the log file is very large, it is full of crashes, the next time MySQL starts, you have to synchronize all the statements inside to disk to start properly, if the transaction log file is large to 2G, in order to be able to synchronize the 2G statements to disk, it is possible that the MySQL server started half an hour; In order to avoid the crash after the boot time is too long, the log file set a little bit, but sometimes, the log fills up, but the log content has not been rushed to the disk, there is a new log to write in, and then start a file;

It is said that the log files generally start 2 or 3 are rotary use, the so-called rotation refers to the first log file is filled with the second, while the first log file synchronization to disk; When the second log is full, you can use the first one, so that the rotation is used; There are multiple transaction log files called transaction log groups, There should be at least two files in the log group, but more is not good;

Transaction log files and data files should not be placed on the same disk because there is a lot of pressure on the disk write IO operations, which affects their performance, and separate storage is ideal, but some scenarios must be put together, for example, based on logical volume operations;

If the disk on which the transaction log is located crashes, the database data cannot be consistent, so the transaction log disk is mirrored, the RAID1 is recommended, and the data file is also important to suggest the use of RAID10;

Transactions: A set of atomic SQL queries, or multiple SQL statements that make up an independent unit of work;

Transaction log: Converts random write to sequential write;

Determine if the storage engine supports transactions: acid testing

A:automicity, atomicity; all operations in the entire transaction are either successfully executed or rolled back after all failures;

C:consistency, consistency; the database always transitions from one consistency state to another;

I:isolation, isolated; an operation made by a firm cannot be seen for other transactions until it is committed, there are multiple levels of isolation, mainly for concurrency; there are 4 isolation levels;

D:durability, persistence; Once a transaction is committed, its modifications are permanently stored in the database;

MyISAM storage Engine does not support transactions, InnoDB support transactions, so to use transactions to ensure that the use of InnoDB and other supporting transactions of the storage engine;

MySQL default to each statement as a transaction commit, you can manually turn off the auto-commit function, manually to start the transaction;

MARIADB's query and storage engine