MySQL index type list make MySQL run efficiently (go)

Transferred from: http://www.php100.com/html/webkaifa/database/Mysql/2010/0409/4279.html

Indexing is the key to fast searching. MySQL indexing is important for the efficient operation of MySQL. Here are a few common types of MySQL indexes.

In a database table, indexing a field can greatly improve query speed. Suppose we create a mytable table:

CREATE TABLE mytable (ID INT NOT NULL, username VARCHAR (+) not NULL); We randomly inserted 10,000 records, including one: 5555, admin.

In the Find username= "admin" record select * from MyTable WHERE username= ' admin '; If an index has been established on username, MySQL does not need any scanning, that is, the record can be found exactly. Instead, MySQL scans all records, that is, to query 10,000 records.

Index sub-column indexes and composite indexes. A single-column index, that is, an index contains only single columns, and a table can have multiple single-row indexes, but this is not a composite index. A composite index, that is, a cable that contains multiple columns.

MySQL index types include:

(1) General index

This is the most basic index and it has no limitations. It is created in the following ways:

Create an index

CREATE INDEX indexname on mytable (username (length)); If it is a Char,varchar type, length can be less than the actual length of the field, and if it is a blob and text type, length must be specified.

Modify Table Structure

ALTER mytable ADD INDEX [IndexName] on (username (length)) When creating a table, specify directly

CREATE TABLE mytable (ID INT NOT NULL, username VARCHAR (+) not NULL, INDEX [IndexName] (username (length))); Syntax for dropping an index:

DROP INDEX [IndexName] on mytable;

(2) Unique index

It is similar to the previous normal index, except that the value of the indexed column must be unique, but it allows for a null value. If it is a composite index, the combination of column values must be unique. It is created in the following ways:

Create an index

CREATE UNIQUE INDEX indexname on mytable (username (length)) Modify table structure

ALTER mytable ADD UNIQUE [IndexName] on (username (length)) When creating a table, specify directly

CREATE TABLE mytable (ID INT NOT NULL, username VARCHAR (+) not NULL, UNIQUE [IndexName] (username (length)));

(3) Primary key index

It is a special unique index and is not allowed to have null values. The primary key index is typically created at the same time as the table is built:

CREATE TABLE mytable (ID INT not NULL, username VARCHAR (+) NOT NULL, PRIMARY KEY (id)); Of course, you can also use the ALTER command. Remember: A table can have only one primary key.

(4) Combined index

To visually compare single-column and composite indexes, add multiple fields to the table:

CREATE TABLE mytable (ID int NOT NULL, username varchar (+) NOT NULL, City VARCHAR (+) NOT NULL, age INT NOT NULL  ); To further extract the efficiency of MySQL, it is necessary to consider building a composite index. is to build name, city, and age into an index:

ALTER TABLE mytable ADD INDEX name_city_age (name (ten), city,age); When the table is built, the usernname length is 16, which is used here in 10. This is because, in general, the length of the name does not exceed 10, which speeds up the index query, reduces the size of the index file, and increases the update speed of the insert.

If you set up a single-column index on Usernname,city,age, so that the table has 3 single-column indexes, the efficiency of the query and the combined index above is very different, much lower than our combined index. Although there are three indexes at this point, MySQL can only use one of the single-column indexes that it considers to be the most efficient.

The establishment of such a composite index, in fact, is equivalent to the following three sets of composite indexes:

Usernname,city,age usernname,city Usernname Why not city,age such a combination index? This is because the MySQL composite index is the result of the "leftmost prefix". The simple understanding is only from the left to the beginning of the combination. Not as long as the combined index is used for queries that contain these three columns, the following SQL uses this combined index:

SELECT * FROM MyTable whree username= "admin" and city= "Zhengzhou" select * FROM MyTable whree username= "admin" and the next few will not be used:

SELECT * FROM MyTable whree age=20 and city= "Zhengzhou" select * FROM MyTable whree city= "Zhengzhou"

(5) Timing of index creation

here we have learned to build an index, so where do we need to build the index? In general, the columns that appear in the where and join need to be indexed, but not entirely, because MySQL uses the index only for <,<=,=,>,>=,between,in, and sometimes like. For example:

SELECT t.name from MyTable T left joins MyTable m on T.name=m.username WHERE m.age=20 and m.city= ' Zhengzhou ' This time you need to establish a cable Because the userame of the MyTable table also appears in the join clause, there is a need to index it.

Just now it is only necessary to index the like at certain times. Because MySQL does not use indexes when querying with wildcards% and _. For example, the following sentence will use the index:

SELECT * FROM MyTable WHERE username like ' admin% ' and the following sentence will not be used:

SELECT * FROM MyTable wheret Name like '%admin ' Therefore, you should pay attention to the above differences when using like.

(6) Deficiencies of the index

The benefits of using indexes are described above, but excessive use of indexes will result in abuse. So the index has its drawbacks as well:

Although the index greatly improves query speed, it also slows down the updating of tables, such as INSERT, UPDATE, and delete on tables. Because when updating a table, MySQL not only saves the data, but also saves the index file.

Index files that create indexes that consume disk space. The general situation is not too serious, but if you create multiple combinations of indexes on a large table, the index file will swell up quickly.

Indexing is just one factor in efficiency, and if your MySQL has a large data size table, you need to spend time studying to build the best indexes, or refine the query statements.

(7) Considerations for using Indexes

There are some tips and considerations when working with indexes:

The index does not contain a column with null 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.

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.

Indexed column Sorting

The MySQL query uses only one index, so if an index is already used in the WHERE clause, the column in order by 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.

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.

Do not perform calculations on columns

SELECT * from the users where year (adddate) <2007; The operation will be performed on each line, 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 ';

