Database basics

1. Why use a database

(1) Data is stored in memory

Advantages: Fast access speed

Disadvantages: Data cannot be stored permanently

(2) Data is saved in files

Advantages: Data is saved permanently

Disadvantages:

1) Slower than memory operations, frequent IO operations.

2) It is inconvenient to query data

(3) Data is stored in the database

1) Data is stored permanently

2) The use of SQL statements, convenient and efficient query.

3) Convenient data management

2. What is SQL?

Structured Query Language (SQL) is a database Query Language.

Purpose: Used to access data, query, update, and manage relational database systems.

3, what is MySQL?

MySQL is a relational database management system developed by MySQL AB, a Swedish company and a product of Oracle. MySQL is one of the most popular Relational Database Management systems, and one of the best RDBMS (Relational Database Management System) applications in WEB applications. It is commonly used in Java enterprise development because MySQL is open source, free and easy to expand.

4. What are the three paradigms of database

First normal form: No column can be split again.

Second normal form: On a first normal form basis, non-primary key columns are completely dependent on the primary key, not part of it.

Third normal form: On a second normal form basis, non-primary key columns depend only on the primary key and not on other non-primary keys.

When designing a database structure, try to follow the three paradigms, and if not, there must be a good reason for it. Like performance. In fact, we often compromise database design for performance.

MySQL > select * from ‘MySQL’

The MySQL server controls user access to the database through the permission table, which is stored in the MySQL database and initialized by the mysql_install_db script. These permission tables are user, DB, table_priv, columns_priv, and host. The structure and contents of these tables are described below:

  • User permission table: records the information about the user accounts that are allowed to connect to the server. The permissions in the table are global.
  • Db rights table: records the operation rights of each account on each database.
  • Table_priv Permission table: records data table-level operation permissions.
  • Columns_priv permission table: records operation permissions at the data column level.
  • Host permission table: Works with db permission table to control database-level operation permissions on a given host. This permission list is not affected by GRANT and REVOKE statements.

MySQL > select * from binlog; What’s the difference?

There are three formats, Statement, Row and mixed.

  • In statement mode, each SQL statement that modifies data is recorded in the binlog. You do not need to record the changes of each row, reducing the amount of binlog logs, saving I/O, and improving performance. Because SQL execution is contextual, relevant information needs to be saved at the time of saving, and some statements that use functions and the like cannot be recorded and copied.
  • At the ROW level, information about the CONTEXT of SQL statements is not recorded. Only the modified record is saved. The recording unit is the change of each row. Basically, all the changes can be recorded, but many operations will lead to a large number of changes of rows (such as ALTER table). Therefore, files in this mode save too much information and log too much.
  • Mixed, a compromise, uses statement records for normal operations and row records for situations where statement is not available.

In addition, the row level has been optimized in the new version of MySQL to record statements instead of row by row when table structure changes.

The data type

What are the data types of mysql


1. The value can be TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT, which are 1-byte, 2-byte, 3-byte, 4-byte, and 8-byte integers respectively. Any integer type can have an UNSIGNED attribute to indicate that the data is UNSIGNED, that is, a non-negative integer.

Length: The length of an integer can be specified. For example, INT(11) indicates an INT of length 11. Length is meaningless in most scenarios. It does not limit the legal range of values, only affects the number of characters displayed, and needs to be used in conjunction with the UNSIGNED ZEROFILL property to be meaningful.

For example, if the type is set to INT(5) and the property is UNSIGNED ZEROFILL, the database will actually store 00012 if the user inserts 12.

Real number types, including FLOAT, DOUBLE, and DECIMAL.

DECIMAL can be used to store integers larger than BIGINT and can store exact decimals.

FLOAT and DOUBLE have a range of values and support approximations using standard floating points.

FLOAT is much more efficient than a DOUBLE in computing DECIMAL, which you can interpret as a string.

3. The value can be VARCHAR, CHAR, TEXT, or BLOB

VARCHAR is used to store variable-length strings, which are more space-efficient than fixed-length types.

VARCHAR stores string length with an extra 1 or 2 bytes. If the column length is less than 255 bytes, 1 byte is used; otherwise, 2 bytes is used.

If the VARCHAR stores more content than the set length, the content is truncated.

CHAR is a fixed length that allocates sufficient space based on the defined length of the string.

CHAR is padded with Spaces as needed for comparison purposes.

CHAR is good for storing very short strings, or all values close to the same length.

CHAR also truncates the content stored beyond the set length.

Use strategies:

CHAR is better than VARCHAR for frequently changing data because CHAR is less prone to fragmentation.

For very short columns, CHAR is more storage efficient than VARCHAR.

Be careful to allocate only as much space as you need; sorting longer columns consumes more memory.

Avoid the TEXT/BLOB type. Temporary tables are used for query, which incurs significant performance overhead.

4. Enumeration type (ENUM), which stores non-repeating data as a predefined set.

Sometimes you can use ENUM instead of the common string type.

ENUM storage is very compact, condensing list values to one or two bytes.

ENUM is stored internally as an integer.

Avoid using numbers as constants in ENUM enumerations because they are confusing.

Sort by internally stored integers

5, date and time type, try to use TIMESTAMP, space efficiency is higher than datetime,

Storing timestamps in integers is usually cumbersome.

If you need to store subtlety, you can use BigInt storage.

See here, this real question is not easier to answer.

engine

MySQL storage engine MyISAM is different from InnoDB

Storage Engine: How data, indexes, and other objects are stored in MySQL is an implementation of a file system.

