Interview questions for MySQL

1 , MySQL principles of replication and processes.

(1) First ask the basic principle flow, 3 threads and the association between them.

A: MySQL replicates three threads: the main library thread, from the library I/O thread, from the library SQL thread;

Replication process: (1) I/O thread makes a request to the main library

(2) The main library thread responds to the request and pushes the Binlog log to the slave library

(3) I/O thread receives thread and logs in the trunk log

(4) SQL thread reads SQL from the log, and Binlog logs from the library, flush into the hard disk;

(2) Re-ask consistency delay, data recovery;

A: (1) The master-slave replication consistency is guaranteed by Binlog Execution Order (TIMESPAN+POS);

The more detailed the log, the more easily the master-slave consistency is ensured;

(2) Delay: The delay performance is behind_master_pos behind the number, in fact, is not accurate;

5.5.30 Previous versions are asynchronous replication, so there are delays. Because the main library is executed from the library after execution, there is a delay after the first one;

The accurate calculation method of master-slave delay is: delay time = The time when the SQL is executed from the library-when the main library starts executing SQL;

(3) Data recovery: Binlog location point recorded at backup (Timespan+pos);

(3) Re-ask all kinds of work encountered in the resolution of the replication bug;

Answer: This problem feeling description is not accurate, not clear is the master-slave replication failure or bug;

Fault generally due to primary key conflict, link is not on the main library, can not find the corresponding Binlog location, etc. caused;

The workaround is to skip the conflict, check the master-slave link, and find the correct POS;

Bug is not common, I met once, share the following:

Environment: The main library from the library are virtual machines, every 10 minutes and host synchronization time, about each time with the host 2 seconds;

Performance: Repeated execution of logs within two seconds from library copy;

From the library show slave status\g,behind_master_pos between 60000 and 0 cycles, once every two seconds;

2, the difference between MyISAM and InnoDB in MySQL , at least 5 points.

(1) Ask 5 different points

A: 1, storage costs are different, storage restrictions are not the same;

2, the CPU usage cost is different, InnoDB caches the data and the index;

3, lock granularity is not the same, support MVCC;

4. Cache mechanism is different (Buffer_pool and Key_buffer)

5, business support;

6. Index support: Full-text index (MYISAM), foreign Key (InnoDB), hash (InnoDB)

7, read and write speed;

8, backup;

(2), ask a variety of different MySQL version of the 2 improvement;

Recently measured 5.1.38 and 5.5.35

Innodb: (1) Adeptive_innodb_index controllable;

(2) InnoDB becomes the default engine;

(3) faster innodb insertion;

(4) Number of read and write threads;

(5) Semi-synchronous replication;

(6) Performance_schame

Myisam (1)

(3) Theway to realize the index of 2 persons;

MyISAM the index and data are stored separately, index records the physical location of the key values in the index, according to the physical location to MyD data page to find the corresponding pages; The data arrangement is the heap data, there is no physical order, the index simply strings the data logically, and does not change the physical location of the data;

InnoDB uses the primary key to physically sort the data, and the newly inserted data will modify the primary key index sequence according to the primary key size; Secondary index finds the data by looking up the primary key;

3 , Ask MySQL in varchar with the Char the difference and varchar (+) in the - the meaning of the representative.

(1) The difference between varchar and char

Answer: variable length and fixed lengths

(2) The meaning of 50 in varchar (50)

A: The maximum length of a character is 50, the number of bytes represented is related to the character set, for example, the UTF8 is 3 bytes, then the varchar (50) field takes up to 150 bytes in the table.

(3) Meaning of 20 in int (20)

A: int is a type of number, in the 2 binary record, the length of the maximum is 20, the number range is -2^19~ (2^19-1);

(4) Why is MySQL so designed?

A: in varchar (M), varchar has the largest number of bytes in a table of 65535, and the actual length is related to the contents of the storage;

4 , asked the InnoDB how the transaction and log are implemented.

(1) How many kinds of logs are there?

Answer: 5 kinds, Binlog, query log, slow query, error log, relay log

(2) The form of log storage

Answer: Binlog, the relay log is binary;

The other three types are text forms;

