MySql读写分离

1、MySql读写分离1.MySQL主从复制入门首先，看一个图：影响MySQL-A数据库的操作，在数据库执行后，都会写入本地的日志系统A中。假设，实时的将变化了的日志系统中的数据库事件操作，在MYSQL-A的3306端口，通过网络发给MYSQL-B。MYSQL-B收到后，写入本地日志系统B，然后一条条的将数据库事件在数据库中完成。那么，MYSQL-A的变化，MYSQL-B也会变化，这样就是所谓的MYSQL的复制，即MYSQL replication。在上面的模型中，MYSQL-A就是主服务器，即master，MYSQL-B就是从服务器，即slave。日志系统A，其实它是MYSQL的日志类型中的二进

2、制日志，也就是专门用来保存修改数据库表的所有动作，即bin log。【注意MYSQL会在执行语句之后，释放锁之前，写入二进制日志，确保事务安全】日志系统B，并不是二进制日志，由于它是从MYSQL-A的二进制日志复制过来的，并不是自己的数据库变化产生的，有点接力的感觉，称为中继日志，即relay log。可以发现，通过上面的机制，可以保证MYSQL-A和MYSQL-B的数据库数据一致，但是时间上肯定有延迟，即MYSQL-B的数据是滞后的。【即便不考虑什么网络的因素，MYSQL-A的数据库操作是可以并发的执行的，但是MYSQL-B只能从relay log中读一条，执行下。因此MYSQL-A的写操作

3、很频繁，MYSQL-B很可能跟不上。】2.主从复制的几种方式同步复制所谓的同步复制，意思是master的变化，必须等待slave-1,slave-2,.,slave-n完成后才能返回。这样，显然不可取，也不是MYSQL复制的默认设置。比如，在WEB前端页面上，用户增加了条记录，需要等待很长时间。异步复制如同AJAX请求一样。master只需要完成自己的数据库操作即可。至于slaves是否收到二进制日志，是否完成操作，不用关心。MYSQL的默认设置。半同步复制master只保证slaves中的一个操作成功，就返回，其他slave不管。这个功能，是由google为MYSQL引入的。3.主从复制分析

4、问题1：master的写操作，slaves被动的进行一样的操作，保持数据一致性，那么slave是否可以主动的进行写操作？假设slave可以主动的进行写操作，slave又无法通知master，这样就导致了master和slave数据不一致了。因此slave不应该进行写操作，至少是slave上涉及到复制的数据库不可以写。实际上，这里已经揭示了读写分离的概念。问题2：主从复制中，可以有N个slave,可是这些slave又不能进行写操作，要他们干嘛？可以实现数据备份。类似于高可用的功能，一旦master挂了，可以让slave顶上去，同时slave提升为master。异地容灾，比如master在北京，地

5、震挂了，那么在上海的slave还可以继续。主要用于实现scale out,分担负载,可以将读的任务分散到slaves上。【很可能的情况是，一个系统的读操作远远多于写操作，因此写操作发向master，读操作发向slaves进行操作】问题3：主从复制中有master,slave1,slave2,.等等这么多MYSQL数据库，那比如一个JAVA WEB应用到底应该连接哪个数据库?当然，我们在应用程序中可以这样，insert/delete/update这些更新数据库的操作，用connection(for master)进行操作，select用connection(for slaves)进行操作。那我们

6、的应用程序还要完成怎么从slaves选择一个来执行select，例如简单的轮循算法。这样的话，相当于应用程序完成了SQL语句的路由，而且与MYSQL的主从复制架构非常关联，一旦master挂了，某些slave挂了，那么应用程序就要修改了。能不能让应用程序与MYSQL的主从复制架构没有什么太多关系呢？可以看下面的图： 找一个组件，application program只需要与它打交道，用它来完成MYSQL的代理，实现SQL语句的路由。mysql proxy并不负责，怎么从众多的slaves挑一个？可以交给另一个组件(比如haproxy)来完成。这就是所谓的MYSQL READ WRITE SPL

