10.SQL優(yōu)化

1.大批量插入數(shù)據(jù)

環(huán)境準備 ：

CREATE TABLE `tb_user_2`
(
    `id`          int(11)     NOT NULL AUTO_INCREMENT,
    `username`    varchar(45) NOT NULL,
    `password`    varchar(96) NOT NULL,
    `name`        varchar(45) NOT NULL,
    `birthday`    datetime    DEFAULT NULL,
    `sex`         char(1)     DEFAULT NULL,
    `email`       varchar(45) DEFAULT NULL,
    `phone`       varchar(45) DEFAULT NULL,
    `qq`          varchar(32) DEFAULT NULL,
    `status`      varchar(32) NOT NULL COMMENT '用戶狀態(tài)',
    `create_time` datetime    NOT NULL,
    `update_time` datetime    DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `unique_user_username` (`username`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

當使用load 命令導(dǎo)入數(shù)據(jù)的時候，適當?shù)脑O(shè)置可以提高導(dǎo)入的效率。

image.png

對于 InnoDB 類型的表，有以下幾種方式可以提高導(dǎo)入的效率：

1） 主鍵順序插入

因為InnoDB類型的表是按照主鍵的順序保存的，所以將導(dǎo)入的數(shù)據(jù)按照主鍵的順序排列，可以有效的提高導(dǎo)入數(shù)據(jù)的效率。如果InnoDB表沒有主鍵，那么系統(tǒng)會自動默認創(chuàng)建一個內(nèi)部列作為主鍵，所以如果可以給表創(chuàng)建一個主鍵，將可以利用這點，來提高導(dǎo)入數(shù)據(jù)的效率。

腳本文件介紹 :
sql1.log ----> 主鍵有序
sql2.log ----> 主鍵無序

插入ID順序排列數(shù)據(jù)：

image.png

插入ID無序排列數(shù)據(jù)：

image.png

2） 關(guān)閉唯一性校驗

在導(dǎo)入數(shù)據(jù)前執(zhí)行 SET UNIQUE_CHECKS=0，關(guān)閉唯一性校驗，在導(dǎo)入結(jié)束后執(zhí)行SET UNIQUE_CHECKS=1，恢復(fù)唯一性校驗，可以提高導(dǎo)入的效率。

image.png

3） 手動提交事務(wù)

如果應(yīng)用使用自動提交的方式，建議在導(dǎo)入前執(zhí)行 SET AUTOCOMMIT=0，關(guān)閉自動提交，導(dǎo)入結(jié)束后再執(zhí)行 SET AUTOCOMMIT=1，打開自動提交，也可以提高導(dǎo)入的效率。

image.png

2. 優(yōu)化insert語句

當進行數(shù)據(jù)的insert操作的時候，可以考慮采用以下幾種優(yōu)化方案

  • 如果需要同時對一張表插入很多行數(shù)據(jù)時，應(yīng)該盡量使用多個值表的insert語句，這種方式將大大的縮減客戶端與數(shù)據(jù)庫之間的連接、關(guān)閉等消耗。使得效率比分開執(zhí)行的單個insert語句快。

示例， 原始方式為：

insert into tb_test values(1,'Tom');
insert into tb_test values(2,'Cat');
insert into tb_test values(3,'Jerry');

優(yōu)化后的方案為 ：

insert into tb_test values(1,'Tom'),(2,'Cat')，(3,'Jerry');
  • 在事務(wù)中進行數(shù)據(jù)插入
start transaction;
insert into tb_test values(1,'Tom');
insert into tb_test values(2,'Cat');
insert into tb_test values(3,'Jerry');
commit;
  • 數(shù)據(jù)有序插入
insert into tb_test values(4,'Tim');
insert into tb_test values(1,'Tom');
insert into tb_test values(3,'Jerry');
insert into tb_test values(5,'Rose');
insert into tb_test values(2,'Cat');

優(yōu)化后

insert into tb_test values(1,'Tom');
insert into tb_test values(2,'Cat');
insert into tb_test values(3,'Jerry');
insert into tb_test values(4,'Tim');
insert into tb_test values(5,'Rose');

3. 優(yōu)化order by語句

3.1 環(huán)境準備

CREATE TABLE `emp`
(
    `id`     int(11)      NOT NULL AUTO_INCREMENT,
    `name`   varchar(100) NOT NULL,
    `age`    int(3)       NOT NULL,
    `salary` int(11) DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4;


insert into `emp` (`id`, `name`, `age`, `salary`) values('1','Tom','25','2300');
insert into `emp` (`id`, `name`, `age`, `salary`) values('2','Jerry','30','3500');
insert into `emp` (`id`, `name`, `age`, `salary`) values('3','Luci','25','2800');
insert into `emp` (`id`, `name`, `age`, `salary`) values('4','Jay','36','3500');
insert into `emp` (`id`, `name`, `age`, `salary`) values('5','Tom2','21','2200');
insert into `emp` (`id`, `name`, `age`, `salary`) values('6','Jerry2','31','3300');
insert into `emp` (`id`, `name`, `age`, `salary`) values('7','Luci2','26','2700');
insert into `emp` (`id`, `name`, `age`, `salary`) values('8','Jay2','33','3500');
insert into `emp` (`id`, `name`, `age`, `salary`) values('9','Tom3','23','2400');
insert into `emp` (`id`, `name`, `age`, `salary`) values('10','Jerry3','32','3100');
insert into `emp` (`id`, `name`, `age`, `salary`) values('11','Luci3','26','2900');
insert into `emp` (`id`, `name`, `age`, `salary`) values('12','Jay3','37','4500');

create index idx_emp_age_salary on emp(age,salary);

3.2 兩種排序方式

1). 第一種是通過對返回數(shù)據(jù)進行排序，也就是通常說的 filesort 排序，所有不是通過索引直接返回排序結(jié)果的排序都叫 FileSort 排序。

image.png

2). 第二種通過有序索引順序掃描直接返回有序數(shù)據(jù)，這種情況即為 using index，不需要額外排序，操作效率高。

