{"id":1913,"date":"2019-04-24T15:02:12","date_gmt":"2019-04-24T07:02:12","guid":{"rendered":"https:\/\/www.yusian.com\/blog\/?p=1913"},"modified":"2020-12-02T15:08:28","modified_gmt":"2020-12-02T07:08:28","slug":"mysql%e7%9a%84%e5%9f%ba%e6%9c%ac%e4%bc%98%e5%8c%96","status":"publish","type":"post","link":"https:\/\/www.yusian.com\/blog\/database\/2019\/04\/24\/1502121913.html","title":{"rendered":"MySQL\u7684\u57fa\u672c\u4f18\u5316"},"content":{"rendered":"<h3>1\u3001\u67e5\u8be2\u6267\u884c\u901f\u5ea6\u6162\u7684sql\u8bed\u53e5<\/h3>\n<ul>\n<li>\u67e5\u770b\u5f53\u524d\u201c\u6162\u8bed\u53e5\u201d\u6807\u51c6\uff1ashow variables like \u2018%long%&#8217;\n<pre><code class=\"language-sql line-numbers\">mysql&gt; show variables like '%long_query_time%';\n+-----------------+----------+\n| Variable_name   | Value    |\n+-----------------+----------+\n| long_query_time | 10.00000 |\n+-----------------+----------+\n1 row in set (0.00 sec)\n\nmysql&gt; show variables like '%slow%';\n+---------------------------+--------------------------------+\n| Variable_name             | Value                          |\n+---------------------------+--------------------------------+\n| log_slow_admin_statements | OFF                            |\n| log_slow_slave_statements | OFF                            |\n| slow_launch_time          | 2                              |\n| slow_query_log            | ON                             |\n| slow_query_log_file       | \/var\/lib\/mysql\/ubuntu-slow.log |\n+---------------------------+--------------------------------+\n5 rows in set (0.00 sec)\n<\/code><\/pre>\n<\/li>\n<li>\u5f00\u542f\u65e5\u5fd7\uff1aset global slow_query_log = on;<\/li>\n<li>\u4fee\u6539\u65f6\u95f4\u6807\u51c6\uff1aset long_query_time = 0.5;<\/li>\n<li>slow_query_log_file\u4e3a\u65e5\u5fd7\u6587\u4ef6\u6240\u5728\u8def\u5f84\uff1b<\/li>\n<li>\u67e5\u770b\u65e5\u5fd7<br \/>\n<!--more--><\/p>\n<pre><code class=\"language-sql line-numbers\">root@ubuntu:\/var\/lib\/mysql# cat ubuntu-slow.log\n\/usr\/sbin\/mysqld, Version: 5.7.25-0ubuntu0.18.04.2 ((Ubuntu)). started with:\nTcp port: 3306  Unix socket: \/var\/run\/mysqld\/mysqld.sock\nTime                 Id Command    Argument\n# Time: 2019-04-24T04:51:29.534395Z\n# User@Host: root[root] @ localhost []  Id:     2\n# Query_time: 0.000295  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0\nuse demo;\nSET timestamp=1556081489;\nset global slow_query_log = on;\n<\/code><\/pre>\n<\/li>\n<\/ul>\n<h3>2\u3001\u6162\u8bed\u53e5\u8be6\u60c5<\/h3>\n<ul>\n<li>explain\u547d\u4ee4\uff0c\u4f7f\u7528explain\u547d\u4ee4\u53ef\u4ee5\u67e5\u770b\u5230\u5f53\u524d\u8bed\u53e5\u6267\u884c\u7684\u8be6\u7ec6\u60c5\u51b5\uff1b\n<pre><code class=\"language-sql line-numbers\">mysql&gt; explain select * from user \\G\n*************************** 1. row ***************************\n         id: 1\nselect_type: SIMPLE\n      table: user\n partitions: NULL\n       type: ALL\npossible_keys: NULL\n        key: NULL\n    key_len: NULL\n        ref: NULL\n       rows: 7\n   filtered: 100.00\n      Extra: NULL\n1 row in set, 1 warning (0.00 sec)\n<\/code><\/pre>\n<\/li>\n<li>profiles\u5c5e\u6027\uff1a\n<pre><code class=\"language-sql line-numbers\">mysql&gt; show variables like '%profiling%';\n+------------------------+-------+\n| Variable_name          | Value |\n+------------------------+-------+\n| have_profiling         | YES   |\n| profiling              | OFF   |\n| profiling_history_size | 15    |\n+------------------------+-------+\n3 rows in set (0.01 sec)\n\nmysql&gt; set profiling = on;\nQuery OK, 0 rows affected, 1 warning (0.00 sec)\n\nmysql&gt; show variables like '%profil%';\n+------------------------+-------+\n| Variable_name          | Value |\n+------------------------+-------+\n| have_profiling         | YES   |\n| profiling              | ON    |\n| profiling_history_size | 15    |\n+------------------------+-------+\n3 rows in set (0.00 sec)\n\nmysql&gt; show profiles;\n+----------+------------+--------------------------------+\n| Query_ID | Duration   | Query                          |\n+----------+------------+--------------------------------+\n|        1 | 0.00293625 | show variables like '%profil%' |\n+----------+------------+--------------------------------+\n1 row in set, 1 warning (0.00 sec)\n\nmysql&gt; select * from user;\n+----+-----------+-----+----------------------------------+-----------------+---------+---------+-----+---------------------+\n| id | user_name | sex | password                         | email           | address | fee     | age | create_at           |\n+----+-----------+-----+----------------------------------+-----------------+---------+---------+-----+---------------------+\n|  1 | sian      | \u7537  | e10adc3949ba59abbe56e057f20f883e | yusian@163.com  | NULL    | 1200.00 |  32 | 2019-04-23 15:57:29 |\n|  2 | \u4f59\u897f\u5b89    | \u7537  | c4ca4238a0b923820dcc509a6f75849b | yusian@163.com  | NULL    | 1002.00 |  33 | 2019-04-23 21:00:03 |\n|  3 | Jack      | \u7537  | c81e728d9d4c2f636f067f89cc14862c | jack@yusian.com | NULL    |    0.00 |  22 | 2019-04-21 20:11:59 |\n|  4 | Rose      | \u5973  | eccbc87e4b5ce2fe28308fd9f2a7baf3 | rose@yusian.com | NULL    |    0.00 |  18 | 2019-04-22 10:46:41 |\n|  5 | Sim       | \u5973  | a87ff679a2f3e71d9181a67b7542122c | sim@yusian.com  | NULL    |    0.00 |  28 | 2019-04-22 10:46:41 |\n|  6 | Tony      | \u7537  | c4ca4238a0b923820dcc509a6f75849b | tony@yusian.com | NULL    |    0.00 |  22 | 2019-04-21 20:41:26 |\n|  7 | lucy      | \u7537  | c81e728d9d4c2f636f067f89cc14862c | lucy@163.com    | NULL    |    0.00 |   0 | 2019-04-23 20:13:01 |\n+----+-----------+-----+----------------------------------+-----------------+---------+---------+-----+---------------------+\n7 rows in set (0.00 sec)\n\nmysql&gt; show profiles;\n+----------+------------+--------------------------------+\n| Query_ID | Duration   | Query                          |\n+----------+------------+--------------------------------+\n|        1 | 0.00293625 | show variables like '%profil%' |\n|        2 | 0.00036200 | select * from user             |\n+----------+------------+--------------------------------+\n2 rows in set, 1 warning (0.00 sec)\n\nmysql&gt; show profile for query 2;\n+----------------------+----------+\n| Status               | Duration |\n+----------------------+----------+\n| starting             | 0.000142 |\n| checking permissions | 0.000011 |\n| Opening tables       | 0.000015 |\n| init                 | 0.000020 |\n| System lock          | 0.000010 |\n| optimizing           | 0.000004 |\n| statistics           | 0.000013 |\n| preparing            | 0.000009 |\n| executing            | 0.000003 |\n| Sending data         | 0.000091 |\n| end                  | 0.000005 |\n| query end            | 0.000007 |\n| closing tables       | 0.000006 |\n| freeing items        | 0.000016 |\n| cleaning up          | 0.000012 |\n+----------------------+----------+\n15 rows in set, 1 warning (0.00 sec)\n<\/code><\/pre>\n<\/li>\n<\/ul>\n<h3>3\u3001\u67e5\u8be2\u8bed\u53e5\u6162\uff0c\u53ef\u4ee5\u901a\u8fc7\u52a0\u7d22\u5f15\u7684\u65b9\u5f0f\u89e3\u51b3\uff1b<\/h3>\n<ul>\n<li>alter table xxx add index(xxxx);<\/li>\n<\/ul>\n<pre><code class=\"language-sql line-numbers\">mysql&gt; explain select * from user where age = 32;\n+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+\n| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |\n+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+\n|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    7 |    14.29 | Using where |\n+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+\n1 row in set, 1 warning (0.01 sec)\n\nmysql&gt; alter table user add index(age);\nQuery OK, 0 rows affected (0.02 sec)\nRecords: 0  Duplicates: 0  Warnings: 0\n\nmysql&gt; explain select * from user where age = 32;\n+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+\n| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |\n+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+\n|  1 | SIMPLE      | user  | NULL       | ref  | age           | age  | 4       | const |    1 |   100.00 | NULL  |\n+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+\n1 row in set, 1 warning (0.00 sec)\n<\/code><\/pre>\n<h3>4\u3001\u7f13\u5b58<\/h3>\n<ul>\n<li>\u67e5\u8be2\u7f13\u5b58\u5f00\u5173\uff1aquery_cache_type<\/li>\n<li>show variables like \u2018%cache%\u2019\u53ef\u4ee5\u67e5\u770b\u8be5\u503c<\/li>\n<li>set global query_cache_type = ON; \u6253\u5f00\uff0c\u6216\u5728\u914d\u7f6e\u6587\u4ef6\u4e2d\u52a0query_cache_type = ON\uff1b\u91cd\u542fmysql\u670d\u52a1\n<pre><code class=\"language-sql line-numbers\">mysql&gt; show variables like '%cache%';\n+--------------------------------+----------------------+\n| Variable_name                  | Value                |\n+--------------------------------+----------------------+\n| binlog_cache_size              | 32768                |\n| binlog_stmt_cache_size         | 32768                |\n| have_query_cache               | YES                  |\n| host_cache_size                | 279                  |\n| innodb_disable_sort_file_cache | OFF                  |\n| innodb_ft_cache_size           | 8000000              |\n| innodb_ft_result_cache_limit   | 2000000000           |\n| innodb_ft_total_cache_size     | 640000000            |\n| key_cache_age_threshold        | 300                  |\n| key_cache_block_size           | 1024                 |\n| key_cache_division_limit       | 100                  |\n| max_binlog_cache_size          | 18446744073709547520 |\n| max_binlog_stmt_cache_size     | 18446744073709547520 |\n| metadata_locks_cache_size      | 1024                 |\n| query_cache_limit              | 1048576              |\n| query_cache_min_res_unit       | 4096                 |\n| query_cache_size               | 16777216             |\n| query_cache_type               | OFF                  |\n| query_cache_wlock_invalidate   | OFF                  |\n| stored_program_cache           | 256                  |\n| table_definition_cache         | 1400                 |\n| table_open_cache               | 2000                 |\n| table_open_cache_instances     | 16                   |\n| thread_cache_size              | 8                    |\n+--------------------------------+----------------------+\n24 rows in set (0.00 sec)\n<\/code><\/pre>\n<\/li>\n<li>\u7f13\u5b58\u547d\u4e2d\u6570\uff0cQcache_hits<\/li>\n<li>show status like \u2018%cache%&#8217;\n<pre><code class=\"language-sql line-numbers\">mysql&gt; show status like '%cache%';\n+--------------------------------+----------+\n| Variable_name                  | Value    |\n+--------------------------------+----------+\n| Binlog_cache_disk_use          | 0        |\n| Binlog_cache_use               | 0        |\n| Binlog_stmt_cache_disk_use     | 0        |\n| Binlog_stmt_cache_use          | 0        |\n| Com_assign_to_keycache         | 0        |\n| Qcache_free_blocks             | 1        |\n| Qcache_free_memory             | 16760152 |\n| Qcache_hits                    | 0        |\n| Qcache_inserts                 | 0        |\n| Qcache_lowmem_prunes           | 0        |\n| Qcache_not_cached              | 146      |\n| Qcache_queries_in_cache        | 0        |\n| Qcache_total_blocks            | 1        |\n| Ssl_callback_cache_hits        | 0        |\n| Ssl_session_cache_hits         | 0        |\n| Ssl_session_cache_misses       | 0        |\n| Ssl_session_cache_mode         | NONE     |\n| Ssl_session_cache_overflows    | 0        |\n| Ssl_session_cache_size         | 0        |\n| Ssl_session_cache_timeouts     | 0        |\n| Ssl_used_session_cache_entries | 0        |\n| Table_open_cache_hits          | 213      |\n| Table_open_cache_misses        | 42       |\n| Table_open_cache_overflows     | 0        |\n| Threads_cached                 | 0        |\n+--------------------------------+----------+\n25 rows in set (0.01 sec)\n<\/code><\/pre>\n<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>1\u3001\u67e5\u8be2\u6267\u884c\u901f\u5ea6\u6162\u7684sql\u8bed\u53e5 \u67e5\u770b\u5f53\u524d\u201c\u6162\u8bed\u53e5\u201d\u6807\u51c6\uff1ashow variables like \u2018%long%&#8217; mysql&gt; show variables like &#8216;%long_query_time%&#8217;; +&#8212;&#8212;&#8212;&#8212;&#8212;&#8211;+&#8212;&#8212;&#8212;-+ | Variable_name | Value | +&#8212;&#8212;&#8212;&#8212;&#8212;&#8211;+&#8212;&#8212;&#8212;-+ | long_query_time | 10.00000 | +&#8212;&#8212;&#8212;&#8212;&#8212;&#8211;+&#8212;&#8212;&#8212;-+ 1 row in set (0.00 sec) mysql&gt; show variables like &#8216;%slow%&#8217;; +&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;+&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8211;+ | Variable_name | Value | +&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;+&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8211;+ | log_slow_admin_statements | OFF | | log_slow_slave_statements | OFF | | slow_launch_time | 2 [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[282],"tags":[327,22,335,108],"class_list":["post-1913","post","type-post","status-publish","format-standard","hentry","category-database","tag-database","tag-mysql","tag-335","tag-108"],"_links":{"self":[{"href":"https:\/\/www.yusian.com\/blog\/wp-json\/wp\/v2\/posts\/1913","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.yusian.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.yusian.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.yusian.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.yusian.com\/blog\/wp-json\/wp\/v2\/comments?post=1913"}],"version-history":[{"count":0,"href":"https:\/\/www.yusian.com\/blog\/wp-json\/wp\/v2\/posts\/1913\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.yusian.com\/blog\/wp-json\/wp\/v2\/media?parent=1913"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.yusian.com\/blog\/wp-json\/wp\/v2\/categories?post=1913"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.yusian.com\/blog\/wp-json\/wp\/v2\/tags?post=1913"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}