This is the 15th day of my participation in Gwen Challenge

How is an SQL statement executed

  • Mysql > create mysql

    The client

    The connector

    The query cache

    analyzer

    The optimizer

    actuator

    The storage engine

How is an SQL update executed

Important log modules redolog, binlog

  • redolog

    Here is a story about kong Yiji, a boss who keeps accounts on credit.

    When a log needs to be updated, innoDB writes the log to redo log and then updates the memory. When the system is idle, innoDB updates the redo log to disk.

    Relog features: only in InnoDB, it is a few files, write cycle (i.e. if full, then update the record, then erase, then fill)

  • binlog

    (1) Binglog is the log of the Server layer, which is actually the log recorded by the executer.

    Rotating_light:

    • The actuator asks the engine for the row with id=2, and when it gets it, it gives it +1,
    • Redolog is prepared, telling the executor that I’m done and ready to commit the transaction.
    • The executor generates a binlog of the operation and calls the commit transaction interface of the engine. The engine changes redolog to commit and the update is complete.

    (3) According to the writing order of log, we can know one thing:

    • Without Redolog, an exception occurred while the engine was executing commands, and there is no way to recover because binlog has not recorded the wipe yet. cash-save

    (4) So why does redolog have two states?

    • If you want to create a slave library, use binlog to create a slave library. If you want to create a slave library, use binlog to create a slave library. There will be one less operation record, the master and slave database content is different.
    • They usually use full backup +binlog archive to achieve this. At this time, if the binglog operation record is missing, the master and slave will be inconsistent.

    (5) Explain why the DBA can restore the database at any time

    • The first one has a backup library, and the second one relies on binlog

Third, isolation

Read uncommitted Read committed repeatability serialization (table lock)

So essentially the database creates a view so that it can be repeatable and read committed

In MYSQL, a rollback operation is actually recorded for each record that is updated. So there’s a problem, you can’t have too many rollback records, you can have too many rollback records when you have a long transaction, so to avoid that, when you start a transaction, you have to commit or roll back at the end. Key: the rotating_light:

Four, simple index (1)

  • In InnoDB, tables are stored according to primary key order. Suppose a table has a primary key and a normal index. If a normal index is used to look up data, it will first use the normal index to find the corresponding primary key, and then use the index of the primary key to find the corresponding record. The reason is that a primary key index stores the entire row of data, while a normal index stores the information corresponding to the primary key. This process is also known as key: back to the table. ** Therefore, to improve efficiency, we should use primary keys to query data. Innodb must have primary keys because of its B+ tree structure. MYisam does not need primary keys because its B+ tree stores addresses. We will talk about this later. :rotating_light:
  • In some cases, the determination of the primary key is also very important. For example, if you want to record the ID of a table, would you use the id as the primary key? Should not be possible, so that each of the ordinary index space is very large, should use the general number increment on the line.
  • Mysql innoDB uses a B+ tree, which is essentially an N-tree. Why? In fact, binary trees store data too scattered, too scattered data addressing must be time-consuming, using n-tree can avoid multiple partition addressing.
  • B+ tree page splitting. Page splitting is when your new ID exceeds N in the index binary tree. This time, it will split. Frequent splitting will lead to degradation of efficiency, so it is recommended to use self-growing primary key as the index. When using the InnoDB storage engine, always use a business-independent increment field as the primary key unless you have a specific need.
  • Page merge: If we delete some values of an index, we will pass through a little more nodes if we leave it alone, and a little less if we rearrange the binary tree. What we usually do is, if the number of nodes is less than N over 2 we merge it with the nearby nodes.

