MySQL-索引優化案例

單表優化

建表

create table article(
id int unsigned not null primary key auto_increment,
author_id int unsigned not null,
category_id int unsigned not null,
views int unsigned not null,
comments int unsigned not null,
title varchar(255) not null,
content text not null
);
           

插入資料

insert into article(`author_id`,`category_id`,`views`,`comments`,`title`,`conte
nt`) values
(1,1,1,1,'1','1'),
(2,2,2,2,'2','2'),
(1,1,3,3,'3','3');
           

現在比如說我們要查詢category_id為1且comments大于1的情況下,views最多的article_id

我們要完成這個功能應該使用的sql語句為

我們使用explain分析這條sql語句發現出現了Using filesort,說明mysql使用了外部的索引排序，這是不好的，

我們應該通過添加索引來優化他，那麼這個索引應該添加到哪個列上呢。

前面講過使用索引查詢時範圍之後全失效。是以不能添加在comments這個列上，那我們嘗試着添加在category_id和views上。再進行explain語句分析

發現Using filesort沒了，那麼便是添加的索引有效了。

雙表優化

建表

商品類别
create table class(
	id int unsigned not null primary key auto_increment,
	card int unsigned not null
);
圖書表
create table book(
	bookid int unsigned not null auto_increment primary key,
	card int unsigned not null
);
           

添加資料

在MySQL中rand函數表示生成随機小數，floor表示将數向下取整

insert into class values(0,floor(rand()*100));
insert into book values(0,floor(rand()*100));
           

我們将這兩條資料分别執行20次，向兩個表中随機插入20條資料

現在比如說我們要查找兩個表中card相同的資料

并使用explain分析該sql語句，會發現出現了Using join buffer,使用了連接配接緩存。那麼我們也得建立索引來進行優化。那麼我們應該在哪個表上建立索引呢。

當使用左連接配接left join時，應該往右表book上添加索引

此時再使用explain分析sql語句發現Using join buffer不見了。

同理使用右連接配接right join時，應該往左表添加索引

使用inner join時随便向一個表添加索引即可

這樣添加的原因主要是因為：

驅動表的概念，mysql中指定了連接配接條件時，滿足查詢條件的記錄行數少的表為驅動表；如未指定查詢條件，則掃描行數少的為驅動表。mysql優化器就是這麼粗暴以小表驅動大表的方式來決定執行順序的。