MySQL进阶

最新推荐文章于 2024-01-29 09:55:05 发布
最新推荐文章于 2024-01-29 09:55:05 发布
阅读量582 收藏
点赞数 1
分类专栏： 数据库 文章标签： 数据库 mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_53316135/article/details/116310469
版权

一、MySQL架构介绍

1、MySQL 简介

概述：

MySQL 是一个开源的关系型数据库，目前属于 Oracle 公司。

MySQL 是一种关系型数据库管理系统，将数据保存在不同的表中，而不是将数据放在一个大仓库内。

MySQL 使用标准的 SQL 数据语言形式。MySQL 采用了 GPL 协议

高级 MySQL

mysql 内核

sql优化

mysql 服务器的优化

各种参数常量设定

查询语句优化

主从复制

软硬件升级

容灾备份

sql 编程

2、MySQL Linux 安装

MySQL 安装

3、mysql 逻辑架构

连接层 => 服务层 => 引擎层 => 存储层

4、MySQL 存储引擎

查看命令：show engines;
在这里插入图片描述

二、索引优化分析

先建个表

 CREATE TABLE `tbl_emp`  (
     `id` int(11) NOT NULL AUTO_INCREMENT,
     `name` varchar(20)  DEFAULT NULL,
     `deptId` int(11) DEFAULT NULL,
     PRIMARY KEY (`id`) ,
     KEY `fk_dept_id`(`deptId`)
 )ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8;

 CREATE TABLE `tbl_dept`  (
     `id` int(11) NOT NULL AUTO_INCREMENT,
     `deptName` varchar(30) DEFAULT NULL,
     `locAdd` varchar(40) DEFAULT NULL,
     PRIMARY KEY (`id`)
 ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8;
 
insert into tbl_dept values(null,'RD',1);
insert into tbl_dept values(null,'HR',12);
insert into tbl_dept values(null,'MK',13);
insert into tbl_dept values(null,'MIS',14);
insert into tbl_dept values(null,'FD',15);

insert into tbl_emp values(null,'z3',1);
insert into tbl_emp values(null,'z4',1);
insert into tbl_emp values(null,'z5',1);
insert into tbl_emp values(null,'w5',2);
insert into tbl_emp values(null,'w6',2);
insert into tbl_emp values(null,'s7',3);
insert into tbl_emp values(null,'s8',4);
insert into tbl_emp values(null,'s9',51);

1、性能下降 SQL 慢，执行时间长，等待时间长

  • 查询语句问题
  • 索引失效
  • 关联查询太多 join
  • 服务器调优及各个参数设置（缓冲、线程数等）

2、SQL 执行顺序：

FROM 笛卡尔积-- ON 主表保留-- JOIN、WHERE – GROUP BY – HAVING – SELECT-- ORDER BY – LIMIT

3、索引简介

什么是索引

索引（Index）是数据结构，排好序的快速查找结构，用来帮助 MySQL 高效获取数据。

在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式指向数据。这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引

索引本身也很大，一般以索引文件形式存储在磁盘上

平常所说的索引，如果没有指定，都是 B 树（多路搜索树）结构的索引，其中聚集索引，次要索引，覆盖所以，复合索引，前缀索引，唯一索引默认都使用 B+树索引，统称索引。除了 B+ 树外，还有 hash，index 等。

优势：

提高数据检索的效率，降低数据库的 IO 成本。

通过索引对数据进行排序，降低数据排序的成本，降低了 CPU 的消耗

劣势：

会降低更新表的速度，在进行写操作时，不仅要保存数据，还要保存一下索引文件每次更新添加了索引的字段

索引分类：

  • 单值索引：一个索引只包含单个列，一个表可以有多个单列索引
  • 唯一索引：索引的值必须唯一，但允许有空值
  • 复合索引：一个索引包含多个列

基本语法

  • 创建：

    CREATE [UNIQUE] INDEX indexName ON myTable(columnName(length));
ALTER myTable ADD [UNIQUE] INDEX [indexName] ON (columnName(length))

  • 删除：

    DROP INDEX [indexName] ON myTable;

  • 查看：

    show INDEX FROM tableName;

常用添加索引 SQL：

ALTER TABLE tbl_name ADD PRIMARY KEY (column_list); 添加一个主键，索引值必须唯一，切且不能为 null
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list); 创建索引的值必须是唯一的，（除了 null 之外，null 可能会出现多次）
ALTER TABLE tbl_name ADD INDEX index_name (column_list); 添加普通索引，索引值可以出现多次
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list); 指定了索引为 FULLTEXT，用于全文索引

