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

Preface

After writing about the addition, deletion, modification and query of MySQL to the table (query is the most important), I feel that MySQL is almost finished, and I don't want to continue to learn. The reason may be due to the influence of others. I think that for MySQL, knowing some complex queries is enough, but I think, whether it's useful or not, what I don't understand now is my weakness. Learn more to be stronger than others.

-- WH

What is an index? Why build an index?

The index is used to quickly find the row with a specific value in a column. Without using the index, MySQL must read the entire table from the first record until the relevant row is found. The larger the table, the more time it takes to query the data. If the query column in the table has an index, MySQL can quickly reach a location to search the data file without having to view all the data. Then it will save a lot of time.

For example, there is a person table with 2W records and 2W personal information. There is a Phone field that records everyone's phone number, and now you want to find out the information of the person whose phone number is xxxx.

If there is no index, it traverses down from the first record in the table until the information is found.

If there is an index, then the Phone field will be stored in a certain way, so that when querying the information on the field, the corresponding data can be found quickly without having to traverse 2W pieces of data. There are two storage types of indexes in MySQL: BTREE and HASH. That is, the field is stored with a tree or hash value, and knowledge of the algorithm is needed to know how to find it in detail. Now we just need to know the function of the index and what the function is.

II. Advantages and disadvantages of indexes in MySQL and principles of using them.

Advantages:

2. All MySql column types (field types) can be indexed, that is, any field can be indexed.

3. Greatly speed up the query speed of data.

Disadvantages:

1. It takes time to create and maintain the index, and the time spent increases as the amount of data increases.

2. The index also needs to take up space. We know that the data in the data table will also have the maximum online setting. If we have a large number of indexes, the index file may reach the online value faster than the data file.

3. When the data in the table is added, deleted and modified, the index also needs dynamic maintenance, which reduces the maintenance speed of the data.

Principles of use:

From the advantages and disadvantages mentioned above, we should know that it is not good to set an index for each field, nor is it that the more indexes the better, but the need for their own reasonable use.

1. Avoid too many indexes on frequently updated tables, and create indexes on fields that are often used for queries.

2. It is best not to use an index for a table with a small amount of data, because because of the lack of data, it may take less time to query all the data than to traverse the index, and the index may not have an optimization effect.

3. Do not build an index on the same column (field) with less value. For example, in the "gender" field of the student table, there are only two different values: male and female. On the contrary, more different values on a field can be indexed.

The above is only a very one-sided things, the index must have many other advantages or disadvantages, as well as the principle of use, first basically understand the index, and then when you really use it later, you will gradually know other functions. Note that to learn this, it is very important to know what the index is, what the index is for, what it does, why it is indexed, and so on. If you do not know, repeat the words written above and have a good understanding. There is enough to create multiple indexes in a table, and these indexes are stored in an index file (a special place to store indexes).

III. Classification of indexes

Note: indexes are implemented in storage engines, that is, different storage engines use different indexes

MyISAM and InnoDB storage engines: only BTREE indexes are supported, that is, BTREE is used by default and cannot be replaced.

MEMORY/HEAP storage engine: supports HASH and BTREE indexes

1. Indexes are divided into four categories: single-column index (general index, unique index, primary key index), combined index, full-text index, spatial index,

1.1. Single-column indexes: an index contains only a single column, but there can be multiple single-column indexes in a table. Don't get confused here.

1.1.1. General 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.

1.1.2. Unique index:

The value in the index column must be unique, but null values are allowed

1.1.3, primary key index:

Is a special unique index, and null values are not allowed.

1.2. Composite index

An index created on a combination of multiple fields in a table is used only if the left field of these fields is used in the query criteria, following the leftmost prefix set when using the combined index. If you don't understand this, I'll explain it in detail when I give an example later.

1.3. Full-text indexing

Full-text indexing, which can only be used on the MyISAM engine, can only be used on CHAR,VARCHAR,TEXT type fields. This paper introduces the requirements, and talks about what a full-text index is, that is, in a pile of text, through one of the keywords, you can find the record row to which the field belongs, such as "you are a big pen, idiot." Through the big evil pen, you may be able to find the record. What is said here is possible, because the use of the full-text index involves a lot of details, we only need to know the general meaning, if you are interested in using it further, then see below to test the index, we will give a blog post for your reference.

1.4. Spatial index

Spatial index is an index established on the fields of spatial data types. There are four spatial data types in MySQL: GEOMETRY, POINT, LINESTRING and POLYGON.

Use the SPATIAL keyword when creating a spatial index.

It is required that the engine is MyISAM and the column that creates a spatial index must be declared as NOT NULL. For details, see below.

Index operations (create and delete)

4.1. Create an index

4.1.1. Create an index 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]

| |-- | -| |-|

