|
|
1 优化原理
说起MySQL的查询优化，相信大家会想到：不能使用SELECT *、不使用NULL字段、合理创建索引、为字段选择合适的数据类型..... 你是否真的理解这些优化技巧？是否理解其背后的工作原理？在实际场景下性能真有提升吗？首先我们从MySQL的逻辑与体系结构:
一、MySQL逻辑架构（暂不介绍）
二、MySQL逻辑架构
如果能在头脑中构建一幅MySQL各组件之间如何协同工作的架构图，有助于深入理解MySQL服务器。下图展示了MySQL的逻辑架构图。
MySQL逻辑架构整体分为三层，最上层为客户端层，所包含的服务并不是MySQL所独有的技术。它们都是服务于C/S程序或者是这些程序所需要的 ：连接处理，身份验证，安全性等等。
MySQL这是MySQL的核心部分。通常叫做 SQL Layer。在 MySQL数据库系统处理底层数据之前的所有工作都是在这一层完成的，包括权限判断， sql解析，行计划优化， query cache 的处理以及所有内置的函数(如日期,时间,数学运算,加密)等等。各个存储引擎提供的功能都集中在这一层，如存储过程，触发器，视图等,所有的跨存储引擎的功能也在这一层实现：存储过程、触发器、视图等。
最下层为存储引擎，其负责MySQL中的数据存储和提取。和Linux下的文件系统类似，每种存储引擎都有其优势和劣势。中间的服务层通过API与存储引擎通信，这些API接口屏蔽了不同存储引擎间的差异。
每个存储引擎都有自己的优点和缺陷。服务器是通过存储引擎API来与它们交互的。这个接口隐藏了各个存储引擎不同的地方。对于查询层尽可能的透明。这个API包含了很多底层的操作。如开始一个事务，或者取出有特定主键的行。
存储引擎不能解析SQL，互相之间也不能通信。仅仅是简单的响应服务器的请求。
1.2.1 MySQL查询过程
我们总是希望MySQL能够获得更高的查询性能，最好的办法是弄清楚MySQL是如何优化和执行查询的。一旦理解了这一点，就会发现：很多的查询优化工作实际上就是遵循一些原则让MySQL的优化器能够按照预想的合理方式运行而已。
当向MySQL发送一个请求的时候，MySQL到底做了些什么呢？
MySQL查询过程
客户端/服务端通信协议
MySQL客户端/服务端通信协议是“半双工”的：在任一时刻，要么是服务器向客户端发送数据，要么是客户端向服务器发送数据，这两个动作不能同时发生。一旦一端开始发送消息，另一端要接收完整个消息才能响应它，所以我们无法也无须将一个消息切成小块独立发送，也没有办法进行流量控制。
客户端用一个单独的数据包将查询请求发送给服务器，所以当查询语句很长的时候，需要设置max_allowed_packet参数。但是需要注意的是，如果查询实在是太大，服务端会拒绝接收更多数据并抛出异常。
与之相反的是，服务器响应给用户的数据通常会很多，由多个数据包组成。但是当服务器响应客户端请求时，客户端必须完整的接收整个返回结果，而不能简单的只取前面几条结果，然后让服务器停止发送。因而在实际开发中，尽量保持查询简单且只返回必需的数据，减小通信间数据包的大小和数量是一个非常好的习惯，这也是查询中尽量避免使用SELECT *以及加上LIMIT限制的原因之一。
1.2.2查询缓存
在解析一个查询语句前，如果查询缓存是打开的，那么MySQL会检查这个查询语句是否命中查询缓存中的数据。如果当前查询恰好命中查询缓存，在检查一次用户权限后直接返回缓存中的结果。这种情况下，查询不会被解析，也不会生成执行计划，更不会执行。
MySQL将缓存存放在一个引用表（不要理解成table，可以认为是类似于HashMap的数据结构），通过一个哈希值索引，这个哈希值通过查询本身、当前要查询的数据库、客户端协议版本号等一些可能影响结果的信息计算得来。所以两个查询在任何字符上的不同（例如：空格、注释），都会导致缓存不会命中。
如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、mysql库中的系统表，其查询结果都不会被缓存。比如函数NOW()或者CURRENT_DATE()会因为不同的查询时间，返回不同的查询结果，再比如包含CURRENT_USER或者CONNECION_ID()的查询语句会因为不同的用户而返回不同的结果，将这样的查询结果缓存起来没有任何的意义。
既然是缓存，就会失效，那查询缓存何时失效呢？MySQL的查询缓存系统会跟踪查询中涉及的每个表，如果这些表（数据或结构）发生变化，那么和这张表相关的所有缓存数据都将失效。正因为如此，在任何的写操作时，MySQL必须将对应表的所有缓存都设置为失效。如果查询缓存非常大或者碎片很多，这个操作就可能带来很大的系统消耗，甚至导致系统僵死一会儿。而且查询缓存对系统的额外消耗也不仅仅在写操作，读操作也不例外：
1.任何的查询语句在开始之前都必须经过检查，即使这条SQL语句永远不会命中缓存
2.如果查询结果可以被缓存，那么执行完成后，会将结果存入缓存，也会带来额外的系统消耗
基于此，我们要知道并不是什么情况下查询缓存都会提高系统性能，缓存和失效都会带来额外消耗，只有当缓存带来的资源节约大于其本身消耗的资源时，才会给系统带来性能提升。但要如何评估打开缓存是否能够带来性能提升是一件非常困难的事情，也不在本文讨论的范畴内。如果系统确实存在一些性能问题，可以尝试打开查询缓存，并在数据库设计上做一些优化，比如：
1.用多个小表代替一个大表，注意不要过度设计
2.批量插入代替循环单条插入
3.合理控制缓存空间大小，一般来说其大小设置为几十兆比较合适
4.可以通过SQL_CACHE和SQL_NO_CACHE来控制某个查询语句是否需要进行缓存
最后的忠告是不要轻易打开查询缓存，特别是写密集型应用。如果你实在是忍不住，可以将query_cache_type设置为DEMAND，这时只有加入SQL_CACHE的查询才会走缓存，其他查询则不会，这样可以非常自由地控制哪些查询需要被缓存。
当然查询缓存系统本身是非常复杂的，这里讨论的也只是很小的一部分，其他更深入的话题，比如：缓存是如何使用内存的？如何控制内存的碎片化？事务对查询缓存有何影响等等，读者可以自行阅读相关资料，这里权当抛砖引玉吧。
1.2.3 语法解析和预处理
MySQL通过关键字将SQL语句进行解析，并生成一颗对应的解析树。这个过程解析器主要通过语法规则来验证和解析。比如SQL中是否使用了错误的关键字或者关键字的顺序是否正确等等。预处理则会根据MySQL规则进一步检查解析树是否合法。比如检查要查询的数据表和数据列是否存在等等。
1.2.4 查询优化
经过前面的步骤生成的语法树被认为是合法的了，并且由优化器将其转化成查询计划。多数情况下，一条查询可以有很多种执行方式，最后都返回相应的结果。优化器的作用就是找到这其中最好的执行计划。
MySQL使用基于成本的优化器，它尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。在MySQL可以通过查询当前会话的last_query_cost的值来得到其计算当前查询的成本。
mysql> select * from t_message limit 10;
...省略结果集
mysql> show status like 'last_query_cost';
+-----------------+-------------+
| Variable_name | Value |
+-----------------+-------------+
| Last_query_cost | 6391.799000 |
+-----------------+-------------+ 示例中的结果表示优化器认为大概需要做6391个数据页的随机查找才能完成上面的查询。这个结果是根据一些列的统计信息计算得来的，这些统计信息包括：每张表或者索引的页面个数、索引的基数、索引和数据行的长度、索引的分布情况等等。
有非常多的原因会导致MySQL选择错误的执行计划，比如统计信息不准确、不会考虑不受其控制的操作成本（用户自定义函数、存储过程）、MySQL认为的最优跟我们想的不一样（我们希望执行时间尽可能短，但MySQL值选择它认为成本小的，但成本小并不意味着执行时间短）等等。
MySQL的查询优化器是一个非常复杂的部件，它使用了非常多的优化策略来生成一个最优的执行计划：
1.重新定义表的关联顺序（多张表关联查询时，并不一定按照SQL中指定的顺序进行，但有一些技巧可以指定关联顺序）
2.优化MIN()和MAX()函数（找某列的最小值，如果该列有索引，只需要查找B+Tree索引最左端，反之则可以找到最大值，具体原理见下文）
3.提前终止查询（比如：使用Limit时，查找到满足数量的结果集后会立即终止查询）
4.优化排序（在老版本MySQL会使用两次传输排序，即先读取行指针和需要排序的字段在内存中对其排序，然后再根据排序结果去读取数据行，而新版本采用的是单次传输排序，也就是一次读取所有的数据行，然后根据给定的列排序。对于I/O密集型应用，效率会高很多）
随着MySQL的不断发展，优化器使用的优化策略也在不断的进化，这里仅仅介绍几个非常常用且容易理解的优化策略，其他的优化策略，大家自行查阅吧。
1.2.5 查询执行引擎
在完成解析和优化阶段以后，MySQL会生成对应的执行计划，查询执行引擎根据执行计划给出的指令逐步执行得出结果。整个执行过程的大部分操作均是通过调用存储引擎实现的接口来完成，这些接口被称为handler API。查询过程中的每一张表由一个handler实例表示。实际上，MySQL在查询优化阶段就为每一张表创建了一个handler实例，优化器可以根据这些实例的接口来获取表的相关信息，包括表的所有列名、索引统计信息等。存储引擎接口提供了非常丰富的功能，但其底层仅有几十个接口，这些接口像搭积木一样完成了一次查询的大部分操作。
1.2.6 返回结果给客户端
查询执行的最后一个阶段就是将结果返回给客户端。即使查询不到数据，MySQL仍然会返回这个查询的相关信息，比如改查询影响到的行数以及执行时间等等。
如果查询缓存被打开且这个查询可以被缓存，MySQL也会将结果存放到缓存中。
结果集返回客户端是一个增量且逐步返回的过程。有可能MySQL在生成第一条结果时，就开始向客户端逐步返回结果集了。 这样服务端就无须存储太多结果而消耗过多内存，也可以让客户端第一时间获得返回结果。需要注意的是，结果集中的每一行都会以一个满足①中所描述的通信协议的数据包发送，再通过TCP协议进行传输，在传输过程中，可能对MySQL的数据包进行缓存然后批量发送。
回头总结一下MySQL整个查询执行过程，总的来说分为6个步骤：
1.客户端向MySQL服务器发送一条查询请求
2.服务器首先检查查询缓存，如果命中缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段
3.服务器进行SQL解析、预处理、再由优化器生成对应的执行计划
4.MySQL根据执行计划，调用存储引擎的API来执行查询
5.将结果返回给客户端，同时缓存查询结果
2 事务及引擎
MySQL的存储引擎可能是所有关系型数据库产品中最具有特色的了，不仅可以同时使用多种存储引擎，而且每种存储引擎和MySQL之间使用插件方式这种非常松的耦合关系。
由于各存储引擎功能特性差异较大，需要关注如何来选择合适的存储引擎来应对不同的业务场景。
一、MyISAM
o 特性
1.不支持事务：MyISAM存储引擎不支持事务，所以对事务有要求的业务场景不能使用
2.表级锁定：其锁定机制是表级索引，这虽然可以让锁定的实现成本很小但是也同时大大降低了其并发性能
3.读写互相阻塞：不仅会在写入的时候阻塞读取，MyISAM还会在读取的时候阻塞写入，但读本身并不会阻塞另外的读
4.只会缓存索引：MyISAM可以通过key_buffer缓存以大大提高访问性能减少磁盘IO，但是这个缓存区只会缓存索引，而不会缓存数据
o 适用场景
1.不需要事务支持（不支持）
2.并发相对较低（锁定机制问题）
3.数据修改相对较少（阻塞问题）
4.以读为主
5.数据一致性要求不是非常高
o 最佳实践
1.尽量索引（缓存机制）
2.调整读写优先级，根据实际需求确保重要操作更优先
3.启用延迟插入改善大批量写入性能
4.尽量顺序操作让insert数据都写入到尾部，减少阻塞
5.分解大的操作，降低单个操作的阻塞时间
6.降低并发数，某些高并发场景通过应用来进行排队机制
7.对于相对静态的数据，充分利用Query Cache可以极大的提高访问效率
8.MyISAM的Count只有在全表扫描的时候特别高效，带有其他条件的count都需要进行实际的数据访问
二、InnoDB
o 特性
1.具有较好的事务支持：支持4个事务隔离级别，支持多版本读
2.行级锁定：通过索引实现，全表扫描仍然会是表锁，注意间隙锁的影响
3.读写阻塞与事务隔离级别相关
4.具有非常高效的缓存特性：能缓存索引，也能缓存数据
5.整个表和主键以Cluster方式存储，组成一颗平衡树
6.所有Secondary Index都会保存主键信息
o 适用场景
1.需要事务支持（具有较好的事务特性）
2.行级锁定对高并发有很好的适应能力，但需要确保查询是通过索引完成
3.数据更新较为频繁的场景
4.数据一致性要求较高
5.硬件设备内存较大，可以利用InnoDB较好的缓存能力来提高内存利用率，尽可能减少磁盘 IO
o 最佳实践
1.主键尽可能小，避免给Secondary index带来过大的空间负担
2.避免全表扫描，因为会使用表锁
3.尽可能缓存所有的索引和数据，提高响应速度
4.在大批量小插入的时候，尽量自己控制事务而不要使用autocommit自动提交
5.合理设置innodb_flush_log_at_trx_commit参数值，不要过度追求安全性
6.避免主键更新，因为这会带来大量的数据移动
三、NDBCluster
o 特性
1.分布式：分布式存储引擎，可以由多个NDBCluster存储引擎组成集群分别存放整体数据的一部分
2.支持事务：和Innodb一样，支持事务
3.可与mysqld不在一台主机：可以和mysqld分开存在于独立的主机上，然后通过网络和mysqld通信交互
4.内存需求量巨大：新版本索引以及被索引的数据必须存放在内存中，老版本所有数据和索引必须存在与内存中
o 适用场景
1.具有非常高的并发需求
2.对单个请求的响应并不是非常的critical
3.查询简单，过滤条件较为固定，每次请求数据量较少，又不希望自己进行水平Sharding
o 最佳实践
1.尽可能让查询简单，避免数据的跨节点传输
2.尽可能满足SQL节点的计算性能，大一点的集群SQL节点会明显多余Data节点
3.在各节点之间尽可能使用万兆网络环境互联，以减少数据在网络层传输过程中的延时
3 缓存参数优化
从内存中读取一个数据库的时间是微秒级别，而从一块普通硬盘上读取一个IO是在毫秒级别，二者相差3个数量级。所以，要优化数据库，首先第一步需要优化的就是IO，尽可能将磁盘IO转化为内存IO。从MySQL 数据库IO相关参数（缓存参数）的角度来看看可以通过以下参数进行IO优化（建议级）：
· query_cache_type : 如果全部使用innodb存储引擎，建议为0，如果使用MyISAM 存储引擎，建议为2，同时在SQL语句中显式控制是否使用query cache；
· query_cache_size: 根据命中率（Qcache_hits/(Qcache_hits+Qcache_inserts)*100)）进行调整，一般不建议太大，256MB可能已经差不多了，大型的配置型静态数据可适当调大；
· binlog_cache_size: 一般环境2MB～4MB是一个合适的选择，事务较大且写入频繁的数据库环境可以适当调大，但不建议超过32MB；
· key_buffer_size: 如果不使用MyISAM存储引擎，16MB足以，用来缓存一些系统表信息等。如果使用 MyISAM存储引擎，在内存允许的情况下，尽可能将所有索引放入内存，简单来说就是“越大越好”；
· bulk_insert_buffer_size: 如果经常性的需要使用批量插入的特殊语句（上面有说明）来插入数据，可以适当调大该参数至16MB～32MB，不建议继续增大，某人8MB；
· innodb_buffer_pool_size: 如果不使用InnoDB存储引擎，可以不用调整这个参数，如果需要使用，在内存允许的情况下，尽可能将所有的InnoDB数据文件存放如内存中，同样将但来说也是“越大越好”；
· innodb_additional_mem_pool_size: 一般的数据库建议调整到8MB～16MB，如果表特别多，可以调整到32MB，可以根据error log中的信息判断是否需要增大；
· innodb_log_buffer_size: 默认是1MB，系的如频繁的系统可适当增大至4MB～8MB。当然如上面介绍所说，这个参数实际上还和另外的flush参数相关。一般来说不建议超过32MB；
· innodb_max_dirty_pages_pct: 根据以往的经验，重启恢复的数据如果要超过1G的话，启动速度会比较慢，几乎难以接受，所以建议不大于1GB/innodb_buffer_pool_size(GB)*100这个值。当然，如果你能够忍受启动时间比较长，而且希望尽量减少内存至磁盘的flush，可以将这个值调整到90，但不建议超过90。
注：以上取值范围仅仅只是根据以往遇到的数据库场景所得到的一些优化经验值，并不一定适用于所有场景，所以在实际优化过程中还需要大家自己不断的调整分析。
4 SQL优化
一、 优化目标
1、减少 IO 次数
IO永远是数据库最容易瓶颈的地方，这是由数据库的职责所决定的，大部分数据库操作中超过90%的时间都是 IO 操作所占用的，减少 IO 次数是 SQL 优化中需要第一优先考虑，当然，也是收效最明显的优化手段。
2、降低 CPU 计算
除了 IO 瓶颈之外，SQL优化中需要考虑的就是 CPU 运算量的优化了。order by,group by,distinct … 都是消耗 CPU 的大户（这些操作基本上都是 CPU 处理内存中的数据比较运算）。当我们的 IO 优化做到一定阶段之后，降低 CPU计算也就成为了我们 SQL 优化的重要目标。
二、 优化方法
一、监控分析
1、硬件资源监控
关注的主要数据库服务器在IO和CPU方面的指标。
2、mysql性能分析器
可以利用mysql profiling（mysql性能分析器）来优化sql语句，即查看SQL执行消耗系统资源的信息（需要开启才能应用该功能）。
3、慢查询分析
通过慢日志查询可以知道哪些SQL语句执行效率低下，那些sql语句使用的频率高等。
对MySQL查询语句的监控、分析、优化是MySQL优化非常重要的一步。开启慢查询日志后，由于日志记录操作，在一定程度上会占用CPU资源影响mysql的性能，但是可以阶段性开启来定位性能瓶颈。
4、实战
提供一个提升SQL水平的链接，有兴趣的可以刷一下：https://www.nowcoder.com/ta/sql
5、SQL 执行计划
明确了优化目标之后，我们需要确定达到我们目标的方法。对于 SQL 语句来说，达到上述2个优化目标的方法其实只有一个，那就是改变 SQL 的执行计划，让他尽量“少走弯路”，尽量通过各种“捷径”来找到我们需要的数据，以达到“减少IO次数”和“降低 CPU 计算”的目标。
使用explain命令查看query语句的性能：
EXPLAIN select * from tablename; ##查看执行计划中的sql性能 上面这是最简单的执行计划实例，会查询出来如下：
具体EXPLAIN 性能参数详细优化可参考：
三、 常见误区
1、count(1)和count(primary_key)优于count(*)
很多人为了统计记录条数，就使用 count(1) 和count(primary_key) 而不是 count(*) ，他们认为这样性能更好，其实这是一个误区。对于有些场景，这样做可能性能会更差，应为数据库对 count(*) 计数操作做了一些特别的优化。
2、count(column)和count(*)是一样的
这个误区甚至在很多的资深工程师或者是 DBA 中都普遍存在，很多人都会认为这是理所当然的。实际上，count(column) 和 count(*) 是一个完全不一样的操作，所代表的意义也完全不一样。
count(column) 是表示结果集中有多少个column字段不为空的记录；
count(*) 是表示整个结果集有多少条记录。
3、select a,bfrom …比 selecta,b,c from …可以让数据库访问更少的数据量
这个误区主要存在于大量的开发人员中，主要原因是对数据库的存储原理不是太了解。
实际上，大多数关系型数据库都是按照行（row）的方式存储，而数据存取操作都是以一个固定大小的IO单元（被称作 block 或者 page）为单位，一般为4KB，8KB…大多数时候，每个IO单元中存储了多行，每行都是存储了该行的所有字段（lob等特殊类型字段除外）。
所以，我们是取一个字段还是多个字段，实际上数据库在表中需要访问的数据量其实是一样的。
当然，也有例外情况，那就是我们的这个查询在索引中就可以完成，也就是说当只取 a,b两个字段的时候，不需要回表，而c这个字段不在使用的索引中，需要回表取得其数据。在这样的情况下，二者的IO量会有较大差异。
4、order by一定需要排序操作
我们知道索引数据实际上是有序的，如果我们的需要的数据和某个索引的顺序一致，而且我们的查询又通过这个索引来执行，那么数据库一般会省略排序操作，而直接将数据返回，因为数据库知道数据已经满足我们的排序需求了。
实际上，利用索引来优化有排序需求的 SQL，是一个非常重要的优化手段
5、执行计划中有 filesort 就会进行磁盘文件排序
有这个误区其实并不能怪我们，而是因为 MySQL 开发者在用词方面的问题。filesort是我们在使用 explain 命令查看一条 SQL 的执行计划的时候可能会看到在“Extra”一列显示的信息。
实际上，只要一条 SQL 语句需要进行排序操作，都会显示“Using filesort”，这并不表示就会有文件排序操作。
四、 基本原则
1、尽量少 join
MySQL 的优势在于简单，但这在某些方面其实也是其劣势。MySQL优化器效率高，但是由于其统计信息的量有限，优化器工作过程出现偏差的可能性也就更多。对于复杂的多表 Join，一方面由于其优化器受限，再者在Join这方面所下的功夫还不够，所以性能表现离Oracle等关系型数据库前辈还是有一定距离。但如果是简单的单表查询，这一差距就会极小甚至在有些场景下要优于这些数据库前辈。
2、尽量少排序
排序操作会消耗较多的 CPU 资源，所以减少排序可以在缓存命中率高等 IO 能力足够的场景下会较大影响 SQL的响应时间。
对于MySQL来说，减少排序有多种办法，比如：
o 上面误区中提到的通过利用索引来排序的方式进行优化
o 减少参与排序的记录条数
o 非必要不对数据进行排序
o 避免使用耗费资源的操作，带有DISTINCT,UNION,MINUS,INTERSECT,ORDERBY的SQL语句会启动SQL引擎 执行，耗费资源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要执行两次排序
o …
3、尽量避免 select *
很多人看到这一点后觉得比较难理解，上面不是在误区中刚刚说 select 子句中字段的多少并不会影响到读取的数据吗？
是的，大多数时候并不会影响到 IO 量，但是当我们还存在order by 操作的时候，select 子句中的字段多少会在很大程度上影响到我们的排序效率。
此外，上面误区中不是也说了，只是大多数时候是不会影响到IO量，当我们的查询结果仅仅只需要在索引中就能找到的时候，还是会极大减少IO量的。
4、尽量用join代替子查询
虽然Join性能并不佳，但是和MySQL的子查询比起来还是有非常大的性能优势。MySQL的子查询执行计划一直存在较大的问题，虽然这个问题已经存在多年，但是到目前已经发布的所有稳定版本中都普遍存在，一直没有太大改善。虽然官方也在很早就承认这一问题，并且承诺尽快解决，但是至少到目前为止我们还没有看到哪一个版本较好的解决了这一问题。
5、尽量少or
当 where 子句中存在多个条件以“或”并存的时候，MySQL 的优化器并没有很好的解决其执行计划优化问题，再加上 MySQL 特有的 SQL 与 Storage 分层架构方式，造成了其性能比较低下，很多时候使用 union all 或者是union（必要的时候）的方式来代替“or”会得到更好的效果。
6、尽量用 union all 代替 union
union 和 union all 的差异主要是前者需要将两个（或者多个）结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的 CPU 运算，加大资源消耗及延迟。所以当我们可以确认不可能出现重复结果集或者不在乎重复结果集的时候，尽量使用 union all 而不是 union。
7、尽量早过滤
这一优化策略其实最常见于索引的优化设计中（将过滤性更好的字段放得更靠前）。
在 SQL 编写中同样可以使用这一原则来优化一些 Join 的 SQL。比如我们在多个表进行分页数据查询的时候，我们最好是能够在一个表上先过滤好数据分好页，然后再用分好页的结果集与另外的表 Join，这样可以尽可能多的减少不必要的 IO 操作，大大节省 IO 操作所消耗的时间。
8、避免类型转换
这里所说的“类型转换”是指 where 子句中出现 column 字段的类型和传入的参数类型不一致的时候发生的类型转换：
o 人为在column_name 上通过转换函数进行转换
直接导致 MySQL（实际上其他数据库也会有同样的问题）无法使用索引，如果非要转换，应该在传入的参数上进行转换
o 由数据库自己进行转换
如果我们传入的数据类型和字段类型不一致，同时我们又没有做任何类型转换处理，MySQL 可能会自己对我们的数据进行类型转换操作，也可能不进行处理而交由存储引擎去处理，这样一来，就会出现索引无法使用的情况而造成执行计划问题。
SELECT emp.ename, emp.job FROM emp WHERE emp.empno = 7369;
不要使用：SELECT emp.ename, emp.job FROM emp WHEREemp.empno = '7369'
9、能用DISTINCT的就不用GROUP BY
group by 操作特别慢，比如：
SELECT OrderID FROM DetailsWHERE UnitPrice > 10 GROUP BY OrderID
可改为：
SELECT DISTINCT OrderID FROMDetails WHERE UnitPrice > 10
10、尽量不要用SELECT INTO语句
SELECT INOT 语句会导致表锁定，阻止其他用户访问该表
11、优先优化高并发的SQL，而不是执行频率低某些“大”SQL
对于破坏性来说，高并发的 SQL 总是会比低频率的来得大，因为高并发的SQL一旦出现问题，甚至不会给我们任何喘息的机会就会将系统压跨。而对于一些虽然需要消耗大量 IO 而且响应很慢的 SQL，由于频率低，即使遇到，最多就是让整个系统响应慢一点，但至少可能撑一会儿，让我们有缓冲的机会。
12、从全局出发优化，而不是片面调整
SQL 优化不能是单独针对某一个进行，而应充分考虑系统中所有的 SQL，尤其是在通过调整索引优化 SQL的执行计划的时候，千万不能顾此失彼，因小失大。
13、尽可能对每一条运行在数据库中的SQL进行explain
优化 SQL，需要做到心中有数，知道 SQL的执行计划才能判断是否有优化余地，才能判断是否存在执行计划问题。在对数据库中运行的 SQL 进行了一段时间的优化之后，很明显的问题 SQL 可能已经很少了，大多都需要去发掘，这时候就需要进行大量的 explain 操作收集执行计划，并判断是否需要进行优化。
14、其他优化方式
（1）适当使用视图加速查询：把表的一个子集进行排序并创建视图，有时能加速查询（特别是要被多次执行的查询）。它有助于避免多重排序操作，而且在其他方面还能简化优化器的工作。视图中的行要比主表中的行少，而且物理顺序就是所要求的顺序，减少了磁盘I/O，所以查询工作量可以得到大幅减少。
（2）算法优化：尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。.使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。
游标提供了对特定集合中逐行扫描的手段，一般使用游标逐行遍历数据，根据取出的数据不同条件进行不同的操作。尤其对多表和大表定义的游标（大的数据集合）循环很容易使程序进入一个漫长的等特甚至死机。
在有些场合，有时也非得使用游标，此时也可考虑将符合条件的数据行转入临时表中，再对临时表定义游标进行操作，可时性能得到明显提高。
（3）封装存储过程：经编译和优化后存储在数据库服务器中，运行效率高，可以降低客户机和服务器之间的通信量，有利于集中控制，易于维护。
5 表结构优化
由于MySQL数据库是基于行（Row）存储的数据库，而数据库操作 IO 的时候是以 page（block）的方式，也就是说，如果我们每条记录所占用的空间量减小，就会使每个page中可存放的数据行数增大，那么每次 IO 可访问的行数也就增多了。反过来说，处理相同行数的数据，需要访问的 page 就会减少，也就是 IO 操作次数降低，直接提升性能。此外，由于我们的内存是有限的，增加每个page中存放的数据行数，就等于增加每个内存块的缓存数据量，同时还会提升内存交换中数据命中的几率，也就是缓存命中率。
一、数据类型选择
数据库操作中最为耗时的操作就是 IO 处理，大部分数据库操作 90%以上的时间都花在了 IO 读写上面。所以尽可能减少 IO 读写量，可以在很大程度上提高数据库操作的性能。我们无法改变数据库中需要存储的数据，但是我们可以在这些数据的存储方式方面花一些心思。原则是：数据行的长度不要超过8020字节，如果超过这个长度的话在物理页中这条数据会占用两行从而造成存储碎片，降低查询效率；字段的长度在最大限度的满足可能的需要的前提下，应该尽可能的设得短一些，这样可以提高查询的效率，而且在建立索引的时候也可以减少资源的消耗。
下面的这些关于字段类型的优化建议主要适用于记录条数较多，数据量较大的场景，因为精细化的数据类型设置可能带来维护成本的提高，过度优化也可能会带来其他的问题：
1、数字类型：非万不得已不要使用DOUBLE，不仅仅只是存储长度的问题，同时还会存在精确性的问题。同样，固定精度的小数，也不建议使用DECIMAL，建议乘以固定倍数转换成整数存储，可以大大节省存储空间，且不会带来任何附加维护成本。对于整数的存储，在数据量较大的情况下，建议区分开 TINYINT / INT / BIGINT 的选择，因为三者所占用的存储空间也有很大的差别，能确定不会使用负数的字段，建议添加unsigned定义。当然，如果数据量较小的数据库，也可以不用严格区分三个整数类型。
能够用数字类型的字段尽量选择数字类型而不用字符串类型的（电话号码），这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
2、字符类型：非万不得已不要使用 TEXT 数据类型，其处理方式决定了他的性能要低于char或者是varchar类型的处理。定长字段，建议使用 CHAR 类型（char查询快，但是耗存储空间，可用于用户名、密码等长度变化不大的字段），不定长字段尽量使用 VARCHAR（varchar查询相对慢一些但是节省存储空间，可用于评论等长度变化大的字段），且仅仅设定适当的最大长度，而不是非常随意的给一个很大的最大长度限定，因为不同的长度范围，MySQL也会有不一样的存储处理。
3、时间类型：尽量使用TIMESTAMP类型，因为其存储空间只需要DATETIME 类型的一半。对于只需要精确到某一天的数据类型，建议使用DATE类型，因为他的存储空间只需要3个字节，比TIMESTAMP还少。不建议通过INT类型类存储一个unix timestamp 的值，因为这太不直观，会给维护带来不必要的麻烦，同时还不会带来任何好处。
4、ENUM &SET：对于状态字段，可以尝试使用 ENUM 来存放，因为可以极大的降低存储空间，而且即使需要增加新的类型，只要增加于末尾，修改结构也不需要重建表数据。如果是存放可预先定义的属性数据呢？可以尝试使用SET类型，即使存在多种属性，同样可以游刃有余，同时还可以节省不小的存储空间。
5、LOB类型：强烈反对在数据库中存放 LOB类型数据，虽然数据库提供了这样的功能，但这不是他所擅长的，我们更应该让合适的工具做他擅长的事情，才能将其发挥到极致。在数据库中存储 LOB 数据就像让一个多年前在学校学过一点Java的营销专业人员来写 Java 代码一样。
二、字符编码
字符集直接决定了数据在MySQL中的存储编码方式，由于同样的内容使用不同字符集表示所占用的空间大小会有较大的差异，所以通过使用合适的字符集，可以帮助我们尽可能减少数据量，进而减少IO操作次数。
1、纯拉丁字符能表示的内容，没必要选择 latin1 之外的其他字符编码，因为这会节省大量的存储空间；
2、如果我们可以确定不需要存放多种语言，就没必要非得使用UTF8或者其他UNICODE字符类型，这回造成大量的存储空间浪费；
3、MySQL的数据类型可以精确到字段，所以当我们需要大型数据库中存放多字节数据的时候，可以通过对不同表不同字段使用不同的数据类型来较大程度减小数据存储量，进而降低 IO 操作次数并提高缓存命中率。
三、适当拆分
有些时候，我们可能会希望将一个完整的对象对应于一张数据库表，这对于应用程序开发来说是很有好的，但是有些时候可能会在性能上带来较大的问题。当我们的表中存在类似于 TEXT 或者是很大的 VARCHAR类型的大字段的时候，如果我们大部分访问这张表的时候都不需要这个字段，我们就该义无反顾的将其拆分到另外的独立表中，以减少常用数据所占用的存储空间。这样做的一个明显好处就是每个数据块中可以存储的数据条数可以大大增加，既减少物理 IO 次数，也能大大提高内存中的缓存命中率。
上面几点的优化都是为了减少每条记录的存储空间大小，让每个数据库中能够存储更多的记录条数，以达到减少 IO 操作次数，提高缓存命中率。下面这个优化建议可能很多开发人员都会觉得不太理解，因为这是典型的反范式设计，而且也和上面的几点优化建议的目标相违背。
四、适度冗余
为什么我们要冗余？这不是增加了每条数据的大小，减少了每个数据块可存放记录条数吗？确实，这样做是会增大每条记录的大小，降低每条记录中可存放数据的条数，但是在有些场景下我们仍然还是不得不这样做：
被频繁引用且只能通过 Join 2张（或者更多）大表的方式才能得到的独立小字段
这样的场景由于每次Join仅仅只是为了取得某个小字段的值，Join到的记录又大，会造成大量不必要的 IO，完全可以通过空间换取时间的方式来优化。不过，冗余的同时需要确保数据的一致性不会遭到破坏，确保更新的同时冗余字段也被更新
五、尽量使用 NOT NULL
NULL 类型比较特殊，SQL 难优化。虽然 MySQL NULL类型和 Oracle 的NULL有差异，会进入索引中，但如果是一个组合索引，那么这个NULL 类型的字段会极大影响整个索引的效率。此外，NULL在索引中的处理也是特殊的，也会占用额外的存放空间。
很多人觉得 NULL 会节省一些空间，所以尽量让NULL来达到节省IO的目的，但是大部分时候这会适得其反，虽然空间上可能确实有一定节省，倒是带来了很多其他的优化问题，不但没有将IO量省下来，反而加大了SQL的IO量。所以尽量确保 DEFAULT 值不是 NULL，也是一个很好的表结构设计优化习惯。
6 索引优化
大家都知道索引对于数据访问的性能有非常关键的作用，都知道索引可以提高数据访问效率。为什么索引能提高数据访问性能？他会不会有“副作用”？是不是索引创建越多，性能就越好？到底该如何设计索引，才能最大限度的发挥其效能？这篇文章主要是带着上面这几个问题来做一个简要的分析，同时排除了业务场景所带来的特殊性，请不要纠结业务场景的影响。
索引为什么能提高数据访问性能？
很多人只知道索引能够提高数据库的性能，但并不是特别了解其原理，其实我们可以用一个生活中的示例来理解。我们让一位不太懂计算机的朋友去图书馆确认一本叫做《MySQL性能调优与架构设计》的书是否在藏，这样对他说：“请帮我借一本计算机类的数据库书籍，是属于 MySQL 数据库范畴的，叫做《MySQL性能调优与架构设计》”。朋友会根据所属类别，前往存放“计算机”书籍区域的书架，然后再寻找“数据库”类存放位置，再找到一堆讲述“MySQL”的书籍，最后可能发现目标在藏（也可能已经借出不在书架上）。在这个过程中：“计算机”->“数据库”->“MySQL”->“在藏”->《MySQL性能调优与架构设计》其实就是一个“根据索引查找数据”的典型案例，“计算机”->“数据库”->“MySQL”->“在藏”就是朋友查找书籍的索引。假设没有这个索引，那查找这本书的过程会变成怎样呢？朋友只能从图书馆入口一个书架一个书架的“遍历”，直到找到《MySQL性能调优与架构设计》这本书为止。如果幸运，可能在第一个书架就找到。但如果不幸呢，那就惨了，可能要将整个图书馆所有的书架都找一遍才能找到我们想要的这本书。注：这个例子中的“索引”是记录在我们大脑中的，实际上，每个图书馆都会有一个非常全的实际存在的索引系统（大多位于入口显眼处），由很多个贴上了明显标签的小抽屉构成。这个索引系统中存放这非常齐全详尽的索引数据，标识出我们需要查找的“目标”在某个区域的某个书架上。而且每当有新的书籍入库，旧的书籍销毁以及书记信息修改，都需要对索引系统进行及时的修正。
下面我们通过上面这个生活中的小示例，来分析一下索引，看看能的出哪些结论？
一、索引有哪些“副作用”？
1、图书的变更（增，删，改）都需要修订索引，索引存在额外的维护成本；
2、查找翻阅索引系统需要消耗时间，索引存在额外的访问成本；
3、这个索引系统需要一个地方来存放，索引存在额外的空间成本。
二、索引是不是越多越好？
1、如果我们的这个图书馆只是一个进出中转站，里面的新书进来后很快就会转发去其他图书馆而从这个馆藏中“清除”，那我们的索引就只会不断的修改，而很少会被用来查找图书。
所以，对于类似于这样的存在大量和频繁更新的数据，索引的维护成本会非常高，如果其检索需求很少，而且对检索效率并没有非常高的要求的时候，我们并不建议创建索引，或者是尽量减少索引。
2、如果我们的书籍量少到只有几本或者就只有一个书架，索引并不会带来什么作用，甚至可能还会浪费一些查找索引所花费的时间。
所以，对于数据量极小到通过索引检索还不如直接遍历来得快的数据，也并不适合使用索引。
3、如果我们的图书馆只有一个10平方的面积，现在连放书架都已经非常拥挤，而且馆藏还在不断增加，我们还能考虑创建索引吗？
所以，当我们连存储基础数据的空间都捉襟见肘的时候，我们也应该尽量减少低效或者是去除索引。
三、索引该如何设计才高效？
1、如果我们仅仅只是这样告诉对方的：“帮我确认一本数据库类别的讲述 MySQL的叫做《MySQL性能调优与架构设计》的书是否在藏”，结果又会如何呢？朋友只能一个大类区域一个大类区域的去寻找“数据库”类别，然后再找到“MySQL”范畴，再看到我们所需是否在藏。由于我们少说了一个“计算机类”，朋友就必须到每一个大类去寻找。
所以，我们应该尽量让查找条件尽可能多的在索引中，尽可能通过索引完成所有过滤，回表只是取出额外的数据字段。
2、如果我们是这样说的：“帮我确认一本讲述 MySQL 的数据库范畴的计算机丛书，叫做《MySQL性能调优与架构设计》，看是否在藏”。如果这位朋友并不知道计算机是一个大类，也不知道数据库属于计算机大类，那这位朋友就悲剧了。首先他得遍历每个类别确认“MySQL”存在于哪些类别中，然后从包含“MySQL”书籍中再看有哪些是“数据库”范畴的（有可能部分是讲述PHP或者其他开发语言的），然后再排除非计算机类的（虽然可能并没有必要），然后才能确认。
所以，字段的顺序对组合索引效率有至关重要的作用，过滤效果越好的字段需要更靠前。
3、如果我们还有这样一个需求（虽然基本不可能）：“帮我将图书馆中所有的计算机图书借来”。朋友如果通过索引来找，每次都到索引柜找到计算机书籍所在的区域，然后从书架上搬下一格（假设只能以一格为单位从书架上取下，类比数据库中以block/page为单位读取），取出第一本，然后再从索引柜找到计算机图书所在区域，再搬下一格，取出一本… 如此往复直至取完所有的书。如果他不通过索引来找又会怎样呢？他需要从第一个书架一直往后找，当找到计算机的书，搬下一格，取出所有计算机的书，再往后，直至所有书架全部看一遍。在这个过程中，如果计算机类书籍较多，通过索引来取所花费的时间很可能要大于直接遍历，因为不断往复的索引翻阅所消耗的时间会非常长。（延伸阅读：可以参照Oracle的索引优化进行解读，索引扫描还是全表扫描（Index Scan Or Full Table Scan））
所以，当我们需要读取的数据量占整个数据量的比例较大抑或者说索引的过滤效果并不是太好的时候，使用索引并不一定优于全表扫描。
4、如果我们的朋友不知道“数据库”这个类别可以属于“计算机”这个大类，抑或者图书馆的索引系统中这两个类别属性并没有关联关系，又会怎样呢？也就是说，朋友得到的是2个独立的索引，一个是告知“计算机”这个大类所在的区域，一个是“数据库”这个小类所在的区域（很可能是多个区域），那么他只能二者选其一来搜索我的需求。即使朋友可以分别通过2个索引检索然后自己在脑中取交集再找，那这样的效率实际过程中也会比较低下。
所以，在实际使用过程中，一次数据访问一般只能利用到1个索引，这一点在索引创建过程中一定要注意，不是说一条SQL语句中Where子句里面每个条件都有索引能对应上就可以了。
5、最后总结一下法则：不要在建立的索引的数据列上进行下列操作：
◆避免对索引字段进行计算操作
◆避免在索引字段上使用not，like ‘%L’，!=，<>，in，or连接
◆避免在索引列上使用IS NULL和IS NOT NULL
◆避免在索引列上出现数据类型转换
◆避免在索引字段上使用函数
◆避免建立索引的列中使用空值。
7 架构优化
一、分布式和集群化
1、负载均衡
负载均衡集群是由一组相互独立的计算机系统构成，通过常规网络或专用网络进行连接，由路由器衔接在一起，各节点相互协作、共同负载、均衡压力，对客户端来说，整个群集可以视为一台具有超高性能的独立服务器。MySQL一般部署的是高可用性负载均衡集群，具备读写分离，一般只对读进行负载均衡。
2、读写分离
读写分离简单的说是把对数据库读和写的操作分开对应不同的数据库服务器，这样能有效地减轻数据库压力，也能减轻io压力。主数据库提供写操作，从数据库提供读操作，其实在很多系统中，主要是读的操作。当主数据库进行写操作时，数据要同步到从的数据库，这样才能有效保证数据库完整性。
3、数据切分
通过某种特定的条件，将存放在同一个数据库中的数据分散存放到多个数据库上，实现分布存储，通过路由规则路由访问特定的数据库，这样一来每次访问面对的就不是单台服务器了，而是N台服务器，这样就可以降低单台机器的负载压力。数据切分一般包括垂直切分和水平切分。
数据的垂直切分，也可以称之为纵向切分。将数据库想象成为由很多个一大块一大块的“数据块”（表）组成，我们垂直的将这些“数据块”切开，然后将他们分散到多台数据库主机上面。这样的切分方法就是一个垂直（纵向）的数据切分。
数据的垂直切分基本上可以简单的理解为按照表、按照模块来切分数据，而水平切分就不再是按照表或者是功能模块来切分了。一般来说，简单的水平切分主要是将某个访问极其平凡的表再按照某个字段的某种规则来分散到多个表之中，每个表中包含一部分数据。
二、可扩展设计的数据库之外延伸之Cache与Search的利用
使用较为成熟的第三方解决方案最大的优势就在于在节省自身研发成本的同时，还能够在互联网上面找到较多的文档信息，帮助我们解决一些日常遇到的问题还是非常有帮助的。
目前比较流行的第三方Cache解决方案主要有基于对象的分布式内存Cache软件Memcached和嵌入式数据库编程库BerkeleyDB这两种。下面我将分别针对这两种解决方案做一个分析和架构探讨。
通过引入Cache（Redis、Memcached），减少数据库的访问，降低磁盘的IO，增加性能。
通过引入Search（Lucene、Solr、ElasticSearch），利用搜索引擎高效的全文索引和分词算法，以及高效的数据检索实现，来解决数据库和传统的Cache软件完全无法解决的全文模糊搜索、分类统计查询等功能。
分布式内存Cache软件Memcached
相信对于很多读者朋友来说，Memcached并不会太陌生了吧，他现在的流行程度已经比MySQL并不会差太多了。Memcached之所以如此的流行，主要是因为以下几个原因：
◆通信协议简单，API接口清晰；
◆高效的Cache算法，基于libevent的事件处理机制，性能卓越；
◆ 面向对象的特性，对应用开发人员来说非常友好；
◆ 所有数据都存放于内存中，数据访问高效；
◆软件开源，基于BSD开源协议；
1. 作为提升系统性能的Cache 工具
如果我们仅仅只是系统通过Memcached来提升系统性能，作为一个Cache软件，那么更多的是需要通过应用程序来维护Memcached中的数据与数据库中数据的同步更新。这时候的Memcached基本可以理解为比MySQL数据库更为前端的一个Cache层。
如果我们将Memcached作为应用系统的一个数据Cache服务，那么对于MySQL数据库来说基本上不用做任何改造，仅仅通过应用程序自己来对这个Cache进行维护更新。这样作最大的好处就在于可以做到完全不用动数据库相关的架构，但是同时也会有一个弊端，那就是如果需要Cache的数据对象较多的时候，应用程序所需要增加的代码量就会增加很多，同时系统复杂度以及维护成本也会直线上升。
下面是将Memcached用为简单的Cache服务层的时候的架构简图。
从图中我们可以看到，所有数据都会写入MySQLMaster中，包括数据第一次写入时候的INSERT，同时也包括对已有数据的UPDATE和DELETE。不过，如果是对已经存在的数据，则需要在UPDATE或者DELETEMySQL中数据的同时，删除Memcached中的数据，以此保证整体数据的一致性。而所有的读请求首先会发往Memcached中，如果读取到数据则直接返回，如果没有读取到数据，则再到MySQLSlaves中读取数据，并将读取得到的数据写入到Memcached中进行Cache。
这种使用方式一般来说比较适用于需要缓存对象类型少，而需要缓存的数据量又比较大的环境，是一个快速有效的完全针对性能问题的解决方案。由于这种架构方式和MySQL数据库本身并没有太大关系，所以这里就不涉及太多的技术细节了。
2.和MySQL整合为数据服务层
除了将Memcached用作快速提升效率的工具之外，我们其实还可以将之利用到提高数据服务层的扩展性方面，和我们的数据库整合成一个整体，或者作为数据库的一个缓冲。
我们首先看看如何将Memcached和MySQL数据库整合成一个整体来对外提供服务吧。一般来说，我们有两种方式将Memcached和MySQL数据库整合成一个整体来对外提供数据服务。一种是直接利用Memcached的内存容量作为MySQL数据库的二级缓存，提升MySQLServer的缓存大小，另一种是通过MySQL的UDF来和Memcached进行数据通信，维护和更新Memcached中的数据，而应用端则直接通过Memcached来读取数据。
对于第一种方式，主要用于业务要求非常特殊，实在难以进行数据切分，而且有很难通过对应用程序进行改造利用上数据库之外的Cache的场景。
当然，在正常情况下是肯定无法做到这一点的，之少目前必须借助外界的力量，开源项目WaffleGrid就是我们需要借助的外部力量。
WaffleGrid是国外的几位DBA在工作之余突发奇想出来的一个点子：既然PCServer的低廉成本如此的吸引我们，而其ScaleUp的能力又很难有一个较大的突破，何不利用上现在非常流行的Memcached作为突破单台PCServer的内存上限呢？就在这个想法的推动下，几位小伙子启动了WaffleGrid这个开源项目，利用MySQL和Memcached双双开源的特性，结合Memcached通信协议简单的特点，将Memcached成功实现成为MySQL主机的外部“二级缓存”，目前仅支持用于Innodb的BufferPool。
WaffleGrid的实现原理其实并不复杂，他所做的事情就是当Innodb在本地的BufferPool（我们姑且称其为LocalBufferPool吧）的时候，在从磁盘数据文件读取数据之前，先通过Memcached的通信API接口尝试从Memcached中读取相应的缓存数据（我们称之为RemoteBuffer吧），只有在RemoteBuffer中也不存在需要的数据的时候，Innodb才会访问磁盘文件来读取数据。而且，只有处于InnodbBufferpool中的LRUList中的数据会被发送到RemoteBufferPool中，而这些数据一旦被修改，就会Innodb就会将之移入FLUSHList，WaffleGrid同时会将进入FLUSHList的数据从RemoteBufferPool中清除掉。所以可以说，RemoteBufferPool中永远不会存在DirtyPages，这也保证了当RemoteBufferPool出现故障的时候不会产生数据丢失的问题。
下图是使用WaffleGrid项目时候的架构简图：
如架构图上所示，我们首先在MySQL数据库端应用WaffleGridPatch，通过他连与其他的Memcached服务器通信。为了保证网络通信的性能，MySQL与Memcached之间尽可能用高带宽私有网络。
另外，这里的架构图中并没有再将数据库区分Master和Slave了，并不是说一定不能区分，只是一个示意图。在实际应用过程中，大部分时候只需要在Slave上面应用WaffleGrid即可，Master本身并不需要如此大的内存。
看了WaffleGrid的实现原理，可能有些读者朋友会有些疑问了。这样做不是所有需要产生物理读的Query的性能就会受到直接影响了吗？所有读取RemoteBuffer的操作都需要通过网络来获取，其性能是否足够高呢？对此，我同样使用作者对Waffle的实测数据来接触大家的疑虑：
通过DBT2所得到的这组测试对比数据，在性能我想并不需要太多的担忧了吧。至于WaffleGrid是否适合您的应用场景，那就只能依靠各位读者朋友自己进行评估了。
下面我们再来介绍一下Memcached和MySQL的另外一种整合方式，也就是通过MySQL所提供的UDF功能，自行编写相应的程序来实现MySQL与Memcached的数据通信更新操作。
这种方式和WaffleGrid不一样的是Memcached中的数据并不完全由MySQL来控制维护，而是由应用程序和MySQL一起来维护数据。每次应用程序从Memcached读取数据的时候，如果发现找不到自己需要的数据，则再转为从数据库中读取数据，然后将读取到的数据写入Memcached中。而MySQL则控制Memcached中数据的失效清理工作，每次数据库中有数据被更新或者被删除的时候，MySQL则通过用户自行编写的UDF来调用Memcached的API来通知Memcached某些数据已经失效并删除该数据。
基于上面的实现原理，我们可以设计出如下这样的一个数据服务层架构：
如图中所示，此架构和上面将Memcached完全和MySQL读离开作为常规的Cache服务器来比较，最大的区别在于Memcached的数据变为由MySQL数据库来维护更新，而不是应用程序来更新。首先数据被应用程序写入MySQL数据库，这时候将会触发MySQL上面用户自行编写的相关UDF，然后通过该UDF调用Memcached的相关通信接口，将数据写入Memcached。而当MySQL中的数据被更新或者删除的时候，MySQL中的相关UDF同样会更新或者删除Memcached中的数据。当然，我们也可以让MySQL做更少一些的事情，仅仅只是遇到数据被更新或者删除的时候，通过UDF来删除Memcached中的数据，写入工作则像前面的架构一样由应用程序来作。
由于Memcached基于对象的数据存取，以及通过Hash进行数据检索的特性，所以所有存储在Memcached中的数据都需要我们设定一个用于标识该数据的Key，所有数据的存取操作都通过该Key来进行。也就是说，如果您并不能像MySQL的Query语句一样通过某一个（或者多个）关键字条件来读取包含多条数据的结果集，仅适用于通过某个唯一键来获取单条数据的数据读取方式。
利用Search实现高效全文检索
不论是使用Memcached还是使用BerkeleyDB，大多数时候都只能通过特定的方式来进行数据的检索，只能满足少部分的检索需求。而数据库本身对于全模糊LIKE操作的性能大家应该也很清楚，是非常低下的，因为这种操作无法利用索引。虽然MySQL的MyISAM存储引擎支持了全文索引，而且官方版本还不支持多字节字符集的数据，所以对于需要存放中文或者需要使用MyISAM之外的存储引擎的用户来说，是完全无法使用的。
对于这种情况，我们只有一个办法可以解决，那就是通过全文索引软件，也就是我们常说的Search（搜索引擎）对数据进行全文索引，才能达到较为高效的数据检索效率。
同样，Search软件的使用也有使用较为成熟的第三方解决方案与自行研发两种方式。目前最为有名的第三方解决方案主要就是基于Java实现的Lucene，隶属于Apache软件基金Jakarta项目组下面的一个子项目。当然，他并不是一个完整的搜索引擎工具，而是一个全文检索引擎的框架，他同时提供了完整的用于检索的查询引擎和数据索引引擎。
加入了Search软件来实现高效的全文检索功能之后，我们的架构可以通过如下这张图来展示：
利用分布式并行计算实现大数据量的高性能运算
说到大规模大数据量的高性能运算的时候，可能很多人都会想到最近风靡整个IT界的一个关键词：云计算，亦或是几年前的“网格计算”。
曾经有朋友建议我将本节标题中的“分布式并行计算”更改为“云计算”，考虑再三之后还是没有更改。说实话，从我个人的理解，不论是“云计算”还是“网格计算”，其实其实质都是一样，都是“分布式并行计算”，只不过是各个商业公司为了吸引大家的眼球所玩的一些“概念游戏”而已，个人认为纯属商业行为。当然，可能有人会认为从“分布式并行计算”到“网格计算”再到“云计算”，每一次“升级”都是在可以利用的计算资源上面有所扩展。可这种扩展都是在概念上面的扩展，而真正技术实现方面所依靠的并不是这些概念，而是各种软硬件的发展。更何况，最初的“分布式并行计算”概念中本就没有限定我们只能以哪种方式使用哪些资源。
目前比较流行的分布式并行计算框架主要就是以Google的MapReduce和Yahoo的Hadoop二者。其实更为准确的说应该是Google的MapReduce+GFS+BigTable以及Yahoo的Hadoop+HDFS+HBase这两大架构体系。二者都是由三个负责不同功能的组件组成，MapReduce与Hadoop同为解决认任务分解与合并的功能，GFS与HDFS都是分布式文件系统，解决数据存储的基础设施问题，最后BigTable与HBase则同为处理结构化数据存储格式的类数据库模块。三大模块共同协作，最终组成一个分布式并行计算的框架体系整体。
其实这分属于两家互联网巨头的分布式并行计算架构框架体系的实现原理基本上可以说是完全一样的。通过前面端的任务分解合并引擎将计算（或者数据存取）任务分解成多个任务，同时发送给多台计算（或数据）节点来进行计算，而后面的每一个节点利用分布式文件系统来作为存储计算数据的基础平台，当然，不论是计算前还是计算后的数据，都是通过BigTable或者是Hbase这样的模块进行组织。三者组成一个完整的整体，相互依赖。当然，不得不说的是Hadoop本身也是由Google最初的一篇关于MapReduce的论文原理为思想所开发出来的。只不过Google在Open思想方面所做的贡献很多时候仅限于论文形式，对于其自身技术架构方面的信息公开的实在是有些少。
其实除了这两个重量级的分布式计算框架之外，完全利用现有开源数据库实现的完整解决方案也有一些，如Inforbright与MySQL合作实现的BI解决方案，Greenplum公司与Sun利用PostGresql开源数据库实现的Greenplum系统，而且两个系统都是依赖MapReduce理论所实现。
小结
数据库只是存储数据的一种工具，其特殊性只是能将数据持久化，且提供统一规范的访问接口而已。除了数据库，其实我们还可以很多其他的数据存储处理方式，结合各种数据存储处理方式，充分发挥各自的特性，扬长避短，形成一个综合的数据中心，这样才能让系统的数据处理系统的扩展性得到最大的提升，性能得到最优化。
|
本帖子中包含更多资源
您需要 登录 才可以下载或查看，没有账号？立即注册
×
|
窥视卡
雷达卡
发表于 2022-1-24 12:27
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
千斤顶
显身卡