mysql的优化-系统状态查看-定位问题,分析sql,索引(2)

通过trace分析sql

1打开trace   设置格式为json
set optimizer_trace="enabled=on",end_markers_in_json=on;

设置trace最大使用内存
set optimizer_trace_max_mem_size=10000000;

做trace的语句
select * from  complain  limit 100000,10;

检查information_schema.optimizer_trace;
select  * from information_schema.optimizer_trace \G
mysql> select  * from information_schema.optimizer_trace \G
*************************** 1. row ***************************
                            QUERY: select * from  complain  limit 100000,10
                            TRACE: {
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `complain`.`ID` AS `ID`,`complain`.`complain_code` AS `complain_code`,`complain`.`complain_name` AS `complain_name`,`complain`.`create_id` AS `create_id`,`complain`.`create_name` AS `create_name`,`complain`.`create_time` AS `create_time`,`complain`.`create_comp_name` AS `create_comp_name`,`complain`.`create_comp_code` AS `create_comp_code`,`complain`.`create_dept_code` AS `create_dept_code`,`complain`.`create_dept_name` AS `create_dept_name`,`complain`.`update_time` AS `update_time`,`complain`.`last_edit_time` AS `last_edit_time`,`complain`.`comp_link_name` AS `comp_link_name`,`complain`.`comp_link_mobile` AS `comp_link_mobile`,`complain`.`problem_name` AS `problem_name`,`complain`.`problem_mobile` AS `problem_mobile`,`complain`.`problem_time` AS `problem_time`,`complain`.`problem_addres` AS `problem_addres`,`complain`.`material_name` AS `material_name`,`complain`.`material_standard` AS `material_standard`,`complain`.`receive_time` AS `receive_time`,`complain`.`receive_no` AS `receive_no`,`complain`.`problem_num` AS `problem_num`,`complain`.`status` AS `status`,`complain`.`unflow_receive_time` AS `unflow_receive_time`,`complain`.`unflow_submit_time` AS `unflow_submit_time`,`complain`.`unflow_result` AS `unflow_result`,`complain`.`unflow_suggest` AS `unflow_suggest`,`complain`.`unflow_user_id` AS `unflow_user_id`,`complain`.`unflow_user_name` AS `unflow_user_name`,`complain`.`unflow_scene_result` AS `unflow_scene_result`,`complain`.`unflow_remark1` AS `unflow_remark1`,`complain`.`unflow_remark2` AS `unflow_remark2`,`complain`.`remark` AS `remark`,`complain`.`remark2` AS `remark2`,`complain`.`remark3` AS `remark3`,`complain`.`material_code` AS `material_code`,`complain`.`provider_id` AS `provider_id`,`complain`.`provider_code` AS `provider_code`,`complain`.`provider_name` AS `provider_name` from `complain` limit 100000,10"
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "table_dependencies": [
              {
                "table": "`complain`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              }
            ] /* table_dependencies */
          },
          {
            "rows_estimation": [
              {
                "table": "`complain`",
                "table_scan": {
                  "rows": 6000498,
                  "cost": 125631
                } /* table_scan */
              }
            ] /* rows_estimation */
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ] /* plan_prefix */,
                "table": "`complain`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "rows_to_scan": 6000498,
                      "access_type": "scan",
                      "resulting_rows": 6e6,
                      "cost": 1.33e6,
                      "chosen": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 6e6,
                "cost_for_plan": 1.33e6,
                "chosen": true
              }
            ] /* considered_execution_plans */
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": null,
              "attached_conditions_computation": [
              ] /* attached_conditions_computation */,
              "attached_conditions_summary": [
                {
                  "table": "`complain`",
                  "attached": null
                }
              ] /* attached_conditions_summary */
            } /* attaching_conditions_to_tables */
          },
          {
            "refine_plan": [
              {
                "table": "`complain`"
              }
            ] /* refine_plan */
          }
        ] /* steps */
      } /* join_optimization */
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ] /* steps */
      } /* join_execution */
    }
  ] /* steps */
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
          INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.01 sec)

索引


mysql中能够使用索引的场景
1:匹配全值,对索引中所有的列都指定具体值,

