⚠️ 本人声明：本教程可100%复现网上看了很多Mysql8安装的方式，基本上都是都过一个yum方式直接访问公网下载依赖资源安装的。但是在企业内部一般并不允许生产环境直接连接公网。针对此情况。本文将介绍如何在内网环境下使用二进制安装包本地安装方式来部署Mysql8。使用二进制安装包安装相对编译安装简单快速很多1. 本次实践介绍1.1 本次实践规划本人使用环境如下Mysql8使用二进制安装包安装请尽量保证主机内存大于1G。存储空间大于20G。hostnameIP地址系统版本内核版本master192.168.129.139BigCloud Enterprise Linux For Euler release 21.10 (LTS-SP2)4.19.90-2107.6.0.0100.oe1.bclinux.x86_641.2 本次实践简介1.本次实践环境建议个人测试环境，生产环境请谨慎操作2.本次实践为在在BClinux for euler 21.10下使用rpm包安装Mysql8.3版本3.请尽量保证主机内存大于1G。存储空间大于20G。1.3 二进制安装包安装Mysql的优缺点特点优点缺点安装速度- 快速安装，无需编译- 预编译的包通常为常见配置优化- 可能不是针对特定硬件优化- 不支持某些特殊配置需求兼容性- 适用于多种操作系统版本- 可能与系统上的其他软件包冲突系统资源- 通常占用较少的磁盘空间- 性能可能不如从源代码编译的版本自定义配置- 配置较为简单- 缺乏灵活性，不能在安装过程中进行深度定制更新维护- 更新方便，通常通过包管理器自动更新- 自动更新可能导致服务中断安全性- 可以利用操作系统的安全更新- 可能包含已知但未修复的安全漏洞2. 本地准备环境配置2.1 卸载已经安装的数据库检查linux是否安装了mariadb和mysql数据库，新系统可能会自带mariadb数据库，mariadb数据库是MySQL的分支当一个系统已经安装了MariaDB时，直接尝试安装MySQL可能会导致冲突包依赖冲突：MariaDB和MySQL的某些文件可能具有相同的名称或路径，这会导致包管理器无法正确处理依赖关系。服务冲突：两个数据库服务可能尝试监听相同的端口（通常是3306），这会导致服务启动失败或不稳定。配置冲突：MariaDB和MySQL的配置文件可能位于相同的位置，比如/etc/my.cnf，这会使得配置混乱。# 检查是否安装了mariadbyum list installed | grep mariadb*# 卸载mariadbyum remove mariadb*# 检查是否安装了mysqlyum list installed | grep mysql*# 卸载mysqlyum remove mysql*执行过程如下[root@localhost ~]# yum list installed | grep mariadb*mariadb-connector-c.x86_64 3.0.6-7.oe1 @anaconda[root@localhost ~]# yum remove mariadb*依赖关系解决。=====================================================================================================Package Architecture Version Repository Size=====================================================================================================移除:mariadb-connector-c x86_64 3.0.6-7.oe1 @anaconda 414 k移除依赖的软件包:rsyslog-relp x86_64 8.2006.0-6.oe1 @anaconda 63 krsyslog-help noarch 8.2006.0-6.oe1 @anaconda 7.0 M事务概要=====================================================================================================移除 9 软件包将会释放空间：14 M确定吗？[y/N]： Y运行事务检查事务检查成功。运行事务测试事务测试成功。运行事务准备中 : 1/1运行脚本: rsyslog-relp-8.2006.0-6.oe1.x86_64 1/1删除 : rsyslog-relp-8.2006.0-6.oe1.x86_64 1/9 9/9已移除:libestr-0.1.11-1.oe1.x86_64 libfastjson-0.99.8-3.oe1.x86_64 libnet-1.2-1.oe1.x86_64 librelp-1.2.16-3.oe1.x86_64 mariadb-connector-c-3.0.6-7.oe1.x86_64net-snmp-libs-1:5.9-3.oe1.x86_64 rsyslog-8.2006.0-6.oe1.x86_64 rsyslog-help-8.2006.0-6.oe1.noarch rsyslog-relp-8.2006.0-6.oe1.x86_64完毕！[root@localhost ~]# yum list installed | grep mysql*[root@localhost ~]# yum remove mysql*未找到匹配的参数: mysql*没有软件包需要移除。依赖关系解决。无需任何处理。完毕！2.2 配置yum源注释:若Linux主机可以访问公网，则只需要配置好公网repo源即可，若无法连接公网，则需要配置好本地repo源。不同类型用户可能需求不一样。这里默认大家使用本地iso搭建镜像yum源。其余情况请参考文章(已经足够详细)yum源配置，这一篇就够了！(包括本地，网络，本地共享yum源)当服务器无法访问公网或者所需要依赖无法在公网获取，我们可以通过使用挂载本地iso镜像来获取我们需要的rpm包和依赖2.2.1 本机单机yum源配置此处以挂载BClinux for openeuler 21.10镜像为例，每个人操作系统型号不同，请选择自己需要的版本切记选择来源安全可靠的镜像下载将下载好后的镜像上传到/mnt中ls在/mnt目录下创建文件夹 BClinuxmkdir BClinux然后输入下面命令进行挂载mount -o loop /mnt/BCLinux-for-Euler-21.10-everything-x86_64.iso /mnt/BClinux下个步骤中，关闭selinux需要重启，会导致本命令失效，需要重新执行即可2.2.2 repo文件配置进入/etc/yum.repos.d/目录，将之前的repo文件进行备份# 进入repo配置文件cd /etc/yum.repos.d/# 将原来的repo文件进行备份mv /etc/yum.repos.d/* *.bak然后创建新的repo源,输入以下命令# 复制下面的脚本输入echo "> [local]> name=bendiyum> baseurl=file:///mnt/BClinux/> enabled=1> gpgcheck=0" > local.repo## 执行yum clean allyum makecacheyum repolist配置完成yum makecacheyum repolist尝试使用yumyum install -y tree本地yum源配置完成2.3 关闭防火墙和selinux关闭防火墙# 查看防火墙是状态systemctl status firewalld# 关闭防火墙systemctl stop firewalld#取消开机自启动systemctl disable firewalld关闭selinuxsed -i 's/SELINUX\=enforcing/SELINUX\=disabled/g' /etc/selinux/config## 重启后生效3. Mysql安装详解本次香菇安装的Mysql版本为Mysql8.3.03.1 官网下载合适安装包Mysql官网链接:cid:link_1查看本地主机glibc的版本ldd --version根据官网提示信息选择合适的版本下载此处我选择的是mysql-8.3.0-linux-glibc2.28-x86_64.tar.xz版本大家的主机若可以直接访问公网，也可以通过在主机上输入命令的方式下载cd /usr/local/src/ && wget https://downloads.mysql.com/archives/get/p/23/file/mysql-8.3.0-linux-glibc2.28-x86_64.tar.xz否则，可以下载到本地，在上传到linux主机对应目录3.2 安装Mysql8.33.2.1 配置安装路径进入Mysql安装包下载的目录，将其移动到/usr/local/src下面mv mysql-8.3.0-linux-glibc2.28-x86_64.tar.xz /usr/local/src/解压文件sudo tar xvJf /usr/local/src/mysql-8.3.0-linux-glibc2.28-x86_64.tar.xz修改文件名称sudo mv mysql-8.3.0-linux-glibc2.28-x86_64 mysql-8.3.0创建mysql相关文件夹sudo mkdir -p /mysql/data # 数据存储sudo mkdir -p /mysql/log # 日志存储sudo mkdir -p /mysql/tmp # 临时文件3.2.2 创建Mysql账户并赋权# 新增主机用户Mysqluseradd -m mysql# 配置密码，此处我设置的密码为Xianggu123echo Xianggu123 | passwd --stdin mysql赋权给Mysql用户mysql安装权限和数据存放路径权限chown -R mysql /usr/local/src/mysql-8.3.0chown -R mysql:mysql /mysql/datachown -R mysql:mysql /mysql/logchown -R mysql:mysql /mysql/tmp3.2.3 安装Mysql在安装目录下执行sudo /usr/local/src/mysql-8.3.0/bin/mysqld --user=mysql --basedir=/usr/local/src/mysql-8.3.0 --datadir=/mysql/data/ --initialize记住初始化密码3.2.4 Mysql配置文件修改正常情况下是没有这个文件的，执行保存之后会自动创建创建并编辑my.cnf文件vi /etc/my.cnf配置文件内容如下（文件路径记得改为自己的）[mysqld]# 设置 MySQL 服务的端口号，默认为 3306port = 3306# 设置 MySQL 服务的安装目录basedir = /usr/local/src/mysql/# 设置 MySQL 数据库的数据存放目录datadir = /mysql/data# 设置允许连接的最大客户端数量max_connections = 200# 设置每个连接使用的缓冲区大小sort_buffer_size = 2Mread_buffer_size = 2Mread_rnd_buffer_size = 8M# 启用查询缓存#query_cache_type = 1#query_cache_size = 16M# 日志相关配置log_error = error.log#slow_query_log = 1#slow_query_log_file = /mysql/log/slow_query.log#long_query_time = 10 # 超过 10 秒的查询被视为慢查询[mysqld]# 设置默认字符集character-set-server = utf8mb4# 设置连接字符集collation-server = utf8mb4_unicode_cisocket=/tmp/mysql.sock[mysqld]# InnoDB 缓冲池大小innodb_buffer_pool_size = 128M# InnoDB 日志文件大小innodb_log_file_size = 64M# InnoDB 日志文件数量innodb_log_files_in_group = 2# InnoDB 自动扩展表空间innodb_autoextend_increment = 64M3.2.5 将mysql注册为系统服务sudo cp -a /usr/local/src/mysql-8.3.0/support-files/mysql.server /usr/lib/systemd/systemchown 775 /usr/lib/systemd/system/mysql.server# 设置启动mysqlsystemctl start mysql# 设置开机自动启动systemctl enable mysql3.2.6 将Mysql添加到系统环境变量中# 编辑文件vi /etc/profile# 在末尾添加下面内容export PATH=$PATH:/usr/local/src/mysql/bin4. Mysql服务初始化配置4.1 初始化Mysql首先将mysql相关路径的权限赋给mysql主机用户chown -R mysql:mysql /mysql启动Mysql服务systemctl start mysql查看mysql状态systemctl status mysql进入mysql主机用户，登录mysql# 切换到mysql用户su - mysql# 登录mysqlmysql -uroot -p更改初始密码#我这里改成了Xianggu@123ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Xianggu@123';flush privileges;设置允许远程登录use mysql;# 允许所有主机登录rootupdate user set host="%" where user='root'flush privileges;4.2 远程连接测试选择一个常用的数据库登录工具，此处我用的是VScode可以看到已经连接成功编写sql语句进行测试create database xianggu;use xianggu;create table student (name varchar(50),age int,id int);5.总结经过一系列的操作，我们成功地在内网环境中使用二进制安装包完成了 MySQL 8.3 版本的安装和基本配置。通过本教程，您应该能够了解如何在没有公网访问的情况下进行 MySQL 的安装，这对于企业内部的部署来说非常实用。安装步骤回顾：环境准备：确认系统内存和存储空间满足要求，确保主机至少有 1GB 的内存和 20GB 的存储空间。卸载旧版数据库：避免与已安装的 MariaDB 或 MySQL 发生冲突。配置本地 yum 源：如果服务器无法访问公网，可以使用本地 ISO 镜像作为 yum 源来获取所需的 RPM 包和依赖。安装 MySQL：下载并解压 MySQL 的二进制安装包，创建必要的文件夹，创建 MySQL 用户并赋予相应的权限。初始化 MySQL 服务：通过指定用户、安装目录和数据目录等参数初始化 MySQL 服务，并记录生成的初始密码。配置 MySQL：创建并编辑 my.cnf 文件，对 MySQL 进行必要的配置，如端口、数据目录、最大连接数等。注册为系统服务：将 MySQL 注册为系统服务，并设置开机自启。环境变量配置：将 MySQL 的二进制路径添加到系统环境变量中。服务初始化：设置初始密码，开启远程连接功能，并启动 MySQL 服务。使用二进制安装包安装 MySQL 是一种快速简便的方法，特别适合于那些无法直接连接公网的内网环境。这种方式不仅简化了安装流程，还减少了编译的时间。通过以上步骤，您可以轻松地在 BigCloud Enterprise Linux For Euler 21.10 上安装并配置 MySQL 8.3 版本，从而满足您的数据库需求。

