Common interview questions

Time:2022-4-23

Prepare for the internship and regularly summarize the interview questions of the regular exam. Let’s cheer together!

Previous articles:

Reference article:

  • https://blog.csdn.net/qq_45966440/category_11098049.html

  • https://csp1999.blog.csdn.net/article/details/116069114

  • https://joonwhee.blog.csdn.net/article/details/106893197

  • https://www.pdai.tech/md/db/sql-mysql/sql-mysql-overview.html

  • https://javaguide.cn/

be careful:

If there are mistakes in this article, please correct them in the comment area!

Article catalogue

Common Basics

1. Tell me about your understanding of MySQL architecture?

Common interview questions

  • Connector:Identity authentication is related to permissions (when logging in to MySQL)
  • Query Cache:When executing query statements, the cache will be queried first (removed after MySQL version 8.0, because this function is not practical)
  • analyzer:If you fail to hit the cache, the SQL statement will pass through the analyzer. The analyzer says that you should first see what your SQL statement wants, and then check whether your SQL statement syntax is correct
  • optimizer:Execute according to the scheme that MySQL thinks is the best
  • Actuator: when the execution scheme is selected, MySQL is ready to start execution. Before execution, it will check whether the user has permission. If not, an error message will be returned. If yes, it will call the engine interface and return the result of interface execution

Generally speaking, MySQL can be divided into two parts: server layer and storage engine:

  • Server layer

    It mainly includes connector, query cache, analyzer, optimizer, executor, etc. all functions across storage engines are implemented in this layerFor example, stored procedures, triggers, views, functions, etc. there is also a general logging module, binglog logging module.

  • Storage engine

    Mainly responsible for data storage and reading, adopt replaceable plug-in architecture and supportInnoDBMyISAMMemoryThere are several storage modules of innoreddib, including its own log engine. Now the most commonly used storage engine is InnoDB, which has been used as the default storage engine since MySQL version 5.5.5.

Attachment: for more detailed functions, please refer toIntroduction to basic components of server layer

2. Talk about the three paradigms of database?

  • First paradigmEnsure that each column remains atomic, all field values in the data table are non decomposable atomic values
  • Second paradigm: the attribute of the entity is required to be completely dependent on the primary keyword. The so-called complete dependency means that there cannot be attributes that only depend on part of the primary keyword. in other wordsEnsure that every column in the table is related to the primary key
  • Third paradigm: any non primary attribute does not depend on other non primary attributes. in other wordsEnsure that each column is directly related to the primary key column, not indirectly

3. How to execute an SQL query statement in MySQL?

  1. First, the application queries SQL statementsSend to server for execution
  2. Query cache, if the query cache is open, the server will not directly query the database after receiving the query request, but find out whether there is corresponding query data in the query cache of the database. If there is, it will directly return to the client. The following operations can be performed only when the cache does not exist
  3. Query optimization processing and generate execution plan。 This stage mainly includes parsing SQL, preprocessing and optimizing SQL execution plan
  4. MySQL executes according to the corresponding planComplete the entire query
  5. Finally, the query resultsReturn to client

👩‍💻The interviewer asked: are you talking about the command execution order of MySQL query?

Writing order:

SELECT DISTINCT
	<select list>
FROM
	<left_table> <join_type>
JOIN
	<right_table> ON <join_condition>
WHERE
	<where_condition>
GROUP BY
	<group_by_list>
HAVING
	<having_condition>
ORDER BY
	<order_by_condition>
LIMIT
	<limit_params>

Execution sequence:

Common interview questions

4. What are the data types in MySQL?

It can be roughly divided into four categories:

  1. integer
    TINYINTSMALLINTMEDIUMINTINTBIGINTIt occupies 8, 16, 24, 32 and 64 bit storage space respectively.
  2. Floating point number
    FLOATDOUBLEandDECIMALIs a floating point number class.
    • Decimal is processed by string and can store accurate decimals. Compared with float and double, decimal is less efficient.
    • Float, double and decimal can all specify the column width. For example, float (5,2) represents a total of 5 digits. Two digits store the decimal part and three digits store the integer part.
  3. character string
    String commonly used areCHARandVARCHAR
  4. date
    More commonly used areyeartimedatedatetimetimestampetc.

