mysql数据库优化的几种方法,mysql数据库优化及sql调优
http://blog..net/samjustin1/article/details/52212421
一、MySQL的主要应用场景
1.网站系统
2.测井系统
3.数据仓库系统
4.嵌入式系统
二、MySQL架构图：
三。MySQL存储引擎概述
1)MyISAM存储引擎
MyISAM存储引擎的表位于数据库中，每个表存储为三个以表名命名的物理文件。首先，必须有一个。frm文件，它是任何存储引擎存储表结构定义信息所不可缺少的。另外，还有。MYD和。存储表格数据的MYI文件。MYD)和索引数据(。MYI)分别。每个表只有三个文件作为MyISAM存储类型表的存储，也就是说，不管这个表有多少个索引，都存储在同一个。MYI档案。
MyISAM支持以下三种类型的索引：
1.b树索引
-B树索引，顾名思义，就是所有的索引节点都按照balancetree的数据结构存储，所有的索引数据节点都在叶子节点中。
2.r树索引
R树索引和b树索引的存储方式有一些不同，b树索引主要是为了索引存储空间和多维数据的字段而设计的。因此，当前的MySQL版本只支持对几何类型的字段进行索引。
3.全文索引
全文索引就是我们讲了很久的全文索引，它的存储结构也是b树。主要是解决我们需要查询like时效率低下的问题。
2)Innodb存储引擎
1.支持事务安装
2.数据的多版本读取
3.锁紧机构的改进。
4.实现外键。
3)ndb集群存储引擎
NDB存储引擎，也称为NDBCluster存储引擎，主要用于MySQLCluster分布式集群环境。集群是MySQL从5.0版本开始提供的新功能。
4)合并存储引擎
合并存储引擎，在MySQL用户手册中也有提及，也被称为MRG_MyISAM引擎。为什么？因为合并存储引擎可以简单的理解为，它的功能是实现相同结构的MyISAM表，并通过一些特殊的包提供对外界的单一访问，从而达到降低应用程序复杂度的目的。要创建合并表，不仅基表的结构(包括字段的顺序)必须相同，而且基表的索引也必须相同。
5)内存存储引擎
内存引擎，顾名思义，是将数据存储在内存中的存储引擎。内存引擎不会在磁盘上存储任何数据，而只会存储一个. frm文件，该文件存储有关磁盘上的表结构的信息。所以一旦MySQL崩溃或主机崩溃，内存表中就只剩下一个结构了。内存表支持索引，同时支持Hash和B树索引。因为它存储在内存中，所以内存将数据存储在固定长度的空间中，不支持BLOB和文本字段。内存存储引擎实现页面级锁定。
6)BDB存储引擎
BDB存储引擎被称为BerkeleyDB存储引擎。和Innodb一样，它不是MySQL自己开发实现的存储引擎，而是由SleepycatSoftware提供的。当然，它也是一个开源的存储引擎，同样支持事务安全。
7)联合存储引擎
联邦存储引擎的功能基本类似于Oracle的DBLINK，主要用于提供对远程MySQL服务器上数据的访问接口。如果我们使用源代码编译来安装MySQL，就必须手动指定启用联邦存储引擎，因为MySQL默认不使用这个存储引擎。
8)归档存储引擎
归档引擎主要用于通过较小的存储空间来存储访问很少的过期历史数据。存档表不支持索引。它是由的结构文件定义的。frm，一个数据压缩文件。ARZ和一个元信息文件。手臂。由于其中存储的数据的特殊性，存档表不支持删除或修改。
仅支持插入和查询操作。锁定机制是行级锁定。
9)黑洞存储引擎
黑洞存储引擎是一个非常有趣的存储引擎，它的功能就像它的名字一样，就是一个“黑洞”。就像我们unix系统下的“/dev/null”设备一样，无论我们写什么信息，都没有回报。
10)CSV存储引擎
存储引擎实际上操作的是标准CSV文件，它不支持索引。主要用途是有时候你可能需要将数据库中的数据导出到报表文件中，而CSV文件是很多软件都支持的标准格式，所以我们可以先在数据库中建立一个CVS表，然后将生成的报表信息插入到表中，得到一个CSV报表文件。
四。影响MySQLServer性能的相关因素
1业务需求对性能的影响
典型要求：统计一个论坛的总帖子数，要求：实时更新。
2系统架构和实现对性能的影响
以下类型的数据不适合存储在数据库中：
二进制多媒体数据
管道队列数据
超大文本数据
通过缓存技术提高系统性能；
系统的各种配置和规则数据；
活跃用户的基本信息数据；
活跃用户的个性化定制信息数据；
准实时统计信息数据；
其他经常访问的数据变化较小；
查询语句对系统性能的影响
要求：取出某个群的用户号(id)和昵称(nick_name)(假设id为1)，按照加入群的时间倒序排列(user_group.gmt_create)，取出前20个。
解决方案1:
SELECT id，nick_name FROM user，user_group其中user_group.group_id=1，user _ group . user _ id=user . id ORDER BY user _ group . GMT _ create desc限制100，20；
解决方案2:
SELECT user.id，user.nick_name FROM(
SELECT user_id FROM user_group其中user _ group . group _ id=1 ORDER BY GMT _ create desc限制100，20)t，user其中t . user _ id=user . id；
通过对比两种方案的执行计划，我们可以看到，第一种方案中MySQL需要与user表连接的记录数，统计数据估计为31156条，即user_group表返回的group_id=1的所有记录数(系统中实际数据为20000条)。但是，在第二个解决方案的执行计划中，参与连接的用户表中只有20条数据，彼此相差很大。我们认为第二种方案应该明显优于第一种方案。
4模式设计对系统性能的影响
最小化数据库访问请求。
尽量减少无用数据的查询请求。
5硬件环境对系统性能的影响
1.典型的OLTP应用系统
各种数据库系统中最常见的OLTP系统，其特点是并发量大，整体数据量大。但是每次访问的数据都比较少，而且访问的数据比较分散，活跃数据在总数据中所占的比例不算太大。对于这类系统，数据库其实是最难维护和优化的，对主机的整体性能要求也是最高的。因为不仅访问量高，而且数据量也不小。
根据以上特点和分析，我们可以得出一个OLTP的大致方向。
虽然系统的总数据量很大，但是系统的活跃数据在总数据量中所占的比例很小，所以我们可以通过扩展内存容量的方式将尽可能多的活跃数据缓存到内存中。
虽然IO访问很频繁，但是每次访问的数据量很小且离散，那么我们对磁盘存储的要求就是IOPS性能要好，吞吐量是次要因素；
并发性很高，那么自然，CPU每秒要处理的请求也很多，所以需要CPU的处理能力比较强；
虽然每次和客户端交互的数据量不是特别大，但是网络交互非常频繁，所以主机和客户端之间的网络设备在流量能力上不能太弱。
2.典型的OLAP应用系统
用于数据分析的OLAP系统的主要特点是数据量非常大，并发访问不多。而每次访问都需要检索大量数据，数据访问相对集中，所以没有明显的主动数据概念。
根据OLAP系统的各种特点和相应的分析，OLAP系统硬件优化的一般策略如下：
数据量非常大，所以磁盘存储系统的单位容量需要尽可能大；
单次访问数据量大，访问数据集中，IO系统的性能要求是每秒有尽可能多的IO吞吐量，所以要选择每秒吞吐量尽可能多的磁盘；
虽然IO性能要求也比较高，但是并发请求比较少，所以CPU处理能力很难成为性能瓶颈，所以对CPU处理能力要求不高；
虽然每个请求的访问次数多，但是执行过程中的大部分数据不会返回给客户端，最终返回给客户端的数据量小，所以对与客户端交互的网络设备的要求不会太高；
此外，由于OLAP系统的运算过程较长，可以很好地并行化，一般的OLAP系统都是由多台主机组成的集群，集群内主机之间的数据交互量一般都很大，所以对集群内主机之间的网络设备要求很高。
3.除了上述两种典型应用，还有一种特殊的应用系统。它们的数据量不是特别大，但是访问请求极其频繁，而且大部分都是读请求。可能需要每秒提供几万甚至几万个请求。每个请求都很简单，大部分可能只返回一个或几个相对较小的记录。例如，基于数据库的DNS服务就是这样一种服务。
虽然数据量很小，但是访问非常频繁，所以大部分数据可以被大内存缓存，这样可以保证非常高的命中率。磁盘的IO比例小，所以磁盘不需要特别高性能。
并发请求非常频繁，需要更多的CPU处理能力来处理；
虽然应用和数据库之间的交互量很大，每次交互的数据也比较少，整体的流量会比较大，但是一般来说，普通的千兆网卡就够用了。
五、MySQL锁机制介绍
行锁定(行级)
表级锁定
页面锁(页面级)
在MySQL数据库中，一些非事务存储引擎如MyISAM、Memory、CSV等。主要用于表级锁定，Innodb存储引擎和NDBCluster存储引擎主要用于行级锁定，页面级锁定主要用于BerkeleyDB存储引擎。
不及物动词MySQL查询的优化
查询语句的优化思想和原则主要体现在以下几个方面：
1.优化查询；需要进一步优化；
2.定位优化对象的性能瓶颈；
3.明确优化目标；
4.从解释开始；
5.更频繁地使用个人资料
6.总是用小结果集驱动大结果集；
7.尽可能在索引中完成排序；
8.只取出列；你需要；
9.仅使用最有效的过滤条件；
10.尽可能避免复杂的连接和子查询；
索引的合理设计和利用
1)B树索引
一般来说，MySQL中B-Tree索引的物理文件多以BalanceTree的结构存储，即实际需要的所有数据都存储在树的LeafNode中，到任意LeafNode的最短路径长度相同，所以我们都称之为B-Tree索引。当然，各种数据库(或者MySQL的各种存储引擎)在存储自己的B树索引时，可能会稍微修改一下存储结构。比如Innodb存储引擎的B树索引实际使用的存储结构其实就是B树，也就是对B树的数据结构做了微小的修改。除了存储索引键的相关信息，每个叶节点还存储指向与该叶节点相邻的下一个叶节点的指针信息，主要是为了加快搜索多个相邻叶节点的效率。
2)散列索引
Hash索引在MySQL中的应用并不广泛。目前主要由内存存储引擎使用，哈希索引是内存存储引擎中默认的索引类型。所谓哈希索引，其实就是通过一定的哈希算法对要索引的键值进行哈希处理，然后将得到的哈希值存储在一个哈希表中。然后每次需要搜索的时候，会用同样的算法对搜索条件进行哈希，然后和哈希表中的哈希值进行比较，得到相应的信息。
哈希索引只能满足“=”、“in”和“=”查询，不能使用范围查询；
哈希不能用来避免对数据进行排序；
不能使用部分索引键查询哈希索引；
哈希索引无法随时避免表扫描；
在大量哈希值相等的情况下，哈希索引并不一定比B树索引具有更高的性能；
3)全文索引
全文索引就是我们常说的全文索引。目前MySQL中只有MyISAM存储引擎支持全文索引，并不是所有的数据类型都支持全文索引。目前，只有具有CHAR、VARCHAR和TEXT三种数据类型的列可以进行全文索引。
索引可以大大提高数据检索的效率以及排序和分组操作的性能。但我们不能忽视的一个问题是，索引是完全独立于基础数据的一部分数据，会导致更新数据带来的IO量和调整索引带来的计算量的资源消耗。
是否有必要创建索引，几个原则：经常作为查询条件的字段要索引；唯一性差的字段不适合单独创建索引，即使经常作为查询条件；频繁更新的字段不适合创建索引；
不应索引未出现在WHERE子句中的字段；
连接语句的优化
最小化Join语句中NestedLoop的循环总数；“总是用小结果集驱动大结果集”。
优化优先级NestedLoop的内环；
请确保Join语句中被驱动表上的Join condition字段已被编入索引；
在不能保证被驱动表的Join condition字段有索引，内存资源充足的情况下，不要太吝啬JoinBuffer的设置；
排序依据、分组依据和不同优化
1)实施和优化1)订购方式
在查询语句中优化ORDER BY时，尽可能使用现有索引来避免实际的排序计算，可以大大提高ORDER BY操作的性能。
优化排序：
1.增加max_length_for_sort_data参数的设置；
2.删除不必要的返回字段；
3.增加sort_buffer_size的参数设置；
Groupby的实现和优化
其实GROUP BY也是需要排序操作的，而且与ORDER BY相比，GROUP BY只是排序后的分组操作更多。当然，如果在分组中使用了其他聚合函数，那么就需要计算一些聚合函数。因此，在GROUP BY的实现中，可以像ORDER BY一样使用索引。
3)实施和优化3)独特的
DISTINCT实际上与GROUP BY非常相似，只是在GROUP BY之后，每个组中只取出一条记录。所以DISTINCT的实现和GROUP BY基本相似，差别不大。也可以通过松散索引扫描或紧凑索引扫描来实现。当然，当仅仅使用索引无法实现DISTINCT时，MySQL只能通过临时表来实现。但是，与GROUP BY不同，DISTINCT不需要排序。也就是说，当仅是DISTINCT操作的查询无法仅使用索引完成操作时，MySQL会使用临时表将数据“缓存”一次，但不会对临时表中的数据执行filesort操作。
七。MySQL数据库模式设计的性能优化
高效的模型设计
适度冗余——让查询减少两个连接。
大型垂直字段拆分-汇总表优化
基于大表水平拆分类型的拆分优化
统计表-准实时优化
适当的数据类型
时间存储格式没有太多类型，我们主要用DATETIME，DATE，TIMESTAMP。从存储空间来说，TIMESTAMP是最少的，四个字节，其他两种数据类型都是八个字节，多了一倍。时间戳的缺点是只能存储1970年以后的时间，其他两类时间可以存储从1001年开始的最早时间。如果需要存储1970年以前的时间，就必须抛弃时间戳类型，但只要不需要使用1970年以前的时间，最好尽可能使用时间戳，以减少存储空间。
字符存储类型
CHAR[(M)]类型属于静态长度类型，存储长度完全由字符数计算，所以最终的存储长度是基于字符集的。例如，latin1的最大存储长度为255字节，但如果使用gbk，则最大存储长度为510字节。CHAR类型的存储特点是，无论我们实际存储多长时间的数据，都会在数据库中存储M个字符。如果不够，就用空格填充，M默认为1。虽然CHAR会按空格填充存储空间，但是MySQL在访问数据时会忽略所有最后的空格，所以如果我们实际数据的末尾真的需要空格，就不能用CHAR类型来存储。
VARCHAR[(M)]属于动态存储长度的类型，只占用实际存储数据的长度。TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT属于同一种存储方式，都是动态存储长度类型。区别只是最大长度限制。
交易优化
1.脏读：脏读是指当一个事务正在访问数据和修改数据，但是这个修改还没有提交到数据库。这时，另一个事务也访问这个数据，然后使用这个数据。
2.不可重复读取：指在一个事务中多次读取相同的数据。在这个事务完成之前，另一个事务也访问相同的数据。然后，在第一事务中的两次数据读取之间，由于第二事务的修改，第一事务两次读取的数据可能不同。这样一个事务中两次读取的数据是不一样的，所以叫不可重复。
3.魔读：是指交易没有独立执行时发生的现象。例如，第一个事务修改了表中的数据，这一修改涉及表中的所有数据行。同时，第二个事务也修改了这个表中的数据。这个修改是在表中插入一个新的数据行。然后以后操作第一笔交易的用户会发现表中仍然有未修改的数据行，好像产生了错觉。
Innodb在事务隔离级别支持的信息如下：
1.未提交读取
通常被称为脏读，可以说是事务中最低的隔离级别：我们看到的数据可能因为普通解锁模式下SELECT的执行而不是查询发起时间点的数据，所以在这个隔离级别上是一致读；
2.已提交读取
在此隔离级别，不会有DirtyRead，但可能有不可重复的Reads和PhantomReads。
3.可重复读
可重复读取隔离级别是InnoDB的默认事务隔离级别。在可重复读取隔离级别下，将不会有DirtyReads或不可重复读取，但仍然存在幻像读取的可能性。
4.可序列化
可序列化隔离级别是标准事务隔离级别中的最高级别。设置SERIALIZABLE隔离级别后，事务中任何时候看到的数据都是事务启动时的状态，不管在此期间是否有数据被其他事务修改和提交。因此，PhantomReads不会出现在可序列化事务隔离级别下。
八。可扩展设计的数据划分
数据的垂直分段
数据的垂直分段也可以称为垂直分段。把数据库想象成由许多“数据块”(表)一个接一个地组成。我们垂直切割这些“数据块”，然后将它们分布到多个数据库主机。这种分割方法是垂直(纵向)数据分割。
垂直细分的优势
数据库拆分简单明了，拆分规则清晰；
应用模块清晰，易于集成；
数据维护方便易行，易于定位；
垂直分割的缺点
有些表关联无法在数据库级完成，需要在程序中完成；
对于访问极其频繁、数据量巨大的表，性能依然从容，可能达不到要求；
交易处理相对更复杂；
分割到一定程度后，扩展性会受到限制；
过度阅读分段可能会使系统过渡复杂，难以维护。
数据的水平分段
数据的纵向分割基本上可以简单理解为按照表和模块对数据进行分割，而横向分割则不再基于表或功能模块。一般来说，简单的横向分段主要是将一个访问极其普通的表按照某个字段的一些规则分布到多个表中，每个表包含一些数据。
水平分段的优势
表关联基本可以在数据库端完成；
对于一些数据量非常大、负载很高的表，不会出现瓶颈问题；
应用端整体架构变化相对较小；
交易处理相对简单；
只要能很好的定义分段规则，基本很难满足扩展性的限制；
水平分段的缺点
分割规则相对更复杂，很难抽象出一个能满足整个数据库的分割规则；
后期数据维护难度更大，手动定位数据难度更大；
应用系统各模块耦合度较高，可能会对后续数据的迁移和拆分造成一定的困难。
数据分割和整合中可能出现的问题
1.引入分布式事务的问题
你可以把一个跨多个数据库的分布式事务完全拆分成只在单个数据库上的多个小事务，并通过应用程序控制每个小事务。当然这其中的要求是我们的俄语应用必须足够健壮，这当然会给应用带来一定的技术难度。
2.跨节点连接的问题
推荐应用程序进行处理，先从驱动表所在的MySQLServer中取出相应的驱动结果集，再根据驱动结果集从被驱动表所在的MySQL Server中取出相应的数据。
3.跨节点合并排序分页问题
从多个数据源并行获取数据，然后应用程序进行汇总和处理。
九。可扩展设计中缓存和搜索的利用
通过引入缓存(Redis，Memcached)，减少了数据库访问，提高了性能。
通过引入Search(Lucene，Solr，ElasticSearch)，利用搜索引擎高效的全文索引和分词算法，高效的数据检索，可以解决数据库和传统缓存软件根本解决不了的全文模糊搜索、分类统计查询等功能。
本文为《MySQL性能调优与架构设计》读书笔记！
郑重声明：本文由网友发布，不代表盛行IT的观点，版权归原作者所有，仅为传播更多信息之目的，如有侵权请联系，我们将第一时间修改或删除，多谢。