7、ITE，MYSQL的读写分离。问题4：如果mysql proxy , direct , master他们中的某些挂了怎么办？总统一般都会弄个副总统，以防不测。同样的，可以给这些关键的节点来个备份。问题5：当master的二进制日志每产生一个事件，都需要发往slave，如果我们有N个slave,那是发N次，还是只发一次？如果只发一次，发给了slave-1，那slave-2,slave-3,.它们怎么办？显然，应该发N次。实际上，在MYSQL master内部，维护N个线程，每一个线程负责将二进制日志文件发往对应的slave。master既要负责写操作，还的维护N个线程，负担会很重。可以这样，sl

8、ave-1是master的从，slave-1又是slave-2,slave-3,.的主，同时slave-1不再负责select。slave-1将master的复制线程的负担，转移到自己的身上。这就是所谓的多级复制的概念。问题6：当一个select发往mysql proxy，可能这次由slave-2响应，下次由slave-3响应，这样的话，就无法利用查询缓存了。应该找一个共享式的缓存，比如memcache来解决。将slave-2,slave-3,.这些查询的结果都缓存至mamcache中。问题7：随着应用的日益增长，读操作很多，我们可以扩展slave，但是如果master满足不了写操作了，怎么办

9、呢？scale on ?更好的服务器？ 没有最好的，只有更好的，太贵了。scale out ? 主从复制架构已经满足不了。可以分库【垂直拆分】，分表【水平拆分】。MySql查询缓存1.缓存机制MySQL缓存机制简单的说就是缓存sql文本及查询结果，如果运行相同的sql，服务器直接从缓存中取到结果，而不需要再去解析和执行sql。如果表更改 了，那么使用这个表的所有缓冲查询将不再有效，查询缓存值的相关条目被清空。更改指的是表中任何数据或是结构的改变，包括INSERT、UPDATE、 DELETE、TRUNCATE、ALTER TABLE、DROP TABLE或DROP DATABASE等，也包括那

10、些映射到改变了的表的使用MERGE表的查询。显然，这对于频繁更新的表，查询缓存是不适合的，而对于一些不常改变数据且有 大量相同sql查询的表，查询缓存会节约很大的性能。查询必须是完全相同的(逐字节相同)才能够被认为是相同的。另外，同样的查询字符串由于其它原因可能认为是不同的。使用不同的数据库、不同的协议版本或者不同 默认字符集的查询被认为是不同的查询并且分别进行缓存。下面sql查询缓存认为是不同的：1. SELECT * FROM tbl_name  2. Select * from tbl_name  2.MyS

11、QL 检查缓存命中的规则1. 在检查缓存的时候，MySQL 不会对语句进行解析、正则化或者参数化，它精确地使用客户端传来的查询语句和其他数据。只要字符大小写、空格或者注释有一点点不同，查询缓存就认为这是一个不同的查询2. 查询缓存不会存储有不确定结果的查询。因此，任何一个包含不确定函数（比如NOW()或CURRENT_DATE()）的查询不会被缓存。同样地，CURRENT_USER()或CONNECTION_ID()这些由不同用户执行，将会产生不同的结果的查询也不会被缓存。事实上，查询缓存不会缓存引用了用户自定义函数、存储函数、用户自定义变量、临时表、mysql 数据库中的表或者任何一个有列级

12、权限的表的查询3. 查询必须是完全相同的(逐字节相同)才能够被认为是相同的。另外，同样的查询字符串由于其它原因可能认为是不同的。使用不同的数据库、不同的协议版本或者不同 默认字符集的查询被认为是不同的查询并且分别进行缓存。3.开启查询缓存的开销1. 读取查询在开始之前必须要检查缓存。2. 如果查询是可以被缓存的，但是不在缓存中，那么在产生结果之后进行保存会带来一些额外的开销。写入数据的查询也会有额外的开销，因为它必须使缓存中相关的数据表失效。这些开销相对来说较小，所以查询缓存还是很有好处的。但是，稍后你会看到，额外的开销有可能也会增加。从缓存中受益最多的查询可能是需要很多资源来产生结果，但是不