👩‍💻Ask the interviewer in detail about the difference between VaR and MySQL?

  • char: fixed length type, such as subscription char (10). When you enter “ABC” three characters, they still occupy 10 bytes, and the other 7 are empty bytes.

    • Advantages: high efficiency
    • Disadvantages: occupied space
    • Applicable scenario: store the MD5 value of the password, which is of fixed length. Char is very suitable
  • varchar: variable length,The stored value is = the byte occupied by each value + the length of a byte used to record its length

    In terms of space, varchar is more appropriate. In terms of efficiency, char is more appropriate, and the use of the two needs to be weighed.

Table comparison:

Comparison item char varchar
Length characteristics Fixed length, store characters Variable length, store characters
Insufficient length When the inserted length is less than the defined length, useSpace filling When less than the defined length, pressActual insertion length storage
performance Access speed ratio varcharfastMuch more Access speed ratio charslowMuch more
Usage scenario Suitable for storing very short,Fixed length string, such ascell-phone numberMD5Value, etc Suitable for use inScene with variable length, such asReceiving address, emailAddress, etc

👩‍💻The interviewer continued to ask: what is the difference between the field types datetime and timesta in MySQL?

type Occupied byte Range Time zone problem
datetime 8 bytes 1000-01-01 00:00:00 to 9999-12-31 23:59:59 storageTime zone independent, it won’t change
timestamp 4 bytes 1970-01-01 00:00:01 to 2038-01-19 11:14:07 Stored isTime zone related, which changes with the time zone of the database

It should be used as much as possibletimestamp, compared todatetimeIt has higher space efficiency

5. Tell me the difference between InnoDB and MyISAM?

contrast InnoDB MyISAM
affair support I won’t support it
Lock type Row lock, table lock Watch lock
MVCC support I won’t support it
Foreign key support I won’t support it
Indexes Clustered index and full-text index after 5.7 Non clustered index, support full-text index
Security Support safe recovery after abnormal database crash I won’t support it
cache It only caches indexes and also caches real data, which requires high memory Only cache the index, not the real data
backups InnoDB supports online hot backup I won’t support it

👩‍💻The interviewer asked: how to choose between the two when choosing the storage engine?

  • InnoDB

    It is the default storage engine of MySQL. It is used for transaction processing applications and supports foreign keys.If the application has high requirements for transaction integrity and requires data consistency under concurrent conditions, data operations include many update and delete operations in addition to insertion and query, the InnoDB storage engine is a more appropriate choice. InnoDB is the most suitable choice for systems that require high data accuracy, such as billing system or financial system.

  • MyISAM

    If the application is dominated by read and insert operations, there are few update and delete operations, and the requirements for transaction integrity and concurrency are not very high, it is very appropriate to choose MyISAM storage engine

6. What is buffer pool?

  • Buffer PoolyesInnoDBA cache area maintained to cache data and indexes. In memory, it is mainly used to speed up data reading and writing, the larger the buffer pool, the more MySQL looks like an in memory database. The default size is128M
  • InnoDB will store the hot data and some data that InnoDB thinks will be accessed in the buffer pool to improve the data reading performance.
  • When InnoDB modifies data, if the data page is in the buffer pool, it will directly modify the buffer pool. At this time, we call this pageDirty pageInnoDB will refresh dirty pages to disk at a certain frequency, which can minimize disk I / O and improve performance

Common interview questions

Index article

1. Talk about your understanding of the index?

Index is a data structure used to quickly query and retrieve data. The common index structures are: B tree, B + tree and hash.

The function of index is equivalent to that of directory. For example: when we look up the dictionary, if there is no directory, we can only find the word we need to look up page by page, which is very slow. If there is a directory, we just need to find the position of words in the directory, and then turn to that page directly.

