MySQL高级学习

最新推荐文章于 2024-07-20 16:33:27 发布
最新推荐文章于 2024-07-20 16:33:27 发布
阅读量305 收藏
点赞数 1
分类专栏： 数据库 文章标签： mysql 数据库 服务器
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/HC199854/article/details/113981799
版权

文章目录


MySQL逻辑结构介绍

在这里插入图片描述

  1. 连接层：最上层是一些客户端和连接服务，包含本地sock通信和大多数基于客户端/服务端工具实现的类似于tcp/ip的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。
  2. 服务层：第二层架构主要完成大多少的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化及部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如过程、函数等。在该层，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化如确定查询表的顺序，是否利用索引等，最后生成相应的执行操作。如果是select语句，服务器还会查询内部的缓存。如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提升系统的性能。
  3. 引擎层：我们可以根据自己的实际需要进行选取引擎。后面介绍MylSAM和InnoDB存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取。后面介绍MylSAM和InnoDB。
  4. 存储层：数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互。

存储引擎介绍

两条常用命令：
看你的mysql现在已提供什么存储引擎: show engines;
看你的mysql当前默认的存储引擎: show variables like '%storage_engine%';

最常用的存储引擎为：MylSAM和InnoDB；两者对比如下：

在这里插入图片描述


索引的优化分析

SQL性能下降的常见原因：

  1. 查询语句效率慢，需优化
  2. 索引失效
  3. 关联查询太多join（设计缺陷或不得已的需求）
  4. 服务器调优及各个参数设置（缓冲、线程数等）

MySQL语句的读取顺序：
在这里插入图片描述

鱼刺图分析：
在这里插入图片描述

索引简介

索引是什么：

  • MySQL官方对索引的定义为:索引（Index）是帮助MySQL高效获取数据的数据结构。

  • 可以得到索引的本质:索引是数据结构。

  • 可以简单理解为"排好序的快速查找数据结构”。

  • 数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向（引用）数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。
    在这里插入图片描述
    为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录。

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

  • 我们平常所说的索引，如果没有特别指明，都是指B树(多路搜索树，并不一定是二叉的)结构组织的索引。其中聚集索引，次要索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然，除了B+树这种类型的索引之外，还有哈稀索引(hash index）等。

索引的优点：

  1. 类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的IO成本
  2. 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗

索引的缺点：

  1. 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的。

  2. 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。

  3. 索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查计。

索引的分类：
4. 单值索引：即一个索引只包含单个列，一个表可以有多个单值索引
5. 唯一索引：索引列的值必须唯一，但允许有空值（主键就是唯一索引）
6. 复合索引：即一个索引包含多个列

聚簇索引和非聚簇索引的区别：

都是B+树的数据结构

  • 聚簇索引：将数据存储与索引放到了一块、并且是按照一定的顺序组织的，找到索引也就找到了数据，数据的物理存放顺序与索引顺序是一致的，即:只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的

  • 非聚簇索引：叶子节点不存储数据、存储的是数据行地址，也就是说根据索引查找到数据行的位置再取磁盘查找数据，这个就有点类似一本树的目录，比如我们要找第三章第一节，那我们先在这个目录里面找，找到对应的页码后再去对应的页码看文章。myisam通过key_buffer把索引先缓存到内存中，当需要访问数据时（通过索引访问数据），在内存中直接搜索索引，然后通过索引找到磁盘相应数据，这也就是为什么索引不在key buffer命中时，速度慢的原因

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

在这里插入图片描述

澄清一个概念：innodb中，在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找，非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引，辅助索引叶子节点存储的不再是行的物理位置，而是主键值。

使用索引的基本语法：
创建：
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 table_name\G

索引的分类：

  1. BTree索引
  2. Hash索引
  3. full-text全文索引
  4. R-Tree索引

B树的检索原理：
在这里插入图片描述

初始化介绍：

  • 一颗b+树，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示)，如磁盘块1包含数据项17和35，包含指针P1、P2、P3，
    P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。
  • 真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。
  • 非叶子节点不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。

