ITPub博客

首页 > 数据库 > MySQL > 阅读笔记

阅读笔记

原创 MySQL 作者:haoge0205 时间:2020-07-06 14:27:44 0 删除 编辑

<高性能MySQL>
===MySQL架构===
mysql的架构图:
客户端

连接/线程处理

查询缓存 解析器

优化器

存储引擎

mysql事务ACID:
1、原子性:automicity 要么全部执行成功,要么全部执行失败,这就是事务的原子性
2、一致性:consistency 从一个一致性的状态转换到另外一个一致性的状态
3、隔离性:isolation 事务在提交之前,对其他事务是不可见的
4、持久性:durability 一旦提交,所做的数据修改就会永远保存在数据库中

隔离级别: set session transaction isolation level read COMMITTED;
1、READ UNCOMMITTED 未提交读
在READ UNCOMMITTED级别,事务中的修改,即使没有提交,对其他事务也都是可见的,事务可以读取未提交的数据,这也被称为"脏读"(dirty read)
2、READ COMMITTED 提交读
大多数的数据库系统的默认隔离级别都是READ COMMITTED。一个事务从开始直到提交之前,所做的任何操作修改对其他事务是不可见的。也叫做不可重复读
3、REPEATABLE READ 可重复读
可重复读解决了脏读,但是会存在幻读的现象。当某个事务在读取范围内的记录时,另外一个事务在该范围内插入新的数据。
4、SERIALIZABLE 可串行化
通过强制事务串行执行,是最高的隔离级别


死锁:
死锁是指两个或两个以上的事务在同一个资源上相互占用,并请求锁定对方占用的资源,从而产生恶性循环的现象。
INNODB目前将持有最少行级排它锁的事务进行回滚

事务日志:
事务日志可以提高事务的效率。使用事务日志,存储引擎在修改表数据时,只需要修改其内存拷贝,再把该修改行为记录到持久在硬盘上的事务日志中。而不是每次修改数据本身持久到硬盘上。
事务日志采用的是追加的方式,因此写日志的操作是硬盘上一小块区域内的顺序I/O,而不是随机I/O。事务日志持久以后,内存被修改的数据在后台慢慢刷回到磁盘。修改数据需要写两次磁盘。
如果在事务日志中持久化,没有落盘,系统崩溃,数据库会自动恢复。

INNODB存储引擎:
innodb表是基于聚簇索引建立的,聚簇索引对主键查询有很高的性能,不过它的二级索引里必须包含主键列,所以主键列很大的话,其他索引都会很大。

--阅读《innodb事务模型和锁》--

表修改存储引擎:
alter table table_name engine=innodb; 按行将数据从原表复制到新表,因此执行时间会很长

性能测试前900s预热,避免预热时的IO影响测试结果 

===MySQL基本测试===
sysbench:
1、CPU
2、IO
3、内存
4、线程
5、OLTP

绘图工具:gnuplot 或者 R

===服务器性能剖析===
日志轮转工具:log rotation

mysqlslowlog tmpdump pt-query-digest mysql-proxy
pt-query-digest --explian 和 V/M 值 更容易识别出性能低下的查询

官方mysql和percona server对比慢查询日志缺少了很多附加信息

show profile

使用 SHOW GLOBAL STATUS 捕获数据
mysqladmin ext -i1 | awk '
/Queries/{q=$4-qp;qp=$4/}
/Threads_connected/{tc=$4}
/Threads_running/{printf "%5d %5d %5d\n", q, tc, $4}'

使用 SHOW PROCESSLIST

innotop工具

每个时间段吞吐量
awk '/^# Time:/{print $3, $4, c;c=0}/^# User/{c++}' mysql-slowlog.log

pt-stalt pt-pmp pt-collect工具

gdb 工具对mysql的分析

iostat vmstat new relic工具

