定位低效率執(zhí)行SQL-show processlist

show processlist;


四、explain分析執(zhí)行計(jì)劃

通過以上步驟查詢到效率低的 SQL 語句后，可以通過 EXPLAIN命令獲取 MySQL如何執(zhí)行 SELECT 語句的信息，包括在 SELECT 語句執(zhí)行過程中表如何連接和連接的順序。

-- 準(zhǔn)備測(cè)試數(shù)據(jù)
create database mydb13_optimize;
use mydb13_optimize;

執(zhí)行sql腳本sql_optimize.sql添加數(shù)據(jù)

explain select * from user where uid = 1;


explain select * from user where uname = '張飛';


Explain分析執(zhí)行計(jì)劃-Explain 之 id

id 字段是 select查詢的序列號(hào)，是一組數(shù)字，表示的是查詢中執(zhí)行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值越大，優(yōu)先級(jí)越高，越先被執(zhí)行。

explain select * from role where rid = (select rid from user_role where uid = (select uid from user where uname = '張飛'))

3、 id 有相同，也有不同，同時(shí)存在。id相同的可以認(rèn)為是一組，從上往下順序執(zhí)行；在所有的組中，id的值越大，優(yōu)先級(jí)越高，越先執(zhí)行。

explain select * from role r , (select * from user_role ur where ur.uid = (select uid from user where uname = '張飛')) t where r.rid = t.rid ;

Explain分析執(zhí)行計(jì)劃-Explain 之 select_type

表示 SELECT 的類型，常見的取值，如下表所示：

Explain分析執(zhí)行計(jì)劃-Explain 之 type

type 顯示的是訪問類型，是較為重要的一個(gè)指標(biāo)，可取值為：

結(jié)果值從最好到最壞以此是：system > const > eq_ref > ref > range > index > ALL

Explain分析執(zhí)行計(jì)劃-其他指標(biāo)字段

? Explain 之 table

顯示這一步所訪問數(shù)據(jù)庫(kù)中表名稱有時(shí)不是真實(shí)的表名字，可能是簡(jiǎn)稱，

? explain 之 rows

掃描行的數(shù)量。

? Explain 之 key

possible_keys : 顯示可能應(yīng)用在這張表的索引， 一個(gè)或多個(gè)。

key ： 實(shí)際使用的索引， 如果為NULL， 則沒有使用索引。

key_len : 表示索引中使用的字節(jié)數(shù)， 該值為索引字段最大可能長(zhǎng)度，并非實(shí)際使用長(zhǎng)度，在不損失精確性的前提下， 長(zhǎng)度越短越好 。

? Explain之 extra

其他的額外的執(zhí)行計(jì)劃信息，在該列展示 。

五、show profile分析SQL

Mysql從5.0.37版本開始增加了對(duì) show profiles 和 show profile 語句的支持。show profiles 能夠在做SQL優(yōu)化時(shí)幫助我們了解時(shí)間都耗費(fèi)到哪里去了。。

通過 have_profiling 參數(shù)，能夠看到當(dāng)前MySQL是否支持profile：

select @@have_profiling;
set profiling=1; -- 開啟profiling 開關(guān)；

通過profile，我們能夠更清楚地了解SQL執(zhí)行的過程。首先，我們可以執(zhí)行一系列的操作

show databases;

use mydb13_optimize;

show tables;

select * from user where id < 2;

select count(*) from user;

執(zhí)行完上述命令之后，再執(zhí)行show profiles 指令， 來查看SQL語句執(zhí)行的耗時(shí)：

show profiles;


通過show profile for query query_id 語句可以查看到該SQL執(zhí)行過程中每個(gè)線程的狀態(tài)和消耗的時(shí)間：

show profile for query 8;


在獲取到最消耗時(shí)間的線程狀態(tài)后，MySQL支持進(jìn)一步選擇all、cpu、block io 、context switch、page faults等明細(xì)類型類查看MySQL在使用什么資源上耗費(fèi)了過高的時(shí)間。例如，選擇查看CPU的耗費(fèi)時(shí)間 ：

show profile cpu for query 133; ?