👩‍💻The interviewer asked: what are the advantages and disadvantages of the index?

advantage:

  • Using index can greatly speed up the retrieval of data(greatly reduce the amount of data retrieved), which is also the main reason for creating an index
  • By creating a unique index, you can ensure the uniqueness of each row of data in the database table

Disadvantages:

  • In terms of time.Creating and maintaining indexes takes a lot of time。 When adding, deleting and modifying data in the table, if the data has an index, the index also needs to be dynamically modified, which will reduce the efficiency of SQL execution
  • From a spatial perspective.The index requires physical file storage, which also consumes a certain amount of disk space

👩‍💻The interviewer continues to ask: what is suitable for indexing and what is not?

Suitable for indexing:

  • In the most frequently usedFields used to narrow the queryIndex on
  • In frequently usedFields to sortIndex on

Not suitable for indexing:

  • For queryColumns rarely involved or columns with many duplicate values, not suitable for indexing
  • aboutSome special data types, it is not suitable to establish an index, such as * * text field (text) * *, etc

2. What are the types of indexes?

  • primary key

    The primary key column of the data table uses the primary key index. A data table can only have one primary key, and the primary key cannot be null or duplicate.

  • Unique key

    A unique index is also a constraint. The attribute column of a unique index cannot have duplicate data, but the data is allowed to be null. Multiple unique indexes can be created in a table. The purpose of establishing a unique index is mostly for the uniqueness of the data of the attribute column, not for query efficiency.

  • General index

    The only function of ordinary index is to query data quickly. A table allows the creation of multiple ordinary indexes and allows data duplication and null.

  • Prefix index

    Prefix index applies only to string type data. Prefix index is to create an index for the first few characters of text. Compared with ordinary index, the data created is smaller, because only the first few characters are taken.

  • Full text index

    Full text index is mainly used to retrieve the keyword information in large text data. It is a technology used in search engine database at present. (before mysql5.6, only MyISAM engine supports full-text indexing, and InnoDB also supports full-text indexing after 5.6)

The unique index, ordinary index, prefix index and full-text index are also called secondary index (auxiliary index)

3. What are the indexes of MySQL?

  • B + tree index
    • Is the default index type for most MySQL storage engines.
  • Hash indices
    • Hash index can be searched in O (1) time, but it loses order.
    • The InnoDB storage engine has a special function called “adaptive hash index”. When an index value is used very frequently, a hash index will be created on top of the B + tree index, so that the B + tree index has some advantages of hash index, such as fast hash search.
  • Full text index
    • MyISAM storage engine supports full-text indexing, which is used to find keywords in text rather than directly compare whether they are equal. The search criteria use match against instead of the ordinary where.
    • Full text index is generally implemented by inverted index, which records the mapping of keywords to their documents.
    • InnoDB storage engine also supports full-text indexing in MySQL version 5.6.4.
  • Spatial index data
    • MyISAM storage engine supports spatial data index (R-tree) and can be used for geographic data storage. Spatial data index will index data from all dimensions, and can effectively use any dimension for combined query.

InnoDBMyISAMMemoryThree storage engines support various index types:

Indexes InnoDB engine MyISAM engine Memory engine
B + tree index support support support
Hash indices I won’t support it I won’t support it support
Spatial index data I won’t support it support I won’t support it
Full text index Supported after version 5.6 support I won’t support it

4. What is a B + tree?

Common interview questions

  1. B+ tree is implemented based on the sequential access pointer of B tree and leaf nodes. It has the balance of B tree, andThe performance of interval query is improved by sequentially accessing pointers
  2. When searching, first perform a binary search on the root node to find the pointer of a key, and then recursively search on the node pointed to by the pointer. Until the leaf node is found, and then perform binary search on the leaf node to find the data corresponding to the key
  3. Inserting and deleting operations will destroy the balance of the balance tree. Therefore, after inserting and deleting operations, you need to split, merge, rotate and other operations on the tree to maintain the balance

