台州做网站的公司有哪些公司,绍兴做网站建设,网站开发人员没有按照设计开发,.net 微信网站开发一、Mysql架构原理和存储机制
1.体系结构 2.查询缓存
3.存储引擎
存储引擎的分类
innodb#xff1a;支持事务#xff0c;具有支持回滚#xff0c;提交#xff0c;崩溃恢复等功能#xff0c;事务安全myisam:不支持事务和外键#xff0c;查询速度高Memory#xff1a;利…一、Mysql架构原理和存储机制
1.体系结构 2.查询缓存
3.存储引擎
存储引擎的分类
innodb支持事务具有支持回滚提交崩溃恢复等功能事务安全myisam:不支持事务和外键查询速度高Memory利用内存创建表访问速度非常快因为数据在内存而且默认使用Hash索引但是一旦关闭数据就会丢失Archive归档类型引擎仅能支持insert和select语句Csv以CSV文件进行数据存储由于文件限制所有列必须强制指定not null另外CSV引擎也不支持索引和分区适合做数据交换的中间表BlackHole: 黑洞只进不出进来消失所有插入数据都不会保存Federated可以访问远端MySQL数据库中的表。一个本地表不保存数据访问远程表内容。MRG_MyISAM一组MyISAM表的组合这些MyISAM表必须结构相同Merge表本身没有数据对Merge操作可以对一组MyISAM表进行操作。
INNODB和myIsam的对比
事务和外键 innodb支持事务和外键具有安全性和完整性适合大量的insert和update操作。myisam不支持事务和外键它提供高速存储和检索适合大量的select操作。锁机制 Innodb支持行级锁锁定指定记录基于索引来加锁实现。myisam支持表级锁锁定整张表。索引结构 innodb使用聚集索引聚簇索引索引和记录在一起存储即缓存索引也缓存记录。myisam使用非聚集索引索引和记录分开。并发处理能力 myisam使用表级锁会导致写操作并发低读之间并不阻塞innodb读写阻塞可以与隔离级别有关可以采用多版本并发控制MVCC来支持高并发。存储文件 innodb表对应两个文件一个.frm表结构文件一个.ibd数据文件myisam表对应三个文件一个.frm表结构文件一个MYD表数据文件一个.MYI索引文件。默认限制是256TB。适用场景 myisam不需要事务支持不支持并发相对较低锁定机制问题数据修改相对较少以读为主数据一致性要求不高。innodb需要事务支持行级锁对高并发有很好的的适应能力数据更新较为频繁的场景数据一致性要求较高硬件设备内存较大可以利用innodb较好的缓存能力来提高内存利用率减少磁盘的IO。
INnoDb内存结构 Buffer Pool:缓冲池简称BP。 Page管理机制 page根据状态可以分为三种类型 free page:空闲page未被使用过clean page被使用的page数据没有被修改过dirty page脏页被使用page数据被修改过页中数据和磁盘中的数据产生了不一致。针对上面三种类型innodb通过三种链表结构来维护和管理 free list:表示空闲缓冲区管理free pageflush list表示需要刷新到磁盘的缓冲区管理dirty page内部page按修改时间排序。脏页即存在于flush 链表也在LRU链表中但是两种互不影响LRU链表负责管理page的可用性和释放而flush链表负责管理脏页的刷盘操作。lru list表示正在使用的缓冲区管理clean page和dirty page缓冲区以midpoint为基点前面链表称为new列表区存放经常访问的数据占63%后面的链表称为old列表区存放使用较少数据占37%。 改进型LRU算法维护 Change Buffer写缓冲区。
3.1 undo log
undo: undo意思为撤销和回退以撤销为目的指能回到过去的某种状态。
undo log 事务在开始之前会将历史的数据保存到undo log中当发生回滚或者未提交事务时数据库崩溃了可以通过undo log回退到数据的快照版本撤销掉未提交的事务对数据库产生的影响。
undo log的产生和销毁 undo log在事务开始之前产生在事务结束之后也不会立即销毁而是先将undo log加入到删除列表中而是通过后台的线程purage thread进行销毁uodo log的本质是逻辑日志是记录的一个相反的过程例如如果执行的是一个delete操作则在undo log中记录一条insert操作执行的是一个insert操作则在undo log日志中记录一条delete操作如果是执行的一个update 操作则在undo log中记录一条相反的update记录。
undo log在MVCC多版本并发控制中的应用 事务提交之前undo log中先保存了旧版本的数据此时事务没结束时可以供其他的事务进行快照读。 理解事务A执行更新操作首先先把旧版本数据同步到 undo buffer中后续会持久化道undo log中事务B手动开启事务执行查询操作会在undo log中读取快照版本数据
3.2 redolog
redo redo的含义就是重做意思是恢复操作在数据库发生意外时重现操作。
redo log: 事务修改的任意数据都将最新的数据备份到redo log中称为重做日志。
redo log的生成和释放随着事务的操作的执行就会产生redo log随着事务的提交会将生产的redo log写到log buffer中并不是随着事务的结束log buffer中的数据会立即写入到磁盘中。等到buffer pool中的脏页刷新到磁盘中之后redo log的使命也就完成了redo log占用的空间就可以被重用(覆盖写入)。
redo log的实现原理 redo log是为了事务的持久化这一特性的产物当事务提交时buffer pool里面的脏页还没有全部的刷新到硬盘时数据库宕机了当重启mysql之后就可以依据redo log里面的内容进行重做继续将数据持久化到硬盘中。
redo log写入机制 redo log的写入机制是顺序写入的方式写满时则进行回溯进行覆盖写。
3.3 binlog
二、Mysql索引存储机制和工作原理
1.索引类型
从索引的存储结构划分B-Tree索引、Hash索引、FULLTEXT全文索引、R Tree索引从应用层次划分普通索引、唯一索引、主键索引、复合索引从索引键值类型划分主键索引、辅助索引二级索引从数据存储和索引键值逻辑关系划分聚集索引、非聚集索引
普通索引即针对数据库表创建索引
唯一索引与普通索引类似添加唯一索引的列值必须唯一但允许有空值
主键索引特殊的唯一索引不允许有空值一般是在建表的时候同时创建主键索引。
组合索引 为了进一步的榨取mysql的效率考虑建立组合索引即将数据库表中的多个字段联合起来作为一个组合索引。
好处形成索引的覆盖提高where语句的查询效率使用原则where后的第一个条件就应该是复合索引的第一列依次类推。如果一个表中的数据在查询的时候有多个字段总是同时出现就可以将这些字段作为复合索引使用提高覆盖率提高查询的效率
全文索引
2.索引原理
用于快速查找记录的一种数据结构。需要额外的开辟空间和数据维护工作。
索引是物理数据页存储在数据文件中innodb-ibd文件利用数据页存储。索引可以加快检索速度但同时也会降低增删改操作速度索引维护需要代价。
二分查找法
二分查找法也叫作折半查找法时间复杂度是log2 N二分查找法的优点是等值查询和范围查询的效率比较高缺点是对于一些增删改的操作比较慢。
查询过程
首先要求查询的序列是有序的在这里我们以递增序列为例定义两个指针左指针L和右指针R分别位于序列的两端计算出LR/2的值M根据这个索引值M获取数据值用数据值和目标数据比较如果相等则返回结果如果数据值大于目标数据值那么将R指针定为M-1L不变如果数据值小于目标数据值那么将L指针定为M1R不变。重复以上查询过程直到查询出数据或者最后也没有数据为止。
Hash结构
BTree结构
B树的阶数指的是每个节点最多有几个子节点N阶指的是有N个子节点
每个节点里面最多存放N-1个值 B-Tree结构 索引值和data数据存放在整棵树结构上每个节点可以存放多个索引值和数据值树节点的多个索引值自左到右升序排列从根节点开始对根节点的数据按照二分查找法查找找不到的话便遍历子节点直到所对应的指针为空或者已经是叶子结点才结束。 BTree结构 非叶子结点只存储索引值这样可以存储更多的索引叶子结点存储所有的索引值和对应数据叶子结点之间用指针连接提高区间的访问性能相比B树B树进行范围查找时只需要查找定位两个节点的索引值然后利用叶子结点的指针进行遍历即可。而B树需要遍历范围内所有的节点和数据显然BTree效率高。
聚簇索引和辅助索引
聚簇索引和非聚簇索引Btree结构中叶子结点中主键索引和数据存放在一块称为聚簇索引索引和数据不存放在一块则称为非聚簇索引。主键索引和辅助索引BTree结构中叶子结点中存放的是主键字段值就属于主键索引如果存放的是非主键字段值就属于辅助索引二级索引。
3.索引分析与优化
3.1 Expain排查sql性能
id固定标识select_type:表示查询的类型常用的值如下 SIMPLE:表示查询的语句比较简单不包含子查询或者unionPRIMART表示此查询是最外层的查询。如果一条查询语句为union或者是含有子查询那么最外层的那个sql查询类型便是PRIMARYUNION表示此查询是UNION的第二个或后续的查询DEPENDENT UNIONunion中的第二个或者后续的查询语句使用到了外边的查询结果UNION RESULT:UNION的结果SUBQUERY:select子查询语句DEPENDENT SUBQUERY:select子查询语句依赖外层 的查询的结果。
最常见的查询类型就是simple表示我们没有使用子查询和联合查询 # 使用sql案例# SELECT_type SIMPLEEXPLAIN SELECT * FROM lagou_auth_code;# SELECT_type PRIMART 和 SUBQUERYEXPLAIN select * from lagou_auth_code where create_time (SELECT MAX(create_time) from lagou_auth_code);EXPLAIN SELECT a.code, (SELECT email from lagou_token where token b53e8b9c-04a2-4b3e-8306-e5a50a8c2c85) temailFROM lagou_auth_code a ;# SELECT_type PRIMART 和 DEPENDENT SUBQUERYexplain SELECT email,codefrom lagou_auth_code l1where create_time (SELECT MAX(create_time) from lagou_auth_code l2 where l1.email l2.email);# SELECT_type PRIMART 和 UNION 和 UNION RESULTEXPLAIN SELECT codefrom lagou_auth_code where email zae_zangchuanlei163.comUNIONSELECT token from lagou_token where email zae_zangchuanlei163.com;# SELECT_type PRIMART 和 DEPENDENT SUBQUERY 和 DEPENDENT UNIONEXPLAINSELECT * from lagou_auth_codewhere id in (SELECT idfrom lagou_auth_codewhere id 394017376523259904UNION allSELECT idfrom lagou_auth_code)table表示哪张表 partitios type连接类型数据库引擎以什么方式去查询出数据是比较重要的属性根据这个属性可以判断出是全局扫描还是基于索引的扫描查询常用属性值如下自上至下效率越来越高。 ALL:表示全表扫描性能最差。 INDEX表示基于索引的全局扫描先扫描索引再扫描全表数据。在排序的情况下效率较高 range表示使用索引范围查询使用、,,,in等等 ref表示使用非唯一索引进行单值查询。 eq_ref:一般情况下出现在多表join查询中表示前面的那个表每一个记录都只能匹配后面表的一行结果表设计时采用的一对一的方式 const表示我们使用主键或唯一索引做等值查询了也被称作常量查询。 NULL表示不用访问表速度最快。 possible_keys表示的是查询时能够使用到的索引。注意并不一定会真正的使用。 key表示查询时真正使用的索引显示的是索引名称。 key_len表示查询使用索引的字节数量可以用它来判断是否全部使用了组合索引或者只用到索引的最左不分的部分字段值。 key_len的计算规则如下 字符串类型字符串长度跟字符集有关latin11、gbk2、utf83、utf8mb44. char(n):n字符集长度 varchar(n):n字符集长度2字节数值类型int4个字节时间类型DATE3个字节字段属性NULL属性占1个字节 ref rows记录行数mysql查询优化器会根据统计信息估算SQL要查询到结果需要扫描多少行记录。原则上rows是越少效果越好 filtered extra额外的扩展的一些信息。表示很多额外的信息各种操作会在extra提示相关信息常见的几种如下 Using where:表示查询需要通过索引回表查询数据Using index表示查询需要通过索引索引就可以满足所需数据。Using filesort表示查询出来的结果需要额外排序数据量小在内存大的话在磁盘因此有Using fileSort建议优化。Using temprorary查询使用到了临时表一般出现去重分组等操作。
3.2 回表查询
通过索引查询主键值然后再去聚簇索引查询记录信息
3.3 覆盖索引
只需要在一棵索引树上就能获取SQL所需的所有列数据无需回表速度更快这就叫做索引覆盖。
3.4 最左前缀原则
复合索引遵循最左前缀原则最左前缀顾名思义就是最左优先即查询中条件使用到最左边的列那么索引将会生效如果查询使用第二列或者其他非最左边的列那么索引将会失效。
3.5 LIKE查询
面试题mysql在使用like查询的时候索引能不能起作用
回答mysql在使用的时候是可以被使用的但是有条件只有把%字符写在后面才会使用到索引。“target%”。
3.6 NULL查询
面试题如果mysql表的某一列含有NULL值那么包含该列的索引是否有效
mysql可以在含有NULL的列上使用索引的但是NULL和其他数据还是有区别的不建议列上允许为NULL。最好设置NOT NULL并给一个默认值。NULL列需要增加额外的空间去记录其值是否为NULL
3.7 索引与排序
4.查询优化
4.1 慢查询定位
开启慢查询日志如果一个sql执行的时间超过了它指定的一个时间那么便把这条sql记录在慢查询日志中。看慢查询日志是否开启show variables like slow_query_log%.通过如下命令开启慢查询日志 set global slow_query_log on;开启慢查询日志set global slow_query_log_file OAK-slow.log; 指定日志文件名set global log_queries_not_using_indexes ON; 表示会记录没有使用索引的查询sqlSET long_query_time 10;指定慢查询的阈值单位是秒。如果sql执行时间超过阈值就属于慢查询记录在文件中。查询慢查询日志mysqldumpslow工具使用文本打开。
4.2 慢查询优化
查询是否使用了索引只表示一个sql的执行过程并不代表这个sql的执行效率列入一个sql中where id 1这个就使用到了主键索引而且是单值查询此时效率就会高但是如果where id 0即使存在主键索引但由于还是范围查找全表臊面效率也还是没有提高。而慢查询日志关注的是sql的执行时间一个sql一旦执行时间超过了指定的阈值那么就会出现在慢查询日志中即使一个sql使用了索引但是它的效率不高执行不快的话也是会出现在慢查询日志中。
我们在关注索引时不要只关注到是否使用了还要关注索引是否减少了扫描表的行数如果扫描行数少了那么效率才会得到提升。对于一个大表来讲不仅仅要去创建索引还是提高过滤性。过滤性好执行效率才会高。
4.3 分页查询优化
三、Mysql事务和锁工作原理
1.ACID特性
原子性一个事务里面对数据的修改操作要么全部执行要么全部不执行。持久型一个事务一旦提交那么对数据的修改将是永久的后续的操作或者故障不应该对其有影响不会丢失。隔离性不同事务之间互相独立互不影响一个事务内部的操作及使用的数据对其他的并发事务是隔离的。一致性事务的开始和结束数据库的完整性并未被破坏一致性包括约束一致性和数据一致性。
2.事务控制的演进
2.1 并发事务
事务并发处理的一些问题
更新丢失当两个或多个事务更新同一行记录会产生更新丢失现象。可以分为回滚覆盖和提交覆盖。 回滚覆盖一个事务的回滚操作把其他数据提交的数据给覆盖了。提交覆盖一个事务的提交操作把其他数据的提交的数据给覆盖了。脏读一个事务读取到了另一个事务修改但为提交的数据。不可重复读一个事务多次读取同一条数据发现读取出的数据不一致。数据内容由于其他事务在这个事务期间更新这条数据的内容幻读一个事务中多次按相同条件查询结果查询出的结果条数不一致多了或者少了几行记录。数据条数由于其他事务在这个事务期间新增或者删除某些数据.注意只能在事务一期间修改了事务二新增的那条数据才会发生幻读否则也不会发生。
2.2 排队
完全顺序的执行所有事务的数据库操作不需要加锁简单的说就是全局排序。序列化的执行所有的事务单元数据库的某一时刻只会执行某一个事务符合强一致性但是执行效率不高处理性能低。
2.3 排他锁
支持并发的处理事务如果两个事务处理中涉及到同一块的数据时则会触发排他锁或者叫互斥锁先进入的事务会独占资源其他的事务进入等到的一个状态等它处理完后释放锁另一个事务才会进入。
排他锁和排队的区别在于排队是对于整个数据库操作而言每次只要一个事务在进行而排他锁使用时可以支持多事务操作数据库处理不同的表数据只有发生事务冲突也就是两个事务操作同一块的表数据时才会触发互斥锁导致其他事务堵塞等待。【可以参考生活中上厕所的案例排队就是卫生间每次只能有一个人排他锁就是多坑位可以允许多个人只有当坑位不够时会导致其他人等待】
2.4 读写锁
读写锁可以让读和读的操作并行读写写读写写还是要加排他锁。
2.5 MVCC多版本控制
使用的是copy on write的思想除了支持读和读的并行还支持读和写写和读的并行但是仍然不能保证写和写的并行。
MVCC被称为多版本控制是很巧妙的产生多个版本让事务可以看到自己应该可以看到的数据版本将稀缺的独占资源互斥转化为并发大大提高了系统的吞吐量和执行效率。
如何生成多版本每次事务操作之前都会在undo日志中记录修改之前的数据状态和事务号该备份记录可以用于其他事务的读取也可以进行必要时的数据回滚。
快照读读取的是记录的快照版本可能是历史版本不用加锁。 理解事务A修改了某条数据但此时还未提交事务事务B要读取这条数据通过undo log拿到历史版本的数据进行读取
当前读读取的是当前最新的版本保证读的是当前返回的信息需要加锁让其他事务不会并发的修改这条记录。 理解事务A修改了某条数据然后事务A在接下来的操作又读取这条数据读取到的就是最新的修改的数据。
MVCC的过程原理
事务一进来修改某条数据将该数据进行加锁然后将操作记录redo log把该行修改前的值记录到undo log中修改该行的数据填写事务号将回滚指针指向undo log中记录的那行记录
3.事务隔离级别
3.1隔离级别类型
读未提交解决回滚覆盖类型的更新丢失其他现象仍然可能发生。读已提交Read Commited解决了脏读不能解决不可重复读和幻读可重复读解决了不可重复读但是仍然可能发生幻读串行化解决了幻读但是导致了大量的超时和锁竞争效率低下。
数据库的隔离级别越高并发问题就越小但是并发处理能力越差代价。 事务隔离级别针对innodb支持事务的引擎。
事务隔离级别和锁的关系
事务隔离级别是sql92定制的标准相当于事务并发控制的整体解决方案本质上是对锁和MVCC使用的封装隐藏了底部细节。锁是数据库实现并发控制的基础事务隔离采用锁来实现对响应的操作加不一样的锁可以防止其他事务同时岁数据进行操作。对用户来讲首先选择使用隔离级别当选用的隔离级别没法解决并发的问题或者需求时才有必要再开发中手动的设置锁。
mysql的默认隔离级别可重复读
oracle的默认隔离级别是读已提交
3.2 Mysql隔离级别控制
显示隔离级别show variables like tx_isolation
select tx_isolation
4.锁机制和实战
4.1 锁分类
从操作的粒度可以分为表级锁行级锁和页级锁 表级锁对整张表进行加锁锁定力度大发生锁冲突的概率最高并发度最低。innodbmyisamBDB使用行级锁对访问的某行数据进行锁定锁定力度低发生锁冲突概率最低并发度最高。innodb使用页级锁每次锁定相邻的一组数据锁定粒度在表级锁和行级锁之间并发度一般。应用在BDB引擎中。从操作的类型可分为读锁和写锁 读锁s锁共享锁针对同一份数据多个读操作可以同时进行而不会互相影响。写锁x锁排他锁当前的操作没有完成之前它会阻断其他写锁和读锁。IS锁、IX锁意向读锁、意向写锁属于表级锁。S和X主要针对行级锁。在对表记录添加X和S锁之前会先对表天剑IS锁和IX锁。解释事务A对记录添加了S锁可以进行读操作不能进行修改操作。其余的事务可以对该记录追加S锁但不能追加X锁如果想追加X锁的话要等所有的S锁释放之后事务A对记录添加了X锁可以对该记录进行读操作和写操作其余的事务不能同时对记录进行读和写的操作。从操作的性能可分为乐观锁和悲观锁 乐观锁一般的实现方式是对记录数据版本进行比对在数据更新提交的时候才会进行冲突检测如果发现冲突了则提示错误信息。悲观锁在对一条数据进行修改的时候为了避免同时是被其他人修改在修改数据前采用先绑定再修改的控制方式。共享锁和排他锁是悲观锁的不同实现但都属于悲观锁的范畴。
4.2 行锁原理
4.3 悲观锁
悲观锁是指在数据处理的过程中国将数据处于锁定状态一般使用数据库的锁机制实现。从广义上来看行锁表锁读锁写锁共享锁排他锁都属于悲观锁的范畴。
表级锁 加读锁lock table 表名 read查看表加过的锁show open tables,删除表锁unlock tables 表级读锁当前表追加读锁当前连接和其他的连接都可以进行读操作但是当前连接修改操作会报错其他连接修改操作会阻塞等待表级写锁当前表追加写锁当前连接可以进行读和修改的操作但是其他连接读和修改的操作都会进入阻塞等待状态。行级锁 共享锁行级锁-读锁【sqlselect * from where deptno1 lock in share mode】总结事务使用了共享锁只能读取不能修改其他事务可以重复加读锁。排他锁行级锁-写锁【sqlselect * from where deptno1 for update】总结事务使用了排他锁当前事务可以读取和修改但是其他的事务不能够读取和修改操作。
行级锁的实现其实是依靠其对应的索引所以说如果操作没用到索引的查询那么会锁住全表记录。
4.4 乐观锁
悲观锁和乐观锁都可以解决事务写写并发在应用中可以根据并发处理能力区分比如对并发率要求高的选择乐观锁对于并发率要求低的可以选择悲观锁。
乐观锁实现原理 使用版本字段version先给数据表新增一个版本号字段每次操作都会将版本号加一每次更新提交的时候将检查版本号和数据库表的版本号。hibernate封装了乐观锁的机制使用时间戳
4.5死锁与解决方案 表锁死锁 产生原因用户A访问A表锁住了A表然后又访问表B另一个用户B访问B表锁住了B表然后又访问A。A和B用户互相等待对方释放锁引发了死锁。 解决方案调整程序逻辑尽量按照相同的顺序执行。 行级锁死锁 产生原因一在事务中执行没有索引的条件的查询引发了全表扫描把行级锁上升为了全表记录锁定等价于表锁多个这样的事务发生之后就容易产生了死锁和阻塞。 解决方案一不要使用太多的连表查询使用explain关键字分析sql必要时添加索引。 产生原因二两个事务分别想拿到对方持有的锁互相等待于是产生死锁。 解决方案二1.同一个事务尽可能的做到一次锁定所需要的所有资源。 2.按照id对资源进行排序然后按照顺序进行处理。 共享锁转换为排他锁
产生原因
事务Aselect * from dept where deptno 1 lock in share mode;// 共享锁1
update dept set dname java where deptno1;//排他锁3 【由于B有一个排他锁在等待所以也没法获取一个排他锁引发了死锁】
事务Bupdate dept set dnamejava where deptno1;//由于A有共享锁没发货区到排他锁需要等待。 2
解决方案 1.不让用户重复点击避免引发同时对同一条记录多次操作。2.使用乐观锁进行控制。
死锁排查 查看死锁日志show engine innodb status查看锁状态变量show status like innodb_row_lock% Innodb_row_lock_current_waits当前正在等待锁的数量Innodb_row_lock_time从系统启动到现在锁定总时间长度Innodb_row_lock_time_avg 每次等待锁的平均时间Innodb_row_lock_time_max从系统启动到现在等待最长的一次锁的时间Innodb_row_lock_waits系统启动后到现在总共等待的次数
四、Mysql集群架构及相关原理
1.集群架构设计
应用架构演变
单一架构 缺点数据量太大超出一台服务器承受读写操作量太大超出一台服务器承受一台服务挂了应用也会挂掉。 2021/4/23 10:42:22 2021/4/23 10:42:24主从架构 优点主库可以抗住写的压力从库承受读的压力保证了高可用。缺点数据量太大超出一台服务器承受写操作太大超出一台服务器承受。分库分表 优点水平拆分每个实例拥有全部数据的1/n解决数据量大的问题。缺点如何保持数据一致性。云数据库将mysql做成一个saas服务服务提供商负责解决可配置性可扩展性多用户存储结构设计等这些疑难问题。
2.主从模式
2.1 主从复制适用场景
主从复制用途
实时灾备用于故障切换 - 高可用读写分离提供查询服务 - 高扩展数据备份避免影响业务 - 高可用
主从复制部署条件
从库数据库能连接主库主库要开启binlog日志设置log——bin参数主从server-id不同
2.2 主从复制实现原理
2.2.1 主从复制
主从复制的步骤
主库将数据库信息的变更记录到binlog日志中从库读取主库的binlog日志将信息写入relay log中继日志中从库读取relay log中继日志从库中进行replay将信息变更到自己的数据库中
涉及到的线程
Master服务将数据库信息的变更记录写入到binlog日志中BinLogDump Thread将binlog日志中的内容传递给Slave的IO Thread。Slave服务的IO Thread将接收到的信息内容写入到relay log中继日志中Slave服务的SQL Threa读取relay log解析relay log的内容放在自己服务器上执行
主从复制可能存在的问题
主库宕机后数据可能会丢失 binlog日志信息还没完全同步到从库的中继日志中就挂掉了从库只有一个Sql Thread主库的写压力大复制很有可能延迟。
针对主从复制存在的问题想到的解决方案
半同步复制 - 解决数据可能丢失的问题并行复制 - 解决重复复制延迟的问题
2.2.2 半同步复制
2.3 并行复制
主从数据库搭建过程 阶段一准备过程 1.准备两台服务器132和130其中130作为master132作为slave。 2.使用rpm 安装包。tar -xvf mysql安装包。 3.检查mariadb rpm -qa|grep mariadb 4.如果存在mariadb的话将进行移除:rpm -e mariadb-libs-5.5.41-2.el7_0.x86_64 --nodeps,避免干扰 5.rpm -ivh mysql-community-common-5.7.28-1.el7.x86_64.rpm 6.rpm -ivh mysql-community-libs-5.7.28-1.el7.x86_64.rpm 7.rpm -ivh mysql-community-libs-compat-5.7.28-1.el7.x86_64.rpm 8.rpm -ivh mysql-community-client-5.7.28-1.el7.x86_64.rpm 9.rpm -ivh mysql-community-server-5.7.28-1.el7.x86_64.rpm 10.rpm -ivh mysql-community-devel-5.7.28-1.el7.x86_64.rpm 11.将mysql实例化mysqld --initialize --usermysql 12.cat /var/log/mysqld.log 查看密码 13.启动mysql服务:systemctl start mysqld.service 14.systemctl status mysqld.service 查看mysql服务的状态 15.mysql -u root -p 登录mysql set passwordpassword(root)修改密码 16.systemctl stop iptables 将iptables关闭 17.systemctl stop firewalld 将防火墙关闭 18.systemctl disable firewalld.service 将防火墙禁掉防止自己启动 阶段二主从配置 1.进入到主库服务器 cd etc -- 进入etc文件夹下 2.vi my.cnf 【开bin_log功能log_binmysql-bin server-id1 sync-binlog1(开启同步每次执行写入性操作都与磁盘同步) binlog-ignore-dbinformation_schema(忽略指定的库不再同步可以配置多个)】 3.systemctl reatart mysqld 重启mysql服务 4.开启mysql 授权先进入mysql主库。然后执行命令grant replication slave on . to root% identified by root; 授权 5.grant all privileges on . to root% identified by root; 6.flush privileges;刷新授权操作 7.show master status;查看数据库的状态 8.进入从库的配置。同样修改my.cof 【server-id2 relay_logmysql-relay-bin read_only1】 9.重新启动从库使修改的配置生效。 10.查看从库的状态show slave status; 11.change master to master_host192.168.95.130,master_port3306,master_userroot,master_passwordroot,master_log_filemysql-bin.000002,master_log_pos869;//设置与master的连接命令针对binlog文件 12.对主库进行一些操作看一下从库有没有记录数据。 13.追加从库的话需要把历史数据给同步过去 mysqldump --all-databases mysql_backup_all.sql -uroot -p 将数据生成sql文件。
半同步复制实战 主库 1.查看是否支持动态的加载select have_dynamic_loading; 2.show plugins;查看可支持的插件 3.install plugin rpl_semi_sync_master soname semisync_master.so;安装插件 4.show variables like %semi%;查看半同步复制 5.设置开启半同步复制功能 set global rpl_semi_sync_master_enabled1;set global set global rpl_semi_sync_master_timeout1000; 从库 1.install plugin rpl_semi_sync_slave soname semisync_slave.so; 安装插件 2.set global rpl_semi_sync_slave_enabled1; 3.stop slave; start slave; 在var文件夹下查看mysql.log就能看到我们的执行日志。
并行复制实战 主库 1.show variables like %binlog_group%; 2.set global binlog_group_commit_sync_delay1000;每组同步提交延迟设置 3.set global binlog_group_commit_sync_no_delay_count100;一个组里面有多少事务数 从库 1.show variables like %slave% 2.set global slave_parallel_typeLOGICAL_CLOCK;// 设置 3.set global slave_parallel_workers 8;// 设置线程数 4.show variables like %relay_log%; 5.set global relay_log_recovery1; 【可读模式下需要进入my.cnf中添加属性】 set global relay_log_info_repositorytable;
2.4 读写分离
主从同步延迟问题解决方案
写后立即读写操作完成的一段时间内读操作去主库中等过了这段时间后读操作去从库中。二次查询读请求来了之后先去从库中读如果从库中没有再去请求主库这样做相当于把读压力又返还给了主库。为了避免恶意攻击建议对数据库访问API进行封装可以有效的提高安全性降低耦合度。根据业务划分对于一些经常用到的业务实时性要求比较高的读写操作都可以在主库。而将一些冷门业务不经常用到的实时性要求不高可以去从库中读。这需要开发人员根据业务进行判断。
读写分离实战 - 借助中间件
1.新增一台主机134proxy (记着关闭防火墙等操作) 2.下载mysql-proxy-0.8.8.5-linux-el6-x86-64bit.tar.gz 3.tar -xzvf mysql-proxy-0.8.8.5-linux-el6-x86-64bit.tar.gz 4.vim /etc/mysql-proxy.cnf : [mysql-proxy] userroot admin-usernameroot //用户名 admin-passwordroot //密码 proxy-address192.168.95.134:4040 // 代理服务的地址 proxy-backend-addresses192.168.59.130:3306 //设置写库的地址 proxy-read-only-bankend-addresses192.168.95.132:3306,ip:port,ip:port // 设置读库的地址 proxy-lua-script/root/mysql-proxy-0.8.8.5-linux-el6-x86-64bit/share/doc/mysql-proxy/rw-splitting.lua //指定lua脚本运行位置 log-file/var/log/mysql-proxy.log //日志位置 log-leveldebug //日志级别 daemontrue //进程方式后台进行 keepalivetrue //是否尝试重启 5.chmod 660 /etc/mysql-proxy.cnf //指定文件权限可读可写 6.修改lua脚本的最小连接数为1方便后续测试看到效果vim mysql-proxy-0.8.8.5-linux-el6-x86-64bit/share/doc/mysql-proxy/rw-splitting.lua 7.cd mysql-proxy-0.8.8.5-linux-el6-x86-64bit/bin 进入到bin路径下开始启动 ./mysql-proxy --defaluts-file/etc/mysql-proxy.cnf
3.双主模式
3.1 适用场景
概念双主模式是指两台服务器互为主从其中任意一台的数据发生变更都会同步到另外一台服务器的数组库。
使用双主双写还是双主单写 建议使用双主单写原因是双主双写存在以下问题
ID冲突如果在A表写入某些数据数据同步到B表时还没结束此刻再从B表写入假设此时ID是自增的话就容易和从A表同步过来的数据发生ID冲突。可以采用mysql自动增长步长来解决问题但是这样对数据库的扩展和运维都不太又好更新丢失同一条记录在两个主库中进行更新会发生前面的覆盖掉后面的更新。
双主模式实战
1.在之前配置的master主机上进行修改vim /etc/my.cnf relay_logmysql-relay-bin log_slave_updates1 #如果是双主双写为了避免id冲突可以设置下面两个参数id从1开始以2递增 auto_increment_offset1 auto_increment_increment2 2.重新启动mysqlsystemctl restart mysqld 3.进入mysql命令端show master status 4.开始配置另外一台新的master重复之前的操作。show master status; 5.开始指定master1和master2互相复制。以下操作master1和master2都要指定互相进行指定。 6.change master to master_host192.168.95.133,master_port3306,master_userroot,master_passwordroot,master_log_filemysql-bin.000001,master_log_pos884; 然后启动slave:start slave;
测试create table test1(id int primary key auto_increment,name varchar(20))engineinnodb charsetutf8;
3.2 MMM架构
3.3 MHA架构
3.4 主备切换
五、互联网海量数据处理实战
六、Mysql第三方工具实战
七、MySql性能优化
1.系统配置优化
1.1 保证从内存中读取数据
mysql会在内存中保存一些数据通过LRU算法将一些不常用的数据写入到磁盘中。要尽可能的扩大内存的数据存储这样的话会提高查询效率。
默认mysql的使用内存存储数据大小为125M要根据场景需要如果某台服务器只是用来部署mysql的可以将mysql的使用内存大小调整到总内存的3/4或者4/5甚至更多如果这台服务器还部署了其他的应用程序那就不宜调整那么高根据实际情况尽可能的多调整。
调整步骤
通过命令show global status like‘innodb_buffer_pool_pages_%’可以查看innodb_buffer_pool的一些参数当前状态假如看到了innodb_buffer_pool_pages_free 0表示内存已经用光。修改my.cnf文件。加入innodb_buffer_pool_size 750M表示将mysql内存使用空间扩大到了750M。
1.2 数据预热
默认情况下只有当数据被读取过一次后才能将数据缓存在innodb_buffer_pool中。
因此我们可以将数据进行预热在mysql启动时就将硬盘中所有的数据加载到内存中数据预热能够提高查询的效率。
1数据预热的脚本
SELECT DISTINCTCONCAT(SELECT ,ndxcollist, FROM ,db,.,tb, ORDER BY ,ndxcollist,;) SelectQueryToLoadCacheFROM(SELECTengine,table_schema db,table_name tb,index_name,GROUP_CONCAT(column_name ORDER BY seq_in_index)
ndxcollistFROM(SELECTB.engine,A.table_schema,A.table_name,A.index_name,A.column_name,A.seq_in_indexFROMinformation_schema.statistics A INNER JOIN(SELECT engine,table_schema,table_nameFROM information_schema.tables WHEREengineInnoDB) B USING (table_schema,table_name)WHERE B.table_schema NOT IN (information_schema,mysql)ORDER BY table_schema,table_name,index_name,seq_in_index) AGROUP BY table_schema,table_name,index_name) AA
ORDER BY db,tb;将该脚本保存为loadtomem.sql
2执行命令
mysql -uroot -proot -AN /root/loadtomem.sql /root/loadtomem.sql(3) 在需要数据预热时重启数据库
mysql -uroot /root/loadtomem.sql /dev/null 21 1.3 降低磁盘的I/O次数
(1) 增大redo_log减少落盘次数
将innodb_log_file_size设置成0.25 * innodb_buffer_pool_size
(2) 生产中不开启慢查询日志只有遇到问题时才开启慢查询日志排查问题。
(3) 写redo_log策略innodb_flush_log_at_trx_commit设置为0和2
如果不涉及安全性比较高的金融系统操作或者事务要求都特别小是可以将redo_log的策略设置为0或者2以减少I/O次数
1.4 提高磁盘的读写能力
使用SSD或者内存硬盘
2.表结构设计优化
2.1 设计中间表
一般针对于统计类型的功能实时性不高的可以设计中间表。
2.2 设计冗余字段
为了减少表之间的关联应适当的增加一些冗余的字段可以有效的提高查询效率。
2.3 拆表
对于单表中的字段太多比如一个表中有100条数据可以进行拆表如果不经常使用的一些属性以及存储数据较多的一些字段可以单独拆出一个表来。
2.4 主键优化
每张表建议都要有一个主键主键索引建议设置为int类型自增原则不考虑分布式场景下时可如此分布式下建议使用雪花算法。
2.5 字段的设计
数据库的表越小那么在它上面执行查询效率也就越高。
因此再给数据库表字段设置宽度时尽可能的设计的小。
另外尽量把字段设置为NOT NULL这样表中不存在NULL值在查询的时候就不需要单独再去比较NULL值了mysql中的NULL值查询很耗费资源。
对于一些省份等字段可以考虑将字段设置为ENUM属性因为在mysql数据库中ENUM属性的查询和数值效率差不多高要高于varchar类型下面将写一段关于ENUM类型使用的代码。
# 首先我本地数据库中存在这么一个表格persion3它的原本字段有(id,name,sex,address,time)
# 首先创建该类型的值时要初始化几个值比如要增加province字段时默认要在山东河南江苏中选择城市
alter table persion3 add column province enum(山东,河南,江苏) collate utf8_bin default NULL comment 省份 after time;# 然后新增一条数据时需要在enum定义的组里面写
insert into persion3 values(1,zae,1,北京,now(),山东)# 下面演示一种错误的写法因为山西没有在ENUM中定义
insert into persion3 values(1,zae,1,北京,now(),山西)另外设置字段时能用数值的就用数值例如sex可以使用0或者1
3.sql语句以及索引优化
4.Mysql开发规约
5.复杂sql优化实践