在獲取到最消耗時(shí)間的線程狀態(tài)后，MySQL支持進(jìn)一步選擇all、cpu、block io 、context switch、page faults等明細(xì)類型類查看MySQL在使用什么資源上耗費(fèi)了過高的時(shí)間。例如，選擇查看CPU的耗費(fèi)時(shí)間 ：

在獲取到最消耗時(shí)間的線程狀態(tài)后，MySQL支持進(jìn)一步選擇all、cpu、block io 、context switch、page faults等明細(xì)類型類查看MySQL在使用什么資源上耗費(fèi)了過高的時(shí)間。例如，選擇查看CPU的耗費(fèi)時(shí)間 ：

六、trace分析優(yōu)化器執(zhí)行計(jì)劃

MySQL5.6提供了對(duì)SQL的跟蹤trace, 通過trace文件能夠進(jìn)一步了解為什么優(yōu)化器選擇A計(jì)劃, 而不是選擇B計(jì)劃

打開trace ， 設(shè)置格式為 JSON，并設(shè)置trace最大能夠使用的內(nèi)存大小，避免解析過程中因?yàn)槟J(rèn)內(nèi)存過小而不能夠完整展示。

SET optimizer_trace="enabled=on",end_markers_in_json=on;
set optimizer_trace_max_mem_size=1000000;

執(zhí)行SQL語句 ：

select * from user where uid < 2;


最后， 檢查
information_schema.optimizer_trace就可以知道MySQL是如何執(zhí)行SQL的 ：

select * from information_schema.optimizer_trace\G;


索引是數(shù)據(jù)庫(kù)優(yōu)化最常用也是最重要的手段之一, 通過索引通?？梢詭椭脩艚鉀Q大多數(shù)的MySQL的性能優(yōu)化問題。