👩‍💻The interviewer asked: what are the similarities and differences between B tree and B + tree?

B tree structure diagram:

Common interview questions

It can be seen that:

  • All nodes of the B tree store both keys and data, andOnly leaf nodes of B + tree store key and data, and other internal nodes store key only
  • The leaf nodes of B tree are independent,The leaf node of B + tree has a reference chain pointing to the leaf node adjacent to it
  • The retrieval process of B-tree is equivalent to binary search for the keywords of each node in the range. The retrieval may end before reaching the leaf node. andThe retrieval efficiency of B + tree is very stable. Any search is a process from root node to leaf node, and the sequential retrieval of leaf nodes is obvious.

👩‍💻The interviewer asked: why does MySQL database use B + tree to store indexes? Instead of red and black trees, hash and B trees?

  • Red black tree

    If in memory, the search efficiency of red black tree is higher than that of B tree, but when it comes to disk operation, B tree is better。 Because the red black tree is a binary tree, when the amount of data is large, the number of layers of the tree is very high. In the process of looking down from the root node of the tree, every reading of a node is equivalent to an IO operation. Therefore, the I / O operation of the red black tree will be much more than that of the B tree.

  • Hash index

    If only a single value is queried, the efficiency of hash index is very high. However, hash indexing has several problems:① Range query is not supported ② sorting operation of index values is not supported ③ leftmost matching rule of union index is not supported.

  • Index tree B

    • Compared with the B + tree, the B-tree requires local middle order traversal during range query, which may require cross layer access. Cross layer access represents additional disk I / O operations
    • Each node of the B tree stores data, while only the leaf node of the B + tree stores data. Therefore, when finding the same amount of data, the height of the B tree is higher and the IO is more frequent
    • Read in page units, so that one node can be fully loaded with one I / O, and the adjacent nodes can also be preloaded; Therefore, the data placed on the leaf node is essentially a page

👩‍💻B + how many nodes in the interview tree are suitable?

1 page or multiple of page is the most appropriate。 Because if the size of a node is less than one page, one page will be read when reading this node, resulting in a waste of resources. Therefore, in order not to cause waste, it is most appropriate to control the size of a node in multiples of 1 page, 2 pages and 3 pages.

The “page” here refers to the unit defined by MySQL (similar to the operating system), which is the user-defined unit of MySQLInnodbThe default size of 1 page in the engine is16k, you can use the commandSHOW GLOBAL STATUS LIKE 'Innodb_page_size'see.

Common interview questions

5. Talk about clustered index and non clustered index?

  • Clustered indexIndex: the index in which the index structure and data are stored together.The primary key index belongs to the clustered index

    In mysql, the table of InnoDB engine.ibdThe file contains the index and data of the table. For the InnoDB engine table, each non leaf node of the index (B + tree) of the table stores the index, and the leaf node stores the index and the data corresponding to the index

  • Nonclustered indexIndex: index structure and data are stored separately.The secondary index is a nonclustered index

    Table of MyISAM engine.MYIThe file contains the index of the table. Each non leaf node of the index (B + tree) of the table stores the index. The leaf node stores the index and the pointer to the data corresponding to the index.MYDFile data.

As shown in the figure:

Common interview questions

As shown above,The leaf node of the primary key index stores real data. The data area of the auxiliary index leaf node stores the value of the primary key index keyword

If you want to query the data with name = C, the search process is as follows:

  1. First query the auxiliary index through C, and finally find the primary key id = 9
  2. The primary key of the data in the final index node is the primary key ID of the search node 9.Therefore, it is necessary to retrieve the index twice through the auxiliary index

One reason for this design is:Like MyISAM, if the data row pointers are stored in the leaf nodes of the primary key index and secondary index, once the data is migrated, you need to reorganize and maintain all indexes

👩‍💻The interviewer asked: what are the advantages and disadvantages of clustered index?

  • Advantages of clustered indexes

