Welcome to 发呆鱼.|
收藏闪存小组博问

发呆鱼

园龄：3年4个月粉丝：1关注：0

mysql 入门学习笔记-高级篇

本文是学习mysql 基础的学习笔记，观看尚硅谷网的mysql数据库教程。如有错误或者不足之处，希望指正。。

mysql 架构介绍
	mysql 简介
		高级mysql
			mysql内核
			slq优化
			myslq服务器的优化
			各种参数常量的设定
			查询语句优化
			主从复制
			软硬件升级
			容灾备份
			s
	mysqllinux版安装
	mysql配置文件
	mysql逻辑架构介绍
	mysql存储引擎
	
索引优化分析
	性能下降 sql慢，执行时间长，等待时间长
	常见通用join查询
	索引简介
	性能分析
	索引优化
	
查询截取分析
	查询优化
	慢查询日志
	批量数据脚本
	show profile
	全局查询日志
	
mysql 锁机制
	表锁
	行锁
	页锁
	
主从复制
	复制基本原则
	复制的最大问题
	主从常见配置

1 mysql 介绍

1.1 Linux 下安装mysql

/var/lib/mysql   mysql数据库文件的存放路径
/usr/share/mysql 配置文件目录
/usr/bin  相关命令目录
/etc/init.d/mysql 启停相关脚本

1.2 配置文件

修改字符集

在mysql  Ver 8.0.28-0ubuntu0.21.10.3 for Linux on x86_64 ((Ubuntu))系统下，配置文件为
/etc/mysql/mysql.conf.d/mysqld.cnf

log-bin

二进制日志，用于主从复制

log-error

错误日志，默认是关闭的，记录严重的警告和错误信息，每次启动和关闭的详细信息

log

查询日志，默认关闭 ，记录查询sql语句，如果开启会减低mysql的整体性能，因为i记录日志也是需要消耗系统资源。

slow_query_log_file

慢查询日志,记录所有执行时间超时的查询SQL。

数据文件

默认路径：

datadir = /var/lib/mysql

frm :存放表结构

myd:存放表数据

myi:存放表索引

1.3 mysql 逻辑结构

image-20220221101745058

  • 客户端连接器（Client Connectors）：提供与MySQL服务器建立的支持。目前几乎支持所有主流 的服务端编程技术，例如常见的 Java、C、Python、.NET等，它们通过各自API技术与MySQL建立连接。

  • 系统管理和控制工具（Management Services & Utilities）：例如备份恢复、安全管理、集群管理等

  • 连接池（Connection Pool）：负责存储和管理客户端与数据库的连接，一个线程负责管理一个连接。[官网性能测试报告:引入线程池,性能稳定性与性能会有很大得提升,128并发,读写模式, mysql高出60倍,只读18倍,若不引用线程池,线程创建关闭性能消耗大]

  • SQL接口（SQL Interface）：用于接受客户端发送的各种SQL命令，并且返回用户需要查询的结果。比如DML、DDL、存储过程、视图、触发器等。

  • 解析器（Parser）：负责将请求的SQL解析生成一个"解析树"。然后根据一些MySQL规则进一步检查解析树是否合法。

  • 查询优化器（Optimizer）：当“解析树”通过解析器语法检查后，将交由优化器将其转化成执行计划，然后与存储引擎交互。

  • 缓存（Cache&Buffer）： 缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，权限缓 存，引擎缓存等。如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。

  • 存储引擎负责MySQL中数据的存储与提取，与底层系统文件进行交互。MySQL存储引擎是插件式的，服务器中的查询执行引擎通过接口与存储引擎进行通信，接口屏蔽了不同存储引擎之间的差异 。

  • 文件系统层（File System）负责将数据库的数据和日志存储在文件系统之上，并完成与存储引擎的交互，是文件的物理存储层。主要包含日志文件，数据文件，配置文件，pid 文件，socket 文件等。

    1.4 存储引擎

    show engines; 查看支持的存储引擎

show variables like 'storage_engine'; 查看使用的存储引擎
    对比项 MyISAM InnoDB
    主外键 不支持 支持
    事务 不支持 支持
    行表锁 表锁，即使操作一条记录也会锁住整个表，不适合高并发 行锁，操作时只锁住某一行，不对其他行有影响，适合高并发
    缓存 只缓存索引，不缓存真实数据 不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能决定性影响
    表空间 小 大
    关注点 性能 事务
    默认安装 Y Y

2 索引优化

2.1 性能下降原因

  • 查询语句写的烂

  • 索引失效

  • 关联查询太多join

  • 服务器调优及各个参数设置（缓冲，线程数）

2.2 join

sql 执行顺序

from [left_table]
on [join_condition]
[join_type] join [right_table]
where [condition]
group by [group_by_list]
having [having_condition]
select
distinct [select_list]
order by [order_by_condition]
l

