文章目录

  • 一、linux环境运行mysql
    • 1.查看mysql运行版本
    • 2.服务的初始化
    • 3.启动MySQL，查看状态
    • 4.MySQL服务自启动
      • a.查看MySQL是否自启动
      • b.设置MySQL自启动
      • c.关闭M有SQL自启动
    • 5.启动MySQL
      • a.首次登陆使用初试密码
      • b.修改密码
    • 6.设置远程登录
      • a.确认网络
      • b.关闭防火墙或开放端口
      • c.使用sql工具远程连接
    • 7.Linux环境修改配置
    • 8.MySQL在Linux下数据库名、表名、列名、别名大小写规则
    • 9.SQL编写建议
  • 二、MySQL的数据目录
    • 1.MySQL8的主要目录结构
      • a.数据库文件的存放路径
      • b.相关命令目录
      • c.配置文件目录
    • 2.数据库和文件系统的关系
      • a.查看默认数据库
  • 三、用户与权限管理
      • 1.用户管理
      • a.登录mysql服务器
      • b.创建用户
      • c.修改用户
      • d.删除用户
      • e.设置当前用户密码
      • f.修改其他用户密码
    • 2.权限管理
      • a.权限列表
      • b.授予权限的原则
      • c.授予权限
      • d.查看权限
      • e.收回权限
      • f.所有权限
    • 3.权限表
      • a.user表
      • b.db表
      • c.tables_priv表和columns_priv表
      • d.procs_priv表
    • 4.角色管理
      • a.角色概念
      • b.创建角色
      • c.给角色赋予权限
      • d.查看角色的权限
      • e.回收角色的权限
      • f.删除角色
      • g.给用户赋予角色
      • h.激活角色
      • i.撤销用户的角色
  • 四、逻辑架构
    • 1.逻辑架构剖析
      • a.服务器处理客户端请求
      • b.Connectors
      • c.第一层：连接层
      • d.第二层：服务层
      • e.第三层：引擎层
      • f.存储层
      • g.小结
    • 2.SQL执行流程
      • a.MySQL中的SQL执行流程
      • b.MySQL8中SQL执行原理
        • 1)确认profiling是否开启
        • 2)多次执行相同SQL查询
        • 3)查看profiles
        • 4)查看profile
    • 3.数据库缓冲池(bufferpool)
      • a.缓冲池vs查询缓存
        • 1)缓冲池(BufferPool)
      • b.缓冲池如何读取数据
      • c.查看/设置缓冲池的大小
      • d.多个BufferPool实例
  • 五、存储引擎
    • 1.查看存储引擎
    • 2.系统默认的存储引擎
    • 3.设置表的存储引擎
      • a.创建表时指定存储引擎
      • b.修改表的存储引擎
      • c.查看表结构
    • 4.引擎介绍
      • a.InnoDB引擎：具备外键支持功能的事务存储引擎
      • b.MyISAM引擎：主要的非事务处理存储引擎
      • c.Archive引擎：用于数据存档
      • d.Blackhole引擎：丢弃写操作，读操作会返回空内容
      • e.CSV引擎：存储数据时，以逗号分隔各个数据项
      • f.Memory引擎：置于内存的表
      • g.Federated引擎：访问远程表
      • h.Merge引擎：管理多个MyISAM表构成的表集合
      • i.NDB引擎：MySQL集群专用存储引擎

一、linux环境运行mysql

1.查看mysql运行版本

mysql--version

2.服务的初始化

为了保证数据库目录与文件的所有者为mysql登录用户，如果以root身份运行mysql服务，需要执行下面的命令初始化

mysqld--initialize--user=mysql

说明：–initialize选项默认以“安全”模式来初始化，则会为root用户生成一个密码并将该密码标记为过期，登录后需要设置一个新的密码。生成的临时密码会往日志中记录一份

查看密码

cat/var/log/mysqld.log

root@localhost:后面就是初始化的密码

3.启动MySQL，查看状态

启动：systemctl start mysqld.service

关闭：systemctl stop mysqld.service

重启：systemctl restart mysqld.service

查看状态：systemctl status mysqld.service

4.MySQL服务自启动

a.查看MySQL是否自启动

