Mysql查询性能优化 - 好文

      Mysql查询性能优化要从三个方面考虑，库表结构优化、索引优化和查询优化。通常在实际应用中，我们要面对这三种搅和一起的情况。

一、库表结构优化

良好的逻辑设计和物理设计是高性能的基石。库表结构的设计既要关注全局，又要专注细节。要设计优秀的库表结构，可从以下几个方面着手：

1.  选择优化的数据类型

         选择优化的数据类型可以遵循以下几个原则：

         更小的通常更好，应该尽量选择正确数据类型的最小数据类型，更小数据类型通常更快，因为它们占用更少的磁盘、内存和cpu缓存。

         简单就好，简单的数据类型需要更少的cpu周期，比如整形比字符串操作代价更低。应该使用mysql内部类型存储时间类型，使用整形存储ip地址。


尽量避免null，尤其是添加索引的列。可为null的列，对mysql来说更难优化，可为null的列使用索引、索引统计和值比较都更复杂。可为null的列需要更多的存储空间，可以null的列被索引时，每个索引记录需要一个额外的字节，在myisam中甚至还可能导致固定大小的索引（列如只有一个整数列的索引）变成可变大小的索引。但是若现有的schema含有可为null列，通常不要首先考虑改掉这种情况，因为，通常修改这种情况对性能的提升不明显。

针对mysql中各个具体的数据类型做优化处理：

1) 整数类型


Mysql中的整数类型包括tinyint、smallint、mediumint、int、bigint，分别使用8、16、24、32和64位空间存储，它们可以存储的范围为-2
n到2n
-1，其中n表示位数。整数类型有可选的unsigned属性，表示不允许为负值，大致可以使用正数的范围提高一倍。Mysql可以为整数指定宽度，列如int（11），这个宽度不会限制值的合法范围，只是规定了mysql的一些交互工具（命令行客户端）用来显示字符的个数，对于存储和计算来说没有任何作用。

2) 实数类型


实数就是带有小数点的数据类型，mysql中的实数类型包括：float、double和decimal。其中，float和double属于浮点类型，是常见的数据类型，存储空间分别占用4和8个字节。Decimal可以指定精确的整数和小数位数，用于存储精确的数值，在财务中的应用广泛。Cpu无法直接执行decimal的计算，但是mysql内部实现了decimal的计算。可以使用decimal存储比bigint更大的整数。

3) 字符串类型

        Mysql中字符串类型包括varchar和char，分别用于存储可变长字符和固定长度字符。

4) Blob和text类型


Blob与text是用来存储大数据的数据类型，分别采用二进制和字符方式存储，与oracle中blob与clob（clob也是二进制存储，不是字符方式）类似。Mysql对blob和text列进行排序的方式与其它类型不同，它只对每个列的最前max_sort_length字节而不是整个字符做排序。Blob和text列不能添加索引。

5) 日期和时间类型

Mysql中包含datetime和timestamp两种存储日期和时间的类型。Datetime可以存储大范围的值，从1001到9999年，精确到秒，与时区无关，使用8个字节存储空间，显示格式为“2018-02-27
10:44:22”。Timestamp保存范围从1970到2038，显示时间依赖时区。通常尽量使用timestamp类型，因为它比达特time的空间效率更高。另外对于需要精确到毫秒的时间存储，可以先将它们转换成整数后保存。

6) 位数类型

      位数类型包括bit和set，使用不多，不建议使用，若要存储boolean型数据，建议转换为tinyint存储。

7) 选择标识符

标识列一般用来作为表的主键或在关联操作中使用，在mysql中标识列最好采用整数，可以利用它的auto_increament属性。对于字符串的标识列，如果能够避免尽量避免，因此字符串需要更大的存储空间，关联操作慢，myisam对字符串使用压缩索引，会导致查询很慢。如果存储uuid值时，尽量去除“-”符号，或者用unhex()函数转换uuid为16字节的数字，并且存储在一个binary(16)列中。检索时可以通过hex()函数来格式化为十六进制格式。具体理解下这个两个函数的使用？？

2.  Mysql schema设计中的陷阱

表的设计不能有太多的列，数千的列会影响性能，mysql的存储引擎api工作时需要在服务器层和存储引擎之间通过行缓存格式拷贝数据，然后在服务器层将缓存内容解码成各个列。从行缓存中将编码过的列转换成行数据结构的操作代价是非常高的，转换的代价依赖于列的数量。关联操作设计的表不要太多，否则执行会很慢，mysql限制了每个关联操作最多只能有61张表，在实际应用中，经验法则得出关联操作的表数量最好控制在12个以内。

