[MySQL]进阶篇-Day1

[MySQL]进阶篇-Day1

学习使用工具

《数据库系统概念（中文第六版）》

黑马程序员 MySQL数据库入门到精通 https://www.bilibili.com/video/BV1Kr4y1i7ru/

https://hillzhang1999.gitee.io/2020/05/29/shu-ju-ku-fu-xi-ji-yu-mysql/#toc-heading-76

一、存储结构

  1. 连接层

    最上层是一些客户端和链接服务，包含本地sock 通信和大多数基于客户端/服务端工具实现的类似于TCP/IP的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。

  2. 服务层

    第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化，部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如 过程、函数等。在该层，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化如确定表的查询的顺序，是否利用索引等，最后生成相应的执行操作。如果是select语句，服务器还会查询内部的缓存，如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提升系统的性能。

  3. 引擎层

    存储引擎层， 存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API和存储引擎进行通信。不同的存储引擎具有不同的功能，这样我们可以根据自己的需要，来选取合适的存储引擎。数据库中的索引是在存储引擎层实现的。

  4. 存储层

    数据存储层， 主要是将数据(如: redolog、undolog、数据、索引、二进制日志、错误日志、查询日志、慢查询日志等)存储在文件系统之上，并完成与存储引擎的交互。

    ​

二、存储引擎

存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型。我们可以在创建表的时候，来指定选择的存储引擎，如果没有指定将自动选择默认的存储引擎。

建表时指定存储引擎

CREATE TABLE 表名(
字段1 字段1类型 [ COMMENT 字段1注释 ] ,
......
字段n 字段n类型 [COMMENT 字段n注释 ]
) ENGINE = INNODB [ COMMENT 表注释 ] ;

最常用的两类引擎：InnoDB、MyISAM

  1. InnoDB

    是一种兼顾高可靠性和高性能的通用存储引擎，在 MySQL 5.5 之后，InnoDB是默认的MySQL 存储引擎。

    • 特点

      DML操作遵循ACID模型，支持事务; 行级锁，提高并发访问性能；支持外键FOREIGN KEY约束，保证数据的完整性和正确性。

      • 插入缓冲（insert buffer）
      • 二次写（double write）
      • 自适应哈希索引（ahi）
      • 预读（read ahead）

    • 文件

      xxx.ibd：xxx代表的是表名，innoDB引擎的每张表都会对应这样一个表空间文件，存储该表的表结构（frm-早期的 、sdi-新版的）、数据和索引。

    • 逻辑存储结构

      • 表空间 : InnoDB存储引擎逻辑结构的最高层，ibd文件其实就是表空间文件，在表空间中可以包含多个Segment段。
      • 段 : 表空间是由各个段组成的， 常见的段有数据段、索引段、回滚段等。InnoDB中对于段的管理，都是引擎自身完成，不需要人为对其控制，一个段中包含多个区。
      • 区 : 区是表空间的单元结构，每个区的大小为1M。 默认情况下， InnoDB存储引擎页大小为16K， 即一个区中一共有64个连续的页。
      • 页 : 页是组成区的最小单元，页也是InnoDB 存储引擎磁盘管理的最小单元，每个页的大小默认为 16KB。为了保证页的连续性，InnoDB 存储引擎每次从磁盘申请 4-5 个区。
      • 行 : InnoDB 存储引擎是面向行的，也就是说数据是按行进行存放的，在每一行中除了定义表时所指定的字段以外，还包含两个隐藏字段(后面会详细介绍)。

  2. MyISAM

    是MySQL早期的默认存储引擎。

    • 特点

      不支持事务，不支持外键。支持表锁，不支持行锁。访问速度快

    • 文件

      xxx.sdi：存储表结构信息
      xxx.MYD: 存储数据
      xxx.MYI: 存储索引

  3. Memory

    Memory引擎的表数据时存储在内存中的，由于受到硬件问题、或断电问题的影响，只能将这些表作为临时表或缓存使用。

    • 特点：hash存放
    • 文件：xxx.sdi：存储表结构信息

  4. 存储引擎的选择

    若数据库平台读多写少，可以选择MyISAM引擎，如博客系统、学习网站等；

    若数据库平台更新操作（增删改）较多，则选择InnoDB，如信息管理系统等。

  5. 完整区别

    • InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务；
    • InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败
    • InnoDB使用B+Tree作为索引结构，数据文件是和（主键）索引绑在一起的（表数据文件本身就是按B+Tree组织的一个索引结构），必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。而MyISAM也是使用B+Tree作为索引结构，索引和数据文件是分离的，索引保存的是数据文件的指针。**主键索引和辅助索引是独立的。
    • InnoDB不保存表的具体行数，执行select count∗∗ from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快（注意不能加有任何WHERE条件）
    • MyISAM表格可以被压缩后进行查询操作
    • InnoDB支持表、行默认默认级锁，而MyISAM支持表级锁
    • InnoDB表必须有主键（用户没有指定的话会自己找或生产一个主键），而Myisam可以没有

    如何选择：

    • 是否要支持事务，如果要请选择innodb，如果不需要可以考虑MyISAM
    • 如果表中绝大多数都只是读查询，可以考虑MyISAM，如果既有读也有写，请使用InnoDB
    • 系统崩溃后，MyISAM恢复起来更困难，能否接受；
    • MySQL5.5版本开始Innodb已经成为Mysql的默认引擎，建议使用InnoDB

    ​