Common storage engines are as follows:

  • Innodb engine: The Innodb engine provides support for DATABASE ACID transactions. Row-level locking and foreign key constraints are also provided. It is designed to handle database systems with large data volumes.
  • MyIASM engine (originally Mysql’s default engine) : does not support transactions, row-level locks and foreign keys.
  • MEMORY engine: All data is stored in MEMORY, data processing speed is fast, but not high security.

2. MyISAM is different from InnoDB

What is the difference between MyISAM index and InnoDB index?

  • InnoDB index is clustered index, MyISAM index is non-clustered index.
  • InnoDB’s primary key index is very efficient because its leaf nodes store rows.
  • The leaf node of the MyISAM index stores the row data address, which needs to be addressed again to get the data back.
  • InnoDB leaf nodes that are not primary key indexes store primary key and other indexed column data, so overwriting indexes can be very efficient when querying.

3. Four features of InnoDB engine

  • Insert buffer
  • Double write
  • Adaptive Hash index (AHI)
  • Pre-reading (read ahead)

4. Select a storage engine

If there are no specific requirements, use the default Innodb.

MyISAM: Read-write and insert-oriented applications, such as blogging systems, news portals.

Innodb: Updates (deletes) frequently, or to ensure data integrity; High concurrency, support for transactions and foreign keys. For example, OA office automation system.

The transaction

1. What are database transactions?

Transaction is an indivisible sequence of database operations and the basic unit of database concurrency control. The result of its execution must make the database change from one consistency state to another. A transaction is a logical set of operations that either all or none of them execute.
The most classic and often cited example of a transaction is the transfer of money.
If Xiao Ming wants to transfer 1000 yuan to Xiao Hong, the transfer will involve two key operations: reducing Xiao Ming’s balance by 1000 yuan and increasing Xiao Hong’s balance by 1000 yuan. If something goes wrong between these two operations like the banking system crashes, and Ming’s balance goes down and Red’s balance doesn’t go up, that’s not right. A transaction is a guarantee that both of these critical operations will either succeed or fail.


2, the four characteristics of things (ACID) introduction?

Relational databases must follow the ACID rule, which reads as follows:

Characteristics of transactions













3. What is dirty reading? Phantom read? Unrepeatable?

  • Drity Read: a transaction has updated a copy of data, and another transaction has Read the same copy of data. For some reason, the first transaction has rolled back, and the data Read by the second transaction is incorrect.
  • Non-repeatable read: Data inconsistency between two queries of a transaction. This may be because the original data updated by a transaction was inserted between the two queries.
  • Phantom Read: a transaction where the number of pens is inconsistent between two queries. For example, if one transaction queries for rows and another inserts new columns, the previous transaction will find columns that it did not have before on subsequent queries.


What is the isolation level of a transaction? What is the default isolation level for MySQL?

In order to achieve the four characteristics of transaction, the database defines four different transaction isolation levels, which are Read uncommitted, Read committed, Repeatable Read, Serializable. The four levels solve the problems of dirty reads, unrepeatable reads, and phantom reads one by one.
Isolation level
Dirty read
Unrepeatable read
The phantom read
READ-UNCOMMITTED
Square root
Square root
Square root
READ-COMMITTED
x
Square root
Square root
REPEATABLE-READ
x
x
Square root
SERIALIZABLE
x
x
x
The SQL standard defines four isolation levels:
  • Read-uncommitted: The lowest isolation level that allows UNCOMMITTED data changes to be READ, potentially resulting in dirty, illusory, or unrepeatable reads.
  • Read-committed: Allows concurrent transactions to READ data that has been COMMITTED, preventing dirty reads, but magic or unrepeatable reads can still occur.
  • REPEATABLE-READ: Multiple reads of the same field are consistent, unless the data is modified by the transaction itself. This can prevent dirty reads and unrepeatable reads, but phantom reads are still possible.
  • SERIALIZABLE: Highest isolation level, fully subject to ACID isolation level. All transactions are executed one by one so that interference between transactions is completely impossible. That is, this level prevents dirty reads, unrepeatable reads, and phantom reads.
REPEATABLE_READ isolation level used by Mysql READ_COMMITTED isolation level used by Oracle
The implementation of transaction isolation mechanism is based on locking mechanism and concurrent scheduling. Among them, concurrent scheduling uses MVVC (Multi-version Concurrency Control), which supports concurrent consistent read and rollback by saving modified old version information.
Most database systems have read-committed isolation: because the lower the isolation level, the fewer locks are COMMITTED, but remember that InnoDB storage engine uses **REPEATABLE READ ** by default without any performance penalty.
InnoDB storage engine typically uses the **SERIALIZABLE ** isolation level for distributed transactions.


The lock

Do you know about MySQL locks

When a database has concurrent transactions, data inconsistencies may occur, and some mechanism is needed to ensure the order of access. The locking mechanism is such a mechanism.
Just like a hotel room, if people go in and out at random, there will be many people snatches for the same room, and a lock will be installed on the room. Only the person who has obtained the key can enter and lock the room, and others can use it again only after they have finished using it.

The relationship between isolation levels and locks

At the Read Uncommitted level, shared locks are not required to Read data so that it does not conflict with exclusive locks on modified data
At the Read Committed level, shared locks are added to Read operations but released after the statement is finished.


The last

Welcome to pay attention to the public number: programmer chase wind, reply 003 to receive a set of latest 200 pages of Java interview questions summary.