5.2 優(yōu)化insert語(yǔ)句

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

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

  示例， 原始方式為：

  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ù)中進(jìn)行數(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');
  

5.3 優(yōu)化order by語(yǔ)句

5.3.1 環(huán)境準(zhǔ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);

5.3.2 兩種排序方式

1). 第一種是通過(guò)對(duì)返回?cái)?shù)據(jù)進(jìn)行排序，也就是通常說(shuō)的 filesort 排序，所有不是通過(guò)索引直接返回排序結(jié)果的排序都叫 FileSort 排序。

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

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

5.3.3 Filesort 的優(yōu)化

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

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

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

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

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

5.4 優(yōu)化group by 語(yǔ)句

由于GROUP BY 實(shí)際上也同樣會(huì)進(jìn)行排序操作，而且與ORDER BY 相比，GROUP BY 主要只是多了排序之后的分組操作。當(dāng)然，如果在分組的時(shí)候還使用了其他的一些聚合函數(shù)，那么還需要一些聚合函數(shù)的計(jì)算。所以，在GROUP BY 的實(shí)現(xiàn)過(guò)程中，與 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;

優(yōu)化后

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

從上面的例子可以看出，第一個(gè)SQL語(yǔ)句需要進(jìn)行"filesort"，而第二個(gè)SQL由于order by null 不需要進(jìn)行 "filesort"， 而上文提過(guò)Filesort往往非常耗費(fèi)時(shí)間。
創(chuàng)建索引 ：

create index idx_emp_age_salary on emp(age,salary)；

5.5 優(yōu)化嵌套查詢

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

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

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

執(zhí)行計(jì)劃為 :

優(yōu)化后 :

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

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

5.6 優(yōu)化OR條件

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

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

示例 ：

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

建議使用 union 替換 or ：

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

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

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

UNION 語(yǔ)句的 type 值為 ref，OR 語(yǔ)句的 type 值為 range，可以看到這是一個(gè)很明顯的差距

UNION 語(yǔ)句的 ref 值為 const，OR 語(yǔ)句的 type 值為 null，const 表示是常量值引用，非?？?/p>

這兩項(xiàng)的差距就說(shuō)明了 UNION 要優(yōu)于 OR 。

5.7 優(yōu)化分頁(yè)查詢

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

5.7.1 優(yōu)化思路一

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

5.7.2 優(yōu)化思路二

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

5.8 使用SQL提示

SQL提示，是優(yōu)化數(shù)據(jù)庫(kù)的一個(gè)重要手段，簡(jiǎn)單來(lái)說(shuō)，就是在SQL語(yǔ)句中加入一些人為的提示來(lái)達(dá)到優(yōu)化操作的目的。

5.8.1 USE INDEX

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

create index idx_seller_name on tb_seller(name);

5.8.2 IGNORE INDEX

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

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

5.8.3 FORCE INDEX

為強(qiáng)制MySQL使用一個(gè)特定的索引，可在查詢中使用 force index 作為hint 。

create index idx_seller_address on tb_seller(address);