注意使用 BTREE 复合索引各字段的 ASC/DESC 以优化 order by 查询效率

最新推荐文章于 2023-04-13 10:32:50 发布
最新推荐文章于 2023-04-13 10:32:50 发布
阅读量2k 收藏
点赞数
分类专栏： Mysql
本文针对一个包含190万条记录的MySQL表tbl_direct_pos_201506进行了查询优化，通过调整ORDER BY子句使排序方向与索引一致，成功将查询时间从2.478秒降低到0.029秒。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

tbl_direct_pos_201506 表有 190 万数据，DDL：
[sql]  view plain  copy
 print ?
  1. CREATE TABLE `tbl_direct_pos_201506` (  
  2.   `acq_ins_code` char(13) NOT NULL DEFAULT '' COMMENT '机构代码',  
  3.   `trace_num` char(6) NOT NULL DEFAULT '' COMMENT '跟踪号',  
  4.   `trans_datetime` char(10) NOT NULL DEFAULT '' COMMENT '交易时间',  
  5.   `process_flag` char(1) DEFAULT NULL COMMENT '处理标识',  
  6.   `rev_flag` char(1) DEFAULT NULL COMMENT '接收标识',  
  7.   `before_trans_code` char(3) DEFAULT NULL COMMENT '交易类型',  
  8.   `trans_amt` decimal(15,3) DEFAULT NULL COMMENT '交易金额',  
  9.   `acct_num` char(21) DEFAULT NULL COMMENT '卡号',  
  10.   `mer_type` char(4) DEFAULT NULL COMMENT '商户类型',  
  11.   `recv_ins_code` char(13) DEFAULT NULL COMMENT '发卡行代码',  
  12.   `retrivl_ref_num` char(12) DEFAULT NULL COMMENT '检索参考号',  
  13.   `resp_auth_code` char(6) DEFAULT NULL COMMENT '授权码',  
  14.   `resp_code` char(2) DEFAULT NULL COMMENT '应答码',  
  15.   `term_id` char(8) DEFAULT NULL COMMENT '终端代码',  
  16.   `mer_code` char(15) DEFAULT NULL COMMENT '商户代码',  
  17.   `mer_addr_name` char(40) DEFAULT NULL COMMENT '商户名称和地址，前 25 字节是名称，后面是地址',  
  18.   `self_define` varchar(300) DEFAULT NULL COMMENT '第 259 字节是卡片类型',  
  19.   `sys_date` char(8) NOT NULL DEFAULT '' COMMENT '交易日期',  
  20.   `sa_sav2` varchar(300) DEFAULT NULL COMMENT '第 243 字节是 DCC 标识',  
  21.   `rec_create_time` datetime DEFAULT NULL COMMENT '联机入库时间',  
  22.   `rec_update_time` datetime DEFAULT NULL COMMENT '最后修改时间',  
  23.   PRIMARY KEY (`sys_date`,`trans_datetime`,`acq_ins_code`,`trace_num`),  
  24.   KEY `idx_direct_pos_create_time` (`rec_create_time`)  
  25. ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='交易月表模板';  

关于该表的一个慢查询日志如下：
# Time: 150701 15:45:28
# User@Host: test[test] @ localhost [127.0.0.1]  Id:     1
# Query_time: 2.478195  Lock_time: 0.010007 Rows_sent: 20  Rows_examined: 450612
SET timestamp=1435736728;
select substr(t.acq_ins_code, 3) merAcqInsCode, t.mer_code, t.term_id, substr(t.mer_addr_name, 1, 12) merName,
tt.trans_desc, t.rev_flag, t.trans_amt, concat(substr(t.sys_date, 1, 4), t.trans_datetime) transTime,t.before_trans_code,
t.acct_num, t.retrivl_ref_num,  t.resp_code, t.resp_auth_code,  r.recv_ins_name,t.acq_ins_code,t.trace_num,t.trans_datetime,
case substr(t.sa_sav2,259,1)  when 1 then '借记卡' when 2 then '贷记卡'
when 3 then '准贷记卡' when 4 then '私有预付卡' else '' end  cardType,
case 
when locate('VIS',t.sa_sav2) > 0 then 'VISA' 
when locate('JCB',t.sa_sav2) > 0 then 'JCB' 
when locate('DNC',t.sa_sav2) > 0 then '大莱卡' 
when locate('CUP',t.sa_sav2) > 0 then '银联境内卡' 
when locate('UPI',t.sa_sav2) > 0 then '银联境外卡'
else '' end cardBrand 
from tbl_direct_pos_201506 t
left join trans_recv_ins r on r.recv_ins_code = t.recv_ins_code
left join tbl_trans_type tt on tt.trans_code = t.before_trans_code
where t.sys_date between '20150622' and '20150628' 
order by
 t.sys_date desc, t.trans_datetime desc, t.acq_ins_code, t.trace_num 
limit 0, 20;
日志中可以看出该 sql 的执行时间是 2.478 s。
我们来查看一下该 sql 的执行计划：
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEtrangePRIMARYPRIMARY24 299392Using index condition; Using filesort
1SIMPLEreq_refPRIMARYPRIMARY41msp.t.recv_ins_code1Using where
1SIMPLEtteq_refPRIMARYPRIMARY14msp.t.before_trans_code1Using where

执行计划分析：
Using filesort。是的，看到它，说明我们的查询需要优化了：文件排序是通过相应的排序算法，将取得的数据在内存中进行排序。
MyISAM 存储引擎的主键索引和非主键索引差别很小，只不过是主键索引的索引键是一个唯一且非空的键而已。MyISAM 的索引默认为 B-TREE。也就是说，主键在这里相当于一个普通的 B-TREE。
该 sql 一个 where 字段，四个 order by 字段，都在主键里边呀，而且 order by 的顺序完全符合最左前缀原则，为什么还要 filesort？
MySql 索引创建手册 里如是说：
索引列的定义可以跟随 ASC 或者 DESC。这些关键字允许为未来扩展用于指定升序或降序索引值存储。这个语法会被解析但却被忽略。索引列总是以升序排列。 ——也就是说你写了不会报错，但写了白写。
这样看来，我们的主键没起排序作用，原因就在于我们的主键是各主键字段 asc 存储， order by 里 desc 和 asc(默认是 asc) 混用。为了验证这个说法，我们把该 order by 换为和主键一致的 asc：
[sql]  view plain  copy
 print ?
  1. select substr(t.acq_ins_code, 3) merAcqInsCode, t.mer_code, t.term_id, substr(t.mer_addr_name, 1, 12) merName,  
  2.                 tt.trans_desc, t.rev_flag, t.trans_amt, concat(substr(t.sys_date, 1, 4), t.trans_datetime) transTime,t.before_trans_code,  
  3.                 t.acct_num, t.retrivl_ref_num,  t.resp_code, t.resp_auth_code,  r.recv_ins_name,t.acq_ins_code,t.trace_num,t.trans_datetime,  
  4.                 case substr(t.sa_sav2,259,1)  when 1 then '借记卡' when 2 then '贷记卡'  
  5.                 when 3 then '准贷记卡' when 4 then '私有预付卡' else '' end  cardType,  
  6.                 case   
  7.                     when locate('VIS',t.sa_sav2) > 0 then 'VISA'   
  8.                     when locate('JCB',t.sa_sav2) > 0 then 'JCB'   
  9.                     when locate('DNC',t.sa_sav2) > 0 then '大莱卡'   
  10.                     when locate('CUP',t.sa_sav2) > 0 then '银联境内卡'   
  11.                     when locate('UPI',t.sa_sav2) > 0 then '银联境外卡'  
  12.                     else '' end cardBrand   
  13.             from tbl_direct_pos_201506 t  
  14.             left join trans_recv_ins r on r.recv_ins_code = t.recv_ins_code  
  15.             left join tbl_trans_type tt on tt.trans_code = t.before_trans_code  
  16.             where t.sys_date between '20150622' and '20150628'   
  17.             order by  
  18.              t.sys_date, t.trans_datetime, t.acq_ins_code, t.trace_num  
  19.             limit 0, 20;  

执行时间：0.023 s。
结果差强人意。查看其执行计划：
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEtrangePRIMARYPRIMARY24 299392Using index condition
1SIMPLEreq_refPRIMARYPRIMARY41msp.t.recv_ins_code1Using where
1SIMPLEtteq_refPRIMARYPRIMARY14msp.t.before_trans_code1Using where

果然，我们利用到了主键索引，Using filesort 没有了。
既然找的了问题的症兆所在，接下来的事情似乎只是走流程了。
问了下业务，分页结果里 sys_date 和 trans_datetime 两个字段必须降序排列，其余两个字段倒不是很在意。
既然我们无法更改索引每一列的降序、升序(默认为升序)，那么我们可以在写 order by 的时候让索引各字段降序/升序一致。最终的 sql 改写为：
[sql]  view plain  copy
 print ?
  1. select substr(t.acq_ins_code, 3) merAcqInsCode, t.mer_code, t.term_id, substr(t.mer_addr_name, 1, 12) merName,  
  2.                 tt.trans_desc, t.rev_flag, t.trans_amt, concat(substr(t.sys_date, 1, 4), t.trans_datetime) transTime,t.before_trans_code,  
  3.                 t.acct_num, t.retrivl_ref_num,  t.resp_code, t.resp_auth_code,  r.recv_ins_name,t.acq_ins_code,t.trace_num,t.trans_datetime,  
  4.                 case substr(t.sa_sav2,259,1)  when 1 then '借记卡' when 2 then '贷记卡'  
  5.                 when 3 then '准贷记卡' when 4 then '私有预付卡' else '' end  cardType,  
  6.                 case   
  7.                     when locate('VIS',t.sa_sav2) > 0 then 'VISA'   
  8.                     when locate('JCB',t.sa_sav2) > 0 then 'JCB'   
  9.                     when locate('DNC',t.sa_sav2) > 0 then '大莱卡'   
  10.                     when locate('CUP',t.sa_sav2) > 0 then '银联境内卡'   
  11.                     when locate('UPI',t.sa_sav2) > 0 then '银联境外卡'  
  12.                     else '' end cardBrand   
  13.             from tbl_direct_pos_201506 t  
  14.             left join trans_recv_ins r on r.recv_ins_code = t.recv_ins_code  
  15.             left join tbl_trans_type tt on tt.trans_code = t.before_trans_code  
  16.             where t.sys_date between '20150622' and '20150628'   
  17.             order by  
  18.              t.sys_date desc, t.trans_datetime desc, t.acq_ins_code desc, t.trace_num desc   
  19.             limit 0, 20;  

执行之，0.029 s，搞定。


参考资料

MySQL 8.x新特性之——优化器索引
冰河的专栏
11-30 2017
MySQL 8.x中新增了三种索引方式，如下所示。 隐藏索引 降序索引 函数索引 一、隐藏索引 1.隐藏索引概述 MySQL 8.0开始支持隐藏索引（invisible index），不可见索引。 隐藏索引不会被优化器使用，但仍然需要进行维护。 应用场景：软删除、灰度发布。 在之前MySQL的版本中，只能通过显式的方式删除索引，如果删除后发现索引删错了，又只能通过创建索引的方式将删除...
性能调优专题(3)之Mysql索引优化实战一
jokeMqc的博客
11-05 91
1、MySQL支持两种方式的排序filesort和index，Using index是指MySQL扫描索引本身完成排序。index效率高，filesort效率低。2、order by满足两种情况会使用Using index。1) order by语句使用索引最左前列。2) 使用where子句与order by子句条件列组合满足索引最左前列。3、尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则。4、如果order by的条件不在索引列上，就会产生Using filesort。
Oracle 之索引，妙不可言（三）——BTREE索引下
qq_36620997的博客
04-13 344
Oracle ——BTREE索引
对于mysql中B-Tree复合索引中各字段的ASC/DESC注意点以及ORDER BY优化等
renjingzhan的博客
05-30 1180
CREATE TABLE `tbl_direct_pos_201506` ( `acq_ins_code` char(13) NOT NULL DEFAULT '' COMMENT '机构代码', `trace_num` char(6) NOT NULL DEFAULT '' COMMENT '跟踪号', `trans_datetime` char(10) NOT NULL DEFA...
使用倒序索引提升ORDER BY DESC性能
IT圈黎俊杰
06-30 1万+
使用倒序索引（INDEX DESC），可以大幅提升带有order by desc子句的SQL语句性能。
[mysql]SQL语句性能优化--Order by中加DESC慢很多的原因调查与处理
王德封-逐浪
06-23 4万+
1.mysql在数据量较大的时候、使用order by查询结果集时速度很慢的原因可能有以下几种：1） 排序字段没有建索引2）排序字段加DESC后索引没有起作用（如何让索引起作用才是关键、且听下文分解）3）排序字段中加函数导致索引不起作用（这种一定要避免、本文不对这种情况展开说明）2.用实例说明排序字段中增加DESC后索引不起作用、查询速度很慢1）例如以下SQL、执行起来需要5秒左右、太慢不可接受：...
POSTGRESQL (PG) 6种索引类型介绍以及使用实例
helenbi的博客
11-18 1万+
POSTGRES 6种索引介绍以及使用实例
数据库优化-排序篇 (order by)
学习真香
02-27 3934
数据库优化-排序篇 (order by) 背景 日常开发工作中, 经常使用order by对结果集进行排序, 但是对其原理和具体实现可能不是很了解. 这样无法进行有效的分析和针对性优化. 因此, 我写下本篇文章介绍一下 order by的基本原理和一些工作中常见的问题. 带着问题去学习, 效率会更高, 更有趣. 数据准备 因为在工作中使用pg多一些, 所以文中的数据库选择了 PostgreSQL 11.14 64-bit 造一些测试数据, 可以参考: pg 快速造1000w测试数据 CREATE TABLE
MySQL 的索引
彳亍
11-04 2722
在 MySQL 中，索引（index）也叫做键（key），它是存储引擎用于快速找到记录的一种数据结构。 索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时，索引对性能的影响就愈发重要。 索引优化是对查询性能优化最有效的手段。
PostgreSQL索引详解4——btree索引
Focus on PostgreSQL
05-07 4911
1、简介 B树索引类型可以说是使用的最广泛的索引了，在PostgreSQL中可以在任何数据类型上使用btree索引，支持排序，支持大于、小于、等于、大于或等于、小于或等于的搜索。 B树具有一些重要的特征： B树是平衡的，也就是说，每个叶页面与根都由相同数量的内部页面分隔开。因此，搜索任何值都需要花费相同的时间。 B树是多分支的，即每个页面（通常为8 KB）包含许多（数百个）ctid。因此，B树的...
mysql asc_mysql – 在字符串列上使用asc和desc的索引
weixin_42300435的博客
02-26 880
当我们想要通过asc和desc订购时,我知道一个通​​过查询更快地进行排序的技巧.对于整数存储否定值.因此,如果我存储-7,-11,-8,-5,-1,-2,则asc的顺序将给出-11,-8,-7,5,-2,-1,因此实际的行将是按该列排序desc(例如,如果我显示非否定版本).问题：如何在字符串中使用此技巧？如何将字符串“否定”或反转,以便该字符串也适用于字符串列？更新MySQL无法使用索引进行类...
mysql order by 索引名字_MySQL|mysql-索引
weixin_42715262的博客
01-27 354
1、索引是什么1.1索引简介索引是表的目录，是数据库中专门用于帮助用户快速查询数据的一种数据结构。类似于字典中的目录，查找字典内容时可以根据目录查找到数据的存放位置，以及快速定位查询数据。对于索引，会保存在额外的文件中。1.2主要注意的地方：(1).索引可以提高查询速度，会减慢写入速度，索引的缺点时创建和维护索引需要耗费时间。(2).索引并不是越多越好，索引固然可以提高相应的select的效率，但...
MySQL数据排序asc、desc
热门推荐
Jason的博客
03-25 35万+
数据排序 asc、desc 1、单一字段排序order by 字段名称 作用： 通过哪个或哪些字段进行排序 含义： 排序采用 order by 子句，order by 后面跟上排序字段，排序字段可以放多个，多个采用逗号间隔，order by默认采用升序（asc），如果存在 where 子句，那么 order by 必须放到where 语句后面。 （1）、按照薪水由小到大排序（系统默认由小到...
Mysql| order by 排序检索数据(ASC,DESC)
YvesHe的专栏
11-12 1万+
在myslq数据中,检索出来的数据往往是以底层数据添加到表中的顺序显示的,但是可能存在更新和删除操作,这样就会影响排序顺序,所有在没有指定排序顺序时从数据中查询的数据是没有排序意义的.这里就需要使用Oder by 子句来实现数据的排序.EG: SELECT * FROM products ORDER BY products.prod_price DESC,products.prod_name
sphinx是支持结果聚类的——WHERE、ORDER BY和GROUP BY
djph26741的博客
03-31 172
原生API提供的匹配筛选、排序和分组配置和SQL语法提供的WHERE、ORDER BY和GROUP BY语句的效果是一样的，你可以对匹配结果进行你需要的筛选、排序和分组匹配。例如，如果你要搜索MySQL中1990年代的书籍，并按照价格排序，可以这么写： $cl = new SphinxClient(); $cl->SetFilterRange("year_publis...
asc desc排序_为排序使用索引Order By优化
weixin_34933151的博客
01-18 695
CREATE TABLE `tblA`( `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT, `age` INT(11) DEFAULT NULL, `birth` timestamp not NULL )ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; insert into t...
高斯数据库给字段加索引
最新发布
07-10
<think>我们正在讨论高斯数据库（GaussDB）中如何为字段添加索引。高斯数据库是华为推出的一款关系型数据库，支持标准的SQL语法。在关系型数据库中，索引是提高查询效率的重要工具。 为字段添加索引的基本SQL语法如下： ```sql CREATE INDEX index_name ON table_name (column_name); ``` 如果要创建唯一索引（即索引列的值必须唯一），可以使用： ```sql CREATE UNIQUE INDEX index_name ON table_name (column_name); ``` 另外，高斯数据库也支持创建复合索引（多个列）： ```sql CREATE INDEX index_name ON table_name (column1, column2, ...); ``` 在创建索引时，还可以指定索引的类型（如B-tree，Hash等），但高斯数据库默认使用B-tree索引。如果需要指定其他类型，可以这样： ```sql CREATE INDEX index_name ON table_name USING hash (column_name); ``` 注意：索引类型的选择需要根据具体的使用场景。B-tree索引适用于范围查询和等值查询，而Hash索引只适用于等值查询。 另外，在大型表上创建索引可能会比较耗时，并且会锁表（在创建过程中可能会影响写操作）。因此，建议在系统负载较低时进行操作。 下面给出一个完整的示例，假设我们有一个名为`employees`的表，其中有一个`last_name`字段，我们想要在这个字段上创建一个索引： ```sql -- 创建索引 CREATE INDEX idx_employees_last_name ON employees (last_name); ``` 如果我们想要在`department_id`和`salary`上创建一个复合索引： ```sql CREATE INDEX idx_employees_dept_salary ON employees (department_id, salary); ``` 在创建索引后，当执行查询条件或排序涉及这些字段时，数据库可能会使用索引来加速操作。 需要注意的是，索引虽然可以提高查询速度，但也会降低数据插入、更新和删除的速度，因为索引也需要维护。因此，索引不是越多越好，需要根据实际查询需求来创建。 另外，高斯数据库还支持部分索引（只对表的一部分数据建立索引）和表达式索引（对表达式的结果建立索引），但这里不展开。 解释： 1. `CREATE INDEX` 是创建索引的标准SQL命令。 2. `index_name` 是索引的名称，必须是唯一的。 3. `table_name` 是要创建索引的表名。 4. `column_name` 是要在其上创建索引的列名，可以是一个或多个（复合索引）。 最后，如果你需要删除索引，可以使用： ```sql DROP INDEX index_name; ``` 请根据实际表结构和查询需求来创建索引。 §§相关问题§§: 1. 高斯数据库中如何查看一个表已有的索引？ 2. 索引对数据库性能有哪些影响（包括正反两方面）？ 3. 在高斯数据库中，如何重建索引？ 4. 高斯数据库支持哪些类型的索引？</think>### 高斯数据库（GaussDB）字段索引添加方法 在高斯数据库中，为字段添加索引的基本语法如下： ```sql CREATE [UNIQUE] INDEX index_name ON table_name (column_name [ASC | DESC], ...) [LOCAL | GLOBAL] [USING index_type]; ``` #### 关键参数说明： - **UNIQUE**：创建唯一索引 - **index_name**：自定义索引名称 - **table_name**：目标表名 - **column_name**：要索引的字段（支持多列） - **LOCAL/GLOBAL**：分布式索引作用域 - **index_type**：索引类型（如 BTREE、GIN、HASH 等） --- ### 示例代码 #### 1. 单字段普通索引 ```sql -- 在employees表的last_name字段创建B树索引 CREATE INDEX idx_emp_lastname ON employees (last_name) USING BTREE; ``` #### 2. 多字段复合索引 ```sql -- 在orders表创建复合索引（优先按日期排序） CREATE INDEX idx_orders_date_customer ON orders (order_date DESC, customer_id); ``` #### 3. 唯一索引 ```sql -- 在users表的email字段创建唯一索引 CREATE UNIQUE INDEX idx_unique_email ON users (email); ``` #### 4. 分布式表索引（LOCAL作用域） ```sql -- 分布式表的分片级索引 CREATE INDEX idx_product_name ON products (product_name) LOCAL; ``` --- ### 索引使用注意事项 1. **索引类型选择**： - **BTREE**：默认类型，适合范围查询 - **GIN**：适合JSON/数组等复杂类型 - **HASH**：仅支持等值查询 2. **性能影响**： - 索引会提升查询速度但降低写操作性能 - 建议在低峰期创建大表索引 - 定期使用`ANALYZE`更新统计信息 3. **查看现有索引**： ```sql SELECT * FROM pg_indexes WHERE tablename = 'your_table'; ``` 4. **删除索引**： ```sql DROP INDEX IF EXISTS idx_emp_lastname; ``` --- ### 索引优化建议 1. **选择性高的字段优先**（如唯一ID） 2. **避免过度索引**：每个索引增加约表大小10%-20% 3. **复合索引字段顺序**：高频查询字段放前面 4. **监控索引使用率**： ```sql SELECT * FROM pg_stat_all_indexes WHERE relname = 'your_table'; ``` ---
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值