MySql读写分离.docx

MySql读写分离.docx

《MySql读写分离.docx》由会员分享，可在线阅读，更多相关《MySql读写分离.docx（11页珍藏版）》请在冰豆网上搜索。

MySql读写分离

MySql读写分离

1.MySQL主从复制入门

首先，看一个图：

影响MySQL-A数据库的操作，在数据库执行后，都会写入本地的日志系统A中。

假设，实时的将变化了的日志系统中的数据库事件操作，在MYSQL-A的3306端口，通过网络发给MYSQL-B。

MYSQL-B收到后，写入本地日志系统B，然后一条条的将数据库事件在数据库中完成。

那么，MYSQL-A的变化，MYSQL-B也会变化，这样就是所谓的MYSQL的复制，即MYSQLreplication。

在上面的模型中，MYSQL-A就是主服务器，即master，MYSQL-B就是从服务器，即slave。

日志系统A，其实它是MYSQL的日志类型中的二进制日志，也就是专门用来保存修改数据库表的所有动作，即binlog。

【注意MYSQL会在执行语句之后，释放锁之前，写入二进制日志，确保事务安全】

日志系统B，并不是二进制日志，由于它是从MYSQL-A的二进制日志复制过来的，并不是自己的数据库变化产生的，有点接力的感觉，称为中继日志，即relaylog。

可以发现，通过上面的机制，可以保证MYSQL-A和MYSQL-B的数据库数据一致，但是时间上肯定有延迟，即MYSQL-B的数据是滞后的。

【即便不考虑什么网络的因素，MYSQL-A的数据库操作是可以并发的执行的，但是MYSQL-B只能从relaylog中读一条，执行下。

因此MYSQL-A的写操作很频繁，MYSQL-B很可能跟不上。

】

2.主从复制的几种方式

同步复制

所谓的同步复制，意思是master的变化，必须等待slave-1,slave-2,...,slave-n完成后才能返回。

这样，显然不可取，也不是MYSQL复制的默认设置。

比如，在WEB前端页面上，用户增加了条记录，需要等待很长时间。

异步复制

如同AJAX请求一样。

master只需要完成自己的数据库操作即可。

至于slaves是否收到二进制日志，是否完成操作，不用关心。

MYSQL的默认设置。

半同步复制

master只保证slaves中的一个操作成功，就返回，其他slave不管。

这个功能，是由google为MYSQL引入的。

3.主从复制分析

问题1：

master的写操作，slaves被动的进行一样的操作，保持数据一致性，那么slave是否可以主动的进行写操作？

假设slave可以主动的进行写操作，slave又无法通知master，这样就导致了master和slave数据不一致了。

因此slave不应该进行写操作，至少是slave上涉及到复制的数据库不可以写。

实际上，这里已经揭示了读写分离的概念。

问题2：

主从复制中，可以有N个slave,可是这些slave又不能进行写操作，要他们干嘛？

可以实现数据备份。

类似于高可用的功能，一旦master挂了，可以让slave顶上去，同时slave提升为master。

异地容灾，比如master在北京，地震挂了，那么在上海的slave还可以继续。

主要用于实现scaleout,分担负载,可以将读的任务分散到slaves上。

【很可能的情况是，一个系统的读操作远远多于写操作，因此写操作发向master，读操作发向slaves进行操作】

问题3：

主从复制中有master,slave1,slave2,...等等这么多MYSQL数据库，那比如一个JAVAWEB应用到底应该连接哪个数据库?

当然，我们在应用程序中可以这样，insert/delete/update这些更新数据库的操作，用connection（formaster）进行操作，select用connection（forslaves）进行操作。

那我们的应用程序还要完成怎么从slaves选择一个来执行select，例如简单的轮循算法。

这样的话，相当于应用程序完成了SQL语句的路由，而且与MYSQL的主从复制架构非常关联，一旦master挂了，某些slave挂了，那么应用程序就要修改了。

能不能让应用程序与MYSQL的主从复制架构没有什么太多关系呢？

可以看下面的图：

找一个组件，applicationprogram只需要与它打交道，用它来完成MYSQL的代理，实现SQL语句的路由。

mysqlproxy并不负责，怎么从众多的slaves挑一个？

可以交给另一个组件（比如haproxy）来完成。

这就是所谓的MYSQLREADWRITESPLITE，MYSQL的读写分离。

问题4：

如果mysqlproxy,direct,master他们中的某些挂了怎么办？

总统一般都会弄个副总统，以防不测。

同样的，可以给这些关键的节点来个备份。

