Summary of Mysql database knowledge, mysql database Summary

Summary of Mysql database knowledge, mysql database Summary

The weather was overcast on January 1, June 8, 2017. The mood is fine.

I dreamed of a long time out of contact with a junior high school student for two consecutive nights. I chatted on the way to work in the morning. The girl is now on a business trip in Guizhou, where the scenery is beautiful. I said, "Your life is very moist now ". The girl said, "That's what you didn't see ". Indeed, in our life, we always present our own glamorous side to others, while "disguising" our fallen side ".

Yesterday, a blog user asked me a question and called me "Great God". I was very worried because I knew it was far from this "Great God", so I had to work harder, move toward the goal of "Great God.

Boyou asked a question about the Mysql database, which involves data engines, repair, and optimization of data table statements. These questions have never been taken into consideration before, but are never used. So I found some information and summarized the information I found as follows.

1. Mysql database storage engine

Concept:

The storage engine is actually a technical implementation method for how to store data, how to create indexes for stored data, and how to update and query data. Data in MySQL is stored in files (or memory) using different technologies. Each of these technologies uses different storage mechanisms and indexing techniques, lock level and ultimately provide a wide range of different functions and capabilities. In MySQL, these different technologies and related functions are called storage engines.

(1) innoDB Storage Engine

① The mysql table of the innodb Storage engine provides transaction, rollback, and transaction security with system crash repair capabilities and multi-version concurrency control.

② Innodb supports auto-increment columns (auto_increment). The value of the auto-increment Column cannot be empty. If it is empty, how can it automatically increase from the existing value, if the value is greater than the current value, save the value.

③ The innodb Storage engine supports the foreign key. the table where the foreign key is located is called a sub-table (or a slave table), and the dependent table is called a parent table (or a master table ).

④ The most important thing about the innodb Storage engine is to support transactions and the related functions of transactions.

⑤ Innodb Storage engine supports mvcc row-level locks.

⑥ Innodb Storage engine indexes use B + Tree.

(2) MyISAM storage engine

1) the storage engine MyISAM does not support transactions, row-level locks, and only supports concurrent inserted Table locks. It is mainly used for select with high load.

2) MyISAM tables support three different storage structures: static, dynamic, and compressed.

① Static type: the size of the defined table column is fixed (that is, it does not include xblob, xtext, varchar, and other variable-length data types), so mysql will automatically use the static myisam format.

The performance of tables in static format is relatively high, because the overhead of data storage in a predefined format is very low during maintenance and access. However, this high performance is calculated by space because it is fixed at the time of definition. Therefore, no matter how big the values in the column are, the maximum value prevails, occupying the entire space.

② Dynamic type: If a column (even if only one column) is defined as dynamic (xblob, xtext, varchar, and other data types), myisam automatically uses the dynamic type.

Although a dynamic table occupies less space than a static table, it reduces the performance because if the content of a field changes, the position of the dynamic table may need to be moved, this will lead to the generation of fragments. As data changes, fragmentation increases and data access performance decreases accordingly.

There are two solutions to reduce data access due to fragmentation:

A. Try to use static data types.

B. The optimize table statement is often used. It sorts out table fragments and restores Space Loss Caused by table update and deletion.

If the storage engine does not support optimize table, you can dump and reload the data, which can also reduce fragments.

③ Compressed type: If a read-only table is created in this database throughout the lifecycle, The myisam compressed table is used to reduce space usage.

3) MyISAM also uses B + tree indexes, but it is somewhat different from Innodb in implementation.

(3) MEMORY storage engine

① The memory storage engine is a little different from the previous storage engines. It uses data stored in the memory to create tables, and all data is stored in the memory.

② Each memory-based storage engine table corresponds to a disk file. The file name and table name are the same, and the type is. frm. This file only stores the table structure, and its data files are all stored in the memory, which facilitates fast data processing and improves the processing capability of the entire table.

③ By default, the memory storage engine uses HASH indexes, which is faster than B + Tree. If you want to use B Tree, you can reference it during creation.

④ The file data of the memory storage engine is stored in the memory. If the mysqld process encounters an exception, the data will disappear when the machine is restarted or shut down. Therefore, the life cycle of a table in the memory storage engine is very short, which is generally used only once.

(4) BlackHole storage engine (black hole engine)

Supports transactions and mvcc row-level locks, mainly used for logging or synchronous archiving. This storage engine is not suitable for use unless it has a special purpose!

2. Optimize and repair tables in Mysql

With the long-term use of mysql, You can optimize it by repairing tables, reducing disk space usage during optimization and facilitating backup.

Repair table 'table _ name' REPAIR table optimize table 'table _ name' optimization table

Repair table -- used to REPAIR damaged tables.
Optimize table -- optimize table, used to reclaim idle database space. When the data rows in the TABLE are deleted, the occupied disk space is not immediately recycled. The space will be recycled after the optimize table command is used, the data rows on the disk are rearranged.

Note: It is a disk, not a database.
You do not need to run optimize table most of the time. You only need to perform a data TABLE optimization operation on a regular basis (once a week or once a month) after deleting data rows in batches, only for those specific tables.