MySQL high frequency interview questions soul torture

Is a unique index faster than a normal index? Why?

Unique indexes are not necessarily faster than normal indexes, but can be slower.

1. In the case that limit 1 is not used, the unique index is returned after matching one data item. The common index continues to match the next data item and returns after finding a mismatch. This appears to be one less match for the unique index, but in reality this cost is negligible.
2. When it comes to updates, the situation becomes more complicated. Normal indexes put records into the change buffer and the statement is executed. For a unique index, it must verify uniqueness, so the data page must be read into memory to make sure there are no conflicts before it can proceed. The change buffer is used to reduce the number of disk accesses. Therefore, the performance of a common index is higher than that of a unique index.

What are the components of MySQL and what are they used for

1. Server
2. Connector: Manages connections and validates permissions.
3. Analyzer: lexical analysis, syntax analysis.
4. Optimizer: perform plan generation, index selection.
5. Executor: Operates the storage engine and returns the execution result.
6. Storage engine: Stores data and provides read and write interfaces.

What are the drawbacks of the MySQL query cache, when should it be used, and what changes are made to the query cache in version 8.0?

• The query cache can be invalidated so frequently that the entire query cache for a table will be emptied whenever there is an update. Therefore, for frequently updated tables, query caching is not necessarily a positive effect.
• Consider using query caching for tables that are read much more than they are written.
• The query cache function of version 8.0 has been deleted (￣.￣).

What are the differences between MyISAM and InnoDB

• InnoDB supports transactions, MyISAM does not.
• InnoDB supports row-level locking and MyISAM supports table-level locking.
• InnoDB supports multi-version Concurrency Control (MVVC), MyISAM does not.
• InnoDB supports foreign keys, MyISAM does not.
• MyISAM supports full-text indexing, InnoDB does not (but you can use Sphinx plug-in)

How to restore data from MySQL half a month ago

Restore through whole database backup +binlog. The prerequisite is that the entire database is backed up periodically and binlog logs are saved.

What are the isolation levels of MySQL transactions

1. Read Uncommitted (RU): Changes made by a transaction can be seen by other transactions before it is committed.
2. Read Commit (RC): After a transaction commits, its changes are seen by other transactions.
3. Repeatable read (RR): The data seen during the execution of a transaction is always the same as the data seen when the transaction is started. Of course, at the repeatable read isolation level, uncommitted changes are also invisible to other transactions.
4. Serialization (S): For the same row, both read and write are locked. When a read-write lock conflict occurs, a later-accessed transaction must wait for the previous transaction to complete before it can continue.

MySQL index related optimizations

• Use primary key queries as much as possible: all data is stored on the clustered index, which reduces the consumption of back tables compared to normal index queries.
• Since MySQL5.6, index push-down optimization has been introduced to reduce the cost of back table judgments by using federated indexes appropriately.
• If a column of data is frequently queried, you can consider using an overwrite index to avoid returning to the table.
• The joint index places the high frequency field at the far left.

A quick word about the database paradigm

• First normal form: Attributes are not separable.
• Second normal form: On a normal form basis, it is required that each instance or row in a database table must be uniquely locatable. It is often necessary to add a column to a table to store the unique identity of each instance. This unique attribute column is called the primary key or primary key.
• Third normal form: On a second normal form basis, a database table is required not to contain non-primary keyword information that is already contained in other tables. So the third normal form has the following characteristics: 1). Each column has only one value. 2). 3). Each table does not contain non-primary keyword information that other tables already contain.

10 million data table, how to paging query

In the case of large data volume, the limit offset page will slow down the query due to too much data scanning. SELECT * FROM T WHERE ID > #{ID} LIMIT #{LIMIT}. Of course, in this case the ID has to be ordered, which is one of the benefits of ordered ids.

How to deal with the slow query caused by the increasing amount of order table data

Database and table: Since the utilization rate of historical orders is not high, the high-frequency orders may only be recent orders, so the order table is split according to time, considering monthly table or yearly table according to the size of data volume. The order ID had better contain the time (such as generated according to the Snowflake algorithm), at this time, the order record can be directly obtained according to the order ID, and can also be queried according to the time.