Есть ли более эффективный способ создания разбиения на страницы в спящем режиме, чем выполнение запросов выбора и подсчета?

Ответ 1

Барон Шварц в MySQLPerformanceBlog.com написал сообщение об этом. Мне жаль, что для этой проблемы не было волшебной пули, но ее нет. Резюме вариантов, которые он представил:

• В первом запросе извлекаются и кэшируются все результаты.
• Не показывать все результаты.
• Не показывать общее количество или промежуточные ссылки на другие страницы. Показывать только "следующую" ссылку.
• Оцените количество результатов.

Ответ 2

Мое решение будет работать для очень распространенного варианта использования Hibernate + Spring + MySQL

Как и в предыдущем ответе, я основывал свое решение на д-ра Ричарда Кеннара. Однако, поскольку Hibernate часто используется с Spring, я хотел, чтобы мое решение отлично работало с Spring и стандартным методом для использования Hibernate. Поэтому для достижения результата мое решение использует комбинацию локалей потоков и singleton beans. Технически перехватчик вызывается для каждого подготовленного оператора SQL для SessionFactory, но он пропускает всю логику и не инициализирует ни один ThreadLocal (s), если только это не задано специально для подсчета полных строк.

Используя следующий класс, ваша конфигурация Spring выглядит следующим образом:

<bean id="foundRowCalculator" class="my.hibernate.classes.MySQLCalcFoundRowsInterceptor" />
    <!-- p:sessionFactoryBeanName="mySessionFactory"/ -->

<bean id="mySessionFactory"
    class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean"
    p:dataSource-ref="dataSource"
    p:packagesToScan="my.hibernate.classes"
    p:entityInterceptor-ref="foundRowCalculator"/>

В принципе, вы должны объявить перехватчик bean, а затем ссылаться на него в свойстве "entityInterceptor" SessionFactoryBean. Вы должны установить "sessionFactoryBeanName", если в вашем контексте Spring есть более одного SessionFactory, а сеанс factory, который вы хотите ссылаться, не называется "sessionFactory". Причина, по которой вы не можете установить ссылку, заключается в том, что это приведет к взаимозависимости между beans, которая не может быть разрешена.

Использование обертки bean для результата:

package my.hibernate.classes;

public class PagedResponse<T> {
    public final List<T> items;
    public final int total;
    public PagedResponse(List<T> items, int total) {
        this.items = items;
        this.total = total;
    }
}

Затем, используя абстрактный базовый класс DAO, вы должны вызывать "setCalcFoundRows (true)" перед выполнением запроса и "reset()" после [в блоке finally, чтобы убедиться, что он вызван]:

package my.hibernate.classes;

import org.hibernate.Criteria;
import org.hibernate.Query;
import org.springframework.beans.factory.annotation.Autowired;

public abstract class BaseDAO {

    @Autowired
    private MySQLCalcFoundRowsInterceptor rowCounter;

    public <T> PagedResponse<T> getPagedResponse(Criteria crit, int firstResult, int maxResults) {
        rowCounter.setCalcFoundRows(true);
        try {
            @SuppressWarnings("unchecked")
            return new PagedResponse<T>(
                crit.
                setFirstResult(firstResult).
                setMaxResults(maxResults).
                list(),
                rowCounter.getFoundRows());
        } finally {
            rowCounter.reset();
        }
    }

    public <T> PagedResponse<T> getPagedResponse(Query query, int firstResult, int maxResults) {
        rowCounter.setCalcFoundRows(true);
        try {
            @SuppressWarnings("unchecked")
            return new PagedResponse<T>(
                query.
                setFirstResult(firstResult).
                setMaxResults(maxResults).
                list(),
                rowCounter.getFoundRows());
        } finally {
            rowCounter.reset();
        }
    }
}

Затем конкретный пример класса DAO для @Entity с именем MyEntity со строковым свойством "prop":

package my.hibernate.classes;

import org.hibernate.SessionFactory;
import org.hibernate.criterion.Restrictions
import org.springframework.beans.factory.annotation.Autowired;

public class MyEntityDAO extends BaseDAO {

