数据库原理与MySQL应用（微课版）.ppt

9.2优化查询数据库原理与应用*9.2.1分析查询语句的执行计划执行计划是SQL语句调优的一个重要依据。查看SQL语句的查询执行计划（QEP）使用EXPLAIN语句和DESCRIBE语句，通过此语句的输出结果能够了解到MySQL优化器是如何执行SQL语句的，提供重要的信息来帮助做出调优决策。MySQL5.6.3及之后的版本对SELECT、DELETE、INSERT和UPDATE语句都可以生成执行计划。EXPLAINSELECT语句;【例9-2】使用EXPLAIN语句分析简单的查询语句。1.EXPLAIN语句USEfruitsales;EXPLAINSELECT*FROMsuppliersWHEREs_idIN(101,102,103);【说明】（1）id：SELECT识别符，是SELECT查询序列号。（2）select_type：表示SELECT语句的类型，它的常用取值：①SIMPLE表示简单查询，其中不包括连接查询和子查询。②PRIMARY表示主查询，或者是最外层的查询语句。③UNION表示连接查询的第二个或后面的查询语句。④UNIONRESULT表示一系列定义在UNION语句中表的返回结果，其对应的table列的值为unionm,n，表示匹配的id行是这个集合的一部分。【例9-3】使用EXPLAIN语句分析带有UNION的查询语句。EXPLAINSELECT*FROMsuppliersWHEREs_call=11111UNIONSELECT*FROMsuppliersWHEREs_call=22222;（3）table：表示查询的表。（4）partitions：表示分区表的分区情况，非分区表该列值为NULL（5）type：表示MySQL在表中找到所需行的方式，下面按照性能由最差到最好的顺序给出常见类型。①ALL：全表扫描，MySQL将进行全表扫描。②index：索引全扫描，MySQL将遍历整个索引来查询匹配的行，index与ALL的区别为index类型只遍历索引树。③range：只检索给定范围的行，使用一个索引来选择行。④index_subquery：表示子查询中使用了普通索引。⑤unique_subquery：表示子查询中使用了UNIQUE或者PRIMARYKEY。⑥ref：表示多表查询时，后面的表使用了普通索引。⑦eq_ref：表示多表连接时，后面的表使用了UNION或者PRIMARYKEY。⑧const：表示表中有多条记录，但只从表中查询一条记录。⑨system：该表是仅有一行的系统表。（6）possible_keys：表示查询中可能使用的索引。（7）key：表示查询使用到的索引。（8）key_len：表示索引字段的长度。（9）ref：表示使用哪个列或常数与索引一起来查询记录。（10）rows：表示查询的行数。（11）filtered：表示针对表里符合条件的记录数的百分比。（12）Extra：表示MySQL在处理查询时的详细信息。【例9-4】使用EXPLAIN语句分析带有子查询的查询语句。CREATEUNIQUEINDEXname_idxONsuppliers(s_name);EXPLAINSELECT*FROMsuppliersWHEREs_id=(SELECTs_idFROMsuppliersWHEREs_name=ACME);DESCRIBE|DESCSELECT语句;【例9-5】使用DESCIBE语句分析一个查询语句。2.DESCRIBE语句DESCRIBESELECT*FROMfruitsWHEREf_name=apple;DESCRIBE语句的使用方法与EXPLAIN语句是一样的，分析结果也是一样。9.2.2优化查询基本原则尽可能对每一条运行在数据库中的SQL语句进行EXPLAIN。尽量少使用JOIN。MySQL的优势在于简单，但是在某些方面其实也是劣势。对于复杂的多表JOIN，一方面由于优化器受限，另一方面JOIN的性能表现距离其它的关系数据库（例如Oracle）有一定的差距。尽量少排序。排序操作会消耗较多的CPU资源。尽量避免使用SELECT*查询。大多数情况下，SELECT子句中的字段的多少并不会影响到读取的数据。但是当存在ORDERBY操作时，SELECT子句中的字段多少在很大的程度上就影响到了排序效率。尽量用JOIN代替子查询。虽然JOIN的性能并不是特别好，但是和MySQL的子查询相比，它还是具有