Shulou(Shulou.com)06/01 Report--

MySQL affairs is a question that we are often asked when we interview middle and senior developers. Although many people often write fluently with MySQL,SQL sentences, they always don't know where to start when they are asked these questions during the interview. Let's first learn what MySQL affairs are, and then share with you 10 necessary MySQL questions for an interview.

MySQL transaction is the smallest operation unit of data processing, which is a set of operations that can not be divided. A series of operations in this operation unit either succeed or fail.

1. The principle of MySQL master-slave replication.

(1) the main database must open the binary log.

(2) when there is a statement of addition, deletion and modification, it will be recorded in the binlog of the main database.

(3) the master database passes the contents of binlog to the relay binlog (relay log) of the slave database through the IO thread (this is the reason why msyql replication is asynchronous replication)

(4) the relay log thread from the library is responsible for reading the information in its SQL and applying it to the database

2. The principle of Seconds_Behind_Master.

Represents the time difference between SQL and io threads

Specific calculation: the current timestamp of the library server is compared with the timestamp of the event in the binary log, so the delay can only be reported when the event is executed.

Deficiency:

Some errors (such as max_allowed_packet mismatch between master and slave, or network instability) may interrupt replication. Because master-slave replication is an asynchronous operation, Seconds_Behind_Master may be displayed as 0.

3. What are the main reasons for the master-slave delay?

(1) too many slow SQL statements

(2) the hardware of the slave library is worse than the master library.

(3) there are too many slave libraries under the same master database.

(4) Network delay

(5) there are too many table partitions

(there are some reasons, welcome to add)

4. MySQL common storage engines and their respective characteristics.

(1), InnoDB

Support transactions, row-level locks, support foreign key constraints, mainly for OLTP applications, the use of next-key locking strategy to avoid the occurrence of phantom reading.

(2), MyISAM

Do not support transactions, table lock design, support full-text indexing, read and write block each other, do not support foreign key constraints; mainly for OLAP application scenarios; cache pool only caches index files, not data files

(3), Memory

Saving all data in RAM provides extremely fast access in environments where references and other similar data need to be found quickly. If the database restarts or crashes, the data will be lost.

(4), TokuDB

Support transactions, high compression, tell read and write, sparse tree-based index design; support most online modification of the index, add fields.

(5), Inforbright/infinidb

Column storage, high compression, fast single-column query

5. What do the innodb_flush_log_at_trx_commit parameters 0, 1, and 2 represent, respectively?

The innodb_flush_log_at_trx_commit parameter controls when update records in redo log buffer are written to the log file and when the log file is flushed to disk.

0

Once per second, log buffer is triggered to write to log file, and log file is flushed to disk.

(due to process scheduling problems, 100% refresh per second cannot be guaranteed; if the mysql process crashes, 1s transactions may be lost; the most efficient, but the least secure)

one

Each transaction commit triggers the log buffer to be written to the log file, and the log file is flushed to disk.

two

Every time the transaction commits, the log buffer is written to the log file; every second the log file is flushed to disk.

(if the operating system crashes or a power outage occurs, 1s transactions may be lost.)

6. The difference between varchar and char in Mysql

The length of the CHAR column is fixed to the length declared when the table is created, in the range of 0-255

The length of VARCHAR column is not fixed, range (0-65535)

7. The meaning of 50 in varchar (50) and the meaning of 20 in int (20).

The 50 in varchar (50) can store up to 50 characters.

The meaning of 20 in int (20) indicates the display width, and it only makes sense along with zerofill.

8. The meaning, applicable scenarios and advantages and disadvantages of several log entry formats of MySQL binlog.

(1), statement level mode

Each sql that modifies the data is recorded in the binlog of the master, and when the slave is copied, the sql process parses to the same sql that was executed on the original master side and executes again.

Applicable scenarios: the requirement of master-slave data consistency is not too high, and functions, stored procedures, triggers and other scenarios are rarely used.

Advantages: a small number of bin-log logs

Disadvantages: some new features (functions, stored procedures, triggers) have obstacles to synchronization, such as now ()

(2), row level mode

Each row of data will be recorded in the log as modified, and then the same data will be modified on the slave side.

Applicable scenarios: scenarios that require high consistency of master-slave data.

Advantages: details of records

Disadvantages: binlog logs are too large

(3), mixed mode

MySQL defaults to statement format for recording binary log files, but row format is used in some cases, possibly:

1). The storage engine of the table is NDB, and all DML operations on the table are recorded in ROW format.

2) when using uncertain functions such as UUID (), USER (), CURRENT_USER (), FOUND_ROWS (), ROW_count (), etc.

3) insert delay statement is used

4), using user-defined functions (UDF)

5) temporary tables are used

Applicable scenarios: do not have high requirements for master-slave data consistency, and may use functions, stored procedures, triggers and other scenarios

Advantages and disadvantages are between statement and row modes.

9. The difference between redo logs and binary logs (at least three points)

(1) the storage engine is different:

Binlog records the operations of all storage engines

Redo log only logs the innodb storage engine

(2) the contents of the records are different:

Binlog records the specific operation of a transaction. For logical log

Redo log records the physical changes of each page.

(3) the time of writing is different:

The binlog file is committed only before the transaction commits, that is, it is written to disk once.

While the transaction is in progress, more and more redo log entries are written to the redo log file.

10. What elements should be paid attention to in the Explain implementation plan?

(1), type: this query table join type, from which you can see the approximate efficiency of this query.

(2), key: the final selected index. If there is no index, the query efficiency is usually very poor.

(3), key_len: the actual length of the index used for result filtering in this query

(4), rows: the estimated number of records to be scanned. The smaller the number of records to be scanned, the better.

(5), extra: additional information, mainly confirming whether similar situations of Using filesort and Using temporary occur.

The above 10 MySQL interview questions are just some of the many MySQL interview questions. I think they are more typical and appear more frequently. I hope they will be helpful to you!

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.