下面针对一些参数进行说明,当然还有其它的设置可以起作用,取决于你的负载或硬件：在慢内存和快磁盘、高并发和写密集型负载情况下,你将需要特殊的调整前言日常的MySQL运维中说起调优，MySQL的配置文件my.cnf是不可忽略的。MySQL的默认参数并不能满足我们日常线上业务的需求，因此对参数进行优化也是不可缺少的环节。这里不想列出my.cnf配置中有多少项和每一项的意思，这些都可以在官方文档上查到。以下仅对日常工作用应该注意的一些参数进行说明。下面针对一些参数进行说明。当然还有其它的设置可以起作用，取决于你的负载或硬件：在慢内存和快磁盘、高并发和写密集型负载情况下，你将需要特殊的调整。然而这里的目标是让你可以快速地获得一个稳健的MySQL配置，而不用花费太多时间在调整一些无关紧要的MySQL设置或读文档，找出哪些设置对你来说是重要的。InnoDB配置从MySQL 5.5版本开始，InnoDB就是默认的存储引擎并且它比任何其它存储引擎的使用要多得多。那也是为什么它需要小心配置的原因。innodb_file_per_table表的数据和索引存放在共享表空间里或者单独表空间里。我们的工作场景安装是默认设置了innodb_file_per_table = ON，这样也有助于工作中进行单独表空间的迁移工作。MySQL 5.6中，这个属性默认值是ON。_flush_log_at_trx_commit默认值为1，表示InnoDB完全支持ACID特性。当你的主要关注点是数据安全的时候这个值是最合适的，比如在一个主节点上。但是对于磁盘（读写）速度较慢的系统，它会带来很巨大的开销，因为每次将改变flush到redo日志都需要额外的fsyncs。如果将它的值设置为2会导致不太可靠（unreliable）。因为提交的事务仅仅每秒才flush一次到redo日志，但对于一些场景是可以接受的，比如对于主节点的备份节点这个值是可以接受的。如果值为0速度就更快了，但在系统崩溃时可能丢失一些数据：只适用于备份节点。说到这个参数就一定会想到另一个sync_binlog。innodb_flush_method这项配置决定了数据和日志写入硬盘的方式。一共有三种方式，我们默认使用O_DIRECT 。O_DIRECT模式：数据文件的写入操作是直接从mysql innodb buffer到磁盘的，并不用通过操作系统的缓冲，而真正的完成也是在flush这步，日志还是要经过OS缓冲。innodb_log_buffer_size这项配置决定了为尚未执行的事务分配的缓存。其默认值（1MB）一般来说已经够用了，但是如果你的事务中包含有二进制大对象或者大文本字段的话，这点缓存很快就会被填满并触发额外的I/O操作。看看Innodb_log_waits状态变量，如果它不是0，增加innodb_log_buffer_size。innodb_buffer_pool_size这个参数应该是运维中必须关注的了。缓冲池是数据和索引缓存的地方，它属于MySQL的核心参数，默认为128MB，正常的情况下这个参数设置为物理内存的60%~70%。（不过我们的实例基本上都是多实例混部的，所以这个值还要根据业务规模来具体分析。）innodb_log_file_size这是redo日志的大小。redo日志被用于确保写操作快速而可靠并且在崩溃时恢复。如果你知道你的应用程序需要频繁地写入数据并且你使用的是MySQL 5.6，那么你可以一开始就把它这是成4G。（具体大小还要根据自身业务进行适当调整）innodb_support_xainnodb_support_xa可以开关InnoDB的XA两段式事务提交。默认情况下，innodb_support_xa=true，支持XA两段式事务提交。由于XA两段式事务提交导致多余flush等操作，性能影响会达到10%，所有为了提高性能，有些DBA会设置innodb_support_xa=false。这样的话，redolog和binlog将无法同步，可能存在事务在主库提交，但是没有记录到binlog的情况。这样也有可能造成事务数据的丢失。innodb_additional_mem_pool_size该参数用来存储数据字段信息和其他内部数据结构。表越多，需要在这里分配的内存越多。如果InnoDB用光了这个池内的内存，InnoDB开始从操作系统分配内存，并且往MySQL错误日志写警告信息，默认8MB。一般设置16MB。max_connectionsMySQL服务器默认连接数比较小，一般也就100来个最好把最大值设大一些。一般设置500~1000即可每一个链接都会占用一定的内存，所以这个参数也不是越大越好。有的人遇到too many connections会去增加这个参数的大小，但其实如果是业务量或者程序逻辑有问题或者sql写的不好，即使增大这个参数也无济于事，再次报错只是时间问题。在应用程序里使用连接池或者在MySQL里使用进程池有助于解决这一问题。Seesion级的内存分配123456789101112max_threads(当前活跃连接数)* (read_buffer_size– 顺序读缓冲，提高顺序读效率+read_rnd_buffer_size– 随机读缓冲，提高随机读效率+sort_buffer_size– 排序缓冲，提高排序效率+join_buffer_size– 表连接缓冲，提高表连接效率+binlog_cache_size– 二进制日志缓冲，提高二进制日志写入效率ß+tmp_table_size– 内存临时表，提高临时表存储效率+thread_stack– 线程堆栈，暂时寄存SQL语句/存储过程+thread_cache_size– 线程缓存，降低多次反复打开线程开销+net_buffer_length– 线程持连接缓冲以及读取结果缓冲+bulk_insert_buffer_size– MyISAM表批量写入数据缓冲)global级的内存分配12345678910111213global buffer(全局内存分配总和) =innodb_buffer_pool_size— InnoDB高速缓冲，行数据、索引缓冲，以及事务锁、自适应哈希等+ innodb_additional_mem_pool_size— InnoDB数据字典额外内存，缓存所有表数据字典+innodb_log_buffer_size— InnoDB REDO日志缓冲，提高REDO日志写入效率+key_buffer_size— MyISAM表索引高速缓冲，提高MyISAM表索引读写效率+query_cache_size–查询高速缓存，缓存查询结果，提高反复查询返回效率+table_cahce — 表空间文件描述符缓存，提高数据表打开效率+table_definition_cache–表定义文件描述符缓存，提高数据表打开效率参数的优化最终目的是让MySQL更好地利用资源通过合理地控制内存的分配，合理的CPU使用建议降低Session的内存分配。server-id复制架构时确保 server-id 要不同，通常主ID要小于从ID。log_bin如果你想让数据库服务器充当主节点的备份节点，那么开启二进制日志是必须的。如果这么做了之后，还别忘了设置server_id为一个唯一的值。就算只有一个服务器，如果你想做基于时间点的数据恢复，这（开启二进制日志）也是很有用的：从你最近的备份中恢复（全量备份），并应用二进制日志中的修改（增量备份）。二进制日志一旦创建就将永久保存。所以如果你不想让磁盘空间耗尽，你可以用 PURGE BINARY LOGS 来清除旧文件，或者设置expire_logs_days 来指定过多少天日志将被自动清除。记录二进制日志不是没有开销的，所以如果你在一个非主节点的复制节点上不需要它的话，那么建议关闭这个选项。skip_name_resolve当客户端连接数据库服务器时，服务器会进行主机名解析，并且当DNS很慢时，建立连接也会很慢。因此建议在启动服务器时关闭skip_name_resolve选项而不进行DNS查找。唯一的局限是之后GRANT语句中只能使用IP地址了，因此在添加这项设置到一个已有系统中必须格外小心。sync_binlogsync_binlog 的默认值是0，像操作系统刷其他文件的机制一样，MySQL不会同步到磁盘中去而是依赖操作系统来刷新binary log。当sync_binlog =N (N>0) ，MySQL 在每写N次二进制日志binary log时，会使用fdatasync()函数将它的写二进制日志binary log同步到磁盘中去。当innodb_flush_log_at_trx_commit和sync_binlog  都为 1 时是最安全的，在mysqld服务崩溃或者服务器主机crash的情况下，binary log只有可能丢失最多一个语句或者一个事务。但是鱼与熊掌不可兼得，双1会导致频繁的IO操作，因此该模式也是最慢的一种方式。出于我们的业务考虑在业务压力允许的情况下默认的都是双1配置。log_slave_update当业务中需要使用级联架构的时候log_slave_update = 1这个参数必须打开，否者第三级可能无法接收到第一级产生的binlog，从而无法进行数据同步。tmpdir如果内存临时表超出了限制，MySQL就会自动地把它转化为基于磁盘的MyISAM表，存储在指定的tmpdir目录下.因此尽可能将tmpdir配置到性能好速度快的存储设备上。慢日志相关slow_query_log = 1   #打开慢日志slow_query_log_file = /mysql/log/mysql.slowlong_query_time = 0.5  #设置超过多少秒的查询会入慢日志其他问题SSD对参数的影响随着科学技术的发展，越来越多的存储设备开始由传统的机械组件转向由电子元件组成的永久存储，且价钱越来越能让企业接受。存储组件速度提升后，再用传统机械组件的DB配置就显得浪费了，所以就需要针对不同的存储技术对MySQL配置作出调整，比如 innodb_io_capacity需要调大， 日志文件和redo放到机械硬盘， undo放到SSD， atomic write不需要Double Write Buffer， InnoDB压缩， 单机多实例+cgroup等等。分析 I/O 情况，动态调整 innodb_io_capacity 和 innodb_max_dirty_pages_pct；试图调整 innodb_adaptive_flushing，查看效果。线程池设置针对innodb_write_io_threads 和 innodb_read_io_threads 的调优我们目前没有做，但我相信调整为8或者16，系统 I/O 性能会更好。还有，需要注意以下几点：任何一个调整，都要建立在数据的支撑和严谨的分析基础上，否则都是空谈； 这类调优是非常有意义的，是真正能带来价值的，所以需要多下功夫，并且尽可能地搞明白为什么要这么调整。CPU相关Innodb_thread_concurrency=0Innodb_sync_spin_loops=288table_definition_cache=2000IO相关的Innodb_flush_method 建议用O_DIRECTInnodb_io_capacity 设置成磁盘支持最大IOPSInnodb_wirte_io_threads=8Innodb_read_io_threads=8Innodb_purge_threads=1Innodb的预读方面，如果基于主建或是唯一索引的系统，建议禁用预读Innodb_random_read_ahead = off

