Database Interview Sunflower Treasure

What is the MySQL version you are currently contacting? In addition to the official version, have you contacted other MySQL branch versions?

Causes of branching

Many developers think it is necessary to split it into other projects, and each branch project has its own expertise. This demand and Oracle's concern about the slow growth of core products has led to the emergence of many sub-projects and branches of interest to developers

Three popular MySQL branches: drizzle, mariadb, and Percona Server (including XTRADB engine)

MariaDB is not just a replacement for MySQL, it is primarily about innovating and improving MySQL's own technology.

Introduction to New features

1. Multi-Source replication Multi-source replication

2. Parallel replication of tables

3. Galera Cluster cluster

4. Spider Horizontal Shard

5. TOKUDB Storage Engine

XtraDB is an enhanced version of the InnoDB storage engine that can be used to better perform the latest computer hardware system performance, and also includes new features in high-performance mode. It can be backwards compatible because it is built on a innodb basis, so he has more indicators and extended functionality. And it can better use the memory under the CPU multi-core condition, when the database performance mentions higher!

The difference between drizzle and MySQL is larger, and not compatible, if you want to run this environment, you need to rewrite some code!

Question 2:

What is the difference between MySQL's main storage engine MyISAM and InnoDB?

1. Transaction support is different (InnoDB support transactions, MyISAM does not support transactions)

2. Lock granularity (InnoDB row lock application, MyISAM table Lock)

3. Storage space (InnoDB both cache index files and cache data files, MyISAM can only cache index files)

4. Storage structure

   (MyISAM: Data file has an extension of. MYD MyData, the extension of the index file is. MYI Myindex)

(InnoDB: All tables are stored in the same data file.) IBD)

5. Number of statistical record lines

(MyISAM: The total number of rows saved with a table, select COUNT (*) from table; The value is directly removed)

(InnoDB: The total number of rows in the table is not saved, select COUNT (*) from table; it will traverse the entire table, consuming considerable amount)

Question 3:

A brief introduction to InnoDB's architecture?

Talking about the architecture of InnoDB, the first thing to consider is the MySQL architecture, which is divided into two parts MySQL server layer and storage engine layer

First, talk to the interviewer about the overall direction of MySQL, and then go into the InnoDB architecture

It is recommended to introduce the INNODB architecture in three ways: memory----Thread-----disk

The memory contains Insert_buffer,data_buffer,index_buffer,redo_log_buffer,double_write

Memory flush to disk mechanism, redo, dirty page, binlog refresh condition

The role of various threads, Master_thread,purge_thread,redo log thread,read thread,write thread,page cleaner Thread

The data files are stored on the disk, redo Log,undo log,binlog

Question 4:

What types of indexes are available for MySQL:

1. Data structure angle can be divided into: B+tree index, hash Index, FULLTEXT index (INNODB,MYISAM support)

2. Storage angle can be divided into: Clustered index, nonclustered index

3. Logical angle can be divided into: primary Key,normal key, single row, composite, overlay index

Question 5:

MySQL Binlog has several formats:

1. Statement

Pros: No need to record changes in each row, reduce the Binlog log volume, save IO, improve performance

Cons: Easy to lose data when using some special functions or cross-Library operations

Note: Not recommended for use in production

2. Row

Pros: Clearly record the data information for each row, without the case of cross-library data loss

Cons: Content when recorded in the log, will be recorded in each row of changes to record, but will generate a lot of binlog, for network overhead is also larger

Note: Recommended use in production

3. Mixed

When the mysql5.1 is a transition version, the DDL statements are recorded as STATEMENT,DML and the row is logged.

Note: It is not recommended to use in production

Qusetion 6:

What is the specific principle of MySQL master-slave replication?

The master server records the data update to the binary log, from the server through the IO thread to the main library to initiate Binlog requests, the master server through the IO dump thread to pass the binary log to the slave library, from the library through the IO thread record to its own trunk log. The contents of the SQL in the relay log are then applied via SQL thread.

Qusetion 7:

What are the two in a database?

Sync_binlog=1

Innodb_flush_log_at_trx_commit=1

Innodb_flush_log_at_trx_commit and Sync_binlog two parameters are key parameters to control MySQL disk write policy and data security

Innodb_flush_log_at_trx_commit is set to 1, and each time the transaction commits, MySQL writes the data of log buffer to log file and brushes it to disk.

Sync_binlog =n (n>0), MySQL uses the Fdatasync () function to synchronize its write binary log binary logs to disk when each write N binary log binary logs

Qusetion 8:

How do I monitor MySQL replication replication latency?

1. Master-Slave delay monitoring can be pt-heartbeat through commands in the Swiss Army knife Percona-toolkit in the third-party tool industry.

2. Traditional methods, by comparing the difference values of the position number between the master and slave servers.

3. You can also estimate the master-slave delay time by looking at the Seconds_behind_master

Qusetion 9:

Large table DDL statement, how to implement, in order to minimize the performance impact?

1. You can import and export data through traditional methods, create a new table structure like the original table, execute the DDL statements that need to be executed in a new table without data, and then import the data from the cousin into the new table, and change the new table to cousin's name.

2. Pt-online-schema-change for online operation via the commands in the Swiss Army knife Percona-toolkit in the third-party tool industry

3. For new versions of MySQL (5.7) online DDL can be directly

Qusetion 10:

Why should I set the self-increment key for the InnoDB table?

1. Use the self-increment key, the write order is self-increment, and the B + number leaf node splitting sequence consistent

2. The table does not specify a self-increment key, and there is no unique index that can be selected as the primary key, InnoDB selects the built-in ROWID as the primary key, and the write order is consistent with the ROWID growth order

So the data write order of the InnoDB table can be the same as the leaf node order of the B + Tree index, when the access efficiency is the highest

Qusetion 11:

How do I optimize a problematic SQL statement?

For the optimization of SQL statements, we do not come up to the answer to add an index, so it seems too unprofessional. We can analyze it from the following angles.

1. Regression to the design level of the table, the data type selection is reasonable

2. Whether the large table fragments are well-organized

3. The statistics of the table are not accurate

4. Review the execution plan of the table and determine if there is an appropriate index on the field

5. Establish appropriate indexes for the selectivity of the index (it also involves the operation of the large table DDL)

Qusetion 12:

Server load is too high or Web page open slowly, simply say your optimization idea?

1. First we need to find the process of the problem, through the operating system, database, program design, hardware angle four dimensions to find the problem

2. Find the location of the bottleneck point

3. Make the optimization plan and form the system to deal with the problem

4. After the system is set up, test the optimization plan in the test environment.

5. Test environment if the optimization effect is good, then the implementation to the production environment

6. Make a good record of handling problems

Qusetion 13:

What MySQL mainstream architecture has been exposed to? What are the issues that need to be considered in the architecture application?

1. J-4

2. MHA

3. MM keepalived

4. PXC

Common problems: The existence of master-slave delay problem, in the main library outage, switching process to consider the problem of data consistency, to avoid the occurrence of master-slave replication inconsistencies

QUSETION14:

What is a deadlock? Lock wait? How to optimize this kind of problem? What tables can be monitored through a database?

A deadlock is a vicious cycle that occurs when two or more transactions occupy each other on the same resource and request locking. Deadlocks occur when multiple transactions attempt to lock the same resource in different order.

Lock wait: MySQL database, different session in the update peer data, there will be lock waiting

Important three-lock monitor table innodb_trx,innodb_locks,innodb_lock_waits

Qusetion 15:

What cases of MySQL have been processed

We can simply chat with him from the MySQL four knowledge module MySQL architecture, data backup recovery, optimization, highly available cluster architecture

1. MySQL version of the upgrade

2. Handles various pits and problems in MySQL cluster

3. According to the company's business type, design reasonable MySQL library, table, architecture.

4. Regular disaster recovery drills

5. Recover data after accidental deletion of data

Database Interview Sunflower Treasure