===Schema和数据类型优化===
1、选择优化的数据类型
更小的数据类型通常更快,因为它们占用更少的磁盘,内存,CPU缓存,并且处理时需要的CPU周期也更少
2、简单就好
3、尽量避免NULL
因为可为NULL的列使得索引,索引统计和值比较更复杂化,可为NULL的列占用更多的存储空间。可为NULL的列被索引时,每个索引记录需要一个额外的字节,在MyISAM里甚至可能导致固定大小的索引变为可变大小的索引

1、时间类型:
DATETIME 和 TIMESAMP 列都可以存储相同类型的数据,时间和日期,精确到表。但是 TIMESAMP只使用 DATEIME 一半的存储空间,并且会根据时区变化,具有特殊的自动更新能力。但是,TIMESAMP允许的时间范围要小的很多

2、整数类型: 整数 和 实数
整数:tinyint 8
samllint 16
mediumint 24
int 32
bigint 64
整数类型有可选的 unsigned ,表示不允许为负值
例如:tinyint unsigned 0 ~ 255
tinyint 128 ~ 127

MySQL可以为整数类型指定宽度,例如int(11),对大多数应用是没有意义的。它不会限制值的合法范围,只是规定了mysql交互工具用来显示的字符的个数。对于存储和计算来说,int(1)和int(20)是相同的。

3、实数类型: 实数是带有小数部分的数字。
尽量在对小数进行精确计算的时候使用DECIMAL-例如存储财务数据。 或者可以使用BIGINT,根据小数的位数乘以相应的倍数后存储在BIGINT里,避免DECIMAL精确计算代价高的问题。

4、字符串类型:
VARCHAR:
a.VARCHAR类型用于存储可变长字符串,它比定长类型更节省空间,因为它仅使用必要的空间。 如果ROW_FORMAT=FIXED创建的话,每一行都是定长存储,很浪费空间。
b.VARCHAR需要使用1或者2个字节存储字符串的长度,如果列的最大长度<=255,则使用1个字节,否则使用2字节。 VARCHAR(10) 需要11个字节,VARCHAR(1000) 需要1002个字节。
VARCHAR节省了存储空间。但是由于行是变长的,在UPDATE时可能使行变得更长,导致额外的工作。MyISAM 会将行拆分成不同的片段存储。 INNODB则需要分裂页来使行放进页内。
c.慷慨不是明智的:
VARCHAR(5) 和 VARCAHR(200) 存储'yoon'的空间开销是一样的。因此短的有什么优势? 更长的列会消耗更多的内存,因为mysql通常会分配固定大小的内存块来保存内部值。尤其是使用内存临时表排序或者操作时会特别
糟糕。在利用磁盘临时表排序时也同样糟糕。 因此分配真正需要的空间。

CHAR:
CHAR的类型是定长的。

BLOB 和 TEXT:
a.BLOB和TEXT都是为存储很大的数据而设计的字符串数据类型,分别采用二进制和字符方式存储。 BLOB类型存储的是二进制,没有排序规则或字符串, 而TEXT类型有字符串和排序规则。
b.如果非要使用BLOB和TEXT,可以将BLOB字段的地方使用SUBSTRING(column,length)将列值转换为字符串,但是要确保截取的够短,否则临时表的大小会超过max_heap_table_size tmp_table_size
,超过以后mysql会将内存临时表转换为磁盘临时表。

日期和时间类型:
DATETIME: 从1001 ~ 9999,精度为秒。它把日期和时间封装为 YYYYMMDDHHMMSS 整数中,与时区无关。使用8个字节的存储空间。 默认情况下,mysql以一种可排序的,无歧视的格式显示DATETIME值。

TIMESAMP:从1970 ~ 2038,占用4个字节的存储空间。 
a.TIMESAMP 显示的值也依赖时区。 mysql服务器,操作系统,以及客户端都有时区设置。
b.如果在多个时区存储或者访问数据,TIMESAMP和DATETIME的行为很不一样,TIMESAMP和时区有关,DATETIME则保留文本表示的日期和时间。
c.存储比秒粒度更小的日期和时间,用mariadb替换mysql

