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)例:StudentClasssid(PK)cid(PK)snamecnamecid(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帮助文档 help context || help keyword.数据库锁:读 共享锁写 独占锁,排他锁锁粒度{ 表,行 } # Oracle可以将颗粒度调整到 列 , 库 等级别LOCK TABLEStbl_name [[AS] alias] lock_type[, tbl_name [[AS] alias] lock_type] …lock_type:READ [LOCAL]| [LOW_PRIORITY] WRITEUNLOCK 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 forthe 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’; —> ACTIVEinsert into test(num) values(2);xa end ‘xa’—> IDLExa prepare ‘xa’;—> PREPADERxa commit ‘xa’ || xa rollback ‘xa’xa recover (要求所有进入 PREPADER 才能监控).用户管理GRANTpriv_type [(column_list)][, priv_type [(column_list)]] …ON [object_type] priv_levelTO user_specification [, user_specification] …[REQUIRE {NONE | ssl_option [[AND] ssl_option] …}][WITH with_option …]object_type:TABLE| FUNCTION| PROCEDUREpriv_level:*| *.*| db_name.*| db_name.tbl_namessl_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_CACHEquery_cache_sizequery_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/loglong_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-bin3. 指定主server-idserver-id=1从服务器操作:1. 指定从server-id (这里不能和主一样)server-id=22. # 启用中继日志(默认已开启)3. 连接主服务器change master tomaster_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_eventON tbl_name FOR EACH ROW trigger_stmtOLD和NEW关键字来更新此前的数据trigger_eventinsert update deleteNEW xx-OLD -xx.存储过程与函数:CREATE PROCEDURE sp_name ([proc_parameter[,…]])[characteristic …] routine_bodyCREATE FUNCTION sp_name ([func_parameter[,…]])RETURNS type[characteristic …] routine_body.索引: 有一些有意思的东西oracle:内部索引(btree多路,多级,常用函数索引用于函数查询创建反转顾名思义)外部索引(位图存在重复数据位图连接将重复数据一次性抽出(更近一步)压缩btree的 i/o 时间片 换 cpu 时间片 策略分区索引有数据分区则会出现索引组织表???簇索引多表中相同的列成簇域索引自定义索引类型 !!!!虚拟索引仅测试可用,不存在索引数据的建立全文没什么好说二进制索引为blob等数据创建索引)mysql索引:存储引擎作用btreeInnoDB,MyISAM左节点小于右节点,提高查询效率rtreeMyISAMbtree是2维结构,,那么rtree多于3维hashMemory/Heap适合键值存储的结构fulltextMyISAM大段文本使用场景例: CREATE INDEX part_of_name ON customer (name(10));

计划:  下一章节更新mysql的优化,基准测试,主从,主主模式复制,备份与恢复

本文出自 “Apprentice” 博客，请务必保留此出处