查找过程：

  • 如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块的P2指针，内存时间因为非常短（相比磁盘的I0）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次I0，同时内存中做二分查找找到29，结束查询，总计三次lO。

真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次lO，那么总共需要百万次的IO，显然成本非常非常高。

为什么MySQL的索引要使用B+树而不是其它树形结构?比如B树？

  1. 因为B树不管叶子节点还是非叶子节点，都会保存数据，这样导致在非叶子节点中能保存的指针数量变少（有些资料也称为扇出）；指针少的情况下要保存大量数据，只能增加树的高度，导致IO操作变多，查询性能变低。

  2. B+树还有一个最大的好处，方便扫库，B树必须用中序遍历的方法按序扫库，而B+树直接从叶子结点挨个扫一遍就完了（叶子节点是一个双向链表的结构），B+树支持range-query（范围查找）非常方便（b+tree 数据节点存在指针，所以范围查找不需要多次查找。），而B树不支持。这是数据库选用B+树的最主要原因。

哪些情况需要创建索引：

  1. 主键自动建立唯一索引
  2. 频繁作为查询条件的字段应该创建索引
  3. 查询中与其它表关联的字段，外键关系建立索引
  4. Where条件里用不到的字段不创建索引
  5. 频繁更新的字段不适合创建索引（因为每次更新不单单是更新了记录还会更新索引）
  6. 单键/组合索引的选择问题(在高并发下倾向创建组合索引)
  7. 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
  8. 查询中统计或者分组字段

哪些情况不应该创建索引：

  1. 表数据太少
  2. 经常增删改的表
  3. 数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引
  4. 如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。

索引的选择性：是指索引列中不同值的数目与表中记录数的比。如果一个表中有2000条记录，表索引列有1980个不同的值，那么这个索引的选择性就是1980/2000=0.99。一个索引的选择性越接近于1，这个索引的效率就越高。


性能分析

MySql Query Optimizer

  • Mysql中有专门负责优化SELECT语句的优化器模块，主要功能：通过计算分析系统中收集到的统计信息，为客户端请求的Query提供他认为最优的执行计划。

  • 当客户端向MySQL请求一条Query，命令解析器模块完成请求分类，区别出是SELECT并转发给MySQLQuery Optimizer时，MySQL Query Optimizer首先会对整条Query进行优化，处理掉一些常量表达式的预算，直接换算成常量值。并对Query 中的查询条件进行简化和转换，如去掉一些无用或显而易见的条件、结构调整等。然后分析Query 中的 Hint信息(如果有)，看显示Hint信息是否可以完全确定该Query 的执行计划。如果没有Hint或Hint信息还不足以完全确定执行计划，则会读取所涉及对象的统计信息，根据Query进行写相应的计算分析，然后再得出最后的执行计划。

MySQL常见瓶颈
  1. CPU：CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候
  2. IO：磁盘I/o瓶颈发生在装入数据远大于内存容量的时候
  3. 服务器硬件的性能瓶颈:top,free,iostat和vmstat来查看系统的性能状态
Explain（执行计划）

Explain是什么

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

Explain怎么使用：

Explain + SQL语句即可查询执行计划包含的信息
在这里插入图片描述

Explain的作用：

  1. 表的读取顺序
  2. 数据读取操作的操作类型
  3. 哪些索引可以使用
  4. 哪些索引被实际使用
  5. 表之间的引用
  6. 每张表有多少行被优化器查询
Explain的各字段解释：

id（执行顺序）：

  select查询的序列号,包含一组数字，表示查询中执行select子句或操作表的顺序。

id的三种情况：

  1. id相同，执行顺序由上至下
    在这里插入图片描述

  2. id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
    在这里插入图片描述

  3. id相同不同，同时存在，相同的视为同一组
    在这里插入图片描述

表的执行顺序为：t3–>deriverd2–>t2


table：

  显示这一行的数据是关于哪张表的


partitions：

  该列显示的为分区表命中的分区情况。非分区表该字段为空（null）。