General creation table statement sets what kind of index (unique, full-text, etc.) index keyword index name which field to set the index to sort the index

4.1.1.1. Create a general index

CREATE TABLE book CREATE TABLE book

(

Bookid INT NOT NULL, bookid INT NOT NULL

Bookname VARCHAR (255) NOT NULL, bookname VARCHAR (255) NOT NULL

Authors VARCHAR (255) NOT NULL, authors VARCHAR (255) NOT NULL

Info VARCHAR (255) NULL, info VARCHAR (255) NULL

Comment VARCHAR (255) NULL, comment VARCHAR (255) NULL,

Year_publication YEAR NOT NULL, year_publication YEAR NOT NULL

INDEX (year_publication) KEY (year_publication)

);)

The above two ways to create a degree can, through this example, you can compare the format, you can almost understand what the format means.

By printing the results, if we do not write the index name when creating the index, it will automatically help us use the field name as the index name.

Test: see if an index is used for query.

EXPLAIN SELECT * FROM book WHERE year_publication = 1990\ G

Explanation: although there is no data in the table, there is an EXPLAIN keyword to see if the index is in use and to output information about the index it uses.

Id: SELECT identifier. This is the query sequence number of SELECT, that is, the number of times the select appears in a statement. In the secondary statement, there is only one select, so it is 1.

Select_type: the type of SELECT query used, represented by SIMPLE as a simple SELECT, without practical UNION or subquery, is a simple SELECT. That is, the index is used in the SELECT query. Other values, PRIMARY: outermost SELECT. When you have a subquery, there are more than two SELECT. The second or subsequent select statement SUBQUERY in UNION:union (two table joins): in the subquery, the second SELECT.

Table: the name of the datasheet. They are sorted in the order in which they were read. Here, since only one table is queried, only book is displayed.

Type: specifies the association between this data table and other data tables, in which all records that match the retrieval values are taken out and joined with records taken from the previous table. Ref is used by the linker to use the leftmost prefix of the key or when the key is not a primary key or unique index (in other words, the linker 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 I don't quite understand here. Baidu has a lot of materials, all in vernacular. When this kind of information is used in the future, I will go back and add that not knowing here has little impact on the later.) Possible values are system, const, eq_ref, index, and All

Each index that possible_keys:MySQL can choose when searching 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). When the value of the key field is null, the length of the index is null. Note that the value of key_len tells you which indexes mysql will actually use in the federated index. One index is used here, so it is 1

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

The number of rows that rows:MySQL expects to read from this data table when executing this query.

Extra: provides information about the associated operation, without which nothing is written.

There are a lot of things above that we can read as much as we can. The most important thing is to look at the two attributes possible_keys and key, which show that key is year_publication. Indicates that an index is used.

4.1.1.2, create a unique index

CREATE TABLE t1

(

Id INT NOT NULL

Name CHAR (30) NOT NULL

UNIQUE INDEX UniqIdx (id)

);

Explanation: the index is used on the id field, and the index name is UniqIdx.

SHOW CREATE TABLE T1\ G

To see the index used in the query, you must first insert data into the table, and then query the data, otherwise looking for an id value that does not exist, the index will not be used.

