sql优化和注意事项

一、SQL的优化和注意事项

现在我们假设我们只有一台MySQL服务器，所有的select/update/insert/delete操作都是在这上面进行的，我们同时有三台Web服务器，通过DNS轮巡来访问，那么我们如何进行我们应用程序和SQL的优化。

1. Where条件
在查询中，WHERE条件也是一个比较重要的因素，尽量少并且是合理的where条件是很重要的，在写每一个where条件的时候都要仔细考虑，尽量在多个条件的时候，把会提取尽量少数据量的条件放在前面，这样就会减少后一个where条件的查询时间。
有时候一些where条件会导致索引无效，当使用了Mysql函数的时候，索引将无效，比如：select * from tbl1 where left(name, 4) = ‘hylr’，那么这时候索引无效，还有就是使用LIKE进行搜索匹配的时候，这样的语句索引是无效的：select * from tbl1 where name like ‘%xxx%’，但是这样索引是有效的：select * from tbl1 where name like ‘xxx%’，所以谨慎的写你的SQL是很重要的。

2. 关联查询和子查询
数据库一个很重要的特点是关联查询，LEFT JOIN 和全关联，特别是多个表进行关联，因为每个关联表查询的时候，进行扫描的时候都是一个笛卡尔乘积的数量级，扫描数量很大，如果确实是需要进行关联操作，请给where或者on的条件进行索引。
关联操作也是可能交给应用去操作的，看数据量的大小，如果数据量不是非常大，比如10万条以下，那么就可以交给程序去处理，程序分别提取左右两个表的数据，然后进行循环的扫描处理，返回结果，这个过程同样非常耗费Web服务器的资源，那么就需要取决于你愿意把压力放在Web服务器上或者数据库服务器上了。
子查询是在mysql5中支持的功能，比如：select * from tbl1 where id in(select id from tbl1)，那样效率是非常非常低，要尽量避免使用子查询，要是我，绝对不用，呵呵。

3. 一些耗费时间和资源的操作
SQL语句中一些浪费的操作，比如 DISTINCT、COUNT、GROUP BY、各种MySQL函数。这些操作都是比较耗资源的，我想应用最多的是count字句吧，如果使用count，尽量不要count（*），最好 count一个字段，比如count（id），或者count(1)，（据totododo测 试效率其实是一样的），同样能够起到统计的作用。如果不是十分必要，尽量不要使用distinct操作，就是提取唯一值，你完全可以把这个操作交给脚本程 序去执行提取唯一值，减少MySQL的负担。group by 操作也是，确实需要分组的话，请谨慎的操作，如果是小批量的数据，可以考虑交给脚本程序去做。
至于MySQL的函数，估计很多常用，比如有人喜欢把截取字符串也交给MySQL去操作，或者时间转换操作，使用比较多的函数像 SUBSTR(), CONCAT(), DATE_FORMAT(), TO_DAYS(), MAX(), MIN(), MD5() 等等，这些操作完全可以交给脚本程序去做，减轻MySQL的负担。

4. 合理的建立索引
索引的提升速度的一个非常重要的手段，索引在对一些经常进行select操作，并且值比较唯一的字段是相当有效的，比如主键的id字段，唯一的名字name字段等等。
但是索引对于唯一值比较少的字段，比如性别gender字段，寥寥无几的类别字段等，意义不大，因为性别是50%的几率，索引几乎没有意义。对于update/delete/insert非常频繁的表，建立索引要慎重考虑，因为这些频繁的操作同样对于索引的维护工作量也是很大的，最后反而得不偿失，这个需要自己仔细考虑。索引同样不是越多越好，适当的索引会起到很关键的作用，不适当的索引，反而减低效率维护，增加维护索引的负担。

5. 监控sql执行效率
在select语句前面使用EXPLAIN字句能够查看当前这个select字句的执行情况，包括使用了什么操作、返回多少几率、对索引的使用情况如何等等，能够有效分析SQL语句的执行效率和合理程度。
另外使用MySQL中本身的慢查询日志：slow-log，同样能够记录查询中花费时间比较多的SQL语句，好对相应的语句进行优化和改写。
另外在MySQL终端下，使用show processlist命令能够有效的查看当前MySQL在进行的线程，包括线程的状态，是否锁表等等，可以实时的查看SQL执行情况，同时对一些锁表操作进行优化。