MySQL之SQL优化

今天参加了公司内部的培训，感想wangqiang带来的技术分享。我又结合公司wiki，记录下mysql优化的一些知识。

show warnings

官方解释

SHOW WARNINGS is a diagnostic statement that displays information about the conditions (errors, warnings, and notes) resulting from executing a statement in the current session.
Warnings are generated for DML statements such as INSERT, UPDATE, and LOAD DATA INFILE as well as DDL statements such as CREATE TABLE and ALTER TABLE.

示例

Here is a simple example that shows data-conversion warnings for INSERT: 【mysql版本不同，可能有些不一样】

mysql> CREATE TABLE t1 (a TINYINT NOT NULL, b CHAR(4));
    Query OK, 0 rows affected (0.05 sec)
mysql> INSERT INTO t1 VALUES(10,'mysql'), (NULL,'test'), (300,'xyz');
    Query OK, 3 rows affected, 3 warnings (0.00 sec)Records: 3  Duplicates: 0  Warnings: 3
mysql> SHOW WARNINGS\G
*************************** 1. row ***************************  
Level: Warning   Code: 1265Message: Data truncated for column 'b' at row 1
*************************** 2. row ***************************  
Level: Warning   Code: 1048Message: Column 'a' cannot be null
*************************** 3. row ***************************  
Level: Warning   Code: 1264Message: Out of range value for column 'a' at row 33 rows in set (0.00 sec)

和sql优化有什么关系？

mysql有时候会修改执行的sql，导致性能问题，show warnings可以查看真正执行的sql语句。

使用【explain或desc命令 sql语句】，我们可以看到某SQL语句的执行计划,desc命令并不是通用，所以我们使用explain sql语句 更多一些。

　　执行Mysql的explain extended的输出会比单纯的explain多一列filtered(MySQL 5.7缺省就会输出filtered)，它指返回结果的行占需要读到的行(rows列的值)的百分比。按说filtered是个非常有用的值，因为对于join操作，前一个表的结果集大小直接影响了循环的次数。
　　filtered值只对index和all的扫描有效（这可以理解，其它场合，通常rows值就等于估算的结果集大小。）

使用show table status;可以提供每个表的大量信息，本语句也显示视图信息。
SHOW TABLE STATUS [from db_name] //所指数据库中所有表的信息
SHOW TABLE STATUS [from db_name] LIKE ‘uc%’ //表名以uc开头的表的信息

另外，补充一小点，我们可能看SQL语句的时候，通常看到很多会在后边加上一个参数\G，如select * from user \G; 该参数会自动在命令行下进行格式化输出，便于我们阅读。自行尝试。

示例

mysql> explain EXTENDED SELECT  * FROM  order t1 where t1.order_id in (select order_id from order)
结果：
| id | select_type | table            | type   | possible_keys          | key     | key_len | ref             | rows    | filtered | Extra       |
+----+-------------+------------------+--------+------------------------+---------+---------+-----------------+---------+----------+-------------
|  1 | SIMPLE      | t1               | ALL    | PRIMARY,order_id_index | NULL    | NULL    | NULL            | 1093303 |   100.00 | NULL        |
+----+-------------+------------------+--------+------------------------+---------+---------+-----------------+---------+----------+-------------
|  1 | SIMPLE      | order | eq_ref | PRIMARY,order_id_index | PRIMARY | 4       | ord.t1.order_id |       1 |   100.00 | Using index |
+----+-------------+------------------+--------+------------------------+---------+---------+-----------------+---------+----------+-------------
2 rows in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row **************************
Level: Note
Code: 1003
Message: /* select#1 */ select `ord`.`t1`.`order_id` AS `order_id`,`ord`.`t1`.`order_num` AS `order_num`,`ord`.`t1`.`pay_status` AS `pay_status`,`ord`.`t1`.`cur
rent_status` AS `current_status`,`ord`.`t1`.`source` AS `source`,`ord`.`t1`.`member_id` AS `member_id`,`ord`.`t1`.`is_sub` AS `is_sub`,`ord`.`t1`.`parent_order_
id` AS `parent_order_id`,`ord`.`t1`.`process_id` AS `process_id`,`ord`.`t1`.`process_status` AS `process_status`,`ord`.`t1`.`priority` AS `priority`,`ord`.`t1`.
`add_uid` AS `add_uid`,`ord`.`t1`.`add_time` AS `add_time`,`ord`.`t1`.`update_uid` AS `update_uid`,`ord`.`t1`.`update_time` AS `update_time`,`ord`.`t1`.`del_fla
g` AS `del_flag`,`ord`.`t1`.`sign_status` AS `sign_status`,`ord`.`t1`.`valid_status` AS `valid_status`,`ord`.`t1`.`release_time` AS `release_time`,`ord`.`t1`.`p
ay_amount` AS `pay_amount`,`ord`.`t1`.`bind_orderId` AS `bind_orderId`,`ord`.`t1`.`follow_up_status` AS `follow_up_status`,`ord`.`t1`.`follow_up_time` AS `follo
w_up_time`,`ord`.`t1`.`end_status` AS `end_status`,`ord`.`t1`.`has_confirmed` AS `has_confirmed`,`ord`.`t1`.`visa_cfm_result` AS `visa_cfm_result`,`ord`.`t1`.`d
istribution_tag` AS `distribution_tag` from `ord`.`phoenix_ru_order` join `ord`.`phoenix_ru_order` `t1` where (`ord`.`phoenix_ru_order`.`order_id` = `ord`.`t1`.
`order_id`)
1 row in set (0.00 sec)

可以看到mysql 自动将in优化成了表关联

相关子查询

索引

执行计划