select_type：

  查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询。

分类如下：

  1. SIMPLER：简单的select 查询,查询中不包含子查询或者UNION
  2. PRIMARY： 查询中若有包含任何复杂的子部分，最外层查询则被标记为主查询
  3. SUBQUERY：在SELECT或WHERE列表中包含的子查询
  4. DERIVED：在FROM列表中包含的子查询被标记为DERIVED(衍生)，MySQL会递归执行这些子查询，把结果放在临时表里。
  5. UNION：若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中，外层SELECT将被标记为DERIVED
  6. UNION RESULT：从UNION表获取结果的SELECT

type：

  显示查询使用了何种类型，是较为重要的一个指标，结果值从最好到最坏依次是：

所有结果值的排序：

system > const > eq_ref> ref > fultext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > all

常见结果值的排序：
system > const > eq_ref > ref > range > index > ALL

对于大量数据（超过百万条），一般来说，得保证查询至少达到range级别，最好能达到ref。

结果值说明如下：

  1. system：表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计。

  2. const：如将主键置于where列表中，MySQL就能将该查询转换为一个常量，表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行据，所以很快。

  3. eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。

  4. ref：非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。

  5. range：只检索给定范围的行,使用一个索引来选择行。key列显示使用了哪个索引。一般就是在你的where语句中出现了between、<、>、in等的查询。这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。

  6. index：Full Index Scan，index与ALL区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘中读的）

  7. ALL：Full Table Scan，将遍历全表以找到匹配的行。


possible_keys：

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


key：
  • 实际使用的索引。如果为NULL，则没有使用索引（没有建立索引或者索引失效）。
  • 查询中若使用了覆盖索引，则该索引仅出现在key列表中。

key_len ：
  • 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好，即where后面附加的条件越多，需要的长度就越长。
  • key_len显示的值为索们字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。

ref：

  显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。
在这里插入图片描述

rows：

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


filtered：

  这个字段表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例，注意是百分比，不是具体记录数。


extra:

  包含不适合在其他列中显示但十分重要的额外信息。具体如下（前三个是重点）：

  1. Using filesort·：说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”。当出现文件排序时应当进行优化。
    在这里插入图片描述

  2. Using temporary：使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。由于使用了临时表性能会很慢。
    在这里插入图片描述

  3. Using index：
    表示相应的select操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，效率不错！
    如果同时出现using where，表明索引被用来执行索引键值的查找;
    如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。
    在这里插入图片描述

覆盖索引：select的数据列只用从索引中就能够取得，不必读取数据行，MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件,换句话说就是查询列要被所建的索引覆盖。 一般出现在查询的那几个字段刚好被复合索引包含。

注意：
  如果要使用覆盖索引，一定要注意select列表中只取出需要的列，不可select*
  因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降。

  1. Using where ：使用了where过滤。
  2. using join buffere：使用了连接缓存。
  3. impossible where：where子句的值总是false，不能用来获取任何元组。
  4. select tables optimized away ：在没有GROUPBY子句的情况下，基于素引优化MIN/MAX操作或者对于MyISAM存储引繁优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段印尧成优化。
  5. distinct：优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作。

索引分析

如何避免索引失效：

  1. 全值匹配最好

  2. 最佳左前缀法则（如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。）（带头大哥不能少，中间兄弟不能断）
    在这里插入图片描述

  3. 不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描

  4. 存储引擎不能使用索引中范围条件右边的列，所以范围条件尽量最后写
    在这里插入图片描述

  5. is null ,is not null也无法使用索引

  6. 尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select*

  7. mysql在使用不等于(!=或者<>)的时候无法使用索引会导致全表扫描回
    在这里插入图片描述

  8. like以通配符以%开头的，mysql索引失效会变成全表扫描的操作；即百分号尽量不要加在左边。如果一定要在左边加%，则需要使用覆盖索引避免索引失效。
    在这里插入图片描述

  9. 字符串不加单引号索引失效；MySQL底层会自动进行的类型转换，所以参照第三条索引会失效。
    在这里插入图片描述

  10. 少用or，用它来连接时会索引失效
    在这里插入图片描述

