现在我们假设我们只有一台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万条以下,那么就可以交给程序去处理(totododo提出笔误,特此修正),程序分别提取左右两个表的数据,然后进行循环的扫描处理,返回结果,这个过程同样非常耗费Web服务器的资源,那么就需要取决于你愿意把压力放在Web服务器上或者数据库服务器上了。
子查询是在mysql5中支持的功能,比如:select * from tbl1 where id in(select id from tbl1),那样效率是非常非常低,要尽量避免使用子查询,要是我,绝对不用,呵呵。
3. 一些耗费时间和资源的操作
SQL语句中一些浪费的操作,比如 DISTINCT、COUNT、GROUP BY、各种MySQL函数。这些操作都是比较耗资源的,同样能够起到统计的作用。如果不是十分必要,尽量不要使用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执行情况,同时对一些锁表操作进行优化。
转载于:https://blog.51cto.com/study86/1332225