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

You can call me Lao Zhang, screen name superZS! Has been engaged in the database industry for more than 10 years, working in a database service company, and a senior database lecturer, on the more confused database problems encountered in the interview, and the students who have just entered the database field, I would like to give you a detailed summary here. I hope it will be helpful to you in your work or interview. Teachers will teach each other with all their money, and we will learn from each other. Let's work hard to learn skills and strive to get a high salary and catch up with the girl we love.

Treasure Book of Sunflower

Question 1:

What is the version of mysql you are currently working with? In addition to the official version, have you been exposed to other branch versions of mysql?

The reason for the branching

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

Three popular MySQL branches: Drizzle, MariaDB and Percona Server (including XtraDB engine)

MariaDB is not only a substitute for mysql, but also to innovate and improve mysql's own technology.

Introduction of new features

Multi-source replication multi-source replication

Parallel replication of tables

Galera cluster cluster

Spider horizontal slicing

TokuDB storage engine

XtraDB is an enhanced version of the innodb storage engine that can be used to better leverage the latest computer hardware system performance, as well as new features in high-performance mode. It is backward compatible, because it is built on innodb, so it has more metrics and extension capabilities. And it in the cpu multi-core conditions, can better use memory, when the database performance mentioned higher!

Drizzle and mysql are quite different and not compatible, so if you want to run this environment, you need to rewrite some code!

Question 2:

What is the difference between myisam and innodb, mysql's main storage engines?

Transaction support is different (innodb supports transactions, myisam does not)

Lock granularity (innodb row lock application, myisam table lock)

Storage space (innodb caches both index files and data files, myisam can only cache index files)

Storage structure

(myisam: the data file has the .MYD myData extension, and the index file has the .MYI myIndex extension)

(innodb: .Ibd) all tables are saved in the same data file.

5. Count the number of record rows

(myisam: the total number of rows that hold the table, which will be fetched directly by select count (*) from table;)

(innodb: if the total number of rows of the table is not saved, select count (*) from table; will traverse the entire table, consuming a lot.)

Question 3:

A brief introduction to the architecture of Innodb?

When talking about the architecture of innodb, we should first consider the architecture of mysql, which is divided into two parts: the server layer of mysql and the storage engine layer.

First talk to the interviewer about the overall direction of mysql, and then talk about the innodb architecture.

It is recommended to introduce the innodb architecture from three aspects: memory-thread-disk

Contains insert_buffer,data_buffer,index_buffer,redo_log_buffer,double_write in memory

Memory flushing to disk mechanism, redo, dirty pages, binlog refresh conditions

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

Data files are stored on the disk, redo log,undo log,binlog

Question 4:

What index types does mysql have:

From the perspective of data structure, it can be divided into B+tree index, hash index and fulltext index (all supported by innodb,myisam).

From the storage point of view, it can be divided into clustered index and nonclustered index.

Logically, it can be divided into: primary key,normal key, single column, compound, overlay index.

Question 5:

There are several formats for mysql binlog:

1. Statement

Advantages: no need to record the changes of each line, reduce the number of binlog logs, save IO and improve performance

Disadvantages: it is easy to lose data when using some special functions or when operating across libraries

Note: use is not recommended in production

2. Row

Advantages: clearly record the data information of each row, so that the data will not be lost across the database.

Disadvantages: when the content is recorded in the log, it will be recorded as each line of changes, but it will generate a lot of binlog, and it will cost a lot of money to the network.

Note: recommended in production

3. Mixed

In the case of mysql5.1, a transitional version, the DDL statement will be recorded as statement,DML will record row.

Note: use is not recommended 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, the slave server initiates the binlog request to the master database through io thread, the master server passes the binary log to the slave database through IO dump thread, and the slave database records it to its own relay log through io thread. Then apply the contents of the sql in the relay log through sql thread.

Qusetion 7:

What is the double one in the database?

Sync_binlog=1

Innodb_flush_log_at_trx_commit=1

Innodb_flush_log_at_trx_commit and sync_binlog are the key parameters that control MySQL disk writing policy and data security.

Innodb_flush_log_at_trx_commit is set to 1, and every time a transaction commits, MySQL writes log buffer's data to log file and flushes it to disk.

Sync_binlog = N (N > 0), MySQL uses the fdatasync () function to synchronize its write binary log binary log to disk every time it writes N binary log binary log

