MySQL Indexing learning Summary

1. What is an index?

Index is the data structure that helps MySQL to get data efficiently. You can get the essence of an index: An index is a data structure.

Can be understood as "sequential quick find data structure"

In addition to data, the database system maintains a data structure that satisfies a particular lookup algorithm, which references (points to) data in some way,

This enables advanced lookup algorithms to be implemented on these data structures, which are indexes.

2. Advantages

Similar to the University library to build bibliographic index, improve the efficiency of data retrieval, reduce the database IO cost.

Sorting data by index reduces the cost of sorting data and reduces CPU consumption.

3. Disadvantages

In fact, the index is also a table, which holds the primary key and index fields, and points to the records of the entity table, so the indexed columns also occupy space.

Although the index greatly improves query speed, it does reduce the speed of updating tables, such as INSERT, UPDATE, DELETE for tables.

Because when updating a table, MySQL not only saves the data, but also saves the index file each time the field that added the indexed column is updated.

Will adjust the index information after the change of key value due to the update.

4, the classification of the index

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 at the end of the example, here is an index consisting of an ID, a name, and a Age3 field, and the index row is stored in id/name/age order, and 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 it is not indexed, for example, the age or (name,age) combination does not use an index query.

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.

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.

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;

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

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

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

5. Basic grammar

ALTER TABLE Tbl_name Add primary key (column_list); To add a primary key index, you must be unique and cannot be null.

ALTER TABLE Tbl_name add unique index_name (column_list); Add a unique index, cannot be null.

ALTER TABLE Tbl_name add index_name (column_list); Add a normal index, and the index value can appear multiple times.

ALTER TABLE Tbl_name add fulltext index_name (column_list); Add full-text index fulltext.

6. mysql Index structure

Btree Index

Hash index

Full-text Full-Text index

R-tree Index

7. Which conditions need to be indexed

1 primary keys automatically create unique indexes

2 fields that are frequently used as query criteria should create an index

3 fields associated with other tables in the query, foreign key relationships indexed

4 frequently updated fields are not suitable for indexing because each update does not just update the record but also updates the index

Do not create an index in a field that is not available in 5WHERE conditions

6 single-touch/combination index selection question, who? (Inclined to create a composite index under high concurrency)

7 sorted fields in the query, sorted fields will greatly improve the sorting speed if accessed by the index

8 statistics or grouping fields in a query

9 for frequently updated tables avoid too many indexes on them, and the fields that are frequently used for queries should be indexed.

10 a table with a small amount of data is best not to use the index, because because of the low 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.

11 Do not index on columns with less value (field), for example, in the "Gender" field of the student table, there are only two different values for male and female. Conversely, there are many different values on a field, but indexes are established.

8. Which cases do not create an index

① table records too little

② frequently deleted and changed tables

Improves query speed while reducing the speed of updating tables, such as INSERT, UPDATE, and delete on tables.

Because when updating a table, MySQL not only saves the data, but also saves the index file.

The data repeats and distributes the average table field, so you should index only the most frequently queried and most frequently sorted data.

③ Note that if a data column contains a lot of duplicate content, indexing it does not have much practical effect.

MySQL Indexing learning Summary