在mysql中ORDER BY keyword是用来给记录中的数据进行分类的,下面我来总结了order by语句利用索引进行优化的方法。
MySQL Order By语法
代码如下 | 复制代码 |
SELECT column_name(s) FROM table_name ORDER BY column_name |
注意:SQL语句是“字母大小写不敏感”的语句(它不区分字母的大小写),即:“ORDER BY”和“order by”是一样的。
MySQL Order By案例
下面的例子:从“Person”表中选取所有记录,并将“Age”列进行分类:
代码如下 | 复制代码 |
$con = mysql_connect("localhost","peter","abc123"); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db("my_db", $con); $result = mysql_query("SELECT * FROM person ORDER BY age"); while($row = mysql_fetch_array($result)) { echo $row['FirstName'] echo " " . $row['LastName']; echo " " . $row['Age']; echo " "; } mysql_close($con); ?> |
上面的代码将输出下面的结果:
Glenn Quagmire 33
Peter Griffin 35
按照升序或者降序进行分类排列
如果你使用了“ORDER BY”关键词,所有记录将按照默认的升序进行排列(即:从1到9,从a到z)
使用“DESC”关键词可以制定所有的数据按照降序排列(即:从9到1,从z到a):
双击代码全选 123 SELECT column_name(s) FROM table_name ORDER BY column_name DESC
MySQL Order By根据两列进行分类
很多时候,我们需要同时根据两列内容(或者更多列)来对数据进行分类。当指定的列数多于一列时,仅在第一列的值完全相同时才参考第二列:
双击代码全选 123 SELECT column_name(s) FROM table_name ORDER BY column_name1, column_name2
通过索引优化来实现MySQL的ORDER BY语句优化:
1、ORDER BY的索引优化。如果一个SQL语句形如:
代码如下 | 复制代码 |
SELECT [column1],[column2],…. FROM [TABLE] ORDER BY [sort]; |
在[sort]这个栏位上建立索引就可以实现利用索引进行order by 优化。
2、WHERE + ORDER BY的索引优化,形如:
SELECT [column1],[column2],…. FROM [TABLE] WHERE [columnX] = [value] ORDER BY [sort];
建立一个联合索引(columnX,sort)来实现order by 优化。
注意:如果columnX对应多个值,如下面语句就无法利用索引来实现order by的优化
代码如下 | 复制代码 |
SELECT [column1],[column2],…. FROM [TABLE] WHERE [columnX] IN ([value1],[value2],…) ORDER BY[sort]; |
3、WHERE+ 多个字段ORDER BY
代码如下 | 复制代码 |
SELECT * FROM [table] WHERE uid=1 ORDER x,y LIMIT 0,10; |
建立索引(uid,x,y)实现order by的优化,比建立(x,y,uid)索引效果要好得多。
MySQL Order By不能使用索引来优化排序的情况
* 对不同的索引键做 ORDER BY :(key1,key2分别建立索引)
代码如下 | 复制代码 |
SELECT * FROM t1 ORDER BY key1, key2; |
* 在非连续的索引键部分上做 ORDER BY:(key_part1,key_part2建立联合索引;key2建立索引)
代码如下 | 复制代码 |
SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2; |
* 同时使用了 ASC 和 DESC:(key_part1,key_part2建立联合索引)
代码如下 | 复制代码 |
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC; |
* 用于搜索记录的索引键和做 ORDER BY 的不是同一个:(key1,key2分别建立索引)
代码如下 | 复制代码 |
SELECT * FROM t1 WHERE key2=constant ORDER BY key1; |
* 如果在WHERE和ORDER BY的栏位上应用表达式(函数)时,则无法利用索引来实现order by的优化
代码如下 | 复制代码 |
SELECT * FROM t1 ORDER BY YEAR(logindate) LIMIT 0,10; |
特别提示:
>mysql一次查询只能使用一个索引。如果要对多个字段使用索引,建立复合索引。
>越小的数据类型通常更好:越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间,处理起来更快。
>简单的数据类型更好:整型数据比起字符,处理开销更小,因为字符串的比较更复杂。在MySQL中,应该用内置的日期和时间数据类型,而不是用字符串来存储时间;以及用整型数据类型存储IP地址。
>尽量避免NULL:应该指定列为NOT NULL,除非你想存储NULL。在MySQL中,含有空值的列很难进行查询优化,因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值。
>在ORDER BY操作中,MySQL只有在排序条件不是一个查询条件表达式的情况下才使用索引。

This article explores optimizing MySQL memory usage in Docker. It discusses monitoring techniques (Docker stats, Performance Schema, external tools) and configuration strategies. These include Docker memory limits, swapping, and cgroups, alongside

This article addresses MySQL's "unable to open shared library" error. The issue stems from MySQL's inability to locate necessary shared libraries (.so/.dll files). Solutions involve verifying library installation via the system's package m

The article discusses using MySQL's ALTER TABLE statement to modify tables, including adding/dropping columns, renaming tables/columns, and changing column data types.

This article compares installing MySQL on Linux directly versus using Podman containers, with/without phpMyAdmin. It details installation steps for each method, emphasizing Podman's advantages in isolation, portability, and reproducibility, but also

This article provides a comprehensive overview of SQLite, a self-contained, serverless relational database. It details SQLite's advantages (simplicity, portability, ease of use) and disadvantages (concurrency limitations, scalability challenges). C

Article discusses configuring SSL/TLS encryption for MySQL, including certificate generation and verification. Main issue is using self-signed certificates' security implications.[Character count: 159]

This guide demonstrates installing and managing multiple MySQL versions on macOS using Homebrew. It emphasizes using Homebrew to isolate installations, preventing conflicts. The article details installation, starting/stopping services, and best pra

Article discusses popular MySQL GUI tools like MySQL Workbench and phpMyAdmin, comparing their features and suitability for beginners and advanced users.[159 characters]


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

SAP NetWeaver Server Adapter for Eclipse
Integrate Eclipse with SAP NetWeaver application server.

MinGW - Minimalist GNU for Windows
This project is in the process of being migrated to osdn.net/projects/mingw, you can continue to follow us there. MinGW: A native Windows port of the GNU Compiler Collection (GCC), freely distributable import libraries and header files for building native Windows applications; includes extensions to the MSVC runtime to support C99 functionality. All MinGW software can run on 64-bit Windows platforms.

VSCode Windows 64-bit Download
A free and powerful IDE editor launched by Microsoft

MantisBT
Mantis is an easy-to-deploy web-based defect tracking tool designed to aid in product defect tracking. It requires PHP, MySQL and a web server. Check out our demo and hosting services.

mPDF
mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),
