MySQL's index detailed

1. What is an index:

The index is the database through a certain organization (b-tree, etc.) to create the index column on the value and location of storage has reached the purpose of fast query.

1. Types of indexes:

(1) Primary key index, one table specifies the primary key, and the index is automatically created.

(2) Unique indexes, column values cannot be the same, but can be null relative to the primary key index.

(3) Fulltext is a full-text index used to search for long text.

(4) Normal index does not have the above limitation

There are examples of individuals who are doing well:

This system has a membership table
The following fields are available:
Member ID INT
Member name VARCHAR (10)
Member ID number VARCHAR (18)
Member Phone VARCHAR (10)
Member Address VARCHAR (50)
Member Note Information TEXT

Then this membership number, as the primary key, using PRIMARY
Member name if you want to index, then it is the normal index
Member ID number if you want to index, then you can choose unique (unique, not allowed to repeat)
Member notes information, if need to build index, you can choose Fulltext, full-text search.

However, the fulltext is best used for indexing long text, and is used in shorter text, the normal index is available.

1. Index creation, deletion, viewing

Mysql>desc Card_info;

+--------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+--------+-------------+------+-----+---------+-------+

| number| Int (8) | NO | PRI |      NULL | |

|name | varchar (10) |    NO | |       NULL | |

|phone | Int (11) |     YES | |      NULL | |

| ID | Int (18) | NO | UNI |       NULL | |

|info | Text |     YES | |      NULL | |

+--------+-------------+------+-----+---------+-------+

1. The primary key index is created by default when the primary key is specified, and the ID also produces a unique index

Mysql>show index from Card_info;

+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+-------- +------+------------+---------+---------------+

| Table | Non_unique | Key_name | Seq_in_index | column_name | Collation | Cardinality | Sub_part | Packed | null| Index_type | Comment | index_comment |

+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+-------- +------+------------+---------+---------------+

|card_info | 0 |           PRIMARY | 1 | number |           A |     0 | NULL |     NULL | |         BTREE |               | |

|card_info | 0 |            ID | 1 | ID |           A |     0 | NULL | NULL | YES |         BTREE |               | |

+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+-------- +------+------------+---------+---------------+

2 rows in Set (0.00 sec)

2. Create a normal index

Create INDEX index_name on card_info (name);

Mysql> Show index Fromcard_info;

+-----------+------------+------------+--------------+-------------+-----------+-------------+----------+------ --+------+------------+---------+---------------+

| Table | Non_unique | Key_name | Seq_in_index | column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | index_comment |

+-----------+------------+------------+--------------+-------------+-----------+-------------+----------+------ --+------+------------+---------+---------------+

|card_info |          0 | primary    |           1 | number      | a         |           0 |     null | null   |     | btree      |         |                |

|card_info |          0 | id          |            1 | id          | a        |           0 |     NULL | null   | yes | btree      |         |                |

|card_info | 1 |            index_name| 1 | name |           A |     0 | NULL |     NULL | |         BTREE |               | |

+-----------+------------+------------+--------------+-------------+-----------+-------------+----------+------ --+------+------------+---------+---------------+

3 Rows in Set (0.00 sec)

3. Create FULLTEXT Index

ALTER TABLE Card_info ADD FULLTEXT index Index_info (info)

4. Delete Index

5. mysql> ALTER TABLE Card_info DROP INDEX index_name;

6. Create a federated index

Create INDEX Name_phone on Card_info (Name,phone);

2. Index Selection and considerations
4. Create an index
   Indexing is especially important for queries that are the primary application. A lot of times the performance problem is simply because we forgot to add an index, or we didn't add a more efficient index. If you do not index, then find any even a specific data will be a full table scan, if a table of large amounts of data and meet the results of a few, then the non-index will cause a fatal performance degradation. However, it is not always possible to index, for example, gender may be only two values, index not only have no advantage, but also affect the speed of the update, which is called over-index.
6. Composite Index
   For example, there is a statement like this: select* from users where Area= ' Beijing ' and age=22;
   If we were to create a single index on area and age, the MySQL query could use only one index at a time, so while the full table scan was a lot more efficient when it was relatively non-indexed, it would be more efficient to create a composite index on the area or age two column. If we create a composite index (area,age, salary), it is actually equivalent to creating (Area,age,salary), (Area,age), (area) Three indexes, which is called the best left-prefix attribute. Therefore, when creating a composite index, the columns that are most commonly used as constraints should be placed on the leftmost, decreasing in turn.
8. The index does not contain a NULL column of Values
   This column is not valid for this composite index as long as the column contains null values that will not be included in the index, as long as there is a column in the composite index that contains null values. So we don't want the default value of the field to be null when the database is designed.
10. Use short Index
    Index A string, or specify a prefix length if possible. For example, if you have a column of char (255), and if the majority value is unique within the first 10 or 20 characters, do not index the entire column. Short indexes not only improve query speed but also save disk space and I/O operations.
12. Indexed issues for sorting
    The MySQL query uses only one index, so if an index is already used in the WHERE clause, then the column in the list is not indexed. So do not use sort operations where the default sorting of the database is acceptable, and try not to include multiple columns, if you need to create a composite index for those columns.
14. like Statement Operations
    It is generally discouraged to use the like operation, which is also an issue if it is not used. Like "%aaa%" does not use the index and like "aaa%" can use the index.
16. Do not perform calculations on columns
    SELECT * from the users where year (adddate) <2007; will operate on each row, which will cause the index to fail and perform a full table scan, so we can change to select* from users where adddate< ' 2007-01-01 ';
18. Do not use Notin and the <> Operation

Notin and <> operations do not use the index to perform a full table scan. Notin can be notexists instead, id<>3 can use Id>3or id<3 instead.

MySQL's index detailed