image.png

多字段排序

image.png

了解了MySQL的排序方式，優(yōu)化目標就清晰了：盡量減少額外的排序，通過索引直接返回有序數(shù)據(jù)。where 條件和Order by 使用相同的索引，并且Order By 的順序和索引順序相同， 并且Order by 的字段都是升序，或者都是降序。否則肯定需要額外的操作，這樣就會出現(xiàn)FileSort

3.3 Filesort 的優(yōu)化

通過創(chuàng)建合適的索引，能夠減少 Filesort 的出現(xiàn)，但是在某些情況下，條件限制不能讓Filesort消失，那就需要加快 Filesort的排序操作。對于Filesort ， MySQL 有兩種排序算法：

1） 兩次掃描算法 ：MySQL4.1 之前，使用該方式排序。首先根據(jù)條件取出排序字段和行指針信息，然后在排序區(qū)sort buffer 中排序，如果sort buffer不夠，則在臨時表 temporary table 中存儲排序結(jié)果。完成排序之后，再根據(jù)行指針回表讀取記錄，該操作可能會導(dǎo)致大量隨機I/O操作。

2）一次掃描算法：一次性取出滿足條件的所有字段，然后在排序區(qū) sort buffer 中排序后直接輸出結(jié)果集。排序時內(nèi)存開銷較大，但是排序效率比兩次掃描算法要高。

MySQL 通過比較系統(tǒng)變量 max_length_for_sort_data 的大小和Query語句取出的字段總大小， 來判定是否那種排序算法，如果max_length_for_sort_data 更大，那么使用第二種優(yōu)化之后的算法；否則使用第一種。

可以適當提高 sort_buffer_size 和 max_length_for_sort_data 系統(tǒng)變量，來增大排序區(qū)的大小，提高排序的效率。

image.png

4.優(yōu)化 group by語句

由于GROUP BY 實際上也同樣會進行排序操作，而且與ORDER BY 相比，GROUP BY 主要只是多了排序之后的分組操作。當然，如果在分組的時候還使用了其他的一些聚合函數(shù)，那么還需要一些聚合函數(shù)的計算。所以，在GROUP BY 的實現(xiàn)過程中，與 ORDER BY 一樣也可以利用到索引。

如果查詢包含 group by 但是用戶想要避免排序結(jié)果的消耗， 則可以執(zhí)行order by null 禁止排序。如下 ：

