MySQL (v) Details of the index in MySQL

Preface

Before writing to MySQL on the table additions and deletions (query the most important), it feels like MySQL is almost finished, did not want to continue to learn the mentality, the reason may be due to other people's influence, think for MySQL, know some complex query, enough, but I think, whether or not use, Now learn not to understand the things, the explanation is their weak place, more learning to be stronger than others

--wh

I. What is an index? Why index?

Indexes are used to quickly find a row with a specific value in a column, and without using an index, MySQL must start reading the entire table from the first record until it finds the related rows, the larger the table, the more time it takes to query the data, and if the columns in the table have an index. MySQL can quickly reach a location to search for data files without having to look at all the data, which can save a significant portion of the time.

For example: There is a person table, which has 2W records, which record 2W personal information. There is a phone field that records each person's phone number and now wants to find out the information for the person with the phone number xxxx.

If there is no index, then the first record in the table will be traversed down the line until the information is found.

If you have an index, the phone field is stored in a way that allows you to quickly find the corresponding data when querying the information on that field, rather than traversing the 2W data. There are two types of storage for indexes in MySQL: BTREE, HASH. That is, using a tree or hash value to store the field, to know how detailed it is to find, you need the knowledge of the algorithm. Now we just need to know what the index does, and what the function is.

Second, the advantages and disadvantages of MySQL index and the use of principles

Advantages:

2. All MySQL column types (field types) can be indexed, that is, you can set an index to any field

3, greatly speed up the data query speed

Disadvantages:

1. It takes time to create indexes and maintain indexes, and increases the time it takes to increase the amount of data

2, the index also need to occupy space, we know the data table data will also have the maximum on-line settings, if we have a large number of indexes, the index file may be faster than the data file to reach the line value

3, when the data in the table to increase, delete, modify, the index also needs dynamic maintenance, reduce the data maintenance speed.

Principle of Use:

Through the advantages and disadvantages mentioned above, we should be able to know that not every field of the index is good, not the more the better, but need to use their own reasonable.

1, to the frequently updated table to avoid excessive indexing, the fields that are frequently used for querying should create an index,

2, the data volume of the table is best not to use the index, because the data is small, may query all the data spend more time than the time to traverse the index, the index may not have an optimization effect.

3, in the column with less value (on the field) do not index, such as in the student table "gender" field only male, female two different values. Conversely, there are many different values on a field, but indexes are established.

　　

Said above is a very one-sided some things, index must have a lot of other advantages or disadvantages, and the use of principles, first basically understand the index, and then after the real use, will slowly know the other role. Note, it is important to learn this, it is necessary to know what the index is, what the index is, what the role, why index and so on, if you do not know, repeat the above to see the written text, a good understanding. A table is enough to create multiple indexes that are stored in an index file (where the index is specifically placed)

Iii. Classification of indexes

Note: Indexes are implemented in the storage engine, which means that different storage engines use different indexes

MyISAM and InnoDB storage Engine: Supports only btree indexes, that is, Btree is used by default and cannot be replaced

MEMORY/HEAP storage Engine: Supports hash and btree indexes

1, index We are divided into four categories of single-column index (normal index, unique index, primary key index), composite index, full-text index, spatial index,

1.1, single-column index: An index contains only a single row, but a table can have multiple single-row indexes. Don't confuse it here.

1.1.1, normal index:

There is no limit to the basic index type in MySQL, which allows duplicate and null values to be inserted in the column that defines the index, simply to query the data a little faster.

1.1.2, UNIQUE index:

The values in the indexed column must be unique, but allow null values,

1.1.3, PRIMARY key index:

is a special unique index and is not allowed to have null values.

1.2. Combined Index

Indexes created on multiple field combinations in a table are used only when the left field of these fields is used in the query criteria, and the combined index is used to follow the leftmost set of prefixes. If this is not understood, wait for an example to be explained in detail later

1.3. Full-Text Indexing

