逻辑架构:
用户请求 [通过连接器]
连接池 [分配线程连接,连接队列]
查询分析引擎 [操作求解器,语法语义词法分析器,优化器,计划执行器]
管理器 [事务管理器,锁管理器,文件缓存磁盘空间管理器,恢复管理器]
存储引擎接口 [文件系统]
事务:ACID
A 原子性
C 一致性
I 隔离性
D 持久性
隔离级别: ansi 99标准
read uncommitted 可读到未提交数据
read committed 读到提交过后的数据
REPEATABLE-READ 可重读数据
serialization 串行化读完数据
更高级的隔离级别
snapshot committer 快照级别提交
snapshow 快照读取
更改隔离级别,配置文件
[mysqld]
transaction-isolation = REPEATABLE-READ
环境变量
SELECT @@GLOBAL.tx_isolation, @@tx_isolation;
SET GLOBAL tx_isolation=‘REPEATABLE-READ‘;
SET SESSION tx_isolation=‘SERIALIZABLE‘;
多日志,分区文件
将磁盘随机IO尽可能转换为顺序IO
三大范式:
1. 不可再分 2. 要有主键引用 3. 主键才能发起传递引用
Student Class
sid(PK) cid(PK)
sname cname
cid(FK)
服务基础查询:
show global variables like ‘%cache%‘\G;
show session variables like ‘%cache%\G‘;
show global/session status;
mysql的使用:
本地通讯 /tmp/mysql.sock
网络通讯 socket
客户端工具 mysql,mysqladmin,mysqldump
导入sql脚本 1. bash > mysql < xxx.sql 2. mysql > /. xxx.sql
帮助文档 help context || help keyword
数据类型,sql_mode
tranitional strict_trans_tables strict_all_tables
查询: select @@global.sql_mode; 等价 show global variables like ‘%cache%‘\G;
设置: set global sql_mode = ‘strict_trans_tables‘;
ddl,dcl,dml 略
数据库锁:
读 共享锁
写 独占锁,排他锁
锁粒度{ 表,行 }
LOCK TABLES
tbl_name [[AS] alias] lock_type
[, tbl_name [[AS] alias] lock_type] ...
lock_type:
READ [LOCAL]
| [LOW_PRIORITY] WRITE
UNLOCK TABLES
事务:
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;
UPDATE table2 SET summary=@A WHERE type=1;
COMMIT;
savepoint 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，为比较理想的情况。
日志:
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
复制:
从发起请求线程包含自己目前的二进制信息A
主接收到请求使用binlog dump线程回应从B
从A线程将请求接收下来保存为中继日志
从再开线程将中继线程保存为执行日志
状态:
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列也可以显示语句的文本。这说明线程已经从中继日志读取了一个事件，从中提取了语句，并且正在执行语句。
复制流程:
GRANT REPLICATION SLAVE ON *.* TO ‘repl‘@‘%.mydomain.com‘ IDENTIFIED BY ‘slavepass‘;
FLUSH TABLES WITH READ LOCK；
[mysqld]
log-bin=mysql-bin
server-id=1
从服务器类型 但server-id不一样
从服务器中
change master to
master_host = ‘‘
master-user = ‘‘
master-password = ‘‘
master-log-file = ‘‘
master-log-position =
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索引:
rtree,btree,hash,fulltext
例: CREATE INDEX part_of_name ON customer (name(10));
计划: 下一章节更新mysql的优化,基准测试,主从,主主模式复制,备份与恢复
本文出自 “Apprentice” 博客，请务必保留此出处http://apprentice.blog.51cto.com/2214645/1393659