Mysql study notes (4) talk about database indexes

Little mood (you can jump directly to the dividing line)

I am in a better mood today. Some strong bad emotions that could not be resolved gradually dissipated after solving a complex logical problem at night.

When I went to eat at noon today, Brother Kun said casually: 'I have finally realized a truth after so many years. People should not work so hard when they are young, because they have no time to do other things.'

What other things should I ask? Brother Kun glanced at me and continued to say casually that he was so busy that he had no time to fall in love.

I think it’s okay not to work so hard? House prices are rising every year, and the cost of living is increasing day by day. If you don’t work hard to make your own value, the money you earn exceeds the economic growth rate. Then we can only continue to be a loser at the bottom of society, continue to live in pain in the good days of youth.

Only by working hard, hard work is not enough, only by working hard enough can you gain a little advantage in the competition with others.

It’s very painful to work so hard, and life is also very monotonous. Which girl would like this kind of life?

I think the decline in my emotional intelligence and interpersonal skills in the past six months is related to the fact that I put all my body and mind into programming.

I originally wanted to use these words to chat with Brother Kun, but the words finally got stuck in my throat and I didn’t say it.

Everyone has his own path to take, whether he is lonely or lively, it is his choice.

When you choose a path, you have to abandon many things.

I am willing to get it, so I am even more willing to lose it.

After the rant, the text begins:

What’s the index? Students who are easily fooled by professional terms should take the initiative to read the popular explanation on Baidu: a tool for retrieving books and materials. Classify and extract the contents or items from books and periodicals, indicate the number of pages, arrange them in a certain order, and attach them to a book, or compile them into a separate volume for the convenience of readers. Formerly known as general inspection or preparation inspection. Also called lead.

The chapter "MySQL Core Technology and Best Practices" is great, so most of today's content is a summary of what is in the book.

Understand the index in a simpler way: The index is the part in front of Xinhua Dictionary that looks up words by pinyin and radicals.

In life, if you don’t know the pronunciation of a rare word, and you don’t use strokes to search it, it may be difficult to find it. If you let a machine do this, it would be to look up all the words in the entire dictionary, and after matching return. This results in a full table scan, which is very inefficient.

But if we look for it through radicals, and then through strokes, the amount of data we may look up is very small. Let the machine do it and just scan the relevant data items, which is very fast.

Therefore, the query speed has no absolute relationship with the size of the data. The query speed depends on retrieving the required data and how many table data are scanned. The smaller their ratio, the higher the efficiency.

Several important knowledge points:

1. When MySQL retrieves data in the table, it first searches in the index according to the value of the index "keyword". If it can be found, it can be directly located. If there is no starting page, the entire table will be scanned.

Listening to "Forget Him" ​​Liu Xijun's song, I began to summarize the following content.

2. The index is actually a copy of the field value in the database table, and the changed field is called the key of the index.

3. A data table can create multiple indexes.

4. What is prefix index:

How to use the "Radical Index" in Xinhua Dictionary. First, we first confirm what the radical of the word we want to find is, then look at the strokes of the word outside the radical, and then search for the corresponding word. Similarly for database tables, the value of the keyword in the index can be part of the "keyword" of the index. This kind of index is called a "prefix index". For example, I can create a prefix index for the name table in the user table. Count the surnames of Zhang in the name. Number of people, number of people with the surname Li.

5. Can the index be a combination of fields?

For a database, if an index in the database table consists of multiple keywords. This index is called a composite index. Regardless of whether the index is a field or a combination of several fields, the value of the Bianche keyword must be a copy of the corresponding field value in the table. , cannot create indexes across tables.

7. Does index data require additional storage space?

This question is nonsense. For example, if you want the cow to run well, but also want the cow to not eat grass, the index file must take up storage space. . For the index of the database table, the index key is placed in the external storage. For the MyISAM database table, the index is stored in the external storage MYI index file. For the InnoDB storage engine, the index data is stored in the external storage InnoDB table. In the space file (which may be a shared table space file or an exclusive table space file), in order to improve the data retrieval efficiency, whether it is the index of the MyISAM table or the index of InnoDB, the keywords of the index are sorted in ascending order and placed in external storage.

8. Which fields in the table are suitable for selection as the index of the table? What is a primary index and what is a clustered index?

For MyISAM tables, MySQL will automatically index the backup of the primary key values ​​of all records in the table and the starting page of each record. An "index table" is generated and stored in external memory like a radical search table. This index is called the primary key. The MYI index file of the MyISAM table and the MYD data file are located in two files. Through the "table record pointer" in the MYI index file, you can find the physical address where the table record in the MYD data file is located.