范式的有点和缺点:
1、范式化的更新操作要比反范式化快
2、当数据较好的范式化时,就有较少的或者没有重复的数据,所以只需要修改更少的行
3、范式化的表通常都很小,可以更好的在内存里执行
4、很少有多余的数据意味着要检索列表数据时更少需要DISTINCT 或者 GOURP BY语句。 在非范式化的结构中要 DISTINCT 和 GROUP BY 才能获得唯一部门的数据
5、范式化设计的缺点通常需要关联。

反范式的优点和缺点:
1、数据都在一个表中,因此可以避免关联
2、如果不需要关联表,对大部分查询最差的情况---即使没有使用索引--是全表扫描。当数据比内存大时,这可能比关联要快的多,避免了随机IO 。(全表扫描基本上是顺序IO)

范式:俗称就是将数据拆分细化,查询时需要关联多张表进行查询想要的数据
反范式:俗称就是将数据混合存放在一起,查询时只需要查询一张表即可,不需要关联

3、混用范式和反范式化

总结:
1、尽量避免过度设计表
2、使用小而简单的数据类型,避免使用NULL值
3、尽量使用相同的或相似的数据类型存储相关的值,尤其要在关联的表中使用的列
4、尽量使用整型定义标识列

===创建高性能的索引===
在mysql中,索引是在存储引擎层而不是服务器层实现,索引没有统一的索引标准。不同存储引擎的索引的工作方式不同,也不是所有的存储引擎都支持相同类型的索引。即使存储引擎支持相同类型的索引,底层实现的也可能不同。

B+Tree索引:
没有特别指明,多半说的都是B-Tree索引,使用B-Tree数据结构来存储数据,即每一个叶子节点都包含指向下一个叶子节点的指针,从而方便叶子节点的遍历搜索。

存储引擎以不同的方式使用B-Tree索引,性能也不相同,各有优劣。 myisam使用前缀压缩技术使得索引更小,innodb则按照原数据格式进行存储。 myisam索引通过数据的物理位置引用被索引的行,innodb则通过主键引用被索引的行。

B-Tree通常意味着值都是按顺序存储的,每一个叶子页到根的距离相同。 根节点存放了指向"叶子节点的指针",叶子节点"指针指向的是被索引的数据"。

索引对多个值进行排序,是根据 CREATE TABLE 语句定义的索引列的顺序,例如: IDX_INDEX(A,B,C) 如果A,B值都一样,则根据C排序。

如果查询中有某个列的范围查询,则其右边的列无法使用索引优化查找,例如:IDX_INDEX(A,B,C)
WHERE A = 'YOON' AND B LIKE 'K%' AND C = '1987-7-7'; 这个查询只能使用所用的前两个列,因为这里的 LIKE 是一个范围条件。

哈希索引:
哈希索引基于哈希表实现,只有精确匹配索引所有列的查询才有效。对于每一行数据,存储引擎会对所有的索引列计算一个哈希码,哈希码是一个较小的值,并且不同的键值计算出来的哈希码也不相同,哈希索引将所有哈希码存储在索引中,同事哈希表中保存指向每个数据的指针。

哈希索引限制:
1、哈希索引只包含哈希值和指针,而不存储字段值。
2、哈希索引数据不是按照顺序存储的,因此无法排序
3、哈希索引也不支持部分列匹配查找
4、哈希索引只支持等值查找,不支持范围查找
5、当哈希冲突的时候,必须遍历链表中所有的行,直到找到符合的行。
6、哈希冲突多的话,索引维护代价也很高。

存储大量的URL,直接用B-Tree存储会很大,例如:select id from url where url='http://www.baiud.com'; 可以删除url列上的索引,新增一个rul_crc列,使用CRC32哈希,就可以如下:
select id from url where url='http://www.baidu.com' and url_crc=CRC32("http://www.baidu.com"); 这样性能就会很高,mysql优化器会选择性能很高而且体积很小的基于rul_crc列的索引来完成。这样缺陷需要维护哈希值。

