文档章节

mysql 优化步骤

nao
 nao
发布于 2015/12/15 12:08
字数 1956
阅读 330
收藏 24

优化SQL语句的一般步骤:
1. 通过 show status 命令了解各种SQL的执行效率。

show status like 'com_%'

        Com_xxx表示每个xxx语句执行的次数,我们通常比较关心的是一下几个参数:
            Com_select: 执行SELECT 操作的次数,一次查询累加1
            Com_insert: 执行INSERT 操作的次数,对于批量插入的INSERT操作,只累加一次。
            Com_updata: 执行UPDATA 操作的次数。
            Com_delete: 执行DELETE 操作的次数。
        可以很容易地了解当前数据库的应用是以插入更新为主还是以查询操作为主,以及各种
        数据类型的SQL大致的执行比例是多少。对于更新操作的计数,是对执行次数的计数,
        不是提交还是回滚都会进行累加。
        对于事务型的应用,通过Com_commit和Com_rollback 可以了解事务提交和回滚的情况,
        对于回滚操作非常频繁的数据库,可意味着应用编写存在问题。
        此外,以下几个参数便于用户了解数据库的基本情况。
        connections:  试图连接mysql服务器的次数。
        Uptime:          服务器工作时间。
        Slow_queries: 慢查询的次数。 


  2. 定位执行效率较低的SQL语句
      1)通过慢查询日志定位那些执行效率较低的SQL语句.
            慢查询日志记录了所有执行时间超过参数long_query_time(单位:秒)设置值并且扫描记录数不小于
            min_examined_row_limit的所有SQL语句的日志(注意:获取表锁定的时间不算作执行时间)。
            long_query_time默认为10秒,最小为0,精确可以到毫秒。

            查看慢日志存放的位置:      

mysql> show variables like 'slow_query_log_file';
            +---------------------+-----------------------------------+
            | Variable_name       | Value                             |
            +---------------------+-----------------------------------+
            | slow_query_log_file | /var/lib/mysql/localhost-slow.log |
            +---------------------+-----------------------------------+
            1 row in set (0.00 sec)

        开启慢查询:

mysql> set global slow_query_log = "ON"; 
            Query OK, 0 rows affected (0.04 sec)

        查看慢查询是否开启:

mysql> show variables like 'slow_query%'; 
            +---------------------+-----------------------------------+
            | Variable_name       | Value                             |
            +---------------------+-----------------------------------+
            | slow_query_log      | ON                                |
            | slow_query_log_file | /var/lib/mysql/localhost-slow.log |
            +---------------------+-----------------------------------+
            2 rows in set (0.00 sec)

        查询一下long_query_time的值

mysql> show variables like "long%";
            +-----------------+-----------+
            | Variable_name   | Value     |
            +-----------------+-----------+
            | long_query_time | 10.000000 |
            +-----------------+-----------+
            1 row in set (0.00 sec)

        为了方便测试,将修改慢查询时间为0.1秒

mysql> set long_query_time=0.1;         
            Query OK, 0 rows affected (0.00 sec)

            mysql> show variables like "long%";
            +-----------------+----------+
            | Variable_name   | Value    |
            +-----------------+----------+
            | long_query_time | 0.100000 |
            +-----------------+----------+
            1 row in set (0.00 sec)

        执行下面的语句:

mysql> select sleep(2);
            +----------+
            | sleep(2) |
            +----------+
            |        0 |
            +----------+
            1 row in set (2.00 sec)

        查看日志文件:

[root@localhost mysql]# vim localhost-slow.log 

            /usr/sbin/mysqld, Version: 5.6.20 (MySQL Community Server (GPL)). started with:
            Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
            Time                 Id Command    Argument
            # Time: 151216 19:00:33
            # User@Host: root[root] @ localhost []  Id:     5
            # Query_time: 2.000178  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
            use nms_db;
            SET timestamp=1450263633;
            select sleep(2);

  2)慢查询日志在查询结束以后才记录,所有在应用反映执行效率出现问题的时候查询慢查询日志并
          不能定位问题,可以使用show processlist 命令查看当前MySQL 在进行的线程,包括线程的状态,
          是否锁表等,可以实时的查看SQL的执行情况,同时对一些锁表操作进行优化。