当mysql CPU告警利用率过高的时候,我们应该怎么定位是哪些SQL导致的呢,本文将介绍一下定位的方法,文章通过代码示例讲解的非常详细,具有一定的参考价值,需要的朋友可以参考下前言当mysql CPU告警利用率过高的时候，我们应该怎么定位是哪些SQL导致的呢，本文将介绍一下定位的方法。本文所使用的方法，前提是你可以登录到Mysql所在的服务器，执行命令查看进程，当然让数据库管理员登录执行也可以。但如果无法或无权限去服务器上执行命令，本方法将不适合定位问题。一.获取Mysql的服务器进程号登陆mysql所在的Linux服务器，执行命令：top,在COMMAND列找到mysqld，并且%CPU使用率高的，比如数值超过100的，获取PID号。12PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND32232 root      20   0 1443252 356688  11748 S 107.0  4.4   2:03.82 mysqld     上述例子中，32232为mysql进程ID，接下来再用它查询出占用CPU多的线程。二.查询进程中的线程使用命令：top -H -p <mysqld 进程 id>，查询线程号：本例中使用命令top -H -p 3223212PID USER      PR  NI    VIRT    RES    SHR S %CPU %MEM     TIME+ COMMAND                                                                               32272 root      20   0 1443252 356688  11748 R 99.7  4.4   2:25.74 mysqld 其中PID 32272为线程id号。三.根据线程ID去mysql查询出对应的SQL1select a.user,a.host,a.db,b.thread_os_id,b.thread_id,a.id processlist_id,a.command,a.time,a.state,a.info from information_schema.processlist a,performance_schema.threads b where a.id = b.processlist_id and b.thread_os_id=32272;查询结果：1234| user     | host      | db   | thread_os_id | thread_id | processlist_id | command | time | state        | info                                        |+----------+-----------+------+--------------+-----------+----------------+---------+------+--------------+---------------------------------------------+| msandbox | localhost | test |        32272 |        32 |              7 | Query   |    2 | Sending data | select * from t_abc order by rand() limit 1 |+----------+-----------+------+--------------+-----------+----------------+---------+------+--------------+---------------------------------------------+复制讲解AI对话其中，info列显示的SQL就是占用CPU较大的SQL，针对其进行优化即可。此外，还可以通过下列SQL，查询下线程的其他信息，方便进一步优化：1select * from performance_schema.events_statements_current where thread_id in (select thread_id from performance_schema.threads where thread_os_id = 32272)通过这个结果我们可以查看具体的 SQL，看到有使用临时表、使用了排序等信息。查询结果节选：1234CREATED_TMP_DISK_TABLES: 1  CREATED_TMP_TABLES: 1  SORT_ROWS: 1  SORT_SCAN: 1 

