mysql笔记(转)_对于密集型的应用来说 直接禁用查询缓存可能会降低系统的性能

mysql笔记(转)

 信仰与初衷 关注

今天看到一位朋友写的mysql笔记总结，觉得写的很详细很用心，这里转载一下，供大家参考下，也希望大家能关注他
原文地址: https://www.jianshu.com/p/47664afa249e

一、MySQL架构与历史

A.并发控制

1.共享锁（shared lock，读锁）：共享的，相互不阻塞的

2.排他锁（exclusive lock，写锁）：排他的，一个写锁会阻塞其他的写锁和读锁

B.事务

1.事务ACID

• 原子性（atomicity）一个事务必须被视为一个不可分割的最小工作单元，整个事务中所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作

• 一致性（consistency）数据库总是从一个一致性的状态转换到另外一个一致性的状态

• 隔离性（isolation）一个事务所做的修改在最终提交以前，对其他事务是不可见的

• 持久性（durability）一旦事务提交，则其所做的修改就会永久保存到数据库中

2.四种隔离级别

• READ UNCOMMITTED（未提交读），事务中的修改，即使没有提交，对其他事务也都是可见的，事务可以读取未提交的数据，也被称为脏读（Dirty Read），这个级别会导致很多问题

• READ COMMITTED（提交读），大多数数据库系统的默认隔离级别，一个事务开始时，只能“看见”已经提交的事务所做的修改，一个事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的，也叫不可重复读（nonrepeatable read），有可能出现幻读（Phantom Read），指的是当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行（Phantom Row）

• REPEATABLE READ（可重复读），通过InnoDB和XtraDB存储引擎，是MySQL的默认事务隔离级别

• SERIALIZABLE（可串行化）最高级别，通过强制事务串行执行，避免了幻读问题，会在读取的每一行数据上都加锁，可能导致大量的超时和锁争用的问题

3.死锁：指两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环的现象

4.事务日志：存储引擎在修改表的数据时只需要修改其内存拷贝，再把该修改行为记录到持久在硬盘上的事务日志中，而不用每次都将修改的数据本身持久到磁盘。事务日志持久以后，内存中被修改的数据在后台可以慢慢地刷回到磁盘，称为预写式日志（Write-Ahead Logging）

C.多版本并发控制

1.多版本并发控制（MVCC）是行级锁的一个变种，但是它在很多情况下避免了加锁操作，因此开销更低。虽然实现机制有所不同，但大都实现了非阻塞的读操作，写操作也只锁定必要的行

2.MVCC的实现，是通过保存数据在某个时间点的快照来实现的，有乐观和悲观两种，只在REPEATABLE READ和READ COMMITTED两个隔离级别下工作

D.MySQL的存储引擎

1.MySQL的.frm文件保存表的定义，SHOW TABLE STATUS显示表的相关信息

2.除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑InnoDB引擎

3.不要轻易相信MyISAM比InnoDB快之类的经验之谈，这个结论并不是绝对的

二、MySQL基准测试

A.为什么需要基准测试

1.基准测试可以观察系统在不同压力下的行为，评估系统的容量，掌握哪些是重要的变化，或者观察系统如何处理不同的数据

B.基准测试的策略

1.两种主要的策略：

• 针对整个系统的整体测试（集成式full-stack）

• 单独测试MySQL（单组件式single-component）

2.测试何种指标：

• 吞吐量，指单位时间内的事务处理数，常用的测试单位是每秒事务数（TPS），或每分钟事务数（TPM）

• 响应时间或者延迟，用于测试任务所需的整体时间，根据具体的应用，测试的时间单位可能是微秒、毫秒、秒或者分钟。通常使用百分比响应时间（percentile response time）来替代最大响应时间

• 并发性，需要关注的是正在工作中的并发操作，或者是同时工作中的线程数或者连接数，在测试期间记录MySQL数据库的Threads_running状态值

• 可扩展性，给系统增加一倍的工作，在理想情况下就能获得两倍的效果（即吞吐量增加一倍），对于容量规范非常有用，可以提供其他测试无法提供的信息，来帮助发现应用的瓶颈

C.基准测试方法

1.需要避免的一些常见错误：

• 使用真实数据的子集而不是全集

• 使用错误的数据分布

• 使用不真实的分布参数

• 在多用户场景中，只做单用户测试

• 在单服务器上测试分布式应用

• 与真实用户行为不匹配

• 反复执行同一个查询

• 没有检查错误

• 忽略了系统预热（warm up）的过程

• 使用默认的服务器配置

• 测试时间太短

2.应该建立将参数和结果文档化的规范，每一轮测试都必须进行详细记录

3.基准测试应该运行足够长的时间，需要在稳定状态下测试并观察

4.在执行基准测试时，需要尽可能多地收集被测试系统的信息

5.自动化基准测试可以防止测试人员偶尔遗漏某些步骤，或者误操作，另外也有助于归档整个测试过程，可以选择shell、php、perl等，要尽可能使所有测试过程都自动化，包括装载数据、系统预热、执行测试、记录结果等

D.基准测试工具

1.集成式测试工具：

• ab，测试HTTP服务器每秒最多可以处理多少请求

• http_load，和ab类似，但更加灵活

• jMeter，可以加载其他应用并测试其性能

2.单组件式测试工具

• mysqlslap，可以模拟服务器的负载，并输出计时信息

• MySQL Benchmark Suite(sql-bench)，单线程的，主要用于测试服务器执行查询的速度

• Super Smack，提供压力测试和负载生成，是一个复杂而强大的工具，可以模拟多用户访问，可以加载测试数据到数据库，并支持使用随机数据填充测试表

• Database Test Suite，类似某些工业标准测试的测试工具集

• Percona's TPCC-MySQWL Tool

• sysbench，多线程系统压测工具，可以根据影响数据库服务器性能的各种因素来评估系统的性能

三、服务器性能剖析

A.性能优化简介

1.性能，为完成某件任务所需要的时间度量，性能即响应时间，这是非常重要的原则

2.如果目标是降低响应时间，就需要理解为什么服务器执行查询需要这么多时间，然后去减少或者消除那些对获得查询结果来说不必要的工作。无法测量就无法有效地优化

3.性能剖析（profiling）是测量和分析时间花费在哪里的主要方法，一般有两个步骤：测量任务所花费的时间，对结果进行统计和排序

B.对应用程序进行性能剖析

1.性能瓶颈可能的影响因素：

• 外部资源

• 应用需要处理大量的数据

• 在循环中执行昂贵的操作

• 使用了低效的算法

2.PHP性能剖析工具：New Relic、xhprof、Ifp

C.剖析MySQL查询

1.剖析服务器负载

• 慢查询日志：5.1后long_query_time为0可以捕获所有的查询，查询的响应时间单位可以做到微秒级

• 生成剖析报告：pt-query-digest

2.剖析单条查询：

• SHOW PROFILES;

• SHOW [GLOBAL] STATUS;，返回一些计数器

D.诊断间歇性问题

1.尽量不要用试错的方式来解决问题，如果一时无法定位，可能是测量的方式不正确，或者测量的点选择有误，或者使用的工具不合适

2.确定单条查询问题还是服务器问题

• 使用SHOW GLOBAL STATUS

• 使用SHOW PROCESSLIST

• 使用查询日志

• 理解发现的问题：使得gnuplot或R，或其他绘图工具将结果绘制成图形

3.捕获诊断数据

• 诊断触发器：在问题出现时能够捕获数据的基础，有两个常见问题可能导致无法达到预期的结果：误报（false positive）或者漏检（false negative）,pt-stalk工具

• 收集数据：尽可能收集所有能收集的数据，但只在需要的时间段内收集，oprofile、strace、tcpdump、GDB堆栈跟踪、pt-collect、pt-stalk

• 解释结果数据：pt-mysql-summary、pt-summary输出结果打包，pt-sift得到样本汇总信息，pt-pmp

E.其他剖析工具

1.使用USER_STATISTICS表

2.使用strace，可以调查系统调用的情况

四、Schema与数据类型优化

A.选择优化的数据类型

1.数据类型的选择原则：

• 更小的通常更好

• 简单就好

• 尽量避免NULL

2.应该尽量只在对小数进行精确计算时才使用DECIMAL，使用int类型通过程序控制单位效果更好

3.使用VARCHAR合适的情况：字符串列的最大长度比平均长度大很多；列的更新很少，所以碎片不是问题；使用了像UTF-8这样复杂的字符集，每个字符都使用不同的字节数进行存储

4.CHAR适合存储很短的字符串，或者所有值都接近同一个长度；不容易产生碎片，在存储空间上更有效率

5.通常应该尽量使用TIMESTAMP，它比DATETIME空间效率更高

B.MySQL schema设计中的陷阱

1.不好的设计：

• 太多的列

• 太多的关联

• 全能的枚举

• 变相的枚举

• 非此发明（Not Invent Here）的NULL

C.范式和反范式

1.范式的优点：

• 范式化的更新操作通常比反范式化要快

• 当数据较好地范式化时，就只有很少或者没有重复数据，所以只需要修改更少的数据

• 范式化的表通常更小，可以更好地放在内存里，所以执行操作会更快

• 很少有多余的数据意味着检索列表数据时更少需要DISTINCT或者GROUP BY语句

2.范式化设计的缺点是通常需要关联

3.反范式的优点：避免关联，避免了随机I/O，能使用更有效的索引策略

D.缓存表和汇总表

1.有时提升性能最好的方法是同一张表中保存衍生的冗余数据，有时也需要创建一张完全独立的汇总表或缓存表

2.物化视图，MySQL并不原生支持，Flexviews

3.如果应用在表中保存计数器，则在更新计数器时可能踫到并发问题，创建一张独立的表存储计数器，可以帮助避免缓存失效

• 解决独立表并发问题可以建多行，根据id随机更新，然后统计时sum()

• 按天或小时可以单独建行，旧时间可定时任务合并到统一的一行

E.加快ALTER TABLE操作的速度

1.两种方式：

• 一是在一台不提供服务的机器上执行ALTER TABLE操作，然后和提供服务的主库进行切换

• 二是通过“影子拷贝”，创建一张新表，然后通过重命名和删表操作交换两张表及里面的数据

2.快速创建MyISAM索引，先禁用索引，导入数据，然后重新启用索引

五、创建高性能的索引

A.索引基础

1.索引可以包含一个或多个列的值，如果索引包含多个列，那么列的顺序也十分重要，因为MySQL只能高效地使用索引的最左前缀列

2.ORM工具能够产生符合逻辑的、合法的查询，除非只是生成非常基本的查询，否则它很难生成适合索引的查询