The query speed of clustered index is very fast, because the whole B + tree itself is a multi fork balanced tree, and the leaf nodes are orderly. Locating the index node is equivalent to locating the data.

  • Disadvantages of clustered indexes

    1. Rely on ordered data

      Because the B + tree is a multi-path balanced tree, if the indexed data is not orderly, it needs to be sorted during insertion. If the data is integer, it’s OK. Otherwise, for long and difficult data like string or UUID, the insertion or search speed must be slow

    2. The cost of updating is high

      If the data of the index column is modified, the corresponding index will also be modified, and the leaf node of the clustered index also stores data. The modification cost must be large. Therefore, for the primary key index, the primary key is generally not modifiable

👩‍💻The interview continues to ask: what are the advantages and disadvantages of non clustered index?

  • Advantages of nonclustered indexes

    Update costs are less than clustered indexes。 The update cost of non clustered index is not as big as that of clustered index. The leaf node of non clustered index does not store data

  • Disadvantages of non clustered indexes

    1. Like a clustered index,Nonclustered indexes also rely on ordered data
    2. It may be queried twice (back to the table)。 This should be the biggest disadvantage of non clustered indexes. After finding the pointer or primary key corresponding to the index, you may need to query the data file or table according to the pointer or primary key

👩‍💻The interview continued to ask: must non clustered indexes be queried back to the table?

Not necessarily. See question 6 below for details

6. What is overlay index?

Overlay index: refers to that in a query, if an index contains or overwrites the values of all the fields to be queried, we call it overwrite index instead of back to table query

Overwriting the index means that the field to be queried is exactly the field of the index. Then you can query the data directly according to the index without going back to the table for query.

Take chestnuts for example:

  • For example, if a SQL needs to query the primary key, the primary key can be found exactly according to the primary key index.
  • Another example is the ordinary index. If an SQL needs to query the name and the name field happens to have an index, the data can be queried directly according to the index without returning to the table.

Common interview questions

Affairs

1. Talk about what is business?

Transaction is a user-defined sequence of database operations. These operations are either not done at all or done at all. It is an inseparable work unit.

Take chestnuts: take the most classic transfer example. If Xiaoming wants to transfer 1000 yuan to Xiaohong, this transfer will involve two key operations: reduce Xiaoming’s balance by 1000 yuan and increase Xiaohong’s balance by 1000 yuan. In case of a sudden error between these two operations, such as the collapse of the banking system, which leads to the decrease of Xiaoming’s balance and the non increase of Xiaohong’s balance, it is wrong. Transaction is to ensure that these two key operations either succeed or fail.

2. Talk about the acid characteristics of transactions?

Common interview questions

  1. Atomicity

    Transaction is the smallest execution unit and cannot be split. The atomicity of the transaction ensures that all operations of the transaction either commit successfully or fail and roll back.

  2. Consistency

    The data is consistent before and after the transaction is executed. For example, in the transfer business, the total amount of the transferor and the payee should remain unchanged regardless of whether the transaction is successful or not.

  3. Isolation

    When accessing the database concurrently, a user’s transaction is not disturbed by other transactions, and the database is independent between concurrent transactions.

  4. Durability

    Once a transaction is committed, its changes to the data in the database should be permanent. The following other operations or faults should not have any impact on its execution results.

3. Talk about the problems caused by concurrent transactions?

  1. Dirty read

    When a transaction is accessing data and making changes to the data, and the changes have not been committed to the database, another transaction also accesses the data and uses the data. Because this data is uncommitted data, the data read by another transaction may be “dirty data”, which is dirty reading.

  2. Lost to modify

    Two transactions T1 and T2 read the same data and modify it. The result submitted by T2 destroys the result submitted by T1, resulting in the loss of T1 modification.

  3. Unrepeatable read

    After transaction T1 reads some data, transaction T2 updates itmodify, when transaction T1 reads the data again, a value different from the previous one is obtained. Therefore, it is called non repeatable reading.

  4. Phantom read

    Similar to non repeatable reading:

    After transaction T1 reads some data records from the database according to certain conditions, transaction T2deleteSome of the records were. When T1 read the data again under the same conditions, it was found that some records mysteriously disappeared.

    After transaction T1 reads some data records from the database according to certain conditions, transaction T2insertFor some records, when T1 reads the data under the same conditions again, some more records are found.

