Why use indexes

What kind of information can be indexed

The data structure of the index

The difference between dense and sparse indexes

How to locate and optimize slow query SQL

  • 1. Locate the SLOW query SQL based on the slow log
  • 2. Use tools such as Explain to analyze SQL
  • 3. Modify the SQL or make the SQL go through the index

The origin of the leftmost matching principle for federated indexes

Leftmost matching principle:

  • A =3 and b=4 and C >5 and d=6; a=3 and b=4 and C >5 and d=6; If the index (a,b,d,c) is used, the order of a,b,d can be arbitrarily adjusted
  • 2.= and in can be in random order, such as a=1 and b=2 and c=3, create (a,b,c) index can be in any order, mysql query optimizer will help you optimize the form to be recognized by the index

Causes: Mysql > select * from order by; select * from order by; select * from order by; The first column is absolutely ordered, and the second column is unordered. Therefore, it is not necessary to use the second column directly to determine the index. This is why mysql emphasizes the leftmost matching principle of the joint index.

Is it better to build as many indexes as possible

  • 1. A table with a small amount of data does not need to be indexed, which increases the index overhead
  • 2. Data changes require indexes to be maintained, so more indexes mean more maintenance costs
  • 3. More indexes also mean more space

What is the difference between MyISAM and InnoDB regarding locking

MyISAM is suitable for scenarios

  • 1. Execute the count statement of the entire table frequently
  • 2. The frequency of data addition, deletion and modification is not high, and the query is very frequent
  • 3. No transactions

InnoDB for scenarios

  • 1. Data is frequently added, deleted, altered and checked
  • 2. High reliability requirements are required to support transactions

Classification of database locks

  • Partition by lock granularity
    • Table level lock
    • Row-level locks
    • Page-level locks
  • Call charges by lock level
    • A Shared lock
    • Exclusive lock
  • Partition by locking mode
    • Automatic lock
    • The explicit lock
  • Division by operation
    • DML locks
    • DDL lock
  • Divided by mode of use
    • Optimistic locking
    • Pessimistic locking

Four features of database transactions

ACID

  • 1. Atomic
  • 2. Consistency
  • 3. Isolation
  • 4. Durability

Transaction isolation levels and concurrent access issues at each level

  • Dirty read: The transaction isolation level is changed to Read COMMITTED, that is, data from other uncommitted transactions cannot be read
  • Set the transaction isolation level to REPEATable Read or higher
  • Phantom reads, serialIZABLE transaction isolation level can be avoided

Transaction isolation levels from low to high

Transaction isolation level Update the lost Dirty read Unrepeatable read Phantom read
Read uncommitted avoid happen happen happen
Read committed avoid avoid happen happen
Committable Read (Read Read) avoid avoid avoid happen
Serialization (Serializable) avoid avoid avoid avoid

Current read and snapshot read

  • The current reading:
    • select… lock in share mode,
    • select… for update,
    • update, delete, insert
  • Snapshot read: unlocked non-blocking read, select

How to avoid phantom reads in InnoDB repeatable Read (RR) isolation level

  • 1. Representation, snapshot read
  • 2. Internal next-key lock (row lock +Gap lock)

Under repeatable read (RR), will Gap lock be used on primary key index or unique index

Gap locks occur mainly in current reads that are not unique indexes or do not move indexes

  • 1. If all where conditions are matched, no Gap lock will be used
  • 2. If the WHERE condition is partially or completely matched, a Gap lock will be added

How to implement InnoDB non-blocking reads at RC and RR levels

  • 1. DB_TRX_ID, DB_ROLL_PTR, and DB_ROW_ID fields in the data row
  • 2. The undo log
  • 3.read view