七种join

image-20220221160612301

image-20220221160625243

2.3 索引简介

简介

mysql:索引是帮助mysql高效获取数据的数据结构。排好序的快速查找数据结构

在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某些方式引用。

一般来说，索引要也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。

优势

  • 提高数据检索效率，降低数据库的io成本。
  • 通过索引对数据进行排序，降低数据排序成本，降低了cpu的消耗。

劣势

  • 索引列也占用空间
  • 虽然提高了查询速度，但是降低了更新表的速度。
  • 需要花时间去优化

分类

  • 单值索引——一个索引只包含单个列一个表可用有多个单列索引。
  • 唯一索引——索引的值必须唯一，但是可以为空。
  • 复合索引——一个索引中包含多个列

基本语法

创建：
create [unique] index indexname on table(columnname(length))
alter table add [unique] index [indexname] on (columnname(length))

删除
drop index [indexname] on table

查看
show index from table_name

alter table table_name add primary key [col] 添加一个主键，
alter table table_name add unique index_name (col) 创建索引的值必须唯一
alter table table_name add index index_name (col) 添加普通索引，索引值可多次出现
alter table table_name add fulltext index_name (col) 指定了索引为fulltext,用于全文索引。

那些情况需要创建索引

  • 主键自动建立唯一索引
  • 频繁作为查询条件的字段应该创建索引
  • 查询中与其他表关联的字段，外键关系建立索引
  • 查询中排序字段，排序字段若通过索引访问将大大提高排序速度。
  • 查询中统计或者分组字段

那些情况不需要创建索引

  • 表中记录太少
  • 经常增删改的表
  • 频繁更新的字段不适合创建索引
  • 如果某个数据列中包含许多重复的内容，为它建立索引就没有太大的实际效果。

2.4 性能分析

2.4.1 mysql query optimizer

  • MySQL中有专门负责优化Select语句的优化器模块，主要功能：通过计算分析系统中收集到的统计信息，为客户端请求的Query提供他认为最优的执行计划（他认为最优的数据检索方式，但不见得是DBA认为最优的，这部分最耗费时间）
  • 当客户端向MySQL请求一条Query，命令解析器模块完成请求分类，区别出是Select并转发给MySQL Query Optimizer时，MySQL -Query Optimizer 首先会对整条Query进行优化，处理掉一些常量表达式的预算，直接换算成常量值。并对Query中的查询条件进行简化和转换，如去掉一些无用或显而易见的条件、结构调整等。然后分析Query中的Hint信息（如果有），看显示Hint信息是否可以完全确定该Query的执行计划。如果没有Hint或Hint信息还不足以完全确定执行计划，则会读取所涉及对象的统计信息，根据Query进行写相应的计算Fenix，然后再得出最后的执行计划。

2.4.2 mysql 常见瓶颈

  • cpu: cpu 在饱和的时候一般发生在数据装入内存或从磁盘上读取数据的时候。
  • io: 磁盘i/o 瓶颈发生在装入数据远大于内存容量的时候。
  • 服务器硬件的性能瓶颈： top,free,iosat,vmstat 来查看系统的性能状态。

2.4.3 explain

是什么

使用explain 关键字可以模拟优化器执行sql查询语句，从而知道mysql 是如何处理mysql语句的，分析查询语句或者表结构的性能瓶颈。

能干嘛

  • 表的读取顺序
  • 数据读取操作的操作类型
  • 那些索引可以使用
  • '那些索引被实际使用
  • 表之间的引用
  • 每张表有多少行被优化器查询

各个字段名词解释