4. What are the isolation levels of transactions?

The SQL standard defines four isolation levels:

  1. Read uncommitted

    The lowest isolation level, which allows reading uncommitted data changes,It may cause dirty reading, unreal reading or non repeatable reading.

  2. Read committed

    It is allowed to read the committed data of concurrent transactions,Dirty reading can be prevented, but unreal reading or non repeatable reading can still occur.

  3. Repeatable read

    The results of multiple reads of the same field are consistent, unless the data is modified by its own transaction,Dirty reading and non repeatable reading can be prevented, but phantom reading can still occur.

  4. Serializable

    The highest isolation level, completely subject to the isolation level of acid. All transactions are executed one by one, so that there is no interference between transactions, that is to say,This level prevents dirty reads, non repeatable reads, and unreal reads.

Summarize a table:

Isolation level Dirty reading Non repeatable reading Unreal reading
Read uncommitted
Read committed ×
Repeatable read × ×
Serializable × × ×

👩‍💻The interviewer asked: what is the default isolation level of MySQL?

The default supported isolation level of MySQL InnoDB storage engine isRepeatable-read。 We can passSELECT @@tx_isolation;Command to view MySQL 8.0. The command is changed toSELECT @@transaction_isolation;

Common interview questions

Optimization article

1. How do you optimize SQL in daily projects?

It can be considered from several aspects:

Optimize table structure

  • Try to use numeric fields。 If the field containing only numerical information should not be designed as character type, it will reduce the performance of query and connection and increase the storage overhead. This is because the engine will compare each character in the string one by one when processing queries and connections. For numeric types, only one comparison is enough.

  • Use as much as possiblevarcharreplacechar。 Variable length field has small storage space, which can save storage space.

  • When there is a large amount of duplicate data in the index column, the index can be deleted。 For example, if there is a column with gender and only men and women, such an index is invalid.

Optimize query

  • Try to avoid using in the where clause!=or<>OperatorOtherwise, the engine will abandon the index and perform a full table scan.
  • Try to avoid using in the where clauseorTo connect conditions, if a field has an index and a field has no index, the engine will give up using the index and scan the whole table.Recommended useunionreplaceor
  • Try to avoid changing fields in the where clausenullValue judgmentOtherwise, it will cause the engine to abandon the index and perform a full table scan
  • inandnot inAlso use it with caution, otherwise it will cause full table scanning
  • When optimizing nested queries, you can replace subqueries with multi table join queries as much as possible(JOIN
  • No queries should appearselect *

Index optimization

  • Try to use composite indexes instead of single column indexes
  • Leftmost prefix rule: if multiple columns are indexed, follow the leftmost prefix rule. It means that the query starts from the top left of the index and does not skip the columns in the index
  • As query criteria andorder byIndex fields
  • To optimize the query, try to avoid full table scanning. First, you should considerwhereandorder byIndex on the column involved

Other optimization

When data processinginsertDuring operation, the following optimization schemes can be considered:

  • If you need to insert many rows of data into a table at the same time, you should try to use the insert statement of multiple value tables, this method will greatly reduce the consumption of connection and shutdown between the client and the database. This makes the efficiency faster than a single insert statement executed separately.
  • Insert data after manually starting a transaction
  • Orderly data insertion (by primary key)

Attachment: for more detailed SQL optimization operations, please refer todba

2. What is the leftmost prefix principle? What is the leftmost matching principle?

Leftmost prefix principleWhen creating a multi column index, the most frequently used column in the where clause should be placed on the left according to business needs.

When we create a composite index, such as (A1, A2, A3), it is equivalent to creating three indexes (A1), (A1, A2) and (A1, A2, A3), which isLeftmost matching principle

3. What are the meanings of the fields in the explain analysis execution plan?

For detailed explanation articles, please refer to:MySQL advanced explanation

stayselectAdd before statementexplainKeyword, which will return the information of the execution plan.

Meaning of each field:

field meaning
id The sequence number of select query is a group of numbers, which indicates the order in which the select clause or operation table is executed in the query.
select_type Indicates the type of select. Common values include simple (simple table, i.e. no table connection or sub query), primary (primary query, i.e. outer query), Union (the second or subsequent query statement in Union), etc
table Show which table the data in this row is about
type Indicates the connection type of the table. The connection type with good performance to poor performance is: system > const > Eq_ ref > ref > fulltext > ref_ or_ null > index_ merge > unique_ subquery > index_ subquery > range > index > ALL
possible_keys Represents the index that may be used when querying
key Represents the index actually used
key_len Length of index field
rows This column is the number of rows that MySQL estimates to read and detect. Note that this is not the number of rows in the result set
extra Other additional execution plan information. For example, using index, using where, using temporary, etc

4. Will the index fail?

Answer → optimization – 1 – optimization query

Master slave copy & read write separation

1. Which three threads are involved in master-slave replication?

Master slave replication mainly involves three threads: binlog thread, I / O thread and SQL thread

  • Binlog threadWrite data changes on the master server:Binary logBinary log)Medium
  • I / O thread: be responsible for reading binary logs from the master server and writing them to the slave serverrelay logs Relay log
  • SQL thread: be responsible for reading the relay log, parsing the data changes executed by the master server and replaying them in the slave server