Do not use not in and <> operations

Above, the MySQL index type is introduced.

Indexing is the key to fast searching. MySQL indexing is important for the efficient operation of MySQL. Here are a few common types of MySQL indexes.

In a database table, indexing a field can greatly improve query speed. Suppose we create a mytable table:

CREATE TABLE mytable (ID INT NOT NULL, username VARCHAR (+) not NULL); We randomly inserted 10,000 records, including one: 5555, admin.

In the Find username= "admin" record select * from MyTable WHERE username= ' admin '; If an index has been established on username, MySQL does not need any scanning, that is, the record can be found exactly. Instead, MySQL scans all records, that is, to query 10,000 records.

Index sub-column indexes and composite indexes. A single-column index, that is, an index contains only single columns, and a table can have multiple single-row indexes, but this is not a composite index. A composite index, that is, a cable that contains multiple columns.

MySQL index types include:

(1) General index

This is the most basic index and it has no limitations. It is created in the following ways:

Create an index

CREATE INDEX indexname on mytable (username (length)); If it is a Char,varchar type, length can be less than the actual length of the field, and if it is a blob and text type, length must be specified.

Modify Table Structure

ALTER mytable ADD INDEX [IndexName] on (username (length)) When creating a table, specify directly

CREATE TABLE mytable (ID INT NOT NULL, username VARCHAR (+) not NULL, INDEX [IndexName] (username (length))); Syntax for dropping an index:

DROP INDEX [IndexName] on mytable;

(2) Unique index

It is similar to the previous normal index, except that the value of the indexed column must be unique, but it allows for a null value. If it is a composite index, the combination of column values must be unique. It is created in the following ways:

Create an index

CREATE UNIQUE INDEX indexname on mytable (username (length)) Modify table structure

ALTER mytable ADD UNIQUE [IndexName] on (username (length)) When creating a table, specify directly

CREATE TABLE mytable (ID INT NOT NULL, username VARCHAR (+) not NULL, UNIQUE [IndexName] (username (length)));

(3) Primary key index

It is a special unique index and is not allowed to have null values. The primary key index is typically created at the same time as the table is built:

CREATE TABLE mytable (ID INT not NULL, username VARCHAR (+) NOT NULL, PRIMARY KEY (id)); Of course, you can also use the ALTER command. Remember: A table can have only one primary key.

(4) Combined index

To visually compare single-column and composite indexes, add multiple fields to the table:

CREATE TABLE mytable (ID int NOT NULL, username varchar (+) NOT NULL, City VARCHAR (+) NOT NULL, age INT NOT NULL  ); To further extract the efficiency of MySQL, it is necessary to consider building a composite index. is to build name, city, and age into an index:

ALTER TABLE mytable ADD INDEX name_city_age (name (ten), city,age); When the table is built, the usernname length is 16, which is used here in 10. This is because, in general, the length of the name does not exceed 10, which speeds up the index query, reduces the size of the index file, and increases the update speed of the insert.

If you set up a single-column index on Usernname,city,age, so that the table has 3 single-column indexes, the efficiency of the query and the combined index above is very different, much lower than our combined index. Although there are three indexes at this point, MySQL can only use one of the single-column indexes that it considers to be the most efficient.

The establishment of such a composite index, in fact, is equivalent to the following three sets of composite indexes:

Usernname,city,age usernname,city Usernname Why not city,age such a combination index? This is because the MySQL composite index is the result of the "leftmost prefix". The simple understanding is only from the left to the beginning of the combination. Not as long as the combined index is used for queries that contain these three columns, the following SQL uses this combined index:

SELECT * FROM MyTable whree username= "admin" and city= "Zhengzhou" select * FROM MyTable whree username= "admin" and the next few will not be used:

SELECT * FROM MyTable whree age=20 and city= "Zhengzhou" select * FROM MyTable whree city= "Zhengzhou"

(5) Timing of index creation

Here we have learned to build an index, so where do we need to build the index? In general, the columns that appear in the where and join need to be indexed, but not entirely, because MySQL uses the index only for <,<=,=,>,>=,between,in, and sometimes like. For example:

SELECT t.name from MyTable T left joins MyTable m on T.name=m.username WHERE m.age=20 and m.city= ' Zhengzhou ' This time you need to establish a cable Because the userame of the MyTable table also appears in the join clause, there is a need to index it.

Just now it is only necessary to index the like at certain times. Because MySQL does not use indexes when querying with wildcards% and _. For example, the following sentence will use the index:

SELECT * FROM MyTable WHERE username like ' admin% ' and the following sentence will not be used:

SELECT * FROM MyTable wheret Name like '%admin ' Therefore, you should pay attention to the above differences when using like.

(6) Deficiencies of the index

The benefits of using indexes are described above, but excessive use of indexes will result in abuse. So the index has its drawbacks as well:

Although the index greatly improves query speed, it also slows down the updating of tables, such as INSERT, UPDATE, and delete on tables. Because when updating a table, MySQL not only saves the data, but also saves the index file.

Index files that create indexes that consume disk space. The general situation is not too serious, but if you create multiple combinations of indexes on a large table, the index file will swell up quickly.

Indexing is just one factor in efficiency, and if your MySQL has a large data size table, you need to spend time studying to build the best indexes, or refine the query statements.

(7) Considerations for using Indexes

There are some tips and considerations when working with indexes:

The index does not contain a column with null 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.

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.

Indexed column Sorting

The MySQL query uses only one index, so if an index is already used in the WHERE clause, the column in order by 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.

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.

Do not perform calculations on columns

SELECT * from the users where year (adddate) <2007; The operation will be performed on each line, 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 ';

Do not use not in and <> operations

Above, the MySQL index type is introduced.