Five, simple index (2)

  • Overwrite index: we know that there is a back table this phenomenon exists, the essence of ordinary index is the primary key information, what if we want something is the primary key? , so you don’t have to go back to the table, so you can speed up.

  • If I have a joint primary key for a and B, should I create a separate primary key for A? The answer is no, because the indexer of the B+ tree can use its left-most prefix to locate it. For example, if you want to find the information of Zhang SAN, (Lao Wang, 1), (Lao Dou, 2) (Zhang SAN, 10), (Zhang SAN, 20), according to the joint primary key, he will quickly find (Zhang SAN, 10), and then find the ones that meet the conditions of Zhang SAN in order. ** is used as long as the left-most prefix is satisfied, important :rotating_light:. ** So when we create a joint index, before and after the order should be considered, the principle is that you can maintain one less index, is good.

  • Index push down: For example, we want to search for a boy whose last name is zhang and age is 10. The index is the combined index of the name and age. Mysql > select * from mysql5.6 where age = 10; mysql > select * from mysql5.6 where age = 10; This greatly reduces the number of times back to the table.

  • Let’s take a closer look at the use of indexes: does all of this work for different statements when there is a joint index? It’s basically based on the left-most prefix principle

    Let’s say we have a table named employees.titles, whose combined primary key index is <emp_no, title, from_date>.

    • select * from employees.titles where emp_no=’1′ and titile=’1′ and from_date=’2020′;
      • This is a full column match, using all the fields in the federated index
    • select * from employees.titles where emp_no=’1′
      • The leftmost prefix matches, which uses emp_no as an index
    • SELECT * FROM employees.titles WHERE emp_no=’10001′ AND from_date=’1986-06-26′;
      • Here, as above, the title field is missing and only emp_no is used.
    • SELECT * FROM employees.titles WHERE from_date=’1986-06-26′;
      • This will not use the index — index failure :rotating_light:
    • SELECT * FROM employees.titles WHERE emp_no=’10001′ AND title LIKE ‘Senior%’;
      • This will use the index, but there is a requirement that % does not start, otherwise invalid, only one index.
    • SELECT * FROM employees.titles WHERE emp_no < ‘10010’ and title=’Senior Engineer’;
      • Only the range of the first column can use the index, and only the first index.
    • SELECT * FROM employees.titles WHERE emp_no=’10001′ AND left(title, 6)=’Senior’;
      • Emp_no index emp_no index emp_no index emp_no

B tree, B+ tree knowledge

This blog to say very good: blog.codinglabs.org/articles/th… 2011, amazing. The main pictures are from this blog post, but it’s really good.

  • An index is something that is on disk and is usually very large. The process of reading an index is to read the index file into memory. For a file with a large index, it will be read in parts.

  • For balanced binary trees or red-black trees, the physical implementation is arrays, and the associated data is sometimes far apart, which can cause you to read a lot of indexes, which can be very inefficient.

  • The structure that appears for indexing is the B-tree.

  • There is a concept called locality principle, which is disk prefetch, when reading the disk, it will read the things after the address into memory, so we should put close data together, so that we can read together, reduce the I/O times. This principle is the knowledge of computer principles.

  • So for b-trees, we know that they are n-fork books, that is, one node stores N data, so we can take full advantage of this locality principle and disk prefetch.

  • B tree structure diagram: Its characteristics are:

    • Each non-leaf node (non?) It consists of keys and Pointers. The number of keys is the number of Pointers +1.
    • Key is in ascending order from left to right
    • As you can see in the figure, the keys that are larger than 15 and smaller than 56, depending on the pointer between them, point to the next node, and the key of this node must be less than 56 and greater than 15

BTree_Search(node, key) {if(node == null) return null; foreach(node.key) { if(node.key[i] == key) return node.data[i]; if(node.key[i] > key) return BTree_Search(point[i]->node); } return BTree_Search(point[i+1]->node); } data = BTree_Search(root, my_key);Copy the code

  • The structure of a B+ tree is:

    • His inner node does not store data, only keys
    • His leaf nodes have no Pointers

  • B+ tree will be further improved in real use, as shown in the following figure:

    • With sequential access pointer, improve the interval access ability, when we query 15 to 30 of the number, after locating to 15, directly after the search is done. That’s why B trees aren’t as good at finding ranges as B+ trees

To sum up: two questions:

  1. What is the difference between a balanced binary/red-black tree and a B tree? Why index B trees instead of those two?
  • Balanced binary tree is a binary tree, each node only has two leaf nodes, associated data may be in different locations, read many times, low efficiency.
  • B tree is to solve this problem and was born, his multi-fork tree feature to ensure that he read IO times will be reduced, but his node data is sequential, using the characteristics of disk prefetch.
  1. B tree and B+ tree
    • The leaf node of B+ tree does not store Pointers, but only stores data. In this way, we only need to traverse the leaf node and the next leaf node.
    • The structure of B+ tree is ordered array list + balanced multi-fork tree, and B tree is ordered array + balanced multi-fork tree. That is, the leaf of a B+ tree is a linked list, and each data point points to the next adjacent data point.
    • B+ trees make him good at scoping, and B trees can’t compare with him.

  • Index structure difference between MyISAM and innoDB

    • The last leaf node of myISam index stores the address. The normal index is the same as the primary key index, which also stores the address. The address points to the corresponding database record. Also called a non-clustered index, the index is separated from the data.

  • innoDB

    For the primary key, it does not store the address, but the whole record, that is, innoDB data files themselves are index files, myISam index and data are separated. The contents of a normal index are primary keys.

  • How InnoDB and MyISAM store data respectively, what are the advantages and disadvantages?

    MyIsam does not support transactions.

