（DBA之路【七】）常用的sql优化技巧

cl_303303303

　　SQL优化的一般步骤
　　① 通过 show status 命令了解各种SQL的执行频率
　　② 定位执行效率较低的SQL语句 （重点select）
　　③ 通过 explain 分析低效率的SQL语句的执行情况
　　④ 确定问题并采取相应的优化措施
　　
　　
　　sql语句优化
　　（原文链接http://blog.163.com/chenxuezhen_1/blog/static/9167375120114159370405/）
　　1.创建必要的索引
　　在经常检索的字段上创建索引，创建索引会给检索带来巨大的性能提升，因此在发现检索速度过慢的时候应该首先想到就是创建索引。
　　2.使用预编译查询
　　程序通常根据用户输入动态执行SQL语句，这个时候应该尽量使用参数化SQL，这样不仅可以避免SQL注入漏洞***，最重要的事数据库会对这些参数化SQL执行预编译。这样，第一次执行的时候DBMS会为这个SQL语句进行查询优化并且执行预编译，以后再执行这个SQL的时候就直接使用预编译的结果，这样可以大大的提高执行的速度。
　　使用预编译查询有两种方式

• 　　使用perl的DBI
  
• 　　prepare 设定一个事务名字 from +‘sql 语句’；
  　　然后执行 execute 事务名字；
  　　最后deallocate prepare 事务名字
  

　　3.调整Where子句中的连接顺序
　　DBMS一般采用自下而上的顺序解析Where子句，根据这个原理，表连接最好写在其他的Where条件之前，这样就可以过滤掉最大的数据记录。
　　如下面的SQL性能较差：
　　select * from T_Person
　　Where FSalary > 50000
　　AND FPosition = 'Manager'