Common interview questions

The specific process of master-slave replication is divided into three steps:

  1. When the transaction is committed, the master master database will record the data changes as time events in the binary log file binlog
  2. The master library pushes the log events in the binary log file binlog to the relay log relay log of the slave library
  3. Slave redoes the events in the relay log from the library, which will change the data reflecting itself

2. Why is the master-slave synchronization delayed?

If a server opens n connections to the client, there will be large concurrent update operations, but there is only one SQL thread reading binlog from the server, so the slave SQL may not keep up with the processing speed of the main database.

👩‍💻The interviewer asked: what is the solution to the delay in master-slave synchronization?

  • Network: try to ensure the communication between the master database and the slave databaseNetwork stability, less delay;
  • Hardware: from libraryBetter configurationHardware to improve the performance of random write;
  • Configuration: try to make MySQL operationsComplete in memory, reduce disk operations. Or upgrade mysql5 Version 7 uses parallel replication;
  • Construction: try to be right in affairsMain library read / write, other non transactional reads are in the slave library. Eliminate partdelayThe resulting database is inconsistent.Increase cacheReduce the load of some slave libraries.

3. When the main database goes down, data may be lost. How to solve this situation?

Common interview questions

You can use semi synchronous replication or full synchronous replication.

  • Semi synchronous replication

    After the main database modification statement is written to binlog, it will not immediately return results to the client. Instead, first send the binlog to the slave library through the log dump thread. After receiving the binlog from the I / O thread of the slave library, write it to the relay log, and then return the ack to the master library. After receiving the ACK, the master library returns it to the client successfully

  • Full synchronous replication

    After the master database writes the binlog, the logs are forced to be synchronized to the slave database, and all slave databases are returned to the client after execution. However, it is obvious that the performance of this method will be seriously affected

4. How do you understand the separation of reading and writing in database?

Read write separationIt is usually implemented by proxy. The proxy server receives the read-write request from the application layer, and then decides which server to forward to. The master server handles write operations and read operations with high real-time requirements, while the slave server handles read operations.

👩‍💻The interviewer asked: why can read-write separation improve performance?

  1. The master and slave servers are responsible for their own reading and writing, which greatly alleviates the contention of locks
  2. Available from serverMyISAM, improve query performance and save system overhead
  3. Increase redundancy and availability

Common interview questions

Lock article

