Retake course day44 (MySQL eight index query II)

Creation of an index

The index slows down the write operation and optimizes the read time

Index: Normal index, which accelerates the time to find.

Fulltext: Full-text indexing, you can select fields that occupy very large text information as indexed fields. The use of Fulltext requires third-party software Sphinx dedicated to the search.

Create a format that is indexed when you create a table:

CREATE TABLE table name (field 1 type constraint key, ...). ）;

CREATE TABLE table name (field 1 type, ....) , constraint key)

Mysql> CREATE TABLE t2 (ID int primary key auto_increment, name char (n) not null unique); Query OK, 0 rows affected (0.24 sec) mysql> desc t2;+-------+----------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+-------+----------+------+-----+---------+----------------+| ID | Int (11) | NO | PRI | NULL | auto_increment | | name | char (15) | NO | UNI |                NULL |    |+-------+----------+------+-----+---------+----------------+2 rows in Set (0.01 sec) mysql> CREATE TABLE t3 (id int, Name Char (*), index idx_id (id)); Query OK, 0 rows affected (0.24 sec) mysql> desc t3;+-------+----------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+----------+------+-----+---------+-------+| ID | Int (11) | YES | MUL |       NULL | || name | char (15) |     YES | |       NULL | |+-------+----------+------+-----+---------+-------+2 rows in Set (0.01 sec) 

Common index constraint keys are: Primary key Unique Key,

The normal indexes are: Index

The specified field is an indexed field after the table is created:

Create index named on table name (field name);

ALTER TABLE name add index named (field name); MySQL

mysql> CREATE table t4 (id int, name char (15)); Query OK, 0 rows affected (0.25 sec) mysql> CREATE TABLE t5 (id int, name char (15)); Query OK, 0 rows affected (0.33 sec) mysql> desc t4;+-------+----------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+----------+------+-----+---------+-------+| ID | Int (11) |     YES | |       NULL | || name | char (15) |     YES | |       NULL | |+-------+----------+------+-----+---------+-------+2 rows in Set (0.03 sec) mysql> CREATE index idx_id on t4 (ID); Query OK, 0 rows affected (0.25 sec) records:0 duplicates:0 warnings:0mysql> desc t4;+-------+----------+------+--- --+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+----------+------+-----+---------+-------+| ID | Int (11) | YES | MUL |       NULL | || name | char (15) |     YES | |       NULL | |+-------+----------+------+-----+---------+-------+2 rows in Set (0.01 sec) mysql> ALTER TABLE T5 ADD index idx_id (ID); Query OK, 0 rows affected (0.19 sec) records:0 duplicates:0 warnings:0mysql> desc t5;+-------+----------+------+--- --+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+----------+------+-----+---------+-------+| ID | Int (11) | YES | MUL |       NULL | || name | char (15) |     YES | |       NULL | |+-------+----------+------+-----+---------+-------+2 rows in Set (0.01 sec)

To delete an index:

Drop index index name on table name

Delete PRIMARY key: ALTER TABLE name drop PRIMARY key;

Mysql> desc t4;+-------+----------+------+-----+---------+-------+| Field | Type     | Null | Key | Default | Extra |+-------+----------+------+-----+---------+-------+| ID    | int  | YES  | MUL | NULL    |       | | name  | char (15) | YES  |     | NULL    |       | +-------+----------+------+-----+---------+-------+2 rows in Set (0.01 sec) mysql> Drop index idx_id on T4; Query OK, 0 rows affected (0.16 sec) records:0  duplicates:0  warnings:0mysql> desc t4;+-------+----------+- -----+-----+---------+-------+| Field | Type     | Null | Key | Default | Extra |+-------+----------+------+-----+---------+-------+| ID    | int  | YES  |     | NULL    |       | | name  | char (15) | YES  |     | NULL    |       | +-------+----------+------+-----+---------+-------+

Two-Test

Test for comparison symbols:< > =! = >= <=

Caveats: Inserting an index over and changing the structure of the tree if the record is added.

If the repetition rate of a field is too high, the efficiency of the query is slowed down if the condition is true. If the condition is not set, the query time will be very block

Try to select a field with a higher degree of sensitivity as the index. Usually there is a repetition in the 10 records.

Indexed fields are not able to participate in the calculation because the condition is not clear, so it is also calculated from the first record, which increases the scope of the lookup. It can be very time-consuming, too.

Tests for logical symbols: and OR not

In and the lookup: From left to right first find the index of high-sensitivity index field, if the condition is set, go to the index of the field to find, if not, it will not be looked up. If the condition is not established, then the order of the lookup will be very block, if the index field before and before the low sensitivity or the scope of the search is large, it is also time-consuming, the search efficiency will be very low.

In the lookup of or: Find records from left to right, if the previous conditions are true, you will not find the following conditions, and if the previous conditions are not true, the following conditions will be found. If the lookup of the index field is low, or will pull down the search efficiency, if the index field to find a high degree of sensitivity, it depends on the scope of the search, if the scope of the search is too large, the efficiency will not be improved, if a condition is defined, or the scope is small, this will improve the efficiency of the search.

Range comparison: In between like

Tests for federated indexes: and OR

A federated index is a combination of fields that need to be judged to create an index. A federated index is the first of the following field unions.

What is called the leftmost prefix: the lookup order of a federated index is found from left to right, and if it is and, the lookup of a high-sensitivity index field is performed first, and if the condition is set to perform other conditions, if it is not, no other condition will be performed, and if it is or, it is executed from left to right, If the condition is true, no other index conditions will be executed.

Note: You should place the explicit condition field in front of the criteria field in the right range.

The sort field must be an indexed field.

For a federated index, as long as there is a field with the first federated index, it will work, and if not, it will not be effective.

Overwrite index You can use the first field of the federated index whenever you have one

Merge indexes: Create multiple single-column indexes that can be combined or used individually.

More information visit: HTTP://WWW.CNBLOGS.COM/LINHAIFENG/ARTICLES/7274563.HTML#_LABEL7

Retake course day44 (MySQL eight index query II)