Upgrade to Pro — share decks privately, control downloads, hide ads and more …

introduction about MySQL

introduction about MySQL

MySQLの基本的な操作やRDBとしての設計・利用などはできるがMySQLのソフトウェアとしての特性をまだちゃんと理解していない方向けMySQLの特徴が伝わるように作りました。

Arakaki Yuji

June 15, 2019
Tweet

More Decks by Arakaki Yuji

Other Decks in Programming

Transcript

  1. Goal
    u MySQL!RDBM
    MySQLNS!CHM
    @
    ?O
    u 24%6=+,71='095:)7."=' MySQLNS
    !CHDG JF!KP
    u MySQLQ+0;*$=*= BAInnoDB>I
    u TRL E03-&#=/-&+09=(&)8=<-&
    U

    View full-size slide


  2. u MySQL

    u
    u
    u

    View full-size slide

  3. MySQL!&
    u +($*%() #0
    u '+"1. = 1) #
    u ) #1./ Linuxps%+# -,
    u SHOW PROCESSLIST %+#,
    u
    PostgreSQL %($*0
    u '+"11. = 1$*

    View full-size slide

  4. MySQL"
    u !#$$
    u MySQL%+2
    &1()0,
    u /!#$$. -3'*.
    u InnoDB
    u MyISAM
    u Memory
    u MyRocks

    View full-size slide

  5. -!"61!
    u InnoDB%*/81! 321!
    -!"61!
    u -!"61!B$..)('0 '7-,&"
    :=
    u 0 '&":=391!5394<0!
    +1!;

    View full-size slide

  6. +)+!
    u (0+! +!
    u +)+! )&$#(0+! 6,

    u +)+! 3 1:% "
    .(0
    +! 1: *'+5
    u (0+! 1:4 79228-/

    View full-size slide

  7. )-/!/(& "
    u /(& ";< -79:
    u /(& ">?
    u '*. #" 2=79:
    u ,"$5/(& " 6@10'*.,+<3
    )-/!/(& "=4 79:
    u #/%-/(& " 6@10#/%-/(& "A ,
    +< ,"$5/(& ",+<
    8)-/!/
    (& "

    View full-size slide


  8. u

    u READ UNCOMMITTED
    u READ COMMITTED
    u REPEATABLE READ
    u SERIALIZABLE
    u MySQL
    u MVCC

    View full-size slide

  9. !% %&-#"
    u !% %&-)%#
    u .0
    &- $(/
    %,'
    u %&-)#(/
    4&-#"
    (SQL+*)
    u READ UNCOMMITTED
    u READ COMMITED
    u REPEATABLE READ
    u SERIALIZABLE

    View full-size slide

  10. READ UNCOMMITED
    u %
    %)* (*035%)* (*,

    u %
    $!6 4
    u -./1+2 "'!

    View full-size slide

  11. READ COMMITTED
    u ).1" #-17 ?E
    !+&)
    ).1" #-1FN
    u ).1" #-1<FN!+&)K).1" #-1
    7
    u 5J0*/ MG
    u 8$'),1)2=@3
    L(% HGCA
    u A).1" #-1@32;B).1" #-1 !+&)64A<
    B>(%B!+&):9OIBDGCA

    View full-size slide

  12. REPEATABLE READ
    u MySQL$& *%%),"!#(,/D+'*
    u READ UNCOMMITED FB

    C

    u %),"!#(, I:- 2%),"!#(,=
    EI:21 8@
    u
    ;>G
    u KH-7J 4A%),"!#(, KH5 -L3
    0.2KH7J5 - <
    u InnoDB0.MVCC9;>?6

    View full-size slide

  13. SERIALIZABLE
    u 80*/5B>
    u '-2#"$,2A467
    '-2#"$,2=<:CF
    )2'+.(@
    u E;31&"?9

    u % +!'1&"A4G6D

    View full-size slide

  14. MySQLW7BAX: MVCC
    u MultiVesion Concurrency Control[
    u ;DTM,)(.*0&2*-"IC SF?I
    u InnoDB98
    u 7 \!R"JZ
    u 7 IC!
    &(+1/'26PY
    u 7 VU!
    &(+1/'26PY
    u &(+1/'26PY-36%$&26 >K EL
    u N-36%$&26>K;D&(+1/'26PY"HQ
    u -36%$&26<$#47ICVU&(+1/'26PY"
    ,)"@G]I
    u -36%$&26&(+1/'26=&(+1/'267":



    "5*$O


    View full-size slide

  15. MySQL7'.-8: MVCC
    u "
    u 2%3,.96 1


    !& &(5$#+/
    u )4!& &(5$#0*
    u REPETABLE READ
    u READ COMMITTED

    View full-size slide


  16. u
    u
    u
    u
    u
    u
    u
    u

    View full-size slide

  17. !
    u (#)
    u $ #
    u

    u %"()
    u #

    View full-size slide

  18. !.)
    u !
    u +*!
    u "!
    u -("!
    u !
    u "! "!0'"& !
    u id124
    u UPDATE users SET age = 30 WHERE id BETWEEN 2 AND 4 ,
    $#'
    id=3& /%!

    View full-size slide

  19. 0(&A8
    u *&')$0(&
    u 20(&%-(,0(&<3"
    u 20(
    7%-(,1@0(& !
    u 1,2,4? 43
    u (negative infinity 1]
    u (1 2]
    u (2 3]
    u (4 positive infinity]
    u *&')$0(&:=2positivie infinity0(& !
    INSERT
    ).+/ 56 >;

    View full-size slide