[Оптимизация производительности Oracle] план выполнения и анализ типа индекса

Oracle
[Оптимизация производительности Oracle] план выполнения и анализ типа индекса

Качество части sql в основном зависит от двух аспектов:

  • 1. На уровне базы данных: это зависит от метода доступа к данным и метода обработки данных, принятых оптимизатором.
  • 2. С точки зрения бизнеса: подходит ли этот SQL для бизнеса?

В качестве примера для анализа возьмем oracle 11g.

1. Как получить доступ к данным

【Без индекса】

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

  • 1. Параллельный доступ
  • 2. Множественный доступ к блоку данных

[с индексом]

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

  • 1. Уникальное сканирование индекса
  • 2. Сканирование диапазона индексов
  • 3. Полное сканирование индекса
  • 4. Быстрое полное сканирование индекса
  • 5. Сканирование с пропуском индекса

2. Метод обработки данных

Выше перечислены несколько методов доступа к данным, которые на самом деле похожи на сортировку, которую мы используем в нашей повседневной разработке.order by, группировкаgroup by,статистикаcountОжидание операций — это все операции над данными, но помимо этих основных операций мы обычно подключаем таблицыjoinОбработка, для метода обработки соединения возможны следующие ситуации:

  • 1,nested loop join(соединение внутреннего вложенного цикла)

  • 2,hash join(хеш-соединение)

  • 3.sort merge join(соединение сортировки слиянием)

Затем мы используем тестовые примеры для проверки трех вышеперечисленныхjoinМетод обработки данных, сценарий использования тестового SQL выглядит следующим образом:

-- 删除nestedLoopTest1、nestedLoopTest2表
drop table nestedLoopTest1 ;
drop table nestedLoopTest2 ;

-- 创建nestedLoopTest表
create table nestedLoopTest1
(
  id    NUMBER(11)
);
commit;

create table nestedLoopTest2
(
  id    NUMBER(11)
);
commit;


-- 各赋值100条数据
BEGIN
FOR i IN 0..100 LOOP
INSERT INTO nestedLoopTest1(id) VALUES(i);
END LOOP;
END;
commit;

BEGIN
FOR i IN 0..100 LOOP
INSERT INTO nestedLoopTest2(id) VALUES(i);
END LOOP;
END;
commit;

-- 1、hash join 哈希连接，因为此时两张表是并行执行的
xxxxxx

-- 2、nested join 内部嵌套连接，此时t2中id建了索引
xxxxxx
-- 3、两个表的id都建立了索引
  • 1,hash join(хеш-соединение)

Продолжаем выполнять следующий sql:

select t1.* from nestedLoopTest1 t1,nestedLoopTest2 t2 where t1.id=t2.id;

РасписаниеnestedLoopTest1и столnestedLoopTest2серединаidНи один из них не индексируется, поэтому мы видим следующий план выполнения:

Этапы выполнения показаны на рисунке выше.Мы видим, что в это время обе таблицы проходят полное сканирование таблицы, а затем снова выполняют полное сканирование таблицы.Hash join, что касаетсяhash joinЭтот принцип будет изучен и представлен отдельно позже.hash joinМаленькая таблица будет загружена в память, а затем большая и малая таблицы будут использоваться для операций ассоциации.

  • 2,nested loop join(соединение внутреннего вложенного цикла)

Продолжаем выполнять следующий sql:

create index nestedLoopTest2index on nestedLoopTest2(id);
select t1.* from nestedLoopTest1 t1,nestedLoopTest2 t2 where t1.id=t2.id;

Как видно из плана выполнения, в первую очередьt1Выполните полное сканирование таблицы, затем проиндексируйтеnestedLoopTest2indexпровестиrangeСканирование по дальности, зачем сканирование по дальности? потому что столt1Запись в , возможно в таблицеt2Соответствует нескольким записям. Кроме того, при написании sql постарайтесь уменьшить операцию возврата таблицы.Из плана выполнения на приведенном выше рисунке нет операции возврата таблицы, то есть нет операции возврата таблицы.TABLE ACCESS BY INDEX ROWID

Для метода соединения с вложенным циклом мы можем представить, что два цикла for являются вложенными.

Кроме того, когда обе таблицы проиндексированы, продолжаем выполнять следующий sql:

create index nestedLoopTest1index on nestedLoopTest1(id);
select t1.* from nestedLoopTest1 t1,nestedLoopTest2 t2 where t1.id=t2.id;

По сравнению с рисунком выше таблицаt1Полное сканирование таблиц больше не выполняется, вместо этого выполняется полное сканирование индекса.

  • 3.sort merge join(соединение сортировки слиянием)