systemctllist-unit-files|grepmysqld.service

b.设置MySQL自启动

systemctl enable mysqld.service

c.关闭M有SQL自启动

systemctl disable mysqld.service

5.启动MySQL

a.首次登陆使用初试密码

mysql-uroot-p

b.修改密码

ALTERUSER'root'@'localhost'IDENTIFIEDBY'root';

6.设置远程登录

a.确认网络

  1. 在远程机器上使用pingip地址保证网络畅通
  2. 在远程机器上使用telnet命令保证端口号开放访问
    • telnet192.168.117.1003306

b.关闭防火墙或开放端口

关闭防火墙

systemctl start firewalld.service

systemctl status firewalld.service

systemctl stop firewalld.service

systemctl enable firewalld.service#设置开机启用防火墙

systemctl disable firewalld.service#设置开机禁用防火墙

开放端口

开放的端口号

firewall-cmd--list-all

设置开放的端口号

firewall-cmd--add-service=http--permanent

firewall-cmd--add-port=3306/tcp--permanent

重启防火墙

firewall-cmd--reload

c.使用sql工具远程连接

注意

  • 这里的密码是虚拟机上mysql的密码

注意

  • 这里的密码是虚拟机的密码

7.Linux环境修改配置

  1. use mysql;
  2. select host,user from user;
  3. update user set host='%' where user = 'root';
  4. ALTERUSER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'root';
  5. flush privileges;

8.MySQL在Linux下数据库名、表名、列名、别名大小写规则

  1. 数据库名、表名、表的别名、变量名是严格区分大小写的
  2. 关键字、函数名称在SQL中不区分大小写
  3. 列名(或字段名)与列的别名(或字段别名)在所有的情况下均是忽略大小写的
  4. MySQL在Windows的环境下全部不区分大小写

9.SQL编写建议

  1. 关键字和函数名称全部大写
  2. 数据库名、表名、表别名、字段名、字段别名等全部小写；
  3. SQL语句必须以分号结尾

二、MySQL的数据目录

1.MySQL8的主要目录结构

find/-namemysql

a.数据库文件的存放路径

MySQL数据库文件的存放路径：/var/lib/mysql/

b.相关命令目录

/usr/bin(mysqladmin、mysqlbinlog、mysqldump等命令)和/usr/sbin

c.配置文件目录

配置文件目录：/usr/share/mysql-8.0(命令及配置文件)，/etc/mysql(如my.cnf)

2.数据库和文件系统的关系

a.查看默认数据库

SHOW DATABASES;

mysql自带的四个数据库

  • mysql
    • MySQL系统自带的核心数据库，它存储了MySQL的用户账户和权限信息，一些存储过程、事件的定义信息，一些运行过程中产生的日志信息，一些帮助信息以及时区信息等
  • information_schema
    • MySQL系统自带的数据库，这个数据库保存着MySQL服务器维护的所有其他数据库的信息，比如有哪些表、哪些视图、哪些触发器、哪些列、哪些索引。这些信息并不是真实的用户数据，而是一些描述性信息，有时候也称之为元数据。在系统数据库information_schema中提供了一些以innodb_sys开头的表，用于表示内部系统表
  • performance_schema
    • MySQL系统自带的数据库，这个数据库里主要保存MySQL服务器运行过程中的一些状态信息，可以用来监控MySQL服务的各类性能指标。包括统计最近执行了哪些语句，在执行过程的每个阶段都花费了多长时间，内存的使用情况等信息
  • sys
    • MySQL系统自带的数据库，这个数据库主要是通过视图的形式把information_schema和performance_schema结合起来，帮助系统管理员和开发人员监控MySQL的技术性能

三、用户与权限管理

1.用户管理

a.登录mysql服务器

mysql –hhostname|hostIP –Pport –uusername– p DatabaseName–e"SQL语句"

  • -h参数后面接主机名或者主机IP，hostname为主机，hostIP为主机IP，eg：-hlocalhost
  • -P参数后面接MySQL服务的端口，通过该参数连接到指定的端口。MySQL服务的默认端口是3306，不使用该参数时自动连接到3306端口，port为连接的端口号，eg：-P3306
  • -u参数后面接用户名，username为用户名，eg：-uroot
  • -p参数会提示输入密码，eg：-p
  • DatabaseName参数指明登录到哪一个数据库中。如果没有该参数，就会直接登录到MySQL数据库中，然后可以使用USE命令来选择数据库，eg：databaseName
  • -e参数后面可以直接加SQL语句。登录MySQL服务器以后即可执行这个SQL语句，然后退出MySQL服务器，eg：-e"selecthost,userfromuser"

