mysql高级

一、mysql的Linux安装主要文件目录

1. /var/lib/mysql  mysql的数据文件也叫数据目录，lnmp安装的在/usr/local/mysql/var/
2. /usr/bin  相关命令目录
3. /etc/my.cnf  mysql配置文件
4. /usr/local/mysql 数据库安装目录

二、数据库文件

• 查看数据文件的存储位置，我使用的是lnmp安装的，一般在/var/lib/mysql下

show variables like 'datadir';

• 进入目录，创建的数据文件都会展示出来，下面以我创建的laravel数据库为例：

• laravel数据库里创建的表有users和student_myisam

• 进入laravel目录

• 数据库有users表数据引擎是InnoDB，对应两个文件users.frm、users.ibd
• 数据库有student_myisam表数据引擎是MyISAM，对应三个文件student_myisam.frm、student_myisam.MYD、student_myisam.MYI
• db.opt 里面记录的是laravel数据库的信息（字符集、比较规则等）
• users.frm 存储表结构的
• users.ibd 独立表空间用于存储数据和索引，mysql8.0中users.frm合并在users.ibd中
• student_myisam.frm 存储表结构的
• student_myisam.MYD和student_myisam.MYI相当于users.ibd 独立表空间用于存储数据和索引

三、用户与权限管理

四、逻辑架构

首先 MySQL 是典型的 C/S架构，即 client/Server 架构，服务器端程序使用的mysqld不论客户端进程和服务器进程是采用哪种方式进行通信，最后实现的效果都是:客户端进程向服务器进程发送-段文本(SQL语句)，服务器进程处理后再向客户端进程发送一段文本(处理结果)

那服务器进程对客户端进程发送的请求做了什么处理，才能产生最后的处理结果呢?这里以查询请求为例展示:

1. 第一步系统（客户端）访问 MySQL 服务器前，做的第一件事就是建立 TCP 连接
2. MySQL 服务器对 TCP 传输过来的账号密码做身份认证、权限获取
3. 接收用户的SQL命令并且返回用户需要查询的结果
4. 查询SQL命令是否有缓存如果有给第三步进行返回
5. 在解析器中对 SQL 语句进行语法分析、语义分析
6. SQL语句在语法解析之后、查询之前会使用查询优化器确定 SQL 语句的执行路径，生成一个 执行计划
7. 存储引擎层真正的负责了MySQL中数据的存储和提取，对物理服务器级别 维护的底层数据执行操作
8. 所有的数据，数据库、表的定义，表的每一行的内容，索引，都是存在 文件系统 上，以 文件 的方式存 在的，并完成与存储引擎的交互
9. MySQL内部维持着一些Cache和Buffer，比如Query Cache用来缓存一条SELECT语句的执行结 果，如果能够在其中找到对应的查询结果，那么就不必再进行查询解析、优化和执行的整个过 程了，直接将结果反馈给客户端（在mysql8.0中查询缓存这一步删除了，因为命中率很低）
10. 接收用户的SQL命令并且返回用户需要查询的结果

  SQL 语句在 MySQL 中的流程是： SQL语句→查询缓存→解析器→优化器→执行器 

查询缓存： 

查询缓存是提前把 查询结果缓存 起来，这样下次不需要执行就可以直接拿到结果。需要说明的是，在 MySQL 中的查询缓存，不是缓存查询计划，而是查询对应的结果。因为命中条件苛刻，而且只要数据表 发生变化，查询缓存就会失效，因此命中率低

 缓存池： 

缓存池和查询缓存是不一样的，mysql和oracle中都有缓存池，在 InnoDB 存储引擎中有一部分数据会放到内存中，缓冲池则占了这部分内存的大部分，它用来存储各种数据的缓存

缓冲池如何读取数据：

缓冲池管理器会尽量将经常使用的数据保存起来，在数据库进行页面读操作的时候，首先会判断该页面 是否在缓冲池中，如果存在就直接读取，如果不存在，就会通过内存或磁盘将页面存放到缓冲池中再进 行读取

查看/设置缓冲池的大小：

如果你使用的是 InnoDB 存储引擎，可以通过查看 innodb_buffer_pool_size 变量来查看缓冲池的大小。命令如下：

show variables like 'innodb_buffer_pool_size';

我们可以修改缓冲池大小，比如 改为512MB，方法如下：

set global innodb_buffer_pool_size = 536870912;

多个Buffer Pool实例：