mysql 索引结构

  • BTree
  • Hash 索引
  • full-text 全文索引
  • R-Tree 索引

哪些情况需要创建索引

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

哪些情况不适合创建索引

  • 表记录太少
  • 经常增删改的表
  • 数据重复且分布平均的字段

4、性能分析

MySQL Query Optimizer

MySQL 常见瓶颈

  • CPU：CPU 在饱和时，一般发生在数据装入内存或者从磁盘上读取数据的时候
  • IO：磁盘 IO 瓶颈发生在装入数据远大于内存容量的时候
  • 服务器硬件的性能瓶颈：top、free、iostat、vmstat 来查看系统的性能状态
explain

什么是 explain？

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

explain使用：

explain SQL语句
在这里插入图片描述

字段解释

  • id：select 查询的序列号，包含一组数字，标识查询中执行 select 语句子句操作或操作表的顺序
    • id 相同：执行顺序由上到下
    • id 不同：如果是子查询的话，id 的序号会递增，id 值的优先级越高，越先被执行
    • id 相同不同，同时存在
  • select_type：查询的类型，用于区别普通查询，联合查询，子查询等复杂的查询
    • SIMPLE：简单的查询，查询中不包含子查询或者 UNION
    • PRIMARY：查询中包含任何复杂的子部分，最外层查询就会被标记为
    • SUBQUERY：在 SELECT 或 WHERE 列表中包含了子查询
    • DERIUED：在 FROM 列表中包含的子查询被标记为 DERIVED（衍生） 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：通过索引一次就找到了，用于比较 primary key 或者 unique 索引，只匹配一行数据，所以很快，将主键置于 where 列表中，MySQL 就能将该查询转换为一个常量
    • eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或者唯一索引扫描
    • ref：非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，返回所有匹配某个单独值的行，可能会找到多个符合条件的行，所以应该属于查找和扫描的混合体
    • range：质检所指定范围的行，key 列显示使用了哪个索引，一般在 where 语句中出现了 between、<、>、in 等的查询，比全表扫描好一点。
    • index：index类型只遍历索引树，比 ALL 快，因为 索引文件通常比数据文件小，index 和 ALL 都是读全表，但是 index 从索引中读取，ALL 从硬盘中读取
    • ALL：全表扫描，遍历全表找到匹配的行
  • possible_keys：显示可能应用在表中的索引，一个或多个，查询设计到字段上存在索引将被列出，但不一定被查询实际使用
  • key：实际使用的索引，如果为 null，没有使用索引
    • 查询中如果使用了覆盖索引，该索引只出现在 key 列表中
  • key_len：索引中使用的字节数，可以通过该列计算查询中使用的索引的长度，在不损失精度的情况下，长度越短越好，key_len 显示的是索引字段的最大可能长度，不是实际使用长度，key_len 是根据表定义计算得到的，不是通过表内检索出的
  • ref：显示索引的哪一列被使用了
  • rows：根据表统计信息和索引选用情况，大概估算出有多少行记录会被查询
  • Extra：包含不适合在其他列中显示但比较重要的信息

5、索引优化

索引失效情况

  • 在索引列上进行任何操作，会导致索引失效而进行全表扫描
  • 存储引擎不能使用索引中范围条件右边的列
  • 尽量使用覆盖索引，只访问索引的查询，减少 SELECT *
  • mysql 在使用 != 、>、< 的时候无法使用索引，会导致全表扫描
  • IS NULL，IS NOT NULL 也无法使用索引
  • LIKE 模糊查询使用通配符开头，索引会失效进行全表扫描
  • 字符串不加单引号会使索引失效

三、查询截取分析

1、查询优化

慢查询的查找并捕获

  • explain + 慢 SQL 分析
  • show profile：查询 SQL 在MySQL 服务器内执行细节和声明周期情况
  • SQL 数据服务器的参数调优

