开发者社区> 嗯哼9925> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

mysql sql优化实例

简介:
+关注继续查看
优化前:

pt-query-degist分析结果:

# Query 3: 0.00 QPS, 0.00x concurrency, ID 0xDC6E62FA021C85B5 at byte 628331
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.19
# Time range: 2016-09-24T15:14:24 to 2016-10-08T07:46:24
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         12      50
# Exec time      6    623s     10s     16s     12s     15s      2s     11s
# Lock time      0    28ms   176us    12ms   553us   568us     2ms   287us
# Rows sent      0     162       3       5    3.24    4.96    0.67    2.90
# Rows examine  11 776.54k  13.80k  16.19k  15.53k  15.96k  761.60  15.96k
# Query size     7  12.74k     261     261     261     261       0     261
# String:
# Databases    wechat_prod
# Hosts        localhost
# Users        test
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s
#  10s+  ################################################################
# Tables
#    SHOW TABLE STATUS FROM `wechat_prod` LIKE 'product'\G
#    SHOW CREATE TABLE `wechat_prod`.`product`\G
#    SHOW TABLE STATUS FROM `wechat_prod` LIKE 'sys_members'\G
#    SHOW CREATE TABLE `wechat_prod`.`sys_members`\G
#    SHOW TABLE STATUS FROM `wechat_prod` LIKE 'product_sku'\G
#    SHOW CREATE TABLE `wechat_prod`.`product_sku`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT `p`.`id`, `p`.`title`, `p`.`fare`, `p`.`sales`, `p`.`user_openid`, `u`.`nickname`, `s`.`price` FROM `product` `p` LEFT JOIN `sys_members` `u` ON p.user_openid = u.openid
 LEFT JOIN `product_sku` `s` ON s.product_id = p.id ORDER BY `wd_sort` LIMIT 3\G
sql 分析

mysql> EXPLAIN /*!50100 PARTITIONS*/
    -> SELECT `p`.`id`, `p`.`title`, `p`.`fare`, `p`.`sales`, `p`.`user_openid`, `u`.`nickname`, `s`.`price` FROM `product` `p` LEFT JOIN `sys_members` `u` ON p.user_openid = u.openid
    ->  LEFT JOIN `product_sku` `s` ON s.product_id = p.id ORDER BY `wd_sort` LIMIT 3\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: p
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2413
     filtered: 100.00
        Extra: Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: u
   partitions: NULL
         type: eq_ref
possible_keys: openid
          key: openid
      key_len: 152
          ref: wechat_prod.p.user_openid
         rows: 1
     filtered: 100.00
        Extra: Using where
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: s
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 518
     filtered: 100.00
        Extra: Using where; Using join buffer (Block Nested Loop)
3 rows in set, 2 warnings (0.00 sec)
product和product_sku表都没有使用索引。

其中product表的分析结果为Extra: Using temporary; Using filesort,此结果表示使用了临时文件排序,product_sku表的分析结果为Extra: Using where; Using join buffer (Block Nested Loop),而此结果表示使用了循环查找,扫描了518行。

product表表结构:

CREATE TABLE `product` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `title` varchar(64) DEFAULT NULL ,
  `description` varchar(1200) DEFAULT '' ,
  `cat_id` smallint(6) DEFAULT '1' ,
  `on_sell` tinyint(4) DEFAULT NULL,
  `sort` int(8) DEFAULT NULL ,
  `nice` tinyint(4) DEFAULT NULL ,
  `user_openid` varchar(32) DEFAULT NULL ,
  `is_return` tinyint(2) DEFAULT NULL ,
  `fare` tinyint(4) DEFAULT NULL ,
  `content` text COMMENT ,
  `add_time` int(11) DEFAULT NULL ,
  `sales` int(11) DEFAULT '0' ,
  `if_audit` tinyint(1) DEFAULT '1,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3321 DEFAULT CHARSET=utf8
product_sku表表结构:

CREATE TABLE `product_sku` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `product_id` bigint(20) DEFAULT NULL,
  `name` varchar(64) DEFAULT NULL ,
  `count` int(8) DEFAULT NULL ,
  `price` decimal(10,2) DEFAULT NULL ,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3367 DEFAULT CHARSET=utf8
添加索引

alter table product add index user_openid(user_openid);
alter table product_sku add index product_id(product_id);
分析添加索引后的查询情况

mysql> explain SELECT `p`.`id`, `p`.`title`, `p`.`fare`, `p`.`sales`, `p`.`user_openid`, `u`.`nickname`, `s`.`price` FROM `product` `p` LEFT JOIN `sys_members` `u` ON p.user_openid = u.openid LEFT JOIN `product_sku` `s` ON s.product_id = p.id LIMIT 3;
+----+-------------+-------+------------+--------+---------------+---------------+---------+--------------------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key           | key_len | ref                      | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------------+---------+--------------------------+------+----------+-------------+
|  1 | SIMPLE      | p     | NULL       | ALL    | NULL          | NULL          | NULL    | NULL                     | 2413 |   100.00 | NULL        |
|  1 | SIMPLE      | u     | NULL       | eq_ref | openid        | openid        | 152     | wechat_prod.p.user_openid |    1 |   100.00 | Using where |
|  1 | SIMPLE      | s     | NULL       | ref    | product_id    | product_id    | 9       | wechat_prod.p.id          |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+--------+---------------+---------------+---------+--------------------------+------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)
使用索引后,product_sku表只扫描了1行。

由平均的12s降为0.0几秒,几乎可以忽略不计。







本文转自秋楓博客园博客,原文链接:http://www.cnblogs.com/rwxwsblog/p/5943268.html,如需转载请自行联系原作者

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
mysql sql优化实例
mysql sql优化实例 优化前: pt-query-degist分析结果: # Query 3: 0.00 QPS, 0.00x concurrency, ID 0xDC6E62FA021C85B5 at byte 628331 # This item is included in the report because it matches --limit.
880 0
sqlServer存储过程
1、创建存储过程报错:     'CREATE/ALTER PROCEDURE' 必须是查询批次中的第一个语句。 解决方法: use databaseName 后面要加上一句: GO ...
817 0
SQL Server基础之<存储过程>
原文:SQL Server基础之   简单来说,存储过程就是一条或者多条sql语句的集合,可视为批处理文件,但是其作用不仅限于批处理。本篇主要介绍变量的使用,存储过程和存储函数的创建,调用,查看,修改以及删除操作。
1462 0
记一次SQL server 2005 到SQL server 2008 数据库迁移
    最近周末完成了一个从SQL server 2005升级到SQL server 2008的升级迁移,其实质是一个服务器迁移的过程。比较复杂的是这个服务器上的复制关系,自身既作为发布服务器,又作为订阅服务器,因此整个迁移同时需保证这些复制关系依旧可用。
1284 0
SQLSERVER存储过程语法详解
SQL SERVER存储过程语法: Create PROC [ EDURE ] procedure_name [ ; number ]     [ { @parameter data_type }         [ VARYING ] [ = default ] [ OUTPUT ]     ] [ ,...n ]   [ WITH     { RECOMPILE | ENCRY
1653 0
+关注
文章
问答
文章排行榜
最热
最新
相关电子书
更多
MySQL 5.7让优化更轻松
立即下载
MySQL表和索引优化实战
立即下载
MySQL查询优化实战
立即下载