Shulou(Shulou.com)05/31 Report--

This article is to share with you what the MySQL high-frequency interview questions are like, the editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

Is the unique index faster than the ordinary index, and why?

A unique index is not necessarily faster than a normal index, and may be slower.

When querying, in the case of not using limit 1, after matching a piece of data, the unique index will return, and the ordinary index will continue to match the next piece of data and return after finding a mismatch. So it'seems that the unique index is missing a match, but in fact the consumption is minimal.

When updating, the situation is more complicated. The ordinary index puts the record in 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 is no conflict before continuing. In the case of more writes and less reads, the general index uses change buffer to effectively reduce the number of visits to the disk, so the performance of the ordinary index is higher than that of the unique index.

Add Q group: 478052716 free (Java framework materials, video materials, BATJ interview materials)

What are the parts of MySQL and what are they used for?

Server

Connector: manage connection, permission verification.

Analyzer: lexical analysis, grammatical analysis.

Optimizer: perform plan generation and index selection.

Actuator: operate the storage engine and return the execution result.

Storage engine: stores data and provides a read-write interface.

Add Q group: 478052716 free (Java framework materials, video materials, BATJ interview materials)

What are the disadvantages of MySQL query cache, when it should be used, and what changes have been made to query cache in version 8.0.

Query caching can be invalidated very frequently, and for a table, all query caches for that table will be emptied whenever there is an update. Therefore, query caching does not necessarily have a positive effect on tables that are updated frequently.

For tables that read far more than write, you can consider using query caching.

The query caching function in version 8.0 has been deleted.

What are the differences between MyISAM and InnoDB

InnoDB supports transactions, but MyISAM does not.

InnoDB supports row-level locks and MyISAM supports table-level locks.

InnoDB supports multi-version concurrency control (MVVC), but MyISAM does not.

InnoDB supports foreign keys, but MyISAM does not.

MyISAM supports full-text indexing, but InnoDB does not (but you can use the Sphinx plug-in)

How does MySQL recover the data from half a month ago?

Restore through full library backup + binlog. The premise is to have regular full library backups and save binlog logs.

What are the isolation levels of MySQL transactions and what are their characteristics

Read uncommitted (RU): when a transaction is not committed, its changes can be seen by other transactions.

Read commit (RC): after a transaction commits, its changes are seen by other transactions.

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, under the repeatable readable isolation level, uncommitted changes are also invisible to other transactions.

Serialization (S): for the same row of records, both reads and writes are locked. When there is a read-write lock conflict, the later accessed transaction must wait for the previous transaction to complete before it can continue execution.

What MySQL index-related optimizations have been done

Try to use primary key query: all data is stored on clustered index, which reduces the consumption of returning table compared with ordinary index query.

After MySQL5.6, index push-down optimization is introduced to reduce the consumption of back table judgment through the appropriate use of federated index.

If you query a column of data frequently, you can consider using an overlay index to avoid returning to the table.

The federated index places the high-frequency field on the far left.

Briefly talk about the database paradigm.

The first paradigm: attributes can no longer be divided.

The second normal form: on the basis of a normal form, each instance or row in the database table must be uniquely distinguished. You usually need to add a column to the table to store the unique identity of each instance. This unique attribute column is called the primary keyword or primary key.

The third normal form: on the basis of the second normal form, it is required that a database table does not contain non-primary keyword information that has been contained in other tables. Therefore, the third paradigm has the following characteristics: 1). Each column has only one value. 2)。 Every line can be distinguished. 3)。 Each table does not contain non-primary keyword information that other tables already contain.

A table with 10 million pieces of data, how to query in pages

When the amount of data is too large, limit offset paging will query more slowly due to too much scanning data. You can query with the last ID of the current page, SELECT * FROM T WHERE id > # {ID} LIMIT # {LIMIT}. Of course, in this case, ID must be ordered, which is one of the benefits of ordered ID.

The increasing amount of data in the order table leads to slow query, how to deal with it

Sub-database and sub-table. As the utilization rate of historical orders is not high, high-frequency orders may only be recent orders, so the order table is split according to time, and monthly or annual tables are considered according to the amount of data. It is best to include the time of the order ID (for example, generated according to the snowflake algorithm). At this time, you can not only obtain the order record directly according to the order ID, but also query according to the time.

The above is what the MySQL high-frequency interview questions are like. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

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.