3.在MySQL中，索引是在存储引擎层而不是服务器层实现的，所以，并没有统一的索引标准：不同存储引擎的索引的工作方式并不一样，也不是所有的存储引擎都支持所有类型的索引

4.B-Tree意味着所有的值都是按顺序存储的，并且每一个叶子页到根的距离相同，能够加快访问数据的速度，从索引的根节点开始进行搜索，适用于全键值、键值范围或键前缀查找

image.png

5.B-Tree索引的限制：

• 如果不是按照索引的最左列开始查找，则无法使用索引

• 不能跳过索引中的列

• 如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找

6.哈希索引（hash index）基于哈希表实现，只有精确匹配索引所有列的查询才有效，只有Memory引擎显式支持哈希索引

7.哈希索引的限制：

• 哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行

• 哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序

• 哈希索引也不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的

• 只支持等值比较查询，不支持任何范围查询

• 访问哈希索引的数据非常快，除非有很多哈希冲突

• 如果哈希冲突很多的话，一些索引维护操作的代价也会很高

8.空间数据索引（R-Tree），MyISAM表支持空间索引，可以用作地理数据存储，开源数据库系统中对GIS的解决方案做得比较好的是PostgreSQL的PostGIS

9.全文索引，适用于MATCH AGAINST操作，而不是普通的WHERE条件操作

B.索引的优点

1.三个优点：

• 索引大大减少了服务器需要扫描的数据量

• 索引可以帮助服务器避免排序和临时表

• 索引可以将随机I/O变为顺序I/O

2.索引三星系统：

• 索引将相关的记录放到一起则获得一星

• 如果索引中的数据顺序和查找中的排序一致则获得二星

• 如果索引中的列包含了查询中需要的全部列则获得三星

C.高性能的索引策略

1.独立的列：如果查询中的列不是独立的，则MySQL不会使用索引。“独立的列”是指索引列不能是表达式的一部分，也不能是函数的参数

2.前缀索引和索引选择性

• 通常可以索引开始的部分字符，可以大大节约索引空间，但也会降低索引的选择性

• 索引的选择性是指，不重复的索引值（也称为基数，cardinality）和数据表的记录总数（#T）的比值，范围从1/#T到1之间，选择性越高则查询效率越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的行

• MySQL无法使用前缀索引做ORDERY BY和GROUP BY，也无法做覆盖扫描

3.选择合适的索引列顺序

• 正确的索引列顺序依赖于使用该索引的查询，并且同时需要考虑如何更好地满足排序和分组的需要

• 在一个多列B-Tree索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列

• 将选择性最高的列放到索引最前列

4.聚簇索引：并不是一种单独的索引类型，而是一种数据存储方式

• 最好避免随机的（不连续且值的分布范围非常大）聚簇索引，特别是对于I/O密集型的应用

5.覆盖索引：如果一个索引包含（或者说覆盖）所有需要查询的字段的值，就称为覆盖索引

• 覆盖索引必须要存储索引列的值，

6.如果EXPLAIN出来的type列的值为“index”，则说明MySQL使用了索引扫描来做排序

7.压缩（前缀）索引，默认只压缩字符串，减少索引大小，对于CPU密集型应用，因为扫描需要随机查找，压缩索引在MyISAM上要慢好几倍

8.重复索引是指在相同的列上按照相同的顺序创建的相同类型的索引，应该避免这样创建重复索引

9.索引可以让查询锁定更少的行

D.维护索引和表

1.CHECK TABLE检查表是否损坏，ALTER TABLE innodb_tb1 ENGINE=INNODB;修复表

2.records_in_range()通过向存储引擎传入两个边界值获取在这个范围大概有多少条记录，对于innodb不精确

3.info()返回各种类型的数据，包括索引的基数

4.可以使用SHOW INDEX FROM命令来查看索引的基数

5.B-Tree索引可能会碎片化，这会降低查询的效率

六、查询性能优化

A.为什么查询速度会慢

1.如果要优化查询，实际上要优化其子任务，要么消除其中一些子任务，要么减少子任务的执行次数，要么让子任务运行得更快

2.查询的生命周期大致可以按照顺序来看：从客户端，到服务器，然后在服务器上进行解析，生成执行计划，执行，并返回结果给客户端

B.慢查询基础：优化数据访问

1.两个分析步骤：

• 确认应用程序是否在检索大量超过需要的数据

• 确认MySQL服务器层是否在分析大量超过需要的数据行

2.是否向数据库请求了不需要的数据

• 查询不需要的记录

• 多表关联并返回全部列

• 总是取出全部列

• 重复查询相同的数据

3.MySQL是否在扫描额外的记录

• 查询开销三个指标：响应时间、扫描的行数、返回的行数

• 响应时间：服务时间和排队时间之和，“快速上限估计”法

• 扫描的行数：较短的行的访问速度更快，内存中的行也比磁盘中的行的访问 速度要快得多

• 访问类型：EXPLAIN中的type列反应了访问类型；通过增加合适的索引；

• 三种方式应用WHERE条件：在索引中使用WHERE条件来过滤不匹配的记录；使用索引覆盖扫描（Extra中出现Using index）来返回记录，直接从索引中过滤不需要的记录并返回命中结果；从数据表中返回数据，然后过滤不满足条件的记录（Extra中出现Using Where）

• 需要扫描大量数据但只返回少数的行的优化技巧：使用索引覆盖扫描，改变库表结构，重写复杂的查询

C.重构查询的方式

1.MySQL从设计上让连接和断开连接都很轻量级，在返回一个小的查询结果方面很高效

2.切分查询，将大查询切分成小查询，每个查询功能完全一样，只完成一小部分，每次只返回一小部分查询结果，可以避免锁住很多数据、占满事务日志、耗尽系统资源、阻塞很多小的但重要的查询

3.分解关联查询优势：

• 让缓存的效率更高

• 将查询分解后，执行单个查询可以减少锁的竞争

• 在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩展

• 查询本身效率也可能会有所提升

• 可以减少冗余记录的查询

• 相当于在应用中实现了哈希关联，而不是使用MySQL的嵌套循环关联

4.分解关联查询的场景：

• 当应用能够方便地缓存单个查询的结果的时候

• 当可以将数据分布到不同的MySQL服务器上的时候

• 当能够使用IN()的方式代替关联查询的时候

• 当查询中使用同一个数据表的时候

D.查询执行的基础

1.查询执行路径

• 客户端发送一条查询给服务器

• 服务器先检查查询缓存，如果命中则立刻返回，否则进入下一阶段

• 服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划

• MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询

• 将结果返回给客户端

2.MySQL客户端和服务器之间的通信协议是“半双工”的，无法将一个消息切成小块独立来发送，没法进行流量控制，一旦一端开始发生消息，另一端要接收完整个消息才能响应它

3.MySQL通常需要等所有的数据都已经发送给客户端才能释放这条查询所占用的资源，所以接收全部结果并缓存通常可以减少服务器的压力

4.查询状态，SHOW FULL PROCESSLIST命令查看：

• Sleep，线程正在等待客户端发送新的请求

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

• Locked，在MySQL服务器层，该线程正在等待表锁

• Analyzing and statistics，线程正在收集存储引擎的统计信息，并生成查询的执行计划

• Copying to tmp table [on disk]，线程正在执行查询，并且将其结果集都复制到一个临时表中，要么是在做GROUP BY操作，要么是文件排序操作，或者是UNION操作

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

• Sending data，线程可能在多个状态之间传送数据，或者在生成结果集，或者在向客户端返回数据

5.语法解析器和预处理，通过关键字将SQL语句进行解析，并生成一棵对应的“解析树”，解析器将使用MySQL语法规则验证和解析查询，预处理器则根据一些MySQL规则进一步检查解析树是否合法

6.查询优化器，找到最好的执行计划，使用基本成本的优化器，将尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个，使用SHOW STATUS LIKE 'Last_query_cost';查看需要多少个数据页的随机查找

7.导致MySQL查询优化器选择错误的原因：

• 统计信息不准确，Innodb不能维护一个数据表的行数的精确统计信息

• 执行计划中的成本估算不等同于实际执行的成本

• MySQL的最优可能和你想的最优不一样

• MySQL从不考虑其他并发执行的查询

• MySQL也并不是任何时候都是基于成本的优化

• MySQL不会考虑不受其控制的操作的成本

• 优化器有时候无法去估算所有可能的执行计划

8.MySQL能处理的优化类型：

• 重新定义关联表的顺序

• 将外链接转化成内链接

• 使用等价变换规则

• 优化COUNT()、MIN()和MAX()，在EXPLAIN中可以看到“Select tables optimized away”

• 预估并转化为常数表达式，当检测到一个表达式可以转化为常数的时候，就会一直把该表达式作为常数进行优化处理

• 覆盖索引扫描，当索引中的列包含所有查询中需要使用的列的时候，就可以使用索引返回需要的数据，而无须查询对应的数据行

• 子查询优化

• 提前终止查询，在发现已经满足查询需求的时候，MySQL总是能够立刻终止查询

• 等值传播，如果两个列的值通过等式关联，那么MySQL能够把其中一个列的WHERE条件传递到另一列上

• 列表IN()的比较，MySQL将IN()列表中的数据先进行排序，然后通过二分查找的方式来确定列表中的值是否满足条件

9.在服务器层有查询优化器，却没有保存数据和索引的统计信息，统计信息由存储引擎实现，不同的存储引擎可能会存储不同的统计信息

10.在MySQL中，每一个查询，每一个片段（包括子查询，甚至基于单表的SELECT）都可能是关联

11.对于UNION查询，MySQL先将一系列的单个查询结果放到一个临时表中，然后再重新读出临时表数据来完成UNION查询

12.MySQL对任何关联都执行“嵌套循环关联”操作，即MySQL先在一个表中循环取出单条数据，然后再嵌套到下一个表中寻找匹配的行，依次下去，直到找到所有表中匹配的行为止

13.全外连接就无法通过嵌套循环和回溯的方式完成，当发现关联表中没有找到任何匹配行的时候，则可能是因为关联恰好从一个没有任何匹配的表开始，MySQL不支持全外连接

14.关联查询优化器，会尝试在所有的关联顺序中选择一个成本最小的来生成执行计划树，如果可能，优化器会遍历每一个表然后逐个做嵌套循环计算每一棵可能的执行树的成本，最后返回一个最优的执行计划

15.如果有超过n个表的关联，那么需要检查n的阶乘关联顺序，称为“搜索空间”，搜索空间的增长速度非常快

