Mysql调优

2021年03月30日 | mysql

查询执行流程

‘SQL JOINS'的解释图

Mysql排查

慢查询日志:MySQL提供的一种日志记录，用于记录MySQL种响应时间超过阀值的SQL语句（long_query_time，默认10秒）

慢查询日志默认是关闭的；建议：开发调优是打开，而最终部署时关闭。

#检查是否开启了 慢查询日志 ：  
show variables like '%slow_query_log%' ;
临时开启：
	set global slow_query_log = 1 ;  --在内存种开启
	exit
	service mysql restart

永久开启：
	/etc/my.cnf 中追加配置：
	vi /etc/my.cnf 
	[mysqld]
	slow_query_log=1
	slow_query_log_file=/var/lib/mysql/localhost-slow.log

 慢查询阀值： 
show variables like '%long_query_time%' ; 

临时设置阀值： 
set global slow_query_log = 1;#开启慢查询日志
set global long_query_time = 5 ; 

--设置完毕后，重新登陆后起效 （不需要重启服务） 
永久设置阀值： /etc/my.cnf 中追加配置： 
vi /etc/my.cnf 
[mysqld] long_query_time=3

select sleep(3);
--查询超过阀值的SQL：  
show global status like '%slow_queries%' ;

(1)慢查询的sql被记录在了日志中，因此可以通过日志 查看具体的慢SQL。
cat /var/lib/mysql/localhost-slow.log

(2)通过mysqldumpslow工具查看慢SQL,可以通过一些过滤条件 快速查找出需要定位的慢SQL
mysqldumpslow --help
s：排序方式
r:逆序
l:锁定时间
g:正则匹配模式

--获取返回记录最多的3个SQL
	mysqldumpslow -s r -t 3  /var/lib/mysql/localhost-slow.log

--获取访问次数最多的3个SQL
	mysqldumpslow -s c -t 3 /var/lib/mysql/localhost-slow.log

--按照时间排序，前10条包含left join查询语句的SQL
	mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/localhost-slow.log

语法：
	mysqldumpslow 各种参数  慢查询日志的文件

海量数据分析

1）show profiles ; --默认关闭 
show variables like '%profiling%'; 
set profiling = on ; 
show profiles ：会记录所有profiling打开之后的 全部SQL查询语句所花费的时间。缺点：不够精确，只能看到 总共消费的时间，不能看到各个硬件消费的时间（cpu io ） 
(2)--精确分析:sql诊断 
show profile all for query 上一步查询的的Query_Id 
show profile cpu,block io for query 上一步查询的的Query_Id 
(3)全局查询日志 ：记录开启之后的 全部SQL语句。 （这次全局的记录操作 仅仅在调优、开发过程中打开即可，在最终的部署实施时 一定关闭）
 show variables like '%general_log%'; --执行的所有SQL记录在表中
 set global general_log = 1 ;--开启全局日志 
set global log_output='table' ; --设置 将全部的SQL 记录在表中 
开启后，会记录所有SQL ： 会被记录 mysql.general_log表中。
select * from  mysql.general_log ;
--执行的所有SQL记录在文件中 
set global log_output='file' ; 
set global general_log = on ; 
set global general_log_file='/tmp/general.log' ;

SQL索引

首先说什么是索引

索引是一种用于快速查询和检索数据的数据结构。常见的索引结构有: B树， B+树和Hash。

索引的作用就相当于目录的作用。打个比方: 我们在查字典的时候，如果没有目录，那我们就只能一页一页的去找我们需要查的那个字，速度很慢。如果有目录了，我们只需要先去目录里查找字的位置，然后直接翻到那一页就行了。

索引优化规则

如果MySQL估计使用索引比全表扫描还慢，则不会使用索引。记住这个范围值——30%，后面所讲的内容都是建立在返回数据的比例在30%以内的基础上
前导模糊查询不能命中索引
数据类型出现隐式转换的时候不会命中索引，特别是当列类型是字符串，一定要将字符常量值用引号引起来。
符合索引的情况下,查询条件不包含索引最左边部分(不满足最左原则),不会命中符合索引
union、in、or都能够命中索引，建议使用in。
用or分割开的条件，如果or前的条件中列有索引，而后面的列中没有索引，那么涉及到的索引都不会被用到。因为or后面的条件列中没有索引，那么后面的查询肯定要走全表扫描，在存在全表扫描的情况下，就没有必要多一次索引扫描增加IO访问。
负向条件查询不能使用索引，可以优化为in查询负向条件有：!=、<>、not in、not exists、not like等
范围条件查询可以命中索引。范围条件有：<、<=、>、>=、between等
数据库执行计算不会命中索引。
利用覆盖索引进行查询，避免回表
建立索引的列，不允许为null。

Mysql调优 - Heart of yearning