MySQL index and index creation principles

is what

Indexes are used to quickly query certain rows of certain special columns. If there is no index, MySQL must start with the first line and then query the entire table for the row. The larger the table, the greater the cost of the query. If the table has an index, then MySQL can quickly determine the location of the data without querying the entire table. This is much faster than the sequential reading of each line. The index is just like the directory where we look in the dictionary, and we can navigate to a row of data by querying the directory of the dictionary.

Most MySQL indexes (primary key index, unique index, normal index, full-text index) are b-trees structures. Exceptions are: Use the r-trees structure in spatial data types. The storage engine is a memory database and can also support hash indexes. The InnoDB storage engine uses a reverse list structure for full-text indexing.

Usage Scenarios

MySQL will use the following scenario for the index:

1. Quickly match a condition to the corresponding line.

2. Reduce the number of rows affected by the query. If a query field has multiple indexes, MySQL usually chooses to use the index with the lowest number of rows (the most selective index). Selective evaluation of Indexes Select count(distinct name) / count(*) from table;

3. For a composite index, the column to the left of the index can query the data using the index prefix optimizer. For example, if you have a three-column combination index (COL1,COL2,COL3), then you can use the index query (col1), (Col1,col2), (COL1,COL2,COL3) data for these three combinations. For more information on the combined index, see another blog MySQL Combo index

4. When querying a table with other tables, if the data type and size of the column being judged are the same, then using the index on these two columns can make the judgment more efficient. For example, adding an index to Tb1.name and tb2.name in the following query can improve query efficiency. SELECT * from tb1, tb2 WHERE tb1. Name = TB2. Name

Here, VARCHAR and CHAR are considered to be of the same type. It is important to note that if you want the index to take effect, you need not only the same type, but also the same size. For example, VARCHAR (Ten) and CHAR (Ten) same size can use index, but VARCHAR (Ten) with CHAR (15)就无法使用索引 .

5. Look for MIN () or MAX () values for index columns.

6. Sort or group by an indexed column, or the left prefix of a combined index.

7. Query the contents of the indexed column. (If you only need to return the value of an indexed column, you do not need to query the data row to read the retrieved value directly from memory.) This condition is known as the overwrite index) for example: SELECT key_part from table WHERE key_part= 1

　　indexes are less important for small tables or for queries that report queries that handle large tables of most or all rows. When a query requires access to most rows, sequential reads are faster than through the index. sequential reads can minimize disk searches, even if the query does not require all rows. Indexes are important only when the data is large and you need access to some of the data.

How to use View Index

SHOW INDEX from table

After running, the following results are displayed:

　　

Where the meanings of each field are:

Table: Names of tables

Non_unique: Whether the index can be duplicated. It cannot be repeated, or 0, or 1 if it can be repeated.

Key_name: Index name. When created, you can choose input without entering MySQL auto-generated. If the index is a primary key, the name is always PRIMARY.

Seq_in_index: The column sequence number in the index, starting at 1.

COLUMN_NAME: The name of the column involved in the index.

Collation: How the columns are sorted in the index. This can have a value of a (ascending ascending), D (Descending descending), or null (unsorted).

Cardinality: The number of unique values in the index (not accurate data that is updated in real time).

Sub_part: Index prefix length. If you use part of a field's character as an index, the number of index characters is displayed. Null if the entire field is used for indexing.

Packed:key the packaging method, NULL means not packaged.

Null: The index column contains null or ' when it is yes.

Index_type: Index type. (BTREE, Fulltext,hash, RTREE) one.

Comment: index information that is not described in the current column, such as whether the disabled index is disabled.

Index_comment: The comment that is provided when the index is created.

Visible: Whether the index is visible to the optimizer (this information appears in some versions).

Add index

CREATE INDEX index_name on table_name (Key_part,...)

ALTER TABLE t1 ADD INDEX index_name (key_part)

Typically, indexes are created when tables are created. Table for the InnoDB storage engine. Where the primary key determines the physical layout of the data, you can add an index to an existing table. Key_part represents the column name of the column that makes up the index, and if it is multiple column names, a composite index is generated. You can add ASC or DESC after the Key_part parameter to specify whether the index is arranged in a positive or reverse order.

The important considerations for creating indexes are:

Combined Index

A composite index is an index that consists of multiple columns. Examples: For example, there are three fields in the table address, each of which is a combined index using three fields when building a table in County county, city, provincial province. The code is as follows:

 1  create  table   address ( 2  provincial varchar  (10  ),  3  city varchar  (10  ),  4  county varchar  (10  ),  5  index   (provincial, city, county)  6 ) 

CREATE TABLE Address

Here the index is created: first sorted by province, then, according to the content of the same province, according to the city to sort, and finally, according to the county to sort. That is, the first column is indexed first, and if the contents of the first column are identical, then the second column is sorted, and so on.

Prefix index