Global locking and table locking

Locks are classified into global locks, table locks and row locks

  • Global lock:

    Lock the whole table, only select query operation, add, delete, change, DDL are not available, generally used when we need to back up the master database. The syntax is Flush table with read lock, which locks all tables when opened, but what if a user is backing up the table? Just till we’re done? We speak before actually otherwise, combined with the isolation level, we have mentioned the database will be rolled back, or MVCC version control, he will give you a view, you can view operation, the practice is before you lock the whole table, to open a transaction, we have to do is, official mysqldump own logical backup tool. When mysqldump uses the parameter — single-transaction, a transaction is started before the data is transferred.

  • Table locks:

    Table locks are divided into two types: table locks and metadata locks MDL

    • Table locks: the syntax is Lock tables…. Read /write to lock a table. If session A runs lock T1 read, then session A can only query table T1. If session A runs lock T1 read, then session A can only query table T1. Session B can look up table T1, and it can look up other tables, but the INSERT table is blocked until session A executes unlock tables.

      If a table specific read or write is locked in a session, the session cannot access other tables, and the session can only be locked for those tables. :rotating_light:

      This is obviously too restrictive and Myisam will still use it, but innoDB has more granular row locks and almost no table locks.

    • Metadata lock metadata lock: locks the surface layer

      This is the lock that the database automatically adds to us, when you have to add, delete, change, change, automatically add read lock. Write locks are automatically added when you have DDL statements. Write locks are mutually exclusive, and the two DDL writes are executed sequentially. Read-write locks are also mutually exclusive, and MDL does not release the locks actively, only after the transaction ends. When we add a field to a table, the database will likely hang.

      Select * from information_schema.innodb_trx; Consider stopping the transactions in the table, or setting the wait time for our DDL statements.

    1, as long as we know the impact of MDL lock on our new field, we have to do what to avoid the impact. 2, is the global lock is mainly used in backup, generally we start the transaction, in the backup is more appropriate. :rotating_light:

Eight, line lock

Row locking is used to lock each row. It is implemented in the storage engine. For example, MyISAM does not have row locking, but only table locking. That’s why he was replaced.

  • After a transaction holds a row lock, other transactions that want to operate on that row are blocked and have to wait for the row lock to be released.

  • In InnoDB transactions, row locks are added when they are needed, but are not released immediately when they are not needed, but wait until the end of the transaction. This is the two-phase lock protocol. Knowing this setting, how does it help us use transactions? That is, if you need to lock more than one row in your transaction, put the locks that are most likely to cause lock conflicts and affect concurrency as far back as possible.

    Suppose you are responsible for implementing an online transaction for movie tickets. Customer A wants to buy tickets at theater B. To simplify things, this business needs to involve the following operations:

    1. Deduct the movie ticket price from customer A’s account balance;

    2. Add this movie ticket to theater B’s account balance;

    3. Log a transaction.

    That is, to complete the transaction, we need to update two records and insert one record. Of course, to preserve the atomicity of the transaction, we put these three operations together in one transaction. So, how would you arrange the order of these three statements in a transaction? If another customer C wants to buy a ticket at cinema B at the same time, then the part of the two transactions that conflict is statement 2. Because they need to update the balance of the same theater account, they need to modify the same line of data. According to the two-phase locking protocol, no matter how you order statements, all operations require row locks to be released at transaction commit time. So, if you put statement 2 at the end, such as 3, 1, 2, then the cinema account balance line will be locked for the least time. This minimizes lock waits between transactions and improves concurrency.

  • The deadlock problem

    Innodb automatically detects deadlocks, i.e. if a lock wait occurs, the engine will scan all locks to see if innoDB is involved in a deadlock. If so, innoDB will roll back the transaction so that others can proceed.

    However, each scan costs too much CPU, so the solution is to reduce the concurrency. We can control the concurrency of the scope database in the middleware, or mysql modify the source code to set the waiting queue.