索引的优点:
1、索引大大减少了服务器需要扫描的数据量
2、索引可以帮助服务器避免排序和临时表
3、索引可以将随机IO变成顺序IO
关于索引的书: "Relational Database Index Design and the Optimizers"

前缀部分索引,alter table sakila.city add key (key(7)); 这种无法通过前缀索引进行 ORDER BY , GROUP BY, 覆盖扫描。

index merge 有时候是一种合并策略的优化结果,但实际上糟糕的索引设计。 

optimizer_switch 索引合并功能

聚簇索引:
1、聚簇索引并不是一种单独的索引类型,而是一种数据存储方式。 innodb的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。
2、聚簇:表示数据行和相邻的键值紧凑的存储在一起。 因为无法同时把数据行存放在不同的地方,所以一个表只有一个"聚簇索引"。
3、innodb 通过主键聚集数据,"被索引的列就是主键列"
4、如果没有主键,innodb会选择一个非空的唯一索引,如果没有这样的索引,innodb会隐式定一个主键来作为聚簇索引。innodb只聚集在同一个页面中的记录,相邻的可能会相聚很远。

聚簇的数据一些重要优点:
1、可以把相关数据保存在一起。 
2、数据访问更快。 聚簇索引将"索引"和"数据"保存在同一个B-Tree中,因此从聚簇索引中查找数据比非聚簇索引中查找数据要快。
3、使用覆盖索引扫描的查询可以直接使用页节点的主键值。

"二级索引需要访问两次,而不是一次":
因为二级索引中保存的 "行指针" 的实质。 二级索引叶子节点保存的不是指向物理位置的指针,而是 "行的主键值"。。。。这意味着,通过二级索引查找数据,存储引擎需要找到二级索引叶子节点对应的主键值,再通过主键
值去"聚簇索引"中找到对应的行数据。 这样就减少了出现行移动或者数据页分裂时,二级索引的维护工作。 但会让二级索引占用更多的空间。

当使用非自增作为主键,例如用UUID作为主键,插入的值不是顺序的,而是随机的。 会导致索引占用的空间更大,页分裂和碎片更多。缺点如下:
1、写入的目标页可能已经从缓存中剔除,或者还没加载到缓存中,innodb在插入之前不得不从磁盘上读取目标页到内存中,这样将导致大量的随机IO
2、因为写入是乱序的,innodb不得不频繁的做页分裂操作,以便为新的行分配空间。页分裂会导致移动大量数据,一次插入最少需要修改三个页而不是一个页。
3、由于频繁的页分裂,页会变得稀疏而不规则,最终数据会有碎片。

innodb_autoinc_lock_mode

覆盖索引:
1、如果一个索引包含(或者说覆盖)所要查询的字段的值,称之为"覆盖索引"
2、如果索引覆盖了where条件中的字段,但是不是整个查询涉及的字段, mysql5.5之前会回表

使用索引来做排序:
1、如果查询需要关联多张表,则只有当 ORDER BY 子句引用的字段全部为第一个表时,才能使用索引排序,和索引的最左前缀列相同。
2、特殊情况下,就是where子句或者join子句中的列指定了常量,如下: idx_index(rental_date,inventory_id,customter_id)
select rental_id,staff_id from rental where rental_date='2005-05-25' order by inventory_id,customter_id;

1、ID为主键,索引(A),扩展为索引(A,ID),这也是冗余索引,因为主键包含在了二级索引中。
2、如果在整数列上有索引,现在需要额外的扩展索引增加VARCHAR字段,有可能会导致性能急剧下降。
例如: Q1:select id,state_id from userinfo where state_id=5; 会很快。
Q2:select state_id,city,address from userinfo where state_id=5; 提升该查询需要扩展索引,让索引能覆盖查询(state_id,city,address), 但是这样Q1变慢了,所以我们需要两个索引,即便冗余。