    @Autowired
    private SessionFactory sessionFactory;

    public PagedResponse<MyEntity> getPagedEntitiesWithPropertyValue(String propVal, int firstResult, int maxResults) {
        return getPagedResponse(
            sessionFactory.
            getCurrentSession().
            createCriteria(MyEntity.class).
            add(Restrictions.eq("prop", propVal)),
            firstResult, 
            maxResults);
    }
}

Наконец, класс перехватчика, который выполняет всю работу:

package my.hibernate.classes;

import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.hibernate.EmptyInterceptor;
import org.hibernate.HibernateException;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.jdbc.Work;
import org.springframework.beans.BeansException;
import org.springframework.beans.factory.BeanFactory;
import org.springframework.beans.factory.BeanFactoryAware;

public class MySQLCalcFoundRowsInterceptor extends EmptyInterceptor implements BeanFactoryAware {



    /**
     * 
     */
    private static final long serialVersionUID = 2745492452467374139L;

    //
    // Private statics
    //

    private final static String SELECT_PREFIX = "select ";

    private final static String CALC_FOUND_ROWS_HINT = "SQL_CALC_FOUND_ROWS ";

    private final static String SELECT_FOUND_ROWS = "select FOUND_ROWS()";

    //
    // Private members
    //
    private SessionFactory sessionFactory;

    private BeanFactory beanFactory;

    private String sessionFactoryBeanName;

    private ThreadLocal<Boolean> mCalcFoundRows = new ThreadLocal<Boolean>();

    private ThreadLocal<Integer> mSQLStatementsPrepared = new ThreadLocal<Integer>() {
        @Override
        protected Integer initialValue() {
            return Integer.valueOf(0);
        }
    };

    private ThreadLocal<Integer> mFoundRows = new ThreadLocal<Integer>();



    private void init() {
        if (sessionFactory == null) {
            if (sessionFactoryBeanName != null) {
                sessionFactory = beanFactory.getBean(sessionFactoryBeanName, SessionFactory.class);
            } else {
                try {
                    sessionFactory = beanFactory.getBean("sessionFactory", SessionFactory.class);
                } catch (RuntimeException exp) {

                }
                if (sessionFactory == null) {
                    sessionFactory = beanFactory.getBean(SessionFactory.class); 
                }
            }
        }
    }

    @Override
    public String onPrepareStatement(String sql) {
        if (mCalcFoundRows.get() == null || !mCalcFoundRows.get().booleanValue()) {
            return sql;
        }
        switch (mSQLStatementsPrepared.get()) {

        case 0: {
            mSQLStatementsPrepared.set(mSQLStatementsPrepared.get() + 1);

            // First time, prefix CALC_FOUND_ROWS_HINT

            StringBuilder builder = new StringBuilder(sql);
            int indexOf = builder.indexOf(SELECT_PREFIX);

            if (indexOf == -1) {
                throw new HibernateException("First SQL statement did not contain '" + SELECT_PREFIX + "'");
            }

            builder.insert(indexOf + SELECT_PREFIX.length(), CALC_FOUND_ROWS_HINT);
            return builder.toString();
        }

        case 1: {
            mSQLStatementsPrepared.set(mSQLStatementsPrepared.get() + 1);

            // Before any secondary selects, capture FOUND_ROWS. If no secondary
            // selects are
            // ever executed, getFoundRows() will capture FOUND_ROWS
            // just-in-time when called
            // directly

            captureFoundRows();
            return sql;
        }

        default:
            // Pass-through untouched
            return sql;
        }
    }

    public void reset() {
        if (mCalcFoundRows.get() != null && mCalcFoundRows.get().booleanValue()) {
            mSQLStatementsPrepared.remove();
            mFoundRows.remove();
            mCalcFoundRows.remove();
        }
    }

    @Override
    public void afterTransactionCompletion(Transaction tx) {
        reset();
    }

    public void setCalcFoundRows(boolean calc) {
        if (calc) {
            mCalcFoundRows.set(Boolean.TRUE);
        } else {
            reset();
        }
    }

