SQL优化

SQL优化

Author: Lijb

Email: lijb1121@163.com

SQL优化概述

基本的SQL优化的思路

1. 截取SQL语句 
2. 识别有问题的SQL语句 
3. 确认语句执行
4. 语句分析 
5. 语句优化
6. 优化验证

截取SQL语句

  • 全面查询日志-启用方式
 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; 
    
    注意 全面日志提供所有连续的sql查询语句顺序,但不提供执行时间,在开发环境开启这个功能,可以很好的来审查SQL语句。但永远不要在生产环境开启这个功能!!!
  • 慢查询日志-启用方式
 1. 在mysql的配置文件中,设置如下: 
    		slow_query_log=1 
    		slow_query_log_file=/usr/local/mysql/logs/slowlog 
    		long_query_time=0.2 
    		log_output=FILE 
  • 进程列表
 show full processlist; 
  • 引擎状态
 show engine innodbstatus ,可以提供SQL语句的详细信息,比如:导致外键验证失败或者造成死锁的SQL语句。 

识别有问题的SQL语句

  • 哪些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. 这个工具的输出结果有3个不同部分 
    		第一个部分提供了总体时间、查询频率、硬件故障细节等信息 
    		第二部分提供最差的不同查询的详细列表,按照总执行时间排序 
    		第三部分是执行时间的分布,以及具体的SQL语句

确认SQL语句执行

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

SQL语句分析

常用的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,表示intnot null 
        key_len:5,表示intnull 
        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:优化器使用的额外信息 

2.show create table 表名

展示表中列和索引定义的细节信息 

3.show indexes from 表名

察看表的索引信息,其中的Cardinality表示索引中每一列唯一值的数量的 估计值

4.show table status like 表名

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

5.show status命令

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

6.show variables命令

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

SQL语句优化

  • 常用的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 

太多太多的细节,这里就不去罗列了,并且网上优化的SQL语句案例很多,只要掌握了从哪些方面优化剩下的都好办.

SQL优化实际是个综合性的工作 硬件服务器、MySQL配置、表结构、索引、临时表、SQL语句等一起综合考虑。

参与评论 您还未登录,请先 登录 后发表或查看评论
相关推荐

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:大白 设计师:CSDN官方博客 返回首页

打赏作者

Aabin⁶⁶⁶

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值