MySQL Index Introduction

Definition:

An index is used to quickly find a row in a column that has a specific value.

Without an index, MySQL must start reading the entire table from the first record until it finds the relevant row. The larger the table, the more time it takes to query the data.

If there is an index to the column in the table, MySQL can quickly reach a location to search for data files without having to look at all the data.

Brief introduction:

An index is a structure that sorts the values of one or more columns in a database table and uses an index to improve the query speed of specific data in the database.

An index is a separate database structure stored on disk that contains reference pointers to all the records in the data table.

Using an index to quickly find a row with a specific value in one or more columns, all MySQL column types can be indexed, and using indexes on related columns is the best way to improve the speed of query operations.

For example: There are 20,000 records in the database, and now you want to execute a query like this:

SELECT * FROM table where num=10000

If there is no index, you must traverse the entire table until this line of Num equals 10000 is found.

If you create an index on the NUM column, MySQL does not need any scanning, just find 10000 in the index, and you will know the location of the line.

Indexes are implemented by the storage engine, so the indexes for each storage engine are not necessarily identical, and each storage engine does not necessarily support all index types.

Defines the maximum number of indexes and the maximum index length per table based on the storage engine. All storage engines support at least 16 indexes per table with a total index length of at least 256 bytes. Most storage engines have a higher limit.

There are two types of storage for indexes in MySQL: Btree and hash, specific to the storage engine of the table, MyISAM and InnoDB storage engines support Btree indexes; Memory/heap storage engine can support btree and hash indexes.

Index Benefits:

1. By creating a unique index, you can guarantee the uniqueness of each row of data in a database table.

2, can greatly speed up the query speed of data, which is the most important reason to create indexes.

3, in the realization of the reference integrity of the data, you can accelerate the link between the table and the table.

4. When using grouping and sorting clauses for data queries, you can also significantly reduce the time to group and sort in queries.

Disadvantages of the index:

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 needs to occupy disk space, in addition to the data table to occupy the data space, each index also occupies a certain physical space, if there are a large number of indexes, the index file may reach the maximum file size faster than the data file.

3. When the data in the table is added, deleted and modified, the index should also be maintained dynamically, thus reducing the maintenance speed of the data.

MySQL Index classification:

1. Normal index and unique index

Normal index: The most basic index type in MySQL, allowing duplicate and null values to be inserted in the column that defines the index.

Unique index: The value of the indexed column must be unique, but a null value is allowed.

If it is a composite index, the column values and combinations must be unique. The primary key index is a special unique index and does not allow null values.

2. Single-column index and combined index

Single-column index: An index consists of only one row, and a table can have multiple single-column indexes.

Composite Index: An index created on multiple field combinations of a table, which is used only when the left field of these fields is used in the query criteria. The combination index is used to follow the leftmost prefix collection.

3. Full-Text Indexing

The full-text index type is fulltext and supports full-text lookups on columns that define the index, allowing duplicate and null values to be inserted in these indexed columns. Full-text indexes can be created on Char,varchar or text types.

Only the MyISAM storage engine supports full-text indexing.

4. Spatial index

Spatial indexes are indexes on fields of spatial data types, with 4 of the spatial data in MySQL: GEOMETRY, point, linestring, and polygon. MYSL uses the spatial keyword to expand to create spatial indexes that are similar to the syntax for creating regular indexes.

The column that creates the spatial index must be declared as not NULL, and the spatial index can only be created in the storage engine for MyISAM.

Design principles for Index tables:

1, the Index table is not the more the better, if there is a large number of indexes in a table, not only occupy hard disk space, but also affect the performance of additions and deletions and other statements, because when the data in the table changes, the index will also be adjusted and updated.

2. Avoid too many indexes on frequently updated tables, and as few columns as possible in the index table. The fields that you query frequently should create indexes, but avoid adding unnecessary fields.

3. A table with a small amount of data is best not to use an index. When the traversal time is short and the index is not optimized.

4, in the conditional expression is often used in a number of different values of the column on the index, in the column of a few different values do not build index. such as sex.

5. Specify a unique index when uniqueness is a feature of the data itself. Using a unique index requires the data integrity of the defined columns to improve the query speed of the data.

6. Index on columns that are frequently sorted or grouped (that is, group by or order by operations), and if there are multiple columns to be sorted, you can create a composite index on those columns.

Create syntax:

