mysql sql优化实例

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介:
优化前:

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,如需转载请自行联系原作者

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
2天前
|
SQL 缓存 关系型数据库
下次老板问你MySQL如何优化时,你可以这样说,老板默默给你加工资
【5月更文挑战第20天】下次老板问你MySQL如何优化时,你可以这样说,老板默默给你加工资
23 3
|
4天前
|
SQL 监控 关系型数据库
【PolarDB开源】PolarDB SQL优化实践:提升查询效率与资源利用
【5月更文挑战第24天】PolarDB是高性能的云原生数据库,强调SQL查询优化以提升性能。本文分享了其SQL优化策略,包括查询分析、索引优化、查询重写、批量操作和并行查询,以及性能监控与调优方法。通过这些措施,可以减少响应时间、提高并发处理能力和降低成本。文中还提供了相关示例代码,展示如何分析查询和创建索引,帮助用户实现更高效的数据库管理。
21 1
|
4天前
|
SQL 关系型数据库 MySQL
mysql插入500条数据sql语句
【5月更文挑战第12天】
|
5天前
|
SQL 存储 关系型数据库
MySQL进阶-增删查改(全网最详细sql教学)-3
MySQL进阶-增删查改(全网最详细sql教学)
14 0
|
5天前
|
SQL 关系型数据库 MySQL
MySQL进阶-增删查改(全网最详细sql教学)-2
MySQL进阶-增删查改(全网最详细sql教学)
11 0
|
5天前
|
SQL NoSQL 关系型数据库
MySQL进阶-增删查改(全网最详细sql教学)-1
MySQL进阶-增删查改(全网最详细sql教学)
12 0
|
5月前
|
SQL 存储 关系型数据库
MySQL下使用SQL命令进行表结构与数据复制实践
MySQL下使用SQL命令进行表结构与数据复制实践
71 0
|
10月前
|
SQL 关系型数据库 MySQL
MySQl数据库第八课-------SQL命令查询-------主要命脉2
MySQl数据库第八课-------SQL命令查询-------主要命脉
|
10月前
|
SQL 关系型数据库 MySQL
MySQl数据库第八课-------SQL命令查询-------主要命脉 1
MySQl数据库第八课-------SQL命令查询-------主要命脉
|
10月前
|
SQL 存储 开发框架
MySQl数据库第六课-------SQl命令的延续------快来看看
MySQl数据库第六课-------SQl命令的延续------快来看看