3.通过Explain分析执行低效SQL的执行计划
        通过以上步骤查询到低效的SQL语句后,可以通过EXPLAIN命令获取MYSQL执行的SELECT 语句的信息,
        包括在SELECT 语句执行过程中表如何连接和连接的顺序。

   explain select count(*) from warning_repaired\G;
        *************************** 1. row ***************************
                   id: 1
          select_type: SIMPLE      
                table: warning_repaired
                 type: index
        possible_keys: NULL
                  key: id
              key_len: 4
                  ref: NULL
                 rows: 483078
                Extra: Using index
        1 row in set (0.00 sec)

       select_type: 表示SELECT的类型,常见的取值有SIMPLE(简单表,即不使用表连接或者子查询)
                     PRLMARY(主查询,即外层的查询), UNION(UNION 中的第二个或者后面的查询语句)
                     SUBQUERY(子查询中的第一个SELECT等)
                    
        table:     输入结果集的表

        type:       表示MYSQL 在表中找到所需行的方式,或者访问类型,常见的类型如下:
                        ALL, index, range, ref, eq_ref, const,system, NULL
                        从左到右,性能由最差到最好。

            (1) type = ALL,全表扫描,MySQL遍历全表来找到匹配的行
            (2) type = index, 索引全扫描,MySQL遍历整个索引来查询匹配的行
            (3) type = range, 索引范围扫描,常见于<, <=, >, >=, between等操作符
            (4) type = ref, 使用非唯一索引扫描或唯一索引的前缀扫描,返回匹配某个单独值的记录行
            (5) type = eq_ref,
            (6) type = const/system,单表中最多有一个匹配行,查询起来非常迅速,所以这个匹配行中的其他
                列的值可以被优化器在当前查询中当作常量来处理。
            (7) type = NULL, MySQL不用访问表或者索引,直接就能够得到结果。

        possible_keys: 表示查询时可能使用的索引
        key:     表示实际使用的索引
        key_len:  使用到索引字段的长度
        rows:     扫描行的数量
        Extra:   执行情况的说明和描述,包含不适合在其他列中显示但是对执行计划非常重要的额外信息


 4. 通过show profile分析SQL
        查看当前MySQL是否支持profile:

 mysql> select @@have_profiling;
        +------------------+
        | @@have_profiling |
        +------------------+
        | YES              |
        +------------------+
        1 row in set, 1 warning (0.02 sec)

        默认profiling是关闭的,可以通过set语句在Session级别开启profiling:   

