sql语句分析

SQL 语句摘要可以用在MySQL的各个方面，比如 性能字典里对语句的分析，查询重写插件规则改写等等。

接下来依次看下语句摘要在这两方面的使用。

1. 性能字典

mysql>call sys.ps_setup_enable_consumer('statements')

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

| summary |

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

| Enabled 4 consumers |

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

1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

开启后，执行几次之前的几条 SQL。

完后可以很方便的从 sys 库里分析这类语句的执行情况，包括执行次数，执行时间，扫描的记录数，锁定的时间，是否用到排序等等。

2. 查询重写插件

比如要阻止对表 p1 通过字段 r1 的删除动作，可以用查询重写插件在 MySQL 语句分析层直接转换，这时候就得用到摘要函数 statement_digest_text。

假设：表 p1 字段 id 值全部为正。

delete from p1 where id = 1000

要改写为，

delete from p1 where id = -1

利用函数 statement_digest_text 来定制这条 SQL 的重写规则。

mysql>INSERT INTO query_rewrite.rewrite_rules (pattern, replacement,pattern_database) ->VALUES( ->statement_digest_text('delete from p1 where id = 1000') , ->statement_digest_text('delete from p1 where id = -1'), ->'ytt' ->)Query OK, 1 row affected (0.01 sec)

语句被查询重写后的效果：

mysql>delete from p1 where id = 20000Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>show warnings\G*************************** 1. row *************************** Level: Note Code: 1105Message: Query 'delete from p1 where id = 20000' rewritten to 'DELETE FROM `p1` WHERE `id` = - 20000' by a query rewrite plugin1 row in set (0.00 sec)

mysql>select count(*) from p1+----------+| count(*) |+----------+| 9000001 |+----------+1 row in set (1.59 sec)

总结

MySQL 8.0 新增的语句摘要函数可以很方便的分析 SQL 语句执行的各个方面，比以前分析类似的场景要简单的多。

1先通过status命令查看Mysql运行状态

mysql>status

--------------

mysql Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1

Connection id: 113752

Current database: information_schema

Current user: push_user@localhost

SSL: Not in use

Current pager: stdout

Using outfile: ''

Using delimiter:

Server version: 5.1.73 Source distribution

Protocol version: 10

Connection: Localhost via UNIX socket

Server characterset: latin1

Db characterset: utf8

Client characterset: latin1

Conn. characterset: latin1

UNIX socket: /tmp/mysql.sock

Uptime: 22 days 8 hours 31 min 23 sec

Threads: 38 Questions: 1037751897 Slow queries: 2356 Opens: 79836

Flush tables: 1 Open tables: 64 Queries per second avg: 537.282

--------------

在上面显示列表的最后一条，我们来查看Slow queries这一项的值，如果多次查看的值大于0的话,说明有些查询sql命令执行时间过长。

2)这时再通过show processlist命令来查看当前正在运行的SQL，从中找出运行慢的SQL语句，找到执行慢的语句后，再用explain命令查看这些语句的执行计划。

mysql>show processlist

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

| Id | User | Host| db | Command | Time | State | Info |

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

| 50270 | ambari| DataBase-01:41512 | ambari | Sleep |23 | | NULL |

| 50271 | ambari| DataBase-01:41511 | ambari | Sleep | 6 | | NULL |

| 50272 | ambari| DataBase-01:41514 | ambari | Sleep |23 | | NULL |

| 62452 | oozie | DataBase-02:42987 | oozie | Sleep |25 | | NULL |

| 63660 | ambari| DataBase-01:56052 | ambari | Sleep | 0 | | NULL |

| 110404 | push_user | localhost:33817 | quartz | Sleep |12 | | NULL |

| 112835 | push_user | localhost:46571 | hibernate | Sleep | 1 | | NULL |

| 113163 | push_user | localhost:56585 | hibernate | Sleep | 1 | | NULL |

| 113289 | push_user | 14.118.132.20:47333 | DW | Sleep | 628 | | NULL |

| 113320 | push_user | localhost:47428 | hibernate | Sleep | 3 | | NULL |

| 113321 | push_user | localhost:47429 | hibernate | Sleep | 3 | | NULL |

| 113322 | push_user | localhost:47430 | hibernate | Sleep | 3 | | NULL |

| 113357 | push_user | localhost:52337 | hibernate | Sleep | 3 | | NULL |

