Shulou(Shulou.com)05/31 Report--

This article mainly introduces "what is the knowledge of MySQL index". In the daily operation, I believe that many people have doubts about the knowledge of MySQL index. The editor consulted all kinds of data and sorted out simple and easy-to-use methods of operation. I hope it will be helpful for you to answer the question of "what is the knowledge of MySQL index?" Next, please follow the editor to study!

I. basic knowledge of Index 1.1 advantages of Index

Hongmeng official Strategic Cooperation to build HarmonyOS Technology Community

It greatly reduces the amount of data that the server needs to scan, and speeds up the retrieval speed of the database.

Help the server avoid sorting and temporary tables

Change random io to sequential io

1.2 usefulness of indexes

Hongmeng official Strategic Cooperation to build HarmonyOS Technology Community

Quickly find lines that match the WHERE clause

Remove rows from consideration, and if you can choose between multiple indexes, mysql usually uses the index that finds the fewest rows

If the table has multiple column indexes, the optimizer can use any leftmost prefix of the index to find rows

Retrieve row data from other tables when there is a table join

Find the min or max value of a specific index column

If sorting or grouping is done on the leftmost prefix of the available index, sort and group the table

In some cases, queries can be optimized to retrieve values without querying data rows

1.3 Classification of indexes

The database creates an index by default, but not on the primary key, but on the unique key, because the properties of the primary key are unique and not empty

Primary key index: is a special unique index, no null values are allowed. (primary key constraint, which is a primary key index)

Unique index: the value in the index column must be unique, but null values are allowed.

Normal index: the basic index type in MySQL, with no restrictions, allows duplicate values and null values to be inserted in the columns that define the index, simply to query the data faster.

Full-text indexing: can only be used on the MyISAM engine, can only be used on CHAR,VARCHAR,TEXT type fields

> what is a full-text index? you can find the record row to which the field belongs through one of the keywords in a pile of text. For example, the record can be found by "LOL LPL herding and small farmers" through herding small farmers. What we're talking about here is possible, because the use of full-text indexing involves a lot of details, and we just need to know the general meaning. In general development, full-text index is not used, because it takes up a lot of physical space and reduces record modification, so it is rarely used.

Composite index: an index created on a combination of fields in a table that is used only if the left field of these fields is used in the query condition, following the leftmost prefix set when using the combined index.

For example, for the index composed of id, name and age3 fields, the index row is stored in the order of id/name/age, and the index can be indexed as a combination of fields (id,name,age), (id,name) or (id). If the field to be queried does not constitute the leftmost prefix of the index, the index will not be used, for example, the age or (name,age) combination will not use the index query.

1.4 Technical terms for interview

Back to the table: after the database finds the row of the specified record according to the index (non-primary key), it also needs to go to the data block again according to the primary key to obtain data, which is called back to the table.

Override Index: see an article I wrote: I fell on a sql question in the third round of the interview-- sql performance Optimization.

Leftmost matching: in a federated index, if the leftmost index in the federated index is used in your SQL statement, then the SQL statement can use the federated index to match. If you encounter a range query (>,

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.