    public int getFoundRows() {
        if (mCalcFoundRows.get() == null || !mCalcFoundRows.get().booleanValue()) {
            throw new IllegalStateException("Attempted to getFoundRows without first calling 'setCalcFoundRows'");
        }
        if (mFoundRows.get() == null) {
            captureFoundRows();
        }

        return mFoundRows.get();
    }

    //
    // Private methods
    //

    private void captureFoundRows() {
        init();

        // Sanity checks

        if (mFoundRows.get() != null) {
            throw new HibernateException("'" + SELECT_FOUND_ROWS + "' called more than once");
        }

        if (mSQLStatementsPrepared.get() < 1) {
            throw new HibernateException("'" + SELECT_FOUND_ROWS + "' called before '" + SELECT_PREFIX + CALC_FOUND_ROWS_HINT + "'");
        }

        // Fetch the total number of rows

        sessionFactory.getCurrentSession().doWork(new Work() {
            @Override
            public void execute(Connection connection) throws SQLException {
                final Statement stmt = connection.createStatement();
                ResultSet rs = null;
                try {
                    rs = stmt.executeQuery(SELECT_FOUND_ROWS);
                    if (rs.next()) {
                        mFoundRows.set(rs.getInt(1));
                    } else {
                        mFoundRows.set(0);
                    }
                } finally {
                    if (rs != null) {
                        rs.close();
                    }
                    try {
                        stmt.close();
                    } catch (RuntimeException exp) {

                    }
                }
            }
        });
    }

    public void setSessionFactoryBeanName(String sessionFactoryBeanName) {
        this.sessionFactoryBeanName = sessionFactoryBeanName;
    }

    @Override
    public void setBeanFactory(BeanFactory arg0) throws BeansException {
        this.beanFactory = arg0;
    }

}

Ответ 3

Если вам не нужно отображать общее количество страниц, я не уверен, что вам нужен запрос count. Множество сайтов, в том числе google, не отображают итоговые результаты. Вместо этого они просто говорят "next > ".

Ответ 4

Вы можете использовать MultiQuery для выполнения обоих запросов в одном вызове базы данных, что намного более эффективно. Вы также можете сгенерировать запрос count, поэтому вам не нужно писать его каждый раз. Вот общая идея...

var hql = "from Item where i.Age > :age"
var countHql = "select count(*) " + hql;

IMultiQuery multiQuery = _session.CreateMultiQuery()
    .Add(s.CreateQuery(hql)
            .SetInt32("age", 50).SetFirstResult(10))
    .Add(s.CreateQuery(countHql)
            .SetInt32("age", 50));

var results = multiQuery.List();
var items = (IList<Item>) results[0];
var count = (long)((IList<Item>) results[1])[0];

Я предполагаю, что было бы достаточно просто обернуть это в простой простой в использовании метод, чтобы вы могли иметь числовые, счетные запросы в одной строке кода.

Как альтернатива , если вы хотите протестировать Linq для работы в NHibernate в nhcontrib, вы можете обнаружить, что можете сделать что-то вроде этого:

var itemSpec = (from i in Item where i.Age > age);
var count = itemSpec.Count();
var list = itemSpec.Skip(10).Take(10).AsList(); 

Очевидно, что не происходит пакетной обработки, так что это не так эффективно, но все равно может удовлетворить ваши потребности?

Надеюсь, это поможет!

Ответ 5

Существует способ

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name
    -> WHERE id > 100 LIMIT 10;
mysql> SELECT FOUND_ROWS();

Второй SELECT возвращает число, указывающее, сколько строк будет возвращено первым SELECT, если бы оно было записано без предложения LIMIT.

Ссылка: FOUND_ROWS()

Ответ 6

Я знаю эту проблему и столкнулся с ней раньше. Во-первых, механизм двойного запроса, где он выполняет те же условия SELECT, действительно не оптимален. Но, это работает, и прежде чем вы уйдете и сделаете гигантские изменения, просто поймите, что это может не стоить того.

Но, в любом случае:

1) Если вы имеете дело с небольшими данными на стороне клиента, используйте реализацию набора результатов, которая позволяет установить курсор в конец набора, получить смещение строки, а затем reset курсор до первого.