3.  范式和反范式

       在初学数据库设计时，我们往往要遵循数据库的范式要求，尤其是前三个范式。严格遵循范式设计的表通常更小、数据冗余少，做更新操作简单快捷，但是，唯一的缺点就是在做查询时需要表关联，关联查询会不仅会带来高的代价，而且还可能造成索引策略失效，导致更低效率的查询。绝对的范式化是实验室中的产物，在实际的应用中要混用范式化和反范式化，根据具有情况，往往会带来较高的查询效率。

4.  缓存表和汇总表

有时提升性能最好的方法是将衍生的冗余数据保存到缓存表或汇总表，然后执行查询这些表即可得出所需要的数据。这里缓存表表示存储那些从其它表获取（但是每次获取的速度很慢）数据的表，而汇总表保存的是使用group
by
语句聚合数据的表（数据不是逻辑上冗余），也可以把这些表称为累积表。比如我们要统计某网站24小时之内发送的信息数量，那么从相关表进行统计就会很慢，如果我们每一个小时就把进行统计一次，并把统计结果存放到一张表中，当我们需要统计24小时之内发送的信息数量时，只需要执行简单地查询就能得到需要的结果，这样就极大地提高的查询效率，但是缺点是数据不是100%精确。

对于衍生数据保存的另外一种方式是使用物化视图，物化视图可以增量地重新计算其内容，不需要通过查询原始表来更新其数据。

计数器表在web应用中很常见，例如记录每个用户的发送信息的数量、下载文件的数量，利用计数器表可以很简单的获取个人的记录数量，比直接从相关数据表中统计的效率高很多，比如一张计数器表，记录网站的点击数量，若只有一行记录，那么在条记录上只能存在一个排它锁，更新查询操作等只能串行进行，影响并发，若将记录保存在多行上，每次随机选择一行进行更新，那么并发性能就会大幅提高。多行记录与单行记录的表设计，要参照具体的应用。

二、索引优化

索引应该是对查询性能优化最有效的手段了，它能够轻易地将查询效率提高几个数量级。

1.  索引类型

索引类型包括：b-tree索引、哈希索引、空间数据索引和全文索引等。

B-tree索引采用b-tree方法实现索引，存储字段值，而且索引列是顺序组织存储的，适合order
by、范围查找等操作。哈希索引存储的是字段值的哈希值，并不是真正的字段值，另外哈希索引无序，不适合做order
by或范围查找等操作。空间索引是用来做地理数据存储，但是mysql对地理信息的支持并不是太好，最好是使用postgis。全文索引是一种特殊的索引类型，它查找的是文本中的关键词，而不是全值对比，在同一列上可以创建全文索引和基于值b-tree索引，不会相冲突，全文索引使用于matchagainst，而不是普通的where操作。

2. 索引策略

了解索引类型后，正确地创建和使用索引是提高查询性能的基础。

1) 独立的列

独立的列就是索引列应该是单独使用不能作为表达式的一部分或函数的参数，例如下面的例子：

Select actor_idfrom actor where actor_id+1=5;

Select actor_idfrom actor where to_days(current_date)-to_days(date_col)<5;

这两种方式均无法使用索引。

2) 前缀索引

有时候需要在很长的字符列上添加索引，这样索引就会很大，会造成很慢。最好的方式就是使用字符串的部分前缀创建索引，前缀索引可以大大减少索引空间，提高索引效率。对于blob、text或很长的varchar类型列必须使用前缀索引，因为mysql不允许索引这些列的完整内容。前缀索引的缺点是mysql无法使用前缀索引做order
by和group by，无法覆盖扫描。在创建前缀索引时，要保证选择足够多的前缀，以保证较高的选择性，同时又不能太长以节约空间。

3) 多列索引

多列索引是相对单列索引来说的，单列索引就是在单个列上创建索引，多列索引就是在多个列上创建索引。Where后面有多个条件时，多列索引往往比单列索引更有效。例如，

Select * fromactor where actor_id=1 or film_id=0;

虽然mysql在执行sql时，会执行一个“索引合并”的优化，但是这只是优化的结果，说明了表上的索引创建的很糟糕。

三、查询优化

1. 重构查询方式

1) 切分查询


主要用于删除信息时，一次性删除大量数据时，则可能会需要锁住很多数据、占满整个事务日志，耗尽系统资源，阻塞很多查询。因此对于影响很多数据的delete语句，切分成多个较小的delete语句，能够快速执行完成，大大减少删除持有锁的时间，降低对mysql性能的影响。

2) 分解关联查询


