Database concepts

Classification of lock

Locks (by lock granularity)

  • Table level lock: Locks the entire table for each operation. Low overhead, fast lock; No deadlocks occur; The lock granularity is large, and the probability of lock conflict is high and the concurrency is low

  • Row-level locking: Locks one row of data per operation. High overhead, slow lock; Deadlocks occur; The lock granularity is the lowest, the probability of lock conflict is the lowest, and the concurrency is the highest

    InnoDBRow-level locks are for indexes, not data records

Although row-level locks are characterized by small granularity and high concurrency, table-level locks sometimes have their own application scenarios:

  1. Transactions update most of the data in a large table directly using table-level locking is more efficient
  2. Transactions are complex, and the use of row-level cables is likely to cause deadlocks and rollback

Locks (by read/write)

Table – and row-level locks can be further divided into shared (S) and exclusive (X) locks

  • A shared lock (S) is a read lock

    Other users can read data concurrently, but no transaction can acquire an exclusive lock on the data until all shared locks have been released.

  • Exclusive lock (X), that is, write lock

    If transaction T locks X on data object A, only T is allowed to read and modify A. No other transaction can lock A of any type until T releases the lock on A.

Read/write lock has some drawbacks. For example, if the resource that needs to be locked has been occupied by an exclusive lock, the user can only obtain the locked resource and add his/her own lock after the resource is released by the lock, so there is an intentional lock.

Intent lock explanation:

When a transaction needs to acquire a resource lock, it can add an appropriate intent lock to the table on which it needs to lock rows if it encounters a resource that is already occupied by an exclusive lock. If you need a shared lock, add an intended shared lock to the table. If you want to add an exclusive lock on a row (or row), add an intentional exclusive lock on the table first. Intentional shared locks can coexist simultaneously, but only one intentional exclusive lock can exist at a time.

InnoDBTwo other table-level locks:

  • Intended Shared lock (IS) : indicates that a transaction intends to place a shared lock on a row. Before a transaction can place a shared lock on a row, it must acquire the IS lock on the table.
  • Intended exclusive lock (IX) : indicates that a transaction intends to assign an exclusive lock to a row. Before a transaction can assign an exclusive lock to a row, it must acquire an IX lock on that table.

An intent lock is a table level lock, indicating an intent that only indicates that a transaction is reading or writing a certain row. A conflict will be determined only when a row lock is actually added. Intent locks areInnoDBAdd automatically without user intervention.

IX, IS IS a table-level lock, which does not conflict with row-level X, S locks, but only with table-level X, S locks.

To sum up, InnoDB grants the requested lock to a transaction when its lock mode is compatible with the current one. Otherwise, if the request is incompatible, the thing waits for the lock to be released.

Page-level locks

Locking A lock whose granularity is intermediate between row-level locking and table-level locking.

Table level locking is fast but has many conflicts, while row level locking is slow but has few conflicts. Page-level locking is a compromise, locking adjacent sets of records at a time. Deadlocks occur when overhead and locking time are between table and row locks. The locking granularity is between table locks and row locks, and the concurrency is average.

Deadlocks and their resolution

InnoDB’s row-level lock is for indexes, not for data records, so even if the same index key is used to access different rows, there will still be lock conflicts. Note that in the following ways

SELECT ... LOCK IN SHARE MODE; SELECT ... FOR UPDATE;Copy the code

When using locks, InnoDB creates a hidden clustered index and uses this index to add record locks if the table does not have any indexes defined.

Also, unlike MyISAM, which always acquires all the locks it needs at once, InnoDB’s locks are acquired gradually, and deadlocks occur when two transactions each need to acquire locks held by the other, resulting in both parties waiting. When a deadlock occurs, InnoDB can generally detect it and cause one transaction to release the lock and roll back and the other transaction to acquire the lock to complete. Of course, we can do some considerations for ourselves beforehand:

  • Table level locking is used to reduce the probability of deadlocks
  • Multiple programs try to agree to access the table in the same order (this is also a way to solve the philosopher’s dining problem in concurrency theory)
  • As many resources as a transaction can lock at once

Cache the query

