MySQL 優(yōu)化

子查詢優(yōu)化

• 將子查詢改變?yōu)楸磉B接，尤其是在子查詢的結(jié)果集較大的情況下；
• 添加復(fù)合索引，其中復(fù)合索引的包含的字段應(yīng)該包括 where 字段與關(guān)聯(lián)字段；
• 復(fù)合索引中的字段順序要遵守最左匹配原則；
• MySQL 8 中自動(dòng)對(duì)子查詢進(jìn)行優(yōu)化；

現(xiàn)有兩個(gè)表

create table Orders
(
    id         integer AUTO_INCREMENT PRIMARY KEY,
    name       varchar(255) not null,
    order_date datetime     NOT NULL
) comment '訂單表';

create table OrderDetails
(
    id           integer AUTO_INCREMENT PRIMARY KEY,
    order_id     integer     not null,
    product_code varchar(20) not null,
    quantity     integer     not null
) comment '訂單詳情表';

子查詢

select * from orders where id in (select order_id from OrderDetails where product_code = 'PC50');

優(yōu)化1：改為表連接

select * from orders as t1 inner join orderdetails o on t1.id = o.order_id where product_code='PC50';

優(yōu)化2：給 order_id 字段添加索引

優(yōu)化3：給 product_code 字段添加索引

結(jié)果證明：給 product_code 字段添加索引 的效果優(yōu)于給 order_id 字段添加索引，因?yàn)椴挥脤?duì)索引列進(jìn)行全表掃描

優(yōu)化4：給 order_id 和 product_code 添加復(fù)合索引

優(yōu)化5：給 product_code 和 order_id 添加復(fù)合索引

對(duì)于復(fù)合索引 idx(order_id, product_code)，因?yàn)椴樵冎行枰袛?product_code 的值是否為 PC51，所以要對(duì) order_id 該列進(jìn)行全索引掃描，性能較低 [ 因?yàn)?product_code 不是有序的，先根據(jù) order_id 進(jìn)行排序，再根據(jù) product_code 進(jìn)行排序 ]；

對(duì)于復(fù)合索引 idx(product_code, order_id) ，因?yàn)?product_code 本身是有序的，所以可以快速定位到該 product_code 然后快速獲取該 order_id，性能較高；

待排序的分頁查詢的優(yōu)化

現(xiàn)有一個(gè)電影表

create table film
(
    id           integer auto_increment primary key,
    score        decimal(2, 1) not null,
    release_date date          not null,
    film_name    varchar(255)  not null,
    introduction varchar(255)  not null
) comment '電影表';

對(duì)于淺分頁

select score, release_date, film_name from film order by score limit 0, 20;

耗時(shí) 825ms

對(duì)于深分頁

select score, release_date, film_name, introduction from film order by score limit 1500000, 20;

耗時(shí) 1s 247ms

若不加處理，淺分頁的速度快，limit 的深度越深，查詢的效率越慢

給排序字段添加索引

給 score 字段添加索引

create index idx_score on film(score);

結(jié)果

淺分頁的速度為 60 ms，深分頁的速度為 1s 134ms

淺分頁的情況得到了優(yōu)化，而深分頁依然很慢

查看深分頁的執(zhí)行情況

其并沒有走 score 索引，走的是全表的掃描，所以給排序字段添加索引只能優(yōu)化淺分頁的情況

解釋

只給 score 添加索引，會(huì)造成回表的情況

對(duì)于淺分頁，回表的性能消耗小于全表掃描，故走 score 索引；

對(duì)于深分頁，回表的性能消耗大于全表掃描，故走 全表掃描；

給排序字段跟 select 字段添加復(fù)合索引

給 score, release_date, film_name 添加復(fù)合索引

create index idx_score_date_name on film(score, release_date, film_name);

淺分頁的速度為 58 ms，深分頁的速度為 357 ms，兩者的速度都得到了提升

查看深分頁的執(zhí)行情況

可見其走了復(fù)合索引

解釋

