Here are all the interview questions about mysql

Interviewer: What is a clustered index? What is a non-clustered index?

Clustered index is the index and data in the same file, such as InnoDB xxx.idb file, enterprise development, I seem to have never used non-InnoDB engine, so we use clustered index in daily development. B+tree. (So it’s easy to remember)

What is a non-clustered index? Of course, the data and index are not in the same file, like MyISAM.

Interviewer: What’s the difference between b-tree and B+tree?

(F * * k, is it interesting to ask this every time?)

B+tree leaf nodes only store indexes, not data. Data is stored in the lowest node, and it is a bidirectional linked list.

B-tree leaf nodes store data and data.

At the bottom is a bidirectional list: each node remembers the pointer at the beginning and end, making it easy to modify, add, delete, and support range lookup.

Interviewer: What are the benefits of B+ Tree’s data structure design?

B+tree only has data on leaf nodes, in order to make IO read more pages (16K) at a time.

The CPU reads 16K I/O data at a time, that is, one non-leaf node. Reading B+ Tree data at a time can read 1170 non-leaf nodes.

Interviewer: Why does Innodb have to have primary keys and use auto-increment primary keys? Instead of using UUID?

Because:

1: saves space. One node stores more indexes.

2. The size of a node is quickly compared by horizontal scanning.

3. Why use autoincrement, insert directly to the end, fast, also will not be leaf nodes branch.

Interviewer: How InnoDB transactions work

InnoDB is a transactional storage engine, and InnoDB transactions are based on redo log and undo log

1. Persistence: Redo log is a redo log that provides write operations and implements transaction persistence.

2. Consistency: Undo log is a rollback log and provides rollback operations to ensure transaction consistency.

Interviewer: So what isolation levels does Innodb support?

1. READ UNCOMMITTED: A query can READ data that is being modified by another transaction, even if the other transaction’s changes have not yet been committed. This isolation level cannot avoid dirty reads.

2. READ COMMITTED: Changes made to the database by other transactions are visible as long as they are COMMITTED, regardless of the order in which the two transactions started. This isolation level avoids dirty reads, but does not allow repeatable reads and may even produce phantom reads.

3. REPEATABLE READ: A step further than READ COMMITTED, it can only READ changes made to the database by transactions committed before it starts, after it starts, all other transactions’ changes to the database are invisible to it. Thus achieving repeatable reading, but still may be unreal

4. SERIALIZABLE: This is the highest transaction isolation level. A read lock is applied to all queries, even queries, to prevent other transactions from modifying the data. So it manages to avoid illusions. The trade-off, however, is that the concurrent processing capacity of the database system is greatly reduced, so it is not used in production systems.

So what is fantasy reading? 😀

A “phantom read “, also called a” phantom read “, is a special case of “unrepeatable read” : when transaction 1 executes “SELECT… Transaction 2 creates (for example, [[INSERT]]) a new row of data in the table that satisfies transaction 1’s “WHERE” clause. Transaction 1 in figure 1 executes the same query twice, the second time has one more data than the first time, this is the phantom read.

A little burn brain….

Interviewer: How to solve the illusion?

Use MVCC!!

In snapshot read cases, mysql uses MVCC to avoid phantom reads.

In the current read case, mysql uses next-key or gap locks to avoid phantom reads.

MVCC is a technique for implementing concurrent access to a database. It makes most of the transaction engines that support row locking no longer use row locking to control the concurrency of the database. Instead, it combines the row locking of the database with multiple versions of the row to achieve unlocked read with little overhead, thus greatly improving the concurrency performance of the database system.

Implementation principle:

Innodb adds two implied columns to each row, one storing the “time” when the row was updated and the other storing the “time” when the row was deleted. However, InnoDB does not store the absolute time, but the version number of the database system corresponding to the time. Whenever a transaction starts, InnoDB assigns an increasing version number to the transaction, so the version number can also be considered as the transaction number. For each “query” statement, InnoDB compares the version number of the query statement to the version number of the row encountered by the query statement, and then decides whether to return the row with different transaction isolation levels.

Innodb hardly acquires any locks when reading data, and each query passes a version check to get only the data version it needs, greatly increasing the concurrency of the system.

• SELECT, read create version <= current transaction version number, delete version number is empty or > current transaction version number.

• When INSERT, save the current transaction version number as the creation version number of the row

• When DELETE, save the current transaction version number as the deleted version number of the row

• UPDATE inserts a new record, saves the current transaction version number as the row creation version number, and saves the current transaction version number to the original deleted row

Interviewer: What locks do you know about mysql? How do you use these locks?

(To be honest, these locks are rarely used in development, who would review them if it wasn’t for an interview)

• Row lock: is added to the index row (yes! Index row! Not data rows!) The lock on the. Select * from user where id=1 and id=10 for update; select * from user where id=1 and ID =10;

• Gap Locks, which Locks the area between two indexes. Select * from user where id>1 and ID <10 for update; select * from user where id>1 and ID <10 for update;

• Next-key Locks: Also known as Gap Locks, next-key Locks are closed interval Locks created by Record Locks + Gap Locks. Select * from user where id>=1 and ID <=10 for update; select * from user where id>=1 and ID <=10 for update; This combination has row-level shared locks, table-level shared locks, row-level exclusive locks, table-level exclusive locks.

• Read lock: Data can only be read and cannot be modified. If transaction A locks data M with S, transaction A can read record M but cannot modify record M. Other transactions (here using transaction B) can only lock record M with S, but cannot lock RECORD M with X, until transaction A releases S lock on record M, ensuring that other transactions (transaction B) can read record M. However, no changes can be made to record M until transaction A releases the S lock on M.

• Write locks: if the transaction of data object M plus X lock, A transaction can read A record M can also modify M, other transaction (transaction B) can no longer record M plus any locks, until the transaction release the lock on the record M, to ensure the other transactions in the transaction (transaction B) before A release the lock on the record M M can no longer read and modify records.

Interviewer: Have you used binlog before?

Binlog is a binary log maintained by Mysql server. It is completely different from redo/undo log in InnoDB. It records all DDL and DML statements (except for data query statements), and saves them on disk in the form of transactions. It also contains the time consumed by the execution of statements. Mysql binary logs are transaction safe.

Generally speaking, there is a performance loss of about 1% when binary logging is enabled (see MySQL official Chinese manual 5.1.24).

The main functions are:

• Replication: MySQL Replication enables binlog on the Master side and the Master passes its binary log to Slaves and plays back to achieve master-slave data consistency

• Data recovery: Use the mysqlbinlog tool to restore data

• Incremental backup