谈谈SQL优化

╰半夏微凉° 2022-05-25 03:23 93阅读 0赞

\#\#一.索引失效的情况  
1.如果条件中有or，即使其中有条件带索引也不会使用（or条件均建独立索引，用UNION替换or）  
2.对于多列索引，不是使用的第一部分，则不会使用索引  
3.like查询是以%开头(以%结尾是可以的)  
4.如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引  
5.如果mysql估计使用全表扫描要比使用索引快,则不使用索引  
6.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如

select id from t where num is null

（最好不要给数据库留NULL，尽可能的使用 NOT NULL填充数据库.）  
7.应尽量避免在 where 子句中使用 != 或 <> 操作符，否则将引擎放弃使用索引而进行全表扫描。  
8.in 和 not in 也要慎用，否则会导致全表扫描（对于连续的数值，能用 between 就不要用 in 了）（用 exists 代替 in）

select num from a where exists(select 1 from b where num=a.num)

9.如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然 而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：

select id from t where num = @num

可以改为强制查询使用索引：

select id from t with(index(索引名)) where num = @num

10.应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where num/2 = 100

应改为

select id from t where num = 100*2

11.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如

select id from t where substring(name,1,3) = ’abc’       -–name以abc开头的id
    select id from t where datediff(day,createdate,’2005-11-30′) = 0    -–‘2005-11-30’    --生成的id

应改为

select id from t where name like 'abc%'
    select id from t where createdate >= '2005-11-30' and createdate < '2005-12-1'

12.在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。  
\#\#其他优化  
1.尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连 接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。  
2.尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。  
3.任何地方都不要使用 select \* from t ，用具体的字段列表代替“\*”，不要返回用不到的任何字段。  
4.尽量避免使用游标，因为游标的效率较差  
5.拆分大的 DELETE 或INSERT 语句，批量提交SQL语句

while(1){
    
     　　//每次只做1000条
    
    　　 mysql_query(“delete from logs where log_date <= ’2012-11-01’ limit 1000”);
    
     　　if(mysql_affected_rows() == 0){
    
    　　 　　//删除完成，退出！
    　　 　　break；
    　　}
    
    //每次暂停一段时间，释放表让其他进程/线程访问。
    usleep(50000)
    
    }

参考文章：http://www.cnblogs.com/yunfeifei/p/3850440.html  
http://www.bxoon.com/archives/%E8%B0%88%E8%B0%88sql%E4%BC%98%E5%8C%96