2) Перепроектируйте запрос, чтобы получить COUNT (*) в качестве дополнительного столбца в обычных строках. Да, он содержит одно и то же значение для каждой строки, но он включает только один дополнительный столбец, который является целым числом. Это неправильный SQL для представления агрегированного значения с неагрегированными значениями, но он может работать.

3) Перепроектируйте запрос, чтобы использовать оценочный лимит, аналогичный тому, что упоминалось. Используйте строки на странице и некоторый верхний предел. Например. просто скажите что-то вроде "Показано от 1 до 10 из 500 или более". Когда они переходят на "Показывать от 25 до 260 из X", это более поздний запрос, поэтому вы можете просто обновить оценку X, сделав верхнюю границу относительно строки страницы * страницы.

Ответ 7

Я думаю, что решение зависит от используемой вами базы данных. Например, мы используем MS SQL и используя следующий запрос

select 
  COUNT(Table.Column) OVER() as TotalRowsCount,
  Table.Column,
  Table.Column2
from Table ...

Эта часть запроса может быть изменена с помощью базы данных, указанной SQL.

Также мы устанавливаем максимальный результат запроса, который мы ожидаем увидеть, например.

query.setMaxResults(pageNumber * itemsPerPage)

И получает экземпляр ScrollableResults в результате выполнения запроса:

ScrollableResults result = null;
try {
    result = query.scroll();
    int totalRowsNumber = result.getInteger(0);
    int from = // calculate the index of row to get for the expected page if any

    /*
     * Reading data form page and using Transformers.ALIAS_TO_ENTITY_MAP
     * to make life easier.
     */ 
}
finally {
    if (result != null) 
        result.close()
}

Ответ 8

На этой странице викинга Hibernate:

https://www.hibernate.org/314.html

Я представляю полное решение для разбивки на страницы; в частности, общее число элементов вычисляется путем прокрутки до конца набора результатов, который в настоящее время поддерживается несколькими драйверами JDBC. Это позволяет избежать второго запроса "count".

Ответ 9

Я нашел способ делать подкачку в спящем режиме, не делая подсчет количества (*) по большому размеру набора данных. Посмотрите на решение, которое я разместил для моего ответа здесь.

обработка большого количества записей в базе данных с пейджингом замедляется со временем

вы можете выполнять пейджинг по одному, не зная, сколько страниц вам потребуется изначально

Ответ 10

Вот решение д-ра Ричарда Кеннарда (помните об ошибке в комментарии к блогу!), используя Hibernate Interceptors

Для сводки вы привязываете свой sessionFactory к классу перехватчиков, чтобы ваш перехватчик мог дать вам количество найденных строк позже.

Код можно найти в ссылке на решение. Ниже приведен пример использования.

SessionFactory sessionFactory = ((org.hibernate.Session) mEntityManager.getDelegate()).getSessionFactory();
MySQLCalcFoundRowsInterceptor foundRowsInterceptor = new MySQLCalcFoundRowsInterceptor( sessionFactory );
Session session = sessionFactory.openSession( foundRowsInterceptor );

try {
   org.hibernate.Query query = session.createQuery( ... )   // Note: JPA-QL, not createNativeQuery!
   query.setFirstResult( ... );
   query.setMaxResults( ... );

   List entities = query.list();
   long foundRows = foundRowsInterceptor.getFoundRows();

   ...

} finally {

   // Disconnect() is good practice, but close() causes problems. Note, however, that
   // disconnect could lead to lazy-loading problems if the returned list of entities has
   // lazy relations

   session.disconnect();
}

Ответ 11

здесь способ разбиения на страницы выполняется в спящем режиме

Query q = sess.createQuery("from DomesticCat cat");
q.setFirstResult(20);
q.setMaxResults(10);
List cats = q.list();

вы можете получить больше информации из hibernate docs по адресу: http://www.hibernate.org/hib_docs/v3/reference/en-US/html_single/#objectstate-querying-executing-pagination 10.4.1.5 и 10.4.1.6 дают вам более гибкие варианты.

BR,
~ А