3、表中的索引越多插入速度就越慢。 增加索引会导致 insert update delete 等操作速度变慢。

4、通过 pt-duplicate-key-checker检查冗余和重复索引。 用 pt-upgrade 工具检查计划中的索引变更。 pt-index-usage 排查哪些索引没有使用。

5、Q1: select actor_id from actor where actor_id > 50;
Q2: select actor_id from actor where actor_id in (1,3,5,7);
Q1是属于范围查询,Q2属于多个等值条件的查询。 这两种访问效率是不同的。对于范围查询,mysql无法再使用范围列后面的其他索引列,多个等值条件查询没有这个限制。

===查询性能优化===
Using Where 从好到坏:
1、在索引中使用where条件来过滤不匹配的记录,在存储引擎完成
2、使用索引覆盖扫描,在Etra中显示Using Index ,直接从索引中过滤不需要的记录,这是在mysql服务器层完成。
3、从数据表中返回数据,然后过滤不满足的条件,在Etra中显示 Using Where ,在 mysql 服务器层完成,需要从数据表中读取记录然后过滤。

"分解sql语句,拆分成几个简单的sql,让应用层在去做关联,减少锁的争用。"

查询执行的基础:
1、客户端发送一条sql语句给服务器
2、服务器先查询缓存,如果命中缓存,则立刻返回存储在缓存中的数据。
3、服务器端进行解析sql,预处理,再由优化器生成执行计划
4、mysql根据执行计划,调用存储引擎的API来执行查询
5、将结果返回给客户端

MySQL客户端:
当客户端从服务器获取数据时,看起来是一个拉数据的过程,实际上是 mysql 在向客户端推送数据的过程。

查询状态:
Sleep: 线程正在等待客户端发送新的请求

Query: 线程正在执行查询或者正在将结果发送给客户端

Locked: 在mysql服务器层,该线程正在等待表锁。在存储引擎层级别实现的锁,例如innodb的行锁,并不会体现在线程状态中。

Analyze and statistics: 线程正在收集存储引擎的统计信息,并生成执行计划

Copying to tmp table【on disk】:线程正在执行查询,并且将结果都复制到一个临时表中,这种状态一般要么是GROUP BY操作,要么是文件排序 FILESORT,或者是UNION操作。如果还有ON DISK,表示正在将内存临时表放到磁盘上。

Sorting result: 线程正在对结果集进行排序

Sending data:表示多种情况:线程可能在多个状态之间传送数据,或者在生成结果集,或者在向客户端返回数据

"要以为你比mysql的优化器更聪明"

"查询优化器在服务器层,统计信息在存储引擎层"

排序优化:
如果需要的排序数据小于"排序缓冲区",mysql则在内存进行快速排序。 如果内存不够排序,mysql则将数据分块,对每个独立的块使用快速排序进行排序,并将各个块的排序结果放在磁盘上,然后进行合并merge,返回结果

关联子查询:
select * from sakila.film where film_id in (select film_id from sakila.film_actor where actor_id=1);
select * from sakila.film where exists (select * from sakila.film_actor where film.film_id=film_acotr.film_id);

select film_id,language_id from sakila.film where not exists (select * from sakila.film_actor where film_actor.film_id=film.film_id);
一般建议通过左外连接重写
select film_id,language_id from sakila.film left outer join sakila.film_actor using(film_id) where film_actor.film_id is null;

pt-upgrade 工具可以检查在升级版本中的sql是否和老版本运行的一样。

用户自定义变量:
1、使用自定义变量的查询,无法使用查询缓存
2、不能在使用常量或者标识符的地方使用自定义变量,例如表名,列名和LIMIT子句中。
3、用户自定义变量的生命周期是在一个链接中有效,不能用他们来做链接间的通信
4、如果使用连接池或者持久化链接,自定义变量可能会让看起来毫无关系的代码发生交互
5、不能显示的声明自定义变量。


===MySQL高级特性===
分区表