To summarize locks: :rotating_light:

  1. Global lock is used for database backup. It is recommended to lock the database and then back up the database after starting transactions.
  2. Select * from MYISAM; lock xx read/write; select * from MYISAM; It hardly makes sense.
  3. Table lock MDL is the surface, so write refers to DDL, read refers to add, delete, change query. Read-write locks are mutually exclusive, and write locks are mutually exclusive. MDL releases the lock after the transaction commits.
  4. Row locks are also released after the transaction commits. Read operations are not mutually exclusive, while update operations are mutually exclusive.

Whether a transaction is isolated or not

First of all, innoDB defaults to repeatability PR, which we know can cause illusory reading. Let’s look at the repeatability and read commit of doing this.

This view is not the database view, but a snapshot, or up_limit_id, which means that I only read versions of data less than or equal to this up_limit_id.

So up_limit_id what is this thing? Row trx_id is a version number, each transaction generates a row trx_id, so each transaction starts with the maximum row trx_id, up_limit_ID

Update operations are performed in the order that the up_limit_id version is read first and then updated. So the final result for Q1 in the PDF is 3. The update operation is performed in the following order: first read, then update. The update operation is performed in the following order: first read, then update. :rotating_light:

So you can see why he can repeat it. Up_limit_id ——>update; select the same id———> obtain the correct version. At the repeatable read isolation level, you only need to find the up_limit_id at the beginning of the transaction, and then all other queries in the transaction share the up_limit_id.

Up_limit_id is updated each time a query is made to read committed isolation levels.

Rotating_light :rotating_light:

So you can see why he can repeat it. Up_limit_id ——>update; select the same id———> obtain the correct version. At the repeatable read isolation level, you only need to find the up_limit_id at the beginning of the transaction, and then all other queries in the transaction share the up_limit_id.

Up_limit_id is updated each time a query is made to read committed isolation levels.

MYSQL > select * from ‘MYSQL’;

Suppose A is the primary library and B is the standby library. There will be A connection between AB and A, and A will have A special thread inside A to communicate with the standby library. Library A receives the cache from library B, reads the binlog from the local, and sends it to B. After B gets it, it writes its own local relay log. There is A special thread in LIBRARY B to read the relay log and execute it.

In practice, two libraries a and B are set to be either primary or secondary. But if B gets the binlog of A, it’s going to record the binglog, and A gets the binlog of B, so it’s going to replicate. If bingLog has a server_id in it, it will check whether it is the binlog generated by its own machine. If so, it will not execute.

How to ensure high availability of MYSQL

  • So we’re talking about consistency, and we’re talking about high availability is the latency between master and slave.

  • The delay refers to the difference between the completion time of the same transaction in library A and the completion time in library B. Then the real time is not the binlog transfer, but the B library read relaylog execution speed. Possible causes of this speed include:

    • Some people may set the performance of the standby machine to be low because it is rarely used. Of course, this is rarely the case nowadays, as there is a lot of switching between master and standby.
    • There’s too much pressure on the back-up. Because some people deliberately produce the database, so as far as possible to use the standby database to check data, but lead to the standby database pressure is very large. Solutions:
      • From more than a master
      • Give the binlog to an external system, such as Hadoop, and let them do the querying
    • Large transactions. Like deleting a lot of data at once
    • Parallel capability of auxiliary library

  • What is the process of MYSQL primary/secondary switchover?

    • Confirm whether the delay time of B library is lower than specified (to prevent high delay, otherwise the later synchronization will be slow)
    • Change library A to readonly
    • Synchronize the remaining binlogs of library A until the delay time of library B is 0.
    • Make library B readable and writable
    • Switch business requests to the B library

    This is called a reliability-first strategy.

12. Why is the standby warehouse delayed by several hours

In the case of a very high frequency of use, the slave library may never catch up with the master library if it is delayed. Next we’ll learn about the parallel copying capabilities of the slave library.

What is it? The relay log from the library is single-threaded, and the concurrency is not enough. After MYSQL5.6, it has been changed to multi-threaded read.

The master library has a problem, how to do with the slave library

Let’s take a look at the structure of a master with many slaves: the master library does most of the write operations and a small number of reads. From the library to read operations. AA ‘is in primary and standby mode.

When primary library A dies, BCD points to A, and it becomes the write library.

So there is A problem, but A hangs up, B connects to A ‘, but there is no guarantee that A and A are the same at that moment? Of course you can wait until the synchronization between master and slave libraries is complete, but you can also turn on the GTID thing, which makes switching from master to slave much easier.

Separation of master from read and write

Application scenario of one master multiple slave architecture: Read/write separation.

