1207MySQL face question

Transfer from http://blog.itpub.net/26435490/viewspace-1133659/

1, the replication principle and process of MySQL.

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

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

(3) How to resolve the replication bug encountered by various jobs

3, ask the difference between varchar and char in MySQL and the meaning of the 30 representation in varchar (50).

(1) The difference between varchar and char

Char is a fixed-length type, and varchar is a variable-length type

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

Up to 50 bytes of storage

(3) Meaning of 20 in int (20)

m indicates the maximum display width in int (m) for integer types. The maximum legal display width is 255.

(4) Why is MySQL so designed?

4, asked the InnoDB of the transaction and the way the log is implemented.

(1) How many kinds of logs are there?

Error log: Log error messages, also record some warning messages or correct information

Slow query log: Sets a threshold that records all SQL statements that run longer than this value to the log file for slow queries.

Binary log: Records all actions that make changes to the database

Query log: Records all information about database requests, whether or not they are executed correctly.

(2) The form of log storage

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

Isolation: Implemented by lock

Atomicity, consistency, and persistence are accomplished through redo and undo.

5, asked the MySQL binlog several log entry format and the difference

(1) The meaning of various log formats

(2) Applicable scenarios

(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?

(1) No experience, can not ask

(2) Experienced, ask them to deal with ideas

7,sql optimization.

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

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

(3) Index problem in explain.

8, backup plan, mysqldump and xtranbackup implementation principle,

(1) Backup plan

(2) Backup recovery time

(3) How to handle backup recovery failure

9, 500 db, restart within the fastest time.


10, in your current job, what is the biggest MySQL db problem you have encountered?

InnoDB optimization of reading and writing parameters

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

(2) Write parameters

(3) IO-related parameters

(4) Caching parameters and the appropriate scenarios for caching

12, please briefly describe the four types of transaction isolation level names supported by InnoDB in MySQL, and the difference between the levels?

The four isolation levels defined by the SQL standard are:

Read uncommited

Read Committed

REPEATABLE READ

Serializable

READ UNCOMMITTED (Read UNCOMMITTED content)

At this isolation level, all transactions can see the execution results of other uncommitted transactions. This isolation level is rarely used in real-world applications because it has no better performance than other levels. Reading uncommitted data is also known as Dirty reading (Dirty read).

Read Committed (read submit content)

This is the default isolation level for most database systems (but not MySQL default). It satisfies the simple definition of isolation: A transaction can only see changes that have been submitted to the firm. This isolation level also supports so-called non-repeatable reads (nonrepeatable read), because other instances of the same transaction may have new commits during the instance processing, so the same select may return different results.

Repeatable Read (can be reread)

This is the default transaction isolation level for MySQL, which ensures that multiple instances of the same transaction will see the same rows of data while concurrently reading the data. In theory, however, this can lead to another tricky problem: Phantom Reading (Phantom read). To put it simply, Phantom reads when a user reads a range of data rows, another transaction inserts a new row within that range, and when the user reads the data row of that range, a new phantom row is found. The InnoDB and Falcon storage engines address this issue through a multi-version concurrency control (mvcc,multiversion Concurrency control gap Lock) mechanism. Note: In fact, multi-version only solves the non-repeatable reading problem, and the gap lock (which is what it calls concurrency control) solves the Phantom reading problem.

Serializable (Serializable)

This is the highest isolation level, which solves the Phantom reading problem by forcing transactions to sort, making it impossible to conflict with one another. In short, it is a shared lock on every data row read. At this level, a large number of timeouts and lock competitions can result.

For different transactions, different isolation levels are used to separate the results. Different isolation levels have different phenomena. There are 3 main types now:

1. Dirty reads (dirty read): One transaction can read another modified data that has not yet committed a transaction.

2, non-repeating read (nonrepeatable read): In the same transaction, the same query reads a row at T1 time, the data in this row is modified, it may be updated (update), or deleted (delete) when the line is re-read at T2 time.

3, Phantom Read (Phantom Read): In the same transaction, the same query is repeated multiple times, due to the other insert operation (insert) of the transaction commits, resulting in a different result set is returned each time.

Different isolation levels have different phenomena, and there are different locking/concurrency mechanisms, the higher the isolation level, the worse the concurrency of the database, the 4 kinds of transaction isolation levels are shown in the following table:

Isolation level	Dirty Read	Non-REPEATABLE READ	Phantom reading
READ UNCOMMITTED	Allow	Allow	Allow
Read Committed		Allow	Allow
REPEATABLE READ			Allow
Serializable

13, the table has a large field x (for example: Text type), and the field X will not be updated frequently, to read as the main, ask

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

(2) Write down your reasons for such a choice?
1 Improve retrieval efficiency

What is the row lock of the InnoDB engine in 14,mysql done (or implemented) by adding? Why is that so?
Multiple versioning via row

The difference between MyISAM and InnoDB

(1) Ask 5 different points

MyISAM Innodb

Things support: Support Not supported

Size of Lock: Table Row

Storage capacity: No upper limit 64TB

Hash index: Support not supported

Full-Text indexing: Support Not supported

FOREIGN key: Support Not supported

(2) Ask for improvements to 2 of different MySQL versions
(3) How to implement the index of 2

+ B-Tree

http://blog.csdn.net/ggxxkkll/article/details/7551766

I understand.

Acid of the transaction
The atomicity of a
C Conformance
I isolation
D Persistence

Three Paradigms of database
1 The first paradigm is a column with no duplicates
2 You typically need to add a column to the table to store unique identities for each instance
3 The third paradigm is that attributes do not depend on other non-primary properties. (My understanding is to eliminate redundancy)

How databases are optimized
Design optimization
Query optimization where sentence
Index optimization
Hardware optimization

How the transaction is implemented through the log, the more deeply the better.??
Isolation: Implemented by lock
Atomicity, consistency, and persistence are accomplished through redo and undo.

Three modes of understanding Bin-log-format
Mix ROW Statement

Each field of the explain
EXPLAIN SELECT 1 from DUAL;
Each field of the explain
Id[select order] key[use which index] ps_key key_length TABLE type[get data type] ref rows[scan rows] Extra

Backup plan, mysqldump and xtranbackup implementation principle???
Includes file system snapshot (LVM), logical Backup tool mysqldump,mydumper, and physical Backup tool xtrabackup
Http://www.cnblogs.com/zengkefu/p/5669512.html

Please briefly describe the four types of transaction isolation level names supported by InnoDB in MySQL, and the difference between levels.??
Non-REPEATABLE READ
REPEATABLE READ
Sequential read

What is the line lock of the InnoDB engine in MySQL done (or implemented) by adding? Why is that so?
That's MVCC.

The difference between MYISAM and InnoDB
Transaction
FOREIGN key
Whether the data file and index file are separate

1207MySQL face question