开发者社区> 嗯哼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性能瓶颈排查定位
优化系列 | 实例解析MySQL性能瓶颈排查定位
0 0
MySQL SELECT LIMIT 实例
MySQL SELECT LIMIT 实例
0 0
Mysql 8.0 C API连接和获取数据实例(附解决乱码的彩蛋)
Mysql 8.0 C API连接和获取数据实例(附解决乱码的彩蛋)
0 0
MySQL数据库快速入门到精通(超详细保姆级,建议收藏)这可能是目前最适合你的教程,从基础语法到实例演示。
此文章旨在为需要掌握快速开发和复习MySQL的同学所准备,您完全可以把此文章当作参考文档来使用,本文将尽量精简,使您快速的理解和掌握语法。
0 0
k8s-部署实例(mysql+wordpress)
安装mysql 安装wordpress 部署ingress 进行流量负载控制
0 0
Spring MVC实现mysql数据库增删改查完整实例
Spring MVC实现mysql数据库增删改查完整实例
0 0
用实例带你了解 MySQL 全局锁
MySQL全局锁会申请一个全局的读锁,对整个库加锁。
0 0
[MySQL FAQ]系列 — 如何安全地关闭MySQL实例
[MySQL FAQ]系列 — 如何安全地关闭MySQL实例
0 0
MySQL---数据库从入门走向大神系列(十七)-JavaWeb分页技术实例演示2
MySQL---数据库从入门走向大神系列(十七)-JavaWeb分页技术实例演示2
0 0
MySQL---数据库从入门走向大神系列(十六)-JavaWeb分页技术实例演示1
MySQL---数据库从入门走向大神系列(十六)-JavaWeb分页技术实例演示1
0 0
+关注
文章
问答
文章排行榜
最热
最新
相关电子书
更多
让 MySQL 原生分布式触手可及
立即下载
好的 MySQL 兼容可以做到什么程度
立即下载
云数据库RDS MySQL从入门到高阶
立即下载