b.创建用户

CREATE USER用户名 [ IDENTIFIED BY '密码' ][ , 用户名[IDENTIFIED BY '密码']];

  • 用户名参数表示新建用户的账户，由用户(User)和主机名(Host)构成，Host默认是%
  • []表示可选，也就是说，可以指定用户登录时需要密码验证，也可以不指定密码验证，这样用户可以直接登录
  • CREATEUSER语句可以同时创建多个用户
  • eg：
    • CREATE USER zhangsan IDENTIFIED BY'123';
    • CREATE USER 'lisi'@'localhost' IDENTIFIED BY '123';

c.修改用户

UPDATE mysql.user SET USER='修改后的用户名' WHERE USER = '想要修改的用户名';

FLUSH PRIVILEGES;

d.删除用户

  • 方式1

    • DROP USER user[,user]…;

    • 注意

      其中删除用户时，如果用户指定了Host，则必须加上Host

      DROP USER zhangsan;

      DROP USER 'lisi'@'localhost';

  • 方式2

    • DELETE FROM mysql.user WHERE Host = 'hostname' AND User = 'username';
    • FLUSHPRIVILEGES;
    • eg：DELETE FROM mysql.user WHERE Host='localhost' AND User ='lisi';

e.设置当前用户密码

  • 方式1
    • 使用ALTERUSER命令来修改当前用户密码
    • ALTER USER USER() IDENTIFIED BY 'new_password';
  • 方式2
    • 使用root用户登录MySQL，使用SET语句来修改密码，自动将密码加密后赋值给当前用户
    • SET PASSWORD ='new_password';

f.修改其他用户密码

  • 方式1
    • 使用ALTER语句来修改普通用户的密码
    • ALTER USER user [ IDENTIFIED BY '新密码' ][ , user [ IDENTIFIEDBY '新密码']]…;
  • 方式2
    • 使用root用户登录到MySQL服务器后，可以使用SET语句来修改普
      通用户的密码
    • SET PASSWORD FOR 'username'@'hostname' = 'new_password';

2.权限管理

a.权限列表

show privileges;

  1. CREATE和DROP权限，可以创建新的数据库和表，或删除(移掉)已有的数据库和表
  2. SELECT、INSERT、UPDATE和DELETE权限允许在一个数据库现有的表上实施操作
  3. INDEX权限允许创建或删除索引，INDEX适用于已有的表。如果具有某个表的CREATE权限，可以在CREATETABLE语句中包括索引定义
  4. ALTER权限可以使用ALTERTABLE来更改表的结构和重新命名表
  5. CREATEROUTINE权限用来创建保存的程序(函数和程序)
  6. ALTERROUTINE权限用来更改和删除保存的程序
  7. EXECUTE权限用来执行保存的程序
  8. GRANT权限允许授权给其他用户，可用于数据库、表和保存的程序
  9. FILE权限使用户可以使用LOADDATAINFILE和SELECT…INTOOUTFILE语句读或写服务器上的文件，任何被授予FILE权
    限的用户都能读或写MySQL服务器上的任何文件(用户可以读任何数据库目录下的文件，因为服务器可以访问这些文件)
权限分布说明
表权限SELECT、INSERT、DELETE、UPDATE、CREATE、DROP、GRANT、REFERENCES、INDEX、ALTER
列权限SELECT、INSERT、UPDATE、REFERENCES
过程权限EXECUTE、ALTERROUTINE、GRANT

b.授予权限的原则

  1. 只授予能满足需要的最小权限
  2. 创建用户的时候限制用户的登录主机，一般是限制成指定IP或者内网IP段
  3. 为每个用户设置满足密码复杂度的密码
  4. 定期清理不需要的用户，回收权限或者删除用户

c.授予权限

