Beckham _ mysql index learning and Optimization _ MySQL-mysql tutorial

Mysql index and Optimization: 1. what is the index? 2. index type and syntax. 3. full-text index description. 1. what is the index? 1. take the book directory as an example. view the directory, then find the corresponding content. Therefore, the index adds a 'Directory' to the data to facilitate quick data discovery. Mysql index and optimization Brief:I. What is an index? ii. index type and syntax. iii. full-text index description I. What is an index?

1. take the book directory as an example. view the directory and find the corresponding content. Therefore, the index adds a 'Directory' to the data to facilitate quick data discovery.

2. Functions of indexes:

Benefits: faster query speed

Disadvantages: a. reduced the speed of adding, deleting, and modifying

B. increase the table file size (the index file may even be larger than the data file)

Case: There are 15 columns in a table and 10 columns have indexes. There are rows of data in total. how can we import data quickly?

Answer: 1. delete all the indexes of the empty table. 2. import data. 3. create an index after the data is imported.

3. index algorithms

There are N random records, no indexes, and an average of N/2 queries. what if an index is used?

3.1. the number of times binary tree indexes correspond to log2N

Example: Data 1, 2, 3, 4, 5, 6, and 7, with the center value 4 as the demarcation point

4 2 6 1 3 5 7

How many times does search 3 take?

Because 3 <4, 3 is on the left of the binary tree. because 3> 2, 2 is on the right of the root node. The result is required twice.

3.2 hash index, theoretically one time

Example: Data 1, 2, 3, 4, 5, 6, 7

Hash [1] = 001 Hash [2] = 003 Hash [3] = 005 Hash [4] = 007 Hash [5] = 009 Hash [6] = 011 Hash [7] = 013

How many times does search 3 take?

Hash the file and obtain 005. Exactly once.

Insufficient hash:

A. It is a waste of space because the hash value is not continuous.

B. high hash requirements. ensure that the hash values of each value are different.

4. indexing principles

A. excessive index

B. index condition columns (the most frequent condition after where is more suitable for indexing)

C. index hash values. values that are too concentrated cannot be indexed (for example, gender)

5. view the table structure

5.1. the storage engine is myisam.

Frm is the table structure, MYD is the data file, and MYI is the index file.

5.2. the storage engine is innodb

Frm is a table structure, ibd is a data file, and an index file.

II. index types and syntax

1. type

A. normal index: just accelerate the query speed

B. unique index: the value on the row cannot be repeated.

C. primary key index (primary key): it cannot be repeated.

D. full-text index ):

The relationship between the unique index and the primary key index:

The primary key must be unique, but the unique index is not necessarily the primary key. a table can have only one primary key, but one or more unique indexes.

2. how to view indexes in a table

3. create an index

3.1 create an index for an existing table

Syntax: alter table name add index/uniqueindex/fulltext index/primary key [index name] (column name) (Note: The index name is optional and is not specified as the same as the column name)

Table structure:

Create table m (id int, emailvarchar (30), tel char (11), intro text) engine = myisam charset = utf8;

A. create a common index for the tel column

(Note: the specified index name is the same as the column name)

B. add a unique index to the email column

C. add full-text indexes to the intro column

D. add a primary key index to the id column.

E. add a composite index of multiple columns

(Note: this general index m applies to the email and tel columns)

Error:

Cause of error: the column where the index is applied is not specified.

3.2 specify the index when creating a new table

Create table m (id int primary keyauto_increment, email varchar (30), tel char (11), intro text, index (tel), uniqueindex (email), fulltext index (intro )) engine = myisam charset = utf8;

4. delete an index

4.1 Delete common index/unique index/full-text index

4.2 Delete the primary key index

If the primary key column itself is auto-incrementing, an error will be reported during deletion.

In this case, you should first modify the column auto-increment attribute.

III. full-text index

Full-text index is of little significance to Chinese characters by default in mysql.

There are spaces and punctuation marks in English to split words into indexes.

For Chinese, there is no space to separate words. Mysql cannot recognize every Chinese word.

Usage: match (full-text index name) against ('keyword ');