Clustered indexes, nonclustered indexes, optimized indexes in the database

In this article, let's discuss the index problem, this article will not describe how to create an index, but will explain how to optimize the index.

What is an index?

An index is a way to sort records by multiple fields. Indexing a field in a table creates another data structure that holds the value of the field, and each value points to the record associated with it. The data structure of this index is sorted so that it can perform a binary lookup.

How do you understand the index? We often use the Windows system to query some files, the system will advise us to establish the index of the file. For example, if you want to query a file name, the system to scan all files for a fool scan, of course, the speed will be very slow. When we set up the index, after the relevant algorithm analysis, we will quickly query out the file name we want.

Now that we know the definition of the index, let's talk about clustered indexes, nonclustered indexes.

Clustered index, nonclustered index

Clustered index

A clustered index is the order in which a column is sorted in a data table. When a clustered index is created in a table, the default sort for the table is sorted by this column.

A table can have only one clustered index, because the table can have only one default sort.

Nonclustered indexes

Nonclustered indexes have a structure that is independent of the data rows. Items in a nonclustered index are stored in the order of the index key values, and the information in the table is stored in a different order (this can be specified by a clustered index). For nonclustered indexes, you can create a nonclustered index for each column that is commonly used when looking up data in a nonclustered index on a table.

Query optimizer

The query optimizer typically chooses the most efficient method when executing a query. However, if there is no index, the query optimizer must scan the table. Your task is to design and create an index that works best for your environment so that the query optimizer can choose from multiple valid indexes.

Optimizing indexes

(These are all online search, here to do some records)

(1) Negative condition query cannot use index

SELECT * from order where status!=0 and stauts!=1

Not in/not exists are not good habits

Can be optimized for in query:

SELECT * from order where status in (2,3)

(2) A leading fuzzy query cannot use an index

SELECT * FROM order where desc like '%XX '

Instead of a leading fuzzy query, you can:

SELECT * FROM order where desc like ' XX% '

(3) The fields with small data sensitivity should not be indexed

SELECT * from user where sex=1

Reason: Gender only male, female, every time the data filtered out is very little, not to use the index.

In experience, indexes can be used when filtering 80% of data. For order status, if the status value is very small, the index should not be used, if the state value is many, can filter a large amount of data, you should establish an index.

(4) Calculations on attributes cannot hit the index

SELECT * FROM order where year (date) < = ' 2017 '

Even if an index is established on date, it is fully scanned and can be optimized for value calculation:

SELECT * from order where date < = Curdate ()

Or:

SELECT * from order where date < = ' 2017-01-01 '

(5) If the business is mostly a single query, using hash index performance is better, such as User Center

SELECT * FROM user where Uid=?select * from user where login_name=?

Reason:

The time complexity of the B-tree index is O (log (n))

The time complexity of the hash index is O (1)

(6) Allow NULL column, query potentially large pits

A single-column index does not have null values, the composite index does not have all null values, and if the column is allowed to be null, you may get a result set that does not match expectations

SELECT * from user where name! = ' Shenjian '

If name is allowed to be null, the index does not store null values and these records are not included in the result set.

Therefore, use the NOT NULL constraint and the default value.

(7) compound index leftmost prefix, not value SQL statement where order is consistent with composite index

The User Center has built a composite index (login_name, passwd)

SELECT * from user where login_name=? and Passwd=?select * from user where passwd=? and login_name=?

are able to hit the index

SELECT * from user where login_name=?

can also hit the index to satisfy the leftmost prefix of the composite Index

SELECT * from user where passwd=?

cannot hit index , does not satisfy the leftmost prefix of composite index

(8) Use Enum instead of string

Enum saves tinyint, do not do in the enumeration of "China" "Beijing" "technical department" Such a string, the string space is large, inefficient.

(9) If it is clear that only one result is returned, limit 1 can improve efficiency

SELECT * from user where login_name=?

Can be optimized to:

SELECT * from user where login_name=? Limit 1

Reason:

You know there's only one result, but the database doesn't know, tell it explicitly, let it actively stop the cursor movement

(10) Put the calculation into the business layer, not the database layer, in addition to the data-saving CPU, there are unexpected query cache optimization effect

SELECT * from order where date < = Curdate ()

This is not a good SQL practice and should be optimized for:

$curDate = Date (' y-m-d '); $res = mysql_query (' SELECT * from order where date < = $curDate ');

Reason:

Freed the CPU of the database

Multiple invocations, the same SQL passed in, can take advantage of query caching

(11) Forced type conversion full table scan

SELECT * from user where phone=13800001234

do you think you'll hit the phone index? It's a big mistake, how is this statement going to change?

Finally, add another, do not use SELECT * (subtext, the article SQL unqualified =_=), only return the required columns, can greatly save the amount of data transfer, and the memory usage of the database yo.

Clustered indexes, nonclustered indexes, optimized indexes in a database