Common interview questions - 4 (MySQL)

MySQL

The nature of transactions ？

Business （Transaction） It's a sequence of operations , Indivisible work unit , With BEGIN TRANSACTION Start , With ROLLBACK/COMMIT end

1. Atomicity ： Transactions are the smallest unit of execution , Division is not allowed . The atomicity of the transaction ensures that the action is either complete , Or it doesn't work at all ;
2. Uniformity ： Before and after the execution of the transaction , The database transitions from one consistency state to another .
3. Isolation, ： When accessing the database concurrently , A user's affairs are not disturbed by other transactions , The database between concurrent transactions is independent ;
4. persistence ： After a transaction is committed . Its changes to the data in the database are persistent , Even if the database Failure should not have any impact on it .

Concurrency consistency issues ？

• Missing changes ： A transaction modifies the data , Before transaction commit , Another transaction modifies the same data , Overwrites previous changes ;
• Dirty reading （Dirty Read）： One transaction has been read and modified by another transaction 、 But did not submit （ Rolled back ） The data of , The data obtained by the two transactions are inconsistent ;
• It can't be read repeatedly （Nonrepeatable Read）： In the same transaction , A query operation reads a row of data at one time and then reads the row of data at a later time , It is found that the data has been modified （ May have been updated or deleted ）;
• Fantasy reading （Phantom Read）： When the same query is executed multiple times , Because other transactions have performed insert operations within this data range , This will result in different result sets being returned each time （ And non repeatable ： It is aimed at a whole data / Range ; And it needs to be an insert operation ）

Understanding of transaction isolation level ？

• Uncommitted read （Read Uncommited）： Before a transaction is committed , The result of its execution is also visible to other transactions . Can lead to dirty reading 、 It can't be read repeatedly 、 Fantasy reading ;
• Submit to read （Read Commited）： A transaction can only see the changes made by the submitted transaction . Avoid dirty reading problems ;
• Repeatable （Repeatable Read）： It can ensure that the same transaction can get the same result when reading the same data multiple times .（MySQL The default isolation level of ）. Avoid dirty reading 、 It can't be read repeatedly ;
• Serializable （Serializable）： Force transactions to execute serially , Make it impossible to conflict with each other , So as to solve the problem of unreal reading . It may lead to a large number of timeout phenomena and lock competition , It is seldom used in practice .

MVCC( Multi version concurrency control )

MVCC Two hidden columns are saved after each row of records , Used to store Create version number and Delete version number .

• Create version number ： Transaction version number when creating a data row （ Transaction version number ： System version number at the start of the transaction ; System version number ： Every time you start a new business , The system version number will increase automatically ）;
• Delete version number ： Transaction version number at the time of deletion ;
• Various operations ：
  • When inserting , Record the creation version number ;
  • Delete operation , Record deletion version number ;
  • When updating operation , Record the deleted version number first , Add another line of record to create version number ;
  • Query operation , Only when the following conditions are met can it be queried ： The deletion version number is undefined or greater than the current transaction version number （ The deletion operation is performed after the current transaction is started ）; The creation version number is less than or equal to the current transaction version number （ The creation operation is completed by the transaction or before the transaction is started ）

Reduce lock contention through version number , Improved system performance ; It can realize two isolation levels: committed read and repeatable read , Uncommitted reads do not need to be used MVCC

Constraint type ？

• Primary key constraint （Primary Key）
• Unique constraint （Unique）
• Check constraint （check）
• Non empty constraint (NOT NULL)
• Foreign key constraints （Foreign Key）

MySQL framework ?

• Server layer ： The connector 、 The query cache 、 analyzer 、 Optimizer 、 actuator 、 Built in functions

• Storage engine ： Data storage and extraction .InnoDB、MyISAM

One SQL Statement execution flow in the database framework ？

1. The application puts the query SQL Statement is sent to the server for execution ;
2. The query cache , If the query cache is turned on , After the server receives the query request , It will not directly query the database , Instead, find out whether there is corresponding query data in the query cache of the database , If there is , It will be returned directly to the client . Only when the cache does not exist , The following operations will be carried out ;
3. Query optimization processing , Generate execution plan . This stage mainly includes parsing SQL、 Preprocessing 、 Optimize SQL Implementation plan ;
4. MySQL Complete the whole query according to the corresponding execution plan ;
5. Return the query results to the client .

What is a view ？ What is a cursor ？