image-20220223160159249

  • id： select 查询的序号，表示查询中执行select 子句或者操作表的顺序。

    • id 相同： 执行顺序由上到下
    • id不同：如果是子查询，id序号会递增，id值越大，优先级越高。
    • id既有相同又有不同。

  • select_type ：查询的类型，主要是用于区别普通查询，联合查询，子子查询等复制查询。

    • simple: 简单查询，查询中不包含子查询或者union
    • primary: 查询中若包含任何复杂的子查询，最外层被标为primary
    • subquery： 早select 或者where列表中的包含子查询
    • derived： 在from 中包含的子查询被标记为DEROVED(衍生)，mysql 会递归执行这些子查询，把结果放在临时表中。
    • union： 若第二个select 出现在union之后，则被标记为union。若union包含在from子句的子查询中，外层select被标记为derived
    • union result: 从union表获取结果的select

  • table : 关于哪一张表的。

  • type: 访问类型排序，显示查询使用了那种类型，从好到坏依次是system> const>eq_ref>ref>range>index>all

    • system 表中只有一行数据（等于系统表），这是const类型的特例，平时不会出现，这个页可以忽略不计。
    • const: 表示索引一次就找到了，const用于比较primary key或者unique 索引，因为值匹配一行数据，所以很快。如将主键置于where列表中，mysql就能将该查询转换为一个常量。
    • eq_ref: 唯一性扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或者唯一索引扫描。
    • ref: 非唯一索引扫描，返回匹配某个单独的值所有行，本质上也是一种索引访问，它返回所有匹配某个单位的行，然后它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。
    • range 之间是给定范围的行，使用一个索引来选择行，key显示使用了哪个索引。一般就是在where语句中出现了between <,>,in 等的查询。这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点，而结束语句另一点，不需要扫描全部索引。
    • index: full index scan ,index 与all的区别为index类型只遍历索引树，这通常比all快，因为索引文件通常比数据文件小。即index和all虽然都是读全表，但是index是从索引中读取，而all是从硬盘中读的。
    • all： 将遍历全表以找到匹配行。

  • possible_keys: 显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若有索引，则该索引将被列出。但不一定被实际查询使用。

  • key: 实际使用到的索引，如果为NULL 则没有用到索引。查询中若使用到了覆盖索引，则该索引仅仅出现在key列表中。

  • key_len:索引中使用的字节数，可通过该列计算查询中使用的索引长度。在部损失精确度的情况下，长度越短越好。key_len显示的值为索引字段最大可能长度，并非实际使用长度，即key_len是根据表定义计算的，不是通过表内检索得出的。

  • ref: 显示索引的那一列被使用，如果可能的话，是一个常说。哪些列或常量被用于查找索引列上的值。

  • rows: 根据表统计信息及索引选用情况，大致估算出找到所需的记录锁需要读取的行数。

  • Extra: 十分重要的额外信息

    • Using_filesort: mysql会对数据使用一个外部索引排序。

    • Using_temporary: 使用了临时表保存中间结果，常见于order by 和 group by

    • Using_index: 表示相应的select 操作中覆盖了索引（covering index），为了避免访问了表的数据行，效率不错。如果同时出现using where 表明所用被用来执行索引键的查找。如果没有同时出现using where,表名索引被用来读取数据而非执行查找动作。

      索引覆盖：selectd的数据只用从索引中就能获取，不必读取数据行，mysql 可以利用索引返回selectl列表中的字段，而不必根据索引再次读取数据文件，换句话说，查询列要被所建的索引覆盖。

2.5 索引失效

  • 最佳左前缀原则：如果索引了多列，要遵守最左前缀法则，指的是查询从索引的最左前列开始并且不能跳过索引中的列。
  • 不在索引列上做任何操作（计算，函数，类型转换），会导致索引失效，而转向全表扫描。
  • 存储引擎不能使用索引中范围条件右边的列（范围后面全失效）。
  • 尽量使用索引覆盖，减少select*
  • 使用不等于（!= 或者 <>）会导致全表扫描
  • is null, is not null 也无法使用索引
  • like 以通配符开头 （%xxx）会导致全表扫描
    • 解决liek '% 字符串%‘ 时索引不被使用的方法：使用覆盖索引。
  • 字符串不加单引号，索引失效。（vchar 类型必须加单引号）
  • 少用or ,使用or 会导致索引失效

建议

  • 对于单键索引，尽量选择针对当前query 过滤型更好的索引。
  • 在选择组合索引的适合，当前query 中过滤型最好的字段尽量靠左
  • 在选择组合索引时，尽量选择可以能够包含当前query 中where 字剧中更多字段的索引
  • 尽可能通过分析统计信息和跳转query 的写法来达到选择合适索引的目的。

3 查询截取分析

分析

  1. 观察，至少跑一天，查看生产的慢查询日志。
  2. 开启慢查询日志，设置阈值
  3. explain + 慢sql 分析
  4. show profile
  5. 运维经理 或 dba ,进行sql 数据库服务器参数调优。

3.1 查询优化

永远小表驱动大表。

image-20220310125726103

select * from table where exists ( subquery)

将主查询的数据，放到子查询中做条件验证，根据验证结果来决定主查询的数据是否保留。

order by 关键字优化

  • 尽量使用index 方式排序，避免使用flesert 方式排序。

    order by 满足两种情况，会使用index 方式排序：

    • order by 语句使用索引最左前列
    • 使用where 子句于orderby子句条件列组合满足索引最左前缀

  • 尽可能在索引上完成排序操作，遵照索引建的最佳左前缀。

  • 如果不在索引列上filesort 有两种算法

    • 双路排序：mysql4.1 之前使用的双路排序，要扫描两次磁盘，最种得到数据。读取行指针和orderby 列，对他们进行排序，然后扫描已经排序号的列表，按照列表种的值重新从列表中重新读取输出。从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段。
    • 单路排序：从磁盘读取所需要查询的所有列，按照order by 列在buffer 对它们进行排序，然后扫描排序后的列表进行输出。它的效率更快，因为避免了第二次读取数据，并且把随机io变成了顺序io,但是他会使用更多的空间。

  • 优化策略

    • 增大sort_buffer_size 参数设置
    • 增大max_length_for_sort_data 参数设置

