通过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分隔开的查询条件