6.1 優化insert陳述句
當進行資料的insert操作的時候，可以考慮采用以下幾種優化方案，
-
如果需要同時對一張表插入很多行資料時，應該盡量使用多個值表的insert陳述句
這種方式將大大的縮減客戶端與資料庫之間的連接、關閉等消耗，
使得效率比分開執行的單個insert陳述句快，
示例， 原始方式為：
insert into xxx values(1,'Tom'); insert into xxx values(2,'Cat'); insert into xxx values(3,'Jerry');優化后的方案為 ：
insert into xxx values(1,'Tom'),(2,'Cat')，(3,'Jerry'); -
資料有序插入
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');優化后
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');
6.2 優化order by陳述句
6.2.1 環境準備
CREATE TABLE `emp` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(100) NOT NULL,
`age` int(3) NOT NULL,
`salary` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
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);
6.2.2 兩種排序方式
1). 第一種是通過對回傳資料進行排序，也就是通常說的 filesort 排序
tips
不是通過索引直接回傳排序結果的排序都叫 FileSort 排序，
explain select * from emp order by age desc;
explain select * from emp order by age asc;
2). 第二種通過有序索引順序掃描直接回傳有序資料，這種情況即為 using index，
不需要額外排序，操作效率高，
explain select id from emp order by age asc;
explain select id,age from emp order by age asc;
explain select id,age,salary from emp order by age asc;
多欄位排序
-- 多欄位排序
explain select id,age,salary from emp order by age, salary;
explain select id,age,salary from emp order by age desc, salary desc;
explain select id,age,salary from emp order by salary desc, age desc;
explain select id,age,salary from emp order by age desc, salary asc ;
總結：
了解了MySQL的排序方式，優化目標就清晰了：
盡量減少額外的排序，通過索引直接回傳有序資料，
where 條件和Order by 使用相同的索引，并且Order By 的順序和索引順序相同， 并且Order by 的欄位都是升序，或者都是降序，否則肯定需要額外的操作，這樣就會出現FileSort，
6.2.3 Filesort 的優化原理
tips
了解即可
通過創建合適的索引，能夠減少 Filesort 的出現，但是在某些情況下，條件限制不能讓Filesort消失，那就需要加快 Filesort的排序操作，對于Filesort ， MySQL 有兩種排序演算法：
1） 兩次掃描演算法 ：MySQL4.1 之前，使用該方式排序，首先根據條件取出排序欄位和行指標資訊，然后在排序區 sort buffer 中排序，如果sort buffer不夠，則在臨時表 temporary table 中存盤排序結果，完成排序之后，再根據行指標回表讀取記錄，該操作可能會導致大量隨機I/O操作，
2）一次掃描演算法：一次性取出滿足條件的所有欄位，然后在排序區 sort buffer 中排序后直接輸出結果集，排序時記憶體開銷較大，但是排序效率比兩次掃描演算法要高，
MySQL 通過比較系統變數 max_length_for_sort_data 的大小和Query陳述句取出的欄位總大小， 來判定使用哪種排序演算法，如果max_length_for_sort_data 更大，那么使用第二種優化之后的演算法；否則使用第一種，
可以適當提高 sort_buffer_size 和 max_length_for_sort_data 系統變數，來增大排序區的大小，提高排序的效率，
show variables like 'max_length_for_sort_data';
show variables like 'sort_buffer_size';
6.3 優化group by 陳述句
tips
延續使用6.2 優化order by陳述句中創建的表emp
創建索引 （準備作業）：
create index idx_emp_age_salary on emp(age,salary);
由于GROUP BY 實際上也同樣會進行排序操作，而且與ORDER BY 相比，GROUP BY 主要只是多了排序之后的分組操作，
當然，如果在分組的時候還使用了其他的一些聚合函式，那么還需要一些聚合函式的計算，所以，在GROUP BY 的實作程序中，與 ORDER BY 一樣也可以利用到索引，
如果查詢包含 group by 但是用戶想要避免排序結果的消耗， 則可以執行order by null 禁止排序，如下 ：
drop index idx_emp_age_salary on emp;
explain select age,count(*) from emp group by age;
優化后
explain select age,count(*) from emp group by age order by null;
從上面的例子可以看出，第一個SQL陳述句需要進行"filesort"，而第二個SQL由于order by null 不需要進行 "filesort"， 而上文提過Filesort往往非常耗費時間，
6.4 優化嵌套查詢
tips
延續使用4.3 使用explain分析執行計劃中創建的表
Mysql4.1版本之后，開始支持SQL的子查詢，這個技術可以使用SELECT陳述句來創建一個單列的查詢結果，然后把這個結果作為過濾條件用在另一個查詢中，使用子查詢可以一次性的完成很多邏輯上需要多個步驟才能完成的SQL操作，同時也可以避免事務或者表鎖死，并且寫起來也很容易，但是，有些情況下，子查詢是可以被更高效的連接（JOIN）替代，
示例 ，查找有角色的所有的用戶資訊 :
explain select * from t_user where id in (select user_id from user_role );
執行計劃為 :
優化后 :
explain select * from t_user u , user_role ur where u.id = ur.user_id;
連接(Join)查詢之所以更有效率一些 ，是因為MySQL不需要在記憶體中創建臨時表來完成這個邏輯上需要兩個步驟的查詢作業，
6.5 使用SQL提示
tips：
延續使用5.2.1 準備環境中創建的表
SQL提示，是優化資料庫的一個重要手段，簡單來說，就是在SQL陳述句中加入一些人為的提示來達到優化操作的目的，
6.5.1 USE INDEX
在查詢陳述句中表名的后面，添加 use index 來提供希望MySQL去參考的索引串列，就可以讓MySQL不再考慮其他可用的索引，
create index idx_seller_name on tb_seller(name);
explain select * from tb_seller where name='小米科技'
explain select * from tb_seller use index(idx_seller_name) where name='小米科技'
6.5.2 IGNORE INDEX
如果用戶只是單純的想讓MySQL忽略一個或者多個索引，則可以使用 ignore index 作為 hint ，
explain select * from tb_seller ignore index(idx_seller_name_sta_addr) where name = '小米科技';
6.5.3 FORCE INDEX
為強制MySQL使用一個特定的索引，可在查詢中使用 force index 作為hint ，
create index idx_seller_address on tb_seller(address);
explain select * from tb_seller force index(idx_seller_address) where address = '北京市';
本文由傳智教育博學谷 - 狂野架構師教研團隊發布
如果本文對您有幫助，歡迎關注和點贊；如果您有任何建議也可留言評論或私信，您的支持是我堅持創作的動力
轉載請注明出處！
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/500278.html
標籤：Java