реальный запрос через JDBC в SQL базу

private static void dumpFreshUsers(
    final String dbDriver, final String dbUrl, 
    final String dbUser, final String dbPass,
    final long time)
    throws ClassNotFoundException, SQLException {

  Class.forName(dbDriver);
  Connection con = DriverManager.getConnection(dbUrl, dbUser, dbPass);

  final PreparedStatement ps = con.prepareStatement(
    "select * from user_info where creation_time < ?");
  ps.setFetchSize(1000);

  ps.setTime(1, new Time(System.currentTimeMillis() - time));

  final ResultSet rs = ps.executeQuery();
  while(rs.next()) {
    System.out.println(rs.getLong(1) + "|" + 
      rs.getString("login") + "|" + 
      rs.getString("FIO"));
  }
  rs.close();
  ps.close();
  con.close(); }

Пример вызова

String dbUser = "schema_name";
final String dbPass = "*********";
final String dbUrl = "jdbc:oracle:thin:@dbserver.world.com:1521:dbinstance";
final String dbDriver = "oracle.jdbc.driver.OracleDriver";

final int millisIn24Hours = 1000 * 60 * 60 * 24;

dumpFreshUsers(dbDriver, dbUrl, dbUser, dbPass, millisIn24Hours);

JDBC - стандарт общения с SQL базами

Спецификации версии 1.0, 2.0, 3.0, 4.0 - на Oracle

Тюториал - раньше Sun, теперь Oracle

получает данные из коллекции - а как?

• перебор + проверка условия - full scan
  нагрузка на блочный IO и на CPU
• по точному ключу - random access - RowId scan
  меньше данных, но штучный IO + позиционирование
• по индексу - index scan
  бежим по индексу и ходим в данные

Понятие "стоимость" запроса:
* сколько ресурсов нужно на получение информации
* сколько времени это займет

Понятие ROWID:
* указатель на ряд данных

Oracle ROWID (url)

The Oracle 8 format is on 10 bytes:

bits 1 to 32 (bytes 1 to 4): 
  data object id (0-4294967295)

bits 33 to 44 (byte 5 and half byte 6): 
  file number inside the tablespace (0-4095)

bits 45 to 64 (half byte 6 and bytes 7 and 8): 
  block number inside the file (0-1048575)

bits 65 to 80 (bytes 9 and 10): 
  row number inside the block (0-65535)

select rowid from users where login like '%pulser%'
AAATCsAAGAAAAH5AAn -- 18 hex digits
  AAATCs - объект (таблица users)
  AAG    - относительный номер файла в tablespace
  AAAAH5 - номер блока в файле
  AAn    - номер ряда таблицы в блоке

В индексе хранятся rowid ~= указатель в памяти

создать индекс

Oracle / MySQL / MongoDB

create table city ( 
  id number primary key, 
  name varchar2(50), 
  state varchar2(50),
  country varchar2(50), 
  yandex_office number(1),
  start_ts timestamp);

insert into city ( 7, 'Москва', 'ЦФО', 'Россия', 1, systimestamp );
insert into city ( 8, 'Санкт-Петербург', 'СЗФО', 'Россия', 1, systimestamp );
insert into city ( 9, 'Тверь', 'ЦФО', 'Россия', 0, null );

-- уникальный индекс - повторов названий нет
create unique index idx_city_name on city (name);

-- простой индекс - допускает повторы
create index idx_city_country on city(country);
create index idx_city_ts on city (start_ts);

-- битовые маски по значению 
-- (nb: если значений много => много масок - плохо)
create bitmap index idx_city_office on city(yandex_office);

-- составной индекс по двум полям
create index idx_compund on city(country, state);

index scan

• index unique scan
  запрос в индекс за одним рядом ( city_id in ( 'Москва', 'Екатеринбург' ) )
  unique индекс idx_city_name
• index range scan
  запрос за диапазоном рядов ( start_time > '2011.09.01' )
  обычный b-tree индекс idx_city_ts
• index skip scan
  запрос по второму полю индекса ( state = 'СЗФО' )
  index idx_city_compound - "точнее", даже при опущенном первом поле
• full index scan
  надо устранить сортировку (order by / group by по полям индекса)
• fast full index scan
  в индексе есть все колонки из select - можно не ходить в таблицу

Стоимость

Oracle генерирует все возможные "пути",
выбирает более дешевый и кеширует его.

Критерии:

• количество disk I/O операций
• I/O блоками или "точечными" запросами
• количество памяти под временные буфера
• количество CPU ресурсов

Точную формулу не знаю и планы сравниваю "в относительных единицах".

План выбран и готов к исполнению

Размер ряда * количество рядов на сервере

Буфер под данные - в памяти ? на диске ?

Скорость получения данных пользователем

Snapshot too old / другие транзакции

