10 little-known SQL statement optimizations

1. Some common SQL practices

(1) Negative conditional queries cannot use indexes

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

not in/not exists is not a good habit

Recommended "mysql video tutorial"

can be optimized as in query:

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

(2) Leading fuzzy query cannot use index

select * from order where desc like '%XX'

but non-leading fuzzy query can:

select * from order where desc like 'XX%'

( 3) It is not appropriate to use indexes for fields with little data differentiation

select * from user where sex=1

Reason: There are only male and female genders, and very little data is filtered out each time, so it is not appropriate to use indexes.

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

(4) Calculation on attributes cannot hit the index

select * from order where YEAR(date) < = &#39;2017&#39;

Even if an index is established on date, the entire table will be scanned, which can be optimized for value calculation:

select * from order where date < = CURDATE()

Or:

select * from order where date < = &#39;2017-01-01&#39;

2. Not a well-known SQL practice

(5) If most of the business is a single query, the performance of using Hash index 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) Columns that are allowed to be null have potential pitfalls in queries

Single column indexes do not store null values, and composite indexes do not store all null values. If a column is allowed to be null, you may get " "Unexpected" result set

select * from user where name != 'shenjian'

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

So, please use not null constraints and default values.

(7) The leftmost prefix of the composite index is not the where order of the value SQL statement must be consistent with the composite 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=?

can also hit the index, which satisfies the leftmost prefix of the composite index

select * from user where passwd=?

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

( 8) Use ENUM instead of string

ENUM saves TINYINT. Don’t include strings like "China", "Beijing" and "Technology Department" in the enumeration. The string space is large and the efficiency is poor. Low.

3. Niche but useful SQL practices

(9) If it is clear that only one result will be 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 is only one result, but the database does not know it. Tell it clearly and let it actively stop the cursor movement

(10) Putting calculations in the business layer instead of the database layer, in addition to saving data CPU, also has unexpected query cache optimization effects

select * from order where date < = CURDATE()

This is not a good SQL practice, it should be optimized as:

$curDate = date('Y-m-d');
$res = mysql_query(
    &#39;select * from order where date < = $curDate&#39;);

Reason:

Release the CPU of the database

Multiple calls, the incoming SQL is the same, the query cache can be used

(11) Forced type conversion will affect the entire table Scan

select * from user where phone=13800001234

Do you think it will hit the phone index? This is a big mistake. How can I change this statement?

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

This article comes from the php Chinese website, mysql tutorial column, welcome to learn!

The above is the detailed content of 10 little-known SQL statement optimizations. For more information, please follow other related articles on the PHP Chinese website!