If you are indexing a column of strings, you can create a prefix index. In general, the selectivity of a prefix is high enough to satisfy query performance. For Blob,text, or long, varchar-type columns, you must use a prefix index. The prefix index is in bytes. The length of the prefix index support depends on the storage engine. For example, for a table with InnoDB using the redundant or COMPACT row format, the prefix can be up to 767 bytes long. For InnoDB tables that use the dynamic or compressed row format, the prefix length is limited to 3072 bytes. For MyISAM tables, the prefix length is limited to 1000 bytes.

If the specified index prefix exceeds the maximum column data type size, for non-unique indexes, if strict SQL mode is enabled, an error occurs. If strict SQL mode is not enabled, the index length is reduced to the maximum column data type size, and a warning is generated.

The length of the prefix index is created, depending on the selectivity of the index. See another blog: Index selectivity

The syntax for creating the prefix index is as follows (10 here means intercept the first 10 characters):

CREATE INDEX key_part_name on table_name (Key_part (ten));

ALTER TABLE table_name ADD INDEX index_name (Key_part (ten))

The prefix index can accommodate both index size and query speed. You can use a relatively small amount of space to detect data at a faster speed. But it also has drawbacks: The prefix index cannot be used for both ORDER by and GROUP by operations, nor for index overrides.

Method Index

The English name of the index type here is: Functional Key Parts Here the author does not know the official translation name, but only according to the index of the way to translate. If not, welcome to the great God.

This index is similar to the concatenation of two prefix indexes. Just for example: There are two columns in the T1 table, col1 and col2 I want to create a combined index of the first 10 bytes that contain the complete col1 column and the col2 column. The code is as follows:

CREATE TABLE T1 (  varchar  ),varchar,   INDEX (col1, col2 (ten)));

Functional Key Parts

In MySQL version 8.0.13 and later, MySQL supports expressions for indexing. Here, the expression of the operation needs to be written in parentheses to make a declaration of the zoom. For example:

1 --Method Index2 CREATE TABLET1 (3Col1INT,4Col2INT,5     INDEXFunc_index ((ABS(col1)))6 );7 8 CREATE INDEXIdx1 onT1 ((col1+col2));9 Ten CREATE INDEXIdx2 onT1 ( One(col1+col2), A(col1-col2), - col1 - ); the  - ALTER TABLET1ADD INDEX((col1*  +)DESC);

Functional Key Parts Unique index

The index created through UNIQUE. The contents of an indexed column must be unique when it is not a null value, and a null value may not be unique. If you add duplicate values, an error occurs. If you specify a prefix value when creating a unique index, the prefix must be unique. Creation syntax: Create UNIQUE INDEX unique_index_name on table_name (key_part)

Full-Text indexing

Full-text indexing, as the name implies, supports indexes for full-text indexing. Only Innodb and MyISAM two storage engines are supported. And can only include CHAR, VARCHAR, and TEXT columns, the index always occurs on the entire column, and the prefix index is not supported. (even if it doesn't work) you can retrieve the field in full-text. For datasets with large data volumes, loading data into a table without data before adding indexes is more efficient than adding data directly to an indexed table.

Spatial Index

Spatial indexes provide a suitable data structure for spatial search to improve search speed. For spatial indexes, the author does not study much, here is about the usefulness of the spatial index. After a deep study, then fill in this part of the content. First, what is a spatial index for? Example: When we need to follow a certain point, we can find out which customers exist within 50 meters of the vicinity. For this requirement, the possible solution is as follows:

1. We can calculate the distance between each person and our current point according to the latitude and longitude of the user, then compare with 50 meters. This can be done when the amount of customer data is small, and the amount of data will be particularly wasteful.

2. First draw a box, the latitude and longitude of the 50-meter range of data drawn out, through the longitude and latitude of the value of the filter, get a square area, and then the calculation. At this time, there will be a lot less computation, but still not the optimal solution.

3. Use a spatial index. The space is divided into different regions according to certain rules, and the corresponding data is taken out according to the area of the design when retrieving. The spatial index structure diagram is as follows (picture from):

　　

Delete Index

Delete Index Nothing to say, the following statement:

1 DROP INDEX  on Talbe_name 2 ALTER TABLE DROP INDEX index_name

DROP INDEX

If the column where the index is located is deleted, the corresponding index for that column is also automatically deleted.

Index Optimization primary Key Optimization

The primary key of a table is a unique and non-empty index, and when using the InnoDB storage engine, table data is mounted directly on the leaf node of the primary key, which is the fastest index on the query.

If the table has a lot of content and is important. But there is no obvious set of columns columns to co-operate as the primary key, you can create an auto-growing value as the main key separately. When using an outer chain query, this ID can be used as a pointer to the content.

foreign key Optimization

If your table has many columns, you can split the columns with lower query frequency into other tables and associate them with the primary table by copying the IDs. In this way, each small table will have a primary key to quickly find other data. When querying, you can query only the set of columns you need. At this point, the query performs less I/O and consumes less memory. The overall principle is: to improve performance, read as little data from disk as possible. This is the principle of splitting the table.