This is the sixth day of my participation in the August More text Challenge. For details, see: August More Text Challenge

An overview of the

Mysql interview is an essential part of the back-end interview, as I have been interviewed and interviewed more than 100 people, it is necessary to master the following basic interview.

The interview questions

1. Describe what is data Three paradigms

- First Normal form (1NF) : The fields in a database table are single-attribute and non-divisible. This single attribute is made up of basic types, including integer, real, character, logical, date, and so on. - Second normal form (2NF) : there is no partial functional dependence of non-key fields on any candidate key field in the database table (partial functional dependence refers to the presence of some fields in the combination of keywords to determine the non-key field), that is, all non-key fields are completely dependent on any set of candidate keywords. - Third normal form (3NF) : On the basis of the second normal form, a data table conforms to the third normal form if there is no transfer function dependence of non-critical fields on any candidate key fields. The so-called transfer function dependence means that the transfer function C depends on A if there is A deterministic relationship "A → B → C". Therefore, a database table that satisfies the third normal form should not have the following dependencies: key field → non-key field X → non-key field YCopy the code

2. Describe DML, DDL and DCL respectively

- Data manipulation Language (DML) : Data Management language (DDL): SELECT, UPDATE, INSERT, DELETE (DDL): SELECT, UPDATE, INSERT, DELETE Data definition language, DDL is more than DML, the main commands are CREATE, ALTER, DROP, etc. DDL is mainly used to define or change the structure of the TABLE (TABLE), data types, links between the tables and constraints on the initialization work. Most of them use Data Control Language (DCL) when creating tables. Grant,deny,revoke is a statement that sets or changes the privileges of a database user or role. By default, only people such as sysadmin, dbCreator, DB_owner, or DB_SecurityAdmin have the authority to execute DCLCopy the code

3. The difference between MyisAM and InnoDB

InnoDB supports transaction while MyISAM does not support transaction. InnoDB supports row-level locking while MyISAM supports table-level locking. InnoDB supports MVCC while MyISAM does not. MyISAM supports it.Copy the code

What are superkeys, candidate keys, primary keys, and foreign keys?

- 1. Superkey: The set of attributes that uniquely identifies a tuple in a relationship is called the superkey of the relational schema. A single property can act as a superkey, or a combination of properties can act as a superkey. Superkeys contain both candidate and primary keys. - 2. Candidate key: the minimum superbond, that is, the one with no redundant elements. - 3. Primary key: a combination of data columns or attributes in a database table that uniquely and completely identifies the stored data object. A data column can have only one primary key, and the value of the primary key cannot be missing, that is, it cannot be Null. - 4. Foreign key: The primary key of another table that exists in a table is called the foreign key of this table.Copy the code

Select * from Mysql where ID = 18 and ID = 15; select * from Mysql where ID = 18 and ID = 15; select * from Mysql where ID = 18 and ID = 15;

- 18 if the table engine is MyISAM. Because MyISAM tables will be put on the primary key of the biggest ID recorded data file, restart MySQL on the primary key of the biggest ID will not be lost; - 15 if the table engine is InnoDB. Since InnoDB tables only record the maximum ID of the auto-increment primary key in memory, the maximum ID will be lost if the database is restarted or the table is operated by OPTION.Copy the code

Select count(*), select count(*), select count(*), select count(*)

- MyISAM is faster because MyISAM maintains an internal counter that stores the total number of tables on disk and can be fetched directly; - InnoDB does not store the total number of rows in the table on disk. It requires a full table scan to accumulate the number of rows, so the larger the table, the more time-consuming; The reason InnoDB does this has to do with its support for transactions. Due to multi-version concurrency control (MVCC), the InnoDB table "should return many rows" is uncertain.Copy the code

What is the difference between count(*), count(1) and count(column name)?

