Undo Log,Redo Log, and Binlog

  • MySQL isolation level, consistency view, undo log, redo log, and binglog MySQL index structure, back table, left-most prefix principle
  • Mysql > innoDB > MyISam 2. InnoDB index implementation, unique index, overwrite index, prefix index, joint index, leftmost matching principle; 3. When and how to select an index; 4. Principle of MVCC, redo, undo, rollback section; 5. What are auto-add lock, shared lock, exclusive lock, intention lock, insert intention lock, record lock and gap lock?
  • Said the undolog redolog 9, MySQL how to ensure that the redo log and binlog data is consistent, if a SQL execution is slow, you can analyse the reason? 10. Why did the database select the wrong index
  • 5, MVCC binlog, redolog, undolog is what, what role? (1) Undolog, also known as rollback log file, is mainly used for transaction execution failure, rollback, and MVCC for the historical version of the data view. InnoDB engine is implemented by engine layer. It is a logical log that records data changes before they are changed. For example, “if id=’B’ is changed to id=’ B2′, undo log will be used to store records with ID =’B'”. When a data needs to be updated, the records before modification are stored in Undolog. If the modification is abnormal, undo logs are used to implement the rollback operation to ensure the consistency of transactions. After the transaction is committed, the undolog cannot be deleted immediately. Instead, it will be put into the linked list to be cleared. The corresponding Undolog can be cleared only when it is determined that there is no use of the version information. It holds a version of the data prior to the transaction, which is used for rolling back, and can provide multi-version concurrent control read (MVCC), or unlocked read. (2) redoLog is a redoLog file that records the value after data modification and is used for persistence to disk. The redo log consists of two parts: an in-memory redo log buffer, which is volatile; The second is a persistent redo log file on disk. Implemented by the InnoDB engine at the engine layer, it is a physical log, which records the information about changes to the physical data page, such as “what changes happened to the content of a data page”. When a piece of data needs to be updated,InnoDB will first update the data, then record the redoLog in memory, and then find a time to execute the redoLog operation to a file on disk. I log whether the commit is successful or not. If you roll back, I log the rollback changes as well. It ensures the persistence of transactions. (3) MVCC multi-version concurrency control is a way to realize isolation level in MySQL based on optimistic lock theory, which is used to realize read committed and repeatable read isolation level. In MySQL, two fields are added to each row of data in the table: the transaction ID of the row that was last modified, and a pointer to the rollback segment of the row. Read View evaluates the visibility of rows and copies a list of active transactions in the current system when a new transaction is created. This means a list of other transaction ids that should not currently be seen by this transaction. Id =’ B2 ‘; id=’ B2 ‘; id=’ B2 ‘; The binlog file is appended to a physical file of the specified size. If the current file is full, a new file is created. Generated: When a transaction is submitted, the SQL statement in the transaction is recorded in binlog in a specified format. For replication and recovery In master/slave replication, the slave library uses the binlog on the master library to replay (perform the modification logic recorded in the log) to achieve master/slave synchronization. If the service data is inconsistent or incorrect, use binlog to restore the service data.
  • Overwrite index, transaction ACID how to implement (MVCC), how to rollback (according to undolog) 10 What is a back table?
  • Are binlog and redolog logs clear? What are the different storage engines that mysql has and what are the differences between them? When does mysql index fail? How does mysql master/slave synchronization work? What are the processes? If there is a new machine to be added to the slave machine, how to process. What is the difference between optimistic and pessimistic locks? Is the binlog pushed by the master or pulled by salve?
  • How is Spring’s @Transactional implemented? The config file turns on the annotation driver, marking relevant classes and methods with the @Transactional annotation. (2) When spring starts, it will parse and generate the relevant beans. At this time, it will look at the classes and methods that have the relevant annotations, generate proxies for these classes and methods, and configure and inject them according to the relevant parameters of @Transaction. This removes the relevant transaction from the broker for us (turning on the normal commit transaction, rolling back the exception transaction). (3) The real database layer commits and rolls back transactions through binlog or redo log
  • How can I Locate isolation Level Slow query? How to optimize? What kind of case did you meet and how did you solve it? The project uses the separate database and table, will the separate database improve the performance? Design of sub – database sub – table? Have there been inconsistencies in distributed transactions? Why is that? How to solve it? Is there any way to avoid it? How? What do I do if I see it? When is manual access needed? Mysql InnoDB mysql InnoDB primary key Can primary keys be null? How many are allowed? What’s the difference between a primary key and a non-primary key? Index storage format? Joint index failure problem? Index failure problem? Mysql > alter table mysql > alter table mysql Back to the table? What does the B+ tree leaf node store? Why not use AVL trees? Database lock, optimism, pessimism, Record Lock? Next, the key lock? Mysql MVCC double write What is the sequence between binlog and redo log? How to deal with a failure to write? Current read and snapshot read? Transaction isolation level? Online SQL optimization experience? Distributed transactions? In what ways? What other ways do you know? How do you do that? Have you ever thought about implementing it yourself? What pits to avoid? How is cross-library aggregation implemented? Paging? What page? The next page? How to implement top implement non-stop capacity expansion? Avoid hot and cold? Expansion without shutdown? Non-stop table expansion? Cross-library transactions? Why is sub-database sub-table so designed? How to do data growth? How to expand capacity? What about uneven data? How to separate hot and cold data? How do you do aggregation? How do you do cross-library aggregation, how do you do queries? How do I do cross-library paging? Mysql group mode One master, many followers? Why is that? How to ensure strong consistency? To address read-write separation? Is it for the sake of one master? What if the main library crashes? From the library? How do distributed transactions work? How does that work? How do you do that? Have there been transaction inconsistencies? Why is that? How did it work out? How to do access requests surge? How to relieve pressure?
  • What optimizations have MySQL made? Overwrite index? What is the difference between the two parameters of limit? Other methods of MySQL page tuning What do you do with SQL tuning? Index data structure? What are the transactions of MYSQL? Which ones are commonly used? How are key SQL optimizations optimized? Why is the performance bad? Do you have to have a primary key? What kinds of indexes does MySQL have? What is the difference between a clustered index and a secondary index? Index data structure? Why use B+ trees? MVCC? How did the version number change? How to determine the version of the updated data? How does unreal read solve? How does an SQL index go? What would MYSQL choose? MySQL > insert into index (s); What kinds of logs does MySQL have? What are redo logs mainly used for? Is it 100% off after submission? MySQL buff memory for redo log
  • Select * from mysql where autocommit=20; Three types, row (single row), table (single table), page (set of data). 6, 1 million pages of data, paging query: jump from 1 million to 1 million 01 pages the fastest, or directly input 1 million the fastest, why? 7. How to use minimum-grained row locking: parameters?
  • How to subscribe to Binlog of sub – database sub – table? 6. How to solve the primary key conflict in the data source of sub-database sub-table? 7, how to ensure the downstream consumption order of Binlog?
  • What storage engines are commonly used in MySQL? What are the differences between them? 2, When does MySQL index fail? (1) If the query condition starts with %, the index is invalid; The index is valid when the like prefix does not have % and the suffix has %. (2) When the OR keyword is used, no index is used before and after the OR statement or only one query field around the OR keyword is an index, the index will be invalid. Only when both query fields around the OR keyword are indexes, the index will take effect. (3) When the combined index is used, if the query condition does not include all the fields of the combined index or the query condition is not the first field on the left of the combined index, the index will be invalid. (4) Implicit transformation of data types. For example, if the data type of an index field is VARCHAR and the query content is 123, if no quotation marks are used, the index may be automatically converted to int. This invalidates the index and generates a full table scan. (5) Use not and operator on index field (6) calculate operation on index field, use function on field, index invalid. 3. What is the difference between InnoDB and MyISam? 4, MySQL index model; MySQL > select * from ‘master’; What are the processes? If there is a new machine to be added to the slave machine, how to process. 6. The difference between optimistic lock and pessimistic lock? 7, Is the binlog pushed by the master or pulled by salve?
  • Database, MVCC implementation, okay? Where is the multi-version implementation? B+ tree index? How to solve the problem of unrepeatable reading? MySQL master/slave synchronization What is illusory? How to use MySQL? Add limit, execute the plan, whether there is a sort, whether the sort can be resolved using an overwrite index, do not increment the primary key externally