If you want to enter dacheng, mysql can’t do that. Let’s take the mysql interview challenge and see how far you can go. \

  1. Can you tell me the difference between MyISam and InnoDB? Myisam engine is the default engine prior to 5.1. It supports full-text search, compression, spatial functions, etc., but does not support transaction and row-level locking, 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 built on clustered indexes, as opposed to MyISam. It supports transactions, foreign keys, and high concurrency via MVCC. Indexes and data are stored together. \

  1. What are the clustered and non-clustered indexes in mysql? Indexes mainly consist of B+ trees and Hash indexes in terms of data structure.

Create table user(ID int(11) not null, age int(11) not null, primary key(ID), key(age)); B+ tree is a sequential storage structure with the smallest on the left and the largest on the right. The node only contains the ID index column, while the leaf node contains the index column and data. This kind of index storage method of data and index is called clustered index, and a table can only have one clustered index. Assuming no primary key is defined, InnoDB selects a unique non-empty index instead, or implicitly defines a primary key as the cluster index if no primary key is defined. \

This is the structure of primary key clustered index storage. What is the structure of non-clustered index storage? Non-clustered indexes (secondary indexes) hold primary key IDS, unlike myISam, which holds data addresses. \

Finally, let’s take a look at the differences between InnoDB and Myisam clustering and non-clustering indexes \

In fact, the above mentioned index is the most basic things, N tree, jump table, LSM I did not cover, but also to create a good index to take into account many aspects: the leftmost prefix matching principle. This is a very important, very important, very important principle, MySQL will keep matching to the right until it encounters a range query (>,<,BETWEEN,LIKE) and stops matching. Select columns with high distinctness as indexes. The distinctness formula is COUNT(DISTINCT Col)/COUNT(*). Represents the rate at which fields are not duplicated. The higher the rate, the fewer records we scan. Index columns cannot participate in the calculation; try to keep the columns “clean”. For example, if FROM_UNIXTIME(create_time)=’2016-06-06′, the index cannot be used, because the B+ tree stores the values of the fields in the table, but requires the function to compare all the elements. Create_time =UNIX_TIMESTAMP(‘2016-06-06’) Expand the index as much as possible, do not create new indexes. For example, if you want to add (a,b) to a table that already has an index of A, you only need to modify the original index. A single multi-column composite index and multiple single-column indexes perform different retrieval queries because MySQL can only use one index when executing SQL, and selects the most restrictive index from multiple single-column indexes. Looking at the third edition of High Performance MySQL, the authors argue that better indexes should be built, rather than relying on a “merge index” strategy. The “merge index” strategy simply means using multiple single-column indexes and combining the results with “union” or “and” \

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

Read locks are shared and can be implemented using lock in share mode. In this case, only read but not write is allowed. Write locks are exclusive and block other write and read locks. From granularity to distinguish, can be divided into table lock and row lock two. A table lock locks the entire table and blocks all read and write operations on the table by other users. For example, a table lock occurs when an ALTER tablespace is modified. Row locks can be divided into optimistic locks and pessimistic locks. Pessimistic locks can be implemented by for UPDATE, and optimistic locks by version number. \

  1. Can you talk about the basic characteristics and isolation levels of transactions? The basic transaction properties of ACID are:

Atomicity refers to the fact that all operations in a transaction either succeed or fail. Consistency refers to the fact that the database always transitions from one consistent state to another consistent state. For example, if A transfers $100 to B, if the system crashes during the execution of the intermediate SQL, A does not lose $100, because the transaction is not committed and the changes are not saved to the database. Isolation means that changes made by one transaction are not visible to other transactions until they are finally committed. Persistence refers to the fact that once a transaction commits, the changes made are permanently stored in the database. The isolation levels are as follows: Read uncommit Data that has not been committed by other transactions may be read, also known as dirty reads. Read commit If a read is committed and the two read results are inconsistent, it is called an unrepeatable read. Repeatable Read: this is the default level of mysql, meaning that the result is the same every time, but phantom reading is possible. Serializable, which is not normally used, locks every row read, causing a lot of timeouts and lock contention. \

  1. What is ACID guaranteed by?

A Atomicity is guaranteed by the Undo log log, which records the log information that needs to be rolled back. When A transaction is rolled back, the SQL that has been successfully executed is cancelled

C consistency is generally guaranteed at the code level

I Isolation is guaranteed by MVCC

Mysql records database changes in memory and redo log at the same time. Mysql records database changes in memory and redo log at the same time. Mysql records database changes in memory and redo log at the same time

This article is just a beginning of mysql, a brief understanding of mysql and the basics, and then I will update mysql advanced knowledge, such as database and table, such as master/slave synchronization, such as double write consistency and so on… Too much, no line, again boast should be broken, I went to study first ！！！！