Mysql基础篇--面试如何定位低效率sql语句

小土豆Yuki

发布于 2020-06-15 17:27:21

1K0

发布于 2020-06-15 17:27:21

当面对一个sql性能问题，我们应该从何处入手使得尽快定位问题sql，我们从基础的命令开始

1.查看各种命令的执行频率

mysql> show status like 'Com_%' \g
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| Com_admin_commands        | 0     |
| Com_assign_to_keycache    | 0     |
| Com_alter_db              | 0     |
| Com_alter_db_upgrade      | 0     |
| Com_alter_event           | 0     |
| Com_alter_function        | 0     |
| Com_alter_procedure       | 0     |
| Com_alter_server          | 0     |
| Com_alter_table           | 0     |
| Com_alter_tablespace      | 0     |
| Com_alter_user            | 0     |
| Com_analyze               | 0     |
| Com_begin                 | 0     |
| Com_binlog                | 0     |
| Com_call_procedure        | 0     |
| Com_change_db             | 0     |
| Com_change_master         | 0     |
| Com_check                 | 0     |
| Com_checksum              | 0     |
| Com_commit                | 0     |

Com_xxx表示每个xxx语句执行的次数，创建的的参数如下

Com_select :执行select操作的次数，一次查询累加一次。

Com_insert: 执行insert操作的次数，批量插入累加一次

Com_delete:执行delete操作的次数

Com_update:执行update操作的次数

下面的参数只针对InnoDB存储引擎，累加的算法也略有不通。

Innodb_rows_read:select查询返回的行数。

Innodb_rows_inserted:insert插入的的行数。

innodb_rows_deleted:delete删除的行数。

innodb_rows_updated:update更新的行数，不管是提交还是回滚都会进行累加。

上面命令可以查出你的数据库到底是以查询为主，还是更新操作为主。

对于事物型的应用，通过Com_commit和Com_rollback可以了解提交和回滚的情况，对于回滚此处过多，就有可能是编程出现了问题。

下面参数是了解数据库的基本情况

Connections:试图连接Mysql服务器的次数。

Uptime:服务器工作时间。

Slow_queries:慢查询的次数。

2.定位执行效率低的sql语句

通过慢查询日志定位那些sql执行效率低，通过--log_slow-queries[=file-names]选项启动，mysqld会记录一个超过long_query_time秒的日志文件。

慢查询日志是在应用执行完之后记录的，索引应用在出现sql效率的问题上并能定位问题，我们可以使用 show processlist命令查看当前mysql在进行的线程，包括线程的状态，是够锁表，可以实时的查看sql的执行情况，同时对一些锁表操作进行优化。

3通过explain分析低效率的sql执行计划

通过上面步骤查看到低效率的sql之后，我们就可以使用explain或desc命令获取mysql如何执行select语句的信息,包括如何连接和链接的顺序。

我们先看几个参数的说明

select_type:表示select类型。常见的取值有SIMPLE(简单表，既不是用表连接或子查询)PRIMARY(朱查询，即外层查询)，UNION( UNION中的第二哥或者后面的查询语句)SUBQUERY(子查询的第一个select)

table:输出结果的表。

type:访问类型，在表中找到所需行的方式。

从左到右，性能依次最好。

type=ALL,全表扫描

mysql> explain select  * from film where rating>9\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: film
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1000
        Extra: Using where
1 row in set (0.00 sec)

type=index.索引全扫描，mysql遍历整个多音来查询匹配的行

mysql> explain select title from film\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: film
         type: index
possible_keys: NULL
          key: idx_title
      key_len: 767
          ref: NULL
         rows: 1000
        Extra: Using index
1 row in set (0.00 sec)

type=range.索引范围查询，常用语>,<,<=,>=,between

mysql> explain select * from payment where customer_id>=300 and customer_id<350\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: payment
         type: range
possible_keys: idx_fk_customer_id
          key: idx_fk_customer_id
      key_len: 2
          ref: NULL
         rows: 1325
        Extra: Using index condition
1 row in set (0.00 sec)

type=ref,使用非唯一索引或唯一索引前缀扫描，返回某个单独之的记录行

