六、SQL优化

六、SQL优化

    1、MySQL执行计划（Explain性能分析）

        (1)、Explain命令在解决数据库性能上是第一推荐使用命令，大部分的性能问题可以通过此命令来简单的解决，Explain可以用来查看SQL语句的执行效 果，可以帮助选择更好的索引和优化查询语句，写出更好的优化语句。

        (2)、Explain语法：explain select … from … [where …]        例如：explain select * from cpoe_advice_fees;

        (3)、属性释义：

            ①、id：这是SELECT的查询序列号

            ②、select_type：select_type就是select的类型，可以有以下几种：

                a、SIMPLE：简单SELECT(不使用UNION或子查询等)

                b、PRIMARY：最外面的SELECT

                c、UNION：UNION中的第二个或后面的SELECT语句

                d、DEPENDENT UNION：UNION中的第二个或后面的SELECT语句，取决于外面的查询

                e、UNION RESULT：UNION的结果。

                f、SUBQUERY：子查询中的第一个SELECT

                g、DEPENDENT SUBQUERY：子查询中的第一个SELECT，取决于外面的查询

                h、DERIVED：导出表的SELECT(FROM子句的子查询)

            ③、table：显示这一行的数据是关于哪张表的

            ④、type：这列最重要，显示了连接使用了哪种类别,有无使用索引，是使用Explain命令分析性能瓶颈的关键项之一。

                结果值从好到坏依次是：

                system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

                一般来说，得保证查询至少达到range级别，最好能达到ref，否则就可能会出现性能问题。

                a、system：系统表，少量数据，往往不需要进行磁盘IO

                b、const：常量连接

                c、eq_ref：主键索引（primary key）或者非空唯一索引（unique not null）等值扫描

                d、ref：非主键非唯一索引等值扫描

                e、range：范围扫描

                f、index：索引树扫描

                g、all：全表扫描（full table scan）

            ⑤、possible_keys：列指出MySQL能使用哪个索引在该表中找到行

            ⑥、key：显示MySQL实际决定使用的键（索引）。如果没有选择索引，键是NULL

            ⑦、key_len：显示MySQL决定使用的键长度。如果键是NULL，则长度为NULL。使用的索引的长度。在不损失精确性的情况下，长度越短越好

            ⑧、ref：显示使用哪个列或常数与key一起从表中选择行。

            ⑨、rows：显示MySQL认为它执行查询时必须检查的行数。

            🔟、Extra：包含MySQL解决查询的详细信息，也是关键参考项之一。

                a、Distinct：一旦MYSQL找到了与行相联合匹配的行，就不再搜索了

                b、Not exists：MYSQL优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了

                c、Range checked for each

                d、Record（index map:#）：没有找到理想的索引，因此对于从前面表中来的每一个行组合，MYSQL检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一

                e、Using filesort：看到这个的时候，查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键                                值和匹配条件的全部行的行指针来 排序全部行

                f、Using index：列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表 的全部的请求列都是同一个索引的部分的时候

                g、Using temporary：看到这个的时候，查询需要优化了。这里，MYSQL需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上

                h、Using where：使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型ALL或index，这就会发生，或者是查询有问题

    2、索引

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

    3、Like关键字

        (1)、通配符(%)在搜寻词首出现，无法使用索引。

            ①、where name like ‘%莫%’：无法使用索引

            ②、where name like ‘%莫’：无法使用索引

        (2)、通配符(%)在搜寻词尾出现，可以使用索引：where name like ‘莫%’

    4、Null关键字

        User表：id、code、name  索引是code字段

        (1)、使用is not null只有完全返回索引字段时才会使用索引，否则将导致引擎放弃使用索引而进行全表扫描，使用is null均使用索引。

            如：select code from t where code is not null;      使用索引

                   select code from t where code is not null and name = ‘A';      不使用索引

                   select code, name from t where code is not null;      不使用索引

        (2)、可以在num上设置默认值0，确保表中num列没有null值，利用默认值查询。    

            如：select id from t where num=0;

    5、Not运算（ <> 和 != ）

        (1)、应尽量避免使用not运算，not运算无法使用索引。

            如：select id from t where num != 1;

        (2)、可以修改<、<=、=、>、>=、between。

            如：select id from t where num < 1 or num >1;

    6、Exists代替In

        (1)、not in是最低效的，因为要对子查询的表进行全表扫描。可以考虑使用外链接或not exists。

        (2)、Exists和In的区别

            ①、In是以子查询表为驱动表，先访问子查询，将内表和外表做一个笛卡尔积，然后按照条件进行筛选。如果子查询得出的结果集记录较少，主查询中的结果集记录较大且又有索引时应该用in。

            ②、Exists是以外层表为驱动表，先访问外层表，Exists是对外表作loop循环，每次loop循环再对内表进行查询。如果子查询得出的结果集记录较多又有索引时，主查询中的结果集记录较小时应该用exists。

            ③、In和Exists可以使用索引。

    7、Union代替Or

        文章里说where里面尽量避免用Or，否则放弃使用索引，进行全表查询，但是用explain实际分析之后可以用索引，疑问？？？？？？？？？？？

    8、Union和Union all

        union具有去重的操作，增加了计算时间。union all不需要去重，但会包含相同记录。同样功能下，首选union all操作。

    9、Where与Having

        where和having是有过滤行为的，过滤行为越能提前完成就越可以减少传递给下一个阶段的数据量，因此如果在having中的过滤行为能够在where中完成，则应该优先考虑where来实现。 

    10、其他

        (1)、应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。

        (2)、应尽量避免在where子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。

            如：select id from t where num/2=100    

            应改为：select id from t where num=100*2