MySql优化的一般步骤:1.通过show status 命令了解各种sql的执行效率 SHOW STATUS提供msyql服务器的状态信息 一般情况下,我们只需要了解以Com开头的指令 show session status like ‘Com%’:显示当前的连接的统计结果 show global status like ...
mysql哪个版本比较稳定MySQL的选择要取决于用途的,mysql5.5或者5.7 的版本,网上资源较多mysql的版本如下:1. MySQL Community Server 社区版本,开源免费,但不提供官方技术支持。2. MySQL Enterprise Edition 企业版本,需付费,可以试用30天。3. MySQL C...
工程目录
sql语句
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(32) NOT NULL COMMENT '用户名称',
`birthday` date DEFAULT NULL COMMENT '生日',
`sex` char(1) DEFAULT NULL COMMENT '性别',
`address` varchar(256) DEFAULT NULL COMMENT '地址',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=78 DEFAULT CHARSET=utf8
1
2
3
4
5
6
7
8
jar包
jar
配置文件
applicationContext.xml
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:mvc="http://www.springframework.org/schema/mvc"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:aop="http://www.springframework.org/schema/aop"
xmlns:tx="http://www.springframework.org/schema/tx"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.2.xsd
http://www.springframework.org/schema/mvc
http://www.springframework.org/schema/mvc/spring-mvc-3.2.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-3.2.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop-3.2.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx-3.2.xsd ">
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
db.properties
db.driver = com.mysql.jdbc.Driver
db.url = jdbc:mysql://localhost:3306/ee19_batis_day03?useUnicode=true&characterEncoding=utf-8
db.username = root
db.password = 123
1
2
3
4
log4j.properties
# Global logging configuration
log4j.rootLogger=DEBUG, stdout
# Console output...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n
1
2
3
4
5
6
java文件
entity
package com.entity;
import java.util.Date;
public class User {
private int id;
private String username;
private Date birthday;
private String address;
private String sex;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", birthday=" + birthday +
", address='" + address + '\'' +
", sex='" + sex + '\'' +
'}';
}
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
mapper
package com.mapper;
import com.entity.User;
public interface UserMapper {
public void insertUser(User user);
}
1
2
3
4
5
6
7
8
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
INSERT INTO USER (username,birthday,sex,address) VALUE (#{username}, #{birthday},#{sex},#{address})
1
2
3
4
5
6
7
8
9
10
11
service
package com.service;
import com.entity.User;
public interface UserService {
public void addUser(User user);
}
1
2
3
4
5
6
7
8
package com.service;
import com.entity.User;
import com.mapper.UserMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
@Service
public class UserServiceImpl implements UserService {
@Autowired
UserMapper userMapper;
@Transactional(propagation= Propagation.REQUIRED)
public void addUser(User user) {
userMapper.insertUser(user);
int i = 1 / 0;
}
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
测试文件
import org.junit.runner.RunWith;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.AbstractJUnit4SpringContextTests;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
@ContextConfiguration(locations = { "classpath:resources/applicationContext.xml" })
@RunWith(SpringJUnit4ClassRunner.class)
public class SpringTestCase extends AbstractJUnit4SpringContextTests {
}
1
2
3
4
5
6
7
8
9
10
11
import com.entity.User;
import com.service.UserService;
import org.junit.Test;
import org.springframework.beans.factory.annotation.Autowired;
public class UserServiceTest extends SpringTestCase {
@Autowired
private UserService userService;
@Test
public void addUserTest(){
User user = new User();
user.setUsername("777");
userService.addUser(user);
}
}
MyBatis 的 SqlSession 提供几个方法来在代码中处理事务。但是当使用 MyBatis-Spring 时,你的 bean 将会注入由 Spring 管理的 SqlSession 或映射器。也就是说,Spring 总是为你处理了事务。
你不能在 Spring 管理的 SqlSession 上调用 SqlSession.commit(),SqlSession.rollback() 或 SqlSession.close() 方法。如果这样做了,就会抛出 UnsupportedOperationException 异常。在使用注入的映射器时,这些方法也不会暴露出来。
无论 JDBC 连接是否设置为自动提交,调用 SqlSession 数据方法或在 Spring 事务之外调用任何在映射器中方法,事务都将会自动被提交。
1.spring和mybatis能处理事物的本质是数据库连接Connection对象的commit(提交)和rollback(回滚)方法,其实是mybatils为了继承spring写了mybatis-spring这个包,和spring的事务相结合。
2.@Transactional方法的注册,使用的是spring自己aop方式。
具体配置如下:
在需要配置事务的服务或方法上,添加注解 @Transactional
@Service("userService")
public class UserService {
@Autowired IUserMapper mapper;
@Transactional
public int txUpdateUsersWhenException() {
// 事务性
User user = new User(9, "Before exception");
int affectedCount = mapper.updateUser(user); // 因后面的异常而回滚
User user2 = new User(10, "After exception");
int i = 1 / 0; // 抛出运行时异常,事务回滚
int affectedCount2 = mapper.updateUser(user2); // 未执行
if (affectedCount == 1 && affectedCount2 == 1) {
return 1;
}
return 0;
}
}
springaop如何接管mybatis的事务,或者说spring和mybatis如何保证操作的是同一个Connection
本文重点:
aop的大致流程
mybatis如何把事务托管给spring
mybatis和spring整合的使用(本文看不懂的建议先看看这个)
1.依赖、测试方法同上篇文章
2.开始测试
aop大致流程
//1.项目启动
->spring的refresh方法
->refresh方法里面的finishBeanFactoryInitialization,初始化剩下的单实例,非懒加载bean
->getBean---doGetBean---createBean---doCreateBean
重点在doCreateBean这一步
populateBean(beanName, mbd, instanceWrapper);//属性赋值,上一步的userService是一个空壳,这里面会创建所需的bean,包括dataSource等
exposedObject = initializeBean(beanName, exposedObject, mbd);//将创建出来的userService暴露出去(供我们使用的bean)
->initializeBean,见下一页
->AnnotationAwareAspectJAutoProxyCreator.postProcessAfterInitialization
->需要增强,获取所有的通知,创建代理bean
->重点看事物的通知类
protected Object initializeBean(final String beanName, final Object bean, @Nullable RootBeanDefinition mbd) {
if (System.getSecurityManager() != null) {
AccessController.doPrivileged((PrivilegedAction
invokeAwareMethods(beanName, bean);
return null;
}, getAccessControlContext());
}
else {
//执行实现了 xxxAware接口的方法
invokeAwareMethods(beanName, bean);
}
Object wrappedBean = bean;
if (mbd == null || !mbd.isSynthetic()) {
//执行BeanPostProcessors的beforeInitialization方法,在初始化之前执行(本文重点不在这)
wrappedBean = applyBeanPostProcessorsBeforeInitialization(wrappedBean, beanName);
}
try {
//执行初始化方法,包括init,jsr注解、实现spring的声明周期函数等
invokeInitMethods(beanName, wrappedBean, mbd);
}
catch (Throwable ex) {
throw new BeanCreationException(
(mbd != null ? mbd.getResourceDescription() : null),
beanName, "Invocation of init method failed", ex);
}
if (mbd == null || !mbd.isSynthetic()) {
//执行BeanPostProcessors的afterInitialization方法,在初始化之后执行,本文重点,aop的重点,遍历所有的BeanPostProcessor,循环执行,关注的重点在于
wrappedBean = applyBeanPostProcessorsAfterInitialization(wrappedBean, beanName);
}
return wrappedBean;
}
//前面判断是否需要增强,org.springframework.aop.framework.autoproxy.AbstractAutoProxyCreator
// Create proxy if we have advice.
//获得所有的通知,这个方法未深究,里面是根据名称获取的beanadvisors.add(this.beanFactory.getBean(name, Advisor.class));,最后的结果是org.springframework.transaction.interceptor.BeanFactoryTransactionAttributeSourceAdvisor
Object[] specificInterceptors = getAdvicesAndAdvisorsForBean(bean.getClass(), beanName, null);
if (specificInterceptors != DO_NOT_PROXY) {
this.advisedBeans.put(cacheKey, Boolean.TRUE);
//创建代理对象,不管是jdk还是cglib关注点应该是,specificInterceptors
Object proxy = createProxy(
bean.getClass(), beanName, specificInterceptors, new SingletonTargetSource(bean));
this.proxyTypes.put(cacheKey, proxy.getClass());
return proxy;
}
spring事物通知类
spring事物通知类,这里不熟悉的参考spring原生aop用法
public class BeanFactoryTransactionAttributeSourceAdvisor extends AbstractBeanFactoryPointcutAdvisor {
//这个类有几个实现,看基于注解的 ,@Transactional
//AnnotationTransactionAttributeSource
@Nullable
private TransactionAttributeSource transactionAttributeSource;
private final TransactionAttributeSourcePointcut pointcut = new TransactionAttributeSourcePointcut() {
@Override
@Nullable
protected TransactionAttributeSource getTransactionAttributeSource() {
return transactionAttributeSource;
}
};
/**
* Set the transaction attribute source which is used to find transaction
* attributes. This should usually be identical to the source reference
* set on the transaction interceptor itself.
* @see TransactionInterceptor#setTransactionAttributeSource
*/
public void setTransactionAttributeSource(TransactionAttributeSource transactionAttributeSource) {
this.transactionAttributeSource = transactionAttributeSource;
}
/**
* Set the {@link ClassFilter} to use for this pointcut.
* Default is {@link ClassFilter#TRUE}.
*/
public void setClassFilter(ClassFilter classFilter) {
this.pointcut.setClassFilter(classFilter);
}
@Override
public Pointcut getPointcut() {
return this.pointcut;
}
}
执行带有@Transactional方法的,是TransactionInterceptor里面的
public Object invoke(MethodInvocation invocation) throws Throwable {
// Work out the target class: may be {@code null}.
// The TransactionAttributeSource should be passed the target class
// as well as the method, which may be from an interface.
Class> targetClass = (invocation.getThis() != null ? AopUtils.getTargetClass(invocation.getThis()) : null);
// Adapt to TransactionAspectSupport's invokeWithinTransaction...
//执行带有事物的方法
return invokeWithinTransaction(invocation.getMethod(), targetClass, invocation::proceed);
}
spring管理事物的代码
spring的事务管理代码(以DataSourceTransactionManager为例,只看重点)
@Nullable
protected Object invokeWithinTransaction(Method method, @Nullable Class> targetClass,
final InvocationCallback invocation) throws Throwable {
// If the transaction attribute is null, the method is non-transactional.
TransactionAttributeSource tas = getTransactionAttributeSource();
final TransactionAttribute txAttr = (tas != null ? tas.getTransactionAttribute(method, targetClass) : null);
//这个就是自己配置的事务类型,springboot默认就有一个DataSourceTransactionManager
final TransactionManager tm = determineTransactionManager(txAttr);
if (this.reactiveAdapterRegistry != null && tm instanceof ReactiveTransactionManager) {
ReactiveTransactionSupport txSupport = this.transactionSupportCache.computeIfAbsent(method, key -> {
if (KotlinDetector.isKotlinType(method.getDeclaringClass()) && KotlinDelegate.isSuspend(method)) {
throw new TransactionUsageException(
"Unsupported annotated transaction on suspending function detected: " + method +
". Use TransactionalOperator.transactional extensions instead.");
}
ReactiveAdapter adapter = this.reactiveAdapterRegistry.getAdapter(method.getReturnType());
if (adapter == null) {
throw new IllegalStateException("Cannot apply reactive transaction to non-reactive return type: " +
method.getReturnType());
}
return new ReactiveTransactionSupport(adapter);
});
return txSupport.invokeWithinTransaction(
method, targetClass, invocation, txAttr, (ReactiveTransactionManager) tm);
}
PlatformTransactionManager ptm = asPlatformTransactionManager(tm);
final String joinpointIdentification = methodIdentification(method, targetClass, txAttr);
//一般txAttr不为空,但是ptm是DataSourceTransactionManager,所以这个判断是true
if (txAttr == null || !(ptm instanceof CallbackPreferringPlatformTransactionManager)) {
// Standard transaction demarcation with getTransaction and commit/rollback calls.
//判断是否需要开启事务,这个是重点
TransactionInfo txInfo = createTransactionIfNecessary(ptm, txAttr, joinpointIdentification);
Object retVal;
try {
// This is an around advice: Invoke the next interceptor in the chain.
// This will normally result in a target object being invoked.
//执行业务方法,自己的方法,包括里面访问数据库的访问,这里面如果有多重aop设置也会正常访问
retVal = invocation.proceedWithInvocation();
}
catch (Throwable ex) {
// target invocation exception
//有一场,在里面处理,包括事务回滚
completeTransactionAfterThrowing(txInfo, ex);
throw ex;
}
finally {
//清理本次事务
cleanupTransactionInfo(txInfo);
}
if (vavrPresent && VavrDelegate.isVavrTry(retVal)) {
// Set rollback-only in case of Vavr failure matching our rollback rules...
TransactionStatus status = txInfo.getTransactionStatus();
if (status != null && txAttr != null) {
retVal = VavrDelegate.evaluateTryFailure(retVal, txAttr, status);
}
}
//提交事务
commitTransactionAfterReturning(txInfo);
//返回结果
return retVal;
}
else {
final ThrowableHolder throwableHolder = new ThrowableHolder();
// It's a CallbackPreferringPlatformTransactionManager: pass a TransactionCallback in.
try {
Object result = ((CallbackPreferringPlatformTransactionManager) ptm).execute(txAttr, status -> {
TransactionInfo txInfo = prepareTransactionInfo(ptm, txAttr, joinpointIdentification, status);
try {
Object retVal = invocation.proceedWithInvocation();
if (vavrPresent && VavrDelegate.isVavrTry(retVal)) {
// Set rollback-only in case of Vavr failure matching our rollback rules...
retVal = VavrDelegate.evaluateTryFailure(retVal, txAttr, status);
}
return retVal;
}
catch (Throwable ex) {
if (txAttr.rollbackOn(ex)) {
// A RuntimeException: will lead to a rollback.
if (ex instanceof RuntimeException) {
throw (RuntimeException) ex;
}
else {
throw new ThrowableHolderException(ex);
}
}
else {
// A normal return value: will lead to a commit.
throwableHolder.throwable = ex;
return null;
}
}
finally {
cleanupTransactionInfo(txInfo);
}
});
// Check result state: It might indicate a Throwable to rethrow.
if (throwableHolder.throwable != null) {
throw throwableHolder.throwable;
}
return result;
}
catch (ThrowableHolderException ex) {
throw ex.getCause();
}
catch (TransactionSystemException ex2) {
if (throwableHolder.throwable != null) {
logger.error("Application exception overridden by commit exception", throwableHolder.throwable);
ex2.initApplicationException(throwableHolder.throwable);
}
throw ex2;
}
catch (Throwable ex2) {
if (throwableHolder.throwable != null) {
logger.error("Application exception overridden by commit exception", throwableHolder.throwable);
}
throw ex2;
}
}
}
createTransactionIfNecessary的重点代码
//createTransactionIfNecessary的重点代码
TransactionInfo txInfo = createTransactionIfNecessary(ptm, txAttr, joinpointIdentification);
protected TransactionInfo createTransactionIfNecessary(@Nullable PlatformTransactionManager tm,
@Nullable TransactionAttribute txAttr, final String joinpointIdentification) {
// If no name specified, apply method identification as transaction name.
if (txAttr != null && txAttr.getName() == null) {
txAttr = new DelegatingTransactionAttribute(txAttr) {
@Override
public String getName() {
return joinpointIdentification;
}
};
}
TransactionStatus status = null;
if (txAttr != null) {
if (tm != null) {
//重点在这
/
}
else {
if (logger.isDebugEnabled()) {
logger.debug("Skipping transactional joinpoint [" + joinpointIdentification +
"] because no transaction manager has been configured");
}
}
}
return prepareTransactionInfo(tm, txAttr, joinpointIdentification, status);
}
//status = tm.getTransaction(txAttr);里面的代码
//开始事物
->return startTransaction(def, transaction, debugEnabled, suspendedResources);
->doBegin(transaction, definition);//这里是核心
->里面获取数据库连接connection
->...
->TransactionSynchronizationManager.bindResource(obtainDataSource(), txObject.getConnectionHolder());
->把连接存放在private static final ThreadLocal
new NamedThreadLocal<>("Transactional resources");里面
可以看到,这里有把资源绑定到当前线程
进入自己的业务方法,mybatis执行sql的connection获取和释放
private class SqlSessionInterceptor implements InvocationHandler {
@Override
public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
//获取sqlsession
SqlSession sqlSession = getSqlSession(SqlSessionTemplate.this.sqlSessionFactory,
SqlSessionTemplate.this.executorType, SqlSessionTemplate.this.exceptionTranslator);
try {
//执行sql
Object result = method.invoke(sqlSession, args);
//判断是否开启事务
if (!isSqlSessionTransactional(sqlSession, SqlSessionTemplate.this.sqlSessionFactory)) {
// force commit even on non-dirty sessions because some databases require
// a commit/rollback before calling close()
//没有就提交
sqlSession.commit(true);
}
return result;
} catch (Throwable t) {
Throwable unwrapped = unwrapThrowable(t);
if (SqlSessionTemplate.this.exceptionTranslator != null && unwrapped instanceof PersistenceException) {
// release the connection to avoid a deadlock if the translator is no loaded. See issue #22
//关闭连接
closeSqlSession(sqlSession, SqlSessionTemplate.this.sqlSessionFactory);
sqlSession = null;
Throwable translated = SqlSessionTemplate.this.exceptionTranslator
.translateExceptionIfPossible((PersistenceException) unwrapped);
if (translated != null) {
unwrapped = translated;
}
}
throw unwrapped;
} finally {
if (sqlSession != null) {
closeSqlSession(sqlSession, SqlSessionTemplate.this.sqlSessionFactory);
}
}
}
}
里面两个关键点
getSqlSession
closeSqlSession
如何获取connection
public static SqlSession getSqlSession(SqlSessionFactory sessionFactory, ExecutorType executorType,
PersistenceExceptionTranslator exceptionTranslator) {
notNull(sessionFactory, NO_SQL_SESSION_FACTORY_SPECIFIED);
notNull(executorType, NO_EXECUTOR_TYPE_SPECIFIED);
//从当前线程获取,第一次一般没有
SqlSessionHolder holder = (SqlSessionHolder) TransactionSynchronizationManager.getResource(sessionFactory);
//有的话就获取SqlSession并返回,这里面如果有的话会把计数加一
SqlSession session = sessionHolder(executorType, holder);
if (session != null) {
return session;
}
LOGGER.debug(() -> "Creating a new SqlSession");
//没有就创建sqlsession,这里也是关键点,重点是事物工厂已经是SpringManagedTransactionFactory,这个类是mybatis-spring里面的,本段后面展开
session = sessionFactory.openSession(executorType);
//存储本次的SqlSessionHolder
registerSessionHolder(sessionFactory, executorType, exceptionTranslator, session);
//返回新创建的sqlsession
return session;
}
//关闭sqlsession的方法
public static void closeSqlSession(SqlSession session, SqlSessionFactory sessionFactory) {
notNull(session, NO_SQL_SESSION_SPECIFIED);
notNull(sessionFactory, NO_SQL_SESSION_FACTORY_SPECIFIED);
//当前线程的map里面有的话计数减一
SqlSessionHolder holder = (SqlSessionHolder) TransactionSynchronizationManager.getResource(sessionFactory);
if ((holder != null) && (holder.getSqlSession() == session)) {
LOGGER.debug(() -> "Releasing transactional SqlSession [" + session + "]");
holder.released();
} else {
LOGGER.debug(() -> "Closing non transactional SqlSession [" + session + "]");
//没有的话直接关闭
session.close();
}
}
//后面通过mybatis操作数据库,里面会获取connection连接,
//采用的方法是DataSourceUtil里面的,这个方法保证了连接的connection是同一个,
return doGetConnection(dataSource);
public static Connection doGetConnection(DataSource dataSource) throws SQLException {
Assert.notNull(dataSource, "No DataSource specified");
//前面已经存了
ConnectionHolder conHolder = (ConnectionHolder) TransactionSynchronizationManager.getResource(dataSource);
if (conHolder != null && (conHolder.hasConnection() || conHolder.isSynchronizedWithTransaction())) {
conHolder.requested();
if (!conHolder.hasConnection()) {
logger.debug("Fetching resumed JDBC Connection from DataSource");
conHolder.setConnection(fetchConnection(dataSource));
}
return conHolder.getConnection();
}
// Else we either got no holder or an empty thread-bound holder here.
logger.debug("Fetching JDBC Connection from DataSource");
Connection con = fetchConnection(dataSource);
if (TransactionSynchronizationManager.isSynchronizationActive()) {
try {
// Use same Connection for further JDBC actions within the transaction.
// Thread-bound object will get removed by synchronization at transaction completion.
ConnectionHolder holderToUse = conHolder;
if (holderToUse == null) {
holderToUse = new ConnectionHolder(con);
}
else {
holderToUse.setConnection(con);
}
holderToUse.requested();
TransactionSynchronizationManager.registerSynchronization(
new ConnectionSynchronization(holderToUse, dataSource));
holderToUse.setSynchronizedWithTransaction(true);
if (holderToUse != conHolder) {
TransactionSynchronizationManager.bindResource(dataSource, holderToUse);
}
}
catch (RuntimeException ex) {
// Unexpected exception from external delegation call -> close Connection and rethrow.
releaseConnection(con, dataSource);
throw ex;
}
}
return con;
}
MyBatis 的 SqlSession 提供几个方法来在代码中处理事务。但是当使用 MyBatis-Spring 时,你的 bean 将会注入由 Spring 管理的 SqlSession 或映射器。也就是说,Spring 总是为你处理了事务。
你不能在 Spring 管理的 SqlSession 上调用 SqlSession.commit(),SqlSession.rollback() 或 SqlSession.close() 方法。如果这样做了,就会抛出 UnsupportedOperationException 异常。在使用注入的映射器时,这些方法也不会暴露出来。
无论 JDBC 连接是否设置为自动提交,调用 SqlSession 数据方法或在 Spring 事务之外调用任何在映射器中方法,事务都将会自动被提交。
相关问题推荐
DDL create table 创建表 alter table 修改表 drop table 删除表 truncate table 删除表中所有行 create index 创建索引 drop index 删除索引 当执行DDL语句时,在每一条语句前后,oracle都将提交当前的事...
java开发应用数据库比较主流的有下面这三种: 1.MySQL MySQL是最受欢迎的开源SQL数据库管理系统,它由MySQL AB开发、发布和支持。MySQL AB是一家基于MySQL开发人员的商业公司,它是一家使用了一种成功的商业模式来结合开源价值和方法论的第二代开...
1,DML(DataManipulationLanguage):数据操作语言,用来定义数据库记录(数据)2,DCL(DataControlLanguage):数据控制语言,用来定义访问权限和安全级别;3,DQL(DataQueryLanguage):数据查询语言,用来查询记录(数据);4,DDL(DataDefinitionLang...
数据库三级模式:1、外模式,外模式又称子模式或用户模式,对应于用户级,外模式是从模式导出的一个子集,包含模式中允许特定用户使用的那部分数据。用户可以通过外模式描述语言来描述、定义对应于用户的数据记录(外模式),也可以利用数据操纵语言(Data Manip...
模式、外模式、内莫斯,亦称逻辑模式,是数据库中全体数据的逻辑结构和特征的描述,是所有用户的公共数据视图。模式描述的是数据的全局逻辑结构。 外模式涉及的是数据的局部逻辑结构,通常是模式的子集 内模式,亦称存储模式,是数据库在数据系统内部的表示...
为了避免上面出现的几种情况,在标准SQL规范中,定义了4个事务隔离级别,不同的隔离级别对事务的处理不同。未授权读取(Read Uncommitted):允许脏读取,但不允许更新丢失。如果一个事务已经开始写数据,则另外一个数据则不允许同时进行写操作,但允许其他事...
from是个关键词,表示要从哪个表查询。。
数据库池连接数量一直保持一个不少于最小连接数的数量,当数量不够时,数据库会创建一些连接,直到一个最大连接数,之后连接数据库就会等待。
仅用慢日志文件,如何快速获取分时报告?如果有监控系统,获取分时报告(每小时慢查询的条数报告)不难,如果只有慢日志文件,就会有点费劲。实验:通过 pt-query-digest --timeline 功能,可以输出带时间戳的慢查询条目用 sed 将 timeline 报告滤出安装 term...
MySql优化的一般步骤:1.通过show status 命令了解各种sql的执行效率 SHOW STATUS提供msyql服务器的状态信息 一般情况下,我们只需要了解以Com开头的指令 show session status like ‘Com%’:显示当前的连接的统计结果 show global status like ...
有可能是你输入的密码有问题,如果你的密码字母在前数字在后,那么你输入前面的字母后敲回车再输入数字就可以了,可以试一下
事实上MySQL 能承受的数据量的多少主要和数据表的结构有关,并不是一个固定的数值。表的结构简单,则能承受的数据量相对比结构复杂时大些。据D.V.B 团队以及Cmshelp 团队做CMS 系统评测时的结果来看,MySQL单表大约在2千万条记录(4G)下能够良好运行,经过数...
事实上MySQL 能承受的数据量的多少主要和数据表的结构有关,并不是一个固定的数值。表的结构简单,则能承受的数据量相对比结构复杂时大些。据D.V.B 团队以及Cmshelp 团队做CMS 系统评测时的结果来看,MySQL单表大约在2千万条记录(4G)下能够良好运行,经过数...
mysql哪个版本比较稳定MySQL的选择要取决于用途的,mysql5.5或者5.7 的版本,网上资源较多mysql的版本如下:1. MySQL Community Server 社区版本,开源免费,但不提供官方技术支持。2. MySQL Enterprise Edition 企业版本,需付费,可以试用30天。3. MySQL C...
事实上MySQL 能承受的数据量的多少主要和数据表的结构有关,并不是一个固定的数值。表的结构简单,则能承受的数据量相对比结构复杂时大些。据D.V.B 团队以及Cmshelp 团队做CMS 系统评测时的结果来看,MySQL单表大约在2千万条记录(4G)下能够良好运行,经过数...