-count (*) includes all columns, equal to the number of rows, and will not ignore the NULL column value when the result is counted. -count (1) contains all columns and uses 1 to represent the line of code. When the result is counted, the column value is not ignored as NULL; - count(column name) Includes only the column name. The count of NULL columns is ignored when the result is counted. Performance comparison - Column name is the primary key, count(column name) is the most efficient; - If the table has only one field (not a primary key), count(*) is the most efficient; - Column name is not the primary key, count(1) is faster than count(column name); - If the table has multiple columns and no primary key, count(1) is faster than count(*);Copy the code

What is the difference between in and EXISTS in MySQL?

- exists: Queries the surface by loop. Each query checks the condition statement of exists. If the condition statement of exists can return a record, the condition is true and the record to which the current loop belongs is returned. Conversely, if a condition statement in EXISTS does not return a record, the current record is discarded. The condition of exists is like a bool. It is true if it returns a result set, and false if it does not. - SELECT * from A where exists (select * from B where in: in) select * from A where in (select id from B); - If the size of the two tables is the same, there is no difference between in and EXISTS. - If one table is small and the other is large, then exists is used if the subtable is large, and IN is used if the subtable is small.Copy the code

9. What’s the difference between UNION and UNION ALL?

- Both SQL statements combine the result set into one. The two SQL statements must have the same number of fields and the field type must be "consistent" (consistent). - UNION removes duplicate data records after table join (low efficiency), while UNION ALL does not remove duplicate data records; - UNION will sort by field order, while UNION ALL will simply combine the two results and return;Copy the code

10. Why use indexes?

- The uniqueness index is used to ensure the uniqueness of data. - Speed up the retrieval of data (greatly reducing the amount of data to be retrieved), this is the main reason for creating indexes. - Speeds up the connection between tables. - Reduces grouping and sorting time when using grouping and sorting clauses for data retrieval. - Can be used in the query process, optimize hidden, to provide system performance.Copy the code

11. Classification of indexes

For details, see my previous article, mysql Basics – Indexes

Physical storage Angle - Clustered index - Non-clustered index, also known as secondary index Data structure Angle - B+ number index - Hash Index - Full text index (only available before 5.6 when MyISam is the storage engine) -r-tree Index Logical Angle - Primary key index: Is a unique index, does not allow null values - the single index: each index can contain only a single column, a table can be multiple single index - composite index: each index contains at least two column, queries to the left - the only index distribution principle: increase the index of the column in the table must be unique - spatial index: in view of the spatial column fields and indexesCopy the code

12, database index principle, why use B+ tree, why not binary tree?

- When the algorithm logic, binary tree search speed and the number of comparisons is the least; - However, since database indexes are stored on disk, you must consider disk IO, which is a time-consuming operation; - When the amount of data is large, the size of the index may be several G, but it is not possible to load all of them into memory; - To load each disk page one by one, where the disk page corresponds to the node of the index tree; - The height of the index tree (hierarchy) is the number of disk I/OS required; - In the case of the same amount of data, the height of B+** tree is smaller than that of binary tree. The bigger the amount of data is, the more obvious the difference will be.Copy the code

13. When do I need to create an index?

1, the primary key automatically create unique index; 2. Frequently query the field of the condition last night; 3. Create indexes for columns associated with other tables by foreign key relationship; 4. The selection of single key/composite index, which tends to create composite index in high concurrency; 5. Sorted fields in the query. Sorted fields can be greatly improved by index access; 6. Statistics or grouping fields in the query;Copy the code

14. In what cases do I not need to create an index?

- Too few table records; - Tables that are frequently added and deleted; - Table fields with duplicate data and uniform distribution; - Frequently updated fields are not suitable for index creation (will increase IO burden); - Create indexes for columns that are not used in the where condition;Copy the code

15. Classification of locks

For details, see my other article: mysql Basics – Locking

