MySQL-进阶篇 ( 存储引擎 + 索引一 )

• MySQL-进阶篇 ( 存储引擎 + 索引一 )
  • 存储引擎
    • MySQL 体系结构
    • 存储引擎简介
    • 存储引擎特点
      • InnoDB
      • MyISAM
      • Memory
      • 引擎特点区分
    • 存储引擎选择
  • 索引
    • 索引概述
    • 索引结构
      • 二叉树
      • B-Tree ( B 树，多路平衡查找树 )
      • B+Tree ( B加树 )
      • Hash
      • 面试思考题
    • 索引分类
      • 在 InnoDB 存储引擎中，根据索引的存储形式，又可以分为以下两种：
      • 练习提问
    • 索引语法
    • SQL 性能分析
      • SQL 执行频率
      • 慢查询日志
      • profile 详请
      • explain 执行计划
        • EXPLAIN 各字段含义：

存储引擎

MySQL 体系结构

• 索引 index 是在存储引擎层实现的，不同的引擎索引的结构不同
  • 其中 InnoDB 是 MySQL 5.5 之后默认的存储引擎

存储引擎简介

• 存储引擎就是存储数据、建立索引、更新 / 查询数据等技术的实现方式。存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型

• 查看数据表设置的引擎

  • show create table account; 查看建表语句时会发现默认为 InnoDB
  • 右键目录里的表，选择 Go to DDL 查看

• 在创建表时，指定存储引擎

  • -- 建表时指定存储引擎
    CREATE TABLE 表名(
       ...
    ) ENGINE = INNODB[COMMENT 表注释];
    

• 查看当前数据库支持的存储引擎

  • show engines;

存储引擎特点

InnoDB

• 介绍

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

• 特点

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

• 文件

  • xxx.ibd：xxx 代表的是表名，innoDB 引擎的每张表都会对应这样一个表空间文件，存储该表的表结构 ( frm、sdi )、数据和索引
    • 在下载的 MySQL 目录下的 Data 下选择库名，打开就会找到 .ibd 文件
    • 想要打开看，可以选择库目录下 cmd 输入 ibd2sdi xxx.ibd
  • 参数：innodb_file_per_table
    • 决定了是多张表公用一个表空间文件，还是一张表一个表空间文件
    • 默认是打开，即每一张表都对应一个表空间文件
  • 查看开关：show variable like 'innodb_file_per_table';
    • ON 就是打开的

• 逻辑存储结构

  

  • TableSpace：表空间
  • Segment：段
  • Extent：区，是固定大小的，为 1M
    • 一个区可包含 64 个页
  • Page：页，是磁盘操作的最小单元，固定大小为 16K
  • Row：行
    • Trx id：最后一次操作事务的 id
    • Roll pointer：各指针
    • col：各字段

MyISAM

• 介绍
  • MyISAM 是 MySQL 早期的默认存储引擎
• 特点
  • 不支持事务，不支持外键
  • 支持表锁，不支持行锁
  • 访问速度快
• 文件
  • xxx.sdi：存储表结构信息
    • 打开后复制访问 Json.cn 粘贴后就会转成常见的 sql 语句
  • xxx.MYD：存储数据
  • xxx.MYI：存储索引

Memory

• 介绍
  • Memory 引擎的表数据是存储在内存中的，由于受到硬件问题或断点问题的影响，只能将这些表作为临时表或缓存使用
• 特点
  • 内存存放
  • hash 索引 ( 默认 )
• 文件
  • xxx.sdi：存储表结构信息

引擎特点区分

• 相比 MyISAM，InnoDB 支持事务、支持行锁、支持外键

存储引擎选择

• 在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合。

  • InnoDB：是 MySQL 的默认存储引擎，支持事务、外键。如果 —— 应用对事物的完整性有比较高的要求；在并发条件下要求数据的一致性；数据操作除了插入和查询之外，还包含很多的更新、删除操作。则 InnoDB 是比较合适的选择
  • MyISAM：如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不高，则此存储引擎是非常合适的。
    • 非核心数据，偶尔使用无碍
  • Memory：将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。Memory 的缺陷是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性

• 但其实只常用 InnoDB 了，因为 MyISAM 被非关系型数据库 mongdb 替代了，Memory 被非关系型数据库的 redis 替代了

索引

索引概述

• 介绍

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

• 演示引出

  • 无索引的话，想要查找某条数据，要遍历、一条条扫描
  • 有索引的话，例如使用维护了的二叉树结构，就可以在查找数据时更有效率

