本人微信公众号"aeolian"~

sql查询优化

where 及 order by 涉及的列上建立索引。

where子句中使用!=<>操作符时,会导致引擎放弃使用索引而进行全表扫描。

like ‘%…%’(全模糊)这样的条件,是无法使用索引的,也会导致全表扫描。

where 子句中对字段进行 null 值判断时,会导致引擎放弃使用索引而进行全表扫描(可以在列上设置默认值0,确保表中的列没有null值)。

select id from table where phone is null
改为
select id from table where phone = 0
  where 子句中使用 or 来连接条件,将导致引擎放弃使用索引而进行全表扫描(union all)。
select id from table where score=99 or score=100    
改为    
select id from table where score=99    
union all    
select id from table where score=100
  in 和 not in 也要慎用,会导致全表扫描(连续的值可以用between代替)。
select id from table where score in(98,99,100)    
改为    
select id from table where score between 98 and 100
  where 子句中对字段进行表达式操作(“=”左边进行函数、算术运算或其他表达式运算),这将导致引擎放弃使用索引而进行全表扫描(不动字段,动固定值)。
select id from table where soccer/2=50    
改为:
select id from table where soccer=50*2
   where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描。
select id from table where substring(name,1,3)='abc'
改为    
select id from table where name like 'abc%'
   exists 代替 in 
select num from a where num in(select num from b)    
改为    
select num from a where exists(select 1 from b where num=a.num)    
索引并不是越多越好,索引固然可以提高 select 的效率,但同时降低了 insert 及 update 的效率,因为 insert 或 update 时有可能会重建索引。一个表的索引数最好不要超过6个
尽量使用数字型字段,若只含数值信息的字段尽量不要设计为字符型,这会降低查询和连接的性能,并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符,而对于数字型而言只需要比较一次就够了(类型尽量用数值标识,如男:1,女:0)。
尽可能的使用 varchar 代替 char ,因为首先变长字段存储空间小,可以节省存储空间,其次对于查询来说,在一个相对较小的字段内搜索效率显然要高些。
具体的字段列表代替“*”,不要返回用不到的任何字段。
避免频繁创建和删除临时表,以减少系统表资源的消耗。
如果一次性插入数据量很大,那么可以使用 select into 代替 create table。如果数据量不大,为了缓和系统表的资源,应先create table,然后insert。
如果使用到了临时表,在存储过程的最后务必将所有的临时表显式删除,先 truncate table ,然后 drop table
尽量避免使用游标,如果游标操作的数据超过1万行,那么就应该考虑改写。
尽量避免大事务操作,提高系统并发能力
尽量避免向客户端返回大数据量,若数据量过大,应该考虑相应需求是否合理。

   当只要一行数据时使用 LIMIT 1,当你查询表的有些时候,你已经知道结果只会有一条结果,在这种情况下,加上 LIMIT 1 可以增加性能。这样一样,MySQL 数据库引擎会在找到一条数据    后停止搜索,而不是继续往后查少下一条符合记录的数据

 选择正确的存储引擎,在 MySQL 中有两个存储引擎 MyISAM 和 InnoDB,每个引擎都有利有弊。
 MyISAM 适合于一些需要大量查询的应用,但其对于有大量写操作并不是很好。甚至你只是需要 update 一个字段,整个表都会被锁起来,而别的进程,就算是读进程都无法操作直到读操作  完成。另外,MyISAM 对于 SELECT COUNT(*) 这类的计算是超快无比的。
 InnoDB 的趋势会是一个非常复杂的存储引擎,对于一些小的应用,它会比 MyISAM 还慢。他是它支持“行锁” ,于是在写操作比较多的时候,会更优秀。并且,他还支持更多的高级应用,比  如:事务。

点赞

Leave a Reply

Your email address will not be published. Required fields are marked *