总结：
在这里插入图片描述like ‘kk%’ 是一种比较特殊的范围查找，但是它之后的条件并不会索引失效。但是如果%写在开头就会导致它后面的条件索引失效。

优化索引的一般建议：

  • 对于单键索引，尽量选择针对当前query过滤性更好的索引。
  • 在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。
  • 在选择组合索引的时候，尽量选择可以能够包含当前query中的where字句中更多字段的索引。
  • 尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的。

优化总结口诀：

全值匹配我最爱，最左前缀要遵守;
带头大哥不能死，中间兄弟不能断;
索引列上少计算，范围之后全失效;
LIKE百分写最右，覆盖索引不写星;
不等空值还有or，索引失效要少用;

查询截取分析

一般过程：

  1. 慢查询的开启并捕获。
  2. explain+慢SQL分析。
  3. show profile查询SQL在Mysql服务器里面的执行细节和生命周期情况。
  4. SQL数据库服务器的参数调优。

小表驱动大表原则（小的数据集驱动大的数据集）

下面用in和exists进行举例：
在这里插入图片描述

SELECT …FROM table WHERE EXISTS(subquery)
该语法可以理解为：将主查询的数据，放到子查询中做条件验证，根据验证结果（TRUE或FALSE）来决定主查询的数据结果是否得以保留。

提示：

  1. EXSTS(sbquery)只返回TRUE或FALSE，因此子查询中的SELECT *也可以是 SELECT 1 或select x 官方说法是实际执行时会忽略SELECT清单，因此没有区别。
  2. EXSTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比，如果担忧效率问题，可进行实际检验以确定是否有效率问题。
  3. EXISTS子查询往往也可以用条件表达式、其他子查询或者JOIN来替代，何种最优需要具体问题具体分析。

order by 关键字优化

  ORDER BY子句，尽量使用Index方式排序,避免使用FileSort方式排序；MySQL支持二种方式的排序，FileSort和Index，Index效率高；它指MySQL扫描索引本身完成排序。FileSort方式效率较低。

ORDER BY满足两情况，会使用Index方式排序：

  1. ORDER BY语句使用索引最左前列
  2. 使用Where子句与Order BY子句条件列组合满足索引最左前列

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

  • 双路排序：MySQL4.1之前是使用双路排序，字面意思就是两次扫描磁盘，最终得到数据，读取行指针和orderby列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出。
  • 单路排序：从磁盘读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO,但是它会使用更多的空间，因为它每一行都保存在内存中了。

需要注意的是：

  • sort_buffer容量大小，再排……从而多次l/O。在sort_buffer中，单路排序比多路排序要多占用很多空间，因为方法B是把所有字段都取出,所以有可能取出的数据的总大小超出了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据，进行排序（创建tmp文件，多路合并)，排完再去取。
  • 本来想省一次lO操作，反而导致了大量的lO操作，反而得不偿失。
  • 所以有时可以通过设置sort_buffer的值提高效率。

提高Order By的速度的方法：

  1. 尽量不使用select *

  2. 尝试提高sort_buffer_size
    两种算法的数据都有可能超出sort_buffer的容量，超出之后，会创建tmp文件进行合并排序，导致多次IO，但是用单路排序算法的风险会更大一些,所以要提高sort_buffer_size。

  3. 尝试提高max_length_for_sort_data
    不管用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的

总结：

在这里插入图片描述


GROUP BY关键字优化

  • 与order by的优化方式差不多，只是多了一个having。

  • group by实质是先排序后进行分组，遵照索引建的最佳左前缀。

  • 当无法使用索引列，增大max_length_for_sort_data参数的设置+增大sort_buffer_size参数的设置。

  • where高于having，能写在where限定的条件就不要去having限定了。


慢查询日志

