{"id":297,"title":"sql\u6162\u67e5\u8be2\u53ca\u4f18\u5316\u65b9\u6cd5","good":0,"bad":0,"hit":3113,"created_at":"2018-06-14 22:22:53","content":"

sql\u4f18\u5316<\/p>

\u6162\u67e5\u8be2\uff1a<\/p>

\u68c0\u67e5\u6162\u67e5\u8be2\u662f\u5426\u5f00\u542f<\/p>

MariaDB [(none)]> show variables like 'slow_query_log';<\/p>

+----------------+-------+<\/p>

| Variable_name  | Value |<\/p>

+----------------+-------+<\/p>

| slow_query_log | OFF   |<\/p>

+----------------+-------+<\/p>

1 row in set (0.00 sec)<\/p>

\u6162\u67e5\u8be2\u65e5\u5fd7\u5b58\u653e\u7684\u4f4d\u7f6e\uff1a<\/p>

MariaDB [(none)]> show variables like 'slow_query_log_file';<\/p>

+---------------------+--------------------+<\/p>

| Variable_name       | Value              |<\/p>

+---------------------+--------------------+<\/p>

| slow_query_log_file | localhost-slow.log |<\/p>

+---------------------+--------------------+<\/p>

1 row in set (0.00 sec)<\/p>

\u662f\u5426\u8bb0\u5f55\u672a\u4f7f\u7528\u7d22\u5f15\u7684\u67e5\u8be2\uff1a<\/p>

MariaDB [(none)]> show variables like 'log_queries_not_using_indexes';<\/p>

+-------------------------------+-------+<\/p>

| Variable_name                 | Value |<\/p>

+-------------------------------+-------+<\/p>

| log_queries_not_using_indexes | OFF   |<\/p>

+-------------------------------+-------+<\/p>

1 row in set (0.00 sec)<\/p>

\u6162\u67e5\u8be2\u65f6\u95f4\u8bbe\u7f6e\uff1a\u6b64\u590410\u79d2<\/p>

MariaDB [(none)]> show variables like 'long_query_time';<\/p>

+-----------------+-----------+<\/p>

| Variable_name   | Value     |<\/p>

+-----------------+-----------+<\/p>

| long_query_time | 10.000000 |<\/p>

+-----------------+-----------+<\/p>

1 row in set (0.00 sec)<\/p>

<\/p>

\u6162\u67e5\u8be2\u8bbe\u7f6e\uff1a<\/p>

\u53ef\u4ee5\u7528 set global \u4e0a\u9762\u76f8\u5e94\u53c2\u6570\u8fdb\u884c\u4fee\u6539(\u4e0d\u7528\u767b\u5f55mysql)<\/p>

\u5982\uff1a<\/p>

[root@localhost ~]# set global slow_query_log='OFF';<\/p>

<\/p>

\u65b9\u6cd5\u4e8c\uff1a\u914d\u7f6e\u6587\u4ef6\u8bbe\u7f6e\u4fee\u6539\u914d\u7f6e\u6587\u4ef6my.cnf\uff0c\u5728[mysqld]\u4e0b\u7684\u4e0b\u65b9\u52a0\u5165<\/p>

[mysqld]slow_query_log = ONslow_query_log_file = \/usr\/local\/mysql\/data\/slow.loglong_query_time = 1<\/pre>
\u91cd\u542fMySQL\u670d\u52a1<\/p>
service mysqld restart<\/pre>
<\/p>
\u6162\u67e5\u8be2\u5de5\u5177<\/p>
[root@localhost ~]# mysqldumpslow -h<\/p>
Option h requires an argument<\/p>
ERROR: bad option<\/p>
<\/p>
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]<\/p>
<\/p>
Parse and summarize the MySQL slow query log. Options are<\/p>
<\/p>
  --verbose    verbose<\/p>
  --debug      debug<\/p>
  --help       write this text to standard output<\/p>
