Mysql

user_alex
127 阅读9分钟

一.mysql基础

二.mysql架构

image.png

image.png

image.png

image.png

1.linux环境mysql的数据目录

I.整体数据目录

image.png

II.存放数据的目录（数据目录）

mysql服务器程序启动时会到文件系统中的数据目录下加载一些文件，之后在运行过程中产生的数据也会存储到这个目录下的某些文件中；

image.png

mysql存放数据的路径，即数据目录对应着一个系统变量datadir，可以使用命令查看

image.png

III.存放命令的目录

存储了许多关于控制客户端和服务器程序的命令，包括许多可执行文件，如mysql，mysqld，mysql_safe等

image.png

IV.存放配置文件目录

image.png

2.数据库与文件系统

I.文件系统是什么

操作系统用来管理磁盘的结构被称为文件系统

II.数据库与文件系统的关系

InnoDB，MyISAM这样的存储引擎都是把表存储到磁盘上的，即把表存储在文件系统上，当我们读取数据的时候，存储引擎会从文件系统中把数据读出来返回给客户端，当写数据的时候，存储引擎会把数据写回到文件系统

III.mysql默认的四个数据库

image.png image.png

IV.数据库在文件系统中的表示（文件结构）

不同的存储引擎的不同版本对应的文件结构都不同；
其中表中的数据会放在独立表空间或者系统表空间中

表空间的设置 image.png

i.InnoDB存储引擎模式

①5.7 image.png ②8.0

image.png

查看8.0版本中，.idb文件的存放内容来验证上述内容 image.png

i.MyISAM存储引擎模式

①5.7 image.png ②8.0 image.png

3.mysql数据库的逻辑架构

I.是什么

mysql是典型的c/s架构，所以他不管怎么通信，结果一定是，客户端发送一个请求（sql语句），服务器处理后把结果响应给客户端；所以对于mysql来说，客户端发送请求，服务器处理请求，最后落盘到文件系统中的处理请求部分就是mysql的逻辑架构

image.png

5.7的经典逻辑架构图（8.0去掉了查询缓存的部分）

image.png

数据库的逻辑架构分为了三层，连接层，服务层，引擎层，connectors以及存储层不属于逻辑架构中的三层

image.png

II.sql语句的执行过程

image.png

了解sql语句的执行过程后，结合索引的底层b+数，以及逻辑架构，就可以谈索引的具体使用场景以及索引的优化
执行流程：sql语句->查询缓存->解析器->优化器->执行器

image.png

III.缓冲池buffer pool

image.png image.png

image.png

数据的一致性的问题：

image.png

image.png

image.png

4.存储引擎

I.是什么

image.png

II.InnoDB与MyISAM区别

image.png

image.png image.png

三.索引及调优

image.png

image.png

image.png

image.png

1.前言

I.一个简单的索引设计方案

image.png

image.png

II.InnoDB中的索引设计

①迭代一次：目录项记录的页 image.png ②迭代二次：多个目录项记录的页 image.png ③迭代三次：目录项记录页的目录页 image.png ④B+Tree

上面不断的迭代，这样的数据结构就是B+Tree

image.png

image.png

重点：为什么一般情况下，B+Tree的层次不超过4层？
    因为Tree的层次越低，磁盘IO的次数越少，层次越高，加载的数据页越多，磁盘IO的次数也就越多

2.常见索引分类

image.png image.png image.png image.png image.png

I.按照物理实现方式

i.聚簇索引

根据主键构建的B+Tree的数据结构，就是聚簇索引

image.png image.png image.png

image.png

image.png

ii.二级索引（非聚簇索引）

image.png image.png image.png

image.png

iii.联合索引

image.png

image.png

image.png

II.按照功能逻辑方式

普通索引
唯一索引
主键索引
全文索引

3.MyISAM与InnoDB对比

image.png

image.png

image.png

4.索引的创建

I.如何创建

i.创建表的时候创建索引

①隐式方式 image.png ②显式方式 image.png image.png image.png image.png

ii.在已经存在的表上建立索引

image.png

II.索引设计原则

image.png image.png

image.png

i.什么时候适合使用索引

①字段的数值有唯一性的限制 image.png ②频繁使用where查询条件的字段以及使用updata，delete的where条件列 image.png ③经常使用group by和order by的列 image.png ④使用distinct的字段 image.png image.png ⑤多表join连接操作，创建索引注意事项 image.png ⑥使用列的类型小的创建索引 image.png ⑦使用字符串前缀创建索引 image.png image.png image.png ⑧区分度高（散列度高）的适合创建索引 image.png ⑨使用最频繁的列放在联合索引的左侧 image.png ⑩多个字段都要创建索引的情况下，联合索引优于单列索引

小结： image.png

ii.什么时候不适合使用索引

①在where当中使用不到的字段 image.png ②数据量小的表不用创建索引 image.png ③有大量重复数据的列 image.png image.png ④避免对经常更新的表使用过多的索引 image.png ⑤不建议用无序的值作为索引 image.png ⑥删除不再使用或很少使用的索引 image.png ⑦不要定义冗余或重复的索引 image.png image.png

III.索引优化与查询优化