CREATE table specifies an index column ALTER TABLE creates an index on a table that is present. Add index creation Table table_name [Col_name data_type][unique| Fulltext| SPATIAL] [index| KEY] [index_name] (col_name [length]) [asc| Desc]unique: Unique index Fulltext: full-text index spatial: spatial Index [index| KEY]: The same role, CREATE INDEX col_name: The field column that needs to be indexed, must be defined in the table index_name: Specify the index name, if you do not specify the MySQL default col_name is the index value. Length: Index lengths, only String type to specify [asc| DESC]: Ascending, descending

1. Create a normal index:

Mysql> CREATE DATABASE lxqdb;mysql> use lxqdb;mysql> CREATE TABLE book--book_id int NOT NULL , Book_name varchar (255) NOT NULL,-authors varchar (255) NOT NULL, info varchar (255) NULL,-& Gt Comment varchar (255) NULL, year_publication year NOT NULL, INDEX (year_publication), and

View:

Mysql> Show CREATE TABLE book \gkey ' Year_publication ' (' Year_publication ') # successfully indexed, MySQL automatically add index name: year_publication

To see if the index is being used correctly:

mysql> explain select * from book where year_publication=2018\g*********  1. row ***************************               id: 1           select_type: SIMPLE        #  represents a simple select and can not use subqueries. Other value:primary | union | subquery          The     table: book         #  row specifies the data table name, They are arranged in the order in which they are read              type: ref The           #  row specifies the association between this data table and other data tables. Other values: system | const | eq_ref | ref | range | index |  all         possible_keys: year_publication         #  An optional index               key: year_ publication         #  actual selection of indexes              key_len: 1                    #  Index calculates the length in bytes, the smaller the value, the quicker the                ref: const          #  associating a column name in another table of data               rows: 1           #  The number of rows of data expected to be read from this data table when the query is executed              extra:  using index condition      #  related operation Information     1 row in set  (0.00  SEC)

2. Create a unique index:

In larger data tables, in order to reduce the time of the index column operation. The only difference from a normal index is that the value of the indexed column must be unique, but it is allowed to have a null value. If it is a composite index, the combination of column values must be unique.

Create a tb1 table, and the ID field creates a unique index using unique.

Mysql> CREATE TABLE TB1-ID int not NULL, name char (a) not NULL, UNIQUE INDEX UID X (ID));

View:

Mysql> Show CREATE table Tb1\gunique KEY ' uidx ' (' ID ') on the # ID field created a unique index named UIDX.

3. Create a single-column index:

A single-column index is an index created on a field in a data table, and multiple single-column indexes can be created in one table.

The indexes created in the two examples above are single-column indexes.

Create a TB2 table and create a single-column index on the Name field in the table.

Mysql> CREATE table TB2, ID int not NULL, name char (a) not NULL, INDEX SINGLE_IDX (name (+));

View:

Mysql> Show CREATE table Tb2\gkey ' Single_idx ' (' name ') # Name field creates a single-column index named SINGLE_IDX and length 10.

4. Create a composite index:

Create an index on more than one field.

Create Tb3 table, id,name,age as Index

Mysql> CREATE table tb3, ID int not NULL, name char (a) not null,--age int NOT NULL , info varchar (255), INDEX multi_idx (id,name,age);

View:

Mysql> Show CREATE table Tb3\gkey ' Multi_idx ' (' id ', ' name ', ' age ') # set up a composite index note: A composite index can serve several indexes, but it is not a random field to query. You can use the index instead of the leftmost prefix: Take advantage of the leftmost Lie match in the index, which is placed in the order of Id\name\age in the index, which can search for the following combination of fields: (Id,name,age), (id,name), id. If the column does not constitute the leftmost prefix of the index, MySQL cannot use a local index, such as the age or name,age combination. The index of the query ID and Name field:mysql> explain select * from Tb3 where id=1 and Name= ' Lxq ' \gkey:multi_idx # can be seen using MULTIID The index of X. Query the index of the name and age fields:mysql> explain select * from tb3 where name= ' Lxq ' and Age=23\gkey:null # indicates that no index query is used 。

5. Create a full-text index:

Only the MyISAM engine is supported and only for char,varchar,text columns. Indexes are always made for an entire column, and local indexes are not supported.

Create a table TB4 and build a full-text index on the info field:

Mysql> CREATE table TB4, ID int not NULL, name char (a) not null,--age int NOT NULL ,-Info varchar (255), Fulltext INDEX Fulltext_idx (info), Engine=myisam;

View:

Mysql> Show CREATE table Tb4\gfulltext KEY ' fulltext_idx ' (' info ') # fulltext named Fulltext_idx. Suitable for large data, do not use in small data.

6. Create a spatial index:

Only the MyISAM engine supports and is not a null column.

Create a tb5 table, and create a spatial index on the Geometry field:

