Today I will share with you 21 MySQL interview questions.
1. What kinds of locks are there in Mysql?
MyISAM supports table locks, InnoDB supports table locks and row locks, and the default is row locks.
Table-level lock: small overhead, fast locking, no deadlock occurs. The locking granularity is large, the probability of lock conflict is the highest, and the concurrency is the lowest.
Row-level lock: high overhead, slow locking, deadlock will occur. The lock force is small, the probability of lock conflict is small, and the concurrency is the highest.
2. Does Mysql support transactions?
In default mode, MYSQL is in autocommit mode, and all database update operations are submitted instantly, so by default, mysql does not support transactions.
But if your MYSQL table type is using InnoDB Tables or BDB tables, your MYSQL can use transaction processing, and using SET AUTOCOMMIT=0 can enable MYSQL to allow in non-autocommit mode. In non-autocommit mode, you must use COMMIT to submit your changes, or use ROLLBACK to roll back your changes.
Examples are as follows:
START TRANSACTION; SELECT @A:=SUM(salary) FROM table1 WHERE type=1; UPDATE table2 SET summmary=@A WHERE type=1; COMMIT;
3. Are Mysql queries case sensitive?
Indistinguishable.
SELECT VERSION(), CURRENT_DATE; SeLect version(), current_date; seleCt vErSiOn(), current_DATE;
These examples are the same, Mysql is case-insensitive.
4. When the column is set to AUTO INCREMENT, what happens if the maximum value is reached in the table?
A: It will stop incrementing and any further insertion will produce an error because the key has been used.
5. A table contains the primary key of the ID. After inserting 17 records, the 15, 16, 17 records are deleted, and then restart Mysql and insert a record. Is the ID of this record 18 or 15?
If the table type is MyISAM, then it is 18. Because the MyISAM table will record the maximum ID of the autoincrement primary key to the data file, the maximum ID of the autoincrement primary key after restarting MySQL will not be lost.
If the table type is InnoDB, then it is 15. The InnoDB table only records the maximum ID of the autoincrement primary key into memory, so restarting the database or performing OPTIMIZE operations on the table will result in the maximum ID loss.
6. What are the three database paradigms?
The first normal equation (1NF): The field is atomic and cannot be further divided. (All relational database systems satisfy that the fields in the first normal format database table are single attributes and cannot be further divided)
The second normal form (2NF) is established on the basis of the first normal form (1NF), that is, to satisfy the second normal form (2NF) that must first satisfy the first normal form (1NF). It is required that each instance or row in the database table must be uniquely distinguished. It is usually necessary to add a column to the table to store the unique identity of each instance. This unique attribute column is called the primary keyword or primary key.
To satisfy the third normal form (3NF) must first satisfy the second normal form (2NF). In short, the third paradigm (3NF) requires that a database table does not contain non-primary keyword information that has been included in other tables. >So the third normal form has the following characteristics: >>1. There is only one value for each column >>2. Each row can be distinguished. >>3. Each table does not contain non-primary keyword information that other tables already contain.
7. What are the replication principles and processes of mysql?
Answer: The built-in replication function of Mysql is the basis for building large-scale, high-performance applications. Distribute Mysql's data to multiple systems. This distribution mechanism is achieved by copying the data of one of Mysql's hosts to other hosts (slaves) and re-executing it. * During the replication process, one server acts as the master server, and one or more other servers acts as the slave server. The master server writes updates to the binary log file and maintains an index of the file to track the log loop. These logs can record updates sent to the server. When a slave server connects to the master server, it notifies the master server of the last successful update location read in the log. Receive any updates that occur from that time from the server, and then block and wait for the master to notify new updates.
The process is as follows:
The main server logs the update to the binary log file.
Copy the binary log of the main server from the server to its own replay log.
Rework the time in the relay log from the server and apply the update to your own database.
8. What is the difference between myISAM and innodb in mysql?
Transaction Support > MyISAM: Emphasizes performance, each query is atomic, and its execution is faster than the InnoDB type, but does not provide transaction support. > InnoDB: Provides transaction support, external key and other advanced database functions. Transaction-safe (ACID compliant) type table with transaction (commit), rollback and crash recovery capabilities.
InnoDB supports row-level locks, while MyISAM supports table-level locks. >> When users operate the myisam table, the select, update, delete, and insert statements will automatically lock the table. If the table after locking meets insert concurrency, new data can be inserted at the end of the table.
InnoDB supports MVCC, while MyISAM does not.
InnoDB supports foreign keys, but MyISAM does not.
Table primary key, MyISAM: allows tables without any index and primary key to exist, and indexes are the addresses of the rows that hold them. InnoDB: If no primary key is set or non-empty unique index is set, a 6-byte primary key (not visible to the user) will be automatically generated. The data is part of the main index, and the attached index saves the value of the main index.
InnoDB does not support full-text indexing, while MyISAM supports it.
Portability, backup and recovery, MyISAM: Data is stored in the form of files, so it will be very convenient in cross-platform data transfer. Operations can be performed separately for a table during backup and restore. InnoDB: The free solution can be to copy data files, backup binlog, or use mysqldump, which will be relatively painful when the data volume reaches tens of G.
Storage structure, MyISAM: Each MyISAM is stored into three files on disk. The first file name begins with the name of the table, and the extension indicates the file type. .frm file storage table definition. The data file has an extension of .MYD (MYData). The extension of the index file is .MYI (MYIndex). InnoDB: All tables are saved in the same data file (or multiple files, or independent tablespace files). The size of the InnoDB table is limited by the size of the operating system file, generally 2GB.
9. What are the four transaction isolation level names supported by InnoDB in MySQL, and the differences between levels?
Read Uncommitted >> At this isolation level, all transactions can see the execution results of other uncommitted transactions. This isolation level is rarely used in practical applications because it is not much better than other levels. Reading unsubmitted data is also called Dirty Read.
Read Committed >> 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 made by the transaction that have been submitted. This isolation level also supports so-called Nonrepeatable Reads, because other instances of the same transaction may have new commits during the instance's processing, so the same select may return different results.
Repeatable Read >> This is the default transaction isolation level for MySQL, which ensures that multiple instances of the same transaction will see the same data rows when reading data concurrently. But in theory, this can lead to another difficult problem: Phantom Read. Simply put, phantom reading refers to when a user reads a data row in a certain range, another transaction inserts a new row in that range. When the user reads a data row in that range, a new "phantom" row will be found. The InnoDB and Falcon storage engines solve this problem through the Multiversion Concurrency Control Gap Lock mechanism. Note: In fact, multiple versions only solve the problem of non-repeatable reading, and adding gap locks (that is, what it calls concurrent control here) solves the problem of phantom reading.
Serializable >> This is the highest isolation level, which solves the phantom reading problem by forcing transaction sorting to make it impossible for them to conflict with each other. In short, it is to add a shared lock to each read data line. At this level, it may lead to a large number of timeouts and lock competition.
10. What are the advantages and disadvantages of the two writing methods of [SELECT *] and [SELECT All Fields]?
The former needs to parse the data dictionary, while the latter does not need to
The result output order is the same as the table column order, and the latter is in the specified field order.
The table field is renamed, the former does not need to be modified, the latter needs to be modified
The latter can create indexes for optimization, while the former cannot be optimized
The latter is more readable than the former
11. Briefly describe the operating principle of Mybatis plug-in and how to write a plug-in?
Mybatis can only write plug-ins for four interfaces: ParameterHandler, ResultSetHandler, StatementHandler, and Executor. Mybatis generates proxy objects for interfaces that need to be intercepted through dynamic proxying to implement interface method interception function. Whenever methods of these 4 interface objects are executed, it will enter the interception method, specifically the invoke() method of InvocationHandler. Of course, it will only intercept those methods that you specify to intercept.
Implement Mybatis' Interceptor interface and rewrite the intercept() method, and then write annotations to the plug-in, specifying which interface to intercept which methods to intercept. Remember, don't forget to configure the plug-in you wrote in the configuration file.
12. What is the difference between #{} and ${}?
{} is precompiled processing, and ${} is string replacement.
When Mybatis processes #{}, it replaces #{} in sql with the ? number and calls the set method of PreparedStatement to assign values.
When Mybatis processes ${}, it replaces ${} with the value of the variable.
Using #{} can effectively prevent SQL injection and improve system security.
13. What is index? Please briefly describe what types of commonly used indexes are?
An index is a structure that sorts the values of one or more columns in a database table. It uses indexes to quickly access specific information in a database table. If you want to find him or her by the last name of a particular employee, searching for all rows in the table will help to get information faster than searching for all rows
In layman's terms, index is a directory of data, just like reading a book. If I want to read the contents of Chapter 3, Section 4, if there is a directory, I can just flip the directory and find the page number of Chapter 3, Section 4. If there is no directory, I need to start from the beginning of the book, turn page by page until I turn to the content of Chapter 3, Section 4.
Classification of MySQL indexes
We roughly divide the index generated by column attributes into two categories:
Single column index: The index tree generated with a single column of the table is called the single column index of the table.
Combined index: The index tree generated by combining multiple columns of the table together is called the combined index of the table.
Single column indexes are divided into specific details:
Primary key index: The index tree generated with the primary key of this table is called the primary key index of the table.
Unique index: The index tree generated with a unique column of the table is called the unique index of the table.
Normal index: An index tree generated with the normal column (non-primary key, non-unique column) of the table is called the normal index of the table.
14. What kind of data structure is an index?
Answer: The data structure of the index is related to the implementation of the specific storage engine. The indexes that are used more frequently in MySQL include Hash index, B+ tree index, etc. The default index implementation of the InnoDB storage engine we often use is: B+ tree index.
15. What is the difference or advantages or disadvantages between Hash index and B+ tree?
Answer: First of all, you need to know the underlying implementation principles of Hash index and B+ tree index:
The bottom layer of the hash index is the hash table. When searching, you can get the corresponding key value by calling the hash function once, and then searching back to the table to obtain the actual data.
The B+ tree bottom layer implementation is a multi-channel balanced search tree. For each query, it starts from the root node. The searcher can obtain the checked key value, and then determine whether it is necessary to return to the table to query the data based on the query.
Then we can see that they have the following differences:
The hash index is faster to query equal value (usually), but it is impossible to query ranges. Because after the hash function is indexed in the hash index, the order of the index cannot be consistent with the original order, and scope query cannot be supported. All nodes of B+ tree follow (the left node is smaller than the parent node, the right node is larger than the parent node, and the multi-fork tree is also similar), and the natural support range is.
Hash index does not support sorting using indexes, the principle is the same as above.
The hash index does not support fuzzy queries and the leftmost prefix matching of multi-column indexes. The principle is also because the hash function is unpredictable, and the indexes of AAAA and AAAB have no correlation.
Hash indexes cannot avoid back-to-table querying data at any time, and B+ trees can only complete query through indexes when they meet certain conditions (clustered indexes, overlay indexes, etc.).
Although hash index is faster in equal value query, it is unstable and has unpredictable performance. When there are a large number of repetitions of a key value, a hash collision occurs, and the efficiency may be extremely poor at this time. The query efficiency of B+ tree is relatively stable. For all queries, it is from the root node to the leaf node, and the height of the tree is low.
Therefore, in most cases, directly selecting the B+ tree index can achieve stable and better query speed. There is no need to use hash index.
16. The above mentioned that when B+ trees meet clustered indexes and overlay indexes, there is no need to return to the table to query data. What is clustered index?
Answer: In the index of B+ tree, the leaf node may store the current key value, or may store the current key value and the data of the entire row, which is the clustered index and non-clustered index. In InnoDB, only the primary key index is a clustered index. If there is no primary key, select a unique key to create a clustered index. If there is no unique key, implicitly generate a key to create a clustered index. When the query uses clustered index, the entire row of data can be obtained in the corresponding leaf nodes, so there is no need to perform a back-to-table query again.
17. Will non-clustered indexes return to table query?
Answer: Not sure. This involves whether all fields required by the query statement hit the index. If all the indexes are hit, there is no need to do a back-table query.
To give a simple example, suppose we have indexed on the age of the employee table, then when we conduct a query of select age from employee where age < 20, the age information is already included on the leaf node of the index, and the table query will not be performed again.
18. Do you know about MySQL locks?
Answer: When there are concurrent transactions in the database, data inconsistencies may occur. At this time, some mechanisms are needed to ensure the order of access. The locking mechanism is such a mechanism.
Just like a hotel room, if everyone enters and exits at will, multiple people will snatch the same room. If the lock is installed on the room, only those who apply for the key can check in and lock the room. Others can only use it again after they have finished using it.
19. What locks do MySQL have? Doesn’t locking like the above example hinder concurrency efficiency a bit?
Answer: In terms of lock categories, there are shared locks and exclusive locks.
Shared lock: also known as read lock. When a user wants to read data, a shared lock is added to the data, and multiple shared locks can be added at the same time.
Exclusive lock: also known as a write lock. When a user wants to write data, an exclusive lock is added to the data. Only one exclusive lock can be added. It is repulsive to other exclusive locks and shared locks.
To use the above example, there are two types of behaviors of users. One is to see the house. It is acceptable for multiple users to see the house together. One is a real stay for one night, during this period, it is not allowed to stay or see the house.
The granularity of the lock depends on the specific storage engine. InnoDB implements row-level locks, page-level locks, and table-level locks. Their locking overhead ranges from large to small, and their concurrency capabilities are also from large to small.
20. How many input formats does MySQL binlog have? What are the differences?
Answer: There are three formats: statement, row and mixed.
In statement mode, the recording unit is a statement. That is, the impact caused by each SQL will be recorded. Since the execution of SQL has a context, relevant information needs to be saved when saving. At the same time, some statements that use functions cannot be recorded and copied.
At the row level, the recording unit can basically record all changes in each row, but due to many operations, it will cause a large number of changes in rows (such as alter table). Therefore, the files in this mode save too much information and the log volume is too large.
mixed, a compromise solution. Normal operations use statement records, and row is used when statement cannot be used.
In addition, the new version of MySQL has also made some optimizations to the row level. When the table structure changes, statements will be recorded instead of row-by-row records.
21. How do you optimize a SQL for too long? What aspects do you do?
1. Check whether SQL involves conjunction tables or subqueries of multiple tables. If so, see if business splits can be performed, and related fields are redundant or merged into temporary tables (optimization of business and algorithms).
2. For queries involving linked lists, whether sub-table queries can be performed, and the results after single table query are integrated in fields.
3. If neither of the above two cannot be operated and a linked list query is required, then consider indexing the corresponding query conditions. Speed up the query.
4. Separate historical tables for large-scale tables (such as transaction flow tables).
5. The database is separated by master-slave, read-write separation, and reduce the pressure of read-write simultaneous targeting the same table. As for master-slave synchronization, mysql has its own binlog to implement master-slave synchronization.
6. Explain analyze SQL statements, check the execution plan, analyze whether the index is used, analyze the number of scanned rows, etc.
For more MySQL interview questions, you can check the following related links