Общий принцип этого метода связывания состоит в том, чтобы определить, отсортирована ли исходная таблица, если нет, отсортировать связанные поля; определить, отсортирована ли связанная таблица, если нет, отсортировать и, наконец, объединить две отсортированные таблицы.

3. План выполнения Oracle

Если мы хотим знать, как осуществляется доступ к данным оракула и как они обрабатываются, нам нужно взглянуть на план выполнения, но план выполнения сообщает нам только эту информацию.

Войдем в sqlplus, возьмем простой sql для объяснения и вкратце поговорим о том, как мы должны понимать план выполнения:

select * from emp;

empТаблица — это таблица сотрудников, которая поставляется с Oracle, щелкните правой кнопкой мыши.Explain Plan, или нажмитеF5Просмотрите план выполнения, как показано ниже:

план выполнения самый левыйDescriptionСтолбец более важен, в нем будут перечислены некоторые шаги выполнения этого sql, у этого столбца следующие правила просмотра:

  • 1. В случае разных уровней сначала выполняется шаг вправо;
  • 2. В случае одного уровня, чем выше результат, тем раньше он будет выполнен;

На приведенном выше рисунке показано, что метод доступа к данным, используемый этим оператором, таков:TABLE ACCESS FULL, то есть полное сканирование таблицы, мы могли бы спросить, этоempРазве таблица не проиндексирована? На самом деле индекс иметь бесполезно, потому что мы просто хотим извлечь данные всей таблицы, индекс бессмысленен, что также показывает ситуацию,Таблицы с индексами не обязательно эффективны, о чем пойдет речь позже.

заCostЭтот индикатор используется оптимизатором оракула для измерения стоимости выполнения этого sql, например, сколько вычислительных ресурсов ЦП ему необходимо потреблять.

Ниже представлены несколько примеров SQL для доступа к методам через индексы.

Во-первых, уникальный индекс (уникальное сканирование индекса)

empnoдаempПервичный ключ таблицы является уникальным индексом.Мы выполняем следующую инструкцию sql, чтобы просмотреть ее план выполнения, как показано на следующем рисунке:

select * from emp where empno=7782

из плана выполненияINDEX UNIQUE SCANВидно, что этот оптимизатор sql, oracle будет выполнять уникальное сканирование индекса, после сканирования индекса мы получим значение индекса7782, а затем Oracle должен перейти к файлу данных, чтобы получить блок данных, соответствующий этому номеру, и вернуть его, поэтому мы можем видеть, что план выполнения показываетTABLE ACCESS BY INDEX ROWID, поскольку в индексе хранится идентификатор каждой строки, поэтому Oracle находит соответствующие данные по атрибуту rowid.

На самом деле иногда нет шага для доступа к блоку данных для получения данных, то есть когда вы хотите получить только его номерempnoвместо*, план выполнения не будет извлекать данные из файла данных, то есть шага 2 не будет, т.к. оракул напрямую возвращает значение индекса после сканирования прямо из индекса, нет необходимости извлекать данные, мы снова Нет, проверьте следующим образом:

select empno from emp where empno=7782

На самом деле, мы обычно не пишем такой sql, ха-ха~~~

Во-вторых, сканирование индекса диапазона (сканирование диапазона индекса)

Предположим, мы выполняем следующий оператор sql:

select job from emp where empno>7782

План его выполнения следующий:

Из плана выполнения видно, что этот тип оператора SQL выполняется следующим образом:index range scanСканирование индекса диапазона.

3. Полное сканирование индекса

Полное сканирование индекса, как следует из названия, сканирует всю область индекса, чтобы определить результат выполнения, например следующую инструкцию SQL:

select count(*) from emp

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

4. Быстрое полное сканирование индекса

Сначала мы копируем таблицу и переименовываем ее с помощью следующего оператора:

create table emp1 as (select * from emp);
truncate table emp1;
-- 插入1,000,000条数据
BEGIN
FOR I IN 0..1000000 LOOP
INSERT INTO EMP1(EMPNO,ENAME) VALUES(
I,CONCAT('TBL',I));
END LOOP;
END;

После того, как таблица построена, давайте посмотрим на план выполнения следующего sql, чтобы увидеть, будет ли этот sql использоваться при большом объеме данных.index fast full scan

index fast full scanотличный отindex full scanДело в том, что первый может читать сразу несколько блоков данных, подобно параллельному, а второй читает последовательно.