三、MySQL索引

索引（index）是帮助MySQL高效获取数据的数据结构(有序)。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

MySQL的基本存储结构：页，记录都存储在页里面。

  • 各个数据页可以组成一个双向链表。
  • 每个数据页中的行记录组成单向链表。
  • 每个数据页都会为存储在它里边儿的记录生成一个页目录，在通过主键（聚集索引）查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录。
  • 以其他列作为搜索条件：只能从最小记录开始依次遍历单链表中的每条记录。

MySQL的索引存储的是创建索引的列上的属性值和对应记录所在物理存储位置的指针。

  • 特点

    • 优点：提高数据检索的效率，降低数据库的IO成本，通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。
    • 缺点：索引列也是要占用空间的，索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行INSERT、UPDATE、DELETE时，效率降低。

  • 常见结构

    • B+树索引：最常见的索引类型
    • Hash索引：底层数据结构是用哈希表实现的, 只有精确匹配索引列的查询才有效，不支持范围查询
    • 空间索引：是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少
    • 全文索引：是一种通过建立倒排索引，快速匹配文档的方式

  • 索引分类

    • 主键索引：针对于表中主键创建的索引，默认自动创建, 只能有一个。关键字为PRIMARY
    • 唯一索引：避免同一个表中某数据列中的值重复。可以有多个。关键字为UNIQUE
    • 常规索引：快速定位特定数据。可以有多个。
    • 全文索引：全文索引查找的是文本中的关键词，而不是比较索引中的值。可以有多个。关键字为FULLTEXT。

    在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种：

    • 聚集索引：将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据。必须有，只能有一个。
      • 聚集索引选取规则：如果存在主键，主键索引就是聚集索引。如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。
    • 二级索引：将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键。可以存在多个。

    ​

    ​

四、SQL性能分析

MySQL 客户端连接成功后，通过 show [session|global] status 命令可以提供服务器状态信息。通过如下指令，可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次：

-- session 是查看当前会话 ;
-- global 是查询全局数据 ;
SHOW GLOBAL STATUS LIKE 'Com_______';

通过查询SQL的执行频次，我们就能够知道当前数据库到底是增删改为主，还是查询为主。

  1. 慢查询日志

    慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志。

    如果要开启慢查询日志，需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息：

    # 开启MySQL慢日志查询开关
slow_query_log=1
# 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2

    通过慢查询日志，就可以定位出执行效率比较低的SQL，从而有针对性的进行优化。

  2. profile详情

show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。可以通过set语句在session/global级别开启profiling：

SET profiling = 1;

执行一系列的业务SQL的操作，然后通过如下指令查看指令的执行耗时：

-- 查看每一条SQL的耗时基本情况
show profiles;
-- 查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;
-- 查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id;

  1. explain

    EXPLAIN 或者 DESC命令获取 MySQL 如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。

    -- 直接在select语句之前加上关键字 explain / desc
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件 ;

五、最左前缀法则

如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将会部分失效(后面的字段索引失效)。

举例：在 tb_user 表中，有一个联合索引，这个联合索引涉及到三个字段，顺序分别为：profession，age，status。对于最左前缀法则指的是，查询时，最左边的列，也就是profession必须存在，否则索引全部失效。而且中间不能跳过某一列，否则该列后面的字段索引将失效。

联合索引中，出现范围查询(>,<)，范围查询右侧的列索引失效。在业务允许的情况下，尽可能的使用类似于 >= 或 <= 这类的范围查询，而避免使用 > 或 <。

posted @ 2023-04-30 19:18  无机呱子  阅读(18)  评论(0)  编辑  收藏  举报