mysql常用命令&架构&引擎

2022-11-22 09:47:53

mysql架构四层

mysql架构图

第一层：

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

Connection Pool连接池

Management Services & Utillties 包括备份，容灾恢复，安全，复制，集群

第二层：

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

Parser 解析通过select,insert命令解析相应操作 Optimizer
sql优化器，mysql自己认为最优的优化,因为做sql验证，至少执行2次。在极端情况下，把此步操作省略，可加快速度
Caches & Buffers 缓存和缓冲

第三层：

可拔插组件的引擎。大部分公司使用MyISAM,InnoDB

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

第四层：

数据存储，主要将数据存储在运行于裸设备的文件系统智商，并完成与存储引擎的交互

总结

插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离。这种架构可以根据业务选择相应存储引擎。

数据库存储引擎

Mysql中的数据用各种不同的技术存储在文件(或者内存)中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。这些不同的技术以及配套的相关功能在MySQL中被称作存储引擎(也称作表类型)。MySql默认配置了许多不同的存储引擎，可以预先设置或者在MySql服务器中启用。

InnoDB 从 MySQL5.5（2010年）版本代替 MyISAM 成为默认引擎，InnoDB相比MyISAM更加强调性能，InnoDB 侧重于提供事务支持以及外部键等高级数据库功能。

InnoDB特点

支持事务。默认的事务隔离级别为可重复读（REPEATABLE-READ），通过MVCC（并发版本控制）来实现。
使用的锁粒度默认为行级锁，可以支持更高的并发；当然，也支持表锁。
支持外键约束；外键约束其实降低了表的查询速度，但是增加了表之间的耦合度。
在InnoDB中存在着缓冲管理，通过缓冲池，将索引和数据全部缓存起来，加快查询的速度
可以通过自动增长列，方法是auto_increment
配合一些热备工具可以支持在线热备份；
InnoDB 表的select count() 比 MyISAM 慢很多；当执行 select count() from t 时，会先把数据读出来，一行一行的累加，最后返回总数量。是的，真的会很慢。需要注意的是，当count(*) 语句包含 where 条件时，两种表的操作是一样的。
对于InnoDB类型的表，其数据的物理组织形式是聚簇表。所有的数据按照主键来组织。数据和索引放在一块，都位于B+数的叶子节点上；

InnoDB存储表和索引形式

共享表空间存储：所有的表和索引存放在同一个表空间中。
多表空间存储：表结构放在.frm文件，数据和索引放在.ibd文件中。分区表的话，每个分区对应单独的.ibd文件，使用分区表的好处在于提升查询效率

MyISAM特点

不支持事务,不支持锁：在读写（Insert、select）效率上，要高于InnoDB不少。场景在：日志记录、调查统计表时，绝对值得一用
体积小，质量大：MyISAM的索引和数据是分开的，并且索引是有压缩的，内存使用率就对应提高了不少。同时能加载更多索引，而Innodb的索引和数据是紧密捆绑的，没有使用压缩从而会造成 Innodb 比 MyISAM 数据文件体积庞大很多。MyISAM表在磁盘上会对应三个文件：.frm文件：存储表的定义数据 .MYD文件：存放表具体记录的数据 .MYI文件：存储索引

常常应用部门需要我给他们定期某些表的数据，MyISAM的话很方便，只要发给他们对应那表的（frm.MYD,MYI）的文件，让他们自己在对应版本的数据库启动就行，而Innodb就需要导出.sql了，因为光给别人文件，受字典数据文件的影响，对方是无法使用的。
如果和 MyISAM 比 Insert 写操作的话，Innodb还达不到MyISAM的写性能，如果是针对基于索引的update操作，确实MyISAM会慢与InnoDB，但在并发环境下，从库同步也是个事儿，还不如通过多实例分库分表架构来解决。
MyISAM 存储引擎中，把表的总行数（row）存储在磁盘上，当执行 select count() from t 时，直接返回总数据。同样，当 count() 语句包含 where条件时，两种表的操作是一样的

mysql常用命令

查看存储引擎

show engines;

默认支持innoDB引擎，支持事务，行锁，表锁

查看默认存储引擎

show variables like '%storage_engine%';

常用命令：


# 查看存储引擎
show engines;
# 查看默认存储引擎 注意使用单引号
show variables like '%storage_engine%';
# 查看所有数据库
show databases;
# 切换使用数据库
use music_db;
# 查当前库中所有表
show tables;
show table status;
#查看某个表的所有字段信息   查表结构
show columns  from music_admin;
# 查表结构
describe music_admin;
# 查看当前用户权限列表
show grants;
# 查询所有自定义函数
show function status ;
# 查看函数
show create function 函数名;
# 删除函数
drop function 函数名;
# 清除表中数据 保留自增主键
delete from 表名;
# 清除表中数据 不保留自增主键，不记录日志，不可恢复相当于重新创建表
truncate table 表名;

码农公寓