MySQL的基本优化

1、查询执行速度慢的sql语句

  • 查看当前“慢语句”标准:show variables like ‘%long%’
    mysql> show variables like '%long_query_time%';
    +-----------------+----------+
    | Variable_name   | Value    |
    +-----------------+----------+
    | long_query_time | 10.00000 |
    +-----------------+----------+
    1 row in set (0.00 sec)
    
    mysql> show variables like '%slow%';
    +---------------------------+--------------------------------+
    | Variable_name             | Value                          |
    +---------------------------+--------------------------------+
    | log_slow_admin_statements | OFF                            |
    | log_slow_slave_statements | OFF                            |
    | slow_launch_time          | 2                              |
    | slow_query_log            | ON                             |
    | slow_query_log_file       | /var/lib/mysql/ubuntu-slow.log |
    +---------------------------+--------------------------------+
    5 rows in set (0.00 sec)
    
  • 开启日志:set global slow_query_log = on;
  • 修改时间标准:set long_query_time = 0.5;
  • slow_query_log_file为日志文件所在路径;
  • 查看日志

    root@ubuntu:/var/lib/mysql# cat ubuntu-slow.log
    /usr/sbin/mysqld, Version: 5.7.25-0ubuntu0.18.04.2 ((Ubuntu)). started with:
    Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock
    Time                 Id Command    Argument
    # Time: 2019-04-24T04:51:29.534395Z
    # User@Host: root[root] @ localhost []  Id:     2
    # Query_time: 0.000295  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
    use demo;
    SET timestamp=1556081489;
    set global slow_query_log = on;
    

2、慢语句详情

  • explain命令,使用explain命令可以查看到当前语句执行的详细情况;
    mysql> explain select * from user \G
    *************************** 1. row ***************************
             id: 1
    select_type: SIMPLE
          table: user
     partitions: NULL
           type: ALL
    possible_keys: NULL
            key: NULL
        key_len: NULL
            ref: NULL
           rows: 7
       filtered: 100.00
          Extra: NULL
    1 row in set, 1 warning (0.00 sec)
    
  • profiles属性:
    mysql> show variables like '%profiling%';
    +------------------------+-------+
    | Variable_name          | Value |
    +------------------------+-------+
    | have_profiling         | YES   |
    | profiling              | OFF   |
    | profiling_history_size | 15    |
    +------------------------+-------+
    3 rows in set (0.01 sec)
    
    mysql> set profiling = on;
    Query OK, 0 rows affected, 1 warning (0.00 sec)
    
    mysql> show variables like '%profil%';
    +------------------------+-------+
    | Variable_name          | Value |
    +------------------------+-------+
    | have_profiling         | YES   |
    | profiling              | ON    |
    | profiling_history_size | 15    |
    +------------------------+-------+
    3 rows in set (0.00 sec)
    
    mysql> show profiles;
    +----------+------------+--------------------------------+
    | Query_ID | Duration   | Query                          |
    +----------+------------+--------------------------------+
    |        1 | 0.00293625 | show variables like '%profil%' |
    +----------+------------+--------------------------------+
    1 row in set, 1 warning (0.00 sec)
    
    mysql> select * from user;
    +----+-----------+-----+----------------------------------+-----------------+---------+---------+-----+---------------------+
    | id | user_name | sex | password                         | email           | address | fee     | age | create_at           |
    +----+-----------+-----+----------------------------------+-----------------+---------+---------+-----+---------------------+
    |  1 | sian      | 男  | e10adc3949ba59abbe56e057f20f883e | yusian@163.com  | NULL    | 1200.00 |  32 | 2019-04-23 15:57:29 |
    |  2 | 余西安    | 男  | c4ca4238a0b923820dcc509a6f75849b | yusian@163.com  | NULL    | 1002.00 |  33 | 2019-04-23 21:00:03 |
    |  3 | Jack      | 男  | c81e728d9d4c2f636f067f89cc14862c | jack@yusian.com | NULL    |    0.00 |  22 | 2019-04-21 20:11:59 |
    |  4 | Rose      | 女  | eccbc87e4b5ce2fe28308fd9f2a7baf3 | rose@yusian.com | NULL    |    0.00 |  18 | 2019-04-22 10:46:41 |
    |  5 | Sim       | 女  | a87ff679a2f3e71d9181a67b7542122c | sim@yusian.com  | NULL    |    0.00 |  28 | 2019-04-22 10:46:41 |
    |  6 | Tony      | 男  | c4ca4238a0b923820dcc509a6f75849b | tony@yusian.com | NULL    |    0.00 |  22 | 2019-04-21 20:41:26 |
    |  7 | lucy      | 男  | c81e728d9d4c2f636f067f89cc14862c | lucy@163.com    | NULL    |    0.00 |   0 | 2019-04-23 20:13:01 |
    +----+-----------+-----+----------------------------------+-----------------+---------+---------+-----+---------------------+
    7 rows in set (0.00 sec)
    
    mysql> show profiles;
    +----------+------------+--------------------------------+
    | Query_ID | Duration   | Query                          |
    +----------+------------+--------------------------------+
    |        1 | 0.00293625 | show variables like '%profil%' |
    |        2 | 0.00036200 | select * from user             |
    +----------+------------+--------------------------------+
    2 rows in set, 1 warning (0.00 sec)
    
    mysql> show profile for query 2;
    +----------------------+----------+
    | Status               | Duration |
    +----------------------+----------+
    | starting             | 0.000142 |
    | checking permissions | 0.000011 |
    | Opening tables       | 0.000015 |
    | init                 | 0.000020 |
    | System lock          | 0.000010 |
    | optimizing           | 0.000004 |
    | statistics           | 0.000013 |
    | preparing            | 0.000009 |
    | executing            | 0.000003 |
    | Sending data         | 0.000091 |
    | end                  | 0.000005 |
    | query end            | 0.000007 |
    | closing tables       | 0.000006 |
    | freeing items        | 0.000016 |
    | cleaning up          | 0.000012 |
    +----------------------+----------+
    15 rows in set, 1 warning (0.00 sec)
    