mysql> explain select *  from payment where customer_id=350\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: payment
         type: ref
possible_keys: idx_fk_customer_id
          key: idx_fk_customer_id
      key_len: 2
          ref: const
         rows: 23
        Extra: NULL
1 row in set (0.00 sec)

type=eq_ref,类似ref,区别是使用的是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单是多表链接中使用primary_key或unique index作为关联条件、

mysql> explain select * from film a, film_text b where a.film_id =b.film_id\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: b
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1000
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: a
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 2
          ref: sakila.b.film_id
         rows: 1
        Extra: Using where
2 rows in set (0.00 sec)

type=const/system，单表中最多匹配一行数据，查询非常迅速，所以这个匹配行中的其他列的值可以被优化器在当前查询中当做常量来处理。例如根据主键或者唯一索引进行查询。

mysql> explain select  *   from (select  * from customer where email='AARON.SELBY@SAKILACUSTOMER.ORG')a\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
         type: system
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
        Extra: NULL
*************************** 2. row ***************************
           id: 2
  select_type: DERIVED
        table: customer
         type: const
possible_keys: uk_email
          key: uk_email
      key_len: 153
          ref: const
         rows: 1
        Extra: NULL
2 rows in set (0.00 sec)

type=null,不用访问表或索引，直接就返回结果。

mysql> explain select   1 from dual where 1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: No tables used
1 row in set (0.00 sec)

类型type还有其他值，如ref_or_null(与ref类似，区别是对查询条件包含null值查询)，index_merge(索引合并优化)，unique_subquery(in的后面是一个查询主键字段的子查询)，index_subquery(与unique_subquery类似，区别是在于in的后面是查询非唯一索引字段的子查询)等，

possible_key:表示查询可能使用的索引
key：实际使用的索引
key_len：使用索引的的字段长度
rows:扫描行的数目
extra:执行情况的说明和描述，包含不适合在其他列中显示但是对执行计划非常重要的额外信息.

mysql4.1引入了explain extended 命令，通过explain extended和show warnings,w我们能够看到sql真正执行之前优化器做了哪些优化，在mysql 5.1开始支持分区的功能，同时可以使用explain partitions 查看数据结果多需要访问的分区名称。

4.show profile分析sql

首先查看是否支持show profile，如果支持，开启profiling,如下是否支持使用

select @@have_profiling.

mysql> select @@have_profiling;  //是否支持show profiling
+------------------+
| @@have_profiling |
+------------------+
| YES              |
+------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select @@profiling;  //默认关闭
+-------------+
| @@profiling |
+-------------+
|           0 |
+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> set profiling=1;  //开启profiling
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           1 |
+-------------+
1 row in set, 1 warning (0.01 sec)

通过profile，我们可以更清除的了解sql执行的过程，比如，我使用count(*).我们知道myisam表使用count(*)是不需要小号太多资源的，相对于Innodb就没有这种元数据缓存，count(*)执行的较慢，如下

mysql> select count(*) from payment;
+----------+
| count(*) |
+----------+
|    16049 |
+----------+
1 row in set (0.01 sec)

使用show profiles查看query_id

mysql> show profiles;
+----------+------------+------------------------------+
| Query_ID | Duration   | Query                        |
+----------+------------+------------------------------+
|        1 | 0.00350500 | select @@profiling           |
|        2 | 0.00863050 | select count(*) from payment |
+----------+------------+------------------------------+
2 rows in set, 1 warning (0.00 sec)

在使用show profiling for query 查看执行过程的每个线程状态和消耗的时间

mysql> show profile for query 2;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000506 |
| checking permissions | 0.000272 |
| Opening tables       | 0.000467 |
| init                 | 0.000039 |
| System lock          | 0.000229 |
| optimizing           | 0.000231 |
| statistics           | 0.000040 |
| preparing            | 0.000222 |
| executing            | 0.000083 |
| Sending data         | 0.006353 |
| end                  | 0.000024 |
| query end            | 0.000013 |
| closing tables       | 0.000022 |
| freeing items        | 0.000090 |
| cleaning up          | 0.000041 |
+----------------------+----------+
15 rows in set, 1 warning (0.00 sec)