16.无论如何排序都是一个成本很高的操作，所以从性能角度考虑，应尽可能避免排序或者尽可能避免对大量数据进行排序

17.当不能使用索引生成排序结果的时候，MySQL需要自己进行排序，如果数据量小则在内存中进行，如果数据量大则需要使用磁盘，MySQL将这个过程称为文件排序（filesort），即使完全是内存排序不需要任何磁盘文件时也是如此

E.MySQL查询优化器的局限性

1.关联子查询：MySQL的子查询实现得非常糟糕，最糟糕的一类查询是WHERE条件中包含IN()的子查询语句，使用GROUP_CONCAT()在IN()中构造一个由逗号分隔的列表，或者使用EXISTS()来改写

2.UNION的限制：有时，MySQL无法将限制条件从外层“下推”到内层，这使得原本能够限制部分返回结果的条件无法应用到内层查询的优化上

3.MySQL无法利用多核特性来并行执行查询

4.MySQL不支持哈希关联，MariaDB已经实现了哈希关联

5.MySQL不支持松散索引扫描，5.0后版本在分组查询中需要找到分组的最大值和最小值时可以使用松散索引扫描

6.对于MIN()和MAX()查询，MySQL的优化做得并不好

F.查询优化器的提示（hint）

1.HIGH_PRIORITY和LOW_PRIORITY，当多个语句同时访问某一个表的时候，哪些语句的优先级相对高些、哪些语句的优先级相对低些

2.DELAYED，对INSERT和REPLACE有效，会将使用该提示的语句立即返回给客户端，并将插入的行数据放入到缓冲区，然后在表空闲时批量将数据写入，并不是所有的存储引擎都支持，并且该提示会导致函数LAST_INSERT_ID()无法正常工作

3.STRAIGHT_JOIN，可以放置在SELECT语句的SELECT关键字之后，也可以放置在任何两个关联表的名字之间。第一个用法是让查询中所有的表按照在语句中出现的顺序进行关联，第二个用法则是固定其前后两个表的关联顺序

4.SQL_SMALL_RESULT和SQL_BIG_RESULT，只对SELECT语句有效，它们告诉优化器对GROUP BY或者DISTINCT查询如何使用临时表及排序

5.SQL_BUFFER_RESULT，告诉优化器将查询结果放入到一个临时表，然后尽可能快地释放表锁

6.SQL_CACHE和SQL_NO_CACHE，告诉MySQL这个结果集是否应该缓存在查询缓存中

7.SQL_CALC_FOUND_ROWS，会计算除去LIMIT子句后这个查询要返回的结果集的总数，而实际上只返回LIMIT要求的结果集，可以通过函数FOUND_ROW()获得这个值

8.FOR UPDATE和LOCK IN SHARE MODE，主要控制SELECT语句的锁机制，但只对实现了行级锁的存储引擎有效，仅InnoDB支持

9.USE INDEX、IGNORE INDEX和FORCE INDEX，告诉优化器使用或者不使用哪些索引来查询记录

10.MySQL5.0后新增的用来控制优化器行为的参数：

• optimizer_search_depth，控制优化器在穷举执行时的限度

• optimizer_prune_level，让优化器会根据需要扫描的行数来决定是否跳过某些执行计划

• optimizer_switch，包含了一些开启/关闭优化器特性的标志位

G.优化特定类型的查询

1.优化COUNT()查询

• COUNT()是一个特殊的函数，有两种非常不同的作用：可以统计某个列值的数量，也可以统计行数，在统计列值时要求列值是非空的（不统计NULL）

• COUNT(*)并不是会像我们猜想的那样扩展成所有的列，实际上，它会忽略所有的列而直接统计所有的行数，当MySQL确认括号内的表达值不可能为空时，实际上就是在统计行数

• MyISAM的COUNT()函数只有没有任何WHERE条件下的COUNT(*)才非常快

• 使用近似值，如EXPLAIN出来的优化器估算行数

• 使用索引覆盖

• 使用汇总表

• 使用外部缓存系统

2.优化关联查询

• 确保ON或者USING子句中的列上有索引

• 确保任何的GROUP BY和ORDER BY中的表达式只涉及到一个表中的列

• 当升级MySQL的时候需要注意：关联语法、运算符优先级等其他可能会发生变化的地方

3.优化子查询：尽可能使用关联查询代替，如果使用MySQL5.6以上或MariaDB则可以忽略这个建议

4.优化GROUP BY和DISTINCT

• 使用索引优化

• 当无法使用索引时，GROUP BY使用两种策略来完成：使用临时表或者文件排序来做分组

• 尽可能的将WITH ROLLUP（超级聚合）功能移动应用程序中处理

5.优化LIMIT分页

• 最简单的办法是尽可能地使用索引覆盖扫描，而不是查询所有的列，然后根据需要做一次关联操作再返回所需的列，select id,name,…… from table innert join (select id from table order by xxx limit 5000,5) as table1 USING(id);

• offset会导致MySQL扫描大量不需要的行然后再抛弃掉，如果可以记录上次取数据的位置，下次就可以直接从该记录的位置开始扫描，可以避免使用offset

• 使用预先计算的汇总表，或者关联到一个冗余表

6.优化UNION查询

• 通过创建并填充临时表的方式来执行UNION查询，因此很多优化策略在UNION查询中都没法很好地使用，经常需要手工地将WHERE、LIMIT、ORDER BY等子句下推到UNION的各个子查询中

• 除非确实需要服务器消除重复的行，否则就一定要使用UNION ALL

七、MySQL高级特性

A.分区表

1.对用户来说，分区表是一个独立的逻辑表，但是底层由多个物理子表组成，实际上是对一组底层表的句柄对象（Handler Object）的封装

2.适用场景：

• 表非常大以至于无法全部都放在内存中，或者只在表的最后部分有热点数据，其他均是历史数据

• 分区表的数据更容易维护

• 分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备

• 可以使用分区表来避免某些特殊的瓶颈

• 如果需要，还可以备份和恢复独立的分区

3.使用限制：

• 一个表最多只能有1024个分区

• 在MySQL5.1中，分区表达式必须是整数，或者是返回整数的表达式。在MySQL5.5中，某些场景中可以直接使用列来进行分区

• 如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须包含进来

• 分区表中无法使用外键约束

4.使用分区表

• 在数据量超大的时候，B-Tree索引就无法起作用了，除非是索引覆盖查询，否则数据库服务器需要根据索引扫描的结果回表，查询所有符合条件的记录，如果数据量巨大，将产生大量随机I/O

5.保证大数据量的可扩展性两个策略：

• 命题扫描数据，不要任何索引

• 索引数据，并分离热点

6.分区策略的问题：

• NULL值会使分区过滤无效

• 分区列和索引列不匹配

• 选择分区的成本可能很高

• 打开并锁住所有底层表的成本可能很高

• 维护分区的成本可能很高

• 所有分区都必须使用相同的存储引擎

• 分区函数中可以使用的函数和表达式也有一些限制

• 某些存储引擎不支持分区

• 对于MyISAM的分区表，不能再使用LOAD INDEX INTO CACHE操作

• 对于MyISAM表，使用分区表时需要打开更多多的文件描述符

7.查询优化

• 很重要的一点是要在WHERE条件中带入分区列

• 只能在使用分区函数的列本身进行比较时才能过滤分区，而不能根据表达式的值去过滤分区，即使这个表达式是分区函数也不行

B.视图

1.视图本身是一个虚拟表，不存放任何数据，返回的数据是MySQL从其他表中生成的

2.MySQL使用两种算法：合并算法（MERGE）和临时表算法（TEMPTABLE），会尽可能地使用合并算法

3.如果视图中包含GROUP BY、DISTINCT、任何聚合函数、UNION、子查询等，只要无法在原表记录和视图记录中建立一一映射的场景中，MySQL都将使用临时表算法来实现视图

4.可更新视图（updatable view）是指可以通过更新这个视图来更新视图涉及的相关表，CHECK OPTION表示任何通过视图更新的行，都必须符合视图本身的WHERE条件定义

5.在重构schema的时候可以使用视图，使得在修改视图底层表结构的时候，应用代码还可能继续不报错运行

6.MySQL中不支持物化视图（指将视图结果数据存放在一个可以查看的表中，并定期从原始表中刷新数据到这个表中）

7.不会保存视图定义的原始SQL语句

C.外键约束

1.使用外键是有成本的，通常要求每次在修改数据时都要在另外一张表中多执行一次查找操作

2.如果想确保两个相关表始终有一致的数据，那么使用外键比在应用程序中检查一致性的性能要高得多，在相关数据的删除和更新上，也比在应用中维护要更高效

3.外键会带来很大的额外消耗

D.在MySQL内部存储代码

1.MySQL允许通过触发器、存储过程、函数的形式来存储代码，从5.1开始还可以在定时任务中存放代码，这个定时任务也被称为“事件”。存储过程和存储函数都被统称为“存储程序”

2.存储代码的优点：

• 它在服务器内部执行，离数据最近，另外在服务器上执行还可以节省带宽和网络延迟

• 这是一种代码重用，可以方便地统一业务规则，保证某些行为总是一致，所以也可以为应用提供一定的安全性

• 它可以简化代码的维护和版本更新

• 可以帮助提升安全，比如提供更细粒度的权限控制

• 服务器端可以缓存存储过程的执行计划，这对于需要反复调用的过程，会大大降低消耗

• 因为是在服务器端部署的，所以备份、维护都可以在服务器端完成

• 可以在应用开发和数据库开发人员之间更好地分工

3.存储代码的缺点：

• MySQL本身没有提供好用的开发和调试工具

• 较之应用程序的代码，存储代码效率要稍微差些

• 存储代码可能会给应用程序代码的部署带来额外的复杂性

• 因为存储程序都部署在服务器内，所以可能有安全隐患

• 存储过程会给数据库服务器增加额外的压力，而数据库服务器的扩展性相比应用服务器要差很多

• MySQL并没有什么选项可以控制存储程序的资源消耗，所以在存储过程中的一个小错误，可能直接把服务器拖死

• 存储代码在MySQL中的实现也有很多限制——执行计划缓存是连接级别的，游标的物化和临时表相同，异常处理也非常困难

• 调试MySQL的存储过程是一件很困难的事情

• 它和基于语句的二进投影日志复制合作得并不好

4.存储过程和函数的限制：

• 优化器无法使用关键字DETERMINISTIC来优化单个查询中多次调用存储函数的情况

• 优化器无法评估存储函数的执行成本

• 每个连接都有独立的存储过程的执行计划缓存