• 优点：

  • 提高数据检索效率，降低数据库的 IO 成本
  • 通过索引列对数据进行排序，降低数据排序的成本，降低 CPU 的消耗

• 缺点：

  • 索引列也是要占用磁盘空间的
    • 但磁盘相较不贵
  • 索引大大提高了查询效率，但降低了更新的速度，比如 INSERT、UPDATE、DELETE ( 就需要同时更新到节点中 )，效率降低
    • 查询占比例远大于增删改

索引结构

• MySQL 的索引是在存储引擎层实现的，不同的存储引擎有不同的结构，主要包含以下几种：

  索引结构	描述
  B+Tree 索引	最常见的索引类型，大部分引擎都支持 B+ 树索引
  Hash 索引	底层数据结构是用哈希表实现，只有精确匹配索引列的查询才有效，不支持范围查询
  R-Tree ( 空间索引 )	空间索引是 MyISAM 引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少
  Full-Text ( 全文索引 )	是一种通过建立倒排索引，快速匹配文档的方式，类似于 Lucene，Solr，ES ( 使用少 )

  • 引擎对索引的支持

    索引	InnoDB	MyISAM	Memory
    B+Tree	支持	支持	支持
    Hash	不支持	不支持	支持
    R-Tree	不支持	支持	不支持
    Full-text	5.6 版本之后支持	支持	不支持

• 我们平常所说的索引，如果没有特别指明，都是指 B+ 树结构组织的索引

二叉树

• 平常的二叉树有一个缺陷：顺序插入时，会形成一个链表，查询性能大大降低。在大数据情况下，层级较深，检索速度慢
• 红黑树可以来解决上一个的顺序插入的平衡问题，但：本质还是二叉树，大数据量情况下，层级较深，检索速度慢 ( 二叉树都会存在的问题 )

B-Tree ( B 树，多路平衡查找树 )

• 以一颗最大度数 ( max-degree ) 为 5 ( 5 阶 ) 的 b-tree 为例 ( 每个节点最多存储 4 个 key，5 个指针 )

  • n 个 key，指针则是 n + 1 个 ( 每个分开的区域都有一个指针 )

    

  • 树的度数指的是一个节点的子节点个数

• 5 阶为例，如若插入数据：23，234，345，860 后已形成 4 key、5 指针，再插入 1200 就会超出，所以开始计算这五个数的中间值，得将 345 上提为根，左下和右下各两个，若是下面又超出 4 个 key，所以再将中间值向上分裂与 345 同为根，直到根也要超出时，再于其中选出中间元素向上分裂为新的根

• 数据结构可视化网站

• B 树可视化部分

B+Tree ( B加树 )

• 实际为 B 树的变种

• 以一颗最大度数 ( max-degree ) 为 4 ( 4 阶 ) 的 b+tree 为例：

  • 最多 3 个 key，4 个指针

    

  • 上面非叶子节点主要起索引的作用，下面的叶子节点用来存放数据，于是下面的叶部分就形成了一个单向链表

• 若是同上5 阶为例，如若插入数据：23，234，345，860 后再插入 600，就会只把中间的 345 复制一个向上为根，下面的就是单向链表 “ 23 234 ——> 345 600 860 ” 这样所有的数据就都会在叶子节点处了，若是插入 1000 后，再插入 1234，则就会把中间的 860 上提到 345 的后面，下面的叶子就变成了 “ 23 234 ——> 345 600 ——> 860 1000 1234 ”

• MySQL 索引数据结构对经典的 B+Tree 进行了优化。在原 B+Tree 的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的 B+Tree，提高了区间访问的性能 ( 即单链变双向的循环链表 )

  

Hash

• 哈希索引就是采用一定的 hash 算法，将键值换算成新的 hash 值，映射到对应的槽位上，存储在 hash 表中
• 如果两个 ( 或多个 ) 键值映射到一个相同的槽位上，他们就产生了 hash 冲突 ( 也称为 hash 碰撞 )，可以通过链表来解决
• 特点：
  • Hash 索引只能用于对等比较 ( =, in )，不支持范围查询 ( between, >, <, ... )
  • 无法利用索引完成排序操作
  • 查询效率高，通常只需要一次检索就可以了 ( 即不出现 hash 碰撞的话 )，效率通常要高于 B+Tree 索引