INSERT INTO T1 VALUES (1meme xxx')

EXPLAIN SELECT * FROM T1 WHERE id = 1\ G

As you can see, a unique index is used when querying through id. And also experimented with querying an id value that does not exist, then the index will not be used. I think the reason is that all id should be stored in a const tables, where there is no id value, so there is no need to find it.

4.1.1.3, create a primary key index

CREATE TABLE t2

(

Id INT NOT NULL

Name CHAR (10)

PRIMARY KEY (id)

);

INSERT INTO T2 VALUES (1 recording QQQ')

EXPLAIN SELECT * FROM T2 WHERE id = 1\ G

Through this primary key index, we should reflect that, in fact, the primary key constraint we declared before is a primary key index, but we haven't learned it before and don't know it.

4.1.1.4. Create a single-column index

In fact, needless to say, the first few are single-column indexes.

4.1.1.5. Create a composite index

A combined index is to create an index on multiple fields.

Create a table T3 and create a combined index on the id, name, and age fields in the table

CREATE TABLE t3

(

Id INT NOT NULL

Name CHAR (30) NOT NULL

Age INT NOT NULL

Info VARCHAR (255)

INDEX MultiIdx (id,name,age)

)

SHOW CREATE T3\ G

Explain the leftmost prefix

Combined index is to comply with the leftmost prefix, using the leftmost column set in the index to match rows, such a column set is called the leftmost prefix, it doesn't matter if you don't understand, just to give a few examples, for example, here the index consists of id, name and age3 fields, the index row is stored in the order of id/name/age, and the index can be indexed in 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, query the id and name fields

EXPLAIN SELECT * FROM T3 WHERE id = 1 AND name = 'joe'\ G

In the T3 table, query the (age,name) field so that the index query is not used. Let's see the results.

EXPLAIN SELECT * FROM T3 WHERE age = 3 AND name = 'bob'\ G

4.1.1.6. Create a full-text index

Full-text indexing can be used for full-text search, but only the MyISAM storage engine supports FULLTEXT indexing and only serves CHAR, VARCHAR, and TEXT columns. Indexing is always performed on the entire column, prefix indexing is not supported

CREATE TABLE t4

(

Id INT NOT NULL

Name CHAR (30) NOT NULL

Age INT 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 through keywords.

INSERT INTO T4 VALUES (8 recordings AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAABBBBBBBBBBBBBB

SELECT * FROM T4 WHERE MATCH (info) AGAINST ('gorlr')

EXPLAIN SELECT * FROM T4 WHERE MATCH (info) AGAINST ('gorlr')

Note: when using full-text search, you need to use the MATCH function, and its full-text search is more limited, for example, you can only use MyISAM engine, for example, you can only set full-text index on CHAR,VARCHAR,TEXT. For example, the search keyword should be at least 4 characters by default. For example, if the search keyword is too short, it will be ignored. Wait, if you were in the experiment, you might not be able to do it. Interested students can read this article, the use of full-text search.

4.1.1.7. Create a spatial index

The spatial index must also use the MyISAM engine, and the fields of the spatial type must be non-empty. I don't know what this spatial index can do, it may have something to do with game development, it may have something to do with something else, and so on, we will know it naturally, and now we only need to be able to create it.

CREATE TABLE t5

(

G GEOMETRY NOT NULL

SPATIAL INDEX spatIdx (g)

) ENGINE = MyISAM

SHOW CREATE TABLE T5\ G

4.1.2. Create an index on an existing table

Format: ALTER TABLE table name ADD [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index name] (index field name) [ASC | DESC]

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

Command 1: SHOW INDEX FROM table name\ G

View the indexes created in a table

SHOW INDEX FROM book\ G

To pick the main point, there are only 5 things we need to know, marked with red color. If you want to know more about it, you can check the information in this area. I personally think that we will encounter a detailed understanding in the actual work in the future.

Table: the table that creates the index

Non_unique: indicates that the index is not unique, 1 represents a non-unique index, and 0 represents a unique index, which means whether the index is unique or not

Key_name: index name

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

Column_name: represents the column field that defines the index

Sub_part: indicates the length of the index

Null: indicates whether the field can be null

Index_type: indicates the type of index

4.1.2.1. Add an index to the table

Take the book table above, for example. We already have a year_publication, but now we are adding a normal index to the table

ALTER TABLE book ADD INDEX BkNameIdx (bookname (30))

If you look at the output, you can see that the index was added successfully.

This is just an example of a general index, and it's the same with adding other indexes. It's just a gourd. I won't explain them one by one here.

4.1.2.2. Create an index using CREATE INDEX.

Format: CREATE [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] Index name ON table name (name of the field that created the index [length]) [ASC | DESC]

Explanation: in fact, it is just a change of clothes, the format has been changed, and the thing is exactly the same as above, do an example.

Add a normal index to the book table with the field authors.

CREATE INDEX BkBookNameIdx ON book (bookname)

SHOW INDEX FROM book\ G; / / View the indexes in the book table

Explanation: the first screenshot was not taken, because the picture is too large, as long as we see our newly added index to prove the success. Other indexes are created the same way.

4.2. Delete the index

I talked about the method of adding and querying the index in a table.

Two ways to add

1 how to create an index while creating a table

2 two ways to add an index to a table after creating the table

The mode of inquiry

The SHOW INDEX FROM table name\ G;\ G just makes the output format better.

Now let's talk about two operations that can delete an index from a table.

Format 1: ALTER TABLE table name DROP INDEX index name.

A very simple statement, now let's take a look at an example, or operate on the book table to delete the index we just added to it.

1. Delete the index named BkBookNameIdx in the book table.

ALTER TABLE book DROP INDEX BkBookNameIdx

SHOW INDEX FROM book\ G; / / if you look at the index in the book table, you will find that the index BkBookNameIdx is no longer there.

Format 2: DROP INDEX index name ON table name

Delete the index named BkNameIdx in the book table

DROP INDEX BkNameIdx ON book

SHOW INDEX FROM book\ G

V. Summary

This is pretty much the end of the MySQL index, so let's sum up what we should know so far.

1. What is the index for? Why is there an index?

This is very important. You need to understand it for yourself. If you don't understand, just look at the explanation at the top.

2. Classification of index

3. Operation of index

Create indexes in tables, add indexes, delete indexes, delete indexes

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.