image.png

i.数据库调优步骤

image.png image.png image.png image.png image.png image.png

ii.查看系统性能参数

image.png image.png image.png

iii.定位慢的sql（慢查询日志）

①开启慢查询日志参数 image.png

image.png

image.png

image.png

image.png

②测试 image.png

image.png

③慢查询日志分析工具mysqldumpslow（脚本文件）

image.png

image.png

image.png

④关闭慢查询日志

image.png

image.png

⑤删除慢查询日志

image.png

iv.查看sql执行的时间成本（show profile）

image.png

image.png

image.png

image.png

image.png

v.分析查询语句（explain）

image.png

image.png

image.png

5.索引失效的11种情况

image.png

image.png

I.失效情况

①最佳左前缀法则 image.png ②主键插入顺序

image.png

image.png

③计算失效 image.png

④类型转换失效 image.png

⑤范围条件右侧的列失效 image.png

image.png

⑥不等于（！=，<>）失效 image.png image.png

⑦is null可以使用索引，is not null索引失效 image.png

⑧like以通配符在开头会失效 image.png

⑨or前后存在非索引的列 image.png image.png

⑩数据库和表的字符集容统一使用utf8mb4 image.png

II.练习

image.png image.png

6.索引优化（查询优化）

I.关联查询优化

i.左外连接

image.png image.png

ii.内连接

iii.join的底层原理

image.png

image.png

II.子查询优化

image.png image.png image.png

III.排序优化

image.png image.png image.png image.png image.png image.png image.png

image.png

i.group by优化

image.png

ii.order by 优化

image.png

VII.update优化

VIII.插入数据优化

i.insert优化

①批量插入 image.png ②手动提交事务 image.png ③主键顺序插入 image.png

ii.大批量百万数据插入

image.png

VIV.主键优化

如果主键长度比较长，二级索引比较多，就会占用大量磁盘空间，搜索的时候就会造成大量的磁盘IO，所以尽量减少主键长度；
如果不按照自增的方式设计主键，可能会出现页分裂现象；
UUID等是无序的，导致插入的时候乱序，会出现页分裂；
业务操作避免对主键的修改，因为会修改索引的结构，代价大。

image.png

VV.limit分页查询优化

image.png

7.mysql性能优化

整体来说有四个方面，第一个是硬件和操作系统的优化，第二个是架构层面的优化，第三个是mysql程序配置的优化，第四个sql的执行优化；
(1)硬件方面：从硬件来说，影响mysql性能的因素主要是cpu，可用内存大小，磁盘读写速度，网络带宽，从操作系统上来说，应用文件句柄数，操作系统的网络配置，都会影响mysql的性能，这部分的优化一般是由DBA或者运维工程师来做；这方面重点应该关注的是服务本身所承载的体量，然后提出合理的指标要求，避免出现资源浪费的现象；
(2)架构层面：mysql是一个磁盘IO访问非常频繁的关系型数据库，在高并发和高性能的场景中，mysql必然会承受巨大的并发压力，在此时的优化方式主要可以分为几个方面，搭建mysql主从集群，单个mysql服务容易导致单点故障，一旦服务宕机，将会导致依赖mysql服务的应用全部无法响应，主从集群或者主主集群都可以保证服务的高可用性，第二个，读写分离设计，在读多写少的场景中，通过读写分离的方案，可以避免读写冲突导致的性能问题，第三个，引入分库分表的机制，通过分库，可以降低单个服务器节省的一个IO压力，通过分表的方式，可以降低单表数据量，从而去提升sql查询效率，第四个，针对热点数据，可以引入更为高效的分布数据库，比如redis，就可以很好的缓解mysql的访问压力，同时还能提升数据的检索性能；
(3)配置优化：对于mysql数据库本身的优化，一般可以通过mysql配置文件my.cnf来完成，比如mysql5.7版本，默认的最大连接数是151个，这个值可以在my.cnf中来修改，第二个，binlog日志，默认是不开启，可以在配置文件中修改为开启，第三个是缓冲池buffer_pool，默认大小的配置等，而这些配置一般是和用户的安装环境以及使用场景有关，因此这些配置，官方只会提供一个默认的配置，具体的使用情况还是要根据使用者去修改，
(4)sql的执行优化：可以分为三个步骤，第一个，慢sql的定位和排查，可以通过慢查询日志和慢查询日志工具分析，得到有问题的sql列表，第二个是执行计划分析，针对慢sql，可以使用explain关键字去查看当前sql的执行计划，可以重点关注type，key，rows等字段去定位sql执行慢的根本原因，再去优化；第三个，使用show profile工具，它是mysql官方提供的，可以用来分析当前回话中，sql语句资源消耗情况的工具，可以用来sql调优的测量，在当前会话中，默认情况下，show profile是关闭的，打开之后会保存最近15次的运行结果，针对运行慢的sql语句，通过show profile进行详细分析，可以得到sql执行过程中，所有资源的开销情况，比如IO开销，内存开销，CPU开销等
以上就是我对mysql性能优化的理解

四.事务

image.png

image.png

五.日志与备份

image.png

image.png

image.png

avatar
文章
阅读
粉丝