mysql> select @@profiling;
        +-------------+
        | @@profiling |
        +-------------+
        |           0 |
        +-------------+
        1 row in set, 1 warning (0.00 sec)

        mysql> set profiling=1;
        Query OK, 0 rows affected, 1 warning (0.00 sec)

        mysql> select @@profiling;
        +-------------+
        | @@profiling |
        +-------------+
        |           1 |
        +-------------+
        1 row in set, 1 warning (0.00 sec)

        查看执行时间:

 mysql> show profiles;
        +----------+------------+---------------------------------------+
        | Query_ID | Duration   | Query                                 |
        +----------+------------+---------------------------------------+
        |        1 | 0.00017075 | select @@profiling                    |
        |        2 | 0.00023175 | show create table warning_repaired    |
        |        3 | 0.12900950 | select count(*) from warning_repaired |
        +----------+------------+---------------------------------------+
        3 rows in set, 1 warning (0.00 sec)

        通过show profile query 语句能够看到执行过程中线程的每个状态和消耗的时间:

 mysql> show profile for query 3;
        +----------------------+----------+
        | Status               | Duration |
        +----------------------+----------+
        | starting             | 0.000065 |
        | checking permissions | 0.000009 |
        | Opening tables       | 0.000026 |
        | init                 | 0.000022 |
        | System lock          | 0.000011 |
        | optimizing           | 0.000010 |
        | statistics           | 0.000028 |
        | preparing            | 0.000019 |
        | executing            | 0.000004 |
        | Sending data         | 0.128745 |
        | end                  | 0.000013 |
        | query end            | 0.000009 |
        | closing tables       | 0.000015 |
        | freeing items        | 0.000022 |
        | cleaning up          | 0.000014 |
        +----------------------+----------+
        15 rows in set, 1 warning (0.00 sec)

        注意: Sending data 状态表示MySQL线程开启访问数据行并把结果返回给客户端,而不仅仅是返回
        结果给客户端,由于在Sending data 状态下,MySQL线程往往需要做大量的磁盘读取操作,所以经常
        是整个查询中耗时最长的状态。
        
        对比MyISAM 表的count(*) 操作,创建一个同样的表结构的MyISAM表,数据量也完全一致:

 mysql> show profiles;
        +----------+------------+-------------------------------------------------------------------+
        | Query_ID | Duration   | Query                                                             |
        +----------+------------+-------------------------------------------------------------------+
        |        1 | 0.00017075 | select @@profiling                                                |
        |        2 | 0.00023175 | show create table warning_repaired                                |
        |        3 | 0.12900950 | select count(*) from warning_repaired                             |
        |        4 | 0.66972975 | create table waring_repaired_myisam like warning_repaired         |
        |        5 | 0.00040075 | show tables                                                       |
        |        6 | 0.00061900 | alter table warning_repaired_myisam engine=myisam                 |
        |        7 | 0.26157325 | alter table waring_repaired_myisam engine=myisam                  |
        |        8 | 0.00024950 | select count(*) from waring_reparied_myisam                       |
        |        9 | 0.01772475 | show create table waring_repaired_myisam                          |
        |       10 | 0.00021850 | select count(*) from waring_repaired_myisam                       |
        |       11 | 0.00028175 | insert into waring_repaired_myisam select * from payment          |
        |       12 | 4.61870225 | insert into waring_repaired_myisam select * from warning_repaired |
        |       13 | 0.12892775 | select count(*) from warning_repaired                             |
        |       14 | 0.00021225 | select count(*) from waring_repaired_myisam                       |
        +----------+------------+-------------------------------------------------------------------+
        
        mysql> show profile for query 14;
        +----------------------+----------+
        | Status               | Duration |
        +----------------------+----------+
        | starting             | 0.000067 |
        | checking permissions | 0.000009 |
        | Opening tables       | 0.000026 |
        | init                 | 0.000019 |
        | System lock          | 0.000013 |
        | optimizing           | 0.000011 |
        | executing            | 0.000012 |
        | end                  | 0.000006 |
        | query end            | 0.000004 |
        | closing tables       | 0.000013 |
        | freeing items        | 0.000017 |
        | cleaning up          | 0.000016 |
        +----------------------+----------+
        12 rows in set, 1 warning (0.00 sec)

        mysql> show profile for query 13;
        +----------------------+----------+
        | Status               | Duration |
        +----------------------+----------+
        | starting             | 0.000066 |
        | checking permissions | 0.000010 |
        | Opening tables       | 0.000027 |
        | init                 | 0.000020 |
        | System lock          | 0.000012 |
        | optimizing           | 0.000008 |
        | statistics           | 0.000020 |
        | preparing            | 0.000018 |
        | executing            | 0.000004 |
        | Sending data         | 0.128666 |
        | end                  | 0.000018 |
        | query end            | 0.000010 |
        | closing tables       | 0.000016 |
        | freeing items        | 0.000021 |
        | cleaning up          | 0.000014 |
        +----------------------+----------+

         从profile的结果能够看出,InnoDB引擎的表在COUNT(*)时经历了Sending data 状态,
        存在访问数据的过程,而MyISAM引擎的表在executing之后直接就结束查询,完全不需要访问数据

        关于MyISAM的神话

            一个容易产生的误解是:MyISAM的COUNT()函数总是非常快,不过这是有前提条的,

           即只有没有任何where条件的COUNT(*) 才非常快,因为此时无需实际地去计算表的行数。MySQL可以利用存储引擎的特性直接获取这个值。如果  MySQL 某列col不可能为NULL值,那么MySQL内部会将COUNT(col) 表达式优化成COUNT(*)。


    5.通过 trace 分析优化器如何选择执行计划
        暂略