GRANT 权限1,权限2,…权限nON数据库名称.表名称 TO 用户名@用户地址 [ IDENTIFIED BY‘密码口令’];

授予用户名@用户地址在数据库或表权限1、权限2…，设置带有密码

d.查看权限

  • 查看当前用户权限
    • SHOW GRANTS;
  • 查看某用户的全局权限
    • SHOW GRANTS FOR'user'@'主机地址';

e.收回权限

在将用户账户从user表删除之前，应该收回相应用户的所有权限

REVOKE 权限1,权限2,…权限n ON数据库名称.表名称 FROM 用户名@用户地址;

f.所有权限

ALL PRIVILEGES：代表除了GRANT的所有权限

3.权限表

a.user表

user表是MySQL中最重要的一个权限表，记录用户账号和权限信息，分成4类，分别是范围列(或用户列)、权限列、安全列和资源控制列

  1. 范围列(或用户列)
    • host：表示连接类型
      • %表示所有远程通过TCP方式的连接
      • IP地址如(192.168.1.2、127.0.0.1)通过制定ip地址进行的TCP方式的连接
      • 机器名通过制定网络中的机器名进行的TCP方式的连接
      • ::1IPv6的本地ip地址，等同于IPv4的127.0.0.1
      • localhost本地方式通过命令行方式的连接，比如mysql-uxxx-pxxx方式的连接
    • user：表示用户名，同一用户通过不同方式链接的权限是不一样的
    • password：密码
      • 所有密码串通过password(明文字符串)生成的密文字符串。MySQL8.0在用户管理方面增加了角色管理，默认的密码加密方式也做了调整，由之前的SHA1改为了SHA2，不可逆。同时加上MySQL5.7的禁用用户和用户过期的功能，MySQL在用户管理方面的功能和安全性都较之前版本大大的增强了
  2. 权限列
    • Grant_priv字段
      • 表示是否拥有GRANT权限
    • Shutdown_priv字段
      • 表示是否拥有停止MySQL服务的权限
    • Super_priv字段
      • 表示是否拥有超级权限
    • Execute_priv字段
      • 表示是否拥有EXECUTE权限
    • Select_priv,Insert_priv等
      • 为该用户所拥有的权限
  3. 安全列
    • 安全列只有6个字段，其中两个是ssl相关，用于加密；两个是x509相关，用于标识用户；两个Plugin字段用于验证用户身份的插件
  4. 资源控制列
    • 资源控制列的字段用来限制用户使用的资源，包含4个字段
      1. max_questions，用户每小时允许执行的查询操作次数
      2. max_updates，用户每小时允许执行的更新操作次数
      3. max_connections，用户每小时允许执行的连接操作次数
      4. max_user_connections，用户允许同时建立的连接次数

b.db表

DESCRIBE mysql.db;

  1. 用户列
    • db表用户列有3个字段，分别是Host、User、Db。分别表示主机名、用户名和数据库名。表示从某个主机连接某个用户对某个数据库的操作权限，这3个字段的组合构成了db表的主键
  2. 权限列
    • Create_routine_priv和Alter_routine_priv这两个字段决定用户是否具有创建和修改存储过程的权限

c.tables_priv表和columns_priv表

  1. tables_priv表用来对表设置操作权限
    • descmysql.tables_priv;
    • tables_priv表有8个字段，分别是Host、Db、User、Table_name、Grantor、Timestamp、Table_priv和Column_priv
      • Host、Db、User和Table_name四个字段分别表示主机名、数据库名、用户名和表名
      • Grantor表示修改该记录的用户
      • Timestamp表示修改该记录的时间
      • Table_priv表示对象的操作权限。包括Select、Insert、Update、Delete、Create、Drop、Grant、References、Index和Alter
      • Column_priv字段表示对表中的列的操作权限，包括Select、Insert、Update和References
  2. columns_priv表用来对表的某一列设置权限
    1. descmysql.columns_priv;

d.procs_priv表

procs_priv表可以对存储过程和存储函数设置操作权限

descmysql.procs_priv;

4.角色管理

a.角色概念

引入角色的目的是方便管理拥有相同权限的用户，恰当的权限设定，可以确保数据的安全性

b.创建角色