The "primary index" of Innodb table is different from the primary index of MyISAM table. The order of the primary index keys of the InnoDB table is consistent with the order of the primary key values ​​recorded in the InnoDB table. This kind of index is called a "clustered index", and each table can only have one clustered index.

9. What is the relationship between index and data structure?

The storage rules for all keywords in the database in the index file are very complex. In order to effectively improve the efficiency of database retrieval. Indexes are usually "organized" using complex data structures such as balanced trees (btrees) or hash tables. When operating the database, the underlying operations are performing such complex operations, but we cannot feel it.

10. Are the more indexes, the better?

If there are too many indexes, when updating data (adding, modifying, deleting), in addition to modifying the data in the table, all indexes of the table also need to be maintained to maintain the consistency of table field values ​​and index key values. consistency. On the contrary, it reduces the data update speed.

Practice table names. When the operations of modifying table records are particularly frequent, too many indexes will cause a significant increase in the number of hard disk I/Os, which will significantly reduce server performance. It may even cause downtime.

11. Principles for selecting index keywords.

Principle 1. The higher the dispersion of a field in the table, the more suitable the field is as a keyword to be selected as an index.

When a database user creates a primary key constraint, MySQL will automatically create a primary index (primary index) and the index name is primary; when creating a unique constraint, MySQL will automatically create a unique constraint (unique index). By default, The index name is the field name of the unique binding constraint.

Principle 2. Fields that take up less storage space are more suitable to be selected as keywords for indexing.

Principle 3. Fields with fixed storage space are more suitable to be selected as keywords for indexing.

Principle 4: Indexes should be created for fields frequently used in where clauses, indexes should be created for grouping fields or sorting fields, and indexes should be created for the connection fields of two tables.

Last night, I couldn’t fall asleep. While browsing the space, I learned that my friend was getting divorced. She was the girl I liked in college. We got together for a short period of time due to her willfulness and other reasons, and then we separated. I have only been married for a year and have a child. My heart suddenly aches, and I fantasize about going back to the past. On that night when the stars filled the sky, I was stubborn and willful, and she was warm and fragrant. Forget it, it’s better not to be sad about these things, everyone is destined to die. I'd better code properly.

The purpose of introducing index is to improve the efficiency of retrieval, so the selection of index keywords is closely related to the select statement. This sentence has two meanings. The design of the select statement can determine the design of the index; the design of the index also affects the design of the select. The where clause, group by clause, and order by clause in the select statement can also affect the design of the index. Indexes should be created for the link fields of the two tables. Once the foreign key constraint is created, MySQL will automatically create an index corresponding to the foreign key. This is because the foreign key field is usually the connection field of the two tables.

Principle 5. Fields that are updated frequently are not suitable for creating indexes, and fields that do not appear in the where clause should not be indexed.

Principle 6. The principle of leftmost prefix.

There is another advantage of compound index, which is reflected by the concept called "leftmost prefixing". Suppose you create a compound index (index) for multiple fields of a table (such as firstName, lastName, address). name: firstname_lastname_address). When the condition of the where query is a combination of the following fields, mysql will use the fname_lastname_address index. The fname_lastname_address index will not be used in other cases.

firstname,lastname,address

firstname,;lastname

firstname

Principle 7. Try to use prefix indexes.

Create an index only on the surname part of the name, which can save the storage space of the index and improve the retrieval efficiency.

Like the design of the database, the design of the index also requires the accumulation of experience and wisdom of database developers. At the same time, it is necessary to design a better index based on the respective characteristics of the system, in order to "speed up retrieval efficiency" and "reduce the update speed". ” strike a balance. This greatly improves the overall performance of the database.

Index and constraint relationship:

What is the relationship between indexes and constraints in mysql. Constraints are divided into primary key constraints, unique constraints, default value constraints, check constraints, non-null constraints, and foreign key constraints. Among them, primary key constraints, unique constraints, foreign key constraints and indexes are more closely related.

Constraints are mainly used to ensure the integrity of the database when business logic operates the database.

The index stores keyword data in external memory in a certain data structure (such as btree, binary tree, hash, etc.) to improve data retrieval performance.

Constraints are concepts at the logical level, while indexes are not only logical concepts, but also a physical storage method, and the fact that they exist requires a certain amount of storage space. E Unqiue Index Index's only index

Index index general index

Index Complex_index (PRICE, PUBLISH_TIME) Composite Index

Most of the contents of this article are excerpted in "MySQL Core Technology and Best Practice". Friends who are interested can Buy this book and read it, it is really well written.

The above is the content of Mysql study notes (4) about database indexing. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!