当前位置：网站首页>12 SQL tips you may not know

12 SQL tips you may not know

2022-04-21 21:25:00 【InfoQ】


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 a ,
Do not use select *
（ Subtext , The article SQL None of them are qualified  =_=）,
Just return the required columns
, Can greatly save data transmission , And database memory usage .

版权声明
本文为[InfoQ]所创，转载请带上原文链接，感谢
https://yzsam.com/2022/04/202204212123005407.html

边栏推荐

猜你喜欢

随机推荐