慢查询日志是什么：

  • MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句。

  • 具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中，long_query_time的默认值为10，意思是运行10秒以上的语句。假如运行时间正好等于long_query_time的情况，并不会被记录下来。也就是说，在mysql源码里是判断大于long_query_time，而非大于等于。

  • 由它来查看哪些SQL超出了我们的最大忍耐时间值，比如一条sql执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒的sql，结合之前explain进行全面分析。

说明：

  • 默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。
  • 当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件。

命令的使用：

  • 查看状态：show variables like '%slow_query_log%';

  • 开启：set global slow_queery_log=1;
    使用set global slow_query_log=1开启了慢查询日志只对当前数据库生效，如果MySQL重启后则会失效。

  • 查看long_query_time：SHOW VARIABLES LIKE 'long_query_time%';

  • 设置慢的阙值时间：set global long_query_time=3;
    需要重新连接或新开一个会话才能看到修改值。

日志分析工具mysqldumpslow：

  在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow。

mysqldumpslow的帮助信息：
在这里插入图片描述

案例：
在这里插入图片描述

Show Profile

  Show Profile是mysql提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优的测量；默认情况下，参数处于关闭状态，并保存最近15次的运行结果。

分析步骤：

  1. 是否支持，看看当前的mysql版本是否支持
  2. 开启功能，默认是关闭，使用前需要开启团
    查看状态：show variables like 'profiling';
    开启：set profiling=on;
  3. 运行SQL
  4. 查看结果，show profiles;
  5. 诊断SQL，show profile cpu, block io for query 上一步前面的问题SQL数字号码

在这里插入图片描述
也可以使用其他参数进行查看相关信息：
在这里插入图片描述

使用show profile cpu, block io for query后需要注意的情况，下面4种情况往往就是导致SQL慢的原因。

  1. converting HEAP to MyISAM： 查询结果太大，内存都不够用了往磁盘上搬了。
  2. Creating tmp table：创建临时表（拷贝数据到临时表，用完再删）。
  3. Copying to tmp table on disk：把内存中临时表复制到磁盘，非常危险，性能消耗极大。
  4. locked。
    在这里插入图片描述

MySQL锁机制

  • 锁是计算机协调多个进程或线程并发访问某一资源的机制。
  • 在数据库中，除传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说锁对数据库而言显得尤其重要，也更加复杂。

锁的分类：

根据数据操作的类型区分：

  • 读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响。
  • 写锁（排它锁）：当前写操作没有完成前，它会阻断其他写锁和读锁。

从对数据操作的粒度区分：

  • 表锁（偏读）
  • 行锁（偏写）
  • 页锁（开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间，并发度一般。不常用。）

表锁

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

查看表上加过的锁：
show open tables;

增加表锁：
lock table 表名字 read(write), 表名字2 read(write);

释放锁：
unlock tables;

给表加读锁的演示：
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

给表加写锁的演示：
在这里插入图片描述在这里插入图片描述

总结：
   MyISAM在执行查询语句(SELECT）前，会自动给涉及的所有表加读锁，在执行增删改操作前，会自动给涉及的表加写锁。
MySQL的表级锁有两种模式:

  • 表共享读锁 (Table Read Lock)
  • 表独占写锁（Table Write Lock）

所以对MylSAM表进行操作，会有以下情况:

  1. 对MyISAM表的读操作（加读锁)，不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其他进程的写操作。
  2. 对MyISAM表的写操作（加写锁），会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其它进程的读写操作。

简而言之，就是读锁会阻塞写，但是不会堵塞读。而写锁则会把读和写都堵塞。

【如何分析表锁定】
可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定：
show status like 'table%';

在这里插入图片描述
这里有两个状态变量记录MySQL内部表级锁定的情况，两个变量说明如下：

  • Table_locks_immediate：产生表级锁定的次数，表示可以立即获取锁的查询次数，每立即获取锁值加1 。
  • Table_locks_waited：出现表级锁定争用而发生等待的次数(不能立即获取锁的次数，每等待一次锁值加1)，此值高则说明存在着较严重的表级锁争用情况。