13、需要很多空间来保存的类型。所以用于存储、返回和失效的代价都较小。聚集查询，比如从大表中利用COUNT()产生较小的结果，就符合这个范畴。 4.Msyql缓存状态的查看have_query_cache 的值如果是yes表示开启缓存Binlog_cache_size:默认大小是32768即32KMax_binlog_cache_size: 默认值是18446744073709547520，这个值很大，够我们使用的了。此参数和binlog_cache_size相对应，代表binlog所能使用的cache最大使用大小。如果系统中事务过多，而此参数值设置有小，则会报错。query_cache_

14、limit：允许 Cache 的单条 Query 结果集的最大容量，默认是1MB，超过此参数设置的 Query 结果集将不会被 Cachequery_cache_min_res_unit：设置 Query Cache 中每次分配内存的最小空间大小，也就是每个 Query 的 Cache 最小占用的内存空间大小query_cache_size：设置 Query Cache 所使用的内存大小，默认值为0，大小必须是1024的整数倍，如果不是整数倍，MySQL 会自动调整降低最小量以达到1024的倍数query_cache_type：控制 Query Cache 功能的开关，可以设置为0(OFF),

15、1(ON)和2(DEMAND)三种，意义分别如下：0(OFF)：关闭 Query Cache 功能，任何情况下都不会使用 Query Cache1(ON)：开启 Query Cache 功能，但是当 SELECT 语句中使用的 SQL_NO_CACHE 提示后，将不使用Query Cache2(DEMAND)：开启 Query Cache 功能，但是只有当 SELECT 语句中使用了 SQL_CACHE 提示后，才使用 Query Cachequery_cache_wlock_invalidate：控制当有写锁定发生在表上的时刻是否先失效该表相关的 Query Cache，如果设置为 1(TR

16、UE)，则在写锁定的同时将失效该表相关的所有 Query Cache，如果设置为0(FALSE)则在锁定时刻仍然允许读取该表相关的 Query Cache。检查查询缓存使用情况此时的查询缓存命中率：175/（175+4）=97%；由于个人的测试数据库，查询较少，更行更少，命中率颇高。嘿嘿MySQL 提供了一系列的 Global Status来记录 Query Cache的当前状态，具体如下：Qcache_free_blocks：目前还处于空闲状态的 Query Cache中内存 Block数目Qcache_free_memory：目前还处于空闲

17、状态的 Query Cache内存总量Qcache_hits：Query Cache命中次数Qcache_inserts：向 Query Cache中插入新的Query Cache的次数，也就是没有命中的次数Qcache_lowmem_prunes：当 Query Cache内存容量不够，需要从中删除老的Query Cache以给新的 Cache 对象使用的次数Qcache_not_cached：没有被 Cache的SQL数，包括无法被 Cache的 SQL 以及由于query_cache_type设置的

18、不会被Cache的 SQLQcache_queries_in_cache：目前在 Query Cache中的SQL数量Qcache_total_blocks：Query Cache中总的Block数量清空缓存之前的Qcache_hits  Qcache_inserts均被清空MySql安全性1.Sql注入SQL注入是攻击者通过把恶意SQL命令插入到Web表单的输入域或页面请求的查询字符串中，来达到欺骗服务器执行恶意的SQL命令的一种攻击方式。利用SQL注入漏洞，攻击者可以操纵数据库的数据（如得到数据库中的机密数据、随意更改数据库中的数据、删除数据库等等），在得到一

