如何避免写出“慢SQL”.docx

《如何避免写出“慢SQL”.docx》由会员分享，可在线阅读，更多相关《如何避免写出“慢SQL”.docx（5页珍藏版）》请在优知文库上搜索。

1、Ol定量认识MySQL这说明，慢SQL对数据库的影响，是一个从量变到质变的过程，对“量”的把握，对于开发人员来说是很重要的。一个合格的程序员，需要对数据库的能力有一个定量的认识。影响MySQL处理能力的因素有很多，比如，服务器的配置、数据库中数据量的大小、MySQL的一些参数配置、数据库的繁忙程度，等等。但是，通常情况下，这些因素对于MySQL的性能和处理能力的影响，大概在一个数量级的范围内，也就是上下几倍的性能差距。所以，我们不需要知道精确的性能数据，只要掌握一个大致的量级，就足够应对实际的开发工作了。目前，一台普通的MySQL数据库服务器，处理能力的极限大致是，每秒一万条左右的简单SQL。

2、这里的“简单SQL”，指的是类似于主键查询这种不需要遍历很多条记录的SQL语句。根据配置的高低，服务器的处理能力也会有所不同，可能低配的服务器只能达到每秒几千条，高配的服务器则可以达到每秒几万条，所以这里给出的每秒一万条是中位数的经验值。考虑到正常的系统不可能只有简单SQL,所以实际的处理能力还要打很大折扣。我个人的经验是，一台MySQL数据库服务器，平均每秒执行的SQL数量在几百左右，一般就已经是非常繁忙了。即使看起来CPU利用率和磁盘繁忙程度并不高，我们也需要考虑为数据库“减负”了。另外一个重要的定量指标是，多慢的SQL才算是慢SQL?这里的“慢”，衡量的单位本来是执行时长，但是对于时长这

3、个指标，我们在编写SQL的时候并不好衡量。因此可以用执行SQL查询时，需要遍历的数据行数来替代时间作为衡量标准，因为查询的执行时长与遍历的数据行数基本上是正相关的。我们在编写一条查询语句的时候，可以依据所要查询数据表的数据总量估算一下这条查询大致需要遍历多少行数据。如果遍历的行数在百万以内，只要不是每秒都要执行几十上百次的查询，就可以认为该查询是安全的。遍历数据行数达到几百万量级的，查询最快也要花费几秒的时间，这时我们就要仔细考虑有没有优化的办法。遍历行数达到千万量级或以上的，这种SQL就不应该出现在系统中了。当然，我们这里讨论的都是在线交易系统，离线分析类系统另当别论。遍历行数达到千万量级的

4、SQL,是MySQL查询的一个坎儿。在MySQL中，单个表的数据量，也要尽量控制在一千万条以下，最多不要超过两三千万这个量级。原因很简单，对一个千万量级的表执行查询，加上几个WHERE条件过滤一下，符合条件的数据最多可能是几十万或百万量级的，还是可以接受的。但如果再与其他的表做一个联合查询，遍历的数据量很可能就会超过千万量级了。所以，每个表的数据量最好控制在千万量级以内。如果数据库中的数据量本身就很多，而且查询业务逻辑确实需要遍历大量数据，应该怎么办呢？02使用索引避免全表扫描使用索引，可以有效减少执行查询时遍历数据的行数，从而提高查询的性能。数据库索引的原理比较简单，一个例子就能说明白。比如

5、，有一个无序的数组，数组中的每个元素都是一个用户对象。如果我们要把所有姓李的用户都找出来，那么比较笨的办法是，用一个循环把数组遍历一遍。是否还有更好的办法呢？答案是肯定的。比如，我们可以用一个MaP（在某些编程语言中是DiCtionary）来为数组做一个索引，Key用于保存姓氏，值是所有这个姓氏的用户对象在数组中序号的集合，如图1所示。这样在查找的时候，就不用遍历数组了，只需要先在MaP中查找，然后再根据序号直接去数组中获取用户数据即可，这样查找速度就快多了。数组下面我们把这个例子对应到数据库中，存放用户数据的数组就是表，我们构建的M叩就是索引。实际上，数据库索引的数据结构与编程语言中的MaP

