Small drop class MySQL related interview questions summary

MySQL > transaction isolation level MySQL > transaction isolation level

• Isolation level of transactions: Low to high (Uncommitted reads => Committed reads => Repeatable reads => serializable)

  • Read Uncommitted: Reads Uncommitted data. Changes in a transaction can be Read even if they are not committed. Committed data is dirty.

    -- Dirty reading examples
    -- Operation little Sister configured a paid course activity. The original price of 500 yuan course was configured to 50 yuan, but the transaction was not submitted. At this moment, you just saw that the course was so cheap and ready to buy, but the operation sister immediately rolled back the transaction, reconfigured and submitted the transaction, and when you prepared to place the order, you found that the price changed back to 500 yuan
    Copy the code

  • Read Committed: When a transaction starts, only the Committed transactions can be Committed. Running the same query twice in a transaction can result in different results. Also known as unrepeatable reads, also known as magic Read problems!

    -- Examples of non-repeatability:
    -- Lao Wang had 1000 points in Xiaodi class and was going to exchange the Interview Special Course. There were 1000 points in the database, but Lao Wang's girlfriend also logged in elsewhere and exchanged 1000 points for SpringCloud Micro Service Special Course and submitted the transaction before Lao Wang; When the system helped Lao Wang to exchange the interview Special Courses, it found that the points were expected to be lost, and the exchange failed.
    
    -- Transaction A read the data in advance, and then transaction B updates the data and commits the transaction. When transaction A reads the data again, the data has changed!
    Copy the code

  • Repeatable Read: default transaction isolation level of mysql to solve the problem of dirty and unrepeatable reads. Phantom Read problems exist.

    Magic reading problem: MySQL’s InnoDB engine automatically helps us with MVCC, i.e. multi-version concurrency control!

    -- Magic reading examples:
    - Lao wang 1000 points in the little drops of classroom and get ready to go for the interview subject curriculum, query the database really have 1000 points, Lao wang's girlfriend, and other places at the same time, the login for the first interview seminar, Lao wang's transaction commit findings suggest buying has existed for the course, before reading is useless, like the illusion.
    Copy the code

  • Serializable: Solve dirty read, unrepeatable read, illusable read, can guarantee transaction security, but force all transactions to execute in serial (i.e., one transaction executes, other transactions need to queue), so concurrency efficiency is low!

In MySQL, the default transaction isolation boundary is repeatable reads. To solve the problem, InnoDB uses MVCC(Multi-version concurrency control) [based on optimistic locking].
-- MVCC(Multi-version concurrency Control) uses two hidden columns (create version number and delete version number) at the end of each piece of data. Each transaction starts with a ** incremented current transaction version number **!

- MVCC new
begin; -- Assuming the current transaction version =1
insert into user (id,name,age) values (1, "zhang",10); -- Added, current transaction version number is 1
insert into user (id,name,age) values (2, "bill",12); -- Added, current transaction version number is 1
commit; Commit transaction
Copy the code

Insert into create_version; insert into create_version;

-- MVCC delete: The delete operation directly updates the deleted version number of row data to the version number of the current transaction
begin;Assume the current transaction version =3
delete from user where id = 2;
commit; Commit transaction
Copy the code

-- MVCC update operation: use delete + add mode to implement, first mark the current data as delete, and then add a new data
begin;Assume the current transaction version =10
update user set age = 11 where id = 1; Update, the current transaction version number is 10
commit; Commit transaction
Copy the code

-- MVCC query operation:
begin;Transaction ID = 12
select * from user where id = 1;
commit; Commit transaction
Copy the code

Query Operation To avoid querying old data or data that has been changed by other transactions, the following conditions must be met:

The version of the current transaction must be greater than or equal to the creation version create_version

2. The version of the current transaction must be smaller than the deleted version delete_version, or the deleted version delete_version must be NULL

That is: (create_version < = current_version < delete_version) | | (create_version < = current_version && delete_version – = NULL), In this way, you can avoid querying data modified by other transactions. In the same transaction, repeatable read is realized!

The result should be:

• MySQL 5.5 uses the MYISAM engine, and 5.5 and later uses the InnoDB engine
• Differences between the two:

Refer to my blog MySQL index analysis and related interview questions

• The ORDER of query commands is: SELECT=> FROM=> WHERE=> GROUP BY => HAVING => ORDER BY

select See which result fields
from -- Which table to query from
where -- Preliminary filtration conditions
group by -- Grouping after filtering [key point]
having -- Secondary filtering of grouped data [key point]
order by In what order to sort

SELECT * num FROM chapter GROUP BY video_id  HAVING  num >10 ORDER BY video_id DESC
Copy the code

• Why timestamp only goes to 2038

MySQL timestamp is 4 bytes and the maximum value is 2 ^ 31 minus 1.
2147483647

B: Beijing time is:
2038- 01- 19 11:14:07
Copy the code

Problem: online database of a commodity table data volume of tens of millions, do deep paging when the performance is very slow, what are the optimization ideas?

-Symptom: Ten million level data is normal, such as data flow, logging, etc., the normal deep paging database will be very slow-Slow cause: SELECTMySQL > select * from product limit N,M - MySQL > select * from product limit N,M - MySQL > select * from product limit N,M - MySQL > select * from product limit N,M - MySQL > select * from product limit N,MCopy the code

Solution:

-- 1, can use the back-end cache Redis, front-end cache localstorage
Select * from ElasticSearch
Mysql > select * from 'mysql'
For example, title and cateory are set as the composite indexes of the table to improve query efficiency
select title,cateory from product limit 1000000.100
  
-- 4, if the ID is increment and there is no intermediate delete data, use sub-query optimization to locate the ID of the offset position
-- This method is time-consuming because the first 1000000 rows need to be retrieved, and then the 1000000-1000500 target data needs to be retrieved
select * from oper_log where type='BUY' limit 1000000.100; - 5 seconds
Select id from first 1000000 rows and select id from first 1000000 rows
select id from oper_log where type='BUY' limit 1000000.1; 0.4 seconds -
- to do a subquery, because increasing is a primary key, so id > = 1000000 rows id value, this is equivalent to skip scan before 100000 rows of data, directly from the article 100 started to retrieve back 1000000 data
select * from oper_log where type='BUY' and  id> =(select id from oper_log where type='BUY' limit 1000000.1) limit 100; 0.8 seconds -
Copy the code

• Isolation is achieved with locks

• Persistence is achieved through the redo log

• Atomicity through undo log rollback log: MySQL database in InnoDB storage engine, also use undo log to achieve multi-version concurrency control

  • When a record is deleted, the undo log records a corresponding INSERT record.
  • When you insert a record, the undo log records a corresponding DELETE record.
  • When a record is updated, the undo log records a corresponding update record.

• MySQL ensures consistency through atomicity, isolation, and persistence. C(consistency) is the end, A(atomicity), I(isolation), D(persistence) is the means, is to ensure consistency, the means provided by the database. The database must implement AID to achieve consistency.

For details about MySQL locks, see Learn about MySQL locks