MySQL SQL优化一

建表语句：

tb_item与tb_item01、tb_item02、tb_item03表结构一致

create table `tb_item` (
	`id` double ,
	`title` varchar (300),
	`sell_point` varchar (1500),
	`price` double ,
	`num` double ,
	`barcode` varchar (90),
	`image` varchar (1500),
	`cid` double ,
	`status` tinyint (4),
	`created` datetime ,
	`updated` datetime 
); 
insert into `tb_item` (`id`, `title`, `sell_point`, `price`, `num`, `barcode`, `image`, `cid`, `status`, `created`, `updated`) values('536563','阿尔卡特','29900000','99999','','','560','1','2015-03-08 21:33:18','2015-04-11 20:38:38');
insert into `tb_item` (`id`, `title`, `sell_point`, `price`, `num`, `barcode`, `image`, `cid`, `status`, `created`, `updated`) values('562379','三星','下单','1100','99999','','','560','1','2015-03-08 21:27:54','2015-04-12 17:10:43');

索引单表优化： 

EXPLAIN 
SELECT * FROM tb_item WHERE title = '中兴 U288 珠光白 移动3G手机' AND price>0 ORDER BY created DESC LIMIT 1;

优化

CREATE INDEX idx_title_p_created ON tb_item(title,price,created);

发现还有：Using filesort

DROP INDEX idx_title_p_created ON tb_item;

 分析：

由于出现type = range，这是可以忍受的。但是extra里使用Using filesort是无法接受的。

我们建立的索引失效的原因是由于先排序title，再排序price，在排序created。但是price>0是一个范围，MySQL无法使用索引对后面的created进行检索。

结论：range类型查询字段后面的索引无效。

继续优化

CREATE INDEX idx_title_created ON tb_item(title,created);

结果：优化成功。

 

索引两表优化：

EXPLAIN
SELECT * FROM tb_item_01 t1 LEFT JOIN tb_item_02 t2 ON t1.title = t2.title;

优化：给右表加索引

CREATE INDEX idx_title ON tb_item_02(title);

 给左表加索引

DROP INDEX idx_title ON tb_item_02;
CREATE INDEX idx_title ON tb_item_01(title);

总结：左连接加右表。右连接加左表。

 

索引优化三表查询：

EXPLAIN
SELECT * FROM tb_item_01 t1 
LEFT JOIN tb_item_02 t2 ON t1.title = t2.title
LEFT JOIN tb_item_03 t3 ON t1.title = t3.title;

 

优化

CREATE INDEX idx_title_02 ON tb_item_02(title);
CREATE INDEX idx_title_03 ON tb_item_03(title);

 

总结：

join的优化：尽可能减少join语句中的NestedLoop的循环总次数：“永远用小的结果集，驱动大的结果集”。

优先优化NestedLoop的内层循环。

保证join语句中被驱动表上join条件字段已经被索引。

当无法保证驱动表的join条件字段被索引且内存资源充足的前提下，不要太吝啬Joinbuffer的设置。