MySQL开发实战总结(一)

原创已于 2025-01-04 23:18:18 修改 · 775 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#运维 #数据库 #database

于 2022-06-03 16:58:29 首次发布

数据库中间件专栏收录该内容

16 篇文章

订阅专栏

本文详细阐述了MySQL数据库的版本选择（5.7和8.x），安装方法（Docker、生产环境主备架构、容灾改造），权限管理（Root、库表权限），建表规范（SQL存档、索引使用）、数据清理（定时删除、分区表）、分库分表策略、时间存储、参数调优、驱动选择、时区编码、ORM框架、监控与事务处理，以及安全设置。提供了一个全面的MySQL开发知识体系。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1、线上确定版本

①、目前主流是5.7.X和8.X版本，8.x版本优化，读写性能更强。

2、安装方式：

①、最简单测试使用，直接官网下载一个包，照个教程安装配置即可，可以使用Docker来安装使用，也比较快捷方便，在测试机器上部署完后开放端口，连接测试使用即可.

②、生产使用：可以在不同机器上面搭建最简单主备或者读写分离库，并配置一些监控脚本，单独挂盘SSD保证稳定，binlog指定对应的保存时间过期清理，也可以购买云服务RDS数据库，选择对应的实例规格，带有完整的监控，迁移数据，扩容规格，运维都比较方便，优化参数，一般企业都在使用，业务使用直接连接vip浮动地址即可，可以做到安全.

③、容灾改造：对于数据库确保高可用容灾架构，购买云服务一般在同一个Region两台机器，大型项目保证容灾架构，一般在不同Region也有实例改造，跨AZ容灾,一主一备一级联，比如北京四和北京一都有，然后给出一个浮动IP，即使一边的AZ挂了也不影响业务稳定.

3、库表权限

①、一般最高权限Root都是DBA负责，对于一些业务创建库和表单独分配一定合理可控权限，grant授予对表的权限，对于一些有定时器和分区表权限单独分配，主被复制单独指定账号，可以指定网段和指定IP来实现复制，防止权限过大执行操作.

②、对于临时业务需要，可以单独指定一个临时账号，然后用完删除即可，适用一些修改业务库密码和特殊业务.

③、端口、密码、账号合规，不用默认的3306，注意网段VPC划分.

4、建表规范

①、建表SQL代码仓库归档，建表字段指定描述，主键根据业务场景，选择自增、UUID、联合多字段主键方式，虽然innodb自增主键可以指定其实位置，按照递增顺序能索引节点顺序连接，B+数索引节点顺序一直，但是如果修改删除也多就不推荐，根据实际场景来设计，后续分表也是比较麻烦了.

②、合适评估业务场景建立适当索引，无用的索引会导致写入数据建立索引过慢影响速度，联合索引优先考虑，覆盖索引优先考虑.

③、评估无用的索引情况，能删则删，不浪费必要的存储空间和写入速度.

④、建表指定数据创建时间和更新时间以及一些业务场景的逻辑删除常见.

问题：遇到日志文件过大例如几个G删除时候不方便时候，可以通过以下命令来进行快速清空，直接rm删除不一定释放空间，因为还是进程在执行写入.

①、echo "" > /opt/data/access.log

②、cat /dev/null > access.log

③、> access.log

du -ah --max-depth=1，不释放空间原理.

