MySQL进阶篇

标签：comment 存储索引进阶篇引擎 InnoDB MySQL 数据

1. 存储引擎

1.1 MySQL体系结构

连接层
最上层是一些客户端和链接服务，包含本地 sock 通信和大多数基于客户端 / 服务端工具实现的类似于 TCP / IP 的通信。主要完成一些类似于连接处理、授权认证及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于 SSL 的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。
服务层
第二层架构主要完成大多数的核心服务功能，如 SQL 接口，并完成缓存的查询，SQL 的分析和优化，部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如过程、函数等。在该层，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化，如确定表的查询顺序，是否利用索引等，最后生成相应的执行操作。如果是 select 语句，服务器还会查询内部的缓存，如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提升系统的性能。
引擎层
存储引擎层，负责 MySQL 中数据的存储和提取，服务器通过 API 和存储引擎进行通信。不同的存储引擎具有不同的功能，可以根据自己的需要，来选取合适的存储引擎。数据库中的索引是在存储引擎层实现的。
存储层
数据存储层，主要将数据(如: redolog、undolog、数据、索引、二进制日志、错误日志、查询日志、慢查询日志等) 存储在文件系统之上，并完成与存储引擎的交互。
和其他数据库相比，MySQL的不同之处在于：它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎上，插件式的存储引擎架构，将查询处理和其他的系统任务以及数据的存储提取分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。

1.2 存储引擎介绍

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

建表时指定存储引擎

create table 表名(
    字段1 字段1类型 [ comment 字段1注释 ] ,
    ......
    字段n 字段n类型 [ comment 字段n注释 ]
) engine = INNODB [ comment 表注释 ] ;

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

show engines;

1.3 存储引擎特点

1.3.1 InnoDB（默认引擎）

1、特点　　

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

2、文件

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

参数：innodb_file_per_table

show variables like 'innodb_file_per_table';

如果该参数开启，代表对于 InnoDB 引擎的表，每一张表都对应一个 ibd 文件。每一个 ibd 文件就对应一张表，而在这个 ibd 文件中不仅存放表结构、数据，还会存放该表对应的索引信息。而该文件是基于二进制存储的，不能直接基于记事本打开，我们可以使用 mysql 提供的一个指令 ibd2sdi ，通过该指令就可以从 ibd 文件中提取 sdi 信息，而 sdi 数据字典信息中就包含该表的表结构。
3、逻辑存储结构

MyISAM

1. 特点
  - 不支持事务，不支持外键
  - 支持表锁，不支持行锁
  - 访问速度快
2. 文件
  xxx.sdi：存储表结构信息
  xxx.MYD: 存储数据
  xxx.MYI: 存储索引

1.3.3 Memory

Memory 引擎的表数据是存储在内存中的，由于受到硬件问题或断电问题的影响，只能将这些表作为临时表或缓存使用。特点：内存存放、 hash 索引（默认）

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

2. 索引

2.1 索引概述

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

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

2.2 索引结构

2.2.1 概述

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

不同的存储引擎对于索引结构的支持:、

注意：我们平常所说的索引，如果没有特别指明，都是指 B+ 树结构组织的索引。（大数据量情况下，二叉树 / 红黑树层级较深，检索速度慢）

2.2.2 B+Tree
B+Tree 与 B-Tree 相比，主要有以下三点区别：

所有的数据都会出现在叶子节点。
叶子节点形成一个单向链表。
非叶子节点仅仅起到索引数据作用，具体的数据都是在叶子节点存放的。
MySQL 索引数据结构对经典的 B+Tree 进行了优化。在原 B+Tree 的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能，利于排序。

思考题：InnoDB主键索引的B+tree高度为多高呢?

假设: 一行数据大小为1k，一页中可以存储16行这样的数据。InnoDB的指针占用6个字节的空间，主键即使为 bigint，占用字节数为 8。
高度为2：n * 8 + (n + 1) * 6 = 16*1024 , 算出 n 约为 1170
1171* 16 = 18736
也就是说，如果树的高度为2，则可以存储 18000 多条记录。
高度为3：1171 * 1171 * 16 = 21939856
也就是说，如果树的高度为3，则可以存储 2200w 左右的记录。

数据结构可视化 - B+ 树

2.2.3 Hash
哈希索引就是采用一定的 hash 算法，将键值换算成新的 hash 值，映射到对应的槽位上，然后存储在 hash 表中。

特点
A. Hash 索引只能用于对等比较(=，in)，不支持范围查询（between，>，< ，…）
B. 无法利用索引完成排序操作
C. 查询效率高，通常(不存在 hash 冲突的情况)只需要一次检索就可以了，效率通常要高于 B+ tree 索引
存储引擎支持
在 MySQL 中，支持 hash 索引的是 Memory 存储引擎。而 InnoDB 中具有自适应hash 功能，hash 索引是 InnoDB 存储引擎根据 B+Tree 索引在指定条件下自动构建的。
为什么 InnoDB 存储引擎选择使用 B+tree 索引结构?
A. 相对于二叉树，层级更少，搜索效率高；
B. 对于B-tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低；
C. 相对 Hash 索引，B+tree 支持范围匹配及排序操作；

2.3 索引分类

2.3.1 索引分类

2.3.2 聚集索引&二级索引

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

聚集索引选取规则:

如果存在主键，主键索引就是聚集索引。
如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。
如果表没有主键，或没有合适的唯一索引，则 InnoDB 会自动生成一个 rowid 作为隐藏的聚集索引。
回表查询：这种先到二级索引中查找数据，找到主键值，然后再到聚集索引中根据主键值，获取数据的方式，就称之为回表查询。

2.4 索引语法
创建索引
create [ unique | fulltext ] index index_name on table name (index_col_name, ...);
1
查看索引
show index from table_name;
1
删除索引
drop index index_name on table_name;

案例演示
数据准备：创建一张表 tb_user

 1 create table tb_user(
 2     id int primary key auto_increment comment '主键',
 3     name varchar(50) not null comment '用户名',
 4     phone varchar(11) not null comment '手机号',
 5     email varchar(100) comment '邮箱',
 6     profession varchar(11) comment '专业',
 7     age tinyint unsigned comment '年龄',
 8     gender char(1) comment '性别 , 1: 男, 2: 女',
 9     status char(1) comment '状态',
10     createtime datetime comment '创建时间'
11 ) comment '系统用户表';
12 
13 INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
14 createtime) VALUES ('吕布', '17799990000', '[email protected]', '软件工程', 23, '1',
15 '6', '2001-02-02 00:00:00');
16 INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
17 createtime) VALUES ('曹操', '17799990001', '[email protected]', '通讯工程', 33,
18 '1', '0', '2001-03-05 00:00:00');
19 INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
20 createtime) VALUES ('赵云', '17799990002', '[email protected]', '英语', 34, '1',
21 '2', '2002-03-02 00:00:00');

标签：comment,存储,索引,进阶篇,引擎,InnoDB,MySQL,数据
From： https://www.cnblogs.com/liuwd/p/16853208.html