一、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，类似某些工业标准测试的测试工具集
* Perconas 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意味着所有的值都是按顺序存储的，并且每一个叶子页到根的距离相同，能够加快访问数据的速度，从索引的根节点开始进行搜索，适用于全键值、键值范围或键前缀查找
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，线程正在对结果集进行排序
* Sing 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 exted-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瓶颈，一般发生在工作所需的数据远远超过有效内存容量的时候，如果应用程序是分布在网络上的，或者如果有大量的查询和低延迟的要。
