第二十一周作业

1、回顾并详细总结MySQL的存储引擎、索引;

常用存储引擎的对比:

特点 MyISAM InnoDB MEMORY MERGE NDB
存储限制 64TB 没有
事务安全 支持
锁机制 表锁 行锁 表锁 表锁 行锁
B树索引 支持 支持 支持 支持 支持
哈希索引 支持
全文索引 支持
集群索引 支持
数据缓存 支持 支持 支持
索引缓存 支持 支持 支持 支持 支持
数据可压缩 支持
空间使用 N/A
内存使用 中等
批量插入的速度
支持外键 支持

InnoDB:5.5版本之后默认的存储引擎,具有提交、回滚和崩溃恢复能力的事务安全机制,但是对比MyISAM,InnoDB写处理效率差一些,并且会占用更多的磁盘空间以保留数据和索引。
处理大量的短期事务;
数据存储于“表空间(table space)”中;
(1) 所有InnoDB表的数据和索引放置于同一个表空间中;
表空间文件:datadir定义的目录下
数据文件:ibddata1, ibddata2, …
(2) 每个表单独使用一个表空间存储表的数据和索引;
innodb_file_per_table=ON

数据文件(存储数据和索引):tbl_name.ibd, 
表格式定义:tbl_name.frm

基于MVCC来支持高并发,支持所有的四个隔离级别,默认级别为REPEATABLE READ; 间隙锁防止幻读;
使用聚集索引
支持“自适应hash索引”
锁粒度:行级锁

MariaDB (XtraDB (percona))

数据存储:表空间
并发:MVCC, 间隙锁
索引:聚集索引、辅助索引
性能:预计操作、自适应hash、插入缓存区
备份:支持热备(xtrabacup)

MyISAM:5.5之前版本的默认存储引擎,MyISAM不支持事务、外键,其优势是访问速度快,并且支持全文索引,对事务完整性没有要求或者以SELECT、INSERT为主的应用基本上都可以使用这个引擎来创建表。
支持全文索引(FULLTEXT index)、压缩、空间函数(GIS); 但不支持事务,且为表级锁;
崩溃后无法安全恢复

适用场景:只读(或者写较少)、表较小(可以接受长时间进行修复操作)
Aria:crash-safe

文件:
tbl_name.frm: 表格式定义
tbl_name.MYD: 数据文件
tbl_name.MYI: 索引文件

特性:
加锁和并发:表级锁
修复:手工或自动修复、但可能丢失数据
索引:非聚集索引
延迟更新索引键:
压缩表

行格式:dynamic, fixed, compressed, compact, redundent

其它的存储引擎:
CSV:将普通的CSV(字段通过逗号分隔)作为MySQL表使用;
MRG_MYISAM:将多个MyISAM表合并成为一个虚拟表;
BLACKHOLE:类似于/dev/null,不真正存储任何数据;
MEMORY:所有数据都保存于内存中,内存表;支持hash索引;表级锁;
临时表
PERFORMANCE_SCHEMA:伪存储引擎;
ARCHIVE:只支持SELECT和INSERT操作;支持行级锁和专用缓存区;
FEDERATED:用于访问其它远程MySQL服务器一个代理,它通过创建一个到远程MySQL服务器的客户端连接,并将查询传输到远程服务器执行,而后完成数据存取;
在MariaDB的上实现是FederatedX

MariaDB支持的其它存储引擎:
OQGraph
SphinxSE
TokuDB
Cassandra
CONNECT
SQUENCE

MySQL中的索引:

基本法则:索引应该构建在被用作查询条件的字段上;

索引类型:
B+ Tree索引:顺序存储,每一个叶子节点到根结点的距离是相同的;左前缀索引,适合查询范围类的数据;

可以使用B-Tree索引的查询类型:
全值匹配:精确某个值, “Jinjiao King”;
匹配最左前缀:只精确匹配起头部分,”Jin%”
匹配范围值:
精确匹配某一列并范围匹配另一列:
只访问索引的查询