优化

  • 使用小表驱动大表
  • ORDER BY 关键字优化
    • ORDER BY 子句尽量使用 INDEX 方式排序，避免使用 fileSort 方式排序
    • 尽可能在索引列上完成排序操作，按照索引建的最佳左排序
  • GROUP BY 优化
    • GROUP BY 是先排序后进行分组，按照索引建的最佳左前缀
    • 无法使用索引列时，增大 max_length_for_sort_data 参数的设置 + 增大 sort_buffer_size 参数的设置
    • where 高于 having，能写在 where 限定的条件不要在 having 中进行限定

2、慢查询日志

什么是慢查询日志

  • MySQL 的慢查询日志是 MySQL 提供的一种日志记录，用来记录在 MySQL 中相应时间超过阈值的语句，具体运行时间超过 long_query_time 的SQL，会被记录到慢查询日志中
  • long_query_time 默认值为 10 秒

开启慢查询日志

SET GLOBAL slow_query_log = 1;

查看慢查询阈值时间

SHOW VARIABLES LIKE '%long_query_time%';
SHOW GLOBAL VARIABLES LIKE '%long_query_time%';

设置慢查询阈值时间

设置后需要重新连接或者新开会话才可以看到修改至哦

SET GLOBAL long_query_time = 3;

查看有多少条慢 SQL 记录

SHOW GLOBAL STATUS LIKE '%Slow_queries%'
日志分析工具 mysqldumpslow
  • s：表示按什么方式排序
  • c：访问次数
  • l：锁定时间
  • r：返回记录
  • t：查询时间
  • al：平均锁定时间
  • ar：平均返回记录数
  • at：平均查询时间
  • t：返回前面多少条的数据
  • g：后面搭配正则匹配模式

3、show profile

什么是 profile

MySQL 提供的分析当前会话中语句执行到的资源消耗情况

查看 profiling是否开启

SHOW VARIABLES LIKE '%profiling%';

开启 profiling

SET PROFILING=on;

运行后查看结果并诊断 SQL

SHOW PROFILES;
SHOW PROFILE cpu, block io for query 前一步的query_id;
  • converting HEAP to MyISAM：查询结果太大，内存不够了往磁盘上写
  • Creating tmp table：创建了临时表
  • Coping to tmp table on disk：把内存中临时表复制到磁盘
  • locked

四、MySQL锁机制

1、表锁：（偏读）

特点

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

展示哪些表被加锁了

show open tables；

show status like 'table%';
  • Table_locks_immediate：产生表级锁定的次数，表示可以立即获得锁的查询次数，每立即获得锁值加 1；
  • Table_locks_waited：出现表级锁定争用而发生等待的次数

手动增加表锁

LOCK table 表名 read/write, 表名2 read/write;

读锁会阻塞写，写锁会阻塞读和写

  • MyISAM 在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行增删改操作前，给涉及的表加写锁。
  • 对MyISAM 表的读操作，不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会进行其它进程的写操作
  • 对 MyISAM 表的写操作，会阻塞其他进程对同一表的读和写操作，只有当写锁释放后才会执行其他进程的写操作

2、行锁

特点：

偏向 Innodb 存储引擎，开销大，加锁慢，会出现死锁，锁粒度最小，发生冲突几率低，并发度高

无效索引行锁升级为表锁

如果查询字段未加索引，或者索引失效后，全表扫描后会导致行锁变表锁

间隙锁（Next-key）

用范围条件而不是相等条件检索数据的时候，并请求共享锁或者排它锁时，InnoDB 会给符合添加你的已有数据记录的索引项加锁，值在范围内但不存在的记录叫做间隙（GAP），

查看行锁

SHOW STATUS LIKE 'INNODB_ROW_LOCK%';
  • Innodb_row_lock_current_waits：当前正在等待锁定的数量
  • Innodb_row_lock_time：从系统启动到现在锁定总时间长度
  • Innodb_row_lock_time_avg：每次等待所花费的平均时长
  • Innodb_row_lock_time_max：等待最长的一次花费的时间
  • Innodb_row_lock_waits：总共等待的次数

