查询执行流程

image.png

‘SQL JOINS'的解释图

image.png

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。

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

索引优化规则

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