mysql开发参考

Z先生09
73 阅读14分钟

mysql开发参考

基础

pom

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

数据库连接池

数据库连接池负责分配、管理和释放数据库连接，它允许应用程序重复使用一个现有的数据库连接，而不是再重新建立一个

常见连接池

DBCP

被淘汰：依赖Commons-Pool，性能差

C3P0

被淘汰：历史悠久，过于复杂，性能差

BoneCP

被淘汰：为解决C3P0/DBCP性能而生，后续出现了更高性能的hikariCP，BoneCP也不再更新

Druid

Druid连接池为监控而生，内置强大的监控功能，监控特性不影响性能。功能强大，能防SQL注入，内置Loging能诊断Hack应用行为

但是（仅从连接池的角度）在生态，维护性，开源规范性，综合性能等方面和HikariCP比还是有很大差距

  • pom

    <dependency>    
	<groupId>com.alibaba</groupId>    
	<artifactId>druid-spring-boot-starter</artifactId>
</dependency>

  • 功能

    • 监控信息采集的StatFilter。监控信息主要是通过StatFilter 采集的，采集的信息非常全面，包括SQL执行、并发、慢查、执行时间区间分布等
    • 诊断支持。内置了LogFilter，将Connection/Statement/ResultSet相关操作的日志输出
    • 防SQL注入。SQL注入攻击是黑客对数据库进行攻击的常用手段，Druid连接池内置了WallFilter 提供防SQL注入功能，在不影响性能的同时防御SQL注入攻击

HikariCP

  • Spring 默认的连接池
  • 优势
    • 字节码精简 ：优化代码，直到编译后的字节码最少，这样，CPU缓存可以加载更多的程序代码
    • 优化代理和拦截器：减少代码，例如HikariCP的Statement proxy只有100行代码，只有BoneCP的十分之一
    • 自定义数组类型（FastStatementList）代替ArrayList：避免每次get()调用都要进行range check，避免调用remove()时的从头到尾的扫描
    • 自定义集合类型（ConcurrentBag)：提高并发读写的效率
    • 针对BoneCP缺陷的优化，比如对于耗时超过一个CPU时间片的方法调用的研究等

JPA

是全自动的ORM框架，用于应用与数据库直接的数据交互处理

pom

<dependency>    
	<groupId>org.springframework.boot</groupId>    
	<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

辅助插件

<dependency>    
	<groupId>com.github.wenhao</groupId>    
	<artifactId>jpa-spec</artifactId>
</dependency>

MyBatis

Mybatis是半自动ORM 映射工具，因为在查询关联对象或关联集合对象时，需要手动编写 sql 来完成

支持主键自动生成，支持4 种主键策略（内含分布式唯一 ID 生成器 - Sequence）

内置全局拦截插件，提供全表 delete 、 update 操作智能分析阻断，也可自定义拦截规则，预防误操作

支持Xml和注解混合模式。但对于复杂SQL使用注解极为不便

功能架构分层模型

  • API接口层
    • 提供给外部使用的接口API，开发人员通过这些本地API来操纵数据库。接口层一接收到调用请求，就会调用数据处理层来完成具体的数据处理
  • 数据处理层
    • 负责具体的SQL查找、SQL解析、SQL执行和执行结果映射处理等。它主要的目的是根据调用的请求完成一次数据库操作
  • 基础支撑层
    • 负责最基础的功能支撑，包括连接管理、事务管理、配置加载和缓存处理，这些都是共用的东西，将他们抽取出来作为最基础的组件。为上层的数据处理层提供最基础的支撑

pom

<dependency>    
	<groupId>org.mybatis.spring.boot</groupId>    
	<artifactId>mybatis-spring-boot-starter</artifactId>
</dependency>

常用注解

  • @Results。与Xml配置中的resultMap等价

  • @Result。与Xml配置中的result等价

  • @Select。与Xml配置中的select等价

  • @Param。指定参数的别名

  • @ResultMap。指定返回结果集的映射，等价于xml中的resultMap=xxx

  • @Many与@One。关联查询，前者是多对多查询，后者是一对一查询

  • @Insert。与Xml配置中的insert等价

  • @Options。指定新增操作时的主键处理细节。示例：@Options(useGeneratedKeys = true, keyProperty = "id")

  • @SelectKey。自定义主键生成。示例：@SelectKey(statement = "SELECT LAST_INSERT_ID()", keyColumn = "id", keyProperty = "id", resultType = Long.class, before = false)

  • @Update。与Xml配置中的update等价

  • @Delete。与Xml配置中的delete等价

