Resource sharing of power node mysql basic video tutorial

title="MySQL is a relational database management system developed by the Swedish MySQL AB company and is currently a product of Oracle. MySQL is one of the most popular relational database management systems. In terms of WEB applications, MySQL is the best RDBMS (Relational Database Management System, relational database management system) application software "

## Course playback address: http://www.php. cn/course/391.html

The teacher’s teaching style:

The teacher’s lectures are simple, clear, layer-by-layer, and comprehensive. It has rigorous argumentation and rigorous structure, uses the logical power of thinking to attract students' attention, and uses reason to control the classroom teaching process. By listening to the teacher's lectures, students not only learn knowledge, but also receive training in thinking, and are also influenced and influenced by the teacher's rigorous academic attitude

The more difficult point in this video is the storage engine:

#What is a storage engine?

Relational database tables are data structures used to store and organize information. A table can be understood as a table composed of rows and columns, similar to the form of an Excel spreadsheet. Some tables are simple, some are complex, some tables are not used to store any long-term data at all, and some tables are very fast to read, but very poor when inserting data; in the actual development process, we may need Various tables, different tables, mean that different types of data are stored, and there will be differences in data processing. For MySQL, it provides many types of storage engines. We can choose different storage engines according to our data processing needs, so as to maximize the use of MySQL's powerful functions. This blog post will summarize and analyze the characteristics of each engine and its applicable situations, and will not get entangled in deeper things. My learning method is to first learn to use it, know how to use it, and then find out how to use it. The following is a brief introduction to the storage engines supported by MySQL.

MyISAM

In the mysql client, use the following command to view the engines supported by MySQL.

MyISAM tables are independent of the operating system, which means that they can be easily ported from Windows servers to Linux servers; whenever we create a MyISAM engine table, it will be on the local disk Create three files, the file names are indicated. For example, if I create a tb_Demo table of the MyISAM engine, the following three files will be generated

1.tb_demo.frm, which stores the table definition;

2.tb_demo.MYD, which stores the data ;
3.tb_demo.MYI, storage index.

MyISAM tables cannot handle transactions, which means that tables with transaction processing requirements cannot use the MyISAM storage engine. The MyISAM storage engine is particularly suitable for use in the following situations:

1. Select intensive tables. The MyISAM storage engine is very fast when filtering large amounts of data, which is its most prominent advantage.

2. Insert into intensive tables. MyISAM's concurrent insert feature allows data to be selected and inserted simultaneously. For example: the MyISAM storage engine is suitable for managing email or web server log data.

InnoDB

InnoDB is a robust transactional storage engine. This storage engine has been used by many Internet companies and provides a powerful solution for users to operate very large data storage. MySQL version 5.6.13 is installed on my computer, and InnoDB is the default storage engine. InnoDB also introduces row-level locking and foreign key constraints. In the following situations, using InnoDB is the most ideal choice:

1. Update intensive tables. The InnoDB storage engine is particularly suitable for handling multiple concurrent update requests.

2. Affairs. The InnoDB storage engine is a standard MySQL storage engine that supports transactions.
3. Automatic disaster recovery. Unlike other storage engines, InnoDB tables can automatically recover from disasters.
4. Foreign key constraints. The only storage engine that supports foreign keys in MySQL is InnoDB.
5. Support automatically adding column AUTO_INCREMENT attribute.

Generally speaking, if transaction support is required and there is a high concurrent read frequency, InnoDB is a good choice.

MEMORY

The starting point for using the MySQL Memory storage engine is speed. To get the fastest response time, the logical storage medium used is system memory. Although storing table data in memory does provide high performance, when the mysqld daemon crashes, all Memory data is lost. With the speed gained comes some drawbacks. It requires that the data stored in the Memory data table use a constant-length format, which means that variable-length data types such as BLOB and TEXT cannot be used. VARCHAR is a variable-length type, but because it is MySQL internally treats it as a CHAR type with a fixed length, so it can be used.

Generally, Memory storage engine is used in the following situations:

1. The target data is small and accessed very frequently. Storing data in memory will cause memory usage. You can control the size of the Memory table through the parameter max_heap_table_size. Setting this parameter can limit the maximum size of the Memory table.

2. If the data is temporary and must be available immediately, it can be stored in a memory table.

3. If the data stored in the Memory table is suddenly lost, it will not have a substantial negative impact on the application service.

Memory supports both hash index and B-tree index. The advantage of B-tree index over hash index is that you can use partial queries and wildcard queries, and you can also use operators such as and >= to facilitate data mining. The hash index performs "equality comparison" very quickly, but the "range comparison" is much slower, so the hash index value is suitable for use in the operators = and , but not suitable for

The above is the detailed content of Resource sharing of power node mysql basic video tutorial. For more information, please follow other related articles on the PHP Chinese website!