CREATEROLE'role_name'[@'host_name'][,'role_name'[@'host_name']]...

  • host_name省略，默认为%
  • role_name不可省略

c.给角色赋予权限

创建一个角色，系统就会自动给一个“USAGE”权限，意思是连接登录数据库的权限，其余权限默认不赋予

GRANT 权限1,权限2... ON table_name TO 'role_name'[@'host_name'];

d.查看角色的权限

SHOW GRANTS FOR 'rolename';

e.回收角色的权限

REVOKE privileges ON tablename FROM 'rolename';

f.删除角色

DROP ROLE role[,role2]...

g.给用户赋予角色

GRANT role [,role2,...]TOuser[,user2,...];

h.激活角色

  1. 方式1：使用set default role命令激活角色，为用户激活所有已拥有的角色
    • SET DEFAULT ROLE ALL TO'用户名';
  2. 方式2：将activate_all_roles_on_login设置为ON
    • SET GLOBAL activate_all_roles_on_login=ON;对所有角色永久激活

i.撤销用户的角色

REVOKE role FROM user;

四、逻辑架构

1.逻辑架构剖析

a.服务器处理客户端请求

MySQL是典型的C/S架构，即Clinet/Server架构，服务端程序使用的mysqld

客户端进程向服务器进程发送一段文本(SQL语句)，服务器进程处理后再向客户端进程发送一段文本(处理结果)

b.Connectors

Connectors,指的是不同语言中与SQL的交互。使用MySQL，首先与MySQL建立TCP连接，之后按照其定义好的协议进行交互。或者比较方便的方法是调用SDK，比如NativeCAPI、JDBC、PHP等各语言MySQLConnecotr,或者通过ODBC。SDK来访问MySQL的本质上还是在TCP连接上通过MySQL协议跟MySQL进行交互

c.第一层：连接层

系统(客户端)访问MySQL服务器前，首先建立TCP连接。经过三次握手建立连接成功后，MySQL服务器对TCP传输过来的账号密码做身份认证、权限获取。连接管理的职责是负责认证、管理连接、获取权限信息

d.第二层：服务层

服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化：如确定查询表的顺序，是否利用索引等，最后生成相应的执行操作

  • SQLInterface:SQL接口

    • 接收用户的SQL命令，并且返回用户需要查询的结果
    • MySQL支持DML(数据操作语言)、DDL(数据定义语言)、存储过程、视图、触发器、自定义函数等多种SQL语言接口

  • Parser:解析器

    • 在解析器中对SQL语句进行语法分析、语义分析。将SQL语句分解成数据结构，并将这个结构传递到后续步骤，以后SQL语句的传递和处理就是基于这个结构的
    • 在SQL命令传递到解析器的时候会被解析器验证和解析，并为其创建语法树

  • Optimizer:查询优化器

    • SQL语句在语法解析之后、查询之前会使用查询优化器确定SQL语句的执行路径，生成一个执行计划，表明应该使用哪些索引进行查询(全表检索还是使用索引检索)，表之间的连接顺序，最后会按照执行计划中的步骤调用存储引擎提供的方法来真正的执行查询，并将查询结果返回给用户
    • 它使用“选取-投影-连接”策略进行查询。例如：

    SELECT id,name FROM student WHERE gender = '女';

    1. SELECT查询先根据WHERE语句进行选取，而不是将表全部查询出来以后再进行gender过滤
    2. SELECT查询先根据id和name进行属性投影，而不是将属性全部取出以后再进行过滤
    3. 将这两个查询条件连接起来生成最终查询结果

  • Caches&Buffers：查询缓存组件

    • MySQL内部维持着一些Cache和Buffer，比如QueryCache用来缓存一条SELECT语句的执行结果，如果能够在其中找到对应的查询结果，那么就不必再进行查询解析、优化和执行的整个过程了，直接将结果反馈给客户端
    • 这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等。这个查询缓存可以在不同客户端之间共享
    • 从MySQL5.7.20开始，在MySQL8.0中删除

e.第三层：引擎层

插件式存储引擎层，负责了MySQL中数据的存储和提取，对物理服务器级别维护的底层数据执行操作，服务器通过API与存储引擎进行通信

f.存储层

