current position:Home>12 very useful SQL tips

12 very useful SQL tips

2022-01-27 01:32:33 Usher_ Ou

One 、 Some common SQL practice

(1) Negative conditional queries cannot use indexes .

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

not in/not exists It's not a good habit .
It can be optimized to in Inquire about :

select * from order where status in(2,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) Index is not suitable for fields with small data differentiation .

select * from user where sex=1

reason : Gender only male , Woman , There is very little data filtered out each time , Index should not be used .

Empirically , Can filter 80% Index can be used for data . For order status , If the state value is small , Index should not be used , If the state is worth a lot , It can filter a lot of data , It should be indexed .

(4) Calculating on properties does not hit the index .

select * from order where YEAR(date) < = '2020'

Even if date Indexed on , It will also scan the whole table , Can be optimized for value calculation :

select * from order where date < = CURDATE()

perhaps :

select  *  from order where date < = '2020-01-01'

Two 、 It's not known SQL practice

(5) If most of the business is single query , Use Hash Better index performance , For example, user center .

select * from user where uid=?

select * from user where login_name=?

reason :

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

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

(6) Allow for null The column of , There is a potential big hole .

Single column index does not exist null value , Composite index does not save all as null Value , If the column is allowed to be null, May get “ Fall short of expectations ” The result set .

select * from user where name != 'shenjian'

If name Allow for null, Index does not store null value , These records are not included in the result set .

therefore , Please use not null Constraints and defaults .

(7) Composite index leftmost prefix , Not mean SQL Of the statement where The order should be consistent with the composite index .

User center established (login_name, passwd) Composite index of

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 index , Satisfy the leftmost prefix of Composite Index .

select * from user where passwd=?

Can't hit index , Does not satisfy the leftmost prefix of Composite Index .

(8) Use ENUM Instead of strings .

ENUM What is kept is TINYINT, Don't do something about enumeration “ China ”“ Beijing ”“ Technology Department ” Such a string , String space is big , Low efficiency .

3、 ... and 、 Small but useful SQL practice

(9) If you know that only one result is returned ,limit 1 Can improve efficiency .

select * from user where login_name=?

It 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 computing in the business layer, not the database layer , Besides saving data CPU, There are also unexpected query cache optimization effects .

select * from order where date < = CURDATE()

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

$curDate = date('Y-m-d');

$res = mysql_query(

  'select * from order where date < = $curDate'

);

Voice over : Don't ask me what language it is .

reason : Free the database of CPU. Multiple calls , Incoming SQL identical , To use the query cache .

(11) Cast will scan the whole table

select * from user where phone=13800001234

You think you'll hit phone Index ? It's a big mistake !!!

Finally, add one more , Do not use select *( Subtext , The article SQL None of them are qualified =_=), Just return the required columns .

copyright notice
author[Usher_ Ou],Please bring the original link to reprint, thank you.
https://en.cdmana.com/2022/01/202201270132319267.html

Random recommended