Database common face questions (with answers) __ Database

1. Four characteristics of the transaction
Atomic, either executed, or not executed
Isolation, the process is not visible to other sessions until all operations have been done
Consistency, before and after transactions, the total amount of data consistent
Persistence, once a transaction is committed, changes to the data are permanent

2. Database isolation level, what problems each level will cause, MySQL default is which level

Dirty reads: Transaction b reads transaction A has not yet submitted data
Non-repeatable reads: Two transactions read inconsistent data
Phantom read: Transaction a modified the data, transaction B also modified the data, at this point in the case of transaction A, obviously modified the data, yesterday is not the same,
Isolation level read Data consistency dirty read non-repeatable read Phantom read
The lowest level of uncommitted reading is yes
Committed read statement level no, yes.
Transaction-level transaction level is no
Serialization highest level no no no

3.MYSQL two storage engine differences (transaction, lock level, etc.), their respective applicable scenarios
MYISAM does not support transactions, foreign keys, table locks, insert data, lock the entire table, check the total number of tables, do not need full table scan
INNODB support transaction, support foreign key, row lock, check table total number, full table scan

4. Index has B + index and hash index, the respective difference
Hash index, equivalent query efficiency is high,
Cannot sort
Cannot make range query

B + Index
Data orderly
Scope Query

5.b+ index data structure, and B-tree difference

1. A single node stores more elements, and a B + tree space utilization is higher, making the query less IO.
2. All inquiries must find the leaf node, the query performance is stable.
3. All leaf nodes form an orderly linked list to facilitate the range query

6. Bottom-level implementation of the index (b + tree, why not red-black tree, B. Tree)
Add, remove, red-black trees will be frequently adjusted to ensure the nature of the red-black tree, wasting time
B-Tree, query performance is not stable, the query results are not high,
B-trees, each of which holds fingerprints pointing to the real data, is a little higher than the number of elements stored per house in each layer of a B + tree.

7. Clustered and nonclustered index differences
Clustered index, data stored in indexed order, and neutron nodes store real physical data.
Nonclustered index, which stores pointers to true data rows

8. Advantages and disadvantages of the index, when to use the index, when not to use the index (emphasis)
The best thing about indexing is to improve query speed,
The disadvantage is that it is inefficient to update data because you want to update the index at the same time
Querying data frequently is indexed, and indexing is not recommended if you want to change the data frequently.

9.B Tree, also B-tree
First look at the binary lookup tree, time complexity log (n), consider disk IO, so need two fork to find the tree is short and narrow, reduce the height of the tree.
1. The root node has at least two children.
2. Each intermediate node contains k-1 elements and K-children, of which M/2 <= K <= m
3. Each leaf node contains k-1 elements, of which m/2 <= K <= m
4. All leaf nodes are located on the same floor.
5. The elements in each node are arranged from small to large, and the K-1 elements in the nodes are just the domain of the elements that the K child contains.
The size of k depends on the size of the disk page, and main memory and disk Exchange data in page units. When the program to read the data is not in main memory, will trigger a page fault exception,
The system will send a read signal to the disk, the disk will find the starting position of the data and read one or several pages back to load in memory, then the exception returned, the program continues to run.

The difference between a 10.InnoDB index and a MyISAM index:
One is the difference between the primary index, the InnoDB data file itself is the index file. and MyISAM's index and data are separate.
The second is the difference between secondary indexes: the secondary index of the InnoDB data field stores the value of the corresponding record primary key instead of the address. MyISAM's secondary index and primary index are not much different.

11. Why Use B-/+tree
The index lookup process will generate disk I/O consumption, mainly looking at IO times, and disk access theory.
According to the definition of b-tree, it is known that a maximum of H nodes must be accessed at one time. The design of the database system cleverly utilizes the principle of disk prefetching,
Sets the size of one node to be equal to one page so that each node needs only one I/O to be fully loaded
The principle of locality and disk pre-read

12.b+ Tree Concrete Implementation
A M-order B + Tree has several characteristics as follows:
1. The intermediate nodes of a K-subtree contain k elements (b-trees are k-1 elements), each element does not save data, is used only for indexing, and all data is stored in leaf nodes.
2. All leaf nodes contain information about all elements, and pointers to records containing these elements, and the leaf nodes themselves are linked in large order by the size of the key word.
3. All intermediate node elements exist at the same time in child nodes, and are the largest (or smallest) element in the child node element

13. Database optimization (one SQL can use an index, multiple indexes will automatically select the optimal index, from the SQL statement optimization and the index two parts to answer)
Principle 1.sql use Index as much as possible
2. Optimizing SQL statements
Subquery becomes a LEFT join
Limit distribution optimization, first use ID positioning, and then paging
or condition optimization, multiple or conditions can be combined with union ALL to combine the results (union ALL results may be repeated)
Unnecessary sort
Where to filter all records after retrieving having,having instead of
Avoid nested queries
When you make an equivalence query for multiple fields, the federated index

