一个优化MySQL查询操作的具体案例分析_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的字段尽量设计为类型完全相同。

ホットAIツール

Undresser.AI Undress
リアルなヌード写真を作成する AI 搭載アプリ

AI Clothes Remover
写真から衣服を削除するオンライン AI ツール。

Undress AI Tool
脱衣画像を無料で

Clothoff.io
AI衣類リムーバー

AI Hentai Generator
AIヘンタイを無料で生成します。

人気の記事

ホットツール

メモ帳++7.3.1
使いやすく無料のコードエディター

SublimeText3 中国語版
中国語版、とても使いやすい

ゼンドスタジオ 13.0.1
強力な PHP 統合開発環境

ドリームウィーバー CS6
ビジュアル Web 開発ツール

SublimeText3 Mac版
神レベルのコード編集ソフト(SublimeText3)

ホットトピック











ビッグ データ構造の処理スキル: チャンキング: データ セットを分割してチャンクに処理し、メモリ消費を削減します。ジェネレーター: データ セット全体をロードせずにデータ項目を 1 つずつ生成します。無制限のデータ セットに適しています。ストリーミング: ファイルやクエリ結果を 1 行ずつ読み取ります。大きなファイルやリモート データに適しています。外部ストレージ: 非常に大規模なデータ セットの場合は、データをデータベースまたは NoSQL に保存します。

MySQL クエリのパフォーマンスは、検索時間を線形の複雑さから対数の複雑さまで短縮するインデックスを構築することで最適化できます。 PreparedStatement を使用して SQL インジェクションを防止し、クエリのパフォーマンスを向上させます。クエリ結果を制限し、サーバーによって処理されるデータ量を削減します。適切な結合タイプの使用、インデックスの作成、サブクエリの使用の検討など、結合クエリを最適化します。クエリを分析してボトルネックを特定し、キャッシュを使用してデータベースの負荷を軽減し、オーバーヘッドを最小限に抑えます。

PHP で MySQL データベースをバックアップおよび復元するには、次の手順を実行します。 データベースをバックアップします。 mysqldump コマンドを使用して、データベースを SQL ファイルにダンプします。データベースの復元: mysql コマンドを使用して、SQL ファイルからデータベースを復元します。

MySQLテーブルにデータを挿入するにはどうすればよいですか?データベースに接続する: mysqli を使用してデータベースへの接続を確立します。 SQL クエリを準備します。挿入する列と値を指定する INSERT ステートメントを作成します。クエリの実行: query() メソッドを使用して挿入クエリを実行します。成功すると、確認メッセージが出力されます。

MySQL 8.4 (2024 年時点の最新の LTS リリース) で導入された主な変更の 1 つは、「MySQL Native Password」プラグインがデフォルトで有効ではなくなったことです。さらに、MySQL 9.0 ではこのプラグインが完全に削除されています。 この変更は PHP および他のアプリに影響します

PHP で MySQL ストアド プロシージャを使用するには: PDO または MySQLi 拡張機能を使用して、MySQL データベースに接続します。ストアド プロシージャを呼び出すステートメントを準備します。ストアド プロシージャを実行します。結果セットを処理します (ストアド プロシージャが結果を返す場合)。データベース接続を閉じます。

PHP を使用して MySQL テーブルを作成するには、次の手順が必要です。 データベースに接続します。データベースが存在しない場合は作成します。データベースを選択します。テーブルを作成します。クエリを実行します。接続を閉じます。

Oracle データベースと MySQL はどちらもリレーショナル モデルに基づいたデータベースですが、Oracle は互換性、スケーラビリティ、データ型、セキュリティの点で優れており、MySQL は速度と柔軟性に重点を置いており、小規模から中規模のデータ セットに適しています。 ① Oracle は幅広いデータ型を提供し、② 高度なセキュリティ機能を提供し、③ エンタープライズレベルのアプリケーションに適しています。① MySQL は NoSQL データ型をサポートし、② セキュリティ対策が少なく、③ 小規模から中規模のアプリケーションに適しています。
