Small problems with database indexing

First, some common SQL practices

(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) < = ' "

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 '

II. SQL Practices that are not known

(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.

Third, niche but useful SQL practices

(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.

Small problems with database indexing