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 还慢。他是它支持“行锁” ，于是在写操作比较多的时候，会更优秀。并且，他还支持更多的高级应用，比 　如：事务。