-- маленькие таблицы можно Oracle (ссылка) просить удерживать в памяти
create table x ( 
  id number (20) primary key, 
  value varchar2(128)) 
  cache;

Получение данных на клиенте

Размер ряда

Количество рядов в буфере

JDBC - statement.setFetchSize(Н рядов)

размер ряда * колво рядов => OutOfMemory

По умолчанию - 10 рядов в Оракле - очень мало

MongoDB - можно установить колво рядов, но буфер <= 4Mb

MySQL - по умолчанию тащит все ряды в память (о ООМ)

Буфер делается под макс. возможный размер
-> таблица с 10 varchar2(4000) полей - плохо

Blog пост про MySQL MySQL JDBC Memory Usage on Large ResultSet

google:useCursorFetch mysql jdbc

Обратите внимание на fetchSize - это важно.

join / left join / right join

Мы не рассматривали join-ы, но они - комбинация доступа за одним и несколькими рядами.

Смотрите explain plan ... и пробуйте

Oracle документация: 11.6 Understanding Joins

Nested Loops:
берем ряд из одной таблицы - и ищем ему соответствие в другой -> o ( n * m ) ?

Merge sort join
* сортируем ряды обоих таблиц по ключу объединения - o(m ln m) + o (n ln n)
* "сливаем" два отсортированных списка -> o(max(n,m))

А как же NoSQL решения?
Ответ NoSQL: we do NOT do joins ...

ибо: объемы, шардирование, партиционирование, много нод с данными, сеть етс етс етс

Пример из документации Oracle

11.4.2.1 Overview of EXPLAIN PLAN

EXPLAIN PLAN FOR
SELECT 
    e.employee_id, 
    j.job_title, 
    e.salary, 
    d.department_name
FROM employees e
    JOIN jobs j on j.job_id = e.job_id
    JOIN departments d on d.department_id = e.department_id
WHERE  
    e.employee_id < 103;

"ожидаемый" plan

-----------------------------------------------------------------------------------
| Id  | Operation                     |  Name        | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |     3 |   189 |    10  (10)|
|   1 |  NESTED LOOPS                 |              |     3 |   189 |    10  (10)|
|   2 |   NESTED LOOPS                |              |     3 |   141 |     7  (15)|
|*  3 |    TABLE ACCESS FULL          | EMPLOYEES    |     3 |    60 |     4  (25)|
|   4 |    TABLE ACCESS BY INDEX ROWID| JOBS         |    19 |   513 |     2  (50)|
|*  5 |     INDEX UNIQUE SCAN         | JOB_ID_PK    |     1 |       |            |
|   6 |   TABLE ACCESS BY INDEX ROWID | DEPARTMENTS  |    27 |   432 |     2  (50)|
|*  7 |    INDEX UNIQUE SCAN          | DEPT_ID_PK   |     1 |       |            |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("E"."EMPLOYEE_ID"<103)
   5 - access("E"."JOB_ID"="J"."JOB_ID")
   7 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID"

реально исполненный план

-------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |               |     3 |   189 |     8  (13)| 00:00:01 |
|   1 |  NESTED LOOPS                   |               |       |       |            |          |
|   2 |   NESTED LOOPS                  |               |     3 |   189 |     8  (13)| 00:00:01 |
|   3 |    MERGE JOIN                   |               |     3 |   141 |     5  (20)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID | JOBS          |    19 |   513 |     2   (0)| 00:00:01 |
|   5 |      INDEX FULL SCAN            | JOB_ID_PK     |    19 |       |     1   (0)| 00:00:01 |
|*  6 |     SORT JOIN                   |               |     3 |    60 |     3  (34)| 00:00:01 |
|   7 |      TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |     3 |    60 |     2   (0)| 00:00:01 |
|*  8 |       INDEX RANGE SCAN          | EMP_EMP_ID_PK |     3 |       |     1   (0)| 00:00:01 |
|*  9 |    INDEX UNIQUE SCAN            | DEPT_ID_PK    |     1 |       |     0   (0)| 00:00:01 |
|  10 |   TABLE ACCESS BY INDEX ROWID   | DEPARTMENTS   |     1 |    16 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   6 - access("E"."JOB_ID"="J"."JOB_ID")
       filter("E"."JOB_ID"="J"."JOB_ID")
   8 - access("E"."EMPLOYEE_ID"<103)
   9 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

Литература

Oracle: The Query Optimizer

MySQL Reference: Chapter 7 Optimization

MongoDB: Manual: Optimization

by Guy Harrison - эксперт в вопросах performance tuning баз данных:
* 10 things you should know about NoSQL databases
* книги по оптимизации
* blog

google:explain plan ИМЯ_БАЗЫ - ищите!

wikipedia:Query_Plan / wikipedia:План_выполнения_запроса