The early bird catches the worm. Attention will be found, Tencent netease SF and other recruitment. NET big factory, are explicitly required MySQL database, this is the vane! It took me half a month to sort out the high frequency MySQL database questions in the past few years and carefully find the answers. The total amount is more than 1.8W words. Now I will share it with you!

Interview Questions complete Content (Outline)





Currently, there are 45 questions in total, with answers, a total of 1.8W words, covering the basic, advanced and optimized content of MySQL. There are too many content, please click on the plugin below to copy my wechat account, add me (Cole) and send it to you!



You can test yourself

The whole set is over 10,000 words, not all of them,I’ve arranged some questions for you to test yourself on. Feel free to pick up all your questions and answers! Some plugins! Some plugins! Click the plugin and ask me to take it!

1.6. Tell us the difference between stored procedures and functions

(1) Stored procedure users complete specific operations or tasks in the database (such as insert, delete, etc.), and functions are used to return specific data.

(2) Stored procedures are declared in procedure and functions in function.

(3) Stored procedures do not need to return types; functions must return types.

(4) Stored procedures can be executed as separate PL-SQL, functions cannot be executed as separate PLSQL and must be part of expressions.

(5) Stored procedures can only return values through out and in/out. Functions can use out and in/out as well as return.

(6) SQL statements (DML or SELECT) cannot call stored procedures, while functions can.

1.13. What are inner join, outer join, cross join, Cartesian product, etc.?

Inside connection: connected only matching row left outer join: contains all the table on the left line (regardless of the right of existence and their matching rows in the table), and all matching rows in the table on the right right outer join: contains all the lines on the right side of the table (whether or not in the table on the left side of the existence and their matching rows), as well as all matching rows in the table on the left For example 1:

SELECT a.,b. FROM luntan LEFT JOIN usertable as b ON a.username=b.username
Copy the code

For example 2:

SELECT a.,b. FROM city as a FULL OUTER JOIN user as b ON a.username=b.username
Copy the code

Full-outer join: Contains all rows of the left and right tables, regardless of whether there are rows matching them in the other table. Cross-join: Generates a Cartesian product – this does not use any matching or selection criteria, but matches each row in one data source with each row in another data source. For example:

SELECT type,pub_name FROM titles CROSS JOIN publishers ORDER BY type
Copy the code

MySQL > alter table lock table lock table lock

MyISAM only supports table locks, InnoDB supports table locks and row locks, default row locks. Table lock: low overhead, fast lock, no deadlock. Large lock granularity has the highest probability of lock conflict and the lowest concurrency. Row-level locking: expensive, slow, and deadlocks. The lock force is small, the probability of lock conflict is small, and the concurrency is the highest.

What is the default transaction isolation level for MySQL?

Read Uncommitted (RU): Changes made by a transaction can be seen by other transactions before it is committed. Read Commit (RC): After a transaction commits, its changes are seen by other transactions. 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. 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.

You can test yourself. There are only a few questions, and I’ll add more as soon as I have time, or you can click on the widget below to find me (Cole) and get the full version, complete with detailed answers.

If you have no foundation for MySQL, or look at the document is too boring, here for everyone to prepare a set of “MySQL entry to master” video tutorial, currently updated more than 40, you can also ask me to get, add the party’s wechat.

Here’s a video

Java/ SQL/ MySQL/ database/index/index optimization /MySQL tuning /MySQL

This is a screenshot of the video listing



You can check out the first section, which contains 45 videos. Click the link below to get the full set of videos. Automatically copy your wechat id and add Cole as a friend.

Plus watch on January 15

2.13. Please introduce the MVCC mechanism of Mysql

MVCC is a multi-version concurrency control mechanism that is a concrete way for MySQL’s InnoDB storage engine to implement isolation levels for committed reads and repeatable reads. MVCC is by saving snapshots of the data at some point to implement the mechanism, its behind each row record save two hidden columns, respectively, save the line to create the version number and delete version number, and then Innodb MVCC use to a snapshot of the stored in the Undo log, the log by rollback Pointers connect a data line all the snapshots.

2.14. What are the common Mysql replication architectures?

(1) In the scenario where the read request pressure of the master library is very high, the read and write separation can be realized by configuring the replication architecture of the master and multiple slaves. A large number of read requests that do not require high real-time performance can be distributed to multiple slave libraries through load balancing to reduce the read pressure of the master library. In the event of an abnormal outage of the primary library, a secondary library can be switched over to the primary library to continue providing services.

(2) The architecture of multi-stage replication with one master and many slaves can solve the requirements of most scenarios with particularly heavy read request pressure. Given that MysQL replication is done by “pushing” Binlog logs from the master to the slave, I/0 stress and network stress on the master increases as the slave grows (each slave has a separate Binlog Dump thread on the master to send events), The multilevel replication architecture solves the extra I/0 and network pressure of the master library in the scenario of one master and multiple slaves.

(3) Dual Master replication refers to Master library and Master2 as Master and slave library. Client clients can access Master library for write requests, and Master library or Master2 for read requests.

2.19. What is your understanding of the two common storage engines of MySQL: MyISAM and InnoDB? InnoDB engine: The InnoDB engine provides acid transaction support, row-level locking and foreign key constraints, and is designed for database systems with large data volumes. When MySQL is running, InnoDB creates a buffer pool in memory to buffer data and indexes. However, this engine does not support full-text search and is slow to start. It does not save the number of rows in a table, so when performing select count() from table, you need to scan the entire table. Because of the small granularity of locks, write operations do not lock the entire table. Therefore, using locks in scenarios with high concurrency can improve efficiency.

MyIASM engine: the default engine for MySQL, but does not support transactions, row-level locks and foreign keys. Therefore, when inserting and updating statements, that is, when writing, the table needs to be locked, resulting in a loss of efficiency. Unlike InnoDB, MyIASM saves the number of rows in a table, so when you select count() from a table, you can read the saved values directly without scanning the entire table. Therefore, MyIASM can be used as the database engine of choice if the tables are being read far more than written, and transaction support is not required.

This set of interview questions and what are missing, welcome to leave a message, I will continue to update. Click the plugin below, copy the wechat id and add Cole, you can get it for free.



Thank you all for clicking and collecting this article and building together. The.net community. I wish every little partner in the New Year can be promoted and pay rise, marry Bai Fumei, to the peak of life!

Part of the interview question comes from the Internet, if there is infringement can be contacted to delete. The whole content is not easy to sort out, the omission of insufficient place also asks everyone to excuse for more, can also make suggestions! I will continue to update dry goods, looking forward to attention! I hope to help more developers through my efforts and knowledge sharing. Working together,.NET will be better tomorrow!

At the end of the article

【 morning Net community 】 【C#】

You can directly access the new recording of C# basic syntax full set of video + code resources!



2021, come on!