项目实际场景中的SQL优化

深碍√TFBOYSˉ_ 2024-02-20 03:52 40阅读 0赞

1.当 limit 的基数比较大时可以使用 between 来代替。  
比如一个查询需要使用 limit 语句从排序了的数据中的第10w条数据开始，查看后面的10条数据，limit 100000，10；那么这种情况它需要检索并排序大量的数据，随着数据量的增大，它的效率也随之降低。  
那么我们就可以使用 between 来代替limit，between 100000 and 100010，between可以利用索引的特性，并且只对条件中的范围进行检索和返回，这样就提减少了数据库的工作量，提高了效率。

例如：select * from admin order by admin_id limit 100000,10
    优化为：select * from admin where admin_id between 100000 and 100010 order by admin_id。

2.避免在索引列上进行计算，这样索引会失效。  
（１）在对索引列使用计算或函数操作时，如加减乘除、不等于等，数据库的优化器不会使用索引，从而会导致全表扫描。  
如下，在salary索引列中进行计算，会放弃索引进行全表扫描。

效率低： select * from user where salary*22>11000(salary是索引列)
    效率高： select * from user where salary>11000/22(salary是索引列)

（２）如果我们需要查询年份大于2014年的数据，如果我们在条件中用了year（）函数，  
“year（时间字段）> 2014” 的话，那么它会对时间字段先计算出年份，然后再比较，这样就没走索引了。  
那么我们可以直接进行比较，比如“ 时间字段 > ‘2014-01-01’ ”,这样就没有了计算，数据库可以直接使用索引进行范围查找。

例如：select * from admin where year(admin_time)>2014
    优化为：select * from admin where admin_time> '2014-01-01′

3.还有比如在 join 连接时，要注意大小表的位置，要用小表驱动大表，left join时，小表在前，right join时，小表在后。

select * from admin left	join	log on admin.admin_id	= log.admin_id where log.admin_id>10
    优化为：select * from (select * from admin where admin_id>10) T1 lef join log on T1.admin_id = log.admin_id。