参考文章:

http://www.cnblogs.com/hongfei/archive/2012/10/20/2732516.html


© 著作权归作者所有

nao

nao

粉丝 28
博文 155
码字总数 108154
作品 0
成都
后端工程师
私信 提问
浅谈MySQL SQL优化

本文首发于个人微信公众号《andyqian》,期待你的关注 前言 有好几天没有写文章了,实在不好意思。之前就有朋友希望我写写MySQL优化的文章。我迟迟没有动笔,主要是因为,SQL优化这个东西,很...

andyqian
2018/01/30
27
2
优化的内存访问--TCMalloc

TCMalloc (google-perftools) 是用于优化C++写的多线程应用,比glibc 2.3的malloc快。这个模块可以用来让MySQL在高并发下内存占用更加稳定。 本站使用的MySQL已经用 TCMalloc 优化过了。 详细...

匿名
2008/09/14
30K
2
深入理解MySQL 5.7 GTID系列(十):实际案例二

导 读 作者:高鹏(重庆八怪) 原文地址: https://www.jianshu.com/p/2c25842d58d3 深入理解MySQL 5.7 GTID系列文章共十篇,本文为第四篇,点击查看: 第一篇:深入理解MySQL 5.7 GTID系列(...

老叶茶馆
2018/04/25
0
0
Spark:将DataFrame 写入mysql

DataFrame 写入mysql 效率问题 上面两段代码为DataFrame写入mysql关键源代码 一开始我觉得DataFrame写入mysql效率感人,太慢了,想了各种手段去优化,最快的是把文件拿下来,load进mysql,但...

利伊奥克儿
2018/10/12
0
0
magento性能优化策略大全

magento的网站实在是太慢了,想了好多办法,参考了好多资料,做了很多测试,总结一下大概的步骤如下 (继续) : 1、压缩js,css代码,如果有必要把所有的css,js分别综合到一个文件中,并压缩...

rio
2010/05/07
5.8K
3

没有更多内容

加载失败,请刷新页面

加载更多

状态模式

//相当把一个State对象存到Context对象中,然后通过Context实例化对象调用保存的state对象去调用state的相应的方法 https://blog.csdn.net/syc434432458/article/details/51210361...

南桥北木
11分钟前
0
0
基于 Jenkins + JaCoCo 实现功能测试代码覆盖率统计

本文首发于:Jenkins 中文社区 使用 JaCoCo 统计功能测试代码覆盖率? 对于 JaCoCo,有所了解但又不是很熟悉。 "有所了解"指的是在 CI 实践中已经使用 JaCoCo 对单元测试代码覆盖率统计: 当...

Jenkins中文社区
19分钟前
2
0
聊聊Elasticsearch的OsProbe

序 本文主要研究一下Elasticsearch的OsProbe OsProbe elasticsearch-7.0.1/server/src/main/java/org/elasticsearch/monitor/os/OsProbe.java public class OsProbe { private static f......

go4it
19分钟前
0
0
谈谈lucene的DocValues特性之NumericDocValuesField

在默认实现的DocValuesCosumer中,数值有可能分块存储也有可能放在一个数据块中存储。 分块的大小默认是16384,并且通过预先计算如果按一个块存储最大值与最小值的差所占用的比特数和分块存储...

FAT_mt
37分钟前
0
0
【BATJ】面试必问MySQL索引实现原理

BATJ面试题剖析 1、为什么需要使用索引? 2、数据结构Hash、平衡二叉树、B树、B+树区别? 3、机械硬盘、固态硬盘区别? 4、Myisam与Innodb B+树的区别? 5、MySQL中的索引什么数据结构? 6、...

须臾之余
今天
6
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部