(3) How the transaction is achieved through the log, the more deeply the better.

A: The InnoDB log needs to turn on explicit submission, which is turned off by default;

First understand the log process. Cache into Log_buffer, swipe in redo_log per second or every 10 seconds, and swipe into hard drive after commit

The transaction log will mark the line in the Innodb_buffer_pool page to update, delete, commit after the flash drive, no commit is not counted to the disk, is dirty data;

5 , asked the MySQL Binlog Several types of log entry formats and differences

(1) The meaning of various log formats

Answer: Three kinds of log format: statement,row,mixed

Each DML operation's SQL is counted into the statement log;

The SQL for each DML operation is recorded as an operation on each data, counted into the row log;

The mixed log is a mixture of the above two, and the specific record is determined by the isolation level +binlog_format together

(2) Applicable scenarios

A: 1, Statement, advantages: Do not need to record each row of changes, reduce the Binlog log volume, saving io, improve performance;

Cons: Because the records are just execution statements, in order for these statements to run correctly on slave, it is also necessary to record some relevant information of each statement at the time of execution, so as to ensure that all statements can be slave and executed at the master side of the same result;

Some specific function functions can cause replication problems, such as the Sleep () function, last_insert_id ();

Some functions cannot be counted into the replication log: Load_file ()

2, row mode, advantages: Do not record the execution of the SQL statement context-sensitive information, only need to record that one record was modified to what;

Disadvantage: Generate a large number of logs, a large number of logs cause IO overhead;

3, Mixed mode, general statement modification using statment format save Binlog,statement can not complete the operation of master-slave replication, the row format is saved binlog,mysql based on SQL to select the logging format, When the table structure changes, the statement mode will be recorded, update or delete and other changes to the data statement, or will record all the changes in the row;

(3) Combining the first problem, each log format in the copy of the pros and cons.

6, asked the next MySQL database CPU soared to 500% how he handled it?

A: (1) Multi-instance server, first top view is that a process, which end share population with more CPU;

(2) Show processeslist see if the load problem is caused by a large number of concurrent locks;

(3) Otherwise, look at the slow query, find out the execution time of the Sql;explain analysis SQL whether to go index, SQL optimization;

(4) To see if the cache fails, you need to see the buffer hit ratio;

7 , SQL optimized.

(1) Explain out of the meaning of the various item

A: Select_type: The type of query used mainly has the following types of queries:

DEPENDENT subquery: The first select of the inner layer of the subquery, dependent on the result set of the external query.

DEPENDENT Union: The Union in the subquery, and all subsequent select from the second select in the Union, also depend on the result set of the outer query.

PRIMARY: The outermost query in the subquery, note that it is not a primary key query.

Simple: A query other than a subquery or union.

Subquery: The first select of a subquery inner query, the result is not dependent on the external query result set.

Uncacheable subquery: The result set cannot be cached by a subquery.

All select after the second select starts in the Union:union statement, and the first select is primary.

The merge result in the UNION result:union.

Table: Displays the names of the tables in the database accessed by this step.

Type: tells us how to access the table, mainly containing the following set of types.

Const: Read constant, at most only one record match, because is a constant, actually only need to read once.

Eq_ref: There will be a maximum of one matching result, typically accessed through a primary key or unique key index.

Fulltext: Full-text index retrieval.

Index: Full index scan.

Index_merge: Two (or more) indexes are used in the query, then the index results are merged (merge), and then the table data is read.

Index_subquery: The returned result field combination in a subquery is an index (or combination of indexes), but not a primary key or unique index.

Rang: Index range scan.

Possible_keys: The index that the query can take advantage of. If no index is available, it is displayed as NULL, which is important for tuning the index.

Key:mysql Query Optimizer The index selected from the Possible_keys.

Key_len: The index key length that is selected to use the index.

REF: Lists whether a constant (const) or a field of a table (if it is a join) is filtered (via key).

Rows:mysql Query Optimizer The number of result set records that are estimated by the statistical information collected by the system.

Extra: The additional details that are implemented in each step of the query are mainly the following.

Note: http://www.cnblogs.com/hustcat/articles/1579244.html

(2) The significance of the profile and the use of the scene.

