Shulou(Shulou.com)06/01 Report--

This article uses easy-to-understand examples to introduce how to optimize SQL statements, the code is very detailed, interested friends can refer to, hope to be helpful to you.

1. Common SQL practices

(1) Index cannot be used in negative conditional queries.

Select * from order where statusThe 0 and stautsmanship 1

Not in/not exists is not a good habit.

Can be optimized to an in query:

Select * from order where status in (2pm 3)

(2) leading fuzzy query cannot use index.

Select * from order where desc like'% XX'

Instead of leading fuzzy queries, you can:

Select * from order where desc like 'XX%'

(3) indexing is not suitable for fields with low data differentiation.

Select * from user where sex=1

Reason: the gender is only male and female, and the data filtered out each time is very small, so it is not suitable to use the index.

Empirically, indexes can be used when 80% of the data can be filtered. For order status, if there are few status values, it is not appropriate to use an index, and if there are many status values that can filter a large amount of data, an index should be established.

(4) calculation on attributes cannot hit the index.

Select * from order where YEAR (date) < = '2017'

Even if an index is established on date, a full table scan is performed, which can be optimized for value calculation:

Select * from order where date < = CURDATE ()

Or:

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

Second, not the well-known practice of SQL

(5) if most of the business is a single query, it is better to use Hash index, such as user center.

Select * from user where uid=?select * from user where login_name=?

Reason:

The time complexity of B-Tree index is O (log (n)).

The time complexity of Hash index is O (1).

(6) columns that are allowed to be null. The query has a potential pit.

The single column index does not store null values, and the composite index does not store all null values. If the column is allowed to be null, you may get a result set that does not meet expectations.

Select * from user where name! = 'shenjian'

If name allows null, the index does not store null values, and these records are not included in the result set.

So, use not null constraints and default values.

(7) the leftmost prefix of the compound index, not the value, the where order of the SQL statement should be the same as that of the compound index.

The user center has established a composite index of (login_name, passwd).

Select * from user where login_name=? And passwd=?select * from user where passwd=? And login_name=?

Can hit the index.

Select * from user where login_name=?

It can also hit the index to satisfy the leftmost prefix of the composite index.

Select * from user where passwd=?

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

(8) use ENUM instead of string

What ENUM saves is TINYINT. Don't make some strings like "China", "Beijing" and "Technical Department" in the enumeration. The string space is large and inefficient.

III. Minority but useful SQL practices

(9) limit 1 can improve efficiency if you clearly know that only one result is returned.

Select * from user where login_name=?

Can be optimized to:

Select * from user where login_name=? Limit 1

Reason:

You know there is only one result, but the database does not know it. Tell it explicitly to stop the cursor movement actively.

(10) put computing in the business layer rather than the database layer, in addition to saving data CPU, there are unexpected query cache optimization effects.

Select * from order where date < = CURDATE ()

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

$curDate = date ('Ymurmmurd'); $res = mysql_query (' select * from order where date < = $curDate')

Reason:

Released the CPU of the database

After multiple calls, the SQL passed in is the same before the query cache can be used.

(11) cast will scan the whole table.

Select * from user where phone=13800001234

Do you think it will hit the phone index? That's a big mistake. How on earth should this sentence be changed?

Finally, add one more item, do not use select *, only return the required columns, which can greatly save the amount of data transfer and the memory usage of the database.

On how to optimize the SQL statement to share here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.