19、定权限后还可以挂马，甚至得到整台服务器的管理员权限。由于SQL注入是通过网站正常端口（通常为80端口）来提交恶意SQL语句，表面上看起来和正常访问网站没有区别，如果不仔细查看WEB日志很难发现此类攻击，隐蔽性非常高。一旦程序出现SQL注入漏洞，危害相当大，所以我们对此应该给予足够的重视。2.Sql注入原理SQL注入的本质是恶意攻击者将SQL代码插入或添加到程序的参数中，而程序并没有对传入的参数进行正确处理，导致参数中的数据会被当做代码来执行，并最终将执行结果返回给攻击者。3防御使用预编译语句，绑定变量。对传入的参数进行验证，确保符合应用中定义的标准。MySql优化最常见的系统瓶颈有以下几种：1

20、. 磁盘搜索。它慢慢地在磁盘中搜索数据块。对现代磁盘来说，平时的搜索时间基本上小于10毫秒，因此理论上每秒钟可以做100次磁盘搜索。这个时间对于全新的新磁盘来说提高的不多，并且对于只有一个表的情况也是如此。加快搜索时间的方法是将数据分开存放到多个磁盘中。2. 磁盘读/写。当磁盘在正确的位置上时，就需要读取数据。对现代磁盘来说，磁盘吞吐量至少是10-20MB/秒。这比磁盘搜索的优化更容易，因为可以从多个媒介中并行地读取数据。3. CPU周期。数据存储在主内存中（或者它已经在主内存中了），这就需要处理这些数据以得到想要的结果。最常见的瓶颈是调度内存资源对数据集进行对比，如果结果集不大则还好。4.

21、内存带宽。当CPU要将更多的数据存放在CPU缓存中时，主内存的带宽就是瓶颈了。在大多数系统中，这不是常见的瓶颈，不过也是要注意的一个因素。三个方面：标准化，数据类型，索引标准化 标准化是在数据库中组织数据的过程。其中包括，根据设计规则创建表并在这些表间建立关系；通过取消冗余度与不一致相关性，该设计规则可以同时保护数据并提高数据的灵活性。通常数据库标准化是让数据库设计符合某一级别的范式，通常满足第三范式即可。也有第四范式（也称为 Boyce Codd范式，BCNF)）与第五范式存在，但是在实际设计中很少考虑。忽视这些规则可能使得数据库的设计不太完美，但这不应影响功能。标准化的特点：1) 所有的“

22、对象”都在它自己的table中，没有冗余。2) 数据库通常由E-R图生成。3) 简洁，更新属性通常只需要更新很少的记录。4) Join操作比较耗时。5) Select，sort优化措施比较少。6) 适用于OLTP应用。非标准化的特点：1) 在一张表中存储很多数据，数据冗余。2) 更新数据开销很大，更新一个属性可能会更新很多表，很多记录。3) 在删除数据是有可能丢失数据。4) Select，order有很多优化的选择。5) 适用于DSS应用。标准化和非标准化都有各自的优缺点，通常在一个数据库设计中可以混合使用，一部分表格标准化，一部分表格保留一些冗余数据：1) 对OLTP使用标准化，对DSS使用

23、非标准化2) 使用物化视图。MySQL不直接支持该数据库特性，但是可以用MyISAM表代替。3) 冗余一些数据在表格中，例如将ref_id和name存在同一张表中。但是要注意更新问题。4) 对于一些简单的对象，直接使用value作为建。例如IP address等5) Reference by PRIMARY/UNIQUE KEY。MySQL可以优化这种操作.数据类型 最基本的优化之一就是使表在磁盘上占据的空间尽可能小。这能带来性能非常大的提升，因为数据小，磁盘读入较快，并且在查询过程中表内容被处理所占用的内存更少。同时，在更小的列上建索引，索引也会占用更少的资源。可以使用下面的技术可以使表的性能更好并且使存储空间最小：1) 使用正确合适的类型，不要将数字存储为字符串。2) 尽可能地使用最有效(最小)的数据类型。MySQL有很多节省磁盘空间和内存的专业化类型。3) 尽可能使用较小的整数类型使表更小。例如，MEDIUMINT经常比INT好一些，因为MEDIUMINT列使用的空间要少25%。4) 如果可能，声明列为NOT NULL。它使任何事情更快而且每列可以