Photo by Iga Palacz on Unsplash
Недавно я решил проблему медленных запросов в продакшн SQL, после устранения неполадок я обнаружил несколько скрытых и легко игнорируемых проблем.
Предыстория бизнеса Введение
В последнее время в бизнесе необходимо запустить функцию раннего предупреждения, а также необходимо выяснить транзакции за определенный период времени и получить текущую успешность транзакций. Когда вероятность успеха ниже установленного порога, будет отправлено SMS-предупреждение. Бизнес-логика очень проста, и после тестирования в тестовой среде развертывание запускается. Фактический производственный цикл показал, что каждый SQL-запрос занимал более 60 секунд.
Введение в системную архитектуру
Весенняя загрузка + Mybatis + Oracle.
Количество запрашиваемых таблиц составляет порядка 100 миллионов.
Устранение неполадок
Структура таблицы транзакций (упрощенная) примерно следующая.
create table TB_TEST
(
BANK_CODE VARCHAR2(20),
CREATE_TIME DATE,
OID_BILL NUMBER(16) not null
)
/
create index TB_TEST_CREATE_TIME_INDEX
on TB_TEST (CREATE_TIME)
/
create unique index TB_TEST_OID_BILL_UINDEX
on TB_TEST (OID_BILL)
/
alter table TB_TEST
add constraint TB_TEST_PK
primary key (OID_BILL)
/
Оператор добавления, удаления, изменения и запроса этого проекта автоматически генерируется MybatisGenerate, а оператор запроса использует CREATE_TIME в качестве условного запроса.Автоматически сгенерированный sql выглядит следующим образом.
select *
from TB_TEST
where CREATE_TIME >= #{start_time}
and CREATE_TIME < #{end_time};
Установив конфигурацию Druid, мы выводим журнал SQL конкретного запроса на консоль. Конкретные настройки следующие.
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
... ...
<property name="filters" value="stat,slf4j" />
</bean>
<!-- logback -->
<logger name="druid.sql.Statement" level="DEBUG" additivity="false">
<appender-ref ref="STDOUT"/>
</logger>
Конкретный журнал sql выглядит следующим образом:
Из журнала мы можем ясно видеть фактический запущенный SQL, а также параметры и типы запросов.
Из оператора запроса условия нашего запроса верны, и поскольку CREATE_TIME имеет независимый индекс, запрос будет проходить через индекс, и скорость запроса должна быть очень высокой, и каждый запрос не займет более 60 секунд.
Поэтому было высказано предположение, что в запросе по какой-то причине было полное сканирование таблицы, а медленный запрос был вызван отсутствием индексации. Найдите соответствующую информацию в Google и просмотрите статьюблог woo woo woo.cn на.com/chen--table/….
Согласно описанию в статье, в Oracle есть неявное преобразование, когда тип не совпадает, Oracle будет активно преобразовывать тип в целевой тип. Глядя на нашу структуру таблицы, CREATE_TIME имеет тип Date, но, согласно журналу, CREATE_TIME, передаваемое нашими параметрами запроса, имеет тип TIMESTAMP.
Таким образом, фактический запрос SQL в базе данных выглядит следующим образом:
SELECT *
FROM TB_TEST
WHERE (CREATE_TIME >= to_timestamp('2018-03-03 18:45:32', 'yyyy-mm-dd hh24:mi:ss') and
CREATE_TIME < to_timestamp('2019-01-03 18:45:32', 'yyyy-mm-dd hh24:mi:ss'));
Здесь может происходить неявное преобразование.
Как доказать это предположение? Мы можем использовать EXPLAIN PLAN для анализа плана выполнения SQL.Приведенный выше план выполнения SQL выглядит следующим образом.
Из приведенного выше рисунка мы можем получитьTB ACCESS FULLВидно, что медленный запрос на этот раз действительно вызван полным сканированием таблицы.
Затем мы проверяем информацию Predicate Information в плане выполнения, Oracle использует INTERNAL_FUNCATIPON для преобразования типа CREATE_TIME. С этого момента видно, что преобразование встроенной функции происходит в поле индекса процесса запроса.
Оптимизация производительности SQL, как правило, небольшая, избегая использования функций в индексированных полях.
Теперь, когда вы знаете причину, решение не так сложно. Мы можем решить проблему, изменив запрос sql на следующий.
select *
from TB_TEST
where CREATE_TIME >= TO_DATE(#{start_time}, 'yyyy-mm-dd hh24:mi:ss')
and CREATE_TIME < TO_DATE(#{end_time}, 'yyyy-mm-dd hh24:mi:ss');
-- 或者使用 cast 函数
select *
from TB_TEST
where CREATE_TIME >= cast(#{start_time} as date)
and CREATE_TIME < cast(#{end_time} as date);
Проанализируйте причины
После решения проблемы давайте проанализируем, почему тип Date в типе Java в итоге будет преобразован в тип TIMESTAMP в Oracle.
В этом случае мы используем инфраструктуру Mybatis, которая внутренне преобразует типы данных Java в соответствующие типы данных JDBC. ПроверятьПреобразование типа MybatisВ этом разделе мы можем обнаружить, что тип Java Date будет преобразован в java.sql.TIMESTAMP.
Затем мы рассмотрим правила преобразования типов данных Oracle JDBC. существуетdocs.Oracle.com/Чэнду/B19306_0…Мы видим, что TIMESTAMP будет преобразован в TIMESTAMP в Oracle.
расширение проблемы
Предположим, мы меняем тип CREATE_TIME на TIMESTAMP, а затем преобразуем CREATE_TIME в тип Date при запросе, будет ли также происходить преобразование встроенной функции, а затем вызывать полное сканирование таблицы? Запрос sql выглядит следующим образом.
-- CREATE_TIME 类型为 TIMESTAMP
select *
from TB_TEST
where CREATE_TIME >= TO_DATE('2018-02-27 19:36:21', 'yyyy-mm-dd hh24:mi:ss')
and CREATE_TIME < TO_DATE('2018-12-27 19:36:21', 'yyyy-mm-dd hh24:mi:ss')
. . . .
. . . .
. . . .
Мы анализируем этот SQL с помощью EXPLAIN PLAN.
Мы видим, что встроенное преобразование происходит, но с другой стороны. Этот запрос проходит через файл index.
Из этого примера видно, что использование функции для индексированного поля приводит к полному сканированию таблицы. Но использование функций для входящих параметров запроса не делает индекс недействительным.
Суммировать
1 При запросе SQL необходимо обращать внимание на непротиворечивость типов данных с обеих сторон.Хотя неявное преобразование базы данных поможет нам решить проблему несогласованности данных, это неявное преобразование приносит некоторые скрытые проблемы, которые мы не можем найти быстро. Поэтому используйте явное преобразование вместо неявного преобразования. Это делает наш SQL более понятным, понятным и более управляемым.
2 Научитесь использовать EXPLAIN PLAN для анализа медленного SQL.
3. Использование связанных функций в полях индекса приведет к медленным запросам. Не используйте функции в полях индекса во время запроса.
Справочная документация
1,преобразование данных оракула
2.,тип времени оракула
Если вы считаете, что это хорошо, пожалуйста, поставьте автору большой палец вверх~ Спасибо.
Читатели, которым понравилась эта статья, добро пожаловать в долгое нажатие, чтобы следить за сообщением программы номера подписки ~ позвольте мне поделиться программой с вами.
Рекомендуемое чтение:
1, Анализ причины аномального выполнения SQL в производственной системе
2, Вопросы об использовании Threadlocal в dubboвопрос
3. Шаблонный метод изучения шаблонов проектирования из исходного кода