Что я могу сделать, чтобы сделать этот SQL более эффективным? (таблица имеет 850 тыс. строк)

ProgramBox

Что я могу сделать, чтобы сделать этот SQL более эффективным? (таблица имеет 850 тыс. строк)

Post author:admin
Запись опубликована:30 марта, 2023
Post category:Вопросы по программированию

#mysql #sql #performance

#mysql — сервер #sql #Производительность #mysql

Вопрос:

Я запускал медленный журнал в mysql из-за производственных проблем, и запрос номер один :

 select * from feeditem feeditem0_ where feeditem0_.importance=0 and feeditem0_.company_id=N limit 21;

Я сократил select (N — идентификатор для FK), поскольку он сгенерирован из hibernate, и просто выбрал все поля в этой таблице. Когда я объясняю mysql, я получаю:

 explain select * from feeditem feeditem0_ where feeditem0_.importance=0 and    feeditem0_.company_id=5045 limit 21 G;;
*************************** 1. row ***************************
       id: 1
select_type: SIMPLE
    table: feeditem0_
     type: index_merge
possible_keys: FKF49961B13D5FD8EF,importance
      key: FKF49961B13D5FD8EF,importance
  key_len: 9,5
      ref: NULL
     rows: 2422
    Extra: Using intersect(FKF49961B13D5FD8EF,importance); Using where

В этой таблице около 850 тыс. строк.

Схема:

 CREATE TABLE `feeditem` (
`DTYPE` varchar(31) NOT NULL,
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`dateCreated` datetime DEFAULT NULL,
`endSentance` varchar(255) DEFAULT NULL,
`importance` int(11) DEFAULT NULL,
`startSentance` varchar(255) DEFAULT NULL,
`summary` varchar(255) DEFAULT NULL,
`summaryComplete` bit(1) NOT NULL,
`targetId` bigint(20) DEFAULT NULL,
`targetSentance` text,
`type` varchar(255) NOT NULL,
`hasRead` bit(1) DEFAULT NULL,
`teamProject_id` bigint(20) DEFAULT NULL,
`user_id` bigint(20) DEFAULT NULL,
`usertoread_id` bigint(20) DEFAULT NULL,
`contentType` varchar(255) DEFAULT NULL,
`company_id` bigint(20) DEFAULT NULL,
`updated` int(1) unsigned DEFAULT NULL,
`feedType` varchar(255) DEFAULT NULL,
`extraInfo` varchar(255) DEFAULT NULL,
`extraTargetId` bigint(20) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `FKF49961B1B74A2DA5` (`user_id`),  
KEY `FKF49961B17CE9E5EF` (`teamProject_id`),
KEY `FKF49961B137B7D1B4` (`usertoread_id`),
KEY `FKF49961B13D5FD8EF` (`company_id`),
KEY `importance` (`importance`),
KEY `dateCreated` (`dateCreated`)
) ENGINE=InnoDB AUTO_INCREMENT=956498 DEFAULT CHARSET=utf8

Есть ли какой-либо способ остановить сканирование 2400 нечетных строк? Это сводка из журнала slow (с использованием mysqlsla):

 Count         : 61  (53.98%)
Time          : 523 s total, 8.57377 s avg, 6 s to 19 s max  (54.03%)
95% of Time : 456 s total, 8 s avg, 6 s to 14 s max
Lock Time (s) : 0 total, 0 avg, 0 to 0 max  (0.00%)
95% of Lock : 0 total, 0 avg, 0 to 0 max
Rows sent     : 34 avg, 21 to 51 max  (38.69%)
Rows examined : 3.49k avg, 40 to 8.89k max  (0.00%)
Users         :100.00% (61) of query, 100.00% (113) of all users

Спасибо

ОБНОВЛЕНИЕ 1: я добавил еще 2 индекса col (называемый feedquery), но, похоже, оптимизатор решил не использовать индекс:

 mysql> explain select id from feeditem feeditem0_ where feeditem0_.importance=0 and    feeditem0_.company_id=5045  G;
*************************** 1. row ***************************
       id: 1
  select_type: SIMPLE
    table: feeditem0_
     type: index_merge
possible_keys: FKF49961B13D5FD8EF,importance,feedquery
      key: FKF49961B13D5FD8EF,feedquery
  key_len: 9,14
      ref: NULL
     rows: 2753
    Extra: Using intersect(FKF49961B13D5FD8EF,feedquery); Using where; Using index

Если я ИГНОРИРУЮ индекс:

  explain select id from feeditem feeditem0_ ignore index (FKF49961B13D5FD8EF) where feeditem0_.importance=0 and  feeditem0_.company_id=5045  G;
 *************************** 1. row ***************************
       id: 1
 select_type: SIMPLE
    table: feeditem0_
     type: ref
 possible_keys: importance,feedquery
      key: feedquery
  key_len: 14
      ref: const,const
     rows: 8496
    Extra: Using where; Using index

