Hello, I’m Leo. Currently, I am engaged in Java backend development in Changzhou. This is the second article in the MySQL Learning collation series. This series will be output together with byte, netease, Ali, Tencent, Meituan and Kuaishou. Hope to help more friends as soon as possible into the factory!

After reading this article, don’t say you have learned MySQL.

After reading this article, stop saying that you have learned MySQL.

After reading this article, stop saying that you have learned MySQL.

Small knowledge, big challenge! This article is participating in the creation activity of “Essential Tips for Programmers”.

7. Storage engine

7.1 Differences between InnoDB and MyISAM indexes

  • MyISAM is a non-transactional storage engine suitable for applications with frequent queries. Table lock, no deadlock, suitable for small data, small concurrency.
  • Innodb is a storage engine that supports transactions, suitable for insert and update applications. If designed properly, it is row lock (the biggest difference is in the level of lock), suitable for big data and large concurrency.

7.2 Why InnoDB is The Default Index

  • Innodb supports transactions
  • Innodb supports larger lock granularity than MyISam and supports concurrency

8. The index fails

  • A like query starts with %
  • <>
  • The index is not used before OR after the OR statement
  • An implicit conversion occurs to the data type
  • When the IS NULL function is used, the index cannot be utilized, only the full table scan can be performed. (Note other functions as well)
  • SQL with OR will also fail

Note that varchar must be quoted, otherwise it will be mistaken for an int, even though the query is consistent. However, index failure increases query performance and disk IO overhead.

9. The transaction

9.1 what is

What is a transaction? A transaction is the same as a bank’s requirement. If the power is interrupted during execution or the execution is stopped if the condition is not met, all SQL statements that have been executed are rolled back. This means that the transaction will either all succeed or all fail! The properties of transaction ACID ensure that the bank does not lose your money

9.2 the ACID

  • Atomicity: The atomicity of a transaction is that it is either all or nothing, and it is impossible to perform only some of the operations
  • Consistency: Consistency is mainly reflected in data consistency. The transaction is not committed and the data modified by the transaction is not saved in the database
  • Isolation: Changes performed by the current transaction are not visible to other transactions until they are finally committed.
  • Persistence: Once a transaction is committed, the modified data is persisted to the database and will not be lost in the event of a database power failure.

9.3 Implementation Principle of MVCC

MVCC is multi-version concurrency control. By saving a snapshot of the data at a point in time. That is, no matter how long it takes. The data for each transaction execution is consistent. On the contrary! The selected snapshots are also different depending on the transaction start time, so each transaction may see different data for the same table at the same time. (It can be a little confusing without this aspect of the concept)

Different versions of concurrency control implementation, the typical implementation of optimistic lock concurrency control and pessimistic lock concurrency control.

MVCC does this by storing two hidden columns after each row record, one for the time the row was created and one for the time the row expired. Instead of storing a time value, you store the version number of the system. The system version number is automatically increased each time a new transaction is started. The system version number at the transaction start time is also the transaction version number, which is used to query the version number of each row for comparison.

Advantages: The advantage of keeping these two additional system versions is that data does not need to be locked separately, which makes data manipulation easy and performance good. It is also guaranteed that only rows that meet the criteria will be read. Cons: Extra storage per row, more checking for rows, and extra maintenance

The MVCC only works at two isolation levels, Repertable Read and Read Committed. The other two isolation levels are not compatible with MVCC!

Tip: Read uncommitted always reads the latest rows, not rows that do not match the current transaction version. Serializable locks all rows read

9.4 Transaction isolation level

Set transaction isolationlevel // to set the isolationlevel, which takes effect when the next transaction starts

  • Read Uncommitted: Changes in a transaction that are visible to other transactions are dirty reads even when they are not committed. This level causes a lot of problems and is not significantly better in terms of performance than other isolation levels, but lacks many of the benefits of other isolation levels. It is rarely used unless there is a specific need
  • Reda COMMITTED: Most databases are committed by default, but MySQL is not! From the time a transaction is executed to the time it is committed, other transactions are invisible, sometimes called unrepeatable reads, because executing the same query twice may result in different query results
  • Repeatable Read (REPEATable Read) : Repeatable Read solves the problem of read committed dirty reads. This isolation level is also MySQL’s default isolation level. This level ensures that the same transaction can read the same data multiple times, but there is a disadvantage of phantom reading! Phantom reading is when a transaction reads data in a range and another transaction inserts data in that range. Phantom rows are generated when the reading transaction reads the range again. Multi-version concurrency control (MVCC) is used to solve the problem of phantom reading.
  • Serializable: This is the highest isolation level. It avoids the magic read problem by forcing transactions to be executed sequentially, simply adding a lock to read data. This exposes another problem. This isolation level should be considered only when there are specific requirements or when no concurrency can be received.

9.5 Transaction Logs

Transaction log: redo log, undo log I will not elaborate on the details, which are written in the fourth module. Again, just to make it a little bit better when the interviewer asks about a transaction, they will definitely ask about the transaction log. Recommend to have a look!