mysql sql优化实例

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
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,如需转载请自行联系原作者

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
打赏
0
0
0
0
60
分享
相关文章
【YashanDB知识库】如何将mysql含有group by的SQL转换成崖山支持的SQL
本文探讨了在YashanDB(崖山数据库)中执行某些SQL语句时出现的报错问题,对比了MySQL的成功执行结果。问题源于SQL-92标准对非聚合列的严格限制,要求这些列必须出现在GROUP BY子句中,而SQL:1999及更高版本允许非聚合列直接出现在选择列中。YashanDB和Oracle遵循SQL-92标准,因此会报错。文章提供了两种解决方法:使用聚合函数处理非聚合列,或将GROUP BY与ORDER BY拆分为两层查询。最后总结指出,SQL-92标准更为严谨合理,建议开发者遵循此规范以避免潜在问题。
基于SQL Server / MySQL进行百万条数据过滤优化方案
对百万级别数据进行高效过滤查询,需要综合使用索引、查询优化、表分区、统计信息和视图等技术手段。通过合理的数据库设计和查询优化,可以显著提升查询性能,确保系统的高效稳定运行。
39 9
MySQL的架构与SQL语句执行过程
MySQL架构分为Server层和存储引擎层,具有高度灵活性和可扩展性。Server层包括连接器、查询缓存(MySQL 8.0已移除)、分析器、优化器和执行器,负责处理SQL语句;存储引擎层负责数据的存储和读取,常见引擎有InnoDB、MyISAM和Memory。SQL执行过程涉及连接、解析、优化、执行和结果返回等步骤,本文详细讲解了一条SQL语句的完整执行过程。
38 3
MySQL和SQLSugar百万条数据查询分页优化
在面对百万条数据的查询时,优化MySQL和SQLSugar的分页性能是非常重要的。通过合理使用索引、调整查询语句、使用缓存以及采用高效的分页策略,可以显著提高查询效率。本文介绍的技巧和方法,可以为开发人员在数据处理和查询优化中提供有效的指导,提升系统的性能和用户体验。掌握这些技巧后,您可以在处理海量数据时更加游刃有余。
83 9
图解MySQL【日志】——磁盘 I/O 次数过高时优化的办法
当 MySQL 磁盘 I/O 次数过高时,可通过调整参数优化。控制刷盘时机以降低频率:组提交参数 `binlog_group_commit_sync_delay` 和 `binlog_group_commit_sync_no_delay_count` 调整等待时间和事务数量;`sync_binlog=N` 设置 write 和 fsync 频率,`innodb_flush_log_at_trx_commit=2` 使提交时只写入 Redo Log 文件,由 OS 择机持久化,但两者在 OS 崩溃时有丢失数据风险。
39 3
【YashanDB 知识库】如何将 mysql 含有 group by 的 SQL 转换成崖山支持的 SQL
在崖山数据库中执行某些 SQL 语句时出现报错(YAS-04316 not a single-group group function),而这些语句在 MySQL 中能成功执行。原因是崖山遵循 SQL-92 标准,不允许选择列表中包含未在 GROUP BY 子句中指定的非聚合列,而 MySQL 默认允许这种操作。解决办法包括:使用聚合函数处理非聚合列或拆分查询为两层,先进行 GROUP BY 再排序。总结来说,SQL-92 更严格,确保数据一致性,MySQL 在 5.7 及以上版本也默认遵循此标准。
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.
992 0
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
|
8月前
|
SQL Server的并行实施如何优化?
【7月更文挑战第23天】SQL Server的并行实施如何优化?
208 13
|
8月前
|
SQL
解锁 SQL Server 2022的时间序列数据功能
【7月更文挑战第14天】要解锁SQL Server 2022的时间序列数据功能,可使用`generate_series`函数生成整数序列,例如:`SELECT value FROM generate_series(1, 10)。此外,`date_bucket`函数能按指定间隔(如周)对日期时间值分组,这些工具结合窗口函数和其他时间日期函数,能高效处理和分析时间序列数据。更多信息请参考官方文档和技术资料。
123 9
AI助理

你好,我是AI助理

可以解答问题、推荐解决方案等