sql优化

562 阅读7分钟

数据库优化呢有很多种,比如升级硬件,最直接的办法。但是sql优化是成本是最少的

基本的SQL优化的思路

1. 截取SQL语句

1.全面查询日志
  1. 启用方式

    1. 在mysql的配置文件中,设置如下:

      general_log=1
      
      general_log_file=/path/logfile
      
      log_output=FILE
      
    2. 也可以通过SQL语句来动态启用和禁用,还可以指定输出到表或者文件 :

      set global general_log=1;
      
      set global log_output='TABLE';
      
      select * from mysql.general_log;
      

2:注意

全面日志提供所有连续的sql查询语句顺序,但不提供执行时间,在开发环境开
启这个功能,可以很好的来审查SQL语句。但永远不要在生产环境开启这个功
能。
2.慢查询日志

1:启用方式

  1. 在mysql的配置文件中,设置如下:

        slow_query_log=1  无效(0或者OFF)、有效(1或者ON)
        slow_query_log_file=/usr/local/mysql/logs/slowlog   指定日志文件
        long_query_time=0.2  超过指定时间的SQL会记录到日志文件(默认时间为10秒)
        log_output=FILE
        日志存储方式。log_output='FILE'表示将日志存入文件,默认值是'FILE'。log_output='TABLE'表示将日志存入数据库,这样日志信息就会被写入到mysql.slow_log表中。MySQL数据<br>库支持同时两种日志存储方式,配置的时候以逗号隔开即可,如:log_output='FILE,TABLE'。日志记录到系统的专用日志表中,要比记录到文件耗费更多的系统资源,因此对于需要启用慢查询日志,又需<br>要能够获得更高的系统性能,那么建议优先记录到文件。
    
    1. 使用set global

       # mysql -u<用户名> -p <数据库名>
       Enter password: [输入密码]
       
       show variables like "%slow%";          //查看一下慢查询是不是已经开启
      
       set global slow_query_log = 1;
       set global slow_query_log_file = ‘/data/log/mysql/slow_query.log';
       set global long_query_time = 5;
       
       配置有效化
       
       重启MySQL或者set global以后,配置才会有效。
      
3. 进程列表

show full processlist;

        processlist命令的输出结果显示了有哪些线程在运行,可以帮助识别出有问题的查询语句,两种方式使用这个命令。

        1. id,不用说了吧,一个标识,你要kill一个语句的时候很有用。
        2. user列,显示单前用户,如果不是root,这个命令就只显示你权限范围内的sql语句。
        3. host列,显示这个语句是从哪个ip的哪个端口上发出的。呵呵,可以用来追踪出问题语句的用户。
        4. db列,显示这个进程目前连接的是哪个数据库。
        5. command列,显示当前连接的执行的命令,一般就是休眠(sleep),查询(query),连接(connect)。
        6. time列,此这个状态持续的时间,单位是秒。
        7. state列,显示使用当前连接的sql语句的状态,很重要的列,后续会有所有的状态的描述,请注意,state只是语句执行中的某一个状态,一个sql语句,以查询为例,可能需要经过copying to tmp table,Sorting result,Sending 
        8. info列,显示这个sql语句,因为长度有限,所以长的sql语句就显示不全,但是一个判断问题语句的重要依据。
4.引擎状态

show engine innodb status ,

可以提供SQL语句的详细信息,比如:导致外键 验证失败或者造成死锁的SQL语句。

5.其它

比如:开源项目sqlstats、MySql proxy等

2. 识别有问题的SQL语句

  • 通常关注

    1. 运行最慢的sql语句
    2. 运行较快,但是执行频率非常高的sql语句
  • 识别方法

    1. MySql自带的mysqldumpslow,(这个具体用法自行百度把)如:

      mysqldumpslow /usr/local/mysql/logs/slowlog

    2. 开源的pt-query-digest

       下载地址:https://www.percona.com/software/mysql-tools/percona-toolkit
       这个工具的输出结果有3个不同部分
       (1)第一个部分提供了总体时间、查询频率、硬件故障细节等信息
       (2)第二部分提供最差的不同查询的详细列表,按照总执行时间排
       (3)第三部分是执行时间的分布,以及具体的SQL语句
      

3. 确认语句执行

  • 通常关注

    1:环境
      应该尽可能跟出问题的环境一样或者相似,也就是要让问题得以重现。 这样也利于优化改进后进行验证,看看优化是否有效果。 
    2:时间统计
      采用同一个监控执行时间的标准,以利于发现问题,以及对比优化前后的效果
    

4. 语句分析

  • 常用的SQL语句分析命令
    1. :explain:分析语句执行基于开销的优化器,以及被优化器考虑的访问策略等,常见的有
      1. select_type:表示table列引用的使用方式的类型:
        • a:simple:不包含子查询和其它复杂语法的简单查询
        • b:primary:复杂查询中的最外层的表
        • c:derived:表不是物理表
        • d:dependent:子查询
      2. table:表名、表的别名或者一个为查询产生的临时表的标识符
      3. type:表使用的连接方式,常用的有:
        • a:const:这个表最多只有一行匹配
        • b:system:表只有一行
        • c:eq_ref:有一行是为了每个之前确定的表而读取的
        • d:ref:所有具有匹配值的行都被用到
        • e:range:所有符合给定范围值的索引行都被用到
        • f:all:全表扫描
      4. possible_keys:优化器为查询选定的索引
      5. key:优化器选择使用的索引
      6. key_len:用于Sql语句的连接条件的键的长度,对于确认索引的有效性,以及 多列索引中用到的列的数据很重要,常见的有:
        • key_len:4,表示int not null
        • key_len:5,表示int null
        • key_len:30,表示char(30) not null
        • key_len:32,表示varchar(30) not null
        • key_len:92,表示varchar(30) null
      7. ref:用来进行索引比较的列或者常量
      8. rows:优化器估计的所有存在于累计结果集中的行数目
      9. extra:优化器使用的额外信息
  1. show create table 表名

     展示表中列和索引定义的细节信息
    
  2. show indexes from 表名

    察看表的索引信息,其中的Cardinality表示索引中每一列唯一值的数量的 估计值 
    
  3. show table status like 表名

    察看表的底层大小以及表结构。对于innodb引擎,都是估计值。 
    
  4. show status命令

     察看服务器当前内部状态信息,
     比如:show global status like
     ‘handler_read%’,用来查看是否使用了索引,以及使用索引读取了多少值
    

6:show variables命令

        查看MySQL系统变量的当前值,比如:show variables like ‘%slow%’,用来查跟慢日志相关的变量的值    

5. 语句优化

  • 常用的SQL语句优化手段

         1:尽量去除表连接操作
         2:尽量减少操作到的列的数目
         3:精简数据类型和约束条件,以改进表结构 
         4:合理的构建索引 
         5:在SQL中有意、合理的利用索引 
         6:去除重复索引 
         7:删除不用的索引 
         8:尽量减少sql语句要扫描的语句数量
         9:确保on或者using子句上的列上有索引
         10:确保group by和order by中的表达式只涉及表中的一个列
         11:尽量明确写出要查询的列,少用select *
         12:尽量不要在where里面使用 不等于 符号,或者是进行null值判断,这会导致全表扫描
         13:尽量不要在where里面对字段进行函数式操作
         14:用exist代替in
         15:…… 太多太多的细节,这里就不去罗列了
    
  • SQL语句优化实际是个综合性的工作

    硬件服务器、MySQL配置、表结构、索引、临时表、SQL语句等一起综合考虑