MySQL 인덱싱 및 파일 정렬 사용

MySQL 인덱싱 및 파일 정렬 사용

이것은 나의 마지막 문제와 관련이 있습니다.목록 테이블에 두 개의 열을 새로 만들었습니다. 하나는 구성된 보기를 위한 것입니다.views_point(100번의 보기마다 increment) 및 날짜에 게시하기 위한 것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''s 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''S 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''s 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''s 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''s 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''s 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''s 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''s the first witness,'' said the Duchess. ''Everything''s 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''s 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''s 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);

첫 번째 쿼리에서 ORDER BY는 다음을 사용하여 수행됩니다.views_pointINDEX. 쿼리의 WHERE 부분에서 사용되었기 때문에 MySQL에서 정렬에 사용할 수 있습니다.

두 번째 쿼리에서 MySQL은 다른 인덱스를 사용하여 WHERE 부분을 해결합니다.listing_pcs을 만족시키는 데 할 수 조건별 ORDER를 만족시키는 데 사용할 수 없습니다.MySQL은 대신 filsort를 사용하는데 인덱스를 사용할 수 없는 경우 가장 좋은 옵션입니다.

MySQL은 인덱스를 사용하여 인덱스가 WHERE 조건에서 사용된 것과 동일한지 여부만 정렬합니다.설명서의 의미는 다음과 같습니다.

MySQL은 인덱스를 사용하여 WHERE 절과 일치하는 행을 찾지만 인덱스를 사용하여 ORDER BY를 해결할 수 없는 경우도 있습니다.이러한 경우에는 다음이 포함됩니다.

행을 가져오는 데 사용된 키가 ORDER BY에 사용된 키와 같지 않습니다.

SELECT * FROM t1 WHERE key2=constant ORDER BY key1;

그럼 할 수 있는 일은?

1. 를 늘립니다.sort_buffer_size파일 정렬을 가능한 한 효과적으로 만드는 config 옵션.정렬 버퍼에 비해 너무 큰 결과는 MySQL이 정렬을 더 느린 청크로 분해하게 합니다.

2. 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
   

일종의 뉴스 데이터베이스인 것 같으니, 매달 일종의 뉴스 아카이브를 만드는 것에 대해 생각해 보세요.

이 해결책에 대해 생각해보세요. 최선은 아니지만 도움이 될 수도 있습니다.

• 이 열을 목록 테이블에 추가
  • publishedmonth되지 않은 tinyint(2)은 NULL DEFAULT '0'
  • publishedyear되지 않은 tinyint(2)은 NULL DEFAULT '0'
  • publishedminute되지 않은 mediumint(6)은 NULL DEFAULT '0'

• 목록 테이블에 이 인덱싱 키 추가

  • ADD KEY published_month (publishedmonth,publishedyear,publishedminute)

• 삽입하는 동안 PHP 코드에서 이 값들을 사용합니다.

  • 발행된 월의 유언서date('n')
  • 출판된 연도의 유언장이 가지고 있는date('y')
  • 출판된 분간의 유언서.date('jHi')

엄청난 수의 레코드를 덤프한 다음 이 쿼리를 테스트합니다.

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

• EXPLAIN말한다listings_pcs , .SHOW CREATE TABLE는 해당 인덱스를 나열하지 않습니다.우리가 놓친게 있나요?
• 사용하지않습니다.SELECT *몇 개의 열만 있으면 됩니다..TEXTcolumns는 쿼리 중 한 가지 형태의 성능 속도 향상을 방지합니다.
• 쿼리의 일부를 해결하기 위한 하위 쿼리는 일반적으로 상황을 보여줍니다.하지만, 당신의 경우에는 (많은MEDIUMTEXT가져오기, 사용하기LIMIT), 먼저 ID를 서브쿼리로 가져온 다음 부피가 큰 열을 가져오는 것이 효율적일 수 있습니다. ("Lazy eval.
• ()publishedon_hourly첫 에 더 는 인덱스에서 첫 번째가 아니라 마지막에 더 나습니다.
• 인덱스 시작하기=열()published가 보통 가장 가 보통 가장 좋습니다.
• Optimizer(옵티마이저)가 때로는 잘못 선택하여 다음 항목에 초점을 맞춥니다.ORDER BY대신에WHERE 쪽도 (고객님의 경우 어느 쪽도 그다지 생산적이지 않습니다.)
• INDEX(published, views_point) 일부 사람들을 도와주면서 그런 종류를 피할 수도 있습니다.WHERE.
• 깃발을 가지는 것(published쿼리에서 항상 테스트되는 것은 스키마의 복잡성과 비효율을 가중시킵니다.
• BETWEEN포괄적이므로 두 번째 쿼리는 실제로 12시간과 1초를 스캔합니다.
• 날짜를 일년+월+일로 나누는 것은 보통 도움이 되는 것보다 더 마음이 아픕니다.
• 설정 안 함sort_buffer_sizeRAM의 1%보다 큽니다.그렇지 않으면 다른 문제가 발생할 수 있습니다.
• FORCE INDEX오늘은 도움이 되겠지만 상수가 바뀌면 내일은 상처가 됩니다.유혹자에게 주의.
• 종종 "click_count", "like" 또는 "upvote"를 별도의 테이블에 넣는 것이 좋습니다.이것은 빠르게 변화하는 카운터와 부피가 크고 상대적으로 정적인 데이터를 구분합니다.따라서 둘 사이의 간섭이 줄어듭니다.
• 위의 작업을 수행할 경우 게시되지 않은 행을 카운터 테이블에서 간단히 제거하면 몇 가지 사항이 간소화됩니다.
• 대부분의 사람들은 그것을 비난합니다.filesort, 하지만 보통 다른 것들이 악당입니다. 여러분의 경우에는 줄의 수나 크기 같은 것들 말이죠.
• 제공해주세요EXPLAIN FORMAT=JSON SELECT ...; 흥미로운 단서가 있을지도 모릅니다.
• 귀하의 발견은 bugs.mysql.com 에서 버그를 메우는 것을 보증하기에 충분히 이상합니다.

주어진 순서대로 열을 사용하여 인덱스를 추가하고 Optimizer가 선택하는 항목을 확인합니다.

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

아니면, 어쩌면, "게으른 평가".

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)

주의:

• 하위 쿼리는 "색인 사용" 즉, 색인이 포함됩니다.
• 파일 종류는 두 가지입니다.하나는 서브쿼리에 있지만, 방대한 텍스트가 아니라 인덱스로 작업합니다.그리고 하나는 부피는 크지만 열 줄밖에 되지 않습니다.

이상한 행동.이유를 알 수 없습니다.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

쿼리 최적화 도구가 완벽하지 않습니다.이것은 그것이 잘못된 결정을 내리는 경우 중 하나입니다.일부 경계선의 경우에 발생합니다.테이블의 데이터가 작은 양이라도 변경되면 다른 인덱스를 사용하여 더 빠른 쿼리를 실행할 수 있습니다.

기다리지 않고 listing_pcs 인덱스를 변경할 수 있습니다.source_id가 있지만 사용하고 있지 않습니다.그렇다면 view_points로 대체하는 것은 어떨까요?

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

또한 작은 int(1)를 사용하여 속도를 높이거나 공간을 절약하는 데 많이 사용되지 않습니다.아직 1바이트가 필요합니다.그리고 같은 mediumint(5)는 3바이트가 걸립니다.결합하다deleted,type,catid그리고.published하나의 열에 인덱스를 입력합니다.

언급URL : https://stackoverflow.com/questions/32952051/mysql-indexing-and-using-filesort