Bufer Pool本质是innoDB向操作系统申请的一块 连续的内存空间，在多线程环境下，访问Bufer Pool中的数据都需要 加锁 处理。在Buffer Pool特别大而且多线程并发访问特别高的情况下，单一的Bufer Pool可能会影响请求的处理速度。所以在Buffer Pool特别大的时候，我们可以把它们拆分成若干个小的Buffer Poo1，每个Buffer Pool都称为一个 实例，它们都是独立的，独立的去申请内存空间，独立的管理各种链表。所以在多线程并发访问时并不会相互影响，从而提高并发处理能力。

我们看下如何查看缓冲池的个数，使用命令：

show variables like 'innodb_buffer_pool_instances';

我们可以在服务器启动的时候通过设置 innodb_buffer_pool_instances的值来修改Bufer Pool实例的个数，修改配置文件

[server]
innodb_buffer_pool_instances = 2

不过也不是说Buffer Pool实例创建的越多越好，分别管理各个Buffer Pool也是需要性能开销的，InnoDB规定:当innodb_buffer_pool_size的值小于1G的时候设置多个实例是无效的，InnoDB会默认把innodb bufer_pool instances的值修改为1。而我们鼓励在Bufer Pool大于或等于1G的时候设置多个Bufer Podl实例

Buffer Pool是MySQL内存结构中十分核心的一个组成，你可以先把它想象成一个黑盒子。

黑盒下的更新数据流程：

当我们查询数据的时候，会先去Buffer Pool中查询。如果Buffer Poo!中不存在，存储引擎会先将数据从磁盘加载到Buffer Pool中，然后将数据返回给客户端；同理，当我们更新某个数据的时候，如果这个数据不存在于BufferPool，同样会先数据加载进来，然后修改修改内存的数据。被修改过的数据会在之后统一刷入磁盘

五、存储引擎

1.查看存储引擎

show engines;

查询结果显示，MySQL8支持9种存储引擎，分别为PERFORMANCE_SCHEMA、CSV、InnoDB、BLACKHOLE、MyISAM、MEMORY、ARCHIVE、MRG_MYISAM、FEDERATED

• Engine 表示存储引擎名称
• Support 表示MySQL数据库管理系统是否支持该存储引擎:YES表示支持，NO表示不支持
• DEFAULT 表示系统默认支持的存储引擎
• Comment 对引擎的评论说明
• Transactions 表示存储引擎是否支持事务:YES表示支持，NO表示不支持
• XA 表示存储引擎所支持的分布式是否符合XA规范:YES表示支持，NO表示不支持。代表着该存储引擎是否支持分布式事务
• Savepoints 表示存储引擎是否支持事务处理的保存点:YES表示支持，NO表示不支持。也就是说，该存储引擎是否支持部分事务回滚

查看默认的存储引擎：

查看默认的存储引擎
show variables like '%storage_engine%';

#或者
SELECT @@default_storage_engine;

2.InnoDB 引擎 

1. 支持外键、支持事务
2. 除了增加和查询外，还需要更新、删除操作，那么，应优先选择InnoDB存储引擎
3. InnoDB是 为处理巨大数据量的最大性能设计
4. 行锁，操作时只锁某一行，不 对其它行有影响，适合高并发 的操作
5. 数据文件结构：表名.frm 存储表结构，表名.ibd 存储数据和索引
6. 对比MyISAM的存储引擎， InnoDB写的处理效率差一些 ，并且会占用更多的磁盘空间以保存数据和索引
7. MyISAM只缓存索引，不缓存真实数据；InnoDB不仅缓存索引还要缓存真实数据， 对内存要求较 高 ，而且内存大小对性能有决定性的影响

3.MyISAM 引擎

1. MyISAM提供了大量的特性，包括全文索引、压缩、空间函数(GIS)等，但MyISAM不支持事务、行级锁、外键 ，有一个毫无疑问的缺陷就是崩溃后无法安全恢复
2. 优势是访问的 速度快 ，对事务完整性没有要求或者以SELECT、INSERT为主的应用
3. 针对数据统计有额外的常数存储。故而 count(*) 的查询效率很高
4. 数据文件结构：表名.frm 存储表结构，表名.MYD 存储数据，表名.MYI 存储索引
5. 应用场景：只读应用或者以读为主的业务

4.Archive 引擎

1. archive 是 归档 的意思，仅仅支持插入和查询两种功能(行被插入后不能再修改)
2. 应用：国家数据档案存储
3. 拥有很好的压缩机制，使用 zlib压缩库，在记录请求的时候实时的进行压缩，经常被用来作为仓库使用
4. archive表适合日志和数据采集(档案)类应用，适合存储大量的独立的作为历史记录的数据，拥有很高的插入速度 ，但是对查询的支持较差

