Supplement 12. Review of the index of MySQL

What is the index in MySQL?

The index, also called "Key" in MySQL, is a marker used by the MySQL storage engine to quickly find records, which plays a key role in improving query speed, and the role of indexes becomes more important when the amount of data in a table is large enough.

Creating and maintaining indexes takes a lot of hard disk space and time, but can greatly improve query speed.

Second, before using the index, you need to pay attention to.

1. As far as possible to use some simple data types, shaping the data type than string, processing cost is much smaller, because the comparison of strings more complex. In MySQL, you should use a built-in date and time data type instead of a string to store the time, and an integer data type to store the IP address.

2. Try to avoid null values (NULL) it is recommended that each field be specified as NOT null (unless you want to store null) in MySQL, columns with null values are difficult to query-optimize because they make indexes, index statistics, and comparison operations more complex. You should use 0, a special value, or an empty string instead of a null value.

3. Do not over-use the index.

4. After the query statement, where after the more frequent conditions, more appropriate to use the index.

5. A field with an overly concentrated value is not recommended, even if it is added, it will not increase the efficiency (e.g. gender: male/female)

6. Remember not to perform operations on an indexed field, for example: SELECT * from the users where year (adddate) <2007, which will be performed on each row, which will cause the index to fail with a full table scan, so we can change to: SELECT * FROM Users where adddate< ' 2007-01-01′. This single quote can cause a performance loss of nearly a hundredfold for MySQL.

Third, the type of index commonly used in MySQL.

1. Normal index: The most basic index type, without any restrictions, only speed up the query speed. (more commonly used)

The index of the default btree type in Myiasm is also the index we use in most cases.

1.1 Create an index directly:

CREATE index name on table name (field name)

1.2 Create an index directly on the table structure:

ALTER TABLE name add index/unique/fulltext[index name] (field name)

1.3 Create a primary key index:

ALTER TABLE name Add primary key (field name)

1.4 Delete an index:

To delete a non-primary key index:

ALTER TABLE name DROP INDEX name;

To delete a primary key index:

ALTER TABLE name drop PRIMARY key;

2. Unique index: Values within a field cannot be duplicated.

Like a normal index, the difference is that the value of the indexed column must be unique, but it allows for a null value (note differs from the primary key). If it is a composite index, the combination of column values must be unique, similar to the creation method and the normal index.

3. Primary KEY index: The value in the field cannot be duplicated, although the primary key index field cannot be duplicated, but the unique index is not necessarily a primary key.

There can be only one primary key on a table, but there may be multiple unique indexes.

4. Full Text index:

Fulltext indexes are only available for MyISAM tables; they can be created as part of a CREATE TABLE statement from char, varchar, or text columns, or subsequently added using ALTER TABLE or CREATE INDEX.

Enter your data into a table without a Fulltext index, and then create an index that is faster than entering the data into an existing Fulltext index. But remember, for a large data table, generating a full-text index is a very expensive way to consume hard disk space.

– Create tables that are suitable for adding full-text indexes

CREATE table ' table ' (

' id ' int (one) not NULL auto_increment,

' title ' char (255) CHARACTER SET UTF8 COLLATE utf8_general_ci not NULL,

' Content ' text CHARACTER SET UTF8 COLLATE utf8_general_ci NULL,

' Time ' int (ten) null DEFAULT NULL,

PRIMARY KEY (' id '),

Fulltext (content)

);

– Modify table structure to add full-text indexes

ALTER TABLE Article ADD fulltext index_content (content)

– Create indexes directly

CREATE Fulltext INDEX index_content on article (content)

5. Multi-column index:

Multiple single-column indexes differ from the query effect of a single multicolumn index because MySQL can use only one index when executing a query, and one of the most restrictive indexes is selected from multiple indexes. (Personally, a multicolumn index does not have a single-column index query efficiency).

This article is from the "Rebirth" blog, make sure to keep this source http://suhaozhi.blog.51cto.com/7272298/1933258

Supplement 12. Review of the index of MySQL