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
Multi-Source replication Multi-source replication
Parallel replication of tables
Galera Cluster cluster
Spider Horizontal Shard
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?
Transaction support is different (InnoDB support transactions, MyISAM does not support transactions)
Lock granularity (InnoDB row lock application, MyISAM table Lock)
Storage space (InnoDB both cache index files and cache data files, MyISAM can only cache index files)
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:
Data structure angle can be divided into: B+tree index, hash Index, FULLTEXT index (INNODB,MYISAM support)
Storage angle can be divided into: Clustered index, nonclustered index
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?
Master-Slave delay monitoring can be pt-heartbeat through commands in the Swiss Army knife Percona-toolkit in the third-party tool industry.
Traditional methods, by comparing the difference values of the position number between the master and slave servers.
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?
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.
Pt-online-schema-change for online operation via the commands in the Swiss Army knife Percona-toolkit in the third-party tool industry
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.
Regression to the design level of the table, the data type selection is reasonable
Whether the large table fragments are well-organized
The statistics of the table are not accurate
Review the execution plan of the table and determine if there is an appropriate index on the field
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?
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
Find the location of the bottleneck point
Make the optimization plan and form the system to deal with the problem
After the system is set up, test the optimization plan in the test environment.
Test environment if the optimization effect is good, then the implementation to the production environment
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?
J-4
MHA
MM keepalived
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
MySQL version of the upgrade
Handles various pits and problems in MySQL cluster
According to the company's business type, design reasonable MySQL library, table, architecture.
Regular disaster recovery drills
Recover data after accidental deletion of data
Database Interview Sunflower Treasure