drop index idx_emp_age_salary on emp;
explain select age,count(*) from emp group by age;
image.png

優(yōu)化后

explain select age,count(*) from emp group by age order by null;
image.png

從上面的例子可以看出，第一個SQL語句需要進行"filesort"，而第二個SQL由于order by null 不需要進行"filesort"， 而上文提過Filesort往往非常耗費時間。

創(chuàng)建索引 ：

create index idx_emp_age_salary on emp(age,salary)；
image.png

5. 優(yōu)化嵌套查詢

Mysql4.1版本之后，開始支持SQL的子查詢。這個技術(shù)可以使用SELECT語句來創(chuàng)建一個單列的查詢結(jié)果，然后把這個結(jié)果作為過濾條件用在另一個查詢中。使用子查詢可以一次性的完成很多邏輯上需要多個步驟才能完成的SQL操作，同時也可以避免事務(wù)或者表鎖死，并且寫起來也很容易。但是，有些情況下，子查詢是可以被更高效的連接（JOIN）替代。

示例 ，查找有角色的所有的用戶信息

explain select * from t_user where id in (select user_id from user_role );

執(zhí)行計劃為 :

image.png

優(yōu)化后 :

explain select * from t_user u , user_role ur where u.id = ur.user_id;
image.png

連接(Join)查詢之所以更有效率一些 ，是因為MySQL不需要在內(nèi)存中創(chuàng)建臨時表來完成這個邏輯上需要兩個步驟的查詢工作

6. 優(yōu)化OR條件

對于包含OR的查詢子句，如果要利用索引，則OR之間的每個條件列都必須用到索引 ， 而且不能使用到復(fù)合索引； 如果沒有索引，則應(yīng)該考慮增加索引。

獲取 emp 表中的所有的索引 ：

image.png

示例 ：

explain select * from emp where id = 1 or age = 30;
image.png
image.png

建議使用 union 替換 or ：

image.png

我們來比較下重要指標，發(fā)現(xiàn)主要差別是 type 和 ref 這兩項

type 顯示的是訪問類型，是較為重要的一個指標，結(jié)果值從好到壞依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge >unique_subquery > index_subquery > range > index > ALL

UNION 語句的 type 值為 ref，OR 語句的 type 值為 range，可以看到這是一個很明顯的差距
UNION 語句的 ref 值為 const，OR 語句的 type 值為 null，const 表示是常量值引用，非?？?br> 這兩項的差距就說明了 UNION 要優(yōu)于 OR 。

7. 優(yōu)化分頁查詢

一般分頁查詢時，通過創(chuàng)建覆蓋索引能夠比較好地提高性能。一個常見又非常頭疼的問題就是 limit 2000000,10 ，此時需要MySQL排序前2000010 記錄，僅僅返回2000000 - 2000010 的記錄，其他記錄丟棄，查詢排序的代價非常大 。

image.png

7.1 優(yōu)化思路一

在索引上完成排序分頁操作，最后根據(jù)主鍵關(guān)聯(lián)回原表查詢所需要的其他列內(nèi)容。

image.png

7.2 優(yōu)化思路二

該方案適用于主鍵自增的表，可以把Limit 查詢轉(zhuǎn)換成某個位置的查詢 。

image.png

8. 使用SQL提示

SQL提示，是優(yōu)化數(shù)據(jù)庫的一個重要手段，簡單來說，就是在SQL語句中加入一些人為的提示來達到優(yōu)化操作的目的。

8.1 USE INDEX

在查詢語句中表名的后面，添加 use index 來提供希望MySQL去參考的索引列表，就可以讓MySQL不再考慮其他可用的索引。

create index idx_seller_name on tb_seller(name);
image.png

8.2 IGNORE INDEX

如果用戶只是單純的想讓MySQL忽略一個或者多個索引，則可以使用 ignore index 作為 hint 。

explain select * from tb_seller ignore index(idx_seller_name) where name = '小米科技';
image.png

8.3 FORCE INDEX

為強制MySQL使用一個特定的索引，可在查詢中使用 force index 作為hint 。

create index idx_seller_address on tb_seller(address);
image.png
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容