Example for Mysql:

My.cnf add the following configuration, restart Mysql startup query cache

query_cache_type=1
query_cache_size=600000
Copy the code

Mysql can also enable query cache by running the following command

set global  query_cache_type=1;
set global  query_cache_size=600000;
Copy the code

As shown above, if the query cache is enabled, the results are directly returned in the cache under the same query conditions and data.

The query criteria described above include the query itself, the database being queried, the client protocol version number, and other information that may affect the results. So any two queries that are different on any character will result in a cache failure. In addition, if the query contains any user-defined functions, storage functions, user variables, temporary tables, or system tables in the Mysql library, the query results will not be cached.

When the cache is created, Mysql’s query caching system keeps track of each table involved in the query. If the table (data or structure) changes, all cached data associated with that table is invalidated.

Although the cache can improve the query performance of the database, the cache also brings additional overhead. Each query must be cached and destroyed after failure. Therefore, it is necessary to be careful to enable the cache query, especially for write-intensive applications. If this function is enabled, properly control the size of the cache space. Generally, it is appropriate to set the size to tens of MB. Sql_cache and SQL_NO_cache can also be used to control whether a query needs to be cached:

select sql_no_cache count(*) from usr;
Copy the code

The transaction

Transaction characteristics

  1. Atomicity: Transactions are the smallest unit of execution and do not allow splitting. The atomicity of the transaction ensures that the action either completes completely or does not work at all;
  2. Consistency: Data is consistent before and after a transaction is executed.
  3. Isolation: when accessing the database concurrently, a user’s things are not disturbed by other things, and the database is independent between the concurrent transactions.
  4. Persistence: After a transaction is committed. Its changes to the data in the database are persistent and should not be affected if the database fails.

Problems with concurrent transactions

  • Dirty read: When a transaction is accessing data and making changes to the data that have not been committed to the database, another transaction also accesses the data and then consumes it. Because this data is not committed yet, another transaction reads this data as “dirty data” and may not be doing the right thing based on “dirty data”.

  • Lost to modify: When a transaction reads data that is also accessed by another transaction, the first transaction changes the data and the second transaction changes the data. The result of the modification within the first transaction is then lost and is therefore called a lost modification.

    For example, transaction 1 reads A table A=20, transaction 2 also reads A=20, transaction 1 changes A=A-1, transaction 2 also changes A=A-1, the final result A=19, the modification of transaction 1 is lost.

  • Unrepeatableread: Reads the same data multiple times within a transaction. While this transaction is not finished, another transaction also accesses the data. Then, between the two reads in the first transaction, the data read by the first transaction may not be the same because of modifications made by the second transaction. This occurs when the data read twice within a transaction is not the same and is therefore called a non-repeatable read.

  • Phantom read: Phantom read is similar to unrepeatable reading. It occurs when one transaction (T1) reads several rows of data, followed by another concurrent transaction (T2) inserts some data. In subsequent queries, the first transaction (T1) will find more records that did not exist before, as if an illusion occurred, so it is called phantom read.

Among them, the key of unrepeatable reading is to modify, and the key of unreal reading is to add or delete.

Transaction isolation level

  1. READ_UNCOMMITTED(Unauthorized read) : The lowest isolation level that allows uncommitted data changes to be read,May cause dirty reads, phantom reads, or unrepeatable reads
  2. READ_COMMITTED(Read authorization) : Allows reading of data already committed by concurrent transactions,Dirty reads can be prevented, but phantom or non-repeatable reads can still occur
  3. REPEATABLE_READ(repeatable) : Multiple reads of the same field are consistent, unless the data is modified by the transaction itself.Dirty and unrepeatable reads can be prevented, but phantom reads can still occur.
  4. SERIALIZABLE(Serial) : Highest isolation level, fully compliant with the ACID isolation level. All transactions are executed one by one, so that there is no possibility of interference between transactions, that is,This level protects against dirty reads, unrepeatable reads, and phantom reads. But this severely affects the performance of the program. This level is also not typically used.

MySql

Architecture design


architect.jpg

The storage engine

In 5.5, for example


mysql_engines.png

