mysql查询优化实战案例【实例较长建议慢慢学习】.doc

《mysql查询优化实战案例【实例较长建议慢慢学习】.doc》由会员分享，可在线阅读，更多相关《mysql查询优化实战案例【实例较长建议慢慢学习】.doc（28页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、1mysql 查询优化实战案例【实例较长，建议慢查询优化实战案例【实例较长，建议慢慢学习】慢学习】这篇文章主要给大家介绍了关于 MySQL 查询优化分析的相关资料，文中通过示例代码介绍的非常详细，对大家学习或者使用 MySQL 具有一定的参考学习价值，需要的朋友们下面来一起学习学习吧前言MySQL 是关系性数据库中的一种，查询功能强，数据一致性高，数据安全性高，支持二级索引。但性能方面稍逊于非关系性数据库，特别是百万级别以上的数据，很容易出现查询慢的现象。这时候需要分析查询慢的原因，一般情况下是程序员 sql 写的烂，或者是没有键索引，或者是索引失效等原因导致的。这时候 MySQL 提供的 E

2、XPLAIN 命令就尤其重要,它可以对 SELECT语句进行分析,并输出 SELECT 执行的详细信息,以供开发人员针对性优化.而且就在查询语句前加上 Explain 就成:2EXPLAIN SELECT*FROM customer WHERE id lt;100;准备首先需要建立两个测试用表及数据:CREATE TABLE customer(id BIGINT(20)unsigned NOT NULL AUTO_INCREMENT,name VARCHAR(50)NOT NULL DEFAULT#39;#39;,age INT(11)unsigned DEFAULT NULL,PRIMARY

3、 KEY(id),KEY name_index(name)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4INSERT INTO customer(name,age)VALUES(#39;a#39;,1);INSERT INTO customer(name,age)VALUES(#39;b#39;,2);INSERT INTO customer(name,age)VALUES(#39;c#39;,3);INSERT INTO customer(name,age)VALUES(#39;d#39;,4);INSERT INTO customer(name,age)VALU

4、ES(#39;e#39;,5);INSERT INTO customer(name,age)VALUES(#39;f#39;,6);INSERT INTO customer(name,age)VALUES(#39;g#39;,7);3INSERT INTO customer(name,age)VALUES(#39;h#39;,8);INSERT INTO customer(name,age)VALUES(#39;i#39;,9);CREATE TABLE orders(id BIGINT(20)unsigned NOT NULL AUTO_INCREMENT,user_id BIGINT(20

5、)unsigned NOT NULL DEFAULT 0,product_nameVARCHAR(50)NOT NULL DEFAULT#39;#39;,productor VARCHAR(30)NOT NULL DEFAULT#39;#39;,PRIMARY KEY(id),KEYuser_product_detail_index(user_id,product_name,productor)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4INSERT INTO orders(user_id,product_name,productor)VALUES(1,#39;p

6、1#39;,#39;WHH#39;);INSERT INTO orders(user_id,product_name,productor)VALUES(1,#39;p2#39;,#39;WL#39;);INSERT INTO orders(user_id,product_name,productor)VALUES(1,#39;p1#39;,#39;DX#39;);4INSERT INTO orders(user_id,product_name,productor)VALUES(2,#39;p1#39;,#39;WHH#39;);INSERT INTO orders(user_id,produc

7、t_name,productor)VALUES(2,#39;p5#39;,#39;WL#39;);INSERT INTO orders(user_id,product_name,productor)VALUES(3,#39;p3#39;,#39;MA#39;);INSERT INTO orders(user_id,product_name,productor)VALUES(4,#39;p1#39;,#39;WHH#39;);INSERT INTO orders(user_id,product_name,productor)VALUES(6,#39;p1#39;,#39;WHH#39;);INS

8、ERT INTO orders(user_id,product_name,productor)VALUES(9,#39;p8#39;,#39;TE#39;);EXPLAIN 输出格式EXPLAIN 命令的输出内容大致如下:mysql explain select*from customer where id=1G*1.row*id:15select_type:SIMPLEtable:customerpartitions:NULLtype:constpossible_keys:PRIMARYkey:PRIMARYkey_len:8ref:constrows:1filtered:100.00Ext

9、ra:NULL1 row in set,1 warning(0.00 sec)各列的含义如下:id:SELECT 查询的标识符.每个 SELECT 都会自动分配一个唯一的标6识符.select_type:SELECT 查询的类型.table:查询的是哪个表 partitions:匹配的分区 type:join 类型possible_keys:此次查询中可能选用的索引key:此次查询中确切使用到的索引.ref:哪个字段或常数与 key 一起被使用rows:显示此查询一共扫描了多少行.这个是一个估计值.filtered:表示此查询条件所过滤的数据的百分比extra:额外的信息接下来我们来重点看一下

10、比较重要的几个字段.select_typeSIMPLE mdash;mdash;简单的 select 查询，查询中不包含子查询或者UNIONPRIMARY mdash;mdash;查询中若包含任何复杂的子查询，最外层查询则被标记为 primaryUNION mdash;mdash;表示此查询是 UNION 的第二或随后的查询DEPENDENT UNION mdash;mdash;UNION 中的第二个或后面的查询语句,取决于外面的查询UNION RESULT mdash;mdash;从 UNION 表获取结果的 select 结果7DERIVED mdash;mdash;在 from 列表中包

11、含的子查询被标记为derived（衍生）MySQL 会递归执行这些子查询，把结果放在临时表里。SUBQUERY mdash;mdash;在 select 或 where 列表中包含了子查询DEPENDENT SUBQUERY mdash;mdash;子查询中的第一个 SELECT,取决于外面的查询.即子查询依赖于外层查询的结果.最常见的查询类别应该是 SIMPLE 了,比如当我们的查询没有子查询,也没有 UNION 查询时,那么通常就是 SIMPLE 类型,例如:mysql explain select*from customer where id=2G*1.row*id:1select_ty

12、pe:SIMPLEtable:customerpartitions:NULLtype:constpossible_keys:PRIMARYkey:PRIMARY8key_len:8ref:constrows:1filtered:100.00Extra:NULL1 row in set,1 warning(0.00 sec)如果我们使用了 UNION 查询,那么 EXPLAIN 输出 的结果类似如下:mysql EXPLAIN(SELECT*FROM customer WHERE id IN(1,2,3)-UNION-(SELECT*FROM customer WHERE id IN(3,4,5

13、);+-+-+-+-+-+-+-+-+-+-+-+-+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+-+-+-+-+-+-+-+-+-+-+-+-+9|1|PRIMARY|customer|NULL|range|PRIMARY|PRIMARY|8|NULL|3|100.00|Using where|2|UNION|customer|NULL|range|PRIMARY|PRIMARY|8|NULL|3|100.00|Using where|NULL|UNION R

14、ESULT|lt;union1,2|NULL|ALL|NULL|NULL|NULL|NULL|NULL|NULL|Using temporary|+-+-+-+-+-+-+-+-+-+-+-+-+3 rows in set,1 warning(0.00 sec)table表示查询涉及的表或衍生表typetype 字段比较重要,它提供了判断查询是否高效的重要依据依据.通过 type 字段,我们判断此次查询是 全表扫描 还是 索引扫描等.type 常用类型type 常用的取值有:system:表中只有一条数据.这个类型是特殊的 const 类型.const:针对主键或唯一索引的等值查询扫描,最多只

15、返回一行数据.10const 查询速度非常快,因为它仅仅读取一次即可.例如下面的这个查询,它使用了主键索引,因此 type 就是 const 类型的.mysql explain select*from customer where id=2G*1.row*id:1select_type:SIMPLEtable:customerpartitions:NULLtype:constpossible_keys:PRIMARYkey:PRIMARYkey_len:8ref:constrows:1filtered:100.00Extra:NULL1 row in set,1 warning(0.00 se

16、c)11eq_ref:此类型通常出现在多表的 join 查询,表示对于前表的每一个结果,都只能匹配到后表的一行结果.并且查询的比较操作通常是=,查询效率较高.例如:mysql EXPLAIN SELECT*FROM customer,order_info WHERE customer.id=order_info.user_idG*1.row*id:1select_type:SIMPLEtable:order_infopartitions:NULLtype:indexpossible_keys:user_product_detail_indexkey:user_product_detail_in

17、dexkey_len:314ref:NULLrows:9filtered:100.0012Extra:Using where;Using index*2.row*id:1select_type:SIMPLEtable:customerpartitions:NULLtype:eq_refpossible_keys:PRIMARYkey:PRIMARYkey_len:8ref:test.order_info.user_idrows:1filtered:100.00Extra:NULL2 rows in set,1 warning(0.00 sec)ref:此类型通常出现在多表的 join 查询,针

18、对于非唯一或非主键索引,或者是使用了 最左前缀 规则索引的查询.例如下面这个例子中,就使用到了 ref 类型的查询:13mysql EXPLAIN SELECT*FROM customer,order_info WHERE customer.id=order_info.user_id AND order_info.user_id=5G*1.row*id:1select_type:SIMPLEtable:customerpartitions:NULLtype:constpossible_keys:PRIMARYkey:PRIMARYkey_len:8ref:constrows:1filtered

19、:100.00Extra:NULL*2.row*14id:1select_type:SIMPLEtable:order_infopartitions:NULLtype:refpossible_keys:user_product_detail_indexkey:user_product_detail_indexkey_len:9ref:constrows:1filtered:100.00Extra:Using index2 rows in set,1 warning(0.01 sec)range:表示使用索引范围查询,通过索引字段范围获取表中部分数据记录.这个类型通常出现在=,lt;,=,lt;

20、,lt;=,IS NULL,lt;=,BETWEEN,IN()操作中.当 type 是 range 时,那么 EXPLAIN 输出的 ref 字段为NULL,并且 key_len 字段是此次查询中使用到的索引的最长的那个.例如下面的例子就是一个范围查询:15mysql EXPLAIN SELECT*FROM customer WHERE id BETWEEN 2 AND 8G*1.row*id:1select_type:SIMPLEtable:customerpartitions:NULLtype:rangepossible_keys:PRIMARYkey:PRIMARYkey_len:8re

21、f:NULLrows:7filtered:100.00Extra:Using where1 row in set,1 warning(0.00 sec)index:表示全索引扫描(full index scan),和 ALL 类型类似,只不过16ALL 类型是全表扫描,而 index 类型则仅仅扫描所有的索引,而不扫描数据.index 类型通常出现在:所要查询的数据直接在索引树中就可以获取到,而不需要扫描数据.当是这种情况时,Extra 字段 会显示 Usingindex.例如:mysql EXPLAIN SELECT name FROM customer G*1.row*id:1select

22、_type:SIMPLEtable:customerpartitions:NULLtype:indexpossible_keys:NULLkey:name_indexkey_len:152ref:NULL17rows:10filtered:100.00Extra:Using index1 row in set,1 warning(0.00 sec)上面的例子中,我们查询的 name 字段恰好是一个索引,因此我们直接从索引中获取数据就可以满足查询的需求了,而不需要查询表中的数据.因此这样的情况下,type 的值是 index,并且 Extra 的值是 Using index.ALL:表示全表扫描

23、,这个类型的查询是性能最差的查询之一.通常来说,我们的查询不应该出现 ALL 类型的查询,因为这样的查询在数据量大的情况下,对数据库的性能是巨大的灾难.如一个查询是ALL 类型查询,那么一般来说可以对相应的字段添加索引来避免.下面是一个全表扫描的例子,可以看到,在全表扫描时,possible_keys和 key 字段都是 NULL,表示没有使用到索引,并且 rows 十分巨大,因此整个查询效率是十分低下的.mysqlEXPLAINSELECTageFROMcustomerWHEREage=20G*1.row18*id:1select_type:SIMPLEtable:customerparti

24、tions:NULLtype:ALLpossible_keys:NULLkey:NULLkey_len:NULLref:NULLrows:10filtered:10.00Extra:Using where1 row in set,1 warning(0.00 sec)type 类型的性能比较通常来说,不同的 type 类型的性能关系如下:ALL lt;index lt;range index_merge lt;ref lt;eq_ref lt;const lt;systemALL 类型因为是全表扫描,因此在相同的查询条件下,它是速度最慢的.19而 index 类型的查询虽然不是全表扫描,但是它

25、扫描了所有的索引,因此比 ALL 类型的稍快.后面的几种类型都是利用了索引来查询数据,因此可以过滤部分或大部分数据,因此查询效率就比较高了.对程序员来说，若保证查询至少达到 range 级别或者最好能达到 ref则算是一个优秀而又负责的程序员。ALL：（full table scan）全表扫描无疑是最差，若是百万千万级数据量，全表扫描会非常慢。index：（full index scan）全索引文件扫描比 all 好很多，毕竟从索引树中找数据，比从全表中找数据要快。range：只检索给定范围的行，使用索引来匹配行。范围缩小了，当然比全表扫描和全索引文件扫描要快。sql 语句中一般会有 betw

26、een，in，lt;等查询。ref：非唯一性索引扫描，本质上也是一种索引访问，返回所有匹配某个单独值的行。比如查询公司所有属于研发团队的同事，匹配的结果是多个并非唯一值。eq_ref：唯一性索引扫描，对于每个索引键，表中有一条记录与之匹配。比如查询公司的 CEO，匹配的结果只可能是一条记录，const：表示通过索引一次就可以找到，const 用于比较 primary key 或者 unique 索引。因为只匹配一行数据，所以很快，若将主键至于 where20列表中，MySQL 就能将该查询转换为一个常量。system：表只有一条记录（等于系统表），这是 const 类型的特列，平时不会出现，了

27、解即可possible_keyspossible_keys 表示 MySQL 在查询时,能够使用到的索引.注意,即使有些索引在 possible_keys 中出现,但是并不表示此索引会真正地被 MySQL 使用到.MySQL 在查询时具体使用了哪些索引,由 key字段决定.key此字段是 MySQL 在当前查询时所真正使用到的索引.key_len表示查询优化器使用了索引的字节数.这个字段可以评估组合索引是否完全被使用,或只有最左部分字段被使用到.key_len 的计算规则如下:字符串char(n):n 字节长度varchar(n):如果是 utf8 编码,则是 3n+2 字节;如果是 utf8

28、mb4 编码,则是 4n+2 字节.21数值类型:TINYINT:1 字节SMALLINT:2 字节MEDIUMINT:3 字节INT:4 字节BIGINT:8 字节时间类型DATE:3 字节TIMESTAMP:4 字节DATETIME:8 字节字段属性:NULL 属性 占用一个字节.如果一个字段是 NOT NULL 的,则没有此属性.我们来举两个简单的栗子:mysql EXPLAIN SELECT*FROM order_info WHERE user_id lt;3 ANDproduct_name=#39;p1#39;AND productor=#39;WHH#39;G*1.row*id:1

29、22select_type:SIMPLEtable:order_infopartitions:NULLtype:rangepossible_keys:user_product_detail_indexkey:user_product_detail_indexkey_len:9ref:NULLrows:5filtered:11.11Extra:Using where;Using index1 row in set,1 warning(0.00 sec)上面的例子是从表 order_info 中查询指定的内容,而我们从此表的建表语句中可以知道,表 order_info 有一个联合索引:KEYuse

30、r_product_detail_index(user_id,product_name,productor)23不过此查询语句 WHERE user_id lt;3 AND product_name=#39;p1#39;AND productor=#39;WHH#39;中,因为先进行 user_id 的范围查询,而根据 最左前缀匹配 原则,当遇到范围查询时,就停止索引的匹配,因此实际上我们使用到的索引的字段只有 user_id,因此在 EXPLAIN中,显示的 key_len 为 9.因为 user_id 字段是 BIGINT,占用 8 字节,而 NULL 属性占用一个字节,因此总共是 9 个

31、字节.若我们将user_id 字 段 改 为BIGINT(20)NOT NULL DEFAULT#39;0#39;,则key_length 应该是 8.上面因为 最左前缀匹配 原则,我们的查询仅仅使用到了联合索引的 user_id 字段,因此效率不算高.接下来我们来看一下下一个例子:mysql EXPLAIN SELECT*FROM order_info WHERE user_id=1 ANDproduct_name=#39;p1#39;G;*1.row*id:1select_type:SIMPLEtable:order_info24partitions:NULLtype:refpossibl

32、e_keys:user_product_detail_indexkey:user_product_detail_indexkey_len:161ref:const,constrows:2filtered:100.00Extra:Using index1 row in set,1 warning(0.00 sec)这次的查询中,我们没有使用到范围查询,key_len 的值为 161.为什么呢?因为我们的查询条件 WHERE user_id=1 AND product_name=#39;p1#39;中,仅仅使用到了联合索引中的前两个字段,因此keyLen(user_id)+keyLen(produ

33、ct_name)=9+50*3+2=161rowsrows 也是一个重要的字段.MySQL 查询优化器根据统计信息,估算SQL 要查找到结果集需要扫描读取的数据行数.这个值非常直观显示 SQL 的效率好坏,原则上 rows 越少越好.Extra25EXplain 中的很多额外的信息会在 Extra 字段显示,常见的有以下几种内容:Using filesort当 Extra 中有 Using filesort 时,表示 MySQL 需额外的排序操作,不能通过索引顺序达到排序效果.一般有 Using filesort,都建议优化去掉,因为这样的查询 CPU 资源消耗大.例如下面的例子:mysql

34、EXPLAIN SELECT*FROM order_info WHERE user_id=1 ANDproduct_name=#39;p1#39;G;*1.row*id:1select_type:SIMPLEtable:order_infopartitions:NULLtype:refpossible_keys:user_product_detail_indexkey:user_product_detail_index26key_len:161ref:const,constrows:2filtered:100.00Extra:Using index1 row in set,1 warning(

35、0.00 sec)我们的索引是KEYuser_product_detail_index(user_id,product_name,productor)但是上面的查询中根据 product_name 来排序,因此不能使用索引进行优化,进而会产生 Using filesort.如果我们将排序依据改为 ORDER BY user_id,product_name,那么就不会出现 Using filesort 了.例如:mysqlEXPLAINSELECT*FROMorder_infoORDERBYuser_id,27product_name G*1.row*id:1select_type:SIMPLE

36、table:order_infopartitions:NULLtype:indexpossible_keys:NULLkey:user_product_detail_indexkey_len:253ref:NULLrows:9filtered:100.00Extra:Using index1 row in set,1 warning(0.00 sec)Using index覆盖索引扫描,表示查询在索引树中就可查找所需数据,不用扫描表28数据文件,往往说明性能不错Using temporary查询有使用临时表,一般出现于排序,分组和多表 join 的情况,查询效率不高,建议优化.总结以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，谢谢大家对脚本之家的支持。