mysql中pager的用法

最新推荐文章于 2021-03-24 03:49:49 发布

转载最新推荐文章于 2021-03-24 03:49:49 发布 · 2.5k 阅读

文章标签：

#mysql #sql #less #database #command #output

数据库开发经验专栏收录该内容

25 篇文章

订阅专栏

本文介绍了如何使用MySQL的pager工具来优化查询过程，包括展示长查询结果的更易读格式和验证新旧SQL语句数据集的一致性。通过设置文件输出和使用特殊参数，可以更有效地管理和理解查询行为。

Over the past few years I have found that "pager" inside of MySQL is a really useful tool. I have come up with a few simple, but extremely effective, ways to use it.

-- Example 1
Lets say you have a MySQL server that is really busy with extremely long queries. You run a “show full processlist” and everything going on streams before your eyes, new lines and all. A more readable way to see what is going on is…

Mysql> pager less –S

By running the above statement the full processlist will NOT appear in the traditional, word wrapped format. The output will show in a very readable, right extended, format. All you need to do now figure out what is going on.

Here is a helpful hint. Set a tee file for MySQL client output.

Mysql> \T /location/to/file.txt

Then, make sure pager is set to “less –S”. From there you can get out of the MySQL client and write a shell script to sort or do whatever to the file.

-- Example 2
Lets say you’re the DBA that is responsible to ensure that all of NEW MySQL SQL statement are functionally equivalent. Or, lets say that you are responsible for optimizing NEW SQL and to ensure that is hold integrity from the old version to the new version. You might say, “is an easy way to tell if you have an exact data set match or not?’ Well, yeppers, there is! All you need to do is use “pager”.

Mysql> pager md5sum

Then run the OLD SQL statement, gather the md5sum, then run the NEW and improved SQL and gather the md5sum. If both are the same then both datasets returned in either SQL statement are exact. Below is an example with the world database, just to illustrate the point.

mysql> select * from City as c, Country ct where c.countrycode = ct.code;
3c8c6b8331c9270f8565edd1a8b8d7a5 -
4079 rows in set (0.05 sec)

mysql> select * from City c join Country ct ON(c.countrycode = ct.code);
3c8c6b8331c9270f8565edd1a8b8d7a5 -
4079 rows in set (0.05 sec)

These are just a couple cool ways to use “pager” inside of the MySQL client. Oh, and before I forget, to turn pager off inside the MySQL command line run the following:

Mysql> nopager

Or just log out and log back in.