MYSQL learning mainly from the following aspects: basic architecture, SQL + Explain, storage engine + log, transaction, case analysis.

Basic architecture

Mysql storage structure is B+ tree.

B+ tree: redundant storage, leaf nodes store data, each node 16KB. The duration of disk I/O at a time should not be too long. It should be related to physical factors. The disk I/O is 4KB at a time. Each node reads data for four times and loads data in one time according to the principle of program locality. Disk reading relies on mechanical motion, seek (5ms), seek point that is to find the circle (7200 RPM average search time 4.17ms), copy to memory (quickly ignored), an IO average time of 9ms, but the amount of data is very large, 80,000 levels can reach 9000s. According to the principle of data locality, the database is read page at a time. The size of a page, depending on the operating system, is typically 4K or 8K

The page chart structure is as follows:

Insert process:

(The page is within a B+ tree node, and the page directory to the user data area is the layer 3 B+ tree structure) (The directory page connected by multiple pages is the layer 2 B+ tree structure)

• Insert by primary key ID order
• The page directory is used to record grouping information, and there is a pointer to record the smallest data in the group (essentially linked list lookup optimization: grouping subscript binary search + space swap time).

• If a page is full, it is a B+ tree node (so innoDB recommends inserting with increment ID).

• When there are too many pages, a long linked list is formed through the next pointer, which abstracts out a page catalog with a pointer holding the minimum id for each page

• To improve query efficiency, InnoDB uses a three-tier B+ tree structure, as shown in the figure below:

** How many index structures can a B+ tree store **

Starting from leaf node 1 is full table scan; Starting from the index page above is to follow the index

Data directory:

• FRM table structure.MYD data.MYI index => Leaf nodes store disk Pointers.
• Innodb: clustered storage engine:.frm table structure.IDB index + data

Why can’t the primary key of a database use a uUID

Mysql execution process

• Connector: Manages connections, validates permissions (system user table) Changing permissions does not take effect on already created connections. The long connection takes 8 hours by default, and the user information is cached in the connector session memory space.

Mysql connection pool design how large is appropriate? It is A basic law of computer science that given A CPU core, it is always faster to execute A and B sequentially than to execute A and B “simultaneously” through time sharding. Once the number of threads exceeds the number of CPU cores, increasing the number of threads makes the system slower, not faster. The database connection formula doesn’t just apply to databases. When I stress-tested a messaging service written using Netty, THE optimal number of threads was exactly double the number of CPU cores.

Number of connections = ((Number of cores x 2) + Number of valid disks)

Note, however, that the size of the connection pool is ultimately dependent on system characteristics. A system with a mix of long and short transactions, for example, is often difficult to tune for any connection pool. The best approach is to create two connection pools, one for long and one for short transactions.

• Buffer: Query_cache_type (0 not enabled, 1 enabled, 2 specified on demand); Select SQL_CACHE * from XXXX as required
• Lexical analyzer: analysis is select or update, also can find out whether SQL statements have syntax errors,mysql built syntax tree, also can use ANTLr4 visualization of a SQL statement
• Optimizer: Execution plan (algorithmic judgment)
• Executor: Calls the engine interface
• Engine: in the form of plug-in, can be flexibly replaced

Joint index B + tree:

Redundant required fields (back table)

Q1: Why the leaf node of the joint index does not store complete data: First, it is a waste of resources to store one more copy of data; second, data consistency needs to be maintained in the update and deletion operation.

Q2: Why don’t you just store the index with nothing, so that the data is not complete? It’s equivalent to storing a row of data in certain fields, and you can’t do that if you have select*

The key to judge whether the joint index goes to the index or not lies in the cost

Explain select * from t1 where b > 1; Mysql > select * from table where id = 1; select * from table where id = 1; select * from table where id = 1; If this example is changed to b>6, the index will be moved; If this example is changed to SELECT B, it will walk the index, because there is no need to return to the table, using an overwrite index.

Summary: When the main point is to write the conditions, it is necessary to write the conditions more accurately

Explain select b from T1 where id = 1

Explain select* from T1 order by b, C,d select* from t1 order by B, C, D select* from T1 order by B, C,d

