MySQL高阶调优，一文让你从入门到精通！

select * from A left join B on A.key = B.key
复制

select * from A inner join B on A.key = B.key
复制

select * from A right join B on A.key = B.key
复制

select * from A LEFT JOIN B on A.aid = B.bid    左连接
where B.bid is null   B不在A的部分
复制

select * from A RIGHT JOIN B on A.aid = B.bid    右连接
where A.aid is null   A不在B的部分
复制

select * from A LEFT JOIN B on A.aid = B.bid    左连接
where B.bid is null   B不在A的部分
union
select * from A RIGHT JOIN B on A.aid = B.bid    右连接
where A.aid is null   A不在B的部分
复制

select * from A LEFT JOIN B on A.aid = B.bid  
union
select * from A RIGHT JOIN B on A.aid = B.bid
复制

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
复制

system>const>eq_ref>ref>range>index>ALL
复制

article表
CREATE TABLE IF NOT EXISTS `article`(
`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`author_id` INT (10) UNSIGNED NOT NULL,
`category_id` INT(10) UNSIGNED NOT NULL , 
`views` INT(10) UNSIGNED NOT NULL , 
`comments` INT(10) UNSIGNED NOT NULL,
`title` VARBINARY(255) NOT NULL,
`content` TEXT NOT NULL
);


class表 
CREATE TABLE IF NOT EXISTS `class`(
`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
);


book表 
CREATE TABLE IF NOT EXISTS `book`(
`bookid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
);
 
phone表
CREATE TABLE IF NOT EXISTS `phone`(
`phoneid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
)ENGINE = INNODB;


staffs表
CREATE TABLE staffs(
id INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(24)NOT NULL DEFAULT'' COMMENT'姓名',
`age` INT NOT NULL DEFAULT 0 COMMENT'年龄',
`pos` VARCHAR(20) NOT NULL DEFAULT'' COMMENT'职位',
`add_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT'入职时间'
)CHARSET utf8 COMMENT'员工记录表';


ALTER TABLE staffs ADD INDEX index_staffs_nameAgePos(`name`,`age`,`pos`)
复制

select id, author_id 
from article 
where category_id = 1 
and comments > 1 
order by views desc limit 1;
复制

create index idx_atc_ccv on article(category_id,comments,views);
复制

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

alter table book add index idx_b_card(card);
复制

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

create index idx_s_nap on staffs(name,age,pos);
复制

select * from staffs where name='July'; 
复制

select * from staffs where name='July' and age = 25; 
复制

select pos from staffs where name='July' and age = 25; 
复制

select * from staffs where age = 23 and pos = 'dev';
复制

select * from staffs where name = 'zhangsan';
复制

select * from staffs where name = 'zhangsan' and pos = 'dev'; 
复制

select * from staffs where left(name, 4) = 'July';
复制

select * from staffs where name = 'July' and age > 14 and pos = 'manager'; 
复制

select * from staffs where name != 'July'; 
复制

select * from staffs where name <> 'July';
复制

select * from staffs where name is null; 
select * from staffs where name is not null;
复制

select * from staffs where name like '%July%';
select * from staffs where name like '%July'; 
select * from staffs where name like 'July%'; 
复制

explain select * from staffs where name = 222; 
复制

select * from staffs where name = '222';
复制

select * from staffs where name = 'July' or name = 'z3'; 
复制