在下面的场景中,分区表可以起到非常大作用,如下:
1、表非常大至于无法全部都放在内存中,或者只在表的最后部分有热点数据,其他均是历史数据
2、分区表的数据更容易维护。
3、分区表的数据可以分布在不同的物理设备上,从而高效的利用设备
4、可以使用分区表避免某些特殊的瓶颈,例如innodb的单个索引的互斥访问,ext3文件系统的inode锁竞争等待
5、还可以备份和恢复独立的分区。

分区表的一些限制,如下:
1、一个表最多有1024个分区
2、在 5.5 中,某些场景中可以直接使用列来就行分区
3、如果分区字段中有主键或者唯一索引,那么所有主键和唯一索引必须包含进来。
4、分区表中无法使用外键。

分区表的原理
分区表由多个相关的底层表实现,这些底层表也是句柄对象(Handler object)表示。存储引擎层管理分区的各个底层表和管理普通表一样。从存储引擎角度来看,底层表和普通表没什么区别。

分区select语句:
当查询一个表的时候,分区层先打开并锁住所有底层表,优化器判断是否可以过滤部分分区,然后再调用存储引擎接口各个分区的数据。

分区insert操作:
当写入一条数据时,分区层打开并锁住所有底层表,然后确定哪个分区接收这条数据,再将记录写入对应底层表

分区delete操作:
当删除一条数据时,分区层打开并锁住所有底层表,然后确定数据分区,最后删除

分区update操作:
当更新一条数据时,分区层打开并锁住所有底层表,确定数据在哪个分区,取出来然后更新,再判断更新后的数据放入哪个分区,最后对底层表进行写入操作,并对原数据所在的底层表进行删除操作。


分区表的类型
1、根据键值进行分区,来减少innodb的互斥量竞争
2、使用数学模函数来进行分区

使用分区表从5.5版本开始,5.6对于分区表做了更多的优化

目前分区中的一些其他限制:
1、所有分区都必须使用相同的存储引擎
2、分区函数中使用的函数和表达式也有一些限制
3、某些存储引擎不支持分区
4、对于MYisAM的分区表,不能再使用 LOAD INDEX INTO CACHE操作
5、对于MyISAM表,使用分区表时需要打开更多的文件描述符。


触发器:
1、对每一个表的每一个时间,最多只能定义一个触发器,换句话说,不能在AFTER INSERT上定义两个触发器
2、mysql只支持基于行的触发---也就是说,触发器始终是针对一条记录的。而不是针对整个sql语句的。变成的数据集非常大的话,效率会很低。


===优化服务器设置===
key_buffer_size:设置这个变量可以一次性为键缓冲区(也叫键缓存)分配所有指定的空间。

table_cache_size:设置变量不会立即生效 --- 会延迟到下次有线程打开表才有效果。当有线程打开表时,mysql会检查变量的值,如果值大于缓存中的表的数量,线程可以直接把最新打开的表放入缓存中,
如果值小于打开的表的数量,mysql将从缓存中删除不经常打开的表。

thread_cache_size:设置这个变量不会立即生效 --- 将在下次有连接被关闭的时才生效。当有连接关闭时,mysql会检查线程缓存中是否有空间来缓存线程,如果有空间,则缓存该线程,以备下次重用,如果没有
空间,它将销毁该线程不再缓存。缓存中的线程数以及线程缓存使用的内存都不会立刻减少,只有在新的连接删除缓存中的一个线程并使用后才减少。(mysql只有在关闭连接时才会在缓存中增加线程,在创建新连接时
才从缓存中删除线程)

query_cache_size:mysql在启动的时候,一次性的分配并初始化这块内存。 如果修改这个值,mysql会立刻删除所有缓存的查询,重新分配指定大小的内存。mysql是逐个清理查询的,不是一次性全部删除。

read_buffer_size:mysql只有在查询时才为该缓存分配内存,并且会一次性分配参数指定大小的内存

read_rnd_buffer_size:mysql只有在查询才会为该缓存分配内存,而且只会分配需要的内存大小,而不是参数指定的大小。