增强注解（Provider）

  • @SelectProvider。示例：@SelectProvider(type=Test.class, method="simpleSql")

  • @InsertProvider

  • @UpdateProvider

  • @DeleteProvider

  • 构建Sql示例

    public class Test{ 
	public String simpleSql(id) {   
		SQL sql = new SQL();   
		sql.SELECT("u.id, u.password, u.create_time, u.update_time"); 		  sql.FROM("tb_user u");    
		sql.WHERE("id = " + id);
	}
}

辅助工具

  • CodeGenerator

MyBatis-Plus

简述

  • 是一个 MyBatis的增强工具，在 MyBatis 的基础上只做增强不做改变，为简化开发、提高效率而生
  • MyBatis需要独立引入PageHelper分页插件，MyBatis-Plus支持了内置分页插件，同PageHelper一样基于 MyBatis 物理分页
  • 支持了内置代码生成器，采用代码或者 Maven 插件，可快速生成 Mapper 、 Model 、 Service 、 Controller 层代码，支持模板引擎
  • 内置性能分析插件, 可输出 SQL 语句以及其执行时间，建议开发测试时启用该功能，能快速揪出慢查询
  • 支持标准 SQL 的数据库。如：MySQL，Oracle，DB2，H2，HSQL，SQLite，PostgreSQL，SQLServer，Phoenix，Gauss ，ClickHouse，Sybase，OceanBase，Firebird，Cubrid，Goldilocks，csiidb

pom

<dependency>    
	<groupId>com.baomidou</groupId>    
	<artifactId>mybatis-plus-boot-starter</artifactId>    				  	  <version>3.5.1</version>
</dependency>

应用示例

  • Mapper层：继承BaseMapper
  • Service层：继承ServiceImpl并实现对应接口
  • Lambda函数式查询
  • 分页采用内置MybatisPlusInterceptor
  • 增强了对于复杂的关联查询的支持

插件

  • 代码自动生成：mybatis-plus-generator
  • 自动分页: PaginationInnerInterceptor
  • 多租户: TenantLineInnerInterceptor
  • 动态表名: DynamicTableNameInnerInterceptor
  • 乐观锁: OptimisticLockerInnerInterceptor
  • sql 性能规范: IllegalSQLInnerInterceptor
  • 防止全表更新与删除: BlockAttackInnerInterceptor
  • 插件使用注意事项
    • 对 sql 进行单次改造的优先放入,不对 sql 进行改造的最后放入
    • 顺序

插件使用与注意事项

  • 对 sql 进行单次改造的优先放入,不对 sql 进行改造的最后放入
  • 顺序
    • 多租户,动态表名
    • 分页,乐观锁
    • sql 性能规范,防止全表更新与删除

数据分页

方式

逻辑分页

  • 从数据库中查询出所有符合条件的数据，在内存中进行分页，然后获取查询页的数据
  • 逻辑分页只访问一次数据库，一次性将数据读取到内存，占用了较大的内容空间
  • 逻辑分页主要用于数据量不大、数据稳定的场合

物理分页

  • 从数据库中查询出需要的数据，例如在mysql中使用limit进行指定查询
  • 物理分页每次都访问数据库，每次只读取一部分数据，占用内存空间较小
  • 物理分页主要用于数据量较大、更新频繁的场合

分页插件

PageHelper

底层使用的是物理分页

pom
<dependency>    
	<groupId>com.github.pagehelper</groupId>    
	<artifactId>pagehelper-spring-boot-starter</artifactId>
</dependency>
语法
  • PageHelper.startPage(pageNum, pageSize, orderBy)
  • 方法后的第一个select是具备分页能力的
示例
PageHelper.startPage(1, 10);
testMapper.selectList();

PageHelper.offsetPage(1, 10);
testMapper.selectList();

PageHelper.startPage(1,10)
.doSelectPage(()->testMapper.selectList());

PageHelper.startPage(1,10)
.doSelectPageInfo(()->testMapper.selectList());
注意事项
  • 只有紧跟在PageHelper.startPage方法后的第一个Mybatis的查询（Select）方法会被分页
  • 不要配置多个分页插件
  • 分页插件不支持带有for update语句的分页
  • 分页插件不支持嵌套结果映射

RowBound

