MySQL效能最佳化（六）

標籤：logs   單表   test   順序   esc   mysql   ora   分析   儲存   

 

一、order by產生using filesort詳解

　　1.首先建表和索引

/*課程表*/create table course(    id int primary key auto_increment,/* 主鍵自增*/    title varchar(50) not null,/* 標題*/    category_id int not null,/* 屬於哪個類目*/    school_id int not null,/* 屬於哪個學校*/    buy_times int not null,/* 購買次數*/    browse_times int not null/* 瀏覽次數*/);insert into course(title,category_id,school_id,buy_times,browse_times) values(‘java課程‘,1,1,800,8680);insert into course(title,category_id,school_id,buy_times,browse_times) values(‘android課程‘,2,1,400,8030);insert into course(title,category_id,school_id,buy_times,browse_times) values(‘mysql課程‘,3,2,200,2902);insert into course(title,category_id,school_id,buy_times,browse_times) values(‘oracle課程‘,2,2,100,6710);insert into course(title,category_id,school_id,buy_times,browse_times) values(‘C#課程‘,1,3,620,2890);insert into course(title,category_id,school_id,buy_times,browse_times) values(‘PS課程‘,4,4,210,4300);insert into course(title,category_id,school_id,buy_times,browse_times) values(‘CAD課程‘,5,1,403,6080);/*在category_id和buy_times上建立複合式索引*/create index idx_cate_buy on course(category_id,buy_times);

 

 

　　2.order by 會產生 using filesort的有哪些？

　　（1）explain select id from course where category_id>1 order by category_id;

　　　　根據最左首碼原則，order by後面的的category_id會用到複合式索引

　　（2）explain select id from course where category_id>1 order by category_id,buy_times;

　　　　根據最左首碼原則，order by後面的的category_id buy_times會用到複合式索引，因為索引就是這兩個欄位

　　（3）explain select id from course where category_id>1 order by buy_times;

　　　　根據最左首碼原則，order by後面的欄位是缺少了最左邊的category_id，所以會產生 using filesort

　　（4）explain select id from course where category_id>1 order by buy_times,category_id;

　　　　order by後面的欄位順序不符合複合式索引中的順序，所以order by後面的不會走索引，即會產生using filesort

　　（5）explain select id from course order by category_id;

　　　　根據最左首碼原則，order by後面存在索引中的最左列，所以會用到索引

　　（6）explain select id from course order by buy_times;

　　　　根據最左首碼原則，order by後面的欄位 沒有索引中的最左列的欄位，所以不會走索引，會產生using filesort

　　（7）explain select id from course where buy_times > 1 order by buy_times;

　　　　根據最左首碼原則，order by後面的欄位 沒有索引中的最左列的欄位，所以不會走索引，會產生using fillesort

　　（8）explain select id from course where buy_times > 1 order by category_id;

　　　　根據最左首碼原則，order by後面的欄位存在於索引中最左列，所以會走索引

　　（9）explain select id from course order by buy_times desc,category_id asc;

　　　　根據最最左首碼原則，order by後面的欄位順序和索引中的不符合，則會產生using filesort

　　（10）explain select id from course order by category_id desc,buy_times asc;

　　　　這一條雖然order by後面的欄位和索引中欄位順序相同，但是一個是降序，一個是升序，所以也會產生using filesort，同時升序和同時降序就不會產生using filesort了

二、in和exists哪個效能更優　　sql指令碼：

/*建庫*/create database testdb6;use testdb6;/* 使用者表 */drop table if exists users;create table users(id int primary key auto_increment,name varchar(20));insert into users(name) values (‘A‘);insert into users(name) values (‘B‘);insert into users(name) values (‘C‘);insert into users(name) values (‘D‘);insert into users(name) values (‘E‘);insert into users(name) values (‘F‘);insert into users(name) values (‘G‘);insert into users(name) values (‘H‘);insert into users(name) values (‘I‘);insert into users(name) values (‘J‘);/* 訂單表 */drop table if exists orders;create table orders(id int primary key auto_increment,/*訂單id*/order_no varchar(20) not null,/*訂單編號*/title varchar(20) not null,/*訂單標題*/goods_num int not null,/*訂單數量*/money decimal(7,4) not null,/*訂單金額*/user_id int not null    /*訂單所屬使用者id*/)engine=myisam default charset=utf8 ;delimiter $$drop procedure batch_orders $$/* 預存程序 */create procedure batch_orders(in max int)begindeclare start int default 0;declare i int default 0;set autocommit = 0;   while i < max do   set i = i + 1;   insert into orders(order_no,title,goods_num,money,user_id)    values (concat(‘NCS-‘,floor(1 + rand()*1000000000000 )),concat(‘訂單title-‘,i),i%50,(100.0000+(i%50)),i%10); end while;commit;end $$delimiter ;/*插入1000萬條訂單資料*/call batch_orders(10000000);     /*插入資料的過程根據機器的效能 花費的時間不同，有的可能3分鐘，有的可能10分鐘*/

 

　　上面的sql中 訂單表中（orders） 存在user_id，而又有使用者表（users），所以我們用orders表中user_id和user表中的id 來in 和 exists。

　　結果分析

　　1.where後面是小表

　　（1）select count(1) from orders o where o.user_id in(select u.id from users u);

　　

 

　　（2）select count(1) from orders o where exists (select 1 from users u where u.id = o.user_id);

 　　

 

　　

三、慢查詢

 

四、鎖機制

MySQL效能最佳化（六）