group by

  • group by 实质时先排序后分组，遵照索引建的最佳左前缀
  • 当无法使用索引列，增大max_length_for_sort_data参数+增大sort_buffer_size
  • where 高于 having, 能写在where 限定的条件就不要去having 限定

3.2 慢查询日志

查看
show variables liek '%slow_query_log%'

开启(仅在调优时开启)
set global show_qyeery_log=1 

如果要永久生效，必须修改配置文件
slow_query_log=1
slow_query_log_file= xxxx

设置阈值,设置后需要重新连接或者新开一个会话，才可以看到。
set global long_query_time=5

image-20220310135923181

image-20220310135945394

3.3 show profile

image-20220310142444635

3.4 全局查询日志

仅在调试时使用

配置在my.cnf
general_log=1
general_log_file= xxxx
log_output=FILE 输出格式


命令配置
set global general_log=1
set global log_output='table'

4 mysql 锁机制

4.1 概述

分类

从对数据库的操作类型：

  • 读锁：针对同一份数据，多个读操作可以同时进行，而不会相互影响。

  • 写锁：当前写操作没有完成前，它会阻断其他写锁和读锁。

从对数据库操作的粒度分：表锁，行锁

4.2 表锁

特点

偏向于MyISAM存储引擎，开销小，加锁快，无死锁，锁定粒度大，发生锁冲突概率高，并发度最低。

使用

查看是否有表锁
show open table


手动添加表锁
lock table table_name read(write)

解锁
unlock tables;

加读锁

当前session 其他session
可以查询加锁表 也可以查询加锁表
不能查询其他表 可以更新查询其他表
不可以更新当前表 插入更新加锁会一直等待

加写锁

当前会话对加写锁的表可以读写，其他会话查询或者写的时候会阻塞。

4.3 行锁

特点

偏向InnoDB存储引擎，开销大，加锁慢，会出现死锁，锁定粒度最小。发生锁冲突的概率最低，并发度也最高。

InnoDB 与 MyISAM 最大的不同：一是支持事务，二是采用行级锁。

索引失效导致行锁变成表锁

间隙锁的危害

  • 什么是间隙锁

    当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁，对于键值条件在范围内但并不存在的记录，叫做 ”间隙（GAP）"

    InnoDB也会对这个间隙加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）

  • 危害

    因为query 执行过程中通过范围查找的话，他会锁定整个范围内所有索引键值，即使这个键值并不存在。而间隙锁会导致无法插入键值范围内的任何数据。

查看状态

show status like 'xxx'

image-20220310155647986

优化建议

  • 尽可能让所有数据检索都通过索引完成，避免无索引行行锁升级为表锁。
  • 合理设计索引，尽量缩小锁的范围
  • 尽可能较少检索条件，避免间隙锁
  • 尽量控制事务大小，减少锁定资源量和时间长度
  • 尽可能低级别事务隔离

4.4 页锁

开销和加锁时间介于表锁和行锁之间，会出现死锁，锁定粒度介于表锁和行锁之间。并发度一般。

5 主从复制

5.1 复制基本原理

slave 会从 master 读取binlog 来进行数据同步。

mysql 复制过程分成三步：

  1. master 将改变记录到二进制文件binary log. 这些记录过程叫做二进制日志事件，binary log events.
  2. slave 将master 的binary log envent 拷贝到它的中继日志relay log
  3. slave 重做中级日志中的事件，将改变应用到自己的数据库中。mysql 复制是异步的串行化的。

image-20220310162218548

5.2 复制的基本原则

  • 每个slave 只有一个master
  • 每个slave 只能有一个唯一的服务器ID
  • 每个master 可以有多个salve

5.3 一主一从常见配置

主机配置

image-20220310164114348

从机Linux 配置

image-20220310164208940

在主机windows 上新建用户并授权salve

image-20220310164323314

在从机 Linux 配置要复制的主机

image-20220310164412263

本文作者：发呆鱼

本文链接：https://www.cnblogs.com/dyiblog/articles/15990285.html

版权声明：本作品采用知识共享署名-非商业性使用-禁止演绎 2.5 中国大陆许可协议进行许可。

posted @   发呆鱼  阅读(53)  评论(0)  编辑  收藏  举报
点击右上角即可分享
微信分享提示
AI IDE Trae
评论
收藏
关注
推荐
深色
回顶
收起