<\/p>
  -v           verbose<\/p>
  -d           debug<\/p>
  -s ORDER     what to sort by (al, at, ar, ae, c, l, r, e, t), 'at' is default<\/p>
                al: average lock time<\/p>
                ar: average rows sent<\/p>
                at: average query time<\/p>
                aa: average rows affected<\/p>
                 c: count<\/p>
                 l: lock time<\/p>
                 r: rows sent<\/p>
                 t: query time  <\/p>
  -r           reverse the sort order (largest last instead of first)<\/p>
  -t NUM       just show the top n queries<\/p>
  -a           don't abstract all numbers to N and strings to 'S'<\/p>
  -n NUM       abstract numbers with at least n digits within names<\/p>
  -g PATTERN   grep: only consider stmts that include this string<\/p>
  -h HOSTNAME  hostname of db server for *-slow.log filename (can be wildcard),<\/p>
               default is '*', i.e. match all<\/p>
  -i NAME      name of server instance (if using mysql.server startup script)<\/p>
  -l           don't subtract lock time from total time<\/p>
<\/p>
<\/p>
<\/p>
\u5bf9\u5177\u4f53\u7684sql\u8fdb\u884c\u4f18\u5316<\/p>
MariaDB [test]> explain select * from users;<\/p>
+------+-------------+-------+------+---------------+------+---------+------+------+-------+<\/p>
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |<\/p>
+------+-------------+-------+------+---------------+------+---------+------+------+-------+<\/p>
|    1 | SIMPLE      | users | ALL  | NULL          | NULL | NULL    | NULL |    1 |       |<\/p>
+------+-------------+-------+------+---------------+------+---------+------+------+-------+<\/p>
\u8bf4\u660e\uff1ahttps:\/\/www.cnblogs.com\/yycc\/p\/7338894.html<\/p>
<\/p>
<\/p>
\u9488\u5bf9max\u8fdb\u884c\u4f18\u5316\uff1a<\/p>
\u5bf9max\u4f5c\u7528\u7684\u5b57\u6bb5\u52a0\u7d22\u5f15\uff1a\u8fd9\u6837\u5c31\u4f1a\u5168\u8868\u67e5\u8be2\uff0c\u800c\u662f\u76f4\u63a5\u901a\u8fc7\u7d22\u5f15\u627e\u5230\u6700\u5927\u7684\u90a3\u6761\uff0c\u901f\u5ea6\u5feb\u5f88\u591a\uff1b<\/p>
count(*)\u4e0ecount(\u5b57\u6bb5)\u7684\u503c\u53ef\u80fd\u4e0d\u4e00\u81f4\uff0ccount(*)\u5305\u542bnull,count(\u5b57\u6bb5)\u4e0d\u5305\u542bnull<\/p>
\u5b50\u67e5\u8be2\u4e2ddistinct\u53bb\u91cd\u590d\u503c<\/p>
order by \u80fd\u7528\u4e3b\u952e\u5c3d\u91cf\u7528\u4e3b\u952e<\/p>
\u7d22\u5f15\u7684\u5efa\u7acb\u4e0a\uff0c\u5c06\u79bb\u6563\u5ea6\u9ad8\u7684\u5b57\u6bb5\u653e\u524d\u9762\uff0c\u4e0d\u592a\u6070\u5f53\u7684\u4f8b\u5b50\uff0c\u5982\uff1a<\/p>
\u5047\u5982\u6709\u4e00\u4e2a\u8be6\u60c5\u5230\u8857\u9053\u7684\u4e2d\u56fd\u57ce\u5e02\u8868\uff0c\u5982\u679ccity_id\u653e\u524d\u9762\u5219\u67e5\u8be2\u5f71\u54cd\u7684\u884c\u4f1a\u627e\u51facity_id\u5bf9\u5e94\u6240\u6709\u7684\u8857\u9053\u518d\u5728\u91cc\u9762\u627etown_id\u7684\uff0c\u8fd9\u6837\u6d89\u53ca\u7684\u884c\u6570\u591a\uff1b\u53cd\u4e4b\u76f8\u5bf9\u5c11\u4e00\u4e9b<\/p>
index(town_id_city_id) \u597d\u8fc7index(city_id_town_id);<\/p>
<\/p>
\u914d\u7f6e\u4f18\u5316\uff1a<\/p>
https:\/\/tools.percona.com\/wizard<\/p>
<\/p>
\u5206\u7247\uff1a<\/p>
\u5168\u5c40\u552f\u4e00id\u95ee\u9898<\/p>
auto_increment_offset\u8981\u4e0e\u5206\u7247\u4e2a\u6570\u76f8\u540c<\/p>
\u6216\u7528redis\u521b\u5efa\u5168\u5c40id<\/p>
<\/p>"}