底层使用的是逻辑分页

Mybatis默认分页

示例
sqlSession.selectList("", null, new RowBounds(0, 10))

MyBatis-plus分页插件

底层使用了物理分页

多数据源/动态数据源

操作流程分析

  • 一条语句的执行一般要经过以下流程：DAO、Mapper、JDBC、代理、数据库。那么就可以从这几个方面入手来解决多数据源或者动态数据源的问题

应用场景

  • 主库和从库（读写分离）
  • 数据库分片
  • 多租户隔离
  • 不同的业务涉及的表位于不同的数据库

解决方案

DAO层的解决方案

  • 基于AOP切面拦截的方式，可以在获取连接的时候进行拦截扩展，根据配置判断需要连接到哪个数据源，再建立连接，可以保证执行的sql语句是在正确的数据库节点服务器上
  • 例如Spring中提供了一个抽象类AbstractRoutingDataSource，实现方法determineCurrentLookupKey()即可做到数据源的动态切换

ORM框架层

  • 如项目中用到的MyBatis框架，里面有插件的拦截机制。我们可以基于插件来拦截query方法和update方法，实现数据源的切换
  • 例如拦截Mybatis中的StatementHandler中的query方法对数据源进行切换

驱动层

  • 常见的ORM框架本质上都是对JDBC的封装，而JDBC中两个核心对象，一个是Connection，一个是DataSource。我们可以自己实现一个DataSource，在项目中配置多个数据源，这样在使用时就可以自己选择dataSource来实现数据源的切换

代理层

  • 使用数据库访问中间件，由中间件来管理多个数据源，项目工厂直接访问中间件来对数据库进行操作，这样就不用考虑多数据源的问题了，只需要关系中间件配置即可。例如MyCat中间件

数据库服务

  • 服务器分片技术。如Redis的RedisCluster分片，可以实现多节点自动路由

数据库多租户

多租户技术（英语：multi-tenancy technology）或称多重租赁技术，是一种软件架构技术，它是在探讨与实现如何于多用户的环境下共用相同的系统或程序组件，并且仍可确保各用户间数据的隔离性

数据隔离方案

DB隔离（独立数据库）

  • 即一个租户一个数据库，这种方案的用户数据隔离级别最高，安全性最好，但成本也高

Schema隔离（共享数据库，隔离数据架构）

  • 即多个或所有租户共享Database，但一个租户（Tenant）一个Schema

字段隔离（共享数据库，共享数据架构）

  • 即租户共享同一个Database、同一个Schema，但在表中通过TenantID区分租户的数据。这是共享程度最高、隔离级别最低的模式

ShardingSphere

分库分表

简述

  • ShardingSphere定位为关系型数据库中间件，旨在充分合理地在分布式的场景下利用关系型数据库的计算和存储能力，而并非实现一个全新的关系型数据库
  • 提供标准化的数据分片、分布式事务和数据库治理功能，可适用于如Java同构、异构语言、容器、云原生等各种多样化的应用场景
  • 水平分片/垂直分片后的每个数据节点的数据内容却并不相同

子产品

  • Sharding-JDBC
  • Sharding-Proxy
  • Sharding-Sidecar

pom

<dependency>    
	<groupId>org.apache.shardingsphere</groupId>    
	<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
</dependency>

注意事项

  • 数据分配后，取消数据库自动增长ID，以及ORM层配置的ID生成策略均不能再使用，改为由ShardingSphere通过指定key-generator来生成ID

读写分离

  • 将数据库拆分为主库和从库，主库负责处理事务性的增删改操作，从库负责处理查询操作，能够有效的避免由数据更新导致的行锁，使得整个系统的查询性能得到极大的改善
  • 读写分离的数据节点中的数据内容是一致的

基础概念

逻辑表

  • 水平拆分的数据库（表）的相同的逻辑和数据结构表的总称
  • 例如：tb_order_0...n的逻辑表名是tb_order

真实表

  • 在分片的数据库中真实存在的物理表
  • 例如：tb_order_0...n的各表

数据节点

  • 数据分片的最小单元，由数据源名称和数据表组成
  • 例如：db_0.tb_order_0

绑定表

  • 指分片规则一致的主表和子表。例如：tb_order表与tb_order_item表，分片规则相同，则此两张表互为绑定表关系
  • 绑定表之间的多表关联查询不会出现笛卡尔积关联，关联查询效率将大大提升