對(duì)于該復(fù)合索引，排序的值和查詢的值都在索引上，沒有進(jìn)行回表的操作，效率很高。唯一的不足是：若要添加新的查詢列，就要更改該索引的列，不夠靈活。

給排序字段加索引 + 手動(dòng)回表

改進(jìn)SQL語句，給 score 字段添加索引

# 給排序字段添加索引 + 手動(dòng)回表
select score, release_date, film_name,introduction from  film a
join (select id from film order by score limit 1500000, 20) b
on  a.id = b.id;

思路：先把 limit 字段的 id 找出來，這里走了 score 索引，效率高。然后再走主鍵索引根據(jù) id 去尋找；

該語句的執(zhí)行情況

可見子查詢中走了 score 索引，而外查詢走了主鍵索引，效率非常高，執(zhí)行速度為 297 ms

缺點(diǎn)

由上面的執(zhí)行計(jì)劃可見，它創(chuàng)建了一張中間表 ，走的是全表掃描，也就是說，中間表中的記錄越多，該執(zhí)行效率就越慢，觀察以下語句，從500000開始查，查找 1500000 條數(shù)據(jù)；

select score, release_date, film_name,introduction from  film a
join (select id from film order by score limit  500000, 1500000) b
on  a.id = b.id;

消耗的時(shí)間為：911ms，接近一秒

所以我們可以通過業(yè)務(wù)的方法，限制每次查詢的條數(shù)即可

解決辦法

• 給排序的字段 + select 的字段添加復(fù)合索引
• 給排序的字段加索引 + 手動(dòng)回表
• 深分頁的性能問題可以通過業(yè)務(wù)方法解決：限制每次查詢的數(shù)量等

排序優(yōu)化

索引的字段要根據(jù)排序的字段走，且要滿足最左匹配原則

create table t_order (
    id integer primary key auto_increment,
    col_1 int not null ,
    col_2 int not null ,
    col_3 int not null 
)

select * from t_order order by col_1, col_2, col_3, 需要?jiǎng)?chuàng)建聯(lián)合索引 col_1,col_2,col_3

select * from t_order order by col_1, col_2，需要?jiǎng)?chuàng)建聯(lián)合索引 col_1, col_2, col_3

select * from t_order order by col_1 asc, col_2 desc ，需要?jiǎng)?chuàng)建聯(lián)合索引 col_1 asc, col_2 desc ，指定索引的排序規(guī)則，只有在 MySQL 8 中才支持

索引失效的情況（避免出現(xiàn) using filesort）

• 沒有遵守最左匹配原則

select * from t_order order by col_1, col_3

select * from t_order order by col_2, col_3

可見都使用到了 ****using filesort

以第一條為例

最左匹配原則的實(shí)質(zhì)是：先根據(jù)第一列排序，若第一列的值相同就根據(jù)第二列來排序，若第二列的值相同就根據(jù)第三列來排序，以此類推；

第一條 SQL 中，排序的字段為 col_2 和 col_3 明顯 在拋開 col_1 的情況下，col_2 和 col_3 的順序是無序的，故要使用 using filesort，不能依靠索引來進(jìn)行排序；

• 使用了范圍查詢

select * from t_order where col1 >= 1 and col1 <= 4 order by col_2

select * from t_order where col1 in(1,2,4) order by col_2

若走該復(fù)合索引 (col_1, col_2, col_3) ，可以發(fā)現(xiàn)查詢計(jì)劃中使用到了 using filesort

解釋

經(jīng)過 col_1 的篩選后，col_2 的數(shù)據(jù)都是無序的

所以要使用 using filesort 再次根據(jù) col_2 排序

若使用等值查詢，則不會(huì)出現(xiàn) using filesort，前提是要滿足最左匹配原則

select col_1, col_2 from t_order where col_1 = 2 order by col_2;

若不滿足 最左匹配原則

select col_1, col_3 from t_order where col_1 = 2 order by col_3;

則使用到了 using filesort

以上就是關(guān)于MySQL查詢語句的優(yōu)化詳解的詳細(xì)內(nèi)容，更多關(guān)于MySQL查詢語句優(yōu)化的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

最新評(píng)論