五、主从复制

复制的基本原理

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

复制的基本原则

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

一主一从常见配置

  1. MySQL 版本一致且后台以服务运行
  2. 主从都配置在 mysqld 节点下
    1. 主机 Windows 修改 my.ini 配置文件
      1. server-id=1：主服务器唯一id
      2. login-bin=本地路径下/msqlbin：必须启用二进制文件
      3. log-err=本地路径/mysqlerr：启用错误日志
      4. basedir=“根目录”：根目录
      5. tmpdir=“临时目录路径”：临时目录
      6. datadir="本地路径/Data/"：数据目录
      7. read-only=0：主机，读写都可以
      8. binlog-ignore-db=mysql：设置不要复制的数据库
      9. binlog-do-db=需要复制的主数据库名字：设置要复制的数据库
    2. 从机 Linux 修改 my.cnf 文件
      1. server-id=2：从服务器服务器 id 修改为
    3. 主机和 Linux 都需要关闭防火墙
      1. Windows 手动关闭防火墙
      2. Linux 系统：systemctl stop firewall
    4. 在 Windows 主机上建立账户并授权 slave
      1. GRANT REPLICATION SLAVE ON *.* TO 'username'@'从机服务器IP' IDENTIFIED BY 'password'：授权给从机以什么用户身份启动
      2. flush privileges;
      3. show master status：查看主机状态
    5. 在 Linux 从机上配置需要复制的主机
      1. GRANT MASTER TO MASTER_HOST='主机IP' MASTER_USER='username',MASTER_PASSWORD='password', MASTER_LOG_FILE='mysqlbin.具体数字',MASTER_LOG_POS=具体值;
      2. start slave;：启动从服务器复制功能
      3. show slave status\G：查看主机是否配置成功
        1. Slave_IO_Running: Yes
        2. Slave_SQL_Running: Yes
    6. 主机新建库，新建表，insert 记录，从机复制
    7. 如何停止主从服务复制功能
      1. stop slave
      2. 关闭后需要重新进行授权，主机 show master status 查看最新状态改变
一起来搬砖呀
关注
  • 1
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 4
    评论