• 存储引擎支持
  • 在 MySQL 中，支持 hash 索引的是 Memory 引擎，而 InnoDB 中具有自适应 hash 功能，hash 索引是存储引擎根据 B+Tree 索引在指定条件下自动构建的

面试思考题

为什么 InnoDB 存储引擎选择使用 B+Tree 索引结构？

• 相对于二叉树，层级更少，搜索效率高
• B-Tree 中无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针也跟着减少，要同样保存大量数据，只能增加树的高度，增加层级，导致性能降低
  • B+Tree 非叶子节点不存储数据，所以可以存放更多的 key、指针，所以相较而言层级更少
  • 而且查数据都要到叶子节点才能找到，效率稳定
  • 双向循环链表，便于范围查找与排序
• 相对 Hash 索引，B+Tree 支持范围匹配及排序操作

索引分类

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

• 也有将二级索引称为辅助索引、非聚集索引

• 聚集索引选取规则

  • 如果存在主键，主键索引就是聚集索引
  • 如果不存在主键，将使用第一个唯一 ( UNIQUE ) 索引作为聚集索引
  • 如果表没有主键或没有合适的唯一索引，则 InnoDB 会自动生成一个 rowid 作为隐藏的聚集索引

• 例：

  

  • 如上图示，第一个聚集索引下的叶子节点中的 row 便是每一行的数据，
  • 聚集索引只能有一个，第二个二级索引的叶子节点就不是存放一行的所有数据了 ( 否则会冗余 )，而是各 name 值对应的 id值

  

  • 在查找数据的时候就是上图所示
  • 回表查询：在二级索引中拿到主键值，然后回到聚集索引中，根据主键查到所有的信息

练习提问

• 以下 SQL 语句哪个执行效率更高？

  select * from user where id = 10;
  select * from user where name = 'Arm';
  -- 备注：id为主键，name字段创建的有索引
  

  • 第一条语句，因为就算 name 有索引，第二条还是需要回表查询

• InnoDB 主键索引的 B+Tree 高度为多少？

  • 一页大小固定 16K，假设：一行数据大小为 1k，一页就可以存储 16 行这样的数据。InnoDB 的指针固定占用 6 个字节的空间，key 占用主要取决于主键的类型，主键假设为 bigint，占用字节数为 8 ( int 的话就是 4，此处用较大的 bigint 举例 )

  • 可得公式：n * 8 + (n + 1) * 6 = 16 * 1024

    • 8 表示 bigint 占用的字节数，n 表示当前节点存储的 key 的数量，(n + 1) 表示指针数量 ( 比 key 多一个 )，一页 16K，1K 为 1024 个字节，算出 n 约为 1170。

  • 如果树的高度为2，那么他能存储的数据量大概为：1171 * 16 = 18736；如果树的高度为3，那么他能存储的数据量大概为：1171 * 1171 * 16 = 21939856

  • 所以两千多万条记录树的结构可能也只有 3 层，所以检索效率很高

    • 再多就涉及到运维相关的分库分表了

索引语法

• 创建索引

  • CREATE [UNIQUE|FULLTEXT] INDEX index_name ON table_name (index_col_name, ...);
    • CREATE 后不加参数就是创建常规索引

• 查看索引

  • SHOW INDEX FROM table_name;

• 删除索引

  • DROP INDEX index_name ON table_name;

• 练习：根据需求创建索引

  -- name字段为姓名字段，该字段的值可能会重复，为该字段创建索引(-- 只能常规)
  create index idx_user_name on tb_user(name); 
  
  -- phone手机号字段的值非空，且唯一，为该字段创建唯一索引
  create unique index idx_user_phone on tb_user(phone);
  
  -- 为profession, age, status创建联合索引
  create index idx_user_pro_age_stat on tb_user(profession, age, status);
  
  -- 为email建立合适的索引来提升查询效率(-- 常规即可)
  create index idx_user_email on tb_user(email);
  
  -- 创建完后可以 show index from 表名随时查看
  
  -- 删除索引
  drop index idx_user_email on tb_user;
  

  • 一般 idx 表示 index，下划线后紧跟表名和字段名

SQL 性能分析

SQL 执行频率

• MySQL 客户端连接成功后，通过 show [session|global] status 命令可以提供服务器状态信息 ( session 查看当前会话、global 查看全局的状态信息 ) 。通过如下指令，可以查看当前数据库的 INSERT、UPDATE、SELECT 的访问频次：
• SHOW GLOBAL STATUS LIKE 'Com_______'
  • Com 后的一个下划线就代表一个字符，此处是七个