问题5：

当master的二进制日志每产生一个事件，都需要发往slave，如果我们有N个slave,那是发N次，还是只发一次？

如果只发一次，发给了slave-1，那slave-2,slave-3,...它们怎么办？

显然，应该发N次。

实际上，在MYSQLmaster内部，维护N个线程，每一个线程负责将二进制日志文件发往对应的slave。

master既要负责写操作，还的维护N个线程，负担会很重。

可以这样，slave-1是master的从，slave-1又是slave-2,slave-3,...的主，同时slave-1不再负责select。

slave-1将master的复制线程的负担，转移到自己的身上。

这就是所谓的多级复制的概念。

问题6：

当一个select发往mysqlproxy，可能这次由slave-2响应，下次由slave-3响应，这样的话，就无法利用查询缓存了。

应该找一个共享式的缓存，比如memcache来解决。

将slave-2,slave-3,...这些查询的结果都缓存至mamcache中。

问题7：

随着应用的日益增长，读操作很多，我们可以扩展slave，但是如果master满足不了写操作了，怎么办呢？

scaleon?

更好的服务器？

没有最好的，只有更好的，太贵了。

。

。

scaleout?

主从复制架构已经满足不了。

可以分库【垂直拆分】，分表【水平拆分】。

MySql查询缓存

1.缓存机制

MySQL缓存机制简单的说就是缓存sql文本及查询结果，如果运行相同的sql，服务器直接从缓存中取到结果，而不需要再去解析和执行sql。

如果表更改了，那么使用这个表的所有缓冲查询将不再有效，查询缓存值的相关条目被清空。

更改指的是表中任何数据或是结构的改变，包括INSERT、UPDATE、DELETE、TRUNCATE、ALTERTABLE、DROPTABLE或DROPDATABASE等，也包括那些映射到改变了的表的使用MERGE表的查询。

显然，这对于频繁更新的表，查询缓存是不适合的，而对于一些不常改变数据且有大量相同sql查询的表，查询缓存会节约很大的性能。

查询必须是完全相同的（逐字节相同）才能够被认为是相同的。

另外，同样的查询字符串由于其它原因可能认为是不同的。

使用不同的数据库、不同的协议版本或者不同默认字符集的查询被认为是不同的查询并且分别进行缓存。

下面sql查询缓存认为是不同的：

1.SELECT * FROM tbl_name 

2.Select * from tbl_name 

2.MySQL检查缓存命中的规则

1.在检查缓存的时候，MySQL不会对语句进行解析、正则化或者参数化，它精确地使用客户端传来的查询语句和其他数据。

只要字符大小写、空格或者注释有一点点不同，查询缓存就认为这是一个不同的查询

2.查询缓存不会存储有不确定结果的查询。

因此，任何一个包含不确定函数（比如NOW（）或CURRENT_DATE（））的查询不会被缓存。

同样地，CURRENT_USER（）或CONNECTION_ID（）这些由不同用户执行，将会产生不同的结果的查询也不会被缓存。

事实上，查询缓存不会缓存引用了用户自定义函数、存储函数、用户自定义变量、临时表、mysql数据库中的表或者任何一个有列级权限的表的查询

3.查询必须是完全相同的（逐字节相同）才能够被认为是相同的。

另外，同样的查询字符串由于其它原因可能认为是不同的。

使用不同的数据库、不同的协议版本或者不同默认字符集的查询被认为是不同的查询并且分别进行缓存。

3.开启查询缓存的开销

1.读取查询在开始之前必须要检查缓存。

2.如果查询是可以被缓存的，但是不在缓存中，那么在产生结果之后进行保存会带来一些额外的开销。

写入数据的查询也会有额外的开销，因为它必须使缓存中相关的数据表失效。

这些开销相对来说较小，所以查询缓存还是很有好处的。

但是，稍后你会看到，额外的开销有可能也会增加。

从缓存中受益最多的查询可能是需要很多资源来产生结果，但是不需要很多空间来保存的类型。

所以用于存储、返回和失效的代价都较小。

聚集查询，比如从大表中利用COUNT（）产生较小的结果，就符合这个范畴。

4.Msyql缓存状态的查看

have_query_cache的值如果是yes表示开启缓存

Binlog_cache_size:

默认大小是32768即32K

Max_binlog_cache_size:

默认值是18446744073709547520，这个值很大，够我们使用的了。

此参数和binlog_cache_size相对应，代表binlog所能使用的cache最大使用大小。

如果系统中事务过多，而此参数值设置有小，则会报错。

