#mysql
#mysql
Вопрос:
Я работаю над большими таблицами моей базы данных (mysql). Некоторые из моих запросов выполняются более 5 минут. Вот пример моего запроса, который выполняется медленно:
select b.DESCRIPTION collateral_type, a.description brand, a.year,
a.model, a.plate_number, d.description fuel, a.chassis_number,
a.engine_number, c.description as color, insurance_name
from lms_loan_application_collateral a inner join
lms_collateral_type b
on a.COLLATERAL_TYPE_ID = b.id left join
lms_color c
on a.color_id = c.id left join
lms_fuel_type d
on a.fuel_type_id = d.id inner join
lms_loan_application e
on e.id = a.loan_application_id inner join
lms_dlr_dtl f
on e.code = f.lano inner join
lms_loanapp_dtl g
on f.lano = g.lano
where b.description in ('Motorcycle', 'Automotive', 'Heavy Equipment');
вот объяснение запроса mysql
---- ------------- ------- ------- -------------------------------------------------------- ------------------------- --------- ------------ ------- ----------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
---- ------------- ------- ------- -------------------------------------------------------- ------------------------- --------- ------------ ------- ----------------------------------------------------
| 1 | SIMPLE | g | index | lms_loanapp_dtl_lano | lms_loanapp_dtl_lano | 23 | NULL | 23432 | Using where; Using index |
| 1 | SIMPLE | f | ref | lano | lano | 23 | new.g.LANO | 1 | Using index |
| 1 | SIMPLE | b | ALL | lms_collateral_type_description,lms_collateral_type_id | NULL | NULL | NULL | 11 | Using where; Using join buffer (Block Nested Loop) |
| 1 | SIMPLE | a | ref | collateral_type_id | collateral_type_id | 5 | new.b.ID | 4067 | Using index condition |
| 1 | SIMPLE | e | ref | lms_loan_application_id | lms_loan_application_id | 153 | func | 1 | Using index condition; Using where |
| 1 | SIMPLE | c | ALL | LMS_color_id | NULL | NULL | NULL | 20 | Range checked for each record (index map: 0x1) |
| 1 | SIMPLE | d | ALL | LMS_fuel_type_id | NULL | NULL | NULL | 4 | Using where; Using join buffer (Block Nested Loop) |
---- ------------- ------- ------- -------------------------------------------------------- ------------------------- --------- ------------ ------- ----------------------------------------------------
под ключевым столбцом находятся значения NULL. Я не уверен, использует ли mysql созданные мной индексные ключи.
Ниже приведен список индексов, которые я создал для задействованных таблиц:
ПОКАЗАТЬ ИНДЕКС ИЗ lms_loan_application_collateral Из нового;
--------------------------------- ------------ --------------------- -------------- --------------------- ----------- ------------- ---------- -------- ------ ------------ --------- ---------------
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
--------------------------------- ------------ --------------------- -------------- --------------------- ----------- ------------- ---------- -------- ------ ------------ --------- ---------------
| lms_loan_application_collateral | 1 | color_id | 1 | color_id | A | 36 | NULL | NULL | YES | BTREE | | |
| lms_loan_application_collateral | 1 | fuel_type_id | 1 | fuel_type_id | A | 6 | NULL | NULL | YES | BTREE | | |
| lms_loan_application_collateral | 1 | loan_application_id | 1 | loan_application_id | A | 89493 | NULL | NULL | YES | BTREE | | |
| lms_loan_application_collateral | 1 | collateral_type_id | 1 | collateral_type_id | A | 22 | NULL | NULL | YES | BTREE | | |
--------------------------------- ------------ --------------------- -------------- --------------------- ----------- ------------- ---------- -------- ------ ------------ --------- ---------------
ПОКАЗАТЬ ИНДЕКС ИЗ lms_collateral_type Из new;
--------------------- ------------ --------------------------------- -------------- ------------- ----------- ------------- ---------- -------- ------ ------------ --------- ---------------
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
--------------------- ------------ --------------------------------- -------------- ------------- ----------- ------------- ---------- -------- ------ ------------ --------- ---------------
| lms_collateral_type | 1 | lms_collateral_type_description | 1 | DESCRIPTION | A | 11 | NULL | NULL | YES | BTREE | | |
| lms_collateral_type | 1 | lms_collateral_type_id | 1 | ID | A | 11 | NULL | NULL | YES | BTREE | | |
--------------------- ------------ --------------------------------- -------------- ------------- ----------- ------------- ---------- -------- ------ ------------ --------- ---------------
ПОКАЗАТЬ ИНДЕКС ИЗ lms_color Из нового;
----------- ------------ -------------- -------------- ------------- ----------- ------------- ---------- -------- ------ ------------ --------- ---------------
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
----------- ------------ -------------- -------------- ------------- ----------- ------------- ---------- -------- ------ ------------ --------- ---------------
| lms_color | 1 | LMS_color_id | 1 | id | A | 20 | NULL | NULL | | BTREE | | |
----------- ------------ -------------- -------------- ------------- ----------- ------------- ---------- -------- ------ ------------ --------- ---------------
ПОКАЗАТЬ ИНДЕКС ИЗ lms_fuel_type Из new;
--------------- ------------ ------------------ -------------- ------------- ----------- ------------- ---------- -------- ------ ------------ --------- ---------------
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
--------------- ------------ ------------------ -------------- ------------- ----------- ------------- ---------- -------- ------ ------------ --------- ---------------
| lms_fuel_type | 1 | LMS_fuel_type_id | 1 | id | A | 4 | NULL | NULL | YES | BTREE | | |
--------------- ------------ ------------------ -------------- ------------- ----------- ------------- ---------- -------- ------ ------------ --------- ---------------
ПОКАЗАТЬ ИНДЕКС ИЗ lms_loan_application Из new;
---------------------- ------------ ------------------------- -------------- -------------- ----------- ------------- ---------- -------- ------ ------------ --------- ---------------
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
---------------------- ------------ ------------------------- -------------- -------------- ----------- ------------- ---------- -------- ------ ------------ --------- ---------------
| lms_loan_application | 1 | lmspis_id | 1 | PIS_ID | A | 1878 | NULL | NULL | YES | BTREE | | |
| lms_loan_application | 1 | loan_type_id | 1 | LOAN_TYPE_ID | A | 6 | NULL | NULL | YES | BTREE | | |
| lms_loan_application | 1 | lms_loan_application_id | 1 | ID | A | 1878 | NULL | NULL | YES | BTREE | | |
---------------------- ------------ ------------------------- -------------- -------------- ----------- ------------- ---------- -------- ------ ------------ --------- ---------------
ПОКАЗАТЬ ИНДЕКС ИЗ lms_dlr_dtl Из нового;
------------- ------------ ---------- -------------- ------------- ----------- ------------- ---------- -------- ------ ------------ --------- ---------------
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
------------- ------------ ---------- -------------- ------------- ----------- ------------- ---------- -------- ------ ------------ --------- ---------------
| lms_dlr_dtl | 0 | PRIMARY | 1 | LDDID | A | 90066 | NULL | NULL | | BTREE | | |
| lms_dlr_dtl | 1 | lano | 1 | LANO | A | 90066 | NULL | NULL | YES | BTREE | | |
------------- ------------ ---------- -------------- ------------- ----------- ------------- ---------- -------- ------ ------------ --------- ---------------
ПОКАЗАТЬ ИНДЕКС ИЗ lms_loanapp_dtl Из нового;
----------------- ------------ ---------------------- -------------- ------------- ----------- ------------- ---------- -------- ------ ------------ --------- ---------------
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
----------------- ------------ ---------------------- -------------- ------------- ----------- ------------- ---------- -------- ------ ------------ --------- ---------------
| lms_loanapp_dtl | 0 | PRIMARY | 1 | LLADID | A | 23432 | NULL | NULL | | BTREE | | |
| lms_loanapp_dtl | 1 | lms_loanapp_dtl_lano | 1 | LANO | A | 23432 | NULL | NULL | YES | BTREE | | |
----------------- ------------ ---------------------- -------------- ------------- ----------- ------------- ---------- -------- ------ ------------ --------- ---------------
Комментарии:
1. Вероятно, это как-то связано с вашим
lms_collateral_type_descriptionиндексом. Смотрите это dba.stackexchange.com/questions/35821 /…
Ответ №1:
Таблицы, которые указывают тип как ALL, не используют индексы. Создайте индексы для столбца id в таблицах, представленных в c и d, и по одному индексу для столбцов description и id в b.
create index ix_id on lms_color(id);
create index ix_id on lms_fuel_type(id);
create index ix_description_id on lms_collateral_type(description,id);
Ответ №2:
MySQL использует ALL для вашего WHERE из-за низкой мощности. Итак, MySQL считает, что дешевле просто выполнять сканирование таблицы по 11 записям, чем использовать двоичный поиск по индексу.
Реальная проблема заключается в том, что вы не фильтруете, используя столбец, индекс которого имеет более высокую мощность ( lms_loanapp_dtl_lano и collateral_type_id ).