专栏目录
SQL进阶教程 | 史上最易懂SQL教程！10小时零基础成长SQL大师！！
weixin_45732235的博客
09-28 1775
mysql学习
sql进阶教程
保持热爱，持续学习
02-01 2383
SQL进阶教程
SQL进阶教程 | 史上最易懂SQL教程 5小时零基础成长SQL大师（1）
m0_51448653的博客
03-23 5698
目录 第一部分：基础——增删查改 【第一章】做好准备 Getting Started (时长25分钟) 【第二章】在单一表格中检索数据 Retrieving Data From a Single Table (时长53分钟) 【第三章】在多张表格中检索数据 Retrieving Data From Multiple Tables (时长1小时2分) 【第四章】插入、更新和删除数据 Inserting, Updating, and Deleting Data (时长42分钟) 第二部分：基础进阶——汇总、复杂
分享7种SQL的进阶用法
最新发布
01-29 4191
本文分享了7种 SQL 的进阶用法，包括子查询、连接查询、聚合函数、GROUP BY 和 HAVING、窗口函数、CASE 语句和事务处理。子查询是 SQL 查询中的一部分，用于从表中检索数据。子查询可以嵌套，即一个查询作为另一个查询的条件。连接查询有多种类型，包括内连接、左连接、右连接和全连接。GROUP BY 用于对查询结果进行分组，而 HAVING 用于过滤分组后的结果。聚合函数用于对一组数据进行汇总，如求和、平均、最大值、最小值等。窗口函数用于对查询结果进行分组和聚合，如计算排名、分组统计等。
SQL入门教程（非常详细）从零基础入门到精通，看完这一篇就够了
热门推荐
Python84310366的博客
07-05 3万+
SQL 是用于访问和处理数据库的标准的计算机语言。SQL 指结构化查询语言SQL 使我们有能力访问数据库SQL 是一种 ANSI 的标准计算机语言SQL 可与数据库程序协同工作，比如 MS Access、DB2、Informix、MS SQL Server、Oracle、Sybase 以及其他数据库系统。但是由于各种各样的数据库出现，导致很多不同版本的 SQL 语言。
mysql进阶学习
10-15
mysql进阶帮助你更好的实现sql的提升，对资深sql水平有一个更高的标准
Mysql 进阶修行学习
09-08
存储引擎：体系结构、InnoDb了解、MyISAM和Memory、引擎选择 索引：结构、Btree、索引分类、索引语法、性能分析、执行频次、慢查询日志、show profiles、explain、使用规则（验证...Mysql管理：系统数据库、常用工具
mysql进阶知识整合
09-16
整合了mysql事务、索引、sql优化、视图、存储过程、锁、逻辑存储引擎和架构以及事务原理等知识
MySQL进阶：优化查询性能与高可用性的实践
08-24
MySQL进阶：优化查询性能与高可用性的实践 随着互联网技术的快速发展，数据量呈现爆炸式增长，数据库作为存储和管理数据的关键工具，其性能和稳定性对企业的运营至关重要。MySQL作为一款开源的关系型数据库管理系统...
SQL语句经典基础进阶完全教程2017
11-07
无论你是一位SQL新手,或是一位只需对SQL复习一下的资料仓储业界的老将,这里将给您提供最完整最经典的SQL教程,它包括: SQL 指令 : SQL如何被用来存储,读取,以及处理数据库之中的资料. 表格处理 : SQL如何被用来处理数据库中的表格. 进阶 SQL : 介绍SQL 进阶概念,以及如何 用SQL 来执行一些复杂的运算. SQL 语法 : 这里列出所有在教材中被提到的SQL 语法.
经典SQL语句大全入门提升进阶
05-10
经典SQL实用语句包含了SQL Server的基础语法、提升、开发时的高级技巧和经典用法、基本函数的介绍以及同步复制技术实现步骤。
SQL进阶（完整总结版||ppt）
03-11
本文档为进阶版sql，建议先学习基础的sql语句知识后查看。
PDF版SQL语法入门及进阶教程
08-09
SQL语法教程，PDF格式 ♦ SQL 指令 : SQL 如何被用来储存、读取、以及处理数据库之中的资料。 ♦ 表格处理 : SQL 如何被用来处理数据库中的表格。 ♦ 进阶 SQL: 介绍 SQL 进阶概念，以及如何用 SQL 来执行一些较复杂的运算。 ♦ SQL 语法 : 这一页列出所有在这个教材中被提到的 SQL 语法。
SQL进阶教程.pdf
12-23
SQL进阶教程.pdf
《Sql进阶教程》总结
weixin_42258633的博客
05-16 82
语句如下： 第一种方法： 第二种方法：
SQL学习——进阶篇
qq_49948651的博客
09-11 2186
窗口函数原则上只能写在select子句中，partition子句可以省略，省略就是不指定分组，但是，这就失去了窗口函数的功能，所以一般不要这么使用。，所以经常用来在每组内排名，同时具有分组（partition by）和排序（order by）的功能。，如排名问题：每个部门按业绩来排名；topN问题：找出每个部门排名前N的员工进行奖励。其中AxB 与 BxA 就是所谓的笛卡尔乘积，很明显AxB!：count(列名)会比count(1)快；：count(1)会比count(列名)快。**窗口函数功能： **
《SQL进阶教程》学习
weixin_53794695的博客
10-31 973
2021/10/31 今天开始学习SQL进阶教程。听了两天的培训讲座，受益还挺多，如果我是想要毕业直接去工作的话，实习的经历非常重要，可以多多学习待人处事的方法，当然，技术的学习也非常重要。所以加油吧！ CASE表达式 CASE表达式概述 简单CASE表达式 CASE sex when '1' then '男' when '2' then '女‘ ELSE '其他' END 搜索CASE表达式 CASE when sex='1' then '男' w..
mysql 进阶实战 pdf
08-16
《MySQL 进阶实战》是一本探讨MySQL数据库高级应用和实战技巧的书籍。MySQL是一种广泛使用的开源关系型数据库管理系统，在Web开发和数据分析等领域有着重要的应用。 《MySQL 进阶实战》将帮助读者理解MySQL的高级...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 4
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

一起来搬砖呀

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值