Profile is designed to lock down resources consumed at each step of the SQL execution process, and then targeted for optimization;

(3) Index problem in explain.

8, backup plan, mysqldump as well Xtranbackup the principle of implementation,

Answer: Mysqldump: Lock All the tables first, and then stitch each SQL in the table into an INSERT statement, one for a small section,

The backup structure is: Table structure +insert

Xtrabackup the backup of InnoDB and MyISAM engine tables;

MyISAM: Lock table for copy;

Innodb:xtrabackup backup InnoDB is the use of InnoDB crach_recovery function;

Crash_recovery is the transaction log, commit SQL into datafile, no commit rollback, this is applied at InnoDB startup;

The Xtrabackup is performed by three threads:

Thread 1,copy InnoDB page, copy1m,64 page per second, copy process page data is RW, using the InnoDB built-in table for copy;

Thread 2, monitoring the Copy Process page data is normal, normal copy, not normal then copy again, up to repeat 10 times;

Thread 3, monitoring logfile, changes are immediately copy away;

When copy is finished, record the location point;

Incremental backup: Check with the last backup, which pages have changed, compare the LSN of the last backup page with the current page LSN size, change the copy to go, after the end of the copy, the last position is recorded;

(1) Backup plan

Dump, backup every day, log backups every 15 minutes;

Xtranbackup: Backup Every three days, one incremental backup per day, one log backup every 15 minutes;

(2) Backup recovery time

This really did not read what the meaning of the question.

(3) How to handle backup recovery failure

A: Check the table is damaged, normal then re-backup recovery;

9 , - Desk DB to restart within the fastest time.

What is the biggest MySQL db problem you have encountered in your current job ?

One , InnoDB optimization of reading and writing parameters

(1) Read parameters, global buffer pool and local buffer

Innodb_buffer_pool_size, in theory, the bigger the better, the proposed server 50%~80%, the actual data size 80%~90% can be;

Innodb_read_io_thread, based on the number of processor cores;

Read_buffer_size;

Sort_buffer_size

(2) Write parameters

Insert_buffer_size;

Innodb_double_write;

Innodb_write_io_thread

Innodb_flush_method

(3) IO-related parameters

Innodb_log_buffer_size

Innodb_flush_log_at_trx_commit

Innodb_file_io_threads

innodb_max_dirty_pages_pct

(4) Caching parameters and the appropriate scenarios for caching

A , please describe briefly the following MySQL in InnoDB What are the four types of transaction isolation level names supported, and what are the differences between tiers?

Uncommitted read (uncommited read), commit read (commited read), repeat read (repeatable read), serial read (SERIALIZABLE)

These four isolation levels are improved by one, and the difference is in dirty reading, non-repetition reading, Phantom reading three points, and concurrency, the higher the isolation level, the worse the concurrency;

The so-called dirty read, that is, the same transaction, will read the uncommitted transaction modification data;

Non-repeating read, refers to in the same transaction, in the T1 moment, reading a row of data for A,t2 moment when reading the same row of data, because of other transactions update, this row of data has changed;

Phantom reading refers to the fact that in the same transaction, when the same query is performed multiple times, a new record is inserted due to the submission of other transactions, resulting in a different result of each query;

The difference is:

Unread read: Will cause dirty reading, non-repetition reading, phantom reading;

Submit read: Will not cause dirty reading, but there will be non-repetition read, Phantom read;

Repeat read: May cause phantom reading;

Serial read: Does not cause dirty reading, non-repetition reading, phantom reading;

- , there are large fields in the table X (for example: text type), and the field X do not update frequently, to read as the main, ask

(1) Do you choose to split into a sub-table, or keep it together?

A) in the sub-table

(2) Write down your reasons for such a choice?

A) Avoid large data being frequently swapped in and out of buffer, affecting the cache of other data;

- , MySQL in InnoDB what is the engine's row lock done (or implemented) by adding? Why is that so?  

The row lock of the InnoDB is added to the index implementation;

The reason is: InnoDB is to put primary key index and related row data in the B + Tree leaf node; InnoDB There will be a primary key,secondary index lookup, also by finding the corresponding primary, Then find the corresponding data row;

Interview questions for MySQL