Interview title: MySQL Database not seen 7

MySQL Database interview SummarySeptember 04, 2017 00:11:40Hits: 151Combined with the online great God also have their own interview experience, collect the summary of MySQL interview questions, to facilitate their own preparation for the interview; MySQL one will never review, try to summarize, almost can go to interview (personal opinion) 1, database transactions four characteristics and meaning

• The database transaction transanction four basic elements that are correctly executed. ACID, Atomicity (atomicity), consistency (correspondence), isolation (isolation), persistence (durability).
• Atomicity: All operations in the entire transaction, either complete or complete, are not likely to stall in the middle of the process. When an error occurs during execution, the transaction is rolled back (Rollback) to the state before the transaction begins, as if the transaction had never been executed.
• Consistency: The integrity constraints of the database are not compromised until the transaction begins and after the transaction has ended.
• Isolation: The isolated state performs transactions so that they appear to be the only operations that the system performs within a given time. If there are two transactions that run at the same time and perform the same function, the isolation of the transaction will ensure that every transaction in the system considers only that the transaction is in use by the system. This attribute is sometimes called serialization, and in order to prevent confusion between transactional operations, the request must be serialized or sequenced so that only one request is used for the same data at a time.
• Persistence: After the transaction is completed, changes made to the database by the firm are persisted in the database and are not rolled back.
• It is also common to ask the four levels of a transaction to understand the premise of a transactional concurrency problem: Concurrency problems can occur when multiple transactions use the same data at the same time.

2, the isolation level of the transaction

• To balance the problem of transaction concurrency with the efficiency of the database, a variety of isolation levels have been designed:

  • There are 4 isolation levels for the database transaction, from low to high, read UNCOMMITTED (reading uncommitted), Read committed (read commit), repeatable read (repeat read), Serializable (serialization), These four levels can solve the problems of dirty reading, non-repeatable reading, and Phantom reading one after the other.

        隔离级别            脏读   不可重复读   虚读（幻读）    Read uncommitted    √       √          √    Read committed      ×       √          √    Repeatable read     ×       ×          √    Serializable        ×       ×          ×    备注： √: 可能出现    ×: 不会出现

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

Add: Story

1. READ UNCOMMITTED not submitted

   • The company paid a salary, the leader of the 5000 yuan to the long-expensive account, but the transaction did not submit, and long expensive to check the account, found that the pay has been to account, is 5000 yuan whole, very happy. But unfortunately, the leader found that the amount of money sent to long is not right, is 2000 yuan, and then quickly rolled back to business, modify the amount, the transaction will be submitted, the last long your actual salary only 2000 yuan, long you empty joy.

   • The above situation, that is what we call dirty Read, two concurrent transactions, "transaction A: Leaders to long your pay", "Transaction B: Long you query Payroll account", transaction B read the transaction A has not yet committed data.

   • When the isolation level is set to read UNCOMMITTED, dirty reads can occur and how to avoid dirty reads, see the next isolation level.

     1. Read Committed reading Commit

   • Long you take the payroll card to spend, the system read to the Cary really have 2000 yuan, and at this time her wife Xie Bigfoot also just in the online transfer, Xie Bigfoot put the salary card of 2000 yuan to his account, and in the long before you submit the business, when the long your deduction, the system check to long your pay card has no money, deduction failure, Long you very puzzled, obviously card money, why ...

   • The above situation, that is what we call non-repeatable read, two concurrent transactions, "transaction A: Long expensive consumption", "Transaction B: Long expensive wife Xie Bigfoot online transfer", transaction a read the data in advance, transaction B immediately updated the data, and committed the transaction, and transaction a read the data again, the data has changed.

   • When the isolation level is set to read Committed, dirty reads are avoided, but may cause non-repeatable reads. The default level for most databases is read committed, such as SQL Server, Oracle. To resolve the issue of non-repeatable reads, see the next isolation level.

     1. REPEATABLE READ repeat

   • You can avoid non-repeatable reads when the isolation level is set to repeatable read. When the long you take the payroll card to spend, once the system began to read the Payroll card information (that is, the start of the transaction), long expensive wife can not be modified, that is, long expensive wife can not be transferred at this time.

   • Although repeatable read avoids non-repeatable reads, it is possible to have phantom reads.

   • Xiedajiao View long-expensive payroll card consumption records. One day, she was inquiring into the total consumption amount of the letter of the long month (select SUM (amount) from transaction where month = this month) is 80 yuan, and the long expensive at this time is good outside Hu eats the sea plug to pay at the checkout counter, consumes 1000 yuan, That is, a new 1000-dollar consumption record (insert transaction ... ), and submitted a transaction, and then Xiedajiao will be long your monthly consumption of the details of the printing to A4 paper, but found that the total consumption of 1080 yuan, Xie Bigfoot is very surprised, thought there was an illusion, the Phantom Read this produced.

   • To put it simply, Phantom reads when a user reads a range of data rows (not the same row of data), another transaction inserts a new row within that range, and when the user reads the data row of that range, a new phantom row is found.

   • Serializable, serialization. The most stringent transaction isolation. Requires transaction serialization execution, which is executed one after another and cannot be executed concurrently. There is no problem, but the efficiency is too low, in practice, it is rarely set.

