This article has been authorized by the author Liu Chao netease cloud community.
Welcome to visit netease Cloud Community to learn more about Netease’s technical product operation experience.
The database is always the most critical part of the application. At the same time, the more to the high concurrency stage, the database often becomes the bottleneck. If the database table and index are not well designed at the beginning, the later database will be horizontally expanded, and the sub-database sub-table will encounter difficulties.
For Internet companies, Mysql database is generally used.
First, the overall architecture of the database
Mysql data structure:
This is a very classic Mysql system architecture diagram, through this diagram you can see the function of each part of Mysql.
When a client connects to a database, it first faces the connection pool, which is used to manage user connections and perform certain authentication and authentication.
After connecting to the database, the client will send SQL statements, and the SQL interface module is to accept the user’s SQL statements.
SQL statements often need to conform to strict syntax rules, so they need to be parsed by a syntax parser. The principle of parsing syntax, as learned in the principles of compilation, is from statements to syntax trees.
Queries to which the user belongs can be optimized so that the fastest query path can be selected, which is where the optimizer comes in.
In order to speed up the query, there will be a query cache module, if the query cache has a matching query result, the query statement can directly go to the query cache to get data.
All of the above components are the database service layer, followed by the database engine layer, the current mainstream database engine is InnoDB.
Any changes to the database are recorded in the Binary log at the database services layer, which is the basis for master/slave replication.
For the database engine layer, a famous diagram looks like this:
At the storage engine level, there are caches, there are logs, and the data eventually falls onto disk.
The cache at the storage engine layer is also used to improve performance, but unlike the cache at the database service layer, which is the query cache, the cache at the database engine layer is cached for both reads and writes. The cache in the database services layer is based on query logic, while the cache in the database engine engine is based on data pages, so to speak, physically.
Even if the data is only written to the cache in the database engine layer, for the database service layer, even if it has been persisted, of course, this time will cause the data inconsistency between the cached page and the page on the hard disk, this inconsistency is guaranteed by the database engine layer log integrity.
So the database engine layer logs are different from the database service layer, where the service layer logs the change logic one by one, while the engine layer logs the physical difference between the cache page and the data page.
Two, the database workflow
Upon receiving a query, the various components of Mysql’s architecture work like this:
The client establishes a TCP connection with the database service layer, and the connection management module establishes the connection and requests a connection thread. If there are free connection threads in the pool, they are allocated to the connection. If not, a new connection thread is created to take care of the client if the maximum number of connections has not been exceeded.
Before the actual operation, you also need to invoke the user module for authorization checking to verify that the user has permissions. After that, the service is provided and the connection thread begins to receive and process SQL statements from the client.
After receiving the SQL statement, the connection thread delivers the statement to the SQL statement parsing module for syntax analysis and semantic analysis.
If it is a query statement, you can check whether there is a result in the query cache. If there is a result, you can directly return it to the client.
If there are no results in the query cache, it is necessary to actually query the database engine layer and send it to the SQL optimizer for optimization of the query. If it is a table change, it will be handed to the INSERT, UPDATE, DELETE, CREATE, ALTER processing module for processing.
The next step is to request the database engine layer to open the table and obtain the corresponding lock if necessary.
Next comes the database engine layer, such as InnoDB.
At the database engine level, the cache page is queried to see if it has the corresponding data. If it does, it can be returned directly. If it does not, it must be read from disk.
When the corresponding data is found in the disk, it will be loaded into the cache, which makes the subsequent query more efficient. Due to the limited memory, flexible LRU table is used to manage the cache page, to ensure that the cache is often accessed data.
The data is returned to the client, the connection is closed, the connection thread is released, and the process ends.
The principle of database index
In the whole process, the bottleneck is the reading and writing of data, which usually means sequential or random reading and writing to disks, and the speed of reading and writing to disks is relatively slow.
How about speeding up the process? I’m sure you’ve guessed that it’s index building.
Why do indexes speed up this process?
I believe everyone has visited the food city, where many restaurants are full of beautiful things in eyes, if you are not anxious, not hungry, there is no requirement for the performance of the search, you can slowly stroll in the mall, walk around one by one, know to find their own restaurant to eat. However, when you are hungry or you make an appointment with a restaurant, you must go straight to the restaurant. At this time, you will often look at the index map of the floor, quickly search for the location of your target restaurant, and then go straight to the topic, which will greatly save time. This is the function of the index.
So the index is to find its location quickly by value, so that it can be accessed quickly.
Index another role is not the real view the data, will be able to do some judgment, emporium have a restaurant, for example, if you look at the index, really need not to shop in a circle, to find out all such as sichuan cuisine, is also just see index, need not run a a sichuan restaurant.
So how does an index work in Mysql?
The Mysql index structure is usually a B+ tree.
An M order B+ tree has the following properties:
-
Nodes are divided into index nodes and data nodes. Index nodes are the internal nodes of a B tree. All index nodes form a B tree and have all the characteristics of a B tree. In an index node, keys and Pointers are stored, not specific elements. The data node is similar to the outer node of the B tree, which is empty and used in the B+ tree to hold the real data element, which contains the Key and other information about the element, but no Pointers.
-
The entire index node b-tree is used only to find which external node a data element with a certain Key resides on. The Key is found in the index node, and the matter is not over. It is necessary to continue to find the data node, and then read out the elements in the data node, or binary search, or sequential scan to find the real data elements.
-
The order M is only used to control the degree of the index node part, and it has nothing to do with how many elements each data node contains.
-
There is also a linked list that links all the data nodes together and can be accessed sequentially.
This definition is a little abstract, so let’s look at a concrete example.
As we can see from the figure, this is a third-order B+ tree, and an external data node contains at most 5 items. If the inserted data is on the data node, the b-tree of the index nodes will not change if it does not cause splitting or merging.
If an entry 76 is inserted in the external node 71 to 75, it causes a split, with 71 72,73 becoming a data node and 74,75,76 becoming a data node, which is equivalent to inserting a Key 74 for the index node.
If 43 is deleted from the external node 41 through 43, it causes a merge. 442,61,62,63 is merged into one node, which is equivalent to deleting Key 60 for the index node.
When searching, the height of B+ tree is very small, so the location can be relatively fast. For example, if we want to search the value 62, we will visit the right side if it is greater than 40 in the root node, visit the left side if it is less than 70, and visit the right side if it is greater than 60. On the second leaf node, we will find 62 and successfully locate it.
In Mysql’s InnoDB, there are two types of B+ tree indexes, one is called clustered index, the other is called secondary index.
The leaf node of the cluster index is the data node. The primary KEY is usually used as the cluster index. The leaf node of the secondary index stores the KEY field and the primary KEY value. Thus accessing data through secondary indexes requires two accesses to the index.
There is also a form of index called a composite index, or compound index, which can be created on multiple columns.
Such indexes are sorted by comparing the first column, if the first column is equal, the second column, and so on.
Advantages and disadvantages of database index
The most obvious advantage of database indexing is the reduction in I/O, which is examined in several scenarios.
For fields with = conditions, it is possible to reach the leaf node directly by searching the B+ tree with a few hard disk reads (equivalent to the B+ tree height), and then locate the data directly.
For range fields, because B+ trees are sorted, ranges can be quickly located through the tree.
Similarly, for Order Derby /group by/distinct/ Max /min, B+ trees are well sorted and can get results quickly.
Another common scenario is called index overwriting data. For example, when A AND B are used as conditional fields, A= A AND B= B is often found. When C AND D are also selected, A joint index (A, B) is usually built, which is A secondary index. Therefore, when searching, corresponding leaf nodes AND records can be found quickly through B+ tree of secondary index. However, there are some cluster index IDS in the record, so we need to search the B+ tree of the cluster index, find the real table records, and then read C,D from the record. If the joint index is (A, B, C, D), then all the data in the secondary index B+ tree can be directly returned, reducing the process of searching the tree once.
Of course indexing must come at a cost, there is no such thing as a free lunch.
The benefits brought by indexes are mostly the improvement of read efficiency, while the cost of indexes is the decrease of write efficiency.
Inserting and modifying data can mean index changes.
When inserting, clustered indexes are often built on primary keys, so it is best to use auto-growth for primary keys, so that data is always inserted last and in order, which is efficient. Do not use the UUID for primary keys. This order is random, resulting in random writes and poor efficiency. Do not use primary keys that are business related, because business related means that they will be updated and will be subject to deletion and reinsertion, which will be inefficient.
From the above introduction of the principle of B+ tree, we can see that the splitting cost of B+ tree is relatively high, and splitting is often generated in the process of insertion.
For data modification, it is basically equivalent to delete and then insert, the cost is relatively large.
The secondary index of some columns of strings often results in random writes and reads, resulting in great pressure on I/O.
Five, unscramble the principle behind database catch-22
Knowing how these two indexes work, we can explain why so many so-called database catch-ups look the way they do. Let’s explain them one by one.
When should you use composite indexes instead of individual indexes?
Suppose the conditional statement A= A AND B= B, if A AND B are two separate indexes, only one index will be used for B, while the combined index (A, B) can be used to traverse A tree, greatly increasing efficiency. For A= A OR B= B, the combined index does not work, so A single index can be used. In this case, both indexes work at the same time.
Why should indexes be discriminated? Should composite indexes be discriminated first?
If there is no distinction, such as gender, it is equivalent to splitting a large table into two parts, and searching for data still requires traversing half of the table to find it, rendering the index meaningless.
If you have composite indexes, do you still need single-column indexes?
If composite index is (A, B), the conditions for A = A, can use the composite index, because composite index is first carried out in accordance with the first column sorting, so there is no need for A separate set up an index, but for B = B can’t use, because only in the first column of the same case, is only the second column, and the second column of the same, It can be distributed on different nodes and cannot be quickly located.
The more indexes, the better?
Of course not, only add indexes where necessary, index will not only make the insertion and modification efficiency, and in the query time, there is a query optimizer, too many indexes will confuse the optimizer, may not be able to find the correct query path, so choose the slow index.
Why use autoincrement primary keys
Because string primary keys and random primary keys cause random data inserts and are inefficient, primary keys should be updated less frequently to avoid B+ tree sums merging and splitting frequently.
Why not use NULL when possible
NULL is difficult to handle in B+ trees and requires special logic to handle it, which reduces efficiency.
Why not index fields that are updated frequently
Updating a field means updating the corresponding index, which usually means deleting and then inserting. An index is originally a way to form a certain data structure in the write stage in advance, so as to make the read stage more efficient. However, if a field is written more than read, it is not recommended to use an index.
Why not use functions in query conditions
For example, if ID+1=10, the index is generated when the index is written in advance. For example, if ID+1 is used in the query phase, the index is unable to calculate all the indexes first and then compare them.
Why NOT use negative query conditions such as NOT
If your condition is equal to 20, go to the left. If your condition is equal to 50, go to the right. But your condition is not equal to 66, what should the index do? I had to go through it all before I knew.
Why don’t fuzzy queries start with wildcards
For a B+ tree, if the root is def, if the wildcard follows, for example, ABC %, then search left, if the wildcard follows, for example, efg%, then search right, if the wildcard follows, for example, % ABC, then I don’t know which side to go, so I’ll scan both.
Why change OR to IN, OR use Union
The optimization of OR query conditions is often difficult to find the best path, especially when there are many OR conditions, especially for the same field, it is better to use IN, the database will sort the conditions IN, and uniformly through the binary search method. Using Union for different fields enables each subquery to use an index.
Why should data types be as small as possible, using integers instead of characters, and using prefixed indexes for long characters?
If the data type is large, the number of pages will be larger, each page will contain less data, and the height of the tree will be higher. Therefore, the number of I/ OS to be read for searching data will be larger, and nodes will be easily split during insertion, resulting in lower efficiency. The reason for using integers instead of characters is that they are more efficient for indexing, such as IP addresses. If you have long character types that need to be queried using an index, consider indexing the prefix of the field rather than the entire field to avoid making the index too large.
Methodology of query optimization
To find SQL statements that need to be optimized, first collect the SQL statements in question.
The MySQL database provides the slow SQL log function. Using the slow_query_log parameter, you can obtain the list of SQL quotes whose execution time exceeds a certain threshold.
SQL statements that do not use indexes can be enabled with the long_QUERies_not_using_INDEXES parameter.
Min_examined_row_limit: SQL statements whose number of scanned records is greater than this value are entered in the slow SQL log.
Find the statement in question. Next, explore explainSQL to find the execution plan of the SQL, whether to scan the record by index. You can optimize the execution efficiency by creating indexes. Check whether the number of scan records is too large. Check whether the lock is held for a long time and whether lock conflicts exist. Whether the number of records returned is large.
Then you can customize the optimization. Create indexes on the fields that are not covered by indexes. If multiple fields are involved, create a joint index.
The number of scanned records is large, but the number of returned records is small, and the distinction is poor. Re-evaluate the fields involved in the SQL statement and select multiple fields with high distinction to create indexes
The number of scan records is very large, and the number of returned records is also very large. The filtering condition is not strong. Add SQL filtering condition
Schema_redundant_indexes Checks redundant indexes.
If multiple indexes involve fields in the same order, you can form a joint index schemA_UNUSED_INDEXES to see which indexes are never used.
Seven, the principle of reading and writing separation
Databases tend to write less and read more, so the first step in performance optimization is read/write separation.
Master/slave replication is implemented based on the service layer logs on the master node, which are read by an IO thread on the slave node and then written locally. Another thread reads from the local log and re-executes from the node.
The following figure shows the flow chart of master/slave asynchronous replication. Success is returned after the master instance writes to the engine, and events are sent to the slave instance for execution on the slave instance. This synchronization method is faster, but data loss may occur if there is no replication when the master is down.
Synchronous database replication is also different. It is returned to the client after the node is dropped from the disk, which of course will reduce the performance. Netease database team improves the performance through group submission, parallel replication and other technologies.
With master-slave replication, read/write separation policies can be set at the database DAO layer, as well as through database middleware.
In fact, the database log has many other uses, such as canal(Open source project: based on mysql database binlog increment subscription & consumption) subscription database binlog, can be used to update the cache, etc.
The basic cloud computing services of netease deeply integrate IaaS, PaaS and container technologies, provide elastic computing, DevOps tool chain and micro-service infrastructure services, help enterprises solve IT, architecture, operation and maintenance problems, and make enterprises more focused on business. As a new-generation cloud computing platform, you can click to try IT for free.
Related articles: [recommended] Android input method popup overwrite input box problem [recommended] let the App fly longer [recommended] quality report my opinion