　　AND 25 < (select  count（*）from T_Manager where>　　我们将子句移到前面，这样的SQL性能比较好：
　　select * from T_Person

　　Where 25 < (select  count（*）from T_Manager where>　　AND  FSalary > 50000
　　AND FPosition = 'Manager'
　　4.Select 语句中避免使用*
　　“Select *” 比较简单，但是除非真的需要检索所有的列，否则这样将检索出不需要的列，增加网络负载和服务器的资源消耗。即使确实需要检索所有的列，也不要使用select *，因为这个是一个非常低效的方法，DBMS在解析的过程中，会将* 依次转换成所有的列名，这意味着将耗费更多的时间。
　　5.尽量将多条SQL语句压缩到一句SQL中   
　　每次执行SQL的时候都要建立网络连接，进行权限校验，进行SQL语句的查询优化，发送执行结果，这个过程是很耗时的，因此应该尽量避免过多的执行SQL语句，能够压缩到一句SQL执行的语句就不要用多条来执行
　　6.用Where子句中替换Having子句
　　要避免使用Having子句，因为Having只会在检索出所有的记录之后才对结果集进行过滤。如果能通过Where子句限制记录的数目，那就能减少这方面的开销。Having中的条件一般用于聚合函数的过滤，除此之外，应该将条件写在Where子句中。
　　7.使用表的别名
　　当在SQL语句中有连接多个表的时候，请使用表的别名并把别名的前缀置于每个列名上，这样就可以减少解析的时间并减少那些有列名歧义引起的语法错误。
　　8. 用EXISTS 替代IN
　　在查询中，为了满足一个条件，往往需要对另一个表进行连接，在这种情况下，使用EXISTS而不是使用IN，通常将提高查询效率，因为IN子句将执行一个子查询内部的排序和合并。下面的语句2就比语句1效率更高。
　　语句1：
　　select * from T_Employee
　　where FNumber > 0 and FDEPTNO in (select FNumber from T_Department where FmanagerName = 'Tom')
　　语句2：
　　select * from T_Employee where FNumber > 0and EXIESTS (select 1 from T_Department were T_Department.ID = T_Empoyee.FDEPTNO  and Fmanagement = 'Tom')
　　9.用表连接替换EXISTS
　　通常来说，表连接方式比EXISTS更有效率，下面的语句比上面的语句2 效率更高
　　select T_Employee.* from T_Employee ,T_Department d  where T_Employee.FNumber > 0 and T_Department.ID = T_Empoyee.FDEPTNO  and d.Fmanagement = 'Tom'
　　10.避免在索引列上使用计算  
　　在Where子句中，如果索引列是计算或者函数的一部分，DBMS优化器将不会使用索引而使用全表扫描
　　11.用UNION ALL替换UNION
　　当SQL语句需要UNION两个查询结果集合的时候，即使检索结果中不会有重复的记录，使用UNION这两个结果集同样会尝试合并结果，然后输出最终结果前进行排序。因此，如果检索结果中不会有重复记录的话，应该用UNION ALL替换UNION，这样效率更高。
　　12.避免隐式类型转换造成全表扫描
　　Select FID，FAGE，FNAME from T_Person where FAGE =10 （注意“FAGE为String类型的）在这个SQL语句中，将字符串类型字段FLevel与数值10 进行比较，由于大部分数据库的隐式转换类型中数值类型的优先级高于字符串类型，因此DBMS会对FAGE字段进行隐式类型转换，
　　相当于执行：
　　Select FID，FAGE，FNAME from T_Person where TO_INT（FAGE） =10  所以应该这样写：  Select FID，FAGE，FNAME from T_Person where FAGE = ’10‘
　　13.防止检索范围过宽
　　如果DBMS优化器认为检索范围过宽，那么它将放弃索引查找而使用全表扫描，下面可能造成检索范围过宽的情况：
　　（1）使用IS NOT NULL或者不等于判断，可能造成优化器假设匹配的记录数太多。
　　（2）使用LIEK的时候，"a%"将会使用索引，而"a%c" 和 "%c" 则会使用全表扫描，因此"a%c" 和 "%c" 不能被有效的评估匹配的数量。
　　其他：http://www.iyunv.net/article/39221.htm

• 　　对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
  　　2.应尽量避免在 where 子句中使用!=或操作符，否则将引擎放弃使用索引而进行全表扫描。
  　　3.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：
  
  　　select>　　可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
  
  　　select>　　4.应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：
  
  　　select>　　可以这样查询：
  
  　　select>　　union all
  
  　　select>　　5.下面的查询也将导致全表扫描：
  
  　　select>　　若要提高效率，可以考虑全文检索。
  　　6.in 和 not in 也要慎用，否则会导致全表扫描，如：
  
  　　select>　　对于连续的数值，能用 between 就不要用 in 了：
  
  　　select>　　7.如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：
  
  　　select>　　可以改为强制查询使用索引：
  
  　　select>　　8.应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：
  
  　　select>　　应改为:
  
  　　select>　　9.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：
  
  　　select>
  　　select>　　应改为:
  　　select id from t where name like 'abc%'
  
  　　select>　　10.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。
  　　11.在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。
  　　12.不要写一些没有意义的查询，如需要生成一个空表结构：
  　　select col1,col2 into #t from t where 1=0
  　　这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：
  　　create table #t(...)
  　　13.很多时候用 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)
  　　14.并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。
  　　15.索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。
  　　16.应尽可能的避免更新 clustered 索引数据列，因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。
  　　17.尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
  　　18.尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
  　　19.任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。
  　　20.尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。
  　　21.避免频繁创建和删除临时表，以减少系统表资源的消耗。
  　　22.临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。
  　　23.在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。
  　　24.如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。
  　　25.尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。
  　　26.使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。
  　　27.与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。
  　　28.在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。
  　　29.尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。
  　　30.尽量避免大事务操作，提高系统并发能力。
  

　　数据备份
　　增量备份 (原文：http://my.oschina.net/u/946060/blog/292710)
　　定义：MySQL数据库会以二进制的形式，把用户对MySQL数据库的操作，记录到文件
　　当用户希望恢复的时候，可以使用备份文件进行恢复。
　　增量备份会记录 dml语句，创建表的语句，但不会记录select语句
　　记录的是 a.操作语句本身 b.操作的时间 c.操作的位置 position
　　案例：如何进行增量备份和恢复
　　步骤：
　　1.配置my.ini 文件 或者 my.conf 启用二进制备份
　　在my.ini 中增加一句话
　　#指定备份文件放在哪个目录下
　　log-bin="/backup/mylog"
　　
　　2.重启MySQL得到文件
　　/backup/mylog.index 索引文件 有哪里增量备份文件
　　/backup/mylog.000001 存放用户对数据库操作的文件
　　3.当我们进行操作(除了select)
　　可以使用 mysql/bin/mysqlbinlog 程序来查看备份文件的内容
　　进入到 终端  shell>mysqlbinlog 备份文件路径
　　在这里 end_log_pos 526表示执行某个命令在文件中的位置，可以根据这个位置恢复相应的数据
　　set timestamp=xxxxxxx 这个表示命令执行时间
　　
　　MySQL把每一个操作的时间记录下来，同时分配了一个位置position
　　我们可以根据时间或者位置来恢复
　　a.根据时间点恢复
　　在 mylog.000001 文件开始 到 2013-05-15 14:25:00 结束
　　mysqlbinlog --stop-datetime="2013-05-15 14:25:00"
　　/backup/mylog.000001 | mysql -uroot -p
　　
　　在 mylog.000001 文件 2013-05-15 14：25：00 开始到文件结束
　　mysqlbinlog --start-datetime="2013-05-15 14:25:00"
　　/backup/mylog.000001 | mysql -uroot -p
　　
　　恢复某个时间段数据
　　mysqlbinlog --start-datetime="2013-05-15 14:24:00" --stop-datetime="2013-05-15 14:25:00"
　　/backup/mylog.000001 | mysql -uroot -p
　　b.根据位置恢复
　　在 mylog.000001 文件开始->21114
　　mysqlbinlog --stop-position="21114" /backup/mylog.000001 | mysql -uroot -p
　　在mylog.000001 文件2111开始->最后
　　mysqlbinlog --start-position="2111" /backup/mylog.000001 | mysql -uroot -p
　　在mylog.000001 文件 751->1195 之间
　　mysqlbinlog --start-position="751" --stop-position="1195" | mysql -uroot -p
　　
　　4.如何在工作中将全备份和增量备份配合使用
　　方案：每周一做一个全备份：mysqldump，
　　然后启用增量备份，把过期时间设为>=7，最好设大一点
　　如果出现数据库崩溃，可以通过时间或者位置恢复 需要去看增量日志文件