du -sh /tmp/*|sort -nr|head -3

kill掉相应的进程.

停掉使用这个文件的应用，让os自动回收磁盘空间.

一个文件在文件系统中的存放分为两个部分：数据部分和指针部分，指针位于文件系统的meta-data中，在将数据删除后，这个指针就从meta-data中清除了，而数据部分存储在磁盘中。在将数据对应的指针从meta-data中清除后，文件数据部分占用的空间就可以被覆盖并写入新的内容，之所以在出现删除access_log文件后，空间还没释放，就是因为httpd进程还在一直向这个文件写入内容，导致虽然删除了access_log文件，但是由于进程锁定，文件对应的指针部分并未从meta-data中清除，而由于指针并未删除，系统内核就认为文件并未删除，因此通过df命令查询空间并未释放也就不足为奇了

5、数据清理

①、一般业务数据会保存一定的时间，根据时长决定删除数据，记得整理表空间碎片，一般删除数据、更新数据会有一定的碎片，无法完整利用，导致mysql写数据时候无完整页空间后，浪费空间.

②、时序数据可以考虑分区表，如按照时间的Range的分区方式，按天分区还是按照月分区，表改造为分区表，预建立未来的表，定时删除之前的分区表，可以完整解决表空间膨胀问题，基于分布式定时任务时间，对于删除失败或者建立分区失败可以发送业务告警来满足稳定性,使用es也是一套按天或者按月划分的方式.

③、可以基于一定规则分表实现，然后过期后直接drop物理表也比较方便，按天按月都可以，业务上垂直分库也方便些.

④、删除库表数据drop、删除部分数据delete、直接清空表数据truncate.

⑤、也可以使用mysql的event定时器，但是不可控，遇到异常情况无法优先.

⑥、数据备份，可以使用xxl-job的shell定时执行备份脚本，到不同的组件中，如果OBS桶或者一些对象存储介质，可以使用Flink CDC2.0来同步到其他数据库中.

6、分库分表

①、垂直分库：根据不同业务拆分不同的数据库方便扩展，可以部署不同的数据库实例有效降低数据库压力 .

②、垂直分表：大表字段拆分、拆分为小表、减少单表字段.

③、水平分表：根据数据字段取模拆分不同的库不同的表，制定单表命名规则，如不同的类型拼接出完整表，然后业务侧读写指定表，有hash取模,对指定账号%20，这里取20张字表，然后分为0-19，数据范围分表方式预估一个大小范围，然后写入不同的库中,当然可以先hash取模进行分库，然后在范围划分分表，以及按照年份或者月份分表如tablename_2024、tablename_2023。

④、数据迁移可以使用一些厂商的云服务，或者是双写迁移，界面对接不同的数据库grafana监控观察.

7、业务时间存储

①、直接使用point的秒级时间戳、毫秒级时间，以long型存储.

②、datetime的日期格式.

③、timestamp

函数	描述
NOW()	返回当前的日期和时间
CURDATE()	返回当前的日期
CURTIME()	返回当前的时间
DATE()	提取日期或日期/时间表达式的日期部分
EXTRACT()	返回日期/时间按的单独部分
DATE_ADD()	给日期添加指定的时间间隔
DATE_SUB()	从日期减去指定的时间间隔
DATEDIFF()	返回两个日期之间的天数
DATE_FORMAT()	用不同的格式显示日期/时间

有一张销售明细表sale，记录了每个商品的销售信息，表中有一个datetime字段记录了商品销售的时间，格式为yyyy-MM-dd HH:mm:ss。现在需要对sale表做聚合统计分析，要求统计每天、周、月、季度的销量数据

每日销量：


SELECT
  DATE( create_time ) sale_day,
  count( * ) record_count 
FROM
  sale
GROUP BY
  sale_day
ORDER BY   
  sale_day

每周销量：


SELECT   
    YEARWEEK(create_time, 1) AS week_number,   
    COUNT(*) AS record_count  
FROM   
    sale
GROUP BY   
    YEARWEEK(create_time, 1)  
ORDER BY   
    week_number;

每月销量：


SELECT
  DATE_FORMAT( create_time, '%Y-%m' ) AS month_num,
  COUNT(*) AS record_count 
FROM
  sale 
GROUP BY
  month_num 
ORDER BY
  month_num

季度销量：


SELECT   
 CONCAT(YEAR(create_time), ' Q', QUARTER(create_time)) AS year_quarter,  
 COUNT(*) AS record_count  
FROM   
 sale  
GROUP BY   
 YEAR(create_time),  
 QUARTER(create_time)  
ORDER BY   
 YEAR(create_time),  
 QUARTER(create_time);

8、必要的参数调优

①、例如最大连接数innodb-buffer-pool-size、max-connections、log-bin、高并发下innodb_lock_wait_timeout、最大时间、最大数据包大小、服务端字符集和字符，最大连接数根据数据库实例连接数情况评估，一般有多少业务方数据库连接池和集群规格情况合理设置.

②、对应数据库连接池指定最长生命周期，过期释放，然后在新创建，一定程度降低服务端连接内存冲高场景，数据库连接池对接一定的监控平台采集数据，观察情况再调整.

③、错误日志、二进制日志、中继日志配置.

9、数据库驱动选择

①、一般选择mysql驱动,8.x驱动兼容5.7.x数据库版本.

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.29</version>
</dependency>

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.49</version>
</dependency>

②、考虑License安全可以使用，是mysql的一个开源分支.

<dependency>
    <groupId>org.mariadb.jdbc</groupId>
    <artifactId>mariadb-java-client</artifactId>
    <version>3.0.5</version>
</dependency>

③、版本查看.[select version()]

10、数据库时间和编码和开启SSL

①、数据库时区查看：show variables like '%time_zone%';

②、设置set time_zone = '+8:00';

③、数据库编码查看：show variables like "char%";

④、默认没有配置SSL证书位置，在驱动中使用useSsl=false，配置证书开启即可;

11、ORM框架选择

①、MyBatis、MyBatisPlus单表多表动态SQL，建议还是封装一下，涉及到改动升级就是一个大的工程了.

②、Hibernate封装BaseDao，其他业务开发自己的XXDao，底层来使用BaseDao中业务方法，后续设计到升级开源包，改动对应的API是最小的，也比较方便，维护平台层也方便.

③、JdbcTemplate/Jdbc来完成原始预编译SQL.

12、监控、死锁、事务、连接

①、information_schema库

②、sys库

定位锁等待

show processlist【查看连接情况Query】

①、select * from information_schema.innodb_lock_waits;

定位锁

①、select * from information_schema.innodb_locks;

定位事务

①、select trx_id,trx_started,trx_requested_lock_id,trx_query,trx_mysql_thread_id from information_schema.innodb_trx;

结果有两个事务，MySQL 事务线程 id 为 38 和 41，很直观的看到 41 是我们的 delete 事务，被 38 锁定

定位线程

①、select * from performance_schema.threads where processlist_id=38;

结果找到 MySQL 事务线程 38 对应的服务器线程 63。

定位加锁 SQL

select * from performance_schema.events_statements_current where thread_id=63;

可以从这些库里面查询相关信息分析问题.

13、统计监控TPS和QPS

在没有云监控的场景中，我们可以通过mysql中的系统状态值来计算 .

QPS = Queries / Seconds

Queries 是系统状态值--总查询次数，可以通过 show status 查询得出

Seconds 是监控的时间区间，单位为秒例如采样10秒内的查询次数，那么先查询一次Queries值（Q1），等待10秒，再查询一次Queries值（Q2） QPS = (Q2 - Q1) / 10

mysql中没有直接的事务计数器，需要通过事务提交数和事务回滚数来计算

TPS = (Com_commit + Com_rollback) / Seconds Com_commit、Com_rollback 的值通过 show status 查询得出

14、MySQL的连接线程数统计

Threads_connected 当前连接的线程的个数 Threads_running 运行状态的线程的个数

Linux下可以使用mytop工具监控 mysql，类似Linux的top工具一样.

15、MySQL的安全机制

mysql数据库是可以开启安全模式，不过默认情况下，安全模式不开启的。

show variables like 'sql_safe_updates';

set global sql_safe_updates=1;

mysql开启安全策略，想正常更新和删除，需要满足一下两个条件.

delete,update必须带条件，或者加limit进行过滤
所带条件必须走索引

16、主从同步

主库执行完一个事务，写入binlog，我们把这个时刻记为T1；

主库同步数据给从库，从库接收完这个binlog的时刻，记录为T2；

从库执行完这个事务，这个时刻记录为T3。

所谓主从延迟，其实就是指同一个事务，在从库执行完的时间和在主库执行完的时间差值，即T3-T1。

常见主从同步延迟以及解决方案：

1、网络延迟，优化网络

①、我们要确保主从服务器之间的网络连接是高速且稳定的。可以考虑使用专用网络连接或提高网络带宽。

2、从库的压力大，多搞几个从库分散压力

3、数据库参数配置不合理，优化调整

①、调整MySQL数据库中的相关参数，如binlog格式、binlog缓冲区大小。

4、使用半同步复制

5、升级硬件配置

①、升级从服务器的硬件，特别是磁盘I/O系统和CPU，以便更快地应用复制事件。

6、避免大事务

①、可以将大事务分解为多个小事务。

7、使用并行复制

①、通过设置slave_parallel_workers参数，可以指定并行工作线程的数量。

8、业务侧加缓存,优化查询

9、避免大表DDL操作

①、尽量避免在主从复制环境中对大表进行DDL操作，或者选择在业务低峰期进行。

17、分库分表常见问题.

分库分表常见问题

18、深度分页解决.

①、子查询延迟关联

select * from order where id in (
 select id from (
  select id from order where time>'2024-08-11' limit 100000, 10
 ) t
)

②、连表查询

select * from order o1
inner join (
   select id from order 
    where create_time>'2024-08-11' 
    limit 100000,10
) as o2 on o1.id=o2.id;

19、InnoDB Buffer Pool缓存

要想提升查询性能，那就加个缓存。所以，当数据从磁盘中取出后，缓存内存中，下次查询同样的数据的时候，直接从内存中读取。

为此，Innodb 存储引擎设计了一个缓冲池（Buffer Pool），来提高数据库的读写性能。

当读取数据时，如果数据存在于 Buffer Pool 中，客户端就会直接读取 Buffer Pool 中的数据，否则再去磁盘中读取。
当修改数据时，首先是修改 Buffer Pool 中数据所在的页，然后将其页设置为脏页，最后由后台线程将脏页写入到磁盘。

原文链接：https://mp.weixin.qq.com/s/Iaz_6hb3tp-lWDOVsDDGsw

20、InnoDB加锁情况分析

InnoDB的三种锁：

Record Lock（记录锁）：锁住某一行记录

Gap Lock（间隙锁）：锁住一段左开右开的区间

Next-key Lock（临键锁）：锁住一段左开右闭的区间哪些语句上面会加行锁？

1）对于常见的 DML 语句（如 UPDATE 、 DELETE 和 INSERT ），InnoDB 会自动给相应的记录行加写锁

2）默认情况下对于普通 SELECT 语句，InnoDB 不会加任何锁，但是在 Serializable 隔离级别下会加行级读锁上面两种是隐式锁定，InnoDB 也支持通过特定的语句进行显式锁定： 3） SELECT * FROM table_name WHERE ... FOR UPDATE ，加行级写锁

4） SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE ，加行级读锁

记录锁（Record Lock）：锁直接加在索引记录上面。

间隙锁（Gap Lock）：锁加在不存在的空闲空间，可以是两个索引记录之间，也可能是第一个索引记录之前或最后一个索引之后的空间。

Next-Key Lock：记录锁与间隙锁组合起来用就叫做Next-Key Lock。

间隙锁：当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”， InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Gap Lock锁）。举例来说，假如user表中只有101条记录，其empid的值分别是 1,2,…,100,101，下面的SQL：

select * from user where user_id > 100 for update;

是一个范围条件的检索，InnoDB不仅会对符合条件的user_id值为101的记录加锁，也会对user_id大于 101（这些记录并不存在）的“间隙”加锁。产生幻读的原因是，行锁只能锁住行，但是新插入记录这个动作，要更新的是记录之间的“间隙”。因此，为了解决幻读问题，InnoDB 只好引入新的锁，也就是间隙锁 (Gap Lock)。 InnoDB使用间隙锁的目的，一方面是为了防止幻读，以满足相关隔离级别的要求，对于上面的例子，要是不使用间隙锁，如果其他事务插入了user_id大于100的任何记录，那么本事务如果再次执行上述语句，就会发生幻读；

假设有这么一张 user 表:

id 为主键（唯一索引）， a 是普通索引（非唯一索引）， b 都是普通的列，b上没有任何索引：

场景1：唯一索引等值查询

当我们用唯一索引进行等值查询的时候，根据查询的记录是否存在，加锁的规则会有所不同：

规则3：当查询的记录是存在的，Next-key Lock 临键锁会退化成 Record Lock 记录锁。

规则4：当查询的记录是不存在的，Next-key Lock 临键锁会退化 Gap Lock 成间隙锁。

场景1.1、查询的记录存在

先来看个查询的记录存在的案例：

select * from user  where id = 25 for update;

结合加锁的规则1、规则2：

规则1：查找过程中访问到的对象才会加锁

规则2：加锁的基本单位是 Next-key Lock 临键锁（左开右闭）

两条要点, 我们可以分析出，这条语句的加锁范围是临键锁 (20, 25] 不过，按照规则3，由于这个唯一索引等值查询的记录 id = 25 是存在的，因此，Next-key Lock 临键锁会退化成 Record Lock 记录锁，因此最终的加锁范围是 id = 25 这一行

场景1.2、查询的记录不存在

再来看查询的记录不存在的案例：

select * from user  where id = 22 for update;

结合加锁的两条要点（重复一下，表示强调）：规则1：查找过程中访问到的对象才会加锁规则2：加锁的基本单位是 Next-key Lock 临键锁（左开右闭）我们可以分析出，这条语句的加锁范围是 (20, 25] 这里为什么是 (20，25] 而不是 (20, 22] ？因为 id = 22 的记录不存在呀，InnoDB 先找到 id = 20 的记录，发现不匹配，于是继续往下找，发现 id = 25，因此，id = 25 的这一行被扫描到了，所以整体的加锁范围是 (20, 25] 不过，按照规则4，由于这个唯一索引等值查询的记录 id = 22 是不存在的，因此，Next-key Lock 会退化成间隙锁，因此最终在主键 id 上的加锁范围是 Gap Lock (20, 25)

场景 2：唯一索引范围查询

唯一索引范围查询的规则和等值查询的规则一样，

规则3：当查询的记录是存在的，Next-key Lock 会退化成记录锁

规则4：当查询的记录是不存在的，Next-key Lock 会退化成间隙锁只有一个区别，就是唯一索引的范围查询，需要一直向右遍历到第一个不满足条件的记录，相当于增加了一条规则：

规则5：唯一索引的范围查询，一直向右遍历到第一个不满足条件的记录

select * from user  where id >= 20 and id < 22 for update;

先来看语句查询条件的前半部分 id >= 20 ，结合加锁的规则1、规则2：规则1：查找过程中访问到的对象才会加锁规则2：加锁的基本单位是 Next-key Lock 临键锁（左开右闭）这条语句最开始要找的第一行是 id = 20，需要加上 Next-key Lock (15,20] 。根据规则3、规则4：又由于 id 是唯一索引，且 id = 20 的这行记录是存在的，因此会退化成记录锁，也就是只会对 id = 20 这一行加锁。根据规则5：再来看语句查询条件的后半部分 id < 22 ，由于是范围查找，就会继续往后找第一个不满足条件的记录，也就是会找到 id = 25 这一行停下来，然后加 Next-key Lock 临界锁 (20, 25] ，根据规则4：重点来了，但由于 id = 25 不满足 id < 22 ，因此会退化成间隙锁，加锁范围变为 (20, 25) 。所以，上述语句在主键 id 上的最终的加锁范围是 Record Lock id = 20 以及 Gap Lock (20, 25)

场景 3：非唯一索引等值查询

当我们用非唯一索引进行等值查询的时候，根据查询的记录是否存在，加锁的规则会有所不同：规则6：当查询的记录是存在的，除了会加 Next-key Lock 外，还会额外加间隙锁（规则是向下遍历到第一个不符合条件的值才能停止），也就是会加两把锁很好记忆，就是要查找记录的左区间加 Next-key Lock 锁住一段左开右闭的区间，右区间加 Gap lock 锁住一段左开右开的区间规则7：当查询的记录是不存在的，Next-key Lock 会退化成 Gap lock 间隙锁（这个规则和唯一索引的等值查询是一样的）

场景3.1、查询的记录存在先来看个查询的记录存在的案例：

select * from user  where a = 16 for update;

规则1：查找过程中访问到的对象才会加锁规则2：加锁的基本单位是 Next-key Lock 临键锁（左开右闭）这条语句首先会对普通索引 a 加上 Next-key Lock，范围是 (8,16] 根据规则6，又因为是非唯一索引等值查询，且查询的记录 a= 16 是存在的，所以还会加上间隙锁，规则是向下遍历到第一个不符合条件的值才能停止，因此间隙锁的范围是 (16,32) 所以，上述语句在普通索引 a 上的最终加锁范围是 Next-key Lock (8,16] 以及 Gap Lock (16,32)

场景3.2、查询的记录不存在

select * from user  where a = 18 for update;

结合加锁的两条核心，这条语句首先会对普通索引 a 加上 Next-key Lock，范围是 (16,32] 但是由于查询的记录 a = 18 是不存在的，因此 Next-key Lock 会退化为间隙锁，即最终在普通索引 a 上的加锁范围是 (16,32) 。

场景 4：非唯一索引范围查询

回顾一下，唯一索引范围查询，其查询的规则和唯一索引等值查询的规则一样，规则3：当查询的记录是存在的，Next-key Lock 会退化成记录锁规则4：当查询的记录是不存在的，Next-key Lock 会退化成间隙锁规则5：唯一索引的范围查询，一直向右遍历到第一个不满足条件的记录非唯一索引的范围查询和唯一索引范围查询不同的是，规则8：非唯一索引的范围查询并不会退化成 Record Lock 或者 Gap Lock。

select * from user  where a >= 16 and a < 18 for update;

先来看语句查询条件的前半部分 a >= 16 ，因此，这条语句最开始要找的第一行是 a = 16，结合加锁的两个核心，需要加上 Next-key Lock (8,16] 。虽然非唯一索引 a = 16 的这行记录是存在的，但此时并不会像唯一索引那样退化成记录锁。再来看语句查询条件的后半部分 a < 18 ，由于是范围查找，就会继续往后找第一个不满足条件的记录，也就是会找到 id = 32 这一行停下来，然后加 Next-key Lock (16, 32] 。虽然 id = 32 不满足 id < 18 ，但此时并不会向唯一索引那样退化成间隙锁。所以，上述语句在普通索引 a 上的最终的加锁范围是 Next-key Lock (8, 16] 和 (16, 32] ，也就是 (8, 32] 。