此外，Myisam的读写锁调度是写优先，所以myisam不适合做写为主表的引擎。因为写锁后，其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永远阻塞。


行锁

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

InnoDB与MyISAM的最大不同有两点：

  • 一是支持事务（TRANSACTION）
  • 二是采用了行级锁。

事务是由一组SQL语句组成的逻辑处理单元，事务具有以下4个属性，通常简称为事务的ACID属性。

  1. 原子性（Atomicity)：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。
  2. 一致性(Consistent)：在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以保持数据的完整性;事务结束时，所有的内部数据结构（如B树索引或双向链表）也都必须是正确的。
  3. 隔离性（lIsolation)：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。
  4. 持久性(Durable)：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。
并发事务处理带来的问题：
  1. 更新丢失(Lost Update)：最后的更新覆盖了由其他事务所做的更新。
  2. 脏读(Dirty Reads)：事务A读取到了事务B已修改但尚未提交的的数据，还在这个数据基础上做了操作。此时，如果B事务回滚，A读取的数据无效，不符合一致性要求。
  3. 不可重复读(Non-Repeatable Reads)：一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改变、或某些记录已经被删除了。不符合隔离性。
  4. 幻读(Phantom Reads)：一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据。
    幻读是针对数据插入（INSERT）操作来说的。假设事务A对某些行的内容作了更改，但是还未提交，此时事务B插入了与事务A更改前的记录相同的记录行，并且在事务A提交之前先提交了，而这时，在事务A中查询，会发现好像刚刚的更改对于某些数据未起作用，但其实是事务B刚插入进来的，让用户感觉很魔幻，感觉出现了幻觉，这就叫幻读。
MVCC：

link

事务的隔离级别：

在这里插入图片描述
  数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大，因为事务隔离实质上就是使事务在一定程度上“串行化”进行，这显然与“并发”是矛盾的。同时，不同的应用对读一致性和事务隔离程度的要求也是不同的，比如许多应用对“不可重复读”和“幻读”并不敏感，可能更关心数据并发访问的能力。

可序列化也叫串行化

查看当前数据库的事务隔离级别: show variables like 'tx_isolation';

mysql8.0后的命令：show variables like 'transaction_isolation';

原因：transaction_isolation在MySQL 5.7.20中添加了作为别名 tx_isolation，现已弃用，并在MySQL 8.0中删除。

MySQL默认的事务隔离级别是：可重复读

行锁的基本演示：
在这里插入图片描述

注意：索引失效会导致行锁变表锁；比如字符串忘记加单引号。所以索引失效导致的性能损失是非常大的。


