Индекс не используется в зависимости от привязки параметра и кодировки ⇐ Php

[Anonymous]

Вопрос
Почему мой индекс игнорируется при определенных обстоятельствах, что приводит к замедлению выполнения. Я нашел причину в сочетании использования UNION ALL + выбранного API базы данных PHP + выбранной кодировки + использованной реализации привязки параметров.
Настройка
Я создал представление my_view, которое выглядит следующим образом:

Код: Выделить всё

SELECT type FROM table1

UNION ALL

SELECT type FROM table2

И таблица1, и таблица2 имеют одинаковую структуру, которая выглядит следующим образом:

Код: Выделить всё

CREATE TABLE table1
(
id INT UNSIGNED AUTO_INCREMENT,
type INT NOT NULL,
... -- some more data columns
PRIMARY KEY (id)
);

CREATE INDEX my_idx ON table1 (type);

Теперь я хочу выбрать все записи, где тип = 1. Это должно быть довольно просто:

Код: Выделить всё

SELECT * FROM my_view WHERE type = 1;

Выполнение EXPLAIN для этого запроса дает следующий результат:

Код: Выделить всё

┌────┬─────────────┬────────────┬───────┬───────────────┬────────┬─────────┬────────┬──────┬─────────────┐
│ id │ select_type │ table      │ type  │ possible_keys │ key    │ key_len │ ref    │ rows │ Extra       │
╞════╪═════════════╪════════════╪═══════╪═══════════════╪════════╪═════════╪════════╪══════╪═════════════╡
│  1 │ PRIMARY     │  │ ALL   │               │        │         │        │ 110  │ Using where │
│  2 │ DERIVED     │ table1     │ const │ my_idx        │ my_idx │ 5       │ const  │ 72   │ Using index │
│  3 │ UNION       │ table2     │ const │ my_idx        │ my_idx │ 5       │ const  │ 38   │ Using index │
└────┴─────────────┴────────────┴───────┴───────────────┴────────┴─────────┴────────┴──────┴─────────────┘

Выполнение операторов SELECT в консоли SQL работает отлично и очень быстро.
Проблема
Это меняется, когда я использую PHP для выполнения запроса. В зависимости от API подключения к базе данных (например, mysqli, PDO) моя база данных решает не использовать индекс для моего представления, используя тот же запрос, что и раньше:

Код: Выделить всё

┌────┬─────────────┬────────────┬───────┬───────────────┬────────┬─────────┬─────┬──────────┬─────────────┐
│ id │ select_type │ table      │ type  │ possible_keys │ key    │ key_len │ ref │ rows     │ Extra       │
╞════╪═════════════╪════════════╪═══════╪═══════════════╪════════╪═════════╪═════╪══════════╪═════════════╡
│ 1  │ PRIMARY     │  │ ALL   │               │        │         │     │ 56247595 │ Using where │
│ 2  │ DERIVED     │ table1     │ index │               │ my_idx │ 5       │     │ 34706361 │ Using index │
│ 3  │ UNION       │ table2     │ index │               │ my_idx │ 5       │     │ 21541234 │ Using index │
└────┴─────────────┴────────────┴───────┴───────────────┴────────┴─────────┴─────┴──────────┴─────────────┘

Отладка
Моя отладка показала, что следующие комбинации используют индекс ( ) или не используют индекс ():




API
кодировка
дополнительно (методы/опции)





mysqli

Код: Выделить всё

latin1

(по умолчанию)

Код: Выделить всё

->execute_query($sql, [$type])

mysqli

Код: Выделить всё

latin1

(по умолчанию)

Код: Выделить всё

->prepare(); ->bind_param()

; ->execute(); ->get_result()



mysqli

Код: Выделить всё

->set_charset('utf8mb4')

Код: Выделить всё

->execute_query($sql, [$type])

mysqli

Код: Выделить всё

->set_charset('utf8mb4')

Код: Выделить всё

->prepare(); ->bind_param()

; ->execute(); ->get_result()



PDO

Код: Выделить всё

latin1

(по умолчанию)

Код: Выделить всё

ATTR_EMULATE_PREPARES = true

(по умолчанию)



PDO

Код: Выделить всё

latin1

(по умолчанию)

Код: Выделить всё

->setAttribute(PDO::ATTR_EMULATE_PREPARES, false)

PDO

Код: Выделить всё

new PDO("...charset=utf8mb4", ...)

Код: Выделить всё

ATTR_EMULATE_PREPARES = true

(по умолчанию)



PDO

Код: Выделить всё

new PDO("...charset=utf8mb4", ...)

Код: Выделить всё

->setAttribute(PDO::ATTR_EMULATE_PREPARES, false)

Я не могу понять смысла этого. Что здесь происходит?
Информация о версии

• MariaDB 10.6.18
  < li>PHP 8.2.26

Последующий запуск EXPLAIN EXTENDED SELECT ... и SHOW WARNINGS также дает тот же результат для все тесты:

Код: Выделить всё

┌───────┬──────┬──────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ Level │ Code │ Message                                                                                                  │
├───────┼──────┼──────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Note  │ 1003 │ /* select#1 */ select `my_view`.`type` AS `type` from `my_database`.`my_view` where `my_view`.`type` = 1 │
└───────┴──────┴──────────────────────────────────────────────────────────────────────────────────────────────────────────┘

При использовании подзапроса вместо представления результат столбца «Сообщение» SHOW WARNINGS изменяется.

Запрос:

Код: Выделить всё

EXPLAIN EXTENDED SELECT * FROM (
SELECT type from table1 UNION ALL SELECT type from table2
) AS q WHERE type = ?;

SHOW WARNINGS;

Результаты:

Вывод всех тестов, отмеченных значком (

Код: Выделить всё

WHEREусловие 

копируется в подзапрос):

Код: Выделить всё

/* select#1 */
SELECT q.type AS type
FROM (
/* select#2 */
SELECT table1.type AS type
FROM my_database.table1
WHERE table1.type = 1

UNION ALL

/* select#3 */
SELECT table2.type AS type
FROM my_database.table2
WHERE table2.type = 1
) q
WHERE q.type = 1;

Вывод всех тестов, отмеченных (

Код: Выделить всё

WHERE

условие НЕ копируется в подзапрос):

Код: Выделить всё

/* select#1 */
SELECT q.type AS type
FROM (
/* select#2 */
SELECT table1.type AS type
FROM my_database.table1

UNION ALL

/* select#3 */
SELECT table2.type AS type
FROM my_database.table2
) q
WHERE q.type = 1;

Воспроизводитель SQL/PHP можно найти здесь:
https://phpize.online/sql/mariadb115/a1 ... 56833df9b/
Я отправил отчет об ошибке, который можно найти здесь:
https://jira.mariadb.org/browse/MDEV-35561
Трассы оптимизатора
Я включил трассировку оптимизатора MariaDB, установив для оптимизированного_trace значение Enabled=on. После выполнения запросов я индивидуально проверил трассировку с помощью

Код: Выделить всё

SELECT * FROM information_schema.optimizer_trace

Разницу обоих выходных данных можно найти здесь:
https://www.diffchecker.com/DX1zel8b/


Подробнее здесь: https://stackoverflow.com/questions/792 ... ng-charset