Shulou(Shulou.com)06/01 Report--

The following mainly brings you in-depth knowledge of MySQL, I hope that these contents can bring you practical use, which is also the main purpose of my editor to master the knowledge of MySQL in depth. All right, don't talk too much nonsense, let's just read the following.

Basic principles of database

First, the composition of the database: storage + instance

Needless to say, the data needs to be stored; storage is not enough. Obviously, the provider needs to encapsulate the stored operation and provide API for addition, deletion, modification and query, that is, instances.

A storage can correspond to multiple instances, which will improve the load capacity and high availability of the storage. Multiple storage can be distributed in different data centers and regions, and disaster recovery will be achieved.

Second, press Block or Page to read the data

As you can see with your thighs, it is impossible for a database to read data by row (Why? ^ _ ^). In essence, databases, such as Oracle/MySQL, are based on physical blocks of fixed size (such as 16K) (Block or Page, which I call Block here) for scheduling and management. To know that Block is the concept of a database, how does it correspond to the file system? It is obvious that you need to point out "where is the address of this Block". When the address is found, reading a fixed size of data is equivalent to reading the Block.

The database is very smart, it will not only read the Block that needs to be read, it will also read and load nearby Block blocks into memory for us. In fact, this is to reduce the number of IO and improve the hit rate. In fact, the Block near a Block block is also hot data, which is necessary!

Third, disk IO is the performance bottleneck of database.

There is no doubt that the data is on disk, without disk IO. What magnetic head rotation, positioning track, addressing process, not to mention, we are programmers, also can not control these. But this process is really time-consuming, and memory reading is not the same order of magnitude, so there are many ways to reduce IO and improve database performance.

Hello,B+Tree

In MySQL, different storage engines implement indexes differently. Here we will focus on MyISAM and Innodb.

We know that for the MyISAM engine, data files and index files are separate. As can be seen from the figure, after the data is found by the index, the physical address of the data is obtained, and then the records in the data file can be located according to the address. This method is also called nonclustered index.

For the Innodb engine, the data file itself is an index file! To put it colloquially, on the leaf node, MyISAM stores the physical address of the record, while Innodb stores the data content, which is called "clustered index".

Another point to note is that for Innodb, the leaf node of the primary key index stores the data content, while the leaf node of the normal index stores the primary key value! In other words, for the general index field lookup of Innodb, the primary key is first found through the B+Tree of the ordinary index, and then through the B+Tree of the primary key index. As you can see here, for Innodb, the establishment of primary keys is very important!

For MyISAM, the only difference between the primary key index and the ordinary index is that the primary key only needs to find a record to stop, while the ordinary index allows repetition. After finding a record, you need to continue to search, and there is no difference in structure, as shown in the above figure.

Go deep into B+Tree

Ask a few questions:

Why does B+Tree put the real data on the leaf node instead of the inner node?

Why do we say that index fields should be as short as possible, preferably monotonously increasing?

Why is there a leftmost matching principle for composite indexes?

The scope query (>, 26 and sex = 'man',) actually makes use of only the name column of the composite index.

If you want to use the index, you have to be "clean".

What do you mean "clean"? Just don't let the index participate in the calculation! For example, applying a function to an index is likely to cause the index to fail. Why?

In fact, you don't have to think about it. What is stored on B+Tree is data. To compare, you need to apply all the data to functions, which is obviously too expensive.

If you want to build an index, take a look at the differentiation index, although it is good and cheap, but don't do it. Count (distinct col) / count (*) can calculate the degree of discrimination of col, which is obviously 1 for the primary key. If the degree of distinction is too low, you can consider whether it is still necessary to establish an index.

Hash index

This is not to analyze the Hash index in depth, but to show that the idea of Hash is ubiquitous!

In MySQL's Memory storage engine, there is a hash function, give a key, through the hash function to calculate the address, so usually, the hash index lookup will be very fast, O (1) speed. But there are also hash conflicts, which, like HashMap, are resolved in the form of a single linked list.

Think about it, does the hash index support range queries?

Obviously not supported, it can only be found by a KEY. Just like HashMap, will it be quick to find key that contains "zhangfengzhe"?

SQL optimization artifact: explain

There are many scenes optimized by SQL, and there are many skills on the Internet. I can't remember them at all!

To thoroughly solve this problem, I think only the index behind the data structure and principle to do a proper understanding, when writing SQL or SQL slow query, we have the basis to analyze, and then use explain tools to verify, it should not be a problem.

The results of the explain query can tell you which indexes are being used, how the table is scanned, and so on. Here I will demonstrate a Demo.

Data Table student:

For the above in-depth knowledge of MySQL, we do not think it is very helpful. If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.