mysql的trace跟踪

root@D0DCS 15:17:24 [jing]> show create table t1\G;
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `c1` int(11) NOT NULL DEFAULT '0',
  `c2` varchar(128) DEFAULT NULL,
  `c3` varchar(64) DEFAULT NULL,
  `c4` int(11) DEFAULT NULL,
  PRIMARY KEY (`c1`),
  KEY `ind_c2` (`c2`),
  KEY `ind_c4` (`c4`),
  KEY `ind_c2_c4` (`c2`,`c4`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)


ERROR: 
No query specified


root@D0DCS 15:17:30 [jing]> select * from t1;
+----+------+------+------+
| c1 | c2   | c3   | c4   |
+----+------+------+------+
|  1 | a    | A    |   10 |
|  2 | b    | B    |   20 |
|  3 | b    | BB   |   20 |
|  4 | b    | BBB  |   30 |
|  5 | b    | BBB  |   40 |
|  6 | c    | C    |   50 |
|  7 | d    | D    |   60 |
+----+------+------+------+
7 rows in set (0.00 sec)






#打开profiling 的设置  
SET profiling = 1;  
SHOW VARIABLES LIKE '%profiling%';  
  
#查看队列的内容  
show profiles;    
#来查看统计信息  
show profile block io,cpu for query 3;  




root@D0DCS 14:46:31 [jing]> explain select * from t1 where  c2='b' and c4=20;
+----+-------------+-------+------+-------------------------+--------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys           | key    | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+-------------------------+--------+---------+-------+------+-------------+
|  1 | SIMPLE      | t1    | ref  | ind_c2,ind_c4,ind_c2_c4 | ind_c4 | 5       | const |    2 | Using where |
+----+-------------+-------+------+-------------------------+--------+---------+-------+------+-------------+
1 row in set (0.00 sec)






查看查看OPTIMIZER_TRACE方法：
set optimizer_trace="enabled=on"; 
set optimizer_trace_max_mem_size=1000000;




root@D0DCS 14:47:12 [jing]>   set end_markers_in_json=on;  
Query OK, 0 rows affected (0.00 sec)


root@D0DCS 14:55:15 [jing]> explain select * from t1 where  c2='b' and c4=20;
+----+-------------+-------+------+-------------------------+--------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys           | key    | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+-------------------------+--------+---------+-------+------+-------------+
|  1 | SIMPLE      | t1    | ref  | ind_c2,ind_c4,ind_c2_c4 | ind_c4 | 5       | const |    2 | Using where |
+----+-------------+-------+------+-------------------------+--------+---------+-------+------+-------------+
1 row in set (0.00 sec)


root@D0DCS 14:55:44 [jing]> select * from information_schema.optimizer_trace\G;
*************************** 1. row ***************************
                            QUERY: explain select * from t1 where  c2='b' and c4=20
                            TRACE: {
  "steps": [
    {
      "join_preparation": {   ---优化准备工作
        "select#": 1,
        "steps": [
          {