一直是想知道一条SQL语句是怎么被执行的,它执行的顺序是怎样的,然后查看总结各方资料,就有了下面这一篇文章了,下面这篇文章主要给大家介绍了关于MySql一条查询语句的执行流程究竟是怎么样的,需要的朋友可以参考下1.前言一条sql语句到底在执行时经历了什么？探究这个问题是学习mysql的重要步骤，面试时常被问到，也使得学习mysql时也有了知识框架的支撑，明白我们背的知识点到底用在哪里，笔者觉得这一点还是很重要的。注：对一个知识点的总结不仅包含知识点本身，还包含对该知识点的联想，这个联想是在面试时可能被追问的，也可以自己主动说出来（我还知道。。。）加分的。2.知识点MySQL 执行流程是怎样的？首先要知道的是，我们可以把mysql分成两层，server层和数据库引擎层，前者主要是对我们的查询进行处理（主要包括 {连接器}，{查询缓存}、{解析器}、{预处理器、优化器、执行器} 等），后者是数据真正存储的地方（从 MySQL 5.5 版本开始， InnoDB 成为了 MySQL 的默认存储引擎）。一条查询的执行流程如下：第一步：通过连接器连接 MySQL 服务1mysql -h$ip -u$user -p[连接器联想1]: 连接经过TCP 三次握手，断开经过四次挥手[连接器联想2]: 如果用户密码都没有问题，连接器就会获取该用户的权限，然后保存起来，后续该用户在此连接里的任何操作，都会基于连接开始时读到的权限进行权限逻辑的判断，意思是管理员修改已登录用户的权限需要等他重新登录才生效[连接器联想3]: 如何查看 MySQL 服务被多少个客户端连接了？show processlist[连接器联想4]: 空闲连接会一直占用着吗？MySQL 定义了空闲连接的最大空闲时长，由 wait_timeout 参数控制的，默认值是 8 小时（28880秒），如果空闲连接超过了这个时间，连接器就会自动将它断开。[连接器联想5]: MySQL 的连接数有限制吗？最大连接数由 max_connections 参数控制，超过这个值，系统就会拒绝接下来的连接请求，并报错提示“Too many connections”。[连接器联想6]: 怎么解决长连接占用内存的问题？MySQL 的连接也跟 HTTP 一样，有短连接和长连接的概念，长连接的好处就是可以减少建立连接和断开连接的过程，但是，使用长连接后可能会占用内存增多，因为 MySQL 在执行查询过程中临时使用内存管理连接对象，这些连接对象资源只有在连接断开时才会释放。有两种解决方式。第一种，定期断开长连接。第二种，客户端主动重置连接。MySQL 5.7 版本实现了 mysql_reset_connection() 函数的接口来重置连接，达到释放内存的效果。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。[连接器联想7]: 连接器的工作？与客户端进行 TCP 三次握手建立连接；校验客户端的用户名和密码，如果用户名或密码不对，则会报错；如果用户名和密码都对了，会读取该用户的权限，然后后面的权限逻辑判断都基于此时读取到的权限；第二步：查询缓存连接器得工作完成后，客户端就可以向 MySQL 服务发送 SQL 语句了，MySQL 服务收到 SQL 语句后，就会解析出 SQL 语句的第一个字段，看看是什么类型的语句。如果 SQL 是查询语句（select 语句），MySQL 就会先去查询缓存（ Query Cache ）里查找缓存数据。但是其实查询缓存挺鸡肋的。对于更新比较频繁的表，查询缓存的命中率很低的，因为只要一个表有更新操作，那么这个表的查询缓存就会被清空。所以，MySQL 8.0 版本直接将server层查询缓存删掉了。第三步：解析SQL在正式执行 SQL 查询语句之前， MySQL 会先对 SQL 语句做解析，这个工作交由「解析器」来完成。解析器会做两件事情:词法分析、 语法分析。[解释器联想1]: 词法分析：MySQL 会根据你输入的字符串识别出关键字出来，例如，SQL语句 select username from userinfo，在分析之后，会得到4个Token，其中有2个Keyword，分别为select和from.[解释器联想2]: 语法分析：根据词法分析的结果，语法解析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法，如果没问题就会构建出 SQL 语法树，这样方便后面模块获取 SQL 类型、表名、字段名、 where 条件等等。[解释器联想3]: 解如果我们输入的 SQL 语句语法不对，就会在解析器这个阶段报错。（释器的主要作用）[解释器联想4]: 解释器只负责检查语法和构建语法树，但是不会去查表或者字段存不存在。第四步：执行 SQL解析SQL无误后，执行SQL需要经过三个步骤：预处理器、优化器、执行器。预处理器检查 SQL 查询语句中的表或者字段是否存在；将 select * 中的 * 符号，扩展为表上的所有列；优化器优化器主要负责将 SQL 查询语句的执行计划确定下来，比如在表里面有多个索引的时候，优化器会基于查询成本的考虑，来决定选择使用哪个索引。[优化器联想1]: 要想知道优化器选择了哪个索引，我们可以在查询语句最前面加个 explain 命令，这样就会输出这条 SQL 语句的执行计划。explain select * from product where id = 1[优化器联想2]: 一般来讲普通索引查询效率高于主键索引，当索引覆盖时会先考虑普通索引的B+树上查询，这就是执行计划，是优化器决定的。执行器确定了执行计划，接下来 MySQL 就真正开始执行语句了，在执行的过程中，执行器就会和存储引擎交互了，交互是以记录为单位的。主键索引查询 select * from product where id = 1; 让InnoDB引擎通过主键索引B+树搜索id=1的记录。全表扫描 select * from product where name = 'iphone'; 查询条件没有用到索引，触发全表扫描，查询每一条记录判断是否满足条件。索引下推 （MySQL 5.6 推出的查询优化策略）[索引下推联想1]: 索引下推能够减少二级索引在查询时的回表操作，提高查询的效率，因为它将 Server 层部分负责的事情，交给存储引擎层去处理了。select * from t_user where age > 20 and reward = 100000;不使用索引下推（MySQL 5.6 之前的版本）时，定位到 age > 20 的一条记录，获取主键值，然后进行回表操作，将完整的记录返回给 Server 层，Server 层再判断该记录的 reward 是否等于 100000。而使用索引下推后，判断记录的 reward 是否等于 100000 的工作交给了存储引擎层：定位到 age > 20 的第一条记录，存储引擎定位到二级索引后，先不执行回表操作，而是先判断一下该索引中包含的列（reward列）的条件（reward 是否等于 100000）是否成立。如果条件不成立，则直接跳过该二级索引。如果成立，则执行回表操作，将完成记录返回给 Server 层。MySQL 执行流程是怎样的？总结：（总结只是简单总结，也就是被问到时该说的，上面的知识点，是可能被追问时涉及的，或者自己说出来的加分项。）连接器：建立连接，管理连接、校验用户身份；查询缓存：查询语句如果命中查询缓存则直接返回，否则继续往下执行。MySQL 8.0 已删除该模块；解析 SQL，通过解析器对 SQL 查询语句进行词法分析、语法分析，然后构建语法树，方便后续模块读取表名、字段、语句类型；执行 SQL：执行 SQL 共有三个阶段：预处理阶段：检查表或字段是否存在；将 select * 中的 * 符号扩展为表上的所有列。优化阶段：基于查询成本的考虑， 选择查询成本最小的执行计划；执行阶段：根据执行计划执行 SQL 查询语句，从存储引擎读取记录，返回给客户端；

MySQL语句学习的难点和重点就在于多表查询,同时MySQL也有诸多方法供大家选择,不论是多表联查（联结表、左连接、右连接……）,这篇文章主要给大家介绍了关于MySQL多表关联查询方式及实际应用的相关资料,需要的朋友可以参考下一、关联查询方式1、 inner join--内连接代表选择的是两个表的交差部分。内连接就是表间的主键与外键相连，只取得键值一致的，可以获取双方表中的数据连接方式。基本语法1SELECT 列名1,列名2... FROM 表1 INNER JOIN 表2 ON 表1.外键=表2.主键 WhERE 条件语句;2、left join--左连接代表选择的是前面一个表的全部。左连接是以左表为标准，只查询在左边表中存在的数据，当然需要两个表中的键值一致。基本语法1SELECT 列名1 FROM 表1 LEFT OUTER JOIN 表2 ON 表1.外键=表2.主键 WhERE 条件语句;3、 right join--右连接代表选择的是后面一个表的全部同理，右连接将会以右边作为基准，进行检索。基本语法1SELECT 列名1 FROM 表1 RIGHT OUTER JOIN 表2 ON 表1.外键=表2.主键 WhERE 条件语句;4、自连接自连接顾名思义就是自己跟自己连接，参与连接的表都是同一张表。(通过给表取别名虚拟出)。基本语法12SELECT 字段名列表  FROM 表1 别名1,表名1 别名2 WHERE 别名1.字段名=别名1.字段名;5、交叉连接不适用任何匹配条件。生成笛卡尔积基本语法1SELECT 列表名 FROM 表名1,表名2;6、子查询1）：子查询是将一个查询语句嵌套在另一个查询语句中。内部嵌套其他select语句的查询，称为外查询或主查询2）：内层查询语句的查询结果，可以为外层查询语句提供查询条件。3）：子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字4）：还可以包含比较运算符：= 、 !=、> 、<等基本语法12SELECT 字段列表 FROM 表名 WHERE 字段名=(SELECT 字段名 FROM 表名);

记录的额外信息记录的额外信息包含 3 个部分：变长字段长度列表、NULL 值列表、记录头信息。1. 变长字段长度列表表的一行数据中，找出类型为 varchar的字段，并将其倒序存储在 变长字段长度列表。比如：表的一行数据中，varchar字段name为11，varchar字段phone是123。那么这个行记录的变长字段长度列表存储样式为 03 02注意：为什么「变长字段长度列表」的信息要按照逆序存放？因为这样可以 使得位置靠前的记录的真实数据和数据对应的字段长度信息可以同时在一个 CPU Cache Line 中，这样就可以提高 CPU Cache 的命中率。每个数据库表的行格式都有「变长字段字节数列表」吗？当数据表没有变长字段的时候，比如全部都是 int 类型的字段，这时候表里的行格式就不会有「变长字段长度列表」了2. NULL 值列表如果存在允许 NULL 值的列，则每个列对应一个二进制位（bit），二进制位按照列的顺序逆序排列。二进制位的值为1时，代表该列的值为NULL。二进制位的值为0时，代表该列的值不为NULL。另外，NULL 值列表必须用整数个字节的位表示（1字节8位），如果使用的二进制位个数不足整数个字节，则在字节的高位补 0。注意：每个数据库表的行格式都有 NULL 值列表 吗？NULL 值列表 不是必须的。当数据表的字段都定义成 NOT NULL 的时候，这时候表里的行格式就不会有 NULL 值列表了。所以在设计数据库表的时候，通常都是建议将字段设置为 NOT NULL，这样可以至少节省 1 字节的空间（NULL 值列表至少占用 1 字节空间）。NULL 值列表 是固定 1 字节空间吗？如果这样的话，一条记录有 9 个字段值都是 NULL，这时候怎么表示？NULL 值列表 的空间不是固定 1 字节的。当一条记录有 9 个字段值都是 NULL，那么就会创建 2 字节空间的 NULL 值列表，以此类推。记录的真实数据 1. row_id如果我们建表的时候指定了主键或者唯一约束列，那么就没有 row_id 隐藏字段了。如果既没有指定主键，又没有唯一约束，那么 InnoDB 就会为记录添加 row_id 隐藏字段。row_id不是必需的，占用 6 个字节。 2. trx_id事务id，表示这个数据是由哪个事务生成的。 trx_id是必需的，占用 6 个字节。 3. roll_pointer这条记录上一个版本的指针。roll_pointer 是必需的，占用 7 个字节。

