Shulou(Shulou.com)06/01 Report--

This article mainly explains "what are the mysql-related interview questions". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn what are the mysql-related interview questions.

1. Can you tell the difference between myisam and innodb?

Myisam engine is the default engine before version 5.1, which supports full-text retrieval, compression, spatial functions, etc., but does not support transactions and row-level locks, so it is generally used in scenarios with a large number of queries and a small number of inserts. Myisam does not support foreign keys, and indexes and data are stored separately.

Innodb is based on clustered indexes. Contrary to myisam, it supports transactions, foreign keys, and supports high concurrency through MVCC, where indexes and data are stored together.

two。 What are the indexes of mysql, and what are clustered and non-clustered indexes?

According to the data structure, the index mainly includes B + tree and Hash index.

Suppose we have a table with the following structure:

Create table user (id int (11) not null, age int (11) not null, primary key (id), key (age)

The B+ tree is a sequential storage structure that is small on the left and large on the right. The node contains only id index columns, while the leaf node contains index columns and data. This kind of index method in which data and indexes are stored together is called clustered index. A table can only have one clustered index. Assuming that no primary key is defined, InnoDB selects a unique non-empty index instead, and if not, implicitly defines a primary key as the clustered index.

This is the structure of the primary key clustered index storage, so what is the structure of the non-clustered index? A non-clustered index (secondary index) holds the primary key id value, which is different from the data address saved by myisam.

Finally, let's take a picture to see the difference between InnoDB and Myisam clustered and non-clustered indexes.

3. Do you know what it means to overwrite indexes and return tables?

An overlay index means that in a query, if an index contains or overrides the values of all the fields that need to be queried, we call it an override index, and we no longer need to return to the table query.

To determine whether a query is an overlay index, we just need the explain sql statement to see if the result of Extra is "Using index".

Using the user table above as an example, let's add another name field and try some queries.

The name of explain select * from user where age=1; / / query cannot get explain select id,age from user where age=1; from index data / / can get 4. 0 directly from index. What are the types of locks?

Mysql locks are divided into shared locks and exclusive locks, also known as read locks and write locks.

Read locks are shared and can be implemented through lock in share mode, which can only be read but not written.

The write lock is exclusive, and it blocks other write and read locks. In terms of granularity, it can be divided into table locks and row locks.

Table locks lock the entire table and block all read and write operations to the table by other users, such as locking the table when alter modifies the table structure.

Row lock can be divided into optimistic lock and pessimistic lock, pessimistic lock can be realized by for update, optimistic lock can be realized by version number.

5. Can you describe the basic characteristics and isolation level of the transaction?

The ACID for the basic characteristics of a transaction are:

Atomicity means that all operations in a transaction either succeed or fail.

Consistency means that the database always transitions from one consistent state to another. For example, if A transfers 100 yuan to B100, assuming that system crash A will not lose 100 yuan during the execution of the intermediate sql, because the transaction is not committed, the changes will not be saved to the database.

Isolation means that the modification of one transaction is not visible to other transactions until it is finally committed.

Persistence means that once the transaction commits, the changes are permanently saved to the database.

There are four isolation levels for isolation, which are:

Read uncommit read uncommitted, may read other transactions uncommitted data, also known as dirty read.

The user age that the user should have read to id=1 should be 10. As a result, it reads transactions that have not been committed by other transactions, and reads the result age=20. This is dirty reading.

Read commit read has been submitted, and the results of the two reads are inconsistent, which is called non-repeatable read.

Unrepeatable reading solves the problem of dirty reading, which only reads transactions that have been committed.

The user starts the transaction to read the id=1 user, queries the age=10, and reads the discovery result again = 20. The same query reads different results in the same transaction is called non-repeatable read.

Repeatable read can be read repeatedly, which is the default level of mysql, that is, the result of each read is the same, but it is possible to produce phantom reading.

Serializable serial, which is generally not used, locks the data read by each row, which leads to a large number of timeouts and lock competition problems.

6. So what guarantees does ACID rely on?

An atomicity is guaranteed by the undo log log, which records the log information that needs to be rolled back. When the transaction is rolled back, the sql that has been successfully executed is undone.

C consistency is generally guaranteed at the code level.

I isolation is guaranteed by MVCC

D persistence is guaranteed by memory + redo log. Mysql modifies data and records the operation in memory and redo log at the same time. The transaction is flushed through redo log when the transaction is committed, and can be recovered from redo log in case of downtime.

7. Then tell me what is illusion and what is MVCC?

To talk about phantom reading, the first thing to understand is that MVCC,MVCC is called multi-version concurrency control, which actually saves a snapshot of the data at a node at a certain time.

We actually hide two columns per row, the creation time version number, the expiration (delete) time version number, and each time we start a new transaction, the version number is automatically incremented.

Let's take the user table above as an example, suppose we insert two pieces of data, and they should actually look like this.

Idnamecreate_versiondelete_version1 Zhang San 1

2 Li Si 2

At this time, suppose Xiaoming executes the query, and at this time current_version=3

Select * from user where id

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.