• 存储程序和复制是一组诡异组合

5.触发器：可以让你在执行INSERT、UPDATE或者DELETE的时候，执行一些特定的操作，可以在MySQL中指定是在SQL语句执行前触发还是在执行后触发，可以使用触发器实现一些强制限制，或者某些业务逻辑，否则，就需要在应用程序中实现这些逻辑

6.触发器的注意和限制：

• 对每一个表的每一个事件，最多只能定义一个触发器

• 只支持“基于行的触发”，也就是说，触发器是针对一条记录的，而不是针对整个SQL语句的，如果变更的数据集非常大的话，效率会很低

• 触发器可以掩盖服务器背后的工作

• 触发器可以掩盖服务器背后的工作，一个简单的SQL语句背后可能包含了很多看不见的工作

• 触发器的问题也很难排查，如果某个性能问题和触发器相关，会很难分析和定位

• 触发器可能导致死锁和锁等待

• 触发器并不能一定保证更新的原子性

7.触发器的用处：

• 实现一些约束、系统维护任务，以及更新反范式化数据的时候

• 记录数据变更日志

8.事件：类似于Linux的定时任务，指定MySQL在某个时候执行一段SQL代码，或者每隔一个时间间隔执行一段SQL代码

E.游标

1.MySQL在服务器端提供提供只读的、单向的游标，而且只能在存储过程或者更底层的客户端API中使用，指向的对象都是存储在临时表中而不是实际查询到的数据，所以总是只读的

2.会带来额外的性能开销

3.不支持客户端的游标

F.绑定变量

1.当创建一个绑定变量SQL时，客户端向服务器发送了一个SQL语句的原型。服务器端收到这个SQL语句框架后，解析并存储这个SQL语句的部分执行计划，返回给客户端一个SQL语句处理句柄。以后每次执行这类查询，客户端都指定使用这个句柄

2.可以更高效地执行大量的重复语句：

• 在服务器端只需要解析一次SQL语句

• 在服务器端某些优化项的工作只需要执行一次，因为它会缓存一部分的执行计划

• 以二进制的方式只发送参数和句柄，比起每次都发送ASC2码文本效率更高

• 仅仅是参数——而不是整个查询语句——需要发送到服务器端，所以网络开销会更小

• MySQL在存储参数的时候，直接将其存放到缓存中，不再需要在内存中多次复制

3.绑定变量相对也更安全。无须在应用程序中处理转义，一则更简单了，二则也大大减少了SQL注入和攻击的风险

4.最主要的用途就是在存储过程中使用，构建并执行“动态”的SQL语句

5.绑定变量的限制：

• 绑定变量是会话级别的，所以连接之间不能共用绑定变量句柄

• 在5.1版本之前，绑定变量的SQL是不能使用查询缓存的

• 并不是所有的时候使用绑定变量都能获得更好的性能

• 如果总是忘记释放绑定变量资源，则在服务器端很容易发生资源“泄漏”

• 有些操作，比如BEGIN，无法在绑定变量中完成

G.用户自定义函数

1.用户自定义函数（UDF）必须事先编译好并动态链接到服务器上，这种平台相关性使得UDF在很多方面都很强大，但一个错误也很可能让服务器直接崩溃，甚至扰乱服务器的内存或者数据

H.插件

1.插件可以在MySQL中新增启动选项和状态值，还可以新增INFORMATION_SCHEMA表，或者在MySQL的后台执行任务等等

2.在5.1后支持的插件接口：

• 存储过程插件

• 后台插件，可以让程序在MySQL中运行，可以实现自己的网络监听、执行自己的定期任务

• INFORMATION_SCHEMA插件，提供一个新的内存INFORMATION_SCHEMA表

• 全文解析插件，提供一种处理文本的功能，可以根据自己的需求来对一个文档进行分词

• 审计插件，在查询执行的过程中的某些固定点被调用，可以记录MySQL的事件日志

• 认证插件，既可可以在MySQL客户端也可在它的服务器端，可以使用这类插件来扩展MySQL的认证功能

I.字符集和校对

1.字符集是一种从二进制编码到某类字符符号的映射，可以参考如何使用一个字节来表示英文字母。“校对”是指一组用于某个字符集的排序规则

2.每种字符集都可能有多种校对规则，并且都有一个默认的校对规则，每个校对规则都是针对某个特定的字符集的，和其他的字符集没有关系

3.MySQL有很多的选项用于控制字符集，这些选项和字符集很容易混淆，只有基于字符的值才真正的“有”字符集的概念

4.MySQL的两类设置：创建对象时的默认设置、服务器和客户端通信时的设置

5.如果比较的两个字符串的字符集不同，MySQL会先将其转成同一个字符集再进行比较

6.一些需要注意的地方：

• 诡异的character_set_database设置，当改变默认数据库的时候，这个变量也会跟着变，所以当连接到MySQL实例上又没有指定要使用的数据库时，默认值会和character_set_server相同

• LOAD DATA INFILE，当使用时，数据库总是将文件中的字符按照字符集character_set_database来解析

• SELECT INTO OUTFILE，MySQL会将结果不做任何转码地写入文件

• 嵌入式转义序列，MySQL会根据character_set_client的设置来解析转义序列

7.某些字符集和校对规则可能会需要更多的CPU操作，可能会消耗更多的内存和存储空间，甚至还会影响索引的正常使用

• 不同的字符集和校对规则之间的转换可能会带来额外的系统开销

• 只有排序查询要求的字符集与服务器数据的字符集相同的时候，才能使用索引进行排序

• 为了能够适应各种字符集，包括客户端字符集、在查询中显式指定的字符集，MySQL会在需要的时候进行字符集转换

J.全文索引

1.MyISAM的全文索引作用对象是一个“全文集合”，这可能是某个数据表的一列，也可能是多个列

2.可以根据WHERE子句中的MATCH AGAINST来区分查询是否使用全文索引

3.在使用全文索引进行排序的时候，MySQL无法再使用索引排序，如果不想使用文件排序的话，就不要在查询中使用ORDER BY子句

4.在布尔搜索中，用户可以在查询中自定义某个被搜索的词语的相关性，可能通过一些前缀修饰符来定制搜索

5.全文索引在INSERT、UPDATE、DELETE中的操作代价很大

6.全文索引会影响索引选择、WHERE子句、ORDER BY等：

• 如果查询中使用了MATCH AGAINST子句，而对应列上又有可用的全文索引，那么MySQL就一定会使用这个全文索引

• 全文索引只能用作全文搜索匹配

• 全文索引不存储索引列的实际值，也就不可能用作索引覆盖扫描

• 除了相关性排序，全文索引不能用作其他的排序

7.全文索引的配置和优化：

• 经常使用OPTIMIZE TABLE来减少碎片，如果是I/O密集型的定期进行全文索引重建

• 保证索引缓存足够大

• 提供一个好的停用词表

• 忽略一些太短的单词

• 导入大量数据时，最好通过命令DISABLE KEYS来禁用全文索引，然后导入结束后使用ENABLE KEYS来建立全文索引

• 如果数据集特别大，则需要对数据进行手动分区，然后将数据分布到不同的节点，再做并行的搜索

K.分布式（XA）事务

1.XA事务中需要有一个事务协调器来保证所有的事务参与者都完成了准备工作。如果协调器收到所有的参与者都准备好的消息，就会告诉所有的事务可以提交了，MySQL在这个XA事务过程中扮演一个参与者的角色，而不是协调者

2.因为通信延迟和参与者本身可能失败，所以外部XA事务比内部消耗会更大

L.查询缓存

1.MySQL查询缓存保存查询返回的完整结果，当查询命中该缓存，MySQL会立刻返回结果，跳过了解析、优化和执行阶段

2.MySQL判断缓存命中的方法很简单：缓存放在一个引用表中，通过一个哈希值引用，这个哈希值包括了如下因素，即查询本身、当前要查询的数据库、客户端协议的版本等一些其他可能会影响返回结果的信息

3.当判断缓存是否命中时，MySQL不会解析、“正规化”或者参数化查询语句，而是直接使用SQL语句和客户端发送过来的其他原始信息。任何字符上的不同，例如空格、注释——都会导致缓存的不命中

4.当查询语句中有一些不确定的数据时，则不会被缓存，例如包含函数NOW()或者CURRENT_DATE()的查询不会被缓存，只要包含任何用户自定义函数、存储函数、用户变量、临时表、mysql库中的系统表，或者任何包含列级别权限的表，都不会被缓存

5.打开查询缓存对读和写操作都会带来额外的消耗：

• 读查询在开始之前必须先检查是否命中缓存

• 如果这个读查询可以被缓存，那么当完成执行后，MySQL若发现查询缓存中没有这个查询，会将其结果存入查询缓存，这会带来额外的系统消耗

• 当向某个表写入数据的时候，MySQL必须将对应表的所有缓存都设置失效，如果查询缓存非常大或者碎片很多，这个操作就可能会带来很大系统消耗

6.对于需要消耗大量资源的查询通常都是非常适合缓存的

7.缓存未命中：

• 查询语句无法被缓存

• MySQL从未处理这个查询

• 查询缓存的内存用完了

• 查询缓存还没有完成预热

• 查询语句之前从未执行过

• 缓存失效操作太多了

8.缓存参数配置：

• query_cache_type，是否打开查询缓存

• query_cache_size，查询缓存使用的总内存空间

• query_cache_min_res_unit，在查询缓存中分配内存块时的最小单位，可以帮助减少由碎片导致的内存空间浪费

• query_cache_limit，MySQL能够缓存的最大查询结果

• query_cache_wlock_invalidate，如果某个数据表被其他的连接锁住，是否仍然从查询缓存中返回结果

9.InnoDB和查询缓存

• 事务是否可以访问查询缓存取决于当前事务ID，以及对应的数据表上是否有锁

• 如果表上有任何的锁，那么对这个表的任何查询语句都是无法被缓存的

10.通用查询缓存优化：

• 用多个小表代替一个大表对查询缓存有好处

• 批量写入时只需要做一次缓存失效，所以相比单条写入效率更好

• 因为缓存空间太大，在过期操作的时候可能会导致服务器僵死，控制缓存空间的大小

• 无法在数据库或者表级别控制查询缓存，但是可以通过SQL_CACHE和SQL_NO_CACHE来控制某个SELECT语句是否需要进行缓存

• 对于 写密集型的应用来说，直接禁用查询缓存可能会提高系统的性能

• 因为对互斥信号量的竞争，有时直接关闭查询缓存对读密集型的应用也会有好处

八、优化服务器设置