1. MySQL encounters deadlock. How do you solve it?

  1. View deadlock logshow engine innodb status;
  2. Find deadlock SQL
  3. Analyze SQL locking
  4. Simulated deadlock
  5. Analyze deadlock log
  6. Analyze deadlock results

2. What are optimistic locks and pessimistic locks in MySQL and their differences?

  • Optimistic lock

    It is considered that the concurrent operation of the same data will not be modified. When updating data, it will modify the data by constantly trying to update。 That is, first apply for the operation directly regardless of whether the resources are occupied by other threads. If there is no conflict, the operation is successful. If there is a conflict, it indicates that other threads are already in use, and then keep trying.

    Implementation method of optimistic lock: generally throughVersion numberandCAS algorithmrealization.

  • Pessimistic lock

    It is considered that the concurrent operation of the same data must be modified. Even if there is no modification, it will be considered as modified。 Therefore, for concurrent operations of the same data, pessimistic locking takes the form of locking. Pessimists believe that there will be problems with concurrent operations without locks.

    Lock sharing and lock sharing,select...lock in share modeselect…for update

3. What is the difference between table level lock and row level lock?

  • Table lock

    A lock with the largest locking granularity in mysql, which locks the whole table of the current operation. It is simple to implement and consumes less resources,Fast locking, no deadlock. It has the largest locking granularity, the highest probability of triggering lock conflict and the lowest concurrency, both MyISAM and InnoDB engines support table level locks.

  • Row level lock

    A lock with the smallest locking granularity in mysql, which locks only the rows of the current operation. Row level lock can greatly reduce the conflict of database operation.The granularity of locking is the smallest and the concurrency is high, but the cost of locking is also the largest. Locking is slow and deadlock will occur。 All InnoDB engines support table level locks, but MyISAM does not.

👩‍💻The interviewer asked: how is InnoDB’s row lock implemented?

InnoDB row locks are implemented through index entries on the index. Signifier,InnoDB will use row level locks only when data is retrieved through index conditions. Otherwise, InnoDB will use table locks!

  • For primary key index:Just lock the primary key index directly
  • For normal indexes:Lock the ordinary index first and then the primary key index. This is because there may be multiple indexes in a table. The primary key index can ensure that the lock is uniqueIf two transactions are locked at the same time, there may be two operations on the same data at the same time.

👩‍💻The interviewer continued to ask: what are the algorithms of InnoDB lock?

There are three types:

  • Record lockRecord lock (row lock), a single index record is locked. The index is always locked, not the record itself.
  • Gap lockClearance lock, lock the gap between index records, or lock before or after an index record, not including the index record itself.
  • Next-key lockKey lock, the combination of record lock and gap lock not only locks the record itself, but also locks the gap between indexes.

For detailed explanations of the three locks, please refer to:How does MySQL solve unreal reading and non repeatability?

4. Talk about shared locks and exclusive locks?

  • Shared lock

    Also known asRead lock, abbreviationS lockAs the name suggests, a shared lock means that multiple transactions can share a lock for the same data, and the data can be accessed, but can only be read and cannot be modified. One transaction obtains a shared lock, and other transactions can add a shared lock, but cannot add an exclusive lock.

  • Exclusive lock

    Also known asWrite lock, abbreviationX lockAs the name suggests, an exclusive lock cannot coexist with other locks. For example, if a transaction obtains an exclusive lock of a data row, other transactions cannot obtain other locks of the row, including shared locks and exclusive locks. However, the transaction that obtains the exclusive lock can read and modify the data row.

Common interview questions
Last favorite little partner, remember the triple!

Recommended Today

JS generate guid method

JS generate guid method https://blog.csdn.net/Alive_tree/article/details/87942348 Globally unique identification(GUID) is an algorithm generatedBinaryCount Reg128 bitsNumber ofidentifier , GUID is mainly used in networks or systems with multiple nodes and computers. Ideally, any computational geometry computer cluster will not generate two identical guids, and the total number of guids is2^128In theory, it is difficult to make two […]