The characteristics of comparison


engine_compare.jpg

MyISAM

  • Row locking is not supported (MyISAMTable lock only), lock all tables that need to be read when read, lock all tables that need to be read when write
  • Transactions not supported
  • Foreign keys are not supported
  • Security recovery after a crash is not supported
  • New records can be inserted into a table while a query is being read
  • supportBLOBandTEXTIndex of the first 500 characters of, support full-text index
  • Delayed index update is supported, which greatly improves write performance
  • Support for tables that will not be modifiedThe compression table, greatly reducing the disk space occupation

InnoDB

  • Table – level and row-level locks are supported, and deadlocks are possible
  • Transactions are supported, and the default transaction isolation level isREPEATABLE_READ.OracleDefault adoptedREAD_COMMITTEDIsolation level
  • Support foreign keys
  • Supports secure recovery after crashes
  • Full-text indexing is not supported (innodbHas been in5.6.4Full text indexing is now supported)

InnoDB row-level lock classification:

  • Record Lock: Locks the index entry to lock the row that meets the condition. Other transactions cannot modify or delete locked entries;
  • Gap Lock: Locks the “gap” between index entries, the range of records (the gap before the first record or the gap after the last record), excluding the index entry itself. Other transactions cannot insert data within the lock, thus preventing other transactions from adding phantom rows.
  • Next-key Lock: Locks the index entry and the index range. namelyRecord LockandGap LockThe combination of. Can solve the illusory problem.

The implementation of transaction isolation mechanism is based on locking mechanism and concurrent scheduling. Among them, concurrent scheduling uses MVVC (Multi-version Concurrency Control), which supports concurrent consistent read and rollback by saving modified old version information

Summary: MyISAM is better for read intensive tables, while InnoDB is better for write intensive tables. When the database is separated from the master, MyISAM can be selected as the storage engine of the master library.

Index structure

Both MyISAM and InnoDB use B+ trees as their index structures

But there is a difference:

  1. inInnoDBIn the table data file is pressedB + treeThe index structure of the organization, the data field of the leaf node of the tree holds the complete data record (this kind of index is called the clustered index), andMyISAMThe index file and the data file are separated (non-clustered). The index file only stores the address of the data record. After locating the specific data record address, it takes another IO to find the specific data record
  2. InnoDBThe secondary index data field stores the value of the corresponding record primary key, i.eInnoDBAll secondary indexes of, reference the primary key as their data field, andMyISAMThe secondary index data field stores the address of the corresponding record

To sum up, When InnoDB searches for data according to the primary index, it directly finds the node where the key resides to fetch the data file. When InnoDB searches for data according to the secondary index, it first needs to find the primary key value in the data file of the node where the key resides. To find the corresponding data file, go through the primary key index again (that is, the secondary index search requires retrieving the index twice). When InnoDB is used to create tables, try to specify primary keys. When InnoDB does not include primary keys, the storage engine will generate a default primary key index with a length of 6B. In addition, it is not recommended to use a field that is too long as the primary key, because the secondary index finds its real data file by referring to the primary index. An excessively long primary index makes the secondary index too large. It is also not recommended to use non-monotonic fields as primary keys. Since InnoDB data files are a B+ tree, non-monotonic primary keys cause data files to be frequently split and adjusted to maintain B+ tree properties when new records are inserted, which is very inefficient. We recommend using auto-increment as primary keys (this allows random inserts to be replaced by sequential appends). No page splitting and fragmentation, improve write performance).

Transaction processing mechanism

InnoDBThe transaction log