有时候一个大的关联查询，可能会很慢，如何把这个关联查询分解成多个单表查询，然后在应用中对数据执行关联操作。Mysql可以实现单表查询缓存，因此多个单表查询，可以有效利用查询缓存。单表查询可以减少锁的影响。将查询的数据在应用中关联，可以更容易对数据库拆分，实现数据库的高性能和可扩展。可以减少冗余记录的查询，而且在应用中做关联，能够数据之间的哈希关联，避免在mysql中的嵌套循环查询，效率就会大幅提高。

2. Mysql查询优化器的限制


服务器收到sql后，对其进行解析、预处理，并由查询优化器处理生成对应的执行计划。查询优化器就是进一步对sql进行优化处理，使其执行速率更高。但是对一些特定的sql语句的写法，查询优化器是有局限的，并不能实现优化处理。因此，在书写sql语句时，要避开这些写法，下面是对查询优化器有局限的sql写法分类：

1) 关联子查询

         使用in()方法加子查询，比如下面sql语句：

Select * from filmwhere film_id in(select film_id from film_actor where
actor_id=1);

优化器会把该sql语句改成如下：

Select * from filmwhere exists(select * from film_actor where actor_id=1 and
film.film_id=

film_actor.film_id);

由于子查询关联到外部表，需要用到外部表的film_id，因此这个sql无法先执行子查询，只能先执行外部表的查询，找到film_id，根据film_id执行子查询语句。如果外部表数据非常大，那么这个sql执行时间就会很长。
因此，使用in()加子查询，性能经常会很低，通常使用exists代替in()来获取更高的效率。

另外对于关联子查询的效率不如使用左外连接（left out join）效率高，这种观点是不正确的，要具体分析。建议通过具体测试验证。

2) Union的限制

         Union的限制外部的条件无法渗入到内层的查询中，如下sql语句：

(Selectfirst_name,last_name from actor order by last_name) union all
(selectfirst_name,

last_name fromcustomer order by last_name) limit 20;

假如actor表有200条记录，customer表有900条记录，那么这条sql是扫描出actor的200条记录和customer的900条记录，然后将这些记录放入临时表时，再从临时表中取出前20条记录。可以通过在两个子查询上分别加上limt来减少临时表的数据，见下：

(Selectfirst_name,last_name from actor order by last_name limit 20) union all
(selectfirst_name,

last_name fromcustomer order by last_name limit 20) limit 20;

另外需要注意的是，临时表的顺序不一定是正确的，最好在外部加上一个全局的order by操作。

3) 最大值和最小值计算

Max()与min()方法，mysql优化的不是太好，比如要查找最小的actor_id，如下sql语句：

Selectmin(actor_id) from film_actor;

Actor_id是主键，但是也会做全表扫描。曲线救国的方式，来改变查询提交效率，就是移除min方法，改后的sql语句如下：

Select actor_idorder by desc limit 1;

但是，这条sql语句已经无法表达它的本意了，但是有时候为了获得更高的查询性能，不得不放弃一些原则。

4) 不能同时在同一个表上执行查询和更新

         如下sql语句：

Update tb1 asouter_tb1 set cnt=(select count(*) from tb1 as inner_tb1 where
inner_tb1.type=

Outer_tb1.type);

这条sql语句是无法执行的，不过可以通过生成表的形式来绕过上面的限制，因为mysql会把这个表当成临时表来处理。这实际上执行了两个查询：一个是子查询中的select语句，另一个是多表关联update，只是关联的表是一个临时表，子查询会在update语句打开表之前就完成，所以下面的sql能正常执行：

Update tb1 innerjoin(select type,count(*) as cnt from tb1 group by type) as
der using(type) set

Tb1.cnt=der.cnt;

3. 优化特定类型的查询

对于特定类型的查询，mysql具有相应的优化机制，当要使用这些特定类型的查询时，遵循优化机制，才能达到性能最佳。下面介绍这些特定类型的查询：

1) 优化count()查询

         Count()函数是用来统计记录行数或某列值的数量，只统计不为null的值，例如count

(name)，只统计name列不为null的个数，若name列都不是null，那么count(name)等价于

Count(*)。当为count(*)时，是统计所有的行数。


在没有任何where条件的count()函数，查询速率非常快，因为mysql此时无需计算实际的行数，而是利用存储引擎的特性直接获取这个值。因此带有where条件的查询的行数越多，count()方法效率就会越低，可以通过间接的方式优化此类情况的查询，例如下面的sql语句：

Select count(*)from city where id>5;

当id>5时，该查询需要扫描上万条数据时，而id<=5需要扫描的条数很少时，就可以通过如下的方式提高查询效率：

