MySQL提升大量數據查詢效率的優化神器
前言
在應用的的開發過程中，由於初期數據量小，開發人員寫 SQL 語句時更重視功能上的實現，但是當應用系統正式上線後，隨著生產數據量的急劇增長，很多 SQL 語句開始逐漸顯露出性能問題，對生產的影響也越來越大，此時這些有問題的 SQL 語句就成為整個系統性能的瓶頸，因此我們必須要對它們進行優化.
為什麼這麼說呢？因為在MySQL查詢語句中，語法比較的通用嗎，要想得到一個數據集可以使用不同的查詢語句！
例如我們需要查詢一個數據表有多少行數據集？
1、暴力搜索
select count(*) from data;
這樣雖然也可以達到效果，但是用 "SELECT * " 數據庫需要解析更多的對象、字段、權限、屬性等相關內容，在 SQL 語句復雜，硬解析較多的情況下，會對數據庫造成沉重的負擔。
增大網絡開銷，* 有時會誤帶上如log、IconMD5之類的無用且大文本字段，數據傳輸size會幾何增長。特別是MySQL和應用程序不在同一臺機器，這種開銷非常明顯。
2、限定字段查詢
select count(id) from data;
這裡的id是一個遞增的序列，查詢效果確實有所提升。
MySQL的優化方式有很多，大致我們可以從以下幾點來優化MySQL:
- 從設計上優化
- 從查詢上優化
- 從索引上優化
- 從存儲上優化
查看SQL執行頻率
MySQL 客戶端連接成功後，通過 show [session|global] status 命令可以查看服務器狀態信息。通過查看狀態信息可以查看對當前數據庫的主要操作類型。
--下面的命令顯示瞭當前 session 中所有統計參數的值 show session status like 'Com_______'; -- 查看當前會話統計結果 show global status like 'Com_______'; -- 查看自數據庫上次啟動至今統計結果 show status like 'Innodb_rows_%'; -- 查看針對Innodb引擎的統計結果
定位低效率執行SQL
可以通過以下兩種方式定位執行效率較低的 SQL 語句。
慢查詢日志 : 通過慢查詢日志定位那些執行效率較低的 SQL 語句。
show processlist：該命令查看當前MySQL在進行的線程，包括線程的狀態、是否鎖表等，可以實時地查看 SQL 的執行情況，同時對一些鎖表操作進行優化。
-- 查看慢日志配置信息 show variables like '%slow_query_log%'; -- 開啟慢日志查詢 set global slow_query_log=1; -- 查看慢日志記錄SQL的最低閾值時間 show variables like 'long_query_time%'; -- 修改慢日志記錄SQL的最低閾值時間 set global long_query_time=4;
show processlist;
- 1） id列，用戶登錄mysql時，系統分配的"connection_id"，可以使用函數connection_id()查看
- 2） user列，顯示當前用戶。如果不是root，這個命令就隻顯示用戶權限范圍的sql語句
- 3） host列，顯示這個語句是從哪個ip的哪個端口上發的，可以用來跟蹤出現問題語句的用戶
- 4） db列，顯示這個進程目前連接的是哪個數據庫
- 5） command列，顯示當前連接的執行的命令，一般取值為休眠（sleep），查詢（query），連接（connect）等
- 6） time列，顯示這個狀態持續的時間，單位是秒
- 7） state列，顯示使用當前連接的sql語句的狀態，很重要的列。state描述的是語句執行中的某一個狀態。一個sql語句，以查詢為例，可能需要經過copying to tmp table、sorting result、sending data等狀態才可以完成
- 8） info列，顯示這個sql語句，是判斷問題語句的一個重要依據
explain分析執行計劃
通過以上步驟查詢到效率低的 SQL 語句後，可以通過 EXPLAIN命令獲取 MySQL如何執行 SELECT 語句的信息，包括在 SELECT 語句執行過程中表如何連接和連接的順序。
-- 準備測試數據 create database mydb13_optimize; use mydb13_optimize;
執行sql腳本sql_optimize.sql添加數據
explain select * from user where uid = 1;
id 字段是 select查詢的序列號，是一組數字，表示的是查詢中執行select子句或者是操作表的順序。id 情況有三種:
1、id 相同表示加載表的順序是從上到下
explain select * from user u, user_role ur, role r where u.uid = ur.uid and ur.rid = r.rid ;
2、 id 不同id值越大，優先級越高，越先被執行。
explain select * from role where rid = (select rid from user_role where uid = (select uid from user where uname = '張飛'))
3、 id 有相同，也有不同，同時存在。id相同的可以認為是一組，從上往下順序執行；在所有的組中，id的值越大，優先級越高，越先執行。
表示 SELECT 的類型，常見的取值，如下表所示：
type 顯示的是訪問類型，是較為重要的一個指標，可取值為：
結果值從最好到最壞以此是：system > const > eq_ref > ref > range > index > ALL
其他的額外的執行計劃信息，在該列展示 。
Mysql從5.0.37版本開始增加瞭對 show profiles 和 show profile 語句的支持。show profiles 能夠在做SQL優化時幫助我們瞭解時間都耗費到哪裡去瞭。
通過 have_profiling 參數，能夠看到當前MySQL是否支持profile：
select @@have_profiling; set profiling=1; -- 開啟profiling 開關；
通過profile，我們能夠更清楚地瞭解SQL執行的過程。首先，我們可以執行一系列的操作
show databases; use mydb13_optimize; show tables; select * from user where id < 2; select count(*) from user;
執行完上述命令之後，再執行show profiles 指令， 來查看SQL語句執行的耗時：
show profiles;
通過show profile for query query_id 語句可以查看到該SQL執行過程中每個線程的狀態和消耗的時間：
show profile for query 8;
在獲取到最消耗時間的線程狀態後，MySQL支持進一步選擇all、cpu、block io 、context switch、page faults等明細類型類查看MySQL在使用什麼資源上耗費瞭過高的時間。例如，選擇查看CPU的耗費時間 ：
show profile cpu for query 133;
在獲取到最消耗時間的線程狀態後，MySQL支持進一步選擇all、cpu、block io 、context switch、page faults等明細類型類查看MySQL在使用什麼資源上耗費瞭過高的時間。例如，選擇查看CPU的耗費時間 ：
trace分析優化器執行計劃
MySQL5.6提供瞭對SQL的跟蹤trace, 通過trace文件能夠進一步瞭解為什麼優化器選擇A計劃, 而不是選擇B計劃
SET optimizer_trace="enabled=on",end_markers_in_json=on; set optimizer_trace_max_mem_size=1000000;
執行SQL語句 ：
select * from user where uid < 2;
最後， 檢查information_schema.optimizer_trace就可以知道MySQL是如何執行SQL的 ：
select * from information_schema.optimizer_trace\G;
使用索引優化
索引是數據庫優化最常用也是最重要的手段之一, 通過索引通常可以幫助用戶解決大多數的MySQL的性能優化問題。
create table `tb_seller` ( `sellerid` varchar (100), `name` varchar (100), `nickname` varchar (50), `password` varchar (60), `status` varchar (1), `address` varchar (100), `createtime` datetime, primary key(`sellerid`) );
索引是數據庫優化最常用也是最重要的手段之一, 通過索引通常可以幫助用戶解決大多數的MySQL的性能優化問題。
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('alibaba','阿裡巴巴','阿裡小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('baidu','百度科技有限公司','百度小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('huawei','華為科技有限公司','華為小店','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('itcast','傳智播客教育科技有限公司','傳智播客','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('itheima','黑馬程序員','黑馬程序員','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('luoji','羅技科技有限公司','羅技小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('oppo','OPPO科技有限公司','OPPO官方旗艦店','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('ourpalm','掌趣科技股份有限公司','掌趣小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('qiandu','千度科技','千度小店','e10adc3949ba59abbe56e057f20f883e','2','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('sina','新浪科技有限公司','新浪官方旗艦店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('xiaomi','小米科技','小米官方旗艦店','e10adc3949ba59abbe56e057f20f883e','1','西安市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('yijia','宜傢傢居','宜傢傢居旗艦店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
-- 創建組合索引 create index idx_seller_name_sta_addr on tb_seller(name,status,address);
該情況下，索引生效，執行效率高。
explain select * from tb_seller where name='小米科技' and status='1' and address='北京市';
避免索引失效應用-最左前綴法則：
-- 最左前綴法則 -- 如果索引瞭多列，要遵守最左前綴法則。指的是查詢從索引的最左前列開始，並且不跳過索引中的列。 explain select * from tb_seller where name='小米科技'; -- 403 explain select * from tb_seller where name='小米科技' and status='1'; -- 410 explain select * from tb_seller where status='1' and name='小米科技'; -- 410 -- 違法最左前綴法則 ， 索引失效： explain select * from tb_seller where status='1'; -- nulll -- 如果符合最左法則，但是出現跳躍某一列，隻有最左列索引生效： explain select * from tb_seller where name='小米科技' and address='北京市'; -- 403
索引失效應用-其他匹配原則：
-- 范圍查詢右邊的列，不能使用索引 。 explain select * from tb_seller where name='小米科技' and status >'1' and address='北京市'; -- 不要在索引列上進行運算操作， 索引將失效。 explain select * from tb_seller where substring(name,3,2)='科技' -- 字符串不加單引號，造成索引失效。 explain select * from tb_seller where name='小米科技' and status = 1 ;
避免索引失效應用-其他匹配原則 ：
-- 1、范圍查詢右邊的列，不能使用索引 。 -- 根據前面的兩個字段name ， status 查詢是走索引的， 但是最後一個條件address 沒有用到索引。 explain select * from tb_seller where name='小米科技' and status >'1' and address='北京市'; -- 2、不要在索引列上進行運算操作， 索引將失效。 explain select * from tb_seller where substring(name,3,2)='科技' -- 3、字符串不加單引號，造成索引失效。 explain select * from tb_seller where name='小米科技' and status = 1 ; -- 4、盡量使用覆蓋索引，避免select * -- 需要從原表及磁盤上讀取數據 explain select * from tb_seller where name='小米科技' and address='北京市'; -- 效率低 -- 從索引樹中就可以查詢到所有數據 explain select name from tb_seller where name='小米科技' and address='北京市'; -- 效率高 explain select name,status,address from tb_seller where name='小米科技' and address='北京市'; -- 效率高 -- 如果查詢列，超出索引列，也會降低性能。 explain select name,status,address,password from tb_seller where name='小米科技' and address='北京市'; -- 效率低
-- 盡量使用覆蓋索引，避免select * -- 需要從原表及磁盤上讀取數據 explain select * from tb_seller where name='小米科技' and address='北京市'; -- 效率低 -- 從索引樹中就可以查詢到所有數據 explain select name from tb_seller where name='小米科技' and address='北京市'; -- 效率高 explain select name,status,address from tb_seller where name='小米科技' and address='北京市'; -- 效率高 -- 如果查詢列，超出索引列，也會降低性能。 explain select name,status,address,password from tb_seller where name='小米科技' and address='北京市'; -- 效率低 -- 用or分割開的條件， 那麼涉及的索引都不會被用到。 explain select * from tb_seller where name='黑馬程序員' or createtime = '2088-01-01 12:00:00'; explain select * from tb_seller where name='黑馬程序員' or address = '西安市'; explain select * from tb_seller where name='黑馬程序員' or status = '1'; -- 以%開頭的Like模糊查詢，索引失效。 explain select * from tb_seller where name like '科技%'; -- 用索引 explain select * from tb_seller where name like '%科技'; -- 不用索引 explain select * from tb_seller where name like '%科技%';-- 不用索引 -- 彌補不足,不用*，使用索引列 explain select name from tb_seller where name like '%科技%';
-- 1、如果MySQL評估使用索引比全表更慢，則不使用索引。 -- 這種情況是由數據本身的特點來決定的 create index index_address on tb_seller(address); explain select * from tb_seller where address = '北京市'; -- 沒有使用索引 explain select * from tb_seller where address = '西安市'; -- 沒有使用索引 -- 2、is NULL ， is NOT NULL 有時有效，有時索引失效。 create index index_address on tb_seller(nickname); explain select * from tb_seller where nickname is NULL; -- 索引有效 explain select * from tb_seller where nickname is not NULL; -- 無效
SQL優化
create table `tb_user` ( `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 '用戶狀態', `create_time` datetime not null, `update_time` datetime default null, primary key (`id`), unique key `unique_user_username` (`username`) );
當使用load 命令導入數據的時候，適當的設置可以提高導入的效率。對於 InnoDB 類型的表，有以下幾種方式可以提高導入的效率：
大量插入數據
1） 主鍵順序插入
因為InnoDB類型的表是按照主鍵的順序保存的，所以將導入的數據按照主鍵的順序排列，可以有效的提高導入數據的效率。如果InnoDB表沒有主鍵，那麼系統會自動默認創建一個內部列作為主鍵，所以如果可以給表創建一個主鍵，將可以利用這點，來提高導入數據的效率。
-- 1、首先，檢查一個全局系統變量 'local_infile' 的狀態， 如果得到如下顯示 Value=OFF，則說明這是不可用的 show global variables like 'local_infile'; -- 2、修改local_infile值為on，開啟local_infile set global local_infile=1; -- 3、加載數據 /* 腳本文件介紹 : sql1.log ----> 主鍵有序 sql2.log ----> 主鍵無序 */ load data local infile 'D:\\sql_data\\sql1.log' into table tb_user fields terminated by ',' lines terminated by '\n';
2 )、關閉唯一性校驗
在導入數據前執行 SET UNIQUE_CHECKS=0，關閉唯一性校驗，在導入結束後執行SET UNIQUE_CHECKS=1，恢復唯一性校驗，可以提高導入的效率。
-- 關閉唯一性校驗 SET UNIQUE_CHECKS=0; truncate table tb_user; load data local infile 'D:\\sql_data\\sql1.log' into table tb_user fields terminated by ',' lines terminated by '\n'; SET UNIQUE_CHECKS=1;
優化insert語句
-- 數據有序插入 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');
優化order by語句
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`)
);
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);
2、兩種排序方式
第一種是通過對返回數據進行排序，也就是通常說的 filesort 排序，所有不是通過索引直接返回排序結果的排序都叫 FileSort 排序。
第二種通過有序索引順序掃描直接返回有序數據，這種情況即為 using index，不需要額外排序，操作效率高。
3、Filesort 的優化
通過創建合適的索引，能夠減少 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 系統變量，來增大排序區的大小，提高排序的效率。
優化group by
於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; create index idx_emp_age_salary on emp(age,salary)；
子查詢優化
使用子查詢可以一次性的完成很多邏輯上需要多個步驟才能完成的SQL操作，同時也可以避免事務或者表鎖死，並且寫起來也很容易。但是，有些情況下，子查詢是可以被更高效的連接（JOIN）替代。
explain select * from user where uid in (select uid from user_role );
explain select * from user u , user_role ur where u.uid = ur.uid; system>const>eq_ref>ref>range>index>ALL
連接(Join)查詢之所以更有效率一些 ，是因為MySQL不需要在內存中創建臨時表來完成這個邏輯上需要兩個步驟的查詢工作。
limit優化
一般分頁查詢時，通過創建覆蓋索引能夠比較好地提高性能。一個常見又非常頭疼的問題就是 limit 900000,10 ，此時需要MySQL排序前900010 記錄，僅僅返回900000 – 900010 的記錄，其他記錄丟棄，查詢排序的代價非常大 。
- 1、在索引上完成排序分頁操作，最後根據主鍵關聯回原表查詢所需要的其他列內容。
- 2、該方案適用於主鍵自增的表，可以把Limit 查詢轉換成某個位置的查詢 。
到此這篇關於MySQL提升大量數據查詢效率的優化神器的文章就介紹到這瞭,更多相關MySQL優化神器內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！