1、定位执行效率较低的sql语句
通常有两种方法。
一种是慢查询日志，用--log-slow-queries[=file_name]启动时，mysql会记录所有执行时间超过long_query_time秒的sql语句的日志文件。
long_query_time参数单位时秒，精度可以精确到微秒，默认情况下管理语句和不使用索引进行查询的语句是不会记录的，如果要记录这两类sql语句可以用--log-slow-admin-statements和log_queries_not_using_indexes来控制。
还有一种方法就是使用show processlist命令查看当前mysql在进行的线程，包括线程的状态，是否锁表等，实时的查看sql的执行情况，同时对锁表操作进行优化。
2、通过explain分析低效sql的执行计划
explain命令可以获取mysql的执行计划。
执行如下命令：
EXPLAIN SELECT * FROM `user` WHERE `uid`=1
会显示：
id: 1 select_type: SIMPLE table: user type: const possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: const rows: 1 Extra: NULL
2.1、select_type:表示select的类型，常见的取值有SIMPLE（简单表，即不使用表连接或子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION中的第二个或者后面的查询语句）、SUBQUERY（子查询中的第一个SELECT）等。
2.2、table：输出结果集的表。
2.3、type：表示mysql在表中找到所需行的方式，或者叫访问类型，常见的类型如下：
ALL、index、range、ref、eq_ref、const，system、NULL。
从左至右，性能有最好到最差。
type=All，全表扫描。
type=index，索引全扫描。
type=range，索引范围扫描，常见于<、<=、>、>=、between等操作符。
type=ref，使用非唯一索引扫描或唯一索引的前缀扫描。
type=eq_ref，类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配;简单来说，就是多表连接中使用primary key或者unique index作为关联条件。
type=const\system，单表中最多有一个匹配行，查询起来非常迅速，所以这个匹配行中的其他列的值可以被优化器在当前查询中当做常量来处理。
例如根据主键或者唯一索引进行的查询。
type=NULL，mysql不用访问表或索引，直接可以得到结果。
type还有其他值，如ref_or_null（与ref类似，区别在于条件中包含对NULL的查询）、index_merge（索引合并优化）、unique_subquery（in的后面是一个查询主键字段的子查询）等。
2.4、possible_keys:查询时可能使用的索引。
2.5、key:实际使用的索引。
2.6、key_len:使用到索引字段的长度。\
2.7、rows：扫描行的数量。
2.8、extra：执行情况的说明和描述，包括不适合在其他列中显示，但是对执行计划非常重要的额外信息。
explain partitions命令可以查看sql所访问的分区。
3、通过show profile分析sql
通过have_profiling参数，能够看到当前mysql是否支持profile：
select @@have_profiling； select @@profiling；
默认是关闭的，通过set语句可以在session级别开启：
set profiling=1；
通过profile可以更清楚地了解sql的执行情况。
执行一条sql：
SELECT COUNT(*) FROM `user`;
执行：
show profiles;
可以看到刚刚执行的sql的query ID。
执行：
show profile for query 2;
，这个2是我这里的query id。
+----------------------+----------+ | Status | Duration | +----------------------+----------+ | starting | 0.027428 | | checking permissions | 0.000009 | | Opening tables | 0.000012 | | init | 0.000010 | | System lock | 0.000005 | | optimizing | 0.000004 | | statistics | 0.000008 | | preparing | 0.000008 | | executing | 0.000002 | | Sending data | 0.000028 | | end | 0.000003 | | query end | 0.000004 | | closing tables | 0.000006 | | freeing items | 0.000013 | | cleaning up | 0.000012 | +----------------------+----------+ 15 rows in set, 1 warning (0.00 sec)
可以清楚的看到执行过程中的每个状态和消耗的时间。
还支持进一步选择all、cpu、block io、context switch、page faults等明细类型来查看mysql在使用什么资源上消耗了过高的时间。
mysql> show profile cpu for query 1; +----------------------+----------+----------+------------+ | Status | Duration | CPU_user | CPU_system | +----------------------+----------+----------+------------+ | starting | 0.000045 | 0.000000 | 0.000000 | | checking permissions | 0.000007 | 0.000000 | 0.000000 | | Opening tables | 0.000016 | 0.000000 | 0.000000 | | init | 0.000013 | 0.000000 | 0.000000 | | System lock | 0.000006 | 0.000000 | 0.000000 | | optimizing | 0.000005 | 0.000000 | 0.000000 | | statistics | 0.000011 | 0.000000 | 0.000000 | | preparing | 0.000011 | 0.000000 | 0.000000 | | executing | 0.000003 | 0.000000 | 0.000000 | | Sending data | 0.000037 | 0.004000 | 0.000000 | | end | 0.000005 | 0.000000 | 0.000000 | | query end | 0.000005 | 0.000000 | 0.000000 | | closing tables | 0.000008 | 0.000000 | 0.000000 | | freeing items | 0.000012 | 0.000000 | 0.000000 | | cleaning up | 0.000009 | 0.000000 | 0.000000 | +----------------------+----------+----------+------------+ 15 rows in set, 1 warning (0.00 sec)
4、通过tract分析优化器如何选择执行计划
mysql5.6提供了对sql的跟踪track，通过track可以了解为什么优化器选择A计划而不选择B计划。
使用方式：首先打开track，格式设置为json，设置track最大能够使用的内存大小。
mysql> SET OPTIMIZER_TRACE="enabled=on",END_MARKERS_IN_JSON=on; Query OK, 0 rows affected (0.01 sec) mysql> SET OPTIMIZER_TRACE_MAX_MEM_SIZE=5000; Query OK, 0 rows affected (0.00 sec)
接下来就是执行想track的语句，
SELECT * FROM `blog_user` WHERE `uid` > 2 AND `uid` < 10;
最后检查INFORMATION_SCHEMA.OPTIMIZER_TRACE就可以知道mysql是如何执行sql的：
mysql> SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\G
*************************** 1. row ***************************
QUERY: SELECT * FROM `blog_user` WHERE `uid` > 2 AND `uid` < 10
TRACE: {
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `blog_user`.`uid` AS `uid`,`blog_user`.`user_name` AS `user_name`,`blog_user`.`password` AS `password`,`blog_user`.`email` AS `email`,`blog_user`.`site` AS `site`,`blog_user`.`type` AS `type`,`blog_user`.`create_time` AS `create_time` from `blog_user` where ((`blog_user`.`uid` > 2) and (`blog_user`.`uid` < 10))"
}
] /* steps */
} /* join_preparation */
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "((`blog_user`.`uid` > 2) and (`blog_user`.`uid` < 10))",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "((`blog_user`.`uid` > 2) and (`blog_user`.`uid` < 10))"
},
{
"transformation": "constant_propagation",
"resulting_condition": "((`blog_user`.`uid` > 2) and (`blog_user`.`uid` < 10))"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "((`blog_user`.`uid` > 2) and (`blog_user`.`uid` < 10))"
}
] /* steps */
} /* condition_processing */
},
{
"table_dependencies": [
{
"table": "`blog_user`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
] /* depends_on_map_bits */
}
] /* table_dependencies */
},
{
"ref_optimizer_key_uses": [
] /* ref_optimizer_key_uses */
},
{
"rows_estimation": [
{
"table": "`blog_user`",
"range_analysis": {
"table_scan": {
"rows": 14,
"cost": 5.9
} /* table_scan */,
"potential_range_indices": [
{
"index": "PRIMARY",
"usable": true,
"key_parts": [
"uid"
] /* key_parts */
}
] /* potential_range_indices */,
"setup_range_conditions": [
] /* setup_range_conditions */,
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
} /* group_index_range */,
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "PRIMARY",
"ranges": [
"2 < uid < 10"
] /* ranges */,
"index_dives_for_eq_ranges": true,
"rowid_ordered": true,
"using_mrr": false,
"index_only": false,
"rows": 1,
"cost": 1.21,
"chosen": true
}
] /* range_scan_alternatives */,
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
} /* analyzing_roworder_intersect */
} /* analyzing_range_alternatives */,
"chosen_range_access_summary": {
"range_access_plan": {
"type": "range_scan",
"index": "PRIMARY",
"rows": 1,
"ranges": [
"2 < uid < 10"
] /* ranges */
} /* range_access_plan */,
"rows_for_plan": 1,
"cost_for_plan": 1.21,
"chosen": true
} /* chosen_range_access_summary */
} /* range_analysis */
}
] /* rows_estimation */
},
{
"considered_execution_plans": [
{
"plan_prefix": [
] /* plan_prefix */,
"table": "`blog_user`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "range",
"rows": 1,
"cost": 1.41,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 1177
INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.00 sec)