Knowledge of MySQL database features

Recently interviewed by the interviewer often asked about the database knowledge, so summed up the interviewer asked questions have been gradually understanding of the database

1, before the Baidu interview officer asked me a special basis of SQL problem: How to clear all the records of the table, used to do project development in the school when used, but when the interview did not think, down to think of how to write SQL, I have to spit trough a bit of their own

The problem of SQL is written in two ways: 1, TRUNCATE TABLE tablename TRUNCATE TABLE command will quickly delete all records in the data table, but keep the data table structure, the data is not recoverable (self-increment primary key from the beginning)

2, delete from tablename where 1=1 get delete from tablename the deletion record in the system rollback segment, data recoverable

2, the database index: mainly InnoDB and MyISAM index, their differences are mainly in the file structure, lock, and operational aspects

1, File structure: InnoDB is the index file and data file separation, Myiasam so and the data file in the same file

2, Lock: InnoDB adopts the row lock, realizes the segmented lock, on the line concurrent writes, each transaction only to the corresponding row to lock, MyISAM uses the table lock, therefore will be concurrent writes the time will have the bottleneck

3. Operational aspects: InnoDB support transactions, Muisam does not support transactions, but count (*) uses MyISAM much faster than InnoDB. Because MyISAM has a built-in counter

3, the characteristics of the transaction:

1. Atomicity: The transaction either succeeds or fails, and MySQL implements the atomicity of the transaction and the Undo.log log has a relationship

2, consistency: The result of transaction execution must be to change the database from one consistency state to another, such as bank transfer problem, two account version0 (a=100,b=0); A to B transfer 100 will go through the following steps: 1, read the amount of a, to see if there are 100 yuan,

If there is on the A-100, there will be a middle state, defined as Version1 (a=0,b=0), 2, b+100, the database state becomes Version2 (a=0,b=100); database consistency requires that the data that the user sees is either in Version0 State, Either in the Version2 state, the user can not see the Version1 this intermediate state, database consistency and atomicity is closely related

3, Isolation: My personal understanding: Isolation is a tradeoff between data consistency and performance tradeoffs, there are four isolation levels: (below I through a, b two transactions to explain)

1, Serializable: The isolation level is the use of pessimistic lock---exclusive lock, through the read-write lock separation to achieve a strong, although the data to achieve strong consistency, but the degree of parallelism is not high

2, READ UNCOMMITTED: If there is a, B two transactions, a transaction modified data, in the B transaction can immediately see the data changes, it feels like the volatile variables in Java

3, Read committed: If there is a, B two transactions, a transaction modified data, and committed the transaction, B transaction will be able to read the modified data, which is the default isolation level of Oracle. There is an issue with non-repeatable reads at this level (MySQL default isolation level)

4, Repeatable: If there is a, B two transactions, a transaction modified data, and committed the transaction, B transaction can not see the data just modified, in the transaction multiple execution Select read out the data is consistent

5, the above isolation level is defined in SQL92, MVCC can be regarded as an extension of the standard; MVCC is an optimistic lock that allows data to be versioned and to achieve consistency of data with a transaction ID for each transaction, a strategy for space-time change

4, Index: about the index of knowledge mainly: Index of the data structure, the use of indexes and optimization

1, whether it is the InnoDB or MyISAM index engine is the b-tree of the transformation---b+tree:b-tree and b+tree the difference: b-tree nodes stored data, and b+tree only on the leaf node to store data. In MySQL, each node is defined as an integer multiple of the page (the logical block of the computer's management memory), so that you can use the local read-ahead feature of the operating system to reduce the number of I/O times on the disk.

2, the establishment of the index need to take into account the following aspects of the problem:

1, we write in the Where condition of SQL will often use the column, if often used to consider building indexes (just consider, but also the main problems below)

2. The leftmost prefix principle: MySQL will always match right until it encounters a range query (>, <, between, like) to stop matching, such as a = 1 and B = 2 and C > 3 and D = 4 If the index of the established (A,B,C,D) order, D is used Not indexed, if the establishment (A,B,D,C) of the index can be used, the order of a,b,d can be arbitrarily adjusted. = and in can be disorderly, such as a = 1 and B = 2 and c = 3 build (a,b,c) index can be arbitrary order, MySQL query optimizer will help you to optimize the form of the index can be recognized

3, index selectivity and prefix index: The index file itself consumes storage space, while the index can aggravate the load of inserting, deleting and modifying records, in addition, MySQL also consumes the resource Maintenance index at runtime, so the index is not the more the better; indexing a column takes into account the selectivity of that column's data. At the same time, there is an index-selectivity-related index optimization strategy called the prefix index, that is, the column prefix instead of the entire column as the index key, when the current prefix length is appropriate, you can make the prefix index selectivity near the full-column index, and because the index key shortened and reduce the size of the index file and maintenance costs

4, the use and optimization of explain (rows is the core indicator)

1, note extra column: If it is useing filesort or
2, see there is no use to the expected index, the scan of rows is not a lot, if it is a lot, the index is not good, we may want to modify the index

5. Simple SQL optimization

1. Choosing the right storage engine

2, the establishment Index, the index establishment reference above

3, avoid the use of select*, only to check the data we need to reduce the transmission of network data

4. Design a self-increment ID for each table

5, try to use not null,null need extra space, and, when you compare, your program will be more complex

6. Split large Delete and insert/to avoid large transactions

7, each field as small as possible, for most of the database engine, hard disk operation may be the most significant bottleneck. So, getting your data compact can reduce disk access

8. Sub-database sub-table (distributed data Base middleware)

Knowledge of MySQL database features