Common SQL practices

1) SELECT * FROM table where state!=0 and state!=1 not in/not exists so try not to appear in the search statement

Can be changed to select * from order where State in (2,3);

2) A leading fuzzy query is not able to use the index

SELECT * FROM table desc like '%XX ';

However, non-leading fuzzy queries can be:

SELECT * FROM table where desc like ' XX% ';

3) No index is recommended for fields with small data sensitivity

SELECT * from table where sex=1;

In gender, only men and women, every time the data filter is very small, it will reduce the 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.

Thought is more important than conclusion, hope has harvest.

Common SQL practices