Select (selectcount(*) from city) –count(*) from city where id<=5;

这样就会大大降低扫描的行数，mysql查询优化阶段会把内部查询结果当成一个常量来处理。

         另外，我们也可以使用count来统计同一列不同内容的数量，如下sql语句：

Selectcount(color=’blue’ or null) as bluecount,count(color=’red’ or null )
asredcount from city;


对于不需要精确统计行数时，比如统计某网站的在线的活跃人数，那么就可以利用explain命令来获取近似值。如若要获取精确的行数，记录由非常多时，带有where的统计，效率肯定会低，并且使用索引也达不到要求时，这时就需要修改应用架构了，比如使用汇总表或外部缓存等。

2) 优化关联查询

当使用关联查询时，确保on或using子句中的列上有索引。确保group by和order
by表达式只涉及到一个表中的列，这样才能使用索引来优化这个过程。

3) 优化子查询

         优化子查询最重要的建议是尽可能的使用关联查询。但是在mysql5.6版本以上不用考虑这个问题。

4) 优化group by和distinct


Mysql内部优化器会相互转化这两种查询，它们都可以使用索引来来优化，而且索引优化是最优效的方式。使用标识列分组效率会比其它列更高，例如下面sql：

Selectactor.first_name,actor.last_name,count(*) from film_actor inner join
actorusing(actor_id)

Group byactor.fist_name,actor.last_name;

改成使用标识列分组，效率会更高，见下面sql：

Selectactor.first_name,actor.last_name,count(*) from film_actor inner join
actorusing(actor_id)

Group byactor.actor_id;

需要注意的是这种查询是利用了actor_id与first_name、last_name的直接关系，改写后结果不受影响。另外，这种sql写法在oracle是行不通的，会报ORA-00979错误，因此oracle要求分组列必须包括所有的非组合函数涉及到的列，对于mysql这种写法也不是都是可以是，可以通过将mysql的sql_mode设置为ONLY_FULL_GROUP_BY来禁止这种模式。对于以上的写法有时会造成查询结果不准确，比如有如下数据：

如果想找到每个class里面的最大的age，则需要使用group by和max。如下的sql语句，则输出结果有错误：

得到的结果，姓名和年龄并不对应，若sql_mode设置为ONLY_FULL_GROUP_BY，上述写法会直接报错的。可以采用下面的写法：

Select id,namefrom test inner join( select max(age),class from test group by
class) tusing(class);

或者

Select * from(select * from test order by age desc) as t group by class;

这两种方式，均用到子查询，成本就会有点高，因为子查询会创建临时表。

5) 优化limit分页

Mysql中使用limit m,n函数分页，其中m代表位置偏移量，n代表获取的行数。在使用limit分页时，通常加上order
by子句。如果有对应的索引的，效率一般会不错。但是若位置偏移量非常大时，比如limit
10000,20，那么mysql需要查询100020条记录，只返回最后20条记录，前面10000条记录都被抛弃掉，这样的代价很大，效率会很低。

优化此类分页查询最简单的方法就是尽可能的执行索引覆盖扫描，而不是查询所有的列，然后根据需要做一次关联操作再返回所需要的列。例如下面的例子：

Selectfilm_id,description from film order by title limit 10000,20;

那么查询改写成下面的形式：

Selectfilm_id,description from film inner join(select film_id from film order
bytitle limit 10000,20) as A using(film_id).

这样的方式就会大大提高查询效率，让mysql扫描尽可能少的页面，获取需要访问的记录后再根据关联列回原表查询所有的列。

Limit的分页效率问题，主要就是位置偏移量的问题，当位置偏移量很大时，mysql需要扫描大量的行并抛弃掉。如果可以使用书签记录上次取数据的位置，那么下次就可以直接从该书签记录的位置开始扫描，而不需要位置偏移量。例如，若需要按租借记录做翻页，那么可以根据最新一条租借记录向后追溯，这种方法可行是因为租借记录的主键是单调增加的。首先使用下面的sql获取第一组结果：

Select * fromrental order by rental_id desc limit 20;

假设上面的返回的的主键16049到16030的记录，那么下一页的查询sql为：

Select * fromrental where rental_id<16030 order by rental_id desc limit 20;

该技术的好处是无论翻到多少页，性能都是好的。

以上是通过学习《高性能mysql》这本书，根据个人理解，对一些关键的知识做了总结，以便以后翻看学习。俗话说“好脑袋不如烂笔头”，就是这个道理。

本文只是总结了基础的mysql性能优化的知识，更高级的性能优化，比如分区、分表等，请参考《高性能mysql》、mycat组件学习资料等。

热门工具换一换