Read and write separation where to implement separate queries to different databases. It may be directly connected on the client side, which is the case with my current project, choosing a specific data source directly. The second is to create a proxy between the client and the database. The proxy determines which database to go to based on the context.

What if I have just updated the data from the master library, and I need to read it soon? What if the slave library has not been synchronized yet? This problem can be avoided without the delay between master and standby libraries. This is not 100% avoidable. There are several ways:

  • Force to go to the main library: differentiate our queries, if for example I buy something, I must see my order when I return, then force him to go to the main library. For example, on a new product, it doesn’t matter to see it later, so put it in the library to check. It seems that this method is a way to escape from reality, but the actual use of more. However, if all your requirements are real time, this approach is not very good, because there is no slave library.
  • Select sleep(1) when I query from the library, so that there is a high probability that master/slave synchronization is completed, but obviously it is not accurate, maybe it should only need 0.5 seconds of synchronization, but you just drag it to 1s.
  • Check whether there is no delay between master and slave. Check whether the delay time is 0 or whether it is a record of GTID master/slave library logs
  • Do not do more to expand learning…

Some of these options may seem like compromises and some may seem less convincing, but they all have real application scenarios that you need to choose based on your business needs.

Even the two schemes, the last waiting site and the waiting GTID, seem to be more reasonable, but there are still trade-offs. If all the slave libraries are delayed, then all the requests will fall on the master library. Will the master library suddenly fail due to increased pressure?

In fact, in practical application, these several schemes can be mixed. For example, the client classifies the requests to distinguish which requests can accept expired read and which requests cannot accept expired read at all. Then, for statements that cannot accept expired reads, the equal GTID or equal locus scheme is used.

To sum up:

We talked about that above

  • How to maintain consistency between master/slave, binlog, connection, relaylog, circular replication.
  • How to achieve high availability is the delay between master and slave. One master with many slaves, symmetric configuration of master and slave, less large transactions of secondary library, parallel replication of secondary library.
  • Master library how to switch to the library, the delay time limit — — — — — > the main library delay time is changed to read only — — — — – > 0 — — — — — > for library to write.
  • How to connect the slave machine to the slave machine when the master library hangs up in the mode of one master and multiple slaves? GTID Indicates the global transaction ID. I didn’t go into the details.
  • Read/write separation caused by “expired read” solution.

A. join B. join C. join D. join

select * from t1 straight_join t2 on t1.a = t2.a; The process goes like this:

  • Take a row from driver table T1. All of this is a full table scan
  • Get a for each row and then follow a to the T2 table. This query is a tree search.
  • Find the rows in T2 that match the conditions, and form a row with the data retrieved by T1
  • Repeat the second and third steps until you reach the end of the T1 table

Obviously, the driven table is a full table scan, the driven table is a tree lookup, and all the driven tables should be as small as possible (more carefully, using the respective conditions to see whether the amount of data involved is large or small). The premise is that the index of the driven table can be used.

MYISAM and InnoDB

My: No line locking, no MMVC multi-version control, the query will be relatively faster

InnoDB: row lock, index is records have some cache, MVCC.

Index failure

  • Or causes an index to stop indexing unless you have an index for every condition
  • A federated index is not used if it is not the first
  • If you start with % immediately, you’re not going to use the index N% for N, this is going to use the index
  • If the column is a string, the condition must be quoted
  • If the mysql optimizer determines that a full-table lookup is faster than an index lookup, then a full-table lookup is used
  • <> \ not \ in \ exists \
  • Statement containment function

18. Magic reading learning

The first time you read a database, the result of the insert statement is ok, but after the insert statement error primary key conflict, this is a phantom read.

Solution: Add a row lock at the beginning of the select. Serialize, which automatically locks rows when querying automatically, is the only isolation level that does not phantom.

Next -key lock, gap lock and row lock can solve the illusion problem.

19. Classification of locks

  • Optimistic locking: first check the data (with the version), then check again during update, if the version is the same as the original version, then update; Java code should be a spin.
  update table set a='a',version = version + 1 where version = #{version} and id = #{id}
Copy the code
  • Pessimistic locks:
    • Shared lock: read lock. A transactionlock in share modeBy locking each row of the result, the A transaction can be updated, but other transactions cannot be read locked or updated. Other sessions can read. That is, when I read data, no other transaction is allowed to change the data.
    • Exclusive lock: write lock,for updateIf this statement is used in one session, only the previous session can be changed. The goal is to keep the other sessions still while I update.
    • forinsert,update,deleteSuch operations. The data involved is automatically locked exclusively;