mysql 优化实例之索引创建

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
简介:
优化前:

pt-query-degist分析结果:

# Query 23: 0.00 QPS, 0.00x concurrency, ID 0x78761E301CC7EE47 at byte 394687
# This item is included in the report because it matches --limit.
# Scores: V/M = 3.27
# Time range: 2016-09-29T11:46:22 to 2016-10-01T12:45:02
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0       3
# Exec time      0     78s     19s     39s     26s     39s      9s     19s
# Lock time      0   328us    66us   136us   109us   131us    30us   125us
# Rows sent      0       6       1       3       2    2.90    0.78    1.96
# Rows examine   0       6       1       3       2    2.90    0.78    1.96
# Query size     0     348     116     116     116     116       0     116
# 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 'sys_files'\G
#    SHOW CREATE TABLE `wechat_prod`.`sys_files`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT `id`, `url_small`, `url_big`, `bus_id` FROM `sys_files` WHERE (`bus_type`='wp_goods') AND (`bus_id`='32597')\G
表结构

CREATE TABLE `sys_files` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `url_small` varchar(255) DEFAULT NULL,
  `url_big` varchar(255) DEFAULT NULL,
  `bus_type` varchar(255) DEFAULT NULL,
  `bus_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_name` (`bus_type`),
  KEY `index_name2` (`bus_id`)
) ENGINE=InnoDB AUTO_INCREMENT=207750 DEFAULT CHARSET=utf8
sql执行分析

mysql> explain SELECT `id`, `url_small`, `url_big`, `bus_id` FROM `sys_files` WHERE (`bus_type`='wp_goods') AND (`bus_id`='32597');
+----+-------------+------------+------------+------+------------------------+-------------+---------+-------+------+----------+------------------------------------+
| id | select_type | table      | partitions | type | possible_keys          | key         | key_len | ref   | rows | filtered | Extra                              |
+----+-------------+------------+------------+------+------------------------+-------------+---------+-------+------+----------+------------------------------------+
|  1 | SIMPLE      | sys_files | NULL       | ref  | index_name,index_name2 | index_name2 | 9       | const |    3 |    50.00 | Using index condition; Using where |
+----+-------------+------------+------------+------+------------------------+-------------+---------+-------+------+----------+------------------------------------+
1 row in set, 1 warning (0.00 sec)
根据业务逻辑,索引创建错误:不应该创建两个单独的索引bus_type和bus_id,此处虽然创建了两个索引,但真正用到的索引只是index_name2,索引index_name没有任何作用。会占用空间,并影响写入和更新的性能。

alter table sys_files drop index index_name;
修改后索引使用情况:

mysql> explain SELECT `id`, `url_small`, `url_big`, `bus_id` FROM `sys_files` WHERE (`bus_type`='wp_goods') AND (`bus_id`='32597');
+----+-------------+------------+------------+------+---------------+-------------+---------+-------+------+----------+------------------------------------+
| id | select_type | table      | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra                              |
+----+-------------+------------+------------+------+---------------+-------------+---------+-------+------+----------+------------------------------------+
|  1 | SIMPLE      | sys_files | NULL       | ref  | index_name2   | index_name2 | 9       | const |    3 |    10.00 | Using index condition; Using where |
+----+-------------+------------+------------+------+---------------+-------------+---------+-------+------+----------+------------------------------------+
1 row in set, 1 warning (0.00 sec)
sql写法

SELECT `id`, `url_small`, `url_big`, `bus_id` FROM `sys_files` WHERE (`bus_type`='wp_goods') AND (`bus_id`='32597')
改为

SELECT `id`, `url_small`, `url_big`, `bus_id` FROM `sys_files` WHERE (`bus_id`='32597') AND (`bus_type`='wp_goods')
sql编写的原则:把辨识度高的重复率低的写在左边。






本文转自秋楓博客园博客,原文链接:http://www.cnblogs.com/rwxwsblog/p/5945060.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
分享
相关文章
MySQL底层概述—8.JOIN排序索引优化
本文主要介绍了MySQL中几种关键的优化技术和概念,包括Join算法原理、IN和EXISTS函数的使用场景、索引排序与额外排序(Using filesort)的区别及优化方法、以及单表和多表查询的索引优化策略。
MySQL底层概述—8.JOIN排序索引优化
MySQL底层概述—7.优化原则及慢查询
本文主要介绍了:Explain概述、Explain详解、索引优化数据准备、索引优化原则详解、慢查询设置与测试、慢查询SQL优化思路
115 15
MySQL底层概述—7.优化原则及慢查询
MySQL底层概述—6.索引原理
本文详细回顾了:索引原理、二叉查找树、平衡二叉树(AVL树)、红黑树、B-Tree、B+Tree、Hash索引、聚簇索引与非聚簇索引。
MySQL底层概述—6.索引原理
基于SQL Server / MySQL进行百万条数据过滤优化方案
对百万级别数据进行高效过滤查询,需要综合使用索引、查询优化、表分区、统计信息和视图等技术手段。通过合理的数据库设计和查询优化,可以显著提升查询性能,确保系统的高效稳定运行。
35 9
MySQL和SQLSugar百万条数据查询分页优化
在面对百万条数据的查询时,优化MySQL和SQLSugar的分页性能是非常重要的。通过合理使用索引、调整查询语句、使用缓存以及采用高效的分页策略,可以显著提高查询效率。本文介绍的技巧和方法,可以为开发人员在数据处理和查询优化中提供有效的指导,提升系统的性能和用户体验。掌握这些技巧后,您可以在处理海量数据时更加游刃有余。
80 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 崩溃时有丢失数据风险。
38 3
Docker Compose V2 安装常用数据库MySQL+Mongo
以上内容涵盖了使用 Docker Compose 安装和管理 MySQL 和 MongoDB 的详细步骤,希望对您有所帮助。
176 42
MySQL生产环境迁移至YashanDB数据库深度体验
这篇文章是作者将 MySQL 生产环境迁移至 YashanDB 数据库的深度体验。介绍了 YashanDB 迁移平台 YMP 的产品相关信息、安装步骤、迁移中遇到的各种兼容问题及解决方案,最后总结了迁移体验,包括工具部署和操作特点,也指出功能有优化空间及暂不支持的部分,期待其不断优化。
如何排查和解决PHP连接数据库MYSQL失败写锁的问题
通过本文的介绍,您可以系统地了解如何排查和解决PHP连接MySQL数据库失败及写锁问题。通过检查配置、确保服务启动、调整防火墙设置和用户权限,以及识别和解决长时间运行的事务和死锁问题,可以有效地保障应用的稳定运行。
134 25
AI助理

你好,我是AI助理

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