• 执行完后就会显示出 Com_delete、Com_insert、Com_select、Com_update 等，以及各个 value 值 ( 访问频次 )

慢查询日志

• 已知 select 占多数，所以需要优化，而要针对哪些 select 语句进行优化？此时就用到了慢查询日志

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

  • 即默认情况下超过 10 秒就记录在慢查询日志中

• 可使用语句 show variables like 'show_query_log'; 查看慢日志是否开启，value 值为 OFF 就表示未开启

• MySQL 的慢查询日志默认没有开启，需要在 MySQL 的配置文件 ( /etc/my.cnf ) 中配置：

  -- linux 环境下(用到vi编辑器)：
  vi /etc/my.cnf
  -- 开启MySQL慢日志查询开关
  show_query_log=1
  
  -- 设置慢日志的时间为两秒，SQL语句执行时间超过两秒，就会视为慢查询，记录慢查询日志
  long_query_time=2
  
  -- 编辑完后 esc，输入 :x，再回车就会保存并退出，此时需要重启mysql再查看
  systemct1 restart mysqld
  

  • 查看慢日志文件中记录的信息 /var/lib/mysql/localhost-show.log

profile 详请

• 慢查询日志只会将超出规定时间的语句找出来，但如果语句执行工作简单但是用时很长，却又不超出规定时间，这样的慢查询就发现不了

• show profiles 能够在做 SQL 优化时帮助我们了解时间都耗费到哪里去了，通过 have_profiling 参数，能够看到当前 MySQL 是否支持：SELECT @@have_profiling;

  • 显示 YES 就表示支持

• 支持的话，再查看是否打开：SELECT @@profiling;

  • 显示 0 就表示没开启

• 默认 profiling 是关闭的，可以通过 set 语句在 session / global 级别开启 profiling：SET profiling = 1;

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

  -- 查看每一条SQL的耗时基本情况
  show profiles;
  -- query_id为排序(便于后面几个步骤使用)，Duration 即为耗时，Query里查看执行的语句
  
  -- 后面的kai'fa
  -- 查看指定query_id的SQL语句各个阶段的耗时情况
  show profile for query query_id; -- 此处的query_id便是show所示的数字
  
  -- 查看指定query_id的SQL语句CPU的使用情况
  show profile cpu for query query_id;
  

explain 执行计划

• 之前的都是根据执行时间粗略的进行判定，并不能真正评判 SQL 语句的性能，所以还要此步 explain
• EXPLAIN 或者 DESC 命令获取 MySQL 如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序
• 语法：直接在 select 语句之前加上关键字 explain / desc，即 EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件;
  • 就可以获取 select 语句的执行计划

EXPLAIN 各字段含义：

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

  • id 并不是自增长

  • id 若是相同，执行顺序从上到下

    • 即一条 select 语句涉及到多张表时，从上到下就是表的执行顺序

  • id 若是不同，值越大，越先执行

  • 练习：查询选修了 MySQL 课程 ( 子查询 )

    • explain select * from student s where s.id in (select studentid from student_course sc where sc.courseid = (select id from course c where c.name = 'MySQL'));

    • 上述语句可得

      id	table
      1	< subquery2 >
      1	s
      2	sc
      3	c

      执行顺序 c ——> sc ——> < subquery2 就是 in 后面的第二个子查询 > ——> s

• select_type：表示 SELECT 的类型，常见取值有 SIMPLE ( 简单表，即不适用表连接或者子查询 )、PRIMARY ( 主查询，即外层的查询 )、UNION ( UNION 中的第二个或者后面的查询语句 )、SUBQUERY ( SELECT / WHERE 之后包含了子查询 ) 等

• type：表示连接类型，性能由好到差的连接类型为 NULL、system、const、eq_ref、ref、range、index、all

  • 查询的时候不访问任何表的话，就会出现 NULL，
    • 尽量往前优化即可，一般无法优化成 NULL
  • 相当于访问系统表时为 system
  • 根据主键和唯一索引时为 const
  • 非唯一性的索引时为 ref

• possible_keys：可能应用在这张表上的索引，一个或多个

• key：实际使用的索引，如果为 NULL，则没有使用索引

• key_len：表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好

• rows：MySQL 认为必须要执行的行数，在 InnoDB 引擎的表中，是一个估计值，可能并不总是准确的

• filtered：表示返回结果的行数占需读取行数的百分比，filtered 的值越大越好

• 重点关注的字段：type、possible_keys、key、key_len