• View ： A virtual table composed of data selected by query from the basic table of the database （ The definition of view stored in the database ）. It can be added / Delete / Change / Check the operations such as . A view is a reference to several basic tables , A virtual table , Query the result of statement execution , Don't store specific data （ The basic table data has changed , The view changes as well ）; It can be the same as the basic table , Add, delete, modify and check ( Addition, deletion and modification are conditional ); For example, the view generated by linked table query cannot be , The addition, deletion and modification of the view will affect the data of the original table . benefits ：
  • By giving users only access to views , Guarantee the data Security ;
  • simplify complex SQL operation , The complexity of hiding data （ For example, complex connections ）;
• The cursor （Cursor）： Used to locate the... Returned in the query Specific rows of the result set , To operate on a specific line . Using cursors, you can easily move and traverse the result set , Scroll or browse as needed / Modify the data in any row . Mainly used for interactive applications .

char and varchar The difference between ？

• **char(n) ：** Fixed length type , such as ： subscribe char(10), When you type ”abc” Three characters , Do they take up space 10 Bytes , other 7 Empty bytes .char advantage ： Efficient ; shortcoming ： Occupancy space ; Applicable scenario ： Storing passwords md5 value , Fixed length , Use char Very suitable .

• **varchar(n) ：** Variable length , The stored value is the byte occupied by each value plus a byte used to record its length .

Classification of indexes ？

From a data structure point of view

1. Tree index (O(log(n)))
2. Hash Indexes

From the perspective of physical storage

1. Clustered index （clustered index）
2. Nonclustered indexes （non-clustered index）

From a logical point of view

1. General index
2. unique index
3. primary key
4. Joint index
5. Full-text index

Understanding of index ？

The principles of indexing ：

1. In the most frequently used 、 To narrow the scope of the query to build an index on the field ;
2. In frequent use 、 Index the fields that need to be sorted .

Not suitable for indexing ：

1. For the columns rarely involved in the query or the columns with more duplicate values , It's not good to index ;
2. For some special data types , It's not good to index , such as ： The text field （text） etc. .

What data structure is used at the bottom of the index ？

• InnoDB Storage engine ：B+ Trees

Advantages and disadvantages of using indexes ？

• Greatly speed up the of data Retrieval speed ;
• It can significantly reduce the number of queries Grouping and sorting Time for ;
• By creating a unique index , Ensure the uniqueness of each row of data in the database table ;
• Will be random I/O Turn into The order I/O（B+Tree The index is ordered , Will store the adjacent data together ）

shortcoming ： Building and maintaining indexes takes time and space , Updating the index is slow .

Under what circumstances will the index fail ？

• With “%( Express arbitrarily 0 Characters or more )” At the beginning LIKE sentence ;
• OR The index is not used before and after the statement ;
• Data types are implicitly transformed （ Such as varchar Without a single quotation mark, it may automatically convert to int type ）;
• For multi-column indexes , Must satisfy Left most matching principle / Leftmost prefix principle ( Top left first ,eg： Multi column index col1、col2 and col3, be The circumstances in which the index takes effect include col1 or col1,col2 or col1,col2,col3);
• If MySQL It is estimated that full table scanning is faster than indexing , Index is not used （ For example, a very small watch ）

B+ Understanding of trees ？

1. B+ Trees are based on B Tree and leaf nodes are implemented by sequentially accessing pointers , It has B The balance of the tree , And improve the performance of interval query through sequential access pointer .
2. When searching , First, binary search is performed at the root node , Find one key Where the pointer is , Then recursively search the node the pointer points to . Until you find the leaf node , Then binary search on the leaf node , find key The corresponding data.
3. Insert 、 Deleting will destroy the balance of the balance tree , So after inserting the delete operation , A split of the tree is needed 、 Merge 、 Rotation and other operations to maintain balance .

Why? InnoDB Storage engine selection B+ Trees, not B Trees ？

• IO low repetition ：B+ The middle node of the tree only stores the index , The data is stored in the leaf node , Therefore, the intermediate node can store more data , Make the index tree fatter ;
• Range query is more efficient ：B The tree needs to traverse the whole tree in the middle order , only B+ The tree needs to traverse the linked list in the leaf node ;
• Query efficiency is more stable ： Every query needs to go from root node to leaf node , The path length is the same , So the efficiency of each query is almost the same

Understanding of hash index ？

Hash index can be used to O(1) Time to search , But there's no order . Cannot be used for sorting and grouping 、 Only exact search is supported , Cannot be used for partial and range lookups .

InnoDB The storage engine has a special function called “ adaptive hash index ”, When an index value is used very frequently , Will be in B+ Create a hash index above the tree index , In this way B+Tree Index has some advantages of hash index , such as ： Fast hash lookup .

