【.com原稿】在程序、语言、架构更新频繁的今天，MySQL大概是使用最多的存储数据库。图片来自PexelsMySQL用于大量信息的存储和查询，因此其优化对于系统性能的提升尤为重要。由于MySQL优化的范围很广，从软件到硬件，从配置到应用，不可能一一讨论。今天，我将从开发人员的角度来介绍MySQL应用优化。包括数据类型、数据表查询/修改、索引和查询等。数据类型优化字段是用来存储数据的单位。设计字段是设计数据库的第一步，同时也会影响系统的性能。设计字段有一个基本原则，少不宜大，即可以使用字节数少的字段，而不使用字节数大的字段，目的是节省空间，提高查询效率。更小的字段占用更少的磁盘空间、内存空间和更少的IO消耗。下面针对使用场景介绍一些字段类型选择的经验，供大家参考。数值型手机号码：通常我们在存储手机号码的时候，喜欢使用Varchar类型。如果是11位的手机号码，假设我们使用utf8编码，每个字节需要3个字节，那么我们需要11*33=33个字节来存储；如果我们使用bigint，只需要8个字符的section就可以存储。IP地址：同上，IP地址也可以用int（4字节）存储，通过INET_ATON()函数可以将IP地址转换成数字。这里需要注意溢出的问题，需要使用unsignedint。age，枚举类型：tinyint可以用来存储，只占1个字节，unsignedtinyint可以表示0-255的范围，基本够用了。字符类型Char和Varchar是常用的字符类型。char(N)用于记录定长字符，如果长度小于N，则用空格填充。varchar(N)用于存储变长字符，它会额外增加1-2个字节来存储字符串的长度。Char和Varchar占用的字节数根据数据库的编码格式不同而不同。Latin1占1个字节，gbk占2个字节，utf8占3个字节。在使用上，如果存储的内容是可变长度的，例如：家庭住址，可以使用Varchar进行用户描述。如果内容是固定长度的，比如：UUID（36位），或者MD5加密字符串（32位），可以用Char存储。时间类型Datetime和Timestamp都是可以精确到秒的时间类型，但是Datetime占8个字节，而Timestamp占4个字节。所以日常建表的时候可以有限的选择Timestamp。但是，它们有以下需要注意的小差异。区别一：存储数据的方式不同。timestamp转换成utc时间存储，查询时转换成client时间返回。区别二：两者的保存时间范围不同。时间戳是“1970-01-0100:00:01.000000”到“2038-01-1903:14:07.999999”。日期时间是“1000-01-0100:00:00.000000”到“9999-12-3123:59:59.999999”。数据表查询/修改优化说完如何高效地选择存储数据的类型，我们来看一下如何高效地读取数据。MySQL作为关系型数据库，在处理复杂业务时，往往会选择表与表之间的关联。这会导致我们在查询数据的时候会关联到其他表，尤其是在查询一些多维数据的时候，这种关联尤为突出。此时，为了提高查询效率，我们会对一些字段进行冗余处理，使这些字段同时存在于多个表中。但是这样会带来其他的问题，比如：修改冗余字段，需要修改多张表，需要把这个修改保存在一个东西里。如果处理不当，会导致数据不一致。这里需要根据具体情况采用查询策略，例如：需要跨多个表查询公司的销售信息。由于销售信息需要连接多个表，并且对销售量和金额进行求和运算，直接查询显然是不合适的。可以生成后台服务，定期从相关表中检索信息，并将计算结果放入汇总表中。为汇总表中需要查询的条件字段添加索引信息，提高查询效率。这种方式仅限于实时查询数据不强的情况。在高速迭代的开发过程中，业务快速变化，数据库会根据业务变化进行迭代。因此，在开发新产品的初期，表结构会面临频繁的修改。MySQL的ALTERTABLE操作的性能对于大表来说是一个问题。MySQL执行修改表结构操作的方式是创建一个新结构的空表，从旧表中找出所有数据插入到新表中，然后删除旧表。这个操作需要很多时间。如果内存不足，表数据大，索引多，会导致锁表时间长。在极端情况下，某些ALTERTABLE操作需要数小时甚至数天才能完成。这里推荐两个小技巧：首先，将数据库复制到非生产服务器上，修改上面的表。此时的修改不会影响生产数据库。修改完成后切换数据库，将非生产数据库切换到生产数据库。但是需要注意的是，在修改表结构的时候，生产库会产生一些数据。这里需要通过脚本根据时间间隔导入这部分数据。“影子复制”，即生成一个表结构相同但名称不同的新数据表（改变数据结构后的表）。然后将原表的数据导入到新表中，导入成功后停止数据库，修改原表和新表的名称，最后将数据访问指向新表。正常运行后，删除原来的表。这里有现成的工具可以辅助进行以上操作，“onlineschemachange”、“openarktoolkit”，如果只是删除或者更改某列的默认值，可以直接使用Alerttablemodifycolumn和Alert表alert列实现。索引优化说完字段和表，再来说说索引。网上关于索引优化的说法很多，都是在实际工作中总结的。这里没有一定的标准。对于InnoDB存储引擎（使用的B-Tree索引），我们为您推荐了几种方法。独立于索引“独立于索引”意味着索引列不能是表达式的一部分，也不能是函数的参数。例如：假设在User表中设置了create_date和userId作为索引。select*fromuserwheredate(create_date)=curdate()selectuserIdfromuserwhereuserId+1=5和上面的语句类似，索引作为函数中参数和表达式的一部分，不推荐使用。前缀索引有时索引字段长度很大，例如：VarChar、Blob、Text。这会使索引在搜索时变大变慢。通常的做法是在开头索引一些字符，这样可以节省索引空间，提高索引效率。既然索引所有的字符是行不通的，索引多少个字符就是我们要讨论的问题。这里需要引入一个概念，索引选择性。索引选择性是指唯一索引值与数据表中记录总数的比率。索引的选择性越高，查询效率就越高，因为选择性高的索引可以让MySQL在查找的时候过滤掉更多的行。例如：有一个用户表，其中一个是FirstName，如何计算该字段的选择性如下：Select1.0*count(distinctFirstName)/count(*)fromuser假设结果为0.75然后使用left函数选择字段取一些字符，比如从左边取3、4、5个字段。分别检查其选择性，目的是查看何时选择了多少个字符，选择性最接近0.75。从左边取3个字段时，Select1.0*count(distinctleft(FirstName,3))/count(*)fromuser的结果是0.58从左边取4个字段时，Select1.0*count(distinctleft(FirstName,4))/count(*)fromuser结果为0.67当从左边取5个字段时，Select1.0*count(distinctleft(FirstName,5))/count(*)fromuser结果为0.74从上面的尝试，字段FirstName取左边的字符。从3-5的获取可以看出，当取左起第五个字符时，选择性0.74最接近0.75。因此，可以将FirstName的前5个字符作为前缀索引，这样索引的效果与索引FirstName的所有字符的效果基本相同。不使用FirstName的整个字段作为索引。所以可以使用如下语句来修改索引信息：Altertableuseraddkey(FirstName(5))多列索引及其顺序多列索引，顾名思义，多列字段作为索引。假设通过搜索LastName和FirstName条件在用户表中找到数据。可能会出现以下语句：Select*fromuserwhereLastName='Green'Select*fromuserwhereLastName='Green'andFirstName='Jack'Select*fromuserwhereLastName='Green'and(FirstName='Jack'orFirstName='Michael'Select*fromuserwhereLastName='Green'andFirstName>='M'andFirstName<'N'如果分别在LastName和FirstName上建立索引：Select*fromuserwhereLastName='Green'andFirstName='Jack'当运行上面的代码时，系统会让高选择性的SQL建立索引生效，其他索引不可用，因此需要创建多列索引（mergeindex），语句如下：Altertableuseraddkey(LastName,FirstName)既然定义了多列索引，那么indexorderalsoneedtoConsiderthat?在一个多列的B-Tree索引中，索引列的顺序是指索引先按照最左边的列排序，然后是第二列。索引可以升序扫描也可以降序扫描为了满足确切的列顺序qORDERBY、GROUPBY和DISTINCT等子句的查询要求。因此，需要考虑多列索引的顺序。这里给出的建议是将最具选择性的索引列放在最前面。沿用上面的例子，仍然是LastNameAndFirstName作为多列索引。看谁应该放在前面。根据选择性规则，编写如下SQL语句：先计算LastName的Selectcount(disctincLastName)/count(*)fromuser结果为0.02，再计算FirstName的Selectcount(disctincFirstName)/count(*)fromuser结果为0.05FirstName的选择性高于LastName。因此，调整多列索引的顺序如下：Altertableuseraddkey(FirstName,LastName)覆盖索引使用Select时，只需要从索引中获取数据列，而不需要从数据表中读取，换句话说，查询列必须被索引覆盖使用。例如：用户表的索引是姓氏。如果写如下查询语句：SelectLastNamefromuserLastName并作为一个索引，并且显示在查询内容中，那么LastName就是一个覆盖索引。覆盖索引是一种查找行的有效方法。可以通过索引读取数据，不需要从数据表中读取数据。而覆盖索引会被标记为Usingindex，可以通过Explain语句查看。说明查看覆盖索引可以看出，覆盖索引主要用于Count等一些聚合操作，以提高查询效率。例如上面提到的Selectcount(LastName)fromuser可以将LastName设置为索引。还有表回优化可以进行列查询，如下：SelectLastName,FirstNamefromuserwhereLastName='Jack'如果此时将LastName设置为索引，则可以将LastName和FirstName设置为多列索引（联合索引）。避免退货行为的发生。这里的回表指的是secondaryindex搜索完后，找到聚合索引，然后进行PK搜索的过程。这需要通过两次搜索来完成。简单的说，使用覆盖索引后，一次就可以找到想要的记录，不需要第二次查找。回表示意图查询优化作为程序开发人员，使用最多的是SQL语句，使用频率最高的操作就是查询。下面我们就来看看哪些因素会影响查询记录，查询的基本原则是什么，如何发现和优化SQL语句。影响查询效率的因素一般来说，影响查询的因素由三部分组成，如下：响应时间由两部分组成，即服务时间和排队时间。服务时间是数据库处理查询所花费的时间。排队时间是指服务器等待某个资源所花费的时间。例如：I/O操作，等待其他事务释放锁的时间。扫描记录行数，查询过程中数据库锁扫描的行记录。理想情况下，扫描的行数和返回的行数相同。但是，一般来说，扫描的行数大于返回的行数。返回记录行数和实际要查询的结果。QueryBasicQueryFlowchart说完影响查询效率的因素，我们再来看看MySQL中的查询是如何进行的，这可以帮助我理解查询优化工作是在哪里进行的：客户端向服务器发送查询。服务器首先检查查询缓存，如果命中缓存，则立即返回存储在缓存中的结果。解析器解析SQL，它通过关键字解析SQL语句，生成对应的“解析树”。MySQL解析器将使用MySQL语法规则验证和解析查询。预处理器根据某些MySQL规则进一步检查解析树的有效性并验证权限。例如，检查表和列是否存在，以及解析名称和别名是否有歧义。MySQL根据优化器生成的执行计划调用存储引擎的API执行查询。将结果返回给客户端。如何找到慢查询SQL说完影响慢查询的因素和查询的基本过程，我们来看看如何找到慢查询SQL。这里MySQL提供了一个日志，可以在里面查询执行慢的SQL。①检查慢查询日志是否开启SHOWVARIABLELIKE'%slow_query_log%';②如果没有启用，通过命令SETGLOBALslow_query_log=1启用慢查询日志；③设置慢查询日志的时间，这里的单位是秒，也就是说只要执行时间超过X秒的查询语句都会被记录到这个日志中。这里的X是你想要设置的。(以下例子设置为3秒)SETGLOBALlong_query_time=3;④查看有多少条SQL语句超过了查询阈值（3秒）解释分析SQL查询通过上面的方法，你可以知道哪些SQL耗时较多，那么如何分析这些SQL语句。毕竟，我们的目的是在分析后优化SQL以提高其性能。在要执行的SQL语句前加上Explain关键字，可以模拟优化器执行SQL语句，从而知道MySQL是如何处理你的SQL语句的。Explain执行SQL示意图中各字段的含义，这里不再赘述。SQL优化建议如果发现查询速度慢的SQL，我们需要对其进行优化。下面针对几个常见的SQL给出一些优化建议。网上像SQL优化之类的文章和例子五花八门，千奇百怪。建议优化前先查看慢查询日志和Explain语句，再有针对性地进行优化。①Count优化在user表中查找所有id大于7的用户，如果是InnoDB存储引擎，会逐行查找。如果表中的记录很多，性能就会有问题。如果selectcount(*)fromuserwhereid>7先统计所有行，再减去id<=7的记录，速度会更快。select(selectcount(*)-(selectcount(*)fromuserwhereid<=7)fromuser)如果有goods表items，里面有个color字段表示商品的颜色，如果需要知道商品的数量color是blue或者red，可以这样写：Selectcount(color='blue'orcolor='red')fromitemsSelectcount(*)fromitemswherecolor='blue'andcolor='red'但是，color本身是一个排除字段，所以可以优化成如下SQL。Selectcount(color='blue'ornull)asblue,count(color='red'ornull)asredfromitems②GROUPBY优化MySQL使用索引来优化GROUPBY查询。当不能使用索引时，使用两种优化策略：临时表和文件排序分组。它的性能可以通过两个参数SQL_BIG_RESULT和SQL_SMALL_RESULT来提高。这两个参数只对Select语句有效。它们告诉优化器使用临时表并对GROUPBY查询进行排序。SQL_SMALL_RESULT告诉优化器结果集会很小，结果集可以放在内存中索引的临时表中以避免排序操作。如果是SQL_BIG_RESULT，则告诉优化器结果集可能非常大，建议使用临时磁盘表进行排序操作。例如：SelectSQL_BUFFER_RESULTfield1,count(*)fromtable1groupbyfield1假设查询关联了两个表，在查询表中选择标识列（主键）进行分组效率会高。比如actor表和film表是通过actorId关联的，查询如下：Selectactor.FirstName,actor.LastName,count(*)fromfilminnerjoinactorusing(actorId)Groupbyactor.FirstName,actor.LastName可以修改为：Selectactor.FirstName,actor.LastName,count(*)fromfilminnerjoinactorusing(actorId)Groupbyfilm.actorId③LimitLimit我们很熟悉，尤其是在做分页操作的时候，经常用到。但是当偏移量很大时，问题就来了。比如Limit1000,20需要偏移1000条记录，然后返回后面的20条记录，前面的1000条记录被丢弃。根据上面的例子，SQL代码如下：selectnamefromuserorderbyidlimit1000,20这里用id索引第1001条记录，然后取20条记录。这里利用id的索引直接跳过前1000条记录。Selectnamefromuserwhereid>=1001orderbyidlimit20总结从开发者的角度了解MySQL应用优化。从数据类型的选择入手，分别举例说明了数值类型、字符类型和时间类型。下面说说作为数据表的查询和修改的优化需要注意的细节。然后，我们讲了独立索引、前缀索引、多列索引和覆盖索引的优化方法。最后，讨论了最常用查询的优化。从影响查询的因素到查询依据，以及如何发现慢查询，我们以几条SQL优化建议结束了我们的MySQL应用优化之旅。写完一篇完整的文章，感觉MySQL博大精深，需要学习的东西很多。一篇文章不能面面俱到，需要不断学习。作者：崔浩简介：十六年开发架构经验。曾在惠普武汉交付中心担任技术专家、需求分析师、项目经理，后在一家初创公司担任技术/产品经理。善于学习，乐于分享。目前专注于技术架构和研发管理。【原创稿件，合作网站转载请注明原作者和出处为.com】
