MYSQL index type, under what circumstances index is not used, and under what circumstances is not recommended

Use of MySQL explain:

http://blog.csdn.net/kaka1121/article/details/53394426

Index type

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

The code is as follows:

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:

I. GENERAL index

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

1. Create an index

The code is as follows:

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.

2. Modify the table structure

The code is as follows:

ALTER mytable ADD INDEX [IndexName] on (username (length))--Specify directly when creating a table

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;

Second, 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:

The code is as follows:

CREATE UNIQUE INDEX indexname on mytable (username (length))
--Modify table structure
ALTER mytable ADD UNIQUE [IndexName] on (username (length))
--Specify directly when creating a table
CREATE TABLE mytable (ID INT NOT NULL, username VARCHAR (+) not NULL, UNIQUE [IndexName] (username (length)));

Third, 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:

The code is as follows:

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.

Iv. Combined Index

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

The code is as follows:

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:

The code is as follows:

ALTER TABLE mytable ADD INDEX name_city_age (name (ten), city,age);

Under what circumstances are indexes, but not used?

Indexes do not always take effect, such as the following, which will invalidate the index :

1. If there is or in the condition, even if some of the conditions in the index are not used ( which is why the use of or is minimized ), in the example user_id no index

Note: If you want to use or, and you want the index to take effect, you can only index each column in the OR condition

2. For multi-column indexes, not the first part of Use , the index is not used

3.like queries are preceded by%

4. The data type of the indexed column is invisible, and the index is not used, for example, if the column type is a string, it is important to use quotation marks in the condition, otherwise the index will not be used.

5. There is a mathematical operation on the index column in the WHERE clause, with no index

6. Use functions for indexed columns in the WHERE clause, not indexed

7. If MySQL estimates that using a full table scan is faster than using an index, the index is not used

For example, a table with a very small amount of data

Under what circumstances is it not recommended to use an index?

1) fields with poor data uniqueness (only a few values in a field) do not use the index

For example, gender, there are only two possible data. This means that the index has less than two tree levels and is more of a peer. Such a two-tree lookup is tantamount to a full-table scan.

2) frequently updated fields do not use the index

For example, Logincount login times, frequent changes in the index also frequent changes, increase the workload of the database, reduce efficiency.

3) The field does not have to be indexed when the where statement appears, and if it has a condition such as null/is not null/like '% input% ', it is not recommended to use the index

MySQL will use the index only if it appears in the WHERE statement

4) The WHERE clause is not equal to the index column (<>), using the index effect generally

Improve query efficiency by using overlay indexes

Overwrite indexes (covering index): MySQL only needs to be indexed to return the data required by the query, without having to query the data after the index is found

For details, please refer to:

https://xiaobin.net/201109/strange-sql-performance-problem/

21 Best practices for MySQL performance optimization

Please refer directly to:

http://blog.csdn.net/kaka1121/article/details/53395587

Optimization steps for MySQL

Please refer directly to:

Http://www.cnblogs.com/hongfei/archive/2012/10/19/2731342.html

Some other blog posts about MySQL

Http://www.cnblogs.com/hongfei/category/349096.html

Reference article:

Http://www.jb51.net/article/49346.htm

Http://www.cnblogs.com/hongfei/archive/2012/10/20/2732589.html

https://xiaobin.net/201109/strange-sql-performance-problem/

Http://www.cnblogs.com/hongfei/archive/2012/10/19/2731342.html

MYSQL index type, under what circumstances index is not used, and under what circumstances is not recommended