所有的数据，数据库、表的定义，表的每一行的内容，索引，都是存在文件系统上，以文件的方式存在的，并完成与存储引擎的交互

g.小结

简化为三层结构：

  1. 连接层：客户端和服务器端建立连接，客户端发送SQL至服务器端
  2. SQL层(服务层)：对SQL语句进行查询处理；与数据库文件的存储方式无关
  3. 存储引擎层：与数据库文件打交道，负责数据的存储和读取

2.SQL执行流程

a.MySQL中的SQL执行流程

MySQL的查询流程：

  1. 查询缓存：Server如果在查询缓存中发现了这条SQL语句，就会直接将结果返回给客户端；如果没有，就进入到解析器阶段，查询缓存的失效非常频繁

  2. 解析器：在解析器中对SQL语句进行词法分析、语法分析

SQL语句的分析分为词法分析与语法分析

分析器先做“词法分析”。MySQL需要识别出输入由多个字符串和空格组成的一条SQL语句里面的字符串分别是什么，代表什么

接着，要做“语法分析”。根据词法分析的结果，语法分析器(比如：Bison)会根据语法规则，判断你输入的这个SQL语句是否满足MySQL语法

如果SQL语句正确，则会生成一个这样的语法树：

下图是SQL分词分析的过程步骤:

  1. 优化器：在优化器中会确定SQL语句的执行路径，比如是根据全表检索，还是根据索引检索等。

    在开始执行SQL语句之前，要先经过优化器的处理。一条查询可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划

    比如：优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联(join)的时候，决定各个表的连接顺序，还有表达式简化、子查询转为连接、外连接转为内连接等

    举例：如下语句是执行两个表的join：

select*from test1 join test2 using(ID) where test1.name='zhangsan'andtest2.name='Java';

方案1：可以先从表test1里面取出name=‘zhangsan’的记录的ID值，再根据ID值关联到表test2，再判断test2里面name的值是否等于’Java’

方案2：可以先从表test2里面取出name=‘Java’的记录的ID值，再根据ID值关联到test1，再判断test1里面name的值是否等于’zhangsan’

在查询优化器中，可以分为逻辑查询优化阶段和物理查询优化阶段

逻辑查询优化是通过改变SQL语句的内容来使得SQL查询更高效，同时为物理查询优化提供更多的候选执行计划。通常采用的方式是对SQL语句进行等价变换，对查询进行重写。对条件表达式进行等价谓词重写、条件简化，对视图进行重写，对子查询进行优化，对连接语义进行了外连接消除、嵌套连接消除等

物理查询优化是基于关系代数进行的查询重写，而关系代数的每一步都对应着物理计算，这些物理计算往往存在多种算法，因此需要计算各种物理路径的代价，从中选择代价最小的作为执行计划。在这个阶段里，对于单表和多表连接的操作，需要高效地使用索引，提升查询效率

  1. 执行器：

在执行之前需要判断该用户是否具备权限。如果没有，就会返回权限错误。如果具备权限，就执行SQL查询并返回结果

select * from test where id=1;

比如：表test中，ID字段没有索引，那么执行器的执行流程是：

  1. 调用InnoDB引擎接口取这个表的第一行，判断ID值是不是1，如果不是则跳过，如果是则将这行存在结果集中
  2. 调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行
  3. 执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端

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

b.MySQL8中SQL执行原理

1)确认profiling是否开启

select@@profiling;show

select @@profiling;show variables like'%profiling;';