3. The difference between Drop,delete and truncate

• This is very long, generally just remember that delete is a DML statement and is not automatically committed. Drop/truncate are DDL statements that are automatically committed after they are executed.
• TRUNCATE and delete only delete data, and drop deletes the entire table (structure and data).
• Speed performance, drop > Truncate > Delete
• Specific Link: http://www.cnblogs.com/8765h/archive/2011/11/25/2374167.html

MySQL engine

• There are various engines in the MySQL database (different versions of the MySQL database support the engine), familiar with the various engines in the software development to apply the engine, so as to develop high-performance software
• Links: http://blog.csdn.net/gaohuanjie/article/details/50944782
• Default InnoDB: The storage engine provides a row-level lock for the MySQL table with ACID transaction support, system crash repair capability, and multiple version concurrency control (that is, MVCC multi-version Concurrency Control), which supports self-growing columns (auto_ increment), the value of the self-growth column cannot be empty, and if it is empty at the time of use, it will automatically increment from the existing value, and if it is larger than the current one, save the value directly; The engine storage engine supports foreign keys (foreign key), and the table on which the foreign key resides is called a child table and depends on the table called the parent table. The engine is the default storage engine in the MySQL database after 5.5.
• Remember two, MyISAM (search will be better)

Index aspects

• Benefits of creating indexes: Create a unique index that guarantees the uniqueness of each row of data in a database table. Increase search speed, improve query performance and improve system performance
• Cons: It takes time to create indexes and maintain indexes, and this time increases as the amount of data increases. The index needs to occupy physical space, in addition to the data table for the data space, each index also occupies a certain amount of physical space, if you want to establish a clustered index, then the space will be larger. When the data in the table is added, deleted and modified, the index is also maintained dynamically, which reduces the maintenance speed of the data.
• Index failure Condition:
• 1. Indexes do not store null values, more accurately, single-column indexes do not store null values, and composite indexes do not store all null values. The index cannot store null, so the IS null condition for this column, because there is no null value on the index and cannot be used to index, only full table scan.
• 2. Not suitable for columns with less key values (columns with a large number of data repeats),
• 3. The leading fuzzy query cannot take advantage of the index (like '%XX ' or like '%xx% ')
• 4. The index fails in several cases
  
  • 1. If there is or in the condition, even if the conditional index is not used (which is why the use of or is minimized), if you want to work with or, and you want the index to take effect, you can only index each column in the or condition.
  • 2. For multi-column indexes, not the first part of use, the index is not used
  • 3.like query starts with%
  • 4. If the column type is a string, be sure to use quotation marks in the condition to reference the data, otherwise you will not use the index
  • 5. If MySQL estimates that using a full table scan is faster than using an index, the index is not used
• Links: http://www.cnblogs.com/binyue/p/4058931.html

MySQL performance boost

• SQL statement Efficiency improvement: Too many, recommend the index failure of several cases,
• Links: http://www.cnblogs.com/lykbk/p/aefweere45454545454.html
• MySQL performance improvement: Still too much, Link: http://www.jianshu.com/p/5dd73a35d70f
• Personal Summary:
  
  • 1, search engine selection, mysql default InnoDB (support transactions), you can choose MyISAM (with B-tree algorithm query) and other different engines
  • 2, the server hardware upgrade
  • 3, Index aspects
  • 4, build the table when possible to use Notnull
  • 5, the field as long as possible fixed length
  • 6, Vertical separation (many fields are divided into several tables), horizontal separation (the big data table into several small orders of magnitude, divided into several tables, but also can be divided into several databases, using the idea of clustering)
  • 7, optimized SQL statement (query execution slow SQL statement))
  • 8. Add appropriate stored procedures, triggers, transactions, etc.
  • 9, the design of the table to conform to the three paradigms.
  • 10. Read/write separation (master-slave database)

A simple understanding of the three paradigms of database

• Links: http://blog.csdn.net/z1002137615/article/details/50836263

Interview title: MySQL Database not seen 7