Mysql优化器

       Mysql在执行时，并不一定就会按照我们写的顺序执行，同时也不需要我们人为的从左到右的安排where后面的条件，mysql优化器会重写sql，如何才能看到mysql优化器重写后的sql呢？此时需要参考explain extended和show warnings这两个命令啦。

explain extended sql语句，然后show warnings查看。explain extended会输出sql的执行计划，查询记录的方式(全表扫描、全索引扫描、索引范围扫描等)、是否用上索引。show warnings会看到优化器重写后的sql。例如：

mysql> EXPLAIN extended SELECT
	IFNULL(SUM(min_investment_amt),0.00)
FROM
	user_coupon uc,
	event_package ep
WHERE
	uc.pid = ep.pid
AND ep.show_channel = '1'
AND uc.`status` = 0
AND uc.valid_to <= CURDATE()
AND uc.uid = '75B87DA8BBF3448BBA431ACC511EEAF0';
+----+-------------+-------+------+-------------------------+---------+---------+----------------------+------+----------+------------------------------------+
| id | select_type | table | type | possible_keys           | key     | key_len | ref                  | rows | filtered | Extra                              |
+----+-------------+-------+------+-------------------------+---------+---------+----------------------+------+----------+------------------------------------+
|  1 | SIMPLE      | uc    | ref  | uid,pid,status,valid_to | uid     | 194     | const                |  294 |      100 | Using index condition; Using where |
|  1 | SIMPLE      | ep    | ref  | idx_pid                 | idx_pid | 98      | trc_coupon_db.uc.pid |    1 |      100 | Using where                        |
+----+-------------+-------+------+-------------------------+---------+---------+----------------------+------+----------+------------------------------------+
2 rows in set

mysql> show warnings;
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select ifnull(sum(`trc_coupon_db`.`uc`.`min_investment_amt`),0.00) AS `IFNULL(SUM(min_investment_amt),0.00)` from `trc_coupon_db`.`user_coupon` `uc` join `trc_coupon_db`.`event_package` `ep` where ((`trc_coupon_db`.`uc`.`status` = 0) and (`trc_coupon_db`.`ep`.`pid` = `trc_coupon_db`.`uc`.`pid`) and (`trc_coupon_db`.`ep`.`show_channel` = '1') and (`trc_coupon_db`.`uc`.`valid_to` <= <cache>(curdate())) and (`trc_coupon_db`.`uc`.`uid` = '75B87DA8BBF3448BBA431ACC511EEAF0')) |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

mysql> 

SELECT
    IFNULL(
        SUM(min_investment_amt),
        0.00
    )
FROM
    user_coupon uc,
    event_package ep
WHERE
    uc.pid = ep.pid
AND ep.show_channel = '1'
AND uc.`status` = 0
AND uc.valid_to <= CURDATE()
AND uc.uid = '75B87DA8BBF3448BBA431ACC511EEAF0';

mysql优化器会将此语句优化成内连接

SELECT
    ifnull(
        sum(
            `trc_coupon_db`.`uc`.`min_investment_amt`
        ),
        0.00
    ) AS `IFNULL(SUM(min_investment_amt),0.00)`
FROM
    `trc_coupon_db`.`user_coupon` `uc`
JOIN `trc_coupon_db`.`event_package` `ep`
WHERE
    (
        (
            `trc_coupon_db`.`uc`.`status` = 0
        )
        AND (
            `trc_coupon_db`.`ep`.`pid` = `trc_coupon_db`.`uc`.`pid`
        )
        AND (
            `trc_coupon_db`.`ep`.`show_channel` = '1'
        )
        AND (
            `trc_coupon_db`.`uc`.`valid_to` <= < CACHE > (curdate())
        )
        AND (
            `trc_coupon_db`.`uc`.`uid` = '75B87DA8BBF3448BBA431ACC511EEAF0'
        )
    )