知数堂《MySQL核心优化课》大纲(2020年最新版)

知数堂MySQL优化培训课程大纲(2020年最新版),未完待续。 最后更新时间:2020/5/27

序号 课程内容 知识要点
1. 基础通识篇
1 MySQL分支和版本的选择 1. MySQL简史、趣闻 2. 主流分支派别特性及区别 3. 几个主要版本5.6、5.7、8.0该如何选择
2 MySQL有别于其他数据库的特点 1. 整体技术特征 2. 如何最大化利用CPU资源 3. 内存设置有什么要注意的地方 4. 如何避免磁盘I/O性能瓶颈
3 MySQL硬件&系统优化checklist 1. 硬件层优化检查checklist 2. 系统层优化检查checklist 3. MySQL层优化检查checklist
4 玩转MySQL的几点建议 1、MySQL应用开发过程中有哪些可以提升和优化性能的地方 2、运维规范 3、安全规范
5 MySQL重点数据类型 1. 整型 2. 字符型 3. 日期时间型 4. 大对象类型 5. JSON,配合虚拟列的新玩法 6. 注意防范数据类型隐式转换
6 MySQL体系结构(1)经典三层结构 1、连接层 2、Server层 3、引擎层 4、MySQL 8.0在体系结构方面有什么变化,对运维及开发有什么影响 5、一个SQL请求(SELECT和UPDATE)的执行过程是怎样的
7 MySQL体系结构(2)文件目录及内存管理 1、文件目录结构 2、内存管理机制
8 MySQL体系结构(3)第三方引擎&工具 1、好用的第三方存储引擎,TokuDB、MyRocks、ColumnStore等引擎 2、好用的第三方工具,pt工具包、gh-ost等 3. 利用ClickHouse搞定OLAP需求
9 MySQL体系结构(4)善用PFS、SYS Schema洞察MySQL运行状态 1. sys schema和pfs(performance_schema)、ifs(information_schema)简述及配置 2. 重要数据字典解读 3. 如何利用sys、pfs洞察MySQL运行状态 4. 如何定位性能瓶颈
2. 优化原理篇
10 MySQL索引基础(1)索引数据结构 1. 二分查找; 2. 二叉树; 3. 平衡二叉树; 4. B树、B+树、B*树; 5. 哈希索引
11 MySQL索引基础(2)聚集索引、主键索引 1. 聚集索引,聚集索引有什么特点; 2. 主键索引和辅助索引; 3. InnoDB索引结构特别之处
12 MySQL索引基础(3)联合索引、覆盖索引等 1. 联合索引; 2. 覆盖索引; 3. 前缀索引; 4. 索引合并; 5. 全文索引
13 MySQL索引基础(3)索引新特性 1. 不可见索引; 2. 倒序索引; 3. 函数索引; 4. 表达式索引; 5. 跳跃索引扫描; 6. 多值索引
14 MySQL 8.0直方图 1. 直方图简介; 2. 如何利用直方图优化SQL查询
15 MySQL索引优化实战(1) 1. 索引的日常管理维护; 2. 索引统计信息怎么维护更新的; 3. 怎么理解三星索引原则; 4. 理解执行计划中的key_len
16 MySQL索引优化实战(2) 1. 没有索引会有怎样的严重后果; 2. 索引什么情况下效率低,甚至不可用; 3. 如何发现冗余索引和无用索引
17 MySQL索引优化实战(3) 1. 利用索引消除额外排序和临时表; 2. 索引优化实战案列剖析; 3. MySQL索引优化建议

对我们的课程有兴趣的同学请扫码入群咨询,群号 176450603

MySQL 8.0.21 is ready, but it’s not just about changing the terminology

There are other new changes that need attention.

P.S, 中文版:MySQL 8.0.21来了,该关心的不只是改专有术语名词

2020.7.13 Beijing time, MySQL released version 8.0.21 as scheduled, here is release notes.

Many people may be ridiculing that MySQL has changed terms such as master/slave/whitelist/blacklist for some reasons as you known. Fortunately, these changes haven’t affected much at present, and the old terms can continue to be used for a while.