| 113364 | push_user | localhost:57206 | hibernate | Sleep | 3 | | NULL |

| 113366 | push_user | localhost:34813 | hibernate | Sleep | 1 | | NULL |

| 113398 | push_user | localhost:37382 | hibernate | Sleep | 1 | | NULL |

| 113498 | push_user | localhost:47626 | quartz | Sleep | 12717 | | NULL |

| 113709 | push_user | localhost:59382 | hibernate | Sleep | 1 | | NULL |

| 113710 | push_user | localhost:33627 | hibernate | Sleep | 1 | | NULL |

| 113715 | hive | DataBase-02:54968 | hive | Sleep | 2390 | | NULL |

| 113716 | hive | DataBase-02:54969 | hive | Sleep | 2390 | | NULL |

| 113717 | hive | DataBase-02:54974 | hive | Sleep | 2336 | | NULL |

| 113718 | hive | DataBase-02:54975 | hive | Sleep | 2336 | | NULL |

| 113719 | push_user | localhost:48243 | hibernate | Sleep | 1 | | NULL |

| 113720 | push_user | localhost:48245 | hibernate | Sleep | 1 | | NULL |

| 113721 | push_user | localhost:48244 | hibernate | Sleep | 1 | | NULL |

| 113722 | push_user | localhost:48247 | hibernate | Sleep | 1 | | NULL |

| 113723 | push_user | localhost:48249 | hibernate | Sleep | 1 | | NULL |

| 113724 | push_user | localhost:48248 | hibernate | Sleep | 1 | | NULL |

| 113745 | push_user | localhost:50684 | hibernate | Sleep | 1 | | NULL |

| 113746 | push_user | localhost:50685 | hibernate | Sleep | 1 | | NULL |

| 113747 | push_user | localhost:50695 | hibernate | Sleep | 1 | | NULL |

| 113748 | push_user | localhost:50696 | hibernate | Sleep | 1 | | NULL |

| 113749 | push_user | localhost:50697 | hibernate | Sleep | 1 | | NULL |

| 113750 | push_user | localhost:50699 | hibernate | Sleep | 1 | | NULL |

| 113751 | push_user | localhost:50700 | hibernate | Sleep | 1 | | NULL |

| 113752 | push_user | localhost | information_schema | Query | 0 | NULL | show processlist |

| 113753 | push_user | 14.118.132.20:28688 | DW | Sleep | 396 | | NULL |

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

38 rows in set (0.00 sec)

或者通过如下命令查询:

mysql>use information_schema

mysql>select * from PROCESSLIST where info is not null

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

| ID | USER | HOST | DB | COMMAND |

TIME | STATE | INFO |

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

| 113752 | push_user | localhost | information_schema | Query |

0 | executing | select * from PROCESSLIST where info is not null |

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

1 row in set (0.00 sec)

从句子中可以看到，选取的字段比较多，另外连接条件也比较多，另外还包括了子查询。

就此语句给出几个需要注意的问题：

1、请先使用explain，对这个语句进行分析，EXPLAIN解释SELECT命令如何被处理。这不仅对决定是否应该增加一个索引，而且对决定一个复杂的Join如何被MySQL处理都是有帮助的。

2、尽量在连接条件多的时候，把数据提取量少的条件放在前面，这样会减少后一个条件的查询时间。对了，这些经常用的连接条件最好建上索引。我不清楚

INNER JOIN table_user_profile AS up ON up.uid = u.uid

INNER JOIN table_user_count AS uc ON uc.uid = u.uid

INNER JOIN table_user_daren AS ud ON ud.uid = u.uid

这些那个先内连接数据比较少，自己排列一下试一试。

3、避免使用!=或＜＞、IS NULL或IS NOT NULL、IN ，NOT IN等这样的 *** 作符,因为这会使系统无法使用索引,而只能直接搜索表中的数据。像in和not in这样的关键字用exists和not exists比较好。u.uid not in(SELECT uid FROM table_user_follow where f_uid=100)改成u.uid not exists(SELECT uid FROM table_user_follow where f_uid=100)，效率会有提高。

4、mysql使用函数的时候会增加负担，完全可以交给脚本程序去解决。比如此子查询：

SELECT MAX(share_id) FROM table_share 完全可以不写在这个sql语句中，交给脚本程序可以了。

---