Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "what are the mysql-related interview questions". In the actual case operation process, many people will encounter such difficulties. Next, let Xiaobian lead you to learn how to deal with these situations! I hope you can read carefully and learn something!

1. What is the difference between MYISAM and INNODB?

A: There are mainly the following differences:

a) Structural differences

MyISAM is stored on disk as three files, where.frm stores table definitions;.MYD (MYData) is a data file; and.MYI (MYIndex) is an index file.

innodb consists of.frm files, tablespaces (separate tablespaces or shared tablespaces), and log files (redo logs).

b) Differences in transactions

Myisam does not support transactions; innodb does.

c) The difference between locks

Myisam uses table locks; innodb uses row locks (innodb also supports table locks).

Table-level lock: directly locks the entire table. During the locking period, other processes cannot write to the table. If the write lock is set, then other processes are not allowed to read. Therefore, myisam supports low concurrency, but myisam will not deadlock.

Row-level locking: locks only specified rows, while other processes can operate on other rows in the table. Thus row locks can greatly reduce conflicts in database operations, but sometimes lead to deadlocks.

d) Whether foreign key differentiation is supported

myisam does not support foreign keys, innodb does

e) select count(*)

MyISAM is much faster than InnoDB for count(*) without where. Because MyISAM has a built-in counter, count(*) reads directly from the counter, whereas InnoDB must scan the entire table.

f)myisam loads only indexes into memory, while innodb loads both data and indexes into memory.

2. How many mysql libraries are there in the company's existing database architecture?

A: Our company now has two sets of MySQL. One of them is a production library and the other is a test library.

Both production and test libraries are highly available using mha + semisynchronous replication.

All of our project web frontend volumes (about 10 projects) point to an instance of mysql on a machine. Because we are a traditional industry, concurrent access is not very large, so at present our production mysql database has no performance issues.

3. How to improve insert performance?

A: There are the following methods:

insert into t values(a,b,c), (d,e,f) ,

Cause analysis: The main reason is that the log volume after multiple inserts are merged (MySQL binlog and innodb transaction log) is reduced, reducing the data volume and frequency of log disk brushing, thus improving efficiency. By merging SQL statements, you can also reduce the number of SQL statements parsed and reduce IO transmitted over the network.

b) Modify the parameter bulk_insert_buffer_size to adjust the cache for bulk insertion;

c) Set innodb_flush_log_at_trx_commit = 0, which can significantly improve the import speed compared to innodb_flush_log_at_trx_commit = 1;

(Note: The innodb_flush_log_at_trx_commit parameter has a critical impact on InnoDB Log write performance.) This parameter can be set to 0, 1, 2, explained as follows:

0: The data in log buffer will be written to log file once per second, and file system to disk synchronization will occur at the same time, but the commit of each transaction will not trigger any log buffer to log file flush or file system to disk flush operation;

1: Write the data in the log buffer to the log file every time the transaction is committed, and also trigger the synchronization of the file system to the disk;

2: Transaction commit triggers log buffer to log file flush, but does not trigger disk file system to disk synchronization. In addition, file system to disk synchronization occurs once per second.

）

d) Manual use of transactions

Because mysql is autocommit by default, so that every time a piece of data is inserted, it will be committed once; therefore, in order to reduce the consumption of creating transactions, we can use transactions manually, i.e. START TRANSACTION;insert., insert。。commit; i.e. execute multiple inserts and then submit them together; generally 1000 inserts are submitted once.

4. Related to the previous problem, if there is a problem with the performance of dml statements such as insert, or the existence of other problems, it may cause synchronization delay, so how to effectively avoid synchronization delay?

A: The main reason for MySQL master-slave synchronization delay is that the master library is multithreaded, while the slave library has only one thread (slave_sql_running) to synchronize, so if there is a ddl or dml operation in the master library that takes 10 minutes, then this operation also takes 10 minutes to execute on the slave library. One might ask,"Why does slave delay when the same DDL and DML on the master library also need to execute 10 minutes?" The answer is master can be concurrent, Slave_SQL_Running threads cannot.

Therefore, in order to reduce the delay from the library, we need to do the following maintenance at ordinary times:

a) Try to make the dml or ddl of the main library execute quickly, such as improving the efficiency of insert (see above for the method);

b) For security reasons, some people may set sync_binlog of the master library to 1, innodb_flush_log_at_trx_commit to 1, etc., while slave does not need such high data security. You can set sync_binlog to 0 or close binlog. innodb_flush log can also be set to 0 to improve the execution efficiency of sql from the slave library.

(Note: sync_binlog controls how often binlog_cache flushes binlog to disk, and innodb_flush_log_at_trx_commit controls how often redo log buffer flushes redolog to disk.) sync_binlog=0, which means MySQL does not control the refresh of binlog, and the file system itself controls the refresh of its cache. If sync_binlog>0, it means that every sync_binlog transaction commits, MySQL calls the file system flush operation to flush the cache. The safest is sync_binlog=1, which means that MySQL will flush binlog every time a transaction is committed. In this case, in the case of a host operating system corruption or sudden power loss, the system may lose 1 transaction of data. So sync_binlog=1 keeps the data safe, but the performance is the worst.)

c) Use better hardware devices than the master library as slaves