query_cache_limit：

允许Cache的单条Query结果集的最大容量，默认是1MB，超过此参数设置的Query结果集将不会被Cache

query_cache_min_res_unit：

设置QueryCache中每次分配内存的最小空间大小，也就是每个Query的Cache最小占用的内存空间大小

query_cache_size：

设置QueryCache所使用的内存大小，默认值为0，大小必须是1024的整数倍，如果不是整数倍，MySQL会自动调整降低最小量以达到1024的倍数

query_cache_type：

控制QueryCache功能的开关，可以设置为0（OFF）,1（ON）和2（DEMAND）三种，意义分别如下：

0（OFF）：

关闭QueryCache功能，任何情况下都不会使用QueryCache

1（ON）：

开启QueryCache功能，但是当SELECT语句中使用的SQL_NO_CACHE提示后，将不使用QueryCache

2（DEMAND）：

开启QueryCache功能，但是只有当SELECT语句中使用了SQL_CACHE提示后，才使用QueryCache

query_cache_wlock_invalidate：

控制当有写锁定发生在表上的时刻是否先失效该表相关的QueryCache，如果设置为1（TRUE），则在写锁定的同时将失效该表相关的所有QueryCache，如果设置为0（FALSE）则在锁定时刻仍然允许读取该表相关的QueryCache。

检查查询缓存使用情况

此时的查询缓存命中率：

175/（175+4）=97%；由于个人的测试数据库，查询较少，更行更少，命中率颇高。

嘿嘿~~

MySQL 提供了一系列的 GlobalStatus来记录 QueryCache的当前状态，具体如下：

Qcache_free_blocks：

目前还处于空闲状态的 QueryCache中内存 Block数目

Qcache_free_memory：

目前还处于空闲状态的 QueryCache内存总量

Qcache_hits：

QueryCache命中次数

Qcache_inserts：

向 QueryCache中插入新的QueryCache的次数，也就是没有命中的次数

Qcache_lowmem_prunes：

当 QueryCache内存容量不够，需要从中删除老的QueryCache以给新的 Cache 对象使用的次数

Qcache_not_cached：

没有被 Cache的SQL数，包括无法被 Cache的 SQL 以及由于query_cache_type设置的不会被Cache的 SQL

Qcache_queries_in_cache：

目前在 QueryCache中的SQL数量

Qcache_total_blocks：

QueryCache中总的Block数量

清空缓存

之前的Qcache_hits Qcache_inserts均被清空

MySql安全性

1.Sql注入

SQL注入是攻击者通过把恶意SQL命令插入到Web表单的输入域或页面请求的查询字符串中，来达到欺骗服务器执行恶意的SQL命令的一种攻击方式。

利用SQL注入漏洞，攻击者可以操纵数据库的数据（如得到数据库中的机密数据、随意更改数据库中的数据、删除数据库等等），在得到一定权限后还可以挂马，甚至得到整台服务器的管理员权限。

由于SQL注入是通过网站正常端口（通常为80端口）来提交恶意SQL语句，表面上看起来和正常访问网站没有区别，如果不仔细查看WEB日志很难发现此类攻击，隐蔽性非常高。

一旦程序出现SQL注入漏洞，危害相当大，所以我们对此应该给予足够的重视。

2.Sql注入原理

SQL注入的本质是恶意攻击者将SQL代码插入或添加到程序的参数中，而程序并没有对传入的参数进行正确处理，导致参数中的数据会被当做代码来执行，并最终将执行结果返回给攻击者。

3防御

使用预编译语句，绑定变量。

对传入的参数进行验证，确保符合应用中定义的标准。

MySql优化

最常见的系统瓶颈有以下几种：

1.磁盘搜索。

它慢慢地在磁盘中搜索数据块。

对现代磁盘来说，平时的搜索时间基本上小于10毫秒，因此理论上每秒钟可以做100次磁盘搜索。

这个时间对于全新的新磁盘来说提高的不多，并且对于只有一个表的情况也是如此。

加快搜索时间的方法是将数据分开存放到多个磁盘中。

2.磁盘读/写。

当磁盘在正确的位置上时，就需要读取数据。

对现代磁盘来说，磁盘吞吐量至少是10-20MB/秒。

这比磁盘搜索的优化更容易，因为可以从多个媒介中并行地读取数据。

3.CPU周期。

数据存储在主内存中（或者它已经在主内存中了），这就需要处理这些数据以得到想要的结果。

最常见的瓶颈是调度内存资源对数据集进行对比，如果结果集不大则还好。

