Category Archives: Database

innodb: 两个事务在binlog文件中不会交叉

在innodb的binlog文件中,如果一个事务从第m行开始,在第n行结束;那么从m到n之间只会记录本事务的操作,不会记录其他事务的行为。 这是因为innodb中binary log entries总是以事务为单位整体写入文件的。 引用 Within an uncommitted transaction, all updates (UPDATE, DELETE, or INSERT) that change transactional tables such as BDB or InnoDB tables are cached until a COMMIT statement is received by the server. At that point, mysqld writes the entire transaction to the binary log before the COMMIT is executed. 推论: 1. […]

innodb中应该尽量把多条语句放在单个事务中执行

默认情况下,一个写操作就是一次事务。 如果一次业务操作包含三次DB写操作,三个写操作就是三个事务,三个事务导致三次log flush(磁盘读写,代价较高): 引用 InnoDB must flush the log to disk at each transaction commit if that transaction made modifications to the database. 所以应该把这三次DB写操作合在一个事务里,这样只需要做一次flush disk。

innodb的binlog跟redo/undo log无关

mysql, 不管用的是不是innodb, 都有binlog. 这个binary log跟transaction没关系,它主要用于replication和backup. log文件会一直递增,不会循环使用(后面的记录不会覆盖前面的) 同时,innodb还有redo log/undo log. 它们用于transaction, 且会循环使用。

MySQL Replication的基本架构

摘自High Performance MySQL 复制过程的核心是binlog 拓扑方面: 1. 一主一备最常见,可满足典型的读写分离需求 2. 一主多备也可以,但要注意备机太多可能导致主机负担过大。有个办法是主机只挂一个特殊的备机,其他备机再从这个特殊备机中同步数据(即把特殊备机当作主机),把主机的同步负担转移到特殊备机上。这个策略叫distribution master 3. 你可能会想用主主双活(Master-Master in Active-Active Mode)来同时分担读负担和写负担,即两台机分别是对方的备机,每台机都可以读和写,并互相数据同步。但这样做在数据一致性方面的问题非常多,严重不推荐。比如说,数据同步失效了,但两台机仍在提供写服务,怎么办?数据肯定不一致了。 4. 比较受推荐的模式是主主轮活(Master-Master in Active-Passive Mode),即在某一时刻总是一主一备,但必要时可以立即互换主备角色。这种模式在可用性方面(即无当机时间)很有帮助。比如,alter table可能会比较久,造成数据库不可用;但在主主轮活模式下,可以先去备机alter table, 然后切换一下角色,让原备机变成新主机提供服务,让原主机以新备机的角色同步alter table行为。这个过程中不会有down time.

mysql: my.cnf在哪里?

先找到mysqld的执行文件 引用 #ps aux|grep mysqld 如:  /usr/libexec/mysqld 运行一下这个程序的help命令,根据输出中的"Default options"关键字找到my.cnf的位置 引用 #sudo /usr/libexec/mysqld –verbose –help |grep -A1 "Default options" 如: 引用 Default options are read from the following files in the given order: /etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf

MySQL query cache怎么个缓存法

1. query cache的key是sql字符串,并且要求精确匹配:大小写不一样或者多了一个空格,都会导致cache miss 2. query cache直接把用户输入的原始sql作为key, 不是把解析后或优化后的sql作为key,也不会把其中的子查询独立缓存 2. 存储过程中的sql结果不会被缓存 3. 如果一个sql会产生不确定的结果(即使表中数据没有变化也会不确定),比如select now(), MySQL就不会缓存它的结果 4. 只要表中数据有任何变化,就会导致本表相关的所有query cache统统失效

MySQL query cache出现负面作用的情形

1. 写语句特别频繁的场景不适合使用query cache,  因为query cache会被不停地失效,导致cache miss, 使用query cache纯粹是浪费性能。因为query cache本身也有使用代价,执行语句前它要先查一下cache中有没有,如果没有,从表中查到值之后还要更新一下cache 2. query cache累积过多可能出问题。当表中数据修改时,所有相关的cache都要失效;在失效过程中,要访问cache的查询都会被阻塞(即使跟这张表没关系);如果cache很多,最终给人的感觉是数据库长时间对查询无响应。 3. innodb中的长事务可能会使query cache失效。只要事务中修改了这张表,yi 会导致这个表关联的query cache在事务结束前全局失效:不仅本事务中会cache miss, 其他事务中也会cache miss.

MySQL触发器的适用场景和约束

最适合的场景可能是:冗余设计中的数据联动,以及summary table这种。 这两类逻辑用触发器来搞定会比用上层代码搞定要简洁的多。 性能方面没有问题,在InnoDB中,它还可以保证原子性。 不过, 1. 这类逻辑比较隐蔽,容易被忽略,埋坑。 2. 调试难,如果没有全局梳理,一不小心可能导致死锁。 3. 一旦要分表分库,可能处理起来会比在上层应用中处理更棘手。

注意Prepared Statement执行时会比普通statement多一次连接

使用Prepared Statement时,你要先输入那种带问号的语句,让数据库服务端生成一个Prepared Statement句柄传给你;你再拿着这个句柄,输入参数让数据库去执行n次sql。 也就是说,这里客户端与服务端的交互(round trip)发生了1 + n次。1 = 生成prep stmt, n = 执行sql. 典型的OLTP应用在大部分情况下,都不会循环执行sql,也就是说 n=1.也就是说,如果不使用prep stmt, 一次数据库交互需要1个round trip; 如果用了,那就是 n +1 = 1 + 1 =2. 使用prep stmt时发生的round trip数增加了1倍! 这对性能还是有一定影响的。不过,出于安全原因,没人不敢不用prep stmt. 所以,讨论性能问题似乎也没有多大意义。