preface

Hi everyone, this is Xiaotao. Interview is always an important part of our job interview experience, so I have compiled some MySQL interview questions recently. This article is just a part of them, and will be updated in the future. Code word is not easy, everyone with a thumbs-up ah, of course, can thumbs-up + collection + forward better!

What is a MySQL?

MySQL is a relational database that is commonly used in Java enterprise development because MySQL is open source and free and easy to expand. Alibaba database system also uses MySQL a lot, so its stability is guaranteed. MySQL is open source, so anyone can download it under the General Public License (GPL) and modify it for their own needs. The default port number of MySQL is 3306.

Recently I have also sorted out some Java related information, want to receive the direct click can!

2021 Latest Java core knowledge points

Java interview Knowledge

MySQL interview 55 questions and answers

Issues related to

What is a transaction?

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.

What are the four properties of ACID?

  • Atomicity: Transactions are the smallest unit of execution and do not allow splitting. The atomicity of the transaction ensures that the action either completes completely or does not work at all;
  • Consistency: Data is consistent before and after a transaction is executed. Multiple transactions read the same data with the same result.
  • Isolation: when accessing the database concurrently, a user’s transaction is not disturbed by other transactions, and the database is independent between the concurrent transactions.
  • Persistence: After a transaction is committed. Its changes to the data in the database are persistent and should not be affected if the database fails.

What are the problems associated with concurrent transactions?

In a typical application, multiple transactions run concurrently, often working on the same data to accomplish their respective tasks (multiple users working on uniform data). Concurrency, while necessary, can lead to the following problems.

  • Dirty read: When a transaction is accessing data and making changes to the data that have not been committed to the database, another transaction also accesses the data and then consumes it. Because this data is not committed yet, another transaction reads this data as “dirty data” and may not be doing the right thing based on “dirty data”.
  • Lost to modify: When a transaction reads data that is also accessed by another transaction, the first transaction changes the data and the second transaction changes the data. The result of the modification within the first transaction is then lost and is therefore called a lost modification. For example, transaction 1 reads A table A=20, transaction 2 also reads A=20, transaction 1 changes A=A-1, transaction 2 also changes A=A-1, the final result A=19, the modification of transaction 1 is lost.
  • Unrepeatableread: Reads the same data multiple times within a transaction. While this transaction is not finished, another transaction also accesses the data. Then, between the two reads in the first transaction, the data read by the first transaction may not be the same because of modifications made by the second transaction. This occurs when the data read twice within a transaction is not the same and is therefore called a non-repeatable read.
  • Phantom read: Phantom read is similar to unrepeatable reading. It occurs when one transaction (T1) reads several rows of data, followed by another concurrent transaction (T2) inserts some data. In subsequent queries, the first transaction (T1) will find more records that did not exist before, as if an illusion occurred, so it is called phantom read.

Non-repeatability and illusory difference:

Unrepeatable read focus is modified, magic read focus is added or deleted.

Example 1: Before Mr. A in transaction 1 reads his salary as 1000, Mr. B in transaction 2 changes A’s salary to 2000, which causes A to read his salary as 2000. This is unrepeatable reading.

Example 2 (under the same conditions, the number of records read on the first and second time is different) : There are 4 people whose salary is more than 3000 in a payroll table. Transaction 1 reads all the people whose salary is more than 3000 and checks 4 records in total. At this time, transaction 2 inserts another record whose salary is more than 3000 and checks 5 records when transaction 1 reads again.

What are the transaction isolation levels? What is the default isolation level for MySQL?

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.

The default isolation level supported by MySQL InnoDB storage engine is REPEATABLE-READ. We can do this by SELECT @@tx_isolation; Command to view

mysql> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
Copy the code

Here are some things to note: Unlike the SQL standard, InnoDB storage engine uses the next-key Lock algorithm in REPEATABLE READ transaction isolation level, thus avoiding phantom reads, unlike other database systems such as SQL Server. The default isolation level of InnoDB storage engine is REPEATABLE-READ, which can guarantee the transaction isolation requirements, i.e. the SERIALIZABLE isolation level of SQL standard.

Most database systems have read-committed isolation because the lower the isolation level, the less locks there are on transaction requests. But remember that InnoDB storage engine uses REPEATABLE READ by default without any performance penalty.

The InnoDB storage engine typically uses the SERIALIZABLE isolation level for distributed transactions.

Indexes related to

Why do indexes speed up queries

Let’s start with the basic storage structure of MySQL

The basic storage structure of MySQL is a page (where records are stored) :

  • Each data page can form a bidirectional linked list
  • The records in each data page can form a one-way linked list
  • Each data page will generate a page directory for the records stored in its edge. When searching for a record through the primary key, dichotomy can be used to quickly locate the corresponding slot in the page directory, and then traverse the records in the corresponding group of the slot to quickly find the specified record
  • Use other columns (non-primary keys) as search criteria: Each record in a single-linked list can only be traversed starting with the smallest record.