前置知识要回答这个问题，首先我们得先知道 MySQL 存储一条记录的格式长什么样子。以  Compact 行格式作为例子，它长这样：可以看到，一条完整的记录分为「记录的额外信息」和「记录的真实数据」两个部分。这里重点讲讲记录的额外信息，它包含 3 个部分：变长字段长度列表、NULL 值列表、记录头信息。变长字段长度列表用于存储一行记录中每个变长字段的长度。「变长字段长度列表」所占用的字节数 = 所有「变长字段长度」占用的字节数之和。举个例子，假设数据库表中有 2 个 varchar(10) 类型的字段，分别为 a 和 b，且数据库表的字符集为 ascii 字符集（1 个字符占用 1 字节）。那么a和b字段的数据值的长度分别只需要用1字节表示就行了，因为1字节能表示最大的字节数是 255，而 varchar(10) 类型的字段最大允许存储的字节数是 10 字节，所以只需要用 1 字节表示变长字段的长度就行。那么这种情况下的 「变长字段长度列表」所占用的字节数 = 1 字节 + 1字节 = 2 字节。「变长字段长度列表」不是必须的，如果数据库表没有变长字段，比如字段类型都是int，那么行格式中就不需要「变长字段长度列表」。NULL 值列表用于标记一行记录中字段值为 NULL 的字段，二进制位的值为 1 时，代表该字段的值为NULL，二进制位的值为 0 时，代表该字段的值不为 NULL。另外，NULL 值列表必须用整数个字节的位表示（1字节8位），如果使用的二进制位个数不足整数个字节，则在字节的高位补 0。如果表中允许为 NULL 值的记录的个数小于等于 8 个，那么 NULL 值列表就会用 1 字节表示。如果如果表中允许为 NULL 值的记录的个数大于8 并且小于等于 16，那么 NULL 值列表就会用 2 字节表示，以此类推。因此，如果表中有字段允许为 NULL，那么「NULL 值列表」至少占用 1 字节空间。「NULL 值列表」不是必须的，如果数据库表中的字段都定义成 NOT NULL，那么行格式中就不需要「NULL 值列表」。记录头信息记录头信息中包含的内容很多，比如记录的删除标记位，指向下一条记录的指针等等，不是本文问题的重点，所以我就不细讲了。varchar(n) 中 n 最大取值为多少？我们要清楚一点，MySQL 规定除了 TEXT、BLOBs 这种大对象类型之外，其他所有的列（不包括隐藏列和记录头信息）占用的字节长度加起来不能超过 65535 个字节。也就是说，一行记录除了 TEXT、BLOBs 类型的列，限制最大为 65535 字节，注意是一行的总长度，不是一列。知道了这个前提之后，我们再来看看这个问题：「varchar(n) 中 n 最大取值为多少？」varchar(n) 字段类型的 n 代表的是最多存储的字符数量，并不是字节大小哦。要算 varchar(n) 最大能允许存储的字节数，还要看数据库表的字符集，因为字符集代表着，1个字符要占用多少字节。比如 ascii 字符集， 1 个字符占用 1 字节，那么  varchar(100) 意味着最大能允许存储 100 字节的数据。单字段的情况前面我们知道了，一行记录最大只能存储 65535 字节的数据。那假设数据库表只有一个 varchar(n) 类型的列且字符集是 ascii，在这种情况下， varchar(n) 中 n 最大取值是 65535 吗？不着急说结论，我们先来做个实验验证一下。我们定义一个 varchar(65535) 类型的字段，字符集为 ascii 的数据库表。结果显示，创建失败了。从报错信息就可以知道一行数据的最大字节数是 65535（不包含 TEXT、BLOBs 这种大对象类型），其中包含了 storage overhead。问题来了，这个 storage overhead 是什么呢？其实就是「变长字段长度列表」和 「NULL 值列表」。也就是说一行数据的最大字节数 65535，其实是包含「变长字段长度列表」和 「NULL 值列表」所占用的字节数的。所以， 我们在算 varchar(n) 中 n 最大值时，需要减去 storage overhead  占用的字节数。这是因为我们存储字段类型为 varchar(n)  的数据时，其实分成了三个部分来存储：真实数据真实数据占用的字节数NULL 标识，如果不允许为NULL，这部分不需要本次案例中，「NULL 值列表」所占用的字节数是多少？前面我创建表的时候，字段是允许为 NULL 的，所以会用 1 字节来表示「NULL 值列表」。本次案例中，「变长字段长度列表」所占用的字节数是多少？「变长字段长度列表」所占用的字节数 = 所有「变长字段长度」占用的字节数之和。所以，我们要先知道每个变长字段的「变长字段长度」需要用多少字节表示？具体情况分为：条件一：如果变长字段允许存储的最大字节数小于等于 255 字节，就会用 1 字节表示「变长字段长度」；条件二：如果变长字段允许存储的最大字节数大于 255 字节，就会用 2 字节表示「变长字段长度」；我们这里字段类型是 varchar(65535) ，字符集是 ascii，所以代表着变长字段允许存储的最大字节数是 65535，符合条件二，所以会用 2 字节来表示「变长字段长度」。因为我们这个案例是只有 1 个变长字段，所以「变长字段长度列表」= 1 个「变长字段长度」占用的字节数，也就是 2 字节。因为我们在算 varchar(n) 中 n 最大值时，需要减去 「变长字段长度列表」和 「NULL 值列表」所占用的字节数的。所以，在数据库表只有一个 varchar(n)  字段且字符集是 ascii 的情况下，varchar(n) 中 n 最大值 =  65535 - 2 - 1 = 65532。我们先来测试看看  varchar(65533)  是否可行？可以看到，还是不行，接下来看看 varchar(65532)  是否可行？可以看到，创建成功了。说明我们的推论是正确的，在算 varchar(n) 中 n 最大值时，需要减去 「变长字段长度列表」和 「NULL 值列表」所占用的字节数的。当然，我上面这个例子是针对字符集为 ascii 情况，如果采用的是 UTF-8，varchar(n)  最多能存储的数据计算方式就不一样了：在 UTF-8 字符集下，一个字符串最多需要三个字节，varchar(n) 的 n 最大取值就是 65532/3 = 21844。上面所说的只是针对于一个字段的计算方式。多字段的情况如果有多个字段的话，要保证所有字段的长度 + 变长字段字节数列表所占用的字节数 + NULL值列表所占用的字节数 <= 65535。总结varchar(n) 中 n 最大取值为多少？一行记录最大能存储 65535 字节的数据，但是这个是包含「变长字段字节数列表所占用的字节数」和「NULL值列表所占用的字节数」。所以， 我们在算 varchar(n) 中 n 最大值时，需要减去这两个列表所占用的字节数。如果一张表只有一个 varchar(n)  字段，且允许为 NULL，字符集为 ascii。varchar(n) 中 n 最大取值为 65532。计算公式：65535 - 变长字段字节数列表所占用的字节数 - NULL值列表所占用的字节数 = 65535 - 2 - 1 = 65532。如果有多个字段的话，要保证所有字段的长度 + 变长字段字节数列表所占用的字节数 + NULL值列表所占用的字节数 <= 65535。

在我们的项目开发过程中，经常需要将时间戳或日期时间字段转换为特定的格式，以满足特定的业务需求。MySQL作为广泛使用的关系型数据库管理系统，提供了丰富的日期和时间函数。本文将介绍如何在MySQL中将时间戳或日期时间字段转换为年月日的格式。一、MySQL中的日期和时间类型在MySQL中，日期和时间相关的数据类型主要有以下集中：DATE：仅包含日期部分，格式为’YYYY-MM-DD’TIME：仅包含时间部分，格式为’HH:MM:SS’DATETIME：包含日期和时间部分，格式为’YYYY-MM-DD HH:MM:SS’TIMESTAMP：与DATETIME类似，但范围较小，且与时区相关二、使用DATE_FORMAT函数进行转换在MySQL中，我们可以使用DATE_FORMAT函数将日期时间字段转换为特定的格式。DATE_FORMAT函数的语法如下：1DATE_FORMAT(date, format)其中，date 是要格式化的日期或时间值，format 是指定的格式字符串。要将日期时间字段转换为年月日的格式，我们可以使用以下查询：12SELECT DATE_FORMAT(your_datetime_column, '%Y-%m-%d') AS formatted_date   FROM your_table;在这个例子中，your_datetime_column是包含日期时间值的列名，your_table是表名。%Y 代表四位数的年份，%m代表两位数的月份，%d 代表两位数的日期。查询结果将返回一个名为formatted_date的列，其中包含按照指定格式转换后的日期。

在MySQL中，我们可以通过WITH AS方法创建临时结果集，这些结果集可以在后续的SELECT、DELETE和UPDATE语句中被使用。通过使用WITH AS，我们可以将复杂的语句和功能分解为更小的、更易于管理的部分，从而提高SQL语句的可读性和可维护性。一、WITH AS 方法的基本语法WITH AS的基本语法如下：WITH cte_name (column1, column2, ...) AS (       -- CTE 的定义，即一个 SELECT 语句       SELECT column1, column2, ...       FROM table_name       WHERE condition       -- 其他可能的 SQL 语句，如 JOIN、GROUP BY 等   )   SELECT * FROM cte_name; 在这个语法中，cte_name是你为临时结果集定义的名称，而括号内的部分则是用于生成这个结果集的SQL查询。这个查询可以是任何有效的SELECT语句，包括JOIN、GROUP BY、HAVING等子句。二、使用 WITH AS 创建临时表的案例假设我们有一个销售数据库，其中包含一个名为orders的表，记录了所有的订单信息。现在，我们想要找出每个客户的总订单金额，并按金额降序排列。我们可以使用WITH AS方法来实现这个需求，将计算总订单金额的逻辑封装成一个临时结果集。WITH CustomerTotals AS (       SELECT customer_id, SUM(order_amount) AS total_amount       FROM orders       GROUP BY customer_id   )   SELECT customer_id, total_amount   FROM CustomerTotals   ORDER BY total_amount DESC; 在这个例子中，我们首先使用WITH AS创建了一个名为CustomerTotals的临时结果集，它包含了每个客户的总订单金额。然后，我们在主查询中从这个临时结果集中选择数据，并按总金额降序排列。通过使用WITH AS，我们将复杂的查询逻辑分解为了两个更简单的部分：一个是计算总订单金额，另一个是基于这个计算结果进行排序。这使得查询更加清晰，也更容易理解和维护。三、WITH AS的优势提高可读性：通过将复杂的查询分解为多个简单的部分，WITH AS使得查询逻辑更加清晰，提高了代码的可读性。可重用性：临时结果集（CTE）可以在后续的查询中被多次引用，避免了重复编写相同的SQL逻辑。模块化：通过将查询分解为多个CTE，可以更容易地对每个部分进行单独测试和优化，实现查询的模块化。简化嵌套查询：对于包含多层嵌套子查询的复杂查询，使用WITH AS可以将其扁平化，使得查询结构更加直观。

