pt-query-digestはPerl！

Slide 1

Slide 1 text

QURVFSZEJHFTU͸1FSMʂʂ :"1$/BHPZB5JOZ େ஥ೳ࢙BLBJEPOL

Slide 2

Slide 2 text

ࣗݾ঺հ w େ஥ೳ࢙BLBJEPOL w גࣜձࣾ͸ͯͳ w ΠϯϑϥͱϚωʔδϟۀΛ΍͍ͬͯ·͢

Slide 3

Slide 3 text

*/%&9ͷޮ͍͍ͯͳ͍  ΫΤϦΛ୳͢ͷ͕झຯ

Slide 4

Slide 4 text

ޮ͍ͯͳ͍ΫΤϦൃݟʂ /* lib/Giga/Core/Repository/SeriesComment.pm at line 200 */ SELECT COUNT(*) FROM `series_comment` WHERE (`is_public` = '1') AND (`series_id` = ‘10834108156628842496');  +----+-------------+-||-+-------+------------------------------------+ | id | select_type | || | rows | Extra | +----+-------------+-||-+-------+------------------------------------+ | 1 | SIMPLE | || | 30872 | Using index condition; Using where | +----+-------------+-||-+-------+------------------------------------+ 1 row in set (0.05 sec)

Slide 5

Slide 5 text

ͪΐͬͱҧ͏ͷ͕͋Δͧʜʜʁ /* lib/Giga/Core/Repository/SeriesComment.pm at line 200 */ SELECT COUNT(*) FROM `series_comment` WHERE (`series_id` = ‘10834108156628842496') AND (`is_public` = '1')\G   /* lib/Giga/Core/Repository/SeriesComment.pm at line 200 */ SELECT COUNT(*) FROM `series_comment` WHERE (`is_public` = '1') AND (`series_id` = '10834108156628842496')\G

Slide 6

Slide 6 text

)BTI3BOEPNJ[BUJPO͡ΌΜ

Slide 7

Slide 7 text

આ໌͠Α͏ʂ w )BTI3BOEPNJ[BUJPOͱ͸ w ϋογϡ͝ͱʹLFZT ΍WBMVFT FBDI ͕ϥϯμϜͳॱ൪ʹͳ Δ͜ͱ w ಉ͡಺༰ LFZ WBMVF ͷϋογϡͰ΋ҧ͏ΦϒδΣΫτͳΒҧ͏ॱ ংͰฦΔ w 1FSMWͰ࠾༻͞Ε·ͨ͠

Slide 8

Slide 8 text

࣮ίʔυ sub get_public_comments_count { args my $class => 'ClassName', my $series => 'Giga::Core::Model::Series', ; my $core_db = Giga::DB->new->core; return $core_db->count( 'series_comment', '*', +{ series_id => $series->id, is_public => 1, }, ); }

Slide 9

Slide 9 text

42-.BLFSͷ࣮૷ my $w = $self->new_condition; my @w = ref $where eq 'ARRAY' ? @$where : %$where; while (my ($col, $val) = splice @w, 0, 2) { $w->add($col => $val); }

Slide 10

Slide 10 text

ͭ·Γ͜͏͢Δͱղܾ return $core_db->count( 'series_comment', '*', - +{ + [ series_id => $series->id, is_public => 1, - }, + ], );

Slide 11

Slide 11 text

'BU$PNNBšŘŠŖʂ return $core_db->count( 'series_comment', '*', - +{ + [ series_id => $series->id, is_public => 1, - }, + ], );

Slide 12

Slide 12 text

શ෦΍Δͷμϧ͍ͳ͊

Slide 13

Slide 13 text

QURVFSZEJHFTUͰ͸্खʹ ॲཧ͞ΕͯͨΓ͢Δͷ͔ͳʁ

Slide 14

Slide 14 text

1FSM͡ΌΜ #!/usr/bin/env perl # This program is part of Percona Toolkit: http://www.percona.com/soft # See "COPYRIGHT, LICENSE, AND WARRANTY" at the end of this file for l # notices and disclaimers. use strict; use warnings FATAL => 'all'; # This tool is "fat-packed": most of its dependent modules are embedde

Slide 15

Slide 15 text

ߦ $ wc pt-query-digest 16920 64009 527581 pt-query-digest

Slide 16

Slide 16 text

ߦ਺ͷଟ͍ཧ༝͸෼͔ͬͨ # This tool is "fat-packed": most of its dependent modules are embedded # in this file. Setting %INC to this file for each module makes Perl aware # of this so it will not try to load the module from @INC. See the tool's # documentation for a full list of dependencies. wQBDLBHFQU@RVFSZ@EJHFTU͸ߦ໨ ͔Βͷߦɻ͜ΕͳΒಡΊΔ

Slide 17

Slide 17 text

pOHFSQSJOU͸2VFSZ3FXSJUFSʹ ࣮૷͞Ε͍ͯΔ $query =~ s/\\["']//g; # quoted strings $query =~ s/".*?"/?/sg; # quoted strings $query =~ s/'.*?'/?/sg; # quoted strings $query =~ s/\bfalse\b|\btrue\b/?/isg; # boolean values ... $query =~ s/\A\s+//; # Chop off leading whitespace chomp $query; # Kill trailing whitespace $query =~ tr[ \n\t\r\f][ ]s; # Collapse whitespace $query = lc $query; $query =~ s/\bnull\b/?/g; # Get rid of NULLs $query =~ s{ # Collapse IN and VALUES lists \b(in|values?)(?:[\s,]*$[\s?,]*$)+ } {$1(?+)}gx;

Slide 18

Slide 18 text

࣮ࡍʹQURVFSZEJHFTUͰ΋ ॏෳͯ͠ݕग़͞ΕΔ͜ͱ͕  ෼͔ͬͨ

Slide 19

Slide 19 text

࣮຿Ͱ͸42-.BLFSΛ্ॻ͖ sub select_query { my ($self, $table, $fields, $where, $opt) = @_; if (ref($where) eq 'HASH') { $where = [map { ($_, $where->{$_}) } (sort keys %$where)]; } $self->SUPER::select_query($table, $fields, $where, $opt); }

Slide 20

Slide 20 text

Φν wզʑ͸%#*Y)BOEMFSͰRVFSZΛݺΜͩߦΛ42-ʹ ؚΊ͍ͯΔͷͰɺίϝϯτ෦෼ΛऔΓग़ͤ͹े෼ͩͬͨ /* lib/Giga/Core/Repository/SeriesComment.pm at line 200 */ SELECT COUNT(*) FROM `series_comment` WHERE (`is_public` = '1') AND (`series_id` = '10834108156628842496')\G

Slide 21

Slide 21 text

42-ΛோΊΔͱ͖ͷྑ͍෢ثΛ खʹೖΕͨ QFSDPOBUPPMLJUMJCҎԼʹ  ৭ʑ͋Δɻ &YQMBJO"OBMZ[FS΍  5DQ%VNQ1BSTFS౳ศརͦ͏ʂ