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

This article mainly explains "the arrangement of MySQL basic knowledge points". The content of the explanation in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "MySQL basic knowledge points arrangement".

There are still a lot of noun concepts related to MySQL, but not many of them are commonly used, so it is easy to review the commonly used statistics:

DQL:data query language, refers to SELECT query statement; DML:data manipulation language, refers to SELECT, UPDATE, INSERT, DELETE four kinds of data manipulation statements; DDL:data definition language, data definition statement, create / modify / delete table structure, mainly CREATE, ALTER, DROP, etc.; MDL lock: Metadata Locking, where metalock refers to the database and table structure information. In MySQL, DDL does not belong to the transaction category. If the transaction and DDL execute in parallel and operate the associated tables, there will be all kinds of unexpected problems, such as the transaction characteristics are destroyed or the binlog sequence is out of order. MDL lock mechanism is introduced to solve these problems.

Three paradigms

The first paradigm: each field is atomic, that is, it can no longer be decomposed; the second paradigm: there is a primary key, and the non-primary key field depends on the primary key field; the third paradigm: non-primary keys can not be interdependent.

Note that the three paradigms are the suggested design principles of data tables, and they do not have to be designed entirely according to this, but should be analyzed according to the actual scenario. There are usually multiple representations of any given data, complete paradigm and anti-paradigm, and a compromise between the two. In the normalized database, any data will appear only once, on the contrary, in the anti-normalization, the data is redundant.

ACID

ACID is the four characteristics of a transaction, namely atomicity, consistency, isolation, and persistence.

A:atomicity, atomicity, a transaction must be regarded as an indivisible minimum unit of work, all operations in the whole transaction are either committed successfully or all failed rollback; C:consistency, consistency, the database always transitions from one consistent state to another consistent state I:isolation, isolation, generally speaking, changes made by a transaction are not visible to other transactions until they are finally committed (the isolation level is not satisfied with non-commit reads); D: once a transaction commits, its changes are permanently saved to the database.

Isolation level

Four isolation levels for database transactions:

Uncommitted read: one transaction can read uncommitted data from another transaction. Submission degree: during the process of updating data by a transaction, if the transaction has not been committed, other transactions cannot read the data. Repeatable: this level ensures that the result of reading the same record multiple times in the same transaction is the same, and solves the problem of non-repeatable reading in "commit read". However, the problem of phantom reading can not be solved in theory (the problem of phantom reading can be solved by gap lock). Serialization: serializes all transactions, the highest level of isolation.

Illusory reading problem

Illusory reading means that when a transaction reads a range of data, another transaction inserts new data in that range, and a magic row occurs when the previous transaction reads the range data again. The reason for the illusion is that the previous transaction does not increase the range lock (range-locks) in the range of reading data, that is, the row-level shared lock is locked at the time of reading, and the entire query interval or table is not locked.

Common index structure

B+ tree index: B+ tree is a common type of index in relational databases. Note: the B+ tree can not find a specific row with a given key value, but can only find the page where the searched data row is located, and then the database reads the page into memory, looks for it in memory, and finally gets the data to be looked up; hash index: the hash index supported by InnoDB is adaptive and cannot be artificially interfered to generate a hash index in a table. Innodb will automatically generate a hash index according to the usage of the table. Full-text indexing: InnoDB supports full-text indexing, but each table can only have one full-text index, which is generally implemented using inverted indexing technology.

Clustered and nonclustered indexes

A clustered index is a primary key index, and its leaf node is the recorded data (page). A nonclustered index is also called a secondary index, and its leaf node records the primary key value. Taking table t as an example, it is illustrated as follows:

Create table T (ID int primary key,k int NOT NULL DEFAULT 0mens varchar (16) NOT NULL DEFAULT'', index k (k)) engine=InnoDB

Insert into T values (100Person1, 'aa'), (200pyr2), (200pyr2), (300pjc'), (500pjc'), (500pjc5), (600pc6)

The primary key index and secondary index corresponding to table T are as follows:

A few logs.

Redo log: the physical operation of the page is recorded. By writing the transaction operation to redo log first, instead of writing the update of the data page to disk, InnoDB is equivalent to randomly writing the disk (data file) into redo log, and then slowly writing to the disk when MySQL is "idle" to improve the performance of the server. Undo log:undo log saves the version of the data before the transaction, which can be used for rollback, while providing multi-version concurrency controlled read (MVCC), that is, unlocked read. Undo log is a logical log, and when undo is executed, it only logically restores the data to the state it was before the transaction, not from the physical page, which is different from redo log. Generating the current version of undo log,undo at the beginning of the transaction also produces redo to ensure undo log reliability; binlog:binlog is an archive log at the mysql level that can be used for master-slave replication and database point-in-time restore. Binlog records logical logs, records DDL and DML operation logs, and can simply be thought of as update sql statements in executed transactions. Slow query, error log, etc.

A few files.

.ibd files and .ibdata files: .ibd files and ibdata files are files that hold innodb data, of which there are two, because innodb supports configuration to decide whether to use shared or exclusive tablespaces. Exclusive tablespaces use ".ibd" files to store data, and each table has an .ibd file; if shared tablespaces are used, ibdata files are used, and all tables share one (or configure multiple) ibdata files. .ifm file: stores metadata information related to the table.

Thank you for your reading, the above is the content of "collation of MySQL basic knowledge points". After the study of this article, I believe you have a deeper understanding of the problem of sorting out MySQL basic knowledge points, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.