A.MySQL配置的工作原理

1.任何打算长期使用的设置都应该写到全局配置文件，而不是在命令行特别指定

2.常用变量和动态修改它们的效果：

• key_buffer_size，可以一次性为键缓冲区（key buffer，也叫键缓存key cache）分配所有指定的空间

• table_cache_size，不会立即生效——会延迟到下次有线程打开表才有效果，如果值大于缓存中表的数量，线程可以把最新打开的表放入缓存，如果比缓存中的表数小，将从缓存中删除不常使用的表

• thread_cache_size，不会立即生效——将在下次有连接被关闭时产生效果，检查缓存中是否还有空间来缓存线程，如果有空间，则缓存该线程以备下次连接征用，如果没空间，将销毁该线程而不再缓存

• query_cache_size，一次性分配并初始化这块内存

• read_buffer_size，只在有查询需要使用时才会为该缓存分配内存

• read_rnd_buffer_size，只在有查询需要使用时才会为该缓存分配内存，并且只会分配需要的内存大小而不是全部指定的大小

• sort_buffer_size，只会在有查询需要做排序时才会为该缓存分配内存

3.对于连接级别的设置，不要轻易地在全局级别增加它们的值，除非确认这样做是对的

4.设置变量时请小心，并不是值越大就越好，而且如果设置的值太高，可能更容易导致问题：可能会由于内存不足导致服务器内存交换，或者超过地址空间

5.不要期望通过建立一套基准测试方案，然后不断迭代地验证对配置项的修改来找到最佳配置方案，而要把时间花在检查备份、监控执行计划的变动之类的事情上，可能会更有意义

B.什么不该做

1.不要根据一些“比率”来调优：例如缓存命中率跟缓存是否过大或过小没有关系2.不要使用调优脚本

3.不要相信很流行的内存消耗公式

C.创建MySQL配置文件

1.MySQL编译的默认设置并不都是靠谱的，虽然其中大部分都比较合适

2.从一个比默认值大一点但不是大得很离谱的安全值开始是比较好的，MySQL的内存利用率并不总是可以预测的：它可能依赖很多的因素，例如查询的复杂性和并发性

3.配置服务器的首选途径：了解它内部做了什么，以及参数之间如何相互影响，然后再决定

4.open_files_limit，在Linux系统上设置得尽可能大，如果参数不够大，将会踫到24号错误“打开的文件太多（too many open files）”

5.每隔60秒查看状态变量的增量变化：mysqladmin extended-status ri60

D.配置内存使用

1.配置MySQL正确地使用内存量对高性能是至关重要的，内存消耗分为两类：可以控制的内存和不可以控制的内存

2.配置内存：

• 确定可以使用的内存上限

• 确定每个连接MySQL需要使用多少内存

• 确定操作系统需要多少内存才够用

• 把剩下的内存全部给MySQL的缓存

3.MySQL保持一个连接（线程）只需要少量的内存，它还需要一个基本量的内存来执行任何给定查询，需要为高峰时期执行的大量查询预留好足够的内存，否则，查询执行可能因为缺乏内存而导致执行效率不佳或执行失败

4.跟查询一样，操作系统也需要保留足够的内存给它工作，如果没有虚拟内存正在交换（Paging）到磁盘，就是表明操作系统内存足够的最佳迹象

5.如果服务器只运行MySQL，所有不需要为操作系统以及查询处理保留的内存都可以用作MySQL缓存

6.大部分情况下最重要的缓存：

• InnoDB缓冲池

• InnoDB日志文件和MyISAM数据的操作系统缓存

• MyISAM键缓存

• 查询缓存

• 无法手工配置的缓存，例如二进制日志和表定义文件的操作系统缓存

7.InnoDB缓冲池并不仅仅缓存索引：它还会缓存行的数据、自适应哈希索引、插入缓冲（Insert Buffer）、锁，以及其他内部数据结构，还使用缓冲池来帮助延迟写入，InnoDB严重依赖缓冲池

8.如果事先知道什么时候需要关闭InnoDB，可以在运行时修改innodb_max_dirty_pages_pct变量，将值改小，等待刷新纯种清理缓冲池，然后在脏页数量较少时关闭，可以监控the Innodb_buffer_pool_pages_dirty状态变量或者使用innotop来监控SHOW INNODB STATUS来观察脏页的刷新量

7.MyISAM的键缓存也被称为键缓冲，默认只有一个键缓存，但也可以创建多个，MyISAM自身只缓存索引，不缓存数据，最重要的配置项是key_buffer_size，不要超过索引的总大小，或者不超过操作系统缓存保留总内存的25%-50%，以更小的为准

8.了解MyISAM索引实际上占用多少磁盘空间，查询INFORMATION_SCHEMA表的INDEX_LENGTH字段，把它们的值相加，就可以得到索引存储占用空间

9.块大小也是很重要的（特别是写密集型负载），因为它影响了MyISAM、操作系统缓存，以及文件系统之间的交互，如果缓存块太小，可能会踫到写时读取

10.线程缓存保存那些当前没有与连接关联但是准备为后面新的连接服务的线程，当一个新的连接创建时，如果缓存中有线程存在，MySQL从缓存中删除一个线程，并且把它分配给这个新的连接，当连接关闭时，如果线程缓存还有空间的话，MySQL又会把线程放回缓存，如果没有空间的话，MySQL会销毁这个线程

11.thread_cache_size变量指定了MySQL可以保持在缓存中的线程数，一般不需要配置这个值，除非服务器会有很多连接请求

12.表缓存（Table Cache）和线程缓存的概念是相似的，但存储的对象代表的是表，缓存对象包含相关表.frm文件的解析结果，加上其他数据。表缓存可以重用资源，让服务器避免修改MyISAM文件头来标记表“正在使用中”，对InnoDB的重要性要小得多

12.表缓存的缺点是，当服务器有很多MyISAM表时，可能会导致关机时间较长，因为关机前索引块必须完成刷新，表都必须标记为不再打开

13.InnoDB数据字典（Data Dictionary），InnoDB自己的表缓存，当InnoDB打开一张表，就增加了一个对应的对象到数据字典

14.InnoDB没有将统计信息持久化，而是在每次打开表时重新计算，5.6以后可以打开innodb_use_sys_stats_table选项来持久化存储统计信息到磁盘

15.可以关闭InnoDB的innodb_stats_on_metadata选项来避免耗时的表统计信息刷新

16.如果可以，最好把innodb_open_files的值设置得足够大以使服务器可以保持所有的.ibd文件同时打开

E.配置MySQL的I/O行为

1.InnoDB I/O配置

• InnoDB不仅允许控制怎么恢复，还允许控制怎么打开和刷新数据（文件），这会对恢复和整体性能产生巨大的影响

• 对于常见的应用，最重要的一小部分内容是InnoDB日志文件大小、InnoDB怎样刷新它的日志缓冲，以及InnoDB怎样执行I/O

• 整体的日志文件大小受控于innodb_log_file_size和innodb_log_files_in_group两个参数，对写性能非常重要

• 通常不需要把日志缓冲区设置得非常大，推荐的范围是1MB-8MB，除非要写很多相当大的BLOB记录

• 可以通过检查SHOW INNODB STATUS的输出中LOG部分来监控InnoDB的日志和日志缓冲区的I/O性能，通过观察Innodb_os_log_written状态变量来查看InnodDB对日志文件写出了多少数据。日志文件的全部大小，应该足够容纳服务器一个小时的活动内容

• 如果和持久相比更在乎性能，可以修改innodb_flush_log_at_trx_commit变量来控制日志缓冲刷新的频繁程度

• 使用innodb_flush_method选项可以配置InnoDB如何跟文件系统相互作用

• InnoDB用表空间并不只是存储表和索引，还保存了回滚日志、插入缓冲（Insert Buffer）、双写缓冲（Doublerite Buffer）及其他内部数据结构

• 为了控制写入速度，可以设置innodb_max_purge_lag变量为一个大于0的值，这个值表示InnoDB开始延迟后面的语句更新数据之前，可以等待被清除的最大的事务数量

• 双写缓冲是表空间的一个特殊的保留区域，在一些连续的块中足够保存100个页，本质上是一个最近写回的页面的备份拷贝，当InnoDB从缓冲池刷新页面到磁盘时，首先把它们写（或者刷新）到双写缓冲，然后再把它们写到其所属的数据区域中，这可以保证每个页面的写入都是原子并且持久化的

• 设置innodb_doublewrite为0来关闭双写缓冲

• sync_binlog选项控制MySQL怎么刷新二进制日志到磁盘

• 二进制日志，如果希望使用expire_logs_days选项来自动清理旧的二进制日志，就不要用rm命令去删

2.MyISAM的I/O配置

• MyISAM通常每次写操作之后就把索引变更刷新磁盘，批量操作会更快一些

• 通过设置delay_key_write变量，可以延迟索引的写入，修改的键缓冲块直到表被关闭才会刷新

• myisam_recover选项控制MyISAM怎样寻找和修复错误

• 内存映射使得MyISAM直接通过操作系统的页面缓存访问.MYD文件，避免系统调用的开销，5.1后可以通过myisam_use_mmap选项打开内存映射

F.配置MySQL并发

1.InnoDB并发配置

• InnoDB有自己的“线程调度器”控制线程怎么进入内核访问数据，以及它们在内核中一次可以做哪些事，最基本的限制并发的方式是使用innodb_thread_concurrency变量，它会限制一次性可以有多少线程进入内核

• 并发值 = CPU数量 * 磁盘数量 * 2，在实践中使用更小的值会更好一点

2.MyISAM并发配置

• 尽管MyISAM是表级锁，它依然可以一边读取，一边并发追加新行，这种情况下只能读取到查询开始时的所有数据，新插入的数据是不可见的，这样可以避免不一致读

• 通过设置concurrent_insert这个变量，可以配置MyISAM打开并发插入

• 让INSERT、REPLACE、DELETE、UPDATE语句的优先级比SELECT语句更低，设置low_priority_updates选项就可以

G.基于工作负载的配置

1.当服务器满载情况下运行时，请尝试记录所有的查询语句，因为这是最好的方式来查看哪种类型的查询语句占用资源最多，同时创建processlist快照，通过state或者command字段来聚合它们

2.优化BLOB和TEXT场景

• BLOB有几个限制使得服务器对它的处理跟其他类型不一样，不能在内存临时表中存储BLOB值，效率很低

• 通过SUBSTRING()函数把值转换为VARCHAR

• 让临时表更快一些：放在基于内存的文件系统

• 如果使用的是InnoDB，也可以调大InnoDB日志缓冲大小