我们发现消耗的时间在 sending data(表示mysql线程开始访问数据，到返回到客户端，而不是仅仅返回结果给客户端，sending data 状态往往需要做大量的磁盘读取操作，所以往往是访问消耗最长的状态)，我们也可以查看mysql在上什么资源(all,cpu，block io,context switch,page faults)上消耗过高的时间，例如，查看CPU

mysql> show profile cpu for query 2;
+----------------------+----------+----------+------------+
| Status               | Duration | CPU_user | CPU_system |
+----------------------+----------+----------+------------+
| starting             | 0.000506 | 0.000000 |   0.000000 |
| checking permissions | 0.000272 | 0.000000 |   0.000000 |
| Opening tables       | 0.000467 | 0.000000 |   0.000000 |
| init                 | 0.000039 | 0.000000 |   0.000000 |
| System lock          | 0.000229 | 0.000000 |   0.000000 |
| optimizing           | 0.000231 | 0.000000 |   0.000000 |
| statistics           | 0.000040 | 0.000000 |   0.000000 |
| preparing            | 0.000222 | 0.000000 |   0.000000 |
| executing            | 0.000083 | 0.000000 |   0.000000 |
| Sending data         | 0.006353 | 0.010000 |   0.000000 |
| end                  | 0.000024 | 0.000000 |   0.000000 |
| query end            | 0.000013 | 0.000000 |   0.000000 |
| closing tables       | 0.000022 | 0.000000 |   0.000000 |
| freeing items        | 0.000090 | 0.000000 |   0.000000 |
| cleaning up          | 0.000041 | 0.000000 |   0.000000 |
+----------------------+----------+----------+------------+
15 rows in set, 1 warning (0.01 sec)

使用myisam存储引擎，效率就比较好，完全不需要访问数据

mysql> create table payment_myisam like payment;
Query OK, 0 rows affected (0.05 sec)

mysql> alter table payment_myisam engine=myisam;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into payment_myisam select  *  from payment;
Query OK, 16049 rows affected (0.09 sec)
Records: 16049  Duplicates: 0  Warnings: 0

mysql> select count(*) from payment_myisam;
+----------+
| count(*) |
+----------+
|    16049 |
+----------+
1 row in set (0.00 sec)

使用show profiles 查看queryid ,查看queryid消耗在哪个状态以及时间

mysql> show profiles
    -> ;
+----------+------------+----------------------------------------------------+
| Query_ID | Duration   | Query                                              |
+----------+------------+----------------------------------------------------+
|        1 | 0.00350500 | select @@profiling                                 |
|        2 | 0.00863050 | select count(*) from payment                       |
|        3 | 0.00044600 | set @query_id :=2                                  |
|        4 | 0.00038475 | select state ,sum(duration) as total_r,            |
|        5 | 0.05704400 | create table payment_myisam like payment           |
|        6 | 0.05384750 | alter table payment_myisam engine=myisam           |
|        7 | 0.08750150 | insert into payment_myisam select  *  from payment |
|        8 | 0.00061250 | select count(*) from payment_myisam                |
+----------+------------+----------------------------------------------------+
8 rows in set, 1 warning (0.00 sec)

mysql> show profile for query 8;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000115 |
| checking permissions | 0.000035 |
| Opening tables       | 0.000064 |
| init                 | 0.000054 |
| System lock          | 0.000053 |
| optimizing           | 0.000023 |
| executing            | 0.000022 |
| end                  | 0.000023 |
| query end            | 0.000022 |
| closing tables       | 0.000037 |
| freeing items        | 0.000048 |
| cleaning up          | 0.000117 |
+----------------------+----------+
12 rows in set, 1 warning (0.00 sec)

发现在myisam存储引擎中，在executing之后就直接结束查询了，完全不需要访问数据。

经过以上步骤基本就可确定问题出现在哪里，此时可以采用响应的措施，进行提高执行的效率。最常见的措施就是添加索引，索引下一篇文章在见，请持续关注。

本文参与腾讯云自媒体分享计划，分享自微信公众号。

原始发表：2019-11-18，如有侵权请联系 cloudcommunity@tencent.com 删除

云数据库 SQL Server