(0 means not all, 1 means index)

Select ‘a’= 0 is equal in mysql because the type is converted to convert a character to a number, that is, any character is 0. So casting fails because the condition needs to convert a number to a character, and the number 1 cannot be converted to a character; all characters are equal to 0. The fourth statement converts the character ‘1’ to 1. Look at the figure above, an SQL statement that gives the number 1 a condition that needs to be converted to vARCHAR fails without indexing.

Summary: Any functional manipulation of a field will invalidate the index.

Explain :(version 5.7)

In mysql, there are the following types of indexes: primary key index, secondary index, overwrite index, and adaptive hash index (when an index value is used very frequently, a hash index is created on top of the B+Tree index).

Of course, the more indexes, the better. Creating and maintaining indexes costs resources. The following principles should be followed to establish indexes:

• Index columns that are frequently searched
• Add index to primary key to enforce uniqueness
• Indexes are built on columns that are frequently joined by tables, mainly foreign keys
• Create indexes on columns that often need to be searched or sorted by scope, sorted and scope-friendly
• Index clauses that use where frequently

Explain mainly contains the following fields:

show warning

Filtered (approximate number of rows in the associated table rowid*f/100)

Select_type (simple/derived query /subquery /primary complex query)

Table Indicates the table from which the query is performed

Id Query sequence. The larger the ID is, the earlier the query is performed

Type (system, const, eq_ref, ref, range, index, all)

Make sure you reach range, preferably ref

• “All” : scans all tables
• Index: scans the index tree
• Range: Range query using primary key, secondary index of a single field, and last field of secondary index of multiple fields
• Ref: equivalent scan of non-primary key and non-unique indexes
• Eq_ref: equivalent scan of the primary key or unique not NULL index
• Const: Uses a primary key index or a unique index, where the joined part is a constant.

• System: a special case of const, where the table being queried has only one record. System tables have a small amount of data and often do not require disk I/O

SQL > select * from proxies_priv; SQL > select * from proxies_priv; SQL > select * from proxies_priv; And the table has only one row (= system table). This is a special case of the const join type.

• Null: no need to access tables and indexes such as min (id)

The SQL optimization rules are as follows:

• select

1. Do not use custom functions, which are frequently called
2. Select The text field, consuming a large amount of network and I/O bandwidth
3. Gorup_concat is a string aggregation function that affects the response time of SQL
4. Select followed by a subquery is called an inline subquery, the number of times the subquery needs to be executed, seriously affecting SQL performance.

• where

When a where condition occurs, the index cannot be converted

• limit

For limit M, n paging query, the later the page, that is, the larger the M is, the longer the SQL time will be. For this case, we should first fetch the primary key ID, and then use the primary key ID to Join associated query with the original table.

• Build table

1. Meet the four paradigms
2. SQL > alter table AUTO_INCREMENT primary key ID
3. NOT NULL attribute
4. The DEFAULT attribute
5. The Text data type is not recommended

Storage engine InnoDB basic design

When data is read from disk, the page information is cached in the Buffer pool (128 MB array A Linked List of Pages (LRU)). When the data in memory is updated and needs to be flushed back to disk, the array will be free. It is worth thinking about where to put the free page the next time it is read.

As shown in the figure below, InnoDB maintains a free list memory pointer to a free area in a BufferPool. Innodb also maintains a list of flush linked pages in a bufferPool:

If bufferPool is full, you need to maintain an LRU list to support the elimination function. Of course, because of the characteristics of LRU, the list is head plug, elimination is based on the end elimination mechanism. When using the full table scan function, the LRU will be full of blood, which will affect the business morning performance because the hot data is replaced! , so the actual LRU linked list is divided into cold data area (3/8) and hot data area (5/8), and the reading rules are as follows:

The header of the cold data area is first accessed when a new page is added

When the consecutive access interval of the data in the cold data area is greater than 1s (principle: the full table scan traverses line by line, so only data on a page can be accessed quickly), the node is added to the head node of the hot data.

How do I restore data flush dirty pages? If mysql has just written to the memory and has not returned to the disk, it will hang down. The core is to use Redolog.

