MYSQL 调优学习笔记

“ 阅读完本文需要 5 分钟。”

最近在看《深入浅出 MYSQL》，将自己所学的知识点做个记录整理下来，尤其是 MYSQL 调优这一块是重点。1、SQL 优化1.1 show status 命令查看各种 SQL 的执行效率

mysql> show status like 'Com_%';

 

主要关心 Com_select、Com_insert、Com_update、Com_delete 的数值，分别代表了不同操作次数，对于 Innodb 引擎而言，参数发生了变化：Innodb_rows_read、Innodb_rows_inserted、Innodb_rows_updated、
Innodb_rows_deleted，通过查看参数可以了解当前数据库的应用是以插入更新为主还是以查询操作为主，以及各种类型的 SQL 大致的执行比例是多少。
1.1.2  定位执行效率较低的 SQL 语句一是通过慢查询日志定位执行效率较低的 SQL 语句，用 --log-slow-queries[= file_name] ；二是使用 mysql> show processlist; 实时命令查看。1.1.3 通过 EXPLAIN 分析低效 SQL 的执行计划

mysql> explain select sum(amount) from customer a, payment b 

 where 1=1 and a.customer_id = b.customer_id and  

 email = '[email protected]';

 

执行结果：

+----+-------------+-------+------------+------+--------------------+--------------------+---------+----------------------+------+----------+-------------+

| id | select_type | table | partitions | type | possible_keys      | key                | key_len | ref                  | rows | filtered | Extra       | 

+----+-------------+-------+------------+------+--------------------+--------------------+---------+----------------------+------+----------+-------------+

|  1 | SIMPLE      | a     | NULL       | ref  | PRIMARY,idx_email  | idx_email          | 153     | const                |    1 |   100.00 | Using index |

|  1 | SIMPLE      | b     | NULL       | ref  | idx_fk_customer_id | idx_fk_customer_id | 2       | sakila.a.customer_id |   26 |   100.00 | NULL        |

+----+-------------+-------+------------+------+--------------------+--------------------+---------+----------------------+------+----------+-------------+

2 rows in set (0.10 sec)

 

注意 explain extended 已经在新的 MYSQL 版本中被取消了，可以使用 show warnings；查看具体执行的 CODE，同时书中在命令后面加上 \G 这个功能和具体的工具有关，Navicat 就不支持这种格式化功能，https://q.cnblogs.com/q/109910/。1.1.4 通过 show profile 分析 SQLMYSQL 从 5.0.37 版本增加了 show profiles 和 show profiles 语句的支持，1、查看是否支持：

mysql>  select @@have_profiling;

 

2、开启 profiling 
 

mysql> select @@profiling;

mysql> set profiling = 1;

 

3、执行查询语句，使用 show profiles 语句查看执行效率
 

mysql> select count(*) from payment;

mysql> show profiles;

+----------+------------+------------------------------+

| Query_ID | Duration   | Query                        |

+----------+------------+------------------------------+

|        1 | 0.00024475 | select @@profiling           |

|        2 | 0.00551000 | select count(*) from payment |

+----------+------------+------------------------------+

2 rows in set (0.03 sec)

 

4、使用 show query for id 语句查看线程中的每个状态和消耗的时间
 

mysql> show profile for query 2;

+----------------------+----------+

| Status               | Duration |

+----------------------+----------+

| starting             | 0.000043 |

| checking permissions  | 0.000006 |

| Opening tables       | 0.000018 |

| init                  | 0.000015 |

| System lock          | 0.000008 |

| optimizing