MySQL建立分区的条件是什么是MySQL分区？MySQL分区是将一张表分割成独立的子表的技术。每个子表被称为分区，它们有着相同的结构和字段，但存储着不同的数据。这项技术可以提高查询速度，减少日志文件和磁盘空间的使用。建立分区的条件要建立MySQL分区，需要满足以下几个条件：1.所需的MySQL版本：MySQL 5.1.5及以上版本支持分区，但仅限于使用InnoDB和MyISAM存储引擎的表。2.分区字段：必须定义一个或多个分区字段来确定如何将数据行分配到各个分区中。分区字段必须是表的主键或唯一索引之一。3.分区类型：MySQL提供了多种分区类型，包括范围分区、哈希分区和列表分区。你需要根据数据特点和查询需求选择合适的分区类型。4.分区数量：决定分区数量需要考虑表的大小、查询的复杂度、硬件资源等因素。建议根据具体情况选取合适的分区数量，一般不宜超过1000个。MySQL分区技术可以大大提高查询效率和管理的便利性，但在实际使用中需要根据具体情况选择合适的分区条件和数量，避免性能瓶颈和资源浪费。分区表介绍MySQL 数据库中的数据是以文件的形势存在磁盘上的，默认放在 /var/lib/mysql/ 目录下面，我们可以通过 show variables like '%datadir%'; 命令来查看：在 MySQL 中，如果存储引擎是 MyISAM，那么在 data 目录下会看到 3 类文件：.frm、.myi、.myd，如下：*.frm：这个是表定义，是描述表结构的文件。*.myd：这个是数据信息文件，是表的数据文件。*.myi：这个是索引信息文件。如果存储引擎是 InnoDB, 那么在 data 目录下会看到两类文件：.frm、.ibd，如下：*.frm：表结构文件。*.ibd：表数据和索引的文件。无论是哪种存储引擎，只要一张表的数据量过大，就会导致 *.myd、*.myi 以及 *.ibd 文件过大，数据的查找就会变的很慢。为了解决这个问题，我们可以利用 MySQL 的分区功能，在物理上将这一张表对应的文件，分割成许多小块，如此，当我们查找一条数据时，就不用在某一个文件中进行整个遍历了，我们只需要知道这条数据位于哪一个数据块，然后在那一个数据块上查找就行了；另一方面，如果一张表的数据量太大，可能一个磁盘放不下，这个时候，通过表分区我们就可以把数据分配到不同的磁盘里面去。通俗地讲表分区是将一大表，根据条件分割成若干个小表。如：某用户表的记录超过了600万条，那么就可以根据入库日期将表分区，也可以根据所在地将表分区。当然也可根据其他的条件分区。MySQL 从 5.1 开始添加了对分区的支持，分区的过程是将一个表或索引分解为多个更小、更可管理的部分。对于开发者而言，分区后的表使用方式和不分区基本上还是一模一样，只不过在物理存储上，原本该表只有一个数据文件，现在变成了多个，每个分区都是独立的对象，可以独自处理，也可以作为一个更大对象的一部分进行处理。需要注意的是，分区功能并不是在存储引擎层完成的，常见的存储引擎如 InnoDB、MyISAM、NDB 等都支持分区。但并不是所有的存储引擎都支持，如 CSV、FEDORATED、MERGE 等就不支持分区，因此在使用此分区功能前，应该对选择的存储引擎对分区的支持有所了解。表分区的优缺点和限制MySQL分区有优点也有一些缺点，如下：优点：查询性能提升：分区可以将大表划分为更小的部分，查询时只需扫描特定的分区，而不是整个表，从而提高查询性能。特别是在处理大量数据或高并发负载时，分区可以显著减少查询的响应时间。管理和维护的简化：使用分区可以更轻松地管理和维护数据。可以针对特定的分区执行维护操作，如备份、恢复、优化和数据清理，而不必处理整个表。这简化了维护任务并减少了操作的复杂性。数据管理灵活性：通过分区，可以根据业务需求轻松地添加或删除分区，而无需影响整个表。这使得数据的增长和变化更具弹性，可以根据需求进行动态调整。改善数据安全性和可用性：可以将不同分区的数据分布在不同的存储设备上，从而提高数据的安全性和可用性。例如，可以将热数据放在高速存储设备上，而将冷数据放在廉价存储设备上，以实现更高的性能和成本效益。缺点：复杂性增加：分区引入了额外的复杂性，包括分区策略的选择、表结构的设计和维护、查询逻辑的调整等。正确地设置和管理分区需要一定的经验和专业知识。索引效率下降：对于某些查询，特别是涉及跨分区的查询，可能会导致索引效率下降。由于查询需要在多个分区之间进行扫描，可能无法充分利用索引优势，从而影响查询性能。存储空间需求增加：使用分区会导致一定程度的存储空间浪费。每个分区都需要占用一定的存储空间，包括分区元数据和一些额外的开销。因此，对于分区键的选择和分区粒度的设置需要权衡存储空间和性能之间的关系。功能限制：在某些情况下，分区可能会限制某些MySQL的功能和特性的使用。例如，某些类型的索引可能无法在分区表上使用，或者某些DDL操作可能需要更复杂的处理。在考虑使用分区时，需要综合考虑业务需求、查询模式、数据规模和硬件资源等因素，并权衡分区带来的优势和缺点。对于特定的应用和数据场景，分区可能是一个有效的解决方案，但并不适用于所有情况。同时分区表也存在一些限制，如下：限制：在mysql5.6.7之前的版本，一个表最多有1024个分区；从5.6.7开始，一个表最多可以有8192个分区。分区表无法使用外键约束。NULL值会使分区过滤无效。所有分区必须使用相同的存储引擎。分区适用场景分区表在以下情况下可以发挥其优势，适用于以下几种使用场景：大型表处理：当面对非常大的表时，分区表可以提高查询性能。通过将表分割为更小的分区，查询操作只需要处理特定的分区，从而减少扫描的数据量，提高查询效率。这在处理日志数据、历史数据或其他需要大量存储和高性能查询的场景中非常有用。时间范围查询：对于按时间排序的数据，分区表可以按照时间范围进行分区，每个分区包含特定时间段内的数据。这使得按时间范围进行查询变得更高效，例如在某个时间段内检索数据、生成报表或执行时间段的聚合操作。数据归档和数据保留：分区表可用于数据归档和数据保留的需求。旧数据可以归档到单独的分区中，并将其存储在低成本的存储介质上。同时，可以保留较新数据在高性能的存储介质上，以便快速查询和操作。并行查询和负载均衡：通过哈希分区或键分区，可以将数据均匀地分布在多个分区中，从而实现并行查询和负载均衡。查询可以同时在多个分区上进行，并在最终合并结果，提高查询性能和系统吞吐量。数据删除和维护：使用分区表，可以更轻松地删除或清理不再需要的数据。通过删除整个分区，可以更快速地删除大量数据，而不会影响整个表的操作。此外，可以针对特定分区执行维护任务，如重新构建索引、备份和优化，以减少对整个表的影响。分区表并非适用于所有情况。在选择使用分区表时，需要综合考虑数据量、查询模式、存储资源和硬件能力等因素，并评估分区对性能和管理的影响。分区方式分区有2种方式，水平切分和垂直切分。MySQL 数据库支持的分区类型为水平分区，它不支持垂直分区。此外，MySQL数据库的分区是局部分区索引，一个分区中既存放了数据又存放了索引。而全局分区是指，数据存放在各个分区中，但是所有数据的索引放在一个对象中。目前，MySQL数据库还不支持全局分区。分区策略RANGE分区RANGE分区是MySQL中的一种分区策略，根据某一列的范围值将数据分布到不同的分区。每个分区包含特定的范围。下面是RANGE分区的定义方式、特点以及代码示例。定义方式：指定分区键：选择作为分区依据的列作为分区键，通常是日期、数值等具有范围特性的列。分区函数：通过PARTITION BY RANGE指定使用RANGE分区策略。定义分区范围：使用VALUES LESS THAN子句定义每个分区的范围。RANGE分区的特点：范围划分：根据指定列的范围进行分区，适用于需要按范围进行查询和管理的情况。灵活的范围定义：可以定义任意数量的分区，并且每个分区可以具有不同的范围。高效查询：根据查询条件的范围，MySQL能够快速定位到特定的分区，提高查询效率。动态管理：可以根据业务需求轻松添加或删除分区，适应数据增长或变更的需求。在上述示例中，我们创建了名为sales的表，使用RANGE分区策略。根据sales_date列的年份范围将数据分布到不同的分区。PARTITION BY RANGE (YEAR(sales_date))：指定使用RANGE分区，基于sales_date列的年份进行分区。PARTITION p1 VALUES LESS THAN (2020)：定义名为p1的分区，包含年份小于2020的数据。PARTITION p2 VALUES LESS THAN (2021)：定义名为p2的分区，包含年份小于2021的数据。PARTITION p3 VALUES LESS THAN (2022)：定义名为p3的分区，包含年份小于2022的数据。PARTITION p4 VALUES LESS THAN MAXVALUE：定义名为p4的分区，包含超出定义范围的数据。RANGE分区允许根据列值的范围将数据分散到不同的分区中，适用于按范围进行查询和管理的情况。它提供了更灵活的数据管理和查询效率的提升。LIST分区LIST分区是根据某一列的离散值将数据分布到不同的分区。每个分区包含特定的列值列表。下面是LIST分区的定义方式、特点以及代码示例。定义方式：指定分区键：选择作为分区依据的列作为分区键，通常是具有离散值的列，如地区、类别等。分区函数：通过PARTITION BY LIST指定使用LIST分区策略。定义分区列表：使用VALUES IN子句定义每个分区包含的列值列表。LIST分区的特点：列值离散：根据指定列的具体取值进行分区，适用于具有离散值的列。灵活的分区定义：可以定义任意数量的分区，并且每个分区可以具有不同的列值列表。高效查询：根据查询条件的列值直接定位到特定分区，提高查询效率。动态管理：可以根据业务需求轻松添加或删除分区，适应数据增长或变更的需求。在上述示例中，我们创建了名为users的表，使用LIST分区策略。根据region列的具体取值将数据分布到不同的分区。PARTITION BY LIST (region)：指定使用LIST分区，基于region列的值进行分区。PARTITION p_east VALUES IN ('New York', 'Boston')：定义名为p_east的分区，包含值为’New York’和’Boston’的region列的数据。PARTITION p_west VALUES IN ('Los Angeles', 'San Francisco')：定义名为p_west的分区，包含值为’Los Angeles’和’San Francisco’的region列的数据。PARTITION p_other VALUES IN (DEFAULT)：定义名为p_other的分区，包含其他region列值的数据。HASH分区是使用哈希算法将数据均匀地分布到多个分区中。下面是HASH分区的定义方式、特点以及代码示例。定义方式：指定分区键：选择作为分区依据的列作为分区键。分区函数：通过PARTITION BY HASH指定使用HASH分区策略。定义分区数量：使用PARTITIONS关键字指定分区的数量。HASH分区的特点：数据均匀分布：HASH分区使用哈希算法将数据均匀地分布到不同的分区中，确保数据在各个分区之间平衡。并行查询性能：通过将数据分散到多个分区，HASH分区可以提高并行查询的性能，多个查询可以同时在不同分区上执行。简化管理：HASH分区使得数据管理更加灵活，可以轻松地添加或删除分区，以适应数据增长或变更的需求。我们创建了名为sensor_data的表，使用HASH分区策略。根据id列的哈希值将数据分布到4个分区中。PARTITION BY HASH (id)：指定使用HASH分区，基于id列的哈希值进行分区。PARTITIONS 4：指定创建4个分区。KEY分区KEY分区是根据某一列的哈希值将数据分布到不同的分区。不同于HASH分区，KEY分区使用的是列值的哈希值而不是哈希函数。下面是KEY分区的定义方式、特点以及代码示例。定义方式：指定分区键：选择作为分区依据的列作为分区键。分区函数：通过PARTITION BY KEY指定使用KEY分区策略。定义分区数量：使用PARTITIONS关键字指定分区的数量。KEY分区的特点：哈希分布：KEY分区使用列值的哈希值将数据分布到不同的分区中，与哈希函数不同，它使用的是列值的哈希值。高度自定义：KEY分区允许根据业务需求自定义分区逻辑，可以灵活地选择分区键和分区数量。并行查询性能：通过将数据分散到多个分区，KEY分区可以提高并行查询的性能，多个查询可以同时在不同分区上执行。简化管理：KEY分区使得数据管理更加灵活，可以轻松地添加或删除分区，以适应数据增长或变更的需求。HASH分区