Mysql> CREATE table Tb5, GMT geometry not NULL, SPATIAL INDEX Spat_idx (GMT) engin E=myisam;

View:

Mysql> Show CREATE TABLE t5\g;

SPATIAL KEY ' spat_idx ' (' GMT ') # geometry named Spat_idx

---------------------------------------------------------------------------------------

I'm a split line 1

---------------------------------------------------------------------------------------

Create an index in a table that already exists:

1. Build Bookname_idx Normal index on the BookName field in the Book table:

Mysql> ALTER TABLE book add index Bookname_idx (book_name (30));

View:

mysql> show index from book\g*************************** 2. row ********           Table: book        non_unique: 1          #   Index is not unique, 1 represents a non-unique index, and 0 represents only         Key_name: bookname_idx      #  Index name       Seq_in_index: 1            #  position in index, 1 is a single column, combined index is the order of each field in the index definition         Column_name: book_name       #  column fields for indexes          Collation: A         cardinality: 0         sub_part: 30           #  Index Length          Packed: NULL           Null:             #  whether the field is empty        Index_type: BTREE          #  Index Types           comment:      index_comment:           table: book

2. Establish a unique index named Uniqididx on the BookID field:

Mysql> ALTER TABLE book add UNIQUE INDEX uniqid_idx (book_id);

View:

Mysql> Show index from book \g;*************************** 1.           Row *************************** table:book non_unique:0 # Index Unique, 1 for non-unique index, 0 for unique cable Key_name:uniqid_idx seq_in_index:1

3. Create a single-column index on comment:

Mysql> ALTER TABLE book add INDEX coment_idx (Comment (50));

View:

Mysql> Show index from book \gsub_part:50 #只要检索前50个字符

4. Build the combined index on book's authors and Info:

Mysql> ALTER TABLE book add INDEX au_info_idx (authors (), Info (50));

View:

Mysql> Show index from book \g; Key_name:au_info_idx seq_in_index:1 # Index sequence 1 column_name:authors******************************* key_name:au_ Info_idx seq_in_index:2 # index sequence 2 column_name:info

5, the Tb6 table GMT character Jianjian Spatial Index:

Build table:

Mysql> CREATE TABLE Tb6 (GMT geometry not null) Engine=myisam;

To increase the spatial index:

Mysql> ALTER TABLE TB6 add SPATIAL INDEX Spat_idx (GMT);

View:

Mysql>show index from Tb6\g

---------------------------------------------------------------------------------------

I'm a split line 2

---------------------------------------------------------------------------------------

View index:

Mysql> Show CREATE TABLE book \g UNIQUE key ' Uniqid_idx ' (' book_id '), key ' year_publication ' (' year_publication '), K EY ' Bookname_idx ' (' book_name '), key ' Coment_idx ' (' comment '), key ' au_info_idx ' (' authors ' (+), ' Info ' (50)) or mysql>show index from book\g

To delete an index:

mysql> ALTER TABLE book DROP INDEX UNIQID_IDX; # Remove the index named UNIQID_IDX.

Attention:

The unique index of the Add auto_increment constraint field cannot be deleted.

Or

Mysql> Drop index coment_idx on book; # Delete the index within the Book table named Coment_idx.

---------------------------------------------------------------------------------------

I'm a split line 3

---------------------------------------------------------------------------------------

Summarize:

1. How important is the index to the database, and how should it be used?

Choosing the correct index for a database is a complex task.

If you have fewer index columns, you need less disk space and maintenance overhead.

If you create multiple combinations of indexes on a large table, the index file expands quickly.

The other side of the index can overwrite more queries.

Deleting the CREATE index does not affect the application, nor does it affect the database schema, so you should try multiple different indexes to establish an optimal index.

2, try to use short index.

Indexes a field of type string, if possible a prefix length should be specified.

For example, there is a char (255) column, and if the majority value is unique within the first 10 or 30 characters, you do not need to index the entire column.

Short indexes not only improve query speed but also save disk space and reduce I/O operations.

3, is not the index to build more the better?

A reasonable index can improve query speed, but it is better to have more indexes. When the INSERT statement is executed, MySQL will index the newly inserted record, so too many indexes can cause the insert operation to become very slow.

In principle, the index is only used in fields that are queried.

4, for the index in the query statement does not play a role?

In some cases, query statements are used with indexed fields. However, the indexed fields do not work.

For example, a string that matches the like keyword in the Where condition begins with "%" and does not work in this case.

The OR keyword link condition is used in the Where condition, and other indexes do not work if there are 1 fields that do not use an index.

If you use a multicolumn index, but you do not use the first field in a multicolumn index, the Multi-column index does not work.

MySQL Index Introduction