sql优化的部分方法

1.对查询进行优化，一定要避免全表扫描查询，首先应在where及order by 涉及的列上建立索引.

2.应尽量避免在where子句中对字段进行null值判断, 否则将导致引擎放弃使用索引进行全表扫描, 比如: select id from user where age is null
解决方案：把age字段设置默认值为0;
然后可以这样查询:select id from where age=0

3.尽量避免在where字句中使用！=或者<>操作符,否则将引擎放弃使用索引进行全表扫描.

4.避免在wher字句中使用or 来连接条件,否则将导致引擎放弃使用索引而进行全表扫描, 比如:select id from user where age = 10 or age=20
优化:select id from user where age = 10 union all select id from user where age = 20

5.in 和 not in 也都要慎用，否则会导致全表扫描,
如:select id from user where age in(1,2,3)
对于连续的数值，能用between 就不要用 in了
如:select id from user where age between 1 and 3

6.下面的模糊查询也将会导致全表扫描
如:select id from user where name like ‘%abc%’

7.避免在where字句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描
如:select id from user where age/2=100
应改为:
如:select id from user where age=100*2

8.避免在where字句对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描
如:select id from user where substring(name,1,3)=‘123’ name以ABC开头的id
应该为:
如:select id from user where name like ‘abc%’

9.不要在where子句中的"=" 左边进行函数 算术运算或其他表达式运算，否则系统将无法正确的使用索引

10.在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且尽可能让字段顺序与索引顺序一致。

11.不要写一些没有意义的查询，如：需要生成一个空表结构:
select id1,id2 into #t from t where 1=0

12.很多时候用exists代替in 是一个好的选择
如:select id from user where age in (select id from order)
优化为
如:select id from user where exists(select 1 from order where user_id=user.id)

13.并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一张表中有sex,male,female字段几乎各一半，那么即使sex上建了索引也对查询效率起不了作用。

14.索引并不是越多越好，索引固然可以提高相应的select的效率，但是同时也降低了insert及update的效率，因为insert或update时可能会重建索引，所以怎么样建索引要慎重考虑，是具体情况而定。一个表的索引最好不好超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。

15.尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符，这会降低查询和连接的性能，并会增加存储开销。

16.尽可能用varchar代替char，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高一些。

17.在任何地方都不要用select * from user ,用具体的字段列表代替 * 不要返回用不到的字段。

18.避免频繁创建和删除临时表，以减少系统资源的消耗,
临时表并不是不可使用，适当地使用它们可以使某些列程更有效，列如：当需要重复引用大型表或常用表中某个数据集时。但是对于一次性事件，最好使用导出表。

19.在新建临时表时，如果一次性插入数据量很大，那么可以使用select into 代替 create table ,避免造成大量log,以提高速度，如果数量不大，为了缓和系统表的资源，应先create table ,然后insert.

20.如果使用到临时表，在存储过程的最后务必将所有的临时表显示删除，先truncate table ，然后 drop table ， 这样可以避免系统表的较长时间锁定。

21.尽量避免大事务操作，提高系统并发能力。

22.尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。

23.给字段添加索引,如当查询用户的name的时候 需要经常使用这个关键字段以及如果是字符串类型，那么就可以给这个字段加上索引,但是索引多了也并不好，当做查找当然效率变高，那么在做修改删除添加的时候，就会效率变慢。
普通索引 添加index: alter table ‘表名’ add index 索引名 （字段名字）
主键索引 添加primary key: alter table ‘表名’ add primary key (字段名字)
唯一索引 添加unique: alter table ‘表名’ add unique (字段名字)
全文索引 添加fulltext: alter table ‘表名’ add fulltext (字段名字)
连动索引: alter table ‘表名’ add index 索引名 （字段名,字段名,字段名）