Full-text indexing, only in the MyISAM engine can be used, only in the Char,varchar,text type field on the use of full-text indexing, describes the requirements, say what is full-text index, is in a pile of text, through one of the keywords, etc., you can find the field belongs to the record line, such as " You are a big idiot, two goods ... " It is possible to find this record through the large idiot. Here is possible, because the use of full-text indexing involves a lot of detail, we just need to know the general meaning, if interested in further use of it, then look at the following test the index, will give a blog post for your reference.

1.4. Spatial index

A spatial index is an index of a field built into a spatial data type, with four types of spatial data in MySQL, GEOMETRY, point, LINESTRING, and POLYGON.

When creating a spatial index, use the spatial keyword.

requirements, the engine is MyISAM, and the column that creates the spatial index must be declared as not NULL. See below for specific details

Iv. index Operations (create and delete)

4.1. Create an index

4.1.1, creating indexes when creating a table

Format: CREATE table table name [field name data type] [unique| Fulltext| Spatial| ...] [index| KEY] [index name] (field name [length]) [asc| DESC]

|--------------------------------------|  　　|-----------------------------------| |------------| |---------| |---------------| |------------|

Normal CREATE TABLE statement set what index (unique, full-text, etc.) index keyword index name to which field to set the index to sort the index

4.1.1.1, creating a normal index

CREATE table book CREATE TABLE book

(　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　(

BookID int NOT NULL, BookID int is not NULL,

BookName varchar (255) is not NULL, bookname varchar (255) is not NULL,

Authors varchar (255) is not NULL, authors varchar (255) is not NULL,

Info varchar (255) NULL, info varchar (255) NULL,

Comment varchar (255) NULL, comment varchar (255) NULL,

Year_publication year isn't null, year_publication year is not NULL,

INDEX (year_publication) KEY (year_publication)

); );

The above two ways to create degrees can, through this example can be compared to the format, almost understand the meaning of the format.

　　　　　　　　　　　　　　　　　　　　　　　　

By printing the result, we will automatically use the field name as the index name if we do not write the index name when we create the index.

Test: See if the index is used for querying.

EXPLAIN SELECT * from book WHERE year_publication = 1990\g;

Explanation: Although there is no data in the table, there is a explain keyword that is used to see if the index is being used and to output information about the index it is using.

　　　　　　　　　　　　　　　　　　　　　　　

The id:select identifier. This is the query sequence number for SELECT, which is a statement in which the select occurs several times. In the second statement, select has only one, so it is 1.

Select_type: The type of select query used, simple is represented as a simple select, not a practical union or subquery, is simply a select. This means that the index is used when the select query is queried. Other values, PRIMARY: The outermost select. When you have a subquery, more than two select are present. Union:union (two table connections) the second or subsequent SELECT statement subquery: In the subquery, the second select.

Table: The name of the data table. They are sorted in the order they are read, because only one table is queried, so only the book is displayed.

