Mysql Database Study Notes index and mysql Study Notes

Mysql Database Study Notes index and mysql Study Notes

What is an index:

There are two types of SQL indexes: clustered index and non-clustered index. The index aims to improve the performance of the SQL Server system, speed up data query, and reduce the system response time.

Clustered index: the logical order of the key values in the index determines the physical order of the corresponding rows in the table.

Non-clustered index: the logical order of the index is different from that of the physical storage on the disk.

Here are two simple examples:

We come up with a Xinhua Dictionary. Its Directory is actually an index: Non-clustered index. We can quickly locate the words we want to query through the directory. The contents of the dictionary are generally sorted by pinyin, which is actually an index: clustered index.

Benefits of creating an index:

1. Accelerate the search speed of frequently searched fields.

2. Use the uniqueness of indexes to control the uniqueness of records

Disadvantages of Indexing:

At least the following additional storage space will be created for an index (a single index occupies 5% to 15% of the original table space. Imagine if you create three or four indexes for a table) the additional maintenance time is only for one purpose: reduce the query time in the case of small-scale queries. If the query returns more than 1% of the data, the index is meaningless.

When to use indexes:

If a field or a group of fields appear in a where clause that will be frequently called, they should be indexed to get results faster. At the same time, the use of unique Indexes has been mentioned earlier, so that they can be used properly to avoid unexpected occurrence. I personally do not recommend full-text indexing. Especially for Chinese characters, full-text indexing is too costly. I would rather choose the intra-site search function provided by the search engine. Although search engine indexing is not timely, I don't think it is unacceptable. Again, I think we should try to do more things when inserting, modifying, and deleting data. These operations are relatively less frequent, and the data volume of a single operation is smaller. Do not leave all pressure to query statements.

 

My blog address: index of Mysql Database Study Notes)