5.Blackhole 引擎

1. 丢弃写操作，读操作会返回空内容
2. Blackhole引擎没有实现任何存储机制，它会丢弃所有插入的数据 ，不做任何保存
3. 但服务器会记录Blackhole表的日志，所以可以用于复制数据到备库，或者简单地记录到日志。但这种应用方式会碰到很多问题，因此并不推荐

6.CSV 引擎

1. 存储数据时，以逗号分隔各个数据项
2. CSV引擎可以将 普通的CSV文件作为MySQL的表来处理，但不支持索引
3. CSV引擎可以作为一种 数据交换的机制，非常有用
4. CSV存储的数据直接可以在操作系统里，用文本编辑器，或者excel读取
5. 对于数据的快速导入、导出是有明显优势的

举例：创建一个csv引擎的表，往表里插入一条数据

CREATE TABLE test (i INT NOT NULL, c CHAR(10) NOT NULL) ENGINE = CSV;
INSERT INTO test VALUES(1,'record one'),(2,'record two');

在存储文件目录查看：

可以用Excel打开

7.Memory 引擎

1. Memory采用的逻辑介质是 内存 ，响应速度很快 ，但是当mysqld守护进程崩溃的时候 数据会丢失 。另 外，要求存储的数据是数据长度不变的格式，比如，Blob和Text类型的数据不可用(长度不固定的)
2. Memory同时 支持哈希（HASH）索引 和 B+树索引
3. Memory表至少比MyISAM表要 快一个数量级
4. MEMORY 表的大小是受到限制 的。表的大小主要取决于两个参数，分别是 max_rows 和 max_heap_table_size 。其中，max_rows可以在创建表时指定；max_heap_table_size的大小默 认为16MB，可以按需要进行扩大
5. 数据文件与索引文件分开存储
6. 缺点：其数据易丢失，生命周期短。基于这个缺陷，选择MEMORY存储引擎时需要特别小心
7. redis可以代替

8.Federated 引擎

1. Federated引擎是访问其他MySQL服务器的一个 代理 ，尽管该引擎看起来提供了一种很好的 跨服务器的灵活性 ，但也经常带来问题，因此默认是禁用的

9.Merge引擎

1. 管理多个MyISAM表构成的表集合

10.NDB引擎

1. MySQL集群专用存储引擎，也叫做 NDB Cluster 存储引擎，主要用于 MySQL Cluster 分布式集群环境，类似于Oracle的RAC 集群

11.MyISAM和InnoDB

对比项	MyISAM	InnoDB
外键	不支持	支持
事务	不支持	支持
行表锁	表锁，即使操作一条记录也会锁住 整个表，不适合高并发的操作	行锁，操作时只锁某一行，不对其它行有影响， 适合高并发的操作
缓存	只缓存索引，不缓存真实数据	不仅缓存索引还要缓存真实数据，对内存要求较 高，而且内存大小对性能有决定性的影响
自带系统表使用	Y	N
关注点	性能：节省资源、消耗少、简单业务	事务：并发写、事务、更大资源
默认安装	Y	Y
默认使用	N	Y

六、索引的数据结构

        索引是存储引擎用于快速找到数据记录的一种数据结构，就好比一本教课书的围录部分，通过目录中找到对应文章的页码，便可快速定位到需要的文章。MySQL中也是一样的道理，进行数据查找时，首先查看查询条件是否命中某条索引，符合则 通过索引査找 相关数据，如果不符合则需要 全表扫描，即需要一条一条地查找记录，直到找到与条件符合的记录。
        MySQL官方对索引的定义为:索引(Index) 是帮助MySQL高效获取数据的数据结构。

1.索引优缺点

1. 提高数据检索的效率，降低 数据库的I0成本（优点）
2. 通过创建唯一索引，可以保证数据库表中每一行数据的唯一性（优点）
3. 在实现数据的参考完整性方面，可以加速表和表之间的连接。换句话说，对于有依赖关系的子表和父表联合查询时，可以提高查询速度（优点）
4. 在使用分组和排序子句进行数据查询时，可以显著减少査询中分组和排序的时间，降低了CPU的消耗（优点）
5. 创建索引和维护索引要 耗费时间 ，并且随着数据量的增加，所耗费的时间也会增加（缺点）
6. 索引需要占磁盘空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，存储在磁盘上，如果有大量的索引，索引文件就可能比数据文件更快达到最大文件尺寸（缺点）
7. 虽然索引大大提高了查询速度，同时却会降低更新表的速度。当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度（缺点）