So, if we write a SQL statement like select * from user where indexName = ‘XXX’ without any optimization, the default is:

  1. Locate the page where the record resides: You need to traverse the bidirectional linked list to find the page
  2. Find records from the page you are on: Because you are not querying by primary key, you can only traverse the singly linked list of the page you are on

Obviously, in the case of a large amount of data this lookup is very slow! The time complexity is O (n).

What does the index do to speed up our queries? This is the idea of turning disordered data into an order (relative) :

To find the record with ID 8 brief steps:

It is obvious that there is no index, we need to traverse the bidirectional linked list to locate the corresponding page, now the “table of contents” can quickly locate the corresponding page! (Binary search, time complexity is approximately O(logn))

In fact, the underlying structure is B+ tree, B+ tree as a realization of the tree, can let us quickly find out the corresponding record.

What is the leftmost prefix principle?

An index in MySQL can reference multiple columns in a certain order, which is called a federated index. For example, the User table name and city with the combined index is (name,city), and the left-most prefix principle refers to when the query condition accurately matches the left row or columns of the index, then this column can be used. As follows:

select * from user where name=xx and city=xx ; Select * from user where name=xx;// Index can be hit
select * from user where city=xx ; // Index could not be hit
Copy the code

Note that if both conditions are used in the query, but the order is different, such as city= xx and name = xx, then the current query engine will automatically optimize to match the order of the joint index, so that the index can be hit.

Due to the left-most prefix principle, when creating a joint index, the order of index fields should consider the number of field values after they are deleted. The ORDER BY clause also follows this rule.

Be careful to avoid redundant indexes

Redundant index refers to the function of the index, the same can certainly will hit hit, it is redundant indexes such as (name, city) and (name) the two indexes is redundant indexes, able to hit the latter query must be able to hit the former, in most cases, should try to expand the existing index rather than creating a new index.

After mysqls.7, you can view redundant indexes by querying the SCHEMA_REDUNdANT_INDEXES table in the SYS library

Mysql > add index to table;

1. Add PRIMARY KEY (PRIMARY KEY)

ALTER TABLE table_name ADD PRIMARY KEY ( column )

2. Add UNIQUE index

ALTER TABLE table_name ADD UNIQUE ( column )

3. Add INDEX(normal INDEX)

ALTER TABLE table_name ADD INDEX index_name ( column )

4. Add FULLTEXT(FULLTEXT index)

ALTER TABLE table_name ADD FULLTEXT ( column)

5. Add multiple column indexes

ALTER TABLE table_name ADD INDEX index_name ( column1, column2, column3 )

The difference between MyISAM and InnoDB

MyISAM is MySQL’s default database engine (pre-5.5), modified from the earlier ISAM (Indexed Sequential Access Method). While it performs extremely well and offers a number of features, including full-text indexing, compression, spatial functions, etc., MyISAM does not support transaction and row-level locking, and its biggest drawback is that it cannot safely recover from a crash. However, after version 5.5, MySQL introduced InnoDB (another database engine).

Most of the time we use the InnoDB storage engine, but there are some situations where using MyISAM is appropriate such as read intensive situations. (If you don’t mind MyISAM crashing to answer questions).

A comparison of the two:

1. Whether row levellocking is supported: MyISAM only has table-level locking, while InnoDB supports row-levellocking and table-level locking. The default is row levellocking.

2. Whether transactions and safe recovery after crashes are supported: MyISAM emphasizes performance, atomicity per query, execution rate is faster than InnoDB type, but does not provide transaction support. However, InnoDB provides advanced database features such as transaction support, transactions, and external keys. Transaction-safe (ACID compliant) tables with commit, rollback, and crash recoverycapabilities.

3. Whether foreign keys are supported: MyISAM does not support them, while InnoDB does.

4. MVCC: Only InnoDB supports MVCC. For high concurrency transactions, MVCC is more efficient than simple locking. MVCC only works at READ COMMITTED and REPEATABLE READ isolation levels; The MVCC can be implemented using optimistic and pessimistic locks; MVCC implementations are not uniform across databases. Mysql-innodb-mvcc Multiversion concurrency control

MySQL High Performance has a sentence that reads:

Don’t believe the rule of thumb that “MyISAM is faster than InnoDB”. This conclusion is not always absolute. InnoDB is faster than MyISAM in many known scenarios, especially with clustered indexes or applications where data that needs to be accessed can be stored in memory.

In general InnoDB is fine, but MyISAM is a good choice for situations where you don’t care about scalability and concurrency, transaction support, and security recovery after a crash. But in general, we need to consider these issues.