• 大字段在InnoDB里可能浪费大量空间

• 扩展存储禁用了自适应哈希，因为需要完整地比较列的整个长度，才能发现是不是正确的数据

• 太长的值可能使得查询中作为WHERE条件不能使用索引

• 如果一张表里有很多大字段，最好是把它们组合起来单独存到一个列里面

• 有时候可以把大字段用COMPRESS()压缩后再存为BLOB，或者发送到MySQL前在应用程序中进行压缩

3.优化排序（Filesorts）：当MySQL必须排序BLOG或TEXT字段时，它只会使用前缀，然后忽略剩下部分的值

H.完成基本配置

1.tmp_table_size和max_heap_table_size，这两个设置控制使得Memory引擎的内存临时表能使用多大的内存

2.max_connections，这个设置的作用就像一个紧急刹车，以保证服务器不会因应用程序激增的连接而不堪重负，设置得以容纳正常可能达到的负载，并且要足够安全，能保证允许你登录和管理服务器

3.thread_cache_size，可以通过观察服务器一段时间的活动，来计算一个有理有据的值，250的上限是一个不错的估算值

4.table_cache_size，应该被设置得足够大，以避免总是需要重新打开和重新解析表的定义，可能通过观察Open_tables的值及其在一段时间的变化来检查该变量

I.安全和稳定的设置

1.expire_logs_days，如果启用了二进制日志，应该打开这个选项，可以让服务器在指定的天数之后清理旧的二进制日志

2.max_allowed_packet，防止服务器发送太大的包，也会控制多大的包可以被接收

3.max_connect_errors，如果知道服务器可以充分抵御蛮力攻击，可以把这个值设得非常大，以有效地禁用主机黑名单

4.skip_name_resolve，禁用了另一个网络相关和鉴权谁相关的陷阱：DNS查找

5.sql_mode，不建议修改

6.sysdate_is_now，可能导致与应用预期向后不兼容的选项

7.read_only，禁止没有特权的用户在备库做变更，只接受从主库传输过来的变更，不接受从应用来的变更，可以把备库设置为只读模式

8.skip_slave_start，阻止MySQL试图自动启动复制

9.slave_net_timeout，控制备库发现跟主库的连接已经失败并且需要重连之前等待的时间，设置为一分钟或更短

10.sync_master_info、sync_relay_log、sync_relay_log_info，5.5以后版本可用，解决了复制中备库长期存在的问题：不把它们的状态文件同步到磁盘，所以服务器崩溃后可能需要人来猜测复制的位置实际上在主库是哪个位置，并且可能在中继日志（Relay Log）里有损坏

J.高级InnoDB设置

1.innodb，如果设置为FORCE，只有在InnoDB可以启动时，服务器才会启动

2.innodb_autoinc_lock_mode，控制InnoDB如何生成自增主键值

3.innodb_buffer_pool_instances，在5.5以后，可以把缓冲池切分为多段，在高负载的多核机器上提升MySQL可扩展性的一个重要方式

4.innodb_io_capacity，有时需要把这个设置得相当高，才能稳定地刷新脏页

5.innodb_read_io_threads和innodb_write_io_threads，控制有多少后台线程可以被I/O操作使用

6.innodb_strict_mode，让MySQL在某些条件下把警告改成抛错，尤其是无效的或者可能有风险的CREATE TABLE选项

7.innodb_old_blocks_time，指定一个页面从LRU链表的“年轻”部分转移到“年老”部分之前必须经过的毫秒数，默认为0，设置为1000毫秒（1秒）非常有效

九、操作系统和硬件优化

A.什么限制了MySQL的性能

1.当数据可以放在内存中或者可以从磁盘中以足够快的速度读取时，CPU可能出现瓶颈，把大量的数据集完全放到大容量的内存中，以现在的硬件条件完全是可行的

2.I/O瓶颈，一般发生在工作所需的数据远远超过有效内存容量的时候，如果应用程序是分布在网络上的，或者如果有大量的查询和低延迟的要求，瓶颈可能转移到网络上

B.如何为MySQL选择CPU

1.可以通过检查CPU利用率来判断是否是CPU密集型的工作负载，还需要看看CPU使用率和大多数重要的查询的I/O之间的平衡，并注意CPU负载是否分配均匀

2.当遇到CPU密集型的工作时，MySQL通常可以从更快的CPU中获益，但还依赖于负载情况和CPU数量

3.MySQL复制也能在高速CPU下工作得非常好，而多CPU对复制的帮助却不大

4.多CPU在联机事务处理（OLTP）系统的场景中非常有用，在这样的环境中，并发可能成为瓶颈

C.平衡内存和磁盘资源

1.配置大量内存最终目的是避免磁盘I/O，最关键的是平衡磁盘的大小、速度、成本和其他因素，以便为工作负载提供高性能的表现

2.设计良好的数据库缓存（如InnoDB缓冲池），其效率通常超过操作系统的缓存，因为操作系统缓存是为通用任务设计的

3.数据库服务器同时使用顺序和随机I/O，随机I/O从缓存从受益最多

4.每个应用程序都有一个数据的“工作集”——就是这个工作确实需要用到的数据

5.工作集包括数据和索引，所以应该采用缓存单位来计数，一个缓存单位是存储引擎工作的数据最小单位

6.找到一个良好的内存/磁盘比例最好的方式是通过试验和基准测试

7.硬盘选择考虑因素：存储容量、传输速度、访问时间、主轴转速、物理尺寸

8.MySQL如何扩展到多个磁盘上取决于存储引擎和工作负载，InnoDB能很好地扩展到多个硬盘驱动器，然而，MyISAM的表锁限制其写的可扩展性，因此写繁重的工作加在MyISAM上，可能无法从多个驱动器中收益

D.固态存储

1.高质量闪存设备具备：

• 相比硬盘有更好的随机读写性能

• 相比硬盘有更好的顺序读写性能

• 相比硬盘能更好地支持并发

• 提升随机I/O和并发性

2.闪存的最重要特征是可以迅速完成多次小单位读取，但是写入更有挑战性。闪存不能在没有做擦除操作前改写一个单元（Cell），并且一次必须擦除一个大块。擦除周期是缓慢的，并且最终会磨损整个块

3.垃圾收集对理解闪存很重要。为了保持一些块是干净的并且可以被写入，设备需要回收脏块。这需要设备上有一些空闲空间

4.许多设备被填满后会开始变慢，速度下降是由于没有空闲块时必须等待擦写完成所造成的

5.固态存储最适合使用在任何有着大量随机I/O工作负载的场景下，随机I/O通常是由于数据大于服务器的内存导致的，闪存设备可能大大缓解这种问题

6.单线程工作负载也是另一个闪存的潜在应用场景

7.闪存也可以为服务器整合提供巨大的帮助

8.Flashcache，磁盘和内存技术的结合，适合以读为主的I/O密集型负载，并且工作集太大，用内存优化并不经济的情况

9.优化固态存储上的MySQL

• 增加InnoDB的I/O容量

• 让InnoDB日志文件更大

• 把一些文件从闪存转移到RAID

• 禁用预读

• 配置InnoDB刷新算法

• 禁用双写缓冲的可能

• 限制插入缓冲大小，插入缓冲设计来用于减少当更新行时不在内存中的非唯一索引引起的随机I/O

• InnoDB的页大小

• 优化InnoDB页面校验（Checksum）的替代算法

E.为备库选择硬件

1.通常需要跟主库差不多的配置

F.RAID性能优化

1.RAID可以帮助做冗余、扩展存储容量、缓存，以及加速

2.RAID 0：如果只是简单的评估成本和性能，是成本最低和性能最高的RAID配置

3.RAID 1：在很多情况下提供很好的读性能，并且在不同的磁盘间冗余数据，所以有很好的冗余性，非常适合用来存放日志或者类似的工作

4.RAID 5：通过分布奇偶校验把数据分散到多个磁盘，如果任何一个盘的数据失效，都可以从奇偶校验块中重建，但如果有两个磁盘失效了，则整个卷的数据无法恢复，最经济的冗余配置。随机写是昂贵的，存放数据或者日志是一种可接受的选择，或者是以读为主的业务

5.RAID 10：对数据存储是个非常好的选择，由分片的镜像组成，对读和写都有良好的扩展性

6.RAID 50：由条带化的RAID 5组成

G.SAN和NAS

1.SAN（Storage Area Network）和NAS（Network-Attached Storage）是两个外部文件存储设备加载到服务器的方法，访问SAN设备时通过块接口，NAS设备通过基于文件的协议来访问

2.SAN允许服务器访问非常大量的硬盘驱动器，并且通常配置大容量智能高速缓存来缓冲写入

3.哪些工作放在SAN上不合适：执行大量的随机I/O的单线程任务

4.SAN的应用：

• 备份，可以只备份SAN

• 简化容量规划

• 存储整合还是服务器整合

• 高可用

• 服务器之间的交互

• 成本

H.使用多磁盘卷

1.二进制日志和数据文件分离的真正的优势，是减少事故中同时丢失数据和日志文件的可能性

2.如果有很多磁盘，投入一些给事务日志可能会从中受益

I.网络配置

1.在生产服务器上启用skip_name_resolve是个好主意，损坏或缓慢的DNS解析对许多应用程序都是个问题，对MySQL尤严重，如果启用skip_name_resolve选项，MySQL将不会做任何DNS查找的工作

2.可以通过MySQL的back_log选项控制MySQL的传入TCP连接队列的大小，在每秒有很多连接创建和销毁的环境中，默认值50是不够的

3.网络物理隔离也是很重要的因素，尽可能避免实时的跨数据中心的操作是明智的

J.选择操作系统

1.一般企业级的MySQL部署在Windows上，但一般的企业级MySQL更多的还是部署在类UNIX操作系统上

K.选择文件系统

1.如果可能，最好使用日志文件系统，如ext3、ext4、XFS、ZFS或者JFS

2.可以调整文件系统的预读行为，因为这可能也是多余的

L.选择磁盘队列调度策略

1.在GUN/Linux上，队列调度决定了到块设备的请求实际上发送到底层设备的顺序，默认情况下使用cfq（Completely Fair Queueing，完全公平排队）策略，在MySQL的工作负载类型下，cfq会导致很差的响应时间，因为会在队列中延迟一些不必要的请求

2.cfq之外的两个选项都适合服务器级的硬件，noop调度适合没有自己的调度算法的设备，deadline则对RAID控制器和直接使用的磁盘都工作良好

M.线程

1.MySQL每个连接使用一个线程，另外还有内部处理线程、特殊用途的线程，以及所有存储引擎创建的线程