6、或Dictionary的结构差不多，基本上都是各种B树和哈希表。绝大多数情况下，我们编写的查询语句，都应该使用索引，以避免遍历整张表，也就是通常所说的，避免全表扫描。在开发新功能时，每当需要为数据库增加一个新的查询时，我们都要事先评估一下，是否可以由索引支撑新的查询语句，如果有必要，则需要新建索引，以支持新增的查询。但是，增加索引需要付出的代价是，会降低数据插入、删除和更新的性能。这一点也很好理解，增加了索引之后，当数据发生变化的时候，不仅要变更数据表里的数据，还要变更各个索引。所以，对于更新频繁并且对更新性能要求较高的表，可以尽量少建索引。而对于查询较多、更新较少的表，可以根据查询的业务逻辑

7、，适当多建一些索引。那公，如何写SQL才能更好地利用索引，使查询效率更高呢？这是一门技艺，需要有丰富的经验，不是学习完本文的内容就能练成的（推荐阅读电商存储系统实战：架构设计与海量数据处理）。但是，对于SQL的查询性能，我们还是有方法评估其是否为一个潜在的“慢SQL”的。对于逻辑不是很复杂的单表查询，我们可能还可以分析出查询会使用哪个索引。但如果是比较复杂的多表联合查询，单看SQL语句本身，我们将很难分析出查询到底会使用哪些索引，会遍历多少行数据。MySQL和大部分数据库都提供了一个可用于分析查询的功能，即执行计划。03分析SQL执行计划在MySQL中使用执行计划非常简单，只要在SQL语句前面

8、加上EXPLAIN关键字，然后执行这个查询语句就可以了。下面就来举例说明，比如，有这样一个用户表，包含用户ID、姓名、部门编号和状态这几个字段，如图2所示。mysqldescuser;Field+-ITypeINull-+IKeyIDefault-IExtraidIbigint(19)unsignedINOIPRIINULLIauto_incrementnameIVarChar(50)INOIINULLIdepartment_codeIVarChar(50)INOIMULINULLIstatusItinyint(4)INO+-I-+-INULLI+图2用户表示例我们希望查询某个二级部门下的所有

9、人，查询条件是，部门代号以OOO28开头的所有人。下面这两个SQL语句的查询结果是一样的，都满足要求。那么，哪个查询语句的性能更好呢？工SELECT*FRoMUSerWHEREIef比的将唾!照怅gde,5)=礴缚；23SELECT*FRoMUSerWHEREdepartmentodeLlKE00028%;我们分别查看一下这两个SQL语句的执行计划，如图3所示。mys4lEXPLAINSELECTFROMuserWHEREIefMdeportiJode,S)-e28,.IidIselecttypeItableItypeIPoSSibIJlCeySkeyIICey-IenIrefIrowslEx

10、traI1ISIMPLEIuserIALLINULLINULLINULLINULLI4534IUsingwhereI1rowtnset(0.0MC)MysqlAEXPLAINSELEQFROMuserWHEREdepartment.codeLIKE02SX,;IidIselect.typetableItypeIpossible.keysIkeyIkey.lenrefIrowsIExtraI1ISIMPLEIuserIraneIdxUISeJdePartMent.codeidx.user.departsent.codeI1S2INULLI8IUsingwhereI1rowinset(.Msec)

11、图3两个SQL语句的执行计划下面就来分析一下这两个SQL语句的执行计划。首先来看rows这一列，rows列的含义是，MySQL预估执行这个SQL可能会遍历的数据行数。第一个SQL遍历了4534行，即整个User表的数据条数；第二个SQL只有8行，这8行其实就是符合条件的8条记录。显然，第二个SQL的查询性能要远高于第一个SQLo为什么第一个SQL需要全表扫描，而第二个SQL只需要遍历很少的行数呢？注意看type这一列，type列表示这个查询的访问类型。ALL代表全表扫描，这是性能最差的情况。range代表使用了索引，表示只在索引中进行范围查找，这是因为SQL语句的WHERE条件中有一个LlKE的查询限制。如果直接使用了索引，则type列显示的是index,并且可以在key列中看到实际上使用的是哪个索引。通过对比这两个SQL的执行计划，我们可以看到，第二个SQL虽然使用了公认为低效的LIKE查询条件，但是由于用到了索引的范围查找，因此遍历数据的行数远远少于第一个SQL,查询性能更好。