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

The following together to understand the Mysql basic design and optimization of simple ideas, I believe that you will benefit a lot after reading, the text is not much in the essence, hope that Mysql basic design and optimization of simple ideas this short article is what you want.

1. Distinction should be made between fixed length and variable length

2. Separate the infrequent columns and the commonly used columns

3. Increase redundancy and anti-normalization

4. The btree index is stored on disk with a tree structure, in which the operation is to distribute it with 2, find a middle point, and then divide the larger one to one side and the small one to one side, and then when you query, start with the number head and find the size separately. This is suitable for interval search, but not for one by one.

5. Hash index, that is, when you store the data on the hard disk, first give you an address, and then the next time you come to find it, you can directly use that address to find that value. This is suitable for a value search, not suitable for interval query, such as where id=1.

6. (1) limit optimization, business logic is used to solve what can be solved by business logic (for example, Baidu search only displays 76 pages)

(2) lean on the index, such as id > 1100, you use id's hash primary key index (default)

(3) inner join

7. The index of the database is stored in a binary tree. The clustered index actually contains other data items of this node under the node of the tree, but the non-clustered index is just a tag, and then you have to go back to the disk to find the corresponding data item value, and the secondary index of the clustered index is just a reference, that is, the id of the primary index is stored under the secondary index. Then the secondary index uses id to find the corresponding data item on the tree.

Non-clustered indexes such as the myisam storage engine, indexes and data are different. Find the index and then find the data.

The clustered index is the node that stores the data, and contains the index, and then there is other data under the index-innodb.

8. The index will take up a lot of disk space, and then the change of data will also operate the index.

9. The core idea of the combinatorial index is the left-half principle. When your last condition produces like, it will lead to unnecessary use of the latter.

Select * from test where id = 1 and name = "aa" and text like "% sss" and desc = "1"

It will cause the desc behind you not to use the index.

10. Hash's idea is to jump out of time and space complexity, but it needs a function with a good hash, the gap is just right, and then it can't conflict.

11. Innodb even if the data you insert is out of order, it is still sorted out after you find it, because it can return the data according to the index of the number, which is also an advantage of the clustered index.

Solid state drives support random reads instead of reading in sequential circles like traditional disks.

After reading this article on basic design and optimization of Mysql, many readers will want to know more about it. If you need more industry information, you can follow our industry information section.

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.