How to know if the created index has been used ？

Use Explain Command to view the execution plan of the statement ,MySQL Before executing a statement , It will go through the query optimizer , After that, I will get the analysis of the statement , That's the execution plan , It contains a lot of information . You can analyze whether the index is hit through the information related to the index , for example ：possilbe_key、key、key_len Etc , The indexes that this statement may use are described respectively 、 The actual index used and the index length used .

How to optimize query performance ？

1. Only necessary columns are returned ： Better not to use SELECT * sentence .
2. Just go back to the necessary lines ： Use LIMIT Statement to limit the data returned .
3. Cache data from repeated queries ： Using caching can avoid queries in the database , Especially when the data to be queried is often repeatedly queried , The query performance improvement brought by cache will be very obvious .

InnoDB and MyISAM Comparison ？

1. Business ：MyISAM Unsupported transaction ,InnoDB Support transactions ;
2. Full-text index ：MyISAM Full text index support ,InnoDB 5.6 Full text indexing was not supported before ;
3. About count()：MyISAM It stores the total number of rows directly ,InnoDB Will not be , Need to scan by line . It means for select count() from table; If you have a lot of data ,MyISAM Will return in an instant , and InnoDB It'll scan line by line ;
4. Foreign keys ：MyISAM Foreign key not supported ,InnoDB Support foreign keys ;
5. lock ：MyISAM Only table locks are supported ,InnoDB Can support row lock .

MySQL Row and table locks ？

MyISAM Only table locks are supported ,InnoDB Support table lock and row lock , The default is row lock .

Table lock ： Low overhead , Locked fast , A deadlock will not occur . Large locking size , The highest probability of lock collisions , Lowest concurrency .

Row-level locks ： Spending big , Lock the slow , A deadlock occurs . Small lock size , The probability of lock conflict is small , Highest concurrency .

Optimistic lock and pessimistic lock ？

• Pessimistic locking ： Think the data will be modified at any time , Therefore, it will be locked before reading data every time , Prevent other transactions from reading or modifying data ; It is applied to scenes with frequent data updates ;
• Optimism lock ： It will not be locked when operating data , However, when updating, it will be judged whether there are other transactions to update this data during this period , If updated , Then fail and try again ; It is suitable for reading more and writing less . The implementation methods of optimistic lock are ：
  • Add a version number or timestamp field , Update this field every time the data is updated ;
  • First read the fields you want to update or all the fields , Compare when updating , Update only if the field has not changed

DROP、DELETE And TRUNCATE The difference between ？

All three can represent deletion , The nuances are as follows ：

	DROP	DELETE	TRUNCATE
SQL Statement type	DDL	DML	DDL
Roll back	Cannot roll back	Roll back	Cannot roll back
Delete content	From the database Delete table , All the data lines , Indexes and permissions will also be removed	The structure of the watch is still , Delete the All or part of the data line	The structure of the watch is still , Delete... From the table All the data
Delete speed	Delete the fastest	Slow to delete , Need to delete line by line	Delete fast

therefore , When you no longer need a watch , use DROP; When you want to delete some data lines , use DELETE; To delete all data while retaining the table TRUNCATE.

What is master-slave replication ？ What is the principle of implementation ？

Master slave copy （Replication） Data can be taken from a MySQL The database master server is copied to one or more slave servers , The slave server can copy all databases or specific databases in the master server , Or a specific watch . Asynchronous mode is adopted by default .

Realization principle ：

• master server binary log dump Threads ： Change the data in the master server （ Additions and deletions ） Log write Binary log in ;
• From the server I/O Threads ： Responsible for reading from the master server binary log, And write local Relay log;
• From the server SQL Threads ： Responsible for reading Relay log, Resolve the data changes that have been performed by the master server , And re execute... From the server （Replay）, Ensure the consistency of master-slave data

Why master-slave replication ？

• Read / write separation ： The master server is responsible for writing , Read from the server
  • Ease lock contention , Even if the primary server is locked , You can still read ;
  • From the server you can use MyISAM, Improve query performance and save system overhead ;
  • Add redundancy , Improve availability
• Real time data backup , When a node in the system fails , It's easy to fail over
• Reduce the cost of a single server disk I/O Frequency of visits , Improve the... Of a single machine I/O performance

Reference material

MySQ Interview questions ( Answer attached )- Handsome play with programming (iamshuaidi.com)

The illustration MySQL Introduce | Kobayashi coding (xiaolincoding.com)

Waking-Up/Database

《MySQL Will know 》

《Effective MySQL》

《 High performance MySQL》

《InnoDB Storage engine 》