个人网站建设价格表,网易企业邮箱怎么设置,wordpress 用svn checkout,php建设网站工具MYSQL应该是最流行的WEB后端数据库。大量应用于PHP#xff0c;Ruby#xff0c;Python#xff0c;Java 等Web语言开发项目中#xff0c;无论NOSQL发展多么快#xff0c;都不影响大部分架构师选择MYSQL作为数据存储。MYSQL如此方便和稳定#xff0c;以至于我们在开发 WEB 程…MYSQL应该是最流行的WEB后端数据库。大量应用于PHPRubyPythonJava 等Web语言开发项目中无论NOSQL发展多么快都不影响大部分架构师选择MYSQL作为数据存储。 MYSQL如此方便和稳定以至于我们在开发 WEB 程序的时候非常少想到它。即使想到优化也是程序级别的比方不要写过于消耗资源的SQL语句。可是除此之外在整个系统上仍然有非常多能够优化的地方。1 优化原理 说起MySQL的查询优化相信大家会想到不能使用SELECT *、不使用NULL字段、合理创建索引、为字段选择合适的数据类型..... 你是否真的理解这些优化技巧是否理解其背后的工作原理在实际场景下性能真有提升吗我想未必。因而理解这些优化建议背后的原理就尤为重要希望本文能让你重新审视这些优化建议并在实际业务场景下合理的运用。 MySQL逻辑架构 如果能在头脑中构建一幅MySQL各组件之间如何协同工作的架构图有助于深入理解MySQL服务器。下图展示了MySQL的逻辑架构图。 MySQL逻辑架构 MySQL逻辑架构整体分为三层最上层为客户端层并非MySQL所独有诸如连接处理、授权认证、安全等功能均在这一层处理。 MySQL大多数核心服务均在中间这一层包括查询解析、分析、优化、缓存、内置函数(比如时间、数学、加密等函数)。所有的跨存储引擎的功能也在这一层实现存储过程、触发器、视图等。 最下层为存储引擎其负责MySQL中的数据存储和提取。和Linux下的文件系统类似每种存储引擎都有其优势和劣势。中间的服务层通过API与存储引擎通信这些API接口屏蔽了不同存储引擎间的差异。 MySQL查询过程 我们总是希望MySQL能够获得更高的查询性能最好的办法是弄清楚MySQL是如何优化和执行查询的。一旦理解了这一点就会发现很多的查询优化工作实际上就是遵循一些原则让MySQL的优化器能够按照预想的合理方式运行而已。 当向MySQL发送一个请求的时候MySQL到底做了些什么呢MySQL查询过程 客户端/服务端通信协议 MySQL客户端/服务端通信协议是“半双工”的在任一时刻要么是服务器向客户端发送数据要么是客户端向服务器发送数据这两个动作不能同时发生。一旦一端开始发送消息另一端要接收完整个消息才能响应它所以我们无法也无须将一个消息切成小块独立发送也没有办法进行流量控制。 客户端用一个单独的数据包将查询请求发送给服务器所以当查询语句很长的时候需要设置max_allowed_packet参数。但是需要注意的是如果查询实在是太大服务端会拒绝接收更多数据并抛出异常。 与之相反的是服务器响应给用户的数据通常会很多由多个数据包组成。但是当服务器响应客户端请求时客户端必须完整的接收整个返回结果而不能简单的只取前面几条结果然后让服务器停止发送。因而在实际开发中尽量保持查询简单且只返回必需的数据减小通信间数据包的大小和数量是一个非常好的习惯这也是查询中尽量避免使用SELECT *以及加上LIMIT限制的原因之一。Linuxc/c服务器开发高阶视频学习资料qun720209036获取内容包括C/CLinuxNginxZeroMQMySQLRedisMongoDBZK流媒体P2PK8SDockerTCP/IPLinux内核协程DPDK多个高级知识点。 查询缓存 在解析一个查询语句前如果查询缓存是打开的那么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的查询才会走缓存其他查询则不会这样可以非常自由地控制哪些查询需要被缓存。 当然查询缓存系统本身是非常复杂的这里讨论的也只是很小的一部分其他更深入的话题比如缓存是如何使用内存的如何控制内存的碎片化事务对查询缓存有何影响等等读者可以自行阅读相关资料这里权当抛砖引玉吧。 语法解析和预处理 MySQL通过关键字将SQL语句进行解析并生成一颗对应的解析树。这个过程解析器主要通过语法规则来验证和解析。比如SQL中是否使用了错误的关键字或者关键字的顺序是否正确等等。预处理则会根据MySQL规则进一步检查解析树是否合法。比如检查要查询的数据表和数据列是否存在等等。 查询优化 经过前面的步骤生成的语法树被认为是合法的了并且由优化器将其转化成查询计划。多数情况下一条查询可以有很多种执行方式最后都返回相应的结果。优化器的作用就是找到这其中最好的执行计划。 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()函数找某列的最小值如果该列有索引只需要查找BTree索引最左端反之则可以找到最大值具体原理见下文 3.提前终止查询比如使用Limit时查找到满足数量的结果集后会立即终止查询 4.优化排序在老版本MySQL会使用两次传输排序即先读取行指针和需要排序的字段在内存中对其排序然后再根据排序结果去读取数据行而新版本采用的是单次传输排序也就是一次读取所有的数据行然后根据给定的列排序。对于I/O密集型应用效率会高很多 随着MySQL的不断发展优化器使用的优化策略也在不断的进化这里仅仅介绍几个非常常用且容易理解的优化策略其他的优化策略大家自行查阅吧。 查询执行引擎 在完成解析和优化阶段以后MySQL会生成对应的执行计划查询执行引擎根据执行计划给出的指令逐步执行得出结果。整个执行过程的大部分操作均是通过调用存储引擎实现的接口来完成这些接口被称为handler API。查询过程中的每一张表由一个handler实例表示。实际上MySQL在查询优化阶段就为每一张表创建了一个handler实例优化器可以根据这些实例的接口来获取表的相关信息包括表的所有列名、索引统计信息等。存储引擎接口提供了非常丰富的功能但其底层仅有几十个接口这些接口像搭积木一样完成了一次查询的大部分操作。 返回结果给客户端 查询执行的最后一个阶段就是将结果返回给客户端。即使查询不到数据MySQL仍然会返回这个查询的相关信息比如改查询影响到的行数以及执行时间等等。 如果查询缓存被打开且这个查询可以被缓存MySQL也会将结果存放到缓存中。 结果集返回客户端是一个增量且逐步返回的过程。有可能MySQL在生成第一条结果时就开始向客户端逐步返回结果集了。 这样服务端就无须存储太多结果而消耗过多内存也可以让客户端第一时间获得返回结果。需要注意的是结果集中的每一行都会以一个满足①中所描述的通信协议的数据包发送再通过TCP协议进行传输在传输过程中可能对MySQL的数据包进行缓存然后批量发送。 回头总结一下MySQL整个查询执行过程总的来说分为6个步骤 1.客户端向MySQL服务器发送一条查询请求 2.服务器首先检查查询缓存如果命中缓存则立刻返回存储在缓存中的结果。否则进入下一阶段 3.服务器进行SQL解析、预处理、再由优化器生成对应的执行计划 4.MySQL根据执行计划调用存储引擎的API来执行查询 5.将结果返回给客户端同时缓存查询结果2 事务及引擎 MySQL的存储引擎可能是所有关系型数据库产品中最具有特色的了不仅可以同时使用多种存储引擎而且每种存储引擎和MySQL之间使用插件方式这种非常松的耦合关系。 由于各存储引擎功能特性差异较大需要关注如何来选择合适的存储引擎来应对不同的业务场景。一、MyISAMo 特性 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都需要进行实际的数据访问二、InnoDBo 特性 1.具有较好的事务支持支持4个事务隔离级别支持多版本读 2.行级锁定通过索引实现全表扫描仍然会是表锁注意间隙锁的影响 3.读写阻塞与事务隔离级别相关 4.具有非常高效的缓存特性能缓存索引也能缓存数据 5.整个表和主键以Cluster方式存储组成一颗平衡树 6.所有Secondary Index都会保存主键信息o 适用场景 1.需要事务支持具有较好的事务特性 2.行级锁定对高并发有很好的适应能力但需要确保查询是通过索引完成 3.数据更新较为频繁的场景 4.数据一致性要求较高 5.硬件设备内存较大可以利用InnoDB较好的缓存能力来提高内存利用率尽可能减少磁盘 IOo 最佳实践 1.主键尽可能小避免给Secondary index带来过大的空间负担 2.避免全表扫描因为会使用表锁 3.尽可能缓存所有的索引和数据提高响应速度 4.在大批量小插入的时候尽量自己控制事务而不要使用autocommit自动提交 5.合理设置innodb_flush_log_at_trx_commit参数值不要过度追求安全性 6.避免主键更新因为这会带来大量的数据移动三、NDBClustero 特性 1.分布式分布式存储引擎可以由多个NDBCluster存储引擎组成集群分别存放整体数据的一部分 2.支持事务和Innodb一样支持事务 3.可与mysqld不在一台主机可以和mysqld分开存在于独立的主机上然后通过网络和mysqld通信交互 4.内存需求量巨大新版本索引以及被索引的数据必须存放在内存中老版本所有数据和索引必须存在与内存中o 适用场景 1.具有非常高的并发需求 2.对单个请求的响应并不是非常的critical 3.查询简单过滤条件较为固定每次请求数据量较少又不希望自己进行水平Shardingo 最佳实践 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_hitsQcache_inserts)*100)进行调整一般不建议太大256MB可能已经差不多了大型的配置型静态数据可适当调大 · binlog_cache_size: 一般环境2MB4MB是一个合适的选择事务较大且写入频繁的数据库环境可以适当调大但不建议超过32MB · key_buffer_size: 如果不使用MyISAM存储引擎16MB足以用来缓存一些系统表信息等。如果使用 MyISAM存储引擎在内存允许的情况下尽可能将所有索引放入内存简单来说就是“越大越好” · bulk_insert_buffer_size: 如果经常性的需要使用批量插入的特殊语句上面有说明来插入数据可以适当调大该参数至16MB32MB不建议继续增大某人8MB · innodb_buffer_pool_size: 如果不使用InnoDB存储引擎可以不用调整这个参数如果需要使用在内存允许的情况下尽可能将所有的InnoDB数据文件存放如内存中同样将但来说也是“越大越好” · innodb_additional_mem_pool_size: 一般的数据库建议调整到8MB16MB如果表特别多可以调整到32MB可以根据error log中的信息判断是否需要增大 · innodb_log_buffer_size: 默认是1MB系的如频繁的系统可适当增大至4MB8MB。当然如上面介绍所说这个参数实际上还和另外的flush参数相关。一般来说不建议超过32MB · innodb_max_dirty_pages_pct: 根据以往的经验重启恢复的数据如果要超过1G的话启动速度会比较慢几乎难以接受所以建议不大于1GB/innodb_buffer_pool_size(GB)*100这个值。当然如果你能够忍受启动时间比较长而且希望尽量减少内存至磁盘的flush可以将这个值调整到90但不建议超过90。 注以上取值范围仅仅只是根据以往遇到的数据库场景所得到的一些优化经验值并不一定适用于所有场景所以在实际优化过程中还需要大家自己不断的调整分析。4 SQL优化4.1 优化目标1、减少 IO 次数 IO永远是数据库最容易瓶颈的地方这是由数据库的职责所决定的大部分数据库操作中超过90%的时间都是 IO 操作所占用的减少 IO 次数是 SQL 优化中需要第一优先考虑当然也是收效最明显的优化手段。2、降低 CPU 计算 除了 IO 瓶颈之外SQL优化中需要考虑的就是 CPU 运算量的优化了。order by,group by,distinct … 都是消耗 CPU 的大户这些操作基本上都是 CPU 处理内存中的数据比较运算。当我们的 IO 优化做到一定阶段之后降低 CPU计算也就成为了我们 SQL 优化的重要目标。4.2 优化方法一、监控分析1、硬件资源监控 关注的主要数据库服务器在IO和CPU方面的指标。2、mysql性能分析器 可以利用mysql profilingmysql性能分析器来优化sql语句即查看SQL执行消耗系统资源的信息需要开启才能应用该功能。3、慢查询分析 通过慢日志查询可以知道哪些SQL语句执行效率低下那些sql语句使用的频率高等。 对MySQL查询语句的监控、分析、优化是MySQL优化非常重要的一步。开启慢查询日志后由于日志记录操作在一定程度上会占用CPU资源影响mysql的性能但是可以阶段性开启来定位性能瓶颈。二、改变 SQL 执行计划 明确了优化目标之后我们需要确定达到我们目标的方法。对于 SQL 语句来说达到上述2个优化目标的方法其实只有一个那就是改变 SQL 的执行计划让他尽量“少走弯路”尽量通过各种“捷径”来找到我们需要的数据以达到“减少IO次数”和“降低 CPU 计算”的目标。 使用explain命令查看query语句的性能 EXPLAIN select * from tablename;##查看执行计划中的sql性能 上面这是最简单的执行计划实例来分析一下上面的这几个字段。1、idid主要是用来标识sql执行顺序如果没有子查询一般来说id只有1个执行顺序也是从上到下。2、select_type每个select子句的类型主要分成下面几种 a:SIMPLE:查询中不包含任何子查询或者union b:PRIMARY:查询中包含了任何复杂的子部分最外层的就会变成PRIMARY c:SUBQUERY:在SELECT或者WHERE列表中包含了子查询 d:DERIVED在FROM中包含了子查询 e:UNION:如果第二个SELECT出现在UNION之后则被标记为UNION如果UNION包含在FROM子句的子查询中外层SELECT会被标记为DERIVED fUNION RESULT从UNION表获取结果的select 3、type是指MySQL在表中找到所需行的方式也就是访问行的“类型”从a开始效率逐渐上升 aall全表扫描效率最低 bindex:index会根据索引树遍历 crange:索引范围扫描返回匹配值域的行。 dref:非唯一性索引扫描返回匹配某个单独值的所有行。一般是指多列的唯一索引中的某一列。 eeq_ref:唯一性索引扫描表中只有一条记录与之匹配。 fconst、system主要针对查询中有常量的情况如果结果只有一行会变成system gNULL:显而易见既不走表也不走索引4、possible_keys possible_keys列预估了mysql能够为当前查询选择的索引这个字段是完全独立于执行计划中输出的表的顺序意味着在实际查询中可能用不到这些索引。 如果该字段为空则意味着没有可使用的索引这个时候你可以考虑为where后面的字段建立索引。5、key 这个字段表示了mysql真实使用的索引如果为NULL则没有使用索引。如果mysql优化过程中没有加索引可以强制加hint使用索引。6、key_len 索引长度字段顾名思义表示了mysql查询中使用的索引的长度最大可能长度并非实际使用长度理论上长度越短越好。key_len是根据表定义计算而得的不是通过表内检索出的。7、ref 这个字段一般是指一些常量用于选择过滤显示索引的那一列被使用了如果可能是一个常量const。8、rows 预估结果集的条数可能不一定完全准确根据表统计信息及索引选用情况大致估算出找到所需的记录所需要读取的行数。9、Extra 不适合在其他字段中显示但是十分重要的额外信息 aUsing filesortmysql对数据使用一个外部的索引排序而不是按照表内的索引进行排序读取。也就是说mysql无法利用索引完成的排序操作成为“文件排序”。 bUsing temporary使用临时表保存中间结果也就是说mysql在对查询结果排序时使用了临时表常见于order by 和 group by。 cUsing index表示相应的select操作中使用了覆盖索引Covering Index避免了访问表的数据行效率高不要使用select *如果同时出现Using where表明索引被用来执行索引键值的查找如果没有同时出现Using where表明索引用来读取数据而非执行查找动作。 dUsing join buffer使用了链接缓存。 eeq_ref:唯一性索引扫描表中只有一条记录与之匹配。 fImpossible WHEREwhere子句的值总是false不能用来获取任何元祖。 gselect tables optimized away在没有group by子句的情况下基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT*操作不必等到执行阶段在进行计算查询执行计划生成的阶段即可完成优化。 Hdistinct优化distinct操作在找到第一个匹配的元祖后即停止找同样值得动作。4.3 常见误区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为单位一般为4KB8KB…大多数时候每个IO单元中存储了多行每行都是存储了该行的所有字段lob等特殊类型字段除外。 所以我们是取一个字段还是多个字段实际上数据库在表中需要访问的数据量其实是一样的。 当然也有例外情况那就是我们的这个查询在索引中就可以完成也就是说当只取 a,b两个字段的时候不需要回表而c这个字段不在使用的索引中需要回表取得其数据。在这样的情况下二者的IO量会有较大差异。4、order by一定需要排序操作 我们知道索引数据实际上是有序的如果我们的需要的数据和某个索引的顺序一致而且我们的查询又通过这个索引来执行那么数据库一般会省略排序操作而直接将数据返回因为数据库知道数据已经满足我们的排序需求了。 实际上利用索引来优化有排序需求的 SQL是一个非常重要的优化手段5、执行计划中有 filesort 就会进行磁盘文件排序 有这个误区其实并不能怪我们而是因为 MySQL 开发者在用词方面的问题。filesort是我们在使用 explain 命令查看一条 SQL 的执行计划的时候可能会看到在“Extra”一列显示的信息。 实际上只要一条 SQL 语句需要进行排序操作都会显示“Using filesort”这并不表示就会有文件排序操作。4.4 基本原则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 73699、能用DISTINCT的就不用GROUP BY group by 操作特别慢比如 SELECT OrderID FROM DetailsWHERE UnitPrice 10 GROUP BY OrderID 可改为 SELECT DISTINCT OrderID FROMDetails WHERE UnitPrice 1010、尽量不要用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 的时候是以 pageblock的方式也就是说如果我们每条记录所占用的空间量减小就会使每个page中可存放的数据行数增大那么每次 IO 可访问的行数也就增多了。反过来说处理相同行数的数据需要访问的 page 就会减少也就是 IO 操作次数降低直接提升性能。此外由于我们的内存是有限的增加每个page中存放的数据行数就等于增加每个内存块的缓存数据量同时还会提升内存交换中数据命中的几率也就是缓存命中率。一、数据类型选择 数据库操作中最为耗时的操作就是 IO 处理大部分数据库操作 90%以上的时间都花在了 IO 读写上面。所以尽可能减少 IO 读写量可以在很大程度上提高数据库操作的性能。我们无法改变数据库中需要存储的数据但是我们可以在这些数据的存储方式方面花一些心思。原则是数据行的长度不要超过8020字节如果超过这个长度的话在物理页中这条数据会占用两行从而造成存储碎片降低查询效率字段的长度在最大限度的满足可能的需要的前提下应该尽可能的设得短一些这样可以提高查询的效率而且在建立索引的时候也可以减少资源的消耗。 下面的这些关于字段类型的优化建议主要适用于记录条数较多数据量较大的场景因为精细化的数据类型设置可能带来维护成本的提高过度优化也可能会带来其他的问题 1、数字类型非万不得已不要使用DOUBLE不仅仅只是存储长度的问题同时还会存在精确性的问题。同样固定精度的小数也不建议使用DECIMAL建议乘以固定倍数转换成整数存储可以大大节省存储空间且不会带来任何附加维护成本。对于整数的存储在数据量较大的情况下建议区分开 TINYINT / INT / BIGINT 的选择因为三者所占用的存储空间也有很大的差别能确定不会使用负数的字段建议添加unsigned定义。当然如果数据量较小的数据库也可以不用严格区分三个整数类型。 能够用数字类型的字段尽量选择数字类型而不用字符串类型的电话号码这会降低查询和连接的性能并会增加存储开销。这是因为引擎在处理查询和连接会逐个比较字符串中每一个字符而对于数字型而言只需要比较一次就够了。 2、字符类型非万不得已不要使用 TEXT 数据类型其处理方式决定了他的性能要低于char或者是varchar类型的处理。定长字段建议使用 CHAR 类型char查询快但是耗存储空间可用于用户名、密码等长度变化不大的字段不定长字段尽量使用 VARCHARvarchar查询相对慢一些但是节省存储空间可用于评论等长度变化大的字段且仅仅设定适当的最大长度而不是非常随意的给一个很大的最大长度限定因为不同的长度范围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、最后总结一下法则不要在建立的索引的数据列上进行下列操作 ◆避免对索引字段进行计算操作 ◆避免在索引字段上使用notlike ‘%L’!inor连接 ◆避免在索引列上使用IS NULL和IS NOT NULL ◆避免在索引列上出现数据类型转换 ◆避免在索引字段上使用函数 ◆避免建立索引的列中使用空值。7 架构优化一、分布式和集群化1、负载均衡 负载均衡集群是由一组相互独立的计算机系统构成通过常规网络或专用网络进行连接由路由器衔接在一起各节点相互协作、共同负载、均衡压力对客户端来说整个群集可以视为一台具有超高性能的独立服务器。MySQL一般部署的是高可用性负载均衡集群具备读写分离一般只对读进行负载均衡。2、读写分离 读写分离简单的说是把对数据库读和写的操作分开对应不同的数据库服务器这样能有效地减轻数据库压力也能减轻io压力。主数据库提供写操作从数据库提供读操作其实在很多系统中主要是读的操作。当主数据库进行写操作时数据要同步到从的数据库这样才能有效保证数据库完整性。3、数据切分 通过某种特定的条件将存放在同一个数据库中的数据分散存放到多个数据库上实现分布存储通过路由规则路由访问特定的数据库这样一来每次访问面对的就不是单台服务器了而是N台服务器这样就可以降低单台机器的负载压力。数据切分一般包括垂直切分和水平切分。 数据的垂直切分也可以称之为纵向切分。将数据库想象成为由很多个一大块一大块的“数据块”表组成我们垂直的将这些“数据块”切开然后将他们分散到多台数据库主机上面。这样的切分方法就是一个垂直纵向的数据切分。 数据的垂直切分基本上可以简单的理解为按照表、按照模块来切分数据而水平切分就不再是按照表或者是功能模块来切分了。一般来说简单的水平切分主要是将某个访问极其平凡的表再按照某个字段的某种规则来分散到多个表之中每个表中包含一部分数据。二、Cache与Search的利用 通过引入CacheRedis、Memcached减少数据库的访问降低磁盘的IO增加性能。 通过引入SearchLucene、Solr、ElasticSearch利用搜索引擎高效的全文索引和分词算法以及高效的数据检索实现来解决数据库和传统的Cache软件完全无法解决的全文模糊搜索、分类统计查询等功能。
