Today read a blog article about mysql index, feeling write very well, but the layout to tell the truth to see I have a headache, so will its transfer to come over and recomposition, convenient if forget to brush up later Blog: www.cnblogs.com/whgk/p/6179…

What is an index? Why build an index?

Index is used to quickly find a specific value in a column of the line, do not use index, MySQL must start from the first record after reading the entire table, until find out the related line, the larger the table, the time it takes to query data, if the query column in the table there is an index, MySQL can quickly reach a location to search data file, You can save a lot of time by not having to look at all the data.

For example, let’s have a Person table with 2W records for 2W individuals. There is a field of Phone to record the Phone number of each person. Now you want to query the information of the person whose Phone number is XXXX.

If there is no index, the first record in the table is iterated down until that information is found.

If the index is configured, the Phone field is stored in a certain way, so that the corresponding data can be quickly found when the information in the field is queried without traversing 2W data items. There are two storage types of indexes in MySQL: BTREE and HASH.

That is, use a tree or Hash value to store the field. To know how to find the field in detail, you need to know the algorithm. We just need to know what the index does and what its function is.

2, MySQL index advantages and disadvantages and use principles

Advantages:

  1. All MySql column types (field types) can be indexed, meaning that any field can be indexed
  2. Greatly speed up the data query speed

Disadvantages:

  1. Creating and maintaining indexes takes time and increases as the volume of data increases
  2. Indexes also take up space, and we know that the data in the table will also have a maximum limit set. If we have a large number of indexes, the index file will probably reach the maximum value faster than the data file
  3. When the data in a table is added, deleted, or modified, the index needs to be dynamically maintained, which reduces the data maintenance speed.

Principle of use:

Through the advantages and disadvantages mentioned above, we should know that it is not good to set the index for each field degree, nor is it better to have more indexes, but to use them reasonably.

  1. Avoid over-indexing tables that are frequently updated, and create indexes for fields that are frequently used for queries.
  2. It is best not to use indexes for small tables, because because there is less data, it may take less time to query all the data than to traverse the index, and indexes may not be optimized.
  3. Do not index columns with fewer values in common, such as the “gender” field in the student table with only two different values for male and female. In contrast, a field with many different values can be indexed. What is said above is only very one-sided some things, index certainly has many other advantages or disadvantages, as well as the principle of use, first basically understand index, and then wait for the real use of later, will slowly know other functions. Note that it’s important to know what an index is, what it does, what it does, why it’s used, etc. If you don’t know, read the text again and again to understand it. It is possible to create multiple indexes in a table, and these indexes are stored in an index file (a special place to store indexes).

Classification of indexes

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

MyISAM and InnoDB storage engine: only BTREE index is supported, that is, BTREE is used by default and cannot be replaced

MEMORY/HEAP storage engine: Supports HASH and BTREE indexes

Indexes are divided into four categories

Single-column index (normal index, unique index, primary key index), composite index, full-text index, spatial index

Single-column index:

An index contains only a single column, but a table can have multiple single-column indexes. Don’t get confused here.

  1. Plain index: a basic index type in MySQL that has no restrictions. It allows duplicate and null values to be inserted in the column where the index is defined, purely to make querying data faster.
  2. Unique index: The value in the index column must be unique, but null values are allowed,
  3. Primary key index: a special unique index that does not allow null values.

Composite index

An index created on a combination of fields in a table is used only if the left field of those fields is used in a query condition, following the leftmost prefix set. If you don’t understand this, we’ll talk about it later when we give examples

The full text indexing

CHAR,VARCHAR,TEXT, CHAR,VARCHAR, CHAR,VARCHAR, CHAR,VARCHAR, VARCHAR, CHAR,VARCHAR

Through the good guys, you might be able to find that record. If you are interested in using it further, there will be a blog post for you to refer to when testing the index.

Spatial index

Spatial indexes are built on fields of spatial data types. There are four spatial data types in MySQL, namely, GEOMETRY, POINT, LINESTRING, and POLYGON. Use the SPATIAL keyword when creating SPATIAL indexes. If the engine is MyISAM, the column that creates the spatial index must be declared NOT NULL. See below for details

4. Index operation (create and delete)

Create indexes

  1. Create indexes when creating tables

