There is A Java program that queries the A table of the database test

create table A(id int primary key auto_increment, name varchar(12) ,age int);

insert into A(name,age)value("Jack", 12), ("Helon", 13); select * from Awhere name = "Jack";
Copy the code

The client

The client refers to programs such as Python and Java that connect to the mysql server

Mysql server

Servers include servers and storage engines

server

Mainly includes connectors, analyzers, optimizers, actuators

  • The connector
    1. Manage client connections
    2. Verify permissions on connections without directly returning an error to the client
    3. After the authentication succeeds, the permission information is loaded to the connected session. Therefore, if the permission is changed, the connection does not take effect and you need to reconnect the session
    4. Because a new connection requires a lot of operations, you are advised to use a long connection to reduce the number of new connections
    5. Memory that is temporarily used by a connection will not be reclaimed. As a result, more and more memory will be used. You can use mysql_reset_connection to release memory
    6. If the connection remains idle beyond wait_TIMEOUT, it will be disconnected by the connector, so long-connected clients need to send timed checks to ensure that the connection is working
  • The query cache
    1. The query cache will be eliminated if the corresponding table is modified. Therefore, do not use the cache for the table with many changes. You can cache the query for a separate static table
    select SQL_CACHE * from A where name = "Jack";
Copy the code
    1. This function is disabled by default and has been removed from mysql8
    query_cache_type = OFF
Copy the code
  • analyzer
    1. Analyze the words of SQL statements, such as tables and fields. If a field or table does not exist, an error is directly returned
    2. Analyze the syntax of SQL statements to determine whether the SQL statement is valid. If the SQL statement is invalid, an error is returned directly
  • The optimizer
    1. Optimization of SQL statements such as index selection
    2. Optimize SQL statements, such as SELECT count(*) from A
    3. Generate an execution plan
  • actuator
    1. Check whether you have permission to execute the query. If you do not have permission, an error is returned
    2. Invoke the storage engine interface to execute the execution plan generated by the optimizer

The storage engine

  • innodb
    There is data in memory
    1. Return data directly
    Memory without data

    There is no index

    1. Because this statement does not use indexes, scan the entire table directly (B+ tree of primary keys)
    2. Find the corresponding data page (not a single record, but the data page where the record is), store it in memory, and return it to the executor

    Index case

    1. First, add an index to column name of table A, and generate A B+ tree of name. The B+ tree contains the field values contained in the index and the primary key values. If it is A joint index, it is the value of multiple fields and the primary key values.
    alter table a add index a_name_index(name);
insert into A(name,age)value("Lam", 12); select * from Awhere name = "Jack";
Copy the code
    1. The execution plan generated by the optimizer then selects the name field as the index
    2. The storage engine searched the B+ tree of name and found the data of Jack. Because all fields were queried, the B+ tree of name only stored the value of name and primary key ID, but not the value of age, so it was necessary to query the B+ tree of primary key ID back to the table, query the value of age, and then query the next record. The value was Lam. If the requirements are not met, the query is ended. Finally, the data page of the corresponding record is put into memory and returned to the executor

Involved in the lock

  • MDL lock
    1. The query statement opens an MDL read lock.
    2. If a long transaction is not committed, the DDL operation cannot be executed, further affecting the DML operation. As shown in the figure below, a query session without a transaction will block operations such as DROP and ALTER and affect subsequent operations such as SELECT.

  • Consider the question, if such a lock table situation occurs, how do you know which transaction owns the lock?
  • How would the query result be different if the table was modified by another join at the time of query? Let’s take a look at experiments with different isolation levels

Performance at different isolation levels

  • Do not commit reads (read-uncommitted)
    1. Set the database isolation level to no commit read and ensure that the isolation level of the two sessions is the same. After the isolation level is reset, only the new connection takes effect.
    set global transaction_isolation="READ-UNCOMMITTED";
show variables like 'transaction_isolation';
Copy the code

As you can see from the figure above, the first transaction can read the changes made by the second uncommitted transaction, which results in invalid data read by the first transaction if the second transaction is rolled back.

  • READ-COMMITTED
  1. Set the isolation bound level to commit read and ensure that the isolation level is the same for both sessions
set global transaction_isolation="READ-COMMITTED";
show variables like 'transaction_isolation';
Copy the code

  • REPEATABLE READ
  1. Set the isolation level to repeatable read
set global transaction_isolation="REPEATABLE-READ";
show variables like 'transaction_isolation';
Copy the code

As can be seen from the figure above, after transaction 2 commits, transaction 1 still has the same result no matter how many times it is read.

  • serialization
  1. Because it’s not used, it’s not studied
Summary of isolation levels
  • At the isolation level of committable reads, each query returns the most recent value on the current record
  • At the serialized isolation level, records are locked and no other transactions are allowed to access them.
  • At the committable read isolation level, each query creates a new consistency view that only sees the most recent value of the record for which a transaction has been committed
  • At the repeatable read isolation level, the first query creates a new consistency view until the transaction is committed, so only the most recent value of the record that committed the transaction when the view was created is seen, and subsequent committed transaction records are not seen.

summary

  1. Queries with different isolation levels will result in different results, so you need to pay attention to the isolation level of the database
  2. The query involved MDL locks

The problem

  1. At the repeatable read isolation level, where are the records of other transaction changes stored and how are the records seen by your own transaction
  2. How does rollback work
  3. Why does the query record read the entire data page instead of just the corresponding record
  4. How does the optimizer select indexes
  5. How to build an index (normal index, unique index, prefix index, overwrite index)
  6. Select for update,select lock in share mode
  7. There is a lock situation, how to check
  8. What is the SQL modification process like
  9. Mysql > lock ()
  10. What is the group by, order by statement query process
  11. What is the process of a join table query
  12. Why is the default isolation level of mysql repeatable? What should I pay attention to when setting other isolation levels
  13. Select count(*),count(id),count(1),count(1),count(1),count(1),count(1),count(1
  14. Where conditional fields can be indexed using aggregate functions
  15. From the above experiment, we can see that the primary key id is discontinuous, why this problem

To be continued…