MySQL(二)性能优化与进阶实战

在这里插入图片描述

  • 表级锁:开销小,加锁快,不会出现死锁;锁粒度大,发生锁冲突的概率高,并发访问效率较低。
  • 行级锁:开销大,加锁慢,有可能会出现死锁;锁定粒度最小,发生锁冲突的概率低,并发访问效率较高。
  • 共享锁(读锁):其他事务可以读,但不能写。MySQL 可以通过 lock in share mode 语句显示使用共享锁。
  • 排他锁(写锁):其他事务不能读取,也不能写。对于 UPDATE、DELETE 和 INSERT 语句,InnoDB会自动给涉及的数据集加排他锁,或者使用 select for update 显示使用排他锁。

存储引擎

  • InnoDB:在 5.5 版本后成为了 MySQL 的默认存储引擎,特点是支持 ACID 事务、支持外键、支持行级锁提高了并发效率。
  • MyISAM:官方提供的存储引擎,其特点是支持全文索引,查询效率比较高,缺点是不支持事务、使用表级锁
  • MEMORY:功能等同与MyISAM,数据存储在内存,速度快。
  • TokuDB:第三方开发的开源存储引擎,有非常快的写速度,支持数据的压缩存储、可以在线添加索引而不影响读写操作。但是因为压缩的原因,TokuDB 非常适合访问频率不高的数据或历史数据归档,不适合大量读取的场景。

版本问题

  • 在 5.6 版本后 InnoDB 引擎也支持了全文索引,并且在 5.7.6 版本后支持了中文索引
  • 在 MySQL 8.0 之后 InnoDB 会把索引持久化到日志中,重启服务之后自增索引是不会丢失的 实例:在一个自增表里面一共有 5 条数据,id 从 1 到 5,删除了最后两条数据,也就是 id 为 4 和 5 的数据,之后重启的 MySQL 服务器,又新增了一条数据,请问新增的数据 id 为几? 通常的答案是如果表为 MyISAM 引擎,那么 id 就是 6,如果是 InnoDB 那么 id 就是 4。

MySQL8.0 的一些新特性

  • 默认字符集格式改为了 UTF-8;
  • 增加了隐藏索引的功能,隐藏后的索引不会被查询优化器使用,可以使用这个特性用于性能调试;
  • 支持了通用表表达式,使复杂查询中的嵌入表语句更加清晰;
  • 新增了窗口函数的概念,可以用来实现新的查询方式。 其中,窗口函数与 SUM、COUNT 等集合函数类似,但不会将多行查询结果合并,而是将结果放在多行中,即窗口函数不需要 GROUP BY。

MySQL的性能优化

原因:性能低、执行时间太长、等待时间太长、SQL语句欠佳(连接查询)、索引失效、服务器参数设置不合理(缓冲、线程数)

SQL语句的解析过程是由命令解析器来解析的,具体可参考: https://www.cnblogs.com/annsshadow/p/5037667.html

编写过程:
	select dinstinct  ..from  ..join ..on ..where ..group by ...having ..order by ..limit ..
解析过程:			
	from .. on.. join ..where ..group by ....having ...select dinstinct ..order by limit ...

SQL优化, 主要就是 在优化索引

索引: 相当于书的目录,是帮助MySQL高效获取数据的数据结构(B+树、Hash...)

索引的优势:

  • 提高查询效率(降低IO使用率)
  • 降低CPU使用率 (...order by age desc,因为 B树索引 本身就是一个 排好序的结构,因此在排序时 可以直接使用)

索引的弊端:

  • 索引本身很大, 可以存放在内存/硬盘(通常为 硬盘)
  • 索引不是所有情况均适用 a.少量数据 b.频繁更新的字段
    c.很少使用的字段
  • 索引会降低增删改的效率

在这里插入图片描述

高阶操作-开启慢查询日志

一、通过SQL命令语句操作

检查是否开启了慢查询日志 :

 show variables like '%slow_query_log%' ;

临时开启慢查询日志(0和OFF则为关闭):

set global slow_query_log = 1 ;
或者
set global slow_query_log='ON'; 

设置慢查询日志存放的位置(此处设置在D盘,文件名为mysql.log):

set global slow_query_log_file='D:\\mysql.log';

查看慢查询阀值(单位秒,long_query_time值默认10):

show variables like '%long_query_time%' ;

临时设置阀值(需要重新打开链接才能看到修改后的值):

set global long_query_time = 0.5 ;

查询超过阀值的SQL语句(或直接去慢查询日志存放的位置查看日志文件):

show global status like '%slow_queries%' ;

以上命令对于慢查询日志的操作都是临时开启的,即关闭/重启数据库服务就失效了 一般开发阶段,优化时才开启,因为开启慢查询日志会或多或少带来一定的性能影响 若需要永久设置慢查询日志开启,以及设置慢查询日志时间阈值可在MySQL的配置文件中进行修改

二、通过mysqldumpslow工具操作

通过mysqldumpslow工具可以通过一些过滤条件快速查找出需要定位的慢SQL语句。 Linux系统专用,windows系统要使用的话,在安装MySQL时需要在开发者工具处勾选,否则默认不安装

mysqldumpslow --help
s:排序方式
r:逆序
l:锁定时间
g:正则匹配模式		
--获取返回记录最多的3个SQL
	mysqldumpslow -s r -t 3  日志文件mysql.log
--获取访问次数最多的3个SQL
	mysqldumpslow -s c -t 3 mysql.log
--按照时间排序,前10条包含left join查询语句的SQL
	mysqldumpslow -s t -t 10 -g "left join" mysql.log
语法:
	mysqldumpslow 各种参数  慢查询日志的文件

优化方法,官网:https://dev.mysql.com/doc/refman/5.5/en/optimization.html 在这里插入图片描述 MySQL的查询优化器会干扰我们的优化

故使用explain命令分析SQL的执行计划,可以模拟SQL优化器执行SQL语句,从而让开发人员 知道自己编写的SQL状况

查询执行计划: explain +SQL语句

在这里插入图片描述 在这里插入图片描述 在这里插入图片描述

end
  • 作者:suoyue_zhan(联系作者)
  • 发表时间:2020-09-29 20:16:44
  • 版权声明:自由转载-非商用-非衍生-保持署名(创意共享3.0许可证)
  • 转载声明:如果是转载栈主转载的文章,请附上原文链接
  • 公众号转载:请在文末添加作者公众号二维码(公众号二维码见右边,欢迎关注)
  • 评论

    学习
    33333333