广播表

  • 指所有的分片数据源中都存在的表，表结构和表中的数据在每个数据库中均完全一致。例如：字典表
  • 适用于数据量不大且需要与海量数据的表进行关联查询的场景

分片

分片键

  • 用于分片的数据库字段，是将数据库(表)水平拆分的关键字段
  • 例如：将订单表中的订单主键的尾数取模分片，则订单主键为分片字段。 SQL中如果无分片字段，将执行全路由，性能较差。 除了对单分片字段的支持，ShardingSphere也支持根据多个字段进行分片

分片算法

简述
  • 通过分片算法将数据分片，支持通过=、>=、<=、>、<、BETWEEN和IN分片。分片算法需要应用方开发者自行实现，可实现的灵活度非常高
  • 由于分片算法和业务实现紧密相关，因此并未提供内置分片算法，而是通过分片策略将各种场景提炼出来，提供更高层级的抽象，并提供接口让应用开发者自行实现分片算法
分类
  • 精确分片算法
    • 对应PreciseShardingAlgorithm，用于处理使用单一键作为分片键的=与IN进行分片的场景。需要配合StandardShardingStrategy使用
  • 范围分片算法
    • 对应RangeShardingAlgorithm，用于处理使用单一键作为分片键的BETWEEN AND、>、<、>=、<=进行分片的场景。需要配合StandardShardingStrategy使用
  • 复合分片算法
    • 对应ComplexKeysShardingAlgorithm，用于处理使用多键作为分片键进行分片的场景，包含多个分片键的逻辑较复杂，需要应用开发者自行处理其中的复杂度。需要配合ComplexShardingStrategy使用
  • Hint分片算法
    • 对应HintShardingAlgorithm，用于处理使用Hint行分片的场景。需要配合HintShardingStrategy使用

分片策略

包含分片键和分片算法，由于分片算法的独立性，将其独立抽离。真正可用于分片操作的是分片键 + 分片算法，也就是分片策略。目前提供5种分片策略

分类
  • 标准分片策略
    • 对应StandardShardingStrategy。提供对SQL语句中的=, >, <, >=, <=, IN和BETWEEN AND的分片操作支持。StandardShardingStrategy只支持单分片键，提供PreciseShardingAlgorithm和RangeShardingAlgorithm两个分片算法。PreciseShardingAlgorithm是必选的，用于处理=和IN的分片。RangeShardingAlgorithm是可选的，用于处理BETWEEN AND, >, <, >=, <=分片。如果不配置RangeShardingAlgorithm，SQL中的BETWEEN AND将按照全库路由处理
  • 复合分片策略
    • 对应ComplexShardingStrategy。复合分片策略。提供对SQL语句中的=, >, <, >=, <=, IN和BETWEEN AND的分片操作支持。ComplexShardingStrategy支持多分片键，由于多分片键之间的关系复杂，因此并未进行过多的封装，而是直接将分片键值组合以及分片操作符透传至分片算法，完全由应用开发者实现，提供最大的灵活度
  • 行表达式分片策略
    • 对应InlineShardingStrategy。使用Groovy的表达式，提供对SQL语句中的=和IN的分片操作支持，只支持单分片键。对于简单的分片算法，可以通过简单的配置使用，从而避免繁琐的Java代码开发
  • Hint分片策略
    • 对应HintShardingStrategy。通过Hint指定分片值而非从SQL中提取分片值的方式进行分片的策略
  • 不分片策略
    • 对应NoneShardingStrategy。不分片的策略

数据库管理工具

Liquibase

简述

  • 用于用于跟踪、管理和应用数据库变化的开源工具
  • 通过日志文件(changelog)的形式记录数据库的变更(changeset)，然后执行日志文件中的修改，将数据库更新或回滚(rollback)到一致的状态
  • 它的目标是提供一种数据库类型无关的解决方案，通过执行schema类型的文件来达到迁移

pom

<dependency>    
	<groupId>org.liquibase</groupId>    
	<artifactId>liquibase-core</artifactId>
</dependency>

Flyway

  • Flyway是一款数据库迁移（migration）工具。简单点说，就是在你部署应用的时候，帮你执行数据库脚本的工具。Flyway支持SQL和Java两种类型的脚本，你可以将脚本打包到应用程序中，在应用程序启动时，由Flyway来管理这些脚本的执行，这些脚本被Flyway称之为migration
avatar
文章
阅读
粉丝