sql优化实战

SQL优化

步骤：
使用explain执行sql语句
根据id进行逐步优化从大到小
先从查找条件以及排序、分组地方优化索引
然后从查询得字段方面优化索引。

单表优化

#表结构：文章表
CREATE TABLE `explain`.`Untitled`  (
  `id` int(11) NOT NULL,
  `author_id` int(10) NOT NULL,
  `category_id` int(10) NOT NULL,
  `views` int(10) NOT NULL,
  `comments` int(10) NOT NULL,
  `title` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `content` text CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Dynamic;

优化实战：

# 单表优化

## 准备数据
insert into article values(1,1,1,1,'1','1','1'),(2,2,2,2,'2','2','2'),(3,3,3,3,'3','3','3');


## 业务需求
### 查询分类id为1且comments大于1情况下，views最多得文章id

### 1.1完成功能
SELECT author_id from article where category_id=1 and comments>1 order by views desc limit 1;

###1.2进行分析
explain SELECT author_id from article where category_id=1 and comments>1 order by views desc limit 1;

##==result:type：all，extra：using filesort

###2.1查看表得索引并优化：解决了type为all得问题
show index from article;#only have primary key

create index idx_article_vcc on article(category_id,comments,views);

### 2.2 running and explain
explain SELECT author_id from article where category_id=1 and comments>1 order by views desc limit 1;

##==result: type:range,extra:using filesort.因为comments使用范围导致views索引失效,comments列需要移除索引

### 3.1 继续优化索引解决filesort问题
drop index idx_article_vcc on article；

create index idx_article_cv on article(category_id,views);

### 3.2 running and explain
explain SELECT author_id from article where category_id=1 and comments>1 order by views desc limit 1;

##==result：type：range，extra：no using filesort

2.2处索引失效得原因

两表优化

表结构

#card为分类card，用作两表关联字段
#商品类别
CREATE TABLE IF NOT EXISTS `class`(
`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
);
#书
CREATE TABLE IF NOT EXISTS `book`(
`bookid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
);

优化实践

## 两表索引优化

##此时两表皆只有主键索引且通过card字段相关联
## 1.1两表左连接查询
explain select * from book left join class on book.card=class.card;

##result==type皆是all且extra中using join buffer使用左连接即左表全部驱动右表，左表必定要全表扫描。故优化右表

##1.2为左连接的右表添加索引
create index idx_class_card on class(card);

explain select * from book left join class on book.card=class.card;

##result==左边依旧全表扫描，右表type变为ref


##2.1两表右连接查询
##删除上边的索引
drop index idx_class_card on class;

explain select * from book right join class on book.card=class.card;
##result==type皆是all且extra中using join buffer使用右连接即右表全部驱动左表，右表必定要全表扫描。故优化左表


##2.2为右连接的左表添加索引
create index idx_book_card on book(card);

explain select * from book right join class on book.card=class.card;
##result==右表依旧全表扫描，左表type变为ref

三表优化

表结构

#在两表基础上添加一个phone表也通过card字段关联
## 三表优化实战
###添加phone表，也通过card字段关联
CREATE TABLE IF NOT EXISTS `phone`(
`phoneid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
)ENGINE = INNODB;

优化实战
sql执行顺序为

## 三表优化实战

## 1.1
explain select * from class left join book on class.card=book.card left join phone on class.card=phone.card;

##result==执行顺序为从左往右。class左连接book后再继续左连接phone

## 1.2为被左连接的右表建立索引
create index idx_book_card on book(card);


create index idx_phone_card on phone(card);



explain select * from class left join book on class.card=book.card left join phone on class.card=phone.card;

##result==驱动表class依旧为all，被连接的表则变成了ref

总结：关于join的优化原则