sort_buffer_size:查询需要排序操作时才会为该缓存分配内存,一旦排序,mysql会立刻分配参数指定大小的内存。

innodb事务日志:ib_logfile0 ib_logfile1
innodb用日志把随机IO变成顺序IO。 一旦日志完全写入磁盘,事务就持久化了。 即使变更还没有写到数据文件,innodb也可以冲放日志并且恢复已经提交的事务。
innodb最后还是把变更写入到数据文件,因为日志有固定的大小。 innodb的环形的,当写到尾部,会重新跳到开头继续写。 但是不会覆盖还没有写到数据文件的日志记录。

innodb_log_buffer_size 控制日志缓冲区大小

innodb_flush_log_at_trx_commit:
1、为 1 时,每秒钟刷新一次,但是事务提交时不做什么操作
2、为 0 时,把日志缓存写到日志文件并且持久化存储,这是最安全的。
3、为 2 时,每次提交把日志缓存到日志,但是并不刷新。
***在大部分操作系统中,把缓冲写到日志只是简单的把数据从innodb缓存转移到了系统缓存,也是在内存里,并没有把数据持久化存储***

max_allow_packet 设置的太小,有可能会出现复制问题,备库不能接收主库发送过来的数据

inodb_io_capacity:像 PCI-E SSD 需要把值设置的很高。 参数定义了innodb后台任务每秒可用的IO操作数

在线配置工具:http://tools.percona.com


===复制===
在mysql5.0及之前都是基于语句复制,也就是逻辑复制。 更新必须是串行的,而且需要更多的锁。

IO线程 SQL线程 特别线程binlog dump线程

mysql5.1开始基于行复制。

*_db_db *_ignore_db:
use test;
delete from sakila.film;

*_do_db *_ignore_db 都会在数据库test下过滤delete语句,而不是在sakila下过滤。 会导致主从不一致。

binlog_do_db binlog_ignore_db 不仅可能会破坏复制,还可能导致某个时间点的备份进行恢复时失败。

主-主复制,也就是一对主库。

主主复制更新步骤:
1、在一主服务器上更新,更新记录到二进制日志中
2、通过复制传递给二主的服务器的中继日志,二主读取中继日志后并将记录到自己的二进制日志中(因为开启了log_slave_updates)
3、由于"事件的服务器ID"与一主的服务器的ID相同,因此一主忽略此事件

采用blackhole引擎的分发主库,在某些情况下会忘记将自增ID写入到二进制日志中。并且无法使用备库来替代主库,因为分发主库的原因,导致各个备库与原始主库的二进制日志坐标已经不相同。

测量备库延迟,Seconds_behind_mater 是参考值,最好使用pt-heartbeat 脚本,是复制心跳的一种实现。

在主从复制断开时,有一张表数据不同时,可以通过 pt-table-sync 来修复,但是要保证主从同步正常。

mysqlbinlog mysql-binlog.000113 | grep '^# at ' 查看偏移量

strings -n 2 -t d mysql-bin.000113

===应用层优化===
1、应用创建了没必要的sql连接吗?
2、应用对一个mysql实例创建了太多的连接?
3、做了太多的垃圾查询?
4、应用使用了连接池吗?
5、应用是否是长连接?
6、应用是否在不使用的时候还打开?

长连接和连接池的区别:
1、长连接是在每个连接的基础上创建连接,不会在进程间共享
2、连接池通常不会导致连接过多,因为他们在进程间排队和共享连接

explain:
index 和 user index区别:
index和全表扫描一样,扫描表时按照索引次序进行而不是行,优点是避免了排序,缺点是承担按索引次序读取整个表的开销。
use index 说明mysql正在使用覆盖索引,只扫描索引的数据。 


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28939273/viewspace-2702703/,如需转载,请注明出处,否则将追究法律责任。

全部评论

注册时间:2013-11-28

  • 博文量
    295
  • 访问量
    1735880