mysql> explain select *  from complain where  complain_code='TS20190110000004';
+----+-------------+----------+------------+-------+-----------------------------------+---------------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type  | possible_keys                     | key           | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+-------+-----------------------------------+---------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | complain | NULL       | const | complain_code,complain_code_index | complain_code | 195     | const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+-------+-----------------------------------+---------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select *  from complain where  complain_code='TS20190110000004' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: complain
   partitions: NULL
         type: const  表示是常量
possible_keys: complain_code,complain_code_index 使用的索引
          key: complain_code
      key_len: 195
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.01 sec)


type: const




2匹配的值进行范围查询,对索引的值能够进行范围查询
mysql> explain select  * from  t1 where  c1 >'a1' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: range
possible_keys: t1_c1_index
          key: t1_c1_index
      key_len: 99
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.38 sec)




3匹配最左前缀,仅仅使用索引的中的左边进行查询 ,如,c1+c2+c3 的字段联合索引,
在查询条件中 c1,c1+c2,c1+c2+c3的条件能够使用到
c2,c2+c3不能使用到
c1+c2只能使用c1部分的索引
mysql> explain  select  * from  t1 where  c1='a1' and c2='a2' and c3='a3' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ref
possible_keys: t1_c1_index,t1_c123_index
          key: t1_c1_index
      key_len: 99
          ref: const
         rows: 2
     filtered: 25.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)


mysql> explain  select  * from  t1 where  c1='a1' and c2='a2' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ref
possible_keys: t1_c1_index,t1_c123_index
          key: t1_c1_index
      key_len: 99
          ref: const
         rows: 2
     filtered: 25.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)



mysql> explain  select  * from  t1 where  c1='a1' and c3='a2' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ref
possible_keys: t1_c1_index,t1_c123_index
          key: t1_c1_index
      key_len: 99
          ref: const
         rows: 2
     filtered: 25.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)





4仅仅对索引字段进行查询,查询的列都在列都在索引字段中,查询的效率更高
extra的部分变成了Using index,直接访问索引就足够获取到数据,不需要通过索引回表,Using index也就是平常说的覆盖是索引扫面
减少不必要的数据访问,提高效率。



mysql> explain  select  c1  from  t1 where  c1>'a1'  \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: range
possible_keys: t1_c1_index,t1_c123_index
          key: t1_c1_index
      key_len: 99
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)



5匹配列的前缀
extra值为Using where 表示优化器需要通过索引回表查询数据;

mysql> explain  select  c1  from  t1 where  c1 like 'a%'  \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: range
possible_keys: t1_c1_index,t1_c123_index
          key: t1_c1_index
      key_len: 99
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)



6能够实现索引匹配部分精确而其他部分进行范围匹配;
mysql> explain  select  c1  from  t1 where  c1>='a1'  and c1<='b1' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: index
possible_keys: t1_c1_index,t1_c123_index
          key: t1_c1_index
      key_len: 99
          ref: NULL
         rows: 4
     filtered: 100.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)


7列名是索引,column is null 会使用索引,这里区别于oracle

mysql> explain  select  c1  from  t1 where  c1 is null \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ref
possible_keys: t1_c1_index,t1_c123_index
          key: t1_c1_index
      key_len: 99
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)



存在索引但是不是有索引的场景

1以%开头的like查询不能使用btree索引

mysql> explain  select  c1  from  t1 where  c1 like '%a%' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: index
possible_keys: NULL
          key: t1_c1_index
      key_len: 99
          ref: NULL
         rows: 4
     filtered: 25.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

mysql> explain  select  c1  from  t1 where  c2 like '%a%' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: index
possible_keys: NULL
          key: t1_c123_index
      key_len: 297
          ref: NULL
         rows: 4
     filtered: 25.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

经验证是会使用是索引的

2数据类型出现隐式的转换

mysql> explain  select  c1  from  t1 where  c1 =1 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: index
possible_keys: t1_c1_index,t1_c123_index
          key: t1_c1_index
      key_len: 99
          ref: NULL
         rows: 4
     filtered: 25.00
        Extra: Using where; Using index
1 row in set, 5 warnings (0.00 sec)

经验证也是会使用索引的 

3复合索引,查询不包含最左边的部分,不满足最左原则

4使用索引比全表扫描更加慢,则不使用索引;

5使用or分隔开的查询条件

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值