14. Whether to use index explain view query plan

15. Index leftmost prefix problem
If you establish a federated index on three fields, the third field will not use the index if the second field does not use the index.
16. Index classification, index failure condition
Normal index: The most basic index, without any restrictions
Unique index: Similar to normal index, except that the value of an indexed column must be unique, but a null value is allowed.
Primary KEY index: It is a special unique index and does not allow null values.
Full-Text indexing: For larger data, generating full-text indexes is a time-consuming space.
Combined index: For more MySQL efficiency can be set up a composite index, following the "left prefix" principle
Failure condition
Condition is OR, if you also want the or condition to take effect, add an index to or each field
Like query, to% development
Internal function
To evaluate an indexed column
Is null will not be used with

16. Master and slave copy of database
Default asynchronous replication, which can easily cause the main library data to be inconsistent with the library
A database is master, a database is slave, through the Binlog log
Slave two threads, one thread to read Master Binlog log, write to own relay log
A thread parsing log, executing SQL
Master starts a thread
Pass Binlog log to Slave

Semi-synchronous replication
Only the Binlog log sent by Master is written to the slave's relay log, when the main library
Returns the feedback that the operation completes, the performance has certain reduce

Parallel replication
Slave multiple threads to request Binlog log

17.long_query How to Solve
Set parameters, turn on slow logging, and get SQL that takes longer than a certain amount of time

Use scenes for 18.varchar and Char
Used to store characters
varchar applicable character lengths are often changed
Char is suitable for character length fixed

19. The role of the database connection pool
Maintain a certain number of connections, reducing the time to create a connection
Faster response times
Unified Management

20. Sub-Library, master-slave copy, read and write separation
Read and write separation, reading from the library, write the main library
Spring configures two databases to dynamically switch data sources through AOP (aspect-oriented programming) and before writing or reading methods.

21. Database Three Paradigm
1NF attribute is not divided
2NF non-primary key properties, completely dependent on primary key properties
3NF non-primary key attribute no delivery dependency

22. Differences between relational and non-relational databases
Database
Type attributes
relational database MySQL 1, relational database, refers to the use of a relational model to organize data database,
2. The greatest feature of relational databases is the consistency of transactions;

                    Advantage
                  1, easy to understand: Two-dimensional table structure is very close to the logic of the world a concept, the relationship model relative to the network, level and other models are easier to understand;
                  2, easy to use: Universal SQL language makes operation of relational database very convenient;
                  3. Easy to maintain: rich integrity (Entity integrity, referential integrity, and user-defined integrity) greatly reduce the probability of data redundancy and data inconsistency;
                  4, support SQL, can be used for complex queries. 
                     disadvantage
                  1, in order to maintain consistency of the huge cost is the poor reading and writing performance,
                  2, fixed table structure,
                  3, do not support high concurrent read and write requirements,
                  4, do not support the efficient reading and writing of massive data;

Non-relational database features
HBase 1, using key value pairs to store data;
2, distributed;
Advantages
High read and write performance without parsing by SQL layer
Based on the key value pairs, the data is not coupling, easy to expand
Format for storing data: The NoSQL storage format is key,value form
Disadvantages
Transaction not supported
Do not provide SQL support

23. Inner JOIN in the database join, outer join, cross Join
Take a, b two tables as an example
A LEFT Join B
Select All records of a, null instead of Table B
Right Join empathy

INNER JOIN
All records of A,b are selected and no records are replaced with null

Cross Join (Cartesian product)
A record is generated for each record in a and each record in B
For example, there are 4 in a, there are 4 in B, and the cross join has 16 records

24. What are the locks and how to lock them when select
Optimistic lock, self implementation, through version number
Pessimistic lock: Shared locks, multiple transactions, can only read and write, plus lock in share mode
Exclusive lock, one transaction, only write, for update
Row lock
Table lock
25. How to solve the deadlock
Process number found, kill process

26 left-most matching principle
The leftmost matching principle is for index
For example, two fields (Name,age) establish a federated index, and if the where age=12 does not use the index, we can simply understand that the value of the Name field is sorted first, and then the age data is sorted, if the age is directly checked, There is no use of the index, query conditions where name= ' xxx ' and age=xx at this point, then use the index, and then think about where age=xx and name= ' xxx ' This SQL will use the index, according to the normal principle is not to use , but the optimizer optimizes and switches the position. This SQL can also use the index.

Please point out your own summary and thank you for your mistakes.