2.MySQL确实需要内核级线程的支持，而不只是用户级线程，这样才能更有效地使用多个CPU，另外也需要有效的同步原子

N.内存交换区

1.内存交换对MySQL性能影响是很糟糕的，它破坏了缓存在内存的目的，并且相对于使用很小的内存做缓存，使用交换区的性能更差

2.在GNU/Linux上，可以用vmstat来监控内存交换，最好查看si和so列报告的内存交换I/O活动，这比看swpd列报告的交换区利用率更重要，最佳为0

3.设置/proc/sys/vm/swappiness为一个很小的值

4.修改存储引擎怎么读取和写入数据，使用innodb_flush_method=0_DIRECT减轻I/O压力

5.使用MySQL的memlock配置项，可以把MySQL锁定在内存

O.操作系统状态

1.vmstat

• vmstat 5，每隔5秒刷新一次

• procs，r列显示多少进程正在等待CPU，b列显示多少进程正在不可中断地休眠

• memory，swpd列显示多少块被换出到了磁盘，剩下的三个列显示了多少块是空闲的、多少块正在被用作缓冲，以及多少正在被用作操作系统的缓存

• swap，显示页面交换活动

• io，显示有多少块从块设备读取（ bi）和写出（bo）

• system，显示了每秒中断（in）和上下文切换（cs）的数量

• cpu，显示所有的CPU时间花费在各类操作的百分比

2.iostat

• iostats -dx 5，每5秒刷新

• rrqm/s和wrqm/s，每秒合并的读和写请求，意味着操作系统从队列中拿出多个逻辑请求合并为一个请求到实际磁盘

• r/s和w/s，每秒发送到设备的读和写请求

• rsec/s和wsec/s，每秒读和写的扇区数

• avgrq-sz，请求的扇区数

• avgqu-sz，在设备队列中等待的请求数

• await，磁盘排除上花费的毫秒数

• svctm，服务请求花费的毫秒数，不包括排除时间

• %util，至少有一个活跃请求所占时间的百分比

3.CPU密集型的机器，vmstat输出通常在us列会有一个很高的值，也可能在sy列有很高的值

4.I/O密集型工作负载下，vmstat会显示很多处理器在非中断休眠（b列）状态，并且wa这一列的值很高

5.发生内存交换的机器可能在swpd列有一个很高的值

十、复制

A.复制概述

1.MySQL支持两种复制方式：基于行的复制和基于语句的复制，都是通过在主库上记录二进制日志、在备库重放日志的方式来实现异步的数据复制

2.复制通常不会增加主库的开销，主要是启用二进制日志带来的开销，但出于备份或及时从崩溃中恢复的目的，这点开销也是必要的

3.通过复制可以将读操作指向备库来获得更好的读扩展，但对于写操作，除非设计得当，否则并不适合通过写复制来扩展写操作

4.复制解决的问题：

• 数据分布

• 负载均衡

• 备份

• 高可用性和故障切换

• MySQL升级测试

5.复制如何工作

• 在主库上把数据更新记录到二进制日志（Binary Log）中

• 备库将主库上的日志复制到自己的中继日志（Relay Log）中

• 备库读取中继日志中的事件，将其重放到备库数据之上

B.配置复制

1.在每台服务器上创建复制帐号

• 用来监控和管理复制的帐号需要REPLICATION CLIENT权限，并且针对这两种目的使用同一个帐号更加容易

• 如果在主库上建立了帐号，然后从主库将数据克隆到备库时，备库也就设置好了——变成主库所需要的配置

2.配置主库和备库

• 必须明确地指定一个唯一的服务器ID

• 有时候只开启了二进制日志，但却没有开启log_slave_updates，可能会踫到一些奇怪的现象

• 如果可能的话，最好使用read_only配置选项，会阻止任何没有特权权限的线程修改数据

3.通知备库连接到主库并从主库复制数据

4.推荐的复制配置

• sync_binlog =1，在提交事务前会将二进制日志同步到磁盘上，保证在服务器崩溃时不会丢失事件

• 如果无法容忍服务器崩溃导致表损坏，推荐使用InnoDB

• 推荐明确指定二进制日志的名字，log_bin=/var/lib/mysql/mysql-bin

• 在备库上为中继日志指定绝对路径，relay_log

• 如果正在使用5.5并且不介意额外的fsync()导致的性能开销，最好设置：sync_master_info，sync_relay_log，sync_relay_log_info

C.复制的原理

1.基于语句的复制

• 5.0之前只支持基于语句的复制（也称为逻辑复制），主库会记录那些造成数据更改的查询，当备库读取并重放这些事件时，实际上只是把主库上执行过的SQL再执行一遍

• 好处是实现相当简单，日志更加紧凑，不会占用太多带宽

• 问题是基于语句的方式可能并不如其看起来那么便利，还存在一些无法被正确复制的SQL，更新必须是串行的这需要更多的锁

2.基于行的复制

• 5.1开始支持，会将实际数据记录在二进制日志中，跟其他数据库的实现比较想像

• 好处是可以正确地复制每一行，一些语句可以被更加有效地复制

• 如果使用全表更新，则开销会很大，因为每一行的数据都会被记录到二进制日志中，这使得二进制日志事件非常庞大，并且会给主库上记录日志和复制增加额外的负载，更慢的日志记录则会降低并发度

3.基于行或基于语句：哪种更优

• 基于语句的复制模式的优点：当主备的模式不同时，逻辑复制能够在多种情况下工作；基于语句的方式执行复制的过程基本上就是执行SQL语句

• 基于语句的复制模式的缺点：很多情况下通过基于语句的模式无法正确复制，如果正在使用触发器或者存储过程，就不要使用基于语句的复制模式，除非能够清楚地确定不会踫到复制的问题

• 基于行的复制模式的优点：几乎没有基于行的复制模式无法处理的场景；可能减少锁的使用，并不要求这种强串行化是可重复的；会记录数据变更；占用更少的CPU；能够帮助更快地找到并解决数据不致的情况

• 基于行的复制模式的缺点：无法判断执行了哪些SQL；无法知道服务器在做什么；在某些情况下，例如找不到要修改的行时，基于行的复制可能会导致复制停止

4.复制文件

• mysql-bin.index，二进制日志文件

• mysql-relay-bin-index，中继日志的索引文件

• master.info，保存备库连接到主库所需要的信息

• relay-log.info，包含了当前备库复制的二进制日志和中继日志坐标

5.发送复制事件到其他备库：log_slave_updates，可以让备库变成其他服务器的主库

6.复制过滤选项

• 在主库上过滤记录到二进制日志中的事件

• 在备库上过滤记录到中继日志的事件

D.复制拓扑

1.基本原则：

• 一个MySQL备库实例只能有一个主库

• 每个备库都必须有一个唯一的服务器ID

• 一个主库可以有多个备库

• 如果打开了log_slave_updates选项，一个备库可以把其主库上的数据变化传播到其他备库

2.一主库多备库

3.主动-主动模式下的主主复制：auto_increment_increment和auto_increment_offset可以让MySQL自动为INSERT语句选择不互相冲突的值

4.主动-被动模式下的主主复制：其中一台服务器是只读的被动服务器

5.拥有备库的主主结构：增加了冗余，能够消除站点单点失效的问题

6.环形复制：每个服务器都是在它之前的服务器的备库，是在它之后的服务器的主库

7.分发主库事实上也是一个备库，提取和提供主库的二进制日志

8.树或金字塔形：减轻了主库的负担，但中间层出现的任何错误都会影响到多个服务器

9.定制的复制方案

• 选择性复制：配置replicate_wild_do_table

• 分离功能：OLTP、OLAP

• 数据归档：在备库上保留主库上删除过的数据

• 将备库用作全文检索

• 只读备库：read_only选项

• 模拟多主库复制

• 创建日志服务器：创建没有数据的日志服务器，更加容易重放并且/或者过滤二进制日志事件

E.复制和容量规划

1.写操作通常是复制的瓶颈，并且很难使用复制来扩展写操作

2.在构建一个大型应用时，有意让服务器不被充分使用，这应该是一种聪明并且蔓延的方式，尤其在使用复制的时候，有多余容量的服务器可以更好地处理负载尖峰，也有更多能力处理慢速查询和维护工作，并且能够更好地跟上复制

F.复制管理和维护

1.在主库上，可以使用SHOW MASTER STATUS命令来查看当前主库的二进制日志位置和配置

2.从库上，使用SHOW SLAVE STATUS

十一、可扩展的MySQL

A.什么是可扩展性

1.可扩展性表明了当需要增加资源以执行更多工作时系统能够获得划算的等同提升（equal bang for the buck）的能力，缺乏扩展能力的系统在达到收益递减的转折点后，将无法进一步增长

2.可扩展性就是能够通过增加资源来提升容量的能力

B.扩展MySQL

1.规划可扩展性最困难的部分是估算需要承担的负载到底有多少，还需要大致正确地估计日程表，需要知道底线在哪里

2.可以做的准备工作：优化性能、购买性能更强的硬件

3.向上扩展（垂直扩展）意味着购买更多性能强悍的硬件

4.向外扩展（横向扩展、水平扩展）：复制、拆分、数据分片

• 按功能拆分（按职责拆分），不同的节点执行不同的任务

• 数据分片，把数据分割成一小片，或者一小块，然后存储到不同的节点中

• 选择分区键（partitioning key）

• 多个分区键

• 跨分片查询，使用C或Java编写一个辅助应用来执行查询并聚合结果集，也可以借助汇总表来执行

• 分配数据、分片和节点

5.通过多实例扩展

6.通过集群扩展

• MySQL Cluster（NDB Cluster）

• Clustrix

• ScaleBase

• GenieDB

• Akiban

7.向内扩展，对不再需要的数据进行归档和清理

8.保持活跃数据独立

C.负载均衡

1.在一个服务器集群中尽可能地平均负载量，通常在服务器前端设置一个负载均衡器

十二、高可用性

A.什么是高可用性

1.高可用性不是绝对的，只有相对更高的可用性，100%的可用性是不可能达到的

2.可用性每提高一点，所花费的成本都会远超之前，可用性的效果和开销的比例并不是线性的

B.导致宕机的原因

1.运行环境问题，最普遍的问题是磁盘空间耗尽

2.性能问题，最普遍的原因是运行很糟糕的SQL，或服务器BUG或错误的行为

3.糟糕的Schema和索引设计

4.复制问题通常由于主备数据不一致导致

5.数据丢失通常由于DROP TABLE的误操作导致，并总是伴随着缺少可用备份的问题