不适合使用B-Tree索引的场景:
如果不从最左列开始,索引无效; (Age,Name)
不能跳过索引中的列;(StuID,Name,Age)
如果查询中某个列是为范围查询,那么其右侧的列都无法再使用索引优化查询;(StuID,Name)

Hash索引:基于哈希表实现,特别适用于精确匹配索引中的所有列;
注意:只有Memory存储引擎支持显式hash索引;

适用场景:
只支持等值比较查询,包括=, IN(), <=>; 

不适合使用hash索引的场景:
存储的非为值的顺序,因此,不适用于顺序查询;
不支持模糊匹配;

空间索引(R-Tree)
MyISAM支持空间索引,它可以使用诸如GEOMETRY这样的地址空间数据类型。和B-Tree索引不同,空间索引不会要求WHERE子句使用索引的最左前缀。它同时全方位的索引了数据。这样就可以高效的使用任何数据组合进行查找,然而,必须使用MySQL GIS函数,例如MBRCONTAINS(),才能得到这个好处。

全文索引(FULLTEXT)
FULLTEXT是MyISAM表的一种特殊索引,它从文本中找到关键字,而不是直接和索引中的值进行比较。

索引优点:
索引可以降低服务需要扫描的数据量,减少了IO次数;
索引可以帮助服务器避免排序和使用临时表;
索引可以帮助将随机I/O转为顺序I/O;

高性能索引策略:
独立使用列,尽量避免其参与运算;
左前缀索引:索引构建于字段的左侧的多少个字符,要通过索引选择性来评估
索引选择性:不重复的索引值和数据表的记录总数的比值;
多列索引:
AND操作时更适合使用多列索引;
选择合适的索引列次序:将选择性最高放左侧;

2、以实战案例的形式总结MySQL的备份类型以及如何恢复等详细操作;

MySQL常见的三种备份方式:

1)mysqldump+binlog,完全备份,通过备份二进制日志实现增量备份

2)lvm2快照+binlog:几乎热备,物理备份

3)xtrabackup: 
对InnoDB:热备,支持完全备份和增量备份
对MyISAM引擎:温备,只支持完全备份

环境:

源库:192.168.0.23

目标库:192.168.0.24

方式1:mysqldump+binlog

方式2:基于lvm2的快照

方式3:xtrabackup

3、MySQL的复制有哪些类型,详细描述复制原理,并完成实际的操作。

方式1:主从模式

主从复制原理:

1) Slave服务器上执行start slave,开启主从复制开关。
2) 此时,Slave服务器的IO线程会通过在Master上授权的复制用户权限请求连接Master服务器,并请求从指定binlog日志文件的指定位置(日志文件名和位置就是在配置主从复制服务时执行change master命令时指定的)之后发送binlog日志内容。
3) Master服务器接收到来自Slave服务器的IO线程的请求后,Master服务器上负责复制IO线程根据Slave服务器的IO线程请求的信息读取指定binlog日志文件指定位置之后的binlog日志信息,然后返回给Slave端的IO线程。返回信息中除了binlog日志内容外,还有本次返回日志内容后在Master服务器的新的Binlog文件名称以及在binlog中的下一个指定更新位置。
4) 当Slave服务器的IO线程获取到来自Master服务器上IO线程发送日志内容及日志文件位置点后,将binlog日志内容依次写入到Slave端自身的relay log(即中继日志)文件(MySQL-relay-bin.xxxxxx)的最末端,并将新的binlog文件名和位置记录到master-info文件中,以便下一次读取Master端新binlog日志时能够告诉Master服务器需要新binlog日志的哪个文件哪个位置开始请求的binlog日志内容。
5) Slave服务器端的SQL线程会实时的检测本地RelayLog中新增加的日志内容,然后及时的把Log文件中的内容解析成在Master端曾经执行的SQL语句的内容,并在自身Slave服务器上按语句的顺序执行应用这些SQL语句,应用完毕后清理应用过的日志。
6) 经过了上面的过程,就可以确保在Master端和Slave端执行了同样的SQL语句。当复制状态正常的情况下,Master端和Slave端的数据是完全一样的。

