作为开发者必须掌握的mysql操作sql语句优化-showprocesslistandexplain - 数据库编程

在我们实际开发过程中，优化几乎很少贯穿整个项目开发周期，性能也是我们在开发时很少关注的，为什么？功能都做不完谁还去关心性能，能够实现功能就不错了！ -- 绝大多数加班开发者的心声！！那么我们开发完了之后怎么优化db呢。今天我们就来讨论讨论用mysql作为db的项目，在写sql操作数据库的结果怎么样，作为开发者，探讨探讨这个还是有必要的。

1、获取有问题的sql

我这里提供两种方式获取有问题的sql，A 通过log，B 通过show processlist;两种方式，当然获取有问题的sql还有更多方式（水平有限）就不过多追求所有方式。

A：log方式

首先查看是否开启：

mysql> show variables like "%slow%";
+---------------------+-----------------------------+
| Variable_name       | Value                       |
+---------------------+-----------------------------+
| log_slow_queries    | ON                          |
| slow_launch_time    | 2                           |    表示超过2秒定义为慢查询
| slow_query_log      | ON                          |    开启慢查询
| slow_query_log_file | /tmp/slow.log               |    日志写入的文件
+---------------------+-----------------------------+
4 rows in set (0.01 sec)

默认情况下是关闭的，这里已经打开，sql运行时间超过2秒的将会被记录到日志。

开启方法：mysql> set global slow_query_log=on;

mysql> set global slow_launch_time=1; 设置超过1秒执行的就记录

B:show processlist;

mysql> show full processlist; 或者是  show processlist;
+-------+------+-----------------+---------+---------+------+-------+-----------------------+
| Id    | User | Host            | db      | Command | Time | State | Info                  |
+-------+------+-----------------+---------+---------+------+-------+-----------------------+
| 69139 | ugp  | localhost:53234 | udm_cnc | Sleep   |  957 |       | NULL                  |
| 69140 | ugp  | localhost:53235 | ugp     | Sleep   |  947 |       | NULL                  |
| 69701 | ugp  | localhost       | NULL    | Query   |    0 | NULL  | show full processlist |
| 69710 | ugp  | localhost:53802 | ugp     | Sleep   |   14 |       | NULL                  |
| 69711 | ugp  | localhost:53803 | ugp     | Sleep   |   14 |       | NULL                  |
| 69712 | ugp  | localhost:53805 | ugp     | Sleep   |   66 |       | NULL                  |
| 69713 | ugp  | localhost:53806 | ugp     | Sleep   |   14 |       | NULL                  |
| 69714 | ugp  | localhost:53807 | ugp     | Sleep   |   14 |       | NULL                  |
| 69715 | ugp  | localhost:53808 | ugp     | Sleep   |   14 |       | NULL                  |
| 69716 | ugp  | localhost:53809 | ugp     | Sleep   |   14 |       | NULL                  |
| 69717 | ugp  | localhost:53810 | ugp     | Sleep   |   14 |       | NULL                  |
| 69718 | ugp  | localhost:53811 | ugp     | Sleep   |   14 |       | NULL                  |
| 69719 | ugp  | localhost:53812 | ugp     | Sleep   |   14 |       | NULL                  |
| 69720 | ugp  | localhost:53813 | ugp     | Sleep   |   14 |       | NULL                  |
+-------+------+-----------------+---------+---------+------+-------+-----------------------+
14 rows in set (0.00 sec)

这个命令的意思是：显示哪些线程正在运行。您也可以使用 mysqladmin processlist语句得到此信息。如果您有SUPER权限，您可以看到所有线程。否则，您只能看到您自己的线程（也就是，与您正在使用的MySQL账户相关的线程）。如果您不使用FULL关键词，则只显示每个查询的前100个字符。本语句报告TCP/IP连接的主机名称（采用 host_name: client_port格式），以方便地判定哪个客户端正在做什么。

执行几次发现sql一直在执行或者查看state是什么原因导致的，就能够知道哪条sql占用了大量资源！

2、sql分析

explain(执行计划)关键字来分析sql。以下转自：http://www.wzzjla.com/Html/201211/648.html

explain语法

有两种用法：

1.EXPLAIN tbl_name

2.EXPLAIN [EXTENDED] SELECT select_options
为了更好的说明它，我们需要建两张表，下面的语句用于创建一张测试用的订单表：
1. CREATE TABLE `t_order` (
2. `order_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '订单ID',
3. `express_type` tinyint(1) unsigned NOT NULL COMMENT '快递方式',
4. `user_id` int(10) unsigned DEFAULT NULL COMMENT '用户ID',
5. `add_time` int(10) NOT NULL COMMENT '下单时间',
6. PRIMARY KEY (`order_id`),
7. KEY `user_id` (`user_id`),
8. KEY `express_type` (`express_type`)
9. ) ENGINE=InnoDB AUTO_INCREMENT=100000 DEFAULT CHARSET=utf8 COMMENT='订单记录表'
然后我往其中插入了100000条记录。

下面是订单的扩展表，仅向其中插入了一条记录，关联某笔订单。
1. CREATE TABLE `t_order_ext` (
2. `order_id` int(10) NOT NULL COMMENT '订单ID',
3. `user_type` int(11) NOT NULL DEFAULT '0' COMMENT '用户类型',
4. `comment` varchar(255) CHARACTER SET utf8 NOT NULL COMMENT '订单备注',
5. KEY `order_id` (`order_id`)
6. ) ENGINE=InnoDB DEFAULT CH

作为开发者必须掌握的mysql操作sql语句优化-showprocesslistandexplain(一)