DBMS振り返る
#
# : : )
#
:
[ ]
[ , ]
[ , , , ]
[ , , , ]
[ ]
.
:ACID
A
C
I
D
.
: ansi 99 ( )
READ UNCOMMITTED ( ) ( )
READ COMMITTED ( ) ( )
REPEATABLE-READ ( )
SERIALIZATION
# : , , ,
(MS-SQL)
snapshot committer
snapshow
.
(MYSQL)
[mysqld]
transaction-isolation = REPEATABLE-READ
SELECT @@GLOBAL.tx_isolation, @@tx_isolation;
SET GLOBAL tx_isolation='REPEATABLE-READ';
SET SESSION tx_isolation='SERIALIZABLE';
.
,
I/O I/O
.
:( , )
1. ( ) 2. (sid,cid) 3. cid(FK) -- cid(PK)
:
Student Class
sid(PK) cid(PK)
sname cname
cid(FK)
.
:(MYSQL)
show global variables like '%cache%'\G;
show session variables like '%cache%\G';
show global/session status;
: sql_mode sql ,
tranitional strict_trans_tables
strict_all_tables
: select @@global.sql_mode; show global variables like '%cache%'\G;
: set global sql_mode = 'strict_trans_tables';
.
mysql :
/tmp/mysql.sock
socket
mysql,mysqladmin,mysqldump
sql 1. bash > mysql < xxx.sql 2. mysql > /. xxx.sql
help context || help keyword
.
:
,
{ , } # Oracle ,
LOCK TABLES
tbl_name [[AS] alias] lock_type
[, tbl_name [[AS] alias] lock_type] ...
lock_type:
READ [LOCAL]
| [LOW_PRIORITY] WRITE
UNLOCK TABLES
: lock table student read; # student
unclock student
.
:
#
o START TRANSACTION or BEGIN start a new transaction.
o COMMIT commits the current transaction, making its changes permanent.
o ROLLBACK rolls back the current transaction, canceling its changes.
o SET autocommit disables or enables the default autocommit mode for
the current session.
:
START TRANSACTION;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
savepoint a //
UPDATE table2 SET summary=@A WHERE type=1;
COMMIT rollback a // rollback a
( SERAILIZABLE)
: + ( )
RM TM
: ( / )
--->
xa start 'xa_test'; ---> ACTIVE
insert into test(num) values(2);
xa end 'xa' ---> IDLE
xa prepare 'xa'; ---> PREPADER
xa commit 'xa' || xa rollback 'xa'
xa recover ( PREPADER )
.
GRANT
priv_type [(column_list)]
[, priv_type [(column_list)]] ...
ON [object_type] priv_level
TO user_specification [, user_specification] ...
[REQUIRE {NONE | ssl_option [[AND] ssl_option] ...}]
[WITH with_option ...]
object_type:
TABLE
| FUNCTION
| PROCEDURE
priv_level:
*
| *.*
| db_name.*
| db_name.tbl_name
ssl_option:
SSL
| X509
| CIPHER 'cipher'
| ISSUER 'issuer'
| SUBJECT 'subject'
with_option:
GRANT OPTION
: grant select,update,delete,insert on *.* to user@'localhost'
revoke select,update,delete,insert on *.* from user@'localhost'
.
:
show global variables like '%query_cache%'
query_cache_type {on,off,demand} demand sql SQL_CACHE
query_cache_size
query_cache_min_res_unit
query_cache_limit
query_cache_wcolck_invalidate ( )
：
MariaDB [hellodb]> SHOW GLOBAL STATUS WHERE Variable_name='Qcache_hits' OR Variable_name='Com_select';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select | 24 |
| Qcache_hits | 4 |
+---------------+-------+
Qcache_hits/(Com_select+Qcache_hits)
： ， Qcache_hits/Qcache_inserts ， 3:1， 。 10:1， 。
.
:
(ORACLE: )
log={ on|off } ( )
log_output={ TABLE|FILE|NONE }
gereral_log={ on|off } ( )
general_log_file=/path/log
long_query_time=time(second)
slow_query_log={ON|OFF}
slow_query_log_file=/path/log
.
:
I/O thread
binlog dump
I/O thread
SQL thread
:
1.
Binlog Dump State 。 Binlog Dump ， — ， 。
· Sending binlog event to slave
， 。 。
· Finished reading one binlog; switching to next binlog
。
· Has sent all binlog to slave; waiting for binlog to be updated
。 ， 。
· Waiting to finalize termination
。
2. I/O
I/O State 。 Slave_IO_State ， SHOW SLAVE STATUS 。 。
· Connecting to master
。
· Checking master version
。
· Registering slave on master
。
· Requesting binlog dump
。 ， 。
· Waiting to reconnect after a failed binlog dump request
( )， ， 。 --master-connect-retry 。
· Reconnecting after a failed binlog dump request
。
· Waiting for master to send event
， 。 ， 。 slave_read_timeout ， 。 ， 。
· Queueing master event to the relay log
， SQL 。
· Waiting to reconnect after a failed master event read
( ) 。 master-connect-retry 。
· Reconnecting after a failed master event read
。 ， Waiting for master to send event。
· Waiting for the slave SQL thread to free enough relay log space
relay_log_space_limit ， 。I/O SQL 。
· Waiting for slave mutex on exit
。
3. SQL
SQL State 。
· Reading event from the relay log
， 。
· Has read all relay log; waiting for the slave I/O thread to update it
， I/O 。
· Waiting for slave mutex on exit
。
I/O State 。 ， ， 。
:
:
1.
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass';
2. ( )
[mysqld]
log-bin=mysql-bin
3. server-id
server-id=1
:
1. server-id ( )
server-id=2
2. # ( )
3.
change master to
master_host = ' ip'
master-user = ' '
master-password = ' '
master-log-file = ' bin-log '
master-log-position =
4.
start slave;
.
:
create view v_view WITH [CASCADED | LOCAL] CHECK OPTION
.
:
CREATE TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_stmt
OLD NEW
trigger_event
insert update delete
NEW x x -
OLD - x x
.
:
CREATE PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body
CREATE FUNCTION sp_name ([func_parameter[,...]])
RETURNS type
[characteristic ...] routine_body
.
:
oracle:
(
btree
, ,
)
(
( )
btree i/o cpu
???
!!!!
,
blob
)
mysql :
btree InnoDB,MyISAM ,
rtree MyISAM btree 2 ,, rtree 3
hash Memory/Heap
fulltext MyISAM
: CREATE INDEX part_of_name ON customer (name(10));計画:次の章ではmysqlの最適化、ベンチマークテスト、プライマリ・スレーブ、プライマリ・プライマリ・モードのレプリケーション、バックアップとリカバリを更新します.