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

introduction about MySQL

introduction about MySQL

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

922f51d458d6ffee8578b9d3ab0a52b6?s=128

Arakaki Yuji

June 15, 2019
Tweet

Transcript

  1. MySQL   Payke   

  2. Goal u MySQL!RDBM  MySQLNS!CHM @ ?O u 24%6=+,71='095:)7."=' MySQLNS

    !CHDG   JF! KP u MySQLQ+0;*$=*=  BAInnoDB >I u TR L E03-&#=/-&+09=(&)8=<-& U
  3.   u MySQL   u   u

      u  
  4. MySQL!& u +($*%() #0 u '+"1. = 1) # u

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

  6. MySQL" u !#$$ u MySQL%+2    &1()0, u

    /!#$$. -3'*.  u InnoDB u MyISAM u Memory u MyRocks
  7.   u    u   

    u   
  8. -!"61&#! u InnoDB%*/81&#! 321&#! -!"61&#! u -!"61&#!B$..)('0 '7-,&" := 

    u 0 '&":= 391&#!5 394<0! +1!; 
  9. +)+!  u (0+! +!  u +)+! )&$#(0+! 6,

     u +)+! 3 1:% "  .(0 +! 1: *'+5 u (0+! 1:4 79228-/
  10. )-/!/(& " u /(& ";< -79: u /(& ">? 

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

    u READ COMMITTED u REPEATABLE READ u SERIALIZABLE u MySQL  u MVCC
  12. !% %&-#" u !% % &-) % # u .0

    &- $(/ %,' u %&-) #(/ 4 &-#" (SQL+*) u READ UNCOMMITTED u READ COMMITED u REPEATABLE READ u SERIALIZABLE
  13. READ UNCOMMITED u &#% %)* (*035%)* (*,  u &#%

    $!6   4 u -./1+2 "'!
  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(% H GCA   u A).1" #-1@32;B).1" #-1 !+&) 64A< B>(%B!+&):9OIBDGCA 
  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 
  16. SERIALIZABLE u 80*/5B> u '-2#"$,2A467 '-2#"$,2=<:CF  )2'+.(@ u E;

    31&"?9  u  % +!'1&"A4G6 D
  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  
  18. MySQL7'.-8: MVCC u " u 2 %3,.96  1 

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

      u   u  u  
  20.  ! u (#  ) u $ # 

     u   u %"( ) u #   
  21. !.) u ! u +*!  u "! u -("!

     u ! u "! "!0'"& ! u id124     u UPDATE users SET age = 30 WHERE id BETWEEN 2 AND 4 , $#' id=3& /%!
  22. 0(&A8 u *&')$0(& u 20(&%-(,0(&<3" u 20(&#9 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   >;
  23.