间隙锁：

  • 当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁，对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，
  • InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁(Next-Key锁）。

间隙锁带来的问题：
在这里插入图片描述
间隙锁的危害：
   因为Query执行过程中通过过范围查找的话，他会锁定整个范围内所有的索引键值，即使这个键值并不存在。 间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害。

如何锁一行数据：

在SQL语句后加上for uplate

select xoxox... for uplate

锁定某—行后，其它的操作会被阻塞，直到锁定行的会话提交commit

如何分析行锁定：

show status like 'innodb_row_lock%";
通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况

在这里插入图片描述
各状态量的说明如下：
在这里插入图片描述

对于这5个状态变量，比较重要的主要是：

  • Innodb_row_lock_time_avg（等待平均时长）
  • Innodb_row_lock_waits（等待总次数）
  • Innodb_row_lock_time（等待总时长）

尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手指定优化计划。

行锁优化建议：

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

数据库的乐观锁和悲观锁

  数据库管理系统（DBMS）中的并发控制的任务是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性。乐观并发控制（乐观锁）和悲观并发控制（悲观锁）是并发控制主要采用的技术手段。

悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。在查询完数据的时候就把事务锁起来，直到提交事务。
  实现方式：使用数据库中的锁机制。

乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。在修改数据的时候把事务锁起来，通过version的方式来进行锁定。
  实现方式：一般会使用版本号机制或CAS算法实现。

两种锁的使用场景:

  • 从上面对两种锁的介绍，我们知道两种锁各有优缺点，不可认为一种好于另一种，像乐观锁适用于写比较少的情况下（多读场景），即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。

  • 但如果是多写的情况，一般会经常产生冲突，这就会导致上层应用会不断的进行retry，这样反倒是降低了性能，所以一般多写的场景下用悲观锁就比较合适。


数据库范式理论

在这里插入图片描述

  • 超键：在关系中能唯一标识元组的属性集称为关系模式的超键。一个属性可以为作为一个超键，多个属性组合在一起也可以作为一个超键。超键包含候选键和主键。
  • 候选键：是最小超键，即没有冗余元素的超键。
  • 主键：数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值（Null）。
  • 外键：在一个表中存在的另一个表的主键称此表的外键。
逍遥自在”
关注
  • 1
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
MySQL高级学习(一)
小秋的博客
04-19 193
MySQL高级学习(一) 1、myisam和innoDB比较 2、SQL慢可能的原因 3、SQL的执行顺序 4、七种join语句 5、注意点 FUll OUT JOIN MySQL无法使用，要使用union关键词链接 第五个语句为： SELECT * from tbl_emp e left JOIN tbl_dept t on t.id = e.id UNION select * from tbl_emp e right join tbl_dept t on t.id = e.id 第七个语句为
Mysql高级学习(用法图解)
05-18
Mysql高级学习(用法图解)Mysql高级学习(用法图解)Mysql高级学习(用法图解)(字数？)
mysql高级学习
weixin_46926109的博客
03-08 92
** 索引 ** 我们要学习一个东西就要先了解他是干什么的有什么作用，学了以后对你会有什么帮助或者是让你感觉麻烦的事情不在那么麻烦，下面我们就来看一下这次我们要学习的内容。 一，索引 1，索引简介 1）什么是索引？ 一般的应用系统，读写比例在 10:1 左右，而且插入操作和一般的更新操作很少出现性能问题，在生产环境 中，我们遇到最多的，也是最容易出问题的，还是一些复杂的查询操作，因此对查询语句的优化显然是重中之重。 说起加速查询，就不得不提到索引了。 2) 为什么要有索引？ 索引在 MySQL 中也叫做“键
mysql理论技能_MySQL 高阶技能
weixin_39809140的博客
02-07 98
MySQL 高阶技能一、底层原理1、数据组织方式B+Tree2、逻辑架构C - S 架构客户端查询处理器数据存储引擎3、客户端使用增、删、改、查二、索引MySQL的InnoDB索引数据结构是B+树，主键索引叶子节点的值存储的就是MySQL的数据行，普通索引的叶子节点的值存储的是主键值MySQL索引用的是 B+树，B+树在查找效率、范围查找中都有着非常不错的性能。1、B 树和 B+树有什么不同呢？第...
mysql高级部分学习
chenjianhuideyueding的博客
03-11 3652
学习尚硅谷的ｍｙｓｑｌ高级视频的笔记。 1、mysql的配置文件。 在windows中是my.ini文件： 1）、log-bin：表示主从复制的日志文件，用于主从复制。 2）、log-error="SO3JFYNN8EW6GMO.err"：表示错误日志。 3）、server-id=1：表示本机的序号为1。 4）、datadir=D:/mysql5.7/Data：存放数据库信息的目录， ...
mysql高级学习（跟着尚硅谷老师周阳学习）
M1275601161的博客
06-21 835
跟着b站尚硅谷老师学习mysql高级教程
MySQL高级学习六
qq_38243612的博客
04-27 806
MySQL高级学习六MySQL复制复制的基本原理复制的类型binlog格式复制差异statement格式的binlogROW格式的binlogmixed格式的binlog MySQL复制 复制的基本原理 待补充 复制的类型 1、异步复制：原理简单，对网络延迟要求较小，不能保证日志被传送到备库，可能导致数据丢失 2、半同步复制：原理简单，对网络延迟有一定要求，最好在同一机房，可以保证日志被传送到了备库，不易丢失。 3、组复制：比较复杂，需要依赖共识算法，实际应用较少，是数据库走向原生分布式的示范 binlog
MySQL高级MySQL高级
02-21
在这个“MySQL高级”主题中，我们将深入探讨SQL语句，这是与MySQL交互的基础。 一、SQL基础 SQL（Structured Query Language）是一种用于管理关系数据库的标准语言。在MySQL中，SQL语句主要包括以下类型： 1. **...
MySQL学习笔记.md
12-07
观看b站：《老杜带你学_mysql入门基础》，所做的随堂笔记，杜老师讲的十分通俗易懂，respect！
Mysql高级教学
01-11
本资源内容包括Mysql高级部分教学视频(性能优化，SQL分析，高级索引建立等等等)，附教学笔记，方便小伙们复习。
Show Profile
taoy86的博客
10-21 222
前言：Show Profile是mysql提供的可以用来分析当前会话中sql语句执行的资源消耗情况的工具，可用于sql调优的测量。默认情况下处于关闭状态，并保存最近15次的运行结果。 1.分析步骤 #1.开启Show Profile功能，默认该功能是关闭的，使用前需开启。 show variables like 'profiling' set profiling=on; set profiling=off; show profile; #2.根据MySQL高级知识（十）——批量插入数据脚本
mysql group by百万数据_mysql百万级数据待优化(group by,sum)
weixin_28869849的博客
01-19 2605
需求：由于数据量极大，现在表的设计已经是一个月一个表。目前有二千多个设备，每小时上传一次数据。一个月就有2000*24小时*30=144万条。表设计如下：Rain201412ObserveTime bigintStationID varcharRain floatObserveTime和StationID做了联合主键和索引由于是灵活统计任意时段内的3小时，6小时，12小时，24小时的降水。每个...
MySQL面试篇章——MySQL核心SQL（基本语法）
itzzan的博客
07-17 944
扩展：group by 查询慢，explain 分析发现是 Using temporary，即产生了一个临时表，因此最好给分组列添加索引。数据控制语句，用于控制不同的许可和访问级别的语句。每次的 insert 操作都会有一个数据库连接操作，是占用资源的，一次连接完成的事情肯定比多次连接完成的事情所花费的时间短。数据操纵语句，用于添加、删除、更新和查询数据库记录，并检查数据完整性，常用的语句关键字主要包括。低，pageNum的偏移所花费的性能，要先偏移到对应的位置，在进行分页拿去后面的数据记录。
MySQL表中允许有多少个 TRIGGERS（触发器）？
sheji888的专栏
07-17 471
MySQL的触发器可以分为六种类型：BEFORE INSERT、AFTER INSERT、BEFORE UPDATE、AFTER UPDATE、BEFORE DELETE、AFTER DELETE，每种类型可以针对一个表定义多个触发器，但是每种类型的触发器在单个表上只能有一个与之关联的命名触发器。：虽然技术上讲每种类型的触发器在单个表上只能有一个命名触发器，但你可以通过创建具有不同名称的相同类型的多个触发器来绕过这个限制，只要这些触发器在触发时满足不同的条件（例如，检查不同的列或满足不同的业务逻辑）。
MySQL：SELECT 语句
最新发布
Zachyy的博客
07-20 333
在 MySQL 中，可以使用 SELECT语句来查询数据。查询数据是指从数据库中根据需求，使用不同的查询方式来获取不同的数据，是使用频率最高、最重要的操作。
尚硅谷MySQL高级学习笔记：存储引擎、SQL优化与索引详解
本资源是一份尚硅谷MySQL高级视频学习笔记，涵盖了多个关键知识点，旨在帮助用户深入理解并掌握MySQL高级操作。以下是对部分章节的详细解读： 1. Linux下MySQL编码设置：当在Linux环境下修改MySQL的字符集编码时，...

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

打赏作者

逍遥自在”

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

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

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

打赏作者

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

抵扣说明：

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

余额充值