MySQL's index

What is an index: when you look up your name in a dictionary, you have two ways. The first to first page of the page, the second, through pinyin, strokes, and other search. Then the second way is to look through the index. So the database is doing the same thing in MYSLQ. Indexes can increase query speed, but MySQL maintenance indexes also need to consume performance.

Classification of indexes:

| Primary key Index

If a field is the primary key (primary key), then MySQL automatically establishes the key index on that field

| Unique index

If the value of a field is unique across the entire table, you can create a unique index on that field, and once you find the data through a unique index, you do not need to continue looking because it is unique. It differs from the primary key index in that the primary key index must be established on the primary key.

| Normal index

If an index is established on a field that is not a primary key and whose value is not unique, then such an index is a normal index.

| Full-Text Indexing

If a full-text index is established on a field, MySQL extracts the values in that field, which it considers to be keywords, and then indexes each keyword, which is the full-text index. Note: Full-text indexing requires MyISAM engine support.

| Composite Index

When we build an index, we can index it together for multiple fields. When multiple fields are used to index together, such indexes are composite indexes.

To create a unique index or full-text index:

Syntax 1:create [Unique|fulltext] index index_name on table_name (table_ field);

Example: A normal index named Goods_name is established on the Goods_name field of the goods table.

To create a normal or composite index:

Syntax 2:alter tabel table_name Add index index_type index_name (field);

Cases:

Query index:

Grammar:

1, show index from TABLE_NAME;
2. Show keys from table_name;

Cases:

To delete an index:

Syntax 1:drop index INDEX_NAMEON table_name;

Cases:

Syntax 2:alter tabletable_name DROP INDEX index_name;

Cases:

To test the index effect:

Syntax: explain SELECT statement;

Cases:

When there is no index:

There are indexing cases:

Select_type: query Type, description

Type: This is an important column that shows what type of connection is used. The best to worst connection types are const, EQ_REG, ref, range, index, and all

All :Full table scan; MySQL will traverse all tables to find matching rows;Index:Index Scan; The difference between index and all is that index types only traverse the index;Range:Index range Scan, the scan of the index starts at a point, returns the row of matching values, common with between,<,> and other queries;Ref:A non-unique index scan that returns all rows that match a single value, and is commonly found in non-unique prefixes that use non-unique indexes, which are unique indexes;Eq_ref:Unique index Scan, for each index key, only one record in the table matches, often used for primary key or unique index scan;Const,system:These access types are used when MySQL optimizes a part of a query and transitions to a constant. If you place the primary key in the where list, MySQL can convert the query into a constant. Disadvantages of the index: | occupies disk space. Sometimes the size of the index is larger than the size of the data. | Slows the speed of data insertion and deletion. because the index needs to be updated each time it is inserted and deleted, the more indexes a table has, the greater the average performance degradation of the write operation. | A problem that causes storage fragmentation. when a record is deleted, it causes the corresponding entry in the corresponding index to be empty, because the index is stored in the B-tree structure, so the corresponding index entry will not be deleted, after a period of deletion and modification, there will be a lot of storage fragmentation in the database. Storage fragmentation not only occupies disk space, but also degrades database performance.

MySQL's index