Индексирование MySQL и использование filesort

Это связано с моей последней проблемой. Я сделал два новых столбца в таблице списков, один для скомпонованных представлений views_point (увеличивать каждые 100 просмотров) и один для публикации в дате publishedon_hourly (только по году-месяцу), чтобы сделать несколько уникальных значений.

Это моя новая таблица:

CREATE TABLE IF NOT EXISTS `listings` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `type` tinyint(1) NOT NULL DEFAULT '1',
  `hash` char(32) NOT NULL,
  `source_id` int(10) unsigned NOT NULL,
  `link` varchar(255) NOT NULL,
  `short_link` varchar(255) NOT NULL,
  `cat_id` mediumint(5) NOT NULL,
  `title` mediumtext NOT NULL,
  `description` mediumtext,
  `content` mediumtext,
  `images` mediumtext,
  `videos` mediumtext,
  `views` int(10) unsigned NOT NULL DEFAULT '0',
  `views_point` int(10) unsigned NOT NULL DEFAULT '0',
  `comments` int(11) DEFAULT '0',
  `comments_update` int(11) NOT NULL DEFAULT '0',
  `editor_id` int(11) NOT NULL DEFAULT '0',
  `auther_name` varchar(255) DEFAULT NULL,
  `createdby_id` int(10) NOT NULL,
  `createdon` int(20) NOT NULL,
  `editedby_id` int(10) NOT NULL,
  `editedon` int(20) NOT NULL,
  `deleted` tinyint(1) NOT NULL,
  `deletedon` int(20) NOT NULL,
  `deletedby_id` int(10) NOT NULL,
  `deletedfor` varchar(255) NOT NULL,
  `published` tinyint(1) NOT NULL DEFAULT '1',
  `publishedon` int(11) unsigned NOT NULL,
  `publishedon_hourly` int(10) unsigned NOT NULL DEFAULT '0',
  `publishedby_id` int(10) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `hash` (`hash`),
  KEY `views_point` (`views_point`),
  KEY `listings` (`publishedon_hourly`,`published`,`cat_id`,`source_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED AUTO_INCREMENT=365513 ;

Когда я запускаю такой запрос:

SELECT *
FROM listings
WHERE (`publishedon_hourly` BETWEEN
       UNIX_TIMESTAMP( '2015-09-5 00:00:00' )
       AND UNIX_TIMESTAMP( '2015-10-5 12:00:00' ))
  AND (published =1)
  AND cat_id IN ( 1, 2, 3, 4, 5 )
ORDER BY by `views_point` DESC
LIMIT 10

Он отлично работает и это объяснение:

Но когда я изменяю диапазон дат из месяца в день следующим образом:

SELECT *
FROM listings
WHERE (`publishedon_hourly` BETWEEN
       UNIX_TIMESTAMP( '2015-09-5 00:00:00' )
       AND UNIX_TIMESTAMP( '2015-09-5 12:00:00' ))
  AND (published =1)
  AND cat_id IN ( 1, 2, 3, 4, 5 )
  ORDER BY `views_point` DESC
  LIMIT 10

Затем запрос становится медленным и появляется файл. Кто-нибудь знает причину и как ее исправить?

образец данных (из медленного запроса)

INSERT INTO `listings` (`id`, `type`, `hash`, `source_id`, `link`, `short_link`, `cat_id`, `title`, `description`, `content`, `images`, `videos`, `views`, `views_point`, `comments`, `comments_update`, `editor_id`, `auther_name`, `createdby_id`, `createdon`, `editedby_id`, `editedon`, `deleted`, `deletedon`, `deletedby_id`, `deletedfor`, `published`, `publishedon`, `publishedon_hourly`, `publishedby_id`) VALUES
(94189, 1, '44a46d128ce730c72927b19c445ab26e', 8, 'http://Larkin.com/sapiente-laboriosam-omnis-tempore-aliquam-qui-nobis', '', 5, 'And Alice was more and.', 'So they got settled down again very sadly and quietly, and.', 'Dormouse. ''Fourteenth of March, I think it so quickly that the Gryphon only answered ''Come on!'' and ran the faster, while more and more sounds of broken glass, from which she concluded that it was looking down at them, and then a voice sometimes choked with sobs, to sing this:-- ''Beautiful Soup, so rich and green, Waiting in a natural way. ''I thought you did,'' said the Dormouse, without considering at all what had become of it; and as it.', NULL, '', 200, 19700, 0, 0, 0, 'Max', 0, 1441442729, 0, 0, 0, 0, 0, '', 1, 1441442729, 1441440000, 0),
(19030, 1, '3438f6a555f2ce7fdfe03cee7a52882a', 3, 'http://Romaguera.com/voluptatem-rerum-quia-sed', '', 2, 'Dodo said, ''EVERYBODY.', 'I wish I hadn''t to bring but one; Bill' got the.', 'I wonder what they''ll do well enough; don''t be particular--Here, Bill! catch hold of this remark, and thought to herself. (Alice had no idea what Latitude or Longitude I''ve got to the confused clamour of the other queer noises, would change to dull reality--the grass would be offended again. ''Mine is a long way. So she went on. ''I do,'' Alice said nothing; she had succeeded in curving it down ''important,'' and some were birds,) ''I suppose so,''.', NULL, '', 800, 19400, 0, 0, 0, 'Antonio', 0, 1441447567, 0, 0, 0, 0, 0, '', 1, 1441447567, 1441447200, 0),
(129247, 4, '87d2029a300d8b4314508786eb620a24', 10, 'http://Ledner.com/', '', 4, 'I ever saw one that.', 'The Cat seemed to be a person of authority among them,.', 'I BEG your pardon!'' she exclaimed in a natural way again. ''I wonder what was the same height as herself; and when she looked down at her feet as the question was evidently meant for her. ''I can tell you my history, and you''ll understand why it is I hate cats and dogs.'' It was all dark overhead; before her was another long passage, and the blades of grass, but she had sat down a very little! Besides, SHE' she, and I''m sure I have dropped them, I wonder?'' As she said to herself; ''his eyes are so VERY tired of being all alone here!'' As she said to itself ''Then I''ll go round a deal.', NULL, '', 1000, 19100, 0, 0, 0, 'Drake', 0, 1441409756, 0, 0, 0, 0, 0, '', 1, 1441409756, 1441407600, 0),
(264582, 2, '5e44fe417f284f42c3b10bccd9c89b14', 8, 'http://www.Dietrich.info/laboriosam-quae-eaque-aut-dolorem', '', 2, 'Alice asked in a very.', 'THINK; or is it directed to?'' said the Mock Turtle,.', 'I can listen all day to such stuff? Be off, or I''ll have you executed.'' The miserable Hatter dropped his teacup and bread-and-butter, and then unrolled the parchment scroll, and read as follows:-- ''The Queen will hear you! You see, she came upon a little of the players to be lost, as she spoke--fancy CURTSEYING as you''re falling through the wood. ''It' the stupidest tea-party I.', NULL, '', 800, 18700, 0, 0, 0, 'Kevin', 0, 1441441192, 0, 0, 0, 0, 0, '', 1, 1441441192, 1441440000, 0),
(44798, 1, '567cc77ba88c05a4a805dc667816a30c', 14, 'http://www.Hintz.com/distinctio-nulla-quia-incidunt-facere-reprehenderit-sapiente-sint.html', '', 5, 'The Cat seemed to Alice.', 'And the moral of that is--"Be what you mean,'' said Alice..', 'Alice very politely; but she felt very lonely and low-spirited. In a little faster?" said a sleepy voice behind her. ''Collar that Dormouse,'' the Queen said severely ''Who is it directed to?'' said the Footman, and began staring at the Footman' head: it just at first, but, after watching it a violent blow underneath her chin: it had no pictures or conversations in it, ''and what is the capital of Paris, and Paris is the same thing, you know.'' ''I DON''T.', NULL, '', 300, 17600, 0, 0, 0, 'Rocio', 0, 1441442557, 0, 0, 0, 0, 0, '', 1, 1441442557, 1441440000, 0),
(184472, 1, 'f852e3ed401c7c72c5a9609687385f65', 14, 'https://www.Schumm.biz/voluptatum-iure-qui-dicta-modi-est', '', 4, 'Alice replied, so.', 'I should have liked teaching it tricks very much, if--if.', 'NEVER come to the Dormouse, not choosing to notice this question, but hurriedly went on, ''What' your name, child?'' ''My name is Alice, so please your Majesty,'' said Two, in a great thistle, to keep back the wandering hair that WOULD always get into her face. ''Wake up, Alice dear!'' said her sister; ''Why, what a dear quiet thing,'' Alice went on, spreading out the answer to shillings and pence. ''Take off your hat,'' the King had said that day. ''No, no!'' said the Gryphon. ''They can''t have anything to say, she simply bowed, and took the watch and looked at it again: but he could.', NULL, '', 900, 17600, 0, 0, 0, 'Billy', 0, 1441407837, 0, 0, 0, 0, 0, '', 1, 1441407837, 1441407600, 0),
(344246, 2, '09dc73287ff642cfa2c97977dc42bc64', 6, 'http://www.Cole.com/sit-maiores-et-quam-vitae-ut-fugiat', '', 1, 'IS the use of a.', 'And when I learn music.'' ''Ah! that accounts for it,'' said.', 'Gryphon answered, very nearly carried it out loud. ''Thinking again?'' the Duchess by this time.) ''You''re nothing but a pack of cards, after all. I needn''t be so stingy about it, you know--'' ''But, it goes on "THEY ALL RETURNED FROM HIM TO YOU,"'' said Alice. ''Call it what you mean,'' the March Hare, ''that "I breathe when I breathe"!'' ''It IS the same side of WHAT? The other guests had taken his watch out of it, and talking over its head. ''Very uncomfortable for the first to speak. ''What size do you like to go and get.', NULL, '', 600, 16900, 0, 0, 0, 'Enrico', 0, 1441406107, 0, 0, 0, 0, 0, '', 1, 1441406107, 1441404000, 0),
(19169, 1, '116c443b5709e870248c93358f9a328e', 12, 'http://www.Gleason.com/et-vero-optio-exercitationem-aliquid-optio-consectetur', '', 4, 'Let this be a lesson to.', 'Sir, With no jury or judge, would be very likely to eat.', 'I wonder who will put on your head-- Do you think I can find them.'' As she said this, she was quite out of sight before the end of every line: ''Speak roughly to your little boy, And beat him when he sneezes; For he can EVEN finish, if he had never heard of such a subject! Our family always HATED cats: nasty, low, vulgar things! Don''t let him know she liked them best, For this must ever be A secret, kept from all the creatures wouldn''t be so kind,'' Alice replied, so eagerly that the way I want to get very tired of being upset, and their curls got entangled together. Alice was not a regular rule: you invented it just grazed his nose, you know?'' ''It' the thing Mock Turtle would be only.', NULL, '', 700, 16800, 0, 0, 0, 'Unique', 0, 1441407961, 0, 0, 0, 0, 0, '', 1, 1441407961, 1441407600, 0),
(192679, 1, '06a33747b5c95799034630e578e53dc5', 10, 'http://www.Pouros.com/qui-id-molestias-non-dolores-non', '', 5, 'Rabbit just under the.', 'KNOW IT TO BE TRUE--" that' the jury-box,'' thought Alice,.', 'Mock Turtle, who looked at Two. Two began in a hoarse, feeble voice: ''I heard every word you fellows were saying.'' ''Tell us a story.'' ''I''m afraid I can''t tell you how it was too dark to see what I should say "With what porpoise?"'' ''Don''t you mean by that?'' said the King; and as it was indeed: she was now more than Alice could not make out exactly what they WILL do next! As for pulling me out of court! Suppress him! Pinch him! Off with his head!"'' ''How dreadfully savage!'' exclaimed Alice. ''That' the first witness,'' said the Duchess. ''Everything' got a moral, if only you can find it.'' And she squeezed herself up and ran the faster, while more and more faintly came, carried on the end of every line:.', NULL, '', 800, 15900, 0, 0, 0, 'Gene', 0, 1441414720, 0, 0, 0, 0, 0, '', 1, 1441414720, 1441411200, 0),
(251878, 4, '3eafacc53f86c8492c309ca2772fbfe9', 5, 'http://www.Schinner.info/tempora-et-est-qui-nulla', '', 2, 'NOT!'' cried the Mouse,.', 'Twinkle, twinkle--"'' Here the Queen till she heard the.', 'Alice and all of them even when they hit her; and the sounds will take care of the gloves, and she dropped it hastily, just in time to begin at HIS time of life. The King' argument was, that she had forgotten the Duchess to play croquet with the Dormouse. ''Write that down,'' the King added in an undertone to the fifth bend, I think?'' ''I had NOT!'' cried the Mouse, sharply and very neatly and simply arranged; the only difficulty was, that if something wasn''t done about it in less than a pig, my dear,'' said Alice, a little wider. ''Come, it' pleased so far,'' said the Gryphon. ''Do you play croquet with the glass table and the King hastily said, and went by without noticing her. Then followed the Knave ''Turn them over!'' The Knave of.', NULL, '', 500, 15900, 0, 0, 0, 'Demarcus', 0, 1441414681, 0, 0, 0, 0, 0, '', 1, 1441414681, 1441411200, 0);

Ответ 1

Кажется, что какая-то база данных новостей, поэтому старайтесь думать о том, чтобы каждый месяц архивировать какие-то новости.

Подумайте об этом решении, это не самое лучшее, но может помочь

Добавьте эти столбцы в таблицу списки.
- publishedmonth tinyint (2) UNSIGNED NOT NULL DEFAULT '0'
- publishedyear tinyint (2) НЕОПРЕДЕЛЕН НЕ НУЛЕЙ ПО УМОЛЧАНИЮ '0'
- publishedminute mediumint (6) НЕОПРЕДЕЛЕН НЕ НУЛЕЙ ПО УМОЛЧАНИЮ '0'
Добавьте этот INDEXING KEY в таблицу листинги
- ADD KEY published_month (publishedmonth,publishedyear,publishedminute)
Во время вставки используйте эти значения из кода PHP
- опубликованныйmonth будет date('n')
- опубликованный год будет иметь date('y')
- опубликованныйminute будет date('jHi')

Удалите огромное количество записей, затем проверьте этот запрос

SELECT * FROM listings WHERE publishedmonth = 2 AND publishedyear = 17 ORDER BY publishedminute

Ответ 2

В вашем первом запросе ORDER BY выполняется с помощью views_point INDEX, поскольку он использовался в части WHERE запроса, и поэтому в MySQL можно использовать для сортировки.

Во втором запросе MySQL решает часть WHERE с использованием другого индекса listing_pcs. Это не может использоваться для удовлетворения условия ORDER BY. Вместо этого MySQL использует filesort, что является лучшим вариантом, если индекс не может быть использован.

MySQL использует индексы только для сортировки, если индекс такой же, как и в условии WHERE. Это руководство означает:

В некоторых случаях MySQL не может использовать индексы для разрешения ORDER BY, хотя он по-прежнему использует индексы для поиска строк, которые соответствуют предложению WHERE. Эти случаи включают следующее:

Ключ, используемый для извлечения строк, не совпадает с ключом, используемым в ORDER BY:
SELECT * FROM t1 WHERE key2=constant ORDER BY key1;

Итак, что вы можете сделать:

Попробуйте увеличить параметр конфигурации sort_buffer_size, чтобы сделать filesorting максимально эффективным. Большие результаты, которые слишком велики для буфера сортировки, заставляют MySQL разбивать сортировку на куски, что медленнее.
Заставить MySQL выбрать другой индекс. Стоит отметить, что разные версии MySQL по-разному выбирают индексы по умолчанию. Версия 5.1, к примеру, довольно плохая, поскольку оптимизатор запросов был сильно переписан для этой версии и нуждался в большой утонченности. Версия 5.6 довольно хороша.
```
SELECT *
FROM listings
FORCE INDEX (views_point)
WHERE (`publishedon_hourly` BETWEEN
       UNIX_TIMESTAMP( '2015-09-5 00:00:00' )
       AND UNIX_TIMESTAMP( '2015-09-5 12:00:00' ))
  AND (published =1)
  AND cat_id IN ( 1, 2, 3, 4, 5 )
ORDER BY `views_point` DESC
LIMIT 10
```

Ответ 3

Очень странное поведение. Трудно понять, почему views_point не будет использоваться для операции сортировки без видимых данных. Вы можете попытаться дать подсказку индекса для MySQL, чтобы использовать views_point для сортировки вроде этого.

SELECT * FROM listings
  USE INDEX FOR ORDER BY (`views_point`)
WHERE
  (
    `publishedon_hourly` BETWEEN UNIX_TIMESTAMP( '2015-09-5 00:00:00' )
    AND UNIX_TIMESTAMP( '2015-09-5 12:00:00' )
  )
  AND (published =1)
  AND cat_id IN ( 1, 2, 3, 4, 5 )
ORDER BY `views_point` DESC LIMIT 10

Ответ 4

EXPLAIN говорит listings_pcs, но SHOW CREATE TABLE не перечисляет этот индекс. Мы что-то упускаем?
Не используйте SELECT *, если вам нужны только несколько столбцов. В частности, столбцы TEXT предотвратят одну форму ускорения производительности во время запроса.
Подзапросы для разработки части запроса обычно показывают, что происходит. Тем не менее, в вашем случае (много MEDIUMTEXT изъято и использование LIMIT), может быть полезно сначала получить идентификаторы в подзапросе, а затем извлечь громоздкие столбцы. ( "Lazy eval" ) См. Ниже.
Значение диапазона (publishedon_hourly) лучше последнего, а не первого, в индексе.
Запуск индекса с столбцом = (published) обычно лучше всего.
Оптимизатор выбирает, иногда неправильно, для фокусировки на ORDER BY вместо WHERE. (В вашем случае также не очень продуктивно).
INDEX(published, views_point) может избежать сортировки, помогая некоторым с WHERE.
Наличие флага (published), которое всегда проверяется в запросах, добавляет сложности и неэффективности схемы.
BETWEEN включен, поэтому второй запрос фактически сканирует 12 часов плюс одна секунда.
Разделение даты на год + месяц + день обычно больно больше, чем помогает.
Не устанавливайте sort_buffer_size больше, чем, скажем, 1% ОЗУ. В противном случае вы можете столкнуться с другими проблемами.
FORCE INDEX может помочь сегодня, но тогда больно завтра, когда константы меняются. Предостережение emptor.
Часто лучше добавлять "click_count" или "нравится" или "вверх" в отдельную таблицу. Это отделяет быстро меняющиеся счетчики от громоздких, относительно статических данных. Следовательно, между ними меньше помех.
Если вы делаете это выше, просто удалите опубликованные строки из таблицы счетчиков, тем самым упростив несколько вещей.
Большинство людей порицают filesort, но обычно это другие вещи, которые являются злодеями - в вашем случае - количество и размер строк.
Предоставьте EXPLAIN FORMAT=JSON SELECT ...; могут быть интересные подсказки.
Ваши выводы достаточно странны, чтобы гарантировать заполнение ошибки на bugs.mysql.com.

Я бы добавил эти индексы с столбцами в указанном порядке и посмотрел, что выбирает Оптимизатор:

INDEX(published, views_point)  -- aiming at the ORDER BY, plus picking up '='
INDEX(published, cat_id, publishedon_hourly) -- possibly the best for WHERE

Или, может быть, "ленивый eval"

SELECT  L.*
    FROM  listings AS L
    JOIN (
        SELECT  id
            FROM  listings
            WHERE  `publishedon_hourly` BETWEEN UNIX_TIMESTAMP(...)
                                            AND UNIX_TIMESTAMP(...) 
              AND  published = 1
              AND  cat_id IN ( 1, 2, 3, 4, 5 )
            ORDER BY  `views_point` DESC
            LIMIT  10
         ) AS s  ON L.id = s.id
ORDER BY views_point DESC

-- with
INDEX(published, cat_id, publishedon_hourly, views_point, id)

Примечания:

Подзапросом будет "Использовать индекс"; т.е. индекс покрывает.
Будет два типа файлов. Один из них находится в подзапросе, но работает с индексом, а не с объемными текстами. И один - всего 10 строк, хотя и громоздкий.

Ответ 5

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

Вы не захотите его ждать, вы можете изменить свой индекс listing_pcs. У него есть source_id, но вы его не используете. Так почему бы не заменить его на view_points?

KEY `listings` (`publishedon_hourly`,`published`,`point`,`cat_id`)

Также использование tinyint (1) не очень удобно для скорости или экономии места. Он по-прежнему занимает один полный байт. И тот же средний уровень (5) занимает 3 байта. Объедините deleted, type, catid и published в один столбец и поместите индекс в один столбец.