検索

一、order by的原理

 

1、利用索引的有序性获取有序数据

 

当查询语句的 order BY 条件和查询的执行计划中所利用的 Index 的索引键(或前面几个索引键)完全一致,且索引访问方式为 range,ref 或者 index 的时候,MySQL 可以利用索引顺序而直接取得已经排好序的数据。这种方式的 order BY 基本上可以说是最优的排序方式了,因为 MySQL 不需要进行实际的排序操作,需要注意的是使用索引排序也有很多限制。

 

当对连接操作进行排序时,如果ORDER BY仅仅引用第一个表的列,MySQL对该表进行filesort操作,然后进行连接处理,此时,EXPLAIN输出“Using filesort”;否则,MySQL必须将查询的结果集生成一个临时表,在连接完成之后进行filesort操作,此时,EXPLAIN输出“Using temporary;Using filesort”。

 

注意:MySQL在查询时最多只能使用一个索引。因此,如果WHERE条件已经占用了索引,那么在排序中就不使用索引了

 

mysql> show create table test \G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `addtime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `a` int(11) unsigned NOT NULL,
  `b` int(11) unsigned NOT NULL,
  `c` int(11) unsigned NOT NULL,
  `data` varchar(20) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `u` (`addtime`,`a`,`b`),
  KEY `a` (`a`),
  KEY `b` (`b`),
  KEY `c` (`c`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8

 

执行查询1

 

mysql> explain  select * from test where addtime='2015-10-13 15:38:32' order by  a , b ;

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

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

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

|  1 | SIMPLE      | test  | ref  | u             | u    | 8       | const |    4 | Using where |

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

  执行查询2

 

mysql> explain  select * from test where addtime='2015-10-13 15:38:32' order by  a , b ,c;

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

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

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

|  1 | SIMPLE      | test  | ref  | u             | u    | 8       | const |    4 | Using where; Using filesort |

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

使用filesort的情况

 

a、where语句与order by语句,使用了不同的索引

 

b、 检查的行数过多,且没有使用覆盖索引

 

c、对索引列同时使用了ASC和DESC

 

d、where语句或者ORDER BY语句中索引列使用了表达式,包括函数表达式

 

e、where 语句与ORDER BY语句组合满足最左前缀,但where语句中使用了条件查询

 

f、 当使用left join,使用右边的表字段排序

2 利用内存/磁盘文件排序获取结果

 

由于没有可以利用的有序索引取得有序的数据,MySQL需要通过相应的排序算法,将取得的数据在sort_buffer_size系统变量所设置大小的排序区进行排序,这个排序区是每个Thread 独享的,所以说可能在同一时刻在 MySQL 中可能存在多个 sort buffer 内存区域

 

MySQL中filesort 的实现算法有两种:

 

1) 双路排序: 是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行指针信息,然后在sort buffer 中进行排序(but this will be essentially hit the table in random order and is not very fast)。

2) 单路排序:是一次性取出满足条件行的所有字段,然后在sort buffer中进行排序。

 

在 MySQL4.1 版本之前只有第一种排序算法,第二种算法是从MySQL4.1开始的改进算法,主要目的是为了减少第一次算法中需要两次访问表数据的IO操作,将两次变成了一次,但相应也会耗用更多的 sort buffer 空间。典型的以空间换时间的优化方式。当然,MySQL4.1开始的以后所有版本同时也支持第一种算法

 

MySQL主要通过比较系统参数 max_length_for_sort_data的大小和Query语句所取出的字段类型大小总和来判定需要使用哪一种排序算法。如果max_length_for_sort_data更大,则使用第二种优化后的算法,反之使用第一种算法。所以如果希望 order BY 操作的效率尽可能的高,需要注意max_length_for_sort_data参数的设置。

 

3、使用Using temporary 临时表来filesort

 

如果order by的子句只引用了联接中的第一个表,MySQL会先对第一个表进行排序,然后进行联接,expain中的Extra会出现Using Filesort

 

否则MySQL先把结果保存到临时表(Temporary Table),然后再对临时表的数据进行排序,此时expain中的Extra的显示Using temporary Using Filesort

 

4、Join排序

 

mysql中有三种方式

 

1)、Use index-based access method that produces ordered output  -》 null

 

mysql中的order by_MySQL

 

2)、Use filesort() on 1st non-constant table -》 “Using filesort” in the first row

 

mysql中的order by_MySQL

 

3)、Put join result into a temporary table and use filesort() on it   -》  “Using temporary; Using filesort” in the first row

 

mysql中的order by_MySQL

5、说一下filesort,可以参考 What does Using filesort mean in MySQL?

 

摘自原文

 

 

 

The truth is, filesort is badly named. Anytime a sort can’t be performed from an index, it’s a filesort. It has nothing to do with files. Filesort should be called “sort.” It is quicksort at heart.

 

 

 

If the sort is bigger than the sort buffer, it is performed a bit at a time, and then the chunks are merge-sorted to produce the final sorted output. There is a lot more to it than this. I refer you to Sergey Petrunia’s article on How MySQL executes ORDER BY. You can also read about it in our book, but if you read Sergey’s article you won’t need to.

 

 

 

这里有一个误区,容易望文生义,Explain 命令输出信息中的 filesort 到底是什么意思呢?其实很简单,就是告诉你 MySQL 需要进行实际的排序操作而不能通过索引获得已排序数据

 

 

 

1)、filesort(其实就是排序) 可不一定会产生临时表

 

2)、filesort 与临时表数据写入磁盘是没有任何直接联系的

 

 

二 、优化order by

 

当无法避免排序操作时,又该如何来优化呢?很显然,优先选择第一种using index 的排序方式,在第一种方式无法满足的情况下,尽可能让 MySQL 选择使用第二种单路算法来进行排序。这样可以减少大量的随机IO操作,很大幅度地提高排序工作的效率。

 

1、加大 max_length_for_sort_data 参数的设置

 

在 MySQL 中,决定使用老式排序算法还是改进版排序算法是通过参数 max_length_for_ sort_data 来决定的。当所有返回字段的最大长度小于这个参数值时,MySQL 就会选择改进后的排序算法,反之,则选择老式的算法。所以,如果有充足的内存让MySQL 存放须要返回的非排序字段,就可以加大这个参数的值来让 MySQL 选择使用改进版的排序算法。

 

2、去掉不必要的返回字段

 

当内存不是很充裕时,不能简单地通过强行加大上面的参数来强迫 MySQL 去使用改进版的排序算法,否则可能会造成 MySQL 不得不将数据分成很多段,然后进行排序,这样可能会得不偿失。此时就须要去掉不必要的返回字段,让返回结果长度适应 max_length_for_sort_data 参数的限制。

 

3 增大 sort_buffer_size 参数设置

 

这个值如果过小的话,再加上你一次返回的条数过多,那么很可能就会分很多次进行排序,然后最后将每次的排序结果再串联起来,这样就会更慢,增大 sort_buffer_size 并不是为了让 MySQL选择改进版的排序算法,而是为了让MySQL尽量减少在排序过程中对须要排序的数据进行分段,因为分段会造成 MySQL 不得不使用临时表来进行交换排序。

 

但是这个值不是越大越好:

 

1 Sort_Buffer_Size 是一个connection级参数,在每个connection第一次需要使用这个buffer的时候,一次性分配设置的内存。

 

2 Sort_Buffer_Size 并不是越大越好,由于是connection级的参数,过大的设置+高并发可能会耗尽系统内存资源。

 

3 据说Sort_Buffer_Size 超过2M的时候,就会使用mmap() 而不是 malloc() 来进行内存分配,导致效率降低。

声明
この記事の内容はネチズンが自主的に寄稿したものであり、著作権は原著者に帰属します。このサイトは、それに相当する法的責任を負いません。盗作または侵害の疑いのあるコンテンツを見つけた場合は、admin@php.cn までご連絡ください。
MySQLの場所:データベースとプログラミングMySQLの場所:データベースとプログラミングApr 13, 2025 am 12:18 AM

データベースとプログラミングにおけるMySQLの位置は非常に重要です。これは、さまざまなアプリケーションシナリオで広く使用されているオープンソースのリレーショナルデータベース管理システムです。 1)MySQLは、効率的なデータストレージ、組織、および検索機能を提供し、Web、モバイル、およびエンタープライズレベルのシステムをサポートします。 2)クライアントサーバーアーキテクチャを使用し、複数のストレージエンジンとインデックスの最適化をサポートします。 3)基本的な使用には、テーブルの作成とデータの挿入が含まれ、高度な使用法にはマルチテーブル結合と複雑なクエリが含まれます。 4)SQL構文エラーやパフォーマンスの問題などのよくある質問は、説明コマンドとスロークエリログを介してデバッグできます。 5)パフォーマンス最適化方法には、インデックスの合理的な使用、最適化されたクエリ、およびキャッシュの使用が含まれます。ベストプラクティスには、トランザクションと準備された星の使用が含まれます

MySQL:中小企業から大企業までMySQL:中小企業から大企業までApr 13, 2025 am 12:17 AM

MySQLは、中小企業に適しています。 1)中小企業は、顧客情報の保存など、基本的なデータ管理にMySQLを使用できます。 2)大企業はMySQLを使用して、大規模なデータと複雑なビジネスロジックを処理して、クエリのパフォーマンスとトランザクション処理を最適化できます。

Phantomの読み取りとは何ですか?Innodbはどのようにそれらを防ぐ(次のキーロック)?Phantomの読み取りとは何ですか?Innodbはどのようにそれらを防ぐ(次のキーロック)?Apr 13, 2025 am 12:16 AM

INNODBは、次のキーロックメカニズムを通じてファントムの読み取りを効果的に防止します。 1)Next-KeyLockingは、Row LockとGap Lockを組み合わせてレコードとギャップをロックして、新しいレコードが挿入されないようにします。 2)実際のアプリケーションでは、クエリを最適化して分離レベルを調整することにより、ロック競争を削減し、並行性パフォーマンスを改善できます。

mysql:プログラミング言語ではありませんが...mysql:プログラミング言語ではありませんが...Apr 13, 2025 am 12:03 AM

MySQLはプログラミング言語ではありませんが、そのクエリ言語SQLにはプログラミング言語の特性があります。1。SQLは条件付き判断、ループ、可変操作をサポートします。 2。ストアドプロシージャ、トリガー、機能を通じて、ユーザーはデータベースで複雑な論理操作を実行できます。

MySQL:世界で最も人気のあるデータベースの紹介MySQL:世界で最も人気のあるデータベースの紹介Apr 12, 2025 am 12:18 AM

MySQLはオープンソースのリレーショナルデータベース管理システムであり、主にデータを迅速かつ確実に保存および取得するために使用されます。その実用的な原則には、クライアントリクエスト、クエリ解像度、クエリの実行、返品結果が含まれます。使用法の例には、テーブルの作成、データの挿入とクエリ、および参加操作などの高度な機能が含まれます。一般的なエラーには、SQL構文、データ型、およびアクセス許可、および最適化の提案には、インデックスの使用、最適化されたクエリ、およびテーブルの分割が含まれます。

MySQLの重要性:データストレージと管理MySQLの重要性:データストレージと管理Apr 12, 2025 am 12:18 AM

MySQLは、データストレージ、管理、クエリ、セキュリティに適したオープンソースのリレーショナルデータベース管理システムです。 1.さまざまなオペレーティングシステムをサポートし、Webアプリケーションやその他のフィールドで広く使用されています。 2。クライアントサーバーアーキテクチャとさまざまなストレージエンジンを通じて、MySQLはデータを効率的に処理します。 3.基本的な使用には、データベースとテーブルの作成、挿入、クエリ、データの更新が含まれます。 4.高度な使用には、複雑なクエリとストアドプロシージャが含まれます。 5.一般的なエラーは、説明ステートメントを介してデバッグできます。 6.パフォーマンスの最適化には、インデックスの合理的な使用と最適化されたクエリステートメントが含まれます。

なぜMySQLを使用するのですか?利点と利点なぜMySQLを使用するのですか?利点と利点Apr 12, 2025 am 12:17 AM

MySQLは、そのパフォーマンス、信頼性、使いやすさ、コミュニティサポートに選択されています。 1.MYSQLは、複数のデータ型と高度なクエリ操作をサポートし、効率的なデータストレージおよび検索機能を提供します。 2.クライアントサーバーアーキテクチャと複数のストレージエンジンを採用して、トランザクションとクエリの最適化をサポートします。 3.使いやすく、さまざまなオペレーティングシステムとプログラミング言語をサポートしています。 4.強力なコミュニティサポートを提供し、豊富なリソースとソリューションを提供します。

InnoDBロックメカニズム(共有ロック、排他的ロック、意図ロック、レコードロック、ギャップロック、次のキーロック)を説明します。InnoDBロックメカニズム(共有ロック、排他的ロック、意図ロック、レコードロック、ギャップロック、次のキーロック)を説明します。Apr 12, 2025 am 12:16 AM

INNODBのロックメカニズムには、共有ロック、排他的ロック、意図ロック、レコードロック、ギャップロック、次のキーロックが含まれます。 1.共有ロックにより、トランザクションは他のトランザクションが読み取らないようにデータを読み取ることができます。 2.排他的ロックは、他のトランザクションがデータの読み取りと変更を防ぎます。 3.意図ロックは、ロック効率を最適化します。 4。ロックロックインデックスのレコードを記録します。 5。ギャップロックロックインデックス記録ギャップ。 6.次のキーロックは、データの一貫性を確保するためのレコードロックとギャップロックの組み合わせです。

See all articles

ホットAIツール

Undresser.AI Undress

Undresser.AI Undress

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

AI Clothes Remover

AI Clothes Remover

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

Undress AI Tool

Undress AI Tool

脱衣画像を無料で

Clothoff.io

Clothoff.io

AI衣類リムーバー

AI Hentai Generator

AI Hentai Generator

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

ホットツール

Dreamweaver Mac版

Dreamweaver Mac版

ビジュアル Web 開発ツール

MinGW - Minimalist GNU for Windows

MinGW - Minimalist GNU for Windows

このプロジェクトは osdn.net/projects/mingw に移行中です。引き続きそこでフォローしていただけます。 MinGW: GNU Compiler Collection (GCC) のネイティブ Windows ポートであり、ネイティブ Windows アプリケーションを構築するための自由に配布可能なインポート ライブラリとヘッダー ファイルであり、C99 機能をサポートする MSVC ランタイムの拡張機能が含まれています。すべての MinGW ソフトウェアは 64 ビット Windows プラットフォームで実行できます。

SAP NetWeaver Server Adapter for Eclipse

SAP NetWeaver Server Adapter for Eclipse

Eclipse を SAP NetWeaver アプリケーション サーバーと統合します。

VSCode Windows 64 ビットのダウンロード

VSCode Windows 64 ビットのダウンロード

Microsoft によって発売された無料で強力な IDE エディター

PhpStorm Mac バージョン

PhpStorm Mac バージョン

最新(2018.2.1)のプロフェッショナル向けPHP統合開発ツール