MySQL is an open source relational database, is one of the most popular open source software, now many websites are using MySQL database. Web programming is one of the most important aspects of Python, so many companies are looking for Python engineers to test their knowledge of MySQL. In the following Python training tutorial, we will share the knowledge of MySQL database.
1, MySQL database stored procedures and functions
A stored procedure and a function are a collection of SQL statements that have been compiled and stored in a database. The difference between the two is that the function must have a return value, while the stored procedure does not. Function parameters can only be IN types, and stored procedure parameters can be IN, OUT, or INOUT types.
2. Sub-database and sub-table design
The purpose of sub-database and sub-table is to reduce the burden of single database and single table, improve the query performance and shorten the query time. By dividing tables, you can reduce the burden of single tables in the database and disperse the pressure to different tables. At the same time, because the amount of data on different tables is less, it improves the query performance and shorts the query time. In addition, it can greatly alleviate the problem of table locking.
Table splitting strategy can be summarized as vertical splitting and horizontal splitting.
Horizontal scale: modular scale belongs to random scale, while time dimension scale belongs to continuous scale.
To design a good vertical split, it is recommended to separate the infrequently used fields into another extended table. Separate large text fields into a separate extended table, place infrequently modified fields in the same table, and frequently changed fields in a separate table.
In the scenario of massive users, you can use a modular partition table to ensure that the data is relatively uniform and is not prone to hotspots and concurrent access bottlenecks. It only solves the problem of large data in a single table, but it does not spread the data in a single table to different physical machines, so it cannot reduce the pressure of MySQL server. There are still resource competition and bottlenecks on the same physical machine, including CPU, memory, disk IO, network bandwidth, etc.
The bicycle
3. Differences between clustered indexes and non-clustered indexes
The fundamental difference between a clustered index and a non-clustered index is whether the table records are sorted in the same order as the index. A leaf of an innoDB index is a data node, while a leaf of a non-innoDB index is still an index node, but it contains a pointer to the corresponding data block.
4. Four characteristics of transaction (ACID)
Atomicity: All operations in a transaction either complete or not complete, and do not end up somewhere in between. If a transaction fails during execution, it will be rolled back to the state before the transaction began, as if the transaction had never been executed.
Consistency: The integrity of the database is not compromised before and after a transaction. This means that the data written must conform to all the preset rules, including the accuracy of the data, the concatenation of the data, and the ability of the subsequent database to do its predetermined work spontaneously.
Isolation: The ability of a database to allow multiple concurrent transactions to read, write, and modify its data at the same time. Isolation prevents data inconsistencies due to cross-execution when multiple transactions are executed concurrently. Transaction isolation can be divided into different levels, including Read uncommitted, Read Committed, Repeatable Read, and Serializable.
Persistence: After a transaction, changes to the data are permanent and will not be lost even if the system fails.
5. Transaction concurrency? Transaction isolation levels, what problems are caused by each level, which level is MySQL’s default?
Dirty reads are when uncommitted data from another transaction is read during a transaction.
Non-repeatable reads: multiple transaction-wide queries for a particular data in the database return different data values.
Phantom read: A phenomenon that occurs when a transaction is not executing independently. During a transaction read, another transaction may insert a new data record, affecting the result of that transaction read.
MySQL’s default isolation level is Repeatable Read.
In theory, the transaction should be completely isolated from each other, in order to avoid the problem of concurrent transactions lead to, however, it will have great impact on performance, because the transaction must be run according to the order in the actual development, in order to improve performance, the transaction will run at a lower isolation level, transaction isolation level can be specified through isolation transaction attribute.