C.如何实现高可用性

1.可以通过同时进行以下两步来获得高可用性

• 可以尝试避免导致宕机的原因来减少宕机时间

• 尽量保证在发生宕机时能够快速恢复

2.提升平均失效时间（MTBF）

• 对系统变更管理的缺失是所有导致宕机的事件中最普遍的原因

• 缺少严格的评估

• 没有正确地监控MySQL的相关信息

3.降低平均恢复时间（MTTR）

• 所有的宕机事件都是由多方面的失效联合在一起导致的，可以通过利用合适的方法确保单点的安全来避免

D.避免单点失效

1.系统中任何不冗余的部分都是一个可能失效的单点

2.可以采用两种方法来为系统增加冗余：增加空余容量和重复组件

3.共享存储或磁盘复制

• 能够为数据库服务器和存储解耦合，通常使用的是SAN

• 两个优点：可以避免除存储外的其他任何组件失效所引起的数据丢失，并为非存储组件建立冗余提供可能

4.MySQL同步复制

• 当使用同步复制时，主库上的事务只有在至少一个备库上提交后才能认为其执行完成

• 完成了两个目标：当服务器崩溃时没有提交的事务会丢失，并且至少有一个备库拥有实时的数据副本

• MySQL Cluster

• Percona XtraDB Cluster

5.基于复制的冗余

• 复制管理器是使用标准MySQL复制来创建冗余的工具

E.故障转移和故障恢复

1.冗余一点也不会增加可用性或减少宕机，和故障转移结合可以帮助更快地恢复，故障转移最重要的部分就是故障恢复

2.提升备库或切换角色

3.虚拟IP地址或IP接管

4.中间件解决方案，可以使用代理、端口转发、网络地址转换或者硬件负载均衡来实现故障转移和故障恢复

5.在应用中处理故障转移

十三、云端的MySQL

A.云的优点、缺点和相关误解

1.优点：

• 云是一种将基础设施外包出去无须自己管理的方法

• 云一般是按照即用即付的方式支付

• 随着供应商发布新的服务和成本降低，云提供的价值越来越大

• 云能够帮助你轻松地准备好服务器和其他资源

• 云代表了对基础设施的另一种思考方式——作为通过API来定义和控制的资源——支持更多的自动化操作

2.缺点：

• 资源是共享并且不可预测的

• 无法保证容量和可用性

• 虚拟的共享资源导致排查故障更加困难

B.MySQL在云端的经济价值

1.云托管比较适合尚处于初级阶段的企业，或者那些持续接触新概念并且本质上是以适用为主的企业

2.大量使用的策略是尽可能又快又便宜地开发和发布应用

3.运行不是很重要的基础设施

C.云中的MySQL的可扩展性和高可用性

1.数据库通常是一个应用系统中主要或唯一的有状态并且持久化的组件

2.MySQL并不具备在一个无共享集群中的对等角色服务器之间迁移的能力

D.四种基础资源

1.CPU通常少且慢

2.内在大小受限制

3.I/O的吞吐量、延迟以及一致性受到限制

4.网络性能还比较好

E.MySQL在云主机上的性能

1.需要高并发的工作负载并不是非常适合云计算

2.那些需要大量I/O的工作负载在云中并不总是表现很好

F.MySQL数据库即服务（DBaaS）

1.将数据库本身作为云资源

十四、应用层优化

A.常见问题

1.什么东西在消耗系统中每台主机的CPU、磁盘、网络，以及内存资源？这些值是否合理？如果不合理，对应用程序做基本的检查，看什么占用了资源

2.应用真是需要所有获取到的数据吗？

3.应用在处理本应由数据库处理的事情吗，或者反过来？

4.应用执行了太多的查询？

5.应用执行的查询太少了？

6.应用创建了没必要的MySQL连接吗？

7.应用对一个MySQL实例创建连接的次数太多了吗？

8.应用做了太多的“垃圾”查询？

9.应用使用了连接池吗？这既可能是好事，也可能是坏事

10.应用是否使用长连接？

11.应用是否在不使用的时候还保持连接撕开？

B.Web服务器问题

1.最常见的问题是保持它的进程的存活（alive）时间过长，或者在各种不同的用途下混合使用，而不是分别对不同类型的工作进行优化

2.如果用一个通用目的的Apache配置直接用于Web服务，最后很可能产生很多重量级的Apache进程

3.不要使用Apache来做静态内容服务，或者至少和动态服务使用不同的Apache实例

4.进程存活时间变短策略：

• 不要让Apache填鸭式地服务客户端

• 打开gzip压缩

• 不要为用于长距离连接的Apache配置启用Keep-Alive选项

C.缓存

1.被动缓存除了存储和返回数据外不做任何事情；主动缓存在访问未命中时做一些额外工作

2.应用可以缓存部分计算结果，所以应用层缓存可能比更低层次的缓存更有效，可以节省两方面的工作：获取数据以及基于这些数据进行计算，重点是缓存命中率可能更低，并且可能使用较多的内存

3.应用层缓存：

• 本地缓存

• 本地共享内存缓存

• 分布式内存缓存

• 磁盘上的缓存

4.缓存控制策略

• TTL（time to live，存活时间）

• 显式失效，如果不能接受脏数据，那么进程在更新原始数据时需要同时使缓存失效

• 读时失效，在更改旧数据时，为了避免要同时失效派生出来的脏数据，可以在缓存中保存一些信息，当从缓存中读数据时可以利用这些信息判断数据是否已经失效

5.可以在后台预先请求一些页面，并将结果存为静态页面，好处：

• 应用代码没有复杂的命中和未命中处理路径

• 当未命中的处理路径慢得不可接受时，这种方案可以很好地工作

• 预生成内容可以避免在缓存未命中时导致的雪崩效应

D.MySQL的替代品

1.搜索：Lucene和Sphinx

2.简单的键值存储：Redis

3.结构化数据：Hadoop

十五、备份与恢复

A.为什么要备份

1.灾难恢复

2.人们改变想法

3.审计

4.测试

B.定义恢复需求

1.规划备份和恢复策略时，有两个重要的需求：恢复点目标（PRO）和恢复时间目标（RTO）

C.设计MySQL备份方案

1.建议

• 在生产实践中，对于大数据库来说，物理备份是必需的：逻辑备份太慢并受到资源限制，从逻辑备份中恢复需要很长时间

• 保留多个备份集

• 定期从逻辑备份（或者物理备份）中抽取数据进行恢复测试

• 保存二进制日志以用于基于故障时间点的恢复

• 完全不借助备份工具本身来监控备份和备份的过程

• 通过演练整个恢复过程来测试备份和恢复

• 对安全性要仔细考虑

2.如果可能，关闭MySQL做备份是最简单最安全的，需要考虑：锁时间、备份时间、备份负载、恢复时间

3.逻辑备份优点：

• 可以用编辑器或像grep和sed之类的命令查看和操作的普通文件

• 恢复非常简单

• 可能通过网络来备份和恢复

• 可以在类似Amazon RDS这样不能访问底层文件系统的系统中使用

• 非常灵活

• 与存储引擎无关

• 有助于避免数据损坏

4.逻辑备份的缺点：

• 必须由数据库服务器完成生成逻辑备份的工作

• 逻辑备份在某些场景下比数据库文件本身更大

• 无法保证导出后再还原出来的一定是同样的数据

• 从逻辑备份中还原需要MySQL加载和解释语句

5.物理备份优点：

• 基于文件的备份，只需要将需要的文件复制到其他地方即可

• 恢复简单

• InnoDB和MyISAM的物理备份非常容易跨平台

6.物理备份缺点：

• InnoDB的原始文件通常比相应的逻辑备份要大得多

• 物理备份不总是可以跨平台

7.除非经过测试，不要假定备份是正常的

8.建议混合使用物理和逻辑两种方式来做备份

9.MySQL备份需要考虑的几点：

• 非显著数据

• 代码

• 复制配置

• 服务器配置

• 选定的操作系统

10.差异备份是对自上次全备份后所有改变的部分做备份，而增量备份则是自从任意类型的上次备份后所有修改做的备份

11.差异、增量备份的建议：

• 使用Percona XtraBackup和MySQL Enterprise Backup中的增量备份特性

• 备份二进制日志，每次备份后FLUSH LOGS

• 不要备份没有改变的表

• 不要备份没有改变的行

• 某些数据根本不需要备份

• 备份所有的数据，然后发送到一个有去重特性的目的地

12.数据一致性：当备份时，应该考虑是否需要数据在指定时间点一致

13.文件一致性：每个文件的内部一致性

14.从备库中备份最大的好处是可以不干扰主库，故意将一个备库延时一段时间对于某些灾难场景非常有用

D.管理和备份二进制日志

1.expire_log_days变量MySQL定期清理日志

E.备份数据

1.生成逻辑备份

• SQL导出：mysqldump方式

• 符号分隔文件备份：使用SELECT INTO OUTFILE以符号分隔文件格式创建数据的逻辑备份

2.文件系统快照

• 支持快照的文件系统和设备包括FreeBSD的文件系统、ZFS文件系统、GNU/Linux的逻辑卷管理（LVM），以及许多的SAN系统和文件存储解决方案

F.从备份中恢复

1.恢复步骤：

• 停止MySQL服务器

• 记录服务器的配置和文件权限

• 将数据从备份中移到MySQL数据目录

• 改变配置

• 改变文件权限

• 以限制访问模式重启服务器，等待完成启动

• 载入逻辑备份文件

• 检查和重放二进制日志

• 检测已经还原的数据

• 以完全权限重启服务器

G.备份和恢复工具

1.MySQL Enterprise Backup

2.Percona XtraBackup

3.mylvmbackup

4.Zmanda Recovery Manager

5.mydunper

6.mysqldump

十六、MySQL用户工具

A.接口工具

1.MySQL Workbench

2.SQLyog

3.phpMyAdmin

4.Adminer

B.命令行工具集

1.Percona Toolkit

2.Maatkit and Aspersa

3.The openark kit

4.MySql workbench

C.SQL实用集

1.common_schema

2.mysql-sr-lib

3.MySQL UDF仓库

4.MySQL Forge

D.监测工具

1.开源的监控工具

• Nagios

• Zabbix

• Zenoss

• OpenNMS

• Groundwork Open Source

• MRTG

• Cacti

• Ganglia

• Munin

2.商业监控系统

• MySQL Enterprise Monitor

• MONyog

• New Relic

• Circonus

• Monitis

• Splunk

• Pingdom

3.Innotop的命令行监控