Format: CREATE TABLE TABLE name [field name data type] [UNIQUE | FULLTEXT | SPATIAL |… INDEX [INDEX | KEY] [name] (field name/length) [ASC | DESC]

|————————————–| |———————————–| |————| |———| | — — — — — — — — — — — — — — – | | — — — — — — — — — — — – |

What index (unique, full text, etc.) index keyword Index name Set to which field index Set to sort the index

  • Creating a normal index
CREATE TABLE book
(
bookid INT NOT NULL,
bookname VARCHAR(255) NOT NULL.authors VARCHAR(255) NOT NULL,
info VARCHAR(255) NULL.comment VARCHAR(255) NULL,
year_publication YEAR NOT NULL.INDEX(year_publication)
)

CREATE TABLE book
(
bookid INT NOT NULL,
bookname VARCHAR(255) NOT NULL.authors VARCHAR(255) NOT NULL,
info VARCHAR(255) NULL.comment VARCHAR(255) NULL,
year_publication YEAR NOT NULL.KEY(year_publication)
)
Copy the code

You can create it either way, but with this example you can compare the format and get a pretty good idea of what the format means.

By printing the result, we will automatically use the field name as the index name if we did not write the index name when creating the index. Test: to see if an index is used for the query.

SELECT * FROM book WHERE year_publication = 1990\G;
Copy the code

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

Id: SELECT identifier. This is the query sequence number of the SELECT, which is the number of occurrences in a statement. Select_type: specifies the type of select query to use. SIMPLE indicates a SIMPLE select without UNION or subquery. That is, indexes are used in this SELECT query. Other values: PRIMARY: the outermost SELECT. When there are subqueries, there are more than two SELECT. UNION: The second or subsequent SELECT statement in the UNION (join two tables) SUBQUERY: In a SUBQUERY, the second select. Table: indicates the name of the data table. They are listed in the order in which they are read. In this case, only book is displayed because only one table is queriedtype: Specifies the association between this table and other tables. All records in this table that match the retrieved value will be federated with records fetched from the previous table. Ref is used when the linker uses the leftmost prefix of the key or when the key is not a primary key or unique index (in other words, the linker cannot obtain only one record based on the key value). This is a good type of join when only a few matching records are found based on the key value. (Note, personal here is not very understand, Baidu a lot of information, all plain English, and so on later used this kind of information, in the back to supplement, here do not understand the impact on the back is not big.) Possible values include system, const, eq_ref, index, and All possible_keys. The table contains only one possible index, y ear_publication key: Key_len: specifies the length of the index used by mysql. If the key field is null, the length of the index is null. Note that the value of key_len can tell you which indexes mysql actually uses in the federated index. Here one index is used, so 1, ref: gives the name of the column in the other table in the association. A const, in this case 1990, is a constant. Rows: The number of rows that MySQL expects to read from the table when executing the query. Extra: Provides information about the associated operation, without which nothing is written. We can see as much as we can about the above list of possible_keys and key. It shows that key is YEAR_publication. Note An index is used.Copy the code
  • Create a unique index
CREATE TABLE t1
(
id INT NOT NULL,
name CHAR(30) NOT NULL,
UNIQUE INDEX UniqIdx(id)
)
Copy the code

Explanation: An index is used for the ID field and the index name is UniqIdx.

SHOW CREATE TABLE t1\G;
Copy the code

To view the index used in the query, you must first insert data into the table, and then query the data. Otherwise, you will not use the index to search for an id value that does not exist.

INSERT INTO t1 VALUES(1,'xxx');
EXPLAIN SELECT * FROM t1 WHERE id = 1\G;
Copy the code

As you can see, when querying by ID, unique indexes are used. I also experimented with querying for an ID value that is not there, so the index is not used. I think the reason is that all ids should be stored in a const tables, so there is no need to look it up.

  • Create 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;
Copy the code

Now, with this primary key index, we should be able to reflect that the primary key constraint that we declared before is actually a primary key index, but we didn’t know that before.

  • Create a single-column index

In fact, I don’t need to say this, the first few are single-column indexes.

* Create composite indexes

A composite index creates an index on multiple fields

Create a table T3 and create composite indexes 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;
Copy the code

Explain the leftmost prefix

Index (id, name, and age) {id, name, and age) {id, name, and age; An index can index the following field combinations (ID, name, age), (ID, name), or (ID). If the field to be queried does not prefix the left-most part of the index, then the index is not used. For example, age or (name, age) combinations are not used

In table T3, query the ID and name fields

EXPLAIN SELECT * FROM t3 WHERE id = 1 AND name = 'joe'\G;
Copy the code

In table T3, query (age, name) fields so that index queries are not used. Let’s see what happens

EXPLAIN SELECT * FROM t3 WHERE age = 3 AND name = 'bob'\G;
Copy the code
  • Creating a full-text index

Full-text indexes can be used for full-text searches, but only the MyISAM storage engine supports FULLTEXT indexes and only serves CHAR, VARCHAR, and TEXT columns. Indexes are always performed on the entire column, prefix indexes are 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;
Copy the code

Use what is called full text search. That is, in many words, the record can be found by keyword.

INSERT INTO t4 VALUES
(8,'AAA', 3,'Text is so good, hei, my name is Bob')
,(9,'BBB', 4,'my name is gorlr');

SELECT * FROM t4 WHERE MATCH(info) AGAINST('gorlr');
Copy the code

EXPLAIN SELECT * FROM t4 WHERE MATCH(info) AGAINST('gorlr');
Copy the code

Use of full text search

  • Creating 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 be related to game development, it may be related to other things, and so on encountered nature will know, now only requires to be able to create it.

CREATE TABLE t5
(
g GEOMETRY NOT NULL,
SPATIAL INDEX spatIdx(g)
) ENGINE = MyISAM;

SHOW CREATE TABLE t5\G;
Copy the code

  1. Create indexes on existing tables

Format: the ALTER TABLE tablename ADD [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [INDEX name] (INDEX field name) [ASC | DESC] with the basis of the above, there wouldn’t have too many statements.

A command:

SHOW INDEX FROM table name \GCopy the code

View the indexes created in a table

SHOW INDEX FROM book\G;
Copy the code

Pick out the key points, we need to understand the five, marked in red, if you want to know more about it, you can look up the information, I personally think, these will be met in the actual work in the future to do a detailed understanding.

Non_unique: indicates that the index is not unique. 1 indicates that the index is not unique. 0 indicates that the index is not unique. Seq_in_index: indicates the position of the column in the index. The value is 1 for a single column index, and the value is 1 for a combined index. Column_name: indicates the column column in which the index is defined. Null: indicates whether the field can be Null. Index_type: indicates the index typeCopy the code
  • Add indexes to tables

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

ALTER TABLE book ADD INDEX BkNameIdx(bookname(30));
Copy the code

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

This is just an example of a normal index, adding any other index is the same. It’s just a copy of the book. I’m not going to explain it here.

  • Use CREATE INDEX to CREATE an INDEX

Format: CREATE UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] INDEX name ON the name of the table (CREATE indexes of field name/length) [ASC | DESC]

Explanation: in fact, it is the same as before, the format has changed, do the same thing as above, do an example.

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

CREATE INDEX BkBookNameIdx ON book(bookname);
Copy the code

SHOW INDEX FROM book\G;     // Check the index in the book tableCopy the code

Explanation: : The first screenshot was not captured, because the image is too large, here as long as we see the index added to prove success. The other indexes are created the same way.

Remove the index

We talked about adding indexes and querying methods to a table.

Two ways to add

1 How to create an index while creating a table

Add index to table (s);

Query mode

SHOW INDEX FROM table_name \G;Copy the code

\G just makes the output format look better

Now let’s talk about two operations to remove an index from a table.

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

Mysql > delete index from book table; delete index from book table

Delete index BkBookNameIdx from book;

ALTER TABLE book DROP INDEX BkBookNameIdx;
Copy the code

SHOW INDEX FROM book\G;     Index BkBookNameIdx (BkBookNameIdx); // Index BkBookNameIdx (BkBookNameIdx)Copy the code

  1. Format 2: DROP INDEX INDEX name ON Indicates the name of the table

Delete index BkNameIdx from book table

DROP INDEX BkNameIdx ON book;
SHOW INDEX FROM book\G;
Copy the code