数据库优化呢有很多种,比如升级硬件,最直接的办法。但是sql优化是成本是最少的
基本的SQL优化的思路
1. 截取SQL语句
1.全面查询日志
-
启用方式
-
在mysql的配置文件中,设置如下:
general_log=1 general_log_file=/path/logfile log_output=FILE
-
也可以通过SQL语句来动态启用和禁用,还可以指定输出到表或者文件 :
set global general_log=1; set global log_output='TABLE'; select * from mysql.general_log;
-
2:注意
全面日志提供所有连续的sql查询语句顺序,但不提供执行时间,在开发环境开
启这个功能,可以很好的来审查SQL语句。但永远不要在生产环境开启这个功
能。
2.慢查询日志
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>要能够获得更高的系统性能,那么建议优先记录到文件。
-
使用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语句
-
通常关注
- 运行最慢的sql语句
- 运行较快,但是执行频率非常高的sql语句
-
识别方法
-
MySql自带的mysqldumpslow,(这个具体用法自行百度把)如:
mysqldumpslow /usr/local/mysql/logs/slowlog
-
开源的pt-query-digest
下载地址:https://www.percona.com/software/mysql-tools/percona-toolkit 这个工具的输出结果有3个不同部分 (1)第一个部分提供了总体时间、查询频率、硬件故障细节等信息 (2)第二部分提供最差的不同查询的详细列表,按照总执行时间排 (3)第三部分是执行时间的分布,以及具体的SQL语句
-
3. 确认语句执行
-
通常关注
1:环境 应该尽可能跟出问题的环境一样或者相似,也就是要让问题得以重现。 这样也利于优化改进后进行验证,看看优化是否有效果。 2:时间统计 采用同一个监控执行时间的标准,以利于发现问题,以及对比优化前后的效果
4. 语句分析
- 常用的SQL语句分析命令
- :explain:分析语句执行基于开销的优化器,以及被优化器考虑的访问策略等,常见的有
- select_type:表示table列引用的使用方式的类型:
- a:simple:不包含子查询和其它复杂语法的简单查询
- b:primary:复杂查询中的最外层的表
- c:derived:表不是物理表
- d:dependent:子查询
- table:表名、表的别名或者一个为查询产生的临时表的标识符
- type:表使用的连接方式,常用的有:
- a:const:这个表最多只有一行匹配
- b:system:表只有一行
- c:eq_ref:有一行是为了每个之前确定的表而读取的
- d:ref:所有具有匹配值的行都被用到
- e:range:所有符合给定范围值的索引行都被用到
- f:all:全表扫描
- possible_keys:优化器为查询选定的索引
- key:优化器选择使用的索引
- 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
- ref:用来进行索引比较的列或者常量
- rows:优化器估计的所有存在于累计结果集中的行数目
- extra:优化器使用的额外信息
- select_type:表示table列引用的使用方式的类型:
- :explain:分析语句执行基于开销的优化器,以及被优化器考虑的访问策略等,常见的有
-
show create table 表名
展示表中列和索引定义的细节信息
-
show indexes from 表名
察看表的索引信息,其中的Cardinality表示索引中每一列唯一值的数量的 估计值
-
show table status like 表名
察看表的底层大小以及表结构。对于innodb引擎,都是估计值。
-
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语句等一起综合考虑