2.索引的设计路线

在说之前，首先要了解数据页、二叉搜索树

        数据页：在我们创建表会存储一条一条的记录，我们查询的时候，一次性全部查询数据量太大我们就会做分页处理，mysql的InnoDB存储引擎存储的方式也是采用分页的方式进行存储，InnoDB 中页的大小一般为16KB，数据超过16KB就新建一个数据页进行存储，页与页之间通过双向链表的方式进行连接，相互关联

        二分法：二分法搜索是在一个有序的数组中，对其砍半搜索

建一表：

CREATE TABLE users(
  id INT,
  name VARCHAR(20),
  phone CHAR(11),
  PRIMARY KEY(id)
);

在我们插入一条数据的时候，实际上数据库存储的信息是包括：

变长字段长度列表：

MySQL支持一些变长的数据类型，比如VARCHAR(M)、VARBINARY(M)、TEXT类型,BLOB类型，这些数据类型修饰列称为变长字段，变长字段中存储多少字节的数据不是因定的，所以我们在存储真实数据的时候需要顺便把这些数据占用的字节数也存起来。在Compact行格式中，把所有变长字段的真实数据占用的字节长度都存放在记录的开头部位从而形成一个变长字段长度列表

NULL值列表：

MySQL会把存在NULL值的列统一管理一起来

记录头信息包括：

名称	大小单位bit	描述
预留位1	1	没有使用
预留位2	1	没有使用
delete_mask	1	标记该记录是否被删除
min_rec_mask	1	B+树的每层非叶子节点中的最小记录都会添加该标记
n_owned	4	表示当前记录拥有的记录数
heap_no	13	表示当前记录在记录堆的位置信息（有两个记录Infimum和Supremum分别记录最小值和最大值）
record_type	3	表示当前记录的类型，0表示普通记录，1表示B+树非叶节点记录，2表示最小记录，3表示最大记录
next_record	16	表示下一条记录的相对位置

真实数据隐藏列：

列名	是否必须	占用空间	描述
row_id	否	6字节	行id，唯一标识一条记录
transaction_id	是	6字节	事务id
roll_pointer	是	7字节	回滚指针

3.哪些情况适合创建索引

1. 字段的数值有唯一性的限制
2. 频繁作为 WHERE 查询条件的字段
3. 经常 GROUP BY 和 ORDER BY 的列（两个同时使用联合索引效率最高）
4. UPDATE、DELETE 的 WHERE 条件列
5. DISTINCT 字段需要创建索引
6. 多表 JOIN 连接操作时，创建索引注意事项（字段在多张表中的 类型必须一致 ）
7. 使用列的类型小的创建索引
8. 使用字符串前缀创建索引（对字符串长的可以取前缀来创建索引）
9. 区分度高(散列性高)的列适合作为索引
10. 使用最频繁的列放到联合索引的左侧
11. 在多个字段都要创建索引的情况下，联合索引优于单值索引
12. 限制索引的数目建议单表索引数量不超过6个

4.哪些情况不适合创建索引

1. 在where中使用不到的字段，不要设置索引
2. 数据量小的表最好不要使用索引
3. 有大量重复数据的列上不要建立索引
4. 避免对经常更新的表创建过多的索引
5. 不建议用无序的值作为索引
6. 删除不再使用或者很少使用的索引
7. 不要定义冗余或重复的索引

七、SQL性能分析和查询优化

定位了查询慢的 SQL之后，我们就可以使用 EXPLAIN做针对性的分析查询语句

1.EXPLAIN分析：

EXPLAIN SELECT * FROM flats_user WHERE id = 1;

列表	描述
id	在一个大的查询语句中每个SELECT关键字都对应一个 唯一的id（id值越大，优先级越高）
select_type	SELECT关键字对应的那个查询的类型
table	表名
partitions	匹配的分区信息
type	针对单表的访问方法
possible_keys	可能用到的索引
key	实际上使用的索引
key_len	实际使用到的索引长度
ref	当使用索引列等值查询时，与索引列进行等值匹配的对象信息
rows	预估的需要读取的记录条数（值越小越好）
filtered	某个表经过搜索条件过滤后剩余记录条数的百分比
Extra	一些额外的信息

 type：

结果值从最好到最坏依次是： system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL 其中比较重要的几个提取出来。SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，最好是 consts级别。（阿里巴巴开发手册要求）

八、数据库设计规范

九、MySQL事务