In addition, there are several new features or performance improvements that I think are also important:

  1. Globally disable REDO LOG (WL#13795) to speed up data import (used for data recovery or initialization).
  2. Optimize lock_sys mutex(WL#10314), adopt the split + queuing idea (I understand the optimization of similar spike business scenarios, not sure whether it is accurate).
  3. DDL operation of the UNDO tablespace is added to The REDL LOG (WL#11819), and the ACID guarantee is added.
  4. Add atomicity of CREATE TABLE…SELECT and crash safe support (WL#13355), so that it can also be used in MGR scenarios (Before it will be refused to execute, because it is split into two SQL, does not support atomicity).
  5. The optimizer has a new switch prefer_ordering_index(WL#13929) to fix errors in individual scenarios. LIMIT optimization practices (bug#97001) .
  6. Single table UPDATE and DELETE also add semijoin optimization support (WL#6057).

Of course, other things not listed are not important, more can be read in full release notes, or this article The MySQL 8.0.21 Maintenance Release is Generally Available.

Further reading

https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-21.html
https://mysqlserverteam.com/the-mysql-8-0-21-maintenance-release-is-generally-available/
https://dev.mysql.com/worklog/task/?id=13795
https://dev.mysql.com/worklog/task/?id=10314
https://dev.mysql.com/worklog/task/?id=11819
https://dev.mysql.com/worklog/task/?id=13355
https://dev.mysql.com/worklog/task/?id=13929
https://dev.mysql.com/worklog/task/?id=6057

Enjoy MySQL 8.0 :)

That’s all.

老叶观点:MySQL开发规范之我见(更新版)

大多数MySQL规范在网上也都能找得到相关的分享,在这里要分享的是老叶个人认为比较重要的,或者容易被忽视的,以及容易被混淆的一些地方。

1、默认使用InnoDB引擎

【老叶观点】已多次呼吁过了,InnoDB适用于几乎99%的MySQL应用场景,而且在MySQL 5.7的系统表都改成InnoDB了,还有什么理由再死守MyISAM呢。

此外,频繁读写的InnoDB表,一定要使用具有自增/顺序特征的整型作为显式主键。

当然了,也不是说MyISAM就一无是处,比如老叶之前就把MyISAM用于临时导数据数据(把数据导入MyISAM,一番处理后再入到InnoDB表)、或者一些特殊的数据统计类场景用MyISAM(大数据量下MyISAM全表顺序读取比InnoDB有明显优势)可能比较合适。前提是,你得非常清楚MyISAM引擎的优势在哪里。

[MySQL FAQ]系列 — 为什么InnoDB表要建议用自增列做主键

2、InnoDB表行记录物理长度不超过8KB

【老叶观点】InnoDB的data page默认是16KB,基于B+Tree的特点,一个data page中需要至少存储2条记录。因此,当实际存储长度超过8KB(尤其是TEXT/BLOB列)的大列(large column)时会引起“page-overflow存储”,类似ORACLE中的“行迁移”。

因此,如果必须使用大列(尤其是TEXT/BLOB类型)且读写频繁的话,则最好把这些列拆分到子表中,不要和主表放在一起存储。如果不太频繁,可以考虑继续保留在主表中。

当然了,如果将 innodb_page_size 选项修改成 8KB,那么行记录物理长度建议不超过4KB。

[MySQL优化案例]系列 — 优化InnoDB表BLOB列的存储效率

3、是否使用表分区(partition)

【老叶观点】在一些使用表分区后明显可以提升性能或者运维便利性的场景下,还是建议使用表分区。

比如老叶就在zabbix的数据库采用TokuDB引擎的前提下,又根据时间维度使用了分区。这样的好处是保证zabbix日常应用不受到影响前提下,方便管理员例行删除过去数据,只需要删除相应分区即可,不需再执行一个非常慢的DELETE而影响整体性能。

当然了,使用表分区可能不利于物理扩展,比如大数据量下想要做哈希水平拆分,这个就见仁见智了,如果你的业务场景下使用表分区更有好处,就放心大胆的用吧。该进行拆分就用拆分方案,不要继续抱着表分区方案不放。

迁移Zabbix数据库到TokuDB

4、是否使用存储过程、触发器

【老叶观点】在一些合适的场景下,用存储过程、触发器也完全没问题。

我们以前就是利用存储完成游戏业务逻辑处理,性能上不是问题,而且一旦需求有变更,只需修改存储过程,变更代价很低。我们还利用触发器维护一个频繁更新的表,对这个表的所有变更都将部分字段同步更新到另一个表中(类似物化视图的变相实现),也不存在性能问题。

有同行认为存储过程和触发器的应用可能会导致发生锁等待、死锁时排查问题上的困惑。嗯,这个是有这个可能性,不过如果真发生时,根据当时现场记录的SQL反查对应的存储过程或触发器,应该也不是难事,只不过要求DBA对线上业务环境更要了然于胸了。

总的来说,不要把MySQL的存储过程和触发器视为洪水猛兽,用好的话,没有问题的,真遇到问题了再优化也不迟。另外,MySQL因为对视图的处理也不太理想也没有物化视图,因此视图能不用就尽量少用吧。

5、选择合适的类型

【老叶观点】除了常见的建议外,还有其他几个要点:

5.1、用INT UNSIGNED存储IPV4地址,用INET_ATON()、INET_NTOA()进行转换,基本上没必要使用CHAR(15)来存储。

5.2、枚举类型可以使用ENUM,ENUM的内部存储机制是采用TINYINT或SMALLINT(并非CHAR/VARCHAR),性能一点都不差,记住千万别用CHAR/VARCHAR 来存储枚举数据。

5.3、还个早前一直在传播的“常识性误导”,建议用TIMESTAMP取代DATETIME。其实从5.6开始,建议优先选择DATETIME存储日期时间,因为它的可用范围比TIMESTAMP更大,物理存储上仅比TIMESTAMP多1个字节,整体性能上的损失并不大。

5.4、所有字段定义中,默认都加上NOT NULL约束,除非必须为NULL(但我也想不出来什么场景下必须要在数据库中存储NULL值,可以用0来表示)。在对该字段进行COUNT()统计时,统计结果更准确(值为NULL的不会被COUNT统计进去),或者执行 WHERE column IS NULL 检索时,也可以快速返回结果。

5.5、杜绝直接 SELECT * 读取全部字段,当表中存在 TEXT/BLOB 大列的时候就会是灾难了。可能本来不需要读取这些列,但因为偷懒写成 SELECT * 导致内存buffer pool被这些“垃圾”数据把真正需要缓冲起来的热点数据给洗出去了。

相应地,在写INSERT时,也要写上相对应的字段列表。

要求在SQL中写清楚每个字段的重要意义还在于,当业务需要表DDL发生更新后,如果不写清楚字段,可能会导致旧业务代码不可用,这个就折腾大发了。

6、关于索引

【老叶观点】除了常见的建议外,还有几个要点:

6.1、超过20个长度的字符串列,最好创建前缀索引而非整列索引(例如:ALTER TABLE t1 ADD INDEX(user(20))),可以有效提高索引利用率,不过它的缺点是对这个列排序时用不到前缀索引。前缀索引的长度可以基于对该字段的统计得出,一般略大于平均长度一点就可以了。

6.2、定期用 pt-duplicate-key-checker 工具检查并删除冗余的索引。比如 index idx1(a, b) 索引已经涵盖了 index idx2(a),就可以删除 idx2 索引了。

6.3、有多字段联合索引时,WHERE中过滤条件的字段顺序无需和索引一致,但如果有排序、分组则就必须一致了。

比如有联合索引 idx1(a, b, c),那么下面的SQL都可以完整用到索引:

SELECT ... WHERE b = ? AND c = ? AND a = ?;  --注意到,WHERE中字段顺序并没有和索引字段顺序一致

SELECT ... WHERE b = ? AND a = ? AND c = ?;

SELECT ... WHERE a = ? AND b IN (?, ?) AND c = ?;

SELECT ... WHERE a = ? AND b = ? ORDER BY c;

SELECT ... WHERE a = ? AND b IN (?, ?) ORDER BY c;

SELECT ... WHERE a = ? ORDER BY b, c;

SELECT ... ORDER BY a, b, c;  -- 可利用联合索引完成排序

而下面几个SQL则只能用到部分索引,或者可利用到ICP特性:

SELECT ... WHERE b = ? AND a = ?; -- 只能用到 (a, b) 部分

SELECT ... WHERE a IN (?, ?) AND b = ?; -- EXPLAIN显示只用到 (a, b) 部分索引,同时有ICP

SELECT ... WHERE (a BETWEEN ? AND ?) AND b = ?; -- EXPLAIN显示只用到 (a, b) 部分索引,同时有ICP

SELECT ... WHERE a = ? AND b IN (?, ?); -- EXPLAIN显示只用到 (a, b) 部分索引,同时有ICP

SELECT ... WHERE a = ? AND (b BETWEEN ? AND ?) AND c = ?; -- EXPLAIN显示用到 (a, b, c) 整个索引,同时有ICP

SELECT ... WHERE a = ? AND c = ?; -- EXPLAIN显示只用到 (a) 部分索引,同时有ICP

SELECT ... WHERE a = ? AND c >= ?; -- EXPLAIN显示只用到 (a) 部分索引,同时有ICP

ICP(index condition pushdown)是MySQL 5.6的新特性,其机制会让索引的其他部分也参与过滤,减少引擎层和server层之间的数据传输和回表请求,通常情况下可大幅提升查询效率。

下面的几个SQL完全用不到该索引:

SELECT ... WHERE b = ?;

SELECT ... WHERE b = ? AND c = ?;

SELECT ... WHERE b = ? AND c = ?;

SELECT ... ORDER BY b;

SELECT ... ORDER BY b, a;

从上面的几个例子就能看的出来,以往强调的WHERE条件字段顺序要和索引顺序一致才能使用索引的 “常识性误导” 无需严格遵守。

此外,有些时候查询优化器指定的索引或执行计划可能并不是最优的,可以手工指定最优索引,或者修改session级的 optimizer_switch 选项,关闭某些导致效果反而更差的特性(比如index merge通常是好事,但也遇到过用上index merge后反而更差的,这时候要么强制指定其中一个索引,要么可以临时关闭 index merge 特性)。

7、其他

7.1、哪怕是基于索引的条件过滤,如果优化器意识到总共需要扫描的数据量超过30%时(ORACLE里貌似是20%,MySQL目前是30%,没准以后会调整),就会直接改变执行计划为全表扫描,不再使用索引。

7.2、多表JOIN时,要把过滤性最大(不一定是数据量最小哦,而是只加了WHERE条件后过滤性最大的那个)的表选为驱动表。此外,如果JOIN之后有排序,排序字段一定要属于驱动表,才能利用驱动表上的索引完成排序。

7.3、绝大多数情况下,排序的大家通常要来的更高,因此如果看到执行计划中有 Using filesort,优先创建排序索引吧。

7.4、利用 pt-query-digest 定期分析slow query log,并结合 Box Anemometer 构建slow query log分析及优化系统。

[MySQL FAQ]系列 — EXPLAIN结果中哪些信息要引起关注

备注:若无特别说明,以上规范建议适用于MySQL 5.6及之前的版本(并且主要是5.6版本,尤其是ICP特性、DATETIME变化这两个地方)。5.7及之后的版本可能会有些变化,个别规范建议需要相应调整。

延伸阅读:

简单几招提高MySQL安全性

导读

如何提高MySQL的安全性

数据库的安全性无疑很重要,这里教大家几招简单方法提高安全性。

1. 正确设置 datadir 权限模式

关于 datadir 正确的权限模式是 0750,甚至是 0700。
也就是最多只允许 mysqld 进程属主用户及其所在用户组可访问,但只有属主可修改文件。
最好是直接设置成 0700,相对更安全些,避免数据文件意外泄漏。

[yejr@imysql.com]# chown -R mysql.mysql /data/mysql57
[yejr@imysql.com]# chmod 0700 /data/mysql57

[yejr@imysql.com]# ls -la /data/
drwxr-x---.  8 mysql mysql 4096 Feb 14 08:08 mysql57

2. 将 mysql socket 文件放在 datadir 下

很多人习惯将 mysql socket文件放在 /tmp 目录下,尤其是跑多实例时,/tmp 目录下可能有 mysql3306.sock、mysql3307.sock、mysql3308.sock 等多个这样的文件。
要注意,mysql.sock 文件默认的权限模式是 0777,也就是任何人都有机会通过 /tmp 目录下的 socket 文件直接登入 mysql,尤其是root密码为空或弱密码,并且还允许本地 socket 方式登入时,是个比较危险的安全隐患。
因此,我们强烈建议把 mysql socket 文件放置在每个实例自己的 datadir 下,并且参考第一条建议,设置正确的权限模式。

[yejr@imysql.com]# chmod 0700 /data/mysql57/mysql.sock

[yejr@imysql.com]# ls -la /data/mysql57/mysql.sock
srwx------. 1 mysql mysql 0 Feb 12 16:00 /data/mysql57/mysql.sock

3. 使用login-path

一般来说,我们会为每个mysql账户设置密码,这样是安全了,但使用和维护起来就不方便了,每次都要输入密码,尤其是调用mysql client工具时,如果直接将密码写在client工具的选项里,则是非常危险的行为,从历史命令就能看到密码了,会有类似下面的提示:

mysql: [Warning] Using a password on the command line interface can be insecure.

这时候,我们其实可以利用login-path功能来提高安全性及便利性。这个特性是MySQL 5.6新增的。
首先,利用 mysql_config_editor 配置login-path:

#选项 ”-G lp-mysql57-3306”设定login-path的别名
mysql_config_editor set -G lp-mysql57-3306 -S /data/mysql57/mysql.sock -uroot -p

设置完后,就会在该用户的家目录下生成 .mylogin.cnf 文件:

[yejr@imysql.com]# ls -la ~/.mylogin.cnf
-rw-------. 1 yejr users 152 Feb 11 22:42 /home/yejr/.mylogin.cnf

[yejr@imysql.com]# file ~/.mylogin.cnf
/home/yejr/.mylogin.cnf: data

这是个加密的二进制文件,即便用明文方式查看,也是无法显示密码的:

[yejr@imysql.com]# mysql_config_editor print --all
mysql_config_editor print --all
[lp-mysql57-13306]
user = root
password = *****
socket = /data/mysql57/mysql.sock

接下来可以利用 login-path 很方便的登入 mysqld 而无需额外的密码:

[yejr@imysql.com]# mysql --login-path=lp-mysql57-13306 -e "select 1+1 from dual"
+-----+
| 1+1 |
+-----+
|   2 |
+-----+

[yejr@imysql.com]# mysqladmin --login-path=lp-mysql57-13306 pr
+----+------+-----------+----+---------+------+----------+------------------+
| Id | User | Host      | db | Command | Time | State    | Info             |
+----+------+-----------+----+---------+------+----------+------------------+
| 3  | root | localhost |    | Query   | 0    | starting | show processlist |
+----+------+-----------+----+---------+------+----------+------------------+

在做好前面两条安全规则的前提下,即便万一家目录下的 .mylogin.cnf 文件被其他普通用户盗取,也无法利用 socket 方式登入 mysql,当然了,除非你之前在 login-path 里设置的是走 tcp/ip 方式,那就悲剧了~

下面是假设 yejr 普通账号想利用 root 账号的 .mylogin.cnf 文件登入,报告失败,因为无法访问 /data/mysql57/mysql.sock 文件:

[yejr@imysql ~]$ /usr/local/mysql57/bin/mysql --login-path=lp-mysql57-13306
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/data/mysql57/mysql.sock' (13)

4. 防范误操作

为了避免通过mysql cli客户端工具误操作执行全表更新/删除,以及大数据量的SELECT、JOIN,可以在 my.cnf 的 [mysql] 区间里设置下面几个选项:

[mysql]
safe-updates
select_limit=4294967295
max_join_size=4294967295

这样就可以避免通过mysql cli客户端工具执行类似下面的SQL了:

DELETE FROM t;
UPDATE t set c=?;
DELETE FROM t WHERE non_key = ?;
UPDATE t set c=? WHERE non_key = ?;

除非是改成像下面这样的:

UPDATE t SET not_key_column=? LIMIT 1;
DELETE FROM t LIMIT 1;

延伸阅读

如何更快随机UPDATE?

导读

UPDATE + RAND()怎么可以更快?

有时候,我们随机更新几行数据,可能会下意识的直接写成下面的SQL:

UPDATE t1 SET c1 = ? WHERE id = ROUND(RAND() * 102400);

不过你可能不知道,这个SQL的效率极低,需要进行全表扫描,因为无法使用索引:

[yejr]@[imysql.com]> EXPLAIN UPDATE t1 SET c1 = 3 WHERE id = ROUND(RAND() * 102400);
*************************** 1. row ***************************
           id: 1
  select_type: UPDATE
        table: t1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 102400
     filtered: 100.00
        Extra: Using where

这就尴尬了。

关注我网站(imysql.com)的同学,可能还记得我以前还写过一个关于随机排序的分享:[MySQL优化案例]系列 — RAND()优化。可以借鉴这篇文章的思路,把上面的SQL用JOIN改造一下:

[yejr]@[imysql.com]> EXPLAIN UPDATE t1, (SELECT 
 ROUND(RAND() * (SELECT MAX(id) FROM t1)) AS rndid) t2 
 SET t1.c1=3 WHERE t1.id=t2.rndid;

*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
   partitions: NULL
         type: system
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: UPDATE
        table: t1
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
*************************** 3. row ***************************
           id: 2
  select_type: DERIVED
        table: NULL
   partitions: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: No tables used
*************************** 4. row ***************************
           id: 3
  select_type: SUBQUERY
        table: NULL
   partitions: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: Select tables optimized away

再来看下两种 UPDATE 的代价:

[yejr]@[imysql.com]>UPDATE t1 SET c1 = 3 WHERE id = ROUND(RAND()*102400);
Query OK, 1 row affected (0.69 sec)

[yejr]@[imysql.com]>SHOW STATUS LIKE 'handler%read%';
+-----------------------+--------+
| Variable_name         | Value  |
+-----------------------+--------+
| Handler_read_first    | 1      |
| Handler_read_key      | 1      |
| Handler_read_last     | 0      |
| Handler_read_next     | 0      |
| Handler_read_prev     | 0      |
| Handler_read_rnd      | 0      |
| Handler_read_rnd_next | 799995 |
+-----------------------+--------+

[yejr]@[imysql.com]>show profile for query 5;
...
| System lock          | 0.000040 |
| updating             | 0.691625 |
| end                  | 0.000020 |
| query end            | 0.000515 |
...

[yejr]@[imysql.com]>UPDATE t1, (SELECT 
 ROUND(RAND() * (SELECT MAX(id) FROM t1)) AS rndid) t2 
 SET t1.c1=3 WHERE t1.id=t2.rndid;
Query OK, 1 row affected (0.02 sec)

[yejr]@[imysql.com]>SHOW STATUS LIKE 'handler%read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 1     |
| Handler_read_key      | 3     |
| Handler_read_last     | 1     |
| Handler_read_next     | 0     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 1     |
| Handler_read_rnd_next | 3     |
+-----------------------+-------+

[yejr]@[imysql.com]>show profile for query 6;
...
| updating reference tables | 0.011772 |
| end                       | 0.000040 |
| end                       | 0.000012 |
| removing tmp table        | 0.000018 |
| end                       | 0.000005 |
...
| query end                 | 0.014745 |
...

不过,上面这种多表UPDATE(Multiple-table UPDATE)有局限性,就是只能更新一行记录,不能同时更新多行,所以也可以改写成下面的SQL:

[yejr]@[imysql.com]> set @rnd_id=ROUND(RAND()*102400);  UPDATE t1 SET c1=3 WHERE id>=@rnd_id LIMIT 2;

延伸阅读:

说说MySQL ORDER BY

导读

在MySQL里,ORDER BY可以有几种玩法?

先看下手册里的说明:

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
....
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ...]

也就是,有三种ORDER BY模式,下面分别简单演示下。

测试表:

[yejr]@[imysql.com] [myDB]>show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `c1` int(10) unsigned NOT NULL DEFAULT '0',
  `c2` int(10) unsigned NOT NULL DEFAULT '0',
  `c3` int(10) unsigned NOT NULL DEFAULT '0',
  `c4` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`c1`),
  KEY `c2` (`c2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

[yejr]@[imysql.com] [myDB]>select * from t1;
+----+----+----+-----+
| c1 | c2 | c3 | c4  |
+----+----+----+-----+
|  0 |  0 |  0 |   0 |
|  1 |  1 |  1 |   0 |
|  3 |  3 |  3 |   0 |
|  4 |  2 |  2 |   0 |
|  6 |  8 |  5 | 123 |
|  7 |  6 |  6 | 123 |
| 10 | 10 |  4 | 123 |
+----+----+----+-----+

例1. 按指定列名ORDER BY

[yejr]@[imysql.com] [myDB]>select * from t1 order by c2;
+----+----+----+-----+
| c1 | c2 | c3 | c4  |
+----+----+----+-----+
|  0 |  0 |  0 |   0 |
|  1 |  1 |  1 |   0 |
|  4 |  2 |  2 |   0 |
|  3 |  3 |  3 |   0 |
|  7 |  6 |  6 | 123 |
|  6 |  8 |  5 | 123 |
| 10 | 10 |  4 | 123 |
+----+----+----+-----+

例2. 按指定序号的列排序

#按第二个列排序(同例1)
[yejr]@[imysql.com] [myDB]>select * from t1 order by 2;
+----+----+----+-----+
| c1 | c2 | c3 | c4  |
+----+----+----+-----+
|  0 |  0 |  0 |   0 |
|  1 |  1 |  1 |   0 |
|  4 |  2 |  2 |   0 |
|  3 |  3 |  3 |   0 |
|  7 |  6 |  6 | 123 |
|  6 |  8 |  5 | 123 |
| 10 | 10 |  4 | 123 |
+----+----+----+-----+

#按第三个列排序
[yejr]@[imysql.com] [myDB]>select * from t1 order by 3;
+----+----+----+-----+
| c1 | c2 | c3 | c4  |
+----+----+----+-----+
|  0 |  0 |  0 |   0 |
|  1 |  1 |  1 |   0 |
|  4 |  2 |  2 |   0 |
|  3 |  3 |  3 |   0 |
| 10 | 10 |  4 | 123 |
|  6 |  8 |  5 | 123 |
|  7 |  6 |  6 | 123 |
+----+----+----+-----+

例3. 根据表达式排序

#ORDER BY c3=3 DESC,也就是如果某条记录c3=3,则它排在第一位
#其他非c3=3的记录,则按照聚集索引的顺序显示
[yejr]@[imysql.com] [myDB]>select * from t1 order by c3=3 desc;
+----+----+----+-----+
| c1 | c2 | c3 | c4  |
+----+----+----+-----+
|  3 |  3 |  3 |   0 |
|  0 |  0 |  0 |   0 |
|  1 |  1 |  1 |   0 |
|  4 |  2 |  2 |   0 |
|  6 |  8 |  5 | 123 |
|  7 |  6 |  6 | 123 |
| 10 | 10 |  4 | 123 |
+----+----+----+-----+

#甚至还可以用case when
#这个例子中,当c3=3时,会被重置成10,其余按照实际值倒序排
[yejr]@[imysql.com] [myDB]>select * from t1 order by 
case when c3=3 then 10 else c3 end desc;
+----+----+----+-----+
| c1 | c2 | c3 | c4  |
+----+----+----+-----+
|  3 |  3 |  3 |   0 |
|  7 |  6 |  6 | 123 |
|  6 |  8 |  5 | 123 |
| 10 | 10 |  4 | 123 |
|  4 |  2 |  2 |   0 |
|  1 |  1 |  1 |   0 |
|  0 |  0 |  0 |   0 |
+----+----+----+-----+

小结

  1. 建议还是用常规的排序语法,别写太奇葩的子句,没准哪天就踩坑了;
  2. MySQL 8.0之前,还不支持倒序索引,但可以支持基于索引的倒序排序(利用索引的有序性,倒序排序,性能也并不差)。当然了,如果有个多列索引,几个列排序顺序不一样的话,那么在8.0以前是不支持的;

延伸阅读