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

This article introduces the relevant knowledge of "how to solve MySql slow query". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Primary key id, we use bigint,8 bytes

A data size 1KB

First floor

A page of 16K, the size of each index key is 8 bytes (bigint) + 6 bytes (pointer size), so the first layer can store 16 '1024 prime 14' 1170 index keys. To query how much data can be stored in the leaves of Mysql, you can use sql: SHOW GLOBAL STATUS LIKE 'Innodb_page_size', is about 16k

Second floor

The second layer only stores index keys, how many index keys can be stored? 1170 (so many pages with pointers extended at the first layer) 1170 (number of index keys per page, as calculated in the first step) = 1368900

What if the second tier stores data? 1170 (so many pages with first-layer extended pointers) 16 (page size of 16KB / data size of 1KB) = 18720, which can store more than 10, 000 numbers.

The third floor

How much data can be stored by looking directly at the third tier? 1170117016021902400, isn't it very powerful? there should be applause and flowers here. More than 20 million data can be queried by IO three times, that is, such a large amount of data is fast if it is found through the primary key index. This is why the performance of type=const is the best when explain is a sql.

As we can see from the above, we need 3 times of disk IO to find out about 20 million of the data, but obviously not 3 times of disk IO if we use other structures.

2. Introduction to MyISAM storage engine (non-clustered index)

Features: index files and data files are separate

Defect: transaction and row-level locks are not supported, and there is no doubt that it cannot be safely recovered after a crash.

It is the storage engine of the table, such as the following statement to create the table, on the specified storage engine

CREATE TABLE `user` (

`id`INT (64) DEFAULT NULL

) ENGINE=MYISAM DEFAULT CHARSET=utf8

3. Introduction to InnoDB storage engine (clustered index)

Features: 1. The table data file itself is an index structure file organized by B + Tree.

two。 Clustered index-leaf nodes contain complete data records

Here is a question:

1. Why InnoDB tables must have primary keys, and it is recommended to use self-increasing primary keys for shaping

InnoDB aggregates data through a primary key, and if no primary key is defined, InnoDB selects a unique non-empty index instead. If there is no such index, InnoDB implicitly defines a primary key as the clustered index.

By using the self-increasing primary key, you can achieve a small balance and be more efficient when the binary tree is split.

This is the end of the content of "how to solve the MySql slow query". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.