MySQL深入学习总结

见贤思齐

作者：yandeng，腾讯PCG应用开发工程师1.数据库基础1.1MySQL架构和其它数据库相比，MySQL有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎的架构上，插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎，各层介绍：1.1.1连接层最上层是一些客户端和连接服务，包含本地sock通信和大多数基于客户端/服务端工具实现的类似于tcp/ip的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。1.1.2服务层1.1.3引擎层存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取。1.1.4存储层数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互。1.2数据引擎不同的存储引擎都有各自的特点，以适应不同的需求，如表所示。为了做出选择，首先要考虑每一个存储引擎提供了哪些不同的功能。1.2.1MyISAM使用这个存储引擎，每个MyISAM在磁盘上存储成三个文件。frm文件：存储表的定义数据MYD文件：存放表具体记录的数据MYI文件：存储索引1.2.2InnoDBInnoDB是默认的数据库存储引擎，他的主要特点有：可以通过自动增长列，方法是auto_increment；支持事务。默认的事务隔离级别为可重复度，通过MVCC（并发版本控制）来实现的；使用的锁粒度为行级锁，可以支持更高的并发；支持外键约束；外键约束其实降低了表的查询速度，但是增加了表之间的耦合度；配合一些热备工具可以支持在线热备份；在InnoDB中存在着缓冲管理，通过缓冲池，将索引和数据全部缓存起来，加快查询的速度；对于InnoDB类型的表，其数据的物理组织形式是聚簇表。所有的数据按照主键来组织。数据和索引放在一块，都位于B+数的叶子节点上。1.2.3Memory将数据存在内存，为了提高数据的访问速度，每一个表实际上和一个磁盘文件关联。文件是frm。支持的数据类型有限制，比如：不支持TEXT和BLOB类型，对于字符串类型的数据，只支持固定长度的行；VARCHAR会被自动存储为CHAR类型；支持的锁粒度为表级锁。所以，在访问量比较大时，表级锁会成为MEMORY存储引擎的瓶颈；由于数据是存放在内存中，一旦服务器出现故障，数据都会丢失；查询的时候，如果有用到临时表，而且临时表中有BLOB，TEXT类型的字段，那么这个临时表就会转化为MyISAM类型的表，性能会急剧降低；默认使用hash索引；如果一个内部表很大，会转化为磁盘表。1.3表与字段设计1.3.1数据库基本设计规范尽量控制单表数据量的大小，建议控制在500万以。500万并不是MySQL数据库的限制，过大会造成修改表结构、备份、恢复都会有很大的问题，可以用历史数据归档（应用于日志数据），分库分表（应用于业务数据）等手段来控制数据量大小；谨慎使用MySQL分区表。分区表在物理上表现为多个文件，在逻辑上表现为一个表谨慎选择分区键，跨分区查询效率可能更低建议采用物理分表的方式管理大数据；禁止在数据库中存储，文件等大的二进制数据。通常文件很大，会短时间内造成数据量快速增长，数据库进行数据库读取时，通常会进行大量的随机IO操作，文件很大时，IO操作很耗时通常存储于文件服务器，数据库只存储文件地址信息；禁止在线上做数据库压力测试。1.3.2数据库字段设计规范优先选择符合存储需要的最小的数据类型。列的字段越大，建立索引时所需要的空间也就越大，这样一页中所能存储的索引节点的数量也就越少也越少，在遍历时所需要的IO次数也就越多，索引的性能也就越差；避免使用TEXT、BLOB数据类型，最常见的TEXT类型可以存储64k的数据；尽可能把所有列定义为NOTNULL。1.3.3索引设计规范限制每张表上的索引数量，建议单张表索引不超过5个；禁止给表中的每一列都建立单独的索引；每个InnoDB表必须有个主键；建立索引的目的是：希望通过索引进行数据查找，减少随机IO，增加查询性能，索引能过滤出越少的数据，则从磁盘中读入的数据也就越少。区分度最高的放在联合索引的最左侧（区分度=列中不同值的数量/列的总行数）。尽量把字段长度小的列放在联合索引的最左侧（因为字段长度越小，一页能存储的数据量越大，IO性能也就越好）。使用最频繁的列放到联合索引的左侧（这样可以比较少的建立一些索引）。1.3.4数据库SQL开发规范充分利用表上已经存在的索引,避免使用双%号的查询条件。如alike'%123%'，（如果无前置%，只有后置%，是可以用到列上的索引的）一个SQL只能利用到复合索引中的一列进行范围查询，如：有a,b,c列的联合索引，在查询条件中有a列的范围查询，则在b,c列上的索引将不会被用到，在定义联合索引时，如果a列要用到范围查找的话，就要把a列放到联合索引的右侧；使用leftjoin或notexists来优化notin操作，因为notin也通常会使用索引失效；禁止使用SELECT*必须使用SELECT查询；避免使用子查询，可以把子查询优化为JOIN操作；避免使用JOIN关联太多的表。1.4范式与反范式1.4.1第一范式该范式是为了排除重复组的出现，因此要求数据库的每个列的值域都由原子值组成；每个字段的值都只能是单一值。1971年埃德加·科德提出了第一范式。即表中所有字段都是不可再分的。解决方案：想要消除重复组的话，只要把每笔记录都转化为单一记录即可。1.4.2第二范式表中必须存在业务主键，并且非主键依赖于全部业务主键。解决方案：拆分将依赖的字段单独成表。1.4.3第三范式表中的非主键列之间不能相互依赖，将不与PK形成依赖关系的字段直接提出单独成表即可。1.5sql索引B树只适合随机检索，适合文件操作，B+树同时支持随机检索和顺序检索；B+树的磁盘读写代价更低，B+树的内部结点并没有指向关键字具体信息的指针；B+树的查询效率更加稳定。B树搜索有可能会在非叶子结点结束；只要遍历叶子节点就可以实现整棵树的遍历，数据库中基于范围的查询是非常频繁，B树这样的操作效率非常低。1.6join连表1.6.1JOIN按照功能大致分为如下三类：INNERJOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。LEFTJOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。RIGHTJOIN（右连接）：与LEFTJOIN相反，用于获取右表所有记录，即使左表没有对应匹配的记录。1.6.2join的原理MySQL使用了嵌套循环（Nested-LoopJoin）的实现方式。Nested-LoopJoin需要区分驱动表和被驱动表，先访问驱动表，筛选出结果集，然后将这个结果集作为循环的基础，访问被驱动表过滤出需要的数据。Nested-LoopJoin分下面几种类型：SNLJ，简单嵌套循环。这是最简单的方案，性能也一般。实际上就是通过驱动表的结果集作为循环基础数据，然后一条一条的通过该结果集中的数据作为过滤条件到下一个表中查询数据，然后合并结果。如果还有第三个参与Join，则再通过前两个表的Join结果集作为循环基础数据，再一次通过循环查询条件到第三个表中查询数据，如此往复。相关来源于网络这个算法相对来说就是很简单了，从驱动表中取出R1匹配S表所有列，然后R2，R3,直到将R表中的所有数据匹配完，然后合并数据，可以看到这种算法要对S表进行RN次访问，虽然简单，但是相对来说开销还是太大了INLJ，索引嵌套循环。索引嵌套联系由于非驱动表上有索引，所以比较的时候不再需要一条条记录进行比较，而可以通过索引来减少比较，从而加速查询。这也就是平时我们在做关联查询的时候必须要求关联字段有索引的一个主要原因。相关来源于网络BNLJ，块嵌套循环。如果join字段没索引，被驱动表需要进行扫描。这里MySQL并不会简单粗暴的应用前面算法，而是加入了buffer缓冲区，降低了内循环的个数，也就是被驱动表的扫描次数。这个buffer被称为joinbuffer，顾名思义，就是用来缓存join需要的字段。MySQL默认buffer大小256K，如果有n个join操作，会生成n-1个joinbuffer。1.6.3join的优化小结果集驱动大结果集。用数据量小的表去驱动数据量大的表，这样可以减少内循环个数，也就是被驱动表的扫描次数。用来进行join的字段要加索引，会触发INLJ算法，如果是主键的聚簇索引，性能最优。例子：第一个子查询是72075条数据，join的第二条子查询是50w数据，主要的优化还是驱动表是小表，后面的是大表，on的条件加上了唯一索引。如果无法使用索引，那么注意调整joinbuffer大小，适当调大些减少不必要的字段查询（字段越少，joinbuffer所缓存的数据就越多）2.数据进阶2.1sql执行过程如上图所示，当向MySQL发送一个请求的时候，MySQL到底做了什么：客户端发送一条查询给服务器。服务器先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段；在解析一个查询语句之前，如果查询缓存是打开的，那么MYSQL会优先检查这个查询是否命中查询缓存中的数据；这个检查是通过一个对大小写敏感的哈希查找的。查询和缓存中的查询即使只有一个不同，也不会匹配缓存结果；如果命中缓存，那么在但会结果前MySQL会检查一次用户权限，有权限则跳过其他步骤直接返回数据；服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划。MySQL解析器将使用MySQL语法规则验证和解析查询。例如验证是否使用错误的关键字、关键字顺序、引号前后是否匹配等；预处理器则根据一些MySQL规则进一步解析树是否合法，例如检查数据表和数据列是否存在，解析名字和别名是否有歧义等；MySQL根据优化器生成的执行计划，再调用存储引擎的API来执行查询。MySQL的查询优化器使用很多策略来生成一个最优的执行计划。优化策略可以简单的分为两种：静态优化：静态优化可以直接对解析树进行分析，并完成优化。例如优化器可以通过简单的代数变化将WHERE条件转换成另外一种等价形式，静态优化在第一次完成后就一直有效，即使使用不同的参数重复执行查询也不会变化。可以认为是一种”编译时优化“。动态优化：和查询的上下文有关，也可能和其他因素有关，例如WHERE中取值、索引中条目对应的数据行数等。这需要在每次查询的时候重新评估，可以让那位u是"运行时优化"。使用showstatuslike‘Last_query_cost’可以查询上次执行的语句的成本，单位为数据页。2.2sql查询计划使用explain进行执行计划分析：2.3sql索引优化遵循索引原则适合大部分的常规数据库查询场景，但不是所有的索引都能符合预期，从索引原理本身来分析对索引的创建会更有帮助。小表的全表扫描往往会比索引更快；中大型表使用索引会有很大的查询效率提升；超大型表，索引也无法解决慢查询，过多和过大的索引会带来更多的磁盘占用和降低INSERT效率。2.3.1前缀索引当要索引的列字符很多时索引则会很大且变慢(可以只索引列开始的部分字符串节约索引空间从而提高索引效率)例如：一个数据表的x_name值都是类似23213223.434323.4543.4543.34324这种值，如果以整个字段值做索引，会使索引文件过大，但是如果设置前7位来做索引则不会出现重复索引值的情况了。查询效率会大大提升：2.3.2联合索引顺序altertabletable1addkey（distribute_type，plat_id）使用选择基数更高（不重复的数据）的字段作为最左索引：2.3.3联合索引左前缀匹配a=?andb=?andc=?；查询效率最高，索引全覆盖a=?andb=?；索引覆盖a和ba=?orb=?；索引覆盖a和bb=?ora=?；无法覆盖索引(>、分库和垂直分表。第二种：网络IO瓶颈，请求的数据太多，网络带宽不够->分库。CPU瓶颈第一种：SQL问题，如SQL中包含join，groupby，orderby，非索引字段条件查询等，增加CPU运算的操作->SQL优化，建立合适的索引，在业务Service层进行业务计算。第二种：单表数据量太大，查询时扫描的行太多，SQL效率低，CPU率先出现瓶颈->水平分表。2.6.2分库分表水平分库相关来源于网络概念：以字段为依据，按照一定策略（hash、range等），将一个库中的数据拆分到多个库中。结果：每个库的结构都一样；每个库的数据都不一样，没有交集；所有库的并集是全量数据；场景：系统绝对并发量上来了，分表难以根本上解决问题，并且还没有明显的业务归属来垂直分库。分析：库多了，io和cpu的压力自然可以成倍缓解。水平分表相关来源于网络概念：以字段为依据，按照一定策略（hash、range等），将一个表中的数据拆分到多个表中。结果：每个表的结构都一样；每个表的数据都不一样，没有交集；所有表的并集是全量数据。场景：系统绝对并发量并没有上来，只是单表的数据量太多，影响了SQL效率，加重了CPU负担，以至于成为瓶颈。分析：表的数据量少了，单次SQL执行效率高，自然减轻了CPU的负担。垂直分库相关来源于网络概念：以表为依据，按照业务归属不同，将不同的表拆分到不同的库中。结果：每个库的结构都不一样；每个库的数据也不一样，没有交集；所有库的并集是全量数据。场景：系统绝对并发量上来了，并且可以抽象出单独的业务模块。分析：到这一步，基本上就可以服务化了。例如，随着业务的发展一些公用的配置表、字典表等越来越多，这时可以将这些表拆到单独的库中，甚至可以服务化。再有，随着业务的发展孵化出了一套业务模式，这时可以将相关的表拆到单独的库中，甚至可以服务化。垂直分表相关来源于网络概念：以字段为依据，按照字段的活跃性，将表中字段拆到不同的表（主表和扩展表）中。结果：每个表的结构都不一样；每个表的数据也不一样，一般来说，每个表的字段至少有一列交集，一般是主键，用于关联数据；所有表的并集是全量数据。2.6.3分库分表工具目前市面上的分库分表中间件相对较多，其中基于代理方式的有MySQLProxy和Amoeba，基于Hibernate框架的是HibernateShards，基于jdbc的有当当sharding-jdbc，基于mybatis的类似maven插件式的有蘑菇街的蘑菇街TSharding，通过重写spring的ibatistemplate类的CobarClient。还有一些大公司的开源产品：3.分布式数据库3.1什么是分布式数据库分布式系统数据库系统原理(第三版)中的描述：“我们把分布式数据库定义为一群分布在计算机网络上、逻辑上相互关联的数据库。分布式数据库管理系统(分布式DBMS)则是支持管理分布式数据库的软件系统，它使得分布对于用户变得透明。有时，分布式数据库系统(DistributedDatabaseSystem,DDBS)用于表示分布式数据库和分布式DBMS这两者。在以上表述中，“一群分布在网络上、逻辑上相互关联”是其要义。在物理上一群逻辑上相互关联的数据库可以分布式在一个或多个物理节点上。当然，主要还是应用在多个物理节点。这一方面是X86服务器性价比的提升有关，另一方面是因为互联网的发展带来了高并发和海量数据处理的需求，原来的单物理服务器节点不足以满足这个需求。3.2分布式数据库的理论基础1.CAP理论首先，分布式数据库的技术理论是基于单节点关系数据库的基本特性的继承，主要涉及事务的ACID特性、事务日志的容灾恢复性、数据冗余的高可用性几个要点。其次，分布式数据的设计要遵循CAP定理，即：一个分布式系统不可能同时满足一致性(Consistency)、可用性(Availability)、分区容忍性(Partitiontolerance)这三个基本需求，最多只能同时满足其中的两项，分区容错性是不能放弃的，因此架构师通常是在可用性和一致性之间权衡。这里的权衡不是简单的完全抛弃，而是考虑业务情况作出的牺牲，或者用互联网的一个术语“降级”来描述。CAP三个特性描述如下：一致性：确保分布式群集中的每个节点都返回相同的、最近更新的数据。一致性是指每个客户端具有相同的数据视图。有多种类型的一致性模型，CAP中的一致性是指线性化或顺序一致性，是强一致性。可用性：每个非失败节点在合理的时间内返回所有读取和写入请求的响应。为了可用，网络分区两侧的每个节点必须能够在合理的时间内做出响应。分区容忍性：尽管存在网络分区，系统仍可继续运行并保证一致性。网络分区已成事实。保证分区容忍度的分布式系统可以在分区修复后从分区进行适当的恢复。2.BASE理论基于CAP定理的权衡，演进出了BASE理论，BASE是BasicallyAvailable(基本可用)、Softstate(软状态)和Eventuallyconsistent(最终一致性)三个短语的缩写。BASE理论的核心思想是：即使无法做到强一致性，但每个应用都可以根据自身业务特点，采用适当的方式来使系统达到最终一致性。BA：BasicallyAvailable基本可用，分布式系统在出现故障的时候，允许损失部分可用性，即保证核心可用；S：Softstate软状态，允许系统存在中间状态，而该中间状态不会影响系统整体可用性；E：Consistency最终一致性，系统中的所有数据副本经过一定时间后，最终能够达到一致的状态。BASE理论本质上是对CAP理论的延伸，是对CAP中AP方案的一个补充。3.3分布式数据库的架构演变三类数据库架构特点：Shard-everting：共享数据库引擎和数据库存储，无数据存储问题。一般是针对单个主机，完全透明共享CPU/MEMORY/IO，并行处理能力是最差的，典型的代表SQLServer；Shared-storage：引擎集群部署，分摊接入压力，无数据存储问题；Shard-noting：引擎集群部署，分摊接入压力，存储分布式部署，存在数据存储问题。各个处理单元都有自己私有的CPU/内存/硬盘等，不存在共享资源，类似于MPP（大规模并行处理）模式，各处理单元之间通过协议通信，并行处理和扩展能力更好。典型代表DB2DPF和hadoop，各节点相互独立，各自处理自己的数据，处理后的结果可能向上层汇总或在节点间流转。近期热文：三年磨一剑——微信OCR轻松提取文字腾讯广告3000+万行大代码库主干开发实战揭晓腾讯人最喜欢用的三大编程语言周四晚腾讯程序员视频号直播：