Hibernate中不必要的查询-MySql [英] Unnecessary queries in Hibernate - MySql
问题描述
我正在使用 spring/hibernate/mysql ，目前在 spring-hibernate.xml
我经常看到 选择@@ session.tx_read_only" 和 选择@@ session.tx_isolation" 查询通常在针对实际数据的select语句之后发送到数据库.
I am constantly seeing "select @@session.tx_read_only" and "select @@session.tx_isolation" queries being sent to the DB mostly after select statements for actual data.
这些查询中的每一个都会增加20-25毫秒的时间，而在Oauth登录上，我会针对数据库运行70个查询.我该如何摆脱它们?
Each of these queries add like 20-25ms time and I get like 70 queries run against the DB on a Oauth login. How can I get rid of them ?
我尝试了statelessSessions，查询消失了，我只能将查询数量减少到应用程序查询，但是我读到，使用statelessSessions不会提供任何一级缓存，并且也容易受到数据别名效应的影响.
I tried statelessSessions and the queries disappeared and I could reduce the number of queries to the application queries only but I read that using statelessSessions will not provide any first-level cache and its also vulnerable to data aliasing effects.
如何避免多次运行"select @@ session.tx_read_only"和select @@ session.tx_isolation.(我使用通用的Dao访问数据库，下面给出了摘录)我正在使用findById，findAll ，getNamedQueryAndNamedParam方法...
How can I avoid the "select @@session.tx_read_only" and select @@session.tx_isolation" running multiple times.(I use a generic Dao to access the DB a extract is given below) i am using findById, findAll, getNamedQueryAndNamedParam methods...
spring-hibernate.xml
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
<property name="driverClass" value="com.mysql.jdbc.Driver" />
<property name="jdbcUrl" value="${JDBC_CON_STRING}" />
<property name="user" value="${USER_NAME}" />
<property name="password" value="${USER_PASSWORD}" />
</bean>
<bean id="sessionFactory" class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
<property name="dataSource" ref="dataSource" />
<property name="packagesToScan" value="com.model" />
<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">org.hibernate.dialect.MySQLInnoDBDialect</prop>
<prop key="hibernate.show_sql">true</prop>
<prop key="hibernate.format_sql">true</prop>
<prop key="hibernate.hbm2ddl.auto">false</prop>
<prop key="hibernate.cache.region.factory_class">org.hibernate.cache.ehcache.EhCacheRegionFactory</prop>
<prop key="hibernate.cache.use_query_cache">true</prop>
<prop key="hibernate.cache.use_second_level_cache">true</prop>
<prop key="hibernate.cache.provider_configuration_file_resource_path">ehcach.xml</prop>
<prop key="hibernate.auto_close_session">true</prop>
</property>
<property name="mappingResources">
<list>
<value>named-queries.xml</value>
<value>native-named-queries.xml</value>
</list>
</property>
</bean>
<bean id="transactionManager" class="org.springframework.orm.hibernate4.HibernateTransactionManager">
<property name="sessionFactory" ref="sessionFactory" />
<property name="dataSource" ref="dataSource" />
</bean>
<tx:annotation-driven transaction-manager="transactionManager" />
<bean id="testClassDao" class="com.dao.GenericHibernateDao">
<property name="clazz" value="com.model.TestClass" />
</bean>
GenericHibernateDao.java
@Repository
@Scope("prototype")
public class GenericHibernateDao<T, PK extends Serializable> implements GenericDao<T, PK> {
private Class<T> clazz;
@Autowired
private SessionFactory sessionFactory;
public void setClazz(final Class<T> clazzToSet) {
this.clazz = clazzToSet;
}
protected Session getSession() {
return sessionFactory.getCurrentSession();
}
protected Session getOpenSession() {
return sessionFactory.openSession();
}
@Transactional(isolation = Isolation.READ_COMMITTED, propagation = Propagation.REQUIRED)
public T findById(PK id) {
Object obj = null;
obj = getSession().get(clazz, id);
//obj = getStatelessSession().get(clazz, id);
return (T) obj;
}
@Transactional(isolation = Isolation.READ_COMMITTED, propagation = Propagation.REQUIRED)
public List<T> findAll() {
String queryString = "from " + clazz.getName();
Query query = getSession().createQuery(queryString);
query.setCacheable(true);
List<T> list = query.list();
return list;
}
@Transactional(isolation = Isolation.READ_COMMITTED, propagation = Propagation.REQUIRED)
public List<T> getNamedQuery(String queryName) {
Query query = getSession().getNamedQuery(queryName);
//Query query = getStatelessSession().getNamedQuery(queryName);
query.setCacheable(true);
List<T> results = query.list();
return results;
}
@Transactional(isolation = Isolation.READ_COMMITTED, propagation = Propagation.REQUIRED)
public List<T> getNamedQueryAndNamedParam(String queryName, String paramName, Object value) {
Query query = getSession().getNamedQuery(queryName).setString(paramName, value.toString());
query.setCacheable(true);
List<T> results = query.list();
return results;
}
@Transactional(isolation = Isolation.READ_COMMITTED, propagation = Propagation.REQUIRED)
public PK save(T persistenceObject) {
Serializable save = getSession().save(persistenceObject);
return (PK) save;
}
@Transactional(isolation = Isolation.READ_COMMITTED, propagation = Propagation.REQUIRED)
public void saveOrUpdate(T persistenceObject) {
getSession().saveOrUpdate(persistenceObject);
}
public void saveOrUpdateBulk(Collection<T> persistenceObject) {
Session session = getOpenSession();
Transaction tx = session.beginTransaction();
int i = 0;
for (Iterator<T> iterator = persistenceObject.iterator(); iterator.hasNext();) {
i++;
session.saveOrUpdate(iterator.next());
if (i % 100 == 0) {
session.flush();
session.clear();
}
}
tx.commit();
session.close();
}
@Transactional(isolation = Isolation.READ_COMMITTED, propagation = Propagation.REQUIRED)
public boolean delete(PK id) {
Object findById = findById(id);
if (findById != null) {
getSession().delete(findById);
return true;
}
return false;
}
}
推荐答案
AFAIK要删除这些多余的查询，请删除对@Transactional批注的所有修饰符.将隔离级别限制为READ_COMMITED所要付出的代价是，Hibernate将需要执行额外的查询以确定数据库是否处于脏状态.对于90％的情况，这些修饰符是不必要的. Hibernate非常擅长确保您的数据干净，而无需尝试添加这些限制.
AFAIK to remove those extra queries, remove all your modifiers to your @Transactional annotations. The price you pay for restricting your isolation level to READ_COMMITED is that Hibernate will need to perform extra queries to determine if the database is in a dirty state. For 90% of cases, these modifiers are unnecessary. Hibernate is very good at ensuring that your data will be clean without you trying to add these restrictions.
如果您绝对有必要确保隔离为READ_COMMITTED，则您对多余的查询将无能为力.
If it is absolutely necessary for you to ensure that your isolation is READ_COMMITTED, you can't do anything about the extra queries.
仅出于摆脱这些查询的原因而移至StatelessSession是一个坏主意.确实，使用StatelessSession的唯一有效理由是对插入的大批数据进行了插入，而您知道这些数据在插入发生时将不会被读取.
Moving to a StatelessSession just to get rid of those queries is a bad idea for exactly the reason you pointed out. Really, the only valid reason to be using a StatelessSession is for large batch inserts of data that you know won't be read while the insert is occuring.
这篇关于Hibernate中不必要的查询-MySql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