Data operation types 1. Read lock (shared lock) : Multiple read operations on the same piece of data can be performed simultaneously without affecting each other. 2. Write lock (exclusive lock) : Blocks other write locks and read locks until the current write operation is complete. 1, table level lock: low overhead, fast locking; Deadlocks do not occur; The lock granularity is large, the probability of lock conflict is high, and the concurrency is the lowest. (MyISAM and Memory engine use table level lock) 2, row level lock: expensive, slow lock; Deadlocks occur; Minimum locking granularity; The probability of lock conflict is the lowest and the concurrency is the highest. (InnoDB supports row-level locking as well as table-level locking. The default is row-level locking.) Deadlocks occur; Lock granularity is between table lock and row lock, and concurrency is mediocre. 4, applicable: From the lock point of view, table lock is suitable for the main query, only a small number of index conditions update data application. Row locking is suitable for applications that have a large number of concurrent updates of a small number of different data by index criteria and concurrent queries.Copy the code

Describe the pessimistic lock and the optimistic lock

- Pessimistic lock: assume that concurrency conflicts will occur and mask any operation that may violate data integrity. - Optimistic lock: assume that concurrency conflicts will not occur and only check for data integrity violations when an operation is committed.Copy the code

InnoDB row lock classification

InnoDB implements two types of shared lock (S) : allows one transaction to read a row and prevents other transactions from acquiring exclusive locks on the same data set. Exclusive lock (X) : Allows transactions that acquire exclusive locks to update data, preventing other transactions from acquiring shared and exclusive locks on the same dataset. 2. In order to allow both row locks and table locks, InnoDB also has two internal intention locks (both table locks) intention shared locks (IS) : if a transaction intends to add a row-shared lock to a row, it must first acquire the IS lock of the table. Intended exclusive lock (IX) : A transaction that intends to assign an exclusive lock to a row must first acquire the table's IX lock.Copy the code

The basic elements of affairs

Atomicity: All operations in a transaction must either be completed or not, and cannot be stuck somewhere in between. An error occurred during transaction execution and the transaction is rolled back to the state before the transaction began. Consistency: Database integrity constraints are not broken before and after a transaction begins. Isolation: The execution of one transaction will not be disturbed by other transactions. That is, the operations within a transaction and the data used are isolated from other concurrent transactions, and the concurrent transactions do not interfere with each other. Durability: After the transaction is finished, they stay in the database for long and won't be rolled back.Copy the code

19. Transaction isolation level

1. Read-uncommitted: Indicates the lowest isolation level. Data changes that have not been committed can be Read, which may result in dirty, phantom, or unrepeatable reads. 2. Read-Committed: Allowed to Read Committed data in a concurrent transaction. Dirty reads are prevented, but phantom or non-repeatable reads may still occur. 3. Repeatable read: Results of multiple reads of the same field are consistent unless the data is modified by the transaction itself, which can prevent dirty reads and non-repeatable reads, but magic reads may still occur. Default isolation level of MySQL. 4, Serializable: highest isolation level, fully compliant to ACID isolation level. All transactions are executed one at a time, so that interference between transactions is completely impossible. This level prevents dirty reads, non-repeatable reads, and magic reads.Copy the code

20. Transaction log classification

Redo log undo logCopy the code

21. The redo log is persistent and atomic

- In InnoDB storage engine, transaction logging is implemented through the Redo log and the InnoDB log Buffer. - When a transaction is started, the operation of the transaction is written to the log cache of the InnoDB storage engine. These cached logs need to be flushed to disk for persistence before the transaction is committed. This is called write-ahead logging. - After the transaction is committed, the data files mapped in the Buffer Pool are slowly flushed to disk. If the database fails, you can restore the database to the state it was in before the crash, according to the redo log. Pending transactions can be committed or rolled back, depending on the recovery strategy. - At system startup, a contiguous storage space is allocated for the redo log. The redo log is sequentially appented to improve performance through sequential I/O. All transactions share storage space in the redo log, and their redo logs are recorded alternately in the order in which the statements are executed.Copy the code

22. Type of mysql log

1. Error log: Records error information, as well as warning information or correct information. 2. Query log: Records information about all requests to the database, whether or not they are executed correctly. 3. Slow query log: Set a threshold and record all SQL statements whose running time exceeds the threshold to the slow query log file. Binary log: Records all the operations that the database performs to change. 5, relay log: is also a binary log, used to restore slave library. Transaction log: Redo log and Undo log.Copy the code