Type: Specifies the association between this data table and other data tables in which all records that match the retrieved values are taken out and combined with records taken from the previous table. Ref is used when the connector uses the leftmost prefix of a key or if the key is not a primary key or a unique index (in other words, the connector cannot get only one record based on the key value). This is a good connection type when only a few matching records are queried based on the key value. (Note that the individual here is not very understanding, Baidu has a lot of information, all is plain English, and so on later used this kind of information, in return to add, here do not understand the impact on the back is not small. Possible values are system, const, EQ_REF, index, and all

Possible_keys:mysql the individual indexes that can be used when searching for data records, there is only one index in the table, year_publication

Key: The actual selected index

Key_len: Shows the length of the index used by MySQL (that is, the number of indexes used), and the length of the index is null when the value of the key field is null. Note that the value of Key_len can tell you what indexes MySQL will actually use in the federated index. 1 indexes are used here, so it is 1,

Ref: gives the name of the data column in another data table in the associated relationship. Constant (const), used here is 1990, is the constant.

Rows:mysql the number of rows of data that are expected to be read from this data table when executing this query.

Extra: Provides information about the associated operation, and nothing is written.

The above a lot of things can see how much to see how much, our main thing is to look at the two properties Possible_keys and key, which shows the key is year_publication. The description uses the index.

4.1.1.2, creating a unique index

CREATE TABLE T1

(

ID INT not NULL,

Name CHAR (+) is not NULL,

UNIQUE INDEX uniqidx (ID)

);

Explanation: The ID field was indexed and the index name was uniqidx.

SHOW CREATE TABLE t1\g;

　　　　　　　　　　　　　　　　　　　　　　

To view the indexes that are used in the query, you must first insert the data into the table and then query the data, or look for an ID value that does not have an index that is not used.

INSERT into T1 VALUES (1, ' xxx ');

EXPLAIN SELECT * from t1 WHERE id = 1\g;

　　　　　　　　　　　　　　　　　　　　　　

As you can see, a unique index is used when querying by ID. And also experimented with querying for an ID value that doesn't have an index, I think the reason is that all IDs should be stored in a const tables, where there is no such ID value, then there is no need to find it.

4.1.1.3, creating a primary key index

CREATE TABLE T2

(

ID INT not NULL,

Name CHAR (10),

PRIMARY KEY (ID)

);

INSERT into T2 VALUES (1, ' QQQ ');

EXPLAIN SELECT * FROM t2 WHERE id = 1\g;

　　　　　　　　　　　　　　　　　　　　　　

Through this primary key index, we should reflect, in fact, we previously declared the PRIMARY key constraint, is a primary key index, but we have not learned before, do not know.

4.1.1.4, creating a single-column index

This does not really need to say, the first few is a single-column index.

4.1.1.5, creating a composite Index

A composite index is the creation of an index on multiple fields

Create a table T3, set up a composite index on the ID, name, and age fields in the table

CREATE TABLE T3

(

ID INT not NULL,

Name CHAR (+) is not NULL,

The age INT is not NULL,

Info VARCHAR (255),

INDEX Multiidx (Id,name,age)

);

SHOW CREATE t3\g;

　　　　　　　　　　　　　　　　　　　　　　

Explaining the leftmost prefix

The combination index is to follow the leftmost prefix, using the leftmost Lie in the index to match the row, such a set of columns called the leftmost prefix, do not understand that it is okay, for example, here is the index of the ID, name and Age3 fields, the index row in the order of Id/name/age, The index can index the following field combination (Id,name,age), (Id,name), or (ID). If the field to be queried does not constitute the leftmost prefix of the index, then the index will not be used, for example, the age or (name,age) combination will not use the index query

In the T3 table, the query ID and Name field

EXPLAIN SELECT * from t3 WHERE id = 1 and name = ' Joe ' \g;

　　　　　　　　　　　　　　　　　　　　　　

In the T3 table, query (age,name) fields so that index queries are not used. Look at the results.

EXPLAIN SELECT * from t3 WHERE age = 3 and name = ' Bob ' \g;

　　　　　　　　　　　　　　　　　　　　　　

4.1.1.6, creating a full-text index

Full-text indexes can be used for full-text search, but only the MyISAM storage engine supports fulltext indexes and serves only char, varchar, and text columns. The index is always on the entire column and does not support the prefix index.

CREATE TABLE T4

(

ID INT not NULL,

Name CHAR (+) is not NULL,

The age INT is not NULL,

Info VARCHAR (255),

Fulltext INDEX Fulltxtidx (info)

) Engine=myisam;

SHOW CREATE TABLE t4\g;

　　　　　　　　　　　　　　　　　　　　

Use what is called full-text search. It is in many words that the record can be found by keyword.

INSERT into T4 VALUES (8, ' AAA ', 3, ' text was so good,hei,my name was Bob '), (9, ' BBB ', 4, ' My name is Gorlr ');

SELECT * from T4 WHERE MATCH (info) against (' GORLR ');

　　　　　　　　　　　　　　　　　　　　

EXPLAIN SELECT * from T4 WHERE MATCH (info) against (' GORLR ');

　　　　　　　　　　　　　　　　　　　　

Note: When using full-text search, the match function is required, and its full-text search is more restrictive, such as only through the MyISAM engine, such as the full-text index can only be set on Char,varchar,text. For example, the search keyword by default at least 4 characters, such as the search keyword is too short to be ignored. Wait, if you're going to experiment, you might not be able to do it. Interested students can take a look at this article, the use of full-text search

4.1.1.7, creating a spatial index

The spatial index must also use the MyISAM engine, and the field of the spatial type must be non-empty. This spatial index is specific to what I do not know, may be related to the development of the game, may be related to other things, and so encountered the nature to know, now only required to be able to create.

CREATE TABLE T5

(

G GEOMETRY not NULL,

SPATIAL INDEX Spatidx (g)

) ENGINE = MyISAM;

SHOW CREATE TABLE t5\g;

　　　　　　　　　　　　　　　　　　　　

4.1.2, creating an index on a table that already exists

Format: ALTER table name add[unique| Fulltext| SPATIAL] [index| KEY] [index name] (index field name) [asc| DESC]

With the foundation above, there is no need to state too much.

Command one: SHOW INDEX from table name \g

View the indexes created in a table

SHOW INDEX from Book\g;

　　　　　　　　　　　　　　　　　　　　　　

Pick the key, we need to know about 5, with red color marked, if you want to know more, you can check the information, I personally feel that these after the actual work encountered in doing a detailed understanding of the.

Table: Tables to create indexes

Non_unique: Indicates that the index is not unique, 1 represents a non-unique index, and 0 represents a unique index, meaning that the index is not a unique index

Key_name: Index Name

Seq_in_index represents the position of the field in the index, the value is 1 for a single-column index, and the combined index is the order of each field in the index definition (this only needs to know the single-column index is 1, the combined index is something else)

COLUMN_NAME: Represents a column field that defines an index

Sub_part: Indicates the length of the index

Null: Indicates whether the field can be a null value

Index_type: Represents the index type

4.1.2.1, adding an index to a table

Take the book table above. Already have a year_publication, now we add a normal index for the table

ALTER TABLE book ADD INDEX bknameidx (BookName (30));

　　　　　　　　　　　　　　　　　　　　

By looking at the output, you will know that the index was added successfully.

This is just an example of an ordinary index, and adding other indexes is the same. It's just a gourd painting. There is no explanation here.

4.1.2.2, use the CREATE index.

Format: CREATE [unique| Fulltext| SPATIAL] [index| KEY] Index name on table name (Create indexed field name [length]) [asc| DESC]

Explanation: In fact, the format changed a bit, do the same thing as above, do an example.

When you add a normal index to the Book table, the field is authors.

CREATE INDEX bkbooknameidx on book (bookname);

　　　　　　　　　　　　　　　　　　　　

SHOW INDEX from Book\g; View the index in the Book table

　　　　　　　　　　　　　　　　　　　　

Explanation: The first article is not cut, because the graph is too big, here just see the new index we added to prove success. Other indexes are created the same way.

4.2. Delete Index

The method for adding and querying an index in a table is described earlier.

Two ways to add

1 How to create an index while creating a table,

2 How to add an index to a table after you create the table in two ways,

How to Query

Show INDEX from table name \g; \g just let the output format look better

Now, let's say two things about how to remove an index from a table.

Format one: ALTER table name DROP index name.

Very simple statement, now with an example to see, or the Book table operation, delete the index we just added for it.

1. Delete the index with the name BKBOOKNAMEIDX in the Book table.

ALTER TABLE book DROP INDEX bkbooknameidx;

SHOW INDEX from Book\g; When you look at the index in the Book table, you'll see that the Bkbooknameidx index is gone.

　　　　　　　　　　　　　　　　　　　　　　

Format two: DROP index index name on table name;

To delete an index named BKNAMEIDX in the Book table

DROP INDEX bknameidx on book;

SHOW INDEX from Book\g;

　　　　　　　　　　　　　　　　　　　　　　　　

V. Summary

The MySQL index is almost finished here, so let's summarize what we should know so far.

1. What do indexes do? Why should I have an index?

This is very important, you need to understand, do not understand the top of the explanation

2, the classification of the index

3, the operation of the index

Create an index in a table, add an index, delete an index, delete an index

MySQL (v) Details of the index in MySQL