4.内存带宽。

当CPU要将更多的数据存放在CPU缓存中时，主内存的带宽就是瓶颈了。

在大多数系统中，这不是常见的瓶颈，不过也是要注意的一个因素。

三个方面：

标准化，数据类型，索引

标准化

标准化是在数据库中组织数据的过程。

其中包括，根据设计规则创建表并在这些表间建立关系；通过取消冗余度与不一致相关性，该设计规则可以同时保护数据并提高数据的灵活性。

通常数据库标准化是让数据库设计符合某一级别的范式，通常满足第三范式即可。

也有第四范式（也称为BoyceCodd范式，BCNF））与第五范式存在，但是在实际设计中很少考虑。

忽视这些规则可能使得数据库的设计不太完美，但这不应影响功能。

标准化的特点：

1）所有的“对象”都在它自己的table中，没有冗余。

2）数据库通常由E-R图生成。

3）简洁，更新属性通常只需要更新很少的记录。

4）Join操作比较耗时。

5）Select，sort优化措施比较少。

6）适用于OLTP应用。

非标准化的特点：

1）在一张表中存储很多数据，数据冗余。

2）更新数据开销很大，更新一个属性可能会更新很多表，很多记录。

3）在删除数据是有可能丢失数据。

4）Select，order有很多优化的选择。

5）适用于DSS应用。

标准化和非标准化都有各自的优缺点，通常在一个数据库设计中可以混合使用，一部分表格标准化，一部分表格保留一些冗余数据：

1）对OLTP使用标准化，对DSS使用非标准化

2）使用物化视图。

MySQL不直接支持该数据库特性，但是可以用MyISAM表代替。

3）冗余一些数据在表格中，例如将ref_id和name存在同一张表中。

但是要注意更新问题。

4）对于一些简单的对象，直接使用value作为建。

例如IPaddress等

5）ReferencebyPRIMARY/UNIQUEKEY。

MySQL可以优化这种操作.

数据类型

最基本的优化之一就是使表在磁盘上占据的空间尽可能小。

这能带来性能非常大的提升，因为数据小，磁盘读入较快，并且在查询过程中表内容被处理所占用的内存更少。

同时，在更小的列上建索引，索引也会占用更少的资源。

可以使用下面的技术可以使表的性能更好并且使存储空间最小：

1）使用正确合适的类型，不要将数字存储为字符串。

2）尽可能地使用最有效（最小）的数据类型。

MySQL有很多节省磁盘空间和内存的专业化类型。

3）尽可能使用较小的整数类型使表更小。

例如，MEDIUMINT经常比INT好一些，因为MEDIUMINT列使用的空间要少25%。

4）如果可能，声明列为NOTNULL。

它使任何事情更快而且每列可以节省一位。

注意如果在应用程序中确实需要NULL，应该毫无疑问使用它，只是避免默认地在所有列上有它。

5）对于MyISAM表，如果没有任何变长列（VARCHAR、TEXT或BLOB列），使用固定尺寸的记录格式。

这比较快但是不幸地可能会浪费一些空间。

即使你已经用CREATE选项让VARCHAR列ROW_FORMAT=fixed，也可以提示想使用固定长度的行。

6）使用samplecharacterset，例如latin1。

尽量少使用utf-8，因为utf-8占用的空间是latin1的3倍。

可以在不需要使用utf-8的字段上面使用latin1，例如mail，url等。

索引

所有MySQL列类型可以被索引。

对相关列使用索引是提高SELECT操作性能的最佳途径。

使用索引应该注意以下几点：

1）MySQL只会使用前缀，例如key（a,b）…whereb=5将使用不到索引。

2）要选择性的使用索引。

在变化很少的列上使用索引并不是很好，例如性别列。

3）在Unique列上定义Uniqueindex。

4）避免建立使用不到的索引。

5）在Btreeindex中（InnoDB使用Btree），可以在需要排序的列上建立索引。

6）避免重复的索引。

7）避免在已有索引的前缀上建立索引。

例如：

如果存在index（a，b）则去掉index（a）。

8）控制单个索引的长度。

使用key（name（8））在数据的前面几个字符建立索引。

9）越是短的键值越好，最好使用integer。

10）在查询中要使用到索引（使用explain查看），可以减少读磁盘的次数，加速读取数据。

11）相近的键值比随机好。

Auto_increment就比uuid好。

12）Optimizetable可以压缩和排序index，注意不要频繁运行。

13）Analyzetable可以更新数据。