随着数据量的不断增长，数据库的性能和扩展性面临越来越大的挑战。为了解决这些问题，MySQL提供了多种数据分割方案，其中最常见的是分表和分区分表。虽然这两种方法都是为了提高数据库性能和管理效率，但它们在实现原理、应用场景和操作方式上存在显著差异。一、什么是分表？分表（Sharding）是将一个大型表的数据按某种规则拆分到多个独立的表中。分表的目的是将数据分散到多个存储单元中，以减轻单表的数据量和访问压力，从而提高数据库的性能和可扩展性。1.1 分表的实现方式分表可以在应用层或者通过数据库中间件来实现。常见的分表策略有：水平分表（Horizontal Sharding）：根据某个字段的值（如用户ID、订单ID等）将数据划分到多个表中，每个表结构相同但存储不同的数据。垂直分表（Vertical Sharding）：根据业务功能或数据模块将表的列拆分到多个表中，每个表存储不同的列，但所有表的主键相同。1.2 分表的示例假设有一个用户表 users，包含大量用户数据，可以按用户ID进行水平分表： CREATE TABLE users_0 (     id INT PRIMARY KEY,     name VARCHAR(50),     email VARCHAR(50) );  CREATE TABLE users_1 (     id INT PRIMARY KEY,     name VARCHAR(50),     email VARCHAR(50) );  -- 应用程序中实现分表逻辑 public String getTableName(int userId) {     int tableIndex = userId % 2;     return "users_" + tableIndex; } 二、什么是分区分表？分区分表（Partitioning）是将一个表的数据按某种规则划分成多个分区，每个分区存储一部分数据。分区分表的目的是优化查询性能和管理效率，特别是在处理大数据量时。2.1 分区分表的类型MySQL支持多种分区类型，常见的有：范围分区（Range Partitioning）：按数值或日期范围划分数据。列表分区（List Partitioning）：按离散的值列表划分数据。哈希分区（Hash Partitioning）：按哈希函数的结果划分数据。键分区（Key Partitioning）：类似于哈希分区，但使用MySQL内置的函数。2.2 分区分表的示例假设有一个订单表 orders，可以按订单日期进行范围分区：CREATE TABLE orders (     id INT PRIMARY KEY,     order_date DATE,     amount DECIMAL(10, 2) ) PARTITION BY RANGE (YEAR(order_date)) (     PARTITION p2021 VALUES LESS THAN (2022),     PARTITION p2022 VALUES LESS THAN (2023),     PARTITION p2023 VALUES LESS THAN (2024) ); 三、分表与分区分表的区别3.1 数据存储结构分表：将数据拆分到多个独立的表中，这些表可以分布在同一个数据库或不同的数据库实例上。每个表都是独立的存储单元。分区分表：将数据划分成多个分区，所有分区仍然属于同一个表和同一个数据库实例。分区是表的逻辑部分，每个分区存储一部分数据。3.2 实现方式分表：通常在应用层或通过数据库中间件实现，需要编写代码逻辑或使用中间件配置来确定数据的存储位置。分区分表：在数据库层实现，通过SQL语句定义分区规则，数据库系统自动管理分区的数据存储和访问。3.3 管理和维护分表：需要手动管理各个分表，包括表的创建、数据迁移和备份恢复等操作。跨表查询需要应用程序处理或使用中间件支持。分区分表：数据库系统自动管理分区，支持自动分区裁剪和优化。跨分区查询由数据库系统处理，不需要额外的应用程序逻辑。3.4 性能与扩展性分表：适合大规模数据的分布式存储和高并发访问，可以通过增加数据库实例来扩展系统的存储和处理能力。但分表后的数据一致性和事务管理变得复杂。分区分表：适合中等规模的数据优化，主要提升查询性能和管理效率。受限于单个数据库实例的资源，扩展性相对较弱。3.5 使用场景分表：适用于数据量特别大、需要分布式存储和高并发访问的场景，如大型电商平台、社交网络等。分区分表：适用于大数据量的查询优化和管理，如日志数据、历史记录等。四、分表和分区分表的优缺点4.1 分表的优缺点优点：提高系统的可扩展性和高可用性。分散数据和负载，减轻单表压力。适用于大规模数据和高并发场景。缺点：实现和维护复杂，增加开发和运维成本。跨表查询复杂，可能需要中间件支持。数据一致性和事务管理变得困难。4.2 分区分表的优缺点优点：简化数据管理，支持自动分区裁剪和优化。提升查询性能，特别是按分区键查询时。管理和维护相对简单，减少开发和运维成本。缺点：受限于单个数据库实例的资源，扩展性有限。不适合数据量特别大的场景。跨分区查询仍需考虑性能问题。五、总结MySQL分表和分区分表是两种常见的数据分割方案，各有优缺点和适用场景。分表适用于大规模数据和高并发访问场景，通过分散数据和负载，提升系统的可扩展性和高可用性。但其实现和维护复杂，跨表查询和数据一致性管理困难。分区分表则主要用于中等规模的数据优化，通过数据库系统自动管理分区，提升查询性能和管理效率，但扩展性相对较弱。

