introduction about MySQL

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

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/(& " 6@10'*.,+<3
    )-/!/(& "=4 79:
    u #/%-/(& " 6@10#/%-/(& "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)2=@3
    L(% HGCA
    u A).1" #-1@32;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 8@
    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%-(,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 Slide

23. 
    
    

    View Slide