Оператор SQL, использующий этот метод выполнения, обычно тот, который может напрямую определить результат выполнения через индекс.Например, мы выполняем следующий SQL:

Пять, сканирование с пропуском индекса (сканирование с пропуском индекса)

index skip scanЭто метод сканирования индекса, представленный после Oracle 9i. Он в основном используется для решения составного индекса. Когда условие where использует запрос неведущего столбца, используется значение по умолчанию.ACCESS TABLE FULLНедостатки полного сканирования таблицы. Однако существуют некоторые ограничения на использование этой функции, в основном следующие пункты:

  • 1. Ведущий столбец комбинированного индекса имеет меньше уникальных значений (много повторяющихся значений)
  • 2. База данных принимает оптимизатор CBO, и анализируются таблицы и индексы.
  • 3. В условии запроса where отсутствует ведущий столбец составного индекса.

Далее мы в основном проверяем два вопроса:

Соответствующий оператор SQL для проверки выглядит следующим образом:

--删除表
drop table student;
commit;

-- 创建Student表
create table STUDENT
(
  stuno    NUMBER(11),
  stuname  VARCHAR2(20),
  schoolno NUMBER(11),
  age      NUMBER(3)
);
commit;

-- 创建组合索引
create index stucombindex on student(stuname,schoolno);
commit;

--F5查看执行计划，会看到是ACCESS TABLE FULL全表扫描,stuname是前导列，schoolno为非前导列
select * from student t where t.schoolno=100;


-- 赋值100万条数据
BEGIN
FOR i IN 0..1000000 LOOP
INSERT INTO student(stuno,stuname,schoolno) VALUES(
i,'TBL',i);
END LOOP;
END;
commit;


-- 更新3条数据的前导列为不同值
update student t set t.stuname=concat('s',t.stuno) where mod(t.stuno,10000)=0;
commit;

select count(*),count(distinct stuname) from student;

-- 对表、索引进行分析
analyze table student compute statistics for table for all columns for all indexes;

-- 此处查看执行计划，可看到优化器采用的是index skip scan
select * from student where schoolno = 1000;

1. В составном индексе при использовании не ведущих столбцов для запроса оптимизатор используетACCESS TABLE FULLполное сканирование таблицы

Во-первых, закомментируйте предпоследний оператор SQL в приведенном выше sql, и будет выведено следующее: По умолчанию используется полное сканирование таблицы:

2. Проверьте сканирование с пропуском индекса

После нашей проверки мы можем знать, что когда мы создаем составной индекс, в ежедневной разработке мы изо всех сил стараемся использовать столбцы, которые часто используются в __requirements и имеют мало значений с высокой избирательностью повторения __ в качестве ведущих столбцов, чтобы чтобы свести к минимуму не ведущие столбцы, не используйте индекс или используйте только индекс пропуска.

Кроме того, когда мы создаем составной индекс? В основном рассмотрим следующие ситуации:

  • 1. При запросе с одним условием возвращайте больше данных
  • 2. Когда запрос соответствует условиям, возвращается меньше данных

Тогда и только тогда, когда условие 1 и условие 2 выполняются одновременно, мы можем построить составной индекс в это время. Например, таблица сотрудниковemployeeсередина,age=28Есть много людей с этим состоянием.role=Java程序员Это условие возвращает много данных, ноage=28 and role=Java程序员Возвращается очень мало данных!

Одно условие означает:where xxx=xxxСложные состояния относятся к:where xxx=xxx and xxx1=xxx1

6. Сценарии применения

1. Oracle обрабатывает десятки миллионов запросов на подкачку больших таблиц.

Традиционная подкачка оракулов использует следующую структуру:

select *
from (
select fundacco,rownum rowno from
tbl_20191231
where rownum <= #{end}) b
where
b.rowno > #{start}

В то время, когда начало становилось все больше и больше, внешнему подзапросу нужно было пройти больше данных, что было бы очень медленным после фактической проверки производства.Объем данных 500 Вт, 250 записей на странице, когда начало было больше, чем 200Вт, среднее потребление было раз в 1-2с.

Как его оптимизировать? Мы не можем оптимизировать на уровне оракула sql, но мы можем добавить новый столбец rownos, значение которого монотонно увеличивается и создает уникальный индекс. Затем мы очень быстро запрашиваем следующий sql, в среднем около 20 мс.

select * from tbl_20191231 t where t.rownos > #{start} and t.rownos <= #{end}

На самом деле нетрудно обнаружить, что мы используем сканирование диапазона индексов оракула (index range scan) только особенность. План выполнения следующий:

Категории