基本建表语句CREATE TABLE table_name (     column_name1 data_type(size) [column_constraints],     column_name2 data_type(size) [column_constraints],     ...     [table_constraints] ) [table_options];table_name: 新建表的名称。column_name1, column_name2, ...: 表中各列的名称。data_type: 列的数据类型，如 INT, VARCHAR, TEXT, DATE, TIMESTAMP 等。size: 数据类型的长度或大小（对于某些数据类型适用）。[column_constraints]: 列级约束，例如 NOT NULL, AUTO_INCREMENT, DEFAULT, PRIMARY KEY, UNIQUE, COMMENT 等。[table_constraints]: 表级约束，如 PRIMARY KEY, FOREIGN KEY, UNIQUE 等。[table_options]: 表的其他选项，如 ENGINE, AUTO_INCREMENT, CHARSET, COMMENT 等。数据类型  MySQL 支持多种数据类型，以下是一些常见的数据类型：INT: 整数类型。VARCHAR(size): 可变长度的字符串，size 表示最大字符数。CHAR(size): 固定长度的字符串。TEXT: 长文本数据。DATE: 日期，格式为 YYYY-MM-DD。DATETIME: 日期和时间，格式为 YYYY-MM-DD HH:MM:SS。TIMESTAMP: 时间戳，记录数据变更的日期和时间。FLOAT: 浮点数。DOUBLE: 双精度浮点数。DECIMAL(M, D): 定点数，M 是总位数，D 是小数点后的位数。列级约束NOT NULL: 该列不能有 NULL 值。AUTO_INCREMENT: 用于整数类型，自动递增。DEFAULT value: 为列指定默认值。PRIMARY KEY: 将列设置为表的主键。UNIQUE: 保证列中的每个值都是唯一的。COMMENT 'string': 为列添加注释。表级约束PRIMARY KEY (column1, column2, ...): 指定一个或多个列作为主键。UNIQUE KEY (column1, column2, ...): 指定一个或多个列作为唯一键。FOREIGN KEY (column) REFERENCES parent_table(column): 指定一个外键，创建与另一个表的引用关系。INDEX (column1, column2, ...): 创建一个或多个列的索引。表选项ENGINE=storage_engine: 指定存储引擎，如 InnoDB（默认）、MyISAM 等。AUTO_INCREMENT=value: 为 AUTO_INCREMENT 的列指定初始值。CHARSET=character_set: 指定表的默认字符集。COMMENT 'string': 为表添加注释。

在数据库设计中，约束（Constraints）是确保数据完整性和一致性的关键工具。MySQL 作为流行的关系型数据库管理系统，提供了多种约束类型来维护数据的准确性和可靠性。本文将详细探讨 MySQL 的各种表约束，包括它们的定义、用法、注意事项以及最佳实践。1. 什么是表约束？表约束是应用于数据库表的规则，用于限制表中的数据，以确保数据的完整性和有效性。约束有助于防止不正确的数据进入数据库，从而保证数据的一致性和准确性。2. 常见的 MySQL 表约束类型2.1 NOT NULL 约束NOT NULL 约束用于确保某列不能有 NULL 值。这对于必须包含数据的字段（如用户名、电子邮件地址等）非常重要。示例：CREATE TABLE Users (     id INT AUTO_INCREMENT PRIMARY KEY,     username VARCHAR(50) NOT NULL,     email VARCHAR(100) NOT NULL ); 在此示例中，username 和 email 列被设置为 NOT NULL，意味着每条记录必须包含这两个字段的值。2.2 UNIQUE 约束UNIQUE 约束用于确保一列或多列的值在表中是唯一的。它防止重复的值出现在指定列中。示例：CREATE TABLE Users (     id INT AUTO_INCREMENT PRIMARY KEY,     username VARCHAR(50) NOT NULL UNIQUE,     email VARCHAR(100) NOT NULL UNIQUE ); 在此示例中，username 和 email 列被设置为 UNIQUE，确保每个用户都有唯一的用户名和电子邮件地址。2.3 PRIMARY KEY 约束PRIMARY KEY 约束用于唯一标识表中的每条记录。一个表只能有一个主键，但主键可以由多列组合而成。示例：CREATE TABLE Users (     id INT AUTO_INCREMENT PRIMARY KEY,     username VARCHAR(50) NOT NULL,     email VARCHAR(100) NOT NULL ); 2.4 FOREIGN KEY 约束FOREIGN KEY 约束用于确保数据的一致性和完整性，通过引用另一表的主键来建立表之间的关系。它确保引用的值在父表中存在，从而保持数据的参照完整性。示例：CREATE TABLE Orders (     order_id INT AUTO_INCREMENT PRIMARY KEY,     user_id INT,     order_date DATE,     FOREIGN KEY (user_id) REFERENCES Users(id) ); 

在实际生产环境中，应按照软件安全设计的「最小特权原则」设置MySQL的文件权限。MySQL「安装目录」的属主和属组需要设置成mysql用户；MySQL的「历史操作文件」、「历史命令文件」、「数据物理存储文件」只给属主用户读写权限；MySQL的「配置文件」只给属主用户读写权限，属组和其他用户给只读权限。依次执行下列命令，检查权限是否符合要求：ll ~/.mysql_history ~/.bash_history 权限600ll /etc/my.cnf 权限644find / -name *.ibd | xargs ls -al 权限600find / -name *.MYD | xargs ls -al 权限600find / -name *.MYI| xargs ls -al 权限600find / -name *.frm| xargs ls -al 权限600接下来给大家解释一下这些文件都是干嘛的。1、数据库配置文件/etc/my.cnf 是MySQL数据库「配置文件」，为了防止未授权篡改，应设置权限为 644。ll /etc/my.cnf 检查配置文件权限：/etc/my.cnf 默认有以下字段：datadir：数据库目录socket：MySQL客户端程序与服务端通信的套接字文件log-error：日志位置pid-file：存放MySQL进程id的文件2、数据存储文件MySQL每创建一个「表」，都会在数据库目录下创建一个「二进制文件」，用来存储表中的「数据」。下图中可以看到，除了information_schema 和 performance_schema ，每个数据库都对应一个目录，目录下存放这个数据库的表文件。MySQL8.0以前，数据存储文件统一用 .frm 扩展名。MySQL8.0以后，不同的数据库引擎，保存文件的扩展名不一样。InnoDB：独享表空间用 .idb，一个表对应一个文件；共享表空间用 .ibdata，多个表公用一个文件。MyISAM：表的数据用 .MYD；表的索引用 .MYI。Archive： .arcCSV： .csv查看支持的引擎 show engines;，default表示默认，正在使用的引擎。为了防止未授权访问和篡改，数据存储文件的权限应配置为 600。检查数据库文件的权限：find / -name *.ibd | xargs ls -alfind / -name *.MYD | xargs ls -alfind / -name *.MYI| xargs ls -alfind / -name *.frm| xargs ls -al3、历史操作文件~/.mysql_history 和 ~/.bash_history 分别存储MySQL「历史操作命令」和「系统历史命令」。为了防止未授权访问和篡改，应将文件权限配置为 600。1ll ~/.mysql_history ~/.bash_history

在 SQL 中，WITH RECURSIVE 是一个用于创建递归查询的语句。它允许你定义一个 Common Table Expression (CTE)，该 CTE 可以引用自身的输出。递归 CTE 非常适合于查询具有层次结构或树状结构的数据，例如组织结构、文件系统或任何其他具有自引用关系的数据。一、基本语法WITH RECURSIVE cte_name (column1, column2, ...) AS (-- 非递归的初始部分，定义了 CTE 的起点SELECT ...FROM ...UNION ALL-- 递归部分，可以引用 CTE 的别名SELECT ...FROM cte_nameWHERE ...)-- 最后的 SELECT 或其他 DML 语句，使用递归 CTESELECT * FROM cte_name;二、示例假设我们有一个表示组织结构的表 employees，其中包含 id, manager_id 和 name 字段。manager_id 是员工的上级经理的 id，如果 manager_id 是 NULL，则表示该员工是 CEO 或顶层经理。我们想要查询整个组织结构中的所有员工及其上级经理。WITH RECURSIVE employee_hierarchy (id, name, manager_id, path) AS (-- 非递归的初始部分：查找顶层经理（没有经理的员工）SELECTid,name,manager_id,CONCAT(name, '/') AS path -- 使用 CONCAT 创建初始路径FROM employeesWHERE manager_id IS NULLUNION ALL-- 递归部分：查找所有下属SELECTe.id,e.name,e.manager_id,CONCAT(e.name, '/', eh.path) AS path -- 将当前员工添加到路径中FROM employees eINNER JOIN employee_hierarchy eh ON e.manager_id = eh.id)SELECT * FROM employee_hierarchy;在这个例子中：WITH RECURSIVE 开始定义一个递归 CTE employee_hierarchy。CTE 中的 column1, column2, … 是你想要在结果中选择的列。初始查询部分（在 UNION ALL 之前）定义了递归的起点，通常是顶级节点或者查询的基本情况。递归查询部分（在 UNION ALL 之后）使用 CTE 的别名来引用自身的输出，以便能够递归地查询下属或子节点。UNION ALL 用于合并初始查询和递归查询的结果，它允许重复的行，这是递归查询的关键部分。最后的 SELECT * FROM employee_hierarchy; 是最终的查询，它将返回 CTE 的全部结果。递归 CTE 是 SQL 中处理分层数据的强大工具，但它们也可能很复杂，需要仔细设计以避免无限递归或不正确的结果。