Qusetion 8:

How do I monitor mysql replication replication latency?

Pt-heartbeat can monitor master-slave delay through commands in Switzerland × × percona-toolkit in the third-party tools industry.

The traditional method is by comparing the difference values of position numbers between master and slave servers.

You can also estimate the master-slave delay time by viewing seconds_behind_master.

Qusetion 9:

How can large table DDL statements be implemented to minimize performance impact?

You can import and export data through the traditional method, create a new table structure like the original table, execute the ddl statements that need to be executed in the new table with no data, then import the data from the old table into the new table, and change the new table to the name of the old table.

Pt-online-schema-change operates online through commands from Swiss × × percona-toolkit in the third-party tool industry.

For the new version of mysql, you can directly online ddl online.

Qusetion 10:

Why set the self-increment column as the primary key for the innodb table?

1. Using the self-increasing column as the primary key, the writing order is self-increasing, which is consistent with the splitting order of B+ leaf nodes.

two。 If the table does not specify a self-incrementing column as the primary key, and there is no unique index that can be selected as the primary key, InnoDB will choose the built-in rowid as the primary key, and the write order is the same as the rowid growth order

Therefore, if the data writing order of the InnoDB table is the same as that of the leaf nodes of the B+ tree index, the access efficiency is the highest.

Qusetion 11:

How to optimize a problematic sql statement?

For the optimization of sql statements, let's not just answer to add indexes, which is too unprofessional. We can analyze it from the following angles

Return to the design level of the table, whether the choice of data type is reasonable

Is the arrangement of large table fragments perfect?

Is the statistical information of the table accurate?

Review the execution plan of the table to determine if there is a suitable index on the field

In view of the selectivity of the index, establish an appropriate index (which also involves the operation of the large table DDL)

Qusetion 12:

The server load is too high or the web page is slow to open, briefly tell me your optimization ideas?

First of all, we have to find the process of the problem, through the operating system, database, programming, hardware perspective four dimensions to find the problem.

Find the location of the bottleneck.

Make a good optimization plan and form a system to deal with the problem

After the system is established, the optimization scheme is tested in the test environment.

If the optimization effect of the test environment is good, it will be implemented in the production environment.

Keep a record of dealing with problems

Qusetion 13:

What mainstream mysql architectures have you come into contact with? What are the issues that need to be considered in architectural applications?

Mmurs

MHA

MM keepalived

PXC

Common problems: the existence of master-slave delay, downtime in the master database, and data consistency should be considered in the switching process to avoid master-slave replication inconsistency.

Qusetion14:

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

Deadlock refers to a vicious circle when two or more transactions occupy each other on the same resource and request a lock. A deadlock occurs when multiple transactions try to lock the same resource in a different order.

Lock waiting: lock waiting occurs when different session update peer data in mysql database

Important monitoring table of three locks innodb_trx,innodb_locks,innodb_lock_waits

Qusetion 15:

Which cases have you dealt with in mysql?

We can simply talk to him about mysql architecture, data backup and recovery, optimization, and high availability cluster architecture from the four knowledge modules of mysql.

Upgrade of mysql version

Deal with various pits and problems of mysql cluster

Design reasonable mysql library, table and architecture according to the company's business type.

Conduct disaster recovery drills regularly

After mistakenly deleting data, restore the data

Simply talk about it from these directions first, and then analyze each problem.

Of course, there will be some personnel questions, such as why you chose our company, what do you think is your advantage? What is your expected salary? These questions are very simple. As long as we pass the technical interview, these are not any questions!

In the future, we will gradually carry out a specific analysis of a problem, and detailed steps to deal with the method! I hope you will continue to pay attention to the later creation.

_ _ _

With a small advertisement, Lao Zhang recently opened a video course. I hope you will support me a lot. Like my article, it will certainly give you a lot of harvest!

In-depth Analysis of MySQL Architecture and practical DBA Video course

Course introduction:

Through in-depth analysis and explanation of MySQL architecture, together with production environment backup and recovery, master-slave replication, highly available cluster architecture and optimization, students can have a shallow-to-deep understanding of MySQL database. The last part of the course will also explain the summary of the examination questions, which is helpful for students to find an ideal job in MySQL DBA.

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.