首页 数据库 mysql教程 一个优化MySQL查询操作的具体案例分析_MySQL

一个优化MySQL查询操作的具体案例分析_MySQL

Jun 01, 2016 pm 01:00 PM
mysql

问题描述

一个用户反映先线一个SQL语句执行时间慢得无法接受。SQL语句看上去很简单(本文描述中修改了表名和字段名):
SELECT count(*) FROM a JOIN b ON a.`S` = b.`S` WHERE a.`L` > '2014-03-30 00:55:00' AND a.`L`

且查询需要的字段都建了索引,表结构如下:

CREATE TABLE `a` (
`L` timestamp NOT NULL DEFAULT '2000-01-01 00:00:00',
`I` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`A` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`S` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`F` tinyint(4) DEFAULT NULL,
`V` varchar(256) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '',
`N` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
KEY `IX_L` (`L`),
KEY `IX_I` (`I`),
KEY `IX_S` (`S`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `b` (
`R` timestamp NOT NULL DEFAULT '2000-01-01 00:00:00',
`V` varchar(32) DEFAULT NULL,
`U` varchar(32) DEFAULT NULL,
`C` varchar(16) DEFAULT NULL,
`S` varchar(64) DEFAULT NULL,
`I` varchar(64) DEFAULT NULL,
`E` bigint(32) DEFAULT NULL,
`ES` varchar(128) DEFAULT NULL,
KEY `IX_R` (`R`),
KEY `IX_C` (`C`),
KEY `IX_S` (`S`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

登录后复制

从语句看,这个查询计划很自然的,就应该是先用a作为驱动表,先后使用 a.L和b.S这两个索引。而实际上explain的结果却是:

+----+-------------+-------+-------+---------------+------+---------+----------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+----------+---------+-------------+
| 1 | SIMPLE | b | index | IX_S | IX_S | 195 | NULL | 1038165 | Using index |
| 1 | SIMPLE | a | ref | IX_L,IX_S | IX_S | 195 | test.b.S | 1 | Using where |
+----+-------------+-------+-------+---------------+------+---------+----------+---------+-------------+
登录后复制

分析

从explain的结果看,查询用了b作为驱动表。

上一篇文章我们介绍到,MySQL选择jion顺序是分别分析各种join顺序的代价后,选择最小代价的方法。

这个join只涉及到两个表,自然也与optimizer_search_depth无关。于是我们的问题就是,我们预期的那个join顺序的为什么没有被选中?

MySQL Tips: MySQL提供straight_join语法,强制设定连接顺序。

explain SELECT count(*) FROM a straight_join b ON a.`S` = b.`S` WHERE a.`L` > '2014-03-30 00:55:00' AND a.`L` < '2014-03-30 01:00:00' ;

+----+-------------+-------+-------+---------------+------+---------+------+---------+---------------------------------------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+-------+-------+---------------+------+---------+------+---------+---------------------------------------------+

| 1 | SIMPLE | a | range | IX_L,IX_S | IX_L | 4 | NULL | 63 | Using where |

| 1 | SIMPLE | b | index | IX_S | IX_S | 195 | NULL | 1038165 | Using where; Using index; Using join buffer |

+----+-------------+-------+-------+---------------+------+---------+------+---------+---------------------------------------------+

登录后复制

MySQL Tips: explain结果中,join的查询代价可以用依次连乘rows估算。

?join顺序对了,简单的分析查询代价:普通join是1038165*1, straight_join是 63*1038165. 貌似MySQL没有错。但一定哪里不对!

发现异常

回到我们最初的设想。我们预计表a作为驱动表,是因为认为表b能够用上IX_S索引,而实际上staight_join的时候确实用上了,但这个结果与我们预期的又不同。

我们知道,索引的过滤性是决定了一个索引在查询中是否会被选中的重要因素,那么是不是b.S的过滤性不好呢?

MySQL Tips: show index from tbname返回结果中Cardinality的值可以表明一个索引的过滤性。

show index的结果太多,也可以从information_schema表中取。

mysql> select * from information_schema.STATISTICS where table_name='b' and index_name='IX_S'\G
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: test
TABLE_NAME: b
NON_UNIQUE: 1
INDEX_SCHEMA: test
INDEX_NAME: IX_S
SEQ_IN_INDEX: 1
COLUMN_NAME: S
COLLATION: A
CARDINALITY: 1038165
SUB_PART: NULL
PACKED: NULL
NULLABLE: YES
INDEX_TYPE: BTREE
COMMENT:
INDEX_COMMENT:

登录后复制

可以这个索引的CARDINALITY: 1038165,已经很大了。那这个表的估算行是多少呢。

show table status like 'b'\G
*************************** 1. row ***************************
Name: b
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 1038165
Avg_row_length: 114
Data_length: 119160832
Max_data_length: 0
Index_length: 109953024
Data_free: 5242880
Auto_increment: NULL
Create_time: 2014-05-23 00:24:25
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)

登录后复制

从Rows: 1038165看出,IX_S这个索引的区分度被认为非常好,已经近似于唯一索引。

MySQL Tips: 在show table status结果中看到的Rows用于表示表的当前行数。对于MyISAM表这是一个精确值,但对InnoDB这是个估算值。

虽然是估算值,但优化器是以此为指导的,也就是说,上面的某个explain里面的数据完全不符合期望:staight_join结果中第二行的rows。

阶段结论

我们发现整个错误的逻辑是这样的:以a为驱动表的执行计划,由于索引b.S的rows估计为1038165导致优化器认为代价大于以b为驱动表。而实际上这个索引的区分度为1.(当然对explan结果比较熟悉的同学会发现,第二行的type字段和Extra字段一起诡异了)

也就是说,straight_join得到的每一行去b中查询的时候,都走了全表扫描。在MySQL里面出现这种情况的最常见的是类型转换。比如一个字符串字段,虽然包含的是全数字,但查询的时候传入的不是字符串格式。

在这个case里面,两个都是字符串。因此,就是字符集相关了。

回到两个表结构,发现S字段的声明差别在于 COLLATE utf8_bin -- 这个就是本case的根本原因了:a表得到的S值是utf8_bin,优化器认为类型不同,无法直接用上索引b.IX_S过滤。

至于为什么还会用上索引,这个是因为覆盖索引带来“误解”。

MySQL Tips:若查询的所有结果能够从某个索引完全得到,则会优先用遍历索引替代遍历数据。

作为验证,

mysql> explain SELECT * FROM a straight_JOIN b ON binary a.`S` = b.`S` WHERE a.`L` > '2014-03-30 00:55:00' AND a.`L` < '2014-03-30 01:00:00' ;

+—-+————-+——-+——-+—————+——+———+——+———+————————————————+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+—-+————-+——-+——-+—————+——+———+——+———+————————————————+

| 1 | SIMPLE | a | range | IX_L | IX_L | 4 | NULL | 63 | Using where |

| 1 | SIMPLE | b | ALL | IX_S | NULL | NULL | NULL | 1038165 | Range checked for each record (index map: 0x4) |

+—-+————-+——-+——-+—————+——+———+——+———+————————————————+

登录后复制

由于结果是select *, 无法使用覆盖索引,因此第二行的key就显示为NULL. (笔者泪:要是早出这个结果查起来可方便多了)

优化

当然最直接的想法就是修改两个表的S字段的定义,改成相同即可。这个方法可以避免修改业务代码,但DDL代价略大。这里提供两种在SQL语句方面的优化。

1、select count(*) from b join (select s from a WHERE a.`L` > '2014-03-30 00:55:00' AND a.`L` < '2014-03-30 01:00:00') ta on b.S=ta.s;

登录后复制

这个写法比较直观,需要注意最后b.S和ta.S的顺序

2、SELECT count(*) FROM a JOIN b ON binary a.`S` = b.`S` WHERE a.`L` > '2014-03-30 00:55:00' AND a.`L` < '2014-03-30 01:00:00' ;

登录后复制

从前面的分析知道是由于b.S定义为utf8_bin.

MySQL Tips: MySQL中字符集命名规则中, XXX_bin与XXX的区别为大小写是否敏感。

这里我们将A.s全部增加binary限定,先转为小写,就是将临时结果集转成utf8_bin,之后使用b.S匹配时就能够直接利用索引。

其实两个改写方法的本质相同,区别是写法1是隐式转换。理论上说写法2速度更快些。

小结

做join的字段尽量设计为类型完全相同。

本站声明
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn

热AI工具

Undresser.AI Undress

Undresser.AI Undress

人工智能驱动的应用程序,用于创建逼真的裸体照片

AI Clothes Remover

AI Clothes Remover

用于从照片中去除衣服的在线人工智能工具。

Undress AI Tool

Undress AI Tool

免费脱衣服图片

Clothoff.io

Clothoff.io

AI脱衣机

AI Hentai Generator

AI Hentai Generator

免费生成ai无尽的。

热门文章

R.E.P.O.能量晶体解释及其做什么(黄色晶体)
3 周前 By 尊渡假赌尊渡假赌尊渡假赌
R.E.P.O.最佳图形设置
3 周前 By 尊渡假赌尊渡假赌尊渡假赌
R.E.P.O.如果您听不到任何人,如何修复音频
3 周前 By 尊渡假赌尊渡假赌尊渡假赌

热工具

记事本++7.3.1

记事本++7.3.1

好用且免费的代码编辑器

SublimeText3汉化版

SublimeText3汉化版

中文版,非常好用

禅工作室 13.0.1

禅工作室 13.0.1

功能强大的PHP集成开发环境

Dreamweaver CS6

Dreamweaver CS6

视觉化网页开发工具

SublimeText3 Mac版

SublimeText3 Mac版

神级代码编辑软件(SublimeText3)

无法以 root 身份登录 mysql 无法以 root 身份登录 mysql Apr 08, 2025 pm 04:54 PM

无法以 root 身份登录 MySQL 的原因主要在于权限问题、配置文件错误、密码不符、socket 文件问题或防火墙拦截。解决方法包括:检查配置文件中 bind-address 参数是否正确配置。查看 root 用户权限是否被修改或删除,并进行重置。验证密码是否准确无误,包括大小写和特殊字符。检查 socket 文件权限设置和路径。检查防火墙是否阻止了 MySQL 服务器的连接。

mysql 是否更改表锁定表 mysql 是否更改表锁定表 Apr 08, 2025 pm 05:06 PM

MySQL修改表结构时,通常使用元数据锁,可能导致锁表。为了减少锁的影响,可采取以下措施:1. 使用在线DDL保持表可用;2. 分批执行复杂修改;3. 在小表或非高峰期操作;4. 使用PT-OSC工具实现更精细的控制。

MySQL 中的查询优化对于提高数据库性能至关重要,尤其是在处理大型数据集时 MySQL 中的查询优化对于提高数据库性能至关重要,尤其是在处理大型数据集时 Apr 08, 2025 pm 07:12 PM

1.使用正确的索引索引通过减少扫描的数据量来加速数据检索select*fromemployeeswherelast_name='smith';如果多次查询表的某一列,则为该列创建索引如果您或您的应用根据条件需要来自多个列的数据,则创建复合索引2.避免选择*仅选择那些需要的列,如果您选择所有不需要的列,这只会消耗更多的服务器内存并导致服务器在高负载或频率时间下变慢例如,您的表包含诸如created_at和updated_at以及时间戳之类的列,然后避免选择*,因为它们在正常情况下不需要低效查询se

mysql用户和数据库的关系 mysql用户和数据库的关系 Apr 08, 2025 pm 07:15 PM

MySQL 数据库中,用户和数据库的关系通过权限和表定义。用户拥有用户名和密码,用于访问数据库。权限通过 GRANT 命令授予,而表由 CREATE TABLE 命令创建。要建立用户和数据库之间的关系,需创建数据库、创建用户,然后授予权限。

mysql 可以在 android 上运行吗 mysql 可以在 android 上运行吗 Apr 08, 2025 pm 05:03 PM

MySQL无法直接在Android上运行,但可以通过以下方法间接实现:使用轻量级数据库SQLite,由Android系统自带,无需单独服务器,资源占用小,非常适合移动设备应用。远程连接MySQL服务器,通过网络连接到远程服务器上的MySQL数据库进行数据读写,但存在网络依赖性强、安全性问题和服务器成本等缺点。

RDS MySQL 与 Redshift 零 ETL 集成 RDS MySQL 与 Redshift 零 ETL 集成 Apr 08, 2025 pm 07:06 PM

数据集成简化:AmazonRDSMySQL与Redshift的零ETL集成高效的数据集成是数据驱动型组织的核心。传统的ETL(提取、转换、加载)流程复杂且耗时,尤其是在将数据库(例如AmazonRDSMySQL)与数据仓库(例如Redshift)集成时。然而,AWS提供的零ETL集成方案彻底改变了这一现状,为从RDSMySQL到Redshift的数据迁移提供了简化、近乎实时的解决方案。本文将深入探讨RDSMySQL零ETL与Redshift集成,阐述其工作原理以及为数据工程师和开发者带来的优势。

mysql 是否要付费 mysql 是否要付费 Apr 08, 2025 pm 05:36 PM

MySQL 有免费的社区版和收费的企业版。社区版可免费使用和修改,但支持有限,适合稳定性要求不高、技术能力强的应用。企业版提供全面商业支持,适合需要稳定可靠、高性能数据库且愿意为支持买单的应用。选择版本时考虑的因素包括应用关键性、预算和技术技能。没有完美的选项,只有最合适的方案,需根据具体情况谨慎选择。

如何针对高负载应用程序优化 MySQL 性能? 如何针对高负载应用程序优化 MySQL 性能? Apr 08, 2025 pm 06:03 PM

MySQL数据库性能优化指南在资源密集型应用中,MySQL数据库扮演着至关重要的角色,负责管理海量事务。然而,随着应用规模的扩大,数据库性能瓶颈往往成为制约因素。本文将探讨一系列行之有效的MySQL性能优化策略,确保您的应用在高负载下依然保持高效响应。我们将结合实际案例,深入讲解索引、查询优化、数据库设计以及缓存等关键技术。1.数据库架构设计优化合理的数据库架构是MySQL性能优化的基石。以下是一些核心原则:选择合适的数据类型选择最小的、符合需求的数据类型,既能节省存储空间,又能提升数据处理速度

See all articles