2.索引优化分析

1.常见通用的Join查询

建表SQL

CREATE TABLE `t_dept` (
 `id` INT(11) NOT NULL AUTO_INCREMENT,
 `deptName` VARCHAR(30) DEFAULT NULL,
 `address` VARCHAR(40) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
 
CREATE TABLE `t_emp` (
 `id` INT(11) NOT NULL AUTO_INCREMENT,
 `name` VARCHAR(20) DEFAULT NULL,
  `age` INT(3) DEFAULT NULL,
 `deptId` INT(11) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `fk_dept_id` (`deptId`)
 #CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
 
 
 
INSERT INTO t_dept(deptName,address) VALUES('华山','华山');
INSERT INTO t_dept(deptName,address) VALUES('丐帮','洛阳');
INSERT INTO t_dept(deptName,address) VALUES('峨眉','峨眉山');
INSERT INTO t_dept(deptName,address) VALUES('武当','武当山');
INSERT INTO t_dept(deptName,address) VALUES('明教','光明顶');
 INSERT INTO t_dept(deptName,address) VALUES('少林','少林寺');
 
INSERT INTO t_emp(NAME,age,deptId) VALUES('风清扬',90,1);
INSERT INTO t_emp(NAME,age,deptId) VALUES('岳不群',50,1);
INSERT INTO t_emp(NAME,age,deptId) VALUES('令狐冲',24,1);
 
 INSERT INTO t_emp(NAME,age,deptId) VALUES('洪七公',70,2);
INSERT INTO t_emp(NAME,age,deptId) VALUES('乔峰',35,2);
 
INSERT INTO t_emp(NAME,age,deptId) VALUES('灭绝师太',70,3);
INSERT INTO t_emp(NAME,age,deptId) VALUES('周芷若',20,3);
 
 
 
INSERT INTO t_emp(NAME,age,deptId) VALUES('张三丰',100,4);
 
INSERT INTO t_emp(NAME,age,deptId) VALUES('张无忌',25,5);
 
INSERT INTO t_emp(NAME,age,deptId) VALUES('韦小宝',18,null);
 

7种JOIN

t_emp表

select * from t_emp;

t_dept表

1.1 A、B两表共有

select * from t_emp inner join t_dept on t_emp.deptId = t_dept.id;

1.2 A、B两表共有+A的独有

select * from t_emp left join t_dept on t_emp.deptId = t_dept.id;

1.3 A、B两表共有+B的独有

select * from t_emp right join t_dept on t_emp.deptId = t_dept.id;

1.4 A的独有

select * from t_emp left join t_dept on t_emp.deptId = t_dept.id where t_dept.id is null;

1.5 B的独有

select * from t_emp right join t_dept on t_emp.deptId = t_dept.id where t_emp.deptId is null;

1.6 AB全有

select * from t_emp full outer join t_dept on t_emp.deptId = t_dept.id;

注意：MySQL不支持FULL OUTER JOIN

mysql> select * from t_emp left join t_dept on t_emp.deptId = t_dept.id
    -> union
    -> select * from t_emp right join t_dept on t_emp.deptId = t_dept.id where t_emp.deptId is null;

1.7 A的独有+B的独有

mysql> select * from t_emp left join t_dept on t_emp.deptId = t_dept.id where t_dept.id is null
    -> union
    -> select * from t_emp right join t_dept on t_emp.deptId = t_dept.id where t_emp.deptId is null;

2.索引简介

2.1 基本语法

3.性能分析

3.1 Explain（查看执行计划）

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈

使用：Explain + SQL语句

执行计划包含的信息

3.1.1 各字段解析

type

type显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依次是：

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

system

1.表中只有一条数据
2.通过常量值匹配主键或者唯一索引

const

1.主键或者唯一索引
2.常量匹配

eq_ref

1.主键或者唯一索引扫描

ref

1.非主键或者唯一索引扫描
2.返回的结果不止一个

range

1.检索给定范围的行
2.关键字：between，< >,in

index

1.全索引扫描

ALL

1.全表扫描

possible_keys(理论上可能被用到的索引)

key（实际用到的索引）

key_len(索引中使用的字节数)

ref（显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值）

4.查询优化

4.1 使用索引

建表语句

 
CREATE TABLE staffs (
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR (24)  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 '员工记录表' ;
 
 
INSERT INTO staffs(NAME,age,pos,add_time) VALUES('z3',22,'manager',NOW());
INSERT INTO staffs(NAME,age,pos,add_time) VALUES('July',23,'dev',NOW());
INSERT INTO staffs(NAME,age,pos,add_time) VALUES('2000',23,'dev',NOW());
INSERT INTO staffs(NAME,age,pos,add_time) VALUES(null,23,'dev',NOW());
SELECT * FROM staffs;
 
ALTER TABLE staffs ADD INDEX idx_staffs_nameAgePos(name, age, pos);

索引失效

1.全值匹配我最爱

索引  idx_staffs_nameAgePos 建立索引时 以 name ， age ，pos 的顺序建立的。全值匹配表示 按顺序匹配的
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July';
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age = 25;
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age = 25 AND pos = 'dev';

2.最佳左前缀法则
如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列

 and 忽略左右关系。既即使没有没有按顺序 由于优化器的存在，会自动优化。
经过试验结论  建立了 idx_nameAge 索引  id 为主键
    1.当使用覆盖索引的方式时，(select name/age/id from staffs where age=10 (后面没有其他没有索引的字段条件))，即使不是以 name 开头，也会使用 idx_nameAge 索引。
    既 select 后的字段 有索引，where 后的字段也有索引，则无关执行顺序。
    2.除开上述条件 才满足最左前缀法则。
 
EXPLAIN SELECT * FROM staffs WHERE age = 25 AND pos = 'dev';
 
EXPLAIN SELECT * FROM staffs WHERE pos = 'dev';

3.不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描

4.存储引擎不能使用索引中范围条件右边的列
范围 若有索引则能使用到索引，范围条件右边的索引会失效(范围条件右边与范围条件使用的同一个组合索引，右边的才会失效。若是不同索引则不会失效)

5.尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select *

6.mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描
索引 idx_nameAgeJob
idx_name
使用 != 和 <> 的字段索引失效( != 针对数值类型。 <> 针对字符类型
前提 where and 后的字段在混合索引中的位置比比当前字段靠后 where age != 10 and name=‘xxx’ ,这种情况下，mysql自动优化，将 name=‘xxx’ 放在 age ！=10 之前，name 依然能使用索引。只是 age 的索引失效)

7.is not null 也无法使用索引,但是is null是可以使用索引的

8.like以通配符开头(’%abc…’)mysql索引失效会变成全表扫描的操作
解决此问题应该用覆盖索引。

9.字符串不加单引号索引失效

10.少用or,用它来连接时会索引失效