MySql—使用汇总

蓝魔Y

已于 2025-04-27 16:36:55 修改

阅读量247

点赞数

分类专栏：数据库文章标签： mysql

于 2022-07-26 13:39:57 首次发布

本文链接：https://blog.youkuaiyun.com/weixin_38938108/article/details/125992945

版权

数据库专栏收录该内容

21 篇文章

订阅专栏

文档结构

1、概念简介
2、架构设计
3、操作汇总
4、运维监控

参考手册：https://www.docs4dev.com/docs/zh/mysql/5.7/reference/

1、概念简介

MySql的历史

MySql原本是一个开放源码的关系数据库管理系统，原开发者为瑞典的MySQL AB公司；
2008年，AB公司被 Sun Microsystems（太阳微系统）公司收购。
2009年，甲骨文公司（Oracle）收购 Sun Microsystems公司，MySQL成为Oracle旗下产品。

MySql的衰落

MySQL分为社区版和商业版，在被Oracle收购后，一方面商业版的价格大幅调涨，另一方面社区版的GPL也在被 “失误”的情况下移除，导致MySQL社区版无法被自由使用和二次开发；且甲骨文公司不再支持另一个自由软件项目OpenSolaris的发展。

基于以上现状，导致自由软件社群们对于Oracle是否还会持续支持MySQL社群版（MySQL之中唯一的免费版本）有所隐忧，MySQL的创始人麦克尔·维德纽斯以MySQL为基础，成立分支计划MariaDB。而原先一些使用MySQL的开源软件逐渐转向MariaDB或其它的数据库。例如维基百科已于2013年正式宣布将从MySQL迁移到MariaDB数据库。

2、架构设计

2.1、存储引擎

主要为 MyISAM 引擎和 InnoDB引擎；

InnoDB是事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键，是默认的MySQL引擎。

2.2、逻辑结构

数据存储：按照表空间、段、簇(区)、页、行的层级；

表空间：一个表空间最多拥有 2^32 个页，默认情况下一个页的大小为 16KB，也就是说一个表空间最多存储 64TB 的数据。

段：表空间是由数据段、索引段、回滚段等各个段组合，段是一个逻辑的概念。一个 ibd 文件（独立表空间文件）里面会由很多个段组成。

区：每个簇（区）的大小是 1MB，即64个连续的页。一个段最少有一个区，段的扩展单位为区；

页：默认情况下一个页的大小为 16KB；这些页面在物理上和逻辑上都是连续的。

2.3、部署模式

mysql 的部署方式分为单节点模式和集群模式；集群模式按照数据分布特点分为：

（一）读写分离集群

该模式集群适用于读多写少的场景，可以有一个 master主节点，多个 slave从节点；

高可用的表现：当主节点宕机无法提供服务时，将提升从节点为主节点保障数据库系统的稳定；

每个节点数据量都是一致的，通过数据冗余来实现主从分离，读写分离；当主节点的数据量很大时，将不再适用；

（二）分库分表集群

该集群模式适用于十几亿数据量级的应用，不支持高可用，某个节点宕机部分数据将不可用；

每个节点的数据通过分片机制来实现数据分发，一般有范围分片和hash分片：
A：范围分片

优点：结构简单，扩展容易；
缺点：容易造成数据分布不均衡，部分节点压力大；

B：hash分片

优点：数据分布均衡；
缺点：节点规模扩展困难，因此一般该种分片模式需要提前评估好需要的节点；

（三）主流架构集群

在这里插入图片描述

该类型的集群模式是将读写分离的设计和分片机制想结合的架构方式；

该种集群下，按照不同的角色，分为不同的节点类型：

管理节点

MySQL集群的管理者，起到联系并管理整体架构的作用，控制其他节点的启停，监控其他节点的状态，一个集群中只有一个管理节点；

数据节点

该类型的节点负责数据的存储；每个节点都会存储所有数据，当某个节点宕机是可以有其他节点来继续承担对应功能；

sql 节点

负责于web程序进行交互，承接来自上层应用的sql命令，主要对外提供sql功能；

3、操作汇总

3.0、字符集

Mysql 中在建库和建表过程中，可以显式指定 character 和 collate 两种属性值；
character 建议使用 utf8mb4，collate 建议设置为 utf8mb4_bin；

character的两种值，utf8mb3 和 utf8mb4的区别？

"utf8mb3"以前被称为普通的UTF-8，在这个编码中最多支持3个字节来表示Unicode字符，可以表示Unicode字符的范围是从U+0000到U+FFFF。
"utf8mb4"是对"utf8mb3"的扩展，支持使用最多4个字节表示Unicode字符，支持的Unicode的范围为从U+0000到U+10FFFF。

数据类型

数据大类	字段类型	说明
布尔型	true	值域 1
布尔型	false	值域 0
日期型	date
日期型	datetime
日期型	timestamp
字符型	char / binary	256个字符
字符型	varchar / varbinary	最大65535个字符
字符型	text / blob	最大65535个字符
字符型	longtext / longblob	最大exp(2,24)-1个字符
数值型	numeric	不会丢失精度
数值型	decimal	不会丢失精度
数值型	float	某些场景下会丢失精度
数值型	double	某些场景下会丢失精度

3.1、DDL相关

创建/删除库

use mysql;

create database|schema <if not exists> $db_name;

drop database|schema <if exists> $db_name;

说明：因为在mysql中schema和database概念等同，所以此处建库关键词可以使用 database或schema都可以；

表创建/删除

mysql 分区类型支持 range、list、hash 三类，分区列支持多列；

mysql中对象的数据量规划，正常单表数据量控制在200~500万以内，阿里的建议是500万，腾讯后面建议2000万；

3.2、DCL相关

创建用户

create user $user_name identified by 'password';

drop user $user_name;

授权/回收

use $target_db;

grant all privileges on $target_db to $target_role@'%';

revoke all on $target_db from $target_role;

说明：以上授权方式可能会报错：ERROR 1142 (42000): CREATE command denied to user 'webadmin'@'localhost' for table 'aaa'；

改为如下的语句：
grant all privileges on $target_db.* to $target_role@'%';

权限查看

show grants for user_name;

3.3、DML相关

mysql不支持 full join 全外关联的语法；

3.4、DQL相关

系统表/视图

information_schema.schemata 查询数据库
information_schema.tables 查询表
information_schema.columns 查询列信息(包含数据类型和字段描述)
information_schema.views 查询视图
information_schema.view_table_usage 查询视图依赖的表
information_schema.TABLE_PRIVILEGES 查询表的权限
information_schema.table_constraints 查询表约束
information_schema.key_column_usage 查询约束列
版本查询

select version();

查询 schema/db

# 查询当前database
select database();

当前用户
select current_user();
查询表清单
SHOW [FULL] TABLES [{FROM | IN} db_name] [LIKE 'pattern' | WHERE expr]
查看DDL

show create table database.object_name;

多表关联
场景：多表关联报错信息
SQL Error [1267] [HY000]: Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '='

报错原因：关联字段使用了不同的字符集排序规则(collation)导致的。具体来说，一个表使用 utf8mb4_0900_ai_ci，另一个表使用 utf8mb4_general_ci。

解决方法：

SELECT t.link_name,
       t.source_server_id,
       s.server_name
  FROM T_CTL_INTEGRATION_LINK t
  left join t_ctl_server s
    on (t.source_server_id COLLATE utf8mb4_0900_ai_ci = s.server_id COLLATE
        utf8mb4_0900_ai_ci)
 where 1 = 1;