SQL调优

---

前言

SQL编写过程：select dinstinct from join … on … where… group by … having … order by

SQL解析过程：from … on … join … where… group by… having …select dinstinct …order by

---

提示：以下是本篇文章正文内容，下面案例可供参考

一、SQL优化

SQL优化主要就是优化索引

• 索引：相当于书的目录
• 索引： index是帮助MYSQL增加高效获取数据的一种数据结构。索引是数据结构，（树：B树（默认），hash树）B树默认是B+树（数据全部存放在叶子节点），所以B+树默认查询的次数都是N次，N指B+树的高度

索引的弊端

• 索引本身很大，可以存放在硬盘/内存（通常为硬盘）
• 索引并不是所有情况都适合用：
  • a.少量的数据
  • 频繁更新的字段
  • 很少使用的字段
• 索引会降低增删改的效率，提高查的效率

索引的优势

• 提高查询效率（降低了IO的使用率）查询的本质是IO输入输出流
• 降低CPU使用率（… order by age desc 因为索引已经是B树有顺序的结构，只需要前序遍历就可以排序好）

二、索引

1.索引的分类

• 主键索引：表中值不能重复，且不能为NULL
• 单值索引：单列，例如给一个表中的age字段加索引，则age就叫做单值索引。一个表可以有多个单值索引。
• 唯一索引：表中值不能重复。一般用id，可以为NULL
• 复合索引：多个列组成的。相当于书的二级目录 （name，age），（a,b,c,d …）

2.创建索引

方式一：
   Create 索引类型 索引名 on 表（字段）
   单值索引：
   Create index dept_index on user(dept);
   唯一索引：
   Create unique index id_index on user(id);
   复合索引:
   Create index dept_name_index on user(dept,name);
方式二：
   alter table 表名 add 索引类型  索引名（字段）
   单值索引：
   alter table user add index dept_index(dept);
   唯一索引：
   alter table user add unique index id_index(id);
   复合索引:
   alter table user add index dept_name_index(dept,name);


删除索引：
   drop index 索引名 on 表名;
查询索引
   show index from 表名

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

2.SQL性能问题

• 分析SQL的执行计划：explain，可以模拟Sql优化器，执行sql语句。从而让开发人员知道sql编写的状况
• MySql查询优化器，会干扰我们的优化

查询执行计划：explain + SQL语句
id 编号 |
select_type 查询类型|
table 类型 |
partitions |
type |
possible_keys 预测用到的索引
key实际使用的索引 |
key_len 实际使用索引的长度 |
ref 表和表之间的引用关系|
rows 通过索引查到的数据个数|
filtered |
Extra额外信息

• id相同，从上往下顺序执行 t3-tc3-c4

• 当增加几条数据之后：tc3-t6-c4
  
  表的执行顺序发生改变，因数量的个数改变而改变原因为：笛卡尔积
  
  数据小的表，优先查询

• id值不同，id值越大，越优先被查询
  

	将多表查询改为子查询
	EXPLAIN SELECT tc.tcdesc FROM teachercard tc,course c,teacher t WHERE
	 t.tid=c.tid AND t.tcid=tc.tcid AND (c.cname='sql'); 
	子查询
	select tc.tcdesc from teachercard where tc.tcid=
	(select t.tcid from teacher t where t.tid=
	  (select c.tid from course c c.cname ='sql' )
	);

select_type 查询类型:

• PRIMARY ：包含子查询sql中的主查询（最外层）
• SUBQUERY：包含子查询SQL中的子查询（非最外层）
• simple：简单查询（不包含子查询，union连接查询）
• derived：衍生查询（使用到了临时表）
  a.在from子查询中，只有一张表
  select *from
  b.在from子查询中如果有table1 union table2 ，则table1就是衍生查询

type类型、索引类型：
system>const>eq_ref>ref>range>index>all

• 对type优化的前提是有索引，越靠前，性能越好
  a.system 与const都是理想化存在的类型，实际能达到ref > range
  eq_ref :唯一性索引:对于每个索引键的查询，返回匹配唯一行数据（有且只有一个，不能多不能为0）常见于主键索引和唯一索引

EXPLAIN SELECT t.tcid FROM teacher t,teachercard tc WHERE t.tcid=tc.tcid;

• 当前sql语句用到的索引是t.tcid ，及teacher表中的tcid字段，如果teacher中的数据个数与连接表查出的数据个数一致，则type为eq-ref

ref：非唯一性索引，对每个索引键的查询，返回匹配的所有行（0，多）
range：检索指定范围的行，where后面是一个范围查询（between，>,<,=，特殊： in不一定，有时候会转换为all，索引失效）
index：查询全部索引中数据

ALL：查询全部表中所有数据

总结

system、const：返回结果只有一条
eq-ref：返回结果是多条，但是每条数据必须是唯一的
ref：返回结果多条，但是每条数据是0条或者多条

---