InnoDB uses undo and redo logs to ensure atomicity, consistency, and persistence of transactions, and uses pre-write logs to change random writes into sequential appending writes to improve transaction performance.

  • undo log: Records the status of the transaction before it changes. Before operating on data, back it up toundo logThe data is then modified if errors occur or the user executesrollbackStatement, the system can be usedundo logThe historical version in returns to the state before the transaction began.
  • redo log: Records the state after the transaction is about to change. When a transaction commits, as long as theredo logPersistent, data can be changed in memory. When the system crashes, the data doesn’t fall, butredo logHas been persisted, the system can be based onredo logTo restore all data to the latest state.
  • checkpoint: As time goes by,redo logIt gets really, really big. If the recovery starts from the first record each time, the recovery process will be slow. In order to reduce the recovery time, introducedcheckpointMechanism. On a regular basis todatabufferFlush the contents to diskdatafileInside, and then clearcheckpointBefore theredo log.
  • Automatic recoveryInnoDB loads the latest snapshot and then replays the latest snapshotcheckpointAll after pointredo logTransactions (including uncommitted and rolled back) are passed againundo logRoll back uncommitted transactions to complete data recovery. The important thing to note is,undo logIt’s actually row data, and writes to it are also recordedredo logInside, that is,undo logthroughredo logTo ensure persistence.

The following figure shows the general process of transaction write execution. There is only one flush, namely the redo log write at transaction commit time.


tx_proc.png

By default, Innodb_flush_log_at_trx_commit=1. Once a redo log is written to disk, it is written to disk immediately.

InnoDB shares a redo log buffer. When a transaction is written to a disk, the redo log is persisted regardless of whether the transaction is committed or not. Other operations also trigger redo log writes. For example:

  • redo log bufferThe space is insufficient
  • The triggercheckpoint
  • The instanceshutdown
  • binlogWhen switching

MVCC

Multi-version Concurrency Control (MVCC). The Concurrency Control protocol divides read operations into two types: snapshot read and current read. The latest version of the record is read, and the returned record is locked to ensure that other transactions cannot modify it concurrently.

  • Snapshot read: a simple query operation. Snapshot read is not locked

    select * from table where ? ;Copy the code

  • Current read: Special read operations and insert/update/delete operations are current reads and need to be locked

    1 select * from table where ? lock in share mode; 2 select * from table where ? for update; 3 Insert into table values (...) ; 4 update table set ? where ? ; 5 delete from table where ? ;Copy the code

Snapshot reading is controlled by using undo log. For each row, row_id indicates the row ID, trx_id indicates the recently modified transaction ID, and db_roll_ptr indicates the undo log pointer to the undo segment. If trx_ID is smaller than the transaction ID, the data is visible to the current transaction. Otherwise, the data is not visible. If the data is not visible, run the db_roll_ptr command to search for historical version records and obtain the most recent historical records. The undo log link is not very deep, and the background purge thread periodically removes useless historical versions (undo logs can be deleted when there are no active transaction dependencies).


undo_log.jpg

Data Recovery Mechanism

Data recovery is divided into the following steps:

  1. First, check the latest Checkpoint, and then commit the data at the Checkpoint to the disk in RedoLog. Then, restore the data at the Checkpoint by searching for the corresponding data in undolog logs
  2. Uncommitted or rolled back data is recovered by looking for the corresponding row in the Undolog log

Table optimization

When the number of records in a single table is too large, the CRUD performance of the database will significantly degrade, so there are the following solutions to solve the problem.

Foundation scheme

  • Limit the scope of data when querying
  • Read/write separation :M-S mode, master library is responsible for writing, slave library is responsible for reading
  • Caching technology: The use of database caching, but also for heavy, less updated data through the application level of caching

In addition to these, there are two key technologies.

table

Split table is the splitting of index data table columns. A table with many columns is divided into multiple tables.

Table can make row data smaller, reduce the number of blocks read during query, reduce I/O times. It can also simplify the structure of the table and make it easy to maintain.

But primary keys are redundant, need to manage redundant columns, and can cause Join operations, which can of course be solved by joining at the application layer, and can make transactions more complex.

depots

Keep the data table structure unchanged and store the data shards with some policy. In this way, each piece of data is dispersed to different tables or libraries, achieving the purpose of distribution. Split repositories (horizontal split) can support very large amounts of data.

The branch library can support very large amount of data storage, and the application side transformation is less, but the fragment transaction is difficult to solve, and the Join performance of the cross-border point is poor, and the logic is complex.

PS: reference blog.codinglabs.org/articles/th… Segmentfault.com/a/119000000…

Write a little rough, welcome to criticize the correction >-<