[MySQL 5.6優化] --order by limit x,x 優化

  • 簡介：
    order by limit x ,x 在實際工作中有各種業務需求要有order by的排序，有時候處理不好則會造成系統宕機！
  • 原理：
    a.通過索引來獲取排序

b.通過內部算法獲取排序：

  • 案例

具體SQL：

SELECT c.order_price orderPrice,c.preferential_amount preferentialAmount,c.order_sumprice orderSumprice,cast(c.mode as SIGNED) rechargeType,cast(c.pay_type as SIGNED) payType,cast(c.type as SIGNED) appType,c.order_sn orderSn,c.create_time payTime,u.nickname nickName,u.headimgurl headImg,u.real_name memberName,cast(c.pay_status as SIGNED) payStatusFROM t_order cLEFT JOIN t_user u ON c.user_id= u.idWHERE c.token= '1392044'and c.pay_status in (1, 3)and c.refund_status= 0and c.store_id= 36574order by c.create_time desclimit 0,15

表結構：

CREATE TABLE `t_order ` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,`order_sn` varchar(30) DEFAULT NULL COMMENT ',`preferential_amount` decimal(10,2) DEFAULT '0.00' COMMENT,`order_sumprice` decimal(10,2) DEFAULT '0.00' COMMENT ,`mode` tinyint(3) unsigned DEFAULT '1' COMMENT '',`pay_type` tinyint(1) DEFAULT '1' COMMENT '',`type` tinyint(4) DEFAULT '1' COMMENT '',`create_time` int(10) unsigned DEFAULT '0' COMMENT '',PRIMARY KEY (`id`),UNIQUE KEY `order_sn` (`order_sn`),KEY `IDX_CR_MO_TO` (`create_time`,`token`,`user_id`),KEY `idx_store_token_createtime` (`store_id`,`token`,`create_time`) USING BTREE,
) ENGINE=InnoDB AUTO_INCREMENT=53925518 DEFAULT CHARSET=utf8CREATE TABLE `t_user ` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,`nickname` varchar(20) DEFAULT NULL COMMENT '',`headimgurl` varchar(255) DEFAULT NULL,`real_name` varchar(20) DEFAULT NULL,PRIMARY KEY (`id`),UNIQUE KEY `openid` (`openid`),KEY `IDX_NICKNAME` (`nickname')
) ENGINE=InnoDB AUTO_INCREMENT=13974852 DEFAULT CHARSET=utf8

1、SQL優化器默認選擇索引執行計劃為：

 *************************** 1. row ***************************id: 1select_type: SIMPLEtable: ctype: ref
possible_keys: idx_tscc,IDX_CR_MO_TOkey: idx_tscpkey_len: 68ref: const,constrows: 26980Extra: Using index condition; Using where; Using filesort
*************************** 2. row ***************************id: 1select_type: SIMPLEtable: utype: eq_ref
possible_keys: PRIMARYkey: PRIMARYkey_len: 4ref: youdian_life_sewsq.c.user_idrows: 1Extra: Using where共返回 2 行記錄,花費 5 ms.

執行時間：共返回 15 行記錄,花費 128 ms.

2、當使用IDX_CR_MO_TO (create_time,token,user_id)索引時，避免Using filesortl臨時表，減少rows
執行計劃為：

*************************** 1. row ***************************id: 1select_type: SIMPLEtable: ctype: index
possible_keys: key: IDX_CR_MO_TOkey_len: 73ref: rows: 15Extra: Using where
*************************** 2. row ***************************id: 1select_type: SIMPLEtable: utype: eq_ref
possible_keys: PRIMARYkey: PRIMARYkey_len: 4ref: youdian_life_sewsq.c.user_idrows: 1Extra: Using where

執行時間：共返回 15 行記錄,花費 234 ms

3、當使用limit 100時強制索引效果：

mysql>explain SELECT c.order_price orderPrice,c.preferential_amount preferentialAmount,c.order_sumprice orderSumprice,cast(c.mode as SIGNED) rechargeType,cast(c.pay_type as SIGNED) payType,cast(c.type as SIGNED) appType,c.order_sn orderSn,c.create_time payTime,u.nickname nickName,u.headimgurl headImg,u.real_name memberName,cast(c.pay_status as SIGNED) payStatusFROM tp_order c force index(IDX_CR_MO_TO)LEFT JOIN tp_user u ON c.user_id= u.idWHERE c.token= '1392044'and c.pay_status in (1, 3)and c.refund_status= 0and c.store_id= 36574order by c.create_time desclimit 100\G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: ctype: index
possible_keys: key: IDX_CR_MO_TOkey_len: 73ref: rows: 100Extra: Using where
*************************** 2. row ***************************id: 1select_type: SIMPLEtable: utype: eq_ref
possible_keys: PRIMARYkey: PRIMARYkey_len: 4ref: youdian_life_sewsq.c.user_idrows: 1Extra: Using where

3、當limit 為1000,10時候的效果：

強制索引：
mysql>explain SELECT c.order_price orderPrice,c.preferential_amount preferentialAmount,c.order_sumprice orderSumprice,cast(c.mode as SIGNED) rechargeType,cast(c.pay_type as SIGNED) payType,cast(c.type as SIGNED) appType,c.order_sn orderSn,c.create_time payTime,u.nickname nickName,u.headimgurl headImg,u.real_name memberName,cast(c.pay_status as SIGNED) payStatusFROM tp_order c force index(IDX_CR_MO_TO)LEFT JOIN tp_user u ON c.user_id= u.idWHERE c.token= '1392044'and c.pay_status in (1, 3)and c.refund_status= 0and c.store_id= 36574order by c.create_time desclimit 1000,10\G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: ctype: index
possible_keys: key: IDX_CR_MO_TOkey_len: 73ref: rows: 1010Extra: Using where
*************************** 2. row ***************************id: 1select_type: SIMPLEtable: utype: eq_ref
possible_keys: PRIMARYkey: PRIMARYkey_len: 4ref: youdian_life_sewsq.c.user_idrows: 1Extra: Using where
默認執行計劃：
************************** 1. row ***************************id: 1select_type: SIMPLEtable: ctype: ref
possible_keys:  idx_tscc,IDX_CR_MO_TOkey: idx_tscpkey_len: 68ref: const,constrows: 27002Extra: Using index condition; Using where; Using filesort
*************************** 2. row ***************************id: 1select_type: SIMPLEtable: utype: eq_ref
possible_keys: PRIMARYkey: PRIMARYkey_len: 4ref: youdian_life_sewsq.c.user_idrows: 1Extra: Using where

4、limit 1000,10執行時間對比

使用idx_tscc索引執行時間：
mysql>SELECT c.order_price orderPrice,c.preferential_amount preferentialAmount,c.order_sumprice orderSumprice,cast(c.mode as SIGNED) rechargeType,cast(c.pay_type as SIGNED) payType,cast(c.type as SIGNED) appType,c.order_sn orderSn,c.create_time payTime,u.nickname nickName,u.headimgurl headImg,u.real_name memberName,cast(c.pay_status as SIGNED) payStatusFROM tp_order c LEFT JOIN tp_user u ON c.user_id= u.idWHERE c.token= '1392044'and c.pay_status in (1, 3)and c.refund_status= 0and c.store_id= 36574order by c.create_time desclimit 1000,10\G
共返回 10 行記錄,花費 220 ms.使用強制索引執行時間：
mysql>SELECT c.order_price orderPrice,c.preferential_amount preferentialAmount,c.order_sumprice orderSumprice,cast(c.mode as SIGNED) rechargeType,cast(c.pay_type as SIGNED) payType,cast(c.type as SIGNED) appType,c.order_sn orderSn,c.create_time payTime,u.nickname nickName,u.headimgurl headImg,u.real_name memberName,cast(c.pay_status as SIGNED) payStatusFROM tp_order c  force index(IDX_CR_MO_TO)LEFT JOIN tp_user u ON c.user_id= u.idWHERE c.token= '1392044'and c.pay_status in (1, 3)and c.refund_status= 0and c.store_id= 36574order by c.create_time desclimit 1000,10\G
共返回 10 行記錄,花費 17444 ms.

總結： 具體場景具體分析：
本例子中

  1. 強制索引是索引全掃描，limit值越大性能就會越差
  2. 而默認走tscp 索引，是根據 where條件 token,store_id值ref 等值過濾的。效果比較強制IDX_CR_MO_TO

本文來自互聯網用戶投稿，該文觀點僅代表作者本人，不代表本站立場。本站僅提供信息存儲空間服務，不擁有所有權，不承擔相關法律責任。
如若轉載，請注明出處：http://www.pswp.cn/news/456198.shtml
繁體地址，請注明出處：http://hk.pswp.cn/news/456198.shtml
英文地址，請注明出處：http://en.pswp.cn/news/456198.shtml

如若內容造成侵權/違法違規/事實不符，請聯系多彩編程網進行投訴反饋email:809451989@qq.com，一經查實，立即刪除！

相關文章

【R】語言第四課----讀取文件

【R】語言第四課----讀取文件

install.packages("readxl") getwd() setwd("E:/作業4") library(readxl) tianmao<-read_excel(tianmaoTV.xlsx,skip1)#把第一行跳過&#xff0c;直接從第二行開始讀取 #創建新變量 tianmao[total_sales]<-tianmao$current_price*tianmao$month_sale…
閱讀更多...
Html.Partial和Html. RenderPartial用法

Html.Partial和Html. RenderPartial用法

Html.Partial和Html. RenderPartial用法Html.partial和RenderPartial的用法與區別Html.partial和RenderPartial都是輸出html片段&#xff0c;區別在于Partial是將視圖內容直接生成一個字符串并返回&#xff08;相當于有個轉義的過程&#xff09;&#xff0c;RenderPartial方法是…
閱讀更多...
算術編碼簡單研究

算術編碼簡單研究

算術編碼 是一種無損數據壓縮方法&#xff0c;也是一種熵編碼的方法。和其它熵編碼方法不同的地方在于&#xff0c;其他的熵編碼方法通常是把輸入的消息分割為符號&#xff0c;然后對每個符號進行編碼&#xff0c;而算術編碼是直接把整個輸入的消息編碼為一個數&#xff0c;一個…
閱讀更多...
Thinkphp5 還有這種操作？

Thinkphp5 還有這種操作？

2019獨角獸企業重金招聘Python工程師標準>>> 在 _initialize 中取出 控制器名和方法名 define(CONTROLLER_NAME,Request::instance()->controller()); define(MODULE_NAME,Request::instance()->module()); define(ACTION_NAME,Request::instance()->actio…
閱讀更多...
【R】語言第五課----畫圖

【R】語言第五課----畫圖

?plot#高級繪圖函數 可以完整地繪制出一張圖 ?mtcars plot(mtcars$wt) plot(mtcars[,1:2]) plot(mtcars) plot(mtcars$wt,mtcars$disp) plot(mtcars$wt,mtcars$disp,typep) plot(mtcars$wt,mtcars$disp,typel) plot(mtcars$wt,mtcars$disp,typeb) plot(mtcars$wt,mtcars$disp…
閱讀更多...
Solidworks如何將參考平面的圖形投影到某曲面上

Solidworks如何將參考平面的圖形投影到某曲面上

1 畫好草圖&#xff0c;點擊曲線-分割線 2 選擇要投影的草圖和被投影的面&#xff08;那個球面&#xff09;&#xff0c;最后效果如下圖所示 3 為了獲取連續的軌跡&#xff0c;我們可以再次選擇這個草圖&#xff0c;然后在投影面中選擇平面&#xff0c;最后得到的圖形如下圖所示…
閱讀更多...
向極限挑戰：算術編碼 (轉)

向極限挑戰：算術編碼 (轉)

向極限挑戰&#xff1a;算術編碼 (轉) http://blog.csdn.net/hhf383530895/archive/2009/08/24/4478605.aspx 我們在上一章中已經明白&#xff0c;Huffman 編碼使用整數個二進制位對符號進行編碼&#xff0c;這種方法在許多情況下無法得到最優的壓縮 效果。假設某個字符的出…
閱讀更多...
np.random.seed(0)作用

np.random.seed(0)作用

在用python時時常會看到如下代碼&#xff1a; import numpy as np np.random.seed(0) 其中np.random.seed(0)的作用是使得隨機數據可預測&#xff0c;當我們設置相同的seed&#xff0c;每次生成的隨機數相同。 如果不設置seed&#xff0c;則每次會生成不同的隨機數&#xf…
閱讀更多...
發送郵件被退回，提示： Helo command rejected: Invalid name 錯誤

發送郵件被退回，提示： Helo command rejected: Invalid name 錯誤

我自己配置的 postfix dovecot server&#xff0c; 配置了outlook 后&#xff0c; 相同的賬號。 在有的電腦上能收發成功&#xff0c; 在有的電腦上發送郵件就出現退信。提示 Helo command rejected: Invalid name 錯誤。經過分析&#xff0c; 原來是計算機名的問題。 計算機名…
閱讀更多...
Series和DataFrame、相關性及NaN處理

Series和DataFrame、相關性及NaN處理

pandas核心數據結構 pandas是以numpy為基礎的&#xff0c;還提供了一些額外的方法 Series series用來表示一維數據結構&#xff0c;與python內部的數組類似&#xff0c;但多了一些額外的功能。 series內部由兩個相互關聯的數組組成&#xff1a;主數組用來存放數組&#xff…
閱讀更多...
Hive謂詞解析過程分析

Hive謂詞解析過程分析

where col1 100 and abs(col2) > 0在Hive中的處理過程 where過濾條件稱為謂詞predicate。 以上where過濾條件在經過Hive的語法解析后&#xff0c;生成如下的語法樹&#xff1a; TOK_WHERE AND TOK_TABLE_OR_C…
閱讀更多...
算術編碼（Arithmetic Coding）源代碼

算術編碼（Arithmetic Coding）源代碼

Ian H. Witten、Radford M. Neal和John G. Cleary在1987年發表了一篇啟發性的論文。論文中描述了一種基于整數運算的通用算術編碼器&#xff0c;而且還給出了由計算錯誤導致的效率低下的分析。以下源代碼來自于這篇論文&#xff1a;《基于算術編碼的數據壓縮》&#xff08;Arit…
閱讀更多...
pandas讀寫各種類型數據

pandas讀寫各種類型數據

read_X()通常是pandas模塊下的&#xff0c;to_X()是dataframe的方法 CSV 讀取 使用pandas.read_csv()方法&#xff0c;返回的是一個dataframe csv默認是以"&#xff0c;"分割的 csv文件內容 1、read_csv()默認以第一行數據作為標題 2、調用dataframe的head()方法…
閱讀更多...
python 類裝飾器

python 類裝飾器

1 裝飾器無參數 class tracer: def __init__(self,func): self.calls 0 self.func func def __call__(self,*args): self.calls 1 print(call %s to %s %(self.calls, self.func.__name__)) self.func(*args) tracer def spam(a, b, c): print(a b c) …
閱讀更多...
【數據分析】使用pandas和numpy分析美國大選獻金項目

【數據分析】使用pandas和numpy分析美國大選獻金項目

1. 數據載入與總覽 1.1 數據加載 #繪圖工具 import matplotlib.pyplot as plt %matplotlib inline #數據處理工具 import numpy as np import pandas as pd from pandas import Series,DataFrame#數據路徑自己指定&#xff0c;本案例數據路徑就在當前文件夾下面子文件夾usa_e…
閱讀更多...
《容器技術系列》一1.4　Docker運行案例分析

《容器技術系列》一1.4　Docker運行案例分析

本節書摘來華章計算機《容器技術系列》一書中的第1章 &#xff0c;第1.4節&#xff0c;孫宏亮　著, 更多章節內容可以訪問云棲社區“華章計算機”公眾號查看。 1.4　Docker運行案例分析 1.3節著重介紹了Docker架構中各個模塊的功能&#xff0c;學完后我們可以對Docker的架構有一…
閱讀更多...
算術編碼的原理與分析

算術編碼的原理與分析

轉自&#xff1a;http://kulasuki115.blogcn.com/diary,201492702.shtml 前言 人類已進入信息時代&#xff0c;信息時代的重要特征是信息的數字化&#xff0c;人們越來越依靠計算機獲取和利用信息&#xff0c;這就需要對信息的表示、存儲、傳輸和處理等關鍵技術進行研究。我們…
閱讀更多...
3月22日AM

3月22日AM

看了思維章節精講視頻課&#xff0c;并且總結了部分思維章節內容轉載于:https://www.cnblogs.com/bgd140206102/p/6601440.html
閱讀更多...
阿里巴巴Dubbo實現的源碼分析

阿里巴巴Dubbo實現的源碼分析

Dubbo概述Dubbo是阿里巴巴開源出來的一個分布式服務框架&#xff0c;致力于提供高性能和透明化的RPC遠程服務調用方案&#xff0c;以及作為SOA服務治理的方案。它的核心功能包括&#xff1a; remoting:遠程通訊基礎&#xff0c;提供對多種NIO框架抽象封裝&#xff0c;包括“同步…
閱讀更多...
POJ 2106-Boolean Expressions，雙棧運用類似表達式求值！

POJ 2106-Boolean Expressions，雙棧運用類似表達式求值！

Boolean Expressions 首先聲明此題后臺可能極水&#xff08;畢竟這種數據不好造&#xff01;&#xff09;。昨天寫了一天卻總是找不到bug&#xff0c;討論區各種數據都過了&#xff0c;甚至懷疑輸入有問題&#xff0c;但看到gets也可以過&#xff0c;難道是思路錯了&#xff1f…
閱讀更多...
最新文章

Copyright @ 2022~2023