First, make it clear that a page is a logical concept, and that data on a page can be distributed in different areas of the disk.

Now there are two ways to back up data. The second way is to write back data to a page with random DISK IO (because pages are logical concepts that need to find the time). After the first way hangs and restarts, we still read the dirty data into memory, but we can redo the data according to redolog. Redolog only records specific operations, so doing them in memory is very fast! And redolog generates a file after mysql runs, and the operation statement adds data to the file. This process itself is a sequential IO.

Redolog can set the size and number of redolog logs. Generally, there are two redolog logs. When both files are full, the redolog logs are returned to a checkpoint. To overwrite the records of the first type of log (of course, this overwrite should be established on the basis of data security), then the dirty pages in the BufferPool need to be returned, generally make redolog larger and more numerous. However, redolog should not be too large, because if it is, it will take more time to restart the database.

New problem: What if there is no data in memory? Log the changes to the Change Buffer and log the redo log. Then load the changes to memory. When the load comes, apply the changes to the Buffer Pool. This action is called merge (Change Buffer -> Buffer Pool). It is important to note that Change Buffer is only used when operating on secondary indexes because clustered indexes must be “unique” (they need to be loaded into memory to determine).

Finally, when do YOU persist redolog? Instead of persisting a statement, it is persisted until the transaction is committed, which is stored in the Log buffer (memory). There are also three configurations from the Log buffer to disk to determine whether or not to flush directly. Purge (Buffer Pool -> Disk) Purge (Buffer Pool -> Disk) Purge (Buffer Pool -> Disk)

The redolog is invalid if the redolog file has been flushed to disk. Therefore, the redo log does not store all historical data changes, and the contents of the file are overwritten. The binlog is a binary file, implemented in the server layer. The binlog records THE DDL and DML operations of the primary database to the secondary database. It is a logical log. Appending, does not overwrite previous logs. The slave database listens for changes to the binlog, and if the binlog changes, we need to write the changes to the corresponding data source.

MySQL needs to make sure that the redo log and the binlog are the same. If they are not, it is a mess. Mysql T ensures consistency through two-phase commit.

Undolog: When we modify data in bufferPool, if the transaction is rolled back, how can we modify data back in memory? This is what Undolog does. It records the reverse operation internally. Undolog is also a logical log. There are two types of undo logs: insert undo log and update undo log

Double write buffer: a page of 16KB for mysql, the operating system reads and writes according to the page, and a page of 4KB needs to cache data. In the double write buffer, the data will be lost due to power interruption during the write process. Of course, this will cause performance degradation.

Summary: Mysql has many log types, and redolog is mainly for persistence, since the bufferpool mechanism, mysql needs to keep dirty data from getting lost when the Purge system went down. Binlog is mainly used for master slave replication to ensure scalability. Undolog is mainly for atomicity, recording compensation operations that are performed when a transaction is rolled back. In addition to the above logs, mysql has the following logs: ErrorLog, slow Query log, general log, relaylog

The transaction

Transaction isolation level

The transaction isolation level of a typical company is read committed. But in fact, MYSQL repeatable reads have solved the problem of unrepeatable reads and illusory reads, collectively known as MVCC, even if another transaction is committed, the current transaction is not detected.

First, the transaction ID of an saction is updated only when the update operation is performed

When the session Select operation does not generate transaction ids, the query generates a readView (consistency view) that is an array of all currently active transaction ids (uncommitted transactions) and the maximum transaction ids that have been created (not necessarily committed).

In the second query, if the snapshot is repeatable, the snapshot uses the readView of the first query, and the version chain is as follows:

When the data falls in the yellow range:

When you get the blue bar, 100 is in the reaView array, indicating that the version was generated by a transaction that has not yet committed, so it is not visible.

If it is not in readView data, it is committed and visible.

When the data falls in the green range: is visible

When the data falls in the red range:, it is not visible

If 300 is not in the readView, the query should be 300.

Core: From the first SELECT, the difference between repeatable and committed reads is the readView generation rule. In the figure above, the last query time of the two sessions is the same, but the query results are different, not because mysql copied the data, but because the readView is different

Mysql lock:

Row locks:

SELECT c FROM t WHERE c BETWEEN 10 and 20 FOR UPDATE; SELECT c FROM t WHERE C BETWEEN 10 and 20 FOR UPDATE; The 15-row lock cannot be inserted (locking the record index rather than the record itself, if no index is set, the hidden cluster index on the primary key is automatically created) : multiple transactions update the same data, and the lock is added. One transaction frees the other. Avoid dirty read next-key Lock: gap Lock + row Lock locks not only indexes on a record, but also gaps between indexes.

Table lock: Index invalid during update. Row locks become table locks (to avoid index failures)

Summary: The process of accessing the version chain of a record while performing a normal SELECT operation. This allows read-write and read-read operations of different transactions to be executed concurrently, thus improving system performance. COMMITTD will generate a ReadView before each normal SELECT operation. REPEATABLE READ generates a ReadView only before the first ordinary SELECT operation, and the REPEATABLE READ of data is actually the repeated use of ReadView. A normal SELECT is a snapshot read and does not require a lock. INSERT, UPDATE, DELETE) need to be locked in order to read the latest data. As you can see, MVCC does not need to lock completely, but only avoids the lock operation of SELECT. Of course, select can also force locking.

Case analysis

Long_query_time =1s. Some INSERT and update statements are slow. IOPS is high. Up to about 70MB/s 3. Analyze binlogs. Max_binlog_size =512MB. View binlogs generated during high IOPS periods. Mysql > insert into request_log (); mysql > insert into request_log (); 5. The improved method uses ES

Extended database

Implementation principle of read/write separation :(improve read)

Proxy: poor performance (network IO) but cross-language, does not support cross-database

JDBC: high performance, but not across languages only support Java, support across databases

After the master database is written, the data from the master database is not synchronized to the data in extreme scenarios (network delay, thread asynchronous). In this case, the atlas can be forced to route by adding /*master/ annotations before SQL

Database sharding strategy

1. Hashing modulus

2. Scope of ID

3. Mapping table (set up mapping table separately) sub-database sub-table :(improve writing)

When do you divide the database and table? The number of single tables reaches 500W or 2GB (estimated within three years)

1. Vertical split :(at least with associated attributes)

• Vertical branch: Branch by serviceCopy the code

• Vertical table: table by fieldCopy the code

Advantages: Clear logic and easy maintenance

Disadvantages:

• If there is a large amount of data in a single table, the write pressure is still high, which is highly relevant to services.
• Because the data is in different libraries, the table cannot be associated with the query, through the javaAPI call, improve the operation difficulty.

2. Horizontal split (also includes branch libraries and tables)

The structure of each table is the same, the data is different, and the content is broken down.

Pros: Does improve performance;

Disadvantages:

• Capacity expansion is cumbersome and involves data migration;
• Split rules are difficult to abstract;
• Distributed transaction

Problems:

Shardingsphere addresses the above issues

Installation Process (Windows)

• Download mysql and unzip it

Dev.mysql.com/downloads/f…

• Create my.ini in mysql directory

Reference: blog.csdn.net/weixin_4855…

Port = 3306 # set mysql client's default character set default-character-set=utf8 [mysqld Datadir =C:\mysql\mysql-8.0.21-winx64 # datadir=C:\ mysql-8.0.21-winx64 # Max_connections =200 # Latin1 character set character-set-server=utf8 # Default storage engine to be used when creating new tables default-storage-engine=INNODBCopy the code

Note: Do not have Chinese characters in the path. Try to configure environment variables. If not, go to mysql bin.

• Open CMD as an administrator

• Mysqld –initialize — insecure –user= mysqld — mysqld –initialize — insecure

Note: If you do not specify the password at the beginning, you need to set the password

Mysqld –initialize-console, and remember to enter the initial password for subsequent login.

• Mysqld -install mysqld -install mysqld -install mysqld -install mysqld -install mysqld

• Net start mysql

• Mysql -u root -p

Note: If you want to change the port, type mysql -u root -p 3307 -p and press Enter

• Alter user user() identified by “new password “;

You can set a new password after login

• quit

• Log in to mysql -u root -p again

• Open the Navicat connection