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

    Payke


    View Slide

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


  3. u MySQL

    u
    u
    u

    View Slide

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

    View Slide

  5. MySQL
    u

    View Slide

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

    View Slide



  7. u

    u

    u

    View Slide

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

    View Slide

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

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

    View Slide

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

    View Slide


  11. u

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

    View Slide

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

    View Slide

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

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

    View Slide

  14. READ COMMITTED
    u ).1" #-17 ?E
    !+&)
    ).1" #-1FN
    u ).1" #-1<FN!+&)K).1" #-1
    7
    u 5J0*/ MG
    u 8$'),1)[email protected]
    L(% HGCA
    u A).1" #[email protected];B).1" #-1 !+&)64A<
    B>(%B!+&):9OIBDGCA

    View Slide

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

    C

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

    View Slide

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

    u % +!'1&"A4G6D

    View Slide

  17. 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 Slide

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


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

    View Slide


  19. u
    u
    u
    u
    u
    u
    u
    u

    View Slide

  20. !
    u (#)
    u $ #
    u

    u %"()
    u #

    View Slide

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

    View Slide

  22. 0(&A8
    u *&')$0(&
    u 20(&%-(,0(&<3"
    u 20(
    7%-(,[email protected](& !
    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 Slide



  23. View Slide