数据库引擎:
ISAM：是一个定义明确经历时间考验的数据表格管理方法，它在设计就是考虑到数据库被查询的次数要远远大于更新的次数。所以，ISAM执行读取的操作很快，而且不占用大量的内存和存储资源。ISAM的两个不足支持是在于 它不支持事物的操作，也不能够容错。如果你的硬盘坏了，那么你的数据文件就无法恢复了。如果你在关键的任务使用到ISAM引擎你可能要时常保存数据了。

MyISAM:它是对ISAM的一个扩展的引擎，除了提供一些ISAM里没有的索引和字段管理的大量功能，MyISAM还使用了一种表格锁定的机制，来优化多个并发的读写操作，其大家就是你想要经常运行OPTIMIZE TABLE 命令，来恢复被更新机制浪费的空间。MyISAM还有一些有用的扩展，比如用来修复数据库文件的MyISAM CHK 工具和用来恢复浪费空间的MyISAMPACK工具。MyISAM强调了快速读取操作，这就是为什么在web开发中受到大家如此青睐的原因:比如在WEB开发中你进行了大量的数据读取操作。所以在大多数虚拟机主机提供商和互联网平台提供商只运行使用MyISAM格式。MyISAM格式的最主要的缺陷是在表格损坏后不能恢复数据。

InnoDB:它是造就Mysql灵活性的技术的直接产品，这项技术就是Mysql+API.在使用Mysql的时候，你面对的挑战都是来源ISAM和MyISAM因为它们2个不支持事物处理 也不支持外来键，尽管要比ISAM和MyISAM要慢，但是InnoDB支持了事物和外来键的支持，所以最终还是看设计的需要选择哪一种数据库引擎了。

MEMORY:它是Mysql数据库的一种特殊引擎。它使用存储在内存中的内容来创建表，而且数据全部都放在内存中。这些特性与前面两个很不同，每个基于MEMORY存储引擎的表实际对应一个磁盘文件。改文件的文件名与表名相同，类型为frm类型。改文件中只存储表的结果。而其数据文件，都是存储在内存中,这样有利于数据的快速处理，提高了整个表的效率。值得注意的是，服务器需要有足够的内存来维持MEMORY存储引擎的表的使用。如果不需要了，就可以释放内存，甚至删除不需要的表。MEMORY默认使用的是哈希索引，速度表使用B型树索引快。当如果你想要使用B型索引，可以在创建索引的时候指定。注意，MEMORY用的很少，因为它是把数据存到内存中，如果内存出现异常就会影响数据。如果重启或者关机，所有数据都会消失，因此，基于MEMORY的表的生命周期很短，一般都是一次性的。

InnoDB:是支持事物，支持外键，支持崩溃修复能力和并发控制。如果需要对事物的完整性要求比较高（比如 银行），要求实现并发控制（比如售票），那么选择InnoDB的可靠性最佳，如果需要频繁的更新 删除操作的数据库，也可以选择InnoDB,因为支持事物提交和回滚
MyISAM:插入数据块，空间和内存使用比较低。如果表主要用于插入新记录和读出记录，那么选择MyISAM的性能处理高效，如果应用完整性，并发要求比较低，也可以使用.
MEMORY:所有的数据都保存在内存中，数据的处理速度是最快的，但是安全性不高。如果需要速度很快，对数据的安全性要求不是那么高，可以考虑使用一下。

注意:同一个数据库也可以使用多种的存储引擎，如果一个表要求比较高事物的处理，那么可以指定这个表的引擎为InnoDB。
这个数据库中可以将查询比较高的表选择MyISAM存储。
如果该数据库需要一个用于查询的临时表，可以选择用MEMORY存储引擎！！

MySQL默认的事务处理级别是’REPEATABLE-READ’,也就是可重复读
1.查看当前会话隔离级别
select @@tx_isolation;
2.查看系统当前隔离级别
select @@global.tx_isolation;
3.设置当前会话隔离级别
set session transaction isolatin level repeatable read;
4.设置系统当前隔离级别
set global transaction isolation level repeatable read;