? 數(shù)據(jù)準(zhǔn)備

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`)
);

索引是數(shù)據(jù)庫(kù)優(yōu)化最常用也是最重要的手段之一, 通過索引通?？梢詭椭脩艚鉀Q大多數(shù)的MySQL的性能優(yōu)化問題。

? 數(shù)據(jù)準(zhǔn)備

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');


索引是數(shù)據(jù)庫(kù)優(yōu)化最常用也是最重要的手段之一, 通過索引通?？梢詭椭脩艚鉀Q大多數(shù)的MySQL的性能優(yōu)化問題。

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');


七、使用索引優(yōu)化

? 數(shù)據(jù)準(zhǔn)備

-- 創(chuàng)建組合索引
create index idx_seller_name_sta_addr on tb_seller(name,status,address);

? 避免索引失效應(yīng)用-全值匹配

該情況下，索引生效，執(zhí)行效率高。

explain select * from tb_seller where name='小米科技' and status='1' and address='北京市';


? 避免索引失效應(yīng)用-最左前綴法則

該情況下，索引生效，執(zhí)行效率高。

-- 最左前綴法則
-- 如果索引了多列，要遵守最左前綴法則。指的是查詢從索引的最左前列開始，并且不跳過索引中的列。
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

-- 如果符合最左法則，但是出現(xiàn)跳躍某一列，只有最左列索引生效：
explain select * from tb_seller where name='小米科技' ?and address='北京市'; -- 403

? 避免索引失效應(yīng)用-其他匹配原則

該情況下，索引生效，執(zhí)行效率高。

-- 范圍查詢右邊的列，不能使用索引 。
explain select * from tb_seller where name='小米科技' and status >'1' and address='北京市’;

-- 不要在索引列上進(jìn)行運(yùn)算操作， 索引將失效。
explain select * from tb_seller where substring(name,3,2)='科技’

-- 字符串不加單引號(hào)，造成索引失效。
explain select * from tb_seller where name='小米科技' and status = 1 ;

-- 1、范圍查詢右邊的列，不能使用索引 。
-- 根據(jù)前面的兩個(gè)字段name ， status 查詢是走索引的， 但是最后一個(gè)條件address 沒有用到索引。
explain select * from tb_seller where name='小米科技' and status >'1' and address='北京市';

-- 2、不要在索引列上進(jìn)行運(yùn)算操作， 索引將失效。
explain select * from tb_seller where substring(name,3,2)='科技'

-- 3、字符串不加單引號(hào)，造成索引失效。
explain select * from tb_seller where name='小米科技' and status = 1 ;

-- 4、盡量使用覆蓋索引，避免select *
-- 需要從原表及磁盤上讀取數(shù)據(jù)
explain select * from tb_seller where name='小米科技' ?and address='北京市'; ?-- 效率低

-- 從索引樹中就可以查詢到所有數(shù)據(jù)
explain select name from tb_seller where name='小米科技' ?and address='北京市'; ?-- 效率高
explain select name,status,address from tb_seller where name='小米科技' ?and address='北京市'; ?-- 效率高
-- 如果查詢列，超出索引列，也會(huì)降低性能。
explain select name,status,address,password from tb_seller where name='小米科技' ?and address='北京市'; ?-- 效率低

-- 盡量使用覆蓋索引，避免select *
-- 需要從原表及磁盤上讀取數(shù)據(jù)
explain select * from tb_seller where name='小米科技' ?and address='北京市'; ?-- 效率低

-- 從索引樹中就可以查詢到所有數(shù)據(jù)
explain select name from tb_seller where name='小米科技' ?and address='北京市'; ?-- 效率高
explain select name,status,address from tb_seller where name='小米科技' ?and address='北京市'; ?-- 效率高
-- 如果查詢列，超出索引列，也會(huì)降低性能。
explain select name,status,address,password from tb_seller where name='小米科技' ?and address='北京市'; ?-- 效率低

-- 用or分割開的條件， 那么涉及的索引都不會(huì)被用到。
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 '%科技%';-- 不用索引
-- 彌補(bǔ)不足,不用*，使用索引列
explain select name from tb_seller where name like '%科技%';

-- ?1、如果MySQL評(píng)估使用索引比全表更慢，則不使用索引。
?-- 這種情況是由數(shù)據(jù)本身的特點(diǎn)來決定的
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 ?有時(shí)有效，有時(shí)索引失效。
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優(yōu)化

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

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 '用戶狀態(tài)',
?`create_time` datetime not null,
?`update_time` datetime default null,
?primary key (`id`),
?unique key `unique_user_username` (`username`)
);

當(dāng)使用load 命令導(dǎo)入數(shù)據(jù)的時(shí)候，適當(dāng)?shù)脑O(shè)置可以提高導(dǎo)入的效率。對(duì)于 InnoDB 類型的表，有以下幾種方式可以提高導(dǎo)入的效率：

1） 主鍵順序插入

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

-- 1、首先，檢查一個(gè)全局系統(tǒng)變量 'local_infile' 的狀態(tài)， 如果得到如下顯示 Value=OFF，則說明這是不可用的
show global variables like 'local_infile';

-- 2、修改local_infile值為on，開啟local_infile
set global local_infile=1;

-- 3、加載數(shù)據(jù)
/*
腳本文件介紹 :
? ?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 )、關(guān)閉唯一性校驗(yàn)

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

-- 關(guān)閉唯一性校驗(yàn)
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;

? 優(yōu)化insert語句

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

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

? 優(yōu)化insert語句

-- 在事務(wù)中進(jìn)行數(shù)據(jù)插入。
begin;
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');

? 優(yōu)化order by語句

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`)
);

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、兩種排序方式

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

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

3、Filesort 的優(yōu)化

通過創(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)存開銷較大，但是排序效率比兩次掃描算法要高。

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

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

? 優(yōu)化group by

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

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

create index idx_emp_age_salary on emp(age,salary)；

? 優(yōu)化子查詢

使用子查詢可以一次性的完成很多邏輯上需要多個(gè)步驟才能完成的SQL操作，同時(shí)也可以避免事務(wù)或者表鎖死，并且寫起來也很容易。但是，有些情況下，子查詢是可以被更高效的連接（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)查詢之所以更有效率一些 ，是因?yàn)镸ySQL不需要在內(nèi)存中創(chuàng)建臨時(shí)表來完成這個(gè)邏輯上需要兩個(gè)步驟的查詢工作。

? 優(yōu)化limit查詢

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

1、優(yōu)化思路一

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

2、優(yōu)化思路二

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