3、查询语句慢,可以通过加索引的方式解决;

  • alter table xxx add index(xxxx);
mysql> explain select * from user where age = 32;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    7 |    14.29 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

mysql> alter table user add index(age);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from user where age = 32;
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | ref  | age           | age  | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

4、缓存

  • 查询缓存开关:query_cache_type
  • show variables like ‘%cache%’可以查看该值
  • set global query_cache_type = ON; 打开,或在配置文件中加query_cache_type = ON;重启mysql服务
    mysql> show variables like '%cache%';
    +--------------------------------+----------------------+
    | Variable_name                  | Value                |
    +--------------------------------+----------------------+
    | binlog_cache_size              | 32768                |
    | binlog_stmt_cache_size         | 32768                |
    | have_query_cache               | YES                  |
    | host_cache_size                | 279                  |
    | innodb_disable_sort_file_cache | OFF                  |
    | innodb_ft_cache_size           | 8000000              |
    | innodb_ft_result_cache_limit   | 2000000000           |
    | innodb_ft_total_cache_size     | 640000000            |
    | key_cache_age_threshold        | 300                  |
    | key_cache_block_size           | 1024                 |
    | key_cache_division_limit       | 100                  |
    | max_binlog_cache_size          | 18446744073709547520 |
    | max_binlog_stmt_cache_size     | 18446744073709547520 |
    | metadata_locks_cache_size      | 1024                 |
    | query_cache_limit              | 1048576              |
    | query_cache_min_res_unit       | 4096                 |
    | query_cache_size               | 16777216             |
    | query_cache_type               | OFF                  |
    | query_cache_wlock_invalidate   | OFF                  |
    | stored_program_cache           | 256                  |
    | table_definition_cache         | 1400                 |
    | table_open_cache               | 2000                 |
    | table_open_cache_instances     | 16                   |
    | thread_cache_size              | 8                    |
    +--------------------------------+----------------------+
    24 rows in set (0.00 sec)
    
  • 缓存命中数,Qcache_hits
  • show status like ‘%cache%’
    mysql> show status like '%cache%';
    +--------------------------------+----------+
    | Variable_name                  | Value    |
    +--------------------------------+----------+
    | Binlog_cache_disk_use          | 0        |
    | Binlog_cache_use               | 0        |
    | Binlog_stmt_cache_disk_use     | 0        |
    | Binlog_stmt_cache_use          | 0        |
    | Com_assign_to_keycache         | 0        |
    | Qcache_free_blocks             | 1        |
    | Qcache_free_memory             | 16760152 |
    | Qcache_hits                    | 0        |
    | Qcache_inserts                 | 0        |
    | Qcache_lowmem_prunes           | 0        |
    | Qcache_not_cached              | 146      |
    | Qcache_queries_in_cache        | 0        |
    | Qcache_total_blocks            | 1        |
    | Ssl_callback_cache_hits        | 0        |
    | Ssl_session_cache_hits         | 0        |
    | Ssl_session_cache_misses       | 0        |
    | Ssl_session_cache_mode         | NONE     |
    | Ssl_session_cache_overflows    | 0        |
    | Ssl_session_cache_size         | 0        |
    | Ssl_session_cache_timeouts     | 0        |
    | Ssl_used_session_cache_entries | 0        |
    | Table_open_cache_hits          | 213      |
    | Table_open_cache_misses        | 42       |
    | Table_open_cache_overflows     | 0        |
    | Threads_cached                 | 0        |
    +--------------------------------+----------+
    25 rows in set (0.01 sec)
    

Leave a Reply