Таблица:

 CREATE TABLE `feeditem` (
.....
PRIMARY KEY  (`id`),
KEY `FKF49961B1B74A2DA5` (`user_id`),
 KEY `FKF49961B17CE9E5EF` (`teamProject_id`),
KEY `FKF49961B137B7D1B4` (`usertoread_id`),
KEY `FKF49961B13D5FD8EF` (`company_id`),
KEY `importance` (`importance`),
KEY `dateCreated` (`dateCreated`),
KEY `feedquery` (`importance`,`company_id`)
) ENGINE=InnoDB AUTO_INCREMENT=999359 DEFAULT CHARSET=utf8

ОБНОВЛЕНИЕ 2: @Salman A

 SHOW profile;
 ---------------------- ---------- 
| Status               | Duration |
 ---------------------- ---------- 
| starting             | 0.000342 |
| checking permissions | 0.000024 |
| Opening tables       | 0.000053 |
| System lock          | 0.000027 |
| init                 | 0.000166 |
| optimizing           | 0.000068 |
| statistics           | 0.012869 |
| preparing            | 0.000202 |
| executing            | 0.000008 |
| Sending data         | 0.332767 |
| end                  | 0.000022 |
| query end            | 0.000009 |
| closing tables       | 0.000016 |
| freeing items        | 0.000040 |
| logging slow query   | 0.000005 |
| cleaning up          | 0.000014 |
 ---------------------- ----------

ibdata1 составляет около 1,5 ГБ

Ответ №1:

Общий ответ:

Не используйте, SELECT * если вам абсолютно не нужны все столбцы. Выберите только нужные вам столбцы.
Добавьте ORDER BY предложение или LIMIT не будет иметь особого смысла.
Создайте составной (т. Е. многоколоночный) индекс, который охватывает
- оба importance и company_id
- поле, которое вы хотите ORDER BY , в ожидаемом порядке
- любые дополнительные поля, которые вы хотите SELECT вернуть (взамен * )

Таким образом, механизм базы данных может искать прямые совпадения с вашим поиском с помощью одной операции поиска по индексу и охватывать сортировку, а также дополнительные столбцы непосредственно из индекса. Индекс содержит копию всех столбцов, которые он охватывает; если все запрошенные данные находятся в индексе, нет необходимости переходить к фактической таблице. Это повысит эффективность запросов.

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

РЕДАКТИРОВАТЬ 1: Кроме того, индекс влияет на скорость операций записи — запросы INSERT, UPDATE и DELETE будут немного медленнее из-за обслуживания индекса — в обмен на то, что SELECT будет быстрее. (спасибо за комментарий, @Thor84no)

РЕДАКТИРОВАТЬ 2: Если этот запрос является основным шаблоном использования для таблицы, и таблица не сильно меняется (этот момент очень важен!), вы могли бы подумать о создании кластеризованного индекса. Кластеризованный индекс указывает на физическую сортировку базовой таблицы, он не существует помимо базовой таблицы, как это делают другие индексы. Каждый раз, когда вы меняете определение кластеризованного индекса или добавляете / удаляете строки «между» существующими записями, фактические данные физически переупорядочиваются, т. Е. На диске, дорогостоящая операция, которой вы хотите избежать.

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

1. Часто снижение производительности записи является более серьезной проблемой, чем дополнительное дисковое пространство, занимаемое индексом, поэтому я бы упомянул и об этом. В остальном очень хороший ответ.

Ответ №2:

Стандартный select x from y where z (который у вас есть, только с несколькими условными обозначениями) является одним из наиболее эффективных запросов, которые вы можете выполнить. Единственное, что вы действительно могли бы добавить, — это индекс, включающий все столбцы, к которым вы запрашиваете; однако это повлияет на производительность при записи в эту таблицу. (А также высокое одноразовое попадание для заполнения указанного индекса в первый раз).

Если вы знаете, что один из столбцов в вашем запросе будет ограничивать результаты намного больше, чем другие, вы можете пойти на компромисс и добавить индекс только к этому запросу. Например. если поиск только строк с company_id = x гарантирует, что у вас осталось всего несколько строк для фильтрации, тогда может быть предпочтительнее иметь индекс только для этого столбца.

Ответ №3:

В принципе, поскольку каждый из ваших индексов содержит только одно из двух полей, на которые ссылаются в вашем предложении WHERE, движок должен извлекать записи, удовлетворяющие первой части, и записи, удовлетворяющие второй части (используя индексы «важность» и «FKF49961B13D5FD8EF» соответственно).

Простое правило для генерации индекса заключается в том, что вы хотите, чтобы индекс выглядел точно так же, как поля в предложении WHERE. Для этого вы могли бы создать индекс, используя «важность» и «company_id» в таком порядке. При этом будут выбраны точно соответствующие строки, и 2,4 тыс. строк больше не будут сканироваться.