variableslike’%profiling;’`

profiling=0代表关闭，把profiling打开，即设置为1

set profiling=1;

2)多次执行相同SQL查询

select * from employees;

3)查看profiles

show profiles;#显示最近的几次查询

4)查看profile

show profile;show profile for query 7;#查询指定的QueryID

3.数据库缓冲池(bufferpool)

a.缓冲池vs查询缓存

缓冲池和查询缓存不是一个东西

1)缓冲池(BufferPool)

在InnoDB存储引擎中有一部分数据会放到内存中，缓冲池则占了这部分内存的大部分，它用来存储各种数据的缓存，包括了数据页、索引页、插入缓冲、锁信息、自适应Hash和数据字典信息等

缓存池的重要性：

  • 消除了CPU与磁盘之间的鸿沟，节省磁盘IO的开销

缓存原则(位置*频次)：

  • 首先，位置决定效率，提供缓冲池就是为了在内存中可以直接访问数据

  • 其次，频次决定优先级顺序。因为缓冲池的大小是有限的，比如磁盘有200G，但是内存只有16G，缓冲池大小只有1G，就无法将所有数据都加载到缓冲池里，这时就涉及到优先级顺序，会优先对使用频次高的热数据进行加载

注意

查询缓存，在MySQL8.0之后不存在，缓冲池在MySQL8.0之后依然存在

b.缓冲池如何读取数据

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

当对数据库中的记录进行修改的时候，首先会修改缓冲池中页里面的记录信息，然后数据库会以一定的频率刷新到磁盘中。注意并不是每次发生更新操作，都会立即进行磁盘回写。缓冲池会采用一种叫做checkpoint的机制将数据回写到磁盘上，这样做的好处就是提升了数据库的整体性能。

c.查看/设置缓冲池的大小

show variables like 'innodb_buffer_pool_size';

默认大小为：134217728/1024/1024=128MB

修改缓冲池大小，比如改为256MB

set global innodb_buffer_pool_size=268435456;

d.多个BufferPool实例

show variables like 'innodb_buffer_pool_instances';

默认情况下，BufferPool的value为1

BufferPool实例创建的越多越好，分别管理各个BufferPool也是需要性能开销的，InnDB规定：当innodb_buffer_pool_size的值小于1G的时候设置多个实例是无效的，InnoDB会默认把innodb_buffer_pool_instances的值修改为1。而我们鼓励在BufferPool大于等于1G的时候设置多个BufferPool实例

五、存储引擎

存储引擎指的是表的类型，功能为接收上层传下来的指令，然后对表中的数据进行提取或写入操作

1.查看存储引擎

查看mysql的存储引擎

show engines;

2.系统默认的存储引擎

  • 查看默认的存储引擎

    show variables like '%storage_engine%';

3.设置表的存储引擎

可以为不同的表设置不同的存储引擎，也就是说不同的表可以有不同的物理存储结构，不同的提取和写入方式

a.创建表时指定存储引擎

CREATE TABLE表名(建表语句;)ENGINE=存储引擎名称;

b.修改表的存储引擎

ALTER TABLE 表名 ENGINE=存储引擎名称;

c.查看表结构

SHOW CREATE TABLE tablename;
  1. CREATE TABLE engine_demo_table(idint)ENGINE=MyISAM;
  2. SHOW CREATE TABLE engine_demo_table;

  1. ALTER TABLE engine_demo_table ENGINE=InnoDB;
  2. SHOW CREATE TABLE engine_demo_table;

4.引擎介绍

a.InnoDB引擎：具备外键支持功能的事务存储引擎

  • InnoDB是MySQL的默认事务型引擎，它被设计用来处理大量的短期(short-lived)事务。可以确保事务的完整提交(Commit)和回滚(Rollback)
  • 除了增加和查询外，还需要更新、删除操作，应优先选择InnoDB存储引擎
  • InnoDB是为处理巨大数据量的最大性能设计
  • 对比MyISAM的存储引擎，InnoDB写的处理效率差一些，并且会占用更多的磁盘空间以保存数据和索引
  • MyISAM只缓存索引，不缓存真实数据；InnoDB不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响
  • InnoDB是MySQL默认的存储引擎

b.MyISAM引擎：主要的非事务处理存储引擎

  • MyISAM提供了大量的特性，包括全文索引、压缩、空间函数(GIS)等，但MyISAM不支持事务、行级锁、外键，有一个毫无疑问的缺陷就是崩溃后无法安全恢复
  • 优势是访问的速度快，对事务完整性没有要求或者以SELECT、INSERT为主的应用
  • 应用场景：只读应用或者以读为主的业务

c.Archive引擎：用于数据存档

d.Blackhole引擎：丢弃写操作，读操作会返回空内容

e.CSV引擎：存储数据时，以逗号分隔各个数据项

f.Memory引擎：置于内存的表

g.Federated引擎：访问远程表

h.Merge引擎：管理多个MyISAM表构成的表集合

i.NDB引擎：MySQL集群专用存储引擎