Recently idle to have nothing to do, sort out a wave of Mysql data, welcome to watch!!

What is the difference between InnoDB and MyISAM in Mysql?

(1) InnoDB storage engine supports transactions while MyISAM does not; InnoDB supports row-level locking while MyISAM only supports table-level locking.

InnoDB implements row locking by locking the index, which means InnoDB only uses row level locking when retrieving data through the index condition, otherwise it uses table level locking! Row-level locks consume more resources per capture and release than table-level locks.

There are two modes of table-level lock: table-shared read lock and table-exclusive write lock. MyIASM does not block read requests from other users to the same table, but blocks write operations to the same table. A write to a MyISAM table blocks other users from reading or writing to the same table.

(3) InnoDB supports foreign keys while MyISAM does not;

(4) InnoDB does not store the exact number of rows in the database table, whereas MyISAM does;

In other words, InnoDB scans the entire table to calculate the number of rows, whereas MyISAM only reads the number of rows saved (it maintains an internal calculator to retrieve them directly). [Note] : When the count() statement contains the WHERE condition, the operation is the same for both tables. This is one of the cases described above where InnoDB uses table locking.

For SELECT, UPDATE, INSERT,delete operations:

MyISAM is a better choice if you perform a large number of selects (since MyISAM does not support transactions, MySQL provides high-speed storage and retrieval, as well as full-text search capabilities);

If you are doing a large number of INSERTS or Updates, you should use InnoDB tables for performance reasons (because InnoDB supports transactions and can roll back any errors in some columns, whereas MyISAM does not).

What are the four features of InnoDB storage engine?

Insert buffer, second write, adaptive hash index, preread

(1) Insert buffer:

In general, the primary key is the unique identifier of a row. Typically, rows in an application are inserted in ascending order of primary key. Therefore, insertion of clustered indexes is generally sequential and does not require random reads from disk. Because, for this kind of insertion, it’s very fast.

If the index is non-clustered and not unique, data storage for leaf nodes of non-clustered index is not sequential during the insertion operation. In this case, non-clustered index pages need to be accessed discreetly, and the existence of random reading leads to the performance degradation of the insertion operation. (This is because the nature of B+ trees determines the discreteness of non-clustered index inserts.)

Insert buffer For non-clustered index inserts and updates, instead of directly inserting into the index page each time, it first determines whether the inserted non-clustered index page is in the cache pool. If in, insert directly; If not, the first in a insert buffer, like cheating database this aggregation index has been inserted into the leaf node, and then insert the buffer at a certain frequency and non clustered index pages of child node merging, then usually can insert multiple merge into one operation (because in an index page), This greatly improves the performance of insert and modify operations on non-clustered indexes.

Two conditions must be met for the use of insert buffers:

Indexes are secondary indexes Indexes are not unique (secondary indexes cannot be unique because we do not look up the case of the index page when it is inserted into the insert buffer. If you look it up, you will definitely see discrete reads again, and the insertion buffer is meaningless. Existing problem: white whoring data

In write-intensive cases, the insert buffer takes up too much buffer pool memory. By default, it can take up up to 1/2 buffer pool memory.

(2) Write twice

When the database is down, a situation can occur where the database is writing a page that is only partially written, which is called partial write failure. When a write failure occurs, the page is restored through a copy of the page and the log is redone. This is a double write.

Doublewrite steps:

When a series of mechanisms (main, checkpoint, etc.) trigger a refresh of dirty pages in the data buffer pool, the dirty pages are copied to the doubleWrite Buffer in memory by memcpy instead of being written directly to disk. The doubleWrite buffer is then used to write the data to the physical disks of the shared tablespace in two consecutive 1MB volumes. Then call fsync immediately to synchronize the dirty pages to disk. In this process, the doubleWrite pages are stored consecutively, so the writes to disk are sequential, with high performance. After the doubleWrite pages are written, the pages in the DoubleWrite buffer are written to each tablespace file, and the writes are discrete. If the operating system crashes while writing a page to disk, the InnoDB storage engine can find a copy of the page from doubleWrite in the shared table space, copy it to the table space file, and apply the redo log to complete the recovery process. You don’t worry about corrupt pages in the table space because you have copies.

(3) Adaptive hash index

The InnoDB storage engine monitors the search for indexes on the table, and if it sees a speed increase in creating a hash index, it will create a hash index, so it is called adaptive. The adaptive hash index is constructed from the BUFFER pool’s B+ tree, so it is fast to create. Instead of hashing the entire table, the InnoDB storage engine automatically hashes certain pages based on the frequency and mode of access.

(4) Preread

InnoDB provides two ways to read ahead. The Linear Read Ahead is controlled by the innodb_read_ahead_threshold parameter. When you read an extent threshold page in a row, The preread of the next extent 64 pages is triggered. The other is Random read-ahead, which is controlled by the innodb_random_read_ahead parameter. When you read a set number of pages in a row, it triggers the reading of the rest of the extent. InnoDB prereads are done asynchronously using background threads.

3. How does InnoDB guarantee the four features of transactions?

InnoDB, MySQL’s storage engine, uses the redo log for consistency and persistence, the undo log for atomicity, and various locks for isolation.

Redo log, undo log, and binlog in MySQL?

There are six log files in MySQL, which are:

Redo log undo log binary log errorlog slowquery log general log relay log

Redo log and rollback log are closely related to transaction operation, and binary log is also related to transaction operation. How is the transaction of white prostitute information realized through the log? Undo records the value before the data is modified. It can be used to rollback rollback when the transaction fails. Redo records the changed value of a data block. This log file can be used to restore the updated data of successful transactions that were not written to the data file. That is,

For example, if the database is DOWN and there are two transactions, one is committed and the other is being processed. When the database is restarted, it is rolled forward and rolled back against the log, writing the changes of committed transactions to the data file, and restoring the changes of uncommitted transactions to the state before the transaction started. All transactions that have been prepared but have not been committed will be rolled back using the undo log. Redo log: The redo log records changes made during each change operation before the transaction is committed. The main reason is to prevent dirty pages from being written to disk at the point of failure. When mysql service is restarted, redo is performed according to the redo log to achieve transaction persistence. (role)

Before the transaction commits, you only need to persist the Redo Log. You do not need to persist the data. When the system crashes, the redo Log can be used to restore all data to the latest state. (Persistent: write redo log to cache, then flush (fsync) to disk)

The redo log is a physical log that records the changes made to each page. After the transaction starts, the Innodb storage engine writes the redo log to the innodb_log_buffer. The innoDB log buffer is then flushed to disk in the following three ways.

The Master Thread refreshes Innodb_log_buffer to the redo log file once per second. The redo log is flushed to the redo log file on each transaction commit. When less than half of the redo log cache is available, the redo log cache is flushed to the redo log file. When a transaction commits, all logs of that transaction must first be written to the redo log file for persistence.

1. Content:

The redo log is written in physical format to the redo log file in order.

2. When was the redo log written to disk?

It’s written down after things have started.

The redo log is written to the redo log file during the transaction. The redo log is written to the redo log file during the transaction. (The redo logs are first written to the cache, and the logs in the log buffer are flushed to the disk. There are three ways to write the redo logs to the disk.)

[Note] The Innodb storage engine will refresh the redo log cache to redo log files every second even if a transaction has not been committed. This is important to know because it explains the short commit times of even the largest transactions.

3. When to release:

Once the dirty pages of the transaction have been written to disk, the redo log is done and the redo log space can be reused (overwritten).Copy the code

Undo log: It holds a version of the data before the transaction occurred and can be used to roll back. It also provides multi-version concurrency controlled reads (MVCC), or non-locked reads. (role)

If a transaction exception occurs and needs to be rolled back, the log needs to be rolled back. A rollback log is different from a redo log in that it is a logical log, and changes to the database are logically cancelled. When a transaction rolls back, it is actually doing the opposite of what it did previously. For each INSERT, the InnoDB storage engine completes a DELETE; For each UPDATE, the InnoDB storage engine performs a reverse UPDATE.

Uncommitted transactions and rolled back transactions also generate redo logs. The InnoDB storage engine will redo all transactions including uncommitted transactions and rolled back transactions, and then roll back those uncommitted transactions through the rollback log. Using this strategy complicates persistence by requiring the rollback log to be written to disk before the redo log. To reduce complexity, the InnoDB storage engine uses the rollback log as data, and the operations recorded in the rollback log are also recorded in the redo log. This allows the rollback log to be cached as data without having to be written to disk before rewriting the log. 1. Content:

Unlike the Redo log, a logic-format log, when undo is executed, only logically restores the data to the state before the transaction, rather than from the physical page.

2. When is it produced?

Before the transaction starts, the current version of the undo log is generated. Undo also generates redo files to ensure the reliability of the undo log

3. When will it be released?

After the transaction commits, the undo log is not immediately deleted. Instead, the purge thread decides whether the undo log space is cleared by another transaction using the previous version of the table in the undo segment.

Binary log (bin log) :

For replication, in master-slave replication, the slave library uses the binlog on the master library to replay, to achieve master-slave synchronization. Point-in-time restore for a database.Copy the code

2. Content:

Logs in logical format can simply be thought of as SQL statements in executed transactions.

However, it is not as simple as the SQL statement, but includes the reverse information of the SQL statement executed (add, delete, modify), which means delete corresponds to delete itself and its reverse INSERT; Update indicates the version information before and after the update is executed. Insert corresponds to information about delete and insert itself.Copy the code

Some things will become clear after the binlog is parsed using mysqlBinlog. Therefore, oracle flashback can be achieved based on binLog, which relies on logging in binLog.

3, when to produce:

When a transaction is committed, the SQL statements in the transaction (one transaction may correspond to multiple SQL statements) are recorded in the binlog in a certain format at a time. The obvious difference with the redo log is that the redo log is not necessarily flushed to disk when a transaction commits. The redo log is written to disk gradually after a transaction has begun. So commit is fast for even large transactions, but with bin_log turned on, commit may be slower for larger transactions. This is because the binlog is written at the time of the transaction commit, which can be verified by testing.Copy the code

4. When to release:

By default, the keepalive time of binlog is set by the expire_LOGs_days parameter. That is, inactive log files that are generated for more than the number of days specified in expire_LOGs_days are automatically deleted.

The difference between binlog and redolog? There is also a binary log in the MySQL database that is used for point-in-time restore and master-slave replication. On the face of it, it is very similar to a redo log, logging an operation on a database. But the substance is very different. First, redo logs are generated in InnoDB storage engine layer, while binary logs are generated in MySQL database layer. Second, the two types of logging have different forms of content. A binary log is a logical log that records the corresponding SQL statements. The redo log is a physical log that records per-page changes. In addition, the two log records write to disk at different points in time, with the binary log writing only once after the transaction commits, and the redo log writing continuously during the transaction.

3. What is a transaction?

A transaction is a sequence of operations that either all or none of them are executed, and it is an indivisible unit of work. Transaction is the unit of database to maintain data consistency. At the end of each transaction, data consistency can be maintained.

4. What are the four features of database transactions?

Atomicity, consistency, isolation, durability (ACID)

Atomicity: means that the entire database transaction is an indivisible unit. The transaction is only successful if all database operations in the transaction are successful. If any SQL statement in the transaction fails, the executed SQL statement must also be undone, and the transaction state returns to the state before the transaction was executed. Consistency: Consistency refers to the fact that transactions must move the database from one consistency state to another. Transaction integrity constraints are not broken before and after the transaction begins. Isolation: The effects of one transaction are not visible to others until the transaction commits. This implements persistence through locking: once a transaction is committed, the result is permanent. (Isolation is achieved by locking; Atomicity, consistency, and persistence are done through the redo and undo of the database.

5. How many problems can occur if transaction isolation is not considered?

The requirement of transaction isolation can be implemented through locking, allowing transactions to work concurrently. Because of transaction isolation requirements, locks can cause three kinds of problems: lost updates, dirty reads, and non-repeatable reads.

Lost updates: White whine data where one transaction is accessing the modified data at the same time that another transaction is accessing the modified data, and the two transactions are unaware of each other’s existence. If transaction B changed the data once before transaction A changed the data, transaction A could only query the false data and the update operation was lost.

Solution:

Pessimistic locking: locking. It is recommended to add exclusive locks when updating data at the last step, instead of locking at the beginning. At the end of the update, first do a lock query to confirm that the data has not changed. If it has not changed, then update the data, otherwise fail. Be sure to do the lock query validation, because if you do not lock, there is a chance that the data will change while you are doing the validation.

Optimistic locking: Implement using version control.

Dirty read:

When a transaction reads uncommitted data from another transaction, the read is a dirty read.

Solution: Change the transaction isolation level of the database to Read Commited.White piao data

Unrepeatable read:

Non-repeatable read refers to a transaction that reads the same data multiple times. Before the transaction ends, another transaction also accesses and modifies the same data. In this case, the data read by the first transaction may be different between the two reads due to the second transaction’s modification. This occurs when the data read twice within a transaction is different and is therefore called non-repeatable reads.

How to avoid it: InnoDB storage engine uses the next-key Lock algorithm to avoid unrepeatable reads. In the next-key Lock algorithm, for index scanning, not only the scanned index is locked, but also the scope of these indexes can be locked. Therefore, insertions in this range are not allowed. InnoDB storage engine’s default transaction isolation level is READ REPEATABLE. Next-key Lock algorithm is adopted to avoid the phenomenon of non-repeatable reads.

The solution: adjust the transaction isolation level of the database to REPEATABLE READ, which does not allow other transactions to modify the data while reading it. The data is consistent regardless of how many times the data is READ during the transaction.

Phantom reads:

A phenomenon that occurs when transactions are not executed independently, such as when the first transaction modifies data in a table that involves all rows in the table. At the same time, the second transaction also modifies the data in the table by inserting a new row into the table. Then, it happens that the user who operated on the first transaction will find that there are still rows in the table that have not been modified, as if it were an illusion.

How to avoid: Repeatable read and higher levels of Repeatable read use gap lock to prevent magic read, that is, lock the front and back gaps of specific data so that data cannot be inserted.

What are the four isolation levels provided by the MySQL database? Read UNcommitted read COMMITTED REPEATable Read: Default isolation level for InnoDB serializable

7. How many types of logs are there? Error log: Records error messages as well as warning messages or correct messages. Query log: Records information about all requests to the database, whether or not they were executed correctly. Slow query log: Set a threshold and record all SQL statements whose running time exceeds the threshold to the log file of slow query. Binary log: Records all operations that make changes to the database. Trunk log Transaction log

Finally, I wish you all an early success in your studies, a satisfactory offer, rapid promotion and salary increase, and the peak of your life. If you can, please give me a triple support yo, we will see you next time

White piao data