MySql优化

MySql优化

1. Sql优化
   原因：性能低、执行时间长、等待时间差、sql语句欠佳(连接查询)、索引失效、服务器参数设置不合理
   1.1． sql解析过程
   
   Mysql语法顺序

SELECT DISTINCT
    < select_list >
FROM
    < left_table > < join_type >
JOIN < right_table > ON < join_condition >
WHERE
    < where_condition >
GROUP BY
    < group_by_list >
HAVING
    < having_condition >
ORDER BY
    < order_by_condition
LIMIT < limit_number >

Mysql执行顺序：

--行过滤
 1 FROM <left_table>
 2 ON <join_condition>
 3 <join_type> JOIN <right_table> 
 4 WHERE <where_condition>        
 5 GROUP BY <group_by_list>
 6 HAVING <having_condition>
 --列过滤
 7 SELECT                         
 8 DISTINCT <select_list>
 --排序
 9 ORDER BY <order_by_condition>
 -- MySQL附加
10 LIMIT <limit_number>           

第二步和第三步会循环执行
第四步会循环执行，多个条件的执行顺序是从左往右的。
分组之后才会执行SELECT
前9步都是SQL92标准语法。limit是MySQL的独有语法。

流程说明：

单表查询：根据WHERE条件过滤表中的记录，形成中间表（这个中间表对用户是不可见的）；然后根据
SELECT的选择列选择相应的列进行返回最终结果。
两表连接查询：对两表求积（笛卡尔积）并用ON条件和连接类型进行过滤形成中间表；然后根据WHERE
条件过滤中间表的记录，并根据SELECT指定的列返回查询结果。
笛卡尔积：行相乘、列相加。
多表连接查询：先对第一个和第二个表按照两表连接做查询，然后用查询结果和第三个表做连接查询，以此类推，直到所有的表都连接上为止，最终形成一个中间的结果表，然后根据WHERE条件过滤中间表的记录，并根据SELECT指定的列返回查询结果。
***WHERE条件解析顺序 ***
MySQL ：从左往右去执行 WHERE 条件的。
Oracle ：从右往左去执行 WHERE 条件的。
1.2 . sql优化
平常工作sql优化一般指优化索引

1.2.1 索引分类

1.  单值索引：一个表可以有多个  主键索引：索引所在字段不能为null  唯一索引：不能重复,索引所在字段可以为null
    复合索引：多个列构成

1.2.2 创建索引

第一种方式

Create 索引类型 索引名 on 表(字段)
单值

create index dept_index on tb(dept);

唯一：

create unique index dept_index on tb(dept);

复合：

create index dept_name_index on tb(dept,name);

第二种方式

Alter table 表名 索引类型 索引名（字段）
单值

alter table tb add index dept_index(dept)；

唯一：

alter table tb add unique index dept_index(dept)；

复合：

alter table tb add index dept_name_index(dept,name)；

注意：如果一个字段是primary key,则该字段默认就是主键索引

1.2.3  删除索引

Drop index 索引名on 表名；

drop index name_index on tb;

1.2.4 查询索引
show index from 表名；
1.2.5 sql性能问题

1. 分析sql执行计划： explain，可以模拟sql优化器执行sql语句 
   

2. Mysql优化器的优化会干扰我们自己的的优化

3. 查询执行计划 ： explain + sql语句
   

课程表：

create table course
(
  cid int(3),
  cname varchar(20),
  tid int(3)
);

教师表：

create table teacher
(
  tid int (3),
  tname varchar(20),
  tcid int(3)
);

教师证表：

create table teacher_card
(
  tcid int(3),
  tcdesc varchar(200)
);

insert into course values(1,'java',1);
insert into course values(2,'html',1);
insert into course values(3,'sql',2);
insert into course values(4,'web',3);

insert into teacher values(1,'tz',1);
insert into teacher values(2,'tw',2);
insert into teacher values(3,'tl',3);

insert into teacher_card values(1,'tzdesc');
insert into teacher_card values(2,'twdesc');
insert into teacher_card values(3,'tldesc');

查看表引擎：show create table 表名;

1. 查询课程编号为2或教师证编号为3的老师信息

explain Select t.* from teacher t,course c,teacher_card tc
where t.tid=c.tid and t.tcid=tc.tcid and (c.cid=2 or tc.tcid=3);

a) id ：编号,

       1.id相同从上往下执行，
       2.ID不同，Id越大越优先，
       3.id值有相同有不同，ID值越大越先执行，ID值相同从上往下顺序执行

现在三张表执行顺序：

1. t3 -tc3- c4  (数字表示表里的数据)
2.	给t表增加数据 

     insert into teacher values(4,'ta',4); insert into teacher values(5,'tb',5); insert into teacher values(6,'tc',6);

执行sql计划发现执行顺序发生变化，数据量小的表优先（我这个显示与教程不一致需要重新确认）执行（原因：笛卡尔积）

b) select_type ：查询类型

1. PRIMARY:包含子查询SQL中的主查询（最外层查询）
2. SUBQUERY:包含子查询sql中的子查询（非最外层查询）
   eg.

 explain select tc.tcdesc from teacher_card tc where tc.tcid=(
 select t.tcid from teacher t where t.tid=(select c.tid from course c where c.cname='sql')
);

执行顺序按Id 3 2 1
eg.

explain select t.tname,tc.tcdesc from teacher t,teacher_card tc where t.tcid=tc.tcid and t.tid=(select c.tid from course c where cname='sql');

执行顺序：先执行ID为2的，Id为1的从上到下执行
3. SIMPLE:简单查询（不包含子查询、union）
4. DERIVED:衍生查询（查询的时候使用到了临时表）

1)	在from子查询中只有一张表(这个在我这个mysql版本显示是简单查询，可能被优化了，与教程不同)
Eg.
explain select cr.cname from (select * from course where tid in (1,2))cr;

``

我的sql版本5.7

 2)	在from子查询中，如果有table1 union table2,则table1就是derived,table2就是union
Eg. 
explain select cr.cname from (select * from course where tid=1
union select * from course where tid=2)cr;

5.UNION
c) table ：表
d) partitions
e) type：
索引类型，常用system>const>eq_ref>ref>range>index>all
实际优化达到ref、range级别，优化前提：有索引
system:只有一条数据的系统表或衍生表只有一条数据的主查询

create table table01(tid int(3),tname varchar(20));
insert into table01 values(1,'a');
alter table table01 add constraint tid_pk primary key(tid);

如下图结果与教程不符，为const：

const:仅仅能查到一条数据的SQL，用于Primary key或unique索引（类型与索引类型有关Primary key或unique）

explain select tid from table01 where tid=1;

删除主键
alter table table01 drop primary key;

加一普通索引，非unique
create index table01_index on table01(tid);非const

eq_ref:唯一性索引，对于每个索引键的查询，返回匹配唯一行数据（有且只有1个，不能多，不能0），常见于唯一索引和主键索引

alter table teacher_card add constraint pk_tcid primary key(tcid);
alter table teacher add constraint uk_tcid unique index(tcid);
explain select t.tcid from teacher t,teacher_card tc where t.tcid=t.tcid;

不是eq_ref级别

查看数据，存在为0的情况（一个表5条数据一个表6条，关联查询有5条数据，存在0的情况）


调整数据

ref:非唯一性索引，对于每个索引键返回0或多条数据
见上面图
f) possible_keys：预测用到的索引
g) key：实际使用的索引
h) key_len
i) ref ：表之间的引用
j) rows： 通过索引查询到的数据条数
k) filtered
l) Extra： 额外的信息