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的优化,基准测试,主从,主主模式复制,备份与恢复

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