d) Use mysql 5.6 new parameter slave_parallel_workers to make the slave library multithreaded. However, slave_parallel_workers can only support concurrent replication among multiple databases under one instance, and cannot truly achieve concurrent replication of multiple tables. Therefore, when the concurrent load is large, the slave still has no way to catch up with the master in time, and needs to find ways to optimize it.

e) Upgrade Mysql to 5.7, because mysql 5.7 supports true multithreading from the library, that is, how many threads are in the main library and how many threads are in the slave library. Mysql 5.7 claims that master-slave replication never loses data (there has been no time to try it out).

5. Do you know GTID?

A: I used GTID. One of the Civil Aviation Administration's projects used to be GTID.

GTID is new to mysql 5.6, replacing binlog location numbers with transaction commit numbers. However, GTID this thing in 5.6 or there are many limitations, personal do not recommend using.

The full name of GTID is global transaction identifier, which can be translated as global transaction identifier.

GTID consists of two parts: GTID = source_id:transaction_id

source_id is used to identify the source server, represented by server_uuid. This value is generated on the first startup and written to the configuration file data/auto.cnf.

Transaction_id is determined by the transaction number committed on the origin server.

6. Innodb is a row lock, when will it generate a row lock, and under what circumstances will it become a table lock?

Answer: Under normal circumstances, innodb only locks the specified rows, and other processes can still operate on other rows in the table. Therefore, innodb adds row locks at this time.

However, if MySQL cannot determine the scope to scan when executing a SQL statement, InnoDB also locks the entire table, e.g. update table set num=1 where name like "%aaa%".

7. Have you ever used a database from another branch? percona,mariadb, etc. What do you know about percona's pxc cluster?

A: In addition to Oracle MySQL, I have also used percona server. Percona is based on the original mysql, optimized and improved, so percona's performance is better than mysql. At present, I know that percona provides free thread pool function, while the community version of mysql does not have thread pool function (of course, the enterprise version of mysql has thread pool, but it needs to be charged); in addition, percona also supports NUMA and other functions.

I am familiar with pxc, I have built pxc in the test environment, but did not use it in production, because there are not many enterprises using pxc at present, I know sohu is using pxc at present.

pxc is to abandon the concept of mysql master-slave, that is, for pxc, each node can read and write, and write a piece of data, other nodes will have it at the same time, this is a synchronous replication scheme (different from Mysql master-slave asynchronous replication)

8. Do you know any other databases besides mysql? Oracle, redis, mongodb, etc.

A: besides mysql, I am familiar with oracle and have two years of experience with oracle.

However, I have no contact with redis and mongodb, if the work needs, I will learn them.

9. The biggest problems at work and the best work done?

Answer: Free play

10. Is the sub-library sub-table used, how to achieve it?

A: At present, according to our business volume, we haven't used sub-warehouse and sub-table. However, I have been paying attention to MySQL's distributed solution. In the past, mysql distributed more commonly used methods were to use Alibaba's cobar to split a table horizontally into multiple parts and put them into different libraries to achieve horizontal splitting of tables, or to put different tables into different libraries. However, it was later discovered that there was a problem with cobar that could not be solved well. So far, I've noticed a lot of people replacing cobar with mycat.

11. What parameters need to be adjusted for a newly created database?

A: Adjust two parameters, namely, adjust the operating system and database my.cnf.

a) Operating system parameters

Linux parameter system default parameters are very conservative, so need to increase some parameters according to server performance, such as I will adjust nofile (maximum number of file handles) and nproc (maximum number of threads), to maximize them; I will set vm. swap to 0, which means to maximize the use of physical memory, then swap space; I will set net.ipv4.tcp_tw_reuse to 1, which means to reuse the TIME-WAIT state sockets appearing in netstat to new TCP connections... wait

b) Database parameters

For mysql, the parameter adjustment of my.cnf is very important. If the default value is used, it is difficult to play mysql performance. Generally, I pay special attention to innodb_buffer_pool, which is generally set to 70% of physical memory, so that mysql tables and indexes can be loaded into memory to the maximum extent, so that mysql database performance can be greatly improved; In addition, I also pay special attention to sync_binlog and innodb_flush_log_at_trx_commit, which are set to the specific meaning; and max_user_connections, which I generally set to 2000; There is also innodb_lock_wait_timeout, depending on whether the program is a long connection or a short connection, generally I will set it to 60 seconds; there is also innodb_log_file_size, which is also set to a larger value, I generally set it to 500M or 1G.

12. How do you manage mysql permissions?

Answer: Only insert,update, select and delete permissions can be given. Sometimes delete is not given.

13. Is there a foundation for development?

A: No.

14. If CPU or IO pressure is high, how to locate the problem?

Answer:

First, I will use the top command and iostat command to locate what process is occupying CPU and disk io;

2. If it is a mysql problem, I will log in to the database and use the show full processlist command to see what sql statements the database is executing now, and whether there are statements that are executed for a long time to make the database stuck;

3. Execute the show innodb engine status command to see if there is contention for lock resources in the database.

4. Check mysql slow query log to see if there is slow sql;

5. Find statements that cause high database resource consumption, optimize them, build indexes for indexes that should be built, delete indexes for inappropriate indexes, or kill SQL statements that consume resources according to the situation.

"What are the mysql-related interview questions?" The content is introduced here. Thank you for reading. If you want to know more about industry-related knowledge, you can pay attention to the website. Xiaobian will output more high-quality practical articles for everyone!

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.