MySQL提升大量資料查詢效率的優化神器

2022-07-06 18:04:40

前言

在應用的的開發過程中，由於初期資料量小，開發人員寫 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;

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_traceG;

使用索引優化

索引是資料庫優化最常用也是最重要的手段之一, 通過索引通常可以幫助使用者解決大多數的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優化神器內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！