SQL优化

1、SQL注入漏洞攻防
例子：
        SELECT (FPassword='123456') AS PwdCorrect from t_user where fUser='admin'
        采用字符串拼接的方式根据用户名和密码拼接SQL语句。看起来没问题，但是如果用户用户名输入框随意填写，密码填写'1 or 1=1'，那样的结算值永远是true。这样的漏洞叫做SQL注入漏洞。
对付SQL注入漏洞的两种方法：
    1、过滤敏感词
            一般是OR、AND、SELECT、DELETE之类的字符串。所以在拼接SQL之前检查用户提交的文本中是否含有这些敏感字符，如果有则终止操作
            两个缺陷：1、给正常用户造成麻烦  2、逻辑难以严槿
    2、使用参数化SQL
            preparedStatement

2、SQL调优
    在使用DBMS时经常对系统的系统有非常高的要求：不能占用过多的系统内存和CPU资源，要尽可能的完成数据库的工作，要尽可能的高的系统吞吐量。决定DBMS的性能的因素有两个：硬件和软件。使
用频率高的CPU、使用多处理器、加大内存容量、增加Cache、提高网络速度等都是非常有效的硬件调优方式。硬件调优是有限的，所以也SQL优化
    SQL调优的基本原则：
        “二八原则”是一种普遍的真理，就是20%的代码资源占用了80%的总资源消耗。因此在SQL调优的时候应该将主要精力放到20%最消耗资源的SQL语句中。
    索引
        索引是数据库调优的最根本优化方法，很多优化方法都是围绕索引展开的，可以说索引是一切优化手法的内功，而其他的优化手法都是索引衍化出来的招式而已
        根据索引的顺序与数据表的物理顺序是否相同，可以把索引分为两种类型：聚簇索引，数据表的物理顺序与索引顺序相同；非聚簇索引，数据表的物理顺序与索引顺序不同。类比字典当中的拼音目
录就是聚簇索引，部首就是非聚簇索引。一个表的聚簇索引只能有一个，因为表的数据的物理顺序只能有一个，而非聚簇索引则可以创建多个
        索引需要占据一定的存储空间，而且索引需要维护成本，会降低数据插入、更新和删除的速度，所以应该只创建必要的索引，一般在检索时用的字段中创建索引。索引还会造成存储碎片问题，因为
索引是采用B树数据存储的，所以对应的索引项不会被删除
    全表扫描和索引查找
        一般地，系统访问数据库中的数据，可以使用两种方法：全表扫描和索引查找。DNMS中都有查询优化器，它会根据分布的统计信息生成该查询语句的优化执行规划，以提高访问数据的效率为目标，
确定是使用全表扫描还是使用索引查找。注意并不是表中存在索引，在进行检索的时候就会使用索引查找，如果使用不当，检索的过程仍然会采用全表扫描，这样索引就起不到作用了。
    优化手法：
            1、创建必要的索引
            2、使用预编译查询
                    不仅可以避免SQL注入攻击，最重要的而是数据库会对这些参数化SQL执行预编译。这样第一次执行的时候DBMS会为这个SQL语句进行查询优化并且执行预编译，以后再执行这个SQL的时候就直
接使用预编译的结果，这样可以大大提高执行的速度
            3、调整WHERE子句中的连接顺序
                    DBMS一般采用自上而下的顺序解析WHERE子句，根据这个原理，表连接最好写在其他WHERE条件之前，这样可以过滤掉最大量记录
                    如：
                        SELECT * FROM T_Person WHERE FSalary > 50000 AND FPosition = 'MANAGER' AND 25 < (SELECT COUNT(*) FROM T_Manager WHERE FManagerId = 2)
                  改成这样就比较好：
                        SELECT * FROM T_Person WHERE 25 < (SELECT COUNT(*) FROM T_Manager WHERE FManagerId = 2) AND FSalary > 5000 AND FPosition = 'MANAGER'
            4、SELECT语句中避免使用*
                    ”SELECT *“ 比较简单，但是除非确实需要检索所有的列，否则将会检索出不需要的列，这回增加网络的负载和服务器的资源消耗。几时确实需要检索所有列，也不要使用SELECT * ,因为这是
一个非常低效的方法，DBMS在解析的过程中，会将*依次转换成所有的列名，这意味着将耗费更多的时间
            5、尽量将多条SQL语句压缩到一句SQL中
                每次执行SQL的时候都要简历网络连接，进行权限校验、进行SQL语句的查询SQL优化，发送执行结果，都是非常耗时的，因此应该尽量避免过多的执行SQL语句，能够压缩到一句SQL执行语句就不
要用多条来执行
        6、用Where子句替换Having子句
                要避免使用Having，因为Having只会在检索出所有记录之后才对结果进行过滤。如果能通过WHERE子句限制记录的数目，那就能减少这方面的开销。HAVING中的条件一般用于聚合函数的过滤，除
此之外，应该将条件写在WHERE子句中
        7、使用表的别名
                当在SQL语句中有连接多个表时，清使用表的别名并且把别名前缀置于每个列名上。这样就可以减少解析的时间并减少那些由列名歧义引起的语法错误
        8、用EXISTS替代IN
                在查询中，为了满足一个条件，往往需要对另一个表进行连接，在这种情况下，使用EXISTS而不是使用IN，通常将提高查询的效率，因为IN子句将执行一个子查询内部的排序和合并
        9、用表连接替换EXISTS
                通常来说，表连接的方式比EXISTS更有效率，因此如果可能的话尽量使用表连接替换EXISTS
        10、避免在索引列上使用计算
                在WHERE子句中，如果索引列是计算或者函数的一部分，DBMS的优化器将不会使用索引而使用全表扫描
        11、用UNION ALL替换UNION
                UNION ALL与UNION的区别是UNION 会去重复且排序，如果数据没有重复，且不要求排序则直接使用UNION All会提高效率
        12、避免隐式类型转换造成的全表扫描
                如：
                    T_Person表的字符串类型字段FLevel为人员的级别，在FLevel字段上建有索引。我们执行下面的SQL用于检索所有级别等于10的员工：
                        SELECT ID FId, FName FROM T_Person WHERE FLevel = 10
                    在这个SQL语句中，将字符串类型字段FLevel与数值10进行比较，由于在大部分数据库的隐式转换类型中数值类型的优先级高于字符串类型，因此DBMS会对FLevel字段进行隐式类型转
换，相当于执行下面的SQL语句：
                        SELECT FId,FName FROM T_Person WHERE TO_INT(FLevel) = 10
                    由于在索引字段上进行了计算，所以造成了索引失效而使用全表扫描。因此应将SQL语句做如下修改：                        
                        SELECT FId,FName FROM T_Person WHERE FLevel = '10'
        13、防止检索范围过宽
                如果DBMS优化器认为检索范围过宽，那么它将放弃索引查找而是使用全表扫描。下面是可能造成索引范围过宽的情况：
                    1、使用 IS NOT NULL 或者不等于判断，可能造成优化器假设匹配的记录数太多
                    2、使用LIKE的时候，"a%"将会使用索引，而"a%c"和"%c"将会使用全表扫描。因为"a%c"和"%c"不能被有效地评估匹配的数量

除了SQL优化，提升效率可以表结构优化，需求曲线解决，如使用缓存等

参考书籍：程序员的SQL金典