配置过程:

环境:

主库:192.168.0.23

备库:192.168.0.24

注意:如果主节点已经运行了一段时间,且有大量数据时,应当通过备份主服务器并恢复数据至从服务器;复制起始位置为备份时,二进制日志文件及其POS;

方式2:主主模式

主主同步实现原理与主从同步相似,只不过两个节点互为主从,相对于主从模式,主主模式具有下列特点:

(1) 都启用binlog和relay log 
(2) 定义自动增长的id字段的增长方式
(3) 都授权有复制权限的用户账号
(4) 各自把对方指定为主服务器

配置过程:

环境:

主库:192.168.0.23

备库:192.168.0.24

方式3:半同步模式

半同步工作方式:介于异步复制和全同步复制之间,主库在执行完客户端提交的事务后不是立刻返回给客户端,而是等待至少一个从库接收到并写到relay log中才返回给客户端。相对于异步复制,半同步复制提高了数据的安全性,同时它也造成了一定程度的延迟,这个延迟最少是一个TCP/IP往返的时间。所以,半同步复制最好在低延时的网络中使用。

原理图:

存在的问题:

客户端事务在存储引擎层提交后,在得到从库确认的过程中,主库宕机了,此时,可能的情况有两种:
1)事务还没发送到从库上。此时,客户端会收到事务提交失败的信息,客户端会重新提交该事务到新的主上,当宕机的主库重新启动后,以从库的身份重新加入到该主从结构中,会发现,该事务在从库中被提交了两次,一次是之前作为主的时候,一次是被新主同步过来的。
2)事务已经发送到从库上。此时,从库已经收到并应用了该事务,但是客户端仍然会收到事务提交失败的信息,重新提交该事务到新的主上。

配置方式:

环境:

主节点:192.168.0.23

备节点:192.168.0.24

1)在主备节点上均安装半同步插件

2)修改主备节点上的配置文件

3)在主库上创建复制用户

4)查看主库当前的binlog日志位置

5)在备库上配置slave

6)在主备节点上分别启动半同步服务

7)从节点上启用半同步

方式4:基于SSL的主从复制

配置过程:

环境:

主库:192.168.0.23

备库:192.168.0.24

1)在主备节点分别搭建CA服务器和客户端

2)分别在主备库上修改配置文件,启用ssl

3)在主库上创建复制账号

4)在从库上开启同步

5)启动主从同步并查看状态

方式5:过滤器

同步方式说明:让从节点仅复制指定的数据库,或指定数据库的指定表;

有两种实现方式:
1) 主服务器仅向二进制日志中记录与特定数据库(特定表)相关的事件;
问题:时间还原无法实现;不建议使用;
2) 从服务器SQL_THREAD在replay中继日志中的事件时,仅读取与特定数据库(特定表)相关的事件并应用于本地;
问题:会造成网络及磁盘IO浪费

配置过程:

环境:

主库:192.168.0.23

备库:192.168.0.24

1)基于数据库过滤

2)基于表过滤

步骤与基于库一致,只是在修改备库参数文件时使用:

replication-do-table=TABLE_NAME

replication-ignore-table=TABLE_NAME

3)基于表的通配符

步骤与基于库一致,只是在修改备库参数文件时使用:

replicate-wild-do-table=PATTERN
replicate-wild-ignore-table=PATTERN

原创文章,作者:N26-西安-方老喵,如若转载,请注明出处:/79377

发表评论

登录后才能评论

联系我们

400-080-6560

在线咨询:点击这里给我发消息

邮件:1823388528@qq.com

工作时间:周一至周五,9:30-18:30,节假日同时也值班

友情链接:万达娱乐平台  万达主管  万达招商QQ  万达注册  万达娱乐招商  万达主管QQ  guoqibee.com  万达娱乐主管  guoqibee.com