Mysql学习记录
Mysql学习记录
mysql的基本概念
| Mysql概念 | 功能 |
|---|---|
| 事务 | 事务是数据库完成一件事情的基本操作 |
| 事务的隔离级别 | 处理多个事务并发或交叉执行数据的准确性 |
| 搜索引擎 | 数据库的存储格式 |
Mysql的安装
window 安装:
Linux安装:
- 先通过 wget https://dev.mysql.com/get/mysql57-community-release-el7-9.noarch.rpm 获取yum源,因为centos默认是没有mysql的yum源的
- 安装yum源: rpm -ivh mysql57-community-release-el7-9.noarch.rpm
- 在 /etcyum.repos.d 下安装msql
- yum install -y mysql-server
- 启动Mysql : systemctl start mysqld
- 获取Mysql的临时密码: grep 'temporary password' /var/log/mysqld.log
- 通过 mysql -u root -p 登录mysql
事务
- 事务
| 事务类型 | 解释 |
|---|---|
| 原子性 | 在同一个事务中的所有操作,要么全部完成,要么全部不完成 |
| 一致性 | 事务的开始和结束之后,数据的完整性没有被破坏(如转账) |
| 隔离性 | 多个事务并发的同时对数据进行读写和修改,事务隔离就是防止多个事务(并发/交叉)执行导致数据不一致 |
| 持久性 | 事务处理结束后,对数据的修改是永久的 |
- 事务的隔离级别
| 事务的隔离级别 | 解释 |
|---|---|
| 未提交读(脏读) | 一个事务修改了数据,但没有提交事务,另一个事务select到未被提交的数据 |
| 提交读(不可重复读,幻读) | 第一个事务执行的过程中,第二个事务提交了新数据,第一个事务读取到的数据结果不一致 ; |
| 可重复读(幻读) | 在不同的事务中,事务是独立的 |
| 串行读(锁表) |
Spring对事务的处理
Mysql的存储引擎
| 引擎类型 | 功能 |
|---|---|
| MyISAM | 不支持事务,用了表级锁, |
| InnoDB | 支持事务,行级锁 |
常用的sql优化规则
| 规则 | 能优化的原因 |
|---|---|
| 建立索引 | 在InnoDB中数据都是使用B+树的形式存储的 |
| 连表的时候,小表join大表 | 如果能在条件中先把小表数据先过滤一遍,那么生成的笛卡尔积就会急剧减少 |
| 用join代替in | 在sql中用in是会去全表扫描的,但是如果用join的话,可以碰撞到索引 |
| 最终的目的都是为了减少IO的次数 |
sql的存储过程
创建存储过程
create procedure 函数名( inout 参数名 参数类型)
输入值的时候用in
输出值得时候用out
输入输出的时候用inout
begin
end
配置Mysql的主从模式
- Mysql的文件目录 --> 正常安装都是在 ( C:\Program Files\MySQL\MySQL Server 5.6 )
| 目录 | 目录对应的功能 |
|---|---|
| bin | 放置可执行文件 |
| data | 放置日志文件及数据库 |
| include | 放置C语言的一些头文件 |
| lib | 放置一些.dll库文件 |
| share | 存放字符集,语言之类的信息(包括一些系统的表) |
| my-default.ini | 数据默认配置文件 |
- 主从同步的原理
Master负责处理有添加,修改,删除的事务,并负责实时数据查询(因为从机数据同步的时候有延迟),Salver只做查询
原因 : 主从同步不是实时的,有一定延迟
原理 :
- 1 : Master数据库在执行DML的时候,会把当前的操作记录到binary-log中
- 2 : Master会有一个线程,将 binary-log 中的DML推送到Salver中(通过一些IO操作)
- 3 : Salver 中会将Master中接收到的DML操作保存到 rely-log 中
- 4 : Salver 中会有线程去监听 rely-log 的变化,并且SQL执行线程会去执行这些DML
Master默认是不开启线程保存数据到 binary-log 中
Salver默认是不开启线程监听数据保存到 rely-log 中的
)
- 配置主从同步
初始数据要相同
主机配置
3. 1. 修改 my.ini(一般在C:\ProgramData\MySQL\MySQL Server 5.6) 文件
#配置当前服务器的id, 用于区分不同的数据库
server-id=1
#配置二进制日志文件的名称
log-bin=master-bin
#配置记录哪个数据库的日志,可以配置多次
binlog-do-db=数据库名
3. 2. 重启Mysql服务
3. 3. 查看Master状态
show master status
| 目录 | 目录对应的功能 |
|:---:|:---:|
| file | binlog日志的文件名, .000001 是binlog日志自动会分片处理 |
| Position | 文件指针当前的偏移量 |
| Binlog_Do_DB | 当前Binlog记录的数据库 |
| Binlog_IgnoreDB | 忽略的数据库(默认是没有的) |
**3. 4. 建立从数据库账号**
> **原因:** 在做主从同步的时候,需要登录到主数据库,但有不能直接给root权限
>> 
>> **在设置主机的时候,% 指的是通配 ,一般配置都是 192.168.% (这样的话就内网ip可以访问)**
#刷新权限
flush PRIVILEGES
从机配置
3. 5. 在Linux中修改my.conf文件(默认是在 /etc 下)
#配置服务器的id,必须是唯一的
server-id=3
#回放哪个数据库日志
replicate-do-db=数据库名
3. 6. 设置主数据库的信息
#配置从数据库连接主数据库的信息
change master to
master_host='192.168.247.2', #主机的地址
master_user='salve', #登录主机的账户
master_password='123456', #登录主机账户的密码
master_log_file='master-bin.000001', #主机binary-log的名称
master_log_pos=764 ; #注意这里是填数字,不能填字符串,填字符串会报错
#停止主从同步
stop slave;
#开启主从同步
start slave;
#查询从数据库是否配置成功
show slave status;
在配置过程可能可能遇到的问题
>1. 如果是Linux系统的话,记得关闭防火墙
>2. 修改数据主库中的账户登录的权限,开放到其他电脑都可以访问到
>> 方式一: GRANT ALL PRIVILEGES ON . TO 用户名 @'%' IDENTIFIED BY "密码";
>> 方式二: 查看 3. 4
4. JAVA实现读写分离
4. 1. 读写分离原理:
> 一个连接池只能操作一个数据库,如果有多个数据库,我们就必须为每一个数据库配置对应的连接池
> 在执行sql语句的时候,DML必选选择MasterDatesource来执行,DQL可以选择SlaveDateSource执行
> 需要有一个路由功能的对象管理所有的连接池和切换连接池
4. 2. 实现步骤
如下图所示,我们需要四个类来实现读写分离的基本配置
|类名|功能|
|:----:|:----:|
| DataSourceTargetType | 一个枚举类,管理主从连接池的key |
| DataSourceContextHolder | 通过ThreadLocal设置key来设定当前线程使用哪个连接池 |
| DataSourcesConfig | 创建主从连接池,和创建路由(管理主从连接池,设置默认连接池)连接池 |
| RoutingDataSource | 继承抽象类 AbstractRoutingDataSource |
)
DataSourceTargetType:
public enum DataSourceTargetType {
MASTER, SLAVE
}
DataSourceContextHolder:
/**
* 通过本地线程管理当前连接池
*/
public class DataSourceContextHolder {
private static ThreadLocal<DataSourceTargetType> local = new ThreadLocal();
public static DataSourceTargetType get() {
return local.get();
}
public static void set(DataSourceTargetType targetType) {
local.set(targetType);
}
/**
* 切换到主库
*/
public static void master() {
if (local.get() == null) {
set(DataSourceTargetType.MASTER);
}
}
/**
* 切换到从库
*/
public static void slave() {
if (local.get() == null) {
set(DataSourceTargetType.SLAVE);
}
}
}
DataSourcesConfig:
@Configuration
public class DataSourcesConfig {
@Bean("masterDataSource")
@ConfigurationProperties(prefix = "master")
public DataSource masterDataSource(){
return DataSourceBuilder.create().type(DruidDataSource.class).build();
}
@Bean("slaveDataSource")
@ConfigurationProperties(prefix = "slave")
public DataSource slaveDataSource(){
return DataSourceBuilder.create().type(DruidDataSource.class).build();
}
@Bean("routingDataSource")
public DataSource routingDataSource(@Qualifier("masterDataSource") DataSource masterDataSource, @Qualifier("slaveDataSource")DataSource slaveDataSource){
Map<Object,Object> dataSources = new HashMap<Object,Object>();
dataSources.put(DataSourceTargetType.MASTER,masterDataSource);
dataSources.put(DataSourceTargetType.SLAVE,slaveDataSource);
RoutingDataSource routingDataSource = new RoutingDataSource();
routingDataSource.setTargetDataSources(dataSources);
routingDataSource.setDefaultTargetDataSource(masterDataSource);
return routingDataSource;
}
}
RoutingDataSource:
/**
* 功能: 获取当前使用的数据库key
*/
public class RoutingDataSource extends AbstractRoutingDataSource {
@Override
protected Object determineCurrentLookupKey() {
return DataSourceContextHolder.get();
}
}
使用MyBatis配置主从读写分离
1. 需要配置MyBatis的连接池改成 RoutingDataSource
@Configuration
@EnableTransactionManagement
@MapperScan("包的路径") //扫描 mapper包下的xml文件为接口做映射
public class MyBatisConfiguration {
@Resource(name = "routingDataSource")
private DataSource myRoutingDataSource;
@Bean("sqlSessionFactory")
public SqlSessionFactory sqlSessionFactory() throws Exception {
MybatisSqlSessionFactoryBean sqlSessionFactory = new MybatisSqlSessionFactoryBean();
sqlSessionFactory.setDataSource(myRoutingDataSource);
sqlSessionFactory.setTypeAliasesPackage("实体类所在的包"); //为表的实体做别名,这样就可以不用全限定名了
MybatisConfiguration configuration = new MybatisConfiguration();
configuration.setJdbcTypeForNull(JdbcType.NULL);
configuration.setMapUnderscoreToCamelCase(true);
configuration.setLogImpl(StdOutImpl.class);
configuration.setCacheEnabled(false);
configuration.setMapUnderscoreToCamelCase(true);
sqlSessionFactory.setConfiguration(configuration);
// sqlSessionFactory.setObjectWrapperFactory(new MapWrapperFactory());
//添加分页功能和SQL格式化打印功能
/*sqlSessionFactory.setPlugins(new Interceptor[]{
performanceInterceptor(),
paginationInterceptor(),
mybatisSqlInterceptor()
});*/
return sqlSessionFactory.getObject();
}
}
2. 利用AOP前置增强对 Service 进行数据库选择
@Order(1)
@Aspect
@Configuration
public class DataSourcesAOP {
//这里的切面表达式要修改成serviceImpl的
@Around(value = "execution (* cn.chuloo..service.impl.*.*(..))")
public Object dataSourceChangeProcess(ProceedingJoinPoint joinPoint) throws Throwable {
DataSourceTargetAnnotation dataSourceAnnotation = AopUtils.getDataSourceAnnotation(joinPoint);
if(dataSourceAnnotation != null && dataSourceAnnotation.value() == DataSourceTargetType.MASTER) {
DataSourceContextHolder.master();
}else {
DataSourceContextHolder.slave();
}
return joinPoint.proceed();
}
}
3. 使用到的注解:
@Target(ElementType.METHOD) //这个注解所能贴的位置
@Retention(RetentionPolicy.RUNTIME) //在运行时生效,(有一些注解不是在运行时生效的,如:lombok的)
public @interface DataSourceTargetAnnotation {
DataSourceTargetType value() default DataSourceTargetType.SLAVE;
}
4. 使用到的AOP工具类
public class AopUtils {
public static DataSourceTargetAnnotation getDataSourceAnnotation(ProceedingJoinPoint joinPoint) {
DataSourceTargetAnnotation dataSourceAnnotation = null;
Method methodsrc = ((MethodSignature)joinPoint.getSignature()